A bunch of invalid MySQL schemas

sqlc-dev · kyleconroy · Jan 3, 2024 · Jan 2, 2024 · Jan 2, 2024 · Jan 2, 2024
commit 2c9edc175ab4bd970b15cf58ba1699a0a2f49caf
diff --git a/internal/endtoend/testdata/ddl_create_table_unknown_type/postgresql/pgx/exec.json b/internal/endtoend/testdata/ddl_create_table_unknown_type/postgresql/pgx/exec.json
@@ -1,3 +1,3 @@
 {
-  "contexts": ["managed-db"]
+  "contexts": ["unknown"]
 }
diff --git a/internal/endtoend/testdata/ddl_drop_schema/mysql/exec.json b/internal/endtoend/testdata/ddl_drop_schema/mysql/exec.json
@@ -0,0 +1,3 @@
+{
+    "contexts": ["base"]
+}
diff --git a/internal/endtoend/testdata/insert_select_invalid/mysql/query.sql b/internal/endtoend/testdata/insert_select_invalid/mysql/query.sql
@@ -1,5 +1,3 @@
-CREATE TABLE foo (bar text);
-
 -- name: InsertFoo :exec
 INSERT INTO foo (bar)
 SELECT 1, ?, ?;
diff --git a/internal/endtoend/testdata/insert_select_invalid/mysql/schema.sql b/internal/endtoend/testdata/insert_select_invalid/mysql/schema.sql
@@ -0,0 +1 @@
+CREATE TABLE foo (bar text);
diff --git a/internal/endtoend/testdata/insert_select_invalid/mysql/sqlc.json b/internal/endtoend/testdata/insert_select_invalid/mysql/sqlc.json
@@ -5,7 +5,7 @@
       "engine": "mysql",
       "path": "go",
       "name": "querytest",
-      "schema": "query.sql",
+      "schema": "schema.sql",
       "queries": "query.sql"
     }
   ]

diff --git a/internal/endtoend/testdata/invalid_named_params/mysql/query.sql b/internal/endtoend/testdata/invalid_named_params/mysql/query.sql
@@ -1,8 +1,3 @@
-CREATE TABLE authors (
-  id   BIGINT PRIMARY KEY,
-  bio  TEXT
-);
-
 -- name: ListAuthors :one
 SELECT  *
 FROM    authors

diff --git a/internal/endtoend/testdata/invalid_named_params/mysql/schema.sql b/internal/endtoend/testdata/invalid_named_params/mysql/schema.sql
@@ -0,0 +1,4 @@
+CREATE TABLE authors (
+  id   BIGINT PRIMARY KEY,
+  bio  TEXT
+);
diff --git a/internal/endtoend/testdata/invalid_table_alias/mysql/query.sql b/internal/endtoend/testdata/invalid_table_alias/mysql/query.sql
@@ -1,10 +1,3 @@
--- https://github.com/sqlc-dev/sqlc/issues/437
-CREATE TABLE authors (
-  id   INT PRIMARY KEY,
-  name VARCHAR(255) NOT NULL,
-  bio  text
-);
-
 -- name: GetAuthor :one
 SELECT  *
 FROM    authors a

diff --git a/internal/endtoend/testdata/invalid_table_alias/mysql/schema.sql b/internal/endtoend/testdata/invalid_table_alias/mysql/schema.sql
@@ -0,0 +1,7 @@
+-- https://github.com/sqlc-dev/sqlc/issues/437
+CREATE TABLE authors (
+  id   INT PRIMARY KEY,
+  name VARCHAR(255) NOT NULL,
+  bio  text
+);
+
diff --git a/internal/endtoend/testdata/invalid_table_alias/mysql/sqlc.json b/internal/endtoend/testdata/invalid_table_alias/mysql/sqlc.json
@@ -5,7 +5,7 @@
 			"path": "go",
 			"engine": "mysql",
 			"name": "querytest",
-			"schema": "query.sql",
+			"schema": "schema.sql",
 			"queries": "query.sql"
 		}
 	]

