3
g                 @   sH  d Z ddlZddlZddlZddlmZmZmZmZ ddl	m
Z
 ddlmZmZ ddlmZ ddlmZ ddlmZmZmZ dd	lmZ dd
lmZmZ ddlmZ ddlmZ edddZe e!ffddZ"dd Z#G dd dZ$G dd de$Z%G dd de$Z&G dd de$Z'G dd de$Z(e(e(e(e&e'e%dZ)G dd  d eZ*dS )!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)DictUnionCallableOptional)apps)BaseCommandCommandError)OutputWrapper)no_style)
connectiontransactionmodels)UniqueConstraint)	AutoFieldIntegerField)normalize_together)signalcommand_orderT)nullc             C   s|   t | }t| } d}x^|t| k rrxDt| | |rf| | sP| j| |d8 }P q$| | | ||d < q$W |d7 }qW || S )Nr      )typelistlen
isinstancepop)lstZltypesZltypei r   k/var/www/tester-filtro-web/env/lib/python3.6/site-packages/django_extensions/management/commands/sqldiff.pyflatten*   s    
r   c             C   s`   g }| j r,xP| jD ]}|jt|j qW n0x.| jD ]$}|jtd}|d krNq4|j| q4W |S )N)r   )	proxyparentsextendall_local_fields_metaZlocal_fieldsdb_typer   append)metaZ
all_fieldsparentfZcol_typer   r   r   r#   :   s    r#   c               @   s  e Zd Zi ZdgZdddddddd	d
ddddddgZdddddddddddddddd Zd!d" Zd#d" Zd$d" Z	d%d" Z
d&d" Zd'd" Zd(d" Zd)d" Zd*d" Zd+d" Zd,d" Zd-d" Zd.d" Zd/d" Zd0Zd0Zd1Zd2d3 Zd4d5 Zd6d7 Zd8d9 Zd:d; Zd<d= Zd>d? Zd@dA ZdBdC Z dDdE Z!dtdFdGZ"dudHdIZ#dJdK Z$dLdM Z%dNdO Z&dPdQ Z'dRdS Z(dTdU Z)dVdW Z*dvdXdYZ+dZd[ Z,d\d] Z-d^d_ Z.d`da Z/dbdc Z0dwdddeZ1dxdfdgZ2dhdi Z3djdk Z4dldm Z5e6 fdndoZ7dpdq Z8drds Z9d1S )ySQLDiffZdjango_migrationserrorcommentztable-missing-in-dbztable-missing-in-modelzfield-missing-in-dbzfield-missing-in-modelzfkey-missing-in-dbzfkey-missing-in-modelzindex-missing-in-dbzindex-missing-in-modelzunique-missing-in-dbzunique-missing-in-modelzfield-type-differzfield-parameter-differznotnull-differzerror: %(0)szcomment: %(0)sz!table '%(0)s' missing in databaseztable '%(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 database)r+   r,   ztable-missing-in-dbztable-missing-in-modelzfield-missing-in-dbzfield-missing-in-modelzfkey-missing-in-dbzfkey-missing-in-modelzindex-missing-in-dbzindex-missing-in-modelzunique-missing-in-dbzunique-missing-in-modelzfield-type-differzfield-parameter-differznotnull-differc                sZ   d j d j||d  j d j||d dj fddt|d	d  D f S )
Nz%s %s
	%s %s %s;zALTER TABLEr   z
ADD COLUMNr    c             3   s.   | ]&\}}|d kr j |n j|V  qdS )r   N)SQL_COLTYPESQL_KEYWORD).0r   a)styler   r   	<genexpr>w   s    z#SQLDiff.<lambda>.<locals>.<genexpr>   )r/   	SQL_TABLE	SQL_FIELDjoin	enumerate)selfr2   qnargsr   )r2   r   <lambda>r   s
   zSQLDiff.<lambda>c             C   s8   d|j d|j||d |j d|j||d f S )Nz%s %s
	%s %s;zALTER TABLEr   zDROP COLUMNr   )r/   r5   r6   )r9   r2   r:   r;   r   r   r   r<   y   s   c                s   d j d j||d  j d j||d dj fddt|d	d  D  j d
 j||d  j||d tjj f	 S )Nz%s %s
	%s %s %s %s %s (%s)%s;zALTER TABLEr   z
ADD COLUMNr   r-   c             3   s.   | ]&\}}|d kr j |n j|V  qdS )r   N)r.   r/   )r0   r   r1   )r2   r   r   r3      s    z#SQLDiff.<lambda>.<locals>.<genexpr>   Z
REFERENCESr4      )r/   r5   r6   r7   r8   r   opsZdeferrable_sql)r9   r2   r:   r;   r   )r2   r   r<      s   "c          
      sd   d|j d|j |d |j d|j |d |jdj fdd|d	 D |j |d
 f S )Nz%s %s
	%s %s (%s%s);zCREATE INDEXr4   ONr   z, c             3   s   | ]} |V  qd S )Nr   )r0   e)r:   r   r   r3      s    z#SQLDiff.<lambda>.<locals>.<genexpr>r   r>   )r/   r5   r6   r7   )r9   r2   r:   r;   r   )r:   r   r<      s
    c             C   s    d|j d|j||d f S )Nz%s %s;z
