| 1 |
# pysqlite2/test/hooks.py: tests for various SQLite-specific hooks |
| 2 |
# |
| 3 |
# Copyright (C) 2006-2007 Gerhard Häring <[email protected]> |
| 4 |
# |
| 5 |
# This file is part of pysqlite. |
| 6 |
# |
| 7 |
# This software is provided 'as-is', without any express or implied |
| 8 |
# warranty. In no event will the authors be held liable for any damages |
| 9 |
# arising from the use of this software. |
| 10 |
# |
| 11 |
# Permission is granted to anyone to use this software for any purpose, |
| 12 |
# including commercial applications, and to alter it and redistribute it |
| 13 |
# freely, subject to the following restrictions: |
| 14 |
# |
| 15 |
# 1. The origin of this software must not be misrepresented; you must not |
| 16 |
# claim that you wrote the original software. If you use this software |
| 17 |
# in a product, an acknowledgment in the product documentation would be |
| 18 |
# appreciated but is not required. |
| 19 |
# 2. Altered source versions must be plainly marked as such, and must not be |
| 20 |
# misrepresented as being the original software. |
| 21 |
# 3. This notice may not be removed or altered from any source distribution. |
| 22 |
|
| 23 |
import contextlib |
| 24 |
import sqlite3 as sqlite |
| 25 |
import unittest |
| 26 |
|
| 27 |
from test.support import import_helper |
| 28 |
from test.support.os_helper import TESTFN, unlink |
| 29 |
|
| 30 |
from .util import memory_database, cx_limit, with_tracebacks |
| 31 |
from .util import MemoryDatabaseMixin |
| 32 |
|
| 33 |
# TODO(picnixz): increase test coverage for other callbacks |
| 34 |
# such as 'func', 'step', 'finalize', and 'collation'. |
| 35 |
|
| 36 |
|
| 37 |
class CollationTests(MemoryDatabaseMixin, unittest.TestCase): |
| 38 |
|
| 39 |
def test_create_collation_not_string(self): |
| 40 |
with self.assertRaises(TypeError): |
| 41 |
self.con.create_collation(None, lambda x, y: (x > y) - (x < y)) |
| 42 |
|
| 43 |
def test_create_collation_not_callable(self): |
| 44 |
with self.assertRaises(TypeError) as cm: |
| 45 |
self.con.create_collation("X", 42) |
| 46 |
self.assertEqual(str(cm.exception), 'parameter must be callable') |
| 47 |
|
| 48 |
def test_create_collation_not_ascii(self): |
| 49 |
self.con.create_collation("collä", lambda x, y: (x > y) - (x < y)) |
| 50 |
|
| 51 |
def test_create_collation_bad_upper(self): |
| 52 |
class BadUpperStr(str): |
| 53 |
def upper(self): |
| 54 |
return None |
| 55 |
mycoll = lambda x, y: -((x > y) - (x < y)) |
| 56 |
self.con.create_collation(BadUpperStr("mycoll"), mycoll) |
| 57 |
result = self.con.execute(""" |
| 58 |
select x from ( |
| 59 |
select 'a' as x |
| 60 |
union |
| 61 |
select 'b' as x |
| 62 |
) order by x collate mycoll |
| 63 |
""").fetchall() |
| 64 |
self.assertEqual(result[0][0], 'b') |
| 65 |
self.assertEqual(result[1][0], 'a') |
| 66 |
|
| 67 |
def test_collation_is_used(self): |
| 68 |
def mycoll(x, y): |
| 69 |
# reverse order |
| 70 |
return -((x > y) - (x < y)) |
| 71 |
|
| 72 |
self.con.create_collation("mycoll", mycoll) |
| 73 |
sql = """ |
| 74 |
select x from ( |
| 75 |
select 'a' as x |
| 76 |
union |
| 77 |
select 'b' as x |
| 78 |
union |
| 79 |
select 'c' as x |
| 80 |
) order by x collate mycoll |
| 81 |
""" |
| 82 |
result = self.con.execute(sql).fetchall() |
| 83 |
self.assertEqual(result, [('c',), ('b',), ('a',)], |
| 84 |
msg='the expected order was not returned') |
| 85 |
|
| 86 |
self.con.create_collation("mycoll", None) |
| 87 |
with self.assertRaises(sqlite.OperationalError) as cm: |
| 88 |
result = self.con.execute(sql).fetchall() |
| 89 |
self.assertEqual(str(cm.exception), 'no such collation sequence: mycoll') |
| 90 |
|
| 91 |
def test_collation_returns_large_integer(self): |
| 92 |
def mycoll(x, y): |
| 93 |
# reverse order |
| 94 |
return -((x > y) - (x < y)) * 2**32 |
| 95 |
self.con.create_collation("mycoll", mycoll) |
| 96 |
sql = """ |
| 97 |
select x from ( |
| 98 |
select 'a' as x |
| 99 |
union |
| 100 |
select 'b' as x |
| 101 |
union |
| 102 |
select 'c' as x |
| 103 |
) order by x collate mycoll |
| 104 |
""" |
| 105 |
result = self.con.execute(sql).fetchall() |
| 106 |
self.assertEqual(result, [('c',), ('b',), ('a',)], |
| 107 |
msg="the expected order was not returned") |
| 108 |
|
| 109 |
def test_collation_register_twice(self): |
| 110 |
""" |
| 111 |
Register two different collation functions under the same name. |
| 112 |
Verify that the last one is actually used. |
| 113 |
""" |
| 114 |
con = self.con |
| 115 |
con.create_collation("mycoll", lambda x, y: (x > y) - (x < y)) |
| 116 |
con.create_collation("mycoll", lambda x, y: -((x > y) - (x < y))) |
| 117 |
result = con.execute(""" |
| 118 |
select x from (select 'a' as x union select 'b' as x) order by x collate mycoll |
| 119 |
""").fetchall() |
| 120 |
self.assertEqual(result[0][0], 'b') |
| 121 |
self.assertEqual(result[1][0], 'a') |
| 122 |
|
| 123 |
def test_collation_register_when_busy(self): |
| 124 |
# See https://github.com/python/cpython/issues/146090. |
| 125 |
con = self.con |
| 126 |
con.create_collation("mycoll", lambda x, y: (x > y) - (x < y)) |
| 127 |
con.execute("CREATE TABLE t(x TEXT)") |
| 128 |
con.execute("INSERT INTO t VALUES (?)", ("a",)) |
| 129 |
con.execute("INSERT INTO t VALUES (?)", ("b",)) |
| 130 |
con.commit() |
| 131 |
|
| 132 |
cursor = self.con.execute("SELECT x FROM t ORDER BY x COLLATE mycoll") |
| 133 |
next(cursor) |
| 134 |
# Replace the collation while the statement is active -> SQLITE_BUSY. |
| 135 |
with self.assertRaises(sqlite.OperationalError) as cm: |
| 136 |
self.con.create_collation("mycoll", lambda a, b: 0) |
| 137 |
|
| 138 |
def test_deregister_collation(self): |
| 139 |
""" |
| 140 |
Register a collation, then deregister it. Make sure an error is raised if we try |
| 141 |
to use it. |
| 142 |
""" |
| 143 |
con = self.con |
| 144 |
con.create_collation("mycoll", lambda x, y: (x > y) - (x < y)) |
| 145 |
con.create_collation("mycoll", None) |
| 146 |
with self.assertRaises(sqlite.OperationalError) as cm: |
| 147 |
con.execute("select 'a' as x union select 'b' as x order by x collate mycoll") |
| 148 |
self.assertEqual(str(cm.exception), 'no such collation sequence: mycoll') |
| 149 |
|
| 150 |
|
| 151 |
class AuthorizerTests(MemoryDatabaseMixin, unittest.TestCase): |
| 152 |
|
| 153 |
def assert_not_authorized(self, func, /, *args, **kwargs): |
| 154 |
with self.assertRaisesRegex(sqlite.DatabaseError, "not authorized"): |
| 155 |
func(*args, **kwargs) |
| 156 |
|
| 157 |
# When a handler has an invalid signature, the exception raised is |
| 158 |
# the same that would be raised if the handler "negatively" replied. |
| 159 |
|
| 160 |
def test_authorizer_invalid_signature(self): |
| 161 |
self.cx.execute("create table if not exists test(a number)") |
| 162 |
self.cx.set_authorizer(lambda: None) |
| 163 |
self.assert_not_authorized(self.cx.execute, "select * from test") |
| 164 |
|
| 165 |
# Tests for checking that callback context mutations do not crash. |
| 166 |
# Regression tests for https://github.com/python/cpython/issues/142830. |
| 167 |
|
| 168 |
@with_tracebacks(ZeroDivisionError, regex="hello world") |
| 169 |
def test_authorizer_concurrent_mutation_in_call(self): |
| 170 |
self.cx.execute("create table if not exists test(a number)") |
| 171 |
|
| 172 |
def handler(*a, **kw): |
| 173 |
self.cx.set_authorizer(None) |
| 174 |
raise ZeroDivisionError("hello world") |
| 175 |
|
| 176 |
self.cx.set_authorizer(handler) |
| 177 |
self.assert_not_authorized(self.cx.execute, "select * from test") |
| 178 |
|
| 179 |
@with_tracebacks(OverflowError) |
| 180 |
def test_authorizer_concurrent_mutation_with_overflown_value(self): |
| 181 |
_testcapi = import_helper.import_module("_testcapi") |
| 182 |
self.cx.execute("create table if not exists test(a number)") |
| 183 |
|
| 184 |
def handler(*a, **kw): |
| 185 |
self.cx.set_authorizer(None) |
| 186 |
# We expect 'int' at the C level, so this one will raise |
| 187 |
# when converting via PyLong_Int(). |
| 188 |
return _testcapi.INT_MAX + 1 |
| 189 |
|
| 190 |
self.cx.set_authorizer(handler) |
| 191 |
self.assert_not_authorized(self.cx.execute, "select * from test") |
| 192 |
|
| 193 |
|
| 194 |
class ProgressTests(MemoryDatabaseMixin, unittest.TestCase): |
| 195 |
|
| 196 |
def assert_interrupted(self, func, /, *args, **kwargs): |
| 197 |
with self.assertRaisesRegex(sqlite.OperationalError, "interrupted"): |
| 198 |
func(*args, **kwargs) |
| 199 |
|
| 200 |
def test_progress_handler_used(self): |
| 201 |
""" |
| 202 |
Test that the progress handler is invoked once it is set. |
| 203 |
""" |
| 204 |
progress_calls = [] |
| 205 |
def progress(): |
| 206 |
progress_calls.append(None) |
| 207 |
return 0 |
| 208 |
self.con.set_progress_handler(progress, 1) |
| 209 |
self.con.execute(""" |
| 210 |
create table foo(a, b) |
| 211 |
""") |
| 212 |
self.assertTrue(progress_calls) |
| 213 |
|
| 214 |
def test_opcode_count(self): |
| 215 |
""" |
| 216 |
Test that the opcode argument is respected. |
| 217 |
""" |
| 218 |
con = self.con |
| 219 |
progress_calls = [] |
| 220 |
def progress(): |
| 221 |
progress_calls.append(None) |
| 222 |
return 0 |
| 223 |
con.set_progress_handler(progress, 1) |
| 224 |
curs = con.cursor() |
| 225 |
curs.execute(""" |
| 226 |
create table foo (a, b) |
| 227 |
""") |
| 228 |
first_count = len(progress_calls) |
| 229 |
progress_calls = [] |
| 230 |
con.set_progress_handler(progress, 2) |
| 231 |
curs.execute(""" |
| 232 |
create table bar (a, b) |
| 233 |
""") |
| 234 |
second_count = len(progress_calls) |
| 235 |
self.assertGreaterEqual(first_count, second_count) |
| 236 |
|
| 237 |
def test_cancel_operation(self): |
| 238 |
""" |
| 239 |
Test that returning a non-zero value stops the operation in progress. |
| 240 |
""" |
| 241 |
def progress(): |
| 242 |
return 1 |
| 243 |
self.con.set_progress_handler(progress, 1) |
| 244 |
curs = self.con.cursor() |
| 245 |
self.assertRaises( |
| 246 |
sqlite.OperationalError, |
| 247 |
curs.execute, |
| 248 |
"create table bar (a, b)") |
| 249 |
|
| 250 |
def test_clear_handler(self): |
| 251 |
""" |
| 252 |
Test that setting the progress handler to None clears the previously set handler. |
| 253 |
""" |
| 254 |
con = self.con |
| 255 |
action = 0 |
| 256 |
def progress(): |
| 257 |
nonlocal action |
| 258 |
action = 1 |
| 259 |
return 0 |
| 260 |
con.set_progress_handler(progress, 1) |
| 261 |
con.set_progress_handler(None, 1) |
| 262 |
con.execute("select 1 union select 2 union select 3").fetchall() |
| 263 |
self.assertEqual(action, 0, "progress handler was not cleared") |
| 264 |
|
| 265 |
@with_tracebacks(ZeroDivisionError, msg_regex="bad_progress") |
| 266 |
def test_error_in_progress_handler(self): |
| 267 |
def bad_progress(): |
| 268 |
1 / 0 |
| 269 |
self.con.set_progress_handler(bad_progress, 1) |
| 270 |
with self.assertRaises(sqlite.OperationalError): |
| 271 |
self.con.execute(""" |
| 272 |
create table foo(a, b) |
| 273 |
""") |
| 274 |
|
| 275 |
@with_tracebacks(ZeroDivisionError, msg_regex="bad_progress") |
| 276 |
def test_error_in_progress_handler_result(self): |
| 277 |
class BadBool: |
| 278 |
def __bool__(self): |
| 279 |
1 / 0 |
| 280 |
def bad_progress(): |
| 281 |
return BadBool() |
| 282 |
self.con.set_progress_handler(bad_progress, 1) |
| 283 |
with self.assertRaises(sqlite.OperationalError): |
| 284 |
self.con.execute(""" |
| 285 |
create table foo(a, b) |
| 286 |
""") |
| 287 |
|
| 288 |
def test_set_progress_handler_keyword_args(self): |
| 289 |
regex = ( |
| 290 |
r"Passing keyword argument 'progress_handler' to " |
| 291 |
r"_sqlite3.Connection.set_progress_handler\(\) is deprecated. " |
| 292 |
r"Parameter 'progress_handler' will become positional-only in " |
| 293 |
r"Python 3.15." |
| 294 |
) |
| 295 |
|
| 296 |
with self.assertWarnsRegex(DeprecationWarning, regex) as cm: |
| 297 |
self.con.set_progress_handler(progress_handler=lambda: None, n=1) |
| 298 |
self.assertEqual(cm.filename, __file__) |
| 299 |
|
| 300 |
# When a handler has an invalid signature, the exception raised is |
| 301 |
# the same that would be raised if the handler "negatively" replied. |
| 302 |
|
| 303 |
def test_progress_handler_invalid_signature(self): |
| 304 |
self.cx.execute("create table if not exists test(a number)") |
| 305 |
self.cx.set_progress_handler(lambda x: None, 1) |
| 306 |
self.assert_interrupted(self.cx.execute, "select * from test") |
| 307 |
|
| 308 |
# Tests for checking that callback context mutations do not crash. |
| 309 |
# Regression tests for https://github.com/python/cpython/issues/142830. |
| 310 |
|
| 311 |
@with_tracebacks(ZeroDivisionError, regex="hello world") |
| 312 |
def test_progress_handler_concurrent_mutation_in_call(self): |
| 313 |
self.cx.execute("create table if not exists test(a number)") |
| 314 |
|
| 315 |
def handler(*a, **kw): |
| 316 |
self.cx.set_progress_handler(None, 1) |
| 317 |
raise ZeroDivisionError("hello world") |
| 318 |
|
| 319 |
self.cx.set_progress_handler(handler, 1) |
| 320 |
self.assert_interrupted(self.cx.execute, "select * from test") |
| 321 |
|
| 322 |
def test_progress_handler_concurrent_mutation_in_conversion(self): |
| 323 |
self.cx.execute("create table if not exists test(a number)") |
| 324 |
|
| 325 |
class Handler: |
| 326 |
def __bool__(_): |
| 327 |
# clear the progress handler |
| 328 |
self.cx.set_progress_handler(None, 1) |
| 329 |
raise ValueError # force PyObject_True() to fail |
| 330 |
|
| 331 |
self.cx.set_progress_handler(Handler.__init__, 1) |
| 332 |
self.assert_interrupted(self.cx.execute, "select * from test") |
| 333 |
|
| 334 |
# Running with tracebacks makes the second execution of this |
| 335 |
# function raise another exception because of a database change. |
| 336 |
|
| 337 |
|
| 338 |
class TraceCallbackTests(MemoryDatabaseMixin, unittest.TestCase): |
| 339 |
|
| 340 |
@contextlib.contextmanager |
| 341 |
def check_stmt_trace(self, cx, expected): |
| 342 |
try: |
| 343 |
traced = [] |
| 344 |
cx.set_trace_callback(lambda stmt: traced.append(stmt)) |
| 345 |
yield |
| 346 |
finally: |
| 347 |
self.assertEqual(traced, expected) |
| 348 |
cx.set_trace_callback(None) |
| 349 |
|
| 350 |
def test_trace_callback_used(self): |
| 351 |
""" |
| 352 |
Test that the trace callback is invoked once it is set. |
| 353 |
""" |
| 354 |
traced_statements = [] |
| 355 |
def trace(statement): |
| 356 |
traced_statements.append(statement) |
| 357 |
self.con.set_trace_callback(trace) |
| 358 |
self.con.execute("create table foo(a, b)") |
| 359 |
self.assertTrue(traced_statements) |
| 360 |
self.assertTrue(any("create table foo" in stmt for stmt in traced_statements)) |
| 361 |
|
| 362 |
def test_clear_trace_callback(self): |
| 363 |
""" |
| 364 |
Test that setting the trace callback to None clears the previously set callback. |
| 365 |
""" |
| 366 |
con = self.con |
| 367 |
traced_statements = [] |
| 368 |
def trace(statement): |
| 369 |
traced_statements.append(statement) |
| 370 |
con.set_trace_callback(trace) |
| 371 |
con.set_trace_callback(None) |
| 372 |
con.execute("create table foo(a, b)") |
| 373 |
self.assertFalse(traced_statements, "trace callback was not cleared") |
| 374 |
|
| 375 |
def test_unicode_content(self): |
| 376 |
""" |
| 377 |
Test that the statement can contain unicode literals. |
| 378 |
""" |
| 379 |
unicode_value = '\xf6\xe4\xfc\xd6\xc4\xdc\xdf\u20ac' |
| 380 |
con = self.con |
| 381 |
traced_statements = [] |
| 382 |
def trace(statement): |
| 383 |
traced_statements.append(statement) |
| 384 |
con.set_trace_callback(trace) |
| 385 |
con.execute("create table foo(x)") |
| 386 |
con.execute("insert into foo(x) values ('%s')" % unicode_value) |
| 387 |
con.commit() |
| 388 |
self.assertTrue(any(unicode_value in stmt for stmt in traced_statements), |
| 389 |
"Unicode data %s garbled in trace callback: %s" |
| 390 |
% (ascii(unicode_value), ', '.join(map(ascii, traced_statements)))) |
| 391 |
|
| 392 |
def test_trace_callback_content(self): |
| 393 |
# set_trace_callback() shouldn't produce duplicate content (bpo-26187) |
| 394 |
traced_statements = [] |
| 395 |
def trace(statement): |
| 396 |
traced_statements.append(statement) |
| 397 |
|
| 398 |
queries = ["create table foo(x)", |
| 399 |
"insert into foo(x) values(1)"] |
| 400 |
self.addCleanup(unlink, TESTFN) |
| 401 |
con1 = sqlite.connect(TESTFN, isolation_level=None) |
| 402 |
con2 = sqlite.connect(TESTFN) |
| 403 |
try: |
| 404 |
con1.set_trace_callback(trace) |
| 405 |
cur = con1.cursor() |
| 406 |
cur.execute(queries[0]) |
| 407 |
con2.execute("create table bar(x)") |
| 408 |
cur.execute(queries[1]) |
| 409 |
finally: |
| 410 |
con1.close() |
| 411 |
con2.close() |
| 412 |
self.assertEqual(traced_statements, queries) |
| 413 |
|
| 414 |
def test_trace_expanded_sql(self): |
| 415 |
expected = [ |
| 416 |
"create table t(t)", |
| 417 |
"BEGIN ", |
| 418 |
"insert into t values(0)", |
| 419 |
"insert into t values(1)", |
| 420 |
"insert into t values(2)", |
| 421 |
"COMMIT", |
| 422 |
] |
| 423 |
with memory_database() as cx, self.check_stmt_trace(cx, expected): |
| 424 |
with cx: |
| 425 |
cx.execute("create table t(t)") |
| 426 |
cx.executemany("insert into t values(?)", ((v,) for v in range(3))) |
| 427 |
|
| 428 |
@with_tracebacks( |
| 429 |
sqlite.DataError, |
| 430 |
regex="Expanded SQL string exceeds the maximum string length" |
| 431 |
) |
| 432 |
def test_trace_too_much_expanded_sql(self): |
| 433 |
# If the expanded string is too large, we'll fall back to the |
| 434 |
# unexpanded SQL statement. |
| 435 |
# The resulting string length is limited by the runtime limit |
| 436 |
# SQLITE_LIMIT_LENGTH. |
| 437 |
template = "select 1 as a where a=" |
| 438 |
category = sqlite.SQLITE_LIMIT_LENGTH |
| 439 |
with memory_database() as cx, cx_limit(cx, category=category) as lim: |
| 440 |
ok_param = "a" |
| 441 |
bad_param = "a" * lim |
| 442 |
|
| 443 |
unexpanded_query = template + "?" |
| 444 |
expected = [unexpanded_query] |
| 445 |
with self.check_stmt_trace(cx, expected): |
| 446 |
cx.execute(unexpanded_query, (bad_param,)) |
| 447 |
|
| 448 |
expanded_query = f"{template}'{ok_param}'" |
| 449 |
with self.check_stmt_trace(cx, [expanded_query]): |
| 450 |
cx.execute(unexpanded_query, (ok_param,)) |
| 451 |
|
| 452 |
@with_tracebacks(ZeroDivisionError, regex="division by zero") |
| 453 |
def test_trace_bad_handler(self): |
| 454 |
with memory_database() as cx: |
| 455 |
cx.set_trace_callback(lambda stmt: 5/0) |
| 456 |
cx.execute("select 1") |
| 457 |
|
| 458 |
def test_set_trace_callback_keyword_args(self): |
| 459 |
regex = ( |
| 460 |
r"Passing keyword argument 'trace_callback' to " |
| 461 |
r"_sqlite3.Connection.set_trace_callback\(\) is deprecated. " |
| 462 |
r"Parameter 'trace_callback' will become positional-only in " |
| 463 |
r"Python 3.15." |
| 464 |
) |
| 465 |
|
| 466 |
with self.assertWarnsRegex(DeprecationWarning, regex) as cm: |
| 467 |
self.con.set_trace_callback(trace_callback=lambda: None) |
| 468 |
self.assertEqual(cm.filename, __file__) |
| 469 |
|
| 470 |
# When a handler has an invalid signature, the exception raised is |
| 471 |
# the same that would be raised if the handler "negatively" replied, |
| 472 |
# but for the trace handler, exceptions are never re-raised (only |
| 473 |
# printed when needed). |
| 474 |
|
| 475 |
@with_tracebacks( |
| 476 |
TypeError, |
| 477 |
regex=r".*<lambda>\(\) missing 6 required positional arguments", |
| 478 |
) |
| 479 |
def test_trace_handler_invalid_signature(self): |
| 480 |
self.cx.execute("create table if not exists test(a number)") |
| 481 |
self.cx.set_trace_callback(lambda x, y, z, t, a, b, c: None) |
| 482 |
self.cx.execute("select * from test") |
| 483 |
|
| 484 |
# Tests for checking that callback context mutations do not crash. |
| 485 |
# Regression tests for https://github.com/python/cpython/issues/142830. |
| 486 |
|
| 487 |
@with_tracebacks(ZeroDivisionError, regex="hello world") |
| 488 |
def test_trace_callback_concurrent_mutation_in_call(self): |
| 489 |
self.cx.execute("create table if not exists test(a number)") |
| 490 |
|
| 491 |
def handler(statement): |
| 492 |
# clear the progress handler |
| 493 |
self.cx.set_trace_callback(None) |
| 494 |
raise ZeroDivisionError("hello world") |
| 495 |
|
| 496 |
self.cx.set_trace_callback(handler) |
| 497 |
self.cx.execute("select * from test") |
| 498 |
|
| 499 |
|
| 500 |
if __name__ == "__main__": |
| 501 |
unittest.main() |