1 """

     2 sqldiff.py - Prints the (approximated) difference between models and database

     3 

     4 TODO:

     5  - better support for relations

     6  - better support for constraints (mainly postgresql?)

     7  - support for table spaces with postgresql

     8  

     9 KNOWN ISSUES:

    10  - MySQL has by far the most problems with introspection. Please be

    11    carefull when using MySQL with sqldiff. 

    12    - Booleans are reported back as Integers, so there's know way to know if

    13      there was a real change.

    14    - Varchar sizes are reported back without unicode support so there size

    15      may change in comparison to the real length of the varchar.   

    16    - Some of the 'fixes' to counter these problems might create false 

    17      positives or false negatives.

    18 """

    19 

    20 from django.core.management.base import BaseCommand

    21 from django.core.management import sql as _sql

    22 from django.core.management import CommandError

    23 from django.core.management.color import no_style

    24 from django.db import transaction, connection

    25 from django.db.models.fields import IntegerField

    26 from optparse import make_option

    27 

    28 ORDERING_FIELD = IntegerField('_order', null=True)

    29 

    30 def flatten(l, ltypes=(list, tuple)):

    31     ltype = type(l)

    32     l = list(l)

    33     i = 0

    34     while i < len(l):

    35         while isinstance(l[i], ltypes):

    36             if not l[i]:

    37                 l.pop(i)

    38                 i -= 1

    39                 break

    40             else:

    41                 l[i:i + 1] = l[i]

    42         i += 1

    43     return ltype(l)

    44 

    45 class SQLDiff(object):

    46     DATA_TYPES_REVERSE_OVERRIDE = {

    47     }

    48     

    49     DIFF_TYPES = [

    50         'comment',

    51         'table-missing-in-db',

    52         'field-missing-in-db',

    53         'field-missing-in-model',

    54         'index-missing-in-db',

    55         'index-missing-in-model',

    56         'unique-missing-in-db',

    57         'unique-missing-in-model',

    58         'field-type-differ',

    59         'field-parameter-differ',

    60     ]

    61     DIFF_TEXTS = {

    62         'comment': 'comment: %(0)s',

    63         'table-missing-in-db': "table '%(0)s' missing in database",

    64         'field-missing-in-db' : "field '%(1)s' defined in model but missing in database",

    65         'field-missing-in-model' : "field '%(1)s' defined in database but missing in model",

    66         'index-missing-in-db' : "field '%(1)s' INDEX defined in model but missing in database",

    67         'index-missing-in-model' : "field '%(1)s' INDEX defined in database schema but missing in model",

    68         'unique-missing-in-db' : "field '%(1)s' UNIQUE defined in model but missing in database",

    69         'unique-missing-in-model' : "field '%(1)s' UNIQUE defined in database schema but missing in model",

    70         'field-type-differ' : "field '%(1)s' not of same type: db='%(3)s', model='%(2)s'",

    71         'field-parameter-differ' : "field '%(1)s' parameters differ: db='%(3)s', model='%(2)s'",

    72     }

    73 

    74     SQL_FIELD_MISSING_IN_DB = lambda self, style, qn, args: "%s %s\n\t%s %s %s;" % (style.SQL_KEYWORD('ALTER TABLE'), style.SQL_TABLE(qn(args[0])), style.SQL_KEYWORD('ADD'), style.SQL_FIELD(qn(args[1])), style.SQL_COLTYPE(args[2]))

    75     SQL_FIELD_MISSING_IN_MODEL = lambda self, style, qn, args: "%s %s\n\t%s %s;" % (style.SQL_KEYWORD('ALTER TABLE'), style.SQL_TABLE(qn(args[0])), style.SQL_KEYWORD('DROP COLUMN'), style.SQL_FIELD(qn(args[1])))

    76     SQL_INDEX_MISSING_IN_DB = lambda self, style, qn, args: "%s %s\n\t%s %s (%s);" % (style.SQL_KEYWORD('CREATE INDEX'), style.SQL_TABLE(qn("%s_idx" % '_'.join(args[0:2]))), style.SQL_KEYWORD('ON'), style.SQL_TABLE(qn(args[0])), style.SQL_FIELD(qn(args[1])))

    77     # FIXME: need to lookup index name instead of just appending _idx to table + fieldname

    78     SQL_INDEX_MISSING_IN_MODEL = lambda self, style, qn, args: "%s %s;" % (style.SQL_KEYWORD('DROP INDEX'), style.SQL_TABLE(qn("%s_idx" % '_'.join(args[0:2]))))

    79     SQL_UNIQUE_MISSING_IN_DB = lambda self, style, qn, args: "%s %s\n\t%s %s (%s);" % (style.SQL_KEYWORD('ALTER TABLE'), style.SQL_TABLE(qn(args[0])), style.SQL_KEYWORD('ADD'), style.SQL_KEYWORD('UNIQUE'), style.SQL_FIELD(qn(args[1])))

    80     # FIXME: need to lookup unique constraint name instead of appending _key to table + fieldname

    81     SQL_UNIQUE_MISSING_IN_MODEL = lambda self, style, qn, args: "%s %s\n\t%s %s %s;" % (style.SQL_KEYWORD('ALTER TABLE'), style.SQL_TABLE(qn(args[0])), style.SQL_KEYWORD('DROP'), style.SQL_KEYWORD('CONSTRAINT'), style.SQL_TABLE(qn("%s_key" % ('_'.join(args[:2])))))

    82     SQL_FIELD_TYPE_DIFFER = lambda self, style, qn, args:  "%s %s\n\t%s %s %s;" % (style.SQL_KEYWORD('ALTER TABLE'), style.SQL_TABLE(qn(args[0])), style.SQL_KEYWORD("MODIFY"), style.SQL_FIELD(qn(args[1])), style.SQL_COLTYPE(args[2]))

    83     SQL_FIELD_PARAMETER_DIFFER = lambda self, style, qn, args:  "%s %s\n\t%s %s %s;" % (style.SQL_KEYWORD('ALTER TABLE'), style.SQL_TABLE(qn(args[0])), style.SQL_KEYWORD("MODIFY"), style.SQL_FIELD(qn(args[1])), style.SQL_COLTYPE(args[2]))

    84     SQL_COMMENT = lambda self, style, qn, args: style.NOTICE('-- Comment: %s' % style.SQL_TABLE(args[0]))

    85     SQL_TABLE_MISSING_IN_DB = lambda self, style, qn, args: style.NOTICE('-- Table missing: %s' % args[0])

    86 

    87     def __init__(self, app_models, options):

    88         self.app_models = app_models

    89         self.options = options

    90         self.dense = options.get('dense_output', False)

    91 

    92         try:

    93             self.introspection = connection.introspection

    94         except AttributeError:

    95             from django.db import get_introspection_module

    96             self.introspection = get_introspection_module()

    97 

    98         self.cursor = connection.cursor()

    99         self.django_tables = self.get_django_tables(options.get('only_existing', True))

   100         self.db_tables = self.introspection.get_table_list(self.cursor)

   101         self.differences = []

   102         self.unknown_db_fields = {}

   103         

   104         self.DIFF_SQL = {

   105             'comment': self.SQL_COMMENT,

   106             'table-missing-in-db': self.SQL_TABLE_MISSING_IN_DB,

   107             'field-missing-in-db': self.SQL_FIELD_MISSING_IN_DB,

   108             'field-missing-in-model': self.SQL_FIELD_MISSING_IN_MODEL,

   109             'index-missing-in-db': self.SQL_INDEX_MISSING_IN_DB,

   110             'index-missing-in-model': self.SQL_INDEX_MISSING_IN_MODEL,

   111             'unique-missing-in-db': self.SQL_UNIQUE_MISSING_IN_DB,

   112             'unique-missing-in-model': self.SQL_UNIQUE_MISSING_IN_MODEL,

   113             'field-type-differ': self.SQL_FIELD_TYPE_DIFFER,

   114             'field-parameter-differ': self.SQL_FIELD_PARAMETER_DIFFER,

   115         }

   116 

   117 

   118     def add_app_model_marker(self, app_label, model_name):

   119         self.differences.append((app_label, model_name, []))

   120         

   121     def add_difference(self, diff_type, *args):

   122         assert diff_type in self.DIFF_TYPES, 'Unknown difference type'

   123         self.differences[-1][-1].append((diff_type, args))

   124 

   125     def get_django_tables(self, only_existing):

   126         try:

   127             django_tables = self.introspection.django_table_names(only_existing=only_existing)

   128         except AttributeError:

   129             # backwards compatibility for before introspection refactoring (r8296)

   130             try:

   131                 django_tables = _sql.django_table_names(only_existing=only_existing)

   132             except AttributeError:

   133                 # backwards compatibility for before svn r7568

   134                 django_tables = _sql.django_table_list(only_existing=only_existing)

   135         return django_tables

   136 

   137     def sql_to_dict(self, query,param):

   138         """ sql_to_dict(query, param) -> list of dicts

   139 

   140         code from snippet at http://www.djangosnippets.org/snippets/1383/

   141         """

   142         cursor = connection.cursor()

   143         cursor.execute(query,param)

   144         fieldnames = [name[0] for name in cursor.description]

   145         result = []

   146         for row in cursor.fetchall():

   147             rowset = []

   148             for field in zip(fieldnames, row):

   149                 rowset.append(field)

   150             result.append(dict(rowset))

   151         return result

   152 

   153     def get_field_model_type(self, field):

   154         return field.db_type()

   155 

   156     def get_field_db_type(self, description, field=None, table_name=None):

   157         from django.db import models

   158         # DB-API cursor.description

   159         #(name, type_code, display_size, internal_size, precision, scale, null_ok) = description

   160         type_code = description[1]

   161         if type_code in self.DATA_TYPES_REVERSE_OVERRIDE:

   162             reverse_type = self.DATA_TYPES_REVERSE_OVERRIDE[type_code]

   163         else:

   164             try:

   165                 try:

   166                     reverse_type = self.introspection.data_types_reverse[type_code]

   167                 except AttributeError:

   168                     # backwards compatibility for before introspection refactoring (r8296)

   169                     reverse_type = self.introspection.DATA_TYPES_REVERSE.get(type_code)

   170             except KeyError:

   171                 # type_code not found in data_types_reverse map

   172                 key = (self.differences[-1][:2], description[:2])

   173                 if key not in self.unknown_db_fields:

   174                     self.unknown_db_fields[key] = 1

   175                     self.add_difference('comment', "Unknown database type for field '%s' (%s)" % (description[0], type_code))

   176                 return None

   177         

   178         kwargs = {}

   179         if isinstance(reverse_type, tuple):

   180             kwargs.update(reverse_type[1])

   181             reverse_type = reverse_type[0]

   182 

   183         if reverse_type == "CharField" and description[3]:

   184             kwargs['max_length'] = description[3]

   185 

   186         if reverse_type == "DecimalField":

   187             kwargs['max_digits'] = description[4]

   188             kwargs['decimal_places'] = description[5]

   189 

   190         if description[6]:

   191             kwargs['blank'] = True

   192             if not reverse_type in ('TextField', 'CharField'):

   193                 kwargs['null'] = True

   194 

   195         field_db_type = getattr(models, reverse_type)(**kwargs).db_type()

   196         return field_db_type

   197 

   198     def strip_parameters(self, field_type):

   199         if field_type:

   200             return field_type.split(" ")[0].split("(")[0]

   201         return field_type

   202 

   203     def find_unique_missing_in_db(self, meta, table_indexes, table_name):

   204         for field in meta.fields:

   205             if field.unique:

   206                 attname = field.db_column or field.attname

   207                 if attname in table_indexes and table_indexes[attname]['unique']:

   208                     continue

   209                 self.add_difference('unique-missing-in-db', table_name, attname)

   210 

   211     def find_unique_missing_in_model(self, meta, table_indexes, table_name):

   212         # TODO: Postgresql does not list unique_togethers in table_indexes

   213         #       MySQL does

   214         fields = dict([(field.db_column or field.name, field.unique) for field in meta.fields])

   215         for att_name, att_opts in table_indexes.iteritems():

   216             if att_opts['unique'] and att_name in fields and not fields[att_name]:

   217                 if att_name in flatten(meta.unique_together): continue

   218                 self.add_difference('unique-missing-in-model', table_name, att_name)

   219 

   220     def find_index_missing_in_db(self, meta, table_indexes, table_name):

   221         for field in meta.fields:

   222             if field.db_index:

   223                 attname = field.db_column or field.attname

   224                 if not attname in table_indexes:

   225                     self.add_difference('index-missing-in-db', table_name, attname)

   226 

   227     def find_index_missing_in_model(self, meta, table_indexes, table_name):

   228         fields = dict([(field.name, field) for field in meta.fields])

   229         for att_name, att_opts in table_indexes.iteritems():

   230             if att_name in fields:

   231                 field = fields[att_name]

   232                 if field.db_index: continue

   233                 if att_opts['primary_key'] and field.primary_key: continue

   234                 if att_opts['unique'] and field.unique: continue

   235                 if att_opts['unique'] and att_name in flatten(meta.unique_together): continue

   236                 self.add_difference('index-missing-in-model', table_name, att_name)

   237 

   238     def find_field_missing_in_model(self, fieldmap, table_description, table_name):

   239         for row in table_description:

   240             if row[0] not in fieldmap:

   241                 self.add_difference('field-missing-in-model', table_name, row[0])

   242 

   243     def find_field_missing_in_db(self, fieldmap, table_description, table_name):

   244         db_fields = [row[0] for row in table_description]

   245         for field_name, field in fieldmap.iteritems():

   246             if field_name not in db_fields:

   247                 self.add_difference('field-missing-in-db', table_name, field_name, field.db_type())

   248 

   249     def find_field_type_differ(self, meta, table_description, table_name, func=None):

   250         db_fields = dict([(row[0], row) for row in table_description])

   251         for field in meta.fields:

   252             if field.name not in db_fields: continue

   253             description = db_fields[field.name]

   254 

   255             model_type = self.strip_parameters(self.get_field_model_type(field))

   256             db_type = self.strip_parameters(self.get_field_db_type(description, field))

   257 

   258             # use callback function if defined

   259             if func:

   260                 model_type, db_type = func(field, description, model_type, db_type)

   261 

   262             if not model_type==db_type:

   263                 self.add_difference('field-type-differ', table_name, field.name, model_type, db_type)

   264 

   265     def find_field_parameter_differ(self, meta, table_description, table_name, func=None):

   266         db_fields = dict([(row[0], row) for row in table_description])

   267         for field in meta.fields:

   268             if field.name not in db_fields: continue

   269             description = db_fields[field.name]

   270 

   271             model_type = self.get_field_model_type(field)

   272             db_type = self.get_field_db_type(description, field, table_name)

   273 

   274             if not self.strip_parameters(model_type)==self.strip_parameters(db_type):

   275                 continue

   276             

   277             # use callback function if defined

   278             if func:

   279                 model_type, db_type = func(field, description, model_type, db_type)

   280 

   281             if not model_type==db_type:

   282                 self.add_difference('field-parameter-differ', table_name, field.name, model_type, db_type)

   283     

   284     @transaction.commit_manually

   285     def find_differences(self):

   286         cur_app_label = None

   287         for app_model in self.app_models:

   288             meta = app_model._meta

   289             table_name = meta.db_table

   290             app_label = meta.app_label

   291 

   292             if cur_app_label!=app_label:

   293                 # Marker indicating start of difference scan for this table_name

   294                 self.add_app_model_marker(app_label, app_model.__name__)

   295 

   296             #if not table_name in self.django_tables:

   297             if not table_name in self.db_tables:

   298                 # Table is missing from database

   299                 self.add_difference('table-missing-in-db', table_name)

   300                 continue

   301             

   302             table_indexes = self.introspection.get_indexes(self.cursor, table_name)

   303             fieldmap = dict([(field.db_column or field.get_attname(), field) for field in meta.fields])

   304             

   305             # add ordering field if model uses order_with_respect_to

   306             if meta.order_with_respect_to:

   307                 fieldmap['_order'] = ORDERING_FIELD

   308 

   309             try:

   310                 table_description = self.introspection.get_table_description(self.cursor, table_name)

   311             except Exception, e:

   312                 model_diffs.append((app_model.__name__, [str(e).strip()]))

   313                 transaction.rollback() # reset transaction

   314                 continue

   315             

   316             # Fields which are defined in database but not in model

   317             # 1) find: 'unique-missing-in-model'

   318             self.find_unique_missing_in_model(meta, table_indexes, table_name)

   319             # 2) find: 'index-missing-in-model'

   320             self.find_index_missing_in_model(meta, table_indexes, table_name)

   321             # 3) find: 'field-missing-in-model'

   322             self.find_field_missing_in_model(fieldmap, table_description, table_name)

   323 

   324             # Fields which are defined in models but not in database

   325             # 4) find: 'field-missing-in-db'

   326             self.find_field_missing_in_db(fieldmap, table_description, table_name)

   327             # 5) find: 'unique-missing-in-db'

   328             self.find_unique_missing_in_db(meta, table_indexes, table_name)

   329             # 6) find: 'index-missing-in-db'

   330             self.find_index_missing_in_db(meta, table_indexes, table_name)

   331 

   332             # Fields which have a different type or parameters

   333             # 7) find: 'type-differs'

   334             self.find_field_type_differ(meta, table_description, table_name)

   335             # 8) find: 'type-parameter-differs'

   336             self.find_field_parameter_differ(meta, table_description, table_name)

   337 

   338     def print_diff(self, style=no_style()):

   339         """ print differences to stdout """

   340         if self.options.get('sql', True):

   341             self.print_diff_sql(style)

   342         else:

   343             self.print_diff_text(style)

   344 

   345     def print_diff_text(self, style):

   346         cur_app_label = None

   347         for app_label, model_name, diffs in self.differences:

   348             if not diffs: continue

   349             if not self.dense and cur_app_label != app_label:

   350                 print style.NOTICE("+ Application:"), style.SQL_TABLE(app_label)

   351                 cur_app_label = app_label

   352             if not self.dense:

   353                 print style.NOTICE("|-+ Differences for model:"), style.SQL_TABLE(model_name)

   354             for diff in diffs:

   355                 diff_type, diff_args = diff

   356                 text = self.DIFF_TEXTS[diff_type] % dict((str(i), style.SQL_TABLE(e)) for i, e in enumerate(diff_args))

   357                 text = "'".join(i%2==0 and style.ERROR_OUTPUT(e) or e for i, e in enumerate(text.split("'")))

   358                 if not self.dense:

   359                     print style.NOTICE("|--+"), text

   360                 else:

   361                     print style.NOTICE("App"), style.SQL_TABLE(app_name), style.NOTICE('Model'), style.SQL_TABLE(model_name), text

   362 

   363     def print_diff_sql(self, style):

   364         cur_app_label = None

   365         qn = connection.ops.quote_name

   366         print style.SQL_KEYWORD("BEGIN;")

   367         for app_label, model_name, diffs in self.differences:

   368             if not diffs: continue

   369             if not self.dense and cur_app_label != app_label:

   370                 print style.NOTICE("-- Application: %s" % style.SQL_TABLE(app_label))

   371                 cur_app_label = app_label

   372             if not self.dense:

   373                 print style.NOTICE("-- Model: %s" % style.SQL_TABLE(model_name))

   374             for diff in diffs:

   375                 diff_type, diff_args = diff

   376                 text = self.DIFF_SQL[diff_type](style, qn, diff_args)

   377                 if self.dense:

   378                     text = text.replace("\n\t", " ")

   379                 print text

   380         print style.SQL_KEYWORD("COMMIT;")

   381         

   382 class GenericSQLDiff(SQLDiff):

   383     pass

   384 

   385 class MySQLDiff(SQLDiff):

   386     # All the MySQL hacks together create something of a problem

   387     # Fixing one bug in MySQL creates another issue. So just keep in mind

   388     # that this is way unreliable for MySQL atm.

   389     def get_field_db_type(self, description, field=None, table_name=None):

   390         from MySQLdb.constants import FIELD_TYPE

   391         # weird bug? in mysql db-api where it returns three times the correct value for field length

   392         # if i remember correctly it had something todo with unicode strings

   393         # TODO: Fix this is a more meaningful and better understood manner

   394         description = list(description)

   395         if description[1] not in [FIELD_TYPE.TINY, FIELD_TYPE.SHORT]: # exclude tinyints from conversion.

   396             description[3] = description[3]/3

   397             description[4] = description[4]/3

   398         db_type = super(MySQLDiff, self).get_field_db_type(description)

   399         if not db_type:

   400             return

   401         if field:

   402             if field.primary_key and db_type=='integer':

   403                 db_type += ' AUTO_INCREMENT'

   404             # MySQL isn't really sure about char's and varchar's like sqlite

   405             field_type = self.get_field_model_type(field)

   406             # Fix char/varchar inconsistencies

   407             if self.strip_parameters(field_type)=='char' and self.strip_parameters(db_type)=='varchar':

   408                 db_type = db_type.lstrip("var")

   409             # They like to call 'bool's 'tinyint(1)' and introspection makes that a integer

   410             # just convert it back to it's proper type, a bool is a bool and nothing else.

   411             if db_type=='integer' and description[1]==FIELD_TYPE.TINY and description[4]==1:

   412                 db_type = 'bool'

   413             if db_type=='integer' and description[1]==FIELD_TYPE.SHORT:

   414                 db_type = 'smallint UNSIGNED' # FIXME: what about if it's not UNSIGNED ?

   415         return db_type

   416 

   417 class SqliteSQLDiff(SQLDiff):

   418     # Unique does not seem to be implied on Sqlite for Primary_key's

   419     # if this is more generic among databases this might be usefull

   420     # to add to the superclass's find_unique_missing_in_db method

   421     def find_unique_missing_in_db(self, meta, table_indexes, table_name):

   422         for field in meta.fields:

   423             if field.unique:

   424                 attname = field.attname

   425                 if attname in table_indexes and table_indexes[attname]['unique']:

   426                     continue

   427                 if table_indexes[attname]['primary_key']:

   428                     continue

   429                 self.add_difference('unique-missing-in-db', table_name, attname)

   430 

   431     # Finding Indexes by using the get_indexes dictionary doesn't seem to work

   432     # for sqlite.

   433     def find_index_missing_in_db(self, meta, table_indexes, table_name):

   434         pass

   435 

   436     def find_index_missing_in_model(self, meta, table_indexes, table_name):

   437         pass

   438 

   439     def get_field_db_type(self, description, field=None, table_name=None):

   440         db_type = super(SqliteSQLDiff, self).get_field_db_type(description)

   441         if not db_type:

   442             return

   443         if field:

   444             field_type = self.get_field_model_type(field)

   445             # Fix char/varchar inconsistencies

   446             if self.strip_parameters(field_type)=='char' and self.strip_parameters(db_type)=='varchar':

   447                 db_type = db_type.lstrip("var")

   448         return db_type

   449 

   450 class PostgresqlSQLDiff(SQLDiff):

   451     DATA_TYPES_REVERSE_OVERRIDE = {

   452         20: 'IntegerField',

   453         1042: 'CharField',

   454     }

   455 

   456     # Hopefully in the future we can add constraint checking and other more

   457     # advanced checks based on this database.

   458     SQL_LOAD_CONSTRAINTS = """

   459     SELECT nspname, relname, conname, attname, pg_get_constraintdef(pg_constraint.oid)

   460     FROM pg_constraint

   461     INNER JOIN pg_attribute ON pg_constraint.conrelid = pg_attribute.attrelid AND pg_attribute.attnum = any(pg_constraint.conkey)

   462     INNER JOIN pg_class ON conrelid=pg_class.oid

   463     INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace

   464     ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END,contype,nspname,relname,conname;

   465     """

   466 

   467     SQL_FIELD_TYPE_DIFFER = lambda self, style, qn, args:  "%s %s\n\t%s %s %s %s;" % (style.SQL_KEYWORD('ALTER TABLE'), style.SQL_TABLE(qn(args[0])), style.SQL_KEYWORD('ALTER'), style.SQL_FIELD(qn(args[1])), style.SQL_KEYWORD("TYPE"), style.SQL_COLTYPE(args[2]))

   468     SQL_FIELD_PARAMETER_DIFFER = lambda self, style, qn, args:  "%s %s\n\t%s %s %s %s;" % (style.SQL_KEYWORD('ALTER TABLE'), style.SQL_TABLE(qn(args[0])), style.SQL_KEYWORD('ALTER'), style.SQL_FIELD(qn(args[1])), style.SQL_KEYWORD("TYPE"), style.SQL_COLTYPE(args[2]))

   469 

   470     def __init__(self, app_models, options):

   471         SQLDiff.__init__(self, app_models, options)

   472         self.check_constraints = {}

   473         self.load_constraints()

   474 

   475     def load_constraints(self):

   476         for dct in self.sql_to_dict(self.SQL_LOAD_CONSTRAINTS, []):

   477             key = (dct['nspname'], dct['relname'], dct['attname'])

   478             if 'CHECK' in dct['pg_get_constraintdef']:

   479                 self.check_constraints[key] = dct

   480 

   481     def get_field_db_type(self, description, field=None, table_name=None):

   482         db_type = super(PostgresqlSQLDiff, self).get_field_db_type(description)

   483         if not db_type:

   484             return

   485         if field:

   486             if field.primary_key and db_type=='integer':

   487                 db_type = 'serial'

   488             if table_name:

   489                 tablespace = field.db_tablespace

   490                 if tablespace=="":

   491                     tablespace = "public"

   492                 check_constraint = self.check_constraints.get((tablespace, table_name, field.attname),{}).get('pg_get_constraintdef', None)

   493                 if check_constraint:

   494                     check_constraint = check_constraint.replace("((", "(")

   495                     check_constraint = check_constraint.replace("))", ")")

   496                     check_constraint = '("'.join([')' in e and '" '.join(e.split(" ", 1)) or e for e in check_constraint.split("(")])

   497                     # TODO: might be more then one constraint in definition ?

   498                     db_type += ' '+check_constraint

   499         return db_type

   500 

   501     """

   502     def find_field_type_differ(self, meta, table_description, table_name):

   503         def callback(field, description, model_type, db_type):

   504             if field.primary_key and db_type=='integer':

   505                 db_type = 'serial'

   506             return model_type, db_type

   507         super(PostgresqlSQLDiff, self).find_field_type_differs(meta, table_description, table_name, callback)

   508     """

   509 

   510 DATABASE_SQLDIFF_CLASSES = {

   511     'postgresql_psycopg2' : PostgresqlSQLDiff,

   512     'postgresql': PostgresqlSQLDiff,

   513     'mysql': MySQLDiff,

   514     'sqlite3': SqliteSQLDiff,

   515     'oracle': GenericSQLDiff

   516 }

   517 

   518 class Command(BaseCommand):

   519     option_list = BaseCommand.option_list + (

   520         make_option('--all-applications', '-a', action='store_true', dest='all_applications',

   521                     help="Automaticly include all application from INSTALLED_APPS."),

   522         make_option('--not-only-existing', '-e', action='store_false', dest='only_existing',

   523                     help="Check all tables that exist in the database, not only tables that should exist based on models."),

   524         make_option('--dense-output', '-d', action='store_true', dest='dense_output',

   525                     help="Shows the output in dense format, normally output is spreaded over multiple lines."),

   526         make_option('--output_text', '-t', action='store_false', dest='sql', default=True,

   527                     help="Outputs the differences as descriptive text instead of SQL"),

   528     )

   529     

   530     help = """Prints the (approximated) difference between models and fields in the database for the given app name(s).

   531 

   532 It indicates how columns in the database are different from the sql that would

   533 be generated by Django. This command is not a database migration tool. (Though

   534 it can certainly help) It's purpose is to show the current differences as a way

   535 to check/debug ur models compared to the real database tables and columns."""

   536 

   537     output_transaction = False

   538     args = '<appname appname ...>'

   539 

   540     def handle(self, *app_labels, **options):

   541         from django.db import models

   542         from django.conf import settings

   543 

   544         if settings.DATABASE_ENGINE =='dummy':

   545             # This must be the "dummy" database backend, which means the user

   546             # hasn't set DATABASE_ENGINE.

   547             raise CommandError("Django doesn't know which syntax to use for your SQL statements,\n" +

   548                 "because you haven't specified the DATABASE_ENGINE setting.\n" +

   549                 "Edit your settings file and change DATABASE_ENGINE to something like 'postgresql' or 'mysql'.")

   550 

   551         if options.get('all_applications', False):

   552             app_models = models.get_models()

   553         else:

   554             if not app_labels:

   555                 raise CommandError('Enter at least one appname.')

   556             try:

   557                 app_list = [models.get_app(app_label) for app_label in app_labels]

   558             except (models.ImproperlyConfigured, ImportError), e:

   559                 raise CommandError("%s. Are you sure your INSTALLED_APPS setting is correct?" % e)

   560 

   561             app_models = []

   562             for app in app_list:

   563                 app_models.extend(models.get_models(app))

   564 

   565         if not app_models:

   566             raise CommandError('Unable to execute sqldiff no models founds.')

   567 

   568         cls = DATABASE_SQLDIFF_CLASSES.get(settings.DATABASE_ENGINE, GenericSQLDiff)

   569         sqldiff_instance = cls(app_models, options)

   570         sqldiff_instance.find_differences()

   571         sqldiff_instance.print_diff(self.style)

   572         return