o /h@sRUdZddlZddlZddlZddlmZmZmZmZddl m Z ddl m Z m Z ddlmZddlmZddlmZmZmZdd lmZdd lmZmZdd lmZdd lmZed ddZee d<e!e"ffddZ#ddZ$GdddZ%Gddde%Z&Gddde%Z'Gddde%Z(Gddde%Z)e)e)e)e'e(e&dZ*Gd d!d!e Z+dS)"ae sqldiff.py - Prints the (approximated) difference between models and database TODO: - better support for relations - better support for constraints (mainly postgresql?) - support for table spaces with postgresql - when a table is not managed (meta.managed==False) then only do a one-way sqldiff ? show differences from db->table but not the other way around since it's not managed. KNOWN ISSUES: - MySQL has by far the most problems with introspection. Please be carefull when using MySQL with sqldiff. - Booleans are reported back as Integers, so there's no way to know if there was a real change. - Varchar sizes are reported back without unicode support so their size may change in comparison to the real length of the varchar. - Some of the 'fixes' to counter these problems might create false positives or false negatives. N)DictUnionCallableOptional)apps) BaseCommand CommandError) OutputWrapper)no_style) connection transactionmodels)UniqueConstraint) AutoField IntegerField)normalize_together) signalcommand_orderT)nullORDERING_FIELDcCst|}t|}d}|t|kr@t|||r6||s%|||d8}n|||||d<t|||s|d7}|t|ks||S)Nr)typelistlen isinstancepop)lstltypesltypeir h/var/www/html/env_mimamsha/lib/python3.10/site-packages/django_extensions/management/commands/sqldiff.pyflatten*s    r"cCsZg}|jr|jD] }|t|jq|S|jD]}|jtd}|dur%q||q|SNr ) proxyparentsextendall_local_fields_meta local_fieldsdb_typer append)meta all_fieldsparentfcol_typer r r!r(:s   r(c@seZdZiZdgZgdZddddddd d d d d dddddZddZddZddZ ddZ ddZ ddZ ddZ ddZddZddZddZddZddZd dZd!Zd!Zd"Zd#d$Zd%d&Zd'd(Zd)d*Zd+d,Zd-d.Zd/d0Zd1d2Zd3d4Z d5d6Z! " " "ded7d8Z"dfd9d:Z#d;d<Z$d=d>Z%d?d@Z&dAdBZ'dCdDZ(dEdFZ)dGdHZ* "dgdIdJZ+dKdLZ,dMdNZ-dOdPZ.dQdRZ/dSdTZ0dgdUdVZ1 "dgdWdXZ2dYdZZ3d[d\Z4d]d^Z5e6fd_d`Z7dadbZ8dcddZ9d"S)hSQLDiffdjango_migrationserrorcommenttable-missing-in-dbtable-missing-in-modelfield-missing-in-dbfield-missing-in-modelfkey-missing-in-dbzfkey-missing-in-modelindex-missing-in-dbindex-missing-in-modelunique-missing-in-dbunique-missing-in-modelfield-type-differfield-parameter-differnotnull-differz error: %(0)szcomment: %(0)sz!table '%(0)s' missing in databaseztable '%(0)s' missing in modelsz6field '%(1)s' defined in model but missing in databasez6field '%(1)s' defined in database but missing in modelzBfield '%(1)s' FOREIGN KEY defined in model but missing in databasezBfield '%(1)s' FOREIGN KEY defined in database but missing in modelzJfield '%(1)s' INDEX named '%(2)s' defined in model but missing in databasezCfield '%(1)s' INDEX defined in database schema but missing in modelzKfield '%(1)s' UNIQUE named '%(2)s' defined in model but missing in databasezDfield '%(1)s' UNIQUE defined in database schema but missing in modelz9field '%(1)s' not of same type: db='%(3)s', model='%(2)s'z:field '%(1)s' parameters differ: db='%(3)s', model='%(2)s'z?field '%(1)s' null constraint should be '%(2)s' in the databasec sZdd||dd||ddfddt|d dDfS) N%s %s %s %s %s; ALTER TABLEr ADD COLUMNr c30|]\}}|dkr|n|VqdSrN SQL_COLTYPE SQL_KEYWORD.0rastyler r! w  #SQLDiff...)rK SQL_TABLE SQL_FIELDjoin enumerateselfrPqnargsr rOr!rszSQLDiff.c Cs8d|d|||d|d|||dfS)Nz %s %s %s %s;rDrz DROP COLUMNrrKrUrVrYr r r!r]|s c sdd||dd||ddfddt|d dDd ||d ||d tjf S) Nz%s %s %s %s %s %s %s (%s)%s;rDrrErrFc3rGrHrIrLrOr r!rQrRrS REFERENCESrT)rKrUrVrWrXr opsdeferrable_sqlrYr rOr!r]sc sdd|d||d|d||d|dfdd|d D||d fS) Nz%s %s %s %s (%s%s);z CREATE INDEXrTONr, c3|]}|VqdSNr rMer[r r!rQrSrrarKrUrVrWrYr rjr!r]s  cCs d|d|||dfS)Nz%s %s;z DROP INDEXrrKrUrYr r r!r]sc s`d|d||d|d||d|d|dfdd |d DfS) Nz%s %s %s %s %s (%s);rDrzADD CONSTRAINTrTUNIQUErec3rfrgr rhrjr r!rQrkrSrrlrYr rjr!r]s c Cs@d|d|||d|d|d|||dfS)NrCrDrDROP CONSTRAINTrrmrYr r r!r]sc CDd|d|||d|d|||d||dfSNrCrDrMODIFYrrTrKrUrVrJrYr r r!r] c CrqrrrtrYr r r!r]ruc CLd|d|||d|d|||d||d|dfS)N%s %s %s %s %s %s;rDrrsrrTNOT NULLr^rYr r r!r] cC|d||dS)Nz -- Error: %sr)NOTICEERRORrYr r r!r]cCrz)Nz-- Comment: %sr)r{rUrYr r r!r]r}cC|d|dS)Nz-- Table missing: %srr{rYr r r!r] cCr~)Nz-- Model missing for table: %srrrYr r r!r]rFNcCsd|_||_||_|d|_||_||_tj|_g|_i|_ t |_ i|_ t |_ |j|j|j|j|j|j|j|j|j|j|j|j|j|j|jd|_dS)N dense_outputr4)has_differences app_modelsoptionsdensestdoutstderrr introspection differencesunknown_db_fieldsset new_db_fieldsrunsigned SQL_ERROR SQL_COMMENTSQL_TABLE_MISSING_IN_DBSQL_TABLE_MISSING_IN_MODELSQL_FIELD_MISSING_IN_DBSQL_FIELD_MISSING_IN_MODELSQL_FKEY_MISSING_IN_DBSQL_INDEX_MISSING_IN_DBSQL_INDEX_MISSING_IN_MODELSQL_UNIQUE_MISSING_IN_DBSQL_UNIQUE_MISSING_IN_MODELSQL_FIELD_TYPE_DIFFERSQL_FIELD_PARAMETER_DIFFERSQL_NOTNULL_DIFFERDIFF_SQL)rZrrrrr r r!__init__s8  zSQLDiff.__init__cCs^t|_|jj|jdd|_dd|j|jD|_|jr$| |j r-| dSdS)N only_existing)rcSg|]}|jqSr name)rM table_infor r r! sz SQLDiff.load..) r cursorrdjango_table_namesr django_tablesget_table_list db_tablescan_detect_notnull_differ load_nullcan_detect_unsigned_differ load_unsignedrZr r r!loads   z SQLDiff.loadcCtd)Nzcload_null functions must be implemented if diff backend has 'can_detect_notnull_differ' set to TrueNotImplementedErrorrr r r!r zSQLDiff.load_nullcCr)Nzgload_unsigned function must be implemented if diff backend has 'can_detect_unsigned_differ' set to Truerrr r r!rrzSQLDiff.load_unsignedcCs|j||gfdSrg)rr,)rZ app_label model_namer r r!add_app_model_markerszSQLDiff.add_app_model_markercGs.||jvs Jd|jdd||fdS)NzUnknown difference type) DIFF_TYPESrr,)rZ diff_typer\r r r!add_differenceszSQLDiff.add_differencecCs|jSrg)DATA_TYPES_REVERSE_OVERRIDErr r r!get_data_types_reverse_override#sz'SQLDiff.get_data_types_reverse_overridecCs|Srgr rZ field_namesr r r!format_field_names'zSQLDiff.format_field_namesc Cspt}|||dd|jD}||}g}|D]}g}t||D]}||q&|t|q|S)z Execute query and return a dict sql_to_dict(query, param) -> list of dicts code from snippet at https://www.djangosnippets.org/snippets/1383/ cSg|]}|dqSrr )rMrr r r!r4z'SQLDiff.sql_to_dict..) r rexecute descriptionrfetchallzipr,dict) rZqueryparamr fieldnamesresultrowrowsetfieldr r r! sql_to_dict*s    zSQLDiff.sql_to_dictcCs |jtdSr#)r+r )rZrr r r!get_field_model_type>s zSQLDiff.get_field_model_typecCiSrgr )rZcurrent_kwargsrr table_name reverse_typer r r!get_field_db_type_kwargsAsz SQLDiff.get_field_db_type_kwargsc CsV|d}|}||vr||}nBz |j||}Wn8tyR||}|sP|jddd|ddf}||jvrMd|j|<|dd|d|fYdSYnwt|rZ|}i}t |t rl| |d|d}|d kr||r|t |d dd kr|d }t |t r| |d|d}|d kr|dr|d|d<|dkr|d|d<|drt|dp|d|d<|drd|d<|dvrd|d<|rt |ddrd|d<|dkr|d} |j|| \}} | r| | d|}||||||} | | ||} | d!i|jtd} |j}|sd}|||jf|jvr)|j| vr)d | |jf} | S)"NrrrTr6z)Unknown database type for field '%s' (%s)rkwargsri2B geom_typePOINTz.django.contrib.gis.db.models.fields.PointField CharFieldra max_length DecimalFieldr_ max_digitsdecimal_placesTblank) TextFieldrr geographyF GeometryFieldz&django.contrib.gis.db.models.fields.%sr$public%s %sr )rrget_field_typeKeyErrorget_field_db_type_lookuprrrcallablerrupdategetattrtupleabsget_geometry_typerget_field_classr+r db_tablespacecolumnrunsigned_suffix)rZrrr type_coderrkeyrgeo_col geo_params extra_kwargs field_class field_db_type tablespacer r r!get_field_db_typeKs               zSQLDiff.get_field_db_typecCdSrgr )rZrr r r!rrz SQLDiff.get_field_db_type_lookupcCs6d|vr|dd\}}t|}t||Stt|S)N.r)rsplit importlib import_modulerr )rZ class_path module_path package_namemoduler r r!rs    zSQLDiff.get_field_classcCs2|j}|dkr d}|jp|j}|j|||fdS)Nrfixme)r db_columnattnamerget)rZrrrrr r r!get_field_db_nullables  zSQLDiff.get_field_db_nullablecCs,|r|dkr|ddddS|S)Nzdouble precisionrFr()splitlower)rZ field_typer r r!strip_parameterss zSQLDiff.strip_parameterscCsDg}t|dr|tt|j7}|jD]}||jq|||S)Nindex_together)hasattrrrrindexesr,fieldsexpand_together)rZr-indexes_normalizedidxr r r!get_index_togethers    zSQLDiff.get_index_togethercCs<tt|j}|jD] }t|tr||jq |||Srg) rrunique_together constraintsrrr,rr)rZr-unique_normalized constraintr r r!get_unique_togethers     zSQLDiff.get_unique_togethercs2g}t|D]}|tfdd|Dq|S)Nc3s|] }|jVqdSrg) get_fieldrrMrr-r r!rQsz*SQLDiff.expand_together..)rr,r)rZtogetherr- new_togetherrr rr!rs  zSQLDiff.expand_togetherc\tt}t|D]q}|r|j|vrq |jrz|jrz|jp|j|id}|s9|r9tfdd| D}|vr@|r@q | |g} | d|g| d|j td} | dri| d|g| d d | d rz| d|g| d d q ||} td d|D} | D]} | | vrq|r| |vrq| || } | d|| | dqdS)Nuniquec3*|]\}}g|dkr|dVqdScolumnsr#Nr rMcontraint_namerrr r!rQz4SQLDiff.find_unique_missing_in_db..r>_uniqr$varcharr<_like varchar_pattern_opstext text_pattern_opscS$g|]}|dr|ds|dqS)r#indexr&r rMvr r r!rz5SQLDiff.find_unique_missing_in_db..r SchemaEditorClassr(rr#managedrr anyitems_create_index_namerr+ startswithrrvaluesrZr- table_indexestable_constraintsr skip_list schema_editorrdb_field_unique index_namer+rdb_unique_columnsunique_columnsr r)r!find_unique_missing_in_dbsf             z!SQLDiff.find_unique_missing_in_dbc Cstddt|D}||}|D]7\}}|dsq|dr"q|d} t| dkr=|| d} | dur8n | jr.r#r2r&rrr?) rr(rr:rr r#rr) rZr-r?r@rrrconstraint_namerr&rr r r!find_unique_missing_in_models&   z$SQLDiff.find_unique_missing_in_modelcCs*tt}t|D]H}|jrQ|jp|j}||vrQ|||g}|d||g|d|jtd} | dr@|d||g|dd| drQ|d||g|ddq | |} t d d | D} | D]} | | vrkqd||| }|d|| |d dqd|j D]} | j|vr|d|| j| jdqdS) Nr<rr$r,r-r.r/r0cSr1)r2r#r&r r3r r r!rUr5z4SQLDiff.find_index_missing_in_db.._idx)r r7r(db_indexrrr;rr+r<rrr=rrr)rZr-r?r@rrBrrrDr+rdb_index_togetherr&r2r r r!find_index_missing_in_db5s^           z SQLDiff.find_index_missing_in_dbc CsZtddt|D}dd|jD}||}|D]\}} ||vr%q| dr.| ds.q| d} || d} | drA| dsE| durFn]t| dkr| d rT| jrTq| d rbt| t j rb| j rbq| drj| j rjq| dr}| d d kr}| d r}| j r}q| dr| j rq| dr| jtdrqt| ddrqn | drt| |vrq|d||qdS)NcSrHr rIrr r r!rlrJz7SQLDiff.find_index_missing_in_model..cSrr rrMrr r r!rmr#r2r&rr primary_key foreign_keyrrorderscheckr$ spatial_indexFr=)rr(rrr:r rrSrr ForeignKey db_constraintr#rNdb_checkr rrr) rZr-r?r@rrmeta_index_namesrrKrr&rr r r!find_index_missing_in_modelisV     z#SQLDiff.find_index_missing_in_modelcCs,|D]}|d|vr|d||dqdS)Nrr:)r)rZfieldmaptable_descriptionrrr r r!find_field_missing_in_models  z#SQLDiff.find_field_missing_in_modelc Csdd|D}|D]_\}}||vrjg}|jr/||jjjj|jjj|jjjgd}nd}| |j t d|j drO| rO| d|||jsW| d|j|||g|R|j||fq dS) NcSrrr rMrr r r!rrz4SQLDiff.find_field_missing_in_db..r;r9r$include_defaultsz DEFAULT %srx)r: remote_fieldr'modelr)db_tabler field_namerr,r+r r has_defaultget_prep_value get_defaultrrradd) rZr]r^r db_fieldsrer field_outputopr r r!find_field_missing_in_dbs4   z SQLDiff.find_field_missing_in_dbc Cstdd|D}t|D]>}|j|vrq ||j}||}||||} |r1||||| \}} || ||ksK| |fdvrK|d||j|| q dS)NcSg|]}|d|fqSrr r`r r r!rz2SQLDiff.find_field_type_differ..>serialinteger bigserialbigintr@)rr(rrrrr) rZr-r^rfuncrjrr model_typer+r r r!find_field_type_differs$       zSQLDiff.find_field_type_differc Cstdd|D}t|D]b}|j|vrq ||j}||}||||} |||| ks1q |r<||||| \}} |jtdd} d| vr[| dd\} } | d d} nd} || kre| | kso| d ||j|| q dS) NcSrnrr r`r r r!rroz7SQLDiff.find_field_parameter_differ..r$rVz CHECKrr )rA) rr(rrrr db_parametersr r striplstriprstripr) rZr-r^rrvrjrrrwr+ model_checkrZr r r!find_field_parameter_differs4    z#SQLDiff.find_field_parameter_differcCsl|jsdSt|D]*}|jp|j}||f|jvrq |||}|j|kr3|jr)dp*d}|d|||q dS)NroSETrB)rr(rrrr rr)rZr-r^rrrractionr r r!find_field_notnull_differs    z!SQLDiff.find_field_notnull_differcCrrgr )rZrrrr r r!get_constraintsrzSQLDiff.get_constraintsc Cs@|jdr!|dd|jD]}||jvr ||jvr |d|qd}|jD]}|j}|j}|j }|jds:|j r:q&||krE|||j ||jvrQ|d|q&t |j dr`|j |j|}n ||j||j }tddt|D}|jr{t|d<z |j |j|} Wn"ty} z|d d t| tWYd} ~ q&d} ~ wwi} |D] \} } | d }t|d kr| d | d| d| d| |d<q||| ||||| ||||| || || ||!|| |||"|| |||#|| ||$|| ||%|| |q&t&dd|j'D|_(dS)Nall_applicationsr8include_proxy_modelsr7rcSsg|] }|jp ||fqSr )r get_attnamerr r r!r0sz,SQLDiff.find_differences..rr5zunable to introspect table: %sr&rrSr#r)rSr#rr(rcSsg|] \}}}t|qSr )r)rM _app_label _model_namediffsr r r!rqs))rrrrIGNORE_MISSING_TABLESrrr)rdrr%__name__rrrrrr(order_with_respect_torget_table_description Exceptionstrr{r rollbackr:rr rLr\r_rmrGrPrxrrmaxrr)rZtable cur_app_label app_modelr-rrr@r]r^rir?r(dctr&r r r!find_differences s               zSQLDiff.find_differencescCs&|jdr ||dS||dS)zPrint differences to stdoutsqlN)rprint_diff_sqlprint_diff_text)rZrPr r r! print_diffts zSQLDiff.print_diffc s|js|jd|jd|js$|jd|jdd}|jD]\}}}|s1q)|jsL|rL||krL|jdd|f|}|jsa|ra|jdd|f|D]\}|\}}|j|t fddt |D} d fd dt | d D} |js|jdd | fqc|r|jd d |d|| fqc|j| qcq)dS)NzE# Detecting notnull changes not implemented for this database backendrzF# Detecting unsigned changes not implemented for this database backendrz+ Application:z|-+ Differences for model:c3s>|]\}}t|t|ttfrd|n|fVqdS)reN)rrUrrrrWrMrrirOr r!rQs z*SQLDiff.print_diff_text..'c3s.|]\}}|ddkr|p|VqdS)rTrN)r|rrOr r!rQs  z|--+z%s %s %s %s %sAppModel) rrwriter{rrrrU DIFF_TEXTSrrXrWr ) rZrPrrrrdiffr diff_argsr/r rOr!r{sr      zSQLDiff.print_diff_textc Cs,|js|j|d|jdd}tjj}|js+|js)|j| ddSdS|j| d|j D]S\}}}|s?q7|jsV||krV|j|d| ||}|jsi|ri|j|d| ||D]}|\}} |j |||| } |jr| dd} |j| qkq7|j| d dS) NzF-- Detecting notnull changes not implemented for this database backendrz-- No differenceszBEGIN;z-- Application: %sz -- Model: %sz rFzCOMMIT;)rrrr{r rb quote_namerrrKrrUrreplace) rZrPrr[rrrrrrr/r r r!rsD   zSQLDiff.print_diff_sql)NNNNNrg):r __module__ __qualname__rrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr rrrrrGrLrPr\r_rmrxrrrrr rrrr r r r!r2Hs   " \   @45  # j Er2c@s$eZdZdZdZddZddZdS)GenericSQLDiffFcCrrgr rr r r!rrzGenericSQLDiff.load_nullcCrrgr rr r r!rrzGenericSQLDiff.load_unsignedN)rrrrrrrr r r r!rs  rcsjeZdZdZdZdZfddZddZddZd d Z d d Z dfdd Z ddZ dddZ ZS) MySQLDiffTUNSIGNEDcstt|_|dSrg)superrrauto_incrementload_auto_incrementr __class__r r!rs  zMySQLDiff.loadcCsdd|DS)NcSsg|]}|qSr )r )rMr0r r r!rrz0MySQLDiff.format_field_names..r rr r r!rszMySQLDiff.format_field_namescCsLd}|jD]}|d|g}|D]}|||df}|ddk|j|<qqdS)Nrz SELECT column_name, is_nullable FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = %s column_name is_nullableYESrrrrZrrrrrr r r!rs zMySQLDiff.load_nullcCsFd}|jD]}|d|g}|D]}|||df}|j|qqdS)Nrz SELECT column_name FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = %s AND column_type LIKE '%%unsigned'r)rrrrirr r r!r s  zMySQLDiff.load_unsignedcCs@|jD]}|d|g}|D]}||df}|j|qqdS)Nz SELECT column_name FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = %s AND extra = 'auto_increment'r)rrrri)rZrrrrr r r!rs  zMySQLDiff.load_auto_incrementNcst|||}|s dS|rC||}||dkr&||dkr&|d}||dkr3|dkr3d}||jf|jvrCd|vrC|d7}|S)Ncharr,varboolrrAUTO_INCREMENTz AUTO_INCREMENT)rrrrr|rrrZrrrr+rrr r!r/s&  zMySQLDiff.get_field_db_typec Cs|tddt|D}dd|jD}||}||}|D]\} } | |vr*q!| dr3| ds3q!| d} || d} t| dkr| sN|d || q!| d rV| j rVq!| d rdt | t j rd| j rdq!| drl| jrlq!| dr| d d kr| dr| jrq!| dr| jrq!| dr| jtdrq!t| ddrq!n| drt| |vrq!| dr| drt| |vrq!|d || q!dS)NcSrHr rIrr r r!rNrJz9MySQLDiff.find_index_missing_in_model..cSrr rrQr r r!rOrRr#r2r&rrr=rSrTrrrUrVr$rWF)rr(rrrr:r rrrSrr rXrYr#rNrZr rr) rZr-r?r@rrr[rrrKrr&rr r r!r\Ksj       z%MySQLDiff.find_index_missing_in_modelcr")Nr#c3r$r%r r'r)r r!rQr*z6MySQLDiff.find_unique_missing_in_db..r>r+r$r,r<r-r.r/r0cSg|] }|dr|dqSr#r&r r3r r r!rz7MySQLDiff.find_unique_missing_in_db..r6r>r r)r!rGsb             z#MySQLDiff.find_unique_missing_in_dbrrg)rrrrrrrrrrrrr\rG __classcell__r r rr!rs BrcsVeZdZdZdZddZddZ dfdd Zd d Zd d Z dfdd Z Z S) SqliteSQLDiffTFcCsH|jD]}d}|d|gD]}|||df}|d |j|<qqdS)NrzPRAGMA table_info('%s');rnotnullr)rZrrrrr r r!rs zSqliteSQLDiff.load_nullcCrrgr rr r r!rrzSqliteSQLDiff.load_unsignedNc s|durg}ddt|D}|D]!}|d}t|dkr4|d} | |vr4|ds/|dr4|| q||} tdd|D} | D] }|| vrR||qGtj|||||d dS) NcSsg|] }|jr|jp |jqSr )r#rrrr r r!rs  z;SqliteSQLDiff.find_unique_missing_in_db..r&rrr#rScSrrr r3r r r!rr)rA)r(r=rr,rrrrG) rZr-r?r@rrArFrr&rrrErr r!rGs6       z'SqliteSQLDiff.find_unique_missing_in_dbcCrrgr rZr-r?r@rr r r!rPz&SqliteSQLDiff.find_index_missing_in_dbcCrrgr rr r r!r\ rz)SqliteSQLDiff.find_index_missing_in_modelcsPt|||}|s dS|r&||}||dkr&||dkr&|d}|S)Nrr,r)rrrrr|rrr r!rs  zSqliteSQLDiff.get_field_db_typergr) rrrrrrrrGrPr\rrr r rr!rs $rcseZdZdZdZdddZdZdZddZd dZ d dZ fd d Z d dZ ddZ ddZddZddZddZdfdd ZddZZS)PostgresqlSQLDiffTz*django.contrib.postgres.fields.HStoreField(django.contrib.postgres.fields.JSONField)hstorejsonba SELECT nspname, relname, conname, attname, pg_get_constraintdef(pg_constraint.oid) FROM pg_constraint INNER JOIN pg_attribute ON pg_constraint.conrelid = pg_attribute.attrelid AND pg_attribute.attnum = any(pg_constraint.conkey) INNER JOIN pg_class ON conrelid=pg_class.oid INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END,contype,nspname,relname,conname; z SELECT nspname, relname, attname, attnotnull FROM pg_attribute INNER JOIN pg_class ON attrelid=pg_class.oid INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace; c CLd|d|||d|d|||d|d||dfSNrwrDrALTERrTYPErTrtrYr r r!r]7s zPostgresqlSQLDiff.c CrrrtrYr r r!r]@s c Crv)NrwrDrz ALTER COLUMNrrTrxr^rYr r r!r]Jrycsti|_|dSrg)rrcheck_constraintsload_constraintsrrr r!rSs  zPostgresqlSQLDiff.loadcCs>||jgD]}|d|d|df}|d |j|<qdS)Nnspnamerelnamer attnotnull)r SQL_LOAD_NULLrrZrrr r r!rXszPostgresqlSQLDiff.load_nullcCrrgr rr r r!r]rzPostgresqlSQLDiff.load_unsignedcCsD||jgD]}|d|d|df}d|dvr||j|<qdS)NrrrCHECKpg_get_constraintdef)rSQL_LOAD_CONSTRAINTSrrr r r!rbs   z"PostgresqlSQLDiff.load_constraintscCsdd||idS)Nz)django.contrib.postgres.fields.ArrayField base_field)rr)r)rZrr r r!get_data_type_arrayfieldhs z*PostgresqlSQLDiff.get_data_type_arrayfieldcsidddfdddfdddfd dd fd dd fd ddfdddfdddfdddfdddfdddfdddfdddfdddfddd fd!dd"fd#dfd$dd%d&d'S)(Niric jddS)N BooleanFieldrrr rr r!r]s zCPostgresqlSQLDiff.get_data_types_reverse_override..icr)N BinaryFieldrrr rr r!r]tricrNrrrr rr r!r]uricrNrrrr rr r!r]vricrrrr rr r!r]wricrrrr rr r!r]xricrrrr rr r!r]yricrrrr rr r!r]zricrrrr rr r!r]{ricr)NBigIntegerFieldrrr rr r!r]|ricrN FloatFieldrrr rr r!r]}ricrrrr rr r!r]~ricrrrr rr r!r]ri[crN DateTimeFieldrrr rr r!r]ricrrrr rr r!r]ricr)Nrrrr rr r!r]rcr)N DurationFieldrrr rr r!r]rz0django.contrib.postgres.search.SearchVectorFieldr)iiir rr rr!rpsN                 z1PostgresqlSQLDiff.get_data_types_reverse_overridec Cs&i}|dd|g|D]6\}}}}||vr;g|dk|dv|dkr3t|dddnd d d d ||<||d |q|d d|g|D]\}}||vrdgd d d dd d ||<||d |qQ|d|g|D]\} } } } | |vrt| | | d d dd || <qy|S)zm Find constraints for table Backport of django's introspection.get_constraints(...) a SELECT kc.constraint_name, kc.column_name, c.constraint_type, array(SELECT table_name::text || '.' || column_name::text FROM information_schema.constraint_column_usage WHERE constraint_name = kc.constraint_name) FROM information_schema.key_column_usage AS kc JOIN information_schema.table_constraints AS c ON kc.table_schema = c.table_schema AND kc.table_name = c.table_name AND kc.constraint_name = c.constraint_name WHERE kc.table_schema = %s AND kc.table_name = %s r primary key)rr#z foreign keyrrrNF)r&rSr#rTrVr2r&a SELECT kc.constraint_name, kc.column_name FROM information_schema.constraint_column_usage AS kc JOIN information_schema.table_constraints AS c ON kc.table_schema = c.table_schema AND kc.table_name = c.table_name AND kc.constraint_name = c.constraint_name WHERE c.constraint_type = 'CHECK' AND kc.table_schema = %s AND kc.table_name = %s Ta SELECT c2.relname, ARRAY( SELECT (SELECT attname FROM pg_catalog.pg_attribute WHERE attnum = i AND attrelid = c.oid) FROM unnest(idx.indkey) i ), idx.indisunique, idx.indisprimary FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index idx WHERE c.oid = idx.indrelid AND idx.indexrelid = c2.oid AND c.relname = %s )rrr rr r,r) rZrrrrrrkind used_colsr2r&r#primaryr r r!rs\         z!PostgresqlSQLDiff.get_constraintsNc st|||}|s dS|r|dr2|jp|j}|d||fdd}|dr0|dd}|S|jrGt |t rG|dkrAd}n|d krGd }|r|j }|d krRd }|jpW|j}|j |||fi d d}|r|dd}|dd}ddd|dD}|d|7}|S)Nz[]a`SELECT attname, format_type(atttypid, atttypmod) AS type FROM pg_attribute WHERE attrelid = %s::regclass AND attname = %s AND attnum > 0 AND NOT attisdropped ORDER BY attnum; rrzcharacter varyingr,rrrqrurtrrrz((r z))ryz("c Ss4g|]}d|vrddd|ddDp|qS)ryz" css|]}|dVqdS)"N)r{)rMpr r r!rQCszAPostgresqlSQLDiff.get_field_db_type...rFr)rWr rhr r r!rAsz7PostgresqlSQLDiff.get_field_db_type..rF)rrendswithrrrr<rrSrrrrr rWr ) rZrrrr+rintrospect_db_typercheck_constraintrr r!r sX          z#PostgresqlSQLDiff.get_field_db_typec CsDz|d|gdd}|j|dWSttfy!YdSw)Nz-SELECT typname FROM pg_type WHERE typelem=%s;rtypname_)rDATA_TYPES_REVERSE_NAMEr r{ IndexErrorr)rZrrr r r!rLsz*PostgresqlSQLDiff.get_field_db_type_lookupr)rrrrrrrrrrrrrrrrrrrrrr r rr!rs.  ? r)postgispostgresql_psycopg2 postgresqlmysqlsqlite3oraclecsTeZdZdZdZfddZfddZeddZfd d Z fd d Z Z S) CommandaPrints the (approximated) difference between models and fields in the database for the given app name(s). It indicates how columns in the database are different from the sql that would be generated by Django. This command is not a database migration tool. (Though it can certainly help) It's purpose is to show the current differences as a way to check/debug ur models compared to the real database tables and columns.Fcst||jddd|jdddddd d |jd d d dddd |jddddddd|jddd dddd|jdddddd|jdddddd|jd dd!dtjddS)"Nr*)nargsz--all-applicationsz-a store_trueFrz8Automaticly include all application from INSTALLED_APPS.)rdefaultdesthelpz--not-only-existingz-e store_falseTrz_Check all tables that exist in the database, not only tables that should exist based on models.z--dense-outputz-drzRShows the output in dense format, normally output is spreaded over multiple lines.)rrrrz --output_textz-trz:Outputs the differences as descriptive text instead of SQLz--include-proxy-modelsrz!Include proxy models in the graphz--include-defaultsraz3Include default values in SQL output (beta feature)z--migrate-for-testsmigrate_for_tests)r add_arguments add_argumentargparseSUPPRESS)rZparserrr r!rssn    zCommand.add_argumentscstj|i|d|_dS)Nr)rr exit_code)rZr\rrr r!rs zCommand.__init__c Os\ddlm}|d}d}t|dr|jdd}n|j}|dkr$td|d r/tjd d }n&|s5td t|t t t fs@|g}g}|D]}t |}| |jd d qD|s[td |d} | rsddlm} | dg|Rd d d|s}tjdd}d|vr|dd}t|t} | |||j|jd} | | | jsd|_| |jdS)Nr)settingsr DATABASESrENGINEdummyzDjango doesn't know which syntax to use for your SQL statements, because you haven't specified the DATABASE_ENGINE setting. Edit your settings file and change DATABASE_ENGINE to something like 'postgresql' or 'mysql'.rT)include_auto_createdzEnter at least one appname.z+Unable to execute sqldiff no models founds.r) call_commandmigrate)no_input run_syncdbrr)rr) django.confrrrDATABASE_ENGINErr get_modelsrrrrget_app_configr'django.core.managementr r rr DATABASE_SQLDIFF_CLASSESr rrrrrrrrrP) rZr\rr app_labelsenginerr app_configrr clssqldiff_instancer r r!handlesN      zCommand.handlec sz tj|i|WdStyD}z,|drt|dd}|s)ttj|jj}| d|j j |ft dWYd}~dSd}~ww)N tracebackrz%s: %srT) rrrrr sysrrPr|rrrexit)rZr\rrirrr r!rs zCommand.executecst|t|jdSrg)r run_from_argvrrr)rZargvrr r!rs zCommand.run_from_argv) rrrroutput_transactionrrrrrrrr r rr!ris  ?  ;r),__doc__rrrtypingrrrr django.appsrrrrdjango.core.management.baser django.core.management.colorr django.dbr r r django.db.modelsrdjango.db.models.fieldsrrdjango.db.models.optionsr"django_extensions.management.utilsrr__annotations__rrr"r(r2rrrrrrr r r r!sP      % ZSE