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