summaryrefslogtreecommitdiffstats
path: root/models/migrations/base
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--models/migrations/base/db.go436
-rw-r--r--models/migrations/base/db_test.go98
-rw-r--r--models/migrations/base/hash.go16
-rw-r--r--models/migrations/base/main_test.go14
4 files changed, 564 insertions, 0 deletions
diff --git a/models/migrations/base/db.go b/models/migrations/base/db.go
new file mode 100644
index 0000000..333fa31
--- /dev/null
+++ b/models/migrations/base/db.go
@@ -0,0 +1,436 @@
+// Copyright 2022 The Gitea Authors. All rights reserved.
+// SPDX-License-Identifier: MIT
+
+package base
+
+import (
+ "errors"
+ "fmt"
+ "reflect"
+ "regexp"
+ "strings"
+
+ "code.gitea.io/gitea/modules/log"
+ "code.gitea.io/gitea/modules/setting"
+
+ "xorm.io/xorm"
+ "xorm.io/xorm/schemas"
+)
+
+// RecreateTables will recreate the tables for the provided beans using the newly provided bean definition and move all data to that new table
+// WARNING: YOU MUST PROVIDE THE FULL BEAN DEFINITION
+func RecreateTables(beans ...any) func(*xorm.Engine) error {
+ return func(x *xorm.Engine) error {
+ sess := x.NewSession()
+ defer sess.Close()
+ if err := sess.Begin(); err != nil {
+ return err
+ }
+ sess = sess.StoreEngine("InnoDB")
+ for _, bean := range beans {
+ log.Info("Recreating Table: %s for Bean: %s", x.TableName(bean), reflect.Indirect(reflect.ValueOf(bean)).Type().Name())
+ if err := RecreateTable(sess, bean); err != nil {
+ return err
+ }
+ }
+ return sess.Commit()
+ }
+}
+
+// RecreateTable will recreate the table using the newly provided bean definition and move all data to that new table
+// WARNING: YOU MUST PROVIDE THE FULL BEAN DEFINITION
+// WARNING: YOU MUST COMMIT THE SESSION AT THE END
+func RecreateTable(sess *xorm.Session, bean any) error {
+ // TODO: This will not work if there are foreign keys
+
+ tableName := sess.Engine().TableName(bean)
+ tempTableName := fmt.Sprintf("tmp_recreate__%s", tableName)
+
+ // We need to move the old table away and create a new one with the correct columns
+ // We will need to do this in stages to prevent data loss
+ //
+ // First create the temporary table
+ if err := sess.Table(tempTableName).CreateTable(bean); err != nil {
+ log.Error("Unable to create table %s. Error: %v", tempTableName, err)
+ return err
+ }
+
+ if err := sess.Table(tempTableName).CreateUniques(bean); err != nil {
+ log.Error("Unable to create uniques for table %s. Error: %v", tempTableName, err)
+ return err
+ }
+
+ if err := sess.Table(tempTableName).CreateIndexes(bean); err != nil {
+ log.Error("Unable to create indexes for table %s. Error: %v", tempTableName, err)
+ return err
+ }
+
+ // Work out the column names from the bean - these are the columns to select from the old table and install into the new table
+ table, err := sess.Engine().TableInfo(bean)
+ if err != nil {
+ log.Error("Unable to get table info. Error: %v", err)
+
+ return err
+ }
+ newTableColumns := table.Columns()
+ if len(newTableColumns) == 0 {
+ return fmt.Errorf("no columns in new table")
+ }
+ hasID := false
+ for _, column := range newTableColumns {
+ hasID = hasID || (column.IsPrimaryKey && column.IsAutoIncrement)
+ }
+
+ sqlStringBuilder := &strings.Builder{}
+ _, _ = sqlStringBuilder.WriteString("INSERT INTO `")
+ _, _ = sqlStringBuilder.WriteString(tempTableName)
+ _, _ = sqlStringBuilder.WriteString("` (`")
+ _, _ = sqlStringBuilder.WriteString(newTableColumns[0].Name)
+ _, _ = sqlStringBuilder.WriteString("`")
+ for _, column := range newTableColumns[1:] {
+ _, _ = sqlStringBuilder.WriteString(", `")
+ _, _ = sqlStringBuilder.WriteString(column.Name)
+ _, _ = sqlStringBuilder.WriteString("`")
+ }
+ _, _ = sqlStringBuilder.WriteString(")")
+ _, _ = sqlStringBuilder.WriteString(" SELECT ")
+ if newTableColumns[0].Default != "" {
+ _, _ = sqlStringBuilder.WriteString("COALESCE(`")
+ _, _ = sqlStringBuilder.WriteString(newTableColumns[0].Name)
+ _, _ = sqlStringBuilder.WriteString("`, ")
+ _, _ = sqlStringBuilder.WriteString(newTableColumns[0].Default)
+ _, _ = sqlStringBuilder.WriteString(")")
+ } else {
+ _, _ = sqlStringBuilder.WriteString("`")
+ _, _ = sqlStringBuilder.WriteString(newTableColumns[0].Name)
+ _, _ = sqlStringBuilder.WriteString("`")
+ }
+
+ for _, column := range newTableColumns[1:] {
+ if column.Default != "" {
+ _, _ = sqlStringBuilder.WriteString(", COALESCE(`")
+ _, _ = sqlStringBuilder.WriteString(column.Name)
+ _, _ = sqlStringBuilder.WriteString("`, ")
+ _, _ = sqlStringBuilder.WriteString(column.Default)
+ _, _ = sqlStringBuilder.WriteString(")")
+ } else {
+ _, _ = sqlStringBuilder.WriteString(", `")
+ _, _ = sqlStringBuilder.WriteString(column.Name)
+ _, _ = sqlStringBuilder.WriteString("`")
+ }
+ }
+ _, _ = sqlStringBuilder.WriteString(" FROM `")
+ _, _ = sqlStringBuilder.WriteString(tableName)
+ _, _ = sqlStringBuilder.WriteString("`")
+
+ if _, err := sess.Exec(sqlStringBuilder.String()); err != nil {
+ log.Error("Unable to set copy data in to temp table %s. Error: %v", tempTableName, err)
+ return err
+ }
+
+ switch {
+ case setting.Database.Type.IsSQLite3():
+ // SQLite will drop all the constraints on the old table
+ if _, err := sess.Exec(fmt.Sprintf("DROP TABLE `%s`", tableName)); err != nil {
+ log.Error("Unable to drop old table %s. Error: %v", tableName, err)
+ return err
+ }
+
+ if err := sess.Table(tempTableName).DropIndexes(bean); err != nil {
+ log.Error("Unable to drop indexes on temporary table %s. Error: %v", tempTableName, err)
+ return err
+ }
+
+ if _, err := sess.Exec(fmt.Sprintf("ALTER TABLE `%s` RENAME TO `%s`", tempTableName, tableName)); err != nil {
+ log.Error("Unable to rename %s to %s. Error: %v", tempTableName, tableName, err)
+ return err
+ }
+
+ if err := sess.Table(tableName).CreateIndexes(bean); err != nil {
+ log.Error("Unable to recreate indexes on table %s. Error: %v", tableName, err)
+ return err
+ }
+
+ if err := sess.Table(tableName).CreateUniques(bean); err != nil {
+ log.Error("Unable to recreate uniques on table %s. Error: %v", tableName, err)
+ return err
+ }
+
+ case setting.Database.Type.IsMySQL():
+ // MySQL will drop all the constraints on the old table
+ if _, err := sess.Exec(fmt.Sprintf("DROP TABLE `%s`", tableName)); err != nil {
+ log.Error("Unable to drop old table %s. Error: %v", tableName, err)
+ return err
+ }
+
+ if err := sess.Table(tempTableName).DropIndexes(bean); err != nil {
+ log.Error("Unable to drop indexes on temporary table %s. Error: %v", tempTableName, err)
+ return err
+ }
+
+ // SQLite and MySQL will move all the constraints from the temporary table to the new table
+ if _, err := sess.Exec(fmt.Sprintf("ALTER TABLE `%s` RENAME TO `%s`", tempTableName, tableName)); err != nil {
+ log.Error("Unable to rename %s to %s. Error: %v", tempTableName, tableName, err)
+ return err
+ }
+
+ if err := sess.Table(tableName).CreateIndexes(bean); err != nil {
+ log.Error("Unable to recreate indexes on table %s. Error: %v", tableName, err)
+ return err
+ }
+
+ if err := sess.Table(tableName).CreateUniques(bean); err != nil {
+ log.Error("Unable to recreate uniques on table %s. Error: %v", tableName, err)
+ return err
+ }
+ case setting.Database.Type.IsPostgreSQL():
+ var originalSequences []string
+ type sequenceData struct {
+ LastValue int `xorm:"'last_value'"`
+ IsCalled bool `xorm:"'is_called'"`
+ }
+ sequenceMap := map[string]sequenceData{}
+
+ schema := sess.Engine().Dialect().URI().Schema
+ sess.Engine().SetSchema("")
+ if err := sess.Table("information_schema.sequences").Cols("sequence_name").Where("sequence_name LIKE ? || '_%' AND sequence_catalog = ?", tableName, setting.Database.Name).Find(&originalSequences); err != nil {
+ log.Error("Unable to rename %s to %s. Error: %v", tempTableName, tableName, err)
+ return err
+ }
+ sess.Engine().SetSchema(schema)
+
+ for _, sequence := range originalSequences {
+ sequenceData := sequenceData{}
+ if _, err := sess.Table(sequence).Cols("last_value", "is_called").Get(&sequenceData); err != nil {
+ log.Error("Unable to get last_value and is_called from %s. Error: %v", sequence, err)
+ return err
+ }
+ sequenceMap[sequence] = sequenceData
+ }
+
+ // CASCADE causes postgres to drop all the constraints on the old table
+ if _, err := sess.Exec(fmt.Sprintf("DROP TABLE `%s` CASCADE", tableName)); err != nil {
+ log.Error("Unable to drop old table %s. Error: %v", tableName, err)
+ return err
+ }
+
+ // CASCADE causes postgres to move all the constraints from the temporary table to the new table
+ if _, err := sess.Exec(fmt.Sprintf("ALTER TABLE `%s` RENAME TO `%s`", tempTableName, tableName)); err != nil {
+ log.Error("Unable to rename %s to %s. Error: %v", tempTableName, tableName, err)
+ return err
+ }
+
+ var indices []string
+ sess.Engine().SetSchema("")
+ if err := sess.Table("pg_indexes").Cols("indexname").Where("tablename = ? ", tableName).Find(&indices); err != nil {
+ log.Error("Unable to rename %s to %s. Error: %v", tempTableName, tableName, err)
+ return err
+ }
+ sess.Engine().SetSchema(schema)
+
+ for _, index := range indices {
+ newIndexName := strings.Replace(index, "tmp_recreate__", "", 1)
+ if _, err := sess.Exec(fmt.Sprintf("ALTER INDEX `%s` RENAME TO `%s`", index, newIndexName)); err != nil {
+ log.Error("Unable to rename %s to %s. Error: %v", index, newIndexName, err)
+ return err
+ }
+ }
+
+ var sequences []string
+ sess.Engine().SetSchema("")
+ if err := sess.Table("information_schema.sequences").Cols("sequence_name").Where("sequence_name LIKE 'tmp_recreate__' || ? || '_%' AND sequence_catalog = ?", tableName, setting.Database.Name).Find(&sequences); err != nil {
+ log.Error("Unable to rename %s to %s. Error: %v", tempTableName, tableName, err)
+ return err
+ }
+ sess.Engine().SetSchema(schema)
+
+ for _, sequence := range sequences {
+ newSequenceName := strings.Replace(sequence, "tmp_recreate__", "", 1)
+ if _, err := sess.Exec(fmt.Sprintf("ALTER SEQUENCE `%s` RENAME TO `%s`", sequence, newSequenceName)); err != nil {
+ log.Error("Unable to rename %s sequence to %s. Error: %v", sequence, newSequenceName, err)
+ return err
+ }
+ val, ok := sequenceMap[newSequenceName]
+ if newSequenceName == tableName+"_id_seq" {
+ if ok && val.LastValue != 0 {
+ if _, err := sess.Exec(fmt.Sprintf("SELECT setval('%s', %d, %t)", newSequenceName, val.LastValue, val.IsCalled)); err != nil {
+ log.Error("Unable to reset %s to %d. Error: %v", newSequenceName, val, err)
+ return err
+ }
+ } else {
+ // We're going to try to guess this
+ if _, err := sess.Exec(fmt.Sprintf("SELECT setval('%s', COALESCE((SELECT MAX(id)+1 FROM `%s`), 1), false)", newSequenceName, tableName)); err != nil {
+ log.Error("Unable to reset %s. Error: %v", newSequenceName, err)
+ return err
+ }
+ }
+ } else if ok {
+ if _, err := sess.Exec(fmt.Sprintf("SELECT setval('%s', %d, %t)", newSequenceName, val.LastValue, val.IsCalled)); err != nil {
+ log.Error("Unable to reset %s to %d. Error: %v", newSequenceName, val, err)
+ return err
+ }
+ }
+ }
+
+ default:
+ log.Fatal("Unrecognized DB")
+ }
+ return nil
+}
+
+// WARNING: YOU MUST COMMIT THE SESSION AT THE END
+func DropTableColumns(sess *xorm.Session, tableName string, columnNames ...string) (err error) {
+ if tableName == "" || len(columnNames) == 0 {
+ return nil
+ }
+ // TODO: This will not work if there are foreign keys
+
+ switch {
+ case setting.Database.Type.IsSQLite3():
+ // First drop the indexes on the columns
+ res, errIndex := sess.Query(fmt.Sprintf("PRAGMA index_list(`%s`)", tableName))
+ if errIndex != nil {
+ return errIndex
+ }
+ for _, row := range res {
+ indexName := row["name"]
+ indexRes, err := sess.Query(fmt.Sprintf("PRAGMA index_info(`%s`)", indexName))
+ if err != nil {
+ return err
+ }
+ if len(indexRes) != 1 {
+ continue
+ }
+ indexColumn := string(indexRes[0]["name"])
+ for _, name := range columnNames {
+ if name == indexColumn {
+ _, err := sess.Exec(fmt.Sprintf("DROP INDEX `%s`", indexName))
+ if err != nil {
+ return err
+ }
+ }
+ }
+ }
+
+ // Here we need to get the columns from the original table
+ sql := fmt.Sprintf("SELECT sql FROM sqlite_master WHERE tbl_name='%s' and type='table'", tableName)
+ res, err := sess.Query(sql)
+ if err != nil {
+ return err
+ }
+ tableSQL := string(res[0]["sql"])
+
+ // Get the string offset for column definitions: `CREATE TABLE ( column-definitions... )`
+ columnDefinitionsIndex := strings.Index(tableSQL, "(")
+ if columnDefinitionsIndex < 0 {
+ return errors.New("couldn't find column definitions")
+ }
+
+ // Separate out the column definitions
+ tableSQL = tableSQL[columnDefinitionsIndex:]
+
+ // Remove the required columnNames
+ for _, name := range columnNames {
+ tableSQL = regexp.MustCompile(regexp.QuoteMeta("`"+name+"`")+"[^`,)]*?[,)]").ReplaceAllString(tableSQL, "")
+ }
+
+ // Ensure the query is ended properly
+ tableSQL = strings.TrimSpace(tableSQL)
+ if tableSQL[len(tableSQL)-1] != ')' {
+ if tableSQL[len(tableSQL)-1] == ',' {
+ tableSQL = tableSQL[:len(tableSQL)-1]
+ }
+ tableSQL += ")"
+ }
+
+ // Find all the columns in the table
+ columns := regexp.MustCompile("`([^`]*)`").FindAllString(tableSQL, -1)
+
+ tableSQL = fmt.Sprintf("CREATE TABLE `new_%s_new` ", tableName) + tableSQL
+ if _, err := sess.Exec(tableSQL); err != nil {
+ return err
+ }
+
+ // Now restore the data
+ columnsSeparated := strings.Join(columns, ",")
+ insertSQL := fmt.Sprintf("INSERT INTO `new_%s_new` (%s) SELECT %s FROM %s", tableName, columnsSeparated, columnsSeparated, tableName)
+ if _, err := sess.Exec(insertSQL); err != nil {
+ return err
+ }
+
+ // Now drop the old table
+ if _, err := sess.Exec(fmt.Sprintf("DROP TABLE `%s`", tableName)); err != nil {
+ return err
+ }
+
+ // Rename the table
+ if _, err := sess.Exec(fmt.Sprintf("ALTER TABLE `new_%s_new` RENAME TO `%s`", tableName, tableName)); err != nil {
+ return err
+ }
+
+ case setting.Database.Type.IsPostgreSQL():
+ cols := ""
+ for _, col := range columnNames {
+ if cols != "" {
+ cols += ", "
+ }
+ cols += "DROP COLUMN `" + col + "` CASCADE"
+ }
+ if _, err := sess.Exec(fmt.Sprintf("ALTER TABLE `%s` %s", tableName, cols)); err != nil {
+ return fmt.Errorf("Drop table `%s` columns %v: %v", tableName, columnNames, err)
+ }
+ case setting.Database.Type.IsMySQL():
+ // Drop indexes on columns first
+ sql := fmt.Sprintf("SHOW INDEX FROM %s WHERE column_name IN ('%s')", tableName, strings.Join(columnNames, "','"))
+ res, err := sess.Query(sql)
+ if err != nil {
+ return err
+ }
+ for _, index := range res {
+ indexName := index["column_name"]
+ if len(indexName) > 0 {
+ _, err := sess.Exec(fmt.Sprintf("DROP INDEX `%s` ON `%s`", indexName, tableName))
+ if err != nil {
+ return err
+ }
+ }
+ }
+
+ // Now drop the columns
+ cols := ""
+ for _, col := range columnNames {
+ if cols != "" {
+ cols += ", "
+ }
+ cols += "DROP COLUMN `" + col + "`"
+ }
+ if _, err := sess.Exec(fmt.Sprintf("ALTER TABLE `%s` %s", tableName, cols)); err != nil {
+ return fmt.Errorf("Drop table `%s` columns %v: %v", tableName, columnNames, err)
+ }
+ default:
+ log.Fatal("Unrecognized DB")
+ }
+
+ return nil
+}
+
+// ModifyColumn will modify column's type or other property. SQLITE is not supported
+func ModifyColumn(x *xorm.Engine, tableName string, col *schemas.Column) error {
+ var indexes map[string]*schemas.Index
+ var err error
+
+ defer func() {
+ for _, index := range indexes {
+ _, err = x.Exec(x.Dialect().CreateIndexSQL(tableName, index))
+ if err != nil {
+ log.Error("Create index %s on table %s failed: %v", index.Name, tableName, err)
+ }
+ }
+ }()
+
+ alterSQL := x.Dialect().ModifyColumnSQL(tableName, col)
+ if _, err := x.Exec(alterSQL); err != nil {
+ return err
+ }
+ return nil
+}
diff --git a/models/migrations/base/db_test.go b/models/migrations/base/db_test.go
new file mode 100644
index 0000000..4010a14
--- /dev/null
+++ b/models/migrations/base/db_test.go
@@ -0,0 +1,98 @@
+// Copyright 2022 The Gitea Authors. All rights reserved.
+// SPDX-License-Identifier: MIT
+
+package base
+
+import (
+ "testing"
+
+ migrations_tests "code.gitea.io/gitea/models/migrations/test"
+ "code.gitea.io/gitea/modules/timeutil"
+
+ "xorm.io/xorm/names"
+)
+
+func Test_DropTableColumns(t *testing.T) {
+ x, deferable := migrations_tests.PrepareTestEnv(t, 0)
+ if x == nil || t.Failed() {
+ defer deferable()
+ return
+ }
+ defer deferable()
+
+ type DropTest struct {
+ ID int64 `xorm:"pk autoincr"`
+ FirstColumn string
+ ToDropColumn string `xorm:"unique"`
+ AnotherColumn int64
+ CreatedUnix timeutil.TimeStamp `xorm:"INDEX created"`
+ UpdatedUnix timeutil.TimeStamp `xorm:"INDEX updated"`
+ }
+
+ columns := []string{
+ "first_column",
+ "to_drop_column",
+ "another_column",
+ "created_unix",
+ "updated_unix",
+ }
+
+ x.SetMapper(names.GonicMapper{})
+
+ for i := range columns {
+ if err := x.Sync(new(DropTest)); err != nil {
+ t.Errorf("unable to create DropTest table: %v", err)
+ return
+ }
+
+ sess := x.NewSession()
+ if err := sess.Begin(); err != nil {
+ sess.Close()
+ t.Errorf("unable to begin transaction: %v", err)
+ return
+ }
+ if err := DropTableColumns(sess, "drop_test", columns[i:]...); err != nil {
+ sess.Close()
+ t.Errorf("Unable to drop columns[%d:]: %s from drop_test: %v", i, columns[i:], err)
+ return
+ }
+ if err := sess.Commit(); err != nil {
+ sess.Close()
+ t.Errorf("unable to commit transaction: %v", err)
+ return
+ }
+ sess.Close()
+ if err := x.DropTables(new(DropTest)); err != nil {
+ t.Errorf("unable to drop table: %v", err)
+ return
+ }
+ for j := range columns[i+1:] {
+ if err := x.Sync(new(DropTest)); err != nil {
+ t.Errorf("unable to create DropTest table: %v", err)
+ return
+ }
+ dropcols := append([]string{columns[i]}, columns[j+i+1:]...)
+ sess := x.NewSession()
+ if err := sess.Begin(); err != nil {
+ sess.Close()
+ t.Errorf("unable to begin transaction: %v", err)
+ return
+ }
+ if err := DropTableColumns(sess, "drop_test", dropcols...); err != nil {
+ sess.Close()
+ t.Errorf("Unable to drop columns: %s from drop_test: %v", dropcols, err)
+ return
+ }
+ if err := sess.Commit(); err != nil {
+ sess.Close()
+ t.Errorf("unable to commit transaction: %v", err)
+ return
+ }
+ sess.Close()
+ if err := x.DropTables(new(DropTest)); err != nil {
+ t.Errorf("unable to drop table: %v", err)
+ return
+ }
+ }
+ }
+}
diff --git a/models/migrations/base/hash.go b/models/migrations/base/hash.go
new file mode 100644
index 0000000..00fd1ef
--- /dev/null
+++ b/models/migrations/base/hash.go
@@ -0,0 +1,16 @@
+// Copyright 2022 The Gitea Authors. All rights reserved.
+// SPDX-License-Identifier: MIT
+
+package base
+
+import (
+ "crypto/sha256"
+ "encoding/hex"
+
+ "golang.org/x/crypto/pbkdf2"
+)
+
+func HashToken(token, salt string) string {
+ tempHash := pbkdf2.Key([]byte(token), []byte(salt), 10000, 50, sha256.New)
+ return hex.EncodeToString(tempHash)
+}
diff --git a/models/migrations/base/main_test.go b/models/migrations/base/main_test.go
new file mode 100644
index 0000000..c625ef0
--- /dev/null
+++ b/models/migrations/base/main_test.go
@@ -0,0 +1,14 @@
+// Copyright 2021 The Gitea Authors. All rights reserved.
+// SPDX-License-Identifier: MIT
+
+package base
+
+import (
+ "testing"
+
+ migrations_tests "code.gitea.io/gitea/models/migrations/test"
+)
+
+func TestMain(m *testing.M) {
+ migrations_tests.MainTest(m)
+}