def test_columns(self, postgresql_conn):
cur = postgresql_conn.cursor()
cur.execute('''
CREATE TABLE test1 (
id serial PRIMARY KEY,
not_null text NOT NULL,
nullable text
);
''')
cur.close()
schema = PostgresqlSchema.create_from_conn(postgresql_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 True
assert not_null_col.notnull is True
assert nullable_col.notnull is False
def schema_pg(request, postgresql_conn):
test_sql_path = os.path.join(os.path.dirname(__file__),
os.pardir, 'data', 'schema.sql')
cur = postgresql_conn.cursor()
cur.execute(open(test_sql_path).read())
cur.close()
return PostgresqlSchema.create_from_conn(postgresql_conn)
def test_columns(self, postgresql_conn):
cur = postgresql_conn.cursor()
cur.execute('''
CREATE TABLE test1 (
id serial PRIMARY KEY,
not_null text NOT NULL,
nullable text
);
''')
cur.close()
schema = PostgresqlSchema.create_from_conn(postgresql_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 True
assert not_null_col.notnull is True
assert nullable_col.notnull is False
def test_dump_relations(self, postgresql_conn):
cur = postgresql_conn.cursor()
cur.execute(self.test_relations_stmts)
cur.close()
schema = PostgresqlSchema.create_from_conn(postgresql_conn)
temp = tempfile.NamedTemporaryFile(mode='wt')
schema.dump_relations(temp)
temp.seek(0)
data = yaml.load(open(temp.name).read())
keys = [
(['fk1'], 'test2', 'test2_fk1_fkey'),
(['fk2'], 'test2', 'test_constraint'),
(['fk3'], 'test2', 'test2_fk3_fkey'),
(['fk4', 'fk5'], 'test2', 'test2_fk4_fkey'),
]
expected_data = []
for (cols, table, name) in keys:
for is_incoming in [True, False]:
row = {'table': table, 'name': name}
if len(cols) == 1:
row['column'] = cols[0]
else:
row['columns'] = cols
if is_incoming:
row['type'] = Relation.TYPE_INCOMING
expected_data.append(row)
expected_data = {'relations': expected_data}
assert data == expected_data
def test_dump_relations(self, postgresql_conn):
cur = postgresql_conn.cursor()
cur.execute(self.test_relations_stmts)
cur.close()
schema = PostgresqlSchema.create_from_conn(postgresql_conn)
temp = tempfile.NamedTemporaryFile(mode='wt')
schema.dump_relations(temp)
temp.seek(0)
data = yaml.load(open(temp.name).read())
keys = [
(['fk1'], 'test2', 'test2_fk1_fkey'),
(['fk2'], 'test2', 'test_constraint'),
(['fk3'], 'test2', 'test2_fk3_fkey'),
(['fk4', 'fk5'], 'test2', 'test2_fk4_fkey'),
]
expected_data = []
for (cols, table, name) in keys:
for is_incoming in [True, False]:
row = {'table': table, 'name': name}
if len(cols) == 1:
row['column'] = cols[0]
else:
row['columns'] = cols
if is_incoming:
row['type'] = Relation.TYPE_INCOMING
expected_data.append(row)
expected_data = {'relations': expected_data}
assert data == expected_data
def test_alternate_primary_keys(self, postgresql_conn, table, count):
cur = postgresql_conn.cursor()
for stmt in self.test_alternate_primary_keys_stmts:
cur.execute(stmt)
cur.close()
schema = PostgresqlSchema.create_from_conn(postgresql_conn)
self.check_alternate_primary_keys(schema, table, count)
def test_self_referencing_non_null_foreign_key(self, postgresql_conn):
cur = postgresql_conn.cursor()
for stmt in [
'''CREATE TABLE test1 (
id SERIAL PRIMARY KEY
);
''',
'''ALTER TABLE test1 ADD COLUMN fk INTEGER NOT NULL DEFAULT 1
REFERENCES test1''',
# Sanity test the above is even possible
'INSERT INTO test1 (id) VALUES(1);'
]:
cur.execute(stmt)
cur.close()
with pytest.raises(RelationIntegrityError):
PostgresqlSchema.create_from_conn(postgresql_conn)
def test_self_referencing_non_null_foreign_key(self, postgresql_conn):
cur = postgresql_conn.cursor()
for stmt in [
'''CREATE TABLE test1 (
id SERIAL PRIMARY KEY
);
''',
'''ALTER TABLE test1 ADD COLUMN fk INTEGER NOT NULL DEFAULT 1
REFERENCES test1''',
# Sanity test the above is even possible
'INSERT INTO test1 (id) VALUES(1);'
]:
cur.execute(stmt)
cur.close()
with pytest.raises(RelationIntegrityError):
PostgresqlSchema.create_from_conn(postgresql_conn)
def test_alternate_primary_keys(self, postgresql_conn,
table, count):
cur = postgresql_conn.cursor()
for stmt in self.test_alternate_primary_keys_stmts:
cur.execute(stmt)
cur.close()
schema = PostgresqlSchema.create_from_conn(postgresql_conn)
self.check_alternate_primary_keys(schema, table, count)
def test_compound_primary_key_constraints(self, postgresql_conn):
cur = postgresql_conn.cursor()
cur.execute('''
CREATE TABLE test1 (
id INTEGER,
name TEXT,
PRIMARY KEY(id, name)
);
''')
cur.close()
schema = PostgresqlSchema.create_from_conn(postgresql_conn)
pk = schema.tables[0].primary_key
assert pk == (schema.tables[0].cols[0], schema.tables[0].cols[1])
def test_compound_primary_key_constraints(self, postgresql_conn):
cur = postgresql_conn.cursor()
cur.execute('''
CREATE TABLE test1 (
id INTEGER,
name TEXT,
PRIMARY KEY(id, name)
);
''')
cur.close()
schema = PostgresqlSchema.create_from_conn(postgresql_conn)
pk = schema.tables[0].primary_key
assert pk == (schema.tables[0].cols[0], schema.tables[0].cols[1])
def test_primary_key_constraints(self, postgresql_conn):
cur = postgresql_conn.cursor()
cur.execute('''
CREATE TABLE test1 (id1 serial PRIMARY KEY, name text);
CREATE TABLE test2 (name text, id2 serial PRIMARY KEY);
CREATE TABLE test3 (id3 serial);
''')
cur.close()
schema = PostgresqlSchema.create_from_conn(postgresql_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_tables(self, postgresql_conn):
cur = postgresql_conn.cursor()
cur.execute('''
CREATE TABLE test1 (id serial PRIMARY KEY);
CREATE TABLE test2 (id serial PRIMARY KEY);
''')
cur.close()
schema = PostgresqlSchema.create_from_conn(postgresql_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 len(list(schema.tables_by_oid.keys())) == 2
def test_primary_key_constraints(self, postgresql_conn):
cur = postgresql_conn.cursor()
cur.execute('''
CREATE TABLE test1 (id1 serial PRIMARY KEY, name text);
CREATE TABLE test2 (name text, id2 serial PRIMARY KEY);
CREATE TABLE test3 (id3 serial);
''')
cur.close()
schema = PostgresqlSchema.create_from_conn(postgresql_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_tables(self, postgresql_conn):
cur = postgresql_conn.cursor()
cur.execute('''
CREATE TABLE test1 (id serial PRIMARY KEY);
CREATE TABLE test2 (id serial PRIMARY KEY);
''')
cur.close()
schema = PostgresqlSchema.create_from_conn(postgresql_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 len(list(schema.tables_by_oid.keys())) == 2
def test_unique_indexes(self, postgresql_conn):
cur = postgresql_conn.cursor()
cur.execute('''
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);
CREATE UNIQUE INDEX uindex3 ON test1(col3, SUBSTR(col4, 4));
''')
cur.close()
schema = PostgresqlSchema.create_from_conn(postgresql_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 test_unique_indexes(self, postgresql_conn):
cur = postgresql_conn.cursor()
cur.execute('''
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);
CREATE UNIQUE INDEX uindex3 ON test1(col3, SUBSTR(col4, 4));
''')
cur.close()
schema = PostgresqlSchema.create_from_conn(postgresql_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 test_foreign_key_constraints(self, postgresql_conn):
cur = postgresql_conn.cursor()
cur.execute(self.test_relations_stmts)
cur.close()
schema = PostgresqlSchema.create_from_conn(postgresql_conn)
table1 = schema.tables[0]
table2 = schema.tables[1]
table1_id = table1.cols[0]
table1_alt_id = table1.cols[1]
table1_alt_id1 = table1.cols[2]
table1_alt_id2 = table1.cols[3]
table2_fk1 = table2.cols[1]
table2_fk2 = table2.cols[2]
table2_fk3 = table2.cols[3]
table2_fk4 = table2.cols[4]
table2_fk5 = table2.cols[5]
assert len(table1.incoming_foreign_keys) == 4
assert len(table2.foreign_keys) == 4
fk1 = table2.foreign_keys[0]
fk2 = table2.foreign_keys[1]
fk3 = table2.foreign_keys[2]
fk4 = table2.foreign_keys[3]
assert fk1 in table1.incoming_foreign_keys
assert fk2 in table1.incoming_foreign_keys
assert fk3 in table1.incoming_foreign_keys
assert fk4 in table1.incoming_foreign_keys
assert fk1.name is not None
assert fk2.name == 'test_constraint'
assert fk3.name is not None
assert fk4.name is not None
assert fk1.src_cols == (table2_fk1,)
assert fk1.dst_cols == (table1_id,)
assert fk2.src_cols == (table2_fk2,)
assert fk2.dst_cols == (table1_id,)
assert fk3.src_cols == (table2_fk3,)
assert fk3.dst_cols == (table1_alt_id,)
assert fk4.src_cols == (table2_fk4, table2_fk5)
assert fk4.dst_cols == (table1_alt_id1, table1_alt_id2)
assert str(fk1) is not None
assert repr(fk1) is not None
def test_foreign_key_constraints(self, postgresql_conn):
cur = postgresql_conn.cursor()
cur.execute(self.test_relations_stmts)
cur.close()
schema = PostgresqlSchema.create_from_conn(postgresql_conn)
table1 = schema.tables[0]
table2 = schema.tables[1]
table1_id = table1.cols[0]
table1_alt_id = table1.cols[1]
table1_alt_id1 = table1.cols[2]
table1_alt_id2 = table1.cols[3]
table2_fk1 = table2.cols[1]
table2_fk2 = table2.cols[2]
table2_fk3 = table2.cols[3]
table2_fk4 = table2.cols[4]
table2_fk5 = table2.cols[5]
assert len(table1.incoming_foreign_keys) == 4
assert len(table2.foreign_keys) == 4
fk1 = table2.foreign_keys[0]
fk2 = table2.foreign_keys[1]
fk3 = table2.foreign_keys[2]
fk4 = table2.foreign_keys[3]
assert fk1 in table1.incoming_foreign_keys
assert fk2 in table1.incoming_foreign_keys
assert fk3 in table1.incoming_foreign_keys
assert fk4 in table1.incoming_foreign_keys
assert fk1.name is not None
assert fk2.name == 'test_constraint'
assert fk3.name is not None
assert fk4.name is not None
assert fk1.src_cols == (table2_fk1, )
assert fk1.dst_cols == (table1_id, )
assert fk2.src_cols == (table2_fk2, )
assert fk2.dst_cols == (table1_id, )
assert fk3.src_cols == (table2_fk3, )
assert fk3.dst_cols == (table1_alt_id, )
assert fk4.src_cols == (table2_fk4, table2_fk5)
assert fk4.dst_cols == (table1_alt_id1, table1_alt_id2)
assert str(fk1) is not None
assert repr(fk1) is not None