#*************************************************************************/ #* PHPBB - MYSQL TO POSTGRESQL - MIGRATION SCRIPT */ #* ============================================ */ #* */ #* Copyright (c) 2006 by Federico Campoli (neo@thezion.net) */ #* */ #* This program is free software. You can redistribute it and/or modify */ #* it under the terms of the GNU General Public License as published by */ #* the Free Software Foundation; either version 2 of the License. */ #*************************************************************************/ # copy structure and data from mysql to postgresql. works with phpbb # VERSION 0.5Beta # FUNCTION LIBRARY def generate_data_type(v_field_type,v_lenght): "generate the correct postgres ddl entry for datatype" #field int8 if v_field_type=="mediumint" or v_field_type=="bigint": v_field_pg_type="int8" return v_field_pg_type #field int4 if v_field_type=="tinyint" or v_field_type=="int" or v_field_type=="smallint": v_field_pg_type="int4" return v_field_pg_type #field date if v_field_type=="datetime": v_field_pg_type="date" return v_field_pg_type #field text if v_field_type=="enum" or v_field_type=="tinytext" or v_field_type=="longtext" or v_field_type=="longblob" or v_field_type=="mediumblob" or v_field_type=="blob" or v_field_type=="mediumtext": v_field_pg_type="text" return v_field_pg_type #field float8 if v_field_type=="double": v_field_pg_type="float8" return v_field_pg_type #field float4 if v_field_type=="float": v_field_pg_type="float4" return v_field_pg_type return v_field_type+ " " + v_lenght def escape_char(sql_string): "escape ' character for sql string" if sql_string!=None: new_sql_string=string.replace(sql_string,"\\","") new_sql_string=string.replace(new_sql_string,"'","''") else: new_sql_string=sql_string return new_sql_string #import objects string and library for mysql and postgresql import string import MySQLdb from pyPgSQL import PgSQL import os #datadictionary for mysql to postgresql translation dic_datatype={'mediumint':'int8','tinyint':'int2','smallint':'int2','int':'int8','varchar':'varchar','bigint':'int8','text':'text','char':'char','datetime':'date','longtext':'text','tinytext':'text'} dic_datavalue={'None':'Null'} dic_null={'YES':'NULL','':'NOT NULL'} #variable multi read. i_multi_read=10000 #connection opening to mysql and postgresql #you need to change the next two lines for your connections mysql_conn = MySQLdb.connect(db='MYSQL_DATABASE', host='MYSQL_HOST', user='MYSQL_USER', passwd='MYSQL_PASSWORD') pgsql_conn = PgSQL.connect(database='PGSQL_DATABASE', host='PGSQL_HOST', user='PGSQL_USER', password='PGSQL_PASSWORD') #MIGRATION OF TABLE STRUCTURE #cursor to mysql c_mys = mysql_conn.cursor() #cursor to postgresql c_pgs = pgsql_conn.cursor() #MYSQL SHOW DATA DICTIONARY c_mys.execute('show tables;') str_l_tab=c_mys.fetchall() #DROP AND GENERATE TABLES ON THE POSTGRESQL TARGET DATABASE for table_name in str_l_tab: c_mys.execute('describe '+table_name[0]+';') str_d_tab=c_mys.fetchall() #drop table try: v_drop_table_pg='DROP TABLE '+table_name[0]+' ;' c_pgs.execute(v_drop_table_pg) pgsql_conn.commit() except: print "Error: PG > Drop Table "+table_name[0]+" is not possible" print "Notice: PG > Create Table: "+table_name[0] v_ddl_pg='CREATE TABLE '+table_name[0]+' ' v_ddl_pg+='(' #generate DDL for table definition for field in str_d_tab: t_field_type = string.split(field[1]) #extract data type and attrib for current field t_field_len_type = string.split(t_field_type[0],'(') str_field_type = t_field_len_type[0] try: str_field_attr = t_field_type [1] except: str_field_attr="" #extract lenght of current field try: str_field_lengt="("+str(string.split(t_field_len_type [1],')')[0])+")" except: str_field_lengt="" v_ddl_pg+=str(field[0])+" "+generate_data_type(str(str_field_type),str(str_field_lengt))+", " v_ddl_pg=v_ddl_pg[0:(len(v_ddl_pg)-2)] v_ddl_pg+=');\n\r' #create table c_pgs.execute(v_ddl_pg) pgsql_conn.commit() #COPY DATA BETWEEN MYSQL AND POSTGRESQL DATABASES for table_name in str_l_tab: print "Notice: PG > importing data into table: " + table_name[0] #extract how many records there are in the table c_mys.execute('select count(*) as num_record from '+table_name[0]+';') #calculate how many iteration needs for data transfer lng_num_record=c_mys.fetchone() i_num_read=lng_num_record[0]/i_multi_read rng_num_read=range(i_num_read+1) v_dml_pg="" #copy data between tables for rng_item in rng_num_read: str_sql='select * from '+table_name[0]+' limit '+str(rng_item*i_multi_read)+', '+str(i_multi_read)+' ;' c_mys.execute(str_sql) v_dml_pg_def="" try: str_d_tab=c_mys.fetchall() for record in str_d_tab: v_dml_pg='INSERT INTO '+table_name[0]+' VALUES (' v_position=0 for value in record: try: v_field_value = dic_datavalue[str(value)] except: v_field_value = "'"+escape_char(str(value))+"'" v_dml_pg+=""+v_field_value +"," v_position=+1 v_dml_pg=v_dml_pg[0:(len(v_dml_pg)-1)] v_dml_pg+=");" try: c_pgs.execute(v_dml_pg) except: print str_sql print v_dml_pg raise ("asd") pgsql_conn.commit() except: print "Error: PG > Copy data for table "+table_name[0]+" is not possible" print str((rng_item+1)*i_multi_read)+" records imported" print str(lng_num_record[0])+" records imported" #CREATE SEQUENCES CONSTRAINTS AND INDEX for table_name in str_l_tab: print " Alter Table: " + table_name[0] v_table_name=table_name[0] #determine the table structure c_mys.execute('describe '+table_name[0]+';') str_desc_tab=c_mys.fetchall() for item in str_desc_tab: #create sequence for auto_increment data type if item[5]=='auto_increment': try: c_pgs.execute('DROP SEQUENCE '+table_name[0]+'_id_seq CASCADE;') pgsql_conn.commit() except: print 'Error: PG > The sequence '+table_name[0]+' is not present' try: c_pgs.execute("CREATE SEQUENCE "+table_name[0]+"_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;") pgsql_conn.commit() except: print "Error: PG > Unable to create the sequence "+table_name[0]+"_id_seq" v_alter_table="ALTER TABLE "+table_name[0]+" ALTER COLUMN "+item[0]+" SET DEFAULT nextval('"+table_name[0]+"_id_seq'::regclass);" c_pgs.execute("select max("+item[0]+") from "+table_name[0]) v_max_seq=c_pgs.fetchone() #reset sequence current value to max of auto_increment value if str(v_max_seq[0])!='None': print table_name[0] v_max_seq=v_max_seq[0]+1 c_pgs.execute("ALTER SEQUENCE "+table_name[0]+"_id_seq RESTART WITH "+str(v_max_seq)+";") pgsql_conn.commit() c_pgs.execute(v_alter_table) pgsql_conn.commit() else: try: v_def_value = dic_datavalue[str(item[4])] except: v_def_value = str(item[4]) #alter column. set default value if v_def_value!="Null": try: v_def_value=int(v_def_value) except: v_def_value="'"+str(v_def_value)+"'" if v_def_value !="": try: v_alter_table="ALTER TABLE "+table_name[0]+" ALTER COLUMN "+item[0]+" SET DEFAULT "+ str(v_def_value) #print v_alter_table c_pgs.execute(v_alter_table) pgsql_conn.commit() except: print "Error: PG > Unable to set default value" #alter table. create primary keys and index on column if item[3]=='PRI': v_ddl_const="ALTER TABLE "+v_table_name+" ADD CONSTRAINT "+v_table_name+"_"+item[0]+"_PK PRIMARY KEY ("+item[0]+");" elif item[3]=='MUL': v_ddl_const="CREATE INDEX "+v_table_name+"_"+item[0]+"_IDX ON "+v_table_name+" USING btree ("+item[0]+");" if v_ddl_const!="": try: c_pgs.execute(v_ddl_const) pgsql_conn.commit() v_ddl_const="" except: print "Error: PG > Unable to set primary key or index on "+v_table_name print v_ddl_const v_ddl_const="" #closing connections c_pgs.close() c_mys.close()