예제 #1
0
 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')")
예제 #2
0
 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
예제 #3
0
 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)")
예제 #4
0
 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)")
예제 #5
0
 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)')
예제 #6
0
 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
예제 #7
0
 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"
예제 #8
0
 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)"
예제 #9
0
 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')
예제 #10
0
 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)"
예제 #11
0
 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)"
예제 #12
0
 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"
예제 #13
0
 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)"
예제 #14
0
 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"
예제 #15
0
 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)"
예제 #16
0
 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"
예제 #17
0
 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"
예제 #18
0
 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'))
예제 #19
0
 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)"
예제 #20
0
 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)")
예제 #21
0
 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 % ''
예제 #22
0
 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"
예제 #23
0
    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")
예제 #24
0
 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"
예제 #25
0
 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")
예제 #26
0
 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') ")
예제 #27
0
 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)"
예제 #28
0
 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 % ''
예제 #29
0
 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";$_$'
예제 #30
0
 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
예제 #31
0
 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"
예제 #32
0
 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
예제 #33
0
 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
예제 #34
0
 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()"
예제 #35
0
 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)"
예제 #36
0
 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
예제 #37
0
파일: test_cast.py 프로젝트: jonike/Pyrseas
 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
예제 #38
0
파일: test_cast.py 프로젝트: jonike/Pyrseas
 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
예제 #39
0
 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.&&))"
예제 #40
0
 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
예제 #41
0
 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)"
예제 #42
0
 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
예제 #43
0
 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 % ''
예제 #44
0
 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
예제 #45
0
 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
예제 #46
0
 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') ")
예제 #47
0
 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')"
예제 #48
0
 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"
예제 #49
0
 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
예제 #50
0
 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
예제 #51
0
 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
예제 #52
0
 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"
예제 #53
0
 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'"
예제 #54
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)
예제 #55
0
 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
예제 #56
0
 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
예제 #57
0
 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
예제 #58
0
 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)"
예제 #59
0
 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\";$_$"
예제 #60
0
 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"