def test_create_trigger2(self):
"Create another simple trigger with"
inmap = new_std_map()
inmap.update({"language plpgsql": {"trusted": 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": "after", "events": ["delete", "truncate"], "procedure": "f1()"}},
}
}
)
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[1]), CREATE_FUNC_STMT)
self.assertEqual(fix_indent(dbsql[2]), CREATE_TABLE_STMT)
self.assertEqual(
fix_indent(dbsql[3]),
"CREATE TRIGGER tr1 AFTER DELETE OR TRUNCATE ON t1 " "FOR EACH STATEMENT EXECUTE PROCEDURE f1()",
)
def test_create_rule_multi_actions(self):
"Create a rule with multiple actions"
inmap = new_std_map()
inmap["schema public"].update(
{
"table t1": {
"columns": [{"c1": {"type": "integer"}}, {"c2": {"type": "text"}}],
"rules": {
"r1": {
"event": "update",
"actions": "(INSERT INTO t1 VALUES (old.c1 + 100); "
"INSERT INTO t1 VALUES (old.c1 + 200));)",
}
},
}
}
)
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 (INSERT INTO t1 VALUES (old.c1 + 100); "
"INSERT INTO t1 VALUES (old.c1 + 200));)",
)
def test_create_trigger(self):
"Create a constraint trigger"
inmap = new_std_map()
inmap.update({"language plpgsql": {"trusted": 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": {
"constraint": True,
"timing": "after",
"events": ["insert", "update"],
"level": "row",
"procedure": "f1()",
}
},
}
}
)
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[1]), CREATE_FUNC_STMT)
self.assertEqual(fix_indent(dbsql[2]), CREATE_TABLE_STMT)
self.assertEqual(
fix_indent(dbsql[3]),
"CREATE CONSTRAINT TRIGGER tr1 " "AFTER INSERT OR UPDATE ON t1 " "FOR EACH ROW EXECUTE PROCEDURE f1()",
)
def test_create_with_foreign_key(self):
"Create a table with a foreign key constraint"
self.db.execute_commit(DROP_STMT + ", t2")
inmap = new_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'}}}}})
dbsql = self.db.process_map(inmap)
# can't control which table will be created first
crt1 = 0
crt2 = 1
if 't1' in dbsql[1]:
crt1 = 1
crt2 = 0
self.assertEqual(fix_indent(dbsql[crt1]),
"CREATE TABLE t1 (c11 integer, c12 text)")
self.assertEqual(fix_indent(dbsql[crt2]),
"CREATE TABLE t2 (c21 integer, c22 text, "
"c23 integer)")
self.assertEqual(fix_indent(dbsql[2]),
"ALTER TABLE t2 ADD CONSTRAINT t2_c23_fkey "
"FOREIGN KEY (c23) REFERENCES t1 (c11)")
def test_create_rule_conditional(self):
"Create a rule with qualification"
inmap = new_std_map()
inmap["schema public"].update(
{
"table t1": {
"columns": [{"c1": {"type": "integer"}}, {"c2": {"type": "text"}}],
"rules": {
"r1": {
"event": "delete",
"condition": "old.c1 < 1000",
"actions": "INSERT INTO t1 VALUES (" "old.c1 + 1000, old.c2)",
}
},
}
}
)
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 DELETE "
"TO t1 WHERE old.c1 < 1000 "
"DO INSERT INTO t1 VALUES (old.c1 + 1000, old.c2)",
)
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_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 = new_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_trigger_with_comment(self):
"Create a trigger with a comment"
inmap = new_std_map()
inmap.update({"language plpgsql": {"trusted": 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": {
"description": "Test trigger tr1",
"timing": "before",
"events": ["insert", "update"],
"level": "row",
"procedure": "f1()",
}
},
}
}
)
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[1]), CREATE_FUNC_STMT)
self.assertEqual(fix_indent(dbsql[2]), CREATE_TABLE_STMT)
self.assertEqual(fix_indent(dbsql[3]), CREATE_STMT)
self.assertEqual(dbsql[4], COMMENT_STMT)
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_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_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}}}})
dbsql = self.db.process_map(inmap)
# can't control which table/constraint will be created first
dbsql[0:2] = list(sorted(dbsql[0:2]))
dbsql[2:5] = list(sorted(dbsql[2:5]))
self.assertEqual(fix_indent(dbsql[0]),
"CREATE TABLE t1 (c11 integer, c12 text)")
self.assertEqual(fix_indent(dbsql[1]),
"CREATE TABLE t2 (c21 integer, c22 text, "
"c23 integer, c24 integer, c25 integer)")
self.assertEqual(fix_indent(dbsql[2]),
"ALTER TABLE t2 ADD CONSTRAINT t2_c23_fkey "
"FOREIGN KEY (c23) REFERENCES t1 (c11)")
self.assertEqual(fix_indent(dbsql[3]),
"ALTER TABLE t2 ADD CONSTRAINT t2_c24_fkey "
"FOREIGN KEY (c24) REFERENCES t1 (c11) "
"DEFERRABLE")
self.assertEqual(fix_indent(dbsql[4]),
"ALTER TABLE t2 ADD CONSTRAINT t2_c25_fkey "
"FOREIGN KEY (c25) REFERENCES t1 (c11) "
"DEFERRABLE INITIALLY DEFERRED")
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_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"}})
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[0]), "CREATE TABLE t1 (c1 integer, "
"c2 text, c3 integer)")
self.assertEqual(fix_indent(dbsql[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 = new_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_rule_nothing(self):
"Create a rule"
inmap = new_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_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'}}}})
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[0]),
"CREATE TABLE t1 (c1 integer, c2 text)")
self.assertEqual(fix_indent(dbsql[1]),
"ALTER TABLE t1 ADD CONSTRAINT cns1 CHECK (c1 > 50)")
self.assertEqual(dbsql[2], COMMENT_STMT)
def test_table_inheritance(self):
"Create a table that inherits from another"
inmap = self.std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c1': {'type': 'integer'}},
{'c2': {'type': 'text'}}]}})
inmap['schema public'].update({'table t2': {
'columns': [{'c1': {'type': 'integer', 'inherited': True}},
{'c2': {'type': 'text', 'inherited': True}},
{'c3': {'type': 'numeric'}}],
'inherits': ['t1']}})
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[0]), CREATE_STMT)
self.assertEqual(fix_indent(dbsql[1]), "CREATE TABLE t2 (c3 numeric) "
"INHERITS (t1)")
def test_create_rule_multi_actions(self):
"Create a rule with multiple actions"
inmap = self.std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c1': {'type': 'integer'}},
{'c2': {'type': 'text'}}],
'rules': {'r1': {
'event': 'update', 'actions':
"(INSERT INTO t1 VALUES (old.c1 + 100); " \
"INSERT INTO t1 VALUES (old.c1 + 200));)"}}}})
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 (INSERT INTO t1 VALUES (old.c1 + 100); "
"INSERT INTO t1 VALUES (old.c1 + 200));)")
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'}}}})
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[0]),
"CREATE TABLE t1 (c1 integer, c2 text)")
self.assertEqual(fix_indent(dbsql[1]),
"ALTER TABLE t1 ADD CONSTRAINT t1_c1_check "
"CHECK (c1 > 0 and c1 < 1000000)")
def test_create_rule_conditional(self):
"Create a rule with qualification"
inmap = self.std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c1': {'type': 'integer'}},
{'c2': {'type': 'text'}}],
'rules': {'r1': {'event': 'delete',
'condition': "old.c1 < 1000",
'actions': "INSERT INTO t1 VALUES (" \
"old.c1 + 1000, old.c2)"}}}})
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 DELETE "
"TO t1 WHERE old.c1 < 1000 "
"DO INSERT INTO t1 VALUES (old.c1 + 1000, old.c2)")
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'],
'access_method': 'btree'}}}})
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[0]),
"CREATE TABLE t1 (c1 integer, c2 text)")
self.assertEqual(fix_indent(dbsql[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'],
'access_method': 'btree'}}}})
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[0]),
"CREATE TABLE t1 (c1 text, c2 integer)")
self.assertEqual(fix_indent(dbsql[1]),
"ALTER TABLE t1 ADD CONSTRAINT t1_pkey "
"PRIMARY KEY (c2)")
def test_drop_add_column3(self):
"Drop and re-add table columns from table with dropped column"
self.db.execute(CREATE_STMT2)
self.db.execute_commit(DROP_COL_STMT)
inmap = self.std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c2': {'type': 'text'}},
{'c3': {'type': 'date'}},
{'c4': {'type': 'text'}}]}})
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[0]),
"ALTER TABLE t1 ADD COLUMN c3 date")
self.assertEqual(fix_indent(dbsql[1]),
"ALTER TABLE t1 ADD COLUMN c4 text")
self.assertEqual(dbsql[2], "ALTER TABLE t1 DROP COLUMN c1")
def test_add_column4(self):
"Add two columns to a table that has a dropped column"
self.db.execute(CREATE_STMT2)
self.db.execute_commit(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'}}]}})
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[0]),
"ALTER TABLE t1 ADD COLUMN c3 date")
self.assertEqual(fix_indent(dbsql[1]),
"ALTER TABLE t1 ADD COLUMN c4 text")
def test_add_foreign_key(self):
"Add a two-column foreign key to an existing table"
self.db.execute(DROP_STMT + ", t2")
self.db.execute("CREATE TABLE t1 (c11 INTEGER NOT NULL, "
"c12 INTEGER NOT NULL, c13 TEXT, "
"PRIMARY KEY (c11, c12))")
self.db.execute_commit("CREATE TABLE t2 (c21 INTEGER NOT NULL, "
"c22 TEXT, c23 INTEGER, c24 INTEGER, "
"PRIMARY KEY (c21))")
inmap = new_std_map()
inmap['schema public'].update({
'table t1': {'columns': [
{'c11': {'type': 'integer', 'not_null': True}},
{'c12': {'type': 'integer', 'not_null': True}},
{'c13': {'type': 'text'}}],
'primary_key': {'t1_pkey': {
'columns': ['c11', 'c12'],
'access_method': 'btree'}}},
'table t2': {'columns': [
{'c21': {'type': 'integer', 'not_null': True}},
{'c22': {'type': 'text'}},
{'c23': {'type': 'integer'}},
{'c24': {'type': 'integer'}}],
'primary_key': {'t2_pkey': {
'columns': ['c21'],
'access_method': 'btree'}},
'foreign_keys': {'t2_c23_fkey': {
'columns': ['c23', 'c24'],
'references': {'columns': ['c11', 'c12'],
'table': 't1'}}}}})
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[0]),
"ALTER TABLE t2 ADD CONSTRAINT t2_c23_fkey "
"FOREIGN KEY (c23, c24) REFERENCES t1 (c11, c12)")
def get_response(self, prompt, btn):
response = openai.Completion.create(
engine="curie-instruct-beta",
prompt=prompt,
temperature=0.8,
max_tokens=250,
top_p=1,
best_of=5,
frequency_penalty=0,
presence_penalty=0,
stop=["###"]
)
new_kv = fix_indent(response.choices[0].text)
print(new_kv)
result_layout = self.app_root.ids.result_layout
code_input = self.app_root.ids.code_input
code_input.lexer = KivyLexer()
result_layout.clear_widgets()
try:
code_input.text = new_kv+"\n"
new_widget = Builder.load_string(new_kv)
result_layout.add_widget(new_widget)
except Exception as e:
print(e)
info_label = InfoLabel()
info_label.text = str(e)
info_label._color = "Red"
info_label.font_style = "H5"
result_layout.add_widget(info_label)
btn.disabled = False
def test_add_columns(self):
"Add two new columns to a table"
self.db.execute(DROP_STMT)
self.db.execute_commit(CREATE_STMT)
inmap = new_std_map()
inmap['schema public'].update({'table t1': {
'columns': [{'c1': {'type': 'integer'}},
{'c2': {'type': 'text'}},
{'c3': {'type': 'smallint', 'not_null': True}},
{'c4': {'type': 'date',
'default': 'now()'}}]}})
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[0]),
"ALTER TABLE t1 ADD COLUMN c3 smallint NOT NULL")
self.assertEqual(fix_indent(dbsql[1]),
"ALTER TABLE t1 ADD COLUMN c4 date DEFAULT now()")
def test_create_trigger_in_schema(self):
"Create a trigger within a non-public schema"
self.db.execute("DROP SCHEMA IF EXISTS s1 CASCADE")
self.db.execute_commit("CREATE SCHEMA s1")
inmap = new_std_map()
inmap.update({"language plpgsql": {"trusted": True}})
inmap.update(
{
"schema s1": {
"function f1()": {"language": "plpgsql", "returns": "trigger", "source": FUNC_SRC},
"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()",
}
},
},
}
}
)
dbsql = self.db.process_map(inmap)
self.assertEqual(
fix_indent(dbsql[3]),
"CREATE TRIGGER tr1 " "BEFORE INSERT OR UPDATE ON s1.t1 FOR EACH ROW " "EXECUTE PROCEDURE f1()",
)
self.db.execute_commit("DROP SCHEMA s1 CASCADE")
def test_create_aggregate(self):
"Create a simple aggregate"
self.db.execute("DROP AGGREGATE IF EXISTS a1(integer)")
self.db.execute_commit(DROP_STMT2)
inmap = new_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'}})
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[1]), CREATE_STMT2)
self.assertEqual(fix_indent(dbsql[2]),
"CREATE AGGREGATE a1(integer) "
"(SFUNC = f1, STYPE = integer)")
def test_create_ts_dict(self):
"Create a text search dictionary that didn't exist"
inmap = self.std_map()
inmap['schema public'].update({'text search dictionary tsd1': {
'template': 'simple', 'options': "stopwords = 'english'"}})
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[0]), CREATE_TSD_STMT)
def test_create_ts_config(self):
"Create a text search configuration that didn't exist"
inmap = self.std_map()
inmap['schema public'].update({'text search configuration tsc1': {
'parser': 'tsp1'}})
dbsql = self.db.process_map(inmap)
self.assertEqual(fix_indent(dbsql[0]), CREATE_TSC_STMT)