DROP INDEXr   )r/   r5   )r9   r2   r:   r;   r   r   r   r<      s   c                s`   d|j d|j |d |j d|j |d |j d|jdj fdd	|d
 D f S )Nz%s %s
	%s %s %s (%s);zALTER TABLEr   zADD CONSTRAINTr4   ZUNIQUEz, c             3   s   | ]} |V  qd S )Nr   )r0   rA   )r:   r   r   r3      s    z#SQLDiff.<lambda>.<locals>.<genexpr>r   )r/   r5   r6   r7   )r9   r2   r:   r;   r   )r:   r   r<      s   c          	   C   s@   d|j d|j||d |j d|j d|j||d f S )Nz%s %s
	%s %s %s;zALTER TABLEr   DROPZ
CONSTRAINTr   )r/   r5   )r9   r2   r:   r;   r   r   r   r<      s
   c             C   sD   d|j d|j||d |j d|j||d |j|d f S )Nz%s %s
	%s %s %s;zALTER TABLEr   MODIFYr   r4   )r/   r5   r6   r.   )r9   r2   r:   r;   r   r   r   r<      s
   c             C   sD   d|j d|j||d |j d|j||d |j|d f S )Nz%s %s
	%s %s %s;zALTER TABLEr   rC   r   r4   )r/   r5   r6   r.   )r9   r2   r:   r;   r   r   r   r<      s
   c             C   sL   d|j d|j||d |j d|j||d |j |d |j df S )Nz%s %s
	%s %s %s %s;zALTER TABLEr   rC   r   r4   zNOT NULL)r/   r5   r6   )r9   r2   r:   r;   r   r   r   r<      s   c             C   s   |j d|j|d  S )Nz-- Error: %sr   )NOTICEERROR)r9   r2   r:   r;   r   r   r   r<      s    c             C   s   |j d|j|d  S )Nz-- Comment: %sr   )rD   r5   )r9   r2   r:   r;   r   r   r   r<      s    c             C   s   |j d|d  S )Nz-- Table missing: %sr   )rD   )r9   r2   r:   r;   r   r   r   r<      s    c             C   s   |j d|d  S )Nz-- Model missing for table: %sr   )rD   )r9   r2   r:   r;   r   r   r   r<      s    FNc             C   s   d | _ || _|| _|d | _|| _|| _tj| _g | _i | _	t
 | _i | _t
 | _| j| j| j| j| j| j| j| j| j| j| j| j| j| j| jd| _d S )Ndense_output)r+   r,   ztable-missing-in-dbztable-missing-in-modelzfield-missing-in-dbzfield-missing-in-modelzfkey-missing-in-dbzfkey-missing-in-modelzindex-missing-in-dbzindex-missing-in-modelzunique-missing-in-dbzunique-missing-in-modelzfield-type-differzfield-parameter-differznotnull-differ)has_differences
app_modelsoptionsdensestdoutstderrr   introspectiondifferencesunknown_db_fieldssetnew_db_fieldsr   unsigned	SQL_ERRORSQL_COMMENTSQL_TABLE_MISSING_IN_DBSQL_TABLE_MISSING_IN_MODELSQL_FIELD_MISSING_IN_DBSQL_FIELD_MISSING_IN_MODELSQL_FKEY_MISSING_IN_DBSQL_INDEX_MISSING_IN_DBSQL_INDEX_MISSING_IN_MODELSQL_UNIQUE_MISSING_IN_DBSQL_UNIQUE_MISSING_IN_MODELSQL_FIELD_TYPE_DIFFERSQL_FIELD_PARAMETER_DIFFERSQL_NOTNULL_DIFFERDIFF_SQL)r9   rH   rI   rK   rL   r   r   r   __init__   s6    
zSQLDiff.__init__c             C   sZ   t j | _| jj| jd d| _dd | jj| jD | _| jrH| j	  | j
rV| j  d S )Nonly_existing)rc   c             S   s   g | ]
}|j qS r   )name)r0   
table_infor   r   r   
<listcomp>   s    z SQLDiff.load.<locals>.<listcomp>)r   cursorrM   Zdjango_table_namesrI   django_tablesZget_table_list	db_tablescan_detect_notnull_differ	load_nullcan_detect_unsigned_differload_unsigned)r9   r   r   r   load   s    
zSQLDiff.loadc             C   s   t dd S )Nzcload_null functions must be implemented if diff backend has 'can_detect_notnull_differ' set to True)NotImplementedError)r9   r   r   r   rk      s    zSQLDiff.load_nullc             C   s   t dd S )Nzgload_unsigned function must be implemented if diff backend has 'can_detect_unsigned_differ' set to True)ro   )r9   r   r   r   rm      s    zSQLDiff.load_unsignedc             C   s   | j j||g f d S )N)rN   r&   )r9   	app_label
model_namer   r   r   add_app_model_marker   s    zSQLDiff.add_app_model_markerc             G   s.   || j kstd| jd d j||f d S )NzUnknown difference typer   rs   )
DIFF_TYPESAssertionErrorrN   r&   )r9   	diff_typer;   r   r   r   add_difference   s    zSQLDiff.add_differencec             C   s   | j S )N)DATA_TYPES_REVERSE_OVERRIDE)r9   r   r   r   get_data_types_reverse_override   s    z'SQLDiff.get_data_types_reverse_overridec             C   s   |S )Nr   )r9   field_namesr   r   r   format_field_names  s    zSQLDiff.format_field_namesc       	      C   sx   t j }|j|| dd |jD }| j|}g }x@|j D ]4}g }xt||D ]}|j| qPW |jt| q<W |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/
        c             S   s   g | ]}|d  qS )r   r   )r0   rd   r   r   r   rf     s    z'SQLDiff.sql_to_dict.<locals>.<listcomp>)	r   rg   executedescriptionr{   fetchallzipr&   dict)	r9   queryparamrg   
fieldnamesresultrowZrowsetfieldr   r   r   sql_to_dict  s    
zSQLDiff.sql_to_dictc             C   s   |j tdS )N)r   )r%   r   )r9   r   r   r   r   get_field_model_type  s    zSQLDiff.get_field_model_typec             C   s   i S )Nr   )r9   Zcurrent_kwargsr}   r   
table_namereverse_typer   r   r   get_field_db_type_kwargs  s    z SQLDiff.get_field_db_type_kwargsc             C   sj  |d }| j  }||kr"|| }ny| jj||}W np tk
r   | j|}|s| jd  d d |d d f}|| jkrd| j|< | jdd|d |f  d S Y nX t|r| }i }t	|t
r|j|d  |d }|dkr|rt|d	d d
krd}t	|tr|j|d  |d }|dkr:|d r:|d |d< |dkrt|d |d< |d rht|d pn|d |d< |d rd|d< |d!krd|d< |rt|ddrd|d< |dkr|d }	| jj||	\}}
|
r|j|
 d| }| j|||||}|j| | j|}|f |jtd}|j}|s8d}|||jf| jkrf| j|krfd|| jf }|S )"Nr   r4   r,   z)Unknown database type for field '%s' (%s)r   kwargsrd   i2B  Z	geom_typeZPOINTz.django.contrib.gis.db.models.fields.PointField	CharFieldr>   
max_lengthDecimalFieldr=   Z
max_digits   Zdecimal_places   TZblank	TextFieldr   Z	geographyFZGeometryFieldz&django.contrib.gis.db.models.fields.%s)r   publicz%s %srs   )r   r   )ry   rM   Zget_field_typeKeyErrorget_field_db_type_lookuprN   rO   rw   callabler   r   updategetattrtupleabsZget_geometry_typer   get_field_classr%   r   db_tablespacecolumnrR   unsigned_suffix)r9   r}   r   r   	type_coderx   r   keyr   Zgeo_colZ
geo_paramsextra_kwargsZfield_classZfield_db_type
tablespacer   r   r   get_field_db_type   sh    






$





 zSQLDiff.get_field_db_typec             C   s   d S )Nr   )r9   r   r   r   r   r   j  s    z SQLDiff.get_field_db_type_lookupc             C   s6   d|kr,|j dd\}}tj|}t||S tt|S )N.r   )rsplit	importlibimport_moduler   r   )r9   Z
class_pathmodule_pathpackage_namemoduler   r   r   r   m  s
    

zSQLDiff.get_field_classc             C   s2   |j }|dkrd}|jp|j}| jj|||fdS )N r   Zfixme)r   	db_columnattnamer   get)r9   r   r   r   r   r   r   r   get_field_db_nullableu  s
    zSQLDiff.get_field_db_nullablec             C   s,   |r(|dkr(|j dd j dd j S |S )Nzdouble precisionr-   r   ()splitlower)r9   
field_typer   r   r   strip_parameters|  s    zSQLDiff.strip_parametersc             C   s6   t t|j}x|jD ]}|j|j qW | j||S )N)r   r   index_togetherindexesr&   fieldsexpand_together)r9   r'   Zindexes_normalizedidxr   r   r   get_index_together  s    zSQLDiff.get_index_togetherc             C   s@   t t|j}x$|jD ]}t|tr|j|j qW | j||S )N)	r   r   unique_togetherconstraintsr   r   r&   r   r   )r9   r'   Zunique_normalized
constraintr   r   r   get_unique_together  s
    
zSQLDiff.get_unique_togetherc                s6   g }x,t |D ] }|jt fdd|D  qW |S )Nc             3   s   | ]} j |jV  qd S )N)	get_fieldr   )r0   r   )r'   r   r   r3     s    z*SQLDiff.expand_together.<locals>.<genexpr>)r   r&   r   )r9   Ztogetherr'   Znew_togetherr   r   )r'   r   r     s
    zSQLDiff.expand_togetherc                sr  t jt }xt|D ]}|r(|j|kr(q|jr|jr|jp>|j |j i jd}| rv|rvt fdd|j	 D } |kr|rq|j
| g}	| jd| g|	d  |jt d}
|
jdr| jd| g|	d	 d
 |
jdr| jd| g|	d	 d qW | j|}tdd |j D }xP|D ]H}||kr4q"|rH||krHq"|j
||}	| jd|||	d  q"W d S )Nuniquec             3   s(   | ] \}} g|d  kr|d V  qdS )columnsr   Nr   )r0   contraint_namer   )r   r   r   r3     s    z4SQLDiff.find_unique_missing_in_db.<locals>.<genexpr>zunique-missing-in-db_uniq)r   varcharzindex-missing-in-db_likez varchar_pattern_opstextz text_pattern_opsc             S   s&   g | ]}|d  r|d  r|d qS )r   indexr   r   )r0   vr   r   r   rf     s    z5SQLDiff.find_unique_missing_in_db.<locals>.<listcomp>)r   SchemaEditorClassr#   r   r   managedr   r   anyitems_create_index_namerw   r%   
startswithr   r   values)r9   r'   table_indexestable_constraintsr   	skip_listschema_editorr   db_field_unique
index_namer%   r   db_unique_columnsunique_columnsr   )r   r   find_unique_missing_in_db  s6    






z!SQLDiff.find_unique_missing_in_dbc             C   s   t dd t|D }| j|}xz|j D ]n\}}|d s<q*|d rFq*|d }	t|	dkr||j|	d }
|
d krrq|
jrq*nt|	|krq*| jd|| q*W d S )	Nc             S   s   g | ]}|j |fqS r   )r   )r0   r   r   r   r   rf     s    z8SQLDiff.find_unique_missing_in_model.<locals>.<listcomp>r   r   r   r   r   zunique-missing-in-model)	r   r#   r   r   r   r   r   r   rw   )r9   r'   r   r   r   r   r   constraint_namer   r   r   r   r   r   find_unique_missing_in_model  s"    
z$SQLDiff.find_unique_missing_in_modelc             C   s:  t jt }xt|D ]}|jr|jp(|j}||kr|j||g}| jd||g|d |jt d}	|	j	dr| jd||g|d d |	j	dr| jd||g|d d qW | j
|}
td	d
 |j D }x8|
D ]0}||krq|j||}| jd|||d d qW x2|jD ](}|j|kr
| jd||j|jd q
W d S )Nzindex-missing-in-dbr   )r   r   r   z varchar_pattern_opsr   z text_pattern_opsc             S   s&   g | ]}|d  r|d  r|d qS )r   r   r   r   )r0   r   r   r   r   rf     s    z4SQLDiff.find_index_missing_in_db.<locals>.<listcomp>Z_idx)r   r   r#   db_indexr   r   r   rw   r%   r   r   r   r   r   rd   r   )r9   r'   r   r   r   r   r   r   r   r%   r   Zdb_index_togetherr   r   r   r   r   find_index_missing_in_db  s,    




z SQLDiff.find_index_missing_in_dbc             C   s|  t dd t|D }dd |jD }| j|}xD|j D ]6\}}	||krPq<|	d rd|	d  rdq<|	d }
|j|
d }|	d r|	d s|d krnt|
dkrL|	d	 r|jrq<|	d
 rt|t	j
r|jrq<|	d r|jrq<|	d r|	d dkr|	jdr|jrq<|	d r |jr q<|	d r:|jtdr:q<t|ddrfq<n|	d rft|
|krfq<| jd|| q<W d S )Nc             S   s   g | ]}|j |fqS r   )r   )r0   r   r   r   r   rf     s    z7SQLDiff.find_index_missing_in_model.<locals>.<listcomp>c             S   s   g | ]
}|j qS r   )rd   )r0   r   r   r   r   rf     s    r   r   r   r   r   primary_keyforeign_keyr   r   orderscheck)r   spatial_indexFzindex-missing-in-model)r   r#   r   r   r   r   r   r   r   r   
ForeignKeydb_constraintr   r   db_checkr   r   r   rw   )r9   r'   r   r   r   r   meta_index_namesr   r   r   r   r   r   r   r   find_index_missing_in_model  s<    
,z#SQLDiff.find_index_missing_in_modelc             C   s0   x*|D ]"}|d |kr| j d||d  qW d S )Nr   zfield-missing-in-model)rw   )r9   fieldmaptable_descriptionr   r   r   r   r   find_field_missing_in_model  s    
z#SQLDiff.find_field_missing_in_modelc       	      C   s   dd |D }x|j  D ]\}}||krg }|jr`|j|jjjj|jjjj|jjjg d}nd}|j	|j
td | jd r|j r|j	d|j|j   |js|j	d | j|||f|  | jj||f qW d S )	Nc             S   s   g | ]}|d  qS )r   r   )r0   r   r   r   r   rf     s    z4SQLDiff.find_field_missing_in_db.<locals>.<listcomp>zfkey-missing-in-dbzfield-missing-in-db)r   include_defaultsz
DEFAULT %szNOT NULL)r   Zremote_fieldr"   modelr$   db_tabler   
field_namer   r&   r%   r   rI   Zhas_defaultZget_prep_valueget_defaultr   rw   rQ   add)	r9   r   r   r   	db_fieldsr   r   Zfield_outputopr   r   r   find_field_missing_in_db  s    (
z SQLDiff.find_field_missing_in_dbc       
      C   s   t dd |D }x|t|D ]p}|j|kr,q||j }| j|}| j|||}	|rd|||||	\}}	| j|	| j|ks| jd||j||	 qW d S )Nc             S   s   g | ]}|d  |fqS )r   r   )r0   r   r   r   r   rf   1  s    z2SQLDiff.find_field_type_differ.<locals>.<listcomp>zfield-type-differ)r   r#   rd   r   r   r   rw   )
