Created
January 20, 2023 12:24
-
-
Save mche/2abfc1a50d5427a99f0cce51a609e6cf to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
package model | |
/********* | |
корень | |
+----migrations | |
| +----- 1.2.0 | |
| | +------ #8563.up.sql | |
| | +------ #8563.down.sql | |
| | +------ #.....up.sql | |
| | +------ #.....down.sql | |
| | +------ #..... | |
| | | |
| +----- 2.5.1 | |
| +------ .....up.sql | |
| +------ .....down.sql | |
| +------ ..... | |
Cопоставляются версии: из settings.Version, таблицы migration и папок-версий файлов миграций. | |
Имена файлов миграций с суффиксами up.sql и down.sql, префиксная часть имени файла влияет на порядок исполнения миграций. | |
1. Сначала поднять обратный список файлов {version}/xxx.down.sql, где version > settings.Version and version <= table.migration.version (если table.migration.version нет записей, тогда 0 и будет пустой список). | |
2. Далее поднять прямой список файлов {version}/xxxx.up.sql, где version > table.migration.version and version <= settings.Version (если table.migration.version нет записей, тогда 0) | |
3. Список (1) в обратном порядке и список (2) в прямом порядке версий, обернуть в одну транзакцию, запускать в цикле. | |
Только потом(?) накатывать gorm.AutoMigrate() в других моделях (а можно и после). | |
Преобразование версий: папка - версия 3 цифры, в папке файлы миграций задач | |
1.1.0/2_up.sql = 1*10^6 + 1*10^3 + 0*10^0 = 1_001_000 | |
1.25.6/0.up.sql = 1*10^6 + 25*10^3 + 6*10^0 = 1_025_006 | |
2.999.077/foo_up.sql = 2*10^6 + 999*10^3 + 77*10^0 = 2_999_077 | |
Включить папку migrations в поставку. | |
**********/ | |
import ( | |
"fmt" | |
"log" | |
"os" | |
"path/filepath" | |
"strings" | |
"time" | |
pq "github.com/lib/pq" | |
"gorm.io/gorm" | |
) | |
const ( | |
migrationsPathUp = "./migrations/*/*up.sql" | |
migrationsPathDown = "./migrations/*/*down.sql" | |
version2NumericFunc = ` | |
CREATE OR REPLACE FUNCTION version2Numeric(int4[]) RETURNS numeric | |
AS $$ | |
-- преобразование массива 3 чисел версии в число для сравнений-сортировок | |
SELECT ($1[1]*10^6 + $1[2]*10^3 + coalesce($1[3], 0)*10^0 /***+ coalesce($1[4], 0)*10^(-3)***/)::numeric; | |
$$ LANGUAGE SQL; | |
` | |
migrationFileVersionFuncDrop = `DROP FUNCTION IF EXISTS migration_file_version(text);` | |
migrationFileVersionFunc = ` | |
CREATE OR REPLACE FUNCTION migration_file_version(text) RETURNS TABLE("path" text[], "version" int4[]) | |
AS $$ | |
/*** из подпапки-версии к файлам (подготовлен как строки-массив листинга папки миграции) миграций получить: | |
- путь-массив, | |
- выделить версию-массив из 3 чисел | |
***/ | |
SELECT "path", "version"[1:3]::int4[] ---(CASE WHEN "version"[4] is NULL OR "version"[4] = '' THEN 3 ELSE 4 END) | |
FROM ( | |
SELECT "path", | |
regexp_split_to_array("path"[array_length("path", 1)-1], '\D+') as "version" -- цифры версии | |
FROM ( | |
SELECT string_to_array(unnest(replace(coalesce($1, '{}'), '\\', '/')::text[]), '/') as "path" | |
) a | |
) a | |
$$ LANGUAGE SQL; | |
` | |
migrationsQuery = ` --- Общий главный запрос всех миграций вниз и вверх | |
WITH "settings" AS ( | |
SELECT version2Numeric("version") as "version_number" | |
FROM ( | |
SELECT (regexp_split_to_array(?::text, '\D+'))[1:3]::int4[] as "version" | |
) a | |
), | |
"migrationsUp" AS ( | |
SELECT *, version2Numeric("version") as "version_number", true as "up" | |
FROM migration_file_version(?) | |
), | |
"migrationsDown" AS ( | |
SELECT *, version2Numeric("version") as "version_number", false as "up" | |
FROM migration_file_version(?) | |
), | |
"currentMigrationVersion" AS ( | |
-- одна строка максимальной версии миграции | |
SELECT * | |
FROM ( | |
SELECT *, version2Numeric("version") as "version_number" | |
FROM "migrations" | |
) m | |
WHERE "version_number" = ( | |
SELECT max(version2Numeric("version")) | |
FROM "migrations" | |
) | |
) | |
-- конец WITH | |
SELECT * FROM ( | |
--- первый подзапрос скатить вниз | |
SELECT * FROM ( | |
SELECT distinct(down.*) -- повторы | |
FROM "migrationsDown" down | |
LEFT JOIN "currentMigrationVersion" cur on true, | |
"settings" s | |
WHERE | |
down."version_number" > s."version_number" | |
AND down."version_number" <= coalesce(cur."version_number", 0::numeric) | |
ORDER BY down."version_number" desc, down."path" desc --- вниз в обратном порядке | |
) down | |
UNION ALL -- ALL сохранит сортировку? | |
--- второй подзапрос накатить вверх | |
SELECT * FROM ( | |
SELECT distinct(up.*) -- тут дубликаты | |
FROM "migrationsUp" up | |
LEFT JOIN "migrations" mg on up."version"=mg."version" | |
LEFT JOIN "currentMigrationVersion" cur on true, | |
"settings" s | |
WHERE mg IS NULL | |
AND up."version_number" > coalesce(cur."version_number", 0::numeric) | |
AND up."version_number" <= s."version_number" | |
ORDER BY up."version_number" asc, up."path" asc | |
) up | |
) a | |
` | |
) | |
type Migration struct { | |
ID uint `gorm:"type:int8;primaryKey"` | |
TS time.Time `gorm:"type:timestamp without time zone;not null;default:now()"` | |
Version pq.Int32Array `gorm:"type:int4[];not null;"` //unique; | |
Path pq.StringArray `gorm:"type:text[];not null;"` | |
} | |
type execMigration struct { | |
Path pq.StringArray `gorm:"type:text[]"` | |
Version pq.Int32Array `gorm:"type:int4[]"` | |
VersionNumber float32 | |
Up bool | |
} | |
func (db *dbStruct) migrate(version string) { | |
db.gorm.AutoMigrate(&Migration{Path:pq.StringArray{""}}) | |
if tx := db.gorm.Exec(version2NumericFunc); tx.Error != nil { | |
log.Fatalf("Cant exec SQL [version2NumericFunc]: %v", tx.Error) | |
} | |
if tx := db.gorm.Exec(migrationFileVersionFuncDrop); tx.Error != nil { | |
log.Fatalf("Cant exec SQL [drop migrationFileVersionFunc]: %v", tx.Error) | |
} | |
if tx := db.gorm.Exec(migrationFileVersionFunc); tx.Error != nil { | |
log.Fatalf("Cant exec SQL [create migrationFileVersionFunc]: %v", tx.Error) | |
} | |
filesUp, err := filepath.Glob(migrationsPathUp) | |
if err != nil { | |
log.Fatalf("Cant read dir [%v]: %v", migrationsPathUp, err) | |
} | |
filesDown, err := filepath.Glob(migrationsPathDown) | |
if err != nil { | |
log.Fatalf("Cant read dir [%v]: %v", migrationsPathDown, err) | |
} | |
if len(filesUp) == 0 && len(filesDown) == 0 { | |
log.Printf("Нет миграций или не найден [%v] и [%v]", migrationsPathUp, migrationsPathDown) | |
return | |
} | |
var execMigrations []execMigration | |
tx := db.gorm.Raw(migrationsQuery, version, pq.Array(filesUp), pq.Array(filesDown)).Scan(&execMigrations) | |
if tx.Error != nil { | |
log.Fatalf("Fail query for migrations files %v", tx.Error) | |
} | |
if len(execMigrations) == 0 { | |
log.Printf("Нет миграций для версии [%v]", version/*, migrationsPathDown, len(filesDown), migrationsPathUp, len(filesUp)*/) | |
return | |
} | |
err = db.gorm.Transaction(func(tx *gorm.DB) error { // транзакция для горм действий | |
db_, _ := tx.DB() | |
if tx_, err := db_.Begin(); err == nil { // отдельная транзакция для (sql.tx).Exec, а горм не может .Exec повтрять в цикле - у него prepared | |
for i, mg := range execMigrations { | |
log.Printf("apply Migration[%v] %+v ...", i, mg) | |
content, err := os.ReadFile(strings.Join(mg.Path, "/")) | |
if err != nil { | |
return fmt.Errorf("fail read file [%+v]: %v", mg.Path, err) | |
} | |
if _, err := tx_.Exec(string(content)); err != nil { | |
return fmt.Errorf("fail exec migration %+v: %v", mg.Path, err) | |
} | |
if mg.Up { | |
if res := tx.Create(&Migration{Version: mg.Version, Path: mg.Path}); res.Error != nil || res.RowsAffected == 0 { | |
return fmt.Errorf("fail save migration [%+v]: %v", mg, tx.Error) | |
} | |
} else { | |
// удалить все строки Миграций с этой версией | |
//log.Printf("Delete migration down %+v", mg) | |
if res := tx.Delete(&Migration{}, "version[1:3]=(?::int4[])[1:3]", pq.Array(mg.Version)); res.Error != nil { | |
return fmt.Errorf("fail delete migration [%+v]: %v", mg, tx.Error) | |
} | |
} | |
} | |
tx_.Commit() | |
} else { | |
log.Printf(`fail sql (not gorm) transaction %v`, err) | |
} | |
return nil | |
}) | |
if err != nil { | |
log.Fatal(err) | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment