Repository: ariga/atlas
Pull request #1140 (2022-09-11 13:08:44)

sql/migrate: support the mysql client DELIMITER command


Patch:
diff --git a/doc/md/versioned/new.mdx b/doc/md/versioned/new.mdx
index bc71b682..83a6f722 100644
--- a/doc/md/versioned/new.mdx
+++ b/doc/md/versioned/new.mdx
@@ -29,7 +29,51 @@ The semicolon character (`;`) is recognized by Atlas as a statement delimiter. I
 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:
+#### Using the `DELIMITER` command used by [MySQL client](https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html)
+
+<Tabs
+defaultValue="migration_file"
+values={[
+{label: 'Migration file', value: 'migration_file'},
+{label: 'Scanned statements', value: 'statements'},
+]}>
+<TabItem value="migration_file">
+
+```sql {1,7-8}
+DELIMITER //
+CREATE PROCEDURE dorepeat(p1 INT)
+    BEGIN
+    SET @x = 0;
+    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+END
+//
+DELIMITER ;
+CALL dorepeat(100)
+```
+
+</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(100);
+```
+
+</TabItem>
+</Tabs>
+
+
+#### Using the `atlas:delimiter` directive to set `\n\n\n` as a separator:
 
 <Tabs
 defaultValue="migration_file"
@@ -75,7 +119,7 @@ CALL dorepeat(1000);
 </Tabs>
 
 
-#### Using `-- end` as a delimiter:
+#### Using the `atlas:delimiter` directive to set `-- end` as a separator:
 
 <Tabs
 defaultValue="migration_file"
diff --git a/sql/migrate/lex.go b/sql/migrate/lex.go
index c5e7dc35..b9c8877e 100644
--- a/sql/migrate/lex.go
+++ b/sql/migrate/lex.go
@@ -63,66 +63,73 @@ func stmts(input string) ([]*Stmt, error) {
 
 type lex struct {
 	input    string
-	pos      int      // current and real position
+	pos      int      // current phase position
 	total    int      // total bytes scanned so far
 	width    int      // size of latest rune
-	depth    int      // depth of parentheses
 	delim    string   // configured delimiter
 	comments []string // collected comments
 }
 
 const (
-	eos       = -1
-	delimiter = ";"
+	eos          = -1
+	delimiter    = ";"
+	delimiterCmd = "delimiter"
 )
 
 func newLex(input string) (*lex, error) {
-	delim := delimiter
+	l := &lex{input: input, delim: delimiter}
 	if d, ok := directive(input, directiveDelimiter, directivePrefixSQL); ok {
-		if d == "" {
-			return nil, errors.New("empty delimiter")
+		if err := l.setDelim(d); err != nil {
+			return nil, err
 		}
 		parts := strings.SplitN(input, "\n", 2)
 		if len(parts) == 1 {
 			return nil, fmt.Errorf("not input found after delimiter %q", d)
 		}
-		// Unescape delimiters. e.g. "\\n" => "\n".
-		delim = strings.NewReplacer(`\n`, "\n", `\r`, "\r", `\t`, "\t").Replace(d)
-		input = parts[1]
+		l.input = parts[1]
 	}
-	l := &lex{input: input, delim: delim}
 	return l, nil
 }
 
 func (l *lex) stmt() (*Stmt, error) {
-	var text string
-	// Trim trailing whitespace.
+	var (
+		depth int
+		text  string
+	)
 	l.skipSpaces()
 Scan:
 	for {
 		switch r := l.next(); {
 		case r == eos:
-			if l.depth > 0 {
+			switch {
+			case depth > 0:
 				return nil, errors.New("unclosed parentheses")
-			}
-			if l.pos > 0 {
+			case l.pos > 0:
 				text = l.input
 				break Scan
+			default:
+				return nil, io.EOF
 			}
-			return nil, io.EOF
 		case r == '(':
-			l.depth++
+			depth++
 		case r == ')':
-			if l.depth == 0 {
+			if depth == 0 {
 				return nil, fmt.Errorf("unexpected ')' at position %d", l.pos)
 			}
-			l.depth--
+			depth--
 		case r == '\'', r == '"', r == '`':
 			if err := l.skipQuote(r); err != nil {
 				return nil, err
 			}