r9   r'   r   r   funcr   r   r}   
model_typer%   r   r   r   find_field_type_differ0  s    


zSQLDiff.find_field_type_differc             C   s   t dd |D }xt|D ]}|j|kr,q||j }| j|}| j|||}	| j|| j|	ksdq|rz|||||	\}}	|jtdd }
d|	kr|	jdd\}	}|j	 j
djd}nd }||	k s|
|k r| jd	||j||	 qW d S )
Nc             S   s   g | ]}|d  |fqS )r   r   )r0   r   r   r   r   rf   B  s    z7SQLDiff.find_field_parameter_differ.<locals>.<listcomp>)r   r   z CHECKr   r   )zfield-parameter-differ)r   r#   rd   r   r   r   Zdb_parametersr   r   striplstriprstriprw   )r9   r'   r   r   r   r   r   r}   r   r%   Zmodel_checkr   r   r   r   find_field_parameter_differA  s$    


z#SQLDiff.find_field_parameter_differc             C   sp   | j s
d S x`t|D ]T}|jp"|j}||f| jkr4q| j||}|j|kr|jrTdpVd}| jd||| qW d S )NrB   ZSETznotnull-differ)rj   r#   r   r   rQ   r   r   rw   )r9   r'   r   r   r   r   r   actionr   r   r   find_field_notnull_differ\  s    
z!SQLDiff.find_field_notnull_differc             C   s   i S )Nr   )r9   rg   r   rM   r   r   r   get_constraintsi  s    zSQLDiff.get_constraintsc             C   sZ  | j d rF| jd d  x.| jD ]$}|| jkr|| jkr| jd| qW d }x| jD ]}|j}|j}|j	}| j d  r|j
rqT||kr| j||j || jkr| jd| qTt| jdr| jj| j|}n| j| j|| j}tdd t|D }|jrt|d< y| jj| j|}	W nF tk
r^ }
 z(| jd	d
t|
j   tj  wTW Y d d }
~
X nX i }xP|j D ]D\}}|d }t|dkrn|d |d |jd|d||d < qnW | j|||| | j|||| | j||	| | j ||	| | j!|||| | j"|||| | j#||	| | j$||	| | j%||	| qTW t&dd | j'D | _(d S )Nall_applicationsztable-missing-in-modelinclude_proxy_modelsztable-missing-in-dbr   c             S   s   g | ]}|j p|j |fqS r   )r   Zget_attname)r0   r   r   r   r   rf     s    z,SQLDiff.find_differences.<locals>.<listcomp>r   r+   zunable to introspect table: %sr   r   r   r   r   )r   r   r   r   r   c             S   s   g | ]\}}}t |qS r   )r   )r0   Z
_app_labelZ_model_namediffsr   r   r   rf     s    ))rI   rr   ri   rh   IGNORE_MISSING_TABLESrw   rH   r$   r   rp   r    __name__hasattrrM   r   rg   r   r#   Zorder_with_respect_toORDERING_FIELDZget_table_description	Exceptionstrr   r   rollbackr   r   r   r   r   r   r   r   r   r   r   r   maxrN   rG   )r9   tablecur_app_labelZ	app_modelr'   r   rp   r   r   r   rA   r   r   dctr   r   r   r   find_differencesl  s^    

zSQLDiff.find_differencesc             C   s$   | j d r| j| n
| j| dS )z Print differences to stdout sqlN)rI   print_diff_sqlprint_diff_text)r9   r2   r   r   r   
print_diff  s    
zSQLDiff.print_diffc       
   	      s  | j s$| jj jd | jjd | jsH| jj jd | jjd d }xF| jD ]:\}}}|shqV| j r|r||kr| jjd jd j|f  |}| j r|r| jjd jd j|f  x|D ]}|\}}| j| t	 fddt
|D  }	d	j fd
dt
|	jd	D }	| jsH| jjd jd|	f  q|r| jjd jd j| jd j||	f  q| jj|	 qW qVW d S )NzE# Detecting notnull changes not implemented for this database backendr   zF# Detecting unsigned changes not implemented for this database backendz%s %sz+ Application:z|-+ Differences for model:c             3   s<   | ]4\}}t | jt|ttfr,d j|n|fV  qdS )z, N)r  r5   r   r   r   r7   )r0   r   rA   )r2   r   r   r3     s   z*SQLDiff.print_diff_text.<locals>.<genexpr>'c             3   s,   | ]$\}}|d  dkr  j |p"|V  qdS )r4   r   N)rE   )r0   r   rA   )r2   r   r   r3     s    z|--+z%s %s %s %s %sZAppZModel)rj   rK   writerD   rl   rN   rJ   r5   
DIFF_TEXTSr   r8   r7   r   )
r9   r2   r
  rp   rq   r   diffrv   	diff_argsr   r   )r2   r   r    s4      


"4zSQLDiff.print_diff_textc             C   s4  | j s$| jj|jd | jjd d }tjj}| jsP| jsN| jj|j	d n| jj|j	d x| j
D ]\}}}|szqj| j r||kr| jj|jd|j|  |}| j r|r| jj|jd|j|  xF|D ]>}|\}}	| j| |||	}
| jr
|
jdd}
| jj|
 qW qjW | jj|j	d	 d S )
NzF-- Detecting notnull changes not implemented for this database backendr   z-- No differenceszBEGIN;z-- Application: %sz-- Model: %sz
	r-   zCOMMIT;)rj   rK   r  rD   r   r?   Z
quote_namerG   rJ   r/   rN   r5   ra   replace)r9   r2   r
  r:   rp   rq   r   r  rv   r  r   r   r   r   r    s0    
zSQLDiff.print_diff_sql)NNN)NN)N)N)N):r  
__module____qualname__rx   r  rt   r  rW   rX   rY   rZ   r[   r\   r]   r^   r_   r`   rS   rT   rU   rV   rj   rl   r   rb   rn   rk   rm   rr   rw   ry   r{   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r  r
   r  r  r  r   r   r   r   r*   H   s   "

J	
$(

Q!r*   c               @   s$   e Zd ZdZdZdd Zdd ZdS )GenericSQLDiffFc             C   s   d S )Nr   )r9   r   r   r   rk     s    zGenericSQLDiff.load_nullc             C   s   d S )Nr   )r9   r   r   r   rm   	  s    zGenericSQLDiff.load_unsignedN)r  r  r  rj   rl   rk   rm   r   r   r   r   r    s   r  c                   sh   e Zd ZdZdZdZ fddZdd Zdd Zd	d
 Z	dd Z
