diff --git a/migrations/v13.go b/migrations/v13.go index 908ceac..f7c0a6d 100644 --- a/migrations/v13.go +++ b/migrations/v13.go @@ -1,58 +1,58 @@ /* * Copyright © 2021 Musing Studio LLC. * * This file is part of WriteFreely. * * WriteFreely is free software: you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License, included * in the LICENSE file in this source code package. */ package migrations func supportLetters(db *datastore) error { t, err := db.Begin() if err != nil { t.Rollback() return err } _, err = t.Exec(`CREATE TABLE publishjobs ( id ` + db.typeIntPrimaryKey() + `, post_id ` + db.typeVarChar(16) + ` not null, action ` + db.typeVarChar(16) + ` not null, delay ` + db.typeTinyInt() + ` not null )`) if err != nil { t.Rollback() return err } _, err = t.Exec(`CREATE TABLE emailsubscribers ( id ` + db.typeChar(8) + ` not null, collection_id ` + db.typeInt() + ` not null, user_id ` + db.typeInt() + ` null, email ` + db.typeVarChar(255) + ` null, subscribed ` + db.typeDateTime() + ` not null, token ` + db.typeChar(16) + ` not null, - confirmed ` + db.typeBool() + ` default 0 not null, - allow_export ` + db.typeBool() + ` default 0 not null, + confirmed ` + db.typeBool() + ` default ` + db.boolFalse() + ` not null, + allow_export ` + db.typeBool() + ` default ` + db.boolFalse() + ` not null, constraint eu_coll_email unique (collection_id, email), constraint eu_coll_user unique (collection_id, user_id), PRIMARY KEY (id) )`) if err != nil { t.Rollback() return err } err = t.Commit() if err != nil { t.Rollback() return err } return nil } diff --git a/migrations/v14.go b/migrations/v14.go index 2883001..14a8140 100644 --- a/migrations/v14.go +++ b/migrations/v14.go @@ -1,37 +1,37 @@ /* * Copyright © 2023 Musing Studio LLC. * * This file is part of WriteFreely. * * WriteFreely is free software: you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License, included * in the LICENSE file in this source code package. */ package migrations func supportPassReset(db *datastore) error { t, err := db.Begin() if err != nil { t.Rollback() return err } _, err = t.Exec(`CREATE TABLE password_resets ( user_id ` + db.typeInt() + ` not null, token ` + db.typeChar(32) + ` not null primary key, - used ` + db.typeBool() + ` default 0 not null, + used ` + db.typeBool() + ` default ` + db.boolFalse() + ` not null, created ` + db.typeDateTime() + ` not null )`) if err != nil { t.Rollback() return err } err = t.Commit() if err != nil { t.Rollback() return err } return nil } diff --git a/migrations/v15.go b/migrations/v15.go index 0875c5a..2a88481 100644 --- a/migrations/v15.go +++ b/migrations/v15.go @@ -1,33 +1,42 @@ /* * Copyright © 2023 Musing Studio LLC. * * This file is part of WriteFreely. * * WriteFreely is free software: you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License, included * in the LICENSE file in this source code package. */ package migrations func addPostRetrievalIndex(db *datastore) error { t, err := db.Begin() if err != nil { t.Rollback() return err } - _, err = t.Exec("CREATE INDEX posts_get_collection_index ON posts (`collection_id`, `pinned_position`, `created`)") + switch db.driverName { + case driverSQLite: + _, err = t.Exec("CREATE INDEX posts_get_collection_index ON posts (`collection_id`, `pinned_position`, `created`)") + case driverMySQL: + _, err = t.Exec("CREATE INDEX posts_get_collection_index ON posts (`collection_id`, `pinned_position`, `created`)") + case driverPostgres: + _, err = t.Exec("CREATE INDEX posts_get_collection_idx ON posts (collection_id, pinned_position, created)") + } + + if err != nil { t.Rollback() return err } err = t.Commit() if err != nil { t.Rollback() return err } return nil } diff --git a/migrations/v4.go b/migrations/v4.go index 4ae267d..7b09e9d 100644 --- a/migrations/v4.go +++ b/migrations/v4.go @@ -1,54 +1,61 @@ /* * Copyright © 2019-2021 Musing Studio LLC. * * This file is part of WriteFreely. * * WriteFreely is free software: you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License, included * in the LICENSE file in this source code package. */ package migrations import ( "context" "database/sql" wf_db "github.com/writefreely/writefreely/db" ) func oauth(db *datastore) error { - dialect := wf_db.DialectMySQL - if db.driverName == driverSQLite { + var dialect wf_db.DialectType + + switch db.driverName { + case driverSQLite: dialect = wf_db.DialectSQLite + case driverMySQL: + dialect = wf_db.DialectMySQL + case driverPostgres: + dialect = wf_db.DialectPostgres } + return wf_db.RunTransactionWithOptions(context.Background(), db.DB, &sql.TxOptions{}, func(ctx context.Context, tx *sql.Tx) error { createTableUsersOauth, err := dialect. Table("oauth_users"). SetIfNotExists(false). Column(dialect.Column("user_id", wf_db.ColumnTypeInteger, wf_db.UnsetSize)). Column(dialect.Column("remote_user_id", wf_db.ColumnTypeInteger, wf_db.UnsetSize)). ToSQL() if err != nil { return err } createTableOauthClientState, err := dialect. Table("oauth_client_states"). SetIfNotExists(false). Column(dialect.Column("state", wf_db.ColumnTypeVarChar, wf_db.OptionalInt{Set: true, Value: 255})). Column(dialect.Column("used", wf_db.ColumnTypeBool, wf_db.UnsetSize)). Column(dialect.Column("created_at", wf_db.ColumnTypeDateTime, wf_db.UnsetSize).SetDefaultCurrentTimestamp()). UniqueConstraint("state"). ToSQL() if err != nil { return err } for _, table := range []string{createTableUsersOauth, createTableOauthClientState} { if _, err := tx.ExecContext(ctx, table); err != nil { return err } } return nil }) } diff --git a/migrations/v5.go b/migrations/v5.go index db18fa1..b10afbf 100644 --- a/migrations/v5.go +++ b/migrations/v5.go @@ -1,88 +1,95 @@ /* * Copyright © 2019-2021 Musing Studio LLC. * * This file is part of WriteFreely. * * WriteFreely is free software: you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License, included * in the LICENSE file in this source code package. */ package migrations import ( "context" "database/sql" wf_db "github.com/writefreely/writefreely/db" ) func oauthSlack(db *datastore) error { - dialect := wf_db.DialectMySQL - if db.driverName == driverSQLite { + var dialect wf_db.DialectType + + switch db.driverName { + case driverSQLite: dialect = wf_db.DialectSQLite + case driverMySQL: + dialect = wf_db.DialectMySQL + case driverPostgres: + dialect = wf_db.DialectPostgres } + return wf_db.RunTransactionWithOptions(context.Background(), db.DB, &sql.TxOptions{}, func(ctx context.Context, tx *sql.Tx) error { builders := []wf_db.SQLBuilder{ dialect. AlterTable("oauth_client_states"). AddColumn(dialect. Column( "provider", wf_db.ColumnTypeVarChar, wf_db.OptionalInt{Set: true, Value: 24}).SetDefault("")), dialect. AlterTable("oauth_client_states"). AddColumn(dialect. Column( "client_id", wf_db.ColumnTypeVarChar, wf_db.OptionalInt{Set: true, Value: 128}).SetDefault("")), dialect. AlterTable("oauth_users"). AddColumn(dialect. Column( "provider", wf_db.ColumnTypeVarChar, wf_db.OptionalInt{Set: true, Value: 24}).SetDefault("")), dialect. AlterTable("oauth_users"). AddColumn(dialect. Column( "client_id", wf_db.ColumnTypeVarChar, wf_db.OptionalInt{Set: true, Value: 128}).SetDefault("")), dialect. AlterTable("oauth_users"). AddColumn(dialect. Column( "access_token", wf_db.ColumnTypeVarChar, wf_db.OptionalInt{Set: true, Value: 512}).SetDefault("")), dialect.CreateUniqueIndex("oauth_users_uk", "oauth_users", "user_id", "provider", "client_id"), } if dialect != wf_db.DialectSQLite { // This updates the length of the `remote_user_id` column. It isn't needed for SQLite databases. builders = append(builders, dialect. AlterTable("oauth_users"). ChangeColumn("remote_user_id", dialect. Column( "remote_user_id", wf_db.ColumnTypeVarChar, wf_db.OptionalInt{Set: true, Value: 128}))) } for _, builder := range builders { query, err := builder.ToSQL() if err != nil { return err } if _, err := tx.ExecContext(ctx, query); err != nil { return err } } return nil }) } diff --git a/migrations/v7.go b/migrations/v7.go index 2056aa0..339a8d9 100644 --- a/migrations/v7.go +++ b/migrations/v7.go @@ -1,46 +1,53 @@ /* * Copyright © 2020-2021 Musing Studio LLC. * * This file is part of WriteFreely. * * WriteFreely is free software: you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License, included * in the LICENSE file in this source code package. */ package migrations import ( "context" "database/sql" wf_db "github.com/writefreely/writefreely/db" ) func oauthAttach(db *datastore) error { - dialect := wf_db.DialectMySQL - if db.driverName == driverSQLite { + var dialect wf_db.DialectType + + switch db.driverName { + case driverSQLite: dialect = wf_db.DialectSQLite + case driverMySQL: + dialect = wf_db.DialectMySQL + case driverPostgres: + dialect = wf_db.DialectPostgres } + return wf_db.RunTransactionWithOptions(context.Background(), db.DB, &sql.TxOptions{}, func(ctx context.Context, tx *sql.Tx) error { builders := []wf_db.SQLBuilder{ dialect. AlterTable("oauth_client_states"). AddColumn(dialect. Column( "attach_user_id", wf_db.ColumnTypeInteger, wf_db.OptionalInt{Set: true, Value: 24}).SetNullable(true)), } for _, builder := range builders { query, err := builder.ToSQL() if err != nil { return err } if _, err := tx.ExecContext(ctx, query); err != nil { return err } } return nil }) } diff --git a/migrations/v8.go b/migrations/v8.go index 36001af..5cb164e 100644 --- a/migrations/v8.go +++ b/migrations/v8.go @@ -1,45 +1,52 @@ /* * Copyright © 2020-2021 Musing Studio LLC. * * This file is part of WriteFreely. * * WriteFreely is free software: you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License, included * in the LICENSE file in this source code package. */ package migrations import ( "context" "database/sql" wf_db "github.com/writefreely/writefreely/db" ) func oauthInvites(db *datastore) error { - dialect := wf_db.DialectMySQL - if db.driverName == driverSQLite { + var dialect wf_db.DialectType + + switch db.driverName { + case driverSQLite: dialect = wf_db.DialectSQLite + case driverMySQL: + dialect = wf_db.DialectMySQL + case driverPostgres: + dialect = wf_db.DialectPostgres } + return wf_db.RunTransactionWithOptions(context.Background(), db.DB, &sql.TxOptions{}, func(ctx context.Context, tx *sql.Tx) error { builders := []wf_db.SQLBuilder{ dialect. AlterTable("oauth_client_states"). AddColumn(dialect.Column("invite_code", wf_db.ColumnTypeChar, wf_db.OptionalInt{ Set: true, Value: 6, }).SetNullable(true)), } for _, builder := range builders { query, err := builder.ToSQL() if err != nil { return err } if _, err := tx.ExecContext(ctx, query); err != nil { return err } } return nil }) } diff --git a/migrations/v9.go b/migrations/v9.go index f542189..a006956 100644 --- a/migrations/v9.go +++ b/migrations/v9.go @@ -1,37 +1,41 @@ /* * Copyright © 2020 Musing Studio LLC. * * This file is part of WriteFreely. * * WriteFreely is free software: you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License, included * in the LICENSE file in this source code package. */ package migrations func optimizeDrafts(db *datastore) error { t, err := db.Begin() if err != nil { t.Rollback() return err } - if db.driverName == driverSQLite { + switch db.driverName { + case driverSQLite: _, err = t.Exec(`CREATE INDEX key_owner_post_id ON posts (owner_id, id)`) - } else { + case driverMySQL: _, err = t.Exec(`ALTER TABLE posts ADD INDEX(owner_id, id)`) + case driverPostgres: + _, err = t.Exec(`CREATE INDEX key_owner_post_idx ON posts (owner_id, id)`) } + if err != nil { t.Rollback() return err } err = t.Commit() if err != nil { t.Rollback() return err } return nil }