+		// Check if the start of the statement is the MySQL DELIMITER command.
+		// See https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html.
+		case l.pos == 1 && len(l.input) > len(delimiterCmd) && strings.EqualFold(l.input[:len(delimiterCmd)], delimiterCmd):
+			l.addPos(len(delimiterCmd) - 1)
+			if err := l.delimCmd(); err != nil {
+				return nil, err
+			}
 		// Delimiters take precedence over comments.
-		case strings.HasPrefix(l.input[l.pos-l.width:], l.delim) && l.depth == 0:
+		case depth == 0 && strings.HasPrefix(l.input[l.pos-l.width:], l.delim):
 			l.addPos(len(l.delim) - l.width)
 			text = l.input[:l.pos]
 			break Scan
@@ -147,6 +154,13 @@ func (l *lex) next() rune {
 	return r
 }
 
+func (l *lex) pick() rune {
+	p, w := l.pos, l.width
+	r := l.next()
+	l.pos, l.width = p, w
+	return r
+}
+
 func (l *lex) addPos(p int) {
 	l.pos += p
 	l.total += p
@@ -207,3 +221,35 @@ func (l *lex) emit(text string) *Stmt {
 	s.Text = strings.TrimSpace(s.Text)
 	return s
 }
+
+// delimCmd checks if the scanned "DELIMITER"
+// text represents an actual delimiter command.
+func (l *lex) delimCmd() error {
+	// A space must come after the delimiter.
+	if l.pick() != ' ' {
+		return nil
+	}
+	// Scan delimiter.
+	for r := l.pick(); r != eos && r != '\n'; r = l.next() {
+	}
+	delim := strings.TrimSpace(l.input[len(delimiterCmd):l.pos])
+	// MySQL client allows quoting delimiters.
+	if strings.HasPrefix(delim, "'") && strings.HasSuffix(delim, "'") {
+		delim = strings.ReplaceAll(delim[1:len(delim)-1], "''", "'")
+	}
+	if err := l.setDelim(delim); err != nil {
+		return err
+	}
+	// Skip all we saw until now.
+	l.emit(l.input[:l.pos])
+	return nil
+}
+
+func (l *lex) setDelim(d string) error {
+	if d == "" {
+		return errors.New("empty delimiter")
+	}
+	// Unescape delimiters. e.g. "\\n" => "\n".
+	l.delim = strings.NewReplacer(`\n`, "\n", `\r`, "\r", `\t`, "\t").Replace(d)
+	return nil
+}


Test patch:
diff --git a/sql/migrate/lex_test.go b/sql/migrate/lex_test.go
index 195d5f29..84b26cbe 100644
--- a/sql/migrate/lex_test.go
+++ b/sql/migrate/lex_test.go
@@ -24,7 +24,7 @@ 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-- end --\n"))
+		require.Equalf(t, string(buf), strings.Join(stmts, "\n-- end --\n"), "mismatched statements in file %q", f.Name())
 	}
 }
 
