Debian Codegraph @

Lib/test/test_sqlite3/test_userfunctions.py

raw
1 # pysqlite2/test/userfunctions.py: tests for user-defined functions and
2 # aggregates.
3 #
4 # Copyright (C) 2005-2007 Gerhard Häring <[email protected]>
5 #
6 # This file is part of pysqlite.
7 #
8 # This software is provided 'as-is', without any express or implied
9 # warranty. In no event will the authors be held liable for any damages
10 # arising from the use of this software.
11 #
12 # Permission is granted to anyone to use this software for any purpose,
13 # including commercial applications, and to alter it and redistribute it
14 # freely, subject to the following restrictions:
15 #
16 # 1. The origin of this software must not be misrepresented; you must not
17 # claim that you wrote the original software. If you use this software
18 # in a product, an acknowledgment in the product documentation would be
19 # appreciated but is not required.
20 # 2. Altered source versions must be plainly marked as such, and must not be
21 # misrepresented as being the original software.
22 # 3. This notice may not be removed or altered from any source distribution.
23
24 import sys
25 import unittest
26 import sqlite3 as sqlite
27
28 from unittest.mock import Mock, patch
29 from test.support import bigmemtest, gc_collect
30
31 from .util import cx_limit, memory_database
32 from .util import with_tracebacks
33
34
35 def func_returntext():
36 return "foo"
37 def func_returntextwithnull():
38 return "1\x002"
39 def func_returnunicode():
40 return "bar"
41 def func_returnint():
42 return 42
43 def func_returnfloat():
44 return 3.14
45 def func_returnnull():
46 return None
47 def func_returnblob():
48 return b"blob"
49 def func_returnlonglong():
50 return 1<<31
51 def func_raiseexception():
52 5/0
53 def func_memoryerror():
54 raise MemoryError
55 def func_overflowerror():
56 raise OverflowError
57
58 class AggrNoStep:
59 def __init__(self):
60 pass
61
62 def finalize(self):
63 return 1
64
65 class AggrNoFinalize:
66 def __init__(self):
67 pass
68
69 def step(self, x):
70 pass
71
72 class AggrExceptionInInit:
73 def __init__(self):
74 5/0
75
76 def step(self, x):
77 pass
78
79 def finalize(self):
80 pass
81
82 class AggrExceptionInStep:
83 def __init__(self):
84 pass
85
86 def step(self, x):
87 5/0
88
89 def finalize(self):
90 return 42
91
92 class AggrExceptionInFinalize:
93 def __init__(self):
94 pass
95
96 def step(self, x):
97 pass
98
99 def finalize(self):
100 5/0
101
102 class AggrCheckType:
103 def __init__(self):
104 self.val = None
105
106 def step(self, whichType, val):
107 theType = {"str": str, "int": int, "float": float, "None": type(None),
108 "blob": bytes}
109 self.val = int(theType[whichType] is type(val))
110
111 def finalize(self):
112 return self.val
113
114 class AggrCheckTypes:
115 def __init__(self):
116 self.val = 0
117
118 def step(self, whichType, *vals):
119 theType = {"str": str, "int": int, "float": float, "None": type(None),
120 "blob": bytes}
121 for val in vals:
122 self.val += int(theType[whichType] is type(val))
123
124 def finalize(self):
125 return self.val
126
127 class AggrSum:
128 def __init__(self):
129 self.val = 0.0
130
131 def step(self, val):
132 self.val += val
133
134 def finalize(self):
135 return self.val
136
137 class AggrText:
138 def __init__(self):
139 self.txt = ""
140 def step(self, txt):
141 self.txt = self.txt + txt
142 def finalize(self):
143 return self.txt
144
145
146 class FunctionTests(unittest.TestCase):
147 def setUp(self):
148 self.con = sqlite.connect(":memory:")
149
150 self.con.create_function("returntext", 0, func_returntext)
151 self.con.create_function("returntextwithnull", 0, func_returntextwithnull)
152 self.con.create_function("returnunicode", 0, func_returnunicode)
153 self.con.create_function("returnint", 0, func_returnint)
154 self.con.create_function("returnfloat", 0, func_returnfloat)
155 self.con.create_function("returnnull", 0, func_returnnull)
156 self.con.create_function("returnblob", 0, func_returnblob)
157 self.con.create_function("returnlonglong", 0, func_returnlonglong)
158 self.con.create_function("returnnan", 0, lambda: float("nan"))
159 self.con.create_function("return_noncont_blob", 0,
160 lambda: memoryview(b"blob")[::2])
161 self.con.create_function("raiseexception", 0, func_raiseexception)
162 self.con.create_function("memoryerror", 0, func_memoryerror)
163 self.con.create_function("overflowerror", 0, func_overflowerror)
164
165 self.con.create_function("isblob", 1, lambda x: isinstance(x, bytes))
166 self.con.create_function("isnone", 1, lambda x: x is None)
167 self.con.create_function("spam", -1, lambda *x: len(x))
168 self.con.execute("create table test(t text)")
169
170 def tearDown(self):
171 self.con.close()
172
173 def test_func_error_on_create(self):
174 with self.assertRaisesRegex(sqlite.ProgrammingError, "not -100"):
175 self.con.create_function("bla", -100, lambda x: 2*x)
176
177 def test_func_too_many_args(self):
178 category = sqlite.SQLITE_LIMIT_FUNCTION_ARG
179 msg = "too many arguments on function"
180 with cx_limit(self.con, category=category, limit=1):
181 self.con.execute("select abs(-1)");
182 with self.assertRaisesRegex(sqlite.OperationalError, msg):
183 self.con.execute("select max(1, 2)");
184
185 def test_func_ref_count(self):
186 def getfunc():
187 def f():
188 return 1
189 return f
190 f = getfunc()
191 globals()["foo"] = f
192 # self.con.create_function("reftest", 0, getfunc())
193 self.con.create_function("reftest", 0, f)
194 cur = self.con.cursor()
195 cur.execute("select reftest()")
196
197 def test_func_return_text(self):
198 cur = self.con.cursor()
199 cur.execute("select returntext()")
200 val = cur.fetchone()[0]
201 self.assertEqual(type(val), str)
202 self.assertEqual(val, "foo")
203
204 def test_func_return_text_with_null_char(self):
205 cur = self.con.cursor()
206 res = cur.execute("select returntextwithnull()").fetchone()[0]
207 self.assertEqual(type(res), str)
208 self.assertEqual(res, "1\x002")
209
210 def test_func_return_unicode(self):
211 cur = self.con.cursor()
212 cur.execute("select returnunicode()")
213 val = cur.fetchone()[0]
214 self.assertEqual(type(val), str)
215 self.assertEqual(val, "bar")
216
217 def test_func_return_int(self):
218 cur = self.con.cursor()
219 cur.execute("select returnint()")
220 val = cur.fetchone()[0]
221 self.assertEqual(type(val), int)
222 self.assertEqual(val, 42)
223
224 def test_func_return_float(self):
225 cur = self.con.cursor()
226 cur.execute("select returnfloat()")
227 val = cur.fetchone()[0]
228 self.assertEqual(type(val), float)
229 if val < 3.139 or val > 3.141:
230 self.fail("wrong value")
231
232 def test_func_return_null(self):
233 cur = self.con.cursor()
234 cur.execute("select returnnull()")
235 val = cur.fetchone()[0]
236 self.assertEqual(type(val), type(None))
237 self.assertEqual(val, None)
238
239 def test_func_return_blob(self):
240 cur = self.con.cursor()
241 cur.execute("select returnblob()")
242 val = cur.fetchone()[0]
243 self.assertEqual(type(val), bytes)
244 self.assertEqual(val, b"blob")
245
246 def test_func_return_long_long(self):
247 cur = self.con.cursor()
248 cur.execute("select returnlonglong()")
249 val = cur.fetchone()[0]
250 self.assertEqual(val, 1<<31)
251
252 def test_func_return_nan(self):
253 cur = self.con.cursor()
254 cur.execute("select returnnan()")
255 self.assertIsNone(cur.fetchone()[0])
256
257 @with_tracebacks(ZeroDivisionError, msg_regex="func_raiseexception")
258 def test_func_exception(self):
259 cur = self.con.cursor()
260 with self.assertRaises(sqlite.OperationalError) as cm:
261 cur.execute("select raiseexception()")
262 cur.fetchone()
263 self.assertEqual(str(cm.exception), 'user-defined function raised exception')
264
265 @with_tracebacks(MemoryError, msg_regex="func_memoryerror")
266 def test_func_memory_error(self):
267 cur = self.con.cursor()
268 with self.assertRaises(MemoryError):
269 cur.execute("select memoryerror()")
270 cur.fetchone()
271
272 @with_tracebacks(OverflowError, msg_regex="func_overflowerror")
273 def test_func_overflow_error(self):
274 cur = self.con.cursor()
275 with self.assertRaises(sqlite.DataError):
276 cur.execute("select overflowerror()")
277 cur.fetchone()
278
279 def test_any_arguments(self):
280 cur = self.con.cursor()
281 cur.execute("select spam(?, ?)", (1, 2))
282 val = cur.fetchone()[0]
283 self.assertEqual(val, 2)
284
285 def test_empty_blob(self):
286 cur = self.con.execute("select isblob(x'')")
287 self.assertTrue(cur.fetchone()[0])
288
289 def test_nan_float(self):
290 cur = self.con.execute("select isnone(?)", (float("nan"),))
291 # SQLite has no concept of nan; it is converted to NULL
292 self.assertTrue(cur.fetchone()[0])
293
294 def test_too_large_int(self):
295 err = "Python int too large to convert to SQLite INTEGER"
296 self.assertRaisesRegex(OverflowError, err, self.con.execute,
297 "select spam(?)", (1 << 65,))
298
299 def test_non_contiguous_blob(self):
300 self.assertRaisesRegex(BufferError,
301 "underlying buffer is not C-contiguous",
302 self.con.execute, "select spam(?)",
303 (memoryview(b"blob")[::2],))
304
305 @with_tracebacks(BufferError, regex="buffer.*contiguous")
306 def test_return_non_contiguous_blob(self):
307 with self.assertRaises(sqlite.OperationalError):
308 cur = self.con.execute("select return_noncont_blob()")
309 cur.fetchone()
310
311 def test_param_surrogates(self):
312 self.assertRaisesRegex(UnicodeEncodeError, "surrogates not allowed",
313 self.con.execute, "select spam(?)",
314 ("\ud803\ude6d",))
315
316 def test_func_params(self):
317 results = []
318 def append_result(arg):
319 results.append((arg, type(arg)))
320 self.con.create_function("test_params", 1, append_result)
321
322 dataset = [
323 (42, int),
324 (-1, int),
325 (1234567890123456789, int),
326 (4611686018427387905, int), # 63-bit int with non-zero low bits
327 (3.14, float),
328 (float('inf'), float),
329 ("text", str),
330 ("1\x002", str),
331 ("\u02e2q\u02e1\u2071\u1d57\u1d49", str),
332 (b"blob", bytes),
333 (bytearray(range(2)), bytes),
334 (memoryview(b"blob"), bytes),
335 (None, type(None)),
336 ]
337 for val, _ in dataset:
338 cur = self.con.execute("select test_params(?)", (val,))
339 cur.fetchone()
340 self.assertEqual(dataset, results)
341
342 # Regarding deterministic functions:
343 #
344 # Between 3.8.3 and 3.15.0, deterministic functions were only used to
345 # optimize inner loops. From 3.15.0 and onward, deterministic functions
346 # were permitted in WHERE clauses of partial indices, which allows testing
347 # based on syntax, iso. the query optimizer.
348 def test_func_non_deterministic(self):
349 mock = Mock(return_value=None)
350 self.con.create_function("nondeterministic", 0, mock, deterministic=False)
351 with self.assertRaises(sqlite.OperationalError):
352 self.con.execute("create index t on test(t) where nondeterministic() is not null")
353
354 def test_func_deterministic(self):
355 mock = Mock(return_value=None)
356 self.con.create_function("deterministic", 0, mock, deterministic=True)
357 try:
358 self.con.execute("create index t on test(t) where deterministic() is not null")
359 except sqlite.OperationalError:
360 self.fail("Unexpected failure while creating partial index")
361
362 def test_func_deterministic_keyword_only(self):
363 with self.assertRaises(TypeError):
364 self.con.create_function("deterministic", 0, int, True)
365
366 def test_function_destructor_via_gc(self):
367 # See bpo-44304: The destructor of the user function can
368 # crash if is called without the GIL from the gc functions
369 def md5sum(t):
370 return
371
372 with memory_database() as dest:
373 dest.create_function("md5", 1, md5sum)
374 x = dest("create table lang (name, first_appeared)")
375 del md5sum, dest
376
377 y = [x]
378 y.append(y)
379
380 del x,y
381 gc_collect()
382
383 @with_tracebacks(OverflowError)
384 def test_func_return_too_large_int(self):
385 cur = self.con.cursor()
386 msg = "string or blob too big"
387 for value in 2**63, -2**63-1, 2**64:
388 self.con.create_function("largeint", 0, lambda value=value: value)
389 with self.assertRaisesRegex(sqlite.DataError, msg):
390 cur.execute("select largeint()")
391
392 @with_tracebacks(UnicodeEncodeError, "surrogates not allowed")
393 def test_func_return_text_with_surrogates(self):
394 cur = self.con.cursor()
395 self.con.create_function("pychr", 1, chr)
396 for value in 0xd8ff, 0xdcff:
397 with self.assertRaises(sqlite.OperationalError):
398 cur.execute("select pychr(?)", (value,))
399
400 @unittest.skipUnless(sys.maxsize > 2**32, 'requires 64bit platform')
401 @bigmemtest(size=2**31, memuse=3, dry_run=False)
402 def test_func_return_too_large_text(self, size):
403 cur = self.con.cursor()
404 for size in 2**31-1, 2**31:
405 self.con.create_function("largetext", 0, lambda size=size: "b" * size)
406 with self.assertRaises(sqlite.DataError):
407 cur.execute("select largetext()")
408
409 @unittest.skipUnless(sys.maxsize > 2**32, 'requires 64bit platform')
410 @bigmemtest(size=2**31, memuse=2, dry_run=False)
411 def test_func_return_too_large_blob(self, size):
412 cur = self.con.cursor()
413 for size in 2**31-1, 2**31:
414 self.con.create_function("largeblob", 0, lambda size=size: b"b" * size)
415 with self.assertRaises(sqlite.DataError):
416 cur.execute("select largeblob()")
417
418 def test_func_return_illegal_value(self):
419 self.con.create_function("badreturn", 0, lambda: self)
420 msg = "user-defined function raised exception"
421 self.assertRaisesRegex(sqlite.OperationalError, msg,
422 self.con.execute, "select badreturn()")
423
424 def test_func_keyword_args(self):
425 regex = (
426 r"Passing keyword arguments 'name', 'narg' and 'func' to "
427 r"_sqlite3.Connection.create_function\(\) is deprecated. "
428 r"Parameters 'name', 'narg' and 'func' will become "
429 r"positional-only in Python 3.15."
430 )
431
432 def noop():
433 return None
434
435 with self.assertWarnsRegex(DeprecationWarning, regex) as cm:
436 self.con.create_function("noop", 0, func=noop)
437 self.assertEqual(cm.filename, __file__)
438
439 with self.assertWarnsRegex(DeprecationWarning, regex) as cm:
440 self.con.create_function("noop", narg=0, func=noop)
441 self.assertEqual(cm.filename, __file__)
442
443 with self.assertWarnsRegex(DeprecationWarning, regex) as cm:
444 self.con.create_function(name="noop", narg=0, func=noop)
445 self.assertEqual(cm.filename, __file__)
446
447
448 class WindowSumInt:
449 def __init__(self):
450 self.count = 0
451
452 def step(self, value):
453 self.count += value
454
455 def value(self):
456 return self.count
457
458 def inverse(self, value):
459 self.count -= value
460
461 def finalize(self):
462 return self.count
463
464 class BadWindow(Exception):
465 pass
466
467
468 @unittest.skipIf(sqlite.sqlite_version_info < (3, 25, 0),
469 "Requires SQLite 3.25.0 or newer")
470 class WindowFunctionTests(unittest.TestCase):
471 def setUp(self):
472 self.con = sqlite.connect(":memory:")
473 self.cur = self.con.cursor()
474
475 # Test case taken from https://www.sqlite.org/windowfunctions.html#udfwinfunc
476 values = [
477 ("a", 4),
478 ("b", 5),
479 ("c", 3),
480 ("d", 8),
481 ("e", 1),
482 ]
483 with self.con:
484 self.con.execute("create table test(x, y)")
485 self.con.executemany("insert into test values(?, ?)", values)
486 self.expected = [
487 ("a", 9),
488 ("b", 12),
489 ("c", 16),
490 ("d", 12),
491 ("e", 9),
492 ]
493 self.query = """
494 select x, %s(y) over (
495 order by x rows between 1 preceding and 1 following
496 ) as sum_y
497 from test order by x
498 """
499 self.con.create_window_function("sumint", 1, WindowSumInt)
500
501 def tearDown(self):
502 self.cur.close()
503 self.con.close()
504
505 def test_win_sum_int(self):
506 self.cur.execute(self.query % "sumint")
507 self.assertEqual(self.cur.fetchall(), self.expected)
508
509 def test_win_error_on_create(self):
510 with self.assertRaisesRegex(sqlite.ProgrammingError, "not -100"):
511 self.con.create_window_function("shouldfail", -100, WindowSumInt)
512
513 @with_tracebacks(BadWindow)
514 def test_win_exception_in_method(self):
515 for meth in "__init__", "step", "value", "inverse":
516 with self.subTest(meth=meth):
517 with patch.object(WindowSumInt, meth, side_effect=BadWindow):
518 name = f"exc_{meth}"
519 self.con.create_window_function(name, 1, WindowSumInt)
520 msg = f"'{meth}' method raised error"
521 with self.assertRaisesRegex(sqlite.OperationalError, msg):
522 self.cur.execute(self.query % name)
523 self.cur.fetchall()
524
525 @with_tracebacks(BadWindow)
526 def test_win_exception_in_finalize(self):
527 # Note: SQLite does not (as of version 3.38.0) propagate finalize
528 # callback errors to sqlite3_step(); this implies that OperationalError
529 # is _not_ raised.
530 with patch.object(WindowSumInt, "finalize", side_effect=BadWindow):
531 name = "exception_in_finalize"
532 self.con.create_window_function(name, 1, WindowSumInt)
533 self.cur.execute(self.query % name)
534 self.cur.fetchall()
535
536 @with_tracebacks(AttributeError)
537 def test_win_missing_method(self):
538 class MissingValue:
539 def step(self, x): pass
540 def inverse(self, x): pass
541 def finalize(self): return 42
542
543 class MissingInverse:
544 def step(self, x): pass
545 def value(self): return 42
546 def finalize(self): return 42
547
548 class MissingStep:
549 def value(self): return 42
550 def inverse(self, x): pass
551 def finalize(self): return 42
552
553 dataset = (
554 ("step", MissingStep),
555 ("value", MissingValue),
556 ("inverse", MissingInverse),
557 )
558 for meth, cls in dataset:
559 with self.subTest(meth=meth, cls=cls):
560 name = f"exc_{meth}"
561 self.con.create_window_function(name, 1, cls)
562 with self.assertRaisesRegex(sqlite.OperationalError,
563 f"'{meth}' method not defined"):
564 self.cur.execute(self.query % name)
565 self.cur.fetchall()
566
567 @with_tracebacks(AttributeError)
568 def test_win_missing_finalize(self):
569 # Note: SQLite does not (as of version 3.38.0) propagate finalize
570 # callback errors to sqlite3_step(); this implies that OperationalError
571 # is _not_ raised.
572 class MissingFinalize:
573 def step(self, x): pass
574 def value(self): return 42
575 def inverse(self, x): pass
576
577 name = "missing_finalize"
578 self.con.create_window_function(name, 1, MissingFinalize)
579 self.cur.execute(self.query % name)
580 self.cur.fetchall()
581
582 def test_win_clear_function(self):
583 self.con.create_window_function("sumint", 1, None)
584 self.assertRaises(sqlite.OperationalError, self.cur.execute,
585 self.query % "sumint")
586
587 def test_win_redefine_function(self):
588 # Redefine WindowSumInt; adjust the expected results accordingly.
589 class Redefined(WindowSumInt):
590 def step(self, value): self.count += value * 2
591 def inverse(self, value): self.count -= value * 2
592 expected = [(v[0], v[1]*2) for v in self.expected]
593
594 self.con.create_window_function("sumint", 1, Redefined)
595 self.cur.execute(self.query % "sumint")
596 self.assertEqual(self.cur.fetchall(), expected)
597
598 def test_win_error_value_return(self):
599 class ErrorValueReturn:
600 def __init__(self): pass
601 def step(self, x): pass
602 def value(self): return 1 << 65
603
604 self.con.create_window_function("err_val_ret", 1, ErrorValueReturn)
605 self.assertRaisesRegex(sqlite.DataError, "string or blob too big",
606 self.cur.execute, self.query % "err_val_ret")
607
608
609 class AggregateTests(unittest.TestCase):
610 def setUp(self):
611 self.con = sqlite.connect(":memory:")
612 cur = self.con.cursor()
613 cur.execute("""
614 create table test(
615 t text,
616 i integer,
617 f float,
618 n,
619 b blob
620 )
621 """)
622 cur.execute("insert into test(t, i, f, n, b) values (?, ?, ?, ?, ?)",
623 ("foo", 5, 3.14, None, memoryview(b"blob"),))
624 cur.close()
625
626 self.con.create_aggregate("nostep", 1, AggrNoStep)
627 self.con.create_aggregate("nofinalize", 1, AggrNoFinalize)
628 self.con.create_aggregate("excInit", 1, AggrExceptionInInit)
629 self.con.create_aggregate("excStep", 1, AggrExceptionInStep)
630 self.con.create_aggregate("excFinalize", 1, AggrExceptionInFinalize)
631 self.con.create_aggregate("checkType", 2, AggrCheckType)
632 self.con.create_aggregate("checkTypes", -1, AggrCheckTypes)
633 self.con.create_aggregate("mysum", 1, AggrSum)
634 self.con.create_aggregate("aggtxt", 1, AggrText)
635
636 def tearDown(self):
637 self.con.close()
638
639 def test_aggr_error_on_create(self):
640 with self.assertRaisesRegex(sqlite.ProgrammingError, "not -100"):
641 self.con.create_function("bla", -100, AggrSum)
642
643 @with_tracebacks(AttributeError, msg_regex="AggrNoStep")
644 def test_aggr_no_step(self):
645 cur = self.con.cursor()
646 with self.assertRaises(sqlite.OperationalError) as cm:
647 cur.execute("select nostep(t) from test")
648 self.assertEqual(str(cm.exception),
649 "user-defined aggregate's 'step' method not defined")
650
651 def test_aggr_no_finalize(self):
652 cur = self.con.cursor()
653 msg = "user-defined aggregate's 'finalize' method not defined"
654 with self.assertRaisesRegex(sqlite.OperationalError, msg):
655 cur.execute("select nofinalize(t) from test")
656 val = cur.fetchone()[0]
657
658 @with_tracebacks(ZeroDivisionError, msg_regex="AggrExceptionInInit")
659 def test_aggr_exception_in_init(self):
660 cur = self.con.cursor()
661 with self.assertRaises(sqlite.OperationalError) as cm:
662 cur.execute("select excInit(t) from test")
663 val = cur.fetchone()[0]
664 self.assertEqual(str(cm.exception), "user-defined aggregate's '__init__' method raised error")
665
666 @with_tracebacks(ZeroDivisionError, msg_regex="AggrExceptionInStep")
667 def test_aggr_exception_in_step(self):
668 cur = self.con.cursor()
669 with self.assertRaises(sqlite.OperationalError) as cm:
670 cur.execute("select excStep(t) from test")
671 val = cur.fetchone()[0]
672 self.assertEqual(str(cm.exception), "user-defined aggregate's 'step' method raised error")
673
674 @with_tracebacks(ZeroDivisionError, msg_regex="AggrExceptionInFinalize")
675 def test_aggr_exception_in_finalize(self):
676 cur = self.con.cursor()
677 with self.assertRaises(sqlite.OperationalError) as cm:
678 cur.execute("select excFinalize(t) from test")
679 val = cur.fetchone()[0]
680 self.assertEqual(str(cm.exception), "user-defined aggregate's 'finalize' method raised error")
681
682 def test_aggr_check_param_str(self):
683 cur = self.con.cursor()
684 cur.execute("select checkTypes('str', ?, ?)", ("foo", str()))
685 val = cur.fetchone()[0]
686 self.assertEqual(val, 2)
687
688 def test_aggr_check_param_int(self):
689 cur = self.con.cursor()
690 cur.execute("select checkType('int', ?)", (42,))
691 val = cur.fetchone()[0]
692 self.assertEqual(val, 1)
693
694 def test_aggr_check_params_int(self):
695 cur = self.con.cursor()
696 cur.execute("select checkTypes('int', ?, ?)", (42, 24))
697 val = cur.fetchone()[0]
698 self.assertEqual(val, 2)
699
700 def test_aggr_check_param_float(self):
701 cur = self.con.cursor()
702 cur.execute("select checkType('float', ?)", (3.14,))
703 val = cur.fetchone()[0]
704 self.assertEqual(val, 1)
705
706 def test_aggr_check_param_none(self):
707 cur = self.con.cursor()
708 cur.execute("select checkType('None', ?)", (None,))
709 val = cur.fetchone()[0]
710 self.assertEqual(val, 1)
711
712 def test_aggr_check_param_blob(self):
713 cur = self.con.cursor()
714 cur.execute("select checkType('blob', ?)", (memoryview(b"blob"),))
715 val = cur.fetchone()[0]
716 self.assertEqual(val, 1)
717
718 def test_aggr_check_aggr_sum(self):
719 cur = self.con.cursor()
720 cur.execute("delete from test")
721 cur.executemany("insert into test(i) values (?)", [(10,), (20,), (30,)])
722 cur.execute("select mysum(i) from test")
723 val = cur.fetchone()[0]
724 self.assertEqual(val, 60)
725
726 def test_aggr_no_match(self):
727 cur = self.con.execute("select mysum(i) from (select 1 as i) where i == 0")
728 val = cur.fetchone()[0]
729 self.assertIsNone(val)
730
731 def test_aggr_text(self):
732 cur = self.con.cursor()
733 for txt in ["foo", "1\x002"]:
734 with self.subTest(txt=txt):
735 cur.execute("select aggtxt(?) from test", (txt,))
736 val = cur.fetchone()[0]
737 self.assertEqual(val, txt)
738
739 def test_agg_keyword_args(self):
740 regex = (
741 r"Passing keyword arguments 'name', 'n_arg' and 'aggregate_class' to "
742 r"_sqlite3.Connection.create_aggregate\(\) is deprecated. "
743 r"Parameters 'name', 'n_arg' and 'aggregate_class' will become "
744 r"positional-only in Python 3.15."
745 )
746
747 with self.assertWarnsRegex(DeprecationWarning, regex) as cm:
748 self.con.create_aggregate("test", 1, aggregate_class=AggrText)
749 self.assertEqual(cm.filename, __file__)
750
751 with self.assertWarnsRegex(DeprecationWarning, regex) as cm:
752 self.con.create_aggregate("test", n_arg=1, aggregate_class=AggrText)
753 self.assertEqual(cm.filename, __file__)
754
755 with self.assertWarnsRegex(DeprecationWarning, regex) as cm:
756 self.con.create_aggregate(name="test", n_arg=0,
757 aggregate_class=AggrText)
758 self.assertEqual(cm.filename, __file__)
759
760
761 class AuthorizerTests(unittest.TestCase):
762 @staticmethod
763 def authorizer_cb(action, arg1, arg2, dbname, source):
764 if action != sqlite.SQLITE_SELECT:
765 return sqlite.SQLITE_DENY
766 if arg2 == 'c2' or arg1 == 't2':
767 return sqlite.SQLITE_DENY
768 return sqlite.SQLITE_OK
769
770 def setUp(self):
771 self.con = sqlite.connect(":memory:")
772 self.con.executescript("""
773 create table t1 (c1, c2);
774 create table t2 (c1, c2);
775 insert into t1 (c1, c2) values (1, 2);
776 insert into t2 (c1, c2) values (4, 5);
777 """)
778
779 # For our security test:
780 self.con.execute("select c2 from t2")
781
782 self.con.set_authorizer(self.authorizer_cb)
783
784 def tearDown(self):
785 self.con.close()
786
787 def test_table_access(self):
788 with self.assertRaises(sqlite.DatabaseError) as cm:
789 self.con.execute("select * from t2")
790 self.assertIn('prohibited', str(cm.exception))
791
792 def test_column_access(self):
793 with self.assertRaises(sqlite.DatabaseError) as cm:
794 self.con.execute("select c2 from t1")
795 self.assertIn('prohibited', str(cm.exception))
796
797 def test_clear_authorizer(self):
798 self.con.set_authorizer(None)
799 self.con.execute("select * from t2")
800 self.con.execute("select c2 from t1")
801
802 def test_authorizer_keyword_args(self):
803 regex = (
804 r"Passing keyword argument 'authorizer_callback' to "
805 r"_sqlite3.Connection.set_authorizer\(\) is deprecated. "
806 r"Parameter 'authorizer_callback' will become positional-only in "
807 r"Python 3.15."
808 )
809
810 with self.assertWarnsRegex(DeprecationWarning, regex) as cm:
811 self.con.set_authorizer(authorizer_callback=lambda: None)
812 self.assertEqual(cm.filename, __file__)
813
814
815 class AuthorizerRaiseExceptionTests(AuthorizerTests):
816 @staticmethod
817 def authorizer_cb(action, arg1, arg2, dbname, source):
818 if action != sqlite.SQLITE_SELECT:
819 raise ValueError
820 if arg2 == 'c2' or arg1 == 't2':
821 raise ValueError
822 return sqlite.SQLITE_OK
823
824 @with_tracebacks(ValueError, msg_regex="authorizer_cb")
825 def test_table_access(self):
826 super().test_table_access()
827
828 @with_tracebacks(ValueError, msg_regex="authorizer_cb")
829 def test_column_access(self):
830 super().test_table_access()
831
832 class AuthorizerIllegalTypeTests(AuthorizerTests):
833 @staticmethod
834 def authorizer_cb(action, arg1, arg2, dbname, source):
835 if action != sqlite.SQLITE_SELECT:
836 return 0.0
837 if arg2 == 'c2' or arg1 == 't2':
838 return 0.0
839 return sqlite.SQLITE_OK
840
841 class AuthorizerLargeIntegerTests(AuthorizerTests):
842 @staticmethod
843 def authorizer_cb(action, arg1, arg2, dbname, source):
844 if action != sqlite.SQLITE_SELECT:
845 return 2**32
846 if arg2 == 'c2' or arg1 == 't2':
847 return 2**32
848 return sqlite.SQLITE_OK
849
850
851 if __name__ == "__main__":
852 unittest.main()