def _test_agg(pipeline, agg, check_fn=None):
name = agg[:agg.find('(')]
q = 'SELECT g::integer, %s OVER (PARTITION BY g ORDER BY ts::timestamp) FROM %s'
cv_name = 'test_%s' % name
table_name = 'test_%s_t' % name
desc = ('ts', 'g', 'x', 'y', 'z')
pipeline.create_cv(cv_name, q % (agg, 'stream'))
pipeline.create_table(table_name, ts='timestamp', x='integer', y='integer', z='integer', g='integer')
rows = []
for i, n in enumerate(range(1000)):
ts = str(datetime.utcnow() + timedelta(seconds=i))
row = ts, n % 10, random.randint(1, 256), random.randint(1, 256), random.randint(1, 256)
rows.append(row)
pipeline.insert('stream', desc, rows)
pipeline.insert(table_name, desc, rows)
if check_fn:
return check_fn(pipeline)
expected = list(pipeline.execute(q % (agg, table_name) + ' ORDER BY g'))
result = list(pipeline.execute('SELECT * FROM %s ORDER BY g' % cv_name))
assert len(expected) == len(result)
for e, r in zip(expected, result):
assert e == r
pipeline.drop_cv(cv_name)
pipeline.drop_table(table_name)
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_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_agg(pipeline, agg, check_fn=None):
name = agg[:agg.find('(')]
q = 'SELECT g::integer, %s OVER (PARTITION BY g ORDER BY ts::timestamp) FROM %s'
cv_name = 'test_%s' % name
table_name = 'test_%s_t' % name
desc = ('ts', 'g', 'x', 'y', 'z')
pipeline.create_cv(cv_name, q % (agg, 'stream'))
pipeline.create_table(table_name,
ts='timestamp',
x='integer',
y='integer',
z='integer',
g='integer')
rows = []
for i, n in enumerate(range(1000)):
ts = str(datetime.utcnow() + timedelta(seconds=i))
row = ts, n % 10, random.randint(1, 256), random.randint(
1, 256), random.randint(1, 256)
rows.append(row)
pipeline.insert('stream', desc, rows)
pipeline.insert(table_name, desc, rows)
if check_fn:
return check_fn(pipeline)
expected = list(pipeline.execute(q % (agg, table_name) + ' ORDER BY g'))
result = list(pipeline.execute('SELECT * FROM %s ORDER BY g' % cv_name))
assert len(expected) == len(result)
for e, r in zip(expected, result):
assert e == r
pipeline.drop_cv(cv_name)
pipeline.drop_table(table_name)
