diff options
| author | zeripath <art27@cantab.net> | 2021-08-08 11:34:24 +0100 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2021-08-08 13:34:24 +0300 |
| commit | 9c116f2bb51ab3da9385ecdbd538a91c0393e609 (patch) | |
| tree | b504ed521c74baccb034459eb911cf180d0b7cfd /models | |
| parent | 07bc380c9f165f9bd199a7d942ef694e572c4387 (diff) | |
| download | gitea-9c116f2bb51ab3da9385ecdbd538a91c0393e609.tar.gz gitea-9c116f2bb51ab3da9385ecdbd538a91c0393e609.zip | |
Restore compatibility with SQLServer 2008 R2 in migrations (#16627)
* Restore compatibility with SQLServer 2008 R2 in migrations
`ALTER TABLE DROP ... IF EXISTS ...` is only supported in SQL Server >16.
The `IF EXISTS` here is a belt-and-braces and does not need to be present. Therefore
can be dropped.
We need to figure out some way of restricting our SQL syntax against the minimum
version of SQL Server we will support.
My suspicion is that `ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 100` may
do that but there may be other side-effects so I am not whether to do that.
Signed-off-by: Andrew Thornton <art27@cantab.net>
* try just dropping the index only
Signed-off-by: Andrew Thornton <art27@cantab.net>
* use lowercase for system tables
Signed-off-by: Andrew Thornton <art27@cantab.net>
Co-authored-by: Lauris BH <lauris@nix.lv>
Co-authored-by: Lunny Xiao <xiaolunwen@gmail.com>
Diffstat (limited to 'models')
| -rw-r--r-- | models/migrations/migrations.go | 9 |
1 files changed, 3 insertions, 6 deletions
diff --git a/models/migrations/migrations.go b/models/migrations/migrations.go index 9b04a364ca..6fbc0290f9 100644 --- a/models/migrations/migrations.go +++ b/models/migrations/migrations.go @@ -840,7 +840,7 @@ func dropTableColumns(sess *xorm.Session, tableName string, columnNames ...strin } cols += "`" + strings.ToLower(col) + "`" } - sql := fmt.Sprintf("SELECT Name FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID = OBJECT_ID('%[1]s') AND PARENT_COLUMN_ID IN (SELECT column_id FROM sys.columns WHERE lower(NAME) IN (%[2]s) AND object_id = OBJECT_ID('%[1]s'))", + sql := fmt.Sprintf("SELECT Name FROM sys.default_constraints WHERE parent_object_id = OBJECT_ID('%[1]s') AND parent_column_id IN (SELECT column_id FROM sys.columns WHERE LOWER(name) IN (%[2]s) AND object_id = OBJECT_ID('%[1]s'))", tableName, strings.ReplaceAll(cols, "`", "'")) constraints := make([]string, 0) if err := sess.SQL(sql).Find(&constraints); err != nil { @@ -851,17 +851,14 @@ func dropTableColumns(sess *xorm.Session, tableName string, columnNames ...strin return fmt.Errorf("Drop table `%s` default constraint `%s`: %v", tableName, constraint, err) } } - sql = fmt.Sprintf("SELECT DISTINCT Name FROM SYS.INDEXES INNER JOIN SYS.INDEX_COLUMNS ON INDEXES.INDEX_ID = INDEX_COLUMNS.INDEX_ID AND INDEXES.OBJECT_ID = INDEX_COLUMNS.OBJECT_ID WHERE INDEXES.OBJECT_ID = OBJECT_ID('%[1]s') AND INDEX_COLUMNS.COLUMN_ID IN (SELECT column_id FROM sys.columns WHERE lower(NAME) IN (%[2]s) AND object_id = OBJECT_ID('%[1]s'))", + sql = fmt.Sprintf("SELECT DISTINCT Name FROM sys.indexes INNER JOIN sys.index_columns ON indexes.index_id = index_columns.index_id AND indexes.object_id = index_columns.object_id WHERE indexes.object_id = OBJECT_ID('%[1]s') AND index_columns.column_id IN (SELECT column_id FROM sys.columns WHERE LOWER(name) IN (%[2]s) AND object_id = OBJECT_ID('%[1]s'))", tableName, strings.ReplaceAll(cols, "`", "'")) constraints = make([]string, 0) if err := sess.SQL(sql).Find(&constraints); err != nil { return fmt.Errorf("Find constraints: %v", err) } for _, constraint := range constraints { - if _, err := sess.Exec(fmt.Sprintf("ALTER TABLE `%s` DROP CONSTRAINT IF EXISTS `%s`", tableName, constraint)); err != nil { - return fmt.Errorf("Drop table `%s` index constraint `%s`: %v", tableName, constraint, err) - } - if _, err := sess.Exec(fmt.Sprintf("DROP INDEX IF EXISTS `%[2]s` ON `%[1]s`", tableName, constraint)); err != nil { + if _, err := sess.Exec(fmt.Sprintf("DROP INDEX `%[2]s` ON `%[1]s`", tableName, constraint)); err != nil { return fmt.Errorf("Drop index `%[2]s` on `%[1]s`: %v", tableName, constraint, err) } } |