1// Copyright 2015 The Gogs Authors. All rights reserved. 2// Copyright 2017 The Gitea Authors. All rights reserved. 3// Use of this source code is governed by a MIT-style 4// license that can be found in the LICENSE file. 5 6package migrations 7 8import ( 9 "context" 10 "errors" 11 "fmt" 12 "os" 13 "reflect" 14 "regexp" 15 "strings" 16 17 "code.gitea.io/gitea/modules/log" 18 "code.gitea.io/gitea/modules/setting" 19 20 "xorm.io/xorm" 21 "xorm.io/xorm/names" 22 "xorm.io/xorm/schemas" 23) 24 25const minDBVersion = 70 // Gitea 1.5.3 26 27// Migration describes on migration from lower version to high version 28type Migration interface { 29 Description() string 30 Migrate(*xorm.Engine) error 31} 32 33type migration struct { 34 description string 35 migrate func(*xorm.Engine) error 36} 37 38// NewMigration creates a new migration 39func NewMigration(desc string, fn func(*xorm.Engine) error) Migration { 40 return &migration{desc, fn} 41} 42 43// Description returns the migration's description 44func (m *migration) Description() string { 45 return m.description 46} 47 48// Migrate executes the migration 49func (m *migration) Migrate(x *xorm.Engine) error { 50 return m.migrate(x) 51} 52 53// Version describes the version table. Should have only one row with id==1 54type Version struct { 55 ID int64 `xorm:"pk autoincr"` 56 Version int64 57} 58 59// This is a sequence of migrations. Add new migrations to the bottom of the list. 60// If you want to "retire" a migration, remove it from the top of the list and 61// update minDBVersion accordingly 62var migrations = []Migration{ 63 // Gitea 1.5.0 ends at v69 64 // v70 -> v71 65 NewMigration("add issue_dependencies", addIssueDependencies), 66 // v71 -> v72 67 NewMigration("protect each scratch token", addScratchHash), 68 // v72 -> v73 69 NewMigration("add review", addReview), 70 71 // Gitea 1.6.0 ends at v73 72 73 // v73 -> v74 74 NewMigration("add must_change_password column for users table", addMustChangePassword), 75 // v74 -> v75 76 NewMigration("add approval whitelists to protected branches", addApprovalWhitelistsToProtectedBranches), 77 // v75 -> v76 78 NewMigration("clear nonused data which not deleted when user was deleted", clearNonusedData), 79 80 // Gitea 1.7.0 ends at v76 81 82 // v76 -> v77 83 NewMigration("add pull request rebase with merge commit", addPullRequestRebaseWithMerge), 84 // v77 -> v78 85 NewMigration("add theme to users", addUserDefaultTheme), 86 // v78 -> v79 87 NewMigration("rename repo is_bare to repo is_empty", renameRepoIsBareToIsEmpty), 88 // v79 -> v80 89 NewMigration("add can close issues via commit in any branch", addCanCloseIssuesViaCommitInAnyBranch), 90 // v80 -> v81 91 NewMigration("add is locked to issues", addIsLockedToIssues), 92 // v81 -> v82 93 NewMigration("update U2F counter type", changeU2FCounterType), 94 95 // Gitea 1.8.0 ends at v82 96 97 // v82 -> v83 98 NewMigration("hot fix for wrong release sha1 on release table", fixReleaseSha1OnReleaseTable), 99 // v83 -> v84 100 NewMigration("add uploader id for table attachment", addUploaderIDForAttachment), 101 // v84 -> v85 102 NewMigration("add table to store original imported gpg keys", addGPGKeyImport), 103 // v85 -> v86 104 NewMigration("hash application token", hashAppToken), 105 // v86 -> v87 106 NewMigration("add http method to webhook", addHTTPMethodToWebhook), 107 // v87 -> v88 108 NewMigration("add avatar field to repository", addAvatarFieldToRepository), 109 110 // Gitea 1.9.0 ends at v88 111 112 // v88 -> v89 113 NewMigration("add commit status context field to commit_status", addCommitStatusContext), 114 // v89 -> v90 115 NewMigration("add original author/url migration info to issues, comments, and repo ", addOriginalMigrationInfo), 116 // v90 -> v91 117 NewMigration("change length of some repository columns", changeSomeColumnsLengthOfRepo), 118 // v91 -> v92 119 NewMigration("add index on owner_id of repository and type, review_id of comment", addIndexOnRepositoryAndComment), 120 // v92 -> v93 121 NewMigration("remove orphaned repository index statuses", removeLingeringIndexStatus), 122 // v93 -> v94 123 NewMigration("add email notification enabled preference to user", addEmailNotificationEnabledToUser), 124 // v94 -> v95 125 NewMigration("add enable_status_check, status_check_contexts to protected_branch", addStatusCheckColumnsForProtectedBranches), 126 // v95 -> v96 127 NewMigration("add table columns for cross referencing issues", addCrossReferenceColumns), 128 // v96 -> v97 129 NewMigration("delete orphaned attachments", deleteOrphanedAttachments), 130 // v97 -> v98 131 NewMigration("add repo_admin_change_team_access to user", addRepoAdminChangeTeamAccessColumnForUser), 132 // v98 -> v99 133 NewMigration("add original author name and id on migrated release", addOriginalAuthorOnMigratedReleases), 134 // v99 -> v100 135 NewMigration("add task table and status column for repository table", addTaskTable), 136 // v100 -> v101 137 NewMigration("update migration repositories' service type", updateMigrationServiceTypes), 138 // v101 -> v102 139 NewMigration("change length of some external login users columns", changeSomeColumnsLengthOfExternalLoginUser), 140 141 // Gitea 1.10.0 ends at v102 142 143 // v102 -> v103 144 NewMigration("update migration repositories' service type", dropColumnHeadUserNameOnPullRequest), 145 // v103 -> v104 146 NewMigration("Add WhitelistDeployKeys to protected branch", addWhitelistDeployKeysToBranches), 147 // v104 -> v105 148 NewMigration("remove unnecessary columns from label", removeLabelUneededCols), 149 // v105 -> v106 150 NewMigration("add includes_all_repositories to teams", addTeamIncludesAllRepositories), 151 // v106 -> v107 152 NewMigration("add column `mode` to table watch", addModeColumnToWatch), 153 // v107 -> v108 154 NewMigration("Add template options to repository", addTemplateToRepo), 155 // v108 -> v109 156 NewMigration("Add comment_id on table notification", addCommentIDOnNotification), 157 // v109 -> v110 158 NewMigration("add can_create_org_repo to team", addCanCreateOrgRepoColumnForTeam), 159 // v110 -> v111 160 NewMigration("change review content type to text", changeReviewContentToText), 161 // v111 -> v112 162 NewMigration("update branch protection for can push and whitelist enable", addBranchProtectionCanPushAndEnableWhitelist), 163 // v112 -> v113 164 NewMigration("remove release attachments which repository deleted", removeAttachmentMissedRepo), 165 // v113 -> v114 166 NewMigration("new feature: change target branch of pull requests", featureChangeTargetBranch), 167 // v114 -> v115 168 NewMigration("Remove authentication credentials from stored URL", sanitizeOriginalURL), 169 // v115 -> v116 170 NewMigration("add user_id prefix to existing user avatar name", renameExistingUserAvatarName), 171 // v116 -> v117 172 NewMigration("Extend TrackedTimes", extendTrackedTimes), 173 174 // Gitea 1.11.0 ends at v117 175 176 // v117 -> v118 177 NewMigration("Add block on rejected reviews branch protection", addBlockOnRejectedReviews), 178 // v118 -> v119 179 NewMigration("Add commit id and stale to reviews", addReviewCommitAndStale), 180 // v119 -> v120 181 NewMigration("Fix migrated repositories' git service type", fixMigratedRepositoryServiceType), 182 // v120 -> v121 183 NewMigration("Add owner_name on table repository", addOwnerNameOnRepository), 184 // v121 -> v122 185 NewMigration("add is_restricted column for users table", addIsRestricted), 186 // v122 -> v123 187 NewMigration("Add Require Signed Commits to ProtectedBranch", addRequireSignedCommits), 188 // v123 -> v124 189 NewMigration("Add original information for reactions", addReactionOriginals), 190 // v124 -> v125 191 NewMigration("Add columns to user and repository", addUserRepoMissingColumns), 192 // v125 -> v126 193 NewMigration("Add some columns on review for migration", addReviewMigrateInfo), 194 // v126 -> v127 195 NewMigration("Fix topic repository count", fixTopicRepositoryCount), 196 // v127 -> v128 197 NewMigration("add repository code language statistics", addLanguageStats), 198 // v128 -> v129 199 NewMigration("fix merge base for pull requests", fixMergeBase), 200 // v129 -> v130 201 NewMigration("remove dependencies from deleted repositories", purgeUnusedDependencies), 202 // v130 -> v131 203 NewMigration("Expand webhooks for more granularity", expandWebhooks), 204 // v131 -> v132 205 NewMigration("Add IsSystemWebhook column to webhooks table", addSystemWebhookColumn), 206 // v132 -> v133 207 NewMigration("Add Branch Protection Protected Files Column", addBranchProtectionProtectedFilesColumn), 208 // v133 -> v134 209 NewMigration("Add EmailHash Table", addEmailHashTable), 210 // v134 -> v135 211 NewMigration("Refix merge base for merged pull requests", refixMergeBase), 212 // v135 -> v136 213 NewMigration("Add OrgID column to Labels table", addOrgIDLabelColumn), 214 // v136 -> v137 215 NewMigration("Add CommitsAhead and CommitsBehind Column to PullRequest Table", addCommitDivergenceToPulls), 216 // v137 -> v138 217 NewMigration("Add Branch Protection Block Outdated Branch", addBlockOnOutdatedBranch), 218 // v138 -> v139 219 NewMigration("Add ResolveDoerID to Comment table", addResolveDoerIDCommentColumn), 220 // v139 -> v140 221 NewMigration("prepend refs/heads/ to issue refs", prependRefsHeadsToIssueRefs), 222 223 // Gitea 1.12.0 ends at v140 224 225 // v140 -> v141 226 NewMigration("Save detected language file size to database instead of percent", fixLanguageStatsToSaveSize), 227 // v141 -> v142 228 NewMigration("Add KeepActivityPrivate to User table", addKeepActivityPrivateUserColumn), 229 // v142 -> v143 230 NewMigration("Ensure Repository.IsArchived is not null", setIsArchivedToFalse), 231 // v143 -> v144 232 NewMigration("recalculate Stars number for all user", recalculateStars), 233 // v144 -> v145 234 NewMigration("update Matrix Webhook http method to 'PUT'", updateMatrixWebhookHTTPMethod), 235 // v145 -> v146 236 NewMigration("Increase Language field to 50 in LanguageStats", increaseLanguageField), 237 // v146 -> v147 238 NewMigration("Add projects info to repository table", addProjectsInfo), 239 // v147 -> v148 240 NewMigration("create review for 0 review id code comments", createReviewsForCodeComments), 241 // v148 -> v149 242 NewMigration("remove issue dependency comments who refer to non existing issues", purgeInvalidDependenciesComments), 243 // v149 -> v150 244 NewMigration("Add Created and Updated to Milestone table", addCreatedAndUpdatedToMilestones), 245 // v150 -> v151 246 NewMigration("add primary key to repo_topic", addPrimaryKeyToRepoTopic), 247 // v151 -> v152 248 NewMigration("set default password algorithm to Argon2", setDefaultPasswordToArgon2), 249 // v152 -> v153 250 NewMigration("add TrustModel field to Repository", addTrustModelToRepository), 251 // v153 > v154 252 NewMigration("add Team review request support", addTeamReviewRequestSupport), 253 // v154 > v155 254 NewMigration("add timestamps to Star, Label, Follow, Watch and Collaboration", addTimeStamps), 255 256 // Gitea 1.13.0 ends at v155 257 258 // v155 -> v156 259 NewMigration("add changed_protected_files column for pull_request table", addChangedProtectedFilesPullRequestColumn), 260 // v156 -> v157 261 NewMigration("fix publisher ID for tag releases", fixPublisherIDforTagReleases), 262 // v157 -> v158 263 NewMigration("ensure repo topics are up-to-date", fixRepoTopics), 264 // v158 -> v159 265 NewMigration("code comment replies should have the commitID of the review they are replying to", updateCodeCommentReplies), 266 // v159 -> v160 267 NewMigration("update reactions constraint", updateReactionConstraint), 268 // v160 -> v161 269 NewMigration("Add block on official review requests branch protection", addBlockOnOfficialReviewRequests), 270 // v161 -> v162 271 NewMigration("Convert task type from int to string", convertTaskTypeToString), 272 // v162 -> v163 273 NewMigration("Convert webhook task type from int to string", convertWebhookTaskTypeToString), 274 // v163 -> v164 275 NewMigration("Convert topic name from 25 to 50", convertTopicNameFrom25To50), 276 // v164 -> v165 277 NewMigration("Add scope and nonce columns to oauth2_grant table", addScopeAndNonceColumnsToOAuth2Grant), 278 // v165 -> v166 279 NewMigration("Convert hook task type from char(16) to varchar(16) and trim the column", convertHookTaskTypeToVarcharAndTrim), 280 // v166 -> v167 281 NewMigration("Where Password is Valid with Empty String delete it", recalculateUserEmptyPWD), 282 // v167 -> v168 283 NewMigration("Add user redirect", addUserRedirect), 284 // v168 -> v169 285 NewMigration("Recreate user table to fix default values", recreateUserTableToFixDefaultValues), 286 // v169 -> v170 287 NewMigration("Update DeleteBranch comments to set the old_ref to the commit_sha", commentTypeDeleteBranchUseOldRef), 288 // v170 -> v171 289 NewMigration("Add Dismissed to Review table", addDismissedReviewColumn), 290 // v171 -> v172 291 NewMigration("Add Sorting to ProjectBoard table", addSortingColToProjectBoard), 292 // v172 -> v173 293 NewMigration("Add sessions table for go-chi/session", addSessionTable), 294 // v173 -> v174 295 NewMigration("Add time_id column to Comment", addTimeIDCommentColumn), 296 // v174 -> v175 297 NewMigration("Create repo transfer table", addRepoTransfer), 298 // v175 -> v176 299 NewMigration("Fix Postgres ID Sequences broken by recreate-table", fixPostgresIDSequences), 300 // v176 -> v177 301 NewMigration("Remove invalid labels from comments", removeInvalidLabels), 302 // v177 -> v178 303 NewMigration("Delete orphaned IssueLabels", deleteOrphanedIssueLabels), 304 305 // Gitea 1.14.0 ends at v178 306 307 // v178 -> v179 308 NewMigration("Add LFS columns to Mirror", addLFSMirrorColumns), 309 // v179 -> v180 310 NewMigration("Convert avatar url to text", convertAvatarURLToText), 311 // v180 -> v181 312 NewMigration("Delete credentials from past migrations", deleteMigrationCredentials), 313 // v181 -> v182 314 NewMigration("Always save primary email on email address table", addPrimaryEmail2EmailAddress), 315 // v182 -> v183 316 NewMigration("Add issue resource index table", addIssueResourceIndexTable), 317 // v183 -> v184 318 NewMigration("Create PushMirror table", createPushMirrorTable), 319 // v184 -> v185 320 NewMigration("Rename Task errors to message", renameTaskErrorsToMessage), 321 // v185 -> v186 322 NewMigration("Add new table repo_archiver", addRepoArchiver), 323 // v186 -> v187 324 NewMigration("Create protected tag table", createProtectedTagTable), 325 // v187 -> v188 326 NewMigration("Drop unneeded webhook related columns", dropWebhookColumns), 327 // v188 -> v189 328 NewMigration("Add key is verified to gpg key", addKeyIsVerified), 329 330 // Gitea 1.15.0 ends at v189 331 332 // v189 -> v190 333 NewMigration("Unwrap ldap.Sources", unwrapLDAPSourceCfg), 334 // v190 -> v191 335 NewMigration("Add agit flow pull request support", addAgitFlowPullRequest), 336 // v191 -> v192 337 NewMigration("Alter issue/comment table TEXT fields to LONGTEXT", alterIssueAndCommentTextFieldsToLongText), 338 // v192 -> v193 339 NewMigration("RecreateIssueResourceIndexTable to have a primary key instead of an unique index", recreateIssueResourceIndexTable), 340 // v193 -> v194 341 NewMigration("Add repo id column for attachment table", addRepoIDForAttachment), 342 // v194 -> v195 343 NewMigration("Add Branch Protection Unprotected Files Column", addBranchProtectionUnprotectedFilesColumn), 344 // v195 -> v196 345 NewMigration("Add table commit_status_index", addTableCommitStatusIndex), 346 // v196 -> v197 347 NewMigration("Add Color to ProjectBoard table", addColorColToProjectBoard), 348 // v197 -> v198 349 NewMigration("Add renamed_branch table", addRenamedBranchTable), 350 // v198 -> v199 351 NewMigration("Add issue content history table", addTableIssueContentHistory), 352 // v199 -> v200 353 NewMigration("No-op (remote version is using AppState now)", addRemoteVersionTableNoop), 354 // v200 -> v201 355 NewMigration("Add table app_state", addTableAppState), 356 // v201 -> v202 357 NewMigration("Drop table remote_version (if exists)", dropTableRemoteVersion), 358 // v202 -> v203 359 NewMigration("Create key/value table for user settings", createUserSettingsTable), 360 // v203 -> v204 361 NewMigration("Add Sorting to ProjectIssue table", addProjectIssueSorting), 362 // v204 -> v205 363 NewMigration("Add key is verified to ssh key", addSSHKeyIsVerified), 364 // v205 -> v206 365 NewMigration("Migrate to higher varchar on user struct", migrateUserPasswordSalt), 366 // v206 -> v207 367 NewMigration("Add authorize column to team_unit table", addAuthorizeColForTeamUnit), 368 // v207 -> v208 369 NewMigration("Add webauthn table and migrate u2f data to webauthn - NO-OPED", addWebAuthnCred), 370 // v208 -> v209 371 NewMigration("Use base32.HexEncoding instead of base64 encoding for cred ID as it is case insensitive - NO-OPED", useBase32HexForCredIDInWebAuthnCredential), 372 // v209 -> v210 373 NewMigration("Increase WebAuthentication CredentialID size to 410 - NO-OPED", increaseCredentialIDTo410), 374 // v210 -> v211 375 NewMigration("v208 was completely broken - remigrate", remigrateU2FCredentials), 376} 377 378// GetCurrentDBVersion returns the current db version 379func GetCurrentDBVersion(x *xorm.Engine) (int64, error) { 380 if err := x.Sync(new(Version)); err != nil { 381 return -1, fmt.Errorf("sync: %v", err) 382 } 383 384 currentVersion := &Version{ID: 1} 385 has, err := x.Get(currentVersion) 386 if err != nil { 387 return -1, fmt.Errorf("get: %v", err) 388 } 389 if !has { 390 return -1, nil 391 } 392 return currentVersion.Version, nil 393} 394 395// ExpectedVersion returns the expected db version 396func ExpectedVersion() int64 { 397 return int64(minDBVersion + len(migrations)) 398} 399 400// EnsureUpToDate will check if the db is at the correct version 401func EnsureUpToDate(x *xorm.Engine) error { 402 currentDB, err := GetCurrentDBVersion(x) 403 if err != nil { 404 return err 405 } 406 407 if currentDB < 0 { 408 return fmt.Errorf("Database has not been initialised") 409 } 410 411 if minDBVersion > currentDB { 412 return fmt.Errorf("DB version %d (<= %d) is too old for auto-migration. Upgrade to Gitea 1.6.4 first then upgrade to this version", currentDB, minDBVersion) 413 } 414 415 expected := ExpectedVersion() 416 417 if currentDB != expected { 418 return fmt.Errorf(`Current database version %d is not equal to the expected version %d. Please run "gitea [--config /path/to/app.ini] migrate" to update the database version`, currentDB, expected) 419 } 420 421 return nil 422} 423 424// Migrate database to current version 425func Migrate(x *xorm.Engine) error { 426 // Set a new clean the default mapper to GonicMapper as that is the default for Gitea. 427 x.SetMapper(names.GonicMapper{}) 428 if err := x.Sync(new(Version)); err != nil { 429 return fmt.Errorf("sync: %v", err) 430 } 431 432 currentVersion := &Version{ID: 1} 433 has, err := x.Get(currentVersion) 434 if err != nil { 435 return fmt.Errorf("get: %v", err) 436 } else if !has { 437 // If the version record does not exist we think 438 // it is a fresh installation and we can skip all migrations. 439 currentVersion.ID = 0 440 currentVersion.Version = int64(minDBVersion + len(migrations)) 441 442 if _, err = x.InsertOne(currentVersion); err != nil { 443 return fmt.Errorf("insert: %v", err) 444 } 445 } 446 447 v := currentVersion.Version 448 if minDBVersion > v { 449 log.Fatal(`Gitea no longer supports auto-migration from your previously installed version. 450Please try upgrading to a lower version first (suggested v1.6.4), then upgrade to this version.`) 451 return nil 452 } 453 454 // Downgrading Gitea's database version not supported 455 if int(v-minDBVersion) > len(migrations) { 456 msg := fmt.Sprintf("Your database (migration version: %d) is for a newer Gita, you can not use the newer database for this old Gitea release (%d).", v, minDBVersion+len(migrations)) 457 msg += "\nGitea will exit to keep your database safe and unchanged. Please use the correct Gitea release, do not change the migration version manually (incorrect manual operation may lose data)." 458 if !setting.IsProd { 459 msg += fmt.Sprintf("\nIf you are in development and really know what you're doing, you can force changing the migration version by executing: UPDATE version SET version=%d WHERE id=1;", minDBVersion+len(migrations)) 460 } 461 _, _ = fmt.Fprintln(os.Stderr, msg) 462 log.Fatal(msg) 463 return nil 464 } 465 466 // Migrate 467 for i, m := range migrations[v-minDBVersion:] { 468 log.Info("Migration[%d]: %s", v+int64(i), m.Description()) 469 // Reset the mapper between each migration - migrations are not supposed to depend on each other 470 x.SetMapper(names.GonicMapper{}) 471 if err = m.Migrate(x); err != nil { 472 return fmt.Errorf("migration[%d]: %s failed: %v", v+int64(i), m.Description(), err) 473 } 474 currentVersion.Version = v + int64(i) + 1 475 if _, err = x.ID(1).Update(currentVersion); err != nil { 476 return err 477 } 478 } 479 return nil 480} 481 482// RecreateTables will recreate the tables for the provided beans using the newly provided bean definition and move all data to that new table 483// WARNING: YOU MUST PROVIDE THE FULL BEAN DEFINITION 484func RecreateTables(beans ...interface{}) func(*xorm.Engine) error { 485 return func(x *xorm.Engine) error { 486 sess := x.NewSession() 487 defer sess.Close() 488 if err := sess.Begin(); err != nil { 489 return err 490 } 491 sess = sess.StoreEngine("InnoDB") 492 for _, bean := range beans { 493 log.Info("Recreating Table: %s for Bean: %s", x.TableName(bean), reflect.Indirect(reflect.ValueOf(bean)).Type().Name()) 494 if err := recreateTable(sess, bean); err != nil { 495 return err 496 } 497 } 498 return sess.Commit() 499 } 500} 501 502// recreateTable will recreate the table using the newly provided bean definition and move all data to that new table 503// WARNING: YOU MUST PROVIDE THE FULL BEAN DEFINITION 504// WARNING: YOU MUST COMMIT THE SESSION AT THE END 505func recreateTable(sess *xorm.Session, bean interface{}) error { 506 // TODO: This will not work if there are foreign keys 507 508 tableName := sess.Engine().TableName(bean) 509 tempTableName := fmt.Sprintf("tmp_recreate__%s", tableName) 510 511 // We need to move the old table away and create a new one with the correct columns 512 // We will need to do this in stages to prevent data loss 513 // 514 // First create the temporary table 515 if err := sess.Table(tempTableName).CreateTable(bean); err != nil { 516 log.Error("Unable to create table %s. Error: %v", tempTableName, err) 517 return err 518 } 519 520 if err := sess.Table(tempTableName).CreateUniques(bean); err != nil { 521 log.Error("Unable to create uniques for table %s. Error: %v", tempTableName, err) 522 return err 523 } 524 525 if err := sess.Table(tempTableName).CreateIndexes(bean); err != nil { 526 log.Error("Unable to create indexes for table %s. Error: %v", tempTableName, err) 527 return err 528 } 529 530 // Work out the column names from the bean - these are the columns to select from the old table and install into the new table 531 table, err := sess.Engine().TableInfo(bean) 532 if err != nil { 533 log.Error("Unable to get table info. Error: %v", err) 534 535 return err 536 } 537 newTableColumns := table.Columns() 538 if len(newTableColumns) == 0 { 539 return fmt.Errorf("no columns in new table") 540 } 541 hasID := false 542 for _, column := range newTableColumns { 543 hasID = hasID || (column.IsPrimaryKey && column.IsAutoIncrement) 544 } 545 546 if hasID && setting.Database.UseMSSQL { 547 if _, err := sess.Exec(fmt.Sprintf("SET IDENTITY_INSERT `%s` ON", tempTableName)); err != nil { 548 log.Error("Unable to set identity insert for table %s. Error: %v", tempTableName, err) 549 return err 550 } 551 } 552 553 sqlStringBuilder := &strings.Builder{} 554 _, _ = sqlStringBuilder.WriteString("INSERT INTO `") 555 _, _ = sqlStringBuilder.WriteString(tempTableName) 556 _, _ = sqlStringBuilder.WriteString("` (`") 557 _, _ = sqlStringBuilder.WriteString(newTableColumns[0].Name) 558 _, _ = sqlStringBuilder.WriteString("`") 559 for _, column := range newTableColumns[1:] { 560 _, _ = sqlStringBuilder.WriteString(", `") 561 _, _ = sqlStringBuilder.WriteString(column.Name) 562 _, _ = sqlStringBuilder.WriteString("`") 563 } 564 _, _ = sqlStringBuilder.WriteString(")") 565 _, _ = sqlStringBuilder.WriteString(" SELECT ") 566 if newTableColumns[0].Default != "" { 567 _, _ = sqlStringBuilder.WriteString("COALESCE(`") 568 _, _ = sqlStringBuilder.WriteString(newTableColumns[0].Name) 569 _, _ = sqlStringBuilder.WriteString("`, ") 570 _, _ = sqlStringBuilder.WriteString(newTableColumns[0].Default) 571 _, _ = sqlStringBuilder.WriteString(")") 572 } else { 573 _, _ = sqlStringBuilder.WriteString("`") 574 _, _ = sqlStringBuilder.WriteString(newTableColumns[0].Name) 575 _, _ = sqlStringBuilder.WriteString("`") 576 } 577 578 for _, column := range newTableColumns[1:] { 579 if column.Default != "" { 580 _, _ = sqlStringBuilder.WriteString(", COALESCE(`") 581 _, _ = sqlStringBuilder.WriteString(column.Name) 582 _, _ = sqlStringBuilder.WriteString("`, ") 583 _, _ = sqlStringBuilder.WriteString(column.Default) 584 _, _ = sqlStringBuilder.WriteString(")") 585 } else { 586 _, _ = sqlStringBuilder.WriteString(", `") 587 _, _ = sqlStringBuilder.WriteString(column.Name) 588 _, _ = sqlStringBuilder.WriteString("`") 589 } 590 } 591 _, _ = sqlStringBuilder.WriteString(" FROM `") 592 _, _ = sqlStringBuilder.WriteString(tableName) 593 _, _ = sqlStringBuilder.WriteString("`") 594 595 if _, err := sess.Exec(sqlStringBuilder.String()); err != nil { 596 log.Error("Unable to set copy data in to temp table %s. Error: %v", tempTableName, err) 597 return err 598 } 599 600 if hasID && setting.Database.UseMSSQL { 601 if _, err := sess.Exec(fmt.Sprintf("SET IDENTITY_INSERT `%s` OFF", tempTableName)); err != nil { 602 log.Error("Unable to switch off identity insert for table %s. Error: %v", tempTableName, err) 603 return err 604 } 605 } 606 607 switch { 608 case setting.Database.UseSQLite3: 609 // SQLite will drop all the constraints on the old table 610 if _, err := sess.Exec(fmt.Sprintf("DROP TABLE `%s`", tableName)); err != nil { 611 log.Error("Unable to drop old table %s. Error: %v", tableName, err) 612 return err 613 } 614 615 if err := sess.Table(tempTableName).DropIndexes(bean); err != nil { 616 log.Error("Unable to drop indexes on temporary table %s. Error: %v", tempTableName, err) 617 return err 618 } 619 620 if _, err := sess.Exec(fmt.Sprintf("ALTER TABLE `%s` RENAME TO `%s`", tempTableName, tableName)); err != nil { 621 log.Error("Unable to rename %s to %s. Error: %v", tempTableName, tableName, err) 622 return err 623 } 624 625 if err := sess.Table(tableName).CreateIndexes(bean); err != nil { 626 log.Error("Unable to recreate indexes on table %s. Error: %v", tableName, err) 627 return err 628 } 629 630 if err := sess.Table(tableName).CreateUniques(bean); err != nil { 631 log.Error("Unable to recreate uniques on table %s. Error: %v", tableName, err) 632 return err 633 } 634 635 case setting.Database.UseMySQL: 636 // MySQL will drop all the constraints on the old table 637 if _, err := sess.Exec(fmt.Sprintf("DROP TABLE `%s`", tableName)); err != nil { 638 log.Error("Unable to drop old table %s. Error: %v", tableName, err) 639 return err 640 } 641 642 if err := sess.Table(tempTableName).DropIndexes(bean); err != nil { 643 log.Error("Unable to drop indexes on temporary table %s. Error: %v", tempTableName, err) 644 return err 645 } 646 647 // SQLite and MySQL will move all the constraints from the temporary table to the new table 648 if _, err := sess.Exec(fmt.Sprintf("ALTER TABLE `%s` RENAME TO `%s`", tempTableName, tableName)); err != nil { 649 log.Error("Unable to rename %s to %s. Error: %v", tempTableName, tableName, err) 650 return err 651 } 652 653 if err := sess.Table(tableName).CreateIndexes(bean); err != nil { 654 log.Error("Unable to recreate indexes on table %s. Error: %v", tableName, err) 655 return err 656 } 657 658 if err := sess.Table(tableName).CreateUniques(bean); err != nil { 659 log.Error("Unable to recreate uniques on table %s. Error: %v", tableName, err) 660 return err 661 } 662 case setting.Database.UsePostgreSQL: 663 var originalSequences []string 664 type sequenceData struct { 665 LastValue int `xorm:"'last_value'"` 666 IsCalled bool `xorm:"'is_called'"` 667 } 668 sequenceMap := map[string]sequenceData{} 669 670 schema := sess.Engine().Dialect().URI().Schema 671 sess.Engine().SetSchema("") 672 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 { 673 log.Error("Unable to rename %s to %s. Error: %v", tempTableName, tableName, err) 674 return err 675 } 676 sess.Engine().SetSchema(schema) 677 678 for _, sequence := range originalSequences { 679 sequenceData := sequenceData{} 680 if _, err := sess.Table(sequence).Cols("last_value", "is_called").Get(&sequenceData); err != nil { 681 log.Error("Unable to get last_value and is_called from %s. Error: %v", sequence, err) 682 return err 683 } 684 sequenceMap[sequence] = sequenceData 685 686 } 687 688 // CASCADE causes postgres to drop all the constraints on the old table 689 if _, err := sess.Exec(fmt.Sprintf("DROP TABLE `%s` CASCADE", tableName)); err != nil { 690 log.Error("Unable to drop old table %s. Error: %v", tableName, err) 691 return err 692 } 693 694 // CASCADE causes postgres to move all the constraints from the temporary table to the new table 695 if _, err := sess.Exec(fmt.Sprintf("ALTER TABLE `%s` RENAME TO `%s`", tempTableName, tableName)); err != nil { 696 log.Error("Unable to rename %s to %s. Error: %v", tempTableName, tableName, err) 697 return err 698 } 699 700 var indices []string 701 sess.Engine().SetSchema("") 702 if err := sess.Table("pg_indexes").Cols("indexname").Where("tablename = ? ", tableName).Find(&indices); err != nil { 703 log.Error("Unable to rename %s to %s. Error: %v", tempTableName, tableName, err) 704 return err 705 } 706 sess.Engine().SetSchema(schema) 707 708 for _, index := range indices { 709 newIndexName := strings.Replace(index, "tmp_recreate__", "", 1) 710 if _, err := sess.Exec(fmt.Sprintf("ALTER INDEX `%s` RENAME TO `%s`", index, newIndexName)); err != nil { 711 log.Error("Unable to rename %s to %s. Error: %v", index, newIndexName, err) 712 return err 713 } 714 } 715 716 var sequences []string 717 sess.Engine().SetSchema("") 718 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 { 719 log.Error("Unable to rename %s to %s. Error: %v", tempTableName, tableName, err) 720 return err 721 } 722 sess.Engine().SetSchema(schema) 723 724 for _, sequence := range sequences { 725 newSequenceName := strings.Replace(sequence, "tmp_recreate__", "", 1) 726 if _, err := sess.Exec(fmt.Sprintf("ALTER SEQUENCE `%s` RENAME TO `%s`", sequence, newSequenceName)); err != nil { 727 log.Error("Unable to rename %s sequence to %s. Error: %v", sequence, newSequenceName, err) 728 return err 729 } 730 val, ok := sequenceMap[newSequenceName] 731 if newSequenceName == tableName+"_id_seq" { 732 if ok && val.LastValue != 0 { 733 if _, err := sess.Exec(fmt.Sprintf("SELECT setval('%s', %d, %t)", newSequenceName, val.LastValue, val.IsCalled)); err != nil { 734 log.Error("Unable to reset %s to %d. Error: %v", newSequenceName, val, err) 735 return err 736 } 737 } else { 738 // We're going to try to guess this 739 if _, err := sess.Exec(fmt.Sprintf("SELECT setval('%s', COALESCE((SELECT MAX(id)+1 FROM `%s`), 1), false)", newSequenceName, tableName)); err != nil { 740 log.Error("Unable to reset %s. Error: %v", newSequenceName, err) 741 return err 742 } 743 } 744 } else if ok { 745 if _, err := sess.Exec(fmt.Sprintf("SELECT setval('%s', %d, %t)", newSequenceName, val.LastValue, val.IsCalled)); err != nil { 746 log.Error("Unable to reset %s to %d. Error: %v", newSequenceName, val, err) 747 return err 748 } 749 } 750 751 } 752 753 case setting.Database.UseMSSQL: 754 // MSSQL will drop all the constraints on the old table 755 if _, err := sess.Exec(fmt.Sprintf("DROP TABLE `%s`", tableName)); err != nil { 756 log.Error("Unable to drop old table %s. Error: %v", tableName, err) 757 return err 758 } 759 760 // MSSQL sp_rename will move all the constraints from the temporary table to the new table 761 if _, err := sess.Exec(fmt.Sprintf("sp_rename `%s`,`%s`", tempTableName, tableName)); err != nil { 762 log.Error("Unable to rename %s to %s. Error: %v", tempTableName, tableName, err) 763 return err 764 } 765 766 default: 767 log.Fatal("Unrecognized DB") 768 } 769 return nil 770} 771 772// WARNING: YOU MUST COMMIT THE SESSION AT THE END 773func dropTableColumns(sess *xorm.Session, tableName string, columnNames ...string) (err error) { 774 if tableName == "" || len(columnNames) == 0 { 775 return nil 776 } 777 // TODO: This will not work if there are foreign keys 778 779 switch { 780 case setting.Database.UseSQLite3: 781 // First drop the indexes on the columns 782 res, errIndex := sess.Query(fmt.Sprintf("PRAGMA index_list(`%s`)", tableName)) 783 if errIndex != nil { 784 return errIndex 785 } 786 for _, row := range res { 787 indexName := row["name"] 788 indexRes, err := sess.Query(fmt.Sprintf("PRAGMA index_info(`%s`)", indexName)) 789 if err != nil { 790 return err 791 } 792 if len(indexRes) != 1 { 793 continue 794 } 795 indexColumn := string(indexRes[0]["name"]) 796 for _, name := range columnNames { 797 if name == indexColumn { 798 _, err := sess.Exec(fmt.Sprintf("DROP INDEX `%s`", indexName)) 799 if err != nil { 800 return err 801 } 802 } 803 } 804 } 805 806 // Here we need to get the columns from the original table 807 sql := fmt.Sprintf("SELECT sql FROM sqlite_master WHERE tbl_name='%s' and type='table'", tableName) 808 res, err := sess.Query(sql) 809 if err != nil { 810 return err 811 } 812 tableSQL := string(res[0]["sql"]) 813 814 // Get the string offset for column definitions: `CREATE TABLE ( column-definitions... )` 815 columnDefinitionsIndex := strings.Index(tableSQL, "(") 816 if columnDefinitionsIndex < 0 { 817 return errors.New("couldn't find column definitions") 818 } 819 820 // Separate out the column definitions 821 tableSQL = tableSQL[columnDefinitionsIndex:] 822 823 // Remove the required columnNames 824 for _, name := range columnNames { 825 tableSQL = regexp.MustCompile(regexp.QuoteMeta("`"+name+"`")+"[^`,)]*?[,)]").ReplaceAllString(tableSQL, "") 826 } 827 828 // Ensure the query is ended properly 829 tableSQL = strings.TrimSpace(tableSQL) 830 if tableSQL[len(tableSQL)-1] != ')' { 831 if tableSQL[len(tableSQL)-1] == ',' { 832 tableSQL = tableSQL[:len(tableSQL)-1] 833 } 834 tableSQL += ")" 835 } 836 837 // Find all the columns in the table 838 columns := regexp.MustCompile("`([^`]*)`").FindAllString(tableSQL, -1) 839 840 tableSQL = fmt.Sprintf("CREATE TABLE `new_%s_new` ", tableName) + tableSQL 841 if _, err := sess.Exec(tableSQL); err != nil { 842 return err 843 } 844 845 // Now restore the data 846 columnsSeparated := strings.Join(columns, ",") 847 insertSQL := fmt.Sprintf("INSERT INTO `new_%s_new` (%s) SELECT %s FROM %s", tableName, columnsSeparated, columnsSeparated, tableName) 848 if _, err := sess.Exec(insertSQL); err != nil { 849 return err 850 } 851 852 // Now drop the old table 853 if _, err := sess.Exec(fmt.Sprintf("DROP TABLE `%s`", tableName)); err != nil { 854 return err 855 } 856 857 // Rename the table 858 if _, err := sess.Exec(fmt.Sprintf("ALTER TABLE `new_%s_new` RENAME TO `%s`", tableName, tableName)); err != nil { 859 return err 860 } 861 862 case setting.Database.UsePostgreSQL: 863 cols := "" 864 for _, col := range columnNames { 865 if cols != "" { 866 cols += ", " 867 } 868 cols += "DROP COLUMN `" + col + "` CASCADE" 869 } 870 if _, err := sess.Exec(fmt.Sprintf("ALTER TABLE `%s` %s", tableName, cols)); err != nil { 871 return fmt.Errorf("Drop table `%s` columns %v: %v", tableName, columnNames, err) 872 } 873 case setting.Database.UseMySQL: 874 // Drop indexes on columns first 875 sql := fmt.Sprintf("SHOW INDEX FROM %s WHERE column_name IN ('%s')", tableName, strings.Join(columnNames, "','")) 876 res, err := sess.Query(sql) 877 if err != nil { 878 return err 879 } 880 for _, index := range res { 881 indexName := index["column_name"] 882 if len(indexName) > 0 { 883 _, err := sess.Exec(fmt.Sprintf("DROP INDEX `%s` ON `%s`", indexName, tableName)) 884 if err != nil { 885 return err 886 } 887 } 888 } 889 890 // Now drop the columns 891 cols := "" 892 for _, col := range columnNames { 893 if cols != "" { 894 cols += ", " 895 } 896 cols += "DROP COLUMN `" + col + "`" 897 } 898 if _, err := sess.Exec(fmt.Sprintf("ALTER TABLE `%s` %s", tableName, cols)); err != nil { 899 return fmt.Errorf("Drop table `%s` columns %v: %v", tableName, columnNames, err) 900 } 901 case setting.Database.UseMSSQL: 902 cols := "" 903 for _, col := range columnNames { 904 if cols != "" { 905 cols += ", " 906 } 907 cols += "`" + strings.ToLower(col) + "`" 908 } 909 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'))", 910 tableName, strings.ReplaceAll(cols, "`", "'")) 911 constraints := make([]string, 0) 912 if err := sess.SQL(sql).Find(&constraints); err != nil { 913 return fmt.Errorf("Find constraints: %v", err) 914 } 915 for _, constraint := range constraints { 916 if _, err := sess.Exec(fmt.Sprintf("ALTER TABLE `%s` DROP CONSTRAINT `%s`", tableName, constraint)); err != nil { 917 return fmt.Errorf("Drop table `%s` default constraint `%s`: %v", tableName, constraint, err) 918 } 919 } 920 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'))", 921 tableName, strings.ReplaceAll(cols, "`", "'")) 922 constraints = make([]string, 0) 923 if err := sess.SQL(sql).Find(&constraints); err != nil { 924 return fmt.Errorf("Find constraints: %v", err) 925 } 926 for _, constraint := range constraints { 927 if _, err := sess.Exec(fmt.Sprintf("DROP INDEX `%[2]s` ON `%[1]s`", tableName, constraint)); err != nil { 928 return fmt.Errorf("Drop index `%[2]s` on `%[1]s`: %v", tableName, constraint, err) 929 } 930 } 931 932 if _, err := sess.Exec(fmt.Sprintf("ALTER TABLE `%s` DROP COLUMN %s", tableName, cols)); err != nil { 933 return fmt.Errorf("Drop table `%s` columns %v: %v", tableName, columnNames, err) 934 } 935 default: 936 log.Fatal("Unrecognized DB") 937 } 938 939 return nil 940} 941 942// modifyColumn will modify column's type or other propertity. SQLITE is not supported 943func modifyColumn(x *xorm.Engine, tableName string, col *schemas.Column) error { 944 var indexes map[string]*schemas.Index 945 var err error 946 // MSSQL have to remove index at first, otherwise alter column will fail 947 // ref. https://sqlzealots.com/2018/05/09/error-message-the-index-is-dependent-on-column-alter-table-alter-column-failed-because-one-or-more-objects-access-this-column/ 948 if x.Dialect().URI().DBType == schemas.MSSQL { 949 indexes, err = x.Dialect().GetIndexes(x.DB(), context.Background(), tableName) 950 if err != nil { 951 return err 952 } 953 954 for _, index := range indexes { 955 _, err = x.Exec(x.Dialect().DropIndexSQL(tableName, index)) 956 if err != nil { 957 return err 958 } 959 } 960 } 961 962 defer func() { 963 for _, index := range indexes { 964 _, err = x.Exec(x.Dialect().CreateIndexSQL(tableName, index)) 965 if err != nil { 966 log.Error("Create index %s on table %s failed: %v", index.Name, tableName, err) 967 } 968 } 969 }() 970 971 alterSQL := x.Dialect().ModifyColumnSQL(tableName, col) 972 if _, err := x.Exec(alterSQL); err != nil { 973 return err 974 } 975 return nil 976} 977