def test_load_grant_select_statements(db_str): child = load_table_details(db_str, "second.child") grant = child.load_grant_select_statements() assert grant == "" cities = load_table_details(db_str, "first.cities") grant = cities.load_grant_select_statements() assert pytest.similar(grant, "grant select on first.cities_duro_temp to jane, john") countries = load_table_details(db_str, "first.countries") grant = countries.load_grant_select_statements() assert pytest.similar( grant, "grant select on first.countries_duro_temp to joan, john" )
def test_remove_comments(): assert remove_comments( "SELECT * from schema.table") == "SELECT * from schema.table" assert remove_comments("SELECT * from --schema.table") == "SELECT * from " assert pytest.similar( remove_comments("""SELECT * from schem.table -- from schema.table"""), "SELECT * from schem.table", ) assert pytest.similar( remove_comments("""SELECT * from schema.table -- from schema.table2"""), """SELECT * from schema.table""", )
def test_get_dependencies(): get_dependencies("first", "cities", connection()) assert pytest.similar( redshift_execute.last_query, """ select distinct nv.nspname + '.' + v.relname as view_name, pg_get_viewdef(v.oid) as view_definition from pg_user, pg_namespace nv, pg_class v, pg_depend dv, pg_depend dt, pg_class t, pg_namespace nt where nv.oid = v.relnamespace and v.relkind = 'v' and v.oid = dv.refobjid and dv.refclassid = 'pg_class'::regclass::oid and dv.classid = 'pg_rewrite'::regclass::oid and dv.deptype = 'i' and dv.objid = dt.objid and dv.refobjid <> dt.refobjid and dt.classid = 'pg_rewrite'::regclass::oid and dt.refclassid = 'pg_class'::regclass::oid and dt.refobjid = t.oid and t.relnamespace = nt.oid and (t.relkind = 'r' or t.relkind = 'v') and nt.nspname = 'first' and t.relname = 'cities'; """, )
def test_find_tables_with_missing_files(views_path_with_missing_files): errors = find_tables_with_missing_files(views_path_with_missing_files) assert pytest.similar( errors, """ Some tables have tests, but not a SELECT query: first.countries, first.cities. Some processors don’t have a SELECT query: first.countries. Some processors don’t have a CREATE TABLE query: first.countries.""", )
def test_get_query_with_dist_sort_keys(db_str): child = load_table_details(db_str, "second.child") query = child.get_query_with_dist_sort_keys() assert pytest.similar( query, """ create table second.child_duro_temp distkey("city") diststyle all as (select city, country from first.cities);""", ) parent = load_table_details(db_str, "second.parent") query = parent.get_query_with_dist_sort_keys() assert pytest.similar( query, """ create table second.parent_duro_temp diststyle even as (select * from second.child limit 10);""", )
def test_get_snapshot_dates(): get_snapshot_dates("first.cities", connection()) assert pytest.similar( redshift_execute.last_query, """ select max(snapshot_timestamp), min(snapshot_timestamp) from first.cities_history """, )
def test_insert_new_snapshot_data(): insert_new_snapshot_data("first.cities", connection()) assert pytest.similar( redshift_execute.last_query, """ insert into first.cities_history select *, current_timestamp from first.cities """, )
def test_replace_old_table(): replace_old_table("first.cities", connection()) assert pytest.similar( redshift_execute.last_query, """ drop table if exists first.cities_duro_old; create table if not exists first.cities (id int); alter table first.cities rename to cities_duro_old; alter table first.cities_duro_temp rename to cities; """, )
def test_update_view(): view = "first.cities_view" definition = "select * from first.cities_duro_temp limit 20" update_view(view, definition, connection()) assert pytest.similar( redshift_execute.last_query, """ create or replace view first.cities_view as (select * from first.cities_duro_temp limit 20) """, )
def test_load_select_query(views_path): countries = load_select_query(views_path, "first.countries") assert pytest.similar( countries, """ select country, continent from first.countries_raw;""", ) child = load_select_query(views_path, "second.child") assert child == "select city, country from first.cities"
def test_load_ddl_query(views_path): ddl = load_ddl_query(views_path, "first.countries") assert pytest.similar( ddl, """create table first.countries_duro_temp( city text, country text )""", ) with pytest.raises(ValueError): load_ddl_query(views_path, "first.cities")
def test_remove_old_snapshots(table): table.snapshots_stored_for_mins = 180 remove_old_snapshots(table, connection()) assert pytest.similar( redshift_execute.last_query, """ delete from first.cities_history where datediff('mins', snapshot_timestamp::timestamp, current_timestamp::timestamp) > 180 """, )
def test_load_tests(views_path): cities_reference = """ select (city = 'Paris') as correct_capital_of_france from first.cities_duro_temp where country = 'France'; select (city = 'Ottawa') as correct_capital_of_canada from first.cities_duro_temp where country = 'Canada'; """ cities_tests = load_tests("first.cities", views_path) assert pytest.similar(cities_reference, cities_tests) countries_reference = """ select (continent = 'Europe') as correct_continent_for_france from first.countries_duro_temp where country = 'France'; """ countries_tests = load_tests("first.countries", views_path) assert pytest.similar(countries_reference, countries_tests)
def test_create_snapshots_table(): create_snapshots_table("first.cities", connection()) assert pytest.similar( redshift_execute.last_query, """ create table first.cities_history as ( select *, current_timestamp as snapshot_timestamp from first.cities limit 1 ); truncate table first.cities_history; """, )
def test_build_query_to_create_timestamps_table(): query = build_query_to_create_timestamps_table() assert pytest.similar( query, """ CREATE TABLE IF NOT EXISTS timestamps ("table" text, "start" int, "connect" int, "select" int, "create_temp" int, "process" int, "csv" int, "s3" int, "insert" int, "clean_csv" int, "tests" int, "replace_old" int, "drop_old" int, "make_snapshot" int, "finish" int) """, )
def test_load_table_from_file(views_path): name = os.path.join(views_path, "first", "cities — 24h.sql") cities = load_table_from_file(views_path, name) assert cities.filename == name assert cities.table == "first.cities" assert cities.interval == "24h" assert pytest.similar( cities.select_query, """select city, country from first.cities_raw""", ) name = os.path.join(views_path, "first", "countries — 1h.sql") cities = load_table_from_file(views_path, name) assert cities.filename == name assert cities.table == "first.countries" assert cities.interval == "1h" assert pytest.similar( cities.select_query, """select country, continent from first.countries_raw;""", )
def test_create_temp_table(table, table_without_config): create_temp_table(table, connection()) assert pytest.similar( redshift_execute.last_query, """ drop table if exists first.cities_duro_temp; create table first.cities_duro_temp distkey("continent") as(select * from first.countries); grant select on first.cities_duro_temp to user_one; """, ) create_temp_table(table_without_config, connection()) print(redshift_execute.last_query) assert pytest.similar( redshift_execute.last_query, """ drop table if exists first.cities_duro_temp; create table first.cities_duro_temp as (select * from first.countries);; """, )
def test_drop_view(): drop_view("first.cities", connection()) assert pytest.similar(redshift_execute.last_query, "drop view if exists first.cities;")
def test_drop_temp_table(): drop_temp_table("first.cities", connection()) assert pytest.similar(redshift_execute.last_query, "drop table if exists first.cities_duro_temp;")