Repository: ariga/atlas
Pull request #1063 (2022-08-17 11:00:05)

sql/sqltool: support replay of existing flyway migration directories
This PR is the first step in understanding existing Flyway migration directories and therefore in "importing" them into the Atlas migration file format.

By default, Flyway skips hidden directories. For now, this PR only skips hidden directories on *nix machines. Windows does not use the `.` prefix for marking directories as hidden. Windows code remains untested.


Patch:
diff --git a/sql/migrate/migrate.go b/sql/migrate/migrate.go
index 0044c743..8b855706 100644
--- a/sql/migrate/migrate.go
+++ b/sql/migrate/migrate.go
@@ -495,9 +495,9 @@ func (e *Executor) Pending(ctx context.Context) ([]File, error) {
 		if err = e.drv.(CleanChecker).CheckClean(ctx, e.rrw.Ident()); err != nil && !errors.As(err, &cerr) {
 			return nil, err
 		}
-		// In case the workspace is not clean one of the flags are required.
+		// In case the workspace is not clean one of the flags is required.
 		if cerr != nil && !e.allowDirty && e.baselineVer == "" {
-			return nil, fmt.Errorf("%w. baseline version or allow-dirty are required", cerr)
+			return nil, fmt.Errorf("%w. baseline version or allow-dirty is required", cerr)
 		}
 		pending = migrations
 		if e.baselineVer != "" {
diff --git a/sql/sqltool/hidden.go b/sql/sqltool/hidden.go
new file mode 100644
index 00000000..184e2dee
--- /dev/null
+++ b/sql/sqltool/hidden.go
@@ -0,0 +1,13 @@
+// Copyright 2021-present The Atlas Authors. All rights reserved.
+// This source code is licensed under the Apache 2.0 license found
+// in the LICENSE file in the root directory of this source tree.
+
+//go:build !windows
+
+package sqltool
+
+import "path/filepath"
+
+func hidden(path string) (bool, error) {
+	return filepath.Base(path)[0] == '.', nil
+}
diff --git a/sql/sqltool/hidden_windows.go b/sql/sqltool/hidden_windows.go
new file mode 100644
index 00000000..f453612b
--- /dev/null
+++ b/sql/sqltool/hidden_windows.go
@@ -0,0 +1,26 @@
+// Copyright 2021-present The Atlas Authors. All rights reserved.
+// This source code is licensed under the Apache 2.0 license found
+// in the LICENSE file in the root directory of this source tree.
+
+package sqltool
+
+import (
+	"path/filepath"
+	"syscall"
+)
+
+func hidden(path string) (bool, error) {
+	abs, err := filepath.Abs(path)
+	if err != nil {
+		return false, err
+	}
+	p, err := syscall.UTF16PtrFromString(abs)
+	if err != nil {
+		return false, err
+	}
+	attr, err := syscall.GetFileAttributes(p)
+	if err != nil {
+		return false, err
+	}
+	return attr&syscall.FILE_ATTRIBUTE_HIDDEN != 0, nil
+}
diff --git a/sql/sqltool/tool.go b/sql/sqltool/tool.go
index 4a11658c..50233665 100644
--- a/sql/sqltool/tool.go
+++ b/sql/sqltool/tool.go
@@ -313,24 +313,41 @@ func NewFlywayDir(path string) (*FlywayDir, error) {
 	return &FlywayDir{dir}, nil
 }
 
-// Files implements Scanner.Files. It looks for all files with .sql suffix.
+// Files implements Scanner.Files. It looks for all files with .sql suffix. The given directory is recursively scanned
+// for non-hidden subdirectories. All found files will be ordered by migration type (Baseline, Versioned, Repeatable)
+// and filename.
 func (d *FlywayDir) Files() ([]migrate.File, error) {
-	var names []string
-	if err := fs.WalkDir(d, "", func(path string, d fs.DirEntry, err error) error {
+	var ff flywayFiles
+	if err := fs.WalkDir(d, "", func(path string, e fs.DirEntry, err error) error {
 		if err != nil {
 			return err
 		}
-		if path != "" && d.IsDir() {
-			return fs.SkipDir
+		if path != "" && e.IsDir() {
+			h, err := hidden(filepath.Join(d.Path(), path))
+			if err != nil {
+				return err
+			}
+			if h {
+				return fs.SkipDir
+			}
+			return nil
 		}
-		if filepath.Ext(d.Name()) == ".sql" && d.Name()[0] == 'V' {
-			names = append(names, path)
+		var (
+			pfx  = e.Name()[0]
+			base = filepath.Base(e.Name())
+			ext  = filepath.Ext(e.Name())
+		)
+		if ext != ".sql" || len(base) < 4 || (pfx != 'V' && pfx != 'B' && pfx != 'R') {
+			return nil
 		}
-		return nil
+		return ff.add(path)
 	}); err != nil {
 		return nil, err
 	}
-	ret := make([]migrate.File, len(names))
+	var (
+		names = ff.names()
+		ret   = make([]migrate.File, len(names))
+	)
 	for i, n := range names {
 		b, err := fs.ReadFile(d, n)
 		if err != nil {
@@ -343,16 +360,12 @@ func (d *FlywayDir) Files() ([]migrate.File, error) {
 
 // Desc implements File.Desc.
 func (f FlywayFile) Desc() string {
-	parts := strings.SplitN(f.Name(), "__", 2)
-	if len(parts) == 1 {
-		return ""
-	}
-	return strings.TrimSuffix(parts[1], ".sql")
+	return flywayDesc(f.Name())
 }
 
 // Version implements File.Version.
 func (f FlywayFile) Version() string {
-	return strings.TrimPrefix(strings.SplitN(strings.TrimSuffix(f.Name(), ".sql"), "__", 2)[0], "V")
+	return flywayVersion(f.Name())
 }
 
 // NewLiquibaseDir returns a new LiquibaseDir.
@@ -374,6 +387,77 @@ var (
 	reDBMatePragma = regexp.MustCompile(dbmatePragma + "up|down")
 )
 
+// flywayFiles retrieves flyway migration files by calls to add(). It will only keep the latest baseline and ignore
+// all versioned files that are included in that baseline.
+type flywayFiles struct {
+	baseline   string
+	versioned  []string
+	repeatable []string
+}
+
+// add the given path to the migration files according to its type. The input directory is assumed to be valid
+// according to the Flyway documentation (no duplicate versions, etc.).
+func (ff *flywayFiles) add(path string) error {
+	switch p := filepath.Base(path)[0]; p {
+	case 'B':
+		if ff.baseline != "" && flywayVersion(path) < flywayVersion(ff.baseline) {
+			return nil
+		}
+		ff.baseline = path
+		// In case we set a new baseline, remove all versioned files with a version smaller than the new baseline.
+		var (
+			bv = flywayVersion(ff.baseline)
+			vs []string
+		)
+		for _, v := range ff.versioned {
+			if v > bv {
+				vs = append(vs, v)
+			}
+		}
+		ff.versioned = vs
+		return nil
+	case 'V':
+		v := flywayVersion(path)
+		if ff.baseline == "" || flywayVersion(ff.baseline) < v {
+			ff.versioned = append(ff.versioned, path)
+		}
+		return nil
+	case 'R':
+		ff.repeatable = append(ff.repeatable, path)
+		return nil
+	default:
+		return fmt.Errorf("sql/sqltool: unexpected Flyway prefix %q", p)
+	}
+}
+
+func (ff *flywayFiles) names() []string {
+	var names []string
+	if ff.baseline != "" {
+		names = append(names, ff.baseline)
+	}
+	sort.Strings(ff.versioned)
+	sort.Strings(ff.repeatable)
+	names = append(names, ff.versioned...)
+	names = append(names, ff.repeatable...)
+	return names
+}
+
+func flywayDesc(path string) string {
+	parts := strings.SplitN(path, "__", 2)
+	if len(parts) == 1 {
+		return ""
+	}
+	return strings.TrimSuffix(parts[1], ".sql")
+}
+
+func flywayVersion(path string) string {
+	// Repeatable migrations don't have a version.
+	if filepath.Base(path)[0] == 'R' {
+		return ""
+	}
+	return strings.SplitN(strings.TrimSuffix(filepath.Base(path), ".sql"), "__", 2)[0][1:]
+}
+
 func unexpectedPragmaErr(f migrate.File, line int, pragma string) error {
 	var tool string
 	switch f := f.(type) {


Test patch:
diff --git a/sql/migrate/migrate_test.go b/sql/migrate/migrate_test.go
index fccc04e9..e084f334 100644
--- a/sql/migrate/migrate_test.go
+++ b/sql/migrate/migrate_test.go
@@ -349,7 +349,7 @@ func TestExecutor_Baseline(t *testing.T) {
 
 	// Require baseline-version or explicit flag to work on a dirty workspace.
 	files, err := ex.Pending(context.Background())
-	require.EqualError(t, err, "sql/migrate: connected database is not clean: found table. baseline version or allow-dirty are required")
+	require.EqualError(t, err, "sql/migrate: connected database is not clean: found table. baseline version or allow-dirty is required")
 	require.Nil(t, files)
 
 	rrw = mockRevisionReadWriter{}
diff --git a/sql/sqltool/testdata/flyway/B2__baseline.sql b/sql/sqltool/testdata/flyway/B2__baseline.sql
new file mode 100644
index 00000000..ea6e1080
--- /dev/null
+++ b/sql/sqltool/testdata/flyway/B2__baseline.sql
@@ -0,0 +1,14 @@
+CREATE TABLE post
+(
+    id    int NOT NULL,
+    title text,
+    body  text,
+    created_at TIMESTAMP NOT NULL
+    PRIMARY KEY (id)
+);
+
+INSERT INTO post (title, created_at) VALUES (
+'This is
+my multiline
+
+value', NOW());
\ No newline at end of file
diff --git a/sql/sqltool/testdata/flyway/R__views.sql b/sql/sqltool/testdata/flyway/R__views.sql
index e69de29b..7e166592 100644
--- a/sql/sqltool/testdata/flyway/R__views.sql
+++ b/sql/sqltool/testdata/flyway/R__views.sql
@@ -0,0 +1,1 @@
+CREATE VIEW `my_view` AS SELECT * FROM `post`;
\ No newline at end of file
diff --git a/sql/sqltool/testdata/flyway/V1__initial.sql b/sql/sqltool/testdata/flyway/V1__initial.sql
index 309932f6..7ec5bf7c 100644
--- a/sql/sqltool/testdata/flyway/V1__initial.sql
+++ b/sql/sqltool/testdata/flyway/V1__initial.sql
@@ -1,4 +1,4 @@
--- migrate:up
+-- comment
 CREATE TABLE post
 (
     id    int NOT NULL,
@@ -9,8 +9,8 @@ CREATE TABLE post
 
 ALTER TABLE post ADD created_at TIMESTAMP NOT NULL;
 
-INSERT INTO post (title) VALUES (
+INSERT INTO post (title, created_at) VALUES (
 'This is
 my multiline
 
-value');
+value', NOW());
diff --git a/sql/sqltool/testdata/flyway/V3__third_migration.sql b/sql/sqltool/testdata/flyway/V3__third_migration.sql
new file mode 100644
index 00000000..040fa194
--- /dev/null
+++ b/sql/sqltool/testdata/flyway/V3__third_migration.sql
@@ -0,0 +1,1 @@
+ALTER TABLE tbl_2 ADD col_1 INTEGER NOT NULL;
\ No newline at end of file
diff --git a/sql/sqltool/testdata/flyway/atlas.sum b/sql/sqltool/testdata/flyway/atlas.sum
index 5647210a..ff4a6285 100644
--- a/sql/sqltool/testdata/flyway/atlas.sum
+++ b/sql/sqltool/testdata/flyway/atlas.sum
@@ -1,5 +1,7 @@
-h1:daCHVoEL4e6azCWER8r5olBEX88NWn5I73YbuGVOJ24=
-R__views.sql h1:BNxp8EUGUbTP09MnMkdyGiMfIZSQdZ1OoNfj5k1lM/s=
-U1__initial.sql h1:i5S73/9af0A4xLl3V7nNhpmbgUGQQpkpAHUnvNaEEC8=
-V1__initial.sql h1:7AOLWFgFgfSztLzeapDhUcDnV/0HieHnWajnXFXGl4U=
-V2__second_migration.sql h1:wXjN9rvi8u9I1HvN6qkG6PrYBXO7GAoW4ALSOE2I9vY=
+h1:cqB3h2YgDucOvuJK1m+065STk4tE5r8TChc/fKR9Lsw=
+B2__baseline.sql h1:15n4zZKX1qkIf4v58ac7FvUZiZj+cR4TBjUMZI1E2K4=
+R__views.sql h1:WeCOf0LhETAdcFpd/Xejo8oiReGZ1uA3jPBGubWmiXk=
+U1__initial.sql h1:b64Db/YPV8vkNdPklYgbP7cjsrbhjObvKfCtMI4Mfd0=
+V1__initial.sql h1:TFqDsXIhPH1xnfv/nbSG9laQzWkidSTw6DnwFjfpei0=
+V2__second_migration.sql h1:6tXT74XoxjYfLbwgoSHkeWyDJIp93FfhjN3vRpPqEZI=
+V3__third_migration.sql h1:dCrdx+mvqPfuQWXLmPc7DP53n35XfdGvx2AbVZEf+Sc=
diff --git a/sql/sqltool/tool_test.go b/sql/sqltool/tool_test.go
index 0f9ea99d..4ca377ea 100644
--- a/sql/sqltool/tool_test.go
+++ b/sql/sqltool/tool_test.go
@@ -176,15 +176,15 @@ func TestScanners(t *testing.T) {
 				require.NoError(t, err)
 				return d
 			}(),
-			versions:     []string{"1", "2"},
-			descriptions: []string{"initial", "second_migration"},
+			versions:     []string{"2", "3", ""},
+			descriptions: []string{"baseline", "third_migration", "views"},
 			stmts: [][]string{
 				{
-					"CREATE TABLE post\n(\n    id    int NOT NULL,\n    title text,\n    body  text,\n    PRIMARY KEY (id)\n);",
-					"ALTER TABLE post ADD created_at TIMESTAMP NOT NULL;",
-					"INSERT INTO post (title) VALUES (\n'This is\nmy multiline\n\nvalue');",
+					"CREATE TABLE post\n(\n    id    int NOT NULL,\n    title text,\n    body  text,\n    created_at TIMESTAMP NOT NULL\n    PRIMARY KEY (id)\n);",
+					"INSERT INTO post (title, created_at) VALUES (\n'This is\nmy multiline\n\nvalue', NOW());",
 				},
-				{"CREATE TABLE tbl_2 (col INT);"},
+				{"ALTER TABLE tbl_2 ADD col_1 INTEGER NOT NULL;"},
+				{"CREATE VIEW `my_view` AS SELECT * FROM `post`;"},
 			},
 		},
 		{