blob: b927cb3ed1857d904808eb2bc41914879a1f7030 [file] [log] [blame]
Petri Lehtinen8b945142013-02-23 19:05:09 +01001#-*- coding: iso-8859-1 -*-
Thomas Wouters49fd7fa2006-04-21 10:40:58 +00002# pysqlite2/test/regression.py: pysqlite regression tests
3#
Gerhard Häringf9cee222010-03-05 15:20:03 +00004# Copyright (C) 2006-2010 Gerhard Häring <gh@ghaering.de>
Thomas Wouters49fd7fa2006-04-21 10:40:58 +00005#
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
Gerhard Häringe7ea7452008-03-29 00:45:29 +000024import datetime
Thomas Wouters49fd7fa2006-04-21 10:40:58 +000025import unittest
Thomas Wouters477c8d52006-05-27 19:21:47 +000026import sqlite3 as sqlite
Thomas Wouters49fd7fa2006-04-21 10:40:58 +000027
28class RegressionTests(unittest.TestCase):
29 def setUp(self):
30 self.con = sqlite.connect(":memory:")
31
32 def tearDown(self):
33 self.con.close()
34
35 def CheckPragmaUserVersion(self):
36 # This used to crash pysqlite because this pragma command returns NULL for the column name
37 cur = self.con.cursor()
38 cur.execute("pragma user_version")
39
Thomas Wouters477c8d52006-05-27 19:21:47 +000040 def CheckPragmaSchemaVersion(self):
41 # This still crashed pysqlite <= 2.2.1
42 con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
43 try:
44 cur = self.con.cursor()
45 cur.execute("pragma schema_version")
46 finally:
47 cur.close()
48 con.close()
49
50 def CheckStatementReset(self):
51 # pysqlite 2.1.0 to 2.2.0 have the problem that not all statements are
52 # reset before a rollback, but only those that are still in the
53 # statement cache. The others are not accessible from the connection object.
54 con = sqlite.connect(":memory:", cached_statements=5)
Guido van Rossum805365e2007-05-07 22:24:25 +000055 cursors = [con.cursor() for x in range(5)]
Thomas Wouters477c8d52006-05-27 19:21:47 +000056 cursors[0].execute("create table test(x)")
57 for i in range(10):
Guido van Rossum805365e2007-05-07 22:24:25 +000058 cursors[0].executemany("insert into test(x) values (?)", [(x,) for x in range(10)])
Thomas Wouters477c8d52006-05-27 19:21:47 +000059
60 for i in range(5):
61 cursors[i].execute(" " * i + "select x from test")
62
63 con.rollback()
64
Thomas Wouters0e3f5912006-08-11 14:57:12 +000065 def CheckColumnNameWithSpaces(self):
66 cur = self.con.cursor()
67 cur.execute('select 1 as "foo bar [datetime]"')
Gregory P. Smith04cecaf2009-07-04 08:32:15 +000068 self.assertEqual(cur.description[0][0], "foo bar")
Thomas Wouters0e3f5912006-08-11 14:57:12 +000069
70 cur.execute('select 1 as "foo baz"')
Gregory P. Smith04cecaf2009-07-04 08:32:15 +000071 self.assertEqual(cur.description[0][0], "foo baz")
Thomas Wouters0e3f5912006-08-11 14:57:12 +000072
Gerhard Häringe7ea7452008-03-29 00:45:29 +000073 def CheckStatementFinalizationOnCloseDb(self):
74 # pysqlite versions <= 2.3.3 only finalized statements in the statement
75 # cache when closing the database. statements that were still
76 # referenced in cursors weren't closed an could provoke "
77 # "OperationalError: Unable to close due to unfinalised statements".
78 con = sqlite.connect(":memory:")
79 cursors = []
80 # default statement cache size is 100
81 for i in range(105):
82 cur = con.cursor()
83 cursors.append(cur)
84 cur.execute("select 1 x union select " + str(i))
85 con.close()
86
87 def CheckOnConflictRollback(self):
88 if sqlite.sqlite_version_info < (3, 2, 2):
89 return
90 con = sqlite.connect(":memory:")
91 con.execute("create table foo(x, unique(x) on conflict rollback)")
92 con.execute("insert into foo(x) values (1)")
93 try:
94 con.execute("insert into foo(x) values (1)")
95 except sqlite.DatabaseError:
96 pass
97 con.execute("insert into foo(x) values (2)")
98 try:
99 con.commit()
100 except sqlite.OperationalError:
101 self.fail("pysqlite knew nothing about the implicit ROLLBACK")
102
103 def CheckWorkaroundForBuggySqliteTransferBindings(self):
104 """
105 pysqlite would crash with older SQLite versions unless
106 a workaround is implemented.
107 """
108 self.con.execute("create table foo(bar)")
109 self.con.execute("drop table foo")
110 self.con.execute("create table foo(bar)")
111
112 def CheckEmptyStatement(self):
113 """
114 pysqlite used to segfault with SQLite versions 3.5.x. These return NULL
115 for "no-operation" statements
116 """
117 self.con.execute("")
118
119 def CheckTypeMapUsage(self):
120 """
121 pysqlite until 2.4.1 did not rebuild the row_cast_map when recompiling
122 a statement. This test exhibits the problem.
123 """
124 SELECT = "select * from foo"
125 con = sqlite.connect(":memory:",detect_types=sqlite.PARSE_DECLTYPES)
126 con.execute("create table foo(bar timestamp)")
127 con.execute("insert into foo(bar) values (?)", (datetime.datetime.now(),))
128 con.execute(SELECT)
129 con.execute("drop table foo")
130 con.execute("create table foo(bar integer)")
131 con.execute("insert into foo(bar) values (5)")
132 con.execute(SELECT)
133
Gerhard Häring873d9ff2008-02-29 22:22:09 +0000134 def CheckErrorMsgDecodeError(self):
135 # When porting the module to Python 3.0, the error message about
136 # decoding errors disappeared. This verifies they're back again.
137 failure = None
138 try:
Georg Brandl3dbca812008-07-23 16:10:53 +0000139 self.con.execute("select 'xxx' || ? || 'yyy' colname",
140 (bytes(bytearray([250])),)).fetchone()
Gerhard Häring873d9ff2008-02-29 22:22:09 +0000141 failure = "should have raised an OperationalError with detailed description"
142 except sqlite.OperationalError as e:
143 msg = e.args[0]
144 if not msg.startswith("Could not decode to UTF-8 column 'colname' with text 'xxx"):
145 failure = "OperationalError did not have expected description text"
146 if failure:
147 self.fail(failure)
148
Georg Brandl3dbca812008-07-23 16:10:53 +0000149 def CheckRegisterAdapter(self):
150 """
151 See issue 3312.
152 """
153 self.assertRaises(TypeError, sqlite.register_adapter, {}, None)
154
155 def CheckSetIsolationLevel(self):
156 """
157 See issue 3312.
158 """
159 con = sqlite.connect(":memory:")
160 setattr(con, "isolation_level", "\xe9")
161
Gerhard Häringf9cee222010-03-05 15:20:03 +0000162 def CheckCursorConstructorCallCheck(self):
163 """
Ezio Melottib5bc3532013-08-17 16:11:40 +0300164 Verifies that cursor methods check whether base class __init__ was
165 called.
Gerhard Häringf9cee222010-03-05 15:20:03 +0000166 """
167 class Cursor(sqlite.Cursor):
168 def __init__(self, con):
169 pass
170
171 con = sqlite.connect(":memory:")
172 cur = Cursor(con)
173 try:
174 cur.execute("select 4+5").fetchall()
175 self.fail("should have raised ProgrammingError")
176 except sqlite.ProgrammingError:
177 pass
178 except:
179 self.fail("should have raised ProgrammingError")
180
181
Gerhard Häring6117f422008-09-22 06:04:51 +0000182 def CheckStrSubclass(self):
183 """
184 The Python 3.0 port of the module didn't cope with values of subclasses of str.
185 """
186 class MyStr(str): pass
187 self.con.execute("select ?", (MyStr("abc"),))
Georg Brandl3dbca812008-07-23 16:10:53 +0000188
Gerhard Häringf9cee222010-03-05 15:20:03 +0000189 def CheckConnectionConstructorCallCheck(self):
190 """
Ezio Melottib5bc3532013-08-17 16:11:40 +0300191 Verifies that connection methods check whether base class __init__ was
192 called.
Gerhard Häringf9cee222010-03-05 15:20:03 +0000193 """
194 class Connection(sqlite.Connection):
195 def __init__(self, name):
196 pass
197
198 con = Connection(":memory:")
199 try:
200 cur = con.cursor()
201 self.fail("should have raised ProgrammingError")
202 except sqlite.ProgrammingError:
203 pass
204 except:
205 self.fail("should have raised ProgrammingError")
206
207 def CheckCursorRegistration(self):
208 """
209 Verifies that subclassed cursor classes are correctly registered with
210 the connection object, too. (fetch-across-rollback problem)
211 """
212 class Connection(sqlite.Connection):
213 def cursor(self):
214 return Cursor(self)
215
216 class Cursor(sqlite.Cursor):
217 def __init__(self, con):
218 sqlite.Cursor.__init__(self, con)
219
220 con = Connection(":memory:")
221 cur = con.cursor()
222 cur.execute("create table foo(x)")
223 cur.executemany("insert into foo(x) values (?)", [(3,), (4,), (5,)])
224 cur.execute("select x from foo")
225 con.rollback()
226 try:
227 cur.fetchall()
228 self.fail("should have raised InterfaceError")
229 except sqlite.InterfaceError:
230 pass
231 except:
232 self.fail("should have raised InterfaceError")
233
234 def CheckAutoCommit(self):
235 """
236 Verifies that creating a connection in autocommit mode works.
237 2.5.3 introduced a regression so that these could no longer
238 be created.
239 """
240 con = sqlite.connect(":memory:", isolation_level=None)
241
242 def CheckPragmaAutocommit(self):
243 """
244 Verifies that running a PRAGMA statement that does an autocommit does
245 work. This did not work in 2.5.3/2.5.4.
246 """
Victor Stinner0201f442010-03-13 03:28:34 +0000247 cur = self.con.cursor()
Gerhard Häringf9cee222010-03-05 15:20:03 +0000248 cur.execute("create table foo(bar)")
249 cur.execute("insert into foo(bar) values (5)")
250
251 cur.execute("pragma page_size")
252 row = cur.fetchone()
253
254 def CheckSetDict(self):
255 """
256 See http://bugs.python.org/issue7478
257
258 It was possible to successfully register callbacks that could not be
259 hashed. Return codes of PyDict_SetItem were not checked properly.
260 """
261 class NotHashable:
262 def __call__(self, *args, **kw):
263 pass
264 def __hash__(self):
265 raise TypeError()
266 var = NotHashable()
Victor Stinner0201f442010-03-13 03:28:34 +0000267 self.assertRaises(TypeError, self.con.create_function, var)
268 self.assertRaises(TypeError, self.con.create_aggregate, var)
269 self.assertRaises(TypeError, self.con.set_authorizer, var)
270 self.assertRaises(TypeError, self.con.set_progress_handler, var)
271
272 def CheckConnectionCall(self):
273 """
274 Call a connection with a non-string SQL request: check error handling
275 of the statement constructor.
276 """
277 self.assertRaises(sqlite.Warning, self.con, 1)
Gerhard Häringf9cee222010-03-05 15:20:03 +0000278
Victor Stinner35466c52010-04-22 11:23:23 +0000279 def CheckCollation(self):
280 def collation_cb(a, b):
281 return 1
282 self.assertRaises(sqlite.ProgrammingError, self.con.create_collation,
283 # Lone surrogate cannot be encoded to the default encoding (utf8)
284 "\uDC80", collation_cb)
285
Petri Lehtinen4a84f582011-05-09 12:24:09 +0200286 def CheckRecursiveCursorUse(self):
287 """
288 http://bugs.python.org/issue10811
289
290 Recursively using a cursor, such as when reusing it from a generator led to segfaults.
291 Now we catch recursive cursor usage and raise a ProgrammingError.
292 """
293 con = sqlite.connect(":memory:")
294
295 cur = con.cursor()
296 cur.execute("create table a (bar)")
297 cur.execute("create table b (baz)")
298
299 def foo():
300 cur.execute("insert into a (bar) values (?)", (1,))
301 yield 1
302
303 with self.assertRaises(sqlite.ProgrammingError):
304 cur.executemany("insert into b (baz) values (?)",
305 ((i,) for i in foo()))
306
Petri Lehtinen8b945142013-02-23 19:05:09 +0100307 def CheckConvertTimestampMicrosecondPadding(self):
308 """
309 http://bugs.python.org/issue14720
310
311 The microsecond parsing of convert_timestamp() should pad with zeros,
312 since the microsecond string "456" actually represents "456000".
313 """
314
315 con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES)
316 cur = con.cursor()
317 cur.execute("CREATE TABLE t (x TIMESTAMP)")
Petri Lehtinen8b945142013-02-23 19:05:09 +0100318
Petri Lehtinen5f794092013-02-26 21:32:02 +0200319 # Microseconds should be 456000
320 cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.456')")
321
322 # Microseconds should be truncated to 123456
323 cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.123456789')")
324
325 cur.execute("SELECT * FROM t")
326 values = [x[0] for x in cur.fetchall()]
327
328 self.assertEqual(values, [
329 datetime.datetime(2012, 4, 4, 15, 6, 0, 456000),
330 datetime.datetime(2012, 4, 4, 15, 6, 0, 123456),
331 ])
Petri Lehtinen8b945142013-02-23 19:05:09 +0100332
Petri Lehtinen4a84f582011-05-09 12:24:09 +0200333
Thomas Wouters49fd7fa2006-04-21 10:40:58 +0000334def suite():
335 regression_suite = unittest.makeSuite(RegressionTests, "Check")
336 return unittest.TestSuite((regression_suite,))
337
338def test():
339 runner = unittest.TextTestRunner()
340 runner.run(suite())
341
342if __name__ == "__main__":
343 test()