#*************************************************************************/
#* 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.        */
#*************************************************************************/
# python script that copy values and structure from mysql to postgresql
# VERSION 0.4

# FUNCTION LIBRARY

def generate_default_values(v_table_name):
	"alter table adding default values to fields"
	for field in str_d_tab:
		if str(field[4])!="None":
			v_ddl_default="ALTER TABLE "+v_table_name+" ALTER COLUMN "+field[0]+" SET DEFAULT '"+str(field[4])+"';"
			try:
				c_pgs.execute(v_ddl_default)
				pgsql_conn.commit()
			except:
				print "error in the table definition. Table name: "+v_table_name+" field: "+field[0]
				print v_ddl_default
		
def generate_constr_idx(v_table_name):
	"alter table adding index and constraints"
	for field in str_d_tab:
		if field[3]=='PRI':
			v_ddl_const="ALTER TABLE "+v_table_name+" ADD CONSTRAINT "+v_table_name+"_"+field[0]+"_PK PRIMARY KEY ("+field[0]+");"
		elif field[3]=='MUL':
			v_ddl_const="CREATE INDEX  "+v_table_name+"_"+field[0]+"_IDX  ON "+v_table_name+" USING btree ("+field[0]+");"
		
		if v_ddl_const!="":
			try:
				c_pgs.execute(v_ddl_const)
				pgsql_conn.commit()
				v_ddl_const=""
			except:
				print "error in alter table. Table name: "+v_table_name
				print v_ddl_const
				v_ddl_const=""

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 
import string
import MySQLdb
from pyPgSQL import PgSQL


#definition of data dictionary for translating data type
dic_datatype={'mediumint':'int8','tinyint':'int2','smallint':'int2','int':'int8','varchar':'varchar'}
dic_datavalue={'None':'0'}
dic_null={'YES':'NULL','':'NOT NULL'}


#connection opening against mysql and postgresql
mysql_conn = MySQLdb.connect(db='mysql_database', host='localhost', user='username', passwd='password')
pgsql_conn = PgSQL.connect(database='postgresql_database', host='localhost', user='username', password='password')


#BEGIN STRUCTURE MIGRATION
#define mysql cursor 
c_mys = mysql_conn.cursor()

#define postgresql cursor 
c_pgs = pgsql_conn.cursor()

#show mysql structure
c_mys.execute('show tables;')
str_l_tab=c_mys.fetchall()

#for cycle for create the table ddl
for table_name in str_l_tab:
	c_mys.execute('describe '+table_name[0]+';')
	str_d_tab=c_mys.fetchall()
	#drop table
	try:
		c_pgs.execute('DROP TABLE '+table_name[0]+' ;' )
		pgsql_conn.commit()
	except:
		print "error in Drop Table: "+table_name[0]
		
	#GENERATING DML TABLES
	v_ddl_pg='CREATE TABLE '+table_name[0]+' '
	v_ddl_pg+='('
	for field in str_d_tab:
		try:
			t_field_type = string.split(field[1])
			t_field_type = string.split(t_field_type[0],'(')
			t_field_leng = string.split(t_field_type[1],')')
		except:
			v_field_type = t_field_type[0]
			t_field_leng = ""
		try:
			if field[5]=='auto_increment':
				v_field_type='serial'
			else:
				v_field_type = dic_datatype[t_field_type[0]]
		except:
			v_field_type=field[1]
	
		v_ddl_pg+=field[0]+" "+ v_field_type + " "+dic_null[field[2]]+", "
	v_ddl_pg=v_ddl_pg[0:(len(v_ddl_pg)-2)]
	v_ddl_pg+=');\n\r'
	try:
		c_pgs.execute(v_ddl_pg)
		pgsql_conn.commit()
		print 'create table: '+table_name[0]
		generate_constr_idx(table_name[0])
		generate_default_values(table_name[0])
	except:
		print 'ERROR: can''t create table: '+table_name[0]
		
	
#END STRUCTURE MIGRATION

#BEGIN DATA MIGRATION
#for cycle for copy data
for table_name in str_l_tab:
	print "importing table: " + table_name[0]
	c_mys.execute('select * from '+table_name[0]+';')
	str_d_tab=c_mys.fetchall()
	for record in str_d_tab:
		v_dml_pg='insert into '+table_name[0]+' values ('
		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_dml_pg=v_dml_pg[0:(len(v_dml_pg)-1)]
		v_dml_pg+=');'
		c_pgs.execute(v_dml_pg)
	pgsql_conn.commit()
	#select the total inserted record
	c_pgs.execute('select COUNT(*) AS num_record from '+table_name[0]+';')
	v_num_rec=c_pgs.fetchall()
	print str(v_num_rec[0][0])+" rows imported"
	#setup serial sequences
	c_pgs.execute("select relname from pg_class where relkind='S' and relname like '%"+table_name[0]+"%'; ")
	v_seq_name=c_pgs.fetchone() 
	
	try:
		v_field_seq=v_seq_name[0][len(table_name[0])+1:len(v_seq_name[0])-4]
		c_pgs.execute("select max("+v_field_seq+") from "+table_name[0])
		v_max_seq=c_pgs.fetchone() 
		v_max_seq=v_max_seq[0]+1
		print v_max_seq
		c_pgs.execute("ALTER SEQUENCE  "+v_seq_name[0]+" RESTART WITH "+str(v_max_seq)+";")
		pgsql_conn.commit()
		print "sequence: "+v_seq_name[0]+" restarted at "+str(v_max_seq)
	except:
		print "WARN: table "+table_name[0]+" don't have auto_increment fields"
	
	
		
c_pgs.close()
c_mys.close()