Repository: ariga/atlas
Pull request #1103 (2022-08-28 19:20:52)

sql/migrate: add docs and examples for statements delimiter


Patch:
diff --git a/doc/md/versioned/new.md b/doc/md/versioned/new.md
deleted file mode 100644
index 6ae65269..00000000
--- a/doc/md/versioned/new.md
+++ /dev/null
@@ -1,48 +0,0 @@
----
-id: new
-slug: /versioned/new
-title: Manual Migrations
----
-In some cases it is desirable to add a migration file manually. This could be done
-to provision resources that Atlas does not yet capture in its DDL (such as triggers and
-views) or to seed data with `INSERT` statements. 
-
-To manually add a new migration file to the directory use the `migrate new` command. 
-
-### Flags
-When using `migrate new` to create a new migration file users may supply the following flags:
-* `--dir` the URL of the migration directory, by default it is `file://migrations`, e.g a
-  directory named `migrations` in the current working directory.
-
-### Migration name
-Users may optionally add a final positional argument to set the name of the migration
-file.  This name will be appended to the migration version number in the filename as such:
-`<version>_<name>.sql`.
-
-### Recalculating the directory hash
-Atlas maintains a file named `atlas.sum` in the migration directory. This file  is used to 
-ensure the integrity of the migration directory and force developers to deal with 
-situations where migration order or contents was modified after the fact. 
-
-After manually editing the contents of a newly created migration file, the checksums for
-the directory must be recalculated. This can be done by running `atlas migrate hash --force`
-command.
-
-### Examples
-
-Creating a new migration file:
-```text
-atlas migrate new
-```
-
-Creating a new migration file named "add_user":
-```text
-atlas migrate new add_user
-```
-
-Creating a new migration file in a specific directory:
-```text
-atlas migrate new --dir file://custom/path/to/dir
-```
-
-
diff --git a/doc/md/versioned/new.mdx b/doc/md/versioned/new.mdx
new file mode 100644
index 00000000..44625623
--- /dev/null
+++ b/doc/md/versioned/new.mdx
@@ -0,0 +1,148 @@
+---
+id: new
+slug: /versioned/new
+title: Manual Migrations
+---
+
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In some cases it is desirable to add a migration file manually. This could be done
+to provision resources that Atlas does not yet capture in its DDL (such as triggers and
+views) or to seed data with `INSERT` statements. 
+
+To manually add a new migration file to the directory use the `migrate new` command. 
+
+### Flags
+When using `migrate new` to create a new migration file users may supply the following flags:
+* `--dir` the URL of the migration directory, by default it is `file://migrations`, e.g a
+  directory named `migrations` in the current working directory.
+
+### Migration name
+Users may optionally add a final positional argument to set the name of the migration
+file.  This name will be appended to the migration version number in the filename as such:
+`<version>_<name>.sql`.
+
+### Custom statements delimiter
+
+The semicolon character (`;`) is recognized by Atlas as a statement delimiter. In some cases, however, the delimiter
+may need to be redefined because the semicolon itself is used in one of the DDL statements. For example, a stored
+program containing semicolon characters.
+
+#### Using `\n\n\n` as a delimiter:
+
+<Tabs
+defaultValue="migration_file"
+values={[
+{label: 'Migration file', value: 'migration_file'},
+{label: 'Scanned statements', value: 'statements'},
+]}>
+<TabItem value="migration_file">
+
+```sql
+//highlight-next-line-info
+-- atlas:delimiter \n\n\n
+
+CREATE PROCEDURE dorepeat(p1 INT)
+BEGIN
+    SET @x = 0;
+    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+END;
+
+
+CALL dorepeat(1000);
+```
+
+</TabItem>
+<TabItem value="statements">
+
+Statement 1:
+
+```sql
+CREATE PROCEDURE dorepeat(p1 INT)
+BEGIN
+    SET @x = 0;
+    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+END;
+```
+
+Statement 2:
+```sql
+CALL dorepeat(1000);
+```
+
+</TabItem>
+</Tabs>
+
+
+#### Using `-- end` as a delimiter:
+
+<Tabs
+defaultValue="migration_file"
+values={[
+{label: 'Migration file', value: 'migration_file'},
+{label: 'Scanned statements', value: 'statements'},
+]}>
+<TabItem value="migration_file">
+
+```sql
+//highlight-next-line-info
+-- atlas:delimiter -- end
+
+CREATE PROCEDURE dorepeat(p1 INT)
+BEGIN
+    SET @x = 0;
+    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+END;
+-- end
+CALL dorepeat(1000);
+```
+
+</TabItem>
+<TabItem value="statements">
+
+Statement 1:
+
+```sql
+CREATE PROCEDURE dorepeat(p1 INT)
+BEGIN
+    SET @x = 0;
+    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+END;
+```
+
+Statement 2:
+```sql
+CALL dorepeat(1000);
+```
+
+</TabItem>
+</Tabs>
+
+### Recalculating the directory hash
+Atlas maintains a file named `atlas.sum` in the migration directory. This file  is used to 
+ensure the integrity of the migration directory and force developers to deal with 
+situations where migration order or contents was modified after the fact. 
+
+After manually editing the contents of a newly created migration file, the checksums for
+the directory must be recalculated. This can be done by running `atlas migrate hash --force`
+command.
+
+### Examples
+
+Creating a new migration file:
+```text
+atlas migrate new
+```
+
+Creating a new migration file named "add_user":
+```text
+atlas migrate new add_user
+```
+
+Creating a new migration file in a specific directory:
+```text
+atlas migrate new --dir file://custom/path/to/dir
+```
+
+
diff --git a/sql/migrate/lex.go b/sql/migrate/lex.go
index 23fc2ce1..4e946df3 100644
--- a/sql/migrate/lex.go
+++ b/sql/migrate/lex.go
@@ -72,6 +72,7 @@ func (l *lex) stmt() (stmt string, err error) {
 		if l.delim != delimiter {
 			stmt = strings.TrimSuffix(stmt, l.delim)
 		}
+		stmt = strings.TrimSpace(stmt)
 	}()
 	// Trim trailing whitespace.
 	l.skipSpaces()
