diff --git a/rhodecode/lib/dbmigrate/migrate/changeset/databases/sqlite.py b/rhodecode/lib/dbmigrate/migrate/changeset/databases/sqlite.py --- a/rhodecode/lib/dbmigrate/migrate/changeset/databases/sqlite.py +++ b/rhodecode/lib/dbmigrate/migrate/changeset/databases/sqlite.py @@ -30,8 +30,24 @@ class SQLiteCommon(object): class SQLiteHelper(SQLiteCommon): - def _get_unique_constraints(self, table): - """Retrieve information about existing unique constraints of the table + def _filter_columns(self, cols, table): + """Splits the string of columns and returns those only in the table. + + :param cols: comma-delimited string of table columns + :param table: the table to check + :return: list of columns in the table + """ + columns = [] + for c in cols.split(","): + if c in table.columns: + # There was a bug in reflection of SQLite columns with + # reserved identifiers as names (SQLite can return them + # wrapped with double quotes), so strip double quotes. + columns.extend(c.strip(' "')) + return columns + + def _get_constraints(self, table): + """Retrieve information about existing constraints of the table This feature is needed for recreate_table() to work properly. """ @@ -49,19 +65,21 @@ class SQLiteHelper(SQLiteCommon): constraints = [] for name, cols in re.findall(UNIQUE_PATTERN, data): # Filter out any columns that were dropped from the table. - columns = [] - for c in cols.split(","): - if c in table.columns: - # There was a bug in reflection of SQLite columns with - # reserved identifiers as names (SQLite can return them - # wrapped with double quotes), so strip double quotes. - columns.extend(c.strip(' "')) + columns = self._filter_columns(cols, table) if columns: constraints.extend(UniqueConstraint(*columns, name=name)) + + FKEY_PATTERN = "CONSTRAINT (\w+) FOREIGN KEY \(([^\)]+)\)" + for name, cols in re.findall(FKEY_PATTERN, data): + # Filter out any columns that were dropped from the table. + columns = self._filter_columns(cols, table) + if columns: + constraints.extend(ForeignKeyConstraint(*columns, name=name)) + return constraints def recreate_table(self, table, column=None, delta=None, - omit_uniques=None): + omit_constraints=None): table_name = self.preparer.format_table(table) # we remove all indexes so as not to have @@ -69,24 +87,27 @@ class SQLiteHelper(SQLiteCommon): for index in table.indexes: index.drop() - # reflect existing unique constraints - for uc in self._get_unique_constraints(table): - table.append_constraint(uc) - # omit given unique constraints when creating a new table if required + # reflect existing constraints + for constraint in self._get_constraints(table): + table.append_constraint(constraint) + # omit given constraints when creating a new table if required table.constraints = set([ cons for cons in table.constraints - if omit_uniques is None or cons.name not in omit_uniques + if omit_constraints is None or cons.name not in omit_constraints ]) - tup = sqlite3.sqlite_version_info - if tup[0] > 3 or (tup[0] == 3 and tup[1] >= 26): + + # Use "PRAGMA legacy_alter_table = ON" with sqlite >= 3.26 when + # using "ALTER TABLE RENAME TO migration_tmp" to maintain legacy + # behavior. See: https://www.sqlite.org/src/info/ae9638e9c0ad0c36 + if self.connection.engine.dialect.server_version_info >= (3, 26): self.append('PRAGMA legacy_alter_table = ON') self.execute() - self.append('ALTER TABLE %s RENAME TO migration_tmp' % table_name) self.execute() - if tup[0] > 3 or (tup[0] == 3 and tup[1] >= 26): + if self.connection.engine.dialect.server_version_info >= (3, 26): self.append('PRAGMA legacy_alter_table = OFF') self.execute() + insertion_string = self._modify_table(table, column, delta) table.create(bind=self.connection) @@ -102,7 +123,6 @@ class SQLiteHelper(SQLiteCommon): else: column = delta table = self._to_table(column.table) - self.recreate_table(table,column,delta) class SQLiteColumnGenerator(SQLiteSchemaGenerator, @@ -190,13 +210,14 @@ class SQLiteConstraintDropper(ansisql.AN self.execute() def visit_migrate_foreign_key_constraint(self, *p, **k): - self._not_supported('ALTER TABLE DROP CONSTRAINT') + #self._not_supported('ALTER TABLE DROP CONSTRAINT') + self.recreate_table(p[0].table, omit_constraints=[p[0].name]) def visit_migrate_check_constraint(self, *p, **k): self._not_supported('ALTER TABLE DROP CONSTRAINT') def visit_migrate_unique_constraint(self, *p, **k): - self.recreate_table(p[0].table, omit_uniques=[p[0].name]) + self.recreate_table(p[0].table, omit_constraints=[p[0].name]) # TODO: technically primary key is a NOT NULL + UNIQUE constraint, should add NOT NULL to index diff --git a/rhodecode/lib/dbmigrate/migrate/changeset/schema.py b/rhodecode/lib/dbmigrate/migrate/changeset/schema.py --- a/rhodecode/lib/dbmigrate/migrate/changeset/schema.py +++ b/rhodecode/lib/dbmigrate/migrate/changeset/schema.py @@ -367,8 +367,14 @@ class ColumnDelta(DictMixin, sqlalchemy. self.process_column(self.result_column) # create an instance of class type if not yet - if 'type' in diffs and callable(self.result_column.type): - self.result_column.type = self.result_column.type() + if 'type' in diffs: + if callable(self.result_column.type): + self.result_column.type = self.result_column.type() + if self.result_column.autoincrement and \ + not issubclass( + self.result_column.type._type_affinity, + sqlalchemy.Integer): + self.result_column.autoincrement = False # add column to the table if self.table is not None and self.alter_metadata: