def test_sliding_windows(pipeline, clean_db):
"""
Verify that sliding window queries are properly dumped and restored
"""
pipeline.create_stream('stream0', x='int')
pipeline.execute(
'CREATE CONTINUOUS VIEW sw_v WITH (sw = \'20 seconds\') AS SELECT count(*) FROM stream0'
)
pipeline.insert('stream0', ('x', ), [(x, ) for x in range(10)])
result = pipeline.execute('SELECT count FROM sw_v').first()
assert result['count'] == 10
_dump(pipeline, 'test_sw.sql')
pipeline.drop_all()
_restore(pipeline, 'test_sw.sql')
result = pipeline.execute('SELECT count FROM sw_v').first()
assert result['count'] == 10
# We should still drop back to 0 within 20 seconds
result = pipeline.execute('SELECT count FROM sw_v').first()
while result['count'] > 0:
time.sleep(1)
result = pipeline.execute('SELECT count FROM sw_v').first()
result = pipeline.execute('SELECT count FROM sw_v').first()
assert result['count'] == 0
def test_single_continuous_view(pipeline, clean_db):
"""
Verify that specific continuous views can be dropped and restored
"""
pipeline.create_stream('stream0', x='int')
pipeline.create_cv('test_single0', 'SELECT COUNT(*) FROM stream0')
pipeline.create_cv('test_single1', 'SELECT COUNT(*) FROM stream0')
pipeline.insert('stream0', ('x', ), [(x, ) for x in range(10)])
result = pipeline.execute('SELECT count FROM test_single0').first()
assert result['count'] == 10
result = pipeline.execute('SELECT count FROM test_single1').first()
assert result['count'] == 10
_dump(pipeline,
'test_single.sql',
tables=['test_single0', 'stream0', 'test_single0_mrel'])
pipeline.drop_all()
_restore(pipeline, 'test_single.sql')
result = pipeline.execute('SELECT count FROM test_single0').first()
assert result['count'] == 10
# We didn't dump this one
result = list(
pipeline.execute(
'SELECT * FROM pg_class WHERE relname LIKE \'%%test_single1%%\''))
assert not result
def test_sliding_windows(pipeline, clean_db):
"""
Verify that sliding window queries are properly dumped and restored
"""
pipeline.create_stream('stream0', x='int')
pipeline.execute('CREATE CONTINUOUS VIEW sw_v WITH (sw = \'20 seconds\') AS SELECT count(*) FROM stream0')
pipeline.insert('stream0', ('x',), [(x,) for x in range(10)])
result = pipeline.execute('SELECT count FROM sw_v').first()
assert result['count'] == 10
_dump(pipeline, 'test_sw.sql')
pipeline.drop_all()
_restore(pipeline, 'test_sw.sql')
result = pipeline.execute('SELECT count FROM sw_v').first()
assert result['count'] == 10
# We should still drop back to 0 within 20 seconds
result = pipeline.execute('SELECT count FROM sw_v').first()
while result['count'] > 0:
time.sleep(1)
result = pipeline.execute('SELECT count FROM sw_v').first()
result = pipeline.execute('SELECT count FROM sw_v').first()
assert result['count'] == 0
def test_single_continuous_view(pipeline, clean_db):
"""
Verify that specific continuous views can be dropped and restored
"""
pipeline.create_stream('stream0', x='int')
pipeline.create_cv('test_single0', 'SELECT COUNT(*) FROM stream0')
pipeline.create_cv('test_single1', 'SELECT COUNT(*) FROM stream0')
pipeline.insert('stream0', ('x',), [(x,) for x in range(10)])
result = pipeline.execute('SELECT count FROM test_single0').first()
assert result['count'] == 10
result = pipeline.execute('SELECT count FROM test_single1').first()
assert result['count'] == 10
_dump(pipeline, 'test_single.sql', tables=['test_single0', 'stream0', 'test_single0_mrel'])
pipeline.drop_all()
_restore(pipeline, 'test_single.sql')
result = pipeline.execute('SELECT count FROM test_single0').first()
assert result['count'] == 10
# We didn't dump this one
result = list(pipeline.execute('SELECT * FROM pg_class WHERE relname LIKE \'%%test_single1%%\''))
assert not result
def test_sliding_windows(pipeline, clean_db):
"""
Verify that sliding window queries are properly dumped and restored
"""
pipeline.create_stream('stream0', x='int')
pipeline.execute('CREATE VIEW sw_v WITH (sw = \'20 seconds\') AS SELECT count(*) FROM stream0')
pipeline.insert('stream0', ('x',), [(x,) for x in range(10)])
result = pipeline.execute('SELECT count FROM sw_v')[0]
assert result['count'] == 10
_dump(pipeline, 'test_sw.sql')
pipeline.drop_all()
_restore(pipeline, 'test_sw.sql')
result = pipeline.execute('SELECT count FROM sw_v')[0]
assert result['count'] == 10
# We should still drop back to 0 within 20 seconds
result = pipeline.execute('SELECT count FROM sw_v')[0]
while result['count'] > 0:
time.sleep(1)
result = pipeline.execute('SELECT count FROM sw_v')[0]
result = pipeline.execute('SELECT count FROM sw_v')[0]
# Disabled until #157 (currently combine doesn't return 0 on NULL input for this aggregate)
# assert result == 0
assert result['count'] is None
def test_repeated_dump_restore(pipeline, clean_db):
"""
Verify that we can dump and restore a restored DB
"""
pipeline.create_stream('stream0', x='int')
q = """
SELECT x::integer % 100 AS g, avg(x) + 1 AS avg, count(*), count(distinct x) AS distincts FROM stream0
GROUP BY g
"""
pipeline.create_cv('test_dump', q)
rows = [(x,) for x in range(1000)]
pipeline.insert('stream0', ('x',), rows)
def _verify():
result = pipeline.execute('SELECT count(*) FROM test_dump')[0]
assert result['count'] == 100
result = pipeline.execute('SELECT sum(avg) FROM test_dump')[0]
assert result['sum'] == 50050
result = pipeline.execute('SELECT sum(distincts) FROM test_dump')[0]
assert result['sum'] == 1000
_verify()
_dump(pipeline, 'test_dump.sql')
pipeline.drop_all()
_restore(pipeline, 'test_dump.sql')
_verify()
_dump(pipeline, 'test_dump.sql')
pipeline.drop_all()
_restore(pipeline, 'test_dump.sql')
_verify()
# Now verify that we can successfully add more data to the restored CV
rows = [(x,) for x in range(2000)]
pipeline.insert('stream0', ('x',), rows)
result = pipeline.execute('SELECT sum(count) FROM test_dump')[0]
assert result['sum'] == 3000
result = pipeline.execute('SELECT sum(distincts) FROM test_dump')[0]
assert result['sum'] == 2000
def test_cont_transforms(pipeline, clean_db):
pipeline.execute('CREATE FOREIGN TABLE cv_stream (x int, y text) SERVER pipelinedb')
pipeline.execute('CREATE FOREIGN TABLE ct_stream (x int, y text) SERVER pipelinedb')
pipeline.create_cv('test_cv', 'SELECT count(*) FROM cv_stream')
pipeline.create_ct('test_ct1', 'SELECT x::int, y::text FROM ct_stream WHERE mod(x, 2) = 0',
"pipelinedb.insert_into_stream('cv_stream', 'cv_stream')")
pipeline.create_table('test_t', x='int', y='text')
pipeline.execute('''
CREATE OR REPLACE FUNCTION test_tg()
RETURNS trigger AS
$$
BEGIN
INSERT INTO test_t (x, y) VALUES (NEW.x, NEW.y);
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
''')
pipeline.create_ct('test_ct2', 'SELECT x::int, y::text FROM ct_stream',
'test_tg')
pipeline.insert('ct_stream', ('x', 'y'), [(1, 'hello'), (2, 'world')])
time.sleep(1)
assert pipeline.execute('SELECT count FROM test_cv')[0]['count'] == 2
_dump(pipeline, 'test_cont_transform.sql')
pipeline.drop_all()
pipeline.drop_table('test_t')
pipeline.execute('DROP FUNCTION test_tg()')
_restore(pipeline, 'test_cont_transform.sql')
pipeline.insert('ct_stream', ('x', 'y'), [(1, 'hello'), (2, 'world')])
time.sleep(1)
assert pipeline.execute('SELECT count FROM test_cv')[0]['count'] == 4
ntups = 0
for row in pipeline.execute('SELECT x, count(*) FROM test_t GROUP BY x'):
assert row['count'] == 2
assert row['x'] in (1, 2)
ntups += 1
assert ntups == 2
def test_tablespace(pipeline, clean_db):
"""
Verify that CVs can be created within tablespaces
"""
path = os.path.abspath('test_tablespace')
if os.path.exists(path):
shutil.rmtree(path)
os.mkdir(path)
pipeline.execute("CREATE TABLESPACE test_tablespace LOCATION '%s'" % path)
pipeline.create_stream('test_tablespace_s', x='int')
q = 'SELECT x % 10 AS g, count(DISTINCT x) FROM test_tablespace_s GROUP BY g'
pipeline.create_cv('test_tablespace0', q)
pipeline.create_cv('test_tablespace1', q, tablespace='test_tablespace')
pipeline.insert('test_tablespace_s', ('x', ),
[(x, ) for x in range(10000)])
result0 = pipeline.execute('SELECT count(*) FROM test_tablespace0')
result1 = pipeline.execute('SELECT count(*) FROM test_tablespace1')
assert len(result0) == 1
assert len(result1) == 1
assert result0[0]['count'] == result1[0]['count']
result0 = pipeline.execute('SELECT combine(count) FROM test_tablespace0')
result1 = pipeline.execute('SELECT combine(count) FROM test_tablespace1')
assert len(result0) == 1
assert len(result1) == 1
assert result0[0]['combine'] == result1[0]['combine']
# Now verify that test_tablespace1 is physically in the tablespace
row = pipeline.execute(
"SELECT oid FROM pg_class WHERE relname = 'test_tablespace1_mrel'")
oid = row[0]['oid']
found = glob.glob('test_tablespace/*/*/%d' % oid)
assert len(found) == 1
pipeline.drop_all()
pipeline.execute('DROP TABLESPACE test_tablespace')
shutil.rmtree(path)
def test_schema_only(pipeline, clean_db):
"""
Verify that it is possible to only dump continuous view schemas and not data
"""
pipeline.create_stream('stream0', x='int')
pipeline.create_cv('test_schema', 'SELECT COUNT(*) FROM stream0')
pipeline.insert('stream0', ('x', ), [(x, ) for x in range(10)])
result = pipeline.execute('SELECT count FROM test_schema').first()
assert result['count'] == 10
_dump(pipeline, 'test_schema.sql', schema_only=True)
pipeline.drop_all()
_restore(pipeline, 'test_schema.sql')
# No data loaded
result = list(pipeline.execute('SELECT count FROM test_schema'))
assert not result
def test_schema_only(pipeline, clean_db):
"""
Verify that it is possible to only dump continuous view schemas and not data
"""
pipeline.create_stream('stream0', x='int')
pipeline.create_cv('test_schema', 'SELECT COUNT(*) FROM stream0')
pipeline.insert('stream0', ('x',), [(x,) for x in range(10)])
result = pipeline.execute('SELECT count FROM test_schema').first()
assert result['count'] == 10
_dump(pipeline, 'test_schema.sql', schema_only=True)
pipeline.drop_all()
_restore(pipeline, 'test_schema.sql')
# No data loaded
result = list(pipeline.execute('SELECT count FROM test_schema'))
assert not result
def test_cont_transforms(pipeline, clean_db):
pipeline.execute('CREATE STREAM cv_stream (x int, y text)')
pipeline.execute('CREATE STREAM ct_stream (x int, y text)')
pipeline.create_cv('test_cv', 'SELECT count(*) FROM cv_stream')
pipeline.create_ct('test_ct1', 'SELECT x::int, y::text FROM ct_stream WHERE mod(x, 2) = 0',
"pipeline_stream_insert('cv_stream', 'cv_stream')")
pipeline.create_table('test_t', x='int', y='text')
pipeline.execute('''
CREATE OR REPLACE FUNCTION test_tg()
RETURNS trigger AS
$$
BEGIN
INSERT INTO test_t (x, y) VALUES (NEW.x, NEW.y);
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
''')
pipeline.create_ct('test_ct2', 'SELECT x::int, y::text FROM ct_stream',
'test_tg()')
pipeline.insert('ct_stream', ('x', 'y'), [(1, 'hello'), (2, 'world')])
time.sleep(1)
_dump(pipeline, 'test_cont_transform.sql')
pipeline.drop_all()
pipeline.drop_table('test_t')
pipeline.execute('DROP FUNCTION test_tg()')
_restore(pipeline, 'test_cont_transform.sql')
pipeline.insert('ct_stream', ('x', 'y'), [(1, 'hello'), (2, 'world')])
time.sleep(1)
assert pipeline.execute('SELECT count FROM test_cv').first()['count'] == 4
ntups = 0
for row in pipeline.execute('SELECT x, count(*) FROM test_t GROUP BY x'):
assert row['count'] == 2
assert row['x'] in (1, 2)
ntups += 1
assert ntups == 2
def test_dump_data_only(pipeline, clean_db):
"""
Verify that it is possible to only dump continuous view data and not schemas
"""
pipeline.create_stream('stream0', x='int')
pipeline.create_cv('test_data', 'SELECT COUNT(*) FROM stream0')
pipeline.insert('stream0', ('x',), [(x,) for x in range(10)])
result = pipeline.execute('SELECT count FROM test_data')[0]
assert result['count'] == 10
_dump(pipeline, 'test_data.sql', data_only=True)
pipeline.drop_all()
pipeline.create_stream('stream0', x='int')
pipeline.create_cv('test_data', 'SELECT COUNT(*) FROM stream0')
_restore(pipeline, 'test_data.sql')
result = pipeline.execute('SELECT count FROM test_data')[0]
assert result['count'] == 10
def test_dump(pipeline, clean_db):
"""
Verify that we can dump and restore CVs using INSERT statements
"""
pipeline.create_stream('stream0', x='int')
q = """
SELECT x::integer %% 100 AS g, avg(x) + 1 AS avg, count(*), count(distinct x) AS distincts FROM stream0
GROUP BY g
"""
pipeline.create_cv('test_dump', q)
rows = [(x,) for x in range(1000)]
pipeline.insert('stream0', ('x',), rows)
def _verify():
result = pipeline.execute('SELECT count(*) FROM test_dump').first()
assert result['count'] == 100
result = pipeline.execute('SELECT sum(avg) FROM test_dump').first()
assert result['sum'] == 50050
result = pipeline.execute('SELECT sum(distincts) FROM test_dump').first()
assert result['sum'] == 1000
_verify()
_dump(pipeline, 'test_dump.sql')
pipeline.drop_all()
_restore(pipeline, 'test_dump.sql')
_verify()
# Now verify that we can successfully add more data to the restored CV
rows = [(x,) for x in range(2000)]
pipeline.insert('stream0', ('x',), rows)
result = pipeline.execute('SELECT sum(count) FROM test_dump').first()
assert result['sum'] == 3000
result = pipeline.execute('SELECT sum(distincts) FROM test_dump').first()
assert result['sum'] == 2000
def test_renamed_objects(pipeline, clean_db):
"""
Verify that we can dump and restore renamed CQs and streams
"""
pipeline.create_stream('s', x='int')
q = """
SELECT x, count(*) FROM s GROUP BY x;
"""
pipeline.create_cv('cv_0', q)
q = """
SELECT (new).x, combine((delta).count) AS count FROM output_of('cv_0') GROUP BY x
"""
pipeline.create_cv('combine_cv_0', q)
q = """
SELECT (new).count + 41 AS v FROM output_of('combine_cv_0')
"""
pipeline.create_ct('transform_combine_cv_0', q)
q = """
SELECT max(v), count(*) FROM output_of('transform_combine_cv_0')
"""
pipeline.create_cv('max_transform_combine_cv_0', q)
rows = [(x,) for x in range(1000)]
pipeline.insert('s', ('x',), rows)
result = pipeline.execute('SELECT combine(count) FROM cv_0')[0]
assert result['combine'] == 1000
pipeline.execute('ALTER VIEW cv_0 RENAME TO cv_0_renamed')
pipeline.execute('ALTER VIEW combine_cv_0 RENAME TO combine_cv_0_renamed')
pipeline.execute('ALTER VIEW transform_combine_cv_0 RENAME TO transform_combine_cv_0_renamed')
pipeline.execute('ALTER VIEW max_transform_combine_cv_0 RENAME TO max_transform_combine_cv_0_renamed')
pipeline.execute('ALTER FOREIGN TABLE s RENAME TO s_renamed')
result = pipeline.execute('SELECT combine(count) FROM cv_0_renamed')[0]
assert result['combine'] == 1000
result = pipeline.execute('SELECT combine(count) FROM combine_cv_0_renamed')[0]
assert result['combine'] == 1000
result = pipeline.execute('SELECT max, count FROM max_transform_combine_cv_0_renamed')[0]
assert result['max'] == 42
assert result['count'] == 1000
_dump(pipeline, 'test_renamed_cqs.sql')
pipeline.execute('DROP VIEW combine_cv_0_renamed CASCADE')
pipeline.drop_all()
_restore(pipeline, 'test_renamed_cqs.sql')
result = pipeline.execute('SELECT combine(count) FROM cv_0_renamed')[0]
assert result['combine'] == 1000
result = pipeline.execute('SELECT combine(count) FROM combine_cv_0_renamed')[0]
assert result['combine'] == 1000
result = pipeline.execute('SELECT max, count FROM max_transform_combine_cv_0_renamed')[0]
assert result['max'] == 42
assert result['count'] == 1000
# Now write some more rows to verify everything updates properly
rows = [(x,) for x in range(1000)]
pipeline.insert('s_renamed', ('x',), rows)
result = pipeline.execute('SELECT combine(count) FROM cv_0_renamed')[0]
assert result['combine'] == 2000
result = pipeline.execute('SELECT combine(count) FROM combine_cv_0_renamed')[0]
assert result['combine'] == 2000
result = pipeline.execute('SELECT max, count FROM max_transform_combine_cv_0_renamed')[0]
assert result['max'] == 43
assert result['count'] == 2000
pipeline.execute('DROP VIEW combine_cv_0_renamed CASCADE')
