Kaarsemaker.net

  1 # Copyright (c) 2010 Dennis Kaarsemaker <dennis@kaarsemaker.net>
  2 # All rights reserved.
  3 # 
  4 # django-admin.py sqlfixtables -- outputs SQL statements to make tables match reality
  5 # See its help output for details
  6 #
  7 # Redistribution and use in source and binary forms, with or without modification,
  8 # are permitted provided that the following conditions are met:
  9 # 
 10 #     1. Redistributions of source code must retain the above copyright notice, 
 11 #        this list of conditions and the following disclaimer.
 12 #     
 13 #     2. Redistributions in binary form must reproduce the above copyright 
 14 #        notice, this list of conditions and the following disclaimer in the
 15 #        documentation and/or other materials provided with the distribution.
 16 # 
 17 #     3. Neither the name of Django nor the names of its contributors may be used
 18 #        to endorse or promote products derived from this software without
 19 #        specific prior written permission.
 20 # 
 21 # THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
 22 # ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
 23 # WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 24 # DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR
 25 # ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 26 # (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 27 # LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
 28 # ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 29 # (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 30 # SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 31 
 32 from django import get_version
 33 from django.core.management.base import AppCommand
 34 from django.conf import settings
 35 from django.db import connection, models
 36 from optparse import make_option
 37 import re
 38 import sys
 39 
 40 # Since this thing relies on django internals that are not guaranteed to be stable,
 41 # do a strict version check.
 42 COMPAT_MIN = '1.0'
 43 COMPAT_MAX = '1.1.9999'
 44 
 45 class Command(AppCommand):
 46     help = """Print SQL statements for model changes, including
 47 - ALTER TABLE to add/drop columns
 48 - ALTER TABLE to change the length of VARCHAR fields
 49 - ALTER TABLE to change default values
 50 - CREATE INDEX for new ForeignKey and OneToOne fields
 51 - CREATE TABLE for new m2m relations
 52 
 53 Not (yet) supported:
 54 - Databases other than MySQL (sqlite will never be supported, others can be)
 55 - Changes in unique_together
 56 - Field type changes (they will be detected and warned about though)
 57 - Index additions/removals
 58 - Django 1.2+
 59 - Deleting old m2m tables
 60 - Parent changes in multi-table inheritance
 61 
 62 If you need those, a complete migration framework like django-south is a
 63 better option for you.
 64 """
 65     output_transaction = True
 66     option_list = AppCommand.option_list + (
 67         make_option('--drop-columns', dest="drop_columns", action="store_true", default=False,
 68                     help="Drop columns that no longer exist in the model"),
 69     )
 70 
 71     def handle_app(self, app, **options):
 72         version = get_version()
 73         if version < COMPAT_MIN or version > COMPAT_MAX:
 74             print "This command is not compatible with django version %s" % version
 75             sys.exit(1)
 76         return u'\n'.join(sql_fix_table(app, options['drop_columns'],  self.style)).encode('utf-8')
 77 
 78 def sql_fix_table(app, drop_columns, style):
 79     if settings.DATABASE_ENGINE == 'dummy':
 80         # This must be the "dummy" database backend, which means the user
 81         # hasn't set DATABASE_ENGINE.
 82         raise CommandError("Django doesn't know which syntax to use for your SQL statements,\n" +
 83             "because you haven't specified the DATABASE_ENGINE setting.\n" +
 84             "Edit your settings file and change DATABASE_ENGINE to something like 'postgresql' or 'mysql'.")
 85     if settings.DATABASE_ENGINE != 'mysql':
 86         raise CommandError("This has only been tested with MySQL and probably doesn't work for others")
 87 
 88     all_models = models.get_models()
 89     app_models = models.get_models(app)
 90     pending_references = {}
 91     final_output = []
 92 
 93     # Fix up tables
 94     for model in app_models:
 95         # Handle opts.unique_together (removals) TODO.
 96         sql, references = sql_alter_table(connection, model, style, all_models, drop_columns)
 97         # Handle opts.unique_together (additions) TODO.
 98         # Handle pending references
 99         for refto, refs in references.items():