diff --git a/sql/migrate/testdata/lex/11_delimiter_mysql_command.sql b/sql/migrate/testdata/lex/11_delimiter_mysql_command.sql
new file mode 100644
index 00000000..516d5c51
--- /dev/null
+++ b/sql/migrate/testdata/lex/11_delimiter_mysql_command.sql
@@ -0,0 +1,15 @@
+-- An example for supporting MySQL client delimiters.
+
+DELIMITER $$
+
+CREATE OR REPLACE FUNCTION gen_uuid() RETURNS VARCHAR(22)
+BEGIN
+    RETURN concat(
+        date_format(NOW(6), '%Y%m%d%i%s%f'),
+        ROUND(1 + RAND() * (100 - 2))
+    );
+END;$$
+
+DELIMITER ;
+
+CALL gen_uuid();
diff --git a/sql/migrate/testdata/lex/11_delimiter_mysql_command.sql.golden b/sql/migrate/testdata/lex/11_delimiter_mysql_command.sql.golden
new file mode 100644
index 00000000..00b83860
--- /dev/null
+++ b/sql/migrate/testdata/lex/11_delimiter_mysql_command.sql.golden
@@ -0,0 +1,9 @@
+CREATE OR REPLACE FUNCTION gen_uuid() RETURNS VARCHAR(22)
+BEGIN
+    RETURN concat(
+        date_format(NOW(6), '%Y%m%d%i%s%f'),
+        ROUND(1 + RAND() * (100 - 2))
+    );
+END;
+-- end --
+CALL gen_uuid();
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/12_delimiter_mysql_command.sql b/sql/migrate/testdata/lex/12_delimiter_mysql_command.sql
new file mode 100644
index 00000000..4a195d7c
--- /dev/null
+++ b/sql/migrate/testdata/lex/12_delimiter_mysql_command.sql
@@ -0,0 +1,11 @@
+delimiter //
+create table t2 (a int) //
+
+delimiter ;
+delimiter //
+
+create table t3 (a int) //
+delimiter ;
+
+show tables;
+drop table t2, t3;
diff --git a/sql/migrate/testdata/lex/12_delimiter_mysql_command.sql.golden b/sql/migrate/testdata/lex/12_delimiter_mysql_command.sql.golden
new file mode 100644
index 00000000..aa0ce264
--- /dev/null
+++ b/sql/migrate/testdata/lex/12_delimiter_mysql_command.sql.golden
@@ -0,0 +1,7 @@
+create table t2 (a int)
+-- end --
+create table t3 (a int)
+-- end --
+show tables;
+-- end --
+drop table t2, t3;
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/13_delimiter_mysql_command.sql b/sql/migrate/testdata/lex/13_delimiter_mysql_command.sql
new file mode 100644
index 00000000..d44fc65d
--- /dev/null
+++ b/sql/migrate/testdata/lex/13_delimiter_mysql_command.sql
@@ -0,0 +1,54 @@
+# Test delimiter :
+select "Test delimiter :" as " ";
+delimiter :
+select * from t1:
+/* Delimiter commands can have comments */
+delimiter ;
+select 'End test :';
+
+/* Test delimiter :; */
+select "Test delimiter :;" as " ";
+delimiter :;
+select * from t1 :;
+delimiter ;
+select 'End test :;';
+
+-- Test delimiter //
+select "Test delimiter //" as " ";
+delimiter //
+select * from t1//
+delimiter ;
+select 'End test //';
+
+# Test delimiter 'MySQL'
+select "Test delimiter MySQL" as " ";
+delimiter 'MySQL'
+select * from t1MySQL
+delimiter ;
+select 'End test MySQL';
+
+# Test delimiter 'delimiter'
+select "Test delimiter delimiter" as " ";
+delimiter delimiter
+select * from t1delimiter
+delimiter ;
+select 'End test delimiter';
+
+# Test delimiter @@
+select "Test delimiter @@" as " ";
+delimiter @@
+select * from t1 @@
+select * from t2@@
+alter table t add column c@@
+delimiter ;
+select 'End test @@';
+
+# Test delimiter \n\n
+select "Test delimiter \n\n" as " ";
+delimiter \n\n
+select * from t1
+
+select * from t2
+
+delimiter ;
+select 'End test \\n\\n';
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/13_delimiter_mysql_command.sql.golden b/sql/migrate/testdata/lex/13_delimiter_mysql_command.sql.golden
new file mode 100644
index 00000000..f631ee3b
--- /dev/null
+++ b/sql/migrate/testdata/lex/13_delimiter_mysql_command.sql.golden
@@ -0,0 +1,47 @@
+select "Test delimiter :" as " ";
+-- end --
+select * from t1
+-- end --
+select 'End test :';
+-- end --
+select "Test delimiter :;" as " ";
+-- end --
+select * from t1
+-- end --
+select 'End test :;';
+-- end --
+select "Test delimiter //" as " ";
+-- end --
+select * from t1
+-- end --
+select 'End test //';
+-- end --
+select "Test delimiter MySQL" as " ";
+-- end --
+select * from t1
+-- end --
+select 'End test MySQL';
+-- end --
+select "Test delimiter delimiter" as " ";
+-- end --
+select * from t1
+-- end --
+select 'End test delimiter';
+-- end --
+select "Test delimiter @@" as " ";
+-- end --
+select * from t1
+-- end --
+select * from t2
+-- end --
+alter table t add column c
+-- end --
+select 'End test @@';
+-- end --
+select "Test delimiter \n\n" as " ";
+-- end --
+select * from t1
+-- end --
+select * from t2
+-- end --
+select 'End test \\n\\n';
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/14_delimiter_mysql_command.sql b/sql/migrate/testdata/lex/14_delimiter_mysql_command.sql
new file mode 100644
index 00000000..7a308780
--- /dev/null
+++ b/sql/migrate/testdata/lex/14_delimiter_mysql_command.sql
@@ -0,0 +1,9 @@
+DELIMITER //
+CREATE PROCEDURE dorepeat(p1 INT)
+    BEGIN
+    SET @x = 0;
+    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+END
+//
+DELIMITER ;
+CALL dorepeat(100)
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/14_delimiter_mysql_command.sql.golden b/sql/migrate/testdata/lex/14_delimiter_mysql_command.sql.golden
new file mode 100644
index 00000000..9e62ebfc
--- /dev/null
+++ b/sql/migrate/testdata/lex/14_delimiter_mysql_command.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(100)
\ 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 354321bf..9507d1d0 100644
--- a/sql/migrate/testdata/lex/5_delimiter.sql
+++ b/sql/migrate/testdata/lex/5_delimiter.sql
@@ -1,6 +1,5 @@
 -- atlas:delimiter \n-- end --\n
 
