/
test_parse_utils.py
413 lines (374 loc) · 15.6 KB
/
test_parse_utils.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
# Copyright 2020 Google LLC All rights reserved.
#
# 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.
import sys
import unittest
from google.cloud.spanner_v1 import param_types
class TestParseUtils(unittest.TestCase):
skip_condition = sys.version_info[0] < 3
skip_message = "Subtests are not supported in Python 2"
def test_classify_stmt(self):
from google.cloud.spanner_dbapi.parse_utils import STMT_DDL
from google.cloud.spanner_dbapi.parse_utils import STMT_INSERT
from google.cloud.spanner_dbapi.parse_utils import STMT_NON_UPDATING
from google.cloud.spanner_dbapi.parse_utils import STMT_UPDATING
from google.cloud.spanner_dbapi.parse_utils import classify_stmt
cases = (
("SELECT 1", STMT_NON_UPDATING),
("SELECT s.SongName FROM Songs AS s", STMT_NON_UPDATING),
(
"WITH sq AS (SELECT SchoolID FROM Roster) SELECT * from sq",
STMT_NON_UPDATING,
),
(
"CREATE TABLE django_content_type (id STRING(64) NOT NULL, name STRING(100) "
"NOT NULL, app_label STRING(100) NOT NULL, model STRING(100) NOT NULL) PRIMARY KEY(id)",
STMT_DDL,
),
(
"CREATE INDEX SongsBySingerAlbumSongNameDesc ON "
"Songs(SingerId, AlbumId, SongName DESC), INTERLEAVE IN Albums",
STMT_DDL,
),
("CREATE INDEX SongsBySongName ON Songs(SongName)", STMT_DDL),
(
"CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget)",
STMT_DDL,
),
("INSERT INTO table (col1) VALUES (1)", STMT_INSERT),
("UPDATE table SET col1 = 1 WHERE col1 = NULL", STMT_UPDATING),
)
for query, want_class in cases:
self.assertEqual(classify_stmt(query), want_class)
@unittest.skipIf(skip_condition, skip_message)
def test_parse_insert(self):
from google.cloud.spanner_dbapi.parse_utils import parse_insert
from google.cloud.spanner_dbapi.exceptions import ProgrammingError
with self.assertRaises(ProgrammingError):
parse_insert("bad-sql", None)
cases = [
(
"INSERT INTO django_migrations (app, name, applied) VALUES (%s, %s, %s)",
[1, 2, 3, 4, 5, 6],
{
"homogenous": True,
"table": "django_migrations",
"columns": ["app", "name", "applied"],
"values": [(1, 2, 3), (4, 5, 6)],
},
),
(
"INSERT INTO django_migrations(app, name, applied) VALUES (%s, %s, %s)",
[1, 2, 3, 4, 5, 6],
{
"homogenous": True,
"table": "django_migrations",
"columns": ["app", "name", "applied"],
"values": [(1, 2, 3), (4, 5, 6)],
},
),
(
"INSERT INTO sales.addresses (street, city, state, zip_code) "
"SELECT street, city, state, zip_code FROM sales.customers"
"ORDER BY first_name, last_name",
None,
{
"sql_params_list": [
(
"INSERT INTO sales.addresses (street, city, state, zip_code) "
"SELECT street, city, state, zip_code FROM sales.customers"
"ORDER BY first_name, last_name",
None,
)
]
},
),
(
"INSERT INTO ap (n, ct, cn) "
"VALUES (%s, %s, %s), (%s, %s, %s), (%s, %s, %s),(%s,%s, %s)",
(1, 2, 3, 4, 5, 6, 7, 8, 9),
{
"homogenous": True,
"table": "ap",
"columns": ["n", "ct", "cn"],
"values": [(1, 2, 3), (4, 5, 6), (7, 8, 9)],
},
),
(
"INSERT INTO `no` (`yes`) VALUES (%s)",
(1, 4, 5),
{
"homogenous": True,
"table": "`no`",
"columns": ["yes"],
"values": [(1,), (4,), (5,)],
},
),
(
"INSERT INTO T (f1, f2) VALUES (1, 2)",
None,
{"sql_params_list": [("INSERT INTO T (f1, f2) VALUES (1, 2)", None)]},
),
(
"INSERT INTO `no` (`yes`, tiff) VALUES (%s, LOWER(%s)), (%s, %s), (%s, %s)",
(1, "FOO", 5, 10, 11, 29),
{
"sql_params_list": [
(
"INSERT INTO `no` (`yes`, tiff) VALUES(%s, LOWER(%s))",
(1, "FOO"),
),
("INSERT INTO `no` (`yes`, tiff) VALUES(%s, %s)", (5, 10)),
("INSERT INTO `no` (`yes`, tiff) VALUES(%s, %s)", (11, 29)),
]
},
),
]
sql = "INSERT INTO django_migrations (app, name, applied) VALUES (%s, %s, %s)"
with self.assertRaises(ProgrammingError):
parse_insert(sql, None)
for sql, params, want in cases:
with self.subTest(sql=sql):
got = parse_insert(sql, params)
self.assertEqual(got, want, "Mismatch with parse_insert of `%s`" % sql)
@unittest.skipIf(skip_condition, skip_message)
def test_parse_insert_invalid(self):
from google.cloud.spanner_dbapi import exceptions
from google.cloud.spanner_dbapi.parse_utils import parse_insert
cases = [
(
"INSERT INTO django_migrations (app, name, applied) VALUES (%s, %s, %s), (%s, %s, %s)",
[1, 2, 3, 4, 5, 6, 7],
"len\\(params\\)=7 MUST be a multiple of len\\(pyformat_args\\)=3",
),
(
"INSERT INTO django_migrations (app, name, applied) VALUES (%s, %s, %s), (%s, %s, LOWER(%s))",
[1, 2, 3, 4, 5, 6, 7],
"Invalid length: VALUES\\(...\\) len: 6 != len\\(params\\): 7",
),
(
"INSERT INTO django_migrations (app, name, applied) VALUES (%s, %s, %s), (%s, %s, LOWER(%s)))",
[1, 2, 3, 4, 5, 6],
"VALUES: expected `,` got \\) in \\)",
),
]
for sql, params, wantException in cases:
with self.subTest(sql=sql):
self.assertRaisesRegex(
exceptions.ProgrammingError,
wantException,
lambda: parse_insert(sql, params),
)
@unittest.skipIf(skip_condition, skip_message)
def test_rows_for_insert_or_update(self):
from google.cloud.spanner_dbapi.parse_utils import rows_for_insert_or_update
from google.cloud.spanner_dbapi.exceptions import Error
with self.assertRaises(Error):
rows_for_insert_or_update([0], [[]])
with self.assertRaises(Error):
rows_for_insert_or_update([0], None, ["0", "%s"])
cases = [
(
["id", "app", "name"],
[(5, "ap", "n"), (6, "bp", "m")],
None,
[(5, "ap", "n"), (6, "bp", "m")],
),
(
["app", "name"],
[("ap", "n"), ("bp", "m")],
None,
[("ap", "n"), ("bp", "m")],
),
(
["app", "name", "fn"],
["ap", "n", "f1", "bp", "m", "f2", "cp", "o", "f3"],
["(%s, %s, %s)", "(%s, %s, %s)", "(%s, %s, %s)"],
[("ap", "n", "f1"), ("bp", "m", "f2"), ("cp", "o", "f3")],
),
(
["app", "name", "fn", "ln"],
[
("ap", "n", (45, "nested"), "ll"),
("bp", "m", "f2", "mt"),
("fp", "cp", "o", "f3"),
],
None,
[
("ap", "n", (45, "nested"), "ll"),
("bp", "m", "f2", "mt"),
("fp", "cp", "o", "f3"),
],
),
(["app", "name", "fn"], ["ap", "n", "f1"], None, [("ap", "n", "f1")]),
]
for i, (columns, params, pyformat_args, want) in enumerate(cases):
with self.subTest(i=i):
got = rows_for_insert_or_update(columns, params, pyformat_args)
self.assertEqual(got, want)
@unittest.skipIf(skip_condition, skip_message)
def test_sql_pyformat_args_to_spanner(self):
import decimal
from google.cloud.spanner_dbapi.parse_utils import sql_pyformat_args_to_spanner
cases = [
(
(
"SELECT * from t WHERE f1=%s, f2 = %s, f3=%s",
(10, "abc", "y**$22l3f"),
),
(
"SELECT * from t WHERE f1=@a0, f2 = @a1, f3=@a2",
{"a0": 10, "a1": "abc", "a2": "y**$22l3f"},
),
),
(
(
"INSERT INTO t (f1, f2, f2) VALUES (%s, %s, %s)",
("app", "name", "applied"),
),
(
"INSERT INTO t (f1, f2, f2) VALUES (@a0, @a1, @a2)",
{"a0": "app", "a1": "name", "a2": "applied"},
),
),
(
(
"INSERT INTO t (f1, f2, f2) VALUES (%(f1)s, %(f2)s, %(f3)s)",
{"f1": "app", "f2": "name", "f3": "applied"},
),
(
"INSERT INTO t (f1, f2, f2) VALUES (@a0, @a1, @a2)",
{"a0": "app", "a1": "name", "a2": "applied"},
),
),
(
# Intentionally using a dict with more keys than will be resolved.
("SELECT * from t WHERE f1=%(f1)s", {"f1": "app", "f2": "name"}),
("SELECT * from t WHERE f1=@a0", {"a0": "app"}),
),
(
# No args to replace, we MUST return the original params dict
# since it might be useful to pass to the next user.
("SELECT * from t WHERE id=10", {"f1": "app", "f2": "name"}),
("SELECT * from t WHERE id=10", {"f1": "app", "f2": "name"}),
),
(
(
"SELECT (an.p + %s) AS np FROM an WHERE (an.p + %s) = %s",
(1, 1.0, decimal.Decimal("31")),
),
(
"SELECT (an.p + @a0) AS np FROM an WHERE (an.p + @a1) = @a2",
{"a0": 1, "a1": 1.0, "a2": str(31)},
),
),
]
for ((sql_in, params), sql_want) in cases:
with self.subTest(sql=sql_in):
got_sql, got_named_args = sql_pyformat_args_to_spanner(sql_in, params)
want_sql, want_named_args = sql_want
self.assertEqual(got_sql, want_sql, "SQL does not match")
self.assertEqual(
got_named_args, want_named_args, "Named args do not match"
)
@unittest.skipIf(skip_condition, skip_message)
def test_sql_pyformat_args_to_spanner_invalid(self):
from google.cloud.spanner_dbapi import exceptions
from google.cloud.spanner_dbapi.parse_utils import sql_pyformat_args_to_spanner
cases = [
(
"SELECT * from t WHERE f1=%s, f2 = %s, f3=%s, extra=%s",
(10, "abc", "y**$22l3f"),
)
]
for sql, params in cases:
with self.subTest(sql=sql):
self.assertRaisesRegex(
exceptions.Error,
"pyformat_args mismatch",
lambda: sql_pyformat_args_to_spanner(sql, params),
)
def test_cast_for_spanner(self):
import decimal
from google.cloud.spanner_dbapi.parse_utils import cast_for_spanner
dec = 3
value = decimal.Decimal(dec)
self.assertEqual(cast_for_spanner(value), str(dec))
self.assertEqual(cast_for_spanner(5), 5)
self.assertEqual(cast_for_spanner("string"), "string")
@unittest.skipIf(skip_condition, skip_message)
def test_get_param_types(self):
import datetime
from google.cloud.spanner_dbapi.parse_utils import DateStr
from google.cloud.spanner_dbapi.parse_utils import TimestampStr
from google.cloud.spanner_dbapi.parse_utils import get_param_types
params = {
"a1": 10,
"b1": "string",
"c1": 10.39,
"d1": TimestampStr("2005-08-30T01:01:01.000001Z"),
"e1": DateStr("2019-12-05"),
"f1": True,
"g1": datetime.datetime(2011, 9, 1, 13, 20, 30),
"h1": datetime.date(2011, 9, 1),
"i1": b"bytes",
"j1": None,
}
want_types = {
"a1": param_types.INT64,
"b1": param_types.STRING,
"c1": param_types.FLOAT64,
"d1": param_types.TIMESTAMP,
"e1": param_types.DATE,
"f1": param_types.BOOL,
"g1": param_types.TIMESTAMP,
"h1": param_types.DATE,
"i1": param_types.BYTES,
}
got_types = get_param_types(params)
self.assertEqual(got_types, want_types)
def test_get_param_types_none(self):
from google.cloud.spanner_dbapi.parse_utils import get_param_types
self.assertEqual(get_param_types(None), None)
@unittest.skipIf(skip_condition, skip_message)
def test_ensure_where_clause(self):
from google.cloud.spanner_dbapi.parse_utils import ensure_where_clause
cases = (
"UPDATE a SET a.b=10 FROM articles a JOIN d c ON a.ai = c.ai WHERE c.ci = 1",
"UPDATE T SET A = 1 WHERE C1 = 1 AND C2 = 2",
"UPDATE T SET r=r*0.9 WHERE id IN (SELECT id FROM items WHERE r / w >= 1.3 AND q > 100)",
)
err_cases = (
"UPDATE (SELECT * FROM A JOIN c ON ai.id = c.id WHERE cl.ci = 1) SET d=5",
"DELETE * FROM TABLE",
)
for sql in cases:
with self.subTest(sql=sql):
ensure_where_clause(sql)
for sql in err_cases:
with self.subTest(sql=sql):
self.assertEqual(ensure_where_clause(sql), sql + " WHERE 1=1")
@unittest.skipIf(skip_condition, skip_message)
def test_escape_name(self):
from google.cloud.spanner_dbapi.parse_utils import escape_name
cases = (
("SELECT", "`SELECT`"),
("dashed-value", "`dashed-value`"),
("with space", "`with space`"),
("name", "name"),
("", ""),
)
for name, want in cases:
with self.subTest(name=name):
got = escape_name(name)
self.assertEqual(got, want)