Debian Codegraph @

aiosqlite/tests/smoke.py

raw
1 # Copyright Amethyst Reese
2 # Licensed under the MIT license
3
4 import asyncio
5 import sqlite3
6 from pathlib import Path
7 from sqlite3 import OperationalError
8 from tempfile import TemporaryDirectory
9 from threading import Thread
10 from unittest import IsolatedAsyncioTestCase, SkipTest
11 from unittest.mock import patch
12
13 import aiosqlite
14 from .helpers import setup_logger
15
16
17 class SmokeTest(IsolatedAsyncioTestCase):
18 @classmethod
19 def setUpClass(cls):
20 setup_logger()
21
22 def setUp(self):
23 td = TemporaryDirectory()
24 self.addCleanup(td.cleanup)
25 self.db = Path(td.name).resolve() / "test.db"
26
27 async def test_connection_await(self):
28 db = await aiosqlite.connect(self.db)
29 self.assertIsInstance(db, aiosqlite.Connection)
30
31 async with db.execute("select 1, 2") as cursor:
32 rows = await cursor.fetchall()
33 self.assertEqual(rows, [(1, 2)])
34
35 await db.close()
36
37 async def test_connection_context(self):
38 async with aiosqlite.connect(self.db) as db:
39 self.assertIsInstance(db, aiosqlite.Connection)
40
41 async with db.execute("select 1, 2") as cursor:
42 rows = await cursor.fetchall()
43 self.assertEqual(rows, [(1, 2)])
44
45 async def test_connection_locations(self):
46 TEST_DB = self.db.as_posix()
47
48 class Fake: # pylint: disable=too-few-public-methods
49 def __str__(self):
50 return TEST_DB
51
52 locs = (Path(TEST_DB), TEST_DB, TEST_DB.encode(), Fake())
53
54 async with aiosqlite.connect(locs[0]) as db:
55 await db.execute("create table foo (i integer, k integer)")
56 await db.execute("insert into foo (i, k) values (1, 5)")
57 await db.commit()
58
59 cursor = await db.execute("select * from foo")
60 rows = await cursor.fetchall()
61
62 for loc in locs:
63 async with aiosqlite.connect(loc) as db:
64 cursor = await db.execute("select * from foo")
65 self.assertEqual(await cursor.fetchall(), rows)
66
67 async def test_multiple_connections(self):
68 async with aiosqlite.connect(self.db) as db:
69 await db.execute(
70 "create table multiple_connections "
71 "(i integer primary key asc, k integer)"
72 )
73
74 async def do_one_conn(i):
75 async with aiosqlite.connect(self.db) as db:
76 await db.execute("insert into multiple_connections (k) values (?)", [i])
77 await db.commit()
78
79 await asyncio.gather(*[do_one_conn(i) for i in range(10)])
80
81 async with aiosqlite.connect(self.db) as db:
82 cursor = await db.execute("select * from multiple_connections")
83 rows = await cursor.fetchall()
84
85 assert len(rows) == 10
86
87 async def test_multiple_queries(self):
88 async with aiosqlite.connect(self.db) as db:
89 await db.execute(
90 "create table multiple_queries "
91 "(i integer primary key asc, k integer)"
92 )
93
94 await asyncio.gather(
95 *[
96 db.execute("insert into multiple_queries (k) values (?)", [i])
97 for i in range(10)
98 ]
99 )
100
101 await db.commit()
102
103 async with aiosqlite.connect(self.db) as db:
104 cursor = await db.execute("select * from multiple_queries")
105 rows = await cursor.fetchall()
106
107 assert len(rows) == 10
108
109 async def test_iterable_cursor(self):
110 async with aiosqlite.connect(self.db) as db:
111 cursor = await db.cursor()
112 await cursor.execute(
113 "create table iterable_cursor " "(i integer primary key asc, k integer)"
114 )
115 await cursor.executemany(
116 "insert into iterable_cursor (k) values (?)", [[i] for i in range(10)]
117 )
118 await db.commit()
119
120 async with aiosqlite.connect(self.db) as db:
121 cursor = await db.execute("select * from iterable_cursor")
122 rows = []
123 async for row in cursor:
124 rows.append(row)
125
126 assert len(rows) == 10
127
128 async def test_multi_loop_usage(self):
129 results = {}
130
131 def runner(k, conn):
132 async def query():
133 async with conn.execute("select * from foo") as cursor:
134 rows = await cursor.fetchall()
135 self.assertEqual(len(rows), 2)
136 return rows
137
138 with self.subTest(k):
139 loop = asyncio.new_event_loop()
140 rows = loop.run_until_complete(query())
141 loop.close()
142 results[k] = rows
143
144 async with aiosqlite.connect(":memory:") as db:
145 await db.execute("create table foo (id int, name varchar)")
146 await db.execute(
147 "insert into foo values (?, ?), (?, ?)", (1, "Sally", 2, "Janet")
148 )
149 await db.commit()
150
151 threads = [Thread(target=runner, args=(k, db)) for k in range(4)]
152 for thread in threads:
153 thread.start()
154 for thread in threads:
155 thread.join()
156
157 self.assertEqual(len(results), 4)
158 for rows in results.values():
159 self.assertEqual(len(rows), 2)
160
161 async def test_context_cursor(self):
162 async with aiosqlite.connect(self.db) as db:
163 async with db.cursor() as cursor:
164 await cursor.execute(
165 "create table context_cursor "
166 "(i integer primary key asc, k integer)"
167 )
168 await cursor.executemany(
169 "insert into context_cursor (k) values (?)",
170 [[i] for i in range(10)],
171 )
172 await db.commit()
173
174 async with aiosqlite.connect(self.db) as db:
175 async with db.execute("select * from context_cursor") as cursor:
176 rows = []
177 async for row in cursor:
178 rows.append(row)
179
180 assert len(rows) == 10
181
182 async def test_cursor_return_self(self):
183 async with aiosqlite.connect(self.db) as db:
184 cursor = await db.cursor()
185
186 result = await cursor.execute(
187 "create table test_cursor_return_self (i integer, k integer)"
188 )
189 self.assertEqual(result, cursor, "cursor execute returns itself")
190
191 result = await cursor.executemany(
192 "insert into test_cursor_return_self values (?, ?)", [(1, 1), (2, 2)]
193 )
194 self.assertEqual(result, cursor)
195
196 result = await cursor.executescript(
197 "insert into test_cursor_return_self values (3, 3);"
198 "insert into test_cursor_return_self values (4, 4);"
199 "insert into test_cursor_return_self values (5, 5);"
200 )
201 self.assertEqual(result, cursor)
202
203 async def test_connection_properties(self):
204 async with aiosqlite.connect(self.db) as db:
205 self.assertEqual(db.total_changes, 0)
206
207 async with db.cursor() as cursor:
208 self.assertFalse(db.in_transaction)
209 await cursor.execute(
210 "create table test_properties "
211 "(i integer primary key asc, k integer, d text)"
212 )
213 await cursor.execute(
214 "insert into test_properties (k, d) values (1, 'hi')"
215 )
216 self.assertTrue(db.in_transaction)
217 await db.commit()
218 self.assertFalse(db.in_transaction)
219
220 self.assertEqual(db.total_changes, 1)
221
222 self.assertIsNone(db.row_factory)
223 self.assertEqual(db.text_factory, str)
224
225 async with db.cursor() as cursor:
226 await cursor.execute("select * from test_properties")
227 row = await cursor.fetchone()
228 self.assertIsInstance(row, tuple)
229 self.assertEqual(row, (1, 1, "hi"))
230 with self.assertRaises(TypeError):
231 _ = row["k"]
232
233 async with db.cursor() as cursor:
234 cursor.row_factory = aiosqlite.Row
235 self.assertEqual(cursor.row_factory, aiosqlite.Row)
236 await cursor.execute("select * from test_properties")
237 row = await cursor.fetchone()
238 self.assertIsInstance(row, aiosqlite.Row)
239 self.assertEqual(row[1], 1)
240 self.assertEqual(row[2], "hi")
241 self.assertEqual(row["k"], 1)
242 self.assertEqual(row["d"], "hi")
243
244 db.row_factory = aiosqlite.Row
245 db.text_factory = bytes
246 self.assertEqual(db.row_factory, aiosqlite.Row)
247 self.assertEqual(db.text_factory, bytes)
248
249 async with db.cursor() as cursor:
250 await cursor.execute("select * from test_properties")
251 row = await cursor.fetchone()
252 self.assertIsInstance(row, aiosqlite.Row)
253 self.assertEqual(row[1], 1)
254 self.assertEqual(row[2], b"hi")
255 self.assertEqual(row["k"], 1)
256 self.assertEqual(row["d"], b"hi")
257
258 async def test_fetch_all(self):
259 async with aiosqlite.connect(self.db) as db:
260 await db.execute(
261 "create table test_fetch_all (i integer primary key asc, k integer)"
262 )
263 await db.execute(
264 "insert into test_fetch_all (k) values (10), (24), (16), (32)"
265 )
266 await db.commit()
267
268 async with aiosqlite.connect(self.db) as db:
269 cursor = await db.execute("select k from test_fetch_all where k < 30")
270 rows = await cursor.fetchall()
271 self.assertEqual(rows, [(10,), (24,), (16,)])
272
273 async def test_enable_load_extension(self):
274 """Assert that after enabling extension loading, they can be loaded"""
275 async with aiosqlite.connect(self.db) as db:
276 try:
277 await db.enable_load_extension(True)
278 await db.load_extension("test")
279 except OperationalError as e:
280 assert "not authorized" not in e.args
281 except AttributeError as e:
282 raise SkipTest(
283 "python was not compiled with sqlite3 "
284 "extension support, so we can't test it"
285 ) from e
286
287 async def test_set_progress_handler(self):
288 """
289 Assert that after setting a progress handler returning 1, DB operations are aborted
290 """
291 async with aiosqlite.connect(self.db) as db:
292 await db.set_progress_handler(lambda: 1, 1)
293 with self.assertRaises(OperationalError):
294 await db.execute(
295 "create table test_progress_handler (i integer primary key asc, k integer)"
296 )
297
298 async def test_create_function(self):
299 """Assert that after creating a custom function, it can be used"""
300
301 def no_arg():
302 return "no arg"
303
304 def one_arg(num):
305 return num * 2
306
307 async with aiosqlite.connect(self.db) as db:
308 await db.create_function("no_arg", 0, no_arg)
309 await db.create_function("one_arg", 1, one_arg)
310
311 async with db.execute("SELECT no_arg();") as res:
312 row = await res.fetchone()
313 self.assertEqual(row[0], "no arg")
314
315 async with db.execute("SELECT one_arg(10);") as res:
316 row = await res.fetchone()
317 self.assertEqual(row[0], 20)
318
319 async def test_create_function_deterministic(self):
320 """Assert that after creating a deterministic custom function, it can be used.
321
322 https://sqlite.org/deterministic.html
323 """
324
325 def one_arg(num):
326 return num * 2
327
328 async with aiosqlite.connect(self.db) as db:
329 await db.create_function("one_arg", 1, one_arg, deterministic=True)
330 await db.execute("create table foo (id int, bar int)")
331
332 # Non-deterministic functions cannot be used in indexes
333 await db.execute("create index t on foo(one_arg(bar))")
334
335 async def test_set_trace_callback(self):
336 statements = []
337
338 def callback(statement: str):
339 statements.append(statement)
340
341 async with aiosqlite.connect(self.db) as db:
342 await db.set_trace_callback(callback)
343
344 await db.execute("select 10")
345 self.assertIn("select 10", statements)
346
347 async def test_connect_error(self):
348 bad_db = Path("/something/that/shouldnt/exist.db")
349 with self.assertRaisesRegex(OperationalError, "unable to open database"):
350 async with aiosqlite.connect(bad_db) as db:
351 self.assertIsNone(db) # should never be reached
352
353 with self.assertRaisesRegex(OperationalError, "unable to open database"):
354 await aiosqlite.connect(bad_db)
355
356 async def test_connect_base_exception(self):
357 # Check if connect task is cancelled, thread is properly closed.
358 def _raise_cancelled_error(*_, **__):
359 raise asyncio.CancelledError("I changed my mind")
360
361 connection = aiosqlite.Connection(lambda: sqlite3.connect(":memory:"), 64)
362 with (
363 patch.object(sqlite3, "connect", side_effect=_raise_cancelled_error),
364 self.assertRaisesRegex(asyncio.CancelledError, "I changed my mind"),
365 ):
366 async with connection:
367 ...
368 # Terminate the thread here if the test fails to have a clear error.
369 if connection._running:
370 connection._stop_running()
371 raise AssertionError("connection thread was not stopped")
372
373 async def test_iterdump(self):
374 async with aiosqlite.connect(":memory:") as db:
375 await db.execute("create table foo (i integer, k charvar(250))")
376 await db.executemany(
377 "insert into foo values (?, ?)", [(1, "hello"), (2, "world")]
378 )
379
380 lines = [line async for line in db.iterdump()]
381 self.assertEqual(
382 lines,
383 [
384 "BEGIN TRANSACTION;",
385 "CREATE TABLE foo (i integer, k charvar(250));",
386 "INSERT INTO \"foo\" VALUES(1,'hello');",
387 "INSERT INTO \"foo\" VALUES(2,'world');",
388 "COMMIT;",
389 ],
390 )
391
392 async def test_cursor_on_closed_connection(self):
393 db = await aiosqlite.connect(self.db)
394
395 cursor = await db.execute("select 1, 2")
396 await db.close()
397 with self.assertRaisesRegex(ValueError, "Connection closed"):
398 await cursor.fetchall()
399 with self.assertRaisesRegex(ValueError, "Connection closed"):
400 await cursor.fetchall()
401
402 async def test_cursor_on_closed_connection_loop(self):
403 db = await aiosqlite.connect(self.db)
404
405 cursor = await db.execute("select 1, 2")
406 tasks = []
407 for i in range(100):
408 if i == 50:
409 tasks.append(asyncio.ensure_future(db.close()))
410 tasks.append(asyncio.ensure_future(cursor.fetchall()))
411 for task in tasks:
412 try:
413 await task
414 except sqlite3.ProgrammingError:
415 pass
416
417 async def test_close_twice(self):
418 db = await aiosqlite.connect(self.db)
419
420 await db.close()
421
422 # no error
423 await db.close()
424
425 async def test_backup_aiosqlite(self):
426 def progress(a, b, c):
427 print(a, b, c)
428
429 async with (
430 aiosqlite.connect(":memory:") as db1,
431 aiosqlite.connect(":memory:") as db2,
432 ):
433 await db1.execute("create table foo (i integer, k charvar(250))")
434 await db1.executemany(
435 "insert into foo values (?, ?)", [(1, "hello"), (2, "world")]
436 )
437 await db1.commit()
438
439 with self.assertRaisesRegex(OperationalError, "no such table: foo"):
440 await db2.execute("select * from foo")
441
442 await db1.backup(db2, progress=progress)
443
444 async with db2.execute("select * from foo") as cursor:
445 rows = await cursor.fetchall()
446 self.assertEqual(rows, [(1, "hello"), (2, "world")])
447
448 async def test_backup_sqlite(self):
449 async with aiosqlite.connect(":memory:") as db1:
450 with sqlite3.connect(":memory:") as db2:
451 await db1.execute("create table foo (i integer, k charvar(250))")
452 await db1.executemany(
453 "insert into foo values (?, ?)", [(1, "hello"), (2, "world")]
454 )
455 await db1.commit()
456
457 with self.assertRaisesRegex(OperationalError, "no such table: foo"):
458 db2.execute("select * from foo")
459
460 await db1.backup(db2)
461
462 cursor = db2.execute("select * from foo")
463 rows = cursor.fetchall()
464 self.assertEqual(rows, [(1, "hello"), (2, "world")])