d fdd	Zdd ZdddZ  ZS )	MySQLDiffTZUNSIGNEDc                s   t  j  t | _| j  d S )N)superrn   rP   auto_incrementload_auto_increment)r9   )	__class__r   r   rn     s    
zMySQLDiff.loadc             C   s   dd |D S )Nc             S   s   g | ]}|j  qS r   )r   )r0   r)   r   r   r   rf     s    z0MySQLDiff.format_field_names.<locals>.<listcomp>r   )r9   rz   r   r   r   r{     s    zMySQLDiff.format_field_namesc             C   sT   d}xJ| j D ]@}| jd|g}x,|D ]$}|||d f}|d dk| j|< q$W qW d S )Nr   z
                SELECT column_name, is_nullable
                FROM information_schema.columns
                WHERE table_schema = DATABASE()
                    AND table_name = %scolumn_nameZis_nullableZYES)ri   r   r   )r9   r   r   r   re   r   r   r   r   rk     s    

zMySQLDiff.load_nullc             C   sN   d}xD| j D ]:}| jd|g}x&|D ]}|||d f}| jj| q$W qW d S )Nr   z
                SELECT column_name
                FROM information_schema.columns
                WHERE table_schema = DATABASE()
                    AND table_name = %s
                    AND column_type LIKE '%%unsigned'r  )ri   r   rR   r   )r9   r   r   r   re   r   r   r   r   rm   &  s    

