def assert_sql_table_equal_with_create_defs(self, handler,
create_definitions,
expected_columns):
sql = self._build_create_table_sql(create_definitions)
actual_table = handler.create_sql_table_from_sql_stmts([sql])
expected_table = SQLTable(self.table_name, expected_columns)
assert_equal_sql_table(expected_table, actual_table)
def expected_sql_table_date_and_time(self):
col_id = SQLColumn('id',
data_types.MySQLInt(11),
primary_key_order=1,
is_nullable=False)
col_dttm = SQLColumn('dttm',
data_types.MySQLDateTime(),
default_value='1000-01-01 00:00:00',
is_nullable=False)
col_tstmp = SQLColumn('tstmp',
data_types.MySQLTimestamp(),
default_value='1970-01-01 00:00:01',
is_nullable=False)
col_tm = SQLColumn('tm',
data_types.MySQLTime(),
default_value='11:12:00',
is_nullable=False)
col_yr = SQLColumn('yr',
data_types.MySQLYear(),
default_value='2000',
is_nullable=False)
col_dt = SQLColumn('dt',
data_types.MySQLDate(),
default_value='1000-01-01',
is_nullable=False)
return SQLTable('date_and_time',
[col_id, col_dttm, col_tstmp, col_tm, col_yr, col_dt])
def test_convert_with_table_namespace_and_aliases(self, converter):
doc = 'I am doc'
aliases = ['foo']
metadata = {
MetaDataKey.NAMESPACE: self.namespace,
MetaDataKey.ALIASES: aliases
}
col_name = 'col'
sql_table = SQLTable(
self.table_name,
[SQLColumn(col_name, mysql_data_types.MySQLInt(11))],
doc=doc,
**metadata)
expected_schema = {
'type':
'record',
'name':
self.table_name,
'namespace':
self.namespace,
'fields': [{
'name': col_name,
'type': ['null', 'int'],
'default': None
}],
'doc':
doc,
'aliases':
aliases
}
actual_schema = converter.convert(sql_table)
assert expected_schema == actual_schema
def test_create_sql_table_from_sql_stmts_with_multi_sqls(self, handler):
sql_table = handler.create_sql_table_from_sql_stmts(
[self.alter_table_sql, self.create_table_sql])
expected_column = SQLColumn('id',
data_types.MySQLInt(11),
is_nullable=False)
expected_table = SQLTable(self.table_name, [expected_column])
assert expected_table == sql_table
def test_create_simple_push_plan_with_simple_table(self, migration):
simple_table = SQLTable(self.table_name, [self.same_col])
actual = migration.create_simple_push_plan(simple_table)
expected = [
'BEGIN;', 'CREATE TABLE {} (same_col varchar(64));'.format(
simple_table.full_name), '', 'COMMIT;'
]
assert expected == actual
def new_table(self):
columns = [self.same_col, self.renamed_col, self.primary_key_column]
table = SQLTable(self.table_name,
columns,
schema_name=self.schema_name)
table.metadata[MetaDataKey.PERMISSION] = [
self.permission_one, self.permission_two
]
return table
def test_create_table_sql_with_real_number_col_types(self, migration):
columns = [self.double_precision_column, self.real_column]
table = SQLTable(self.table_name, columns)
actual = migration.create_table_sql(table)
expected = ('CREATE TABLE {0} ({1} double precision,{2} real);'.format(
self.table_name, self.double_precision_column.name,
self.real_column.name))
assert expected == actual
def _create_redshift_table(self, record_schema):
cols = [self._create_column(field) for field in record_schema.fields]
table_metadata = self._get_table_metadata(record_schema)
return SQLTable(
record_schema.name,
columns=cols,
doc=record_schema.doc,
# TODO(chohan|DATAPIPE-1133): Define this property in
# AvroMetaDataKeys in data_pipeline_avro_util and update
# this line accordingly.
schema_name=record_schema.get_prop('schema_name'),
**table_metadata)
def _convert_and_assert_with_one_column(self, converter, sql_column,
expected_field):
sql_table = SQLTable(self.table_name, [sql_column])
expected_schema = {
'type': 'record',
'name': self.table_name,
'namespace': self.empty_namespace,
'fields': [expected_field],
}
if sql_column.primary_key_order:
expected_schema.update(
{AvroMetaDataKeys.PRIMARY_KEY: [sql_column.name]})
actual_schema = converter.convert(sql_table)
assert expected_schema == actual_schema
def test_create_table_sql_with_composite_primary_keys(self, migration):
columns = [self.second_primary_key_column, self.primary_key_column]
table = SQLTable(self.table_name, columns)
actual = migration.create_table_sql(table)
expected = (
'CREATE TABLE {0} ({1} integer,{2} integer,PRIMARY KEY ({3}));'.
format(
self.table_name, self.second_primary_key_column.name,
self.primary_key_column.name, ', '.join([
self.primary_key_column.name,
self.second_primary_key_column.name
])))
assert expected == actual
def _convert_and_assert_with_one_column(
self,
converter,
avro_field,
expected_column
):
record_schema = self.compose_record_schema(avro_field)
expected_table = SQLTable(
self.avro_schema_name,
columns=[expected_column],
doc=record_schema.get('doc'),
schema_name=self.redshift_schema_name,
**self.get_table_metadata()
)
actual_table = converter.convert(record_schema)
assert expected_table == actual_table
def expected_sql_table_foo(self):
col_id = SQLColumn('id',
data_types.MySQLInt(11),
primary_key_order=1,
is_nullable=False)
col_name = SQLColumn('name', data_types.MySQLVarChar(255))
col_amount = SQLColumn('amount',
data_types.MySQLDecimal(10, 2, unsigned=True),
default_value=0.0)
col_age = SQLColumn('age', data_types.MySQLInt(None))
col_bar = SQLColumn(
'bar',
data_types.MySQLEnum(values=['a1', 'a2', 'a3'], char_set="latin1"))
col_bar_one = SQLColumn(
'bar_one',
data_types.MySQLSet(values=['a1', 'a2', 'a3'], char_set="latin1"))
return SQLTable(
'foo',
[col_id, col_name, col_amount, col_age, col_bar, col_bar_one])
def test_convert_with_no_table_metadata(self, converter):
record_schema = {
'type': 'record',
'name': self.avro_schema_name,
'namespace': None,
'fields': [{'name': self.col_name, 'type': 'int'}],
'doc': 'sample doc',
}
expected_column = SQLColumn(
self.col_name,
redshift_types.RedshiftInteger(),
is_nullable=False
)
expected_table = SQLTable(
self.avro_schema_name,
columns=[expected_column],
doc=record_schema.get('doc')
)
actual_table = converter.convert(record_schema)
assert expected_table == actual_table
def test_convert_with_table_metadata(self, converter):
pkey_col1 = SQLColumn('pkey_col_one',
mysql_data_types.MySQLInt(11),
primary_key_order=1)
pkey_col2 = SQLColumn('pkey_col_two',
mysql_data_types.MySQLInt(11),
primary_key_order=2)
col = SQLColumn('col', mysql_data_types.MySQLInt(11))
sql_table = SQLTable(self.table_name, [pkey_col2, pkey_col1, col])
expected_schema = {
'type':
'record',
'name':
self.table_name,
'namespace':
'',
'fields': [
{
'name': pkey_col2.name,
'type': ['null', 'int'],
'default': None,
AvroMetaDataKeys.PRIMARY_KEY: 2
},
{
'name': pkey_col1.name,
'type': ['null', 'int'],
'default': None,
AvroMetaDataKeys.PRIMARY_KEY: 1
},
{
'name': col.name,
'type': ['null', 'int'],
'default': None
},
],
AvroMetaDataKeys.PRIMARY_KEY: [pkey_col1.name, pkey_col2.name]
}
actual_schema = converter.convert(sql_table)
assert expected_schema == actual_schema
def test_convert_with_composite_primary_keys(self, converter):
record_schema = {
'type': 'record',
'name': self.avro_schema_name,
'namespace': None,
'fields': [
{
'name': self.col_name,
'type': 'int',
AvroMetaDataKeys.PRIMARY_KEY: 2
},
{
'name': 'bar',
'type': 'int',
AvroMetaDataKeys.PRIMARY_KEY: 1
}
],
'doc': 'sample doc',
}
expected_column_col = SQLColumn(
self.col_name,
redshift_types.RedshiftInteger(),
is_nullable=False,
primary_key_order=2
)
expected_column_bar = SQLColumn(
'bar',
redshift_types.RedshiftInteger(),
is_nullable=False,
primary_key_order=1
)
expected_table = SQLTable(
self.avro_schema_name,
columns=[expected_column_col, expected_column_bar],
doc=record_schema.get('doc')
)
actual_table = converter.convert(record_schema)
assert expected_table == actual_table
def sql_table(self):
return SQLTable(
table_name='foo',
columns=[SQLColumn('id', MySQLInt(11), is_nullable=False)])
def another_old_table(self):
columns = [self.same_col, self.old_col, self.random_col]
return SQLTable(self.another_table_name,
columns,
schema_name=self.schema_name)
def test_create_temp_table(self, handler):
sql = 'create temporary table `foo` (bar int(11));'
actual = handler.create_sql_table_from_sql_stmts([sql])
expected = SQLTable('foo', [SQLColumn('bar', data_types.MySQLInt(11))])
assert actual == expected
def test_convert_with_unsupported_type(self, converter):
with pytest.raises(UnsupportedTypeException):
column = SQLColumn('col', MySQLUnsupportedType())
sql_table = SQLTable(self.table_name, [column])
converter.convert(sql_table)
