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_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_proc_valid_ddl(p_sql):
result = {}
result['code'] = '0'
result['message'] = ''
#规则一：允许有TRUNCATE语句
if p_sql.upper().count('TRUNCATE') > 0 and p_sql.upper().count('TABLE') > 0 \
and p_sql.upper().find("CREATE")>p_sql.upper().find("TRUNCATE") :
return result
#规则二：允许有CREATE INDEX，DROP INDEX语句,允许创建临时表
if p_sql.upper().count('CREATE') > 0 and p_sql.upper().count('INDEX') > 0 \
and p_sql.upper().find("INDEX")>p_sql.upper().find("CREATE") :
return result
if p_sql.upper().count('DROP') > 0 and p_sql.upper().count('INDEX') > 0 \
and p_sql.upper().find("INDEX")>p_sql.upper().find("DROP") :
return result
if p_sql.upper().count('CREATE') > 0 and p_sql.upper().count('TABLE') > 0 and p_sql.upper().count('TEMPORARY')>0 \
and p_sql.upper().find("TEMPORARY") > p_sql.upper().find("CREATE") \
and p_sql.upper().find("TABLE") > p_sql.upper().find("TEMPORARY"):
return result
#规则三：不允许在过程中创建普通表，修改表结构
if p_sql.upper().count('CREATE') > 0 and p_sql.upper().count('TABLE') > 0 and p_sql.upper().count('TEMPORARY')== 0 \
and p_sql.upper().find("TABLE") > p_sql.upper().find("CREATE") :
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_check(v_env, '存储过程中不允许创建普通表！')
return result
if p_sql.upper().count('ALTER') > 0 and p_sql.upper().count('TABLE') > 0 \
and p_sql.upper().find("TABLE")>p_sql.upper().find("ALTER") :
v_env = 'UAT'
result['code'] = '1'
result['message'] = format_check(v_env, '存储过程中不允许修改表结构！')
return result
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