def check_virtual_column(p_dbid, p_sql):
result = {}
result['code'] = '0'
result['message'] = ''
p_ds = get_ds_by_dsid(p_dbid)
db_uat = get_connection_ds_uat(p_ds)
cr_uat = db_uat.cursor()
v_sql_check = ''
if get_obj_type(p_sql) == 'TABLE' and p_sql.upper().count("CREATE") > 0:
v_sql_check = '''select count(0) from information_schema.columns
where upper(table_schema)='{0}'
and upper(table_name)='{1}'
and extra='VIRTUAL GENERATED'
'''.format(p_ds['uat_service'].upper(),
get_obj_name(p_sql).upper())
print(v_sql_check)
cr_uat.execute(p_sql)
cr_uat.execute(v_sql_check)
rs_uat = cr_uat.fetchone()
if rs_uat[0] > 0:
cr_uat.execute('drop table ' + get_obj_name(p_sql))
cr_uat.close()
db_uat.commit()
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_check(v_env, '表中不能含有虚拟列！')
return result
else:
cr_uat.execute('drop table ' + get_obj_name(p_sql))
cr_uat.close()
db_uat.commit()
return result
def check_column_len(p_dbid, p_sql):
result = {}
result['code'] = '0'
result['message'] = ''
p_ds = get_ds_by_dsid(p_dbid)
db_uat = get_connection_ds_uat(p_ds)
cr_uat = db_uat.cursor()
v_sql_check = ''
if get_obj_type(p_sql) == 'TABLE' and p_sql.upper().count("CREATE") > 0:
v_sql_check = '''select IFNULL(SUM(character_maximum_length),0)
from information_schema.columns
where upper(table_schema)='{0}'
and upper(table_name)='{1}'
and data_type='VARCHAR'
'''.format(p_ds['uat_service'].upper(),
get_obj_name(p_sql).upper())
print(v_sql_check)
cr_uat.execute(p_sql)
cr_uat.execute(v_sql_check)
rs_uat = cr_uat.fetchone()
if rs_uat[0] > 8000:
cr_uat.execute('drop table ' + get_obj_name(p_sql))
cr_uat.close()
db_uat.commit()
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_check(v_env, '表中字符列不能超过8000个字符！')
return result
else:
cr_uat.execute('drop table ' + get_obj_name(p_sql))
cr_uat.close()
db_uat.commit()
return result
def check_proc_valid(p_dbid, p_sql):
result = {}
result['code'] = '0'
result['message'] = ''
p_ds = get_ds_by_dsid(p_dbid)
db_uat = get_connection_ds_uat(p_ds)
cr_uat = db_uat.cursor()
if get_obj_type(
p_sql) == 'PROCEDURE' and p_sql.upper().count("CREATE") > 0:
#创建存储过程在UAT环境中
cr_uat.execute(p_sql)
#从数据字典中读取过程定义，并将过程中的语句拆分出来，保存至list中
v_sql_proc = '''select routine_definition from information_schema.`routines`
where upper(routine_schema)='{0}'
and upper(routine_name)='{1}'
'''.format(p_ds['uat_service'].upper(),
get_obj_name(p_sql).upper())
v_sql_drop = 'drop procedure {0}'.format(get_obj_name(p_sql))
cr_uat.execute(v_sql_proc)
rs_uat = cr_uat.fetchone()
v_list = rs_uat[0].upper().replace('\n',
'').replace('BEGIN', '').replace(
'END', '').split(';')
#逐个验证每一个语句是否满足规则
for i in range(len(v_list)):
print(i, v_list[i])
result = check_proc_valid_ddl(v_list[i])
if result['code'] != '0':
cr_uat.execute(v_sql_drop)
db_uat.commit()
cr_uat.close()
return result
cr_uat.execute(v_sql_drop)
db_uat.commit()
cr_uat.close()
return result
def check_obj_exists(p_dbid, p_sql):
result = {}
result['code'] = '0'
result['message'] = ''
p_ds = get_ds_by_dsid(p_dbid)
db_uat = get_connection_ds_uat(p_ds)
db_prod = get_connection_ds(p_ds)
cr_uat = db_uat.cursor()
cr_prod = db_prod.cursor()
v_sql_check = ''
if get_obj_type(p_sql) == 'TABLE' and p_sql.upper().count("CREATE") > 0:
v_sql_check = '''select COUNT(0) from information_schema.tables
where upper(table_schema)='{0}'
and upper(table_type)='BASE TABLE'
and upper(table_name)='{1}'
'''.format(p_ds['uat_service'].upper(),
get_obj_name(p_sql).upper())
print(v_sql_check)
cr_uat.execute(v_sql_check)
rs_uat = cr_uat.fetchone()
if rs_uat[0] > 0:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_check(v_env, '表已经存在！')
return result
else:
v_sql_check = '''select COUNT(0) from information_schema.tables
where upper(table_schema)='{0}'
and upper(table_type)='BASE TABLE'
and upper(table_name)='{1}'
'''.format(p_ds['service'].upper(),
get_obj_name(p_sql).upper())
cr_prod.execute(v_sql_check)
rs_prod = cr_prod.fetchone()
if rs_prod[0] > 0:
v_env = 'PROD'
result['code'] = '1'
result['message'] = format_check(v_env, '表已经存在！')
return result
if get_obj_type(p_sql) == 'VIEW' and p_sql.upper().count("CREATE") > 0:
v_sql_check = '''select COUNT(0) from information_schema.tables
where upper(table_schema)='{0}'
and upper(table_type)='VIEW'
and upper(table_name)='{1}'
'''.format(p_ds['uat_service'].upper(),
get_obj_name(p_sql).upper())
print(v_sql_check)
cr_uat.execute(v_sql_check)
rs_uat = cr_uat.fetchone()
if rs_uat[0] > 0:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_check(v_env, '视图已经存在！')
return result
else:
v_sql_check = '''select COUNT(0) from information_schema.tables
where upper(table_schema)='{0}'
and upper(table_type)='VIEW'
and upper(table_name)='{1}'
'''.format(
p_ds['service'].upper(),
get_obj_name(p_sql).upper())
cr_prod.execute(v_sql_check)
rs_prod = cr_prod.fetchone()
if rs_prod[0] > 0:
v_env = 'PROD'
result['code'] = '1'
result['message'] = format_check(v_env, '视图已经存在！')
return result
if get_obj_type(p_sql) == 'INDEX' and p_sql.upper().count("CREATE") > 0:
v_sql_check = '''select COUNT(0) from information_schema.statistics
where upper(table_schema)='{0}'
and upper(index_name)='{1}'
'''.format(p_ds['uat_service'].upper(),
get_obj_name(p_sql).upper())
print(v_sql_check)
cr_uat.execute(v_sql_check)
rs_uat = cr_uat.fetchone()
if rs_uat[0] > 0:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_check(v_env, '索引已经存在！')
return result
else:
v_sql_check = '''select COUNT(0) from information_schema.statistics
where upper(table_schema)='{0}'
and upper(index_name)='{1}'
'''.format(
p_ds['service'].upper(),
get_obj_name(p_sql).upper())
cr_prod.execute(v_sql_check)
rs_prod = cr_prod.fetchone()
if rs_prod[0] > 0:
v_env = 'PROD'
result['code'] = '1'
result['message'] = format_check(v_env, '索引已经存在！')
return result
if get_obj_type(p_sql) == 'FUNCTION' and p_sql.upper().count("CREATE") > 0:
v_sql_check = '''select COUNT(0) from information_schema.routines
where upper(routine_schema)='{0}'
and upper(routine_type)='FUNCTION'
and upper(routine_name)='{1}'
'''.format(p_ds['uat_service'].upper(),
get_obj_name(p_sql).upper())
print(v_sql_check)
cr_uat.execute(v_sql_check)
rs_uat = cr_uat.fetchone()
if rs_uat[0] > 0:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_check(v_env, '函数已经存在！')
return result
else:
v_sql_check = '''select COUNT(0) from information_schema.routines
where upper(routine_schema)='{0}'
and upper(routine_type)='FUNCTION'
and upper(routine_name)='{1}'
'''.format(
p_ds['service'].upper(),
get_obj_name(p_sql).upper())
cr_prod.execute(v_sql_check)
rs_prod = cr_prod.fetchone()
if rs_prod[0] > 0:
v_env = 'PROD'
result['code'] = '1'
result['message'] = format_check(v_env, '函数已经存在！')
return result
if get_obj_type(
p_sql) == 'PROCEDURE' and p_sql.upper().count("CREATE") > 0:
v_sql_check = '''select COUNT(0) from information_schema.routines
where upper(routine_schema)='{0}'
and upper(routine_type)='PROCEDURE'
and upper(routine_name)='{1}'
'''.format(p_ds['uat_service'].upper(),
get_obj_name(p_sql).upper())
print(v_sql_check)
cr_uat.execute(v_sql_check)
rs_uat = cr_uat.fetchone()
if rs_uat[0] > 0:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_check(v_env, '过程已经存在！')
return result
else:
v_sql_check = '''select COUNT(0) from information_schema.routines
where upper(routine_schema)='{0}'
and upper(routine_type)='PROCEDURE'
and upper(routine_name)='{1}'
'''.format(
p_ds['service'].upper(),
get_obj_name(p_sql).upper())
cr_prod.execute(v_sql_check)
rs_prod = cr_prod.fetchone()
if rs_prod[0] > 0:
v_env = 'PROD'
result['code'] = '1'
result['message'] = format_check(v_env, '过程已经存在！')
return result
if get_obj_type(p_sql) == 'TRIGGER' and p_sql.upper().count("CREATE") > 0:
v_sql_check = '''select COUNT(0) from information_schema.triggers
where upper(trigger_schema)='{0}'
and upper(trigger_name)='{1}'
'''.format(
p_ds['uat_service'].upper(),
get_obj_name(p_sql).upper())
print(v_sql_check)
cr_uat.execute(v_sql_check)
rs_uat = cr_uat.fetchone()
if rs_uat[0] > 0:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_check(v_env, '触发器已经存在！')
return result
else:
v_sql_check = '''select COUNT(0) from information_schema.triggers
where upper(trigger_schema)='{0}'
and upper(trigger_schema)='{1}'
'''.format(
p_ds['service'].upper(),
get_obj_name(p_sql).upper())
cr_prod.execute(v_sql_check)
rs_prod = cr_prod.fetchone()
if rs_prod[0] > 0:
v_env = 'PROD'
result['code'] = '1'
result['message'] = format_check(v_env, '触发器已经存在！')
return result
cr_uat.close()
cr_prod.close()
return result
def check_ddl_syntax(p_dbid, p_sql):
result = {}
result['code'] = '0'
result['message'] = ''
p_ds = get_ds_by_dsid(p_dbid)
db_uat = get_connection_ds_uat(p_ds)
cr_uat = db_uat.cursor()
v_sql_check = ''
v_sql_roll = ''
if p_sql.upper().count("CREATE") > 0 and get_obj_type(p_sql) == 'TABLE':
try:
v_sql_check = p_sql.replace(get_obj_name(p_sql),
get_obj_name(p_sql) + '_test')
v_sql_roll = 'drop table ' + get_obj_name(p_sql) + '_test'
cr_uat.execute(v_sql_check)
cr_uat.execute(v_sql_roll)
db_uat.commit()
cr_uat.close()
except:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_mysql_error(v_env,
exception_info_mysql())
return result
if p_sql.upper().count("CREATE") > 0 and get_obj_type(p_sql) == 'VIEW':
try:
v_sql_check = p_sql.replace(get_obj_name(p_sql),
get_obj_name(p_sql) + '_test')
v_sql_roll = 'drop view ' + get_obj_name(p_sql) + '_test'
cr_uat.execute(v_sql_check)
cr_uat.execute(v_sql_roll)
db_uat.commit()
cr_uat.close()
except:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_mysql_error(v_env,
exception_info_mysql())
return result
if p_sql.upper().count("CREATE") > 0 and get_obj_type(p_sql) == 'INDEX':
try:
v_tab_name = p_sql.upper().split('ON')[1].split('(')[0].replace(
' ', '')
v_sql_check = p_sql
v_sql_roll = 'drop index ' + get_obj_name(
p_sql) + ' on ' + v_tab_name
cr_uat.execute(v_sql_check)
cr_uat.execute(v_sql_roll)
db_uat.commit()
cr_uat.close()
except:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_mysql_error(v_env,
exception_info_mysql())
return result
if get_obj_type(p_sql) == 'FUNCTION':
try:
v_sql_check = p_sql.replace(get_obj_name(p_sql),
get_obj_name(p_sql) + '_test')
v_sql_roll = 'drop function ' + get_obj_name(p_sql) + '_test'
cr_uat.execute(v_sql_check)
cr_uat.execute(v_sql_roll)
db_uat.commit()
cr_uat.close()
except:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_mysql_error(v_env,
exception_info_mysql())
return result
if get_obj_type(p_sql) == 'PROCEDURE':
try:
v_sql_check = p_sql.replace(get_obj_name(p_sql),
get_obj_name(p_sql) + '_test')
v_sql_roll = 'drop procedure ' + get_obj_name(p_sql) + '_test'
cr_uat.execute(v_sql_check)
cr_uat.execute(v_sql_roll)
db_uat.commit()
cr_uat.close()
except:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_mysql_error(v_env,
exception_info_mysql())
return result
if get_obj_type(p_sql) == 'TRIGGER':
try:
v_sql_check = p_sql.replace(get_obj_name(p_sql),
get_obj_name(p_sql) + '_test')
v_sql_roll = 'drop trigger ' + get_obj_name(p_sql) + '_test'
cr_uat.execute(v_sql_check)
cr_uat.execute(v_sql_roll)
db_uat.commit()
cr_uat.close()
except:
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_mysql_error(v_env,
exception_info_mysql())
return result
return result
