def run(self):
tdSql.prepare()
startTime = time.time()
print("==============step1")
sql = "create table stb(ts timestamp, "
for i in range(1022):
sql += "col%d binary(14), " % (i + 1)
sql += "col1023 binary(22))"
tdSql.execute(sql)
for i in range(4096):
sql = "insert into stb values(%d, "
for j in range(1022):
str = "'%s', " % self.get_random_string(14)
sql += str
sql += "'%s')" % self.get_random_string(22)
tdSql.execute(sql % (self.ts + i))
tdSql.query("select * from stb")
tdSql.checkRows(4096)
tdDnodes.stop(1)
tdDnodes.start(1)
tdSql.query("select * from stb")
tdSql.checkRows(4096)
endTime = time.time()
print("total time %ds" % (endTime - startTime))
def run(self):
tdSql.prepare()
tdSql.execute("create table st (ts timestamp, voltage int) tags (loc nchar(30))")
tdSql.execute("insert into t0 using st tags('beijing') values(%d, 220) (%d, 221) (%d, 225) (%d, 228) (%d, 222)"
% (self.ts, self.ts + 1000000000, self.ts + 2000000000, self.ts + 3000000000, self.ts + 6000000000))
tdSql.execute("insert into t1 using st tags('shanghai') values(%d, 220) (%d, 221) (%d, 225) (%d, 228) (%d, 222)"
% (self.ts, self.ts + 2000000000, self.ts + 4000000000, self.ts + 5000000000, self.ts + 7000000000))
tdSql.query("select avg(voltage) from st interval(1n)")
tdSql.checkRows(3)
tdSql.checkData(0, 0, "2020-07-01 00:00:00")
tdSql.checkData(0, 1, 221.4)
tdSql.checkData(1, 0, "2020-08-01 00:00:00")
tdSql.checkData(1, 1, 227.0)
tdSql.checkData(2, 0, "2020-09-01 00:00:00")
tdSql.checkData(2, 1, 222.0)
tdSql.query("select avg(voltage) from st interval(1n, 15d)")
tdSql.checkRows(4)
tdSql.checkData(0, 0, "2020-06-16 00:00:00")
tdSql.checkData(0, 1, 220.333333)
tdSql.checkData(1, 0, "2020-07-16 00:00:00")
tdSql.checkData(1, 1, 224.666666)
tdSql.checkData(2, 0, "2020-08-16 00:00:00")
tdSql.checkData(2, 1, 225.0)
tdSql.checkData(3, 0, "2020-09-16 00:00:00")
tdSql.checkData(3, 1, 222.0)
tdSql.query("select avg(voltage) from st interval(1n, 15d) group by loc")
tdSql.checkRows(7)
tdSql.checkData(0, 0, "2020-06-16 00:00:00")
tdSql.checkData(0, 1, 220.5)
tdSql.checkData(1, 0, "2020-07-16 00:00:00")
tdSql.checkData(1, 1, 226.5)
tdSql.checkData(2, 0, "2020-08-16 00:00:00")
tdSql.checkData(2, 1, 222.0)
tdSql.checkData(3, 0, "2020-06-16 00:00:00")
tdSql.checkData(3, 1, 220.0)
tdSql.checkData(4, 0, "2020-07-16 00:00:00")
tdSql.checkData(4, 1, 221.0)
tdSql.checkData(5, 0, "2020-08-16 00:00:00")
tdSql.checkData(5, 1, 226.5)
tdSql.checkData(6, 0, "2020-09-16 00:00:00")
tdSql.checkData(6, 1, 222.0)
# test case for https://jira.taosdata.com:18080/browse/TD-2298
tdSql.execute("create database test keep 36500")
tdSql.execute("use test")
tdSql.execute("create table t (ts timestamp, voltage int)")
for i in range(10000):
tdSql.execute("insert into t values(%d, 0)" % (1000000 + i * 6000))
tdDnodes.stop(1)
tdDnodes.start(1)
tdSql.query("select last(*) from t interval(1s)")
tdSql.checkRows(10000)
def run(self):
tdSql.prepare()
tdDnodes.stop(1)
sql = "use db"
try:
tdSql.execute(sql)
except Exception as e:
expectError = 'Unable to establish connection'
if expectError in str(e):
pass
else:
caller = inspect.getframeinfo(inspect.stack()[1][1])
tdLog.exit("%s(%d) failed: sql:%s, expect error not occured" %
(caller.filename, caller.lineno, sql))
def run(self):
tdSql.prepare()
print("============== last_row_cache_0.sim")
tdSql.execute("create database test1 cachelast 0")
tdSql.execute("use test1")
self.insertData()
self.executeQueries()
self.insertData2()
self.executeQueries2()
print("============== alter last cache")
tdSql.execute("alter database test1 cachelast 1")
self.executeQueries2()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries2()
tdSql.execute("alter database test1 cachelast 0")
self.executeQueries2()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries2()
print("============== last_row_cache_1.sim")
tdSql.execute("create database test2 cachelast 1")
tdSql.execute("use test2")
self.insertData()
self.executeQueries()
self.insertData2()
self.executeQueries2()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries2()
tdSql.execute("alter database test2 cachelast 0")
self.executeQueries2()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries2()
tdSql.execute("alter database test2 cachelast 1")
self.executeQueries2()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries2()
def run(self):
tdSql.prepare()
print("==============step1")
tdSql.execute(
"create table if not exists st (ts timestamp, tagtype int) tags(dev nchar(50))"
)
tdSql.execute(
'CREATE TABLE if not exists dev_001 using st tags("dev_01")')
tdSql.execute(
'CREATE TABLE if not exists dev_002 using st tags("dev_02")')
print("==============step2")
tdSql.execute(
"""INSERT INTO dev_001(ts, tagtype) VALUES('2020-05-13 10:00:00.000', 1),
('2020-05-13 10:00:00.001', 1)
dev_002 VALUES('2020-05-13 10:00:00.001', 1)""")
tdSql.query("select * from db.st where ts='2020-05-13 10:00:00.000'")
tdSql.checkRows(1)
## test case for https://jira.taosdata.com:18080/browse/TD-2488
tdSql.execute("create table m1(ts timestamp, k int) tags(a int)")
tdSql.execute("create table t1 using m1 tags(1)")
tdSql.execute("create table t2 using m1 tags(2)")
tdSql.execute("insert into t1 values('2020-1-1 1:1:1', 1)")
tdSql.execute("insert into t1 values('2020-1-1 1:10:1', 2)")
tdSql.execute("insert into t2 values('2020-1-1 1:5:1', 99)")
tdSql.query("select count(*) from m1 where ts = '2020-1-1 1:5:1' ")
tdSql.checkRows(1)
tdSql.checkData(0, 0, 1)
tdDnodes.stop(1)
tdDnodes.start(1)
tdSql.query("select count(*) from m1 where ts = '2020-1-1 1:5:1' ")
tdSql.checkRows(1)
tdSql.checkData(0, 0, 1)
def run(self):
tdSql.prepare()
# last_cache_0.sim
tdSql.execute("create database test1 cachelast 0")
tdSql.execute("use test1")
self.insertData()
self.executeQueries()
tdSql.execute("alter database test1 cachelast 1")
self.executeQueries()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries()
tdSql.execute("alter database test1 cachelast 0")
self.executeQueries()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries()
# last_cache_1.sim
tdSql.execute("create database test2 cachelast 1")
tdSql.execute("use test2")
self.insertData()
self.executeQueries()
tdSql.execute("alter database test2 cachelast 0")
self.executeQueries()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries()
tdSql.execute("alter database test2 cachelast 1")
self.executeQueries()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries()
def restartTaosd(self, index=1, db_name="db"):
tdDnodes.stop(index)
tdDnodes.startWithoutSleep(index)
tdSql.execute(f"use {db_name}")
def run(self):
tdSql.prepare()
binPath = tdFindPath.getTaosdemoPath()
TDenginePath = tdFindPath.getTDenginePath()
## change system time to 2020/10/20
os.system('timedatectl set-ntp off')
tdLog.sleep(10)
os.system('timedatectl set-time 2020-10-20')
#run taosdemo to insert data. one row per second from 2020/10/11 to 2020/10/20
#11 data files should be generated
#vnode at TDinternal/community/sim/dnode1/data/vnode
try:
os.system(
f"{binPath}taosdemo -f tools/taosdemoAllTest/manual_change_time_1_1_A.json"
)
commandArray = [
'ls', '-l',
f'{TDenginePath}/sim/dnode1/data/vnode/vnode2/tsdb/data'
]
result = subprocess.run(
commandArray, stdout=subprocess.PIPE).stdout.decode('utf-8')
except BaseException:
os.system('sudo timedatectl set-ntp on')
tdLog.sleep(10)
if result.count('data') != 11:
os.system('sudo timedatectl set-ntp on')
tdLog.sleep(10)
tdLog.exit('wrong number of files')
else:
tdLog.debug("data file number correct")
try:
tdSql.query('select first(ts) from stb_0'
) #check the last data in the database
tdSql.checkData(0, 0, datetime(2020, 10, 11, 0, 0, 0, 0))
except BaseException:
os.system('sudo timedatectl set-ntp on')
tdLog.sleep(10)
#moves 5 days ahead to 2020/10/25 and restart taosd
#4 oldest data file should be removed from tsdb/data
#7 data file should be found
#vnode at TDinternal/community/sim/dnode1/data/vnode
try:
os.system('timedatectl set-time 2020-10-25')
tdDnodes.stop(1)
tdDnodes.start(1)
tdSql.query('select first(ts) from stb_0')
tdSql.checkData(0, 0,
datetime(2020, 10, 14, 8, 0, 0,
0)) #check the last data in the database
except BaseException:
os.system('sudo timedatectl set-ntp on')
tdLog.sleep(10)
os.system('sudo timedatectl set-ntp on')
tdLog.sleep(10)
commandArray = [
'ls', '-l',
f'{TDenginePath}/sim/dnode1/data/vnode/vnode2/tsdb/data'
]
result = subprocess.run(commandArray,
stdout=subprocess.PIPE).stdout.decode('utf-8')
print(result.count('data'))
if result.count('data') != 7:
tdLog.exit('wrong number of files')
else:
tdLog.debug("data file number correct")
os.system('sudo timedatectl set-ntp on')
tdLog.sleep(10)
def run(self):
tdSql.prepare()
print("==============step1")
tdSql.execute(
"create table if not exists st (ts timestamp, tagtype int) tags(dev nchar(50))"
)
tdSql.execute(
'CREATE TABLE if not exists dev_001 using st tags("dev_01")')
tdSql.execute(
'CREATE TABLE if not exists dev_002 using st tags("dev_02")')
print("==============step2")
tdSql.execute(
"""INSERT INTO dev_001(ts, tagtype) VALUES('2020-05-13 10:00:00.000', 1),
('2020-05-13 10:00:00.001', 1)
dev_002 VALUES('2020-05-13 10:00:00.001', 1)""")
tdSql.query("select * from db.st where ts='2020-05-13 10:00:00.000'")
tdSql.checkRows(1)
tdSql.query("select tbname, dev from dev_001")
tdSql.checkRows(1)
tdSql.checkData(0, 0, 'dev_001')
tdSql.checkData(0, 1, 'dev_01')
tdSql.query("select tbname, dev, tagtype from dev_001")
tdSql.checkRows(2)
tdSql.checkData(0, 0, 'dev_001')
tdSql.checkData(0, 1, 'dev_01')
tdSql.checkData(0, 2, 1)
tdSql.checkData(1, 0, 'dev_001')
tdSql.checkData(1, 1, 'dev_01')
tdSql.checkData(1, 2, 1)
## test case for https://jira.taosdata.com:18080/browse/TD-2488
tdSql.execute("create table m1(ts timestamp, k int) tags(a int)")
tdSql.execute("create table t1 using m1 tags(1)")
tdSql.execute("create table t2 using m1 tags(2)")
tdSql.execute("insert into t1 values('2020-1-1 1:1:1', 1)")
tdSql.execute("insert into t1 values('2020-1-1 1:10:1', 2)")
tdSql.execute("insert into t2 values('2020-1-1 1:5:1', 99)")
tdSql.query("select count(*) from m1 where ts = '2020-1-1 1:5:1' ")
tdSql.checkRows(1)
tdSql.checkData(0, 0, 1)
tdDnodes.stop(1)
tdDnodes.start(1)
tdSql.query("select count(*) from m1 where ts = '2020-1-1 1:5:1' ")
tdSql.checkRows(1)
tdSql.checkData(0, 0, 1)
## test case for https://jira.taosdata.com:18080/browse/TD-1930
tdSql.execute(
"create table tb(ts timestamp, c1 int, c2 binary(10), c3 nchar(10), c4 float, c5 bool)"
)
for i in range(10):
tdSql.execute(
"insert into tb values(%d, %d, 'binary%d', 'nchar%d', %f, %d)"
% (self.ts + i, i, i, i, i + 0.1, i % 2))
tdSql.error("select * from tb where c2 = binary2")
tdSql.error("select * from tb where c3 = nchar2")
tdSql.query("select * from tb where c2 = 'binary2' ")
tdSql.checkRows(1)
tdSql.query("select * from tb where c3 = 'nchar2' ")
tdSql.checkRows(1)
tdSql.query("select * from tb where c1 = '2' ")
tdSql.checkRows(1)
tdSql.query("select * from tb where c1 = 2 ")
tdSql.checkRows(1)
tdSql.query("select * from tb where c4 = '0.1' ")
tdSql.checkRows(1)
tdSql.query("select * from tb where c4 = 0.1 ")
tdSql.checkRows(1)
tdSql.query("select * from tb where c5 = true ")
tdSql.checkRows(5)
tdSql.query("select * from tb where c5 = 'true' ")
tdSql.checkRows(5)
def run(self):
tdSql.prepare()
print("============== Step1: last_row_cache_0.sim")
tdSql.execute("create database test1 cachelast 0")
tdSql.execute("use test1")
self.insertData()
self.executeQueries()
self.insertData2()
self.executeQueries2()
print("============== Step2: alter database test1 cachelast 1")
tdSql.execute("alter database test1 cachelast 1")
self.executeQueries2()
print("============== Step3: alter database test1 cachelast 2")
tdSql.execute("alter database test1 cachelast 2")
self.executeQueries2()
print("============== Step4: alter database test1 cachelast 3")
tdSql.execute("alter database test1 cachelast 3")
self.executeQueries2()
print(
"============== Step5: alter database test1 cachelast 0 and restart taosd"
)
tdSql.execute("alter database test1 cachelast 0")
self.executeQueries2()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries2()
print(
"============== Step6: alter database test1 cachelast 1 and restart taosd"
)
tdSql.execute("alter database test1 cachelast 1")
self.executeQueries2()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries2()
print(
"============== Step7: alter database test1 cachelast 2 and restart taosd"
)
tdSql.execute("alter database test1 cachelast 2")
self.executeQueries2()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries2()
print(
"============== Step8: alter database test1 cachelast 3 and restart taosd"
)
tdSql.execute("alter database test1 cachelast 3")
self.executeQueries2()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries2()
print("============== Step9: create database test2 cachelast 1")
tdSql.execute("create database test2 cachelast 1")
tdSql.execute("use test2")
self.insertData()
self.executeQueries()
self.insertData2()
self.executeQueries2()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries2()
print("============== Step8: alter database test2 cachelast 0")
tdSql.execute("alter database test2 cachelast 0")
self.executeQueries2()
print("============== Step9: alter database test2 cachelast 1")
tdSql.execute("alter database test2 cachelast 1")
self.executeQueries2()
print("============== Step10: alter database test2 cachelast 2")
tdSql.execute("alter database test2 cachelast 2")
self.executeQueries2()
print("============== Step11: alter database test2 cachelast 3")
tdSql.execute("alter database test2 cachelast 3")
self.executeQueries2()
print(
"============== Step12: alter database test2 cachelast 0 and restart taosd"
)
tdSql.execute("alter database test2 cachelast 0")
self.executeQueries2()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries2()
print(
"============== Step13: alter database test2 cachelast 1 and restart taosd"
)
tdSql.execute("alter database test2 cachelast 1")
self.executeQueries2()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries2()
print(
"============== Step14: alter database test2 cachelast 2 and restart taosd"
)
tdSql.execute("alter database test2 cachelast 2")
self.executeQueries2()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries2()
print(
"============== Step15: alter database test2 cachelast 3 and restart taosd"
)
tdSql.execute("alter database test2 cachelast 3")
self.executeQueries2()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries2()
print(
"============== Step16: select last_row(*) from st group by tbname"
)
tdSql.query("select last_row(*) from st group by tbname")
tdSql.checkRows(10)
def run(self):
tdSql.prepare()
tdSql.query('show databases')
tdSql.checkData(0, 15, 0)
buildPath = self.getBuildPath()
if (buildPath == ""):
tdLog.exit("taosd not found!")
else:
tdLog.info("taosd found in %s" % buildPath)
binPath = buildPath + "/build/bin/"
#write 5M rows into db, then restart to force the data move into disk.
#create 500 tables
os.system(
"%staosdemo -f tools/taosdemoAllTest/insert_5M_rows.json -y " %
binPath)
tdDnodes.stop(1)
tdDnodes.start(1)
tdSql.execute('use db')
#prepare to query 500 tables last_row()
tableName = []
for i in range(500):
tableName.append(f"stb_{i}")
tdSql.execute('use db')
lastRow_Off_start = datetime.now()
slow = 0 #count time where lastRow on is slower
for i in range(5):
#switch lastRow to off and check
tdSql.execute('alter database db cachelast 0')
tdSql.query('show databases')
tdSql.checkData(0, 15, 0)
#run last_row(*) query 500 times
for i in range(500):
tdSql.execute(f'SELECT LAST_ROW(*) FROM {tableName[i]}')
lastRow_Off_end = datetime.now()
tdLog.debug(f'time used:{lastRow_Off_end-lastRow_Off_start}')
#switch lastRow to on and check
tdSql.execute('alter database db cachelast 1')
tdSql.query('show databases')
tdSql.checkData(0, 15, 1)
#run last_row(*) query 500 times
tdSql.execute('use db')
lastRow_On_start = datetime.now()
for i in range(500):
tdSql.execute(f'SELECT LAST_ROW(*) FROM {tableName[i]}')
lastRow_On_end = datetime.now()
tdLog.debug(f'time used:{lastRow_On_end-lastRow_On_start}')
#check which one used more time
if (lastRow_Off_end - lastRow_Off_start >
lastRow_On_end - lastRow_On_start):
pass
else:
slow += 1
tdLog.debug(slow)
if slow > 1: #tolerance for the first time
tdLog.exit('lastRow hot alter failed')
def run(self):
tdSql.prepare()
tdSql.execute(
"create table st (ts timestamp, voltage int) tags (loc nchar(30))")
tdSql.execute(
"insert into t0 using st tags('beijing') values(%d, 220) (%d, 221) (%d, 225) (%d, 228) (%d, 222)"
% (self.ts, self.ts + 1000000000, self.ts + 2000000000,
self.ts + 3000000000, self.ts + 6000000000))
tdSql.execute(
"insert into t1 using st tags('shanghai') values(%d, 220) (%d, 221) (%d, 225) (%d, 228) (%d, 222)"
% (self.ts, self.ts + 2000000000, self.ts + 4000000000,
self.ts + 5000000000, self.ts + 7000000000))
tdSql.query("select avg(voltage) from st interval(1n)")
tdSql.checkRows(3)
tdSql.checkData(0, 0, "2020-07-01 00:00:00")
tdSql.checkData(0, 1, 221.4)
tdSql.checkData(1, 0, "2020-08-01 00:00:00")
tdSql.checkData(1, 1, 227.0)
tdSql.checkData(2, 0, "2020-09-01 00:00:00")
tdSql.checkData(2, 1, 222.0)
tdSql.query("select avg(voltage) from st interval(1n, 15d)")
tdSql.checkRows(4)
tdSql.checkData(0, 0, "2020-06-16 00:00:00")
tdSql.checkData(0, 1, 220.333333)
tdSql.checkData(1, 0, "2020-07-16 00:00:00")
tdSql.checkData(1, 1, 224.666666)
tdSql.checkData(2, 0, "2020-08-16 00:00:00")
tdSql.checkData(2, 1, 225.0)
tdSql.checkData(3, 0, "2020-09-16 00:00:00")
tdSql.checkData(3, 1, 222.0)
tdSql.query(
"select avg(voltage) from st interval(1n, 15d) group by loc")
tdSql.checkRows(7)
tdSql.checkData(0, 0, "2020-06-16 00:00:00")
tdSql.checkData(0, 1, 220.5)
tdSql.checkData(1, 0, "2020-07-16 00:00:00")
tdSql.checkData(1, 1, 226.5)
tdSql.checkData(2, 0, "2020-08-16 00:00:00")
tdSql.checkData(2, 1, 222.0)
tdSql.checkData(3, 0, "2020-06-16 00:00:00")
tdSql.checkData(3, 1, 220.0)
tdSql.checkData(4, 0, "2020-07-16 00:00:00")
tdSql.checkData(4, 1, 221.0)
tdSql.checkData(5, 0, "2020-08-16 00:00:00")
tdSql.checkData(5, 1, 226.5)
tdSql.checkData(6, 0, "2020-09-16 00:00:00")
tdSql.checkData(6, 1, 222.0)
# test case for https://jira.taosdata.com:18080/browse/TD-2298
tdSql.execute("create database test keep 36500")
tdSql.execute("use test")
tdSql.execute("create table t (ts timestamp, voltage int)")
for i in range(10000):
tdSql.execute("insert into t values(%d, 0)" % (1000000 + i * 6000))
tdDnodes.stop(1)
tdDnodes.start(1)
tdSql.query("select last(*) from t interval(1s)")
tdSql.checkRows(10000)
# test case for https://jira.taosdata.com:18080/browse/TD-2601
newTs = 1601481600000
tdSql.execute("create database test2")
tdSql.execute("use test2")
tdSql.execute("create table t (ts timestamp, voltage int)")
for i in range(100):
tdSql.execute("insert into t values(%d, %d)" %
(newTs + i * 10000000, i))
tdSql.query(
"select sum(voltage) from t where ts >='2020-10-01 00:00:00' and ts <='2020-12-01 00:00:00' interval(1n) fill(NULL)"
)
tdSql.checkRows(3)
tdSql.checkData(0, 1, 4950)
tdSql.checkData(1, 1, None)
tdSql.checkData(2, 1, None)
# test case for https://jira.taosdata.com:18080/browse/TD-2659， https://jira.taosdata.com:18080/browse/TD-2660
tdSql.execute("create database test3")
tdSql.execute("use test3")
tdSql.execute("create table tb(ts timestamp, c int)")
tdSql.execute("insert into tb values('2020-10-30 18:11:56.680', -111)")
tdSql.execute("insert into tb values('2020-11-19 18:11:45.773', null)")
tdSql.execute("insert into tb values('2020-12-09 18:11:17.098', null)")
tdSql.execute("insert into tb values('2020-12-29 11:00:49.412', 1)")
tdSql.execute("insert into tb values('2020-12-29 11:00:50.412', 2)")
tdSql.execute("insert into tb values('2020-12-29 11:00:52.412', 3)")
tdSql.query("select first(ts),twa(c) from tb interval(14a)")
tdSql.checkRows(6)
tdSql.query("select twa(c) from tb group by c")
tdSql.checkRows(4)