Debian Codegraph @

Lib/test/test_sqlite3/test_transactions.py

raw
1 # pysqlite2/test/transactions.py: tests transactions
2 #
3 # Copyright (C) 2005-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 unittest
24 import sqlite3 as sqlite
25 from contextlib import contextmanager
26
27 from test.support.os_helper import TESTFN, unlink
28 from test.support.script_helper import assert_python_ok
29
30 from .util import memory_database
31 from .util import MemoryDatabaseMixin
32
33
34 class TransactionTests(unittest.TestCase):
35 def setUp(self):
36 # We can disable the busy handlers, since we control
37 # the order of SQLite C API operations.
38 self.con1 = sqlite.connect(TESTFN, timeout=0)
39 self.cur1 = self.con1.cursor()
40
41 self.con2 = sqlite.connect(TESTFN, timeout=0)
42 self.cur2 = self.con2.cursor()
43
44 def tearDown(self):
45 try:
46 self.cur1.close()
47 self.con1.close()
48
49 self.cur2.close()
50 self.con2.close()
51
52 finally:
53 unlink(TESTFN)
54
55 def test_dml_does_not_auto_commit_before(self):
56 self.cur1.execute("create table test(i)")
57 self.cur1.execute("insert into test(i) values (5)")
58 self.cur1.execute("create table test2(j)")
59 self.cur2.execute("select i from test")
60 res = self.cur2.fetchall()
61 self.assertEqual(len(res), 0)
62
63 def test_insert_starts_transaction(self):
64 self.cur1.execute("create table test(i)")
65 self.cur1.execute("insert into test(i) values (5)")
66 self.cur2.execute("select i from test")
67 res = self.cur2.fetchall()
68 self.assertEqual(len(res), 0)
69
70 def test_update_starts_transaction(self):
71 self.cur1.execute("create table test(i)")
72 self.cur1.execute("insert into test(i) values (5)")
73 self.con1.commit()
74 self.cur1.execute("update test set i=6")
75 self.cur2.execute("select i from test")
76 res = self.cur2.fetchone()[0]
77 self.assertEqual(res, 5)
78
79 def test_delete_starts_transaction(self):
80 self.cur1.execute("create table test(i)")
81 self.cur1.execute("insert into test(i) values (5)")
82 self.con1.commit()
83 self.cur1.execute("delete from test")
84 self.cur2.execute("select i from test")
85 res = self.cur2.fetchall()
86 self.assertEqual(len(res), 1)
87
88 def test_replace_starts_transaction(self):
89 self.cur1.execute("create table test(i)")
90 self.cur1.execute("insert into test(i) values (5)")
91 self.con1.commit()
92 self.cur1.execute("replace into test(i) values (6)")
93 self.cur2.execute("select i from test")
94 res = self.cur2.fetchall()
95 self.assertEqual(len(res), 1)
96 self.assertEqual(res[0][0], 5)
97
98 def test_toggle_auto_commit(self):
99 self.cur1.execute("create table test(i)")
100 self.cur1.execute("insert into test(i) values (5)")
101 self.con1.isolation_level = None
102 self.assertEqual(self.con1.isolation_level, None)
103 self.cur2.execute("select i from test")
104 res = self.cur2.fetchall()
105 self.assertEqual(len(res), 1)
106
107 self.con1.isolation_level = "DEFERRED"
108 self.assertEqual(self.con1.isolation_level , "DEFERRED")
109 self.cur1.execute("insert into test(i) values (5)")
110 self.cur2.execute("select i from test")
111 res = self.cur2.fetchall()
112 self.assertEqual(len(res), 1)
113
114 def test_raise_timeout(self):
115 self.cur1.execute("create table test(i)")
116 self.cur1.execute("insert into test(i) values (5)")
117 with self.assertRaises(sqlite.OperationalError):
118 self.cur2.execute("insert into test(i) values (5)")
119
120 def test_locking(self):
121 # This tests the improved concurrency with pysqlite 2.3.4. You needed
122 # to roll back con2 before you could commit con1.
123 self.cur1.execute("create table test(i)")
124 self.cur1.execute("insert into test(i) values (5)")
125 with self.assertRaises(sqlite.OperationalError):
126 self.cur2.execute("insert into test(i) values (5)")
127 # NO self.con2.rollback() HERE!!!
128 self.con1.commit()
129
130 def test_rollback_cursor_consistency(self):
131 """Check that cursors behave correctly after rollback."""
132 with memory_database() as con:
133 cur = con.cursor()
134 cur.execute("create table test(x)")
135 cur.execute("insert into test(x) values (5)")
136 cur.execute("select 1 union select 2 union select 3")
137
138 con.rollback()
139 self.assertEqual(cur.fetchall(), [(1,), (2,), (3,)])
140
141 def test_multiple_cursors_and_iternext(self):
142 # gh-94028: statements are cleared and reset in cursor iternext.
143
144 # Provoke the gh-94028 by using a cursor cache.
145 CURSORS = {}
146 def sql(cx, sql, *args):
147 cu = cx.cursor()
148 cu.execute(sql, args)
149 CURSORS[id(sql)] = cu
150 return cu
151
152 self.con1.execute("create table t(t)")
153 sql(self.con1, "insert into t values (?), (?), (?)", "u1", "u2", "u3")
154 self.con1.commit()
155
156 # On second connection, verify rows are visible, then delete them.
157 count = sql(self.con2, "select count(*) from t").fetchone()[0]
158 self.assertEqual(count, 3)
159 changes = sql(self.con2, "delete from t").rowcount
160 self.assertEqual(changes, 3)
161 self.con2.commit()
162
163 # Back in original connection, create 2 new users.
164 sql(self.con1, "insert into t values (?)", "u4")
165 sql(self.con1, "insert into t values (?)", "u5")
166
167 # The second connection cannot see uncommitted changes.
168 count = sql(self.con2, "select count(*) from t").fetchone()[0]
169 self.assertEqual(count, 0)
170
171 # First connection can see its own changes.
172 count = sql(self.con1, "select count(*) from t").fetchone()[0]
173 self.assertEqual(count, 2)
174
175 # The second connection can now see the changes.
176 self.con1.commit()
177 count = sql(self.con2, "select count(*) from t").fetchone()[0]
178 self.assertEqual(count, 2)
179
180
181 class RollbackTests(unittest.TestCase):
182 """bpo-44092: sqlite3 now leaves it to SQLite to resolve rollback issues"""
183
184 def setUp(self):
185 self.con = sqlite.connect(":memory:")
186 self.cur1 = self.con.cursor()
187 self.cur2 = self.con.cursor()
188 with self.con:
189 self.con.execute("create table t(c)");
190 self.con.executemany("insert into t values(?)", [(0,), (1,), (2,)])
191 self.cur1.execute("begin transaction")
192 select = "select c from t"
193 self.cur1.execute(select)
194 self.con.rollback()
195 self.res = self.cur2.execute(select) # Reusing stmt from cache
196
197 def tearDown(self):
198 self.con.close()
199
200 def _check_rows(self):
201 for i, row in enumerate(self.res):
202 self.assertEqual(row[0], i)
203
204 def test_no_duplicate_rows_after_rollback_del_cursor(self):
205 del self.cur1
206 self._check_rows()
207
208 def test_no_duplicate_rows_after_rollback_close_cursor(self):
209 self.cur1.close()
210 self._check_rows()
211
212 def test_no_duplicate_rows_after_rollback_new_query(self):
213 self.cur1.execute("select c from t where c = 1")
214 self._check_rows()
215
216
217
218 class SpecialCommandTests(MemoryDatabaseMixin, unittest.TestCase):
219
220 def test_drop_table(self):
221 self.cur.execute("create table test(i)")
222 self.cur.execute("insert into test(i) values (5)")
223 self.cur.execute("drop table test")
224
225 def test_pragma(self):
226 self.cur.execute("create table test(i)")
227 self.cur.execute("insert into test(i) values (5)")
228 self.cur.execute("pragma count_changes=1")
229
230
231 class TransactionalDDL(MemoryDatabaseMixin, unittest.TestCase):
232
233 def test_ddl_does_not_autostart_transaction(self):
234 # For backwards compatibility reasons, DDL statements should not
235 # implicitly start a transaction.
236 self.con.execute("create table test(i)")
237 self.con.rollback()
238 result = self.con.execute("select * from test").fetchall()
239 self.assertEqual(result, [])
240
241 def test_immediate_transactional_ddl(self):
242 # You can achieve transactional DDL by issuing a BEGIN
243 # statement manually.
244 self.con.execute("begin immediate")
245 self.con.execute("create table test(i)")
246 self.con.rollback()
247 with self.assertRaises(sqlite.OperationalError):
248 self.con.execute("select * from test")
249
250 def test_transactional_ddl(self):
251 # You can achieve transactional DDL by issuing a BEGIN
252 # statement manually.
253 self.con.execute("begin")
254 self.con.execute("create table test(i)")
255 self.con.rollback()
256 with self.assertRaises(sqlite.OperationalError):
257 self.con.execute("select * from test")
258
259
260 class IsolationLevelFromInit(unittest.TestCase):
261 CREATE = "create table t(t)"
262 INSERT = "insert into t values(1)"
263
264 def setUp(self):
265 self.traced = []
266
267 def _run_test(self, cx):
268 cx.execute(self.CREATE)
269 cx.set_trace_callback(lambda stmt: self.traced.append(stmt))
270 with cx:
271 cx.execute(self.INSERT)
272
273 def test_isolation_level_default(self):
274 with memory_database() as cx:
275 self._run_test(cx)
276 self.assertEqual(self.traced, ["BEGIN ", self.INSERT, "COMMIT"])
277
278 def test_isolation_level_begin(self):
279 with memory_database(isolation_level="") as cx:
280 self._run_test(cx)
281 self.assertEqual(self.traced, ["BEGIN ", self.INSERT, "COMMIT"])
282
283 def test_isolation_level_deferred(self):
284 with memory_database(isolation_level="DEFERRED") as cx:
285 self._run_test(cx)
286 self.assertEqual(self.traced, ["BEGIN DEFERRED", self.INSERT, "COMMIT"])
287
288 def test_isolation_level_immediate(self):
289 with memory_database(isolation_level="IMMEDIATE") as cx:
290 self._run_test(cx)
291 self.assertEqual(self.traced,
292 ["BEGIN IMMEDIATE", self.INSERT, "COMMIT"])
293
294 def test_isolation_level_exclusive(self):
295 with memory_database(isolation_level="EXCLUSIVE") as cx:
296 self._run_test(cx)
297 self.assertEqual(self.traced,
298 ["BEGIN EXCLUSIVE", self.INSERT, "COMMIT"])
299
300 def test_isolation_level_none(self):
301 with memory_database(isolation_level=None) as cx:
302 self._run_test(cx)
303 self.assertEqual(self.traced, [self.INSERT])
304
305
306 class IsolationLevelPostInit(unittest.TestCase):
307 QUERY = "insert into t values(1)"
308
309 def setUp(self):
310 self.cx = sqlite.connect(":memory:")
311 self.cx.execute("create table t(t)")
312 self.traced = []
313 self.cx.set_trace_callback(lambda stmt: self.traced.append(stmt))
314
315 def tearDown(self):
316 self.cx.close()
317
318 def test_isolation_level_default(self):
319 with self.cx:
320 self.cx.execute(self.QUERY)
321 self.assertEqual(self.traced, ["BEGIN ", self.QUERY, "COMMIT"])
322
323 def test_isolation_level_begin(self):
324 self.cx.isolation_level = ""
325 with self.cx:
326 self.cx.execute(self.QUERY)
327 self.assertEqual(self.traced, ["BEGIN ", self.QUERY, "COMMIT"])
328
329 def test_isolation_level_deferrred(self):
330 self.cx.isolation_level = "DEFERRED"
331 with self.cx:
332 self.cx.execute(self.QUERY)
333 self.assertEqual(self.traced, ["BEGIN DEFERRED", self.QUERY, "COMMIT"])
334
335 def test_isolation_level_immediate(self):
336 self.cx.isolation_level = "IMMEDIATE"
337 with self.cx:
338 self.cx.execute(self.QUERY)
339 self.assertEqual(self.traced,
340 ["BEGIN IMMEDIATE", self.QUERY, "COMMIT"])
341
342 def test_isolation_level_exclusive(self):
343 self.cx.isolation_level = "EXCLUSIVE"
344 with self.cx:
345 self.cx.execute(self.QUERY)
346 self.assertEqual(self.traced,
347 ["BEGIN EXCLUSIVE", self.QUERY, "COMMIT"])
348
349 def test_isolation_level_none(self):
350 self.cx.isolation_level = None
351 with self.cx:
352 self.cx.execute(self.QUERY)
353 self.assertEqual(self.traced, [self.QUERY])
354
355
356 class AutocommitAttribute(unittest.TestCase):
357 """Test PEP 249-compliant autocommit behaviour."""
358 legacy = sqlite.LEGACY_TRANSACTION_CONTROL
359
360 @contextmanager
361 def check_stmt_trace(self, cx, expected, reset=True):
362 try:
363 traced = []
364 cx.set_trace_callback(lambda stmt: traced.append(stmt))
365 yield
366 finally:
367 self.assertEqual(traced, expected)
368 if reset:
369 cx.set_trace_callback(None)
370
371 def test_autocommit_default(self):
372 with memory_database() as cx:
373 self.assertEqual(cx.autocommit,
374 sqlite.LEGACY_TRANSACTION_CONTROL)
375
376 def test_autocommit_setget(self):
377 dataset = (
378 True,
379 False,
380 sqlite.LEGACY_TRANSACTION_CONTROL,
381 )
382 for mode in dataset:
383 with self.subTest(mode=mode):
384 with memory_database(autocommit=mode) as cx:
385 self.assertEqual(cx.autocommit, mode)
386 with memory_database() as cx:
387 cx.autocommit = mode
388 self.assertEqual(cx.autocommit, mode)
389
390 def test_autocommit_setget_invalid(self):
391 msg = "autocommit must be True, False, or.*LEGACY"
392 for mode in "a", 12, (), None:
393 with self.subTest(mode=mode):
394 with self.assertRaisesRegex(ValueError, msg):
395 sqlite.connect(":memory:", autocommit=mode)
396
397 def test_autocommit_disabled(self):
398 expected = [
399 "SELECT 1",
400 "COMMIT",
401 "BEGIN",
402 "ROLLBACK",
403 "BEGIN",
404 ]
405 with memory_database(autocommit=False) as cx:
406 self.assertTrue(cx.in_transaction)
407 with self.check_stmt_trace(cx, expected):
408 cx.execute("SELECT 1")
409 cx.commit()
410 cx.rollback()
411
412 def test_autocommit_disabled_implicit_rollback(self):
413 expected = ["ROLLBACK"]
414 with memory_database(autocommit=False) as cx:
415 self.assertTrue(cx.in_transaction)
416 with self.check_stmt_trace(cx, expected, reset=False):
417 cx.close()
418
419 def test_autocommit_enabled(self):
420 expected = ["CREATE TABLE t(t)", "INSERT INTO t VALUES(1)"]
421 with memory_database(autocommit=True) as cx:
422 self.assertFalse(cx.in_transaction)
423 with self.check_stmt_trace(cx, expected):
424 cx.execute("CREATE TABLE t(t)")
425 cx.execute("INSERT INTO t VALUES(1)")
426 self.assertFalse(cx.in_transaction)
427
428 def test_autocommit_enabled_txn_ctl(self):
429 for op in "commit", "rollback":
430 with self.subTest(op=op):
431 with memory_database(autocommit=True) as cx:
432 meth = getattr(cx, op)
433 self.assertFalse(cx.in_transaction)
434 with self.check_stmt_trace(cx, []):
435 meth() # expect this to pass silently
436 self.assertFalse(cx.in_transaction)
437
438 def test_autocommit_disabled_then_enabled(self):
439 expected = ["COMMIT"]
440 with memory_database(autocommit=False) as cx:
441 self.assertTrue(cx.in_transaction)
442 with self.check_stmt_trace(cx, expected):
443 cx.autocommit = True # should commit
444 self.assertFalse(cx.in_transaction)
445
446 def test_autocommit_enabled_then_disabled(self):
447 expected = ["BEGIN"]
448 with memory_database(autocommit=True) as cx:
449 self.assertFalse(cx.in_transaction)
450 with self.check_stmt_trace(cx, expected):
451 cx.autocommit = False # should begin
452 self.assertTrue(cx.in_transaction)
453
454 def test_autocommit_explicit_then_disabled(self):
455 expected = ["BEGIN DEFERRED"]
456 with memory_database(autocommit=True) as cx:
457 self.assertFalse(cx.in_transaction)
458 with self.check_stmt_trace(cx, expected):
459 cx.execute("BEGIN DEFERRED")
460 cx.autocommit = False # should now be a no-op
461 self.assertTrue(cx.in_transaction)
462
463 def test_autocommit_enabled_ctx_mgr(self):
464 with memory_database(autocommit=True) as cx:
465 # The context manager is a no-op if autocommit=True
466 with self.check_stmt_trace(cx, []):
467 with cx:
468 self.assertFalse(cx.in_transaction)
469 self.assertFalse(cx.in_transaction)
470
471 def test_autocommit_disabled_ctx_mgr(self):
472 expected = ["COMMIT", "BEGIN"]
473 with memory_database(autocommit=False) as cx:
474 with self.check_stmt_trace(cx, expected):
475 with cx:
476 self.assertTrue(cx.in_transaction)
477 self.assertTrue(cx.in_transaction)
478
479 def test_autocommit_compat_ctx_mgr(self):
480 expected = ["BEGIN ", "INSERT INTO T VALUES(1)", "COMMIT"]
481 with memory_database(autocommit=self.legacy) as cx:
482 cx.execute("create table t(t)")
483 with self.check_stmt_trace(cx, expected):
484 with cx:
485 self.assertFalse(cx.in_transaction)
486 cx.execute("INSERT INTO T VALUES(1)")
487 self.assertTrue(cx.in_transaction)
488 self.assertFalse(cx.in_transaction)
489
490 def test_autocommit_enabled_executescript(self):
491 expected = ["BEGIN", "SELECT 1"]
492 with memory_database(autocommit=True) as cx:
493 with self.check_stmt_trace(cx, expected):
494 self.assertFalse(cx.in_transaction)
495 cx.execute("BEGIN")
496 cx.executescript("SELECT 1")
497 self.assertTrue(cx.in_transaction)
498
499 def test_autocommit_disabled_executescript(self):
500 expected = ["SELECT 1"]
501 with memory_database(autocommit=False) as cx:
502 with self.check_stmt_trace(cx, expected):
503 self.assertTrue(cx.in_transaction)
504 cx.executescript("SELECT 1")
505 self.assertTrue(cx.in_transaction)
506
507 def test_autocommit_compat_executescript(self):
508 expected = ["BEGIN", "COMMIT", "SELECT 1"]
509 with memory_database(autocommit=self.legacy) as cx:
510 with self.check_stmt_trace(cx, expected):
511 self.assertFalse(cx.in_transaction)
512 cx.execute("BEGIN")
513 cx.executescript("SELECT 1")
514 self.assertFalse(cx.in_transaction)
515
516 def test_autocommit_disabled_implicit_shutdown(self):
517 # The implicit ROLLBACK should not call back into Python during
518 # interpreter tear-down.
519 code = """if 1:
520 import sqlite3
521 cx = sqlite3.connect(":memory:", autocommit=False)
522 cx.set_trace_callback(print)
523 """
524 assert_python_ok("-c", code, PYTHONIOENCODING="utf-8")
525
526
527 if __name__ == "__main__":
528 unittest.main()