diff --git a/internal/endtoend/testdata/join_left/mysql/schema.sql b/internal/endtoend/testdata/join_left/mysql/schema.sql
@@ -28,11 +28,11 @@ CREATE TABLE super_authors (
 CREATE TABLE users_2 (
     user_id           INT PRIMARY KEY,
     user_nickname     VARCHAR(30) UNIQUE NOT NULL,
-    user_email        TEXT UNIQUE        NOT NULL,
+    user_email        VARCHAR(20) UNIQUE        NOT NULL,
     user_display_name TEXT               NOT NULL,
     user_password     TEXT               NULL,
-    user_google_id    TEXT UNIQUE        NULL,
-    user_apple_id     TEXT UNIQUE        NULL,
+    user_google_id    VARCHAR(20) UNIQUE        NULL,
+    user_apple_id     VARCHAR(20) UNIQUE        NULL,
     user_bio          VARCHAR(160)       NOT NULL DEFAULT '',
     user_created_at   TIMESTAMP          NOT NULL DEFAULT CURRENT_TIMESTAMP,
     user_avatar_id    INT UNIQUE        NULL

diff --git a/internal/endtoend/testdata/join_table_name/mysql/schema.sql b/internal/endtoend/testdata/join_table_name/mysql/schema.sql
@@ -1,3 +1,3 @@
 CREATE TABLE bar (id serial not null);
-CREATE TABLE foo (id serial not null, bar serial references bar(id));
+CREATE TABLE foo (id serial not null, bar integer references bar(id));
 
diff --git a/internal/endtoend/testdata/join_two_tables/mysql/schema.sql b/internal/endtoend/testdata/join_two_tables/mysql/schema.sql
@@ -1,4 +1,4 @@
-CREATE TABLE foo (bar_id serial not null, baz_id serial not null);
+CREATE TABLE foo (bar_id serial not null, baz_id integer not null);
 CREATE TABLE bar (id serial not null);
 CREATE TABLE baz (id serial not null);
 
diff --git a/internal/endtoend/testdata/mix_param_types/mysql/schema.sql b/internal/endtoend/testdata/mix_param_types/mysql/schema.sql
@@ -0,0 +1,5 @@
+CREATE TABLE bar (
+       id serial not null,
+       name text not null,
+       phone text not null
+);
diff --git a/internal/endtoend/testdata/mix_param_types/mysql/sqlc.json b/internal/endtoend/testdata/mix_param_types/mysql/sqlc.json
@@ -4,7 +4,7 @@
     {
       "path": "go",
       "name": "querytest",
-      "schema": "test.sql",
+      "schema": "schema.sql",
       "queries": "test.sql",
       "engine": "mysql"
     }

diff --git a/internal/endtoend/testdata/mix_param_types/mysql/test.sql b/internal/endtoend/testdata/mix_param_types/mysql/test.sql
@@ -1,9 +1,3 @@
-CREATE TABLE bar (
-       id serial not null,
-       name text not null,
-       phone text not null
-);
-
 -- name: CountOne :one
 SELECT count(1) FROM bar WHERE id = sqlc.arg(id) AND name <> ?; 
 

diff --git a/internal/endtoend/testdata/mysql_reference_manual/aggregate_functions/group_concat.sql b/internal/endtoend/testdata/mysql_reference_manual/aggregate_functions/group_concat.sql
@@ -1,8 +1,3 @@
-CREATE TABLE student (
-    student_name VARCHAR(255),
-    score DOUBLE
-);
-
 -- name: GroupConcat :many
 SELECT student_name, GROUP_CONCAT(test_score)
 FROM student

diff --git a/internal/endtoend/testdata/mysql_reference_manual/schema.sql b/internal/endtoend/testdata/mysql_reference_manual/schema.sql
@@ -0,0 +1,4 @@
+CREATE TABLE student (
+    student_name VARCHAR(255),
+    test_score DOUBLE
+);
diff --git a/internal/endtoend/testdata/schema_scoped_create/mysql/exec.json b/internal/endtoend/testdata/schema_scoped_create/mysql/exec.json
@@ -0,0 +1,3 @@
+{
+  "contexts": ["base"]
+}
diff --git a/internal/endtoend/testdata/schema_scoped_delete/mysql/exec.json b/internal/endtoend/testdata/schema_scoped_delete/mysql/exec.json
@@ -0,0 +1,4 @@
+{
+  "contexts": ["base"]
+}
+
diff --git a/internal/endtoend/testdata/schema_scoped_filter/mysql/exec.json b/internal/endtoend/testdata/schema_scoped_filter/mysql/exec.json
@@ -0,0 +1,3 @@
+{
+    "contexts": ["base"]
+}
diff --git a/internal/endtoend/testdata/schema_scoped_list/mysql/exec.json b/internal/endtoend/testdata/schema_scoped_list/mysql/exec.json
@@ -0,0 +1,3 @@
+{
+    "contexts": ["base"]
+}
diff --git a/internal/endtoend/testdata/select_empty_column_list/mysql/query.sql b/internal/endtoend/testdata/select_empty_column_list/mysql/query.sql
@@ -1,4 +1,2 @@
-CREATE TABLE bar (name text);
-
 -- name: GetBars :many
-SELECT FROM bar;
+SELECT FROM bar;
diff --git a/internal/endtoend/testdata/select_empty_column_list/mysql/schema.sql b/internal/endtoend/testdata/select_empty_column_list/mysql/schema.sql
@@ -0,0 +1 @@
+CREATE TABLE bar (name text);
diff --git a/internal/endtoend/testdata/select_empty_column_list/mysql/sqlc.json b/internal/endtoend/testdata/select_empty_column_list/mysql/sqlc.json
@@ -5,7 +5,7 @@
       "path": "go",
       "engine": "mysql",
       "name": "querytest",
-      "schema": "query.sql",
+      "schema": "schema.sql",
       "queries": "query.sql"
     }
   ]

