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	"time"
23
24	"cloud.google.com/go/civil"
25)
26
27func boolAddr(b bool) *bool {
28	return &b
29}
30
31func TestSQL(t *testing.T) {
32	reparseDDL := func(s string) (interface{}, error) {
33		ddl, err := ParseDDLStmt(s)
34		if err != nil {
35			return nil, err
36		}
37		ddl.clearOffset()
38		return ddl, nil
39	}
40	reparseDML := func(s string) (interface{}, error) {
41		dml, err := ParseDMLStmt(s)
42		if err != nil {
43			return nil, err
44		}
45		return dml, nil
46	}
47	reparseQuery := func(s string) (interface{}, error) {
48		q, err := ParseQuery(s)
49		return q, err
50	}
51	reparseExpr := func(s string) (interface{}, error) {
52		e, pe := newParser("f-expr", s).parseExpr()
53		if pe != nil {
54			return nil, pe
55		}
56		return e, nil
57	}
58
59	latz, err := time.LoadLocation("America/Los_Angeles")
60	if err != nil {
61		t.Fatalf("Loading Los Angeles time zone info: %v", err)
62	}
63
64	line := func(n int) Position { return Position{Line: n} }
65	tests := []struct {
66		data    interface{ SQL() string }
67		sql     string
68		reparse func(string) (interface{}, error)
69	}{
70		{
71			&CreateTable{
72				Name: "Ta",
73				Columns: []ColumnDef{
74					{Name: "Ca", Type: Type{Base: Bool}, NotNull: true, Position: line(2)},
75					{Name: "Cb", Type: Type{Base: Int64}, Position: line(3)},
76					{Name: "Cc", Type: Type{Base: Float64}, Position: line(4)},
77					{Name: "Cd", Type: Type{Base: String, Len: 17}, Position: line(5)},
78					{Name: "Ce", Type: Type{Base: String, Len: MaxLen}, Position: line(6)},
79					{Name: "Cf", Type: Type{Base: Bytes, Len: 4711}, Position: line(7)},
80					{Name: "Cg", Type: Type{Base: Bytes, Len: MaxLen}, Position: line(8)},
81					{Name: "Ch", Type: Type{Base: Date}, Position: line(9)},
82					{Name: "Ci", Type: Type{Base: Timestamp}, Options: ColumnOptions{AllowCommitTimestamp: boolAddr(true)}, Position: line(10)},
83					{Name: "Cj", Type: Type{Array: true, Base: Int64}, Position: line(11)},
84					{Name: "Ck", Type: Type{Array: true, Base: String, Len: MaxLen}, Position: line(12)},
85					{Name: "Cl", Type: Type{Base: Timestamp}, Options: ColumnOptions{AllowCommitTimestamp: boolAddr(false)}, Position: line(13)},
86					{Name: "Cm", Type: Type{Base: Int64}, Generated: Func{Name: "CHAR_LENGTH", Args: []Expr{ID("Ce")}}, Position: line(14)},
87					{Name: "Cn", Type: Type{Base: JSON}, Position: line(15)},
88				},
89				PrimaryKey: []KeyPart{
90					{Column: "Ca"},
91					{Column: "Cb", Desc: true},
92				},
93				Position: line(1),
94			},
95			`CREATE TABLE Ta (
96  Ca BOOL NOT NULL,
97  Cb INT64,
98  Cc FLOAT64,
99  Cd STRING(17),
100  Ce STRING(MAX),
101  Cf BYTES(4711),
102  Cg BYTES(MAX),
103  Ch DATE,
104  Ci TIMESTAMP OPTIONS (allow_commit_timestamp = true),
105  Cj ARRAY<INT64>,
106  Ck ARRAY<STRING(MAX)>,
107  Cl TIMESTAMP OPTIONS (allow_commit_timestamp = null),
108  Cm INT64 AS (CHAR_LENGTH(Ce)) STORED,
109  Cn JSON,
110) PRIMARY KEY(Ca, Cb DESC)`,
111			reparseDDL,
112		},
113		{
114			&CreateTable{
115				Name: "Tsub",
116				Columns: []ColumnDef{
117					{Name: "SomeId", Type: Type{Base: Int64}, NotNull: true, Position: line(2)},
118					{Name: "OtherId", Type: Type{Base: Int64}, NotNull: true, Position: line(3)},
119					// This column name uses a reserved keyword.
120					{Name: "Hash", Type: Type{Base: Bytes, Len: 32}, Position: line(4)},
121				},
122				PrimaryKey: []KeyPart{
123					{Column: "SomeId"},
124					{Column: "OtherId"},
125				},
126				Interleave: &Interleave{
127					Parent:   "Ta",
128					OnDelete: CascadeOnDelete,
129				},
130				Position: line(1),
131			},
132			`CREATE TABLE Tsub (
133  SomeId INT64 NOT NULL,
134  OtherId INT64 NOT NULL,
135  ` + "`Hash`" + ` BYTES(32),
136) PRIMARY KEY(SomeId, OtherId),
137  INTERLEAVE IN PARENT Ta ON DELETE CASCADE`,
138			reparseDDL,
139		},
140		{
141			&CreateTable{
142				Name: "WithRowDeletionPolicy",
143				Columns: []ColumnDef{
144					{Name: "Name", Type: Type{Base: String, Len: MaxLen}, NotNull: true, Position: line(2)},
145					{Name: "DelTimestamp", Type: Type{Base: Timestamp}, NotNull: true, Position: line(3)},
146				},
147				PrimaryKey: []KeyPart{{Column: "Name"}},
148				RowDeletionPolicy: &RowDeletionPolicy{
149					Column:  ID("DelTimestamp"),
150					NumDays: 30,
151				},
152				Position: line(1),
153			},
154			`CREATE TABLE WithRowDeletionPolicy (
155  Name STRING(MAX) NOT NULL,
156  DelTimestamp TIMESTAMP NOT NULL,
157) PRIMARY KEY(Name),
158  ROW DELETION POLICY ( OLDER_THAN ( DelTimestamp, INTERVAL 30 DAY ))`,
159			reparseDDL,
160		},
161		{
162			&DropTable{
163				Name:     "Ta",
164				Position: line(1),
165			},
166			"DROP TABLE Ta",
167			reparseDDL,
168		},
169		{
170			&CreateIndex{
171				Name:  "Ia",
172				Table: "Ta",
173				Columns: []KeyPart{
174					{Column: "Ca"},
175					{Column: "Cb", Desc: true},
176				},
177				Position: line(1),
178			},
179			"CREATE INDEX Ia ON Ta(Ca, Cb DESC)",
180			reparseDDL,
181		},
182		{
183			&DropIndex{
184				Name:     "Ia",
185				Position: line(1),
186			},
187			"DROP INDEX Ia",
188			reparseDDL,
189		},
190		{
191			&CreateView{
192				Name:      "SingersView",
193				OrReplace: true,
194				Query: Query{
195					Select: Select{
196						List: []Expr{ID("SingerId"), ID("FullName"), ID("Picture")},
197						From: []SelectFrom{SelectFromTable{
198							Table: "Singers",
199						}},
200					},
201					Order: []Order{
202						{Expr: ID("LastName")},
203						{Expr: ID("FirstName")},
204					},
205				},
206				Position: line(1),
207			},
208			"CREATE OR REPLACE VIEW SingersView SQL SECURITY INVOKER AS SELECT SingerId, FullName, Picture FROM Singers ORDER BY LastName, FirstName",
209			reparseDDL,
210		},
211		{
212			&DropView{
213				Name:     "SingersView",
214				Position: line(1),
215			},
216			"DROP VIEW SingersView",
217			reparseDDL,
218		},
219		{
220			&AlterTable{
221				Name:       "Ta",
222				Alteration: AddColumn{Def: ColumnDef{Name: "Ca", Type: Type{Base: Bool}, Position: line(1)}},
223				Position:   line(1),
224			},
225			"ALTER TABLE Ta ADD COLUMN Ca BOOL",
226			reparseDDL,
227		},
228		{
229			&AlterTable{
230				Name:       "Ta",
231				Alteration: DropColumn{Name: "Ca"},
232				Position:   line(1),
233			},
234			"ALTER TABLE Ta DROP COLUMN Ca",
235			reparseDDL,
236		},
237		{
238			&AlterTable{
239				Name:       "Ta",
240				Alteration: SetOnDelete{Action: NoActionOnDelete},
241				Position:   line(1),
242			},
243			"ALTER TABLE Ta SET ON DELETE NO ACTION",
244			reparseDDL,
245		},
246		{
247			&AlterTable{
248				Name:       "Ta",
249				Alteration: SetOnDelete{Action: CascadeOnDelete},
250				Position:   line(1),
251			},
252			"ALTER TABLE Ta SET ON DELETE CASCADE",
253			reparseDDL,
254		},
255		{
256			&AlterTable{
257				Name: "Ta",
258				Alteration: AlterColumn{
259					Name: "Cg",
260					Alteration: SetColumnType{
261						Type: Type{Base: String, Len: MaxLen},
262					},
263				},
264				Position: line(1),
265			},
266			"ALTER TABLE Ta ALTER COLUMN Cg STRING(MAX)",
267			reparseDDL,
268		},
269		{
270			&AlterTable{
271				Name: "Ta",
272				Alteration: AlterColumn{
273					Name: "Ci",
274					Alteration: SetColumnOptions{
275						Options: ColumnOptions{
276							AllowCommitTimestamp: boolAddr(false),
277						},
278					},
279				},
280				Position: line(1),
281			},
282			"ALTER TABLE Ta ALTER COLUMN Ci SET OPTIONS (allow_commit_timestamp = null)",
283			reparseDDL,
284		},
285		{
286			&AlterTable{
287				Name:       "WithRowDeletionPolicy",
288				Alteration: DropRowDeletionPolicy{},
289				Position:   line(1),
290			},
291			"ALTER TABLE WithRowDeletionPolicy DROP ROW DELETION POLICY",
292			reparseDDL,
293		},
294		{
295			&AlterTable{
296				Name: "WithRowDeletionPolicy",
297				Alteration: AddRowDeletionPolicy{
298					RowDeletionPolicy: RowDeletionPolicy{
299						Column:  ID("DelTimestamp"),
300						NumDays: 30,
301					},
302				},
303				Position: line(1),
304			},
305			"ALTER TABLE WithRowDeletionPolicy ADD ROW DELETION POLICY ( OLDER_THAN ( DelTimestamp, INTERVAL 30 DAY ))",
306			reparseDDL,
307		},
308		{
309			&AlterTable{
310				Name: "WithRowDeletionPolicy",
311				Alteration: ReplaceRowDeletionPolicy{
312					RowDeletionPolicy: RowDeletionPolicy{
313						Column:  ID("DelTimestamp"),
314						NumDays: 30,
315					},
316				},
317				Position: line(1),
318			},
319			"ALTER TABLE WithRowDeletionPolicy REPLACE ROW DELETION POLICY ( OLDER_THAN ( DelTimestamp, INTERVAL 30 DAY ))",
320			reparseDDL,
321		},
322		{
323			&AlterDatabase{
324				Name: "dbname",
325				Alteration: SetDatabaseOptions{Options: DatabaseOptions{
326					EnableKeyVisualizer: func(b bool) *bool { return &b }(true),
327				}},
328				Position: line(1),
329			},
330			"ALTER DATABASE dbname SET OPTIONS (enable_key_visualizer=true)",
331			reparseDDL,
332		},
333		{
334			&AlterDatabase{
335				Name: "dbname",
336				Alteration: SetDatabaseOptions{Options: DatabaseOptions{
337					OptimizerVersion: func(i int) *int { return &i }(2),
338				}},
339				Position: line(1),
340			},
341			"ALTER DATABASE dbname SET OPTIONS (optimizer_version=2)",
342			reparseDDL,
343		},
344		{
345			&AlterDatabase{
346				Name: "dbname",
347				Alteration: SetDatabaseOptions{Options: DatabaseOptions{
348					VersionRetentionPeriod: func(s string) *string { return &s }("7d"),
349					OptimizerVersion:       func(i int) *int { return &i }(2),
350					EnableKeyVisualizer:    func(b bool) *bool { return &b }(true),
351				}},
352				Position: line(1),
353			},
354			"ALTER DATABASE dbname SET OPTIONS (optimizer_version=2, version_retention_period='7d', enable_key_visualizer=true)",
355			reparseDDL,
356		},
357		{
358			&AlterDatabase{
359				Name: "dbname",
360				Alteration: SetDatabaseOptions{Options: DatabaseOptions{
361					VersionRetentionPeriod: func(s string) *string { return &s }(""),
362					OptimizerVersion:       func(i int) *int { return &i }(0),
363					EnableKeyVisualizer:    func(b bool) *bool { return &b }(false),
364				}},
365				Position: line(1),
366			},
367			"ALTER DATABASE dbname SET OPTIONS (optimizer_version=null, version_retention_period=null, enable_key_visualizer=null)",
368			reparseDDL,
369		},
370		{
371			&Delete{
372				Table: "Ta",
373				Where: ComparisonOp{
374					LHS: ID("C"),
375					Op:  Gt,
376					RHS: IntegerLiteral(2),
377				},
378			},
379			"DELETE FROM Ta WHERE C > 2",
380			reparseDML,
381		},
382		{
383			&Update{
384				Table: "Ta",
385				Items: []UpdateItem{
386					{Column: "Cb", Value: IntegerLiteral(4)},
387					{Column: "Ce", Value: StringLiteral("wow")},
388					{Column: "Cf", Value: ID("Cg")},
389					{Column: "Cg", Value: Null},
390					{Column: "Ch", Value: nil},
391				},
392				Where: ID("Ca"),
393			},
394			`UPDATE Ta SET Cb = 4, Ce = "wow", Cf = Cg, Cg = NULL, Ch = DEFAULT WHERE Ca`,
395			reparseDML,
396		},
397		{
398			Query{
399				Select: Select{
400					List: []Expr{ID("A"), ID("B")},
401					From: []SelectFrom{SelectFromTable{Table: "Table"}},
402					Where: LogicalOp{
403						LHS: ComparisonOp{
404							LHS: ID("C"),
405							Op:  Lt,
406							RHS: StringLiteral("whelp"),
407						},
408						Op: And,
409						RHS: IsOp{
410							LHS: ID("D"),
411							Neg: true,
412							RHS: Null,
413						},
414					},
415					ListAliases: []ID{"", "banana"},
416				},
417				Order: []Order{{Expr: ID("OCol"), Desc: true}},
418				Limit: IntegerLiteral(1000),
419			},
420			`SELECT A, B AS banana FROM Table WHERE C < "whelp" AND D IS NOT NULL ORDER BY OCol DESC LIMIT 1000`,
421			reparseQuery,
422		},
423		{
424			Query{
425				Select: Select{
426					List: []Expr{ID("A")},
427					From: []SelectFrom{SelectFromTable{
428						Table: "Table",
429						Hints: map[string]string{"FORCE_INDEX": "Idx"},
430					}},
431					Where: ComparisonOp{
432						LHS: ID("B"),
433						Op:  Eq,
434						RHS: Param("b"),
435					},
436				},
437			},
438			`SELECT A FROM Table@{FORCE_INDEX=Idx} WHERE B = @b`,
439			reparseQuery,
440		},
441		{
442			Query{
443				Select: Select{
444					List: []Expr{ID("A")},
445					From: []SelectFrom{SelectFromTable{
446						Table: "Table",
447						Hints: map[string]string{"FORCE_INDEX": "Idx", "GROUPBY_SCAN_OPTIMIZATION": "TRUE"},
448					}},
449					Where: ComparisonOp{
450						LHS: ID("B"),
451						Op:  Eq,
452						RHS: Param("b"),
453					},
454				},
455			},
456			`SELECT A FROM Table@{FORCE_INDEX=Idx,GROUPBY_SCAN_OPTIMIZATION=TRUE} WHERE B = @b`,
457			reparseQuery,
458		},
459		{
460			Query{
461				Select: Select{
462					List: []Expr{IntegerLiteral(7)},
463				},
464			},
465			`SELECT 7`,
466			reparseQuery,
467		},
468		{
469			ComparisonOp{LHS: ID("X"), Op: NotBetween, RHS: ID("Y"), RHS2: ID("Z")},
470			`X NOT BETWEEN Y AND Z`,
471			reparseExpr,
472		},
473		{
474			Query{
475				Select: Select{
476					List: []Expr{
477						ID("Desc"),
478					},
479				},
480			},
481			"SELECT `Desc`",
482			reparseQuery,
483		},
484		{
485			DateLiteral(civil.Date{Year: 2014, Month: time.September, Day: 27}),
486			`DATE '2014-09-27'`,
487			reparseExpr,
488		},
489		{
490			TimestampLiteral(time.Date(2014, time.September, 27, 12, 34, 56, 123456e3, latz)),
491			`TIMESTAMP '2014-09-27 12:34:56.123456 -07:00'`,
492			reparseExpr,
493		},
494		{
495			Query{
496				Select: Select{
497					List: []Expr{
498						ID("A"), ID("B"),
499					},
500					From: []SelectFrom{
501						SelectFromJoin{
502							Type: InnerJoin,
503							LHS:  SelectFromTable{Table: "Table1"},
504							RHS:  SelectFromTable{Table: "Table2"},
505							On: ComparisonOp{
506								LHS: PathExp{"Table1", "A"},
507								Op:  Eq,
508								RHS: PathExp{"Table2", "A"},
509							},
510						},
511					},
512				},
513			},
514			"SELECT A, B FROM Table1 INNER JOIN Table2 ON Table1.A = Table2.A",
515			reparseQuery,
516		},
517		{
518			Query{
519				Select: Select{
520					List: []Expr{
521						ID("A"), ID("B"),
522					},
523					From: []SelectFrom{
524						SelectFromJoin{
525							Type: InnerJoin,
526							LHS: SelectFromJoin{
527								Type: InnerJoin,
528								LHS:  SelectFromTable{Table: "Table1"},
529								RHS:  SelectFromTable{Table: "Table2"},
530								On: ComparisonOp{
531									LHS: PathExp{"Table1", "A"},
532									Op:  Eq,
533									RHS: PathExp{"Table2", "A"},
534								},
535							},
536							RHS:   SelectFromTable{Table: "Table3"},
537							Using: []ID{"X"},
538						},
539					},
540				},
541			},
542			"SELECT A, B FROM Table1 INNER JOIN Table2 ON Table1.A = Table2.A INNER JOIN Table3 USING (X)",
543			reparseQuery,
544		},
545	}
546	for _, test := range tests {
547		sql := test.data.SQL()
548		if sql != test.sql {
549			t.Errorf("%v.SQL() wrong.\n got %s\nwant %s", test.data, sql, test.sql)
550			continue
551		}
552
553		// As a confidence check, confirm that parsing the SQL produces the original input.
554		data, err := test.reparse(sql)
555		if err != nil {
556			t.Errorf("Reparsing %q: %v", sql, err)
557			continue
558		}
559		if !reflect.DeepEqual(data, test.data) {
560			t.Errorf("Reparsing %q wrong.\n got %v\nwant %v", sql, data, test.data)
561		}
562	}
563}
564