Repository: cloudspannerecosystem/memefish
Pull request #228 (2024-12-18 00:40:36)

Fix the format of CREATE INDEX statement
This PR fixes the format of `CREATE INDEX` statement to more official format.

```diff
-CREATE INDEX foo_bar ON foo (bar DESC, baz ASC)
+CREATE INDEX foo_bar ON foo(bar DESC, baz ASC)
```

It is requested by another user in GCPUG Slack `#memefish` so I want to prioritize it.

- Part of #190
  - I think it is not finished because I am not yet research other differences.


Patch:
diff --git a/ast/sql.go b/ast/sql.go
index eedd404..8113ec4 100644
--- a/ast/sql.go
+++ b/ast/sql.go
@@ -1027,32 +1027,16 @@ func (r *RenameTable) SQL() string { return "RENAME TABLE " + sqlJoin(r.Tos, ",
 func (r *RenameTableTo) SQL() string { return r.Old.SQL() + " TO " + r.New.SQL() }
 
 func (c *CreateIndex) SQL() string {
-	sql := "CREATE "
-	if c.Unique {
-		sql += "UNIQUE "
-	}
-	if c.NullFiltered {
-		sql += "NULL_FILTERED "
-	}
-	sql += "INDEX "
-	if c.IfNotExists {
-		sql += "IF NOT EXISTS "
-	}
-	sql += c.Name.SQL() + " ON " + c.TableName.SQL() + " ("
-	for i, k := range c.Keys {
-		if i != 0 {
-			sql += ", "
-		}
-		sql += k.SQL()
-	}
-	sql += ")"
-	if c.Storing != nil {
-		sql += " " + c.Storing.SQL()
-	}
-	if c.InterleaveIn != nil {
-		sql += c.InterleaveIn.SQL()
-	}
-	return sql
+	return "CREATE " +
+		strOpt(c.Unique, "UNIQUE ") +
+		strOpt(c.NullFiltered, "NULL_FILTERED ") +
+		"INDEX " +
+		strOpt(c.IfNotExists, "IF NOT EXISTS ") +
+		c.Name.SQL() + " ON " + c.TableName.SQL() + "(" +
+		sqlJoin(c.Keys, ", ") +
+		")" +
+		sqlOpt(" ", c.Storing, "") +
+		sqlOpt("", c.InterleaveIn, "")
 }
 
 func (c *CreateVectorIndex) SQL() string {


Test patch:
diff --git a/testdata/result/ddl/create_index.sql.txt b/testdata/result/ddl/create_index.sql.txt
index 568a898..1e7c971 100644
--- a/testdata/result/ddl/create_index.sql.txt
+++ b/testdata/result/ddl/create_index.sql.txt
@@ -54,4 +54,4 @@ create index foo_bar on foo (
 }
 
 --- SQL
-CREATE INDEX foo_bar ON foo (bar DESC, baz ASC)
+CREATE INDEX foo_bar ON foo(bar DESC, baz ASC)
diff --git a/testdata/result/ddl/create_index_if_not_exists.sql.txt b/testdata/result/ddl/create_index_if_not_exists.sql.txt
index 760d3db..fc35d85 100644
--- a/testdata/result/ddl/create_index_if_not_exists.sql.txt
+++ b/testdata/result/ddl/create_index_if_not_exists.sql.txt
@@ -42,4 +42,4 @@ create index if not exists foo_bar on foo (bar)
 }
 
 --- SQL
-CREATE INDEX IF NOT EXISTS foo_bar ON foo (bar)
+CREATE INDEX IF NOT EXISTS foo_bar ON foo(bar)
diff --git a/testdata/result/ddl/create_index_interleave.sql.txt b/testdata/result/ddl/create_index_interleave.sql.txt
index c4efd04..32e8a5d 100644
--- a/testdata/result/ddl/create_index_interleave.sql.txt
+++ b/testdata/result/ddl/create_index_interleave.sql.txt
@@ -62,4 +62,4 @@ create index foo_bar on foo (
 }
 
 --- SQL
-CREATE INDEX foo_bar ON foo (foo DESC) STORING (bar), INTERLEAVE IN foobar
+CREATE INDEX foo_bar ON foo(foo DESC) STORING (bar), INTERLEAVE IN foobar
diff --git a/testdata/result/ddl/create_index_storing.sql.txt b/testdata/result/ddl/create_index_storing.sql.txt
index 3c538de..1d08e6e 100644
--- a/testdata/result/ddl/create_index_storing.sql.txt
+++ b/testdata/result/ddl/create_index_storing.sql.txt
@@ -59,4 +59,4 @@ create index foo_bar on foo (
 }
 
 --- SQL
-CREATE INDEX foo_bar ON foo (bar ASC) STORING (foo, baz)
+CREATE INDEX foo_bar ON foo(bar ASC) STORING (foo, baz)
diff --git a/testdata/result/ddl/create_uniq_null_filtered_index.sql.txt b/testdata/result/ddl/create_uniq_null_filtered_index.sql.txt
index 8337e5b..d6bd80b 100644
--- a/testdata/result/ddl/create_uniq_null_filtered_index.sql.txt
+++ b/testdata/result/ddl/create_uniq_null_filtered_index.sql.txt
@@ -42,4 +42,4 @@ create unique null_filtered index foo_bar on foo (foo)
 }
 
 --- SQL
-CREATE UNIQUE NULL_FILTERED INDEX foo_bar ON foo (foo)
+CREATE UNIQUE NULL_FILTERED INDEX foo_bar ON foo(foo)
diff --git a/testdata/result/ddl/named_schemas_create_index.sql.txt b/testdata/result/ddl/named_schemas_create_index.sql.txt
index 7f58a8e..8ee2b3f 100644
--- a/testdata/result/ddl/named_schemas_create_index.sql.txt
+++ b/testdata/result/ddl/named_schemas_create_index.sql.txt
@@ -51,4 +51,4 @@ CREATE INDEX sch1.indexOnSingers ON sch1.Singers(FirstName)
 }
 
 --- SQL
