diff options
author | Derek Anderson <public@kered.org> | 2007-08-02 03:28:52 +0000 |
---|---|---|
committer | Derek Anderson <public@kered.org> | 2007-08-02 03:28:52 +0000 |
commit | 38c1cd721dc78b17088257caff78f668315d9b25 (patch) | |
tree | 70daf72f58bc04f8eaa5227328787039774e4f25 | |
parent | 0b4c2c7ab1b32d5fa1befd86207d8ead52d6e4f7 (diff) | |
download | django-38c1cd721dc78b17088257caff78f668315d9b25.tar.gz |
schema-evolution:
added new "pk_requires_unique" option to the backend, because sqlite3 requires "UNIQUE" when creating PKs in order to
_actually_ create the constraint.
fixed "get_known_column_flags" introspection for sqlite3
implemented "get_drop_column_sql" for sqlite3 to work around sqlite's lack of DROP COLUMN support
added partial of the sqlite3 unit tests
git-svn-id: http://code.djangoproject.com/svn/django/branches/schema-evolution@5785 bcc190cf-cafb-0310-a4f2-bffc1f526a37
-rw-r--r-- | django/core/management.py | 20 | ||||
-rw-r--r-- | django/db/backends/ado_mssql/base.py | 1 | ||||
-rw-r--r-- | django/db/backends/mysql/base.py | 3 | ||||
-rw-r--r-- | django/db/backends/mysql_old/base.py | 1 | ||||
-rw-r--r-- | django/db/backends/postgresql/base.py | 3 | ||||
-rw-r--r-- | django/db/backends/postgresql/introspection.py | 4 | ||||
-rw-r--r-- | django/db/backends/postgresql_psycopg2/base.py | 1 | ||||
-rw-r--r-- | django/db/backends/sqlite3/base.py | 38 | ||||
-rw-r--r-- | django/db/backends/sqlite3/introspection.py | 33 | ||||
-rw-r--r-- | django/test/simple.py | 2 | ||||
-rw-r--r-- | tests/modeltests/schema_evolution/models.py | 66 |
11 files changed, 135 insertions, 37 deletions
diff --git a/django/core/management.py b/django/core/management.py index 64857d52e5..815c7a571b 100644 --- a/django/core/management.py +++ b/django/core/management.py @@ -170,7 +170,7 @@ def _get_sql_model_create(model, known_models=set()): field_output = [style.SQL_FIELD(backend.quote_name(f.column)), style.SQL_COLTYPE(col_type)] field_output.append(style.SQL_KEYWORD('%sNULL' % (not f.null and 'NOT ' or ''))) - if f.unique and (not f.primary_key or backend.allows_unique_and_pk): + if (f.unique and (not f.primary_key or backend.allows_unique_and_pk)) or (f.primary_key and backend.pk_requires_unique): field_output.append(style.SQL_KEYWORD('UNIQUE')) if f.primary_key: field_output.append(style.SQL_KEYWORD('PRIMARY KEY')) @@ -569,18 +569,6 @@ def get_sql_evolution_check_for_new_fields(klass, new_table_name): data_type = f.get_internal_type() col_type = data_types[data_type] if col_type is not None: -# field_output = [] -# field_output.append('ALTER TABLE') -# field_output.append(db_table) -# field_output.append('ADD COLUMN') -# field_output.append(backend.quote_name(f.column)) -# field_output.append(style.SQL_COLTYPE(col_type % rel_field.__dict__)) -# field_output.append(style.SQL_KEYWORD('%sNULL' % (not f.null and 'NOT ' or ''))) -# if f.unique: -# field_output.append(style.SQL_KEYWORD('UNIQUE')) -# if f.primary_key: -# field_output.append(style.SQL_KEYWORD('PRIMARY KEY')) -# output.append(' '.join(field_output) + ';') output.append( backend.get_add_column_sql( db_table, f.column, style.SQL_COLTYPE(col_type % rel_field.__dict__), f.null, f.unique, f.primary_key ) ) return output @@ -664,7 +652,7 @@ def get_sql_evolution_check_for_changed_field_flags(klass, new_table_name): ( column_flags['unique']!=f.unique and ( settings.DATABASE_ENGINE!='postgresql' or not f.primary_key ) ) or \ column_flags['primary_key']!=f.primary_key: #column_flags['foreign_key']!=f.foreign_key: -# print +# print 'need to change' # print db_table, f.column, column_flags # print "column_flags['allow_null']!=f.null", column_flags['allow_null']!=f.null # print "not f.primary_key and isinstance(f, CharField) and column_flags['maxlength']!=str(f.maxlength)", not f.primary_key and isinstance(f, CharField) and column_flags['maxlength']!=str(f.maxlength) @@ -703,9 +691,9 @@ def get_sql_evolution_check_for_dead_fields(klass, new_table_name): suspect_fields.discard(f.aka) if f.aka: suspect_fields.difference_update(f.aka) if len(suspect_fields)>0: - output.append( '-- warning: as the following may cause data loss, it/they must be run manually' ) + output.append( '-- warning: the following may cause data loss' ) for suspect_field in suspect_fields: - output.append( '-- '+ backend.get_drop_column_sql( db_table, suspect_field ) ) + output.extend( backend.get_drop_column_sql( db_table, suspect_field ) ) output.append( '-- end warning' ) return output diff --git a/django/db/backends/ado_mssql/base.py b/django/db/backends/ado_mssql/base.py index 0deb6aae64..5ed192114b 100644 --- a/django/db/backends/ado_mssql/base.py +++ b/django/db/backends/ado_mssql/base.py @@ -91,6 +91,7 @@ class DatabaseWrapper(local): allows_group_by_ordinal = True allows_unique_and_pk = True +pk_requires_unique = False autoindexes_primary_keys = True needs_datetime_string_cast = True needs_upper_for_iops = False diff --git a/django/db/backends/mysql/base.py b/django/db/backends/mysql/base.py index 6e9a5f0e46..ebe0c312aa 100644 --- a/django/db/backends/mysql/base.py +++ b/django/db/backends/mysql/base.py @@ -136,6 +136,7 @@ class DatabaseWrapper(local): allows_group_by_ordinal = True allows_unique_and_pk = True +pk_requires_unique = False autoindexes_primary_keys = False needs_datetime_string_cast = True # MySQLdb requires a typecast for dates needs_upper_for_iops = False @@ -284,7 +285,7 @@ def get_add_column_sql( table_name, col_name, col_type, null, unique, primary_ke def get_drop_column_sql( table_name, col_name ): output = [] output.append( 'ALTER TABLE '+ quote_name(table_name) +' DROP COLUMN '+ quote_name(col_name) + ';' ) - return '\n'.join(output) + return output OPERATOR_MAPPING = { diff --git a/django/db/backends/mysql_old/base.py b/django/db/backends/mysql_old/base.py index 0116af53ea..274d27a0be 100644 --- a/django/db/backends/mysql_old/base.py +++ b/django/db/backends/mysql_old/base.py @@ -151,6 +151,7 @@ class DatabaseWrapper(local): allows_group_by_ordinal = True allows_unique_and_pk = True +pk_requires_unique = False autoindexes_primary_keys = False needs_datetime_string_cast = True # MySQLdb requires a typecast for dates needs_upper_for_iops = False diff --git a/django/db/backends/postgresql/base.py b/django/db/backends/postgresql/base.py index 0cf8967c7a..7984c13d69 100644 --- a/django/db/backends/postgresql/base.py +++ b/django/db/backends/postgresql/base.py @@ -117,6 +117,7 @@ class DatabaseWrapper(local): allows_group_by_ordinal = True allows_unique_and_pk = True +pk_requires_unique = False autoindexes_primary_keys = True needs_datetime_string_cast = True needs_upper_for_iops = False @@ -321,7 +322,7 @@ def get_add_column_sql( table_name, col_name, col_type, null, unique, primary_ke def get_drop_column_sql( table_name, col_name ): output = [] output.append( 'ALTER TABLE '+ quote_name(table_name) +' DROP COLUMN '+ quote_name(col_name) + ';' ) - return '\n'.join(output) + return output # Register these custom typecasts, because Django expects dates/times to be # in Python's native (standard-library) datetime/time format, whereas psycopg diff --git a/django/db/backends/postgresql/introspection.py b/django/db/backends/postgresql/introspection.py index 602239277a..bbeee184e0 100644 --- a/django/db/backends/postgresql/introspection.py +++ b/django/db/backends/postgresql/introspection.py @@ -81,8 +81,8 @@ def get_known_column_flags( cursor, table_name, column_name ): dict['foreign_key'] = False dict['unique'] = False dict['default'] = '' - -# dict['allow_null'] = False + dict['allow_null'] = False + for row in cursor.fetchall(): if row[0] == column_name: diff --git a/django/db/backends/postgresql_psycopg2/base.py b/django/db/backends/postgresql_psycopg2/base.py index c0ecbf80e9..e1687baae8 100644 --- a/django/db/backends/postgresql_psycopg2/base.py +++ b/django/db/backends/postgresql_psycopg2/base.py @@ -79,6 +79,7 @@ class DatabaseWrapper(local): allows_group_by_ordinal = True allows_unique_and_pk = True +pk_requires_unique = False autoindexes_primary_keys = True needs_datetime_string_cast = False needs_upper_for_iops = False diff --git a/django/db/backends/sqlite3/base.py b/django/db/backends/sqlite3/base.py index 67c56daada..1670c3bc37 100644 --- a/django/db/backends/sqlite3/base.py +++ b/django/db/backends/sqlite3/base.py @@ -2,6 +2,7 @@ SQLite3 backend for django. Requires pysqlite2 (http://pysqlite.org/). """ +from django.core import management from django.db.backends import util try: try: @@ -102,6 +103,7 @@ class SQLiteCursorWrapper(Database.Cursor): allows_group_by_ordinal = True allows_unique_and_pk = True +pk_requires_unique = True # or else the constraint is never created autoindexes_primary_keys = True needs_datetime_string_cast = True needs_upper_for_iops = False @@ -227,13 +229,16 @@ def get_change_column_name_sql( table_name, indexes, old_col_name, new_col_name, output.append( 'ALTER TABLE '+ quote_name(table_name) +' ADD COLUMN '+ quote_name(new_col_name) +' '+ col_def + ';' ) output.append( 'UPDATE '+ quote_name(table_name) +' SET '+ new_col_name +' = '+ old_col_name +' WHERE '+ pk_name +'=(select '+ pk_name +' from '+ table_name +');' ) output.append( '-- FYI: sqlite does not support deleting columns, so '+ quote_name(old_col_name) +' remains as cruft' ) - # use the following when sqlite gets drop support - #output.append( 'ALTER TABLE '+ quote_name(table_name) +' DROP COLUMN '+ quote_name(old_col_name) ) return '\n'.join(output) -def get_change_column_def_sql( table_name, col_name, col_def ): +def get_change_column_def_sql( table_name, col_name, col_type, null, unique, primary_key ): # sqlite doesn't support column modifications, so we fake it output = [] + col_def = col_type +' '+ ('%sNULL' % (not null and 'NOT ' or '')) + if unique or primary_key: + col_def += ' '+ 'UNIQUE' + if primary_key: + col_def += ' '+ 'PRIMARY KEY' # TODO: fake via renaming the table, building a new one and deleting the old output.append('-- sqlite does not support column modifications '+ quote_name(table_name) +'.'+ quote_name(col_name) +' to '+ col_def) return '\n'.join(output) @@ -247,7 +252,7 @@ def get_add_column_sql( table_name, col_name, col_type, null, unique, primary_ke field_output.append(quote_name(col_name)) field_output.append(col_type) field_output.append(('%sNULL' % (not null and 'NOT ' or ''))) - if unique: + if unique or primary_key: field_output.append(('UNIQUE')) if primary_key: field_output.append(('PRIMARY KEY')) @@ -255,11 +260,28 @@ def get_add_column_sql( table_name, col_name, col_type, null, unique, primary_ke return '\n'.join(output) def get_drop_column_sql( table_name, col_name ): + model = get_model_from_table_name(table_name) output = [] - output.append( '-- FYI: sqlite does not support deleting columns, so '+ quote_name(old_col_name) +' remains as cruft' ) - # use the following when sqlite gets drop support - # output.append( '-- ALTER TABLE '+ quote_name(table_name) +' DROP COLUMN '+ quote_name(col_name) ) - return '\n'.join(output) + output.append( '-- FYI: sqlite does not support deleting columns, so we create a new '+ quote_name(col_name) +' and delete the old (ie, this could take a while)' ) + tmp_table_name = table_name + '_1337_TMP' # unlikely to produce a namespace conflict + output.append( get_change_table_name_sql( tmp_table_name, table_name ) ) + output.extend( management._get_sql_model_create(model, set())[0] ) + new_cols = [] + for f in model._meta.fields: + new_cols.append( quote_name(f.column) ) + output.append( 'INSERT INTO '+ quote_name(table_name) +' SELECT '+ ','.join(new_cols) +' FROM '+ quote_name(tmp_table_name) +';' ) + output.append( 'DROP TABLE '+ quote_name(tmp_table_name) +';' ) + return output + +def get_model_from_table_name(table_name): + from django.db import models + for app in models.get_apps(): + app_name = app.__name__.split('.')[-2] + if app_name == table_name.split('_')[0] or app_name == '_'.join(table_name.split('_')[0:1]) or app_name == '_'.join(table_name.split('_')[0:2]): + for model in models.get_models(app): + if model._meta.db_table == table_name: + return model + return None # SQLite requires LIKE statements to include an ESCAPE clause if the value diff --git a/django/db/backends/sqlite3/introspection.py b/django/db/backends/sqlite3/introspection.py index ce033757c9..b59ba70312 100644 --- a/django/db/backends/sqlite3/introspection.py +++ b/django/db/backends/sqlite3/introspection.py @@ -53,17 +53,38 @@ def get_columns(cursor, table_name): def get_known_column_flags( cursor, table_name, column_name ): cursor.execute("PRAGMA table_info(%s)" % quote_name(table_name)) dict = {} + dict['primary_key'] = False + dict['foreign_key'] = False + dict['unique'] = False + dict['default'] = '' + dict['allow_null'] = True + for row in cursor.fetchall(): +# print row if row[1] == column_name: + col_type = row[2] # maxlength check goes here if row[2][0:7]=='varchar': dict['maxlength'] = row[2][8:len(row[2])-1] # default flag check goes here - #if row[2]=='YES': dict['allow_null'] = True - #else: dict['allow_null'] = False + dict['allow_null'] = row[3]==0 + # default value check goes here + dict['default'] = row[4] + + cursor.execute("PRAGMA index_list(%s)" % quote_name(table_name)) + index_names = [] + for row in cursor.fetchall(): + index_names.append(row[1]) + for index_name in index_names: + cursor.execute("PRAGMA index_info(%s)" % quote_name(index_name)) + for row in cursor.fetchall(): + if row[2]==column_name: + if col_type=='integer': dict['primary_key'] = True # sqlite3 does not distinguish between unique and pk; all + else: dict['unique'] = True # unique integer columns are treated as part of the pk. + # primary/foreign/unique key flag check goes here #if row[3]=='PRI': dict['primary_key'] = True #else: dict['primary_key'] = False @@ -72,12 +93,8 @@ def get_known_column_flags( cursor, table_name, column_name ): #if row[3]=='UNI': dict['unique'] = True #else: dict['unique'] = False - # default value check goes here - # if row[4]=='NULL': dict['default'] = None - # else: dict['default'] = row[4] - #dict['default'] = row[4] - - print table_name, column_name, dict + +# print dict return dict def _table_info(cursor, name): diff --git a/django/test/simple.py b/django/test/simple.py index 9730a0df1d..e2d2359569 100644 --- a/django/test/simple.py +++ b/django/test/simple.py @@ -98,4 +98,4 @@ def run_tests(module_list, verbosity=1, extra_tests=[]): teardown_test_environment() return len(result.failures) + len(result.errors) -
\ No newline at end of file + diff --git a/tests/modeltests/schema_evolution/models.py b/tests/modeltests/schema_evolution/models.py index b401881714..7f9e59c255 100644 --- a/tests/modeltests/schema_evolution/models.py +++ b/tests/modeltests/schema_evolution/models.py @@ -141,3 +141,69 @@ if settings.DATABASE_ENGINE == 'postgresql': """ +if settings.DATABASE_ENGINE == 'sqlite3': + __test__['API_TESTS'] += """ +# the table as it is supposed to be +>>> create_table_sql = management.get_sql_all(app) +>>> print create_table_sql +['CREATE TABLE "schema_evolution_person" (\\n "id" integer NOT NULL UNIQUE PRIMARY KEY,\\n "name" varchar(20) NOT NULL,\\n "gender" varchar(1) NOT NULL,\\n "gender2" varchar(1) NOT NULL\\n)\\n;'] + +# make sure we don't evolve an unedited table +>>> management.get_sql_evolution(app) +[] + +# delete a column, so it looks like we've recently added a field +>>> cursor.execute( 'DROP TABLE "schema_evolution_person";' ).__class__ +<class 'django.db.backends.sqlite3.base.SQLiteCursorWrapper'> +>>> cursor.execute( 'CREATE TABLE "schema_evolution_person" ( "id" integer NOT NULL UNIQUE PRIMARY KEY, "name" varchar(20) NOT NULL, "gender" varchar(1) NOT NULL );' ).__class__ +<class 'django.db.backends.sqlite3.base.SQLiteCursorWrapper'> +>>> management.get_sql_evolution(app) +['ALTER TABLE "schema_evolution_person" ADD COLUMN "gender2" varchar(1) NOT NULL;'] + +# reset the db +>>> cursor.execute('DROP TABLE schema_evolution_person;').__class__ +<class 'django.db.backends.sqlite3.base.SQLiteCursorWrapper'> +>>> cursor.execute(create_table_sql[0]).__class__ +<class 'django.db.backends.sqlite3.base.SQLiteCursorWrapper'> + +# add a column, so it looks like we've recently deleted a field +>>> cursor.execute( 'DROP TABLE "schema_evolution_person";' ).__class__ +<class 'django.db.backends.sqlite3.base.SQLiteCursorWrapper'> +>>> cursor.execute( 'CREATE TABLE "schema_evolution_person" ( "id" integer NOT NULL UNIQUE PRIMARY KEY, "name" varchar(20) NOT NULL, "gender" varchar(1) NOT NULL, "gender2" varchar(1) NOT NULL, "gender_new" varchar(1) NOT NULL );' ).__class__ +<class 'django.db.backends.sqlite3.base.SQLiteCursorWrapper'> +>>> management.get_sql_evolution(app) +['-- warning: the following may cause data loss', u'-- FYI: sqlite does not support deleting columns, so we create a new "gender_new" and delete the old (ie, this could take a while)', 'ALTER TABLE "schema_evolution_person" RENAME TO "schema_evolution_person_1337_TMP";', 'CREATE TABLE "schema_evolution_person" (\\n "id" integer NOT NULL UNIQUE PRIMARY KEY,\\n "name" varchar(20) NOT NULL,\\n "gender" varchar(1) NOT NULL,\\n "gender2" varchar(1) NOT NULL\\n)\\n;', 'INSERT INTO "schema_evolution_person" SELECT "id","name","gender","gender2" FROM "schema_evolution_person_1337_TMP";', 'DROP TABLE "schema_evolution_person_1337_TMP";', '-- end warning'] + +# reset the db +>>> cursor.execute('DROP TABLE schema_evolution_person;').__class__ +<class 'django.db.backends.sqlite3.base.SQLiteCursorWrapper'> +>>> cursor.execute(create_table_sql[0]).__class__ +<class 'django.db.backends.sqlite3.base.SQLiteCursorWrapper'> + +""" + +crap = """ + +# rename column, so it looks like we've recently renamed a field +>>> cursor.execute( backend.get_change_column_name_sql( 'schema_evolution_person', {}, 'gender2', 'gender_old', 'varchar(1)' ) ) +>>> management.get_sql_evolution(app) +['ALTER TABLE "schema_evolution_person" RENAME COLUMN "gender_old" TO "gender2";'] + +# reset the db +>>> cursor.execute('DROP TABLE schema_evolution_person;'); cursor.execute(create_table_sql[0]) + +# rename table, so it looks like we've recently renamed a model +>>> cursor.execute( backend.get_change_table_name_sql( 'schema_evolution_personold', 'schema_evolution_person' ) ) +>>> management.get_sql_evolution(app) +['ALTER TABLE "schema_evolution_personold" RENAME TO "schema_evolution_person";'] + +# reset the db +>>> cursor.execute(create_table_sql[0]) + +# change column flags, so it looks like we've recently changed a column flag +>>> cursor.execute( backend.get_change_column_def_sql( 'schema_evolution_person', 'name', 'varchar(10)', True, False, False ) ) +>>> management.get_sql_evolution(app) +['ALTER TABLE "schema_evolution_person" ADD COLUMN "name_tmp" varchar(20);\\nUPDATE "schema_evolution_person" SET "name_tmp" = "name";\\nALTER TABLE "schema_evolution_person" DROP COLUMN "name";\\nALTER TABLE "schema_evolution_person" RENAME COLUMN "name_tmp" TO "name";\\nALTER TABLE "schema_evolution_person" ALTER COLUMN "name" SET NOT NULL;'] + +""" + |