Xׯdc}ddlZGddZGddeZGddeZGdd eZGd d ZGd d eZGddeZGddeZGddeZ GddZ Gdde Z Gdde Z Gdde Z GddZdS)Nc<eZdZdZedZdZdZdZdS) ImportScriptcd|_dS)Nzimport_errors.logerror_log_nameselfs &..\modules\migration_bulk_copy_data.py__init__zImportScript.__init__s1c~|dkrtS|dkrtS|dkrtSdSNwindowslinuxdarwin)ImportScriptWindowsImportScriptLinuxImportScriptDarwin) target_oss r createzImportScript.createsL  ! !&(( ( ' ! !$&& & ( " "%'' '4r cdSN)r connection_args path_to_file schema_names r generate_import_scriptz#ImportScript.generate_import_script(r cdSrrrs r get_script_extzImportScript.get_script_ext*rr cdSrrr tablers r get_import_cmdzImportScript.get_import_cmd,rr N) __name__ __module__ __qualname__r staticmethodrrr r$rr r rrsX222\WVV"""77777r rc eZdZdZdZdZdS)rc2d|dd|d|ddSNzLOAD DATA INFILE ' target_schema_#####_import/z ' INTO TABLE target_tablez) FIELDS TERMINATED BY ',' ENCLOSED BY '';rr"s r r$z"ImportScriptWindows.get_import_cmd1gqvxGrHrHrHJVJVJVX]^lXmXmXmn nr cdS)Ncmdrrs r r z"ImportScriptWindows.get_script_ext4sur c dg}|d|d|d|jd|j|d|dd|d d |j|d |d |d |d|d|d|d |d|d|z|d|d|j|d|d|d|d |d|d|j|d|d|d|d |d|dd|d d|d|d|j |d|d|d|d |d|z|d|d|d|S)Nz @ECHO OFF$echo Started load data. Please wait.zSET MYPATH=%%~dp0zIF EXIST %%MYPATH%%z del /F %%MYPATH%%$SET command=mysql.exe -h127.0.0.1 -P target_port -u target_userz~ -p -s -N information_schema -e "SELECT Variable_Value FROM GLOBAL_VARIABLES WHERE Variable_Name = 'datadir'" 2^>^> %%MYPATH%%z=FOR /F "tokens=* USEBACKQ" %%%%F IN ^(^`%%command%%^`^) DO ^(z SET DADADIR=%%%%Fz^)zif %%ERRORLEVEL%% GEQ 1 ^(z9 echo Script has failed. See the log file for details.z exit /b 1zpushd %%DADADIR%%mkdir %s_#####_importzxcopy %%MYPATH%%*.csv z _#####_import\* 2^>^> %%MYPATH%%zxcopy %%MYPATH%%*.sql zmysql.exe -h127.0.0.1 -Pz -p ^< z_#####_import\z 2^>^> %%MYPATH%%zrmdir %s_#####_import /s /qecho Finished load datapopdpauseappendrr rrroutputs r rz*ImportScriptWindows.generate_import_script7s <=== )*** TM`M`M`bfbubuvwww N]^kNlNlNln}~KnLnLnLNRNaNab c c c UVVV -... d 2333 QRRR o&&& d )*** - ;<<< ^i^i^ikok~k~ A A A 2333 QRRR o&&& d ^i^i^ikok~k~ A A A 2333 QRRR o&&& d  kz|IlJlJlJL[\iLjLjLjlwlwlwyEyEyEGKGZGZ[ \ \ \ 2333 QRRR o&&& d 3kABBB /000 f g r Nr%r&r'r$r rrr r rr0sDnnn.....r rc eZdZdZdZdZdS)rc2d|dd|d|ddSr+rr"s r r$z ImportScriptLinux.get_import_cmdjr/r cdSNshrrs r r z ImportScriptLinux.get_script_extmtr c dg}|d|d|jz|d|jz|d|d|dd|d d |j|d |d |d |d|d|d|z|d|d|j|d |d |d |d|d|d|j|d |d |d |d|d|d|dd|d d|d|d|j |d |d |d |d|d|d|z|d|SNz #!/bin/bashzMYPATH=\`pwd\`zif [ -f \$MYPATH/%s ] ; thenz rm \$MYPATH/%sfiz@TARGET_DIR=\`MYSQL_PWD=$arg_target_password mysql -h127.0.0.1 -Pr5r6r7z| -s -N information_schema -e 'SELECT Variable_Value FROM GLOBAL_VARIABLES WHERE Variable_Name = \"datadir\"'\` 2>> \$MYPATH/zif [ \$? -ne 0 ];thenz8 echo Script has failed. See the log file for details.z exit 1zpushd \$TARGET_DIRr8zcp \$MYPATH/*.csv z_#####_import/ 2>> \$MYPATH/zcp \$MYPATH/*.sql r3z3MYSQL_PWD=$arg_target_password mysql -h127.0.0.1 -Pz < r-z 2>> \$MYPATH/r9zrm -rf %s_#####_importr:r<r>s r rz(ImportScriptLinux.generate_import_scriptpv &''' 4t7JJKKK *T-@@AAA d fuvCfDfDfDFUVcFdFdFdfjfyfyz { { { -... PQQQ k""" d *+++ - ;<<< kkk[_[n[noppp -... PQQQ k""" d kkk[_[n[noppp -... PQQQ k""" d <=== |KLY|Z|Z|Z\kly\z\z\z|G|G|GIUIUIUW[WjWjk l l l -... PQQQ k""" d /000 .<=== f r Nr@rr r rriDnnn+++++r rc eZdZdZdZdZdS)rc2d|dd|d|ddSr+rr"s r r$z!ImportScriptDarwin.get_import_cmdr/r cdSrDrrs r r z!ImportScriptDarwin.get_script_extrFr c dg}|d|d|jz|d|jz|d|d|dd|d d |j|d |d |d |d|d|d|z|d|d|j|d |d |d |d|d|d|j|d |d |d |d|d|d|dd|d d|d|d|j |d |d |d |d|d|d|z|d|SrHr<r>s r rz)ImportScriptDarwin.generate_import_scriptrJr Nr@rr r rrrKr rc0eZdZdZedZdZdS) SourceRDBMSc||_dSr source_os)r rTs r r zSourceRDBMS.__init__s "r c|dkrt|S|dkrt|S|dkrt|S|dkrt|SdS)Nmssqlmysql postgresql sqlanywhere)SourceRDBMSMssqlSourceRDBMSMysqlSourceRDBMSPostgresqlSourceRDBMSSqlAnywhere) source_rdbmsrTs r rzSourceRDBMS.createsk 7 " "#I.. . W $ $#I.. . \ ) )(33 3 ] * *))44 44r cdSrr)r r#s r get_copy_table_cmdzSourceRDBMS.get_copy_table_cmdrr N)r%r&r'r r(rr`rr r rQrQsE###  \ -,,,,r rQceZdZdZdS)rZcdtt|t|zzS)Nzbcp "SELECT %(select_expression)s FROM %(unquoted_source_schema)s.%(unquoted_source_table)s" queryout %(source_table)s.csv -c -t, -T -S .\%(source_instance)s -U %(source_user)s -P %%arg_source_passwordsdictlistitemsr r#rs r r`z#SourceRDBMSMssql.get_copy_table_cmds\_cdhinititivivdwdwz~NTTVVzWzWdW_X_XX Xr Nr%r&r'r`rr r rZrZs(XXXXXr rZceZdZdZdZdS)r[cd|jdkrSdtt|t|zzSdtt|t|zzS)Nrzumysqldump.exe --login-path=wb_migration_source -t --tab=. %(source_schema)s %(source_table)s --fields-terminated-by=,zMYSQL_PWD=$arg_source_password mysqldump -h127.0.0.1 -P%(source_port)s -u%(source_user)s -t --tab=/tmp %(source_schema)s %(source_table)s --fields-terminated-by=','rTrdrerfrgs r r`z#SourceRDBMSMysql.get_copy_table_cmdsK >Y & &KNRSWX]XcXcXeXeSfSfimn}nCnCnEnEiFiFSFNGNGG G}@DEIJOJUJUJWJWEXEX[_`o`u`u`w`w[x[xEx@y@yy yr c&|jdkrd|zSdS)Nrzomysql_config_editor.exe set --login-path=wb_migration_source -h127.0.0.1 -P%(source_port)s -u%(source_user)s -prS)r rs r get_cfg_editor_cmdz#SourceRDBMSMysql.get_cfg_editor_cmds0 >Y & &EHWW W ' &r N)r%r&r'r`rmrr r r[r[s:yyy WWWWWr r[ceZdZdZdS)r\cdtt|t|zzS)Nzwpsql -U %(source_user)s -d %(source_schema)s -c "COPY %(source_table)s TO stdout DELIMITER ',';" > %(source_table)s.csvrcrgs r r`z(SourceRDBMSPostgresql.get_copy_table_cmdsKNRSWX]XcXcXeXeSfSfimn}nCnCnEnEiFiFSFNGNGG Gr Nrhrr r r\r\s(GGGGGr r\ceZdZdZdS)r]cd|jdkrSdtt|t|zzSdtt|t|zzS)Nrzdbisql.exe -c "DBN=%(source_schema)s;UID=%(source_user)s;PWD=%arg_source_password%" "SELECT * FROM %(source_table)s; OUTPUT TO '%(source_table)s.csv' FORMAT TEXT DELIMITED BY ',' QUOTE '';zdbisql -c "DBN=%(source_schema)s;UID=%(source_user)s;PWD=$arg_source_password" "SELECT * FROM %(source_table)s; OUTPUT TO '%(source_table)s.csv' FORMAT TEXT DELIMITED BY ',' QUOTE '';rkrgs r r`z)SourceRDBMSSqlAnywhere.get_copy_table_cmdsK >Y & &VY]^bchcncncpcp^q^qtxyHyNyNyPyPtQtQ^QYRYRR RQTXY]^c^i^i^k^kYlYlostCtItItKtKoLoLYLTMTMM Mr Nrhrr r r]r]s(MMMMMr r]c0eZdZdZedZdZdS)DataCopyScriptcd|_dS)Nzbulk_copy_errors.logrrs r r zDataCopyScript.__init__ s4r c~|dkrtS|dkrtS|dkrtSdSr)DataCopyScriptWindowsDataCopyScriptLinuxDataCopyScriptDarwinrSs r rzDataCopyScript.createsL  ! !(** * ' ! !&(( ( ( " "')) )4r cdSrr)r tables script_pathr^s r generatezDataCopyScript.generaterr N)r%r&r'r r(rr|rr r rsrs sE555\?>>>>r rsceZdZdZdS)rvc d}dt|z}|dd}|dd} d|z} d| z} d|d|} d |z} t|d 5}|d |d |d |jd|jdt |t r|d|jz|d|d|d|d|||d|jd|d|d|d|d|d|dd|dd|jd|d|d|d|d|d|d|dn*|d|dt |ts|d |d!|d"|| fz|d#| z|d$| z|d%z}|d&| z|d'd(d)| d|d'd*d)| d|d'd+| zd)| d|d,|d-z|z z|D]J}|| ||d|jd|d|d|d|dt |t rF|d.|d/d0|d/d1|d2|d/z|d'| |d3|d/zd)| d|d%z}|d4|d-z|z |d/fzL|d&| z| || | }|D]}|d5|d6| d|d%z}|d7|d-z|z | fz|d8|d9| z|d:|d;|d<|d=|d>|d?|d@|dA|dB|dC|dD| dE|jd|d|d|d|d|d%z}|dF|d-z|z | fz|dG| dH|jd|d|d|d|d|dI| z|dJ|dK| z|dL| z|d8|dM| z|dNddddS#1swxYwYdS)ONr source_schemar,dump_%s%s.logimport_. import_%s.sqlw+z @ECHO OFF zSET MYPATH=%~dp0 zIF EXIST %MYPATH%z del /F %MYPATH%z zTmysql_config_editor.exe remove --login-path=wb_migration_source 2>> "%%MYPATH%%%s" zif %ERRORLEVEL% GEQ 1 ( z; echo Script has failed. See the log file for details. z exit /b 1 z) z 2>> "%MYPATH%z" r4 source_portr6 source_userz{ -p -s -N information_schema -e "SELECT Variable_Value FROM GLOBAL_VARIABLES WHERE Variable_Name = 'datadir'" 2>> "%MYPATH%z6FOR /F "tokens=* USEBACKQ" %%F IN (`%command%`) DO ( z SET DADADIR=%%F z6set arg_source_password="" zSET DADADIR=%TEMP%\ z6set arg_target_password="" zpushd %DADADIR% z&echo [%d %%%%] Creating directory %s z mkdir %s z pushd %s z copy NUL %s zecho SET SESSION UNIQUE_CHECKS=0;z >> !SET SESSION FOREIGN_KEY_CHECKS=0;use %s;z%echo [%d %%%%] Start dumping tables dzrename source_table.txt z.csv z del %s.sql %s.csvz echo [%d %%%%] Dumped table %s z(echo z) >> z+echo [%d %%%%] Generated import script %s zpopd zset TEMPDIR=%%DADADIR%%%s zHecho Set fso = CreateObject("Scripting.FileSystemObject") > _zipIt.vbs zUecho InputFolder = fso.GetAbsolutePathName(WScript.Arguments.Item(0)) >> _zipIt.vbs zQecho ZipFile = fso.GetAbsolutePathName(WScript.Arguments.Item(1)) >> _zipIt.vbs zecho CreateObject("Scripting.FileSystemObject").CreateTextFile(ZipFile, True).Write "PK" ^& Chr(5) ^& Chr(6) ^& String(18, vbNullChar) >> _zipIt.vbs zEecho Set objShell = CreateObject("Shell.Application") >> _zipIt.vbs zGecho Set source = objShell.NameSpace(InputFolder).Items >> _zipIt.vbs zAecho objShell.NameSpace(ZipFile).CopyHere(source) >> _zipIt.vbs zxecho Do Until objShell.NameSpace( ZipFile ).Items.Count ^= objShell.NameSpace( InputFolder ).Items.Count >> _zipIt.vbs z&echo wScript.Sleep 200 >> _zipIt.vbs zecho Loop >> _zipIt.vbs z,CScript _zipIt.vbs "%TEMPDIR%" "%DADADIR%z.zip" 2>> "%MYPATH%z0echo [%d %%%%] Zipped all files to %s.zip file zxcopy z.zip %MYPATH% 2>> "%MYPATH%z del %s.zip zdel _zipIt.vbs zdel /F /Q %s\*.* z rmdir %s z]echo Now you can copy %%MYPATH%%%s.zip file to the target server and run the import script. zpause ) lenr openwriter isinstancer[rmrr`r$rr rzrr{r^ import_scriptprogresstotal_progressrr,dir_namelog_fileimport_file_nameimport_sql_file_namefr#import_file_lineslines r r|zDataCopyScriptWindows.generates c&kk/q /2 q /2 },h&-:]]M +t $ $k ! GGO $ $ $ GG* + + + GGGtGZGZGZ\`\o\o\op q q q,(899 4pswtGGHHH5666WXXX+,,,   |7V7VWf7g7g7g7gimi|i|i|}~~~5666WXXX+,,,   RaboRpRpRprABOrPrPrPRVReReRefgggRSSS1222   5666WXXX+,,,    TUUU2333m-@AA VTUUU GG) * * * GG>(HAUU V V V GGNX- . . . GGNX- . . .!|H GG%(<< = = = GGG+I+I+IK_K_K_` a a a GGG+N+N+NPdPdPde f f f GGG9}+D+D+DFZFZFZ[ \ \ \ GG=CR`A`a b b b y y|7V7VW\^m7n7n7n7nptqDqDqDEFFF5666WXXX+,,,   l,<==FGGG%:O:O:OQVWeQfQfQfghhhGG,u^/DDEEE}/K/KES[_des_tSu/v/v/v/vyMyMyMNOOO#a<<3Q_@_afguav?wwxxxx GG%(88 9 9 9 - D D_Vjly z z ) J J7G7G7GHIIII!|H GGCxRU~XfGfhxFyy z z z GGJ    GG3h> ? ? ? GG` a a a GGm n n n GGi j j j GGo p p p GG] ^ ^ ^ GG_ ` ` ` GGY Z Z Z GGQ R R R GG> ? ? ? GG1 2 2 2 GGGhphphprvsFsFsFG H H H GG1 2 2 2 GGS T T T GG' ( ( ( GGG   !|H GGHHWZN]kLkmuKvv w w w GGGRVReReRef g g g GG1 2 2 2 GGS T T T GG' ( ( ( GGG    GG$x/ 0 0 0 GG( ) ) ) GG*X5 6 6 6 GGNX- . . . GGJ    GGuyAA B B B GGK Wk !k !k !k !k !k !k !k !k !k !k !k !k !k !k !k !k !k !s*_aaaNr%r&r'r|rr r rvrvs(u!u!u!u!u!r rvceZdZdZdS)rwc  d}dt|z}|dd}|dd} d|z} d| z} d|d|} d |z} t|d 5}tj|d |d |d |d|d|d|d|jz|d|jz|d|D]}|d|dz|d|d|dz|d|d|dz|d|d|d|d|d|| fz|d| z|d| z|d z}|d!d"d#| d$|d!d%d&| d$|d!d'| zd&| d$|d(|d)z|z z|D]}||||d*|jd$|d+|d,|d-|dt|tr|d.|dd/|dd0|d1|dz|d|d2|dz|d|d!| |d3|dzd&| d$|d z}|d4|d)z|z |dfz|d5| zt|tst|tr|d6| z| || | }|D]}|d!|d&| d$|d z}|d7|d)z|z | fz|d8|d9| d:| d*|jd$|d+|d,|d-|d|d z}|d;|d)z|z | fz|d<| z|d=| d>|jd$|d+|d,|d-|d|d8|d?| z|d@ddddS#1swxYwYdSANrrrr,rrrrrriz #!/bin/bash z MYPATH=`pwd` z1arg_source_password="" z1arg_target_password="" z if [ -z "$arg_source_password" ] && [ -z "$arg_target_password" ] ; then echo WARNING: Both source and target RDBMSes passwords are empty. You should edit this file to set them. exit 1 fi zif [ -f $MYPATH/%s ] ; then z rm $MYPATH/%s zfi zif [ -f /tmp/%s.txt ]; rzthen z rm /tmp/%s.txt 2> /dev/null z if [ $? -ne 0 ];then zb echo "File /tmp/%s.txt already exists. You should remove file before running this script." z exit 1 z fi z pushd /tmp z#echo [%d %%] Creating directory %s z mkdir %s z pushd %s rzecho "rz" >  rz" >> rz"echo [%d %%] Start dumping tables rz 2>> $MYPATH/zif [ $? -ne 0 ];then z9 echo Script has failed. See the log file for details. z exit 1 zcp /tmp/rz.csv zif [ -f /tmp/%s.sql ]; z rm /tmp/%s.sql rzecho [%d %%] Dumped table %s z touch %s z chmod +x %s z(echo [%d %%] Generated import script %s zpopd zzip -r z.zip z-echo [%d %%] Zipped all files to %s.zip file z rm -rf %s zcp z.zip $MYPATH 2>> $MYPATH/zdecho Now you can copy $MYPATH/%s.zip file to the target server, unzip it and run the import script. z*read -p "Press [Enter] key to continue..."rr roschmodrrr`rr[r$rrrrs r r|zDataCopyScriptLinux.generatec&kk/q /2 q /2 },h&-:]]M +t $ $Y B H[% ( ( ( GG% & & & GG$ % % % GGJ K K K GGJ K K K GG    GG3d6II J J J GG)D,?? @ @ @ GGFOOO  2U>5JJKKK!!!:U>=RRSSS3444}AFGUAVVWWW)*** """ GGN # # # GG:h=QQ R R R GGL8+ , , , GGL8+ , , ,!|H GGG*H*H*HJ^J^J^_ ` ` ` GGG+N+N+NPdPdPde f f f GGG9}+D+D+DFZFZFZ[ \ \ \ GG9X^n=\] ^ ^ ^ u u1P1PQVXg1h1h1h1hjnj}j}j}~0111TUUU &&&l,<==$GGG~9N9N9NPUVdPePePefgggGG6~9NNOOOGGH%%%GG1E.4IIJJJGGFOOO}/K/KES[_des_tSu/v/v/v/vyMyMyMNOOO#a<8HsN^<[]bcq]r;sstttt GGL#33 4 4 4-);<< < =Zk@l@l <*::;;; - D D_Vjly z z ) H Httt5E5E5EFGGGG!|H GG?8c>TbCbdtBuu v v v GGH    GGG888XXXtObObObc d d d GG, - - - GGP Q Q Q GGM " " " GGFOOO!|H GGDSVYgHgiqGrr s s s GGMH, - - - GGGHHHdFYFYFYZ [ [ [ GG, - - - GGP Q Q Q GGM " " " GGFOOO GGH    GG{GG H H H GG@ A A AsY BY BY BY BY BY BY BY BY BY BY BY BY BY BY BY BY BY B*X=Z44Z8;Z8Nrrr r rwrw-cBcBcBcBcBr rwceZdZdZdS)rxc  d}dt|z}|dd}|dd} d|z} d| z} d|d|} d |z} t|d 5}tj|d |d |d |d|d|d|d|jz|d|jz|d|D]}|d|dz|d|d|dz|d|d|dz|d|d|d|d|d|| fz|d| z|d| z|d z}|d!d"d#| d$|d!d%d&| d$|d!d'| zd&| d$|d(|d)z|z z|D]}||||d*|jd$|d+|d,|d-|dt|tr|d.|dd/|dd0|d1|dz|d|d2|dz|d|d!| |d3|dzd&| d$|d z}|d4|d)z|z |dfz|d5| zt|tst|tr|d6| z| || | }|D]}|d!|d&| d$|d z}|d7|d)z|z | fz|d8|d9| d:| d*|jd$|d+|d,|d-|d|d z}|d;|d)z|z | fz|d<| z|d=| d>|jd$|d+|d,|d-|d|d8|d?| z|d@ddddS#1swxYwYdSrrrs r r|zDataCopyScriptDarwin.generaterrNrrr r rxrxrr rxc&eZdZdZdZdZdZdS)DataCopyFactoryct|||_t||_t ||_dSr)rQrr^rsdatacopy_scriptrr)r rTrr^s r r zDataCopyFactory.__init__hsL'..|YGG-44Y??)00;;r c^d|vr |ddS||ddzdS)Nrr)index)r identifiers r _unquoteIdentifierz"DataCopyFactory._unquoteIdentifierms?j  ad# #j..s33a7:; ;r c,|D]}|d|d<|d|d<||d|d<||d|d<||d|d<||d|d<dS)Nrunquoted_source_schemarunquoted_source_tabler,r.)r)r rzr#s r unquoteIdentifiersz"DataCopyFactory.unquoteIdentifiersss S SE.3O.DE* +-2>-BE) *%)%<%rs, 88888888.55555,555r22222 222l22222222l--------*XXXXX{XXX W W W W W{ W W WGGGGGKGGG MMMMM[MMM????????&v!v!v!v!v!Nv!v!v!tdBdBdBdBdB.dBdBdBPdBdBdBdBdB>dBdBdBPssssssssssr