U 4Je=x@s<dZddlZddlZddlZddlZddlZddlmZddl m Z ddl m Z ddl mZddlmZmZddlmZddlZddlZdd lmZdd lmZdd lmZdd lmZdd lmZmZddl m!Z!m"Z"m#Z#m$Z$m%Z%m&Z&m'Z'ddl(m)Z)m*Z*m+Z+m,Z,m-Z-m.Z.m/Z/m0Z0m1Z1ddl2m3Z3m4Z4Gddde3Z5dS)z.Use to transfer an SQLite 3 database to MySQL.N) timedelta)Decimal)chain)ceil)isfilerealpath)stdout) CharacterSet) __version__) errorcode)version)tqdmtrange) adapt_decimaladapt_timedelta check_sqlite_table_xinfo_support convert_dateconvert_decimalconvert_timedeltaunicase_compare) MYSQL_BLOB_COLUMN_TYPESMYSQL_COLUMN_TYPES"MYSQL_COLUMN_TYPES_WITHOUT_DEFAULTMYSQL_INSERT_METHODMYSQL_TEXT_COLUMN_TYPES!MYSQL_TEXT_COLUMN_TYPES_WITH_JSONcheck_mysql_fulltext_supportcheck_mysql_json_supportsafe_identifier_length)SQLite3toMySQLAttributesSQLite3toMySQLParamsc @seZdZUdZedZeje e d<edZ eje e d<e eZe je d<ejeddd Zed6ejeje d feejd ddZe dddZe dddZe edddZd dddZee ejej e dddZ!e e dddZ"ed7e ejeje e#e$fe dd d!Z%d8e ed d"d#d$Z&e d d%d&d'Z'e d d%d(d)Z(d9e e ej)e ej*fe ej+ej)e ej*fd+fe#d d,d-d.Z,e d d%d/d0Z-d:e e#d d1d2d3Z.d dd4d5Z/d S);SQLite3toMySQLz9Use this class to transfer an SQLite 3 database to MySQL.z^[^(]+COLUMN_PATTERNz\(\d+\)$COLUMN_LENGTH_PATTERNMYSQL_CONNECTOR_VERSION)kwargsc Ks*|ddkrtdn0tt|ds4tdntt|d|_|ddk rht|d|_ntdt|dpd|_|dpd |_ |d pd |_ |d pt |_ t |j d kp|dpd|_|dpd|_|dpd|_|dpd|_|j|dpd|jd|_|dp2d|_t|dpFd|_|jtkrbd|_|dppd|_t|dpd|_t|dpd|_t|dpd|_|jtkrd|_|dpd |_|d!pt|j d |_!|d!s&|j!d"kr&d#|_!|d$p4d|_"|d%pFd|_#|d&pXd|_$t%&t't(t%)d't*t%&t+t,t%)d(t-t%)d)t.t%j/t|jt%j0d*|_1t%j2|j1_3|j14d+t5|j16|_7|8|_9t:|j9|_;zt|ts       zSQLite3toMySQL._setup_logger)rec CszD|jd|j}|r*t|dWS|jdtjdWn:t tjjfk r~}z|jd|W5d}~XYnXdS)NzSHOW VARIABLES LIKE 'version'rz(MySQL failed checking for InnoDB versionz,MySQL failed checking for InnoDB version: %s) rXexecutefetchoner6r?r]rSrTrZ IndexError)r`rowrarbrbrcr^s   z!SQLite3toMySQL._get_mysql_versionc Cs^z |jdt|jdWSttjfk rX}z|jd|W5d}~XYnXdS)NzSELECT sqlite_version()rz-SQLite failed checking for InnoDB version: %s) rPrxr6ryrzrMrZr?r]r`rarbrbrcrQs z"SQLite3toMySQL._get_sqlite_version)tablerecCsBz$|jd|d|jWdStjk r<YdSXdS)NzSELECT rowid FROM "z " LIMIT 1TF)rPrxfetchallrMZOperationalError)r`r}rbrbrc_sqlite_table_has_rowids  z&SQLite3toMySQL._sqlite_table_has_rowidc CszX|jd|jd|jd|jd|j|j|j|j_|jj dd|_Wn:t j j k r}z|j d|j|W5d}~XYnXdS)Nz0 CREATE DATABASE IF NOT EXISTS `z(` DEFAULT CHARACTER SET z! DEFAULT COLLATE z Tr2z$MySQL failed creating databse %s: %s)rXrxr@rGrIcloserVcommitrYrOrSrTrZr?r]r|rbrbrcr\s*   zSQLite3toMySQL._create_database) column_typerecCs|j|SN)r#matchstrip)rurrbrbrc_valid_column_typesz!SQLite3toMySQL._valid_column_typecCst|}||}|std|d}|dkr:|jS|dkrPd||S|dkr|jtkrh|jS||}|s||jS||j}|r|d|S|dkrdS|d krd ||d S|d kr|jS|d kr8||}|s|jS||j}|r8|j dr&|d|d S|d|S|dkrRd ||dS|dkr`dS|t krp|jS|S)z@This could be optimized even further, however is seems adequate.zInvalid column_type!r>r-STRINGCLOB>NATIVE CHARACTERNCHAR CHARACTERCHAR>NVARCHARVARYING CHARACTERVARCHARzDOUBLE PRECISIONZDOUBLEzUNSIGNED BIG INTBIGINTz UNSIGNED>INT1INT2>INTINTEGERZUNSIGNED>INT64NUMERICZBOOLEANz TINYINT(1)) rArr5grouprF_column_type_lengthrErrDendswithr)r`rZfull_column_typerZ data_typelengthrbrbrc$_translate_type_from_sqlite_to_mysqlsP          z3SQLite3toMySQL._translate_type_from_sqlite_to_mysql)rdefaultrecCs.|j|}|r|dS|r*d|dSdS)Nr())r$searchr)rurrsuffixrbrbrcr%s    z"SQLite3toMySQL._column_type_length) table_nametransfer_rowidrec Cs>g}dt|d}|r |d7}|jr<|jd|dn|jd|d|j}ttdd|Dd k}|D]}t|}t|d } ||d } d |kr|d d krqx|d dko| do| } |dj | | |ds|d rdnd| rdnd|dr | t kr | s d|dndd7}|d dkrx| dd} | t t ksZ| drj|| d| d<|| qx|d}t|dkr|dj ddd|Dd 7}|r|d!t|d"7}|d#|jd$|j7}z|j||jWn>tjjk r8} z|jd%t|| W5d} ~ XYnXdS)&NzCREATE TABLE IF NOT EXISTS `z` ( z `rowid` BIGINT NOT NULL, zPRAGMA table_xinfo("")PRAGMA table_info("css"|]}t|ddkrdVqdS)pkrTNdict.0r{rbrbrc <sz/SQLite3toMySQL._create_table..rnametypehiddenrr)rrz7 `{name}` {type} {notnull} {default} {auto_increment}, notnullzNOT NULLZNULLZAUTO_INCREMENTrZ dflt_valuezDEFAULT )rrrauto_incrementrcolumnr)rrr, z, PRIMARY KEY ({columns})css|]}djf|VqdS)`{column}`{length}N)format)r primary_keyrbrbrcrgs)columnsz, CONSTRAINT `z_rowid` UNIQUE (`rowid`)z! ) ENGINE=InnoDB DEFAULT CHARSET=z COLLATE=z"MySQL failed creating table %s: %s)rrRrPrxr~r:r8rr startswithrrrrrappendrstripjoinrGrIrXrVrrSrTrZr?r])r`rrZ primary_keyssqlZrowsZcompound_primary_keyr{rZmysql_safe_namerrrrarbrbrc _create_table.sv       zSQLite3toMySQL._create_table)rrecCsX|jd|jt|ft|jdkrT|jdt||jdt|ddS)Nz SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = %s AND `TABLE_NAME` = %s LIMIT 1 rzTruncating table %szTRUNCATE TABLE ``)rXrxr@rr:r~r?info)r`rrbrbrc_truncate_tablezs  zSQLite3toMySQL._truncate_tablec sF|jd|di|jD]}t|}|d|d<q"|jd|dtdd|jD}|D]}|dd krqp|jd |ddtd d|jD}t|d d krdnd}tfdd|Dr(|jr|jrd}d dd|D}nd fdd|D}n|g} |D]h} d} | d t krTd} n$|j | d} | rx| d} | dt| dd| q0d | }z|j|||||dWqptjjk r>} z^| jtjkr,|dkr,|j|t|d d krdnd|d fdd|D|dnW5d} ~ XYqpXqpdS)NrrrrzPRAGMA index_list("css|]}t|VqdSrrrrbrbrcrsz.SQLite3toMySQL._add_indices..originrzPRAGMA index_info("css|]}t|VqdSrrrrbrbrcrsuniquerZUNIQUEZINDEXc3s"|]}|dtkVqdSrN)rArr index_infoZ table_columnsrbrcrsZFULLTEXT,css"|]}dt|ddVqdS)rrNrrrbrbrcrsrc3s<|]4}djt|d|dtkr,dnddVqdSrr(255)rrNrrrArrrrbrcrs rrrr)r index_typeindex index_columns index_infosc3s<|]4}djt|d|dtkr,dnddVqdSrrrrrbrcrs )rPrxr~rr8intanyrKr_rrArr$rrrr _add_indexrSrTrZr[r ER_BAD_FT_COLUMN)r`rr{rindicesrrrrZ column_listrZ index_lengthrrarbrrc _add_indicessp         "   zSQLite3toMySQL._add_indicesr.)rrrrrindex_iterationrec Csdjt|||dkr t|dnt|dddd||d}zV|jdt|d d kr^d nd d dd|Dt||j||j Wnt j j k r}z|j tjkr*|js |j||||||d d|jdt|dt|t|d|d n|jdt|dt|n^|j tjkr`|jdd dd|Dt|n(|jdd dd|Dt||W5d}~XYnXdS)Nz\ ALTER TABLE `{table}` ADD {index_type} `{name}`({columns}) rr<) max_length_)r}rrrz$Adding %s to column "%s" in table %srrz unique indexrrcss|]}t|dVqdSrrrrbrbrcrsz,SQLite3toMySQL._add_index..)rrrrrrzMDuplicate key "%s" in table %s detected! Trying to create new key "%s_%s" ...z(Ignoring duplicate key "%s" in table %s!zVFailed adding FULLTEXT index to column "%s" in table %s. Retrying without FULLTEXT ...css|]}t|dVqdSrrrrbrbrcrsz8MySQL failed adding index to column "%s" in table %s: %scss|]}t|dVqdSrrrrbrbrcr s)rrr?rrrrXrxrVrrSrTrZr[r ZER_DUP_KEYNAMErJrwarningrr]) r`rrrrrrrrarbrbrcrsl      zSQLite3toMySQL._add_indexc CsP|jd|d|jD]*}t|}dj|d|dt|t|dt|dt|d|d d kr||d nd |d d kr|d nd d }zJ|jdt|t|dt|dt|d|j ||j Wqt j jk rH}z8|jdt|t|dt|dt|d|W5d}~XYqXqdS)NzPRAGMA foreign_key_list("ra ALTER TABLE `{table}` ADD CONSTRAINT `{table}_FK_{id}_{seq}` FOREIGN KEY (`{column}`) REFERENCES `{ref_table}`(`{ref_column}`) ON DELETE {on_delete} ON UPDATE {on_update} idseqfromr}to on_deletez SET DEFAULTz NO ACTION on_update)rrr}rZ ref_tableZ ref_columnrrz-Adding foreign key to %s.%s referencing %s.%sz>MySQL failed adding foreign key to %s.%s referencing %s.%s: %s)rPrxr~rrrrAr?rrXrVrrSrTrZr])r`rr{Z foreign_keyrrarbrbrc_add_foreign_keys&sJ          z SQLite3toMySQL._add_foreign_keys)r total_recordsrec Cs|jdk r\|jdkr\tdtt||j|jdD]&}|j|dd|j|jDq2n*|j|ddt |j ||jdD|j dS)Nr)disablecss|]}t|VqdSrr8rrbrbrcr[sz6SQLite3toMySQL._transfer_table_data..css|]}t|VqdSrrrrbrbrcr`s)totalr) r<rrrr=rXZ executemanyrPZ fetchmanyr r~rVr)r`rrrrbrbrc_transfer_table_dataVs"" z#SQLite3toMySQL._transfer_table_datac Cst|jdkr8|jddt|jdd|jn |jdzBz"|jd|jD]}t|}|jo| |d }|j |d |d |j r| |d |jd |d d t t|jd }|dkrF|jd|d |jdj|rdnd|d ddd|jjD}|jdkrdjt|d dt|dj|dt|ddt|djttdd|Dd}nPdj|jdkrdndt|d dt|dj|dt|dd}z|j||d WnBtjjk rD}z|jd!t|d |W5d"}~XYnX||d |jsb| |d qbWnt!k rYnXW5|jdX|jd#d"S)$z@The primary and only method with which we transfer all the data.rz SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' AND name IN(z?, z ,z) z SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' zSET FOREIGN_KEY_CHECKS=1zSET FOREIGN_KEY_CHECKS=0r)rz'SELECT COUNT(*) AS total_records FROM ""rzTransferring table %sz$SELECT {rowid} * FROM "{table_name}"zrowid as "rowid",r)ZrowidrcSsg|]}t|dqS)rrrrrbrbrc sz+SQLite3toMySQL.transfer..ZUPDATEz INSERT INTO `{table}` ({fields}) VALUES ({placeholders}) AS `__new__` ON DUPLICATE KEY UPDATE {field_updates} z`{}`, z%s, z`{}`=`__new__`.`{}`, css|]}||fVqdSrrbrrbrbrcrsz*SQLite3toMySQL.transfer..)r}fields placeholdersZ field_updatesz INSERT {ignore} INTO `{table}` ({fields}) VALUES ({placeholders}) r,)ignorer}rr)rrz6MySQL transfer failed inserting data into table %s: %sNzDone!)"r:r9rPrxrrXr~rrLrrrCrrryr?rr descriptionrBrArlistr from_iterablerrSrTrZr]rr;r Exception)r`r{r}rrrrrarbrbrcr+ks          zSQLite3toMySQL.transfer)NF)N)F)r)r)0rn __module__ __qualname____doc__recompiler#tPatternr6__annotations__r$r parsemysql_connector_version_stringr%VersiontxZUnpackr!rd classmethodOptionalUnionboolrkLoggerr>r^rQrr\MatchrrrfloatrrrrDictAnyTuplerrrr+rbrbrbrcr"/sL r ,(L\ H0r")6rrkosrrMtypingrdatetimerdecimalr itertoolsrmathros.pathrrsysrZmysql.connectorrSZtyping_extensionsrr r rr packagingr r rZsqlite3_to_mysql.sqlite_utilsrrrrrrrZ mysql_utilsrrrrrrrrrtypesr r!r"rbrbrbrcs,         $ ,