################################################################### # 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 random import string from util.log import * from util.cases import * from util.sql import * from util.sqlset import * from util import constant from util.common import * class TDTestCase: def init(self, conn, logSql): tdLog.debug("start to execute %s" % __file__) tdSql.init(conn.cursor()) self.setsql = TDSetSql() self.ntbname = 'ntb' self.stbname = 'stb' self.binary_length = 20 # the length of binary for column_dict self.nchar_length = 20 # the length of nchar for column_dict self.column_dict = { 'ts' : 'timestamp', 'col1': 'tinyint', 'col2': 'smallint', 'col3': 'int', 'col4': 'bigint', 'col5': 'tinyint unsigned', 'col6': 'smallint unsigned', 'col7': 'int unsigned', 'col8': 'bigint unsigned', 'col9': 'float', 'col10': 'double', 'col11': 'bool', 'col12': f'binary({self.binary_length})', 'col13': f'nchar({self.nchar_length})' } self.tag_dict = { 'ts_tag' : 'timestamp', 't1': 'tinyint', 't2': 'smallint', 't3': 'int', 't4': 'bigint', 't5': 'tinyint unsigned', 't6': 'smallint unsigned', 't7': 'int unsigned', 't8': 'bigint unsigned', 't9': 'float', 't10': 'double', 't11': 'bool', 't12': f'binary({self.binary_length})', 't13': f'nchar({self.nchar_length})' } self.tag_list = [ f'now,1,2,3,4,5,6,7,8,9.9,10.1,true,"abcd","涛思数据"' ] self.tbnum = 1 self.values_list = [ f'now,1,2,3,4,5,6,7,8,9.9,10.1,true,"abcd","涛思数据"' ] self.column_add_dict = { 'col_time' : 'timestamp', 'col_tinyint' : 'tinyint', 'col_smallint' : 'smallint', 'col_int' : 'int', 'col_bigint' : 'bigint', 'col_untinyint' : 'tinyint unsigned', 'col_smallint' : 'smallint unsigned', 'col_int' : 'int unsigned', 'col_bigint' : 'bigint unsigned', 'col_bool' : 'bool', 'col_float' : 'float', 'col_double' : 'double', 'col_binary' : f'binary({constant.BINARY_LENGTH_MAX})', 'col_nchar' : f'nchar({constant.NCAHR_LENGTH_MAX})' } def get_long_name(self, length, mode="mixed"): """ generate long name mode could be numbers/letters/letters_mixed/mixed """ if mode == "numbers": population = string.digits elif mode == "letters": population = string.ascii_letters.lower() elif mode == "letters_mixed": population = string.ascii_letters.upper() + string.ascii_letters.lower() else: population = string.ascii_letters.lower() + string.digits return "".join(random.choices(population, k=length)) def alter_stable_column_check(self,dbname,stbname,tbname): tdSql.execute(f'create database if not exists {dbname}') tdSql.execute(f'use {dbname}') tdSql.execute( f'create stable {stbname} (ts timestamp, c1 tinyint, c2 smallint, c3 int, \ c4 bigint, c5 tinyint unsigned, c6 smallint unsigned, c7 int unsigned, c8 bigint unsigned, c9 float, c10 double, c11 bool,c12 binary(20),c13 nchar(20)) tags(t0 int) ') tdSql.execute(f'create table {tbname} using {stbname} tags(1)') tdSql.execute(f'insert into {tbname} values (now,1,2,3,4,5,6,7,8,9.9,10.1,true,"abcd","涛思数据")') tdSql.execute(f'alter stable {stbname} add column c14 int') tdSql.query(f'select c14 from {stbname}') tdSql.checkRows(1) tdSql.execute(f'alter stable {stbname} add column `c15` int') tdSql.query(f'select c15 from {stbname}') tdSql.checkRows(1) tdSql.query(f'describe {stbname}') tdSql.checkRows(17) tdSql.execute(f'alter stable {stbname} drop column c14') tdSql.query(f'describe {stbname}') tdSql.checkRows(16) tdSql.execute(f'alter stable {stbname} drop column `c15`') tdSql.query(f'describe {stbname}') tdSql.checkRows(15) tdSql.execute(f'alter stable {stbname} modify column c12 binary(30)') tdSql.query(f'describe {stbname}') tdSql.checkData(12,2,30) tdSql.execute(f'alter stable {stbname} modify column `c12` binary(35)') tdSql.query(f'describe {stbname}') tdSql.checkData(12,2,35) tdSql.error(f'alter stable {stbname} modify column `c12` binary(34)') tdSql.execute(f'alter stable {stbname} modify column c13 nchar(30)') tdSql.query(f'describe {stbname}') tdSql.checkData(13,2,30) tdSql.error(f'alter stable {stbname} modify column c13 nchar(29)') tdSql.error(f'alter stable {stbname} rename column c1 c21') tdSql.error(f'alter stable {stbname} modify column c1 int') tdSql.error(f'alter stable {stbname} modify column c4 int') tdSql.error(f'alter stable {stbname} modify column c8 int') tdSql.error(f'alter stable {stbname} modify column c1 unsigned int') tdSql.error(f'alter stable {stbname} modify column c9 double') tdSql.error(f'alter stable {stbname} modify column c10 float') tdSql.error(f'alter stable {stbname} modify column c11 int') tdSql.error(f'alter stable {stbname} drop tag t0') tdSql.execute(f'drop database {dbname}') def alter_stable_tag_check(self,dbname,stbname,tbname): tdSql.execute(f'create database if not exists {dbname}') tdSql.execute(f'use {dbname}') tdSql.execute( f'create stable {stbname} (ts timestamp, c1 int) tags(ts_tag timestamp, t1 tinyint, t2 smallint, t3 int, \ t4 bigint, t5 tinyint unsigned, t6 smallint unsigned, t7 int unsigned, t8 bigint unsigned, t9 float, t10 double, t11 bool,t12 binary(20),t13 nchar(20)) ') tdSql.execute(f'create table {tbname} using {stbname} tags(now,1,2,3,4,5,6,7,8,9.9,10.1,true,"abcd","涛思数据")') tdSql.execute(f'insert into {tbname} values(now,1)') tdSql.execute(f'alter stable {stbname} add tag t14 int') tdSql.query(f'select t14 from {stbname}') tdSql.checkRows(1) tdSql.execute(f'alter stable {stbname} add tag `t15` int') tdSql.query(f'select t14 from {stbname}') tdSql.checkRows(1) tdSql.query(f'describe {stbname}') tdSql.checkRows(18) tdSql.execute(f'alter stable {stbname} drop tag t14') tdSql.query(f'describe {stbname}') tdSql.checkRows(17) tdSql.execute(f'alter stable {stbname} drop tag `t15`') tdSql.query(f'describe {stbname}') tdSql.checkRows(16) tdSql.execute(f'alter stable {stbname} modify tag t12 binary(30)') tdSql.query(f'describe {stbname}') tdSql.checkData(14,2,30) tdSql.execute(f'alter stable {stbname} modify tag `t12` binary(35)') tdSql.query(f'describe {stbname}') tdSql.checkData(14,2,35) tdSql.error(f'alter stable {stbname} modify tag `t12` binary(34)') tdSql.execute(f'alter stable {stbname} modify tag t13 nchar(30)') tdSql.query(f'describe {stbname}') tdSql.checkData(15,2,30) tdSql.error(f'alter stable {stbname} modify tag t13 nchar(29)') tdSql.execute(f'alter table {stbname} rename tag t1 t21') tdSql.query(f'describe {stbname}') tdSql.checkData(3,0,'t21') tdSql.execute(f'alter table {stbname} rename tag `t21` t1') tdSql.query(f'describe {stbname}') tdSql.checkData(3,0,'t1') for i in ['bigint','unsigned int','float','double','binary(10)','nchar(10)']: for j in [1,2,3]: tdSql.error(f'alter stable {stbname} modify tag t{j} {i}') for i in ['int','unsigned int','float','binary(10)','nchar(10)']: tdSql.error(f'alter stable {stbname} modify tag t8 {i}') tdSql.error(f'alter stable {stbname} modify tag t4 int') tdSql.error(f'alter stable {stbname} drop column t0') #!bug TD-16410 # tdSql.error(f'alter stable {tbname} set tag t1=100 ') # tdSql.execute(f'create table ntb (ts timestamp,c0 int)') tdSql.error(f'alter stable ntb add column c2 ') tdSql.execute(f'drop database {dbname}') def alter_stable_check(self): tdSql.prepare() tdSql.execute(self.setsql.set_create_stable_sql(self.stbname,self.column_dict,self.tag_dict)) for i in range(self.tbnum): tdSql.execute(f'create table {self.stbname}_{i} using {self.stbname} tags({self.tag_list[i]})') for j in self.values_list: tdSql.execute(f'insert into {self.stbname}_{i} values({j})') for key,values in self.column_add_dict.items(): tdSql.execute(f'alter stable {self.stbname} add column {key} {values}') tdSql.query(f'describe {self.stbname}') tdSql.checkRows(len(self.column_dict)+len(self.tag_dict)+1) for i in range(self.tbnum): tdSql.query(f'describe {self.stbname}_{i}') tdSql.checkRows(len(self.column_dict)+len(self.tag_dict)+1) tdSql.query(f'select {key} from {self.stbname}_{i}') tdSql.checkRows(len(self.values_list)) tdSql.execute(f'alter stable {self.stbname} drop column {key}') tdSql.query(f'describe {self.stbname}') tdSql.checkRows(len(self.column_dict)+len(self.tag_dict)) for i in range(self.tbnum): tdSql.query(f'describe {self.stbname}_{i}') tdSql.checkRows(len(self.column_dict)+len(self.tag_dict)) tdSql.error(f'select {key} from {self.stbname} ') for key,values in self.column_dict.items(): if 'binary' in values.lower(): v = f'binary({self.binary_length+1})' v_error = f'binary({self.binary_length-1})' tdSql.error(f'alter stable {self.stbname} modify column {key} {v_error}') tdSql.execute(f'alter stable {self.stbname} modify column {key} {v}') tdSql.query(f'describe {self.stbname}') result = tdCom.getOneRow(1,'VARCHAR') tdSql.checkEqual(result[0][2],self.binary_length+1) for i in range(self.tbnum): tdSql.query(f'describe {self.stbname}_{i}') result = tdCom.getOneRow(1,'VARCHAR') tdSql.checkEqual(result[0][2],self.binary_length+1) elif 'nchar' in values.lower(): v = f'nchar({self.binary_length+1})' v_error = f'nchar({self.binary_length-1})' tdSql.error(f'alter stable {self.stbname} modify column {key} {v_error}') tdSql.execute(f'alter stable {self.stbname} modify column {key} {v}') tdSql.query(f'describe {self.stbname}') result = tdCom.getOneRow(1,'NCHAR') tdSql.checkEqual(result[0][2],self.binary_length+1) for i in range(self.tbnum): tdSql.query(f'describe {self.stbname}_{i}') result = tdCom.getOneRow(1,'NCHAR') tdSql.checkEqual(result[0][2],self.binary_length+1) else: for v in self.column_dict.values(): tdSql.error(f'alter stable {self.stbname} modify column {key} {v}') pass def run(self): # dbname = self.get_long_name(length=10, mode="letters") # stbname = self.get_long_name(length=5, mode="letters") # tbname = self.get_long_name(length=5, mode="letters") # self.alter_stable_column_check(dbname,stbname,tbname) # self.alter_stable_tag_check(dbname,stbname,tbname) self.alter_stable_check() def stop(self): tdSql.close() tdLog.success("%s successfully executed" % __file__) tdCases.addWindows(__file__, TDTestCase()) tdCases.addLinux(__file__, TDTestCase())