# -*- coding: utf-8 -*- # """ 1.读取mysql源端数据库信息 2.查询系统表的数据类型，做出映射表 3.根据映射表，生成oracle的ddl 4.创建索引和试图等对象 5.字段名命中关键字检查 6.分区表、主键、唯一索引、二级索引 7. """ import random import string import os import re import subprocess import time import datetime from configparser import ConfigParser import ast import mysql.connector def f_prepare_datatype(): pass def f_get_tidb_datatype(db_conn_str='', tabschema=''): """ get table datatype """ tidb_conn_dict = ast.literal_eval(db_conn_str) stmt_query = """ select data_type, max(character_maximum_length) as character_maximum_length, max(character_octet_length) as character_octet_length, max(numeric_precision) as numeric_precision, max(numeric_scale) as numeric_scale from information_schema.columns where table_schema='%s' group by data_type """ %(tabschema) #debug # print(type(tidb_conn_dict), tidb_conn_dict) # print(type(stmt_query), stmt_query) cnx = mysql.connector.connect(**tidb_conn_dict) cursor = cnx.cursor() cursor.execute(stmt_query) t2o_datatype_mapping_lst = [] t2o_datatype_mapping_dic = {} for row in cursor: # print(row) t2o_datatype_mapping_lst.append(row[0]) if row[0] in string_type_mapping_dict: print('datatype mapping :%10s --> %15s ,max length: %4s' %(row[0], ora_data_type_mapping_dict[row[0]], row[1])) elif row[0] in numeric_type_mapping_dict: print('datatype mapping :%10s --> %15s ,max length: %4s,%4s' %(row[0], ora_data_type_mapping_dict[row[0]], row[3], row[4])) else: print('datatype mapping :%10s --> %15s ' % (row[0], ora_data_type_mapping_dict[row[0]])) print("find %s types from %s tables" % (len(t2o_datatype_mapping_lst), tabschema)) print(t2o_datatype_mapping_lst) cursor.close() cnx.close() def f_get_tidb_tables(db_conn_str='', tabschema=''): """ get table list """ tidb_conn_dict = ast.literal_eval(db_conn_str) stmt_query = """ select table_name from information_schema.tables where table_schema='%s' order by CREATE_TIME """ %(tabschema) stmt_query = """ select t1.table_name from information_schema.tables t1 inner join information_schema.columns t2 on t1.table_schema=t2.table_schema and t1.table_name=t2.table_name where t1.table_schema='%s' and t2.data_type in ('blob','mediumblob','tinyblob','varbinary','bit','binary') and t2.column_key='PRI' order by CREATE_TIME """ %(tabschema) #debug # print(type(tidb_conn_dict), tidb_conn_dict) # print(type(stmt_query), stmt_query) cnx = mysql.connector.connect(**tidb_conn_dict) cursor = cnx.cursor() cursor.execute(stmt_query) table_lst = [] for row in cursor: # print(row) table_lst.append(row[0]) print("total %s tables in %s" % (len(table_lst), tabschema)) cursor.close() cnx.close() return table_lst def f_generate_create_table(db_conn_str='', tabschema='', tabname=''): ''' generate create table stmt ''' tidb_conn_dict = ast.literal_eval(db_conn_str) stmt_query = """ select ORDINAL_POSITION, COLUMN_NAME,DATA_TYPE, case when DATA_TYPE in ('char','varchar') then CHARACTER_MAXIMUM_LENGTH when DATA_TYPE in ('decimal','float') then NUMERIC_PRECISION end as MAXIMUM_LENGTH, case when DATA_TYPE in ('decimal') then NUMERIC_SCALE end as NUMERIC_SCALE, case when is_nullable='NO' then 'NOT NULL' else '' end as is_nullable from information_schema.columns where table_schema='%s' and table_name='%s' order by ORDINAL_POSITION """ % (tabschema, tabname) # print(stmt_query) cnx = mysql.connector.connect(**tidb_conn_dict) cursor = cnx.cursor() cursor.execute(stmt_query) # generate table ddl print('%s [INFO] create table %s.%s () start' % (time.strftime("%Y-%m-%d-%H.%M.%S", time.localtime()), tabschema, tabname)) tab_ddl0 = 'create table %s.%s (\n' % (tabschema, tabname) for row in cursor: # print(row) colname = row[1] coltype = ora_data_type_mapping_dict[row[2]] if re.search('\(', coltype) or coltype in ['date','blob','clob','raw']: # print('%s %s %s,' %(colname, coltype, row[5])) tab_ddl0 = '%s %s %s %s,\n' %(tab_ddl0, colname, coltype, row[5]) elif coltype in ['char', 'float', 'varchar2']: # print('%s %s(%s) %s,' % (colname, coltype, row[3], row[5])) if row[3] > ora_type_max_length_dict[coltype]: max_len = ora_type_max_length_dict[coltype] else: max_len = row[3] tab_ddl0 = '%s %s %s(%s) %s,\n' % (tab_ddl0, colname, coltype, max_len, row[5]) elif coltype in ['number','decimal']: # print('%s %s(%s,%s) %s,' % (colname, coltype, row[3], row[4], row[5])) if row[3] > ora_type_max_length_dict[coltype]: max_len = ora_type_max_length_dict[coltype] else: max_len = row[3] tab_ddl0 = '%s %s %s(%s,%s) %s,\n' % (tab_ddl0, colname, coltype, max_len, row[4], row[5]) else: # print('%s %s %s,' % (colname, coltype, row[5])) tab_ddl0 = '%s %s %s %s,\n' % (tab_ddl0, colname, coltype, row[5]) # print(') TABLESPACE %s ;' % (tbspacename)) # tab_dml0 = re.sub(',$', ')', tab_dml0) #get pk ddl stmt_query = """ select group_concat(column_name order by seq_in_index SEPARATOR ',') as pk_cols from INFORMATION_SCHEMA.TIDB_INDEXES where table_schema='%s' and table_name='%s' and key_name='PRIMARY' """ % (tabschema, tabname) stmt_query = """ select group_concat(t1.column_name order by seq_in_index SEPARATOR ',') as pk_cols, group_concat(t2.data_type) as pk_cols_type from INFORMATION_SCHEMA.TIDB_INDEXES t1 inner join INFORMATION_SCHEMA.columns t2 on t1.table_schema=t2.table_schema and t1.table_name=t2.table_name and t1.column_name=t2.column_name where t1.table_schema='%s' and t1.table_name='%s' and t1.key_name='PRIMARY' """ % (tabschema, tabname) cursor.execute(stmt_query) for row in cursor: if row[0] is not None and not re.search('blob|bit|binary', row[1]): pk_cols = row[0] tab_ddl0 = '%s CONSTRAINT %s_pk PRIMARY KEY (%s))\n' % (tab_ddl0, tabname.lower(), pk_cols) else: tab_ddl0 = re.sub(',$', ')', tab_ddl0) tab_ddl0 = '%s TABLESPACE %s ;' % (tab_ddl0, tbspacename) # print('\n-------------------------------------------') # print('----create table %s.%s' % (tabschema, tabname)) # print('-------------------------------------------\n') # print('%s TABLESPACE %s ;' % (tab_ddl0, tbspacename)) stmt_query = """ select key_name,NON_UNIQUE,group_concat(column_name order by seq_in_index SEPARATOR ',') as key_cols from INFORMATION_SCHEMA.TIDB_INDEXES where table_schema='%s' and table_name='%s' and key_name<>'PRIMARY' group by key_name,NON_UNIQUE """ % (tabschema, tabname) # print(stmt_query) idx_ddl0 = '' cursor.execute(stmt_query) for row in cursor: # key_cols = row[1] if row[1] == 0: uk = 'unique' else: uk = '' #index name duplicate if re.search(tabname.lower(), row[0]): idx_name = '%s.%s' % (tabschema, row[0]) else: idx_name = '%s.%s_%s' % (tabschema, tabname, row[0]) idx_ddl0 = '%s create %s index %s on %s.%s(%s);\n' % (idx_ddl0, uk, idx_name, tabschema, tabname, row[2]) # print('\n----create indexes for table %s.%s' % (tabschema, tabname)) # print(idx_ddl0) tab_ddl_header = "\n%s \n----create table %s.%s \n%s" %('-'*40, tabschema, tabname, '-'*40) idx_ddl_header = "\n----create index for table %s.%s " %(tabschema, tabname) tab_ddl = "\n%s \n%s \n%s \n%s" %(tab_ddl_header, tab_ddl0, idx_ddl_header, idx_ddl0) # print(tab_ddl) cursor.close() cnx.close() return tab_ddl def f_write_file(filename='', context=''): ''' write ddl into files ''' with open(filename, mode='a', encoding="utf-8") as f: f.write(context) def main(): """ """ #run test result global config global tbspacename config = ConfigParser() config.read('t2o_transfer_dbcfg.ini') # read config file tabschema = config.get('db', 'dbschema') tbspacename = config.get('oracle', 'tablespace') db_conn_str = config.get('tidb', 'connstr') ora_type_max_length_str = config.get('datatype', 'ora_type_max_length') numeric_type_mapping_str = config.get('datatype', 'numeric_type_mapping') date_type_mapping_str = config.get('datatype', 'date_type_mapping') string_type_mapping_str = config.get('datatype', 'string_type_mapping') # debug print('db_conn_str:', type(db_conn_str), db_conn_str) print('tabschema:', type(tabschema), tabschema) print('string_type_mapping_str:', type(string_type_mapping_str), string_type_mapping_str) global ora_data_type_mapping_dict global date_type_mapping_dict global numeric_type_mapping_dict global string_type_mapping_dict global ora_type_max_length_dict #handle data type mapping ora_type_max_length_dict = ast.literal_eval(ora_type_max_length_str) string_type_mapping_dict = ast.literal_eval(string_type_mapping_str) numeric_type_mapping_dict = ast.literal_eval(numeric_type_mapping_str) date_type_mapping_dict = ast.literal_eval(date_type_mapping_str) ora_data_type_mapping_dict = {} ora_data_type_mapping_dict.update(string_type_mapping_dict) ora_data_type_mapping_dict.update(numeric_type_mapping_dict) ora_data_type_mapping_dict.update(date_type_mapping_dict) print('ora_data_type_mapping_dict:', type(ora_data_type_mapping_dict), ora_data_type_mapping_dict) #create empty ddl file ddl_filename = '%s.ddl' % (tabschema) if os.path.exists(ddl_filename): os.remove(ddl_filename) #get table datatype mapping, can modify config file if mapping rule is wrong f_get_tidb_datatype(db_conn_str, tabschema ) #get all tables in certain schema table_list = f_get_tidb_tables(db_conn_str, tabschema ) print(type(table_list), len(table_list)) # table_list = ['ACCOUNT'] #generate ddl by table for tablename in table_list: tableddl_str = f_generate_create_table(db_conn_str, tabschema, tablename) f_write_file(ddl_filename, tableddl_str) # print(tableddl_str) if __name__ == '__main__': main()