-CREATE INDEX sch1.indexOnSingers ON sch1.Singers (FirstName)
+CREATE INDEX sch1.indexOnSingers ON sch1.Singers(FirstName)
diff --git a/testdata/result/statement/create_index.sql.txt b/testdata/result/statement/create_index.sql.txt
index 568a898..1e7c971 100644
--- a/testdata/result/statement/create_index.sql.txt
+++ b/testdata/result/statement/create_index.sql.txt
@@ -54,4 +54,4 @@ create index foo_bar on foo (
 }
 
 --- SQL
-CREATE INDEX foo_bar ON foo (bar DESC, baz ASC)
+CREATE INDEX foo_bar ON foo(bar DESC, baz ASC)
diff --git a/testdata/result/statement/create_index_if_not_exists.sql.txt b/testdata/result/statement/create_index_if_not_exists.sql.txt
index 760d3db..fc35d85 100644
--- a/testdata/result/statement/create_index_if_not_exists.sql.txt
+++ b/testdata/result/statement/create_index_if_not_exists.sql.txt
@@ -42,4 +42,4 @@ create index if not exists foo_bar on foo (bar)
 }
 
 --- SQL
-CREATE INDEX IF NOT EXISTS foo_bar ON foo (bar)
+CREATE INDEX IF NOT EXISTS foo_bar ON foo(bar)
diff --git a/testdata/result/statement/create_index_interleave.sql.txt b/testdata/result/statement/create_index_interleave.sql.txt
index c4efd04..32e8a5d 100644
--- a/testdata/result/statement/create_index_interleave.sql.txt
+++ b/testdata/result/statement/create_index_interleave.sql.txt
@@ -62,4 +62,4 @@ create index foo_bar on foo (
 }
 
 --- SQL
-CREATE INDEX foo_bar ON foo (foo DESC) STORING (bar), INTERLEAVE IN foobar
+CREATE INDEX foo_bar ON foo(foo DESC) STORING (bar), INTERLEAVE IN foobar
diff --git a/testdata/result/statement/create_index_storing.sql.txt b/testdata/result/statement/create_index_storing.sql.txt
index 3c538de..1d08e6e 100644
--- a/testdata/result/statement/create_index_storing.sql.txt
+++ b/testdata/result/statement/create_index_storing.sql.txt
@@ -59,4 +59,4 @@ create index foo_bar on foo (
 }
 
 --- SQL
-CREATE INDEX foo_bar ON foo (bar ASC) STORING (foo, baz)
+CREATE INDEX foo_bar ON foo(bar ASC) STORING (foo, baz)
diff --git a/testdata/result/statement/create_uniq_null_filtered_index.sql.txt b/testdata/result/statement/create_uniq_null_filtered_index.sql.txt
index 8337e5b..d6bd80b 100644
--- a/testdata/result/statement/create_uniq_null_filtered_index.sql.txt
+++ b/testdata/result/statement/create_uniq_null_filtered_index.sql.txt
@@ -42,4 +42,4 @@ create unique null_filtered index foo_bar on foo (foo)
 }
 
 --- SQL
-CREATE UNIQUE NULL_FILTERED INDEX foo_bar ON foo (foo)
+CREATE UNIQUE NULL_FILTERED INDEX foo_bar ON foo(foo)
diff --git a/testdata/result/statement/named_schemas_create_index.sql.txt b/testdata/result/statement/named_schemas_create_index.sql.txt
index 7f58a8e..8ee2b3f 100644
--- a/testdata/result/statement/named_schemas_create_index.sql.txt
+++ b/testdata/result/statement/named_schemas_create_index.sql.txt
@@ -51,4 +51,4 @@ CREATE INDEX sch1.indexOnSingers ON sch1.Singers(FirstName)
 }
 
 --- SQL
-CREATE INDEX sch1.indexOnSingers ON sch1.Singers (FirstName)
+CREATE INDEX sch1.indexOnSingers ON sch1.Singers(FirstName)