diff --git a/internal/endtoend/testdata/single_param_conflict/mysql/schema.sql b/internal/endtoend/testdata/single_param_conflict/mysql/schema.sql
@@ -7,5 +7,5 @@ CREATE TABLE authors (
 
 -- https://github.com/sqlc-dev/sqlc/issues/1290
 CREATE TABLE users (
-  sub TEXT PRIMARY KEY
+  sub VARCHAR(10) PRIMARY KEY
 );
diff --git a/internal/endtoend/testdata/sqlc_arg_invalid/mysql/query.sql b/internal/endtoend/testdata/sqlc_arg_invalid/mysql/query.sql
@@ -1,8 +1,3 @@
-CREATE TABLE users (
-    id         serial,
-    first_name text not null
-);
-
 -- name: WrongFunc :one
 select id, first_name from users where id = sqlc.argh(target_id);
 

diff --git a/internal/endtoend/testdata/sqlc_arg_invalid/mysql/schema.sql b/internal/endtoend/testdata/sqlc_arg_invalid/mysql/schema.sql
@@ -0,0 +1,4 @@
+CREATE TABLE users (
+    id         serial,
+    first_name text not null
+);
diff --git a/internal/endtoend/testdata/valid_group_by_reference/mysql/schema.sql b/internal/endtoend/testdata/valid_group_by_reference/mysql/schema.sql
@@ -1,6 +1,6 @@
 CREATE TABLE authors (
   id   BIGINT  NOT NULL AUTO_INCREMENT PRIMARY KEY,
-  name text    NOT NULL,
+  name VARCHAR(10)    NOT NULL,
   bio  text,
   UNIQUE(name)
 );

diff --git a/internal/sqltest/local/mysql.go b/internal/sqltest/local/mysql.go
@@ -5,6 +5,7 @@ import (
 	"database/sql"
 	"fmt"
 	"os"
+	"strings"
 	"sync"
 	"testing"
 
@@ -79,6 +80,9 @@ func MySQL(t *testing.T, migrations []string) string {
 	defer db.Close()
 
 	for _, q := range seed {
+		if len(strings.TrimSpace(q)) == 0 {
+			continue
+		}
 		if _, err := db.ExecContext(ctx, q); err != nil {
 			t.Fatalf("%s: %s", q, err)
 		}

diff --git a/internal/sqltest/local/postgres.go b/internal/sqltest/local/postgres.go
@@ -5,6 +5,7 @@ import (
 	"fmt"
 	"net/url"
 	"os"
+	"strings"
 	"sync"
 	"testing"
 
@@ -79,6 +80,9 @@ func PostgreSQL(t *testing.T, migrations []string) string {
 	defer conn.Close(ctx)
 
 	for _, q := range seed {
+		if len(strings.TrimSpace(q)) == 0 {
+			continue
+		}
 		if _, err := conn.Exec(ctx, q); err != nil {
 			t.Fatalf("%s: %s", q, err)
 		}