zMySQLDiff.load_unsignedc             C   sH   xB| j D ]8}| jd|g}x$|D ]}||d f}| jj| q W qW d S )Nz
                SELECT column_name
                FROM information_schema.columns
                WHERE table_schema = DATABASE()
                   AND table_name = %s
                   AND extra = 'auto_increment'r  )ri   r   r  r   )r9   r   r   re   r   r   r   r   r  3  s    

zMySQLDiff.load_auto_incrementNc                s   t  j|||}|sd S |r| j|}| j|dkrL| j|dkrL|jd}| j|dkrf|dkrfd}||jf| jkrd|kr|d7 }|S )Ncharr   varboolintegerZAUTO_INCREMENTz AUTO_INCREMENT)r  r   r   r   r   r   r  )r9   r}   r   r   r%   r   )r  r   r   r   B  s    

zMySQLDiff.get_field_db_typec             C   s  t dd t|D }dd |jD }| j|}| j|}xb|j D ]T\}	}
|	|krZqF|
d rn|
d  rnqF|
d }|j|d }t|dkrP|s| jd	||	 qF|
d
 r|j	rqF|
d rt
|tjr|jrqF|
d r|jrqF|
d r|
d dkr|
jdr|jrqF|
d r$|jr$qF|
d r>|jtdr>qFt|ddrqFn>|
d rjt||krjqF|
d r|
d rt||krqF| jd	||	 qFW d S )Nc             S   s   g | ]}|j |fqS r   )r   )r0   r   r   r   r   rf   Y  s    z9MySQLDiff.find_index_missing_in_model.<locals>.<listcomp>c             S   s   g | ]
}|j qS r   )rd   )r0   r   r   r   r   rf   Z  s    r   r   r   r   r   zindex-missing-in-modelr   r   r   r   r   r   )r   r   F)r   r#   r   r   r   r   r   r   rw   r   r   r   r   r   r   r   r   r   r   r   )r9   r'   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   X  sD    

