wehub-resource-sync
298 lines
8.3 KiB
Go
Executable File
298 lines
8.3 KiB
Go
Executable File
// Copyright 2024 Dolthub, Inc.
|
|
//
|
|
// Licensed under the Apache License, Version 2.0 (the "License");
|
|
// you may not use this file except in compliance with the License.
|
|
// You may obtain a copy of the License at
|
|
//
|
|
// http://www.apache.org/licenses/LICENSE-2.0
|
|
//
|
|
// Unless required by applicable law or agreed to in writing, software
|
|
// distributed under the License is distributed on an "AS IS" BASIS,
|
|
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
|
|
// See the License for the specific language governing permissions and
|
|
// limitations under the License.
|
|
|
|
package _go
|
|
|
|
import (
|
|
"testing"
|
|
|
|
"github.com/dolthub/go-mysql-server/sql"
|
|
)
|
|
|
|
func TestInsert(t *testing.T) {
|
|
RunScripts(t, []ScriptTest{
|
|
{
|
|
Name: "simple insert",
|
|
SetUpScript: []string{
|
|
"CREATE TABLE mytable (id INT PRIMARY KEY, name TEXT)",
|
|
},
|
|
Assertions: []ScriptTestAssertion{
|
|
{
|
|
Query: "INSERT INTO mytable (id, name) VALUES (1, 'hello')",
|
|
SkipResultsCheck: true,
|
|
},
|
|
{
|
|
Query: "INSERT INTO mytable (ID, naME) VALUES (2, 'world')",
|
|
SkipResultsCheck: true,
|
|
},
|
|
{
|
|
Query: "SELECT * FROM mytable order by id",
|
|
Expected: []sql.Row{
|
|
{1, "hello"},
|
|
{2, "world"},
|
|
},
|
|
},
|
|
},
|
|
},
|
|
{
|
|
Name: "keyless insert",
|
|
SetUpScript: []string{
|
|
"CREATE TABLE mytable (id INT, name TEXT)",
|
|
},
|
|
Assertions: []ScriptTestAssertion{
|
|
{
|
|
Query: "INSERT INTO mytable (id, name) VALUES (1, 'hello')",
|
|
SkipResultsCheck: true,
|
|
},
|
|
{
|
|
Query: "INSERT INTO mytable (ID, naME) VALUES (2, 'world')",
|
|
SkipResultsCheck: true,
|
|
},
|
|
{
|
|
Query: "SELECT * FROM mytable order by id",
|
|
Expected: []sql.Row{
|
|
{1, "hello"},
|
|
{2, "world"},
|
|
},
|
|
},
|
|
},
|
|
},
|
|
{
|
|
Name: "on conflict clause",
|
|
SetUpScript: []string{
|
|
"CREATE TABLE mytable (id INT primary key, name TEXT)",
|
|
"create table t2 (id int primary key, c1 text, c2 text)",
|
|
},
|
|
Assertions: []ScriptTestAssertion{
|
|
{
|
|
Query: "INSERT INTO mytable (id, name) VALUES (1, 'hello')",
|
|
SkipResultsCheck: true,
|
|
},
|
|
{
|
|
Query: "INSERT INTO mytable (ID, naME) VALUES (2, 'world')",
|
|
SkipResultsCheck: true,
|
|
},
|
|
{
|
|
Query: "INSERT INTO mytable (ID, naME) VALUES (1, 'world') ON CONFLICT (id) DO UPDATE SET name = 'world'",
|
|
SkipResultsCheck: true,
|
|
},
|
|
{
|
|
Query: "INSERT INTO mytable (ID, naME) VALUES (2, 'hello') ON CONFLICT (id) DO UPDATE SET name = 'conflict'",
|
|
SkipResultsCheck: true,
|
|
},
|
|
{
|
|
Query: "INSERT INTO mytable (ID, naME) VALUES (1, 'not inserted') ON CONFLICT (id) DO NOTHING",
|
|
},
|
|
{
|
|
Query: "SELECT * FROM mytable order by id",
|
|
Expected: []sql.Row{
|
|
{1, "world"},
|
|
{2, "conflict"},
|
|
},
|
|
},
|
|
{
|
|
Query: "INSERT INTO mytable (ID, naME) VALUES (1, 'hello') ON CONFLICT (id) DO UPDATE set name = concat('new', name)",
|
|
},
|
|
{
|
|
Query: "SELECT * FROM mytable order by id",
|
|
Expected: []sql.Row{
|
|
{1, "newworld"},
|
|
{2, "conflict"},
|
|
},
|
|
},
|
|
{
|
|
Query: "INSERT INTO t2 (id, c1, c2) VALUES (1, 'hello', 'world'), (2, 'world', 'hello')",
|
|
SkipResultsCheck: true,
|
|
},
|
|
{
|
|
Query: "INSERT INTO t2 (id, c1, c2) VALUES (1, 'hello', 'world') ON CONFLICT (id) DO UPDATE SET c1 = 'conflict', c2 = c1",
|
|
SkipResultsCheck: true,
|
|
},
|
|
{
|
|
Query: "INSERT INTO t2 (id, c1, c2) VALUES (2, 'hello', 'world') ON CONFLICT (id) DO UPDATE SET c2 = c1",
|
|
SkipResultsCheck: true,
|
|
},
|
|
{
|
|
Query: "SELECT * FROM t2 order by id",
|
|
Expected: []sql.Row{
|
|
{1, "conflict", "conflict"},
|
|
{2, "world", "world"},
|
|
},
|
|
},
|
|
{
|
|
Query: `INSERT INTO t2 (id, c1, c2)
|
|
VALUES ($1, $2, $3)
|
|
ON CONFLICT (id) do update set c1 = $4`,
|
|
BindVars: []any{1, "x", "y", "no conflict expected"},
|
|
},
|
|
},
|
|
},
|
|
{
|
|
Name: "null and unspecified default values",
|
|
SetUpScript: []string{
|
|
"CREATE TABLE t (i INT DEFAULT NULL, j INT)",
|
|
},
|
|
Assertions: []ScriptTestAssertion{
|
|
{
|
|
Query: "INSERT INTO t VALUES (default, default)",
|
|
SkipResultsCheck: true,
|
|
},
|
|
{
|
|
Query: "SELECT * FROM t",
|
|
Expected: []sql.Row{
|
|
{nil, nil},
|
|
},
|
|
},
|
|
},
|
|
},
|
|
{
|
|
Name: "implicit default values",
|
|
SetUpScript: []string{
|
|
"CREATE TABLE t (i INT DEFAULT 123, j INT default 456);",
|
|
},
|
|
Assertions: []ScriptTestAssertion{
|
|
{
|
|
Query: "INSERT INTO t DEFAULT VALUES;",
|
|
SkipResultsCheck: true,
|
|
},
|
|
{
|
|
Query: "SELECT * FROM t",
|
|
Expected: []sql.Row{
|
|
{123, 456},
|
|
},
|
|
},
|
|
},
|
|
},
|
|
{
|
|
Name: "types",
|
|
SetUpScript: []string{
|
|
`create table child (i2 int2, i4 int4, i8 int8, f float, d double precision, v varchar, vl varchar(100), t text, j json, ts timestamp);`,
|
|
},
|
|
Assertions: []ScriptTestAssertion{
|
|
{
|
|
Query: `insert into child values (1, 2, 3, 4.5, 6.7, 'hello', 'world', 'text', '{"a": 1}', '2021-01-01 00:00:00');`,
|
|
},
|
|
{
|
|
Query: `select * from child;`,
|
|
Expected: []sql.Row{
|
|
{int16(1), int32(2), int64(3), float32(4.5), float64(6.7), "hello", "world", "text", `{"a": 1}`, "2021-01-01 00:00:00"},
|
|
},
|
|
},
|
|
},
|
|
},
|
|
{
|
|
Name: "insert returning",
|
|
SetUpScript: []string{
|
|
"CREATE TABLE t (i serial, j INT)",
|
|
"CREATE TABLE u (u uuid DEFAULT 'ac1f3e2d-1e4b-4d3e-8b1f-2b7f1e7f0e3d', j INT)",
|
|
"CREATE TABLE s (v1 varchar DEFAULT 'hello', v2 varchar DEFAULT 'world')",
|
|
"CREATE SCHEMA ts",
|
|
"CREATE TABLE ts.t (i serial, j INT)",
|
|
},
|
|
Assertions: []ScriptTestAssertion{
|
|
{
|
|
Query: "INSERT INTO t (j) VALUES (5), (6), (7) RETURNING i",
|
|
Expected: []sql.Row{
|
|
{1}, {2}, {3},
|
|
},
|
|
},
|
|
{
|
|
Query: "INSERT INTO t (j) VALUES (5), (6), (7) RETURNING i+3",
|
|
Expected: []sql.Row{
|
|
{7}, {8}, {9},
|
|
},
|
|
},
|
|
{
|
|
Query: "INSERT INTO t (j) VALUES (5), (6), (7) RETURNING i+j, j-3*i",
|
|
Expected: []sql.Row{
|
|
{12, -16}, {14, -18}, {16, -20},
|
|
},
|
|
},
|
|
{
|
|
Query: "INSERT INTO u (j) VALUES (5), (6), (7) RETURNING u",
|
|
Expected: []sql.Row{
|
|
{"ac1f3e2d-1e4b-4d3e-8b1f-2b7f1e7f0e3d"}, {"ac1f3e2d-1e4b-4d3e-8b1f-2b7f1e7f0e3d"}, {"ac1f3e2d-1e4b-4d3e-8b1f-2b7f1e7f0e3d"},
|
|
},
|
|
},
|
|
{
|
|
Query: "INSERT INTO s (v2) VALUES (' a') RETURNING concat(v1, v2)",
|
|
Expected: []sql.Row{
|
|
{"hello a"},
|
|
},
|
|
},
|
|
{
|
|
Query: "INSERT INTO s (v1) VALUES ('sup ') RETURNING concat(v1, v2)",
|
|
Expected: []sql.Row{
|
|
{"sup world"},
|
|
},
|
|
},
|
|
{
|
|
Query: "INSERT INTO s (v2, v1) VALUES ('def', 'abc'), ('xyz', 'uvw') RETURNING concat(v1, v2), concat(v2, v1), 100",
|
|
Expected: []sql.Row{
|
|
{"abcdef", "defabc", 100},
|
|
{"uvwxyz", "xyzuvw", 100},
|
|
},
|
|
},
|
|
{
|
|
Query: "INSERT INTO t (j) VALUES (5), (6), (7) RETURNING i, doesnotexist",
|
|
ExpectedErr: "could not be found",
|
|
},
|
|
{
|
|
Query: "INSERT INTO t (j) VALUES (5), (6), (7) RETURNING i, doesnotexist(j)",
|
|
ExpectedErr: "function: 'doesnotexist' not found",
|
|
},
|
|
{
|
|
Query: "INSERT INTO public.t (j) VALUES (8) RETURNING t.j",
|
|
Expected: []sql.Row{{8}},
|
|
},
|
|
{
|
|
Query: "INSERT INTO public.t (j) VALUES (9) RETURNING public.t.j",
|
|
Expected: []sql.Row{{9}},
|
|
},
|
|
{
|
|
Query: "INSERT INTO ts.t (j) VALUES (10) RETURNING ts.t.j",
|
|
Expected: []sql.Row{{10}},
|
|
},
|
|
{
|
|
Query: "INSERT INTO public.t (j) VALUES ($1) RETURNING j;",
|
|
BindVars: []any{11},
|
|
Expected: []sql.Row{{11}},
|
|
},
|
|
{
|
|
Query: "INSERT INTO public.t (j) VALUES ($1) RETURNING t.j;",
|
|
BindVars: []any{12},
|
|
Expected: []sql.Row{{12}},
|
|
},
|
|
{
|
|
Query: "INSERT INTO public.t (j) VALUES ($1) RETURNING public.t.j;",
|
|
BindVars: []any{13},
|
|
Expected: []sql.Row{{13}},
|
|
},
|
|
},
|
|
},
|
|
{
|
|
Name: "insert iso8601 timestamptz literal",
|
|
SetUpScript: []string{
|
|
"CREATE TABLE django_migrations (id serial primary key, app varchar, name varchar, applied timestamptz)",
|
|
},
|
|
Assertions: []ScriptTestAssertion{
|
|
{
|
|
Query: `INSERT INTO "django_migrations" ("app", "name", "applied") VALUES ('contenttypes', '0001_initial', '2025-03-24T19:21:59.690479+00:00'::timestamptz) RETURNING "django_migrations"."id"`,
|
|
Expected: []sql.Row{{1}},
|
|
},
|
|
},
|
|
},
|
|
})
|
|
}
|