def test_bad_table_map(self): "Error creating a table with a bad map" inmap = new_std_map() inmap['schema public'].update({'t1': { 'columns': [{'c1': {'type': 'integer'}}, {'c2': {'type': 'text'}}]}}) self.assertRaises(KeyError, self.db.process_map, inmap)
def test_create_table_with_defaults(self): "Create a table with two column DEFAULTs, one referring to a SEQUENCE" self.db.execute(DROP_STMT) self.db.execute_commit("DROP SEQUENCE IF EXISTS t1_c1_seq") inmap = new_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_bad_sequence_map(self): "Error creating a sequence with a bad map" inmap = new_std_map() inmap['schema public'].update({'seq1': { 'start_value': 1, 'increment_by': 1, 'max_value': None, 'min_value': None, 'cache_value': 1}}) self.assertRaises(KeyError, self.db.process_map, inmap)
def test_drop_enum(self): "Drop an existing enum" self.db.execute(DROP_STMT) self.db.execute_commit(CREATE_ENUM_STMT) inmap = new_std_map() dbsql = self.db.process_map(inmap) self.assertEqual(dbsql, ["DROP TYPE t1"])
def test_drop_table(self): "Drop an existing table" self.db.execute(DROP_STMT) self.db.execute_commit(CREATE_STMT) inmap = new_std_map() dbsql = self.db.process_map(inmap) self.assertEqual(dbsql, ["DROP TABLE t1"])
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_bad_conversion_map(self): "Error creating a conversion with a bad map" inmap = new_std_map() inmap["schema public"].update( {"c1": {"source_encoding": "LATIN1", "dest_encoding": "UTF8", "function": "iso8859_1_to_utf8"}} ) self.assertRaises(KeyError, self.db.process_map, inmap)
def test_drop_cast(self): "Drop an existing cast" self.db.execute(DROP_STMT) self.db.execute(CREATE_FUNC) self.db.execute_commit(CREATE_STMT1) dbsql = self.db.process_map(new_std_map()) self.assertEqual(dbsql[0], "DROP CAST (smallint AS boolean)")
def test_bad_function_map(self): "Error creating a function with a bad map" inmap = new_std_map() inmap['schema public'].update({'f1()': { 'language': 'sql', 'returns': 'text', 'source': SOURCE1}}) self.assertRaises(KeyError, self.db.process_map, inmap)
def test_drop_composite(self): "Drop an existing composite" self.db.execute(DROP_STMT) self.db.execute_commit(CREATE_COMPOSITE_STMT) inmap = new_std_map() dbsql = self.db.process_map(inmap) self.assertEqual(dbsql, ["DROP TYPE t1"])
def test_drop_rule_table(self): "Drop an existing rule and the related table" self.db.execute(CREATE_TABLE_STMT) self.db.execute_commit(CREATE_STMT % ("INSERT", "NOTHING")) dbsql = self.db.process_map(new_std_map()) self.assertEqual(dbsql[0], "DROP RULE r1 ON t1") self.assertEqual(dbsql[1], "DROP TABLE t1")
def test_create_domain(self): "Create a simple domain" self.db.execute_commit(DROP_STMT) inmap = new_std_map() inmap['schema public'].update({'domain d1': {'type': 'integer'}}) dbsql = self.db.process_map(inmap) self.assertEqual(dbsql, [CREATE_STMT])
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_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_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_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_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 test_bad_cast_map(self): "Error creating a cast with a bad map" inmap = new_std_map() inmap.update({'(smallint AS boolean)': { 'function': 'int2_bool(smallint)', 'context': 'explicit', 'method': 'function'}}) self.assertRaises(KeyError, self.db.process_map, inmap)
def test_change_trigger_comment(self): "Change existing comment on a trigger" self.db.execute(CREATE_TABLE_STMT) self.db.execute(CREATE_FUNC_STMT) self.db.execute(CREATE_STMT) self.db.execute_commit(COMMENT_STMT) 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": "Changed trigger tr1", "timing": "before", "events": ["insert", "update"], "level": "row", "procedure": "f1()", } }, } } ) dbsql = self.db.process_map(inmap) self.assertEqual(dbsql, ["COMMENT ON TRIGGER tr1 ON t1 IS 'Changed trigger tr1'"])
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_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_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_drop_domain(self): "Drop an existing domain" self.db.execute(DROP_STMT) self.db.execute_commit(CREATE_STMT) inmap = new_std_map() dbsql = self.db.process_map(inmap) self.assertEqual(dbsql, ["DROP DOMAIN d1"])
def test_create_domain_default(self): "Create a domain with a DEFAULT and NOT NULL" self.db.execute_commit(DROP_STMT) inmap = new_std_map() inmap['schema public'].update({'domain d1': { 'type': 'integer', 'not_null': True, 'default': 0}}) dbsql = self.db.process_map(inmap) self.assertEqual(dbsql, [CREATE_STMT + " NOT NULL DEFAULT 0"])
def test_create_enum(self): "Create an enum" self.db.execute_commit(DROP_STMT) inmap = new_std_map() inmap['schema public'].update({'type t1': { 'labels': ['red', 'green', 'blue']}}) dbsql = self.db.process_map(inmap) self.assertEqual(fix_indent(dbsql[0]), CREATE_ENUM_STMT)
def _tblmap(self): "Return a table input map with a comment" inmap = new_std_map() inmap['schema public'].update({'table t1': { 'description': 'Test table t1', 'columns': [{'c1': {'type': 'integer'}}, {'c2': {'type': 'text'}}]}}) return inmap
def test_create_operator(self): "Create a simple operator" self.db.execute_commit(DROP_STMT) inmap = new_std_map() inmap['schema public'].update({'operator +(text, text)': { 'procedure': 'textcat'}}) dbsql = self.db.process_map(inmap) self.assertEqual(fix_indent(dbsql[0]), CREATE_STMT)
def test_create_conversion(self): "Create a conversion" inmap = new_std_map() inmap["schema public"].update( {"conversion c1": {"source_encoding": "LATIN1", "dest_encoding": "UTF8", "function": "iso8859_1_to_utf8"}} ) dbsql = self.db.process_map(inmap) self.assertEqual(fix_indent(dbsql[0]), CREATE_STMT)
def test_create_function(self): "Create a very simple function with no arguments" self.db.execute_commit(DROP_STMT1) inmap = new_std_map() inmap['schema public'].update({'function f1()': { 'language': 'sql', 'returns': 'text', 'source': SOURCE1, 'volatility': 'immutable'}}) dbsql = self.db.process_map(inmap) self.assertEqual(fix_indent(dbsql[1]), CREATE_STMT1)
def test_rename_domain(self): "Rename an existing domain" self.db.execute(DROP_STMT) self.db.execute_commit(CREATE_STMT) inmap = new_std_map() inmap['schema public'].update({'domain d2': { 'oldname': 'd1', 'type': 'integer'}}) dbsql = self.db.process_map(inmap) self.assertEqual(dbsql, ["ALTER DOMAIN d1 RENAME TO d2"])