def test_create_aggregate_init_final(self):
"Create an aggregate with an INITCOND and a FINALFUNC"
self.db.execute("DROP AGGREGATE IF EXISTS a1(integer)")
self.db.execute_commit("DROP FUNCTION IF EXISTS f2(integer)")
self.db.execute_commit(DROP_STMT2)
inmap = self.std_map()
inmap['schema public'].update({'function f1(integer, integer)': {
'language': 'sql', 'returns': 'integer',
'source': SOURCE2,
'volatility': 'immutable'}})
inmap['schema public'].update({'function f2(integer)': {
'language': 'sql', 'returns': 'double precision',
'source': "SELECT $1::float",
'volatility': 'immutable'}})
inmap['schema public'].update({'aggregate a1(integer)': {
'sfunc': 'f1', 'stype': 'integer', 'initcond': '-1',
'finalfunc': 'f2(integer)'}})
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[1]), CREATE_STMT2)
self.assertEqual(fix_indent(dbsql[2]),
"CREATE FUNCTION f2(integer) "
"RETURNS double precision LANGUAGE sql IMMUTABLE "
"AS $_$SELECT $1::float$_$")
self.assertEqual(fix_indent(dbsql[3]),
"CREATE AGGREGATE a1(integer) "
"(SFUNC = f1, STYPE = integer, FINALFUNC = f2, "
"INITCOND = '-1')")
def test_create_wrapper_server(self):
"Create a foreign data wrapper and its server"
inmap = self.std_map()
inmap.update({'foreign data wrapper fdw1': {'server fs1': {}}})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == CREATE_FDW_STMT
assert fix_indent(sql[1]) == CREATE_FS_STMT
def test_create_with_foreign_key(self):
"Create a table with a foreign key constraint"
inmap = self.std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c11': {'type': 'integer'}},
{'c12': {'type': 'text'}}]},
'table t2': {
'columns': [{'c21': {'type': 'integer'}},
{'c22': {'type': 'text'}},
{'c23': {'type': 'integer'}}],
'foreign_keys': {'t2_c23_fkey': {
'columns': ['c23'],
'references': {'columns': ['c11'],
'table': 't1'}}}}})
sql = self.to_sql(inmap)
# can't control which table will be created first
crt1 = 0
crt2 = 1
if 't1' in sql[1]:
crt1 = 1
crt2 = 0
self.assertEqual(fix_indent(sql[crt1]),
"CREATE TABLE t1 (c11 integer, c12 text)")
self.assertEqual(fix_indent(sql[crt2]),
"CREATE TABLE t2 (c21 integer, c22 text, "
"c23 integer)")
self.assertEqual(fix_indent(sql[2]),
"ALTER TABLE t2 ADD CONSTRAINT t2_c23_fkey "
"FOREIGN KEY (c23) REFERENCES t1 (c11)")
def test_create_table_with_defaults(self):
"Create a table with two column DEFAULTs, one referring to a SEQUENCE"
self.db.execute_commit("DROP SEQUENCE IF EXISTS t1_c1_seq")
inmap = self.std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c1': {
'type': 'integer',
'not_null': True,
'default': "nextval('t1_c1_seq'::regclass)"}},
{'c2': {'type': 'text', 'not_null': True}},
{'c3': {
'type': 'date', 'not_null': True,
'default': "('now'::text)::date"}}]},
'sequence t1_c1_seq': {
'cache_value': 1, 'increment_by': 1, 'max_value': None,
'min_value': None, 'start_value': 1}})
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[0]),
"CREATE TABLE t1 (c1 integer NOT NULL, "
"c2 text NOT NULL, "
"c3 date NOT NULL DEFAULT ('now'::text)::date)")
self.assertEqual(fix_indent(dbsql[1]),
"CREATE SEQUENCE t1_c1_seq START WITH 1 "
"INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1")
self.assertEqual(dbsql[2], "ALTER TABLE t1 ALTER COLUMN c1 "
"SET DEFAULT nextval('t1_c1_seq'::regclass)")
def test_alter_foreign_key2(self):
"Change foreign key: foreign column"
stmts = ["CREATE TABLE t1 (c11 INTEGER NOT NULL UNIQUE, "
"c12 INTEGER NOT NULL UNIQUE)",
"CREATE TABLE t2 (c21 INTEGER PRIMARY KEY NOT NULL, "
"c22 INTEGER, c23 INTEGER)",
"ALTER TABLE t2 ADD CONSTRAINT t2_c22_fkey " \
"FOREIGN KEY (c22) REFERENCES t1 (c11)"]
inmap = self.std_map()
inmap['schema public'].update({
'table t1': {'columns': [
{'c11': {'type': 'integer', 'not_null': True}},
{'c12': {'type': 'integer', 'not_null': True}}],
'unique_constraints': {
't1_c11_key': {'columns': ['c11']},
't1_c12_key': {'columns': ['c12']}}},
'table t2': {'columns': [
{'c21': {'type': 'integer', 'not_null': True}},
{'c22': {'type': 'integer'}},
{'c23': {'type': 'integer'}}],
'primary_key': {'t2_pkey': {'columns': ['c21']}},
'foreign_keys': {'t2_c22_fkey': {
'columns': ['c22'],
'references': {'columns': ['c12'],
'table': 't1'}}}}})
sql = self.to_sql(inmap, stmts)
self.assertEqual(2, len(sql))
self.assertEqual(fix_indent(sql[0]),
'ALTER TABLE t2 DROP CONSTRAINT t2_c22_fkey')
self.assertEqual(fix_indent(sql[1]),
'ALTER TABLE t2 ADD CONSTRAINT t2_c22_fkey ' \
'FOREIGN KEY (c22) REFERENCES t1 (c12)')
def test_create_wrapper_server_mapping(self):
"Create a FDW, server and user mapping"
inmap = self.std_map()
inmap.update({'foreign data wrapper fdw1': {'server fs1': {
'user mappings': {'PUBLIC': {}}}}})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == CREATE_FDW_STMT
assert fix_indent(sql[1]) == CREATE_FS_STMT
assert fix_indent(sql[2]) == CREATE_UM_STMT
def test_alter_statistics(self):
"Alter a table to add column statistics"
inmap = self.std_map()
inmap["schema public"].update(
{"table t1": {"columns": [{"c1": {"type": "integer", "statistics": 100}}, {"c2": {"type": "text"}}]}}
)
sql = self.to_sql(inmap, [CREATE_STMT1, "ALTER TABLE t1 ALTER c2 " "SET STATISTICS 1000"])
assert fix_indent(sql[0]) == "ALTER TABLE t1 ALTER COLUMN c1 SET STATISTICS 100"
assert fix_indent(sql[1]) == "ALTER TABLE t1 ALTER COLUMN c2 SET STATISTICS -1"
def test_change_column_types(self):
"Change the datatypes of two columns"
inmap = self.std_map()
inmap["schema public"].update(
{"table t1": {"columns": [{"c1": {"type": "bigint"}}, {"c2": {"type": "varchar(25)"}}]}}
)
sql = self.to_sql(inmap, [CREATE_STMT1])
assert fix_indent(sql[0]) == "ALTER TABLE t1 ALTER COLUMN c1 TYPE bigint"
assert fix_indent(sql[1]) == "ALTER TABLE t1 ALTER COLUMN c2 TYPE varchar(25)"
def test_create_rule_nothing(self):
"Create a rule"
inmap = self.std_map()
inmap['schema sd'].update({'table t1': {
'columns': [{'c1': {'type': 'integer'}}, {'c2': {'type': 'text'}}],
'rules': {'r1': {'event': 'insert', 'actions': 'NOTHING'}}}})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == CREATE_TABLE_STMT
assert fix_indent(sql[1]) == CREATE_STMT % ('INSERT', 'NOTHING')
def test_create_w_unique_constraint(self):
"Create new table with a single column unique constraint"
inmap = self.std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c1': {'type': 'integer'}}, {'c2': {'type': 'text'}}],
'unique_constraints': {'t1_c1_key': {'columns': ['c1']}}}})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == "CREATE TABLE t1 (c1 integer, c2 text)"
assert fix_indent(sql[1]) == \
"ALTER TABLE t1 ADD CONSTRAINT t1_c1_key UNIQUE (c1)"
def test_create_with_primary_key(self):
"Create new table with single column primary key"
inmap = self.std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c1': {'type': 'text'}}, {'c2': {'type': 'integer'}}],
'primary_key': {'t1_pkey': {'columns': ['c2']}}}})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == "CREATE TABLE t1 (c1 text, c2 integer)"
assert fix_indent(sql[1]) == "ALTER TABLE t1 ADD CONSTRAINT t1_pkey " \
"PRIMARY KEY (c2)"
def test_add_column4(self):
"Add two columns to a table that has a dropped column"
stmts = [CREATE_STMT2, DROP_COL_STMT]
inmap = self.std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c1': {'type': 'integer'}}, {'c2': {'type': 'text'}},
{'c3': {'type': 'date'}}, {'c4': {'type': 'text'}}]}})
sql = self.to_sql(inmap, stmts)
assert fix_indent(sql[0]) == "ALTER TABLE t1 ADD COLUMN c3 date"
assert fix_indent(sql[1]) == "ALTER TABLE t1 ADD COLUMN c4 text"
def test_create_domain_check(self):
"Create a domain with a CHECK constraint"
inmap = self.std_map()
inmap['schema sd'].update({'domain d1': {
'type': 'integer', 'check_constraints': {'d1_check': {
'expression': '(VALUE >= 1888)'}}}})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == CREATE_STMT
assert fix_indent(sql[1]) == "ALTER DOMAIN sd.d1 ADD CONSTRAINT " + \
"d1_check CHECK (VALUE >= 1888)"
def test_drop_add_column3(self):
"Drop and re-add table columns from table with dropped column"
stmts = [CREATE_STMT2, DROP_COL_STMT]
inmap = self.std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c2': {'type': 'text'}}, {'c3': {'type': 'date'}},
{'c4': {'type': 'text'}}]}})
sql = self.to_sql(inmap, stmts)
assert fix_indent(sql[0]) == "ALTER TABLE t1 ADD COLUMN c3 date"
assert fix_indent(sql[1]) == "ALTER TABLE t1 ADD COLUMN c4 text"
assert sql[2] == "ALTER TABLE t1 DROP COLUMN c1"
def test_change_column_types(self):
"Change the datatypes of two columns"
inmap = self.std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c1': {'type': 'bigint'}},
{'c2': {'type': 'varchar(25)'}}]}})
sql = self.to_sql(inmap, [CREATE_STMT1])
assert fix_indent(sql[0]) == \
"ALTER TABLE t1 ALTER COLUMN c1 TYPE bigint"
assert fix_indent(sql[1]) == \
"ALTER TABLE t1 ALTER COLUMN c2 TYPE varchar(25)"
def test_create_rule_instead(self):
"Create a rule with an INSTEAD action"
inmap = self.std_map()
inmap['schema sd'].update({'table t1': {
'columns': [{'c1': {'type': 'integer'}}, {'c2': {'type': 'text'}}],
'rules': {'r1': {'event': 'update', 'instead': True,
'actions': 'NOTHING'}}}})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == CREATE_TABLE_STMT
assert fix_indent(sql[1]) == "CREATE RULE r1 AS ON UPDATE TO sd.t1 " \
"DO INSTEAD NOTHING"
def test_drop_add_column3(self):
"Drop and re-add table columns from table with dropped column"
stmts = [CREATE_STMT2, DROP_COL_STMT]
inmap = self.std_map()
inmap["schema public"].update(
{"table t1": {"columns": [{"c2": {"type": "text"}}, {"c3": {"type": "date"}}, {"c4": {"type": "text"}}]}}
)
sql = self.to_sql(inmap, stmts)
assert fix_indent(sql[0]) == "ALTER TABLE t1 ADD COLUMN c3 date"
assert fix_indent(sql[1]) == "ALTER TABLE t1 ADD COLUMN c4 text"
assert sql[2] == "ALTER TABLE t1 DROP COLUMN c1"
def test_create_rule_nothing(self):
"Create a rule"
inmap = self.std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c1': {'type': 'integer'}},
{'c2': {'type': 'text'}}],
'rules': {'r1': {'event': 'insert',
'actions': 'NOTHING'}}}})
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[0]), CREATE_TABLE_STMT)
self.assertEqual(fix_indent(dbsql[1]), CREATE_STMT % (
'INSERT', 'NOTHING'))
def test_create_aggregate(self):
"Create a simple aggregate"
inmap = self.std_map()
inmap['schema public'].update({'function f1(integer, integer)': {
'language': 'sql', 'returns': 'integer', 'source': SOURCE2,
'volatility': 'immutable'}})
inmap['schema public'].update({'aggregate a1(integer)': {
'sfunc': 'f1', 'stype': 'integer'}})
sql = self.to_sql(inmap)
assert fix_indent(sql[1]) == CREATE_STMT2
assert fix_indent(sql[2]) == "CREATE AGGREGATE a1(integer) " \
"(SFUNC = f1, STYPE = integer)"
def test_change_column_types(self):
"Change the datatypes of two columns"
self.db.execute_commit(CREATE_STMT1)
inmap = self.std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c1': {'type': 'bigint'}},
{'c2': {'type': 'varchar(25)'}}]}})
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[0]),
"ALTER TABLE t1 ALTER COLUMN c1 TYPE bigint")
self.assertEqual(fix_indent(dbsql[1]),
"ALTER TABLE t1 ALTER COLUMN c2 TYPE varchar(25)")
def test_create_event_trigger_simple(self):
"Create a simple event trigger"
inmap = self.std_map(plpgsql_installed=True)
inmap['schema sd'].update({'function f1()': {
'language': 'plpgsql', 'returns': 'event_trigger',
'source': FUNC_SRC}})
inmap.update({'event trigger et1': {
'enabled': True, 'event': 'ddl_command_end',
'procedure': 'sd.f1()'}})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == CREATE_FUNC_STMT
assert fix_indent(sql[1]) == CREATE_STMT % ''
def test_alter_statistics(self):
"Alter a table to add column statistics"
inmap = self.std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c1': {'type': 'integer', 'statistics': 100}},
{'c2': {'type': 'text'}}]}})
sql = self.to_sql(inmap, [CREATE_STMT1, "ALTER TABLE t1 ALTER c2 "
"SET STATISTICS 1000"])
assert fix_indent(sql[0]) == \
"ALTER TABLE t1 ALTER COLUMN c1 SET STATISTICS 100"
assert fix_indent(sql[1]) == \
"ALTER TABLE t1 ALTER COLUMN c2 SET STATISTICS -1"
def test_create_foreign_key_deferred(self):
"Create a table with various foreign key deferring constraint"
inmap = self.std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c11': {'type': 'integer'}},
{'c12': {'type': 'text'}}]},
'table t2': {
'columns': [{'c21': {'type': 'integer'}},
{'c22': {'type': 'text'}},
{'c23': {'type': 'integer'}},
{'c24': {'type': 'integer'}},
{'c25': {'type': 'integer'}},
],
'foreign_keys': {
't2_c23_fkey': {
'columns': ['c23'],
'references': {'columns': ['c11'],
'table': 't1'}},
't2_c24_fkey': {
'columns': ['c24'],
'references': {'columns': ['c11'],
'table': 't1'},
'deferrable': True},
't2_c25_fkey': {
'columns': ['c25'],
'references': {'columns': ['c11'],
'table': 't1'},
'deferrable': True,
'deferred': True}}}})
sql = self.to_sql(inmap)
# can't control which table/constraint will be created first
sql[0:2] = list(sorted(sql[0:2]))
sql[2:5] = list(sorted(sql[2:5]))
self.assertEqual(fix_indent(sql[0]),
"CREATE TABLE t1 (c11 integer, c12 text)")
self.assertEqual(fix_indent(sql[1]),
"CREATE TABLE t2 (c21 integer, c22 text, "
"c23 integer, c24 integer, c25 integer)")
self.assertEqual(fix_indent(sql[2]),
"ALTER TABLE t2 ADD CONSTRAINT t2_c23_fkey "
"FOREIGN KEY (c23) REFERENCES t1 (c11)")
self.assertEqual(fix_indent(sql[3]),
"ALTER TABLE t2 ADD CONSTRAINT t2_c24_fkey "
"FOREIGN KEY (c24) REFERENCES t1 (c11) "
"DEFERRABLE")
self.assertEqual(fix_indent(sql[4]),
"ALTER TABLE t2 ADD CONSTRAINT t2_c25_fkey "
"FOREIGN KEY (c25) REFERENCES t1 (c11) "
"DEFERRABLE INITIALLY DEFERRED")
def test_create_view(self):
"Create a view"
inmap = self.std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c1': {'type': 'integer'}}, {'c2': {'type': 'text'}},
{'c3': {'type': 'integer'}}]}})
inmap['schema public'].update({'view v1': {
'definition': "SELECT c1, c3 * 2 FROM t1"}})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == "CREATE TABLE t1 (c1 integer, " \
"c2 text, c3 integer)"
assert fix_indent(sql[1]) == \
"CREATE VIEW v1 AS SELECT c1, c3 * 2 FROM t1"
def test_create_rule_instead(self):
"Create a rule with an INSTEAD action"
inmap = self.std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c1': {'type': 'integer'}},
{'c2': {'type': 'text'}}],
'rules': {'r1': {'event': 'update', 'instead': True,
'actions': 'NOTHING'}}}})
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[0]), CREATE_TABLE_STMT)
self.assertEqual(fix_indent(dbsql[1]),
"CREATE RULE r1 AS ON UPDATE TO t1 "
"DO INSTEAD NOTHING")
def test_create_event_trigger_filter(self):
"Create an event trigger with tag filter variables"
inmap = self.std_map(plpgsql_installed=True)
inmap['schema sd'].update({'function f1()': {
'language': 'plpgsql', 'returns': 'event_trigger',
'source': FUNC_SRC}})
inmap.update({'event trigger et1': {
'enabled': True, 'event': 'ddl_command_end',
'procedure': 'sd.f1()', 'tags': ['CREATE TABLE', 'CREATE VIEW']}})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == CREATE_FUNC_STMT
assert fix_indent(sql[1]) == CREATE_STMT % (
"WHEN tag IN ('CREATE TABLE', 'CREATE VIEW') ")
def test_create_w_check_constraint(self):
"Create new table with a single column CHECK constraint"
inmap = self.std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c1': {'type': 'integer'}},
{'c2': {'type': 'text'}}],
'check_constraints': {
't1_c1_check': {'columns': ['c1'],
'expression': 'c1 > 0 and c1 < 1000000'}}}})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == "CREATE TABLE t1 (c1 integer, c2 text)"
assert fix_indent(sql[1]) == "ALTER TABLE t1 ADD CONSTRAINT " \
"t1_c1_check CHECK (c1 > 0 and c1 < 1000000)"
def test_create_event_trigger_simple(self):
"Create a simple event trigger"
if self.db.version < 90300:
self.skipTest('Only available on PG 9.3')
inmap = self.std_map(plpgsql_installed=True)
inmap['schema public'].update({'function f1()': {
'language': 'plpgsql', 'returns': 'event_trigger',
'source': FUNC_SRC}})
inmap.update({'event trigger et1': {
'enabled': True, 'event': 'ddl_command_end', 'procedure': 'f1()'}})
sql = self.to_sql(inmap)
assert fix_indent(sql[1]) == CREATE_FUNC_STMT
assert fix_indent(sql[2]) == CREATE_STMT % ''
def test_create_lang_extension(self):
"Create a language extension and a function in that language"
if self.db.version < 90100:
self.skipTest("Only available on PG 9.1")
inmap = self.std_map()
inmap.update({"extension plperl": {"description": "PL/Perl procedural language"}})
inmap["schema public"].update(
{"function f1()": {"language": "plperl", "returns": "text", "source": 'return "dummy";'}}
)
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == "CREATE EXTENSION plperl"
# skip over COMMENT and SET statements
assert fix_indent(sql[3]) == "CREATE FUNCTION f1() RETURNS text " 'LANGUAGE plperl AS $_$return "dummy";$_$'
def test_check_constraint_with_comment(self):
"Create a CHECK constraint with a comment"
inmap = self.std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c1': {'type': 'integer'}}, {'c2': {'type': 'text'}}],
'check_constraints': {'cns1': {
'columns': ['c1'], 'expression': 'c1 > 50',
'description': 'Test constraint cns1'}}}})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == "CREATE TABLE t1 (c1 integer, c2 text)"
assert fix_indent(sql[1]) == \
"ALTER TABLE t1 ADD CONSTRAINT cns1 CHECK (c1 > 50)"
assert sql[2] == COMMENT_STMT
def test_inherit_drop_parent_column(self):
"Drop a column from a parent table, child should not drop as well"
stmts = [CREATE_STMT1, "CREATE TABLE t2 (c3 date) INHERITS (t1)"]
inmap = self.std_map()
inmap['schema sd'].update({'table t1': {
'columns': [{'c1': {'type': 'integer'}}]}})
inmap['schema sd'].update({'table t2': {
'columns': [{'c1': {'type': 'integer', 'inherited': True}},
{'c3': {'type': 'date'}}], 'inherits': ['t1']}})
sql = self.to_sql(inmap, stmts)
assert len(sql) == 1
assert fix_indent(sql[0]) == "ALTER TABLE sd.t1 DROP COLUMN c2"
def test_create_conversion(self):
"Create a conversion"
inmap = self.std_map()
inmap['schema sd'].update({
'conversion c1': {
'source_encoding': 'LATIN1',
'dest_encoding': 'UTF8',
'function': 'iso8859_1_to_utf8'
}
})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == CREATE_STMT
def test_create_trigger1(self):
"Create a simple trigger"
inmap = self.std_map(plpgsql_installed=True)
inmap['schema sd'].update({
'function f1()': {
'language': 'plpgsql',
'returns': 'trigger',
'source': FUNC_SRC
}
})
inmap['schema sd'].update({
'table t1': {
'columns': [{
'c1': {
'type': 'integer'
}
}, {
'c2': {
'type': 'text'
}
}, {
'c3': {
'type': 'date'
}
}],
'triggers': {
'tr1': {
'timing': 'before',
'events': ['insert', 'update'],
'level': 'row',
'procedure': 'sd.f1'
}
}
}
})
sql = self.to_sql(inmap)
crt0, crt1 = (0, 1) if 'TABLE' in sql[0] else (1, 0)
assert fix_indent(sql[crt0]) == CREATE_TABLE_STMT
assert fix_indent(sql[crt1]) == CREATE_FUNC_STMT
assert fix_indent(sql[2]) == CREATE_STMT
def test_create_trigger2(self):
"Create another simple trigger with"
inmap = self.std_map(plpgsql_installed=True)
inmap['schema sd'].update({
'function f1()': {
'language': 'plpgsql',
'returns': 'trigger',
'source': FUNC_SRC
}
})
inmap['schema sd'].update({
'table t1': {
'columns': [{
'c1': {
'type': 'integer'
}
}, {
'c2': {
'type': 'text'
}
}, {
'c3': {
'type': 'date'
}
}],
'triggers': {
'tr1': {
'timing': 'after',
'events': ['delete', 'truncate'],
'procedure': 'sd.f1'
}
}
}
})
sql = self.to_sql(inmap)
crt0, crt1 = (0, 1) if 'TABLE' in sql[0] else (1, 0)
assert fix_indent(sql[crt0]) == CREATE_TABLE_STMT
assert fix_indent(sql[crt1]) == CREATE_FUNC_STMT
assert fix_indent(sql[2]) == "CREATE TRIGGER tr1 AFTER DELETE OR " \
"TRUNCATE ON sd.t1 FOR EACH STATEMENT EXECUTE PROCEDURE sd.f1()"
def test_table_inheritance(self):
"Create a table that inherits from another"
inmap = self.std_map()
inmap['schema sd'].update({
'table t1': {
'columns': [{
'c1': {
'type': 'integer'
}
}, {
'c2': {
'type': 'text'
}
}]
}
})
inmap['schema sd'].update({
'table t2': {
'columns': [{
'c1': {
'type': 'integer',
'inherited': True
}
}, {
'c2': {
'type': 'text',
'inherited': True
}
}, {
'c3': {
'type': 'numeric'
}
}],
'inherits': ['t1']
}
})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == CREATE_STMT
assert fix_indent(sql[1]) == "CREATE TABLE sd.t2 (c3 numeric) " \
"INHERITS (sd.t1)"
def test_collation_with_comment(self):
"Create a collation with a comment"
inmap = self.std_map()
inmap['schema sd'].update({
'collation c1': {
'description': 'Test collation c1',
'lc_collate': COLL,
'lc_ctype': COLL
}
})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == CREATE_STMT
assert sql[1] == COMMENT_STMT
def test_create_cast_inout(self):
"Create a cast with INOUT"
stmts = [CREATE_DOMAIN, "DROP CAST IF EXISTS (d1 AS integer)"]
inmap = self.std_map()
inmap.update({
'cast (d1 as integer)': {
'context': 'implicit',
'method': 'inout'
}
})
inmap['schema public'].update({'domain d1': {'type': 'integer'}})
sql = self.to_sql(inmap, stmts)
assert fix_indent(sql[0]) == CREATE_STMT3
def test_create_cast_function(self):
"Create a cast with a function"
stmts = [DROP_STMT, CREATE_FUNC]
inmap = self.std_map()
inmap.update({
'cast (smallint as boolean)': {
'function': 'int2_bool(smallint)',
'context': 'explicit',
'method': 'function'
}
})
sql = self.to_sql(inmap, stmts)
assert fix_indent(sql[0]) == CREATE_STMT1
def test_create_operator_commutator(self):
"Create an operator with a commutator"
inmap = self.std_map()
inmap['schema public'].update({
'operator &&(integer, integer)': {
'procedure': 'int4pl',
'commutator': 'public.&&'
}
})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == "CREATE OPERATOR && (" \
"PROCEDURE = int4pl, LEFTARG = integer, RIGHTARG = integer, " \
"COMMUTATOR = OPERATOR(public.&&))"
def test_create_function_with_args(self):
"Create a function with two arguments"
inmap = self.std_map()
inmap['schema public'].update({
'function f1(integer, integer)': {
'language': 'sql',
'returns': 'integer',
'source': SOURCE2
}
})
sql = self.to_sql(inmap)
assert fix_indent(sql[1]) == "CREATE FUNCTION f1(integer, integer) " \
"RETURNS integer LANGUAGE sql AS $_$%s$_$" % SOURCE2
def test_create_operator_in_schema(self):
"Create a operator within a non-public schema"
inmap = self.std_map()
inmap.update({
'schema s1': {
'operator +(text, text)': {
'procedure': 'textcat'
}
}
})
sql = self.to_sql(inmap, ["CREATE SCHEMA s1"])
assert fix_indent(sql[0]) == "CREATE OPERATOR s1.+ " \
"(PROCEDURE = textcat, LEFTARG = text, RIGHTARG = text)"
def test_create_function1(self):
"Create a very simple function with no arguments"
inmap = self.std_map()
inmap['schema public'].update({
'function f1()': {
'language': 'sql',
'returns': 'text',
'source': SOURCE1,
'volatility': 'immutable'
}
})
sql = self.to_sql(inmap)
assert fix_indent(sql[1]) == CREATE_STMT1
def test_create_event_trigger_simple(self):
"Create a simple event trigger"
if self.db.version < 90300:
self.skipTest('Only available on PG 9.3')
inmap = self.std_map(plpgsql_installed=True)
inmap['schema public'].update({
'function f1()': {
'language': 'plpgsql',
'returns': 'event_trigger',
'source': FUNC_SRC
}
})
inmap.update({
'event trigger et1': {
'enabled': True,
'event': 'ddl_command_end',
'procedure': 'f1()'
}
})
sql = self.to_sql(inmap)
assert fix_indent(sql[1]) == CREATE_FUNC_STMT
assert fix_indent(sql[2]) == CREATE_STMT % ''
def test_create_trigger(self):
"Create a simple trigger"
inmap = self.std_map(plpgsql_installed=True)
inmap['schema public'].update({
'function f1()': {
'language': 'plpgsql',
'returns': 'trigger',
'source': FUNC_SRC
}
})
inmap['schema public'].update({
'table t1': {
'columns': [{
'c1': {
'type': 'integer'
}
}, {
'c2': {
'type': 'text'
}
}, {
'c3': {
'type': 'timestamp with time zone'
}
}],
'triggers': {
'tr1': {
'timing': 'before',
'events': ['insert', 'update'],
'level': 'row',
'procedure': 'f1()'
}
}
}
})
sql = self.to_sql(inmap)
assert fix_indent(sql[1]) == CREATE_FUNC_STMT
assert fix_indent(sql[2]) == CREATE_TABLE_STMT
assert fix_indent(sql[3]) == CREATE_STMT
def test_create_event_trigger_with_comment(self):
"Create an event trigger with a comment"
inmap = self.std_map(plpgsql_installed=True)
inmap['schema sd'].update({
'function f1()': {
'language': 'plpgsql',
'returns': 'event_trigger',
'source': FUNC_SRC
}
})
inmap.update({
'event trigger et1': {
'enabled': True,
'event': 'ddl_command_end',
'procedure': 'sd.f1()',
'description': 'Test event trigger et1'
}
})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == CREATE_FUNC_STMT
assert fix_indent(sql[1]) == CREATE_STMT % ''
assert sql[2] == COMMENT_STMT
def test_create_event_trigger_filter(self):
"Create an event trigger with tag filter variables"
inmap = self.std_map(plpgsql_installed=True)
inmap['schema sd'].update({
'function f1()': {
'language': 'plpgsql',
'returns': 'event_trigger',
'source': FUNC_SRC
}
})
inmap.update({
'event trigger et1': {
'enabled': True,
'event': 'ddl_command_end',
'procedure': 'sd.f1()',
'tags': ['CREATE TABLE', 'CREATE VIEW']
}
})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == CREATE_FUNC_STMT
assert fix_indent(sql[1]) == CREATE_STMT % (
"WHEN tag IN ('CREATE TABLE', 'CREATE VIEW') ")
def test_create_server_options(self):
"Create a foreign server with options"
inmap = self.std_map()
inmap.update({
'foreign data wrapper fdw1': {
'server fs1': {
'options': ['dbname=test']
}
}
})
sql = self.to_sql(inmap, [CREATE_FDW_STMT], superuser=True)
assert fix_indent(sql[0]) == "CREATE SERVER fs1 " \
"FOREIGN DATA WRAPPER fdw1 OPTIONS (dbname 'test')"
def test_create_table_with_defaults(self):
"Create a table with two column DEFAULTs, one referring to a SEQUENCE"
inmap = self.std_map()
inmap.update({'schema s1': {'table t1': {
'columns': [{'c1': {'type': 'integer', 'not_null': True,
'default':
"nextval('s1.t1_c1_seq'::regclass)"}},
{'c2': {'type': 'text', 'not_null': True,
'collation': 'en_US.utf8'}},
{'c3': {'type': 'date', 'not_null': True,
'default': "('now'::text)::date"}}]},
'sequence t1_c1_seq': {
'cache_value': 1, 'increment_by': 1, 'max_value': None,
'min_value': None, 'start_value': 1,
'owner_table': 't1', 'owner_column': 'c1'}}})
sql = self.to_sql(inmap, ["CREATE SCHEMA s1"])
assert fix_indent(sql[0]) == "CREATE SEQUENCE s1.t1_c1_seq " \
"START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1"
assert fix_indent(sql[1]) == "CREATE TABLE s1.t1 (c1 integer " \
"NOT NULL DEFAULT nextval('s1.t1_c1_seq'::regclass), c2 text " \
'NOT NULL COLLATE "en_US.utf8", c3 date NOT NULL ' \
"DEFAULT ('now'::text)::date)"
assert sql[2] == "ALTER SEQUENCE s1.t1_c1_seq OWNED BY s1.t1.c1"
def test_conversion_with_comment(self):
"Create a conversion with a comment"
inmap = self.std_map()
inmap['schema public'].update({
'conversion c1': {
'description': 'Test conversion c1',
'source_encoding': 'LATIN1',
'dest_encoding': 'UTF8',
'function': 'iso8859_1_to_utf8'
}
})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == CREATE_STMT
assert sql[1] == COMMENT_STMT
def test_view_with_comment(self):
"Create a materialized view with a comment"
if self.db.version < 90300:
self.skipTest('Only available on PG 9.3')
inmap = self.std_map()
inmap['schema public'].update({
'materialized view mv1': {
'definition': VIEW_STMT,
'description': "Test matview mv1"
}
})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == CREATE_STMT
assert sql[1] == COMMENT_STMT
def test_create_function_default_args(self):
"Create a function with default arguments"
inmap = self.std_map()
inmap['schema sd'].update({
'function f1(integer, INOUT integer)': {
'allargs': 'integer, INOUT integer DEFAULT 1',
'language': 'sql', 'returns': 'integer', 'source': SOURCE2,
'owner': self.db.user}})
sql = self.to_sql(inmap)
assert fix_indent(sql[1]) == \
"CREATE FUNCTION sd.f1(integer, INOUT integer DEFAULT 1) " \
"RETURNS integer LANGUAGE sql AS $_$%s$_$" % SOURCE2
assert sql[2] == "ALTER FUNCTION sd.f1(integer, INOUT integer) " \
"OWNER TO %s" % self.db.user
def test_create_server_type_version(self):
"Create a foreign server with type and version"
inmap = self.std_map()
inmap.update({
'foreign data wrapper fdw1': {
'server fs1': {
'type': 'test',
'version': '1.0'
}
}
})
sql = self.to_sql(inmap, [CREATE_FDW_STMT], superuser=True)
assert fix_indent(sql[0]) == "CREATE SERVER fs1 TYPE 'test' " \
"VERSION '1.0' FOREIGN DATA WRAPPER fdw1"
def test_create_extension_schema(self):
"Create a extension in a given schema"
inmap = self.std_map()
inmap.update({
'schema s1': {},
'extension pg_trgm': {
'schema': 's1',
'version': '1.0'
}
})
sql = self.to_sql(inmap)
assert sql[0] == 'CREATE SCHEMA s1'
assert fix_indent(sql[1]) == \
"CREATE EXTENSION pg_trgm SCHEMA s1 VERSION '1.0'"
def test_create_collation_schema(self):
"Create a collation in a non-default schema"
inmap = self.std_map()
inmap.update({
'schema s1': {
'collation c1': {
'lc_collate': COLL,
'lc_ctype': COLL
}
}
})
sql = self.to_sql(inmap, ["CREATE SCHEMA s1"])
assert fix_indent(sql[0]) == "CREATE COLLATION s1.c1 (" \
"LC_COLLATE = '%s', LC_CTYPE = '%s')" % (COLL, COLL)
def test_create_security_definer_function(self):
"Create a SECURITY DEFINER function"
inmap = self.std_map()
inmap['schema public'].update({
'function f1()': {
'language': 'sql',
'returns': 'text',
'source': SOURCE1,
'security_definer': True
}
})
sql = self.to_sql(inmap)
assert fix_indent(sql[1]) == "CREATE FUNCTION f1() RETURNS text " \
"LANGUAGE sql SECURITY DEFINER AS $_$%s$_$" % SOURCE1
def test_create_sequence(self):
"Create a sequence"
inmap = self.std_map()
inmap['schema public'].update({
'sequence seq1': {
'start_value': 1,
'increment_by': 1,
'max_value': None,
'min_value': None,
'cache_value': 1
}
})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == CREATE_STMT_FULL
def test_create_ts_parser(self):
"Create a text search parser that didn't exist"
inmap = self.std_map()
inmap['schema sd'].update({
'text search parser tsp1': {
'start': 'prsd_start',
'gettoken': 'prsd_nexttoken',
'end': 'prsd_end',
'lextypes': 'prsd_lextype',
'headline': 'prsd_headline'
}
})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == CREATE_TSP_STMT
def test_create_ts_config_in_schema(self):
"Create a text search config with parser in non-default schema"
inmap = self.std_map()
inmap.update({
'schema s1': {
'text search parser tsp1': {
'start': 'prsd_start',
'gettoken': 'prsd_nexttoken',
'end': 'prsd_end',
'lextypes': 'prsd_lextype'
}
}
})
inmap['schema sd'].update(
{'text search configuration tsc1': {
'parser': 's1.tsp1'
}})
sql = self.to_sql(inmap, ["CREATE SCHEMA s1"])
assert fix_indent(sql[0]) == "CREATE TEXT SEARCH PARSER s1.tsp1 " \
"(START = prsd_start, GETTOKEN = prsd_nexttoken, " \
"END = prsd_end, LEXTYPES = prsd_lextype)"
assert fix_indent(sql[1]) == \
"CREATE TEXT SEARCH CONFIGURATION sd.tsc1 (PARSER = s1.tsp1)"
def test_create_lang_extension(self):
"Create a language extension and a function in that language"
if self.db.version < 90100:
self.skipTest('Only available on PG 9.1')
inmap = self.std_map()
inmap.update({
'extension plperl': {
'schema': 'pg_catalog',
'description': "PL/Perl procedural language"
}
})
inmap['schema public'].update({
'function f1()': {
'language': 'plperl',
'returns': 'text',
'source': "return \"dummy\";"
}
})
sql = self.to_sql(inmap)
assert fix_indent(sql[0]) == "CREATE EXTENSION plperl"
# skip over COMMENT and SET statements
assert fix_indent(sql[3]) == "CREATE FUNCTION f1() RETURNS text " \
"LANGUAGE plperl AS $_$return \"dummy\";$_$"
def test_inherit_add_parent_column(self):
"Add a column to parent table, child should not add as well"
stmts = [CREATE_STMT1, "CREATE TABLE t2 (c3 date) INHERITS (t1)"]
inmap = self.std_map()
inmap['schema public'].update({
'table t1': {
'columns': [{
'c1': {
'type': 'integer'
}
}, {
'c2': {
'type': 'text'
}
}, {
'c4': {
'type': 'text'
}
}]
}
})
inmap['schema public'].update({
'table t2': {
'columns': [{
'c1': {
'type': 'integer',
'inherited': True
}
}, {
'c2': {
'type': 'text',
'inherited': True
}
}, {
'c3': {
'type': 'date'
}
}, {
'c4': {
'type': 'text',
'inherited': True
}
}],
'inherits': ['t1']
}
})
sql = self.to_sql(inmap, stmts)
assert len(sql) == 1
assert fix_indent(sql[0]) == "ALTER TABLE t1 ADD COLUMN c4 text"