-DELIMITER $$
 CREATE DEFINER='boring' PROCEDURE proc ()
     COMMENT 'ATLAS_DELIMITER'
     SQL SECURITY INVOKER
@@ -12,8 +11,7 @@ BEGIN
     WHERE type = 'BACKGROUND';
 
     SELECT CONCAT('Enabled ', @rows := ROW_COUNT(), ' background thread', IF(@rows != 1, 's', '')) AS summary;
-END$$
-DELIMITER ;
+END
 
 -- end --
 
diff --git a/sql/migrate/testdata/lex/5_delimiter.sql.golden b/sql/migrate/testdata/lex/5_delimiter.sql.golden
index 549ebcdd..100fa7a3 100644
--- a/sql/migrate/testdata/lex/5_delimiter.sql.golden
+++ b/sql/migrate/testdata/lex/5_delimiter.sql.golden
@@ -1,4 +1,3 @@
-DELIMITER $$
 CREATE DEFINER='boring' PROCEDURE proc ()
     COMMENT 'ATLAS_DELIMITER'
     SQL SECURITY INVOKER
@@ -10,7 +9,6 @@ BEGIN
     WHERE type = 'BACKGROUND';
 
     SELECT CONCAT('Enabled ', @rows := ROW_COUNT(), ' background thread', IF(@rows != 1, 's', '')) AS summary;
-END$$
-DELIMITER ;
+END
 -- end --
 CALL proc();
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/7_delimiter_2n.sql b/sql/migrate/testdata/lex/7_delimiter_2n.sql
index 5cb0d166..e7ef4388 100644
--- a/sql/migrate/testdata/lex/7_delimiter_2n.sql
+++ b/sql/migrate/testdata/lex/7_delimiter_2n.sql
@@ -2,13 +2,11 @@
 
 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
index a1885a5d..d559ef78 100644
--- a/sql/migrate/testdata/lex/7_delimiter_2n.sql.golden
+++ b/sql/migrate/testdata/lex/7_delimiter_2n.sql.golden
@@ -1,12 +1,9 @@
 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
+LANGUAGE plpgsql;
\ 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
index 5d35bbd1..4b66c219 100644
--- a/sql/migrate/testdata/lex/8_delimiter_3n.sql
+++ b/sql/migrate/testdata/lex/8_delimiter_3n.sql
@@ -3,13 +3,10 @@
 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
index a1885a5d..d559ef78 100644
--- a/sql/migrate/testdata/lex/8_delimiter_3n.sql.golden
+++ b/sql/migrate/testdata/lex/8_delimiter_3n.sql.golden
@@ -1,12 +1,9 @@
 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
+LANGUAGE plpgsql;
\ No newline at end of file