def test_trigger_primary_key_function(self, connection): tables = { 'book': ['isbn'], 'publisher': ['id'], 'book_language': ['id'], 'author': ['id'], 'language': ['id'], 'subject': ['id'], 'city': ['id'], 'country': ['id'], 'continent': ['id'], } pg_base = Base(connection.engine.url.database) for table_name, primary_keys in tables.items(): query = ( f"SELECT ARRAY_AGG(attname) " f"FROM pg_index " f"JOIN pg_attribute ON attrelid = indrelid AND attnum = ANY(indkey) " f"WHERE indrelid = '{table_name}'::regclass AND indisprimary") rows = pg_base.query(query)[0] assert list(rows)[0] == primary_keys
def test_trigger_foreign_key_function(self, connection): tables = { 'book': ['publisher_id'], 'publisher': None, 'book_language': ['book_isbn', 'language_id'], 'author': ['city_id'], 'language': None, 'subject': None, 'city': ['country_id'], 'country': ['continent_id'], 'continent': None, } pg_base = Base(connection.engine.url.database) for table_name, foreign_keys in tables.items(): query = ( f"SELECT ARRAY_AGG(column_name::TEXT) FROM information_schema.key_column_usage " f"WHERE constraint_catalog=current_catalog AND " f"table_name='{table_name}' AND position_in_unique_constraint NOTNULL " ) rows = pg_base.query(query)[0] assert rows[0] == foreign_keys