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)