,"z%MySQLDiff.find_index_missing_in_modelc                sr  t jt }xt|D ]}|r(|j|kr(q|jr|jr|jp>|j |j i jd}| rv|rvt fdd|j	 D } |kr|rq|j
| g}	| jd| g|	d  |jt d}
|
jdr| jd| g|	d	 d
 |
jdr| jd| g|	d	 d qW | j|}tdd |j D }xP|D ]H}||kr4q"|rH||krHq"|j
||}	| jd|||	d  q"W d S )Nr   c             3   s(   | ] \}} g|d  kr|d V  qdS )r   r   Nr   )r0   r   r   )r   r   r   r3     s    z6MySQLDiff.find_unique_missing_in_db.<locals>.<genexpr>zunique-missing-in-dbr   )r   r   zindex-missing-in-dbr   z varchar_pattern_opsr   z text_pattern_opsc             S   s   g | ]}|d  r|d qS )r   r   r   )r0   r   r   r   r   rf     s    z7MySQLDiff.find_unique_missing_in_db.<locals>.<listcomp>)r   r   r#   r   r   r   r   r   r   r   r   rw   r%   r   r   r   r   )r9   r'   r   r   r   r   r   r   r   r   r%   r   r   r   r   )r   r   r     s6    






z#MySQLDiff.find_unique_missing_in_db)NN)N)r  r  r  rj   rl   r   rn   r{   rk   rm   r  r   r   r   __classcell__r   r   )r  r   r    s   .r  c                   sT   e Zd ZdZdZdd Zdd Zd fdd		Zd
d Zdd Z	d fdd	Z
  ZS )SqliteSQLDiffTFc             C   sP   xJ| j D ]@}d}x6| jd| g D ]"}|||d f}|d  | j|< q"W qW d S )Nr   zPRAGMA table_info('%s');rd   Znotnull)ri   r   r   )r9   r   r   re   r   r   r   r   rk     s
    zSqliteSQLDiff.load_nullc             C   s   d S )Nr   )r9   r   r   r   rm     s    zSqliteSQLDiff.load_unsignedNc                s   |d krg }dd t |D }xN|j D ]B}|d }t|dkr(|d }	|	|kr(|d s`|d r(|j|	 q(W | j|}
tdd |j D }x|
D ]}||kr|j| qW t j|||||d	 d S )
Nc             S   s   g | ]}|j r|jp|jqS r   )r   r   r   )r0   r   r   r   r   rf     s    z;SqliteSQLDiff.find_unique_missing_in_db.<locals>.<listcomp>r   r   r   r   r   c             S   s   g | ]}|d  r|d qS )r   r   r   )r0   r   r   r   r   rf     s    )r   )r#   r   r   r&   r   r   r  r   )r9   r'   r   r   r   r   r   r   r   r   r   r   )r  r   r   r     s    

z'SqliteSQLDiff.find_unique_missing_in_dbc             C   s   d S )Nr   )r9   r'   r   r   r   r   r   r   r     s    z&SqliteSQLDiff.find_index_missing_in_dbc             C   s   d S )Nr   )r9   r'   r   r   r   r   r   r   r     s    z)SqliteSQLDiff.find_index_missing_in_modelc                sP   t  j|||}|sd S |rL| j|}| j|dkrL| j|dkrL|jd}|S )Nr   r   r!  )r  r   r   r   r   )r9   r}   r   r   r%   r   )r  r   r   r     s    

zSqliteSQLDiff.get_field_db_type)N)NN)r  r  r  rj   rl   rk   rm   r   r   r   r   r$  r   r   )r  r   r%    s   
r%  c                   s   e Zd ZdZdZdddZdZdZdd Zd	d Z	d
d Z
 fddZdd Zdd Zdd Zdd Zdd Zdd Zd fdd	Zdd Z  ZS )PostgresqlSQLDiffTz*django.contrib.postgres.fields.HStoreFieldz(django.contrib.postgres.fields.JSONField)ZhstoreZjsonba  
        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          	   C   sL   d|j d|j||d |j d|j||d |j d|j|d f S )Nz%s %s
	%s %s %s %s;zALTER TABLEr   ALTERr   TYPEr4   )r/   r5   r6   r.   )r9   r2   r:   r;   r   r   r   r<     s    zPostgresqlSQLDiff.<lambda>c          	   C   sL   d|j d|j||d |j d|j||d |j d|j|d f S )Nz%s %s
	%s %s %s %s;zALTER TABLEr   r'  r   r(  r4   )r/   r5   r6   r.   )r9   r2   r:   r;   r   r   r   r<     s    c             C   sL   d|j d|j||d |j d|j||d |j |d |j df S )Nz%s %s
	%s %s %s %s;zALTER TABLEr   zALTER COLUMNr   r4   zNOT NULL)r/   r5   r6   )r9   r2   r:   r;   r   r   r   r<     s    c                s   t  j  i | _| j  d S )N)r  rn   check_constraintsload_constraints)r9   )r  r   r   rn   	  s    
zPostgresqlSQLDiff.loadc             C   sB   x<| j | jg D ]*}|d |d |d f}|d  | j|< qW d S )Nnspnamerelnamer   Z
attnotnull)r   SQL_LOAD_NULLr   )r9   r  r   r   r   r   rk     s    zPostgresqlSQLDiff.load_nullc             C   s   d S )Nr   )r9   r   r   r   rm     s    zPostgresqlSQLDiff.load_unsignedc             C   sH   xB| j | jg D ]0}|d |d |d f}d|d kr|| j|< qW d S )Nr+  r,  r   ZCHECKpg_get_constraintdef)r   SQL_LOAD_CONSTRAINTSr)  )r9   r  r   r   r   r   r*    s    z"PostgresqlSQLDiff.load_constraintsc             C   s   dd| j | idS )Nz)django.contrib.postgres.fields.ArrayField
base_field)rd   r   )r   )r9   r0  r   r   r   get_data_type_arrayfield  s    z*PostgresqlSQLDiff.get_data_type_arrayfieldc                s   d fdd fdd fdd fdd fdd fdd fd	d fd
d fdd fdd fdd fdd fdd fdd fdd fdd fdddddS )Nr   c                  s    j ddS )NZBooleanField)r0  )r1  r   )r9   r   r   r<   )  s    zCPostgresqlSQLDiff.get_data_types_reverse_override.<locals>.<lambda>c                  s    j ddS )NZBinaryField)r0  )r1  r   )r9   r   r   r<   *  s    c                  s    j ddS )Nr   )r0  )r1  r   )r9   r   r   r<   +  s    c                  s    j ddS )Nr   )r0  )r1  r   )r9   r   r   r<   ,  s    c                  s    j ddS )Nr   )r0  )r1  r   )r9   r   r   r<   -  s    c                  s    j ddS )Nr   )r0  )r1  r   )r9   r   r   r<   .  s    c                  s    j ddS )Nr   )r0  )r1  r   )r9   r   r   r<   /  s    c                  s    j ddS )Nr   )r0  )r1  r   )r9   r   r   r<   0  s    c                  s    j ddS )Nr   )r0  )r1  r   )r9   r   r   r<   1  s    c                  s    j ddS )NZBigIntegerField)r0  )r1  r   )r9   r   r   r<   2  s    c                  s    j ddS )N
FloatField)r0  )r1  r   )r9   r   r   r<   3  s    c                  s    j ddS )Nr2  )r0  )r1  r   )r9   r   r   r<   4  s    c                  s    j ddS )Nr2  )r0  )r1  r   )r9   r   r   r<   5  s    c                  s    j ddS )NDateTimeField)r0  )r1  r   )r9   r   r   r<   6  s    c                  s    j ddS )Nr3  )r0  )r1  r   )r9   r   r   r<   7  s    c                  s    j ddS )Nr   )r0  )r1  r   )r9   r   r   r<   8  s    c                  s    j ddS )NZDurationField)r0  )r1  r   )r9   r   r   r<   :  s    z0django.contrib.postgres.search.SearchVectorFieldz(django.contrib.postgres.fields.JSONField)i  i  i  i  i  i  i  i  i  i  i  i  i  i  i[  i  i  i  i  i  r   )r9   r   )r9   r   ry   &  s(    
















z1PostgresqlSQLDiff.get_data_types_reverse_overridec             C   s6  i }|j dd|g xx|j D ]l\}}}}||krxg |j dk|j dk|j dkrht|d jddnd	d
d
d||< || d j| qW |j dd|g xD|j D ]8\}}||krg d
d
d	dd
d||< || d j| qW |j d|g x>|j D ]2\}	}
}}|	|k rt|
||d	d
dd||	<  qW |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 keyr   zforeign keyr   r   r   NF)r   r   r   r   r   r   r   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
        )r4  r   )r|   r~   r   r   r   r&   r   )r9   rg   r   rM   r   r   r   kindZ	used_colsr   r   r   Zprimaryr   r   r   r   @  sD    

"

z!PostgresqlSQLDiff.get_constraintsNc       	         s  t  j|||}|sd S |r|jdrf|jp2|j}| jd||fd d }|jdrb|jdd}|S |jrt	|t
r|dkrd}n|d	krd
}|r|j}|dkrd}|jp|j}| jj|||fi jdd }|r|jdd}|jdd}djdd |jdD }|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;
                    r   r   zcharacter varyingr   r#  serialZbigintZ	bigserialr   r   r.  z((r   z))r   z("c             S   s4   g | ],}d |kr,dj dd |jddD p.|qS )r   z" c             s   s   | ]}|j d V  qdS )"N)r   )r0   pr   r   r   r3     s    zAPostgresqlSQLDiff.get_field_db_type.<locals>.<listcomp>.<genexpr>r-   r   )r7   r   )r0   rA   r   r   r   rf     s    z7PostgresqlSQLDiff.get_field_db_type.<locals>.<listcomp>r-   )r  r   endswithr   r   r   r   r  r   r   r   r   r)  r   r7   r   )	r9   r}   r   r   r%   r   Zintrospect_db_typer   Zcheck_constraint)r  r   r   r     s<    


z#PostgresqlSQLDiff.get_field_db_typec             C   sF   y(| j d|gd d }| jj|jdS  ttfk
r@   Y nX d S )Nz-SELECT typname FROM pg_type WHERE typelem=%s;r   Ztypname_)r   DATA_TYPES_REVERSE_NAMEr   r   
IndexErrorr   )r9   r   rd   r   r   r   r     s
    z*PostgresqlSQLDiff.get_field_db_type_lookup)NN)r  r  r  rj   rl   r;  r/  r-  r^   r_   r`   rn   rk   rm   r*  r1  ry   r   r   r   r$  r   r   )r  r   r&    s&   s0r&  )ZpostgisZpostgresql_psycopg2Z
