1/*
2Copyright 2019 Google LLC
3
4Licensed under the Apache License, Version 2.0 (the "License");
5you may not use this file except in compliance with the License.
6You may obtain a copy of the License at
7
8    http://www.apache.org/licenses/LICENSE-2.0
9
10Unless required by applicable law or agreed to in writing, software
11distributed under the License is distributed on an "AS IS" BASIS,
12WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13See the License for the specific language governing permissions and
14limitations under the License.
15*/
16
17package spansql
18
19import (
20	"reflect"
21	"testing"
22)
23
24func boolAddr(b bool) *bool {
25	return &b
26}
27
28func TestSQL(t *testing.T) {
29	reparseDDL := func(s string) (interface{}, error) {
30		ddl, err := ParseDDLStmt(s)
31		if err != nil {
32			return nil, err
33		}
34		ddl.clearOffset()
35		return ddl, nil
36	}
37	reparseDML := func(s string) (interface{}, error) {
38		dml, err := ParseDMLStmt(s)
39		if err != nil {
40			return nil, err
41		}
42		return dml, nil
43	}
44	reparseQuery := func(s string) (interface{}, error) {
45		q, err := ParseQuery(s)
46		return q, err
47	}
48	reparseExpr := func(s string) (interface{}, error) {
49		e, pe := newParser("f-expr", s).parseExpr()
50		if pe != nil {
51			return nil, pe
52		}
53		return e, nil
54	}
55
56	line := func(n int) Position { return Position{Line: n} }
57	tests := []struct {
58		data    interface{ SQL() string }
59		sql     string
60		reparse func(string) (interface{}, error)
61	}{
62		{
63			&CreateTable{
64				Name: "Ta",
65				Columns: []ColumnDef{
66					{Name: "Ca", Type: Type{Base: Bool}, NotNull: true, Position: line(2)},
67					{Name: "Cb", Type: Type{Base: Int64}, Position: line(3)},
68					{Name: "Cc", Type: Type{Base: Float64}, Position: line(4)},
69					{Name: "Cd", Type: Type{Base: String, Len: 17}, Position: line(5)},
70					{Name: "Ce", Type: Type{Base: String, Len: MaxLen}, Position: line(6)},
71					{Name: "Cf", Type: Type{Base: Bytes, Len: 4711}, Position: line(7)},
72					{Name: "Cg", Type: Type{Base: Bytes, Len: MaxLen}, Position: line(8)},
73					{Name: "Ch", Type: Type{Base: Date}, Position: line(9)},
74					{Name: "Ci", Type: Type{Base: Timestamp}, AllowCommitTimestamp: boolAddr(true), Position: line(10)},
75					{Name: "Cj", Type: Type{Array: true, Base: Int64}, Position: line(11)},
76					{Name: "Ck", Type: Type{Array: true, Base: String, Len: MaxLen}, Position: line(12)},
77					{Name: "Cl", Type: Type{Base: Timestamp}, AllowCommitTimestamp: boolAddr(false), Position: line(13)},
78				},
79				PrimaryKey: []KeyPart{
80					{Column: "Ca"},
81					{Column: "Cb", Desc: true},
82				},
83				Position: line(1),
84			},
85			`CREATE TABLE Ta (
86  Ca BOOL NOT NULL,
87  Cb INT64,
88  Cc FLOAT64,
89  Cd STRING(17),
90  Ce STRING(MAX),
91  Cf BYTES(4711),
92  Cg BYTES(MAX),
93  Ch DATE,
94  Ci TIMESTAMP OPTIONS (allow_commit_timestamp = true),
95  Cj ARRAY<INT64>,
96  Ck ARRAY<STRING(MAX)>,
97  Cl TIMESTAMP OPTIONS (allow_commit_timestamp = null),
98) PRIMARY KEY(Ca, Cb DESC)`,
99			reparseDDL,
100		},
101		{
102			&CreateTable{
103				Name: "Tsub",
104				Columns: []ColumnDef{
105					{Name: "SomeId", Type: Type{Base: Int64}, NotNull: true, Position: line(2)},
106					{Name: "OtherId", Type: Type{Base: Int64}, NotNull: true, Position: line(3)},
107					// This column name uses a reserved keyword.
108					{Name: "Hash", Type: Type{Base: Bytes, Len: 32}, Position: line(4)},
109				},
110				PrimaryKey: []KeyPart{
111					{Column: "SomeId"},
112					{Column: "OtherId"},
113				},
114				Interleave: &Interleave{
115					Parent:   "Ta",
116					OnDelete: CascadeOnDelete,
117				},
118				Position: line(1),
119			},
120			`CREATE TABLE Tsub (
121  SomeId INT64 NOT NULL,
122  OtherId INT64 NOT NULL,
123  ` + "`Hash`" + ` BYTES(32),
124) PRIMARY KEY(SomeId, OtherId),
125  INTERLEAVE IN PARENT Ta ON DELETE CASCADE`,
126			reparseDDL,
127		},
128		{
129			&DropTable{
130				Name:     "Ta",
131				Position: line(1),
132			},
133			"DROP TABLE Ta",
134			reparseDDL,
135		},
136		{
137			&CreateIndex{
138				Name:  "Ia",
139				Table: "Ta",
140				Columns: []KeyPart{
141					{Column: "Ca"},
142					{Column: "Cb", Desc: true},
143				},
144				Position: line(1),
145			},
146			"CREATE INDEX Ia ON Ta(Ca, Cb DESC)",
147			reparseDDL,
148		},
149		{
150			&DropIndex{
151				Name:     "Ia",
152				Position: line(1),
153			},
154			"DROP INDEX Ia",
155			reparseDDL,
156		},
157		{
158			&AlterTable{
159				Name:       "Ta",
160				Alteration: AddColumn{Def: ColumnDef{Name: "Ca", Type: Type{Base: Bool}, Position: line(1)}},
161				Position:   line(1),
162			},
163			"ALTER TABLE Ta ADD COLUMN Ca BOOL",
164			reparseDDL,
165		},
166		{
167			&AlterTable{
168				Name:       "Ta",
169				Alteration: DropColumn{Name: "Ca"},
170				Position:   line(1),
171			},
172			"ALTER TABLE Ta DROP COLUMN Ca",
173			reparseDDL,
174		},
175		{
176			&AlterTable{
177				Name:       "Ta",
178				Alteration: SetOnDelete{Action: NoActionOnDelete},
179				Position:   line(1),
180			},
181			"ALTER TABLE Ta SET ON DELETE NO ACTION",
182			reparseDDL,
183		},
184		{
185			&AlterTable{
186				Name:       "Ta",
187				Alteration: SetOnDelete{Action: CascadeOnDelete},
188				Position:   line(1),
189			},
190			"ALTER TABLE Ta SET ON DELETE CASCADE",
191			reparseDDL,
192		},
193		{
194			&Delete{
195				Table: "Ta",
196				Where: ComparisonOp{
197					LHS: ID("C"),
198					Op:  Gt,
199					RHS: IntegerLiteral(2),
200				},
201			},
202			"DELETE FROM Ta WHERE C > 2",
203			reparseDML,
204		},
205		{
206			Query{
207				Select: Select{
208					List: []Expr{ID("A"), ID("B")},
209					From: []SelectFrom{{Table: "Table"}},
210					Where: LogicalOp{
211						LHS: ComparisonOp{
212							LHS: ID("C"),
213							Op:  Lt,
214							RHS: StringLiteral("whelp"),
215						},
216						Op: And,
217						RHS: IsOp{
218							LHS: ID("D"),
219							Neg: true,
220							RHS: Null,
221						},
222					},
223					ListAliases: []string{"", "banana"},
224				},
225				Order: []Order{{Expr: ID("OCol"), Desc: true}},
226				Limit: IntegerLiteral(1000),
227			},
228			`SELECT A, B AS banana FROM Table WHERE C < "whelp" AND D IS NOT NULL ORDER BY OCol DESC LIMIT 1000`,
229			reparseQuery,
230		},
231		{
232			Query{
233				Select: Select{
234					List: []Expr{IntegerLiteral(7)},
235				},
236			},
237			`SELECT 7`,
238			reparseQuery,
239		},
240		{
241			ComparisonOp{LHS: ID("X"), Op: NotBetween, RHS: ID("Y"), RHS2: ID("Z")},
242			`X NOT BETWEEN Y AND Z`,
243			reparseExpr,
244		},
245		{
246			Query{
247				Select: Select{
248					List: []Expr{
249						ID("Desc"),
250					},
251				},
252			},
253			"SELECT `Desc`",
254			reparseQuery,
255		},
256	}
257	for _, test := range tests {
258		sql := test.data.SQL()
259		if sql != test.sql {
260			t.Errorf("%v.SQL() wrong.\n got %s\nwant %s", test.data, sql, test.sql)
261			continue
262		}
263
264		// As a sanity check, confirm that parsing the SQL produces the original input.
265		data, err := test.reparse(sql)
266		if err != nil {
267			t.Errorf("Reparsing %q: %v", sql, err)
268			continue
269		}
270		if !reflect.DeepEqual(data, test.data) {
271			t.Errorf("Reparsing %q wrong.\n got %v\nwant %v", sql, data, test.data)
272		}
273	}
274}
275