| 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_table_dump_filter(self): |
| 60 |
all_table_sqls = [ |
| 61 |
"""CREATE TABLE "some_table_2" ("id_1" INTEGER);""", |
| 62 |
"""INSERT INTO "some_table_2" VALUES(3);""", |
| 63 |
"""INSERT INTO "some_table_2" VALUES(4);""", |
| 64 |
"""CREATE TABLE "test_table_1" ("id_2" INTEGER);""", |
| 65 |
"""INSERT INTO "test_table_1" VALUES(1);""", |
| 66 |
"""INSERT INTO "test_table_1" VALUES(2);""", |
| 67 |
] |
| 68 |
all_views_sqls = [ |
| 69 |
"""CREATE VIEW "view_1" AS SELECT * FROM "some_table_2";""", |
| 70 |
"""CREATE VIEW "view_2" AS SELECT * FROM "test_table_1";""", |
| 71 |
] |
| 72 |
# Create database structure. |
| 73 |
for sql in [*all_table_sqls, *all_views_sqls]: |
| 74 |
self.cu.execute(sql) |
| 75 |
# %_table_% matches all tables. |
| 76 |
dump_sqls = list(self.cx.iterdump(filter="%_table_%")) |
| 77 |
self.assertEqual( |
| 78 |
dump_sqls, |
| 79 |
["BEGIN TRANSACTION;", *all_table_sqls, "COMMIT;"], |
| 80 |
) |
| 81 |
# view_% matches all views. |
| 82 |
dump_sqls = list(self.cx.iterdump(filter="view_%")) |
| 83 |
self.assertEqual( |
| 84 |
dump_sqls, |
| 85 |
["BEGIN TRANSACTION;", *all_views_sqls, "COMMIT;"], |
| 86 |
) |
| 87 |
# %_1 matches tables and views with the _1 suffix. |
| 88 |
dump_sqls = list(self.cx.iterdump(filter="%_1")) |
| 89 |
self.assertEqual( |
| 90 |
dump_sqls, |
| 91 |
[ |
| 92 |
"BEGIN TRANSACTION;", |
| 93 |
"""CREATE TABLE "test_table_1" ("id_2" INTEGER);""", |
| 94 |
"""INSERT INTO "test_table_1" VALUES(1);""", |
| 95 |
"""INSERT INTO "test_table_1" VALUES(2);""", |
| 96 |
"""CREATE VIEW "view_1" AS SELECT * FROM "some_table_2";""", |
| 97 |
"COMMIT;" |
| 98 |
], |
| 99 |
) |
| 100 |
# some_% matches some_table_2. |
| 101 |
dump_sqls = list(self.cx.iterdump(filter="some_%")) |
| 102 |
self.assertEqual( |
| 103 |
dump_sqls, |
| 104 |
[ |
| 105 |
"BEGIN TRANSACTION;", |
| 106 |
"""CREATE TABLE "some_table_2" ("id_1" INTEGER);""", |
| 107 |
"""INSERT INTO "some_table_2" VALUES(3);""", |
| 108 |
"""INSERT INTO "some_table_2" VALUES(4);""", |
| 109 |
"COMMIT;" |
| 110 |
], |
| 111 |
) |
| 112 |
# Only single object. |
| 113 |
dump_sqls = list(self.cx.iterdump(filter="view_2")) |
| 114 |
self.assertEqual( |
| 115 |
dump_sqls, |
| 116 |
[ |
| 117 |
"BEGIN TRANSACTION;", |
| 118 |
"""CREATE VIEW "view_2" AS SELECT * FROM "test_table_1";""", |
| 119 |
"COMMIT;" |
| 120 |
], |
| 121 |
) |
| 122 |
# % matches all objects. |
| 123 |
dump_sqls = list(self.cx.iterdump(filter="%")) |
| 124 |
self.assertEqual( |
| 125 |
dump_sqls, |
| 126 |
["BEGIN TRANSACTION;", *all_table_sqls, *all_views_sqls, "COMMIT;"], |
| 127 |
) |
| 128 |
|
| 129 |
def test_dump_autoincrement(self): |
| 130 |
expected = [ |
| 131 |
'CREATE TABLE "t1" (id integer primary key autoincrement);', |
| 132 |
'INSERT INTO "t1" VALUES(NULL);', |
| 133 |
'CREATE TABLE "t2" (id integer primary key autoincrement);', |
| 134 |
] |
| 135 |
self.cu.executescript("".join(expected)) |
| 136 |
|
| 137 |
# the NULL value should now be automatically be set to 1 |
| 138 |
expected[1] = expected[1].replace("NULL", "1") |
| 139 |
expected.insert(0, "BEGIN TRANSACTION;") |
| 140 |
expected.extend([ |
| 141 |
'DELETE FROM "sqlite_sequence";', |
| 142 |
'INSERT INTO "sqlite_sequence" VALUES(\'t1\',1);', |
| 143 |
'COMMIT;', |
| 144 |
]) |
| 145 |
|
| 146 |
actual = [stmt for stmt in self.cx.iterdump()] |
| 147 |
self.assertEqual(expected, actual) |
| 148 |
|
| 149 |
def test_dump_autoincrement_create_new_db(self): |
| 150 |
self.cu.execute("BEGIN TRANSACTION") |
| 151 |
self.cu.execute("CREATE TABLE t1 (id integer primary key autoincrement)") |
| 152 |
self.cu.execute("CREATE TABLE t2 (id integer primary key autoincrement)") |
| 153 |
self.cu.executemany("INSERT INTO t1 VALUES(?)", ((None,) for _ in range(9))) |
| 154 |
self.cu.executemany("INSERT INTO t2 VALUES(?)", ((None,) for _ in range(4))) |
| 155 |
self.cx.commit() |
| 156 |
|
| 157 |
with memory_database() as cx2: |
| 158 |
query = "".join(self.cx.iterdump()) |
| 159 |
cx2.executescript(query) |
| 160 |
cu2 = cx2.cursor() |
| 161 |
|
| 162 |
dataset = ( |
| 163 |
("t1", 9), |
| 164 |
("t2", 4), |
| 165 |
) |
| 166 |
for table, seq in dataset: |
| 167 |
with self.subTest(table=table, seq=seq): |
| 168 |
res = cu2.execute(""" |
| 169 |
SELECT "seq" FROM "sqlite_sequence" WHERE "name" == ? |
| 170 |
""", (table,)) |
| 171 |
rows = res.fetchall() |
| 172 |
self.assertEqual(rows[0][0], seq) |
| 173 |
|
| 174 |
def test_unorderable_row(self): |
| 175 |
# iterdump() should be able to cope with unorderable row types (issue #15545) |
| 176 |
class UnorderableRow: |
| 177 |
def __init__(self, cursor, row): |
| 178 |
self.row = row |
| 179 |
def __getitem__(self, index): |
| 180 |
return self.row[index] |
| 181 |
self.cx.row_factory = UnorderableRow |
| 182 |
CREATE_ALPHA = """CREATE TABLE "alpha" ("one");""" |
| 183 |
CREATE_BETA = """CREATE TABLE "beta" ("two");""" |
| 184 |
expected = [ |
| 185 |
"BEGIN TRANSACTION;", |
| 186 |
CREATE_ALPHA, |
| 187 |
CREATE_BETA, |
| 188 |
"COMMIT;" |
| 189 |
] |
| 190 |
self.cu.execute(CREATE_BETA) |
| 191 |
self.cu.execute(CREATE_ALPHA) |
| 192 |
got = list(self.cx.iterdump()) |
| 193 |
self.assertEqual(expected, got) |
| 194 |
|
| 195 |
def test_dump_custom_row_factory(self): |
| 196 |
# gh-118221: iterdump should be able to cope with custom row factories. |
| 197 |
def dict_factory(cu, row): |
| 198 |
fields = [col[0] for col in cu.description] |
| 199 |
return dict(zip(fields, row)) |
| 200 |
|
| 201 |
self.cx.row_factory = dict_factory |
| 202 |
CREATE_TABLE = "CREATE TABLE test(t);" |
| 203 |
expected = ["BEGIN TRANSACTION;", CREATE_TABLE, "COMMIT;"] |
| 204 |
|
| 205 |
self.cu.execute(CREATE_TABLE) |
| 206 |
actual = list(self.cx.iterdump()) |
| 207 |
self.assertEqual(expected, actual) |
| 208 |
self.assertEqual(self.cx.row_factory, dict_factory) |
| 209 |
|
| 210 |
@requires_virtual_table("fts4") |
| 211 |
def test_dump_virtual_tables(self): |
| 212 |
# gh-64662 |
| 213 |
expected = [ |
| 214 |
"BEGIN TRANSACTION;", |
| 215 |
"PRAGMA writable_schema=ON;", |
| 216 |
("INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)" |
| 217 |
"VALUES('table','test','test',0,'CREATE VIRTUAL TABLE test USING fts4(example)');"), |
| 218 |
"CREATE TABLE 'test_content'(docid INTEGER PRIMARY KEY, 'c0example');", |
| 219 |
"CREATE TABLE 'test_docsize'(docid INTEGER PRIMARY KEY, size BLOB);", |
| 220 |
("CREATE TABLE 'test_segdir'(level INTEGER,idx INTEGER,start_block INTEGER," |
| 221 |
"leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx));"), |
| 222 |
"CREATE TABLE 'test_segments'(blockid INTEGER PRIMARY KEY, block BLOB);", |
| 223 |
"CREATE TABLE 'test_stat'(id INTEGER PRIMARY KEY, value BLOB);", |
| 224 |
"PRAGMA writable_schema=OFF;", |
| 225 |
"COMMIT;" |
| 226 |
] |
| 227 |
self.cu.execute("CREATE VIRTUAL TABLE test USING fts4(example)") |
| 228 |
actual = list(self.cx.iterdump()) |
| 229 |
self.assertEqual(expected, actual) |
| 230 |
|
| 231 |
|
| 232 |
if __name__ == "__main__": |
| 233 |
unittest.main() |