postgresqlZmysqlZsqlite3oraclec                   sT   e Zd ZdZdZ fddZ fddZedd Z fd	d
Z	 fddZ
  ZS )Commanda  Prints 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.Fc                s   t  j| |jddd |jdddddd	d
 |jddddddd
 |jddddddd |jddddddd |jdddddd |jdddddd |jd dd!dtjd d S )"Nrp   *)nargsz--all-applicationsz-a
store_trueFr   z8Automaticly include all application from INSTALLED_APPS.)r   defaultdesthelpz--not-only-existingz-estore_falseTrc   z_Check all tables that exist in the database, not only tables that should exist based on models.z--dense-outputz-drF   zRShows the output in dense format, normally output is spreaded over multiple lines.)r   rC  rB  rD  z--output_textz-tr  z:Outputs the differences as descriptive text instead of SQLz--include-proxy-modelsr   z!Include proxy models in the graphz--include-defaultsr   z3Include default values in SQL output (beta feature)z--migrate-for-testsmigrate_for_tests)r  add_argumentsadd_argumentargparseSUPPRESS)r9   parser)r  r   r   rG    s@    zCommand.add_argumentsc                s   t  j|| d| _d S )Nr   )r  rb   	exit_code)r9   r;   r   )r  r   r   rb   1  s    zCommand.__init__c             O   sb  ddl m} |d }d }t|dr2|jd d }n|j}|dkrHtd|d	 r^tjd
d}nP|sjtdt|t	t
tfs|g}g }x(|D ] }tj|}|j|jd
d qW |std|d }	|	rddlm}
 |
