509318396 ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ U SW]@sdZddlmZddlZddlZddlTddlmZddlmZddlmZddlm Z dd lm Z dd lm Z Gd d d e Z d dZGddde ZGdddeZdZGdddedeZGdddeZGdddeZddZdS)a Lightweight schema migrations. NOTE: Currently tested with SQLite and Postgresql. MySQL may be missing some features. Example Usage ------------- Instantiate a migrator: # Postgres example: my_db = PostgresqlDatabase(...) migrator = PostgresqlMigrator(my_db) # SQLite example: my_db = SqliteDatabase('my_database.db') migrator = SqliteMigrator(my_db) Then you will use the `migrate` function to run various `Operation`s which are generated by the migrator: migrate( migrator.add_column('some_table', 'column_name', CharField(default='')) ) Migrations are not run inside a transaction, so if you wish the migration to run in a transaction you will need to wrap the call to `migrate` in a transaction block, e.g.: with my_db.transaction(): migrate(...) Supported Operations -------------------- Add new field(s) to an existing model: # Create your field instances. For non-null fields you must specify a # default value. pubdate_field = DateTimeField(null=True) comment_field = TextField(default='') # Run the migration, specifying the database table, field name and field. migrate( migrator.add_column('comment_tbl', 'pub_date', pubdate_field), migrator.add_column('comment_tbl', 'comment', comment_field), ) Renaming a field: # Specify the table, original name of the column, and its new name. migrate( migrator.rename_column('story', 'pub_date', 'publish_date'), migrator.rename_column('story', 'mod_date', 'modified_date'), ) Dropping a field: migrate( migrator.drop_column('story', 'some_old_field'), ) Making a field nullable or not nullable: # Note that when making a field not null that field must not have any # NULL values present. migrate( # Make `pub_date` allow NULL values. migrator.drop_not_null('story', 'pub_date'), # Prevent `modified_date` from containing NULL values. migrator.add_not_null('story', 'modified_date'), ) Renaming a table: migrate( migrator.rename_table('story', 'stories_tbl'), ) Adding an index: # Specify the table, column names, and whether the index should be # UNIQUE or not. migrate( # Create an index on the `pub_date` column. migrator.add_index('story', ('pub_date',), False), # Create a multi-column index on the `pub_date` and `status` fields. migrator.add_index('story', ('pub_date', 'status'), False), # Create a unique index on the category and title fields. migrator.add_index('story', ('category_id', 'title'), True), ) Dropping an index: # Specify the index name. migrate(migrator.drop_index('story', 'story_pub_date_status')) ) namedtupleN)*) CommaClause)EnclosedClauseEntity) Expression)Node)OPc@s8eZdZdZddZddZddZdd Zd d Zd S) Operationz/Encapsulate a single schema altering operation.cOs||_||_||_||_dSN)migratormethodargskwargs)selfr rrrrD/opt/alt/python38/lib64/python3.8/site-packages/playhouse/migrate.py__init__uszOperation.__init__cCs|jj}||Sr )r databasecompilerZ parse_node)rnoderrrr _parse_node{s zOperation._parse_nodecCs"||\}}|jj||dSr )rr r execute_sql)rrsqlZparamsrrrexecuteszOperation.executecCsPt|tr||n6t|tr*|n"t|ttfrL|D]}||q.inner) functoolswraps)r,r-rr+r operationsr0c@seZdZdZdZddZeddZeddZ edd Z d d Z ed d Z eddZ eddZed$ddZeddZddZeddZeddZeddZed%dd Zed!d"Zd#S)&SchemaMigratorFcCs ||_dSr )r)rrrrrrszSchemaMigrator.__init__cCs0t|trt|St|tr$t|St|SdSr )rZPostgresqlDatabasePostgresqlMigratorZ MySQLDatabase MySQLMigratorSqliteMigrator)clsrrrr from_databases   zSchemaMigrator.from_databasec CsJ|j}t|r|}ttdt|tdtt|tjt| |ddS)NZUPDATEZSETT)Zflat) defaultcallableClauseSQLrrr ZEQZParamZdb_value)rtable column_namefieldr7rrr apply_defaults zSchemaMigrator.apply_defaultcCsj|jd}|_||_|_|j|}||_tdt|td|g}t|t rb| | |t |S)NT ALTER TABLEz ADD COLUMN) nullname db_columnrrZfield_definitionr:rrForeignKeyFieldextendget_inline_fk_sqlr9)rr;r<r=Z field_nullZ field_clausepartsrrralter_add_columns  zSchemaMigrator.alter_add_columncCs$tdt|jjjtt|jjgS)N REFERENCES)r:r rel_model_metadb_tablerto_fieldrBrr=rrrrEs z SchemaMigrator.get_inline_fk_sqlcCstdSr NotImplementedError)rr;r<r=rrradd_foreign_key_constraintsz)SchemaMigrator.add_foreign_key_constraintcCs|js|jdkrtd|t|t}|r8|js8td||||g}|jsn|||||| ||g|r|j r| | |||j jj|jj|S)Nz!%s is not null but has no defaultz'Foreign keys must specify a `to_field`.)r@r7 ValueErrorrrCrLrGrDr> add_not_nullexplicit_create_foreign_keyappendrPrIrJrKrB)rr;r<r=is_foreign_key operationsrrr add_columns*      zSchemaMigrator.add_columncCstdSr rNrr;r<rrrdrop_foreign_key_constraintsz*SchemaMigrator.drop_foreign_key_constraintTcCsrtdt|tdt|g}|r.|tdt|}dd|j|D}||krj|jrj||||gS|SdS)Nr?z DROP COLUMNZCASCADEcSsg|] }|jqSrcolumn).0Z foreign_keyrrr sz.SchemaMigrator.drop_column..)r:rrTr9rget_foreign_keysexplicit_delete_foreign_keyrY)rr;r<cascadenodesZdrop_column_nodeZ fk_columnsrrr drop_columns   zSchemaMigrator.drop_columncCs*ttdt|tdt|tdt|S)Nr?z RENAME COLUMNTOr9r:r)rr;old_namenew_namerrr rename_columnszSchemaMigrator.rename_columncCstdt|tdt|gS)Nr?z ALTER COLUMN)r:rrr;r[rrr _alter_columns zSchemaMigrator._alter_columncCs"|||}|tdt|S)Nz SET NOT NULLrirTr:r9rr;r[rarrrrR"s zSchemaMigrator.add_not_nullcCs"|||}|tdt|S)Nz DROP NOT NULLrjrkrrr drop_not_null(s zSchemaMigrator.drop_not_nullcCsttdt|tdt|S)Nr?z RENAME TOrdrrerfrrr rename_table.s zSchemaMigrator.rename_tablecCsL|j}|rdnd}tt|t|||tdt|tdd|DS)NzCREATE UNIQUE INDEXz CREATE INDEXONcSsg|] }t|qSrrr\r[rrrr]?sz,SchemaMigrator.add_index..)rrr9r:r index_namer)rr;columnsuniquerZ statementrrr add_index6s  zSchemaMigrator.add_indexcCsttdt|S)N DROP INDEXrdrr;rqrrr drop_indexAszSchemaMigrator.drop_indexN)T)F)r&r'r(rSr_r classmethodr6r0r>rGrErPrWrYrbrgrirRrlrnrtrwrrrrr1s>     !       r1cs(eZdZddZefddZZS)r2cCs&d}|j||}dd|DS)Nai SELECT pg_attribute.attname FROM pg_index, pg_class, pg_attribute WHERE pg_class.oid = '%s'::regclass AND indrelid = pg_class.oid AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = any(pg_index.indkey) AND indisprimary; cSsg|] }|dqSrr)r\rowrrrr]Usz;PostgresqlMigrator._primary_key_columns..)rrfetchall)rZtblquerycursorrrr_primary_key_columnsIs z'PostgresqlMigrator._primary_key_columnsc s||}tt|}|j||ddg}t|dkrd||df}d}|j||f}t|rd||df} | |j|| dd|S)NT)r#z %s_%s_seqrz SELECT 1 FROM information_schema.sequences WHERE LOWER(sequence_name) = LOWER(%s) ) r~superr2rnlenrrboolfetchonerT) rrerfZpk_namesZ ParentClassrVZseq_namer|r}Z new_seq_name __class__rrrnWs     zPostgresqlMigrator.rename_table)r&r'r(r~r0rn __classcell__rrrrr2Hsr2)rA definitionr@pkr7extrac@s:eZdZeddZeddZeddZd dd ZdS) MySQLColumncCs |jdkS)NZPRIrrrrris_pkrszMySQLColumn.is_pkcCs |jdkS)NZUNIrrrrr is_uniquevszMySQLColumn.is_uniquecCs |jdkS)NZYES)r@rrrris_nullzszMySQLColumn.is_nullNcCs|dkr|j}|dkr|j}t|t|jg}|jrB|td|rV|tdn|td|jrx|td|jr|t|jt |S)NZUNIQUEZNULLNOT NULLz PRIMARY KEY) rrArr:rrrTrrr9)rr<rrFrrrr~s"zMySQLColumn.sql)NN)r&r'r(propertyrrrrrrrrrqs   rZ_Columnc@seZdZdZdZeddZddZeddZdd Z ed d Z d d Z eddZ eddZ eddZeddZdS)r3TcCsttdt|tdt|S)Nz RENAME TABLErcrdrmrrrrns zMySQLMigrator.rename_tablecCs@|jd|}|}|D]}t|}|j|kr|SqdS)Nz DESCRIBE %s;F)rrr{rrA)rr;r<r}Zrowsrzr[rrr_get_column_definitions  z$MySQLMigrator._get_column_definitionc CsRd|||f}ttdt|tdt|tdtt|tdt|tt| S)Nzfk_%s_%s_refs_%sr?zADD CONSTRAINTz FOREIGN KEYrH)r9r:rr)rr;r<ZrelZ rel_columnZ constraintrrrrPs  z(MySQLMigrator.add_foreign_key_constraintcCs6|jd||f}|}|s.td||f|dS)NzSELECT constraint_name FROM information_schema.key_column_usage WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s;z=Unable to find foreign key constraint for "%s" on table "%s".r)rrrAttributeError)rr;r<r}r!rrrget_foreign_key_constraintsz(MySQLMigrator.get_foreign_key_constraintc Cs&ttdt|tdt|||S)Nr?zDROP FOREIGN KEY)r9r:rrrXrrrrYs z)MySQLMigrator.drop_foreign_key_constraintcCsgSr rrMrrrrEszMySQLMigrator.get_inline_fk_sqlcCs.|||}ttdt|td|jddS)Nr?MODIFYFr)rr9r:rrrhrrrrRs  zMySQLMigrator.add_not_nullcCs<|||}|jrtdttdt|td|jddS)NzPrimary keys can not be nullr?rTr)rrrQr9r:rrrhrrrrls  zMySQLMigrator.drop_not_nullc Cstdd|j|D}||k}|||}ttdt|tdt||j|d}|r||}||||| |||j |j gS|SdS)Ncss|]}|j|fVqdSr rZ)r\Zfkrrr sz.MySQLMigrator.rename_column..r?ZCHANGE)r<) dictrr^rr9r:rrrYrPZ dest_tableZ dest_column) rr;rerfZ fk_objectsrUr[Z rename_clauseZ fk_metadatarrrrgs0     zMySQLMigrator.rename_columncCsttdt|tdt|S)Nrurordrvrrrrws zMySQLMigrator.drop_indexN)r&r'r(rSr_r0rnrrPrrYrErRrlrgrwrrrrr3s&       r3c@seZdZdZedZedZedZedej Z ddZ dd Z e d d Zd d Ze dddZe ddZe ddZe ddZdS)r4z SQLite supports a subset of ALTER TABLE queries, view the docs for the full details http://sqlite.org/lang_altertable.html z (.+?)\((.+)\)z(?:[^,(]|\([^)]*\))+z ["`']?([\w]+)z FOREIGN KEY\s+\("?([\w]+)"?\)\s+cCs |jd|}dd|jDS)Nzselect * from "%s" limit 1cSsg|] }|dqSryr)r\r"rrrr]sz4SqliteMigrator._get_column_names..)rrZ descriptionrr;resrrr_get_column_namessz SqliteMigrator._get_column_namescCs|jdd|g}|S)NzBselect name, sql from sqlite_master where type=? and LOWER(name)=?r;)rrlowerrrrrr_get_create_tables  z SqliteMigrator._get_create_tablec stddj|D}||kr6td||f|\}}j|}j|t dd|}j | \}}j |} dd| D} g} g} g} | D]}j| \}||kr |||}|r6| || |j| \}| |q| ||ds| || |qtt| | }||d d }shd d }n|krfd d }g}| D]F}j|}|dk r| d |kr||}|r||q|d}td|tj}| d||}d|}ttdt|td||fg}ttdt|tdd| Dtdtdd| Dtdt|}|||ttdt|| ||t!dd |D]R}||j"kr|t|j#n.r$|j#|}|dk r|t|q|S)Ncss|]}|jVqdSr )rArrprrrr sz0SqliteMigrator._update_column..z"Column "%s" does not exist on "%s"z\s+ cSsg|] }|qSrstripr\colrrrr]8sz1SqliteMigrator._update_column..)ZforeignZprimarycSs|Sr r column_defrrrSz/SqliteMigrator._update_column..cSsdSr rrrrrrVrcsjd|S)NzFOREIGN KEY ("%s") )fk_resubr new_columnrrrrYsrZ__tmp__z ("?)%s("?)z\1%s\2, zDROP TABLE IF EXISTSz%s (%s)z INSERT INTOcSsg|] }t|qSrrrrrrr]vsZSELECTcSsg|] }t|qSrrrrrrr]xsZFROMz DROP TABLEcSs|jSr )r)idxrrrrr)%setrZ get_columnsrrQrZ get_indexesr^rer column_researchgroupscolumn_split_refindallcolumn_name_rematchrT startswithrzipgetrcompileIjoinr9r:rrrrrnfilterrrr _fix_index)rr;column_to_updater,rrZ create_tableZindexesZ raw_createZ raw_columnsZ split_columnsZ column_defsZnew_column_defsZnew_column_namesZoriginal_column_namesrr<Znew_column_defZoriginal_to_newZ fk_filter_fnZcleaned_columnsrZ temp_tableZrgxZcreateZqueriesZpopulate_tableindexrrrr_update_columns                      zSqliteMigrator._update_columnc Cs||}t|dkr"|||S|dd\}}t||dkrXd||||fS|dddd}dd |D}g}|D]2} td || rt| t|d} || qd |d d d|DfS)N(rz%s(%s)r,cSsg|]}|dqS)z"`[]' r)r\partrrrr]sz-SqliteMigrator._fix_index..z%s(?:['"`\]]?\s|$)z%s(%s)rcss|]}d|VqdS)z"%s"Nr)r\crrrrsz,SqliteMigrator._fix_index..) splitrreplacersplitrrZ new_columnerTr) rrrrrFZlhsZrhsrrZcleanr[rrrrs    zSqliteMigrator._fix_indexTcCs|||ddS)NcSsdSr r)abrrrrrz,SqliteMigrator.drop_column..r)rr;r<r`rrrrbszSqliteMigrator.drop_columncsfdd}||||S)Ncs ||Sr rr<rrfrr_renamesz-SqliteMigrator.rename_column.._renamer)rr;rerfrrrrrgs zSqliteMigrator.rename_columncCsdd}||||S)NcSs|dS)Nz NOT NULLrrrrr _add_not_nullsz2SqliteMigrator.add_not_null.._add_not_nullr)rr;r[rrrrrRszSqliteMigrator.add_not_nullcCsdd}||||S)NcSs |ddS)Nrrrrrr_drop_not_nullsz4SqliteMigrator.drop_not_null.._drop_not_nullr)rr;r[rrrrrlszSqliteMigrator.drop_not_nullN)T)r&r'r(r)rrrrrrrrrr0rrrbrgrRrlrrrrr4 s$    p   r4cOs|D] }|qdSr )r)rVrr0rrrmigratesr)r) collectionsrr.rZpeeweerrrrr r objectr r0r1r2Z_column_attributesrr3r4rrrrrs*e         -'"v=