@@ -96,15 +97,16 @@ func (l *lex) stmt() (stmt string, err error) {
 			if err := l.skipQuote(r); err != nil {
 				return "", err
 			}
+		// Delimiters take precedence over comments.
+		case strings.HasPrefix(l.input[l.pos-l.width:], l.delim) && l.depth == 0:
+			l.pos += len(l.delim) - l.width
+			return l.input[:l.pos], nil
 		case r == '#':
 			l.skipComment("#", "\n")
 		case r == '-' && l.next() == '-':
 			l.skipComment("--", "\n")
 		case r == '/' && l.next() == '*':
 			l.skipComment("/*", "*/")
-		case strings.HasPrefix(l.input[l.pos-l.width:], l.delim) && l.depth == 0:
-			l.pos += len(l.delim) - l.width
-			return l.input[:l.pos], nil
 		}
 	}
 }


Test patch:
diff --git a/sql/migrate/lex_test.go b/sql/migrate/lex_test.go
index 2f290f07..ac93d968 100644
--- a/sql/migrate/lex_test.go
+++ b/sql/migrate/lex_test.go
@@ -24,6 +24,6 @@ func TestLocalFile_Stmts(t *testing.T) {
 		require.NoError(t, err)
 		buf, err := os.ReadFile(filepath.Join(path, f.Name()+".golden"))
 		require.NoError(t, err)
-		require.Equal(t, string(buf), strings.Join(stmts, "\n\n"))
+		require.Equal(t, string(buf), strings.Join(stmts, "\n-- end --\n"))
 	}
 }
diff --git a/sql/migrate/testdata/lex/1.sql.golden b/sql/migrate/testdata/lex/1.sql.golden
index 0375e1de..663bcc1c 100644
--- a/sql/migrate/testdata/lex/1.sql.golden
+++ b/sql/migrate/testdata/lex/1.sql.golden
@@ -1,19 +1,19 @@
 CREATE TABLE t1(id int);