d|d
d
d | stjjdd }d|kr|jdd }tj|t}|||| j| jd}|j  |j  |jsRd| _|j| j d S )Nr   )settingsrp   	DATABASESrB  ZENGINEdummyzDjango 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'.r   T)Zinclude_auto_createdzEnter at least one appname.z+Unable to execute sqldiff no models founds.rF  )call_commandmigrate)no_inputZ
run_syncdbr   r4   r   )rK   rL   )rQ  rs   )Zdjango.confrM  r  rN  ZDATABASE_ENGINEr   r   Z
get_modelsr   r   r   rP   Zget_app_configr"   django.core.managementrP  r   r  r   DATABASE_SQLDIFF_CLASSESr   r  rK   rL   rn   r  rG   rL  r  r2   )r9   r;   rI   rM  Z
app_labelsZenginerH   rp   Z
app_configrF  rP  clsZsqldiff_instancer   r   r   handle5  sH    



zCommand.handlec                s   yt  j|| W nl tk
r~ } zP|d r. t| dd }|sNttj| jj}|j	d|j
j|f  tjd W Y d d }~X nX d S )N	tracebackrL   z%s: %sr4   )r  r|   r   r   r	   sysrL   r2   rE   r  r  r  exit)r9   r;   rI   rA   rL   )r  r   r   r|   k  s    zCommand.executec                s   t  j| tj| j d S )N)r  run_from_argvrY  rZ  rL  )r9   argv)r  r   r   r[  y  s    zCommand.run_from_argv)r  r  r  rD  Zoutput_transactionrG  rb   r   rW  r|   r[  r$  r   r   )r  r   r>    s   )6r>  )+__doc__r   rY  rI  typingr   r   r   r   Zdjango.appsr   rT  r   r   Zdjango.core.management.baser	   Zdjango.core.management.colorr
   Z	django.dbr   r   r   Zdjango.db.modelsr   Zdjango.db.models.fieldsr   r   Zdjango.db.models.optionsr   Z"django_extensions.management.utilsr   r  r   r   r   r#   r*   r  r  r%  r&  rU  r>  r   r   r   r   <module>   sH        ? !>  