Debian Codegraph @

Lib/test/test_sqlite3/test_dump.py

raw
1 # Author: Paul Kippes <[email protected]>
2
3 import unittest
4
5 from .util import memory_database
6 from .util import MemoryDatabaseMixin
7 from .util import requires_virtual_table
8
9
10 class DumpTests(MemoryDatabaseMixin, unittest.TestCase):
11
12 def test_table_dump(self):
13 expected_sqls = [
14 "PRAGMA foreign_keys=OFF;",
15 """CREATE TABLE "index"("index" blob);"""
16 ,
17 """INSERT INTO "index" VALUES(X'01');"""
18 ,
19 """CREATE TABLE "quoted""table"("quoted""field" text);"""
20 ,
21 """INSERT INTO "quoted""table" VALUES('quoted''value');"""
22 ,
23 "CREATE TABLE t1(id integer primary key, s1 text, " \
24 "t1_i1 integer not null, i2 integer, unique (s1), " \
25 "constraint t1_idx1 unique (i2), " \
26 "constraint t1_i1_idx1 unique (t1_i1));"
27 ,
28 "INSERT INTO \"t1\" VALUES(1,'foo',10,20);"
29 ,
30 "INSERT INTO \"t1\" VALUES(2,'foo2',30,30);"
31 ,
32 "CREATE TABLE t2(id integer, t2_i1 integer, " \
33 "t2_i2 integer, primary key (id)," \
34 "foreign key(t2_i1) references t1(t1_i1));"
35 ,
36 # Foreign key violation.
37 "INSERT INTO \"t2\" VALUES(1,2,3);"
38 ,
39 "CREATE TRIGGER trigger_1 update of t1_i1 on t1 " \
40 "begin " \
41 "update t2 set t2_i1 = new.t1_i1 where t2_i1 = old.t1_i1; " \
42 "end;"
43 ,
44 "CREATE VIEW v1 as select * from t1 left join t2 " \
45 "using (id);"
46 ]
47 [self.cu.execute(s) for s in expected_sqls]
48 i = self.cx.iterdump()
49 actual_sqls = [s for s in i]
50 expected_sqls = [
51 "PRAGMA foreign_keys=OFF;",
52 "BEGIN TRANSACTION;",
53 *expected_sqls[1:],
54 "COMMIT;",
55 ]
56 [self.assertEqual(expected_sqls[i], actual_sqls[i])
57 for i in range(len(expected_sqls))]
58
59 def test_dump_single_quote_in_identifier(self):
60 # A single quote in a table or column name must not break the dump.
61 self.cu.execute("""CREATE TABLE "a'b" ("c'd" text);""")
62 self.cu.execute("""INSERT INTO "a'b" VALUES('x''y');""")
63 expected = [
64 "BEGIN TRANSACTION;",
65 """CREATE TABLE "a'b" ("c'd" text);""",
66 """INSERT INTO "a'b" VALUES('x''y');""",
67 "COMMIT;",
68 ]
69 actual = list(self.cx.iterdump())
70 self.assertEqual(expected, actual)
71 # The dump restores into a fresh database.
72 with memory_database() as cx2:
73 cx2.executescript("".join(actual))
74 row = cx2.execute("""SELECT "c'd" FROM "a'b";""").fetchone()
75 self.assertEqual(row[0], "x'y")
76
77 def test_table_dump_filter(self):
78 all_table_sqls = [
79 """CREATE TABLE "some_table_2" ("id_1" INTEGER);""",
80 """INSERT INTO "some_table_2" VALUES(3);""",
81 """INSERT INTO "some_table_2" VALUES(4);""",
82 """CREATE TABLE "test_table_1" ("id_2" INTEGER);""",
83 """INSERT INTO "test_table_1" VALUES(1);""",
84 """INSERT INTO "test_table_1" VALUES(2);""",
85 ]
86 all_views_sqls = [
87 """CREATE VIEW "view_1" AS SELECT * FROM "some_table_2";""",
88 """CREATE VIEW "view_2" AS SELECT * FROM "test_table_1";""",
89 ]
90 # Create database structure.
91 for sql in [*all_table_sqls, *all_views_sqls]:
92 self.cu.execute(sql)
93 # %_table_% matches all tables.
94 dump_sqls = list(self.cx.iterdump(filter="%_table_%"))
95 self.assertEqual(
96 dump_sqls,
97 ["BEGIN TRANSACTION;", *all_table_sqls, "COMMIT;"],
98 )
99 # view_% matches all views.
100 dump_sqls = list(self.cx.iterdump(filter="view_%"))
101 self.assertEqual(
102 dump_sqls,
103 ["BEGIN TRANSACTION;", *all_views_sqls, "COMMIT;"],
104 )
105 # %_1 matches tables and views with the _1 suffix.
106 dump_sqls = list(self.cx.iterdump(filter="%_1"))
107 self.assertEqual(
108 dump_sqls,
109 [
110 "BEGIN TRANSACTION;",
111 """CREATE TABLE "test_table_1" ("id_2" INTEGER);""",
112 """INSERT INTO "test_table_1" VALUES(1);""",
113 """INSERT INTO "test_table_1" VALUES(2);""",
114 """CREATE VIEW "view_1" AS SELECT * FROM "some_table_2";""",
115 "COMMIT;"
116 ],
117 )
118 # some_% matches some_table_2.
119 dump_sqls = list(self.cx.iterdump(filter="some_%"))
120 self.assertEqual(
121 dump_sqls,
122 [
123 "BEGIN TRANSACTION;",
124 """CREATE TABLE "some_table_2" ("id_1" INTEGER);""",
125 """INSERT INTO "some_table_2" VALUES(3);""",
126 """INSERT INTO "some_table_2" VALUES(4);""",
127 "COMMIT;"
128 ],
129 )
130 # Only single object.
131 dump_sqls = list(self.cx.iterdump(filter="view_2"))
132 self.assertEqual(
133 dump_sqls,
134 [
135 "BEGIN TRANSACTION;",
136 """CREATE VIEW "view_2" AS SELECT * FROM "test_table_1";""",
137 "COMMIT;"
138 ],
139 )
140 # % matches all objects.
141 dump_sqls = list(self.cx.iterdump(filter="%"))
142 self.assertEqual(
143 dump_sqls,
144 ["BEGIN TRANSACTION;", *all_table_sqls, *all_views_sqls, "COMMIT;"],
145 )
146
147 def test_dump_autoincrement(self):
148 expected = [
149 'CREATE TABLE "t1" (id integer primary key autoincrement);',
150 'INSERT INTO "t1" VALUES(NULL);',
151 'CREATE TABLE "t2" (id integer primary key autoincrement);',
152 ]
153 self.cu.executescript("".join(expected))
154
155 # the NULL value should now be automatically be set to 1
156 expected[1] = expected[1].replace("NULL", "1")
157 expected.insert(0, "BEGIN TRANSACTION;")
158 expected.extend([
159 'DELETE FROM "sqlite_sequence";',
160 'INSERT INTO "sqlite_sequence" VALUES(\'t1\',1);',
161 'COMMIT;',
162 ])
163
164 actual = [stmt for stmt in self.cx.iterdump()]
165 self.assertEqual(expected, actual)
166
167 def test_dump_autoincrement_create_new_db(self):
168 self.cu.execute("BEGIN TRANSACTION")
169 self.cu.execute("CREATE TABLE t1 (id integer primary key autoincrement)")
170 self.cu.execute("CREATE TABLE t2 (id integer primary key autoincrement)")
171 self.cu.executemany("INSERT INTO t1 VALUES(?)", ((None,) for _ in range(9)))
172 self.cu.executemany("INSERT INTO t2 VALUES(?)", ((None,) for _ in range(4)))
173 self.cx.commit()
174
175 with memory_database() as cx2:
176 query = "".join(self.cx.iterdump())
177 cx2.executescript(query)
178 cu2 = cx2.cursor()
179
180 dataset = (
181 ("t1", 9),
182 ("t2", 4),
183 )
184 for table, seq in dataset:
185 with self.subTest(table=table, seq=seq):
186 res = cu2.execute("""
187 SELECT "seq" FROM "sqlite_sequence" WHERE "name" == ?
188 """, (table,))
189 rows = res.fetchall()
190 self.assertEqual(rows[0][0], seq)
191
192 def test_unorderable_row(self):
193 # iterdump() should be able to cope with unorderable row types (issue #15545)
194 class UnorderableRow:
195 def __init__(self, cursor, row):
196 self.row = row
197 def __getitem__(self, index):
198 return self.row[index]
199 self.cx.row_factory = UnorderableRow
200 CREATE_ALPHA = """CREATE TABLE "alpha" ("one");"""
201 CREATE_BETA = """CREATE TABLE "beta" ("two");"""
202 expected = [
203 "BEGIN TRANSACTION;",
204 CREATE_ALPHA,
205 CREATE_BETA,
206 "COMMIT;"
207 ]
208 self.cu.execute(CREATE_BETA)
209 self.cu.execute(CREATE_ALPHA)
210 got = list(self.cx.iterdump())
211 self.assertEqual(expected, got)
212
213 def test_dump_custom_row_factory(self):
214 # gh-118221: iterdump should be able to cope with custom row factories.
215 def dict_factory(cu, row):
216 fields = [col[0] for col in cu.description]
217 return dict(zip(fields, row))
218
219 self.cx.row_factory = dict_factory
220 CREATE_TABLE = "CREATE TABLE test(t);"
221 expected = ["BEGIN TRANSACTION;", CREATE_TABLE, "COMMIT;"]
222
223 self.cu.execute(CREATE_TABLE)
224 actual = list(self.cx.iterdump())
225 self.assertEqual(expected, actual)
226 self.assertEqual(self.cx.row_factory, dict_factory)
227
228 @requires_virtual_table("fts4")
229 def test_dump_virtual_tables(self):
230 # gh-64662
231 expected = [
232 "BEGIN TRANSACTION;",
233 "PRAGMA writable_schema=ON;",
234 ("INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"
235 "VALUES('table','test','test',0,'CREATE VIRTUAL TABLE test USING fts4(example)');"),
236 "CREATE TABLE 'test_content'(docid INTEGER PRIMARY KEY, 'c0example');",
237 "CREATE TABLE 'test_docsize'(docid INTEGER PRIMARY KEY, size BLOB);",
238 ("CREATE TABLE 'test_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,"
239 "leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx));"),
240 "CREATE TABLE 'test_segments'(blockid INTEGER PRIMARY KEY, block BLOB);",
241 "CREATE TABLE 'test_stat'(id INTEGER PRIMARY KEY, value BLOB);",
242 "PRAGMA writable_schema=OFF;",
243 "COMMIT;"
244 ]
245 self.cu.execute("CREATE VIRTUAL TABLE test USING fts4(example)")
246 actual = list(self.cx.iterdump())
247 self.assertEqual(expected, actual)
248
249
250 if __name__ == "__main__":
251 unittest.main()