100             pending_references.setdefault(refto, []).extend(refs)
101             if refto in all_models:
102                 sql.extend(connection.creation.sql_for_pending_references(refto, style, pending_references))
103         sql.extend(sql_new_many_to_many(connection, model, style, connection.introspection.table_names()))
104 
105         final_output.extend(sql) 
106     return final_output
107 
108 def sql_alter_table(connection, model, style, known_models, drop_columns):
109     # Do nothing fo unmanaged models
110     opts = model._meta
111     if not opts.managed or opts.proxy:
112         return [], {}
113 
114     qn = connection.ops.quote_name
115     fields = dict([(x.column, x) for x in opts.local_fields])
116     cursor = connection.cursor()
117     cursor.execute('DESCRIBE %s' % qn(opts.db_table))
118     final_output = []
119     m2m_sql = []
120     pending_references = {}
121 
122     for f_name, f_type, f_null, f_key, f_default, f_extra in cursor.fetchall():
123         # Skip _ptr_id fields. Parent handling not yet implemented. TODO
124         if f_name.endswith('_ptr_id'):
125             continue
126 
127         field = fields.pop(f_name, None)
128 
129         # Drop columns no longer relevant
130         if not field:
131             final_output.append(style.NOTICE('-- Field %s no longer exists in the model' % f_name))
132             if drop_columns:
133                 final_output.append(' '.join([style.SQL_KEYWORD('ALTER TABLE'),
134                     style.SQL_TABLE(qn(opts.db_table)),
135                     style.SQL_KEYWORD('DROP COLUMN'),
136                     style.SQL_FIELD(qn(f_name)),
137                     ]) + ';')
138 
139         # Fix up a few things
140         modify_column = False
141 
142         # varchar size
143         n_type = field.db_type().lower()
144         f_type_l = f_type.lower()
145         if not are_equivalent(f_type_l, n_type):
146             modify_column = True
147             if f_type_l[:f_type_l.find('(')] != n_type[:n_type.find('(')]:
148                 final_output.append(
149                         style.NOTICE('-- Field %s.%s type changed from %s to %s, this cannot be fixed automatically' %
150                         (model.__name__, field.name, f_type, field.db_type())))
151                 continue
152             final_output.append(style.NOTICE('-- Field %s.%s type changed from %s to %s' %
153                                 (model.__name__, field.name, f_type, field.db_type())))
154 
155         # null/not null
156         if (f_null.lower() == 'yes') != field.null:
157             final_output.append(style.NOTICE('-- Field %s.%s changed nullness requirements' % (model.__name__, field.name)))
158             modify_column = True
159         if (f_key.lower() in ('uni','pri')) != field.unique:
160             final_output.append(style.NOTICE('-- Field %s.%s changed uniqueness requirements' % (model.__name__, field.name)))
161             modify_column = True
162 
163         if modify_column:
164             field_output = [style.SQL_KEYWORD('ALTER TABLE'),
165                 style.SQL_TABLE(qn(opts.db_table)),
166                 style.SQL_KEYWORD('MODIFY COLUMN'),
167                 style.SQL_FIELD(qn(f_name)),
168                 style.SQL_COLTYPE(field.db_type()),
169                 ]
170             if not field.null:
171                 field_output.append(style.SQL_KEYWORD('NOT NULL'))
172             elif field.unique:
173                 field_output.append(style.SQL_KEYWORD('UNIQUE'))
174             final_output.append(' '.join(field_output)+';')
175 
176     # Create new columns
177     for f in fields.values():
178         if f.name.endswith('_ptr'):
179             # TODO: parent handling
180             continue
181         col_type = f.db_type()
182         tablespace = f.db_tablespace or opts.db_tablespace
183 
184         # Make the definition (e.g. 'foo VARCHAR(30)') for this field.
185         field_output = [style.SQL_KEYWORD('ALTER TABLE'),
186             style.SQL_TABLE(qn(opts.db_table)),
187             style.SQL_KEYWORD('ADD COLUMN'),
188             style.SQL_FIELD(qn(f.column)),
189             style.SQL_COLTYPE(col_type)]
190         if not f.null:
191             field_output.append(style.SQL_KEYWORD('NOT NULL'))
192         if f.primary_key:
193             field_output.append(style.SQL_KEYWORD('PRIMARY KEY'))
194         elif f.unique:
195             field_output.append(style.SQL_KEYWORD('UNIQUE'))
196         if tablespace and f.unique:
197             # We must specify the index tablespace inline, because we
198             # won't be generating a CREATE INDEX statement for this field.
199             field_output.append(connection.ops.tablespace_sql(tablespace, inline=True))
200         if f.rel:
201             ref_output, pending = connection.creation.sql_for_inline_foreign_key_references(f, known_models, style)
202             if pending:
203                 pr = pending_references.setdefault(f.rel.to, []).append((model, f))
204             else:
205                 field_output.extend(ref_output)
206         final_output.append(' '.join(field_output)+';')
207         final_output.extend(connection.creation.sql_indexes_for_field(model, f, style))
208     
209     return final_output, pending_references
210 
211 def sql_new_many_to_many(connection, model, style, all_tables):
212     "Return the CREATE TABLE statments for all the many-to-many tables defined on a model"
213 
214     output = []
215     for f in model._meta.local_many_to_many:
216         if f.m2m_db_table() in all_tables:
217             continue
218         if model._meta.managed or f.rel.to._meta.managed:
219             output.extend(connection.creation.sql_for_many_to_many_field(model, f, style))
220     return output
221 
222 equivalence_mapping = {
223     'integer auto_increment': ('int(11)',),
224     'integer': ('int(11)',),
225 }
226 def are_equivalent(old, new):
227     return old == new or old in equivalence_mapping.get(new,[])