-
+-- end --
 CREATE TABLE t2(id int);
-
+-- end --
 CREATE TABLE t3(id int);
-
+-- end --
 CREATE TABLE t4(
     id int,
     name varchar(255)
 );
-
+-- end --
 CREATE TABLE t4(
     id int,
     `name` varchar(255) DEFAULT ';'
 ) ENGINE=InnoDB;
-
+-- end --
 CREATE TABLE t5(
     id int
     /* comment */
diff --git a/sql/migrate/testdata/lex/10_delimiter_comment.sql b/sql/migrate/testdata/lex/10_delimiter_comment.sql
new file mode 100644
index 00000000..d097d6eb
--- /dev/null
+++ b/sql/migrate/testdata/lex/10_delimiter_comment.sql
@@ -0,0 +1,9 @@
+-- atlas:delimiter -- end
+
+CREATE PROCEDURE dorepeat(p1 INT)
+BEGIN
+    SET @x = 0;
+    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+END;
+-- end
+CALL dorepeat(1000);
diff --git a/sql/migrate/testdata/lex/10_delimiter_comment.sql.golden b/sql/migrate/testdata/lex/10_delimiter_comment.sql.golden
new file mode 100644
index 00000000..2a19a94a
--- /dev/null
+++ b/sql/migrate/testdata/lex/10_delimiter_comment.sql.golden
@@ -0,0 +1,7 @@
+CREATE PROCEDURE dorepeat(p1 INT)
+BEGIN
+    SET @x = 0;
+    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+END;
+-- end --
+CALL dorepeat(1000);
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/2_mysql.sql.golden b/sql/migrate/testdata/lex/2_mysql.sql.golden
index 5dc690e2..71c7c986 100644
--- a/sql/migrate/testdata/lex/2_mysql.sql.golden
+++ b/sql/migrate/testdata/lex/2_mysql.sql.golden
@@ -1,23 +1,23 @@
 create table t1 (b char(0));
-
+-- end --
 create table t1 (b char(0) not null);
-
+-- end --
 create table if not exists t1 (b char(0) not null);
-
+-- end --
 create table t2 engine=heap select * from t1;
-
+-- end --
 create table t1 (ordid int(8) not null auto_increment, ord  varchar(50) not null, primary key (ord,ordid)) engine=heap;
-
+-- end --
 create table mysqltest.$test1 (a$1 int, $b int, c$ int);
-
+-- end --
 create table t2 (b int) select a as b, a+1 as b from t1;
-
+-- end --
 create table t1 select if('2002'='2002','Y','N');
-
+-- end --
 create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2));
-
+-- end --
 insert into t1 values ("a", 1), ("b", 2);
-
+-- end --
 create table t2
 select
     a,
@@ -30,21 +30,21 @@ select
     ifnull(h,cast('yet another binary data' as binary))   as h,
     addtime(cast('1:0:0' as time),cast('1:0:0' as time))  as dd
 from t1;
-
+-- end --
 CREATE TABLE t1(id varchar(10) NOT NULL PRIMARY KEY, dsc longtext);
-
+-- end --
 INSERT INTO t1 VALUES ('5000000001', NULL),('5000000003', 'Test'),('5000000004', NULL);
-
+-- end --
 create table t1 (
     a varchar(112) charset utf8 collate utf8_bin not null,
     primary key (a)
 ) select 'test' as a ;
-
+-- end --
 create table טבלה_של_אריאל
 (
     כמות int
 );
