def test_non_existent_foreign_key_unknown_table(self, sqlite_conn):
for stmt in [
'''CREATE TABLE test1 (
id SERIAL PRIMARY KEY
);''',
'''CREATE TABLE test2 (
id SERIAL PRIMARY KEY,
fk1 INTEGER,
FOREIGN KEY(fk1) REFERENCES foo(id)
);''']:
sqlite_conn.execute(stmt)
with pytest.raises(UnknownTableError):
SqliteSchema.create_from_conn(sqlite_conn)
def test_non_existent_foreign_key_unknown_table(self, sqlite_conn):
for stmt in [
'''CREATE TABLE test1 (
id SERIAL PRIMARY KEY
);''', '''CREATE TABLE test2 (
id SERIAL PRIMARY KEY,
fk1 INTEGER,
FOREIGN KEY(fk1) REFERENCES foo(id)
);'''
]:
sqlite_conn.execute(stmt)
with pytest.raises(UnknownTableError):
SqliteSchema.create_from_conn(sqlite_conn)
def test_set_effective_primary_key(self, sqlite_conn):
for stmt in [
'''
CREATE TABLE test1 (
id SERIAL PRIMARY KEY,
something_else INTEGER
);
''', '''
CREATE TABLE test2 (
id INTEGER UNIQUE,
something_else INTEGER
);
''', '''
CREATE TABLE test3 (
id1 INTEGER,
id2 INTEGER
);'''
]:
sqlite_conn.execute(stmt)
schema = SqliteSchema.create_from_conn(sqlite_conn)
tables = schema.tables
assert tables[0].effective_primary_key == (tables[0].cols[0], )
assert tables[0].can_have_duplicated_rows is False
assert tables[1].effective_primary_key == (tables[1].cols[0], )
assert tables[1].can_have_duplicated_rows is False
assert tables[2].effective_primary_key == (tables[2].cols[0],
tables[2].cols[1])
assert tables[2].can_have_duplicated_rows is True
def test_cols(self, sqlite_conn):
sqlite_conn.execute('''
CREATE TABLE test1 (
id INTEGER PRIMARY KEY,
not_null text NOT NULL,
nullable text
);
''')
schema = SqliteSchema.create_from_conn(sqlite_conn)
assert len(schema.tables) == 1
table = schema.tables[0]
assert len(table.cols) == 3
assert len(list(table.cols_by_name.keys())) == 3
assert str(table.cols[0]) is not None
assert repr(table.cols[0]) is not None
names = [c.name for c in table.cols]
assert 'id' in names
assert 'not_null' in names
assert 'nullable' in names
id_col = list(filter(lambda r: r.name == 'id', table.cols))[0]
not_null_col = list(filter(lambda r: r.name == 'not_null',
table.cols))[0]
nullable_col = list(filter(lambda r: r.name == 'nullable',
table.cols))[0]
assert id_col.notnull is False
assert not_null_col.notnull is True
assert nullable_col.notnull is False
def test_foreign_key_constraints(self, sqlite_conn):
for stmt in self.test_relations_stmts:
sqlite_conn.execute(stmt)
schema = SqliteSchema.create_from_conn(sqlite_conn)
table1 = schema.tables[0]
table2 = schema.tables[1]
table1_id = table1.cols[0]
table1_alt_id = table1.cols[1]
assert len(table1.cols) == 2
assert len(table2.cols) == 14
assert len(table1.incoming_foreign_keys) == 13
assert len(table2.foreign_keys) == 13
fks_by_col = {}
for i in range(1, 14):
for fk in table2.foreign_keys:
assert len(fk.src_cols) == 1
assert len(fk.dst_cols) == 1
fks_by_col[fk.src_cols[0]] = fk
for i in range(1, 14):
assert table2.cols[i] in fks_by_col
assert fk in table1.incoming_foreign_keys
assert str(fk) is not None
assert repr(fk) is not None
if fk.src_cols[0].name in ('fk10', 'fk11'):
assert fk.name == 'test_%s' % fk.src_cols[0].name
if fk.src_cols[0].name == 'fk5':
assert fk.dst_cols == (table1_alt_id, )
else:
assert fk.dst_cols == (table1_id, )
def schema_out(self):
'''
test1 -> sticky -> test3 <- test2
-> non_sticky -> test3 <- test2
'''
for stmt in [
'''
CREATE TABLE non_sticky (
id INTEGER PRIMARY KEY,
test3_id INTEGER REFERENCES test3
);
''', '''
CREATE TABLE sticky (
id INTEGER PRIMARY KEY,
test3_id INTEGER REFERENCES test3
);
''', '''
CREATE TABLE test1 (
id INTEGER PRIMARY KEY,
sticky INTEGER REFERENCES sticky,
non_sticky INTEGER REFERENCES non_sticky
);
''', '''
CREATE TABLE test2 (
id INTEGER PRIMARY KEY,
test3_id INTEGER REFERENCES test3
);
''', '''
CREATE TABLE test3 (
id INTEGER PRIMARY KEY
);
''',
]:
self.database.execute(stmt)
return SqliteSchema.create_from_conn(self.database.connection)
def test_set_effective_primary_key(self, sqlite_conn):
for stmt in [
'''
CREATE TABLE test1 (
id SERIAL PRIMARY KEY,
something_else INTEGER
);
''', '''
CREATE TABLE test2 (
id INTEGER UNIQUE,
something_else INTEGER
);
''', '''
CREATE TABLE test3 (
id1 INTEGER,
id2 INTEGER
);'''
]:
sqlite_conn.execute(stmt)
schema = SqliteSchema.create_from_conn(sqlite_conn)
tables = schema.tables
assert tables[0].effective_primary_key == (tables[0].cols[0],)
assert tables[0].can_have_duplicated_rows is False
assert tables[1].effective_primary_key == (tables[1].cols[0],)
assert tables[1].can_have_duplicated_rows is False
assert tables[2].effective_primary_key == (tables[2].cols[0],
tables[2].cols[1])
assert tables[2].can_have_duplicated_rows is True
def test_cols(self, sqlite_conn):
sqlite_conn.execute('''
CREATE TABLE test1 (
id INTEGER PRIMARY KEY,
not_null text NOT NULL,
nullable text
);
''')
schema = SqliteSchema.create_from_conn(sqlite_conn)
assert len(schema.tables) == 1
table = schema.tables[0]
assert len(table.cols) == 3
assert len(list(table.cols_by_name.keys())) == 3
assert str(table.cols[0]) is not None
assert repr(table.cols[0]) is not None
names = [c.name for c in table.cols]
assert 'id' in names
assert 'not_null' in names
assert 'nullable' in names
id_col = list(
filter(lambda r: r.name == 'id', table.cols))[0]
not_null_col = list(
filter(lambda r: r.name == 'not_null', table.cols))[0]
nullable_col = list(
filter(lambda r: r.name == 'nullable', table.cols))[0]
assert id_col.notnull is False
assert not_null_col.notnull is True
assert nullable_col.notnull is False
def test_foreign_key_constraints(self, sqlite_conn):
for stmt in self.test_relations_stmts:
sqlite_conn.execute(stmt)
schema = SqliteSchema.create_from_conn(sqlite_conn)
table1 = schema.tables[0]
table2 = schema.tables[1]
table1_id = table1.cols[0]
table1_alt_id = table1.cols[1]
assert len(table1.cols) == 2
assert len(table2.cols) == 14
assert len(table1.incoming_foreign_keys) == 13
assert len(table2.foreign_keys) == 13
fks_by_col = {}
for i in range(1, 14):
for fk in table2.foreign_keys:
assert len(fk.src_cols) == 1
assert len(fk.dst_cols) == 1
fks_by_col[fk.src_cols[0]] = fk
for i in range(1, 14):
assert table2.cols[i] in fks_by_col
assert fk in table1.incoming_foreign_keys
assert str(fk) is not None
assert repr(fk) is not None
if fk.src_cols[0].name in ('fk10', 'fk11'):
assert fk.name == 'test_%s' % fk.src_cols[0].name
if fk.src_cols[0].name == 'fk5':
assert fk.dst_cols == (table1_alt_id,)
else:
assert fk.dst_cols == (table1_id,)
def schema1(self):
for stmt in [
'''
CREATE TABLE test1 (
id INTEGER PRIMARY KEY
);
''',
'''
CREATE TABLE test2 (
id INTEGER PRIMARY KEY,
test1_id INTEGER REFERENCES test1
);
''',
'''
CREATE TABLE test3 (
id INTEGER PRIMARY KEY,
test2_id INTEGER REFERENCES test2
);
''',
'''
CREATE TABLE test4 (
id INTEGER PRIMARY KEY,
test1_id INTEGER REFERENCES test1,
test2_id INTEGER REFERENCES test2
);
''',
]:
self.database.execute(stmt)
return SqliteSchema.create_from_conn(self.database.connection)
def schema1(self):
for stmt in [
'''CREATE TABLE test1 (
id INTEGER PRIMARY KEY,
test1_id INTEGER REFERENCES test1
);''',
]:
self.database.execute(stmt)
return SqliteSchema.create_from_conn(self.database.connection)
def test_non_primary_key_table(self, schema, data):
for stmt in schema:
self.database.execute(stmt)
schema = SqliteSchema.create_from_conn(self.database.connection)
for i, datum in enumerate(data):
(table_name, row_values) = datum
table = schema.tables_by_name[table_name]
data[i] = (table, row_values)
self.database.insert_rows(data)
self.check_one_subject(schema, [{'table': 'test1'}], data,
global_relations=self.REL_EVERYTHING)
def schema7(self):
for stmt in [
# No primary key, no unique indexes
'''CREATE TABLE test1 (id INTEGER);''',
# No primary key, with a unique index
'''CREATE TABLE test2 (id INTEGER UNIQUE);''',
]:
self.database.execute(stmt)
return SqliteSchema.create_from_conn(self.database.connection)
def schema7(self):
for stmt in [
# No primary key, no unique indexes
'''CREATE TABLE test1 (id INTEGER);''',
# No primary key, with a unique index
'''CREATE TABLE test2 (id INTEGER UNIQUE);''',
]:
self.database.execute(stmt)
return SqliteSchema.create_from_conn(self.database.connection)
def test_tables(self, sqlite_conn):
sqlite_conn.execute('CREATE TABLE test1 (id INTEGER PRIMARY KEY);')
sqlite_conn.execute('CREATE TABLE test2 (id INTEGER PRIMARY KEY);')
schema = SqliteSchema.create_from_conn(sqlite_conn)
assert 'test1' in schema.tables_by_name
assert 'test2' in schema.tables_by_name
assert len(schema.tables) == 2
assert len(list(schema.tables_by_name.keys())) == 2
assert str(schema.tables[0]) is not None
assert repr(schema.tables[0]) is not None
def test_tables(self, sqlite_conn):
sqlite_conn.execute('CREATE TABLE test1 (id INTEGER PRIMARY KEY);')
sqlite_conn.execute('CREATE TABLE test2 (id INTEGER PRIMARY KEY);')
schema = SqliteSchema.create_from_conn(sqlite_conn)
assert 'test1' in schema.tables_by_name
assert 'test2' in schema.tables_by_name
assert len(schema.tables) == 2
assert len(list(schema.tables_by_name.keys())) == 2
assert str(schema.tables[0]) is not None
assert repr(schema.tables[0]) is not None
def test_compound_primary_key_constraints(self, sqlite_conn):
sqlite_conn.execute('''
CREATE TABLE test1 (
id INTEGER,
name TEXT,
PRIMARY KEY(id, name)
);
''')
schema = SqliteSchema.create_from_conn(sqlite_conn)
pk = schema.tables[0].primary_key
assert pk == (schema.tables[0].cols[0], schema.tables[0].cols[1],)
def test_self_referencing_non_null_foreign_key(self, sqlite_conn):
for stmt in [
'''CREATE TABLE test1 (
id SERIAL PRIMARY KEY
);
''',
# Work around SQL lite not liking
# NOT NULL foreign keys with a default, but disabling foreign
# key checking
'PRAGMA foreign_keys = 0;',
'''ALTER TABLE test1 ADD COLUMN fk INTEGER NOT NULL DEFAULT 1
REFERENCES test1''',
'PRAGMA foreign_keys = 1;',
# Sanity test the above is even possible
'INSERT INTO test1 (id) VALUES(1);'
]:
sqlite_conn.execute(stmt)
with pytest.raises(RelationIntegrityError):
SqliteSchema.create_from_conn(sqlite_conn)
def test_self_referencing_non_null_foreign_key(self, sqlite_conn):
for stmt in [
'''CREATE TABLE test1 (
id SERIAL PRIMARY KEY
);
''',
# Work around SQL lite not liking
# NOT NULL foreign keys with a default, but disabling foreign
# key checking
'PRAGMA foreign_keys = 0;',
'''ALTER TABLE test1 ADD COLUMN fk INTEGER NOT NULL DEFAULT 1
REFERENCES test1''',
'PRAGMA foreign_keys = 1;',
# Sanity test the above is even possible
'INSERT INTO test1 (id) VALUES(1);'
]:
sqlite_conn.execute(stmt)
with pytest.raises(RelationIntegrityError):
SqliteSchema.create_from_conn(sqlite_conn)
def schema2(self):
for stmt in [
'''CREATE TABLE managers (
id INTEGER PRIMARY KEY,
staff_id INTEGER NOT NULL REFERENCES staff
);''',
'''CREATE TABLE staff (
id INTEGER PRIMARY KEY,
manager_id INTEGER REFERENCES managers
);''',
]:
self.database.execute(stmt)
return SqliteSchema.create_from_conn(self.database.connection)
def schema2(self):
for stmt in [
'''CREATE TABLE managers (
id INTEGER PRIMARY KEY,
staff_id INTEGER NOT NULL REFERENCES staff
);''',
'''CREATE TABLE staff (
id INTEGER PRIMARY KEY,
manager_id INTEGER REFERENCES managers
);''',
]:
self.database.execute(stmt)
return SqliteSchema.create_from_conn(self.database.connection)
def test_non_primary_key_table(self, schema, data):
for stmt in schema:
self.database.execute(stmt)
schema = SqliteSchema.create_from_conn(self.database.connection)
for i, datum in enumerate(data):
(table_name, row_values) = datum
table = schema.tables_by_name[table_name]
data[i] = (table, row_values)
self.database.insert_rows(data)
self.check_one_subject(schema, [{
'table': 'test1'
}],
data,
global_relations=self.REL_EVERYTHING)
def schema1(self):
for stmt in [
'''CREATE TABLE test1 (
id INTEGER PRIMARY KEY,
name TEXT
);''',
'''CREATE TABLE test2 (
id INTEGER PRIMARY KEY,
name TEXT
);''',
]:
self.database.execute(stmt)
return SqliteSchema.create_from_conn(self.database.connection)
def test_primary_key_constraints(self, sqlite_conn):
stmts = [
'CREATE TABLE test1 (id1 INTEGER PRIMARY KEY, name text);',
'CREATE TABLE test2 (name text, id2 INTEGER PRIMARY KEY);',
'CREATE TABLE test3 (id3 INTEGER);',
]
for stmt in stmts:
sqlite_conn.execute(stmt)
schema = SqliteSchema.create_from_conn(sqlite_conn)
assert schema.tables[0].primary_key == (schema.tables[0].cols[0], )
assert schema.tables[1].primary_key == (schema.tables[1].cols[1], )
assert schema.tables[2].primary_key is None
def test_primary_key_constraints(self, sqlite_conn):
stmts = [
'CREATE TABLE test1 (id1 INTEGER PRIMARY KEY, name text);',
'CREATE TABLE test2 (name text, id2 INTEGER PRIMARY KEY);',
'CREATE TABLE test3 (id3 INTEGER);',
]
for stmt in stmts:
sqlite_conn.execute(stmt)
schema = SqliteSchema.create_from_conn(sqlite_conn)
assert schema.tables[0].primary_key == (schema.tables[0].cols[0],)
assert schema.tables[1].primary_key == (schema.tables[1].cols[1],)
assert schema.tables[2].primary_key is None
def schema3(self):
for stmt in [
'''CREATE TABLE test1 (
id INTEGER PRIMARY KEY,
name TEXT,
test2_id INTEGER NOT NULL REFERENCES test2
);''',
'''CREATE TABLE test2 (
id INTEGER PRIMARY KEY,
name TEXT
);''',
]:
self.database.execute(stmt)
return SqliteSchema.create_from_conn(self.database.connection)
def test_compound_primary_key_constraints(self, sqlite_conn):
sqlite_conn.execute('''
CREATE TABLE test1 (
id INTEGER,
name TEXT,
PRIMARY KEY(id, name)
);
''')
schema = SqliteSchema.create_from_conn(sqlite_conn)
pk = schema.tables[0].primary_key
assert pk == (
schema.tables[0].cols[0],
schema.tables[0].cols[1],
)
def test_compound_foreign_key_constraints(self, sqlite_conn):
for stmt in [
'''CREATE TABLE test1 (
id1 INTEGER,
id2 INTEGER,
id3 INTEGER,
id4 INTEGER,
PRIMARY KEY(id1, id2),
UNIQUE(id3, id4)
);''', '''CREATE TABLE test2 (
fk1 INTEGER,
fk2 INTEGER,
fk3 INTEGER,
fk4 INTEGER,
fk5 INTEGER,
fk6 INTEGER,
FOREIGN KEY(fk1, fk2) REFERENCES test1,
FOREIGN KEY(fk3, fk4) REFERENCES test1(id1, id2)
FOREIGN KEY(fk5, fk6) REFERENCES test1(id3, id4)
);'''
]:
sqlite_conn.execute(stmt)
schema = SqliteSchema.create_from_conn(sqlite_conn)
table1 = schema.tables[0]
table2 = schema.tables[1]
assert len(table1.incoming_foreign_keys) == 3
assert len(table2.foreign_keys) == 3
fks_by_col = {}
for i in range(1, 3):
for fk in table2.foreign_keys:
assert len(fk.src_cols) == 2
assert len(fk.dst_cols) == 2
fks_by_col[(fk.src_cols[0], fk.src_cols[1])] = fk
expected_cols = [
# table2 col indexes -> table1 col indexes
((0, 1), (0, 1)),
((2, 3), (0, 1)),
((4, 5), (2, 3)),
]
for (table2_cols, table1_cols) in expected_cols:
(t11, t12) = table1_cols
(t21, t22) = table2_cols
fk = fks_by_col[(table2.cols[t21], table2.cols[t22])]
assert fk.dst_cols == (table1.cols[t11], table1.cols[t12])
def test_compound_foreign_key_constraints(self, sqlite_conn):
for stmt in [
'''CREATE TABLE test1 (
id1 INTEGER,
id2 INTEGER,
id3 INTEGER,
id4 INTEGER,
PRIMARY KEY(id1, id2),
UNIQUE(id3, id4)
);''',
'''CREATE TABLE test2 (
fk1 INTEGER,
fk2 INTEGER,
fk3 INTEGER,
fk4 INTEGER,
fk5 INTEGER,
fk6 INTEGER,
FOREIGN KEY(fk1, fk2) REFERENCES test1,
FOREIGN KEY(fk3, fk4) REFERENCES test1(id1, id2)
FOREIGN KEY(fk5, fk6) REFERENCES test1(id3, id4)
);''']:
sqlite_conn.execute(stmt)
schema = SqliteSchema.create_from_conn(sqlite_conn)
table1 = schema.tables[0]
table2 = schema.tables[1]
assert len(table1.incoming_foreign_keys) == 3
assert len(table2.foreign_keys) == 3
fks_by_col = {}
for i in range(1, 3):
for fk in table2.foreign_keys:
assert len(fk.src_cols) == 2
assert len(fk.dst_cols) == 2
fks_by_col[(fk.src_cols[0], fk.src_cols[1])] = fk
expected_cols = [
# table2 col indexes -> table1 col indexes
((0, 1), (0, 1)),
((2, 3), (0, 1)),
((4, 5), (2, 3)),
]
for (table2_cols, table1_cols) in expected_cols:
(t11, t12) = table1_cols
(t21, t22) = table2_cols
fk = fks_by_col[(table2.cols[t21], table2.cols[t22])]
assert fk.dst_cols == (table1.cols[t11], table1.cols[t12])
def test_unique_indexes(self, sqlite_conn):
for stmt in [
'''CREATE TABLE test1 (
id SERIAL PRIMARY KEY,
col1 TEXT UNIQUE,
col2 TEXT UNIQUE,
col3 TEXT UNIQUE,
col4 TEXT UNIQUE,
UNIQUE(col1, col2)
);
''',
'CREATE INDEX index1 ON test1(col1, col2);',
'CREATE UNIQUE INDEX uindex1 ON test1(col1, col2);',
'CREATE UNIQUE INDEX uindex2 ON test1(col3, col4);'
]:
sqlite_conn.execute(stmt)
schema = SqliteSchema.create_from_conn(sqlite_conn)
assert len(schema.tables[0].unique_indexes) == 8 # including PK
tuples = set()
named_tuples = set()
for ui in schema.tables[0].unique_indexes:
assert str(ui) is not None
assert repr(ui) is not None
col_names = sorted([c.name for c in ui.cols])
named_tuple_list = list(col_names)
named_tuple_list.insert(0, ui.name)
named_tuples.add(tuple(named_tuple_list))
tuples.add(tuple(col_names))
assert ('uindex1', 'col1', 'col2') in named_tuples
assert ('uindex2', 'col3', 'col4') in named_tuples
# One assertion for each index
assert('id',) in tuples
assert('col1',) in tuples
assert('col2',) in tuples
assert('col3',) in tuples
assert('col4',) in tuples
assert('col1', 'col2') in tuples
assert('col3', 'col4') in tuples
# Just because you're paranoid doesn't mean they aren't after you
assert('col1', 'col3') not in tuples
assert('col1', 'col4') not in tuples
assert('col2', 'col3') not in tuples
assert('col2', 'col4') not in tuples
def schema3(self):
for stmt in [
'''
CREATE TABLE test1 (
id INTEGER PRIMARY KEY,
alt_id INTEGER UNIQUE
);
''',
'''
CREATE TABLE test2 (
id INTEGER PRIMARY KEY,
alt_test1_id INTEGER NOT NULL REFERENCES test1(alt_id)
);
''',
]:
self.database.execute(stmt)
return SqliteSchema.create_from_conn(self.database.connection)
def test_unique_indexes(self, sqlite_conn):
for stmt in [
'''CREATE TABLE test1 (
id SERIAL PRIMARY KEY,
col1 TEXT UNIQUE,
col2 TEXT UNIQUE,
col3 TEXT UNIQUE,
col4 TEXT UNIQUE,
UNIQUE(col1, col2)
);
''', 'CREATE INDEX index1 ON test1(col1, col2);',
'CREATE UNIQUE INDEX uindex1 ON test1(col1, col2);',
'CREATE UNIQUE INDEX uindex2 ON test1(col3, col4);'
]:
sqlite_conn.execute(stmt)
schema = SqliteSchema.create_from_conn(sqlite_conn)
assert len(schema.tables[0].unique_indexes) == 8 # including PK
tuples = set()
named_tuples = set()
for ui in schema.tables[0].unique_indexes:
assert str(ui) is not None
assert repr(ui) is not None
col_names = sorted([c.name for c in ui.cols])
named_tuple_list = list(col_names)
named_tuple_list.insert(0, ui.name)
named_tuples.add(tuple(named_tuple_list))
tuples.add(tuple(col_names))
assert ('uindex1', 'col1', 'col2') in named_tuples
assert ('uindex2', 'col3', 'col4') in named_tuples
# One assertion for each index
assert ('id', ) in tuples
assert ('col1', ) in tuples
assert ('col2', ) in tuples
assert ('col3', ) in tuples
assert ('col4', ) in tuples
assert ('col1', 'col2') in tuples
assert ('col3', 'col4') in tuples
# Just because you're paranoid doesn't mean they aren't after you
assert ('col1', 'col3') not in tuples
assert ('col1', 'col4') not in tuples
assert ('col2', 'col3') not in tuples
assert ('col2', 'col4') not in tuples
def schema4(self):
for stmt in [
'''CREATE TABLE test1 (
id INTEGER PRIMARY KEY,
name TEXT,
test2_id INTEGER NOT NULL REFERENCES test2
);''',
'''CREATE TABLE test2 (
id INTEGER PRIMARY KEY,
name TEXT,
test3_id INTEGER NOT NULL REFERENCES test3
);''',
'''CREATE TABLE test3 (
id INTEGER PRIMARY KEY,
name TEXT
);''',
]:
self.database.execute(stmt)
return SqliteSchema.create_from_conn(self.database.connection)
def schema1(self):
for stmt in [
'''
CREATE TABLE test1 (
id1 INTEGER,
id2 INTEGER,
PRIMARY KEY(id1, id2)
);
''', '''
CREATE TABLE test2 (
id INTEGER PRIMARY KEY,
test1_id INTEGER,
test2_id INTEGER,
FOREIGN KEY(test1_id, test2_id) REFERENCES test1
);
''',
]:
self.database.execute(stmt)
return SqliteSchema.create_from_conn(self.database.connection)
def schema1(self):
for stmt in [
'''
CREATE TABLE test1 (
id1 INTEGER,
id2 INTEGER,
PRIMARY KEY(id1, id2)
);
''',
'''
CREATE TABLE test2 (
id INTEGER PRIMARY KEY,
test1_id INTEGER,
test2_id INTEGER,
FOREIGN KEY(test1_id, test2_id) REFERENCES test1
);
''',
]:
self.database.execute(stmt)
return SqliteSchema.create_from_conn(self.database.connection)
def schema_out(self):
'''
test1 -> sticky -> test3 <- test2
-> non_sticky -> test3 <- test2
'''
for stmt in [
'''
CREATE TABLE non_sticky (
id INTEGER PRIMARY KEY,
test3_id INTEGER REFERENCES test3
);
''',
'''
CREATE TABLE sticky (
id INTEGER PRIMARY KEY,
test3_id INTEGER REFERENCES test3
);
''',
'''
CREATE TABLE test1 (
id INTEGER PRIMARY KEY,
sticky INTEGER REFERENCES sticky,
non_sticky INTEGER REFERENCES non_sticky
);
''',
'''
CREATE TABLE test2 (
id INTEGER PRIMARY KEY,
test3_id INTEGER REFERENCES test3
);
''',
'''
CREATE TABLE test3 (
id INTEGER PRIMARY KEY
);
''',
]:
self.database.execute(stmt)
return SqliteSchema.create_from_conn(self.database.connection)
def schema6(self):
for stmt in [
'''CREATE TABLE test1 (
id INTEGER PRIMARY KEY,
test2_id INTEGER NOT NULL REFERENCES test2
);''',
'''CREATE TABLE test2 (
id INTEGER PRIMARY KEY,
test1_id INTEGER REFERENCES test1,
test3_id INTEGER NOT NULL REFERENCES test3
);''',
'''CREATE TABLE test3 (
id INTEGER PRIMARY KEY,
test1_id INTEGER REFERENCES test1
);''',
'''CREATE TABLE test4 (
id INTEGER PRIMARY KEY,
test4_id INTEGER REFERENCES test4
);''',
]:
self.database.execute(stmt)
return SqliteSchema.create_from_conn(self.database.connection)
def schema1(self):
self.database.execute('''
CREATE TABLE test1 (id INTEGER PRIMARY KEY, name TEXT);
''')
return SqliteSchema.create_from_conn(self.database.connection)
def schema_sl(request, sqlite_conn):
test_sql_path = os.path.join(os.path.dirname(__file__), os.pardir, 'data',
'schema.sql')
for statement in open(test_sql_path).read().split(';'):
sqlite_conn.execute(statement)
return SqliteSchema.create_from_conn(sqlite_conn)
def test_schema_sqlite_alternate_primary_keys(self, sqlite_conn, table,
count):
for stmt in self.test_alternate_primary_keys_stmts:
sqlite_conn.execute(stmt)
schema = SqliteSchema.create_from_conn(sqlite_conn)
self.check_alternate_primary_keys(schema, table, count)