You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@devlake.apache.org by kl...@apache.org on 2022/10/18 07:16:01 UTC
[incubator-devlake-website] 01/01: docs: migration script code example for v0.15
This is an automated email from the ASF dual-hosted git repository.
klesh pushed a commit to branch kw-devlake-3459-migration
in repository https://gitbox.apache.org/repos/asf/incubator-devlake-website.git
commit 9cc97181541093915453121942e129d9e79c4123
Author: Klesh Wong <zh...@merico.dev>
AuthorDate: Tue Oct 18 15:13:21 2022 +0800
docs: migration script code example for v0.15
---
docs/DeveloperManuals/DBMigration.md | 116 +++++------------------------------
1 file changed, 14 insertions(+), 102 deletions(-)
diff --git a/docs/DeveloperManuals/DBMigration.md b/docs/DeveloperManuals/DBMigration.md
index b4394d33c..2c42acb9f 100644
--- a/docs/DeveloperManuals/DBMigration.md
+++ b/docs/DeveloperManuals/DBMigration.md
@@ -55,112 +55,24 @@ for the framework-only migrations defined under the `models` package.
## Best Practices
-When you write a new migration script, please pay attention to the fault tolerance and the side effect. It would be better if the failed script could be safely retry, in case of something goes wrong during the migration. For this purpose, the migration scripts should be well-designed. For example, if you created a temporary table in the Up method, it should be dropped before exiting, regardless of success or failure. Using the defer statement to do some cleanup is a good idea. Let's demo [...]
-Suppose we want to recalculate the column `name` of the table `user`
+When you write a new migration script, please pay attention to the fault tolerance and the side effect. It would be better if the failed script could be safely retry, in case of something goes wrong during the migration. For this purpose, the migration scripts should be well-designed. For example, if you created a temporary table in the Up method, it should be dropped before exiting, regardless of success or failure.
-1. rename `user` to `user_bak` (stop if error, define `defer` to rename back on error)
-2. create new `user` (stop if error, define `defer` to drop TABLE on error)
-3. convert data from `user_bak` to `user` (stop if error)
-4. drop `user_bak`
+Suppose we want to change the type of the Primary Key `name` of table `users` from `int` to `varchar(255)`
-```golang
+1. Rename `users` to `users_20221018` (stop if error, otherwise define a `defer` to rename back on error)
+2. Create new `users` (stop if error, otherwise define a `defer` to drop the table on error)
+3. Convert data from `users_20221018` to `users` (stop if error)
+4. Drop table `users_20221018`
-type User struct {
- name string `gorm:"type:varchar(255)"`
-}
-
-func (User) TableName() string {
- return "user"
-}
+With these steps, the `defer` functions would be executed in reverse order if any error occurred during the migration process so the database would roll back to the original state in most cases.
-type NewUser struct {
- name string `gorm:"type:text"`
-}
+However, you don't neccessary deal with all these mess. We had summarized some of the most useful code examples for you to follow:
-func (NewUser) TableName() string {
- return "user"
-}
-
-type UserBak struct {
- name string `gorm:"type:varchar(255)"`
-}
-
-func (UserBak) TableName() string {
- return "user_bak"
-}
-
-func (*exampleScript) Up(ctx context.Context, db *gorm.DB) (errs errors.Error) {
- var err error
-
- // rename the user_bak to cache old table
- err = db.Migrator().RenameTable(&User{}, &UserBak{})
- if err != nil {
- return errors.Default.Wrap(err, "error no rename user to user_bak")
- }
-
- // rollback for rename back
- defer func() {
- if errs != nil {
- err = db.Migrator().RenameTable(&UserBak{}, &User{})
- if err != nil {
- errs = errors.Default.Wrap(err, fmt.Sprintf("fail to rollback table user_bak , you must to rollback by yourself. %s", err.Error()))
- }
- }
- }()
-
- // create new user table
- err = db.Migrator().AutoMigrate(&NewUser{})
-
- if err != nil {
- return errors.Default.Wrap(err, "error on auto migrate user")
- }
-
- // rollback for create new table
- defer func() {
- if errs != nil {
- err = db.Migrator().DropTable(&User{})
- if err != nil {
- errs = errors.Default.Wrap(err, fmt.Sprintf("fail to rollback table OldTable , you must to rollback by yourself. %s", err.Error()))
- }
- }
- }()
-
- // update old id to new id and write to the new table
- cursor, err := db.Model(&UserBak{}).Rows()
- if err != nil {
- return errors.Default.Wrap(err, "error on select NewTable")
- }
- defer cursor.Close()
-
- // caculate and save the data to new table
- batch, err := helper.NewBatchSave(api.BasicRes, reflect.TypeOf(&NewUser{}), 200)
- if err != nil {
- return errors.Default.Wrap(err, "error getting batch from table user")
- }
- defer batch.Close()
- for cursor.Next() {
- ot := UserBak{}
- err = db.ScanRows(cursor, &ot)
- if err != nil {
- return errors.Default.Wrap(err, "error scan rows from table user_bak")
- }
- nt := NewUser(ot)
-
- nt.name = nt.name + "new"
-
- err = batch.Add(&nt)
- if err != nil {
- return errors.Default.Wrap(err, "error on user batch add")
- }
- }
-
- // drop the old table
- err = db.Migrator().DropTable(&UserBak{})
- if err != nil {
- return errors.Default.Wrap(err, "error no drop user_bak")
- }
-}
-
-```
+- [Create new tables](https://github.com/apache/incubator-devlake/blob/main/models/migrationscripts/20220406_add_frame_tables.go)
+[Rename column](https://github.com/apache/incubator-devlake/blob/main/models/migrationscripts/20220505_rename_pipeline_step_to_stage.go)
+- [Add columns with default value](https://github.com/apache/incubator-devlake/blob/main/models/migrationscripts/20220616_add_blueprint_mode.go)
+- [Change the values(or type) of Primary Key](https://github.com/apache/incubator-devlake/blob/main/models/migrationscripts/20220913_fix_commitfile_id_toolong.go)
+- [Change the values(or type) of Column](https://github.com/apache/incubator-devlake/blob/main/models/migrationscripts/20220929_modify_lead_time_minutes.go)
+The above examples should cover most of the scenarios you may run into. Feel free to post issue on our github repo otherwise.