-
+-- end --
 CREATE TABLE t1(
     c1 INT DEFAULT 12 COMMENT 'column1',
     c2 INT NULL COMMENT 'column2',
diff --git a/sql/migrate/testdata/lex/3_delimiter.sql.golden b/sql/migrate/testdata/lex/3_delimiter.sql.golden
index d3f85e97..5642c3ae 100644
--- a/sql/migrate/testdata/lex/3_delimiter.sql.golden
+++ b/sql/migrate/testdata/lex/3_delimiter.sql.golden
@@ -1,3 +1,3 @@
 CREATE INDEX i1 ON t1(c1)
-
+-- end --
 CREATE INDEX i2 ON t2(c2)
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/4_delimiter.sql.golden b/sql/migrate/testdata/lex/4_delimiter.sql.golden
index d3f85e97..5642c3ae 100644
--- a/sql/migrate/testdata/lex/4_delimiter.sql.golden
+++ b/sql/migrate/testdata/lex/4_delimiter.sql.golden
@@ -1,3 +1,3 @@
 CREATE INDEX i1 ON t1(c1)
-
+-- end --
 CREATE INDEX i2 ON t2(c2)
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/5_delimiter.sql b/sql/migrate/testdata/lex/5_delimiter.sql
index e4563c40..354321bf 100644
--- a/sql/migrate/testdata/lex/5_delimiter.sql
+++ b/sql/migrate/testdata/lex/5_delimiter.sql
@@ -1,7 +1,6 @@
--- atlas:delimiter ATLAS_DELIMITER
+-- atlas:delimiter \n-- end --\n
 
 DELIMITER $$
-
 CREATE DEFINER='boring' PROCEDURE proc ()
     COMMENT 'ATLAS_DELIMITER'
     SQL SECURITY INVOKER
@@ -14,7 +13,8 @@ BEGIN
 
     SELECT CONCAT('Enabled ', @rows := ROW_COUNT(), ' background thread', IF(@rows != 1, 's', '')) AS summary;
 END$$
-
 DELIMITER ;
 
+-- end --
+
 CALL proc();
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/5_delimiter.sql.golden b/sql/migrate/testdata/lex/5_delimiter.sql.golden
index 3edc85f5..549ebcdd 100644
--- a/sql/migrate/testdata/lex/5_delimiter.sql.golden
+++ b/sql/migrate/testdata/lex/5_delimiter.sql.golden
@@ -1,5 +1,4 @@
 DELIMITER $$
-
 CREATE DEFINER='boring' PROCEDURE proc ()
     COMMENT 'ATLAS_DELIMITER'
     SQL SECURITY INVOKER
@@ -12,7 +11,6 @@ BEGIN
 
     SELECT CONCAT('Enabled ', @rows := ROW_COUNT(), ' background thread', IF(@rows != 1, 's', '')) AS summary;
 END$$
-
 DELIMITER ;
-
+-- end --
 CALL proc();
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/6_skip_comment.sql.golden b/sql/migrate/testdata/lex/6_skip_comment.sql.golden
index 4bb3b004..013d94c6 100644
--- a/sql/migrate/testdata/lex/6_skip_comment.sql.golden
+++ b/sql/migrate/testdata/lex/6_skip_comment.sql.golden
@@ -1,15 +1,15 @@
 CREATE TABLE t1(id int);
-
+-- end --
 CREATE TABLE t2(id int);
-
+-- end --
 CREATE TABLE t3(id int);
-
+-- end --
 CREATE TABLE t4(
     id int
     /* comment */
     -- comment
 ) ENGINE=InnoDB;
-
+-- end --
 CREATE TABLE t5(
     id int
     /* comment */
diff --git a/sql/migrate/testdata/lex/7_delimiter_2n.sql b/sql/migrate/testdata/lex/7_delimiter_2n.sql
new file mode 100644
index 00000000..5cb0d166
--- /dev/null
+++ b/sql/migrate/testdata/lex/7_delimiter_2n.sql
@@ -0,0 +1,14 @@
+-- atlas:delimiter \n\n
+
+CREATE EXTENSION IF NOT EXISTS unaccent;
+
+DELIMITER $$
+CREATE OR REPLACE FUNCTION public.slugify(
+    v TEXT
+) RETURNS TEXT STRICT IMMUTABLE AS $$
+BEGIN
+    RETURN trim(BOTH '-' FROM regexp_replace(lower(unaccent(trim(v))), '[^a-z0-9\\-_]+', '-', 'gi'));
+END;
+$$
+LANGUAGE plpgsql;
+DELIMITER ;
diff --git a/sql/migrate/testdata/lex/7_delimiter_2n.sql.golden b/sql/migrate/testdata/lex/7_delimiter_2n.sql.golden
new file mode 100644
index 00000000..a1885a5d
--- /dev/null
+++ b/sql/migrate/testdata/lex/7_delimiter_2n.sql.golden
@@ -0,0 +1,12 @@
+CREATE EXTENSION IF NOT EXISTS unaccent;
+-- end --
+DELIMITER $$
+CREATE OR REPLACE FUNCTION public.slugify(
+    v TEXT
+) RETURNS TEXT STRICT IMMUTABLE AS $$
+BEGIN
+    RETURN trim(BOTH '-' FROM regexp_replace(lower(unaccent(trim(v))), '[^a-z0-9\\-_]+', '-', 'gi'));
+END;
+$$
+LANGUAGE plpgsql;
+DELIMITER ;
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/8_delimiter_3n.sql b/sql/migrate/testdata/lex/8_delimiter_3n.sql
new file mode 100644
index 00000000..5d35bbd1
--- /dev/null
+++ b/sql/migrate/testdata/lex/8_delimiter_3n.sql
@@ -0,0 +1,15 @@
+-- atlas:delimiter \n\n\n
+
+CREATE EXTENSION IF NOT EXISTS unaccent;
+
+
+DELIMITER $$
+CREATE OR REPLACE FUNCTION public.slugify(
+    v TEXT
+) RETURNS TEXT STRICT IMMUTABLE AS $$
+BEGIN
+    RETURN trim(BOTH '-' FROM regexp_replace(lower(unaccent(trim(v))), '[^a-z0-9\\-_]+', '-', 'gi'));
+END;
+$$
+LANGUAGE plpgsql;
+DELIMITER ;
diff --git a/sql/migrate/testdata/lex/8_delimiter_3n.sql.golden b/sql/migrate/testdata/lex/8_delimiter_3n.sql.golden
new file mode 100644
index 00000000..a1885a5d
--- /dev/null
+++ b/sql/migrate/testdata/lex/8_delimiter_3n.sql.golden
@@ -0,0 +1,12 @@
+CREATE EXTENSION IF NOT EXISTS unaccent;
+-- end --
+DELIMITER $$
+CREATE OR REPLACE FUNCTION public.slugify(
+    v TEXT
+) RETURNS TEXT STRICT IMMUTABLE AS $$
+BEGIN
+    RETURN trim(BOTH '-' FROM regexp_replace(lower(unaccent(trim(v))), '[^a-z0-9\\-_]+', '-', 'gi'));
+END;
+$$
+LANGUAGE plpgsql;
+DELIMITER ;
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/9_delimiter_3n.sql b/sql/migrate/testdata/lex/9_delimiter_3n.sql
new file mode 100644
index 00000000..e07af836
--- /dev/null
+++ b/sql/migrate/testdata/lex/9_delimiter_3n.sql
@@ -0,0 +1,10 @@
+-- atlas:delimiter \n\n\n
+
+CREATE PROCEDURE dorepeat(p1 INT)
+BEGIN
+    SET @x = 0;
+    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+END;
+
+
+CALL dorepeat(1000);
diff --git a/sql/migrate/testdata/lex/9_delimiter_3n.sql.golden b/sql/migrate/testdata/lex/9_delimiter_3n.sql.golden
new file mode 100644
index 00000000..2a19a94a
--- /dev/null
+++ b/sql/migrate/testdata/lex/9_delimiter_3n.sql.golden
@@ -0,0 +1,7 @@
+CREATE PROCEDURE dorepeat(p1 INT)
+BEGIN
+    SET @x = 0;
+    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+END;
+-- end --
+CALL dorepeat(1000);
\ No newline at end of file