def setUpClass(cls) -> None:
"""Create the expected schema in the test database"""
drop_all_user_databases()
database_name = "constraints_database"
schema_name = "dbo"
query_list = list(create_database(database_name, "Latin1_General_CS_AS"))
query_list.extend(enable_database_tracking(database_name))
table_name = "no_constraints"
cls.EXPECTED_METADATA = {
'{}_{}_{}'.format(database_name, schema_name, table_name): {
'is-view': False,
'schema-name': schema_name,
'row-count': 0,
'values': [(0, ), (1, ), (2, )],
'table-key-properties': set(),
'selected': None,
'database-name': database_name,
'stream_name': table_name,
'fields': [
{'replication_key_column': {'sql-datatype': 'int', 'selected-by-default': True,
'inclusion': 'available'}}],
'schema': {
'type': 'object',
'properties': {
'replication_key_column': {
'type': ['integer', 'null'],
'minimum': -2147483648,
'maximum': 2147483647,
'inclusion': 'available',
'selected': True}},
'selected': True}},
}
column_name = ["replication_key_column"]
column_type = ["int"]
primary_key = set()
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(create_table(database_name, schema_name, table_name, column_def,
primary_key=primary_key))
query_list.extend(insert(database_name, schema_name, table_name,
cls.EXPECTED_METADATA['{}_{}_{}'.format(
database_name, schema_name, table_name)]["values"]))
table_name = "multiple_column_pk"
primary_key = ["first_name", "last_name"]
cls.EXPECTED_METADATA['{}_{}_{}'.format(database_name, schema_name, table_name)] = {
'is-view': False,
'schema-name': schema_name,
'row-count': 0,
'values': [
("Tim", "Berners-Lee", 64),
("Sergey", "Brin", 45),
("Larry", "Page", 46)],
'table-key-properties': primary_key,
'selected': None,
'database-name': database_name,
'stream_name': table_name,
'fields': [
{'first_name': {'sql-datatype': 'varchar', 'selected-by-default': True, 'inclusion': 'automatic'}},
{'last_name': {'sql-datatype': 'varchar', 'selected-by-default': True, 'inclusion': 'automatic'}},
{'replication_key_column': {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'available'}}],
'schema': {
'type': 'object',
'selected': True,
'properties': {
'replication_key_column': {
'maximum': 2147483647,
'type': ['integer', 'null'],
'inclusion': 'available',
'selected': True,
'minimum': -2147483648},
'first_name': {
'type': ['string'],
'maxLength': 256,
'inclusion': 'automatic',
'selected': True}, # 'minLength': 0},
'last_name': {
'type': ['string'],
'maxLength': 256,
'inclusion': 'automatic',
'selected': True} # 'minLength': 0},(1, 4, 2, 5)
}}
}
column_name = ["first_name", "last_name", "replication_key_column"]
column_type = ["varchar(256)", "varchar(256)", "int"]
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(create_table(database_name, schema_name, table_name, column_def,
primary_key=primary_key, tracking=True) )
query_list.extend(insert(database_name, schema_name, table_name,
cls.EXPECTED_METADATA['{}_{}_{}'.format(
database_name, schema_name, table_name)]["values"]))
# Already covered in other tests
# table_name = "single_column_pk"
# primary_key = ["pk"]
# cls.EXPECTED_METADATA['{}_{}_{}'.format(database_name, schema_name, table_name)] = {
# 'is-view': False,
# 'schema-name': schema_name,
# 'row-count': 0,
# 'values': [
# (0, 3),
# (1, 4),
# (2, 5)],
# 'table-key-properties': primary_key,
# 'selected': None,
# 'database-name': database_name,
# 'stream_name': table_name,
# 'fields': [
# {'pk': {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'automatic'}},
# {'replication_key_column': {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'available'}}],
# 'schema': {
# 'type': 'object',
# 'selected': True,
# 'properties': {
# 'pk': {
# 'maximum': 2147483647,
# 'type': ['integer'],
# 'inclusion': 'automatic',
# 'selected': True,
# 'minimum': -2147483648},
# 'replication_key_column': {
# 'maximum': 2147483647,
# 'type': ['integer', 'null'],
# 'inclusion': 'available',
# 'selected': True,
# 'minimum': -2147483648},
# "_sdc_deleted_at": {'format': 'date-time', 'type': 'string'}}}
# }
# column_name = ["pk", "data"]
# column_type = ["int", "int"]
# column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
# query_list.extend(create_table(database_name, schema_name, table_name, column_def,
# primary_key=primary_key, tracking=True))
# query_list.extend(insert(database_name, schema_name, table_name,
# cls.EXPECTED_METADATA['{}_{}_{}'.format(
# database_name, schema_name, table_name)]["values"]))
table_name = "pk_with_unique_not_null"
primary_key = ["pk"]
cls.EXPECTED_METADATA['{}_{}_{}'.format(database_name, schema_name, table_name)] = {
'is-view': False,
'schema-name': schema_name,
'row-count': 0,
'values': [
(0, 3),
(1, 4),
(2, 5)],
'table-key-properties': primary_key,
'selected': None,
'database-name': database_name,
'stream_name': table_name,
'fields': [
{'pk': {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'automatic'}},
{'replication_key_column': {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'available'}}],
'schema': {
'type': 'object',
'selected': True,
'properties': {
'pk': {
'maximum': 2147483647,
'type': ['integer'],
'inclusion': 'automatic',
'selected': True,
'minimum': -2147483648},
'replication_key_column': {
'maximum': 2147483647,
'type': ['integer', 'null'],
'inclusion': 'available',
'selected': True,
'minimum': -2147483648}}}
}
column_name = ["pk", "replication_key_column"]
column_type = ["int", "int NOT NULL UNIQUE"]
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(create_table(database_name, schema_name, table_name, column_def,
primary_key=primary_key, tracking=True) )
query_list.extend(insert(database_name, schema_name, table_name,
cls.EXPECTED_METADATA['{}_{}_{}'.format(
database_name, schema_name, table_name)]["values"]))
table_name = "pk_with_fk"
primary_key = ["pk"]
cls.EXPECTED_METADATA['{}_{}_{}'.format(database_name, schema_name, table_name)] = {
'is-view': False,
'schema-name': schema_name,
'row-count': 0,
'values': [
(3, 1),
(1, 0),
(2, 0),
(0, 1),
(4, None)],
'table-key-properties': primary_key,
'selected': None,
'database-name': database_name,
'stream_name': table_name,
'fields': [
{'pk': {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'automatic'}},
{'replication_key_column': {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'available'}}],
'schema': {
'type': 'object',
'selected': True,
'properties': {
'pk': {
'maximum': 2147483647,
'type': ['integer'],
'inclusion': 'automatic',
'selected': True,
'minimum': -2147483648},
'replication_key_column': {
'maximum': 2147483647,
'type': ['integer', 'null'],
'inclusion': 'available',
'selected': True,
'minimum': -2147483648}}}
}
column_name = ["pk", "replication_key_column"]
column_type = ["int", "int"]
foreign_key = "replication_key_column"
reference = "{}.pk_with_unique_not_null(pk)".format(schema_name)
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(create_table(
database_name, schema_name, table_name, column_def,
primary_key=primary_key, foreign_key=foreign_key, reference=reference, tracking=True))
query_list.extend(insert(database_name, schema_name, table_name,
cls.EXPECTED_METADATA['{}_{}_{}'.format(
database_name, schema_name, table_name)]["values"]))
table_name = "view_with_join"
primary_key = []
cls.EXPECTED_METADATA['{}_{}_{}'.format(database_name, schema_name, table_name)] = {
'is-view': True,
'schema-name': schema_name,
'row-count': 0,
'values': [
(1, 4, 0),
(0, 3, 1),
(0, 3, 2),
(1, 4, 3),
(None, None, 4)],
'table-key-properties': primary_key,
'selected': None,
'database-name': database_name,
'stream_name': table_name,
'fields': [
{'column1': {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'available'}},
{'data': {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'available'}},
{'replication_key_column': {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'available'}}],
'schema': {
'type': 'object',
'selected': True,
'properties': {
'column1': {
'maximum': 2147483647,
'type': ['integer', 'null'],
'inclusion': 'available',
'selected': True,
'minimum': -2147483648},
'data': {
'maximum': 2147483647,
'type': ['integer', 'null'],
'inclusion': 'available',
'selected': True,
'minimum': -2147483648},
'replication_key_column': {
'maximum': 2147483647,
'type': ['integer', 'null'],
'inclusion': 'available',
'selected': True,
'minimum': -2147483648}}}
}
select = ("SELECT p.pk as column1, p.replication_key_column as data, f.pk as replication_key_column "
"FROM pk_with_unique_not_null p "
"RIGHT JOIN pk_with_fk f on p.pk = f.replication_key_column")
query_list.extend(create_view(schema_name, table_name, select))
# This doesn't look to add value
# table_name = "table_with_index"
# primary_key = []
# cls.EXPECTED_METADATA['{}_{}_{}'.format(database_name, schema_name, table_name)] = {
# 'is-view': False,
# 'schema-name': schema_name,
# 'row-count': 0,
# 'values': [
# (0, 3),
# (1, 4)],
# 'table-key-properties': primary_key,
# 'selected': None,
# 'database-name': database_name,
# 'stream_name': table_name,
# 'fields': [
# {'not_pk': {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'available'}},
# {'replication_key_column': {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'available'}}],
# 'schema': {
# 'type': 'object',
# 'selected': True,
# 'properties': {
# 'not_pk': {
# 'maximum': 2147483647,
# 'type': ['integer', 'null'],
# 'inclusion': 'available',
# 'selected': True,
# 'minimum': -2147483648},
# 'replication_key_column': {
# 'maximum': 2147483647,
# 'type': ['integer', 'null'],
# 'inclusion': 'available',
# 'selected': True,
# 'minimum': -2147483648}}}
# }
# column_name = ["not_pk", "replication_key_column"]
# column_type = ["int", "int NOT NULL INDEX myindex"]
# column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
# query_list.extend(create_table(database_name, schema_name, table_name, column_def,
# primary_key=primary_key))
# query_list.extend(insert(database_name, schema_name, table_name,
# cls.EXPECTED_METADATA['{}_{}_{}'.format(
# database_name, schema_name, table_name)]["values"]))
table_name = "default_column"
primary_key = ["pk"]
cls.EXPECTED_METADATA['{}_{}_{}'.format(database_name, schema_name, table_name)] = {
'is-view': False,
'schema-name': schema_name,
'row-count': 0,
'values': [
(0, ),
(1, )],
'table-key-properties': primary_key,
'selected': None,
'database-name': database_name,
'stream_name': table_name,
'fields': [
{'pk': {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'automatic'}},
{'replication_key_column': {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'available'}}],
'schema': {
'type': 'object',
'selected': True,
'properties': {
'pk': {
'maximum': 2147483647,
'type': ['integer'],
'inclusion': 'automatic',
'selected': True,
'minimum': -2147483648},
'replication_key_column': {
'maximum': 2147483647,
'type': ['integer', 'null'],
'inclusion': 'available',
'selected': True,
'minimum': -2147483648}}}
}
column_name = ["pk", "replication_key_column"]
column_type = ["int", "int DEFAULT -1"]
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(create_table(
database_name, schema_name, table_name, column_def, primary_key=primary_key, tracking=True) )
query_list.extend(insert(database_name, schema_name, table_name,
cls.EXPECTED_METADATA['{}_{}_{}'.format(
database_name, schema_name, table_name)]["values"],
column_names=["pk"]))
cls.EXPECTED_METADATA['{}_{}_{}'.format(database_name, schema_name, table_name)]["values"] = [
(0, -1),
(1, -1)]
table_name = "check_constraint"
primary_key = ["pk"]
cls.EXPECTED_METADATA['{}_{}_{}'.format(database_name, schema_name, table_name)] = {
'is-view': False,
'schema-name': schema_name,
'row-count': 0,
'values': [
(0, 37),
(1, 34)],
'table-key-properties': primary_key,
'selected': None,
'database-name': database_name,
'stream_name': table_name,
'fields': [
{'pk': {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'automatic'}},
{'replication_key_column': {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'available'}}],
'schema': {
'type': 'object',
'selected': True,
'properties': {
'pk': {
'maximum': 2147483647,
'type': ['integer'],
'inclusion': 'automatic',
'selected': True,
'minimum': -2147483648},
'replication_key_column': {
'maximum': 2147483647,
'type': ['integer', 'null'],
'inclusion': 'available',
'selected': True,
'minimum': -2147483648}}}
}
column_name = ["pk", "replication_key_column"]
column_type = ["int", "int CHECK (replication_key_column <= 120)"]
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(create_table(
database_name, schema_name, table_name, column_def, primary_key=primary_key, tracking=True) )
query_list.extend(insert(database_name, schema_name, table_name,
cls.EXPECTED_METADATA['{}_{}_{}'.format(
database_name, schema_name, table_name)]["values"]))
table_name = "even_identity"
primary_key = ["pk"]
cls.EXPECTED_METADATA['{}_{}_{}'.format(database_name, schema_name, table_name)] = {
'is-view': False,
'schema-name': schema_name,
'row-count': 0,
'values': [
(1, ),
(2, )],
'table-key-properties': primary_key,
'selected': None,
'database-name': database_name,
'stream_name': table_name,
'fields': [
{'pk': {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'automatic'}},
{'replication_key_column': {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'available'}}],
'schema': {
'type': 'object',
'selected': True,
'properties': {
'pk': {
'maximum': 2147483647,
'type': ['integer'],
'inclusion': 'automatic',
'selected': True,
'minimum': -2147483648},
'replication_key_column': {
'maximum': 2147483647,
'type': ['integer', 'null'],
'inclusion': 'available',
'selected': True,
'minimum': -2147483648}}}
}
column_name = ["pk", "replication_key_column"]
column_type = ["int", "int IDENTITY(2,2)"]
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(create_table(
database_name, schema_name, table_name, column_def, primary_key=primary_key, tracking=True) )
query_list.extend(insert(database_name, schema_name, table_name,
cls.EXPECTED_METADATA['{}_{}_{}'.format(
database_name, schema_name, table_name)]["values"],
column_names=["pk"]))
cls.EXPECTED_METADATA['{}_{}_{}'.format(database_name, schema_name, table_name)]["values"] = [
(1, 2),
(2, 4)]
mssql_cursor_context_manager(*query_list)
cls.expected_metadata = cls.discovery_expected_metadata
def setUpClass(cls) -> None:
"""Create the expected schema in the test database"""
database_name = "data_types_database"
schema_name = "dbo"
drop_all_user_databases()
values = [
(0, date(1, 1, 1), datetime(1753, 1, 1, 0, 0, tzinfo=timezone.utc),
datetime(1, 1, 1, 0, 0, tzinfo=timezone.utc),
datetime(1, 1, 1, 13, 46,
tzinfo=timezone(timedelta(hours=-14))).isoformat(),
datetime(1900, 1, 1, 0, 0,
tzinfo=timezone.utc), time(0, 0, tzinfo=timezone.utc)),
(1, date(9999, 12, 31),
datetime(9999, 12, 31, 23, 59, 59, 997000, tzinfo=timezone.utc),
datetime(9999, 12, 31, 23, 59, 59, 999000, tzinfo=timezone.utc),
datetime(9999,
12,
31,
10,
14,
tzinfo=timezone(timedelta(hours=14))).isoformat(),
datetime(2079, 6, 6, 23, 59, tzinfo=timezone.utc),
time(23, 59, 59, tzinfo=timezone.utc)),
(2, None, None, None, None, None, None),
(3, date(4533, 6, 9),
datetime(3099, 2, 6, 4, 27, 37, 983000, tzinfo=timezone.utc),
datetime(9085, 4, 30, 21, 52, 57, 492920, tzinfo=timezone.utc),
datetime(5749,
4,
3,
1,
47,
47,
110809,
tzinfo=timezone(timedelta(hours=10,
minutes=5))).isoformat(),
datetime(2031, 4, 30, 19, 32, tzinfo=timezone.utc),
time(21, 9, 56, 0, tzinfo=timezone.utc)),
(4, date(3476, 10, 14),
datetime(7491, 4, 5, 8, 46, 0, 360000, tzinfo=timezone.utc),
datetime(8366, 7, 13, 17, 15, 10, 102386, tzinfo=timezone.utc),
datetime(2642,
6,
19,
21,
10,
28,
546280,
tzinfo=timezone(timedelta(hours=6,
minutes=15))).isoformat(),
datetime(2024, 6, 22, 0, 36, tzinfo=timezone.utc),
time(2, 14, 4, 0, tzinfo=timezone.utc))
]
schema = {
'selected': True,
'properties': {
'its_time': {
'selected': True,
'inclusion': 'available',
'type': ['string', 'null']
},
'pk': {
'maximum': 2147483647,
'selected': True,
'inclusion': 'automatic',
'type': ['integer'],
'minimum': -2147483648
},
'just_a_date': {
'selected': True,
'inclusion': 'available',
'type': ['string', 'null'],
'format': 'date-time'
},
'date_and_time': {
'selected': True,
'inclusion': 'available',
'type': ['string', 'null'],
'format': 'date-time'
},
"bigger_range_and_precision_datetime": {
'selected': True,
'inclusion': 'available',
'type': ['string', 'null'],
'format': 'date-time'
},
"datetime_with_timezones": {
'selected': True,
'inclusion': 'available',
'type': ['string', 'null'],
'format': 'date-time'
},
"datetime_no_seconds": {
'selected': True,
'inclusion': 'available',
'type': ['string', 'null'],
'format': 'date-time'
},
"_sdc_deleted_at": {
'format': 'date-time',
'type': ['string', 'null']
}
},
'type': 'object'
}
feilds = [{
'pk': {
'sql-datatype': 'int',
'selected-by-default': True,
'inclusion': 'automatic'
}
}, {
'just_a_date': {
'sql-datatype': 'date',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'date_and_time': {
'sql-datatype': 'datetime',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'bigger_range_and_precision_datetime': {
'sql-datatype': 'datetime2',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'datetime_with_timezones': {
'sql-datatype': 'datetimeoffest',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'datetime_no_seconds': {
'sql-datatype': 'smalldatetime',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'its_time': {
'sql-datatype': 'time',
'selected-by-default': True,
'inclusion': 'available'
}
}]
query_list = list(
create_database(database_name, "Latin1_General_CS_AS"))
query_list.extend(enable_database_tracking(database_name))
table_name = "dates_and_times"
primary_key = {"pk"}
column_name = [
"pk", "just_a_date", "date_and_time",
"bigger_range_and_precision_datetime", "datetime_with_timezones",
"datetime_no_seconds", "its_time"
]
column_type = [
"int", "date", "datetime", "datetime2", "datetimeoffset",
"smalldatetime", "time"
]
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(
create_table(database_name,
schema_name,
table_name,
column_def,
primary_key=primary_key,
tracking=True))
query_list.extend(
insert(database_name, schema_name, table_name, values))
mssql_cursor_context_manager(*query_list)
values = [
(0, date(1, 1, 1), datetime(1753, 1, 1, 0, 0, tzinfo=timezone.utc),
datetime(1, 1, 1, 0, 0, tzinfo=timezone.utc),
datetime(1, 1, 1, 13, 46, tzinfo=timezone(
timedelta(hours=-14))).astimezone(timezone.utc),
datetime(1900, 1, 1, 0, 0,
tzinfo=timezone.utc), time(0, 0, tzinfo=timezone.utc)),
(1, date(9999, 12, 31),
datetime(9999, 12, 31, 23, 59, 59, 997000, tzinfo=timezone.utc),
datetime(9999, 12, 31, 23, 59, 59, 999000, tzinfo=timezone.utc),
datetime(9999,
12,
31,
10,
14,
tzinfo=timezone(timedelta(hours=14))).astimezone(
timezone.utc),
datetime(2079, 6, 6, 23, 59, tzinfo=timezone.utc),
time(23, 59, 59, tzinfo=timezone.utc)),
(2, None, None, None, None, None, None),
(3, date(4533, 6, 9),
datetime(3099, 2, 6, 4, 27, 37, 983000, tzinfo=timezone.utc),
datetime(9085, 4, 30, 21, 52, 57, 492920, tzinfo=timezone.utc),
datetime(5749,
4,
3,
1,
47,
47,
110809,
tzinfo=timezone(timedelta(
hours=10, minutes=5))).astimezone(timezone.utc),
datetime(2031, 4, 30, 19, 32, tzinfo=timezone.utc),
time(21, 9, 56, 0, tzinfo=timezone.utc)),
(4, date(3476, 10, 14),
datetime(7491, 4, 5, 8, 46, 0, 360000, tzinfo=timezone.utc),
datetime(8366, 7, 13, 17, 15, 10, 102386, tzinfo=timezone.utc),
datetime(2642,
6,
19,
21,
10,
28,
546280,
tzinfo=timezone(timedelta(
hours=6, minutes=15))).astimezone(timezone.utc),
datetime(2024, 6, 22, 0, 36, tzinfo=timezone.utc),
time(2, 14, 4, 0, tzinfo=timezone.utc))
]
cls.EXPECTED_METADATA = {
'{}_{}_{}'.format(database_name, schema_name, table_name): {
'is-view': False,
'schema-name': schema_name,
'row-count': 0,
'values': values,
'table-key-properties': primary_key,
'selected': None,
'database-name': database_name,
'stream_name': table_name,
'fields': feilds,
'schema': schema
}
}
cls.expected_metadata = cls.discovery_expected_metadata
def setUpClass(cls) -> None:
"""Create the expected schema in the test database"""
drop_all_user_databases()
database_name = "data_types_database"
schema_name = "dbo"
query_list = list(create_database(database_name, "Latin1_General_CS_AS"))
query_list.extend(enable_database_tracking(database_name))
text_values = [
(0, None, None, None),
(1, "abc", "def", "ghi".encode('utf-8'))
]
text_schema = {
'selected': True,
'properties': {
'pk': {
'inclusion': 'automatic',
'maximum': 2147483647,
'minimum': -2147483648,
'type': ['integer'],
'selected': True},
'rowversion_synonym_timestamp': {'inclusion': 'available', 'selected': True, 'type': ['string', 'null']},
'varchar_text': {},
'nvarchar_text': {},
'varbinary_data': {},
"_sdc_deleted_at": {'format': 'date-time', 'type': ['string', 'null']}},
'type': 'object'}
other_values = [
(0, None, None, None, "827376B0-AEF4-11E9-8002-0800276BC1DF", None, None, None),
(1, None, None, None, "ACC9A986-AEF4-11E9-8002-0800276BC1DF", None, None, None),
(2, None, None, None, "B792681C-AEF4-11E9-8002-0800276BC1DF", None, None, None)
]
other_schema = {
'selected': True,
'properties': {
'markup': {},
'variant': {},
'geospacial': {},
'SpecialPurposeColumns': {},
'tree': {},
'guid': {
'inclusion': 'available',
'selected': True,
'pattern': '[A-F0-9]{8}-([A-F0-9]{4}-){3}[A-F0-9]{12}',
'type': ['string', 'null']},
'geospacial_map': {},
'pk': {
'inclusion': 'automatic',
'maximum': 2147483647,
'minimum': -2147483648,
'type': ['integer'],
'selected': True},
'version': {'inclusion': 'available', 'selected': True, 'type': ['string', 'null']},
"_sdc_deleted_at": {'format': 'date-time', 'type': ['string', 'null']}},
'type': 'object'}
comp_values = [
(0, datetime(1970, 7, 8, 3), datetime.now()),
(1, datetime(1970, 1, 1, 0), datetime.now())
]
comp_schema = {
'selected': True,
'properties': {
'started_at': {
'selected': False,
'type': ['string', 'null'],
'inclusion': 'available',
'format': 'date-time'},
'durations_days': {
'inclusion': 'available',
'maximum': 2147483647,
'minimum': -2147483648,
'type': ['integer', 'null'],
'selected': True},
'ended_at': {
'format': 'date-time',
'inclusion': 'available',
'type': ['string', 'null'],
'selected': False},
'pk': {
'inclusion': 'automatic',
'maximum': 2147483647,
'minimum': -2147483648,
'type': ['integer'],
'selected': True},
"_sdc_deleted_at": {'format': 'date-time', 'type': ['string', 'null']}},
'type': 'object'}
cls.EXPECTED_METADATA = {
'data_types_database_dbo_text_and_image_deprecated_soon': {
'is-view': False,
'schema-name': schema_name,
'row-count': 0,
'values': text_values,
'table-key-properties': {'pk'},
'selected': None,
'database-name': database_name,
'stream_name': 'text_and_image_deprecated_soon',
'fields': [
{"pk": {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'automatic'}},
{"nvarchar_text": {'sql-datatype': 'ntext', 'selected-by-default': False,
'inclusion': 'unavailable'}},
{"varchar_text": {'sql-datatype': 'text', 'selected-by-default': False,
'inclusion': 'unavailable'}},
{"varbinary_data": {'sql-datatype': 'image', 'selected-by-default': False,
'inclusion': 'unavailable'}},
{"rowversion_synonym_timestamp": {'sql-datatype': 'timestamp', 'selected-by-default': True,
'inclusion': 'available'}}],
'schema': text_schema},
'data_types_database_dbo_weirdos': {
'is-view': False,
'schema-name': schema_name,
'row-count': 0,
'values': other_values,
'table-key-properties': {'pk'},
'selected': None,
'database-name': database_name,
'stream_name': 'weirdos',
'fields': [
{"pk": {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'automatic'}},
{"geospacial": {'sql-datatype': 'geometry', 'selected-by-default': False,
'inclusion': 'unavailable'}},
{"geospacial_map": {'sql-datatype': 'geography', 'selected-by-default': False,
'inclusion': 'unavailable'}},
{"markup": {'sql-datatype': 'xml', 'selected-by-default': False, 'inclusion': 'unavailable'}},
{"guid": {'sql-datatype': 'uniqueidentifier', 'selected-by-default': True,
'inclusion': 'available'}},
{"tree": {'sql-datatype': 'hierarchyid', 'selected-by-default': False, 'inclusion': 'unavailable'}},
{"variant": {'sql-datatype': 'sql_variant', 'selected-by-default': False,
'inclusion': 'unavailable'}},
{"SpecialPurposeColumns": {'sql-datatype': 'xml', 'selected-by-default': False,
'inclusion': 'unavailable'}},
{"version": {'sql-datatype': 'timestamp', 'selected-by-default': True, 'inclusion': 'available'}}],
'schema': other_schema},
'data_types_database_dbo_computed_columns': {
'is-view': False,
'schema-name': schema_name,
'row-count': 0,
'values': comp_values,
'table-key-properties': {'pk'},
'selected': None,
'database-name': database_name,
'stream_name': 'computed_columns',
'fields': [
{"pk": {'sql-datatype': 'int', 'selected-by-default': True, 'inclusion': 'automatic'}},
{"started_at": {'sql-datatype': 'datetimeoffset', 'selected-by-default': True,
'inclusion': 'available'}},
{"ended_at": {'sql-datatype': 'datetimeoffset', 'selected-by-default': True,
'inclusion': 'available'}},
{"durations_days": {'sql-datatype': 'int', 'selected-by-default': True,
'inclusion': 'unavailable'}}],
'schema': comp_schema},
}
# test timestamp and usnupported
table_name = "text_and_image_deprecated_soon"
column_name = ["pk", "nvarchar_text", "varchar_text", "varbinary_data",
"rowversion_synonym_timestamp"]
column_type = ["int", "ntext", "text", "image", "timestamp"]
primary_key = {"pk"}
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(create_table(database_name, schema_name, table_name, column_def,
primary_key=primary_key, tracking=True))
query_list.extend(insert(database_name, schema_name, table_name, text_values, column_name[:-1]))
# test uniqueidentifier and rowversion
table_name = "weirdos"
column_name = [
"pk", "geospacial", "geospacial_map", "markup", "guid", "tree",
"variant", "SpecialPurposeColumns", "version"
]
column_type = [
"int", "geometry", "geography", "xml", "uniqueidentifier", "hierarchyid",
"sql_variant", "xml COLUMN_SET FOR ALL_SPARSE_COLUMNS", "rowversion"
]
primary_key = {"pk"}
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(create_table(database_name, schema_name, table_name, column_def,
primary_key=primary_key, tracking=True))
# not sure why I have to do this but getting error - Parameter information is missing from a user-defined type.
for value in other_values:
query_list.extend(insert(database_name, schema_name, table_name, [value], column_name[:-1]))
table_name = "computed_columns"
column_name = ["pk", "started_at", "ended_at", "durations_days"]
column_type = ["int", "datetimeoffset", "datetimeoffset", "AS DATEDIFF(day, started_at, ended_at)"]
primary_key = {"pk"}
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(create_table(database_name, schema_name, table_name, column_def,
primary_key=primary_key, tracking=True))
query_list.extend(insert(database_name, schema_name, table_name, comp_values, column_name[:-1]))
mssql_cursor_context_manager(*query_list)
# update values with rowversions
rows = mssql_cursor_context_manager(*["select version from data_types_database.dbo.weirdos order by pk"])
rows = ["0x{}".format(value.hex().upper()) for value, in rows]
cls.EXPECTED_METADATA['data_types_database_dbo_weirdos']['values'] = \
[other_values[row] + (version,) for row, version in enumerate(rows)]
rows = mssql_cursor_context_manager(*[
"select rowversion_synonym_timestamp from data_types_database.dbo.text_and_image_deprecated_soon order by pk"])
rows = ["0x{}".format(value.hex().upper()) for value, in rows]
cls.EXPECTED_METADATA['data_types_database_dbo_text_and_image_deprecated_soon']['values'] = \
[text_values[row] + (version,) for row, version in enumerate(rows)]
rows = mssql_cursor_context_manager(
*["select durations_days from data_types_database.dbo.computed_columns order by pk"])
cls.EXPECTED_METADATA['data_types_database_dbo_computed_columns']['values'] = \
[comp_values[row] + tuple(version) for row, version in enumerate(rows)]
cls.expected_metadata = cls.discovery_expected_metadata
def setUpClass(cls) -> None:
"""Create the expected schema in the test database"""
database_name = "data_types_database"
schema_name = "dbo"
cls.EXPECTED_METADATA = {
'data_types_database_dbo_integers': {
'is-view':
False,
'schema-name':
schema_name,
'row-count':
0,
'values': [(0, -9223372036854775808, -2147483648, -32768),
(1, 0, 0, 0),
(2, 9223372036854775807, 2147483647, 32767),
(3, None, None, None),
(4, 5603121835631323156, 9665315, 11742),
(5, -4898597031243117659, 140946744, -16490),
(6, -5168593529138936444, -1746890910, 2150),
(7, 1331162887494168851, 1048867088, 12136),
(8, -4495110645908459596, -1971955745, 18257),
(9, -1575653240237191360, -533282078, 22022),
(10, 6203877631305833079, 271324086, -18782),
(11, 7293147954924079156, 1003163272, 3593),
(12, -1302715001442736465, -1626372079, 3788),
(13, -9062593720232233398, 1646478731, 17621)],
'table-key-properties': {'pk'},
'selected':
None,
'database-name':
database_name,
'stream_name':
'integers',
'fields': [{
'pk': {
'sql-datatype': 'int',
'selected-by-default': True,
'inclusion': 'automatic'
}
}, {
'MyBigIntColumn': {
'sql-datatype': 'bigint',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'MyIntColumn': {
'sql-datatype': 'int',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'MySmallIntColumn': {
'sql-datatype': 'smallint',
'selected-by-default': True,
'inclusion': 'available'
}
}],
'schema': {
'type': 'object',
'properties': {
'MySmallIntColumn': {
'type': ['integer', 'null'],
'minimum': -32768,
'maximum': 32767,
'inclusion': 'available',
'selected': True
},
'pk': {
'type': ['integer'],
'minimum': -2147483648,
'maximum': 2147483647,
'inclusion': 'automatic',
'selected': True
},
'MyBigIntColumn': {
'type': ['integer', 'null'],
'minimum': -9223372036854775808,
'maximum': 9223372036854775807,
'inclusion': 'available',
'selected': True
},
'MyIntColumn': {
'type': ['integer', 'null'],
'minimum': -2147483648,
'maximum': 2147483647,
'inclusion': 'available',
'selected': True
},
"_sdc_deleted_at": {
'format': 'date-time',
'type': ['string', 'null']
}
},
'selected': True
}
},
'data_types_database_dbo_tiny_integers_and_bools': {
'is-view':
False,
'schema-name':
schema_name,
'row-count':
0,
'values': [(0, 0, False), (1, 255, True), (2, None, None),
(3, 230, False), (4, 6, True), (5, 236, True),
(6, 27, True), (7, 132, True), (8, 251, False),
(9, 187, True), (10, 157, True), (11, 51, True),
(12, 144, True)],
'table-key-properties': {'pk'},
'selected':
None,
'database-name':
database_name,
'stream_name':
'tiny_integers_and_bools',
'fields': [{
'pk': {
'sql-datatype': 'int',
'selected-by-default': True,
'inclusion': 'automatic'
}
}, {
'MyTinyIntColumn': {
'sql-datatype': 'tinyint',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'my_boolean': {
'sql-datatype': 'bit',
'selected-by-default': True,
'inclusion': 'available'
}
}],
'schema': {
'type': 'object',
'properties': {
'MyTinyIntColumn': {
'type': ['integer', 'null'],
'minimum': 0,
'maximum': 255,
'inclusion': 'available',
'selected': True
},
'pk': {
'type': ['integer'],
'minimum': -2147483648,
'maximum': 2147483647,
'inclusion': 'automatic',
'selected': True
},
'my_boolean': {
'type': ['boolean', 'null'],
'inclusion': 'available',
'selected': True
},
"_sdc_deleted_at": {
'format': 'date-time',
'type': ['string', 'null']
}
},
'selected': True
}
}
}
drop_all_user_databases()
query_list = list(
create_database(database_name, "Latin1_General_CS_AS"))
query_list.extend(enable_database_tracking(database_name))
table_name = "integers"
column_name = [
"pk", "MyBigIntColumn", "MyIntColumn", "MySmallIntColumn"
]
column_type = ["int", "bigint", "int", "smallint"]
primary_key = {"pk"}
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(
create_table(database_name,
schema_name,
table_name,
column_def,
primary_key=primary_key,
tracking=True))
query_list.extend(
insert(
database_name, schema_name, table_name,
cls.EXPECTED_METADATA["data_types_database_dbo_integers"]
["values"]))
table_name = "tiny_integers_and_bools"
column_name = ["pk", "MyTinyIntColumn", "my_boolean"]
column_type = ["int", "tinyint", "bit"]
primary_key = {"pk"}
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(
create_table(database_name,
schema_name,
table_name,
column_def,
primary_key=primary_key,
tracking=True))
query_list.extend(
insert(
database_name, schema_name, table_name, cls.EXPECTED_METADATA[
"data_types_database_dbo_tiny_integers_and_bools"]
["values"]))
mssql_cursor_context_manager(*query_list)
cls.expected_metadata = cls.discovery_expected_metadata
def setUpClass(cls) -> None:
"""Create the expected schema in the test database"""
drop_all_user_databases()
database_name = "data_types_database"
schema_name = "dbo"
# use all valid unicode characters
chars = list(range(0, 55296))
chars.extend(range(57344, sys.maxunicode))
chars.reverse() # pop starting with ascii characters
char_values = [(pk, "".join([chr(chars.pop()) for _ in range(2)]))
for pk in range(16)]
char_schema = {
'type': 'object',
'selected': True,
'properties': {
CHAR_NAME: {
'type': ['string', 'null'],
'maxLength': 2,
'inclusion': 'available',
'selected': True
},
# 'minLength': 2},
'pk': {
'maximum': 2147483647,
'type': ['integer'],
'inclusion': 'automatic',
'selected': True,
'minimum': -2147483648
},
"_sdc_deleted_at": {
'format': 'date-time',
'type': ['string', 'null']
}
}
}
varchar_values = [(pk, chr(chars.pop()), "".join([
chr(chars.pop()) for _ in range(15)
]), "".join([chr(chars.pop()) for _ in range(randint(1, 16))]))
for pk in range(3)]
varchar_schema = {
'type': 'object',
'selected': True,
'properties': {
'pk': {
'maximum': 2147483647,
'type': ['integer'],
'inclusion': 'automatic',
'selected': True,
'minimum': -2147483648
},
'varchar_8000': {
'type': ['string', 'null'],
'maxLength': 8000,
'inclusion': 'available',
'selected': True
}, # 'minLength': 0},
VARCHAR_NAME: {
'type': ['string', 'null'],
'maxLength': 5,
'inclusion': 'available',
'selected': True
},
# 'minLength': 0},
'varchar_max': {
'type': ['string', 'null'],
'maxLength': 2147483647,
'inclusion': 'available',
'selected': True
},
"_sdc_deleted_at": {
'format': 'date-time',
'type': ['string', 'null']
}
}
}
# 'minLength': 0}}}
nchar_values = [(pk, "".join([chr(chars.pop()) for _ in range(4)]))
for pk in range(3)]
# expect that values are right padded with spaces in the db.
nchar_values = [
(x, "{}{}".format(y,
" " * ((16 - len(y.encode('utf-16-le'))) // 2)))
for x, y in nchar_values
]
nchar_schema = {
'type': 'object',
'selected': True,
'properties': {
NCHAR_NAME: {
'type': ['string', 'null'],
'maxLength': 8,
'inclusion': 'available',
'selected': True
},
# 'minLength': 8}, # length is based on bytes, not characters
'pk': {
'maximum': 2147483647,
'type': ['integer'],
'inclusion': 'automatic',
'selected': True,
'minimum': -2147483648
},
"_sdc_deleted_at": {
'format': 'date-time',
'type': ['string', 'null']
}
}
}
chars.reverse()
nvarchar_values = [(pk, chr(chars.pop()), "".join([
chr(chars.pop()) for _ in range(8)
]), "".join([chr(chars.pop()) for _ in range(randint(1, 8))]))
for pk in range(4)]
nvarchar_schema = {
'type': 'object',
'selected': True,
'properties': {
'nvarchar_max': {
'type': ['string', 'null'],
'maxLength': 2147483647,
'inclusion': 'available',
'selected': True
},
# 'minLength': 0},
'pk': {
'maximum': 2147483647,
'type': ['integer'],
'inclusion': 'automatic',
'selected': True,
'minimum': -2147483648
},
'nvarchar_4000': {
'type': ['string', 'null'],
'maxLength': 4000,
'inclusion': 'available',
'selected': True
},
# 'minLength': 0},
NVARCHAR_NAME: {
'type': ['string', 'null'],
'maxLength': 5,
'inclusion': 'available',
'selected': True
},
"_sdc_deleted_at": {
'format': 'date-time',
'type': ['string', 'null']
}
}
}
# 'minLength': 0}}}
cls.EXPECTED_METADATA = {
'data_types_database_dbo_{}'.format(CHAR_NAME): {
'is-view':
False,
'schema-name':
schema_name,
'row-count':
0,
'values':
char_values,
'table-key-properties': {'pk'},
'selected':
None,
'database-name':
database_name,
'stream_name':
CHAR_NAME,
'fields': [{
'pk': {
'sql-datatype': 'int',
'selected-by-default': True,
'inclusion': 'automatic'
}
}, {
CHAR_NAME: {
'sql-datatype': 'char',
'selected-by-default': True,
'inclusion': 'available'
}
}],
'schema':
char_schema
},
'data_types_database_dbo_{}'.format(VARCHAR_NAME): {
'is-view':
False,
'schema-name':
schema_name,
'row-count':
0,
'values':
varchar_values,
'table-key-properties': {'pk'},
'selected':
None,
'database-name':
database_name,
'stream_name':
VARCHAR_NAME,
'fields': [{
'pk': {
'sql-datatype': 'int',
'selected-by-default': True,
'inclusion': 'automatic'
}
}, {
VARCHAR_NAME: {
'sql-datatype': 'varchar',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'varchar_8000': {
'sql-datatype': 'varchar',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'varchar_max': {
'sql-datatype': 'varchar',
'selected-by-default': True,
'inclusion': 'available'
}
}],
'schema':
varchar_schema
},
'data_types_database_dbo_{}'.format(NCHAR_NAME): {
'is-view':
False,
'schema-name':
schema_name,
'row-count':
0,
'values':
nchar_values,
'table-key-properties': {'pk'},
'selected':
None,
'database-name':
database_name,
'stream_name':
NCHAR_NAME,
'fields': [{
'pk': {
'sql-datatype': 'int',
'selected-by-default': True,
'inclusion': 'automatic'
}
}, {
NCHAR_NAME: {
'sql-datatype': 'nchar',
'selected-by-default': True,
'inclusion': 'available'
}
}],
'schema':
nchar_schema
},
'data_types_database_dbo_{}'.format(NVARCHAR_NAME): {
'is-view':
False,
'schema-name':
schema_name,
'row-count':
0,
'values':
nvarchar_values,
'table-key-properties': {'pk'},
'selected':
None,
'database-name':
database_name,
'stream_name':
NVARCHAR_NAME,
'fields': [{
'pk': {
'sql-datatype': 'int',
'selected-by-default': True,
'inclusion': 'automatic'
}
}, {
NVARCHAR_NAME: {
'sql-datatype': 'nvarchar',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'nvarchar_4000': {
'sql-datatype': 'nvarchar',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'nvarchar_max': {
'sql-datatype': 'nvarchar',
'selected-by-default': True,
'inclusion': 'available'
}
}],
'schema':
nvarchar_schema
},
}
query_list = list(
create_database(database_name, "Latin1_General_CS_AS"))
query_list.extend(enable_database_tracking(database_name))
table_name = '"{}"'.format(CHAR_NAME)
column_name = ["pk", table_name] # , "char_8000"]
column_type = ["int", "char(2)"] # , "char(8000)"]
primary_key = {"pk"}
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(
create_table(database_name,
schema_name,
table_name,
column_def,
primary_key=primary_key,
tracking=True))
query_list.extend(
insert(database_name, schema_name, table_name, char_values))
table_name = "[{}]".format(VARCHAR_NAME)
column_name = ["pk", table_name, "varchar_8000", "varchar_max"]
column_type = ["int", "varchar(5)", "varchar(8000)", "varchar(max)"]
primary_key = {"pk"}
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(
create_table(database_name,
schema_name,
table_name,
column_def,
primary_key=primary_key,
tracking=True))
query_list.extend(
insert(database_name, schema_name, table_name, varchar_values))
table_name = "[{}]".format(NCHAR_NAME)
column_name = ["pk", "[{}]".format(NCHAR_NAME)]
column_type = ["int", "nchar(8)"] # , "nchar(4000)"]
primary_key = {"pk"}
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(
create_table(database_name,
schema_name,
table_name,
column_def,
primary_key=primary_key,
tracking=True))
# strip padding off query data
nchar_query_values = [(x, y.rstrip() if isinstance(y, str) else y)
for x, y in nchar_values]
query_list.extend(
insert(database_name, schema_name, table_name, nchar_query_values))
table_name = NVARCHAR_NAME
column_name = ["pk", NVARCHAR_NAME, "nvarchar_4000", "nvarchar_max"]
column_type = ["int", "nvarchar(5)", "nvarchar(4000)", "nvarchar(max)"]
primary_key = {"pk"}
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(
create_table(database_name,
schema_name,
table_name,
column_def,
primary_key=primary_key,
tracking=True))
query_list.extend(
insert(database_name, schema_name, table_name, nvarchar_values))
query_list.extend(
['-- there are {} characters left to test'.format(len(chars))])
cls.expected_metadata = cls.discovery_expected_metadata
mssql_cursor_context_manager(*query_list)
def setUpClass(cls) -> None:
"""Create the expected schema in the test database"""
database_name = "data_types_database"
schema_name = "dbo"
drop_all_user_databases()
numeric_values = [
(0, Decimal('-99999.9999'), Decimal('-9999999.999999999999'),
Decimal('-999999.9999999999999999999999'),
Decimal('-99999999999999999999999999999999999.999')),
(1, 0, 0, 0, 0), (2, None, None, None, None),
(3, Decimal('99999.9993'), Decimal('9999999.999999999999'),
Decimal('999999.9999999999999999999999'),
Decimal('99999999999999999999999999999999999.993')),
(4, Decimal('96701.9382'), Decimal('-4371716.186100650268'),
Decimal('-367352.306093776232045517794'),
Decimal('-81147872128956247517327931319278572.985')),
(5, Decimal('-73621.9366'), Decimal('2564047.277589545531'),
Decimal('336177.4754683699464233786667'),
Decimal('46946462608534127558389411015159825.758')),
(6, Decimal('-3070.7339'), Decimal('6260062.158440967433'),
Decimal('-987006.0035971607740533206418'),
Decimal('95478671259010046866787754969592794.61'))
]
numeric_schema = {
'type': 'object',
'properties': {
'numeric_9_4': {
'exclusiveMaximum': True,
'type': ['number', 'null'],
'selected': True,
'multipleOf': 0.0001,
'maximum': 1e5,
'inclusion': 'available',
'exclusiveMinimum': True,
'minimum': -1e5
},
'numeric_19_12': {
'exclusiveMaximum': True,
'type': ['number', 'null'],
'selected': True,
'multipleOf': 1e-12,
'maximum': 1e7,
'inclusion': 'available',
'exclusiveMinimum': True,
'minimum': -1e7
},
'numeric_28_22': {
'exclusiveMaximum': True,
'type': ['number', 'null'],
'selected': True,
'multipleOf': 1e-22,
'maximum': 1e6,
'inclusion': 'available',
'exclusiveMinimum': True,
'minimum': -1e6
},
'replication_key_column': {
'exclusiveMaximum': True,
'type': ['number', 'null'],
'selected': True,
'multipleOf': .001,
'maximum': 1e35,
'inclusion': 'available',
'exclusiveMinimum': True,
'minimum': -1e35
},
'pk': {
'maximum': 2147483647,
'type': ['integer'],
'inclusion': 'automatic',
'minimum': -2147483648,
'selected': True
}
},
'selected': True
}
decimal_values = [
(0, Decimal('-99999.9999'), Decimal('-9999999999999.999999'),
Decimal('-9999999999999999999999.999999'),
Decimal('-9999999999999999999999999.9999999999999')),
(1, 0, 0, 0, 0), (2, None, None, None, None),
(3, Decimal('99999.9993'), Decimal('9999999999999.999999'),
Decimal('9999999999999999999999.999999'),
Decimal('9999999999999999999999999.9999999999993')),
(4, Decimal('-92473.8401'), Decimal('-4182159664734.645653'),
Decimal('6101329656084900380190.268036'),
Decimal('4778017533841887320066645.9761464001349')),
(5, Decimal('-57970.8157'), Decimal('7735958802279.086687'),
Decimal('4848737828398517845540.057905'),
Decimal('2176036096567853905237453.5152648989022')),
(6, Decimal('57573.9037'), Decimal('5948502499261.181557'),
Decimal('-6687721783088280707003.076638'),
Decimal('-6264019242578746090842245.3746225058202'))
]
decimal_schema = {
'type': 'object',
'properties': {
'decimal_9_4': {
'exclusiveMaximum': True,
'type': ['number', 'null'],
'selected': True,
'multipleOf': 0.0001,
'maximum': 1e5,
'inclusion': 'available',
'exclusiveMinimum': True,
'minimum': -1e5
},
'decimal_19_6': {
'exclusiveMaximum': True,
'type': ['number', 'null'],
'selected': True,
'multipleOf': 1e-6,
'maximum': 1e13,
'inclusion': 'available',
'exclusiveMinimum': True,
'minimum': -1e13
},
'decimal_28_6': {
'exclusiveMaximum': True,
'type': ['number', 'null'],
'selected': True,
'multipleOf': 1e-6,
'maximum': 1e22,
'inclusion': 'available',
'exclusiveMinimum': True,
'minimum': -1e22
},
'replication_key_column': {
'exclusiveMaximum': True,
'type': ['number', 'null'],
'selected': True,
'multipleOf': 1e-13,
'maximum': 1e25,
'inclusion': 'available',
'exclusiveMinimum': True,
'minimum': -1e25
},
'pk': {
'maximum': 2147483647,
'type': ['integer'],
'inclusion': 'automatic',
'minimum': -2147483648,
'selected': True
}
},
'selected': True
}
cls.EXPECTED_METADATA = {
'data_types_database_dbo_numeric_precisions': {
'is-view':
False,
'schema-name':
schema_name,
'row-count':
0,
'values':
numeric_values,
'table-key-properties': {'pk'},
'selected':
None,
'database-name':
database_name,
'stream_name':
'numeric_precisions',
'fields': [{
'pk': {
'sql-datatype': 'int',
'selected-by-default': True,
'inclusion': 'automatic'
}
}, {
'numeric_9_4': {
'sql-datatype': 'numeric(9,4)',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'numeric_19_12': {
'sql-datatype': 'numeric(19,12)',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'numeric_28_22': {
'sql-datatype': 'numeric(28,22)',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'replication_key_column': {
'sql-datatype': 'numeric(38,3)',
'selected-by-default': True,
'inclusion': 'available'
}
}],
'schema':
numeric_schema
},
'data_types_database_dbo_decimal_precisions': {
'is-view':
False,
'schema-name':
schema_name,
'row-count':
0,
'values':
decimal_values,
'table-key-properties': {'pk'},
'selected':
None,
'database-name':
database_name,
'stream_name':
'decimal_precisions',
'fields': [{
'pk': {
'sql-datatype': 'int',
'selected-by-default': True,
'inclusion': 'automatic'
}
}, {
'decimal_9_4': {
'sql-datatype': 'decimal(9,4)',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'decimal_19_6': {
'sql-datatype': 'decimal(19,6)',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'decimal_28_6': {
'sql-datatype': 'decimal(28,6)',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'replication_key_column': {
'sql-datatype': 'decimal(38,13)',
'selected-by-default': True,
'inclusion': 'available'
}
}],
'schema':
decimal_schema
}
}
query_list = list(
create_database(database_name, "Latin1_General_CS_AS"))
query_list.extend(enable_database_tracking(database_name))
# TODO - BUG https://stitchdata.atlassian.net/browse/SRCE-1075
table_name = "numeric_precisions"
precision_scale = NUMERIC_PRECISION_SCALE
column_type = [
"numeric({},{})".format(precision, scale)
for precision, scale in precision_scale
]
column_name = ["pk"] + [
x.replace("(", "_").replace(",", "_").replace(")", "")
for x in column_type[:-1]
] + ["replication_key_column"]
column_type = ["int"] + column_type
primary_key = {"pk"}
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(
create_table(database_name,
schema_name,
table_name,
column_def,
primary_key=primary_key,
tracking=True))
query_list.extend(
insert(
database_name, schema_name, table_name, cls.EXPECTED_METADATA[
"data_types_database_dbo_numeric_precisions"]["values"]))
table_name = "decimal_precisions"
precision_scale = DECIMAL_PRECISION_SCALE
column_type = [
"decimal({},{})".format(precision, scale)
for precision, scale in precision_scale
]
column_name = ["pk"] + [
x.replace("(", "_").replace(",", "_").replace(")", "")
for x in column_type[:-1]
] + ["replication_key_column"]
column_type = ["int"] + column_type
primary_key = {"pk"}
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(
create_table(database_name,
schema_name,
table_name,
column_def,
primary_key=primary_key,
tracking=True))
query_list.extend(
insert(
database_name, schema_name, table_name, cls.EXPECTED_METADATA[
"data_types_database_dbo_decimal_precisions"]["values"]))
mssql_cursor_context_manager(*query_list)
cls.expected_metadata = cls.discovery_expected_metadata
def setUpClass(cls) -> None:
"""Create the expected schema in the test database"""
database_name = "data_types_database"
schema_name = "dbo"
drop_all_user_databases()
values = [
(0, 1.1754944e-38, 2.2250738585072014e-308, 1.1754944e-38),
(1, 3.4028230e+38, 1.7976931348623157e+308, 3.4028235e+38),
(2, -1.1754944e-38, -2.2250738585072014e-308, -1.1754944e-38),
(3, -3.4028235e+38, -1.7976931348623157e+308, -3.4028235e+38),
(4, 0.0, 0.0, 0.0), (5, None, None, None),
(6, 7.830105e-33, 6.46504535047369e-271, 4.0229383e-27),
(7, 4.4540307e-21, 7.205251086772512e-202, 7.196247e-19),
(8, 647852.6, 2.1597057137884757e+40, 8.430207e+34),
(9, 3603.407, 8.811948588549982e+23, 9.1771755e+35),
(10, -8.451405e-24, -1.783306877438393e-178, -2.2775854e-31),
(11, -5.8271772e-27, -9.344274532947989e-227, -3.5728205e-18),
(12, -8.519153e+23, -2.3035944912603858e+241, -5.7120217e+35),
(13, -30306750.0, -5.222263032559684e+106, -1.9535917e+27)
]
schema = {
'selected': True,
'type': 'object',
'properties': {
'replication_key_column': {
'selected': True,
'type': ['number', 'null'],
'inclusion': 'available'
},
'float_53': {
'selected': True,
'type': ['number', 'null'],
'inclusion': 'available'
},
'real_24_bits': {
'selected': True,
'type': ['number', 'null'],
'inclusion': 'available'
},
'pk': {
'selected': True,
'type': ['integer'],
'maximum': 2147483647,
'minimum': -2147483648,
'inclusion': 'automatic'
}
}
}
cls.EXPECTED_METADATA = {
'data_types_database_dbo_float_precisions': {
'is-view':
False,
'schema-name':
schema_name,
'row-count':
0,
'values':
values,
'table-key-properties': {'pk'},
'selected':
None,
'database-name':
database_name,
'stream_name':
'float_precisions',
'fields': [{
'pk': {
'sql-datatype': 'int',
'selected-by-default': True,
'inclusion': 'automatic'
}
}, {
'replication_key_column': {
'sql-datatype': 'real',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'float_53': {
'sql-datatype': 'float',
'selected-by-default': True,
'inclusion': 'available'
}
}, {
'real_24_bits': {
'sql-datatype': 'real',
'selected-by-default': True,
'inclusion': 'available'
}
}],
'schema':
schema
}
}
query_list = list(
create_database(database_name, "Latin1_General_CS_AS"))
query_list.extend(enable_database_tracking(database_name))
table_name = "float_precisions"
column_name = [
"pk", "replication_key_column", "float_53", "real_24_bits"
]
column_type = ["int", "float(24)", "float(53)", "real"]
primary_key = {"pk"}
column_def = [" ".join(x) for x in list(zip(column_name, column_type))]
query_list.extend(
create_table(database_name,
schema_name,
table_name,
column_def,
primary_key=primary_key,
tracking=True))
query_list.extend(
insert(
database_name, schema_name, table_name, cls.EXPECTED_METADATA[
"data_types_database_dbo_float_precisions"]["values"]))
mssql_cursor_context_manager(*query_list)
cls.expected_metadata = cls.discovery_expected_metadata