def test_change_column_data_type_no_log_int(self):
# The tuple is here because it's required by the class. It's not used in this test
single_tuple = ('my_schema.my_table', 'column_one', 'INTEGER')
CD = ColumnDDL(tuple_or_tuple_list=single_tuple,
change_type='data_type',
has_log=False,
case='UPPER')
query = CD.change_column_data_type(schema_name='MY_SCHEMA',
table_name='MY_TABLE',
column_name='MY_COLUMN',
new_column_type='INTEGER',
new_column_type_no_count='INTEGER')
expected_query = """BEGIN TRANSACTION;
ALTER TABLE "MY_SCHEMA"."MY_TABLE"
RENAME COLUMN "MY_COLUMN" TO "MY_COLUMN_TMP";
ALTER TABLE "MY_SCHEMA"."MY_TABLE"
ADD COLUMN "MY_COLUMN" INTEGER;
UPDATE "MY_SCHEMA"."MY_TABLE"
SET "MY_COLUMN" = "MY_COLUMN_TMP"::INTEGER
WHERE "MY_COLUMN" IS NULL;
ALTER TABLE "MY_SCHEMA"."MY_TABLE"
DROP COLUMN "MY_COLUMN_TMP" CASCADE;
COMMIT;"""
self.assertEqual(re.sub(r'[\n\t\s]*', '', expected_query),
re.sub(r'[\n\t\s]*', '', query))
def test_create_ddl_statements_add_two_columns_has_log_upper(self):
tuple_list = [('schema_name.table_name', 'column_one',
'VARCHAR(16777216)'),
('schema_name.table_name', 'column_two', 'INTEGER')]
CD = ColumnDDL(tuple_or_tuple_list=tuple_list,
change_type='add',
has_log=True,
case='UPPER')
query_list = CD.create_ddl_statements()
expected_query_list = """ALTER TABLE "SCHEMA_NAME"."TABLE_NAME"
ADD COLUMN "COLUMN_ONE" VARCHAR(16777216);
ALTER TABLE "SCHEMA_NAME"."TABLE_NAME_LOG"
ADD COLUMN "COLUMN_ONE" VARCHAR(16777216);
ALTER TABLE "SCHEMA_NAME"."TABLE_NAME"
ADD COLUMN "COLUMN_TWO" INTEGER;
ALTER TABLE "SCHEMA_NAME"."TABLE_NAME_LOG"
ADD COLUMN "COLUMN_TWO" INTEGER;"""
self.assertEqual(re.sub(r'\W', '', expected_query_list),
re.sub(r'\W', '', query_list))
def test_create_ddl_statements_drop_two_columns_has_log_upper(self):
tuple_list = [('schema_name.table_name', 'column_one'),
('schema_name.table_name', 'column_two')]
CD = ColumnDDL(tuple_or_tuple_list=tuple_list,
change_type='drop',
has_log=True,
case='UPPER')
query_list = CD.create_ddl_statements()
expected_query_list = """BEGIN TRANSACTION;
ALTER TABLE "SCHEMA_NAME"."TABLE_NAME"
DROP COLUMN "COLUMN_ONE" CASCADE;
ALTER TABLE "SCHEMA_NAME"."TABLE_NAME_LOG"
DROP COLUMN "COLUMN_ONE" CASCADE ;
COMMIT;
BEGIN TRANSACTION;
ALTER TABLE "SCHEMA_NAME"."TABLE_NAME"
DROP COLUMN "COLUMN_TWO" CASCADE;
ALTER TABLE "SCHEMA_NAME"."TABLE_NAME_LOG"
DROP COLUMN "COLUMN_TWO" CASCADE ;
COMMIT;"""
self.assertEqual(re.sub(r'\W', '', expected_query_list),
re.sub(r'\W', '', query_list))
def test_create_ddl_statements_combine_two_columns_no_log_upper(self):
tuple_list = [('my_schema.my_table', 'my_bad_column_name_one',
'my_good_column_name_one', 'INTEGER'),
('my_schema.my_table', 'my_bad_column_name_two',
'my_good_column_name_two', 'VARCHAR(16777216)')]
CD = ColumnDDL(tuple_or_tuple_list=tuple_list,
change_type='combine',
has_log=False,
case='UPPER')
query_list = CD.create_ddl_statements()
expected_query_list = """BEGIN TRANSACTION;
UPDATE TABLE "MY_SCHEMA"."MY_TABLE"
SET "MY_GOOD_COLUMN_NAME_ONE" = "MY_BAD_COLUMN_NAME_ONE"::INTEGER
WHERE "MY_GOOD_COLUMN_NAME_ONE" IS NULL;
ALTER TABLE "MY_SCHEMA"."MY_TABLE"
DROP COLUMN "MY_BAD_COLUMN_NAME_ONE" CASCADE;
COMMIT;
BEGIN TRANSACTION;
UPDATE TABLE "MY_SCHEMA"."MY_TABLE"
SET "MY_GOOD_COLUMN_NAME_TWO" = "MY_BAD_COLUMN_NAME_TWO"::VARCHAR(16777216)
WHERE "MY_GOOD_COLUMN_NAME_TWO" IS NULL;
ALTER TABLE "MY_SCHEMA"."MY_TABLE"
DROP COLUMN "MY_BAD_COLUMN_NAME_TWO" CASCADE;
COMMIT;"""
self.assertEqual(re.sub(r'[\n\t\s]*', '', expected_query_list),
re.sub(r'[\n\t\s]*', '', query_list))
def test_create_ddl_statements_combine_two_columns_no_log_lower(self):
tuple_list = [('my_schema.my_table', 'my_bad_column_name_one',
'my_good_column_name_one', 'INTEGER'),
('my_schema.my_table', 'my_bad_column_name_two',
'my_good_column_name_two', 'VARCHAR(16777216)')]
CD = ColumnDDL(tuple_or_tuple_list=tuple_list,
change_type='combine',
has_log=False,
case='lower')
query_list = CD.create_ddl_statements()
expected_query_list = """begin transaction;
update table "my_schema"."my_table"
set "my_good_column_name_one" = "my_bad_column_name_one"::integer
where "my_good_column_name_one" is null;
alter table "my_schema"."my_table"
drop column "my_bad_column_name_one" cascade;
commit;
begin transaction;
update table "my_schema"."my_table"
set "my_good_column_name_two" = "my_bad_column_name_two"::varchar(16777216)
where "my_good_column_name_two" is null;
alter table "my_schema"."my_table"
drop column "my_bad_column_name_two" cascade;
commit;"""
self.assertEqual(re.sub(r'[\n\t\s]*', '', expected_query_list),
re.sub(r'[\n\t\s]*', '', query_list))
def test_create_ddl_statements_rename_two_columns_has_log_upper(self):
tuple_list = [
('schema_name.table_name', 'column_one', 'new_column_one'),
('schema_name.table_name', 'column_two', 'new_column_two')
]
CD = ColumnDDL(tuple_or_tuple_list=tuple_list,
change_type='rename',
has_log=True,
case='UPPER')
query_list = CD.create_ddl_statements()
expected_query_list = """ALTER TABLE "SCHEMA_NAME"."TABLE_NAME"
RENAME COLUMN "COLUMN_ONE" TO "NEW_COLUMN_ONE";
ALTER TABLE "SCHEMA_NAME"."TABLE_NAME_LOG"
RENAME COLUMN "COLUMN_ONE" TO "NEW_COLUMN_ONE";
ALTER TABLE "SCHEMA_NAME"."TABLE_NAME"
RENAME COLUMN "COLUMN_TWO" TO "NEW_COLUMN_TWO";
ALTER TABLE "SCHEMA_NAME"."TABLE_NAME_LOG"
RENAME COLUMN "COLUMN_TWO" TO "NEW_COLUMN_TWO";
"""
self.assertEqual(re.sub(r'\W', '', expected_query_list),
re.sub(r'\W', '', query_list))
def test_combine_column_has_log(self):
# The tuple is here because it's required by the class. It's not used in this test
single_tuple = ('schema_name.table_name', 'my_bad_column',
'my_good_column', 'INT')
CD = ColumnDDL(tuple_or_tuple_list=single_tuple,
change_type='combine',
has_log=True,
case='UPPER')
query = CD.combine_columns(schema_name='MY_SCHEMA',
table_name='MY_TABLE',
column_to_drop='MY_BAD_COLUMN_NAME',
column_to_keep='MY_GOOD_COLUMN_NAME',
column_to_keep_type='INTEGER')
expected_query = """BEGIN TRANSACTION;
UPDATE TABLE "MY_SCHEMA"."MY_TABLE"
SET "MY_GOOD_COLUMN_NAME" = "MY_BAD_COLUMN_NAME"::INTEGER
WHERE "MY_GOOD_COLUMN_NAME" IS NULL;
ALTER TABLE "MY_SCHEMA"."MY_TABLE"
DROP COLUMN "MY_BAD_COLUMN_NAME" CASCADE;
UPDATE TABLE "MY_SCHEMA"."MY_TABLE_LOG"
SET "MY_GOOD_COLUMN_NAME" = "MY_BAD_COLUMN_NAME"::INTEGER
WHERE "MY_GOOD_COLUMN_NAME" IS NULL;
ALTER TABLE "MY_SCHEMA"."MY_TABLE_LOG"
DROP COLUMN "MY_BAD_COLUMN_NAME" CASCADE;
COMMIT;"""
self.assertEqual(re.sub(r'[\n\t\s]*', '', expected_query),
re.sub(r'[\n\t\s]*', '', query))
def test_create_ddl_statements_change_data_type_one_column_no_log_lower_varchar(
self):
single_tuple = ('my_schema.my_table', 'column_one',
'VARCHAR(16777216)')
CD = ColumnDDL(tuple_or_tuple_list=single_tuple,
change_type='data_type',
has_log=False,
case='lower')
query_list = CD.create_ddl_statements()
expected_query = """begin transaction;
alter table "my_schema"."my_table"
rename column "column_one" to "column_one_tmp";
alter table "my_schema"."my_table"
add column "column_one" varchar(16777216);
update "my_schema"."my_table"
set "column_one" = "column_one_tmp"::varchar(16777216)
where "column_one" is null;
alter table "my_schema"."my_table"
drop column "column_one_tmp" cascade;
commit;"""
self.assertEqual(re.sub(r'\W', '', expected_query),
re.sub(r'\W', '', query_list))
def test_create_ddl_statements_combine_two_columns_has_log_upper(self):
tuple_list = [('my_schema.my_table', 'my_bad_column_name_one',
'my_good_column_name_one', 'INTEGER'),
('my_schema.my_table', 'my_bad_column_name_two',
'my_good_column_name_two', 'VARCHAR(16777216)')]
CD = ColumnDDL(tuple_or_tuple_list=tuple_list,
change_type='combine',
has_log=True,
case='UPPER')
query_list = CD.create_ddl_statements()
expected_query_list = """BEGIN TRANSACTION;
def test_rename_column_no_log(self):
# The tuple is here because it's required by the class. It's not used in this test
single_tuple = ('my_schema.my_table', 'my_column', 'my_new_column')
CD = ColumnDDL(tuple_or_tuple_list=single_tuple,
change_type='rename',
has_log=False,
case='UPPER')
query = CD.rename_column(schema_name='MY_SCHEMA',
table_name='MY_TABLE',
existing_column_name='MY_COLUMN',
new_column_name='MY_NEW_COLUMN')
expected_query = """ALTER TABLE "MY_SCHEMA"."MY_TABLE"
RENAME COLUMN "MY_COLUMN" TO "MY_NEW_COLUMN";"""
self.assertEqual(re.sub(r'[\n\t\s]*', '', expected_query),
re.sub(r'[\n\t\s]*', '', query))
def test_add_column_no_log(self):
# The tuple is here because it's required by the class. It's not used in this test
single_tuple = ('my_schema.my_table', 'column_one',
'VARCHAR(16777216)')
CD = ColumnDDL(tuple_or_tuple_list=single_tuple,
change_type='drop',
has_log=False,
case='UPPER')
query = CD.add_column(schema_name='MY_SCHEMA',
table_name='MY_TABLE',
column_to_add='COLUMN_ONE',
new_column_type='VARCHAR(16777216)')
expected_query = """ALTER TABLE "MY_SCHEMA"."MY_TABLE"
ADD COLUMN "COLUMN_ONE" VARCHAR(16777216);"""
self.assertEqual(re.sub(r'[\n\t\s]*', '', expected_query),
re.sub(r'[\n\t\s]*', '', query))
def test_create_ddl_statements_add_two_columns_no_log_lower(self):
tuple_list = [('schema_name.table_name', 'column_one',
'VARCHAR(16777216)'),
('schema_name.table_name', 'column_two', 'INTEGER')]
CD = ColumnDDL(tuple_or_tuple_list=tuple_list,
change_type='add',
has_log=False,
case='lower')
query_list = CD.create_ddl_statements()
expected_query_list = """alter table "schema_name"."table_name"
add column "column_one" varchar(16777216);
alter table "schema_name"."table_name"
add column "column_two" integer;"""
self.assertEqual(re.sub(r'\W', '', expected_query_list),
re.sub(r'\W', '', query_list))
def test_drop_column_no_log(self):
# The tuple is here because it's required by the class. It's not used in this test
single_tuple = ('my_schema.my_table', 'column_one',
'VARCHAR(16777216)')
CD = ColumnDDL(tuple_or_tuple_list=single_tuple,
change_type='drop',
has_log=False,
case='UPPER')
query = CD.drop_column(schema_name='MY_SCHEMA',
table_name='MY_TABLE',
column_to_drop='MY_COLUMN')
expected_query = """BEGIN TRANSACTION;
ALTER TABLE "MY_SCHEMA"."MY_TABLE"
DROP COLUMN "MY_COLUMN" CASCADE;
COMMIT;"""
self.assertEqual(re.sub(r'[\n\t\s]*', '', expected_query),
re.sub(r'[\n\t\s]*', '', query))
def test_create_ddl_statements_rename_two_columns_no_log_lower(self):
tuple_list = [
('schema_name.table_name', 'column_one', 'new_column_one'),
('schema_name.table_name', 'column_two', 'new_column_two')
]
CD = ColumnDDL(tuple_or_tuple_list=tuple_list,
change_type='rename',
has_log=False,
case='lower')
query_list = CD.create_ddl_statements()
expected_query_list = """alter table "schema_name"."table_name"
rename column "column_one" to "new_column_one";
alter table "schema_name"."table_name"
rename column "column_two" to "new_column_two";"""
self.assertEqual(re.sub(r'\W', '', expected_query_list),
re.sub(r'\W', '', query_list))
def test_create_ddl_statements_change_data_type_two_column_no_log_lower_int_and_varchar(
self):
tuple_list = [('my_schema.my_table', 'column_one', 'INTEGER'),
('my_schema.my_table', 'column_two', 'VARCHAR(16777216)')
]
CD = ColumnDDL(tuple_or_tuple_list=tuple_list,
change_type='data_type',
has_log=False,
case='UPPER')
query_list = CD.create_ddl_statements()
expected_query = """BEGIN TRANSACTION;
ALTER TABLE "MY_SCHEMA"."MY_TABLE"
RENAME COLUMN "COLUMN_ONE" TO "COLUMN_ONE_TMP";
ALTER TABLE "MY_SCHEMA"."MY_TABLE"
ADD COLUMN "COLUMN_ONE" INTEGER;
UPDATE "MY_SCHEMA"."MY_TABLE"
SET "COLUMN_ONE" = "COLUMN_ONE_TMP"::INTEGER
WHERE "COLUMN_ONE" IS NULL;
ALTER TABLE "MY_SCHEMA"."MY_TABLE"
DROP COLUMN "COLUMN_ONE_TMP" CASCADE;
COMMIT;
BEGIN TRANSACTION;
ALTER TABLE "MY_SCHEMA"."MY_TABLE"
RENAME COLUMN "COLUMN_TWO" TO "COLUMN_TWO_TMP";
ALTER TABLE "MY_SCHEMA"."MY_TABLE"
ADD COLUMN "COLUMN_TWO" VARCHAR(16777216);
UPDATE "MY_SCHEMA"."MY_TABLE"
SET "COLUMN_TWO" = "COLUMN_TWO_TMP"::VARCHAR(16777216)
WHERE "COLUMN_TWO" IS NULL;
ALTER TABLE "MY_SCHEMA"."MY_TABLE"
DROP COLUMN "COLUMN_TWO_TMP" CASCADE;
COMMIT;"""
self.assertEqual(re.sub(r'\W', '', expected_query),
re.sub(r'\W', '', query_list))
def test_create_ddl_statements_drop_two_columns_no_log_lower(self):
tuple_list = [('schema_name.table_name', 'column_one'),
('schema_name.table_name', 'column_two')]
CD = ColumnDDL(tuple_or_tuple_list=tuple_list,
change_type='drop',
has_log=False,
case='lower')
query_list = CD.create_ddl_statements()
expected_query_list = """begin transaction;
alter table "schema_name"."table_name"
drop column "column_one" cascade;
commit;
begin transaction;
alter table "schema_name"."table_name"
drop column "column_two" cascade;
commit;"""
self.assertEqual(re.sub(r'\W', '', expected_query_list),
re.sub(r'\W', '', query_list))
def test_create_ddl_statements_change_data_type_one_column_no_log_upper_int(
self):
single_tuple = ('my_schema.my_table', 'column_one', 'INTEGER')
CD = ColumnDDL(tuple_or_tuple_list=single_tuple,
change_type='data_type',
has_log=False,
case='UPPER')
query_list = CD.create_ddl_statements()
expected_query = """BEGIN TRANSACTION;
ALTER TABLE "MY_SCHEMA"."MY_TABLE"
RENAME COLUMN "COLUMN_ONE" TO "COLUMN_ONE_TMP";
ALTER TABLE "MY_SCHEMA"."MY_TABLE"
ADD COLUMN "COLUMN_ONE" INTEGER;
UPDATE "MY_SCHEMA"."MY_TABLE"
SET "COLUMN_ONE" = "COLUMN_ONE_TMP"::INTEGER
WHERE "COLUMN_ONE" IS NULL;
ALTER TABLE "MY_SCHEMA"."MY_TABLE"
DROP COLUMN "COLUMN_ONE_TMP" CASCADE;
COMMIT;"""
self.assertEqual(re.sub(r'\W', '', expected_query),
re.sub(r'\W', '', query_list))
def test_tuple_to_tuple_list_input_list(self):
single_tuple = ('schema_name.table_name', 'column_one', 'INT')
CD = ColumnDDL(tuple_or_tuple_list=single_tuple)
columns = CD.convert_tuple_to_list()
self.assertTrue(isinstance(columns, list))
def test_tuple_to_tuple_list_input_tuple(self):
tuple_list = [('schema_name.table_name', 'column_one'),
('schema_name.table_name', 'column_two')]
CD = ColumnDDL(tuple_or_tuple_list=tuple_list)
columns = CD.convert_tuple_to_list()
self.assertTrue(isinstance(columns, list))