################################################################### # Copyright (c) 2016 by TAOS Technologies, Inc. # All rights reserved. # # This file is proprietary and confidential to TAOS Technologies. # No part of this file may be reproduced, stored, transmitted, # disclosed or used in any form or by any means other than as # expressly provided by the written permission from Jianhui Tao # ################################################################### # -*- coding: utf-8 -*- import sys from numpy.lib.function_base import insert import taos from util.log import * from util.cases import * from util.sql import * import numpy as np # constant define WAITS = 10 # wait seconds class TDTestCase: updatecfgDict = {'numOfNodes': 3, '1':{'replica': 3}, '2':{'replica': 3}, '3':{'replica': 3}} # init def init(self, conn, logSql): tdLog.debug("start to execute %s" % __file__) tdSql.init(conn.cursor()) tdSql.prepare() self.create_tables() self.ts = 1500000000000 # run case def run(self): # insert data for i in range(10): tbname = "t%d"%i self.insert_data(tbname, self.ts, (i+1)*10000, 20000); tdLog.debug(" INSERT data 10 tables ....... [OK]") # test base case self.test_case1() tdLog.debug(" DELETE test_case1 ............ [OK]") # test advance case self.test_case2() tdLog.debug(" DELETE test_case2 ............ [OK]") # test advance case self.test_case3() tdLog.debug(" DELETE test_case3 ............ [OK]") # stop def stop(self): tdSql.close() tdLog.success("%s successfully executed" % __file__) # # --------------- case ------------------- # # create table def create_tables(self): # super table tdSql.execute("create table st(ts timestamp, i1 int) tags(area int)") # child table for i in range(10): sql = "create table t%d using st tags(%d)"%(i, i) tdSql.execute(sql) return # insert data1 def insert_data(self, tbname, ts_start, count, batch_num): pre_insert = "insert into %s values"%tbname sql = pre_insert tdLog.debug("doing insert table %s rows=%d ..."%(tbname, count)) for i in range(count): sql += " (%d,%d)"%(ts_start + i*1000, i) if i >0 and i%batch_num == 0: tdSql.execute(sql) sql = pre_insert # end sql if sql != pre_insert: tdSql.execute(sql) return # test case1 base def test_case1(self): # # delete from single table # # where < sql = "select count(*) from t0 where ts < 1500000120000" tdSql.query(sql) tdSql.checkData(0, 0, 120) sql = "delete from t0 where ts < 1500000120000" tdSql.execute(sql) tdSql.checkAffectedRows(120) sql = "select count(*) from t0" tdSql.query(sql) tdSql.checkData(0, 0, 10000 - 120 ) sql = "select * from t0 limit 1" tdSql.query(sql) tdSql.checkData(0, 1, 120) # where > and < sql = "delete from t0 where ts > 1500000240000 and ts <= 1500000300000" tdSql.execute(sql) tdSql.checkAffectedRows(60) sql = "select count(*) from t0" tdSql.query(sql) tdSql.checkData(0, 0, 10000 - 120 - 60) sql = "select * from t0 limit 2 offset 120" tdSql.query(sql) tdSql.checkData(0, 1, 240) tdSql.checkData(1, 1, 301) # where > delete 1000 rows from end sql = "delete from t0 where ts >= 1500009000000; " tdSql.execute(sql) tdSql.checkAffectedRows(1000) sql = "select count(*) from t0" tdSql.query(sql) tdSql.checkData(0, 0, 10000 - 120 - 60 - 1000) sql = "select last_row(*) from t0; " tdSql.query(sql) tdSql.checkData(0, 1, 8999) sql = "select last(*) from t0" tdSql.query(sql) tdSql.checkData(0, 1, 8999) # insert last_row sql = "insert into t0 values(1500009999000,9999); " tdSql.execute(sql) sql = "select last_row(*) from t0; " tdSql.query(sql) tdSql.checkData(0, 1, 9999) sql = "select last(*) from t0" tdSql.query(sql) tdSql.checkData(0, 1, 9999) # insert last sql = "insert into t0 values(1500010000000,10000); " tdSql.execute(sql) sql = "insert into t0 values(1500010002000,NULL); " tdSql.execute(sql) sql = "insert into t0 values(1500010001000,10001); " tdSql.execute(sql) sql = "delete from t0 where ts = 1500010001000; " tdSql.execute(sql) sql = "select last_row(i1) from t0; " tdSql.query(sql) tdSql.checkData(0, 0, None) sql = "select last(i1) from t0; " tdSql.query(sql) tdSql.checkData(0, 0, 10000) # delete whole sql = "delete from t0;" tdSql.execute(sql) tdSql.checkAffectedRows(8823) return # test advance def test_case2(self): # # delete from super table # # where < sql = "select count(*) from st where ts < 1500000120000;" tdSql.query(sql) tdSql.checkData(0, 0, 9*120) #1080 sql = "delete from st where ts < 1500000120000;" tdSql.execute(sql) tdSql.checkAffectedRows(9*120) #1080 sql = "select count(*) from st;" tdSql.query(sql) tdSql.checkData(0, 0, 540000 - 9*120 ) sql = "select * from st limit 1;" tdSql.query(sql) tdSql.checkData(0, 1, 120) # where > and < sql = "delete from st where ts > 1500000240000 and ts <= 1500000300000;" tdSql.execute(sql) tdSql.checkAffectedRows(9*60) sql = "select count(*) from st;" tdSql.query(sql) tdSql.checkData(0, 0, 540000 - 9*120 - 9*60) sql = "select * from st limit 2 offset 120" tdSql.query(sql) tdSql.checkData(0, 1, 240) tdSql.checkData(1, 1, 301) # where > delete 1000 rows from end sql = "delete from st where ts >= 1500009000000; " tdSql.execute(sql) tdSql.checkAffectedRows(459000) sql = "select count(*) from st;" tdSql.query(sql) tdSql.checkData(0, 0, 79380) sql = "select last_row(*) from st; " tdSql.query(sql) tdSql.checkData(0, 1, 8999) sql = "select last(*) from st" tdSql.query(sql) tdSql.checkData(0, 1, 8999) # insert last_row sql = "insert into t0 values(1500009999000,9999); " tdSql.execute(sql) sql = "select last_row(*) from st; " tdSql.query(sql) tdSql.checkData(0, 1, 9999) sql = "select last(*) from st" tdSql.query(sql) tdSql.checkData(0, 1, 9999) # insert last sql = "insert into t0 values(1500010000000,10000); " tdSql.execute(sql) sql = "insert into t0 values(1500010002000,NULL); " tdSql.execute(sql) sql = "insert into t0 values(1500010001000,10001); " tdSql.execute(sql) sql = "delete from t0 where ts = 1500010001000; " tdSql.execute(sql) sql = "select last_row(i1) from st; " tdSql.query(sql) tdSql.checkData(0, 0, None) sql = "select last(i1) from st; " tdSql.query(sql) tdSql.checkData(0, 0, 10000) # delete whole sql = "delete from st;" tdSql.execute(sql) tdSql.checkAffectedRows(79383) return # verify function results after delete def test_case3(self): tdSql.execute("create database test") tdSql.execute("use test") self.create_tables() # insert data for i in range(10): tbname = "t%d"%i self.insert_data(tbname, self.ts, (i+1)*10000, 20000); tdSql.query("select count(*) from st") tdSql.checkData(0, 0, 550000) count = tdSql.getData(0, 0) tdSql.query("select sum(i1) from st") sum = tdSql.getData(0, 0) tdSql.query("select avg(i1) from st") avg = tdSql.getData(0, 0) tdSql.query("select count(*) from t0") count1 = tdSql.getData(0, 0) tdSql.query("select sum(i1) from t0") sum1 = tdSql.getData(0, 0) tdSql.query("select avg(i1) from t0") avg1 = tdSql.getData(0, 0) tdSql.execute("delete from st where tbname='t0'") tdSql.checkAffectedRows(count1) tdSql.query("select count(*) from st") tdSql.checkData(0, 0, count - count1) tdSql.query("select sum(i1) from st") tdSql.checkData(0, 0, sum - sum1) tdSql.query("select avg(i1) from st") tdSql.checkData(0, 0, (sum - sum1) / (count - count1)) # # add case with filename # tdCases.addWindows(__file__, TDTestCase()) tdCases.addLinux(__file__, TDTestCase())