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;")
