def test_create_table_with_errors(self):
"""Test creating a table with invalid values."""
with self.assertRaises(AssertionError):
Table(table_name=None)
with self.assertRaises(AssertionError):
Table(table_name="Table1", schema_name=None)
def test_create_table_with_primary_keys(self):
"""Tests creating a table with primary keys."""
table = Table(table_name="Table1", primary_key="column_1")
self.assertEqual(table.primary_key, ["column_1"])
table = Table(table_name="Table2", primary_key=["column_1", "column_2"])
self.assertEqual(table.primary_key, ["column_1", "column_2"])
def test_create_table_with_schema(self):
"""Tests creating a table with a schema."""
table = Table(table_name="Table")
self.assertEqual(table.schema_name, "falcon_default_schema")
table = Table(table_name="Table", schema_name="some_other_schema")
self.assertEqual(table.schema_name, "some_other_schema")
def test_add_and_drop_column(self):
"""Tests adding / dropping a column from a table."""
dc = DDLCompare()
db1 = Database(database_name="database1")
db2 = Database(database_name="database2")
t1 = Table(table_name="table1")
db1.add_table(t1)
t2 = Table(table_name="table1")
t2.add_column(column=Column(column_name="column1", column_type="INT"))
db2.add_table(t2)
diff1, diff2 = dc.compare_databases(db1=db1, db2=db2)
self.assertTrue(type(diff1[0] is ColumnAddedDifference))
self.assertEqual(diff1[0].table_name, "table1")
self.assertEqual(diff1[0].column.column_name, "column1")
self.assertEqual(diff1[0].column.column_type, "INT")
self.assertTrue(type(diff2[0] is ColumnDroppedDifference))
self.assertEqual(diff2[0].table_name, "table1")
self.assertEqual(diff2[0].column.column_name, "column1")
self.assertEqual(diff2[0].column.column_type, "INT")
def test_valid_database(self):
"""Tests a good database to make sure there are no false positives."""
good_db = Database(database_name="good_db")
table1 = Table(
table_name="table1",
primary_key=["col1", "col2"],
shard_key=ShardKey(shard_keys="col1", number_shards=128),
)
table1.add_column(Column(column_name="col1", column_type="INT"))
table1.add_column(Column(column_name="col2", column_type="INT"))
good_db.add_table(table1)
table2 = Table(
table_name="table2",
primary_key=["col3", "col4"],
shard_key=ShardKey(shard_keys="col3", number_shards=128),
)
table2.add_foreign_key(
from_keys=["col3", "col4"],
to_table="table1",
to_keys=["col1", "col2"],
)
table2.add_column(Column(column_name="col3", column_type="INT"))
table2.add_column(Column(column_name="col4", column_type="INT"))
good_db.add_table(table2)
dv = DatabaseValidator(good_db)
results = dv.validate()
self.assertTrue(results.is_valid)
self.assertEqual([], results.issues)
def get_test_table():
"""
Returns a table for testing columns and such.
:returns: A table with columns for testing.
:rtype: Table
"""
table = Table(table_name="Table", schema_name="test")
table.add_column(Column(column_name="column_1", column_type="INT"))
table.add_column(Column(column_name="column_2", column_type="DOUBLE"))
table.add_column(Column(column_name="column_3", column_type="DATETIME"))
table.add_column(Column(column_name="column_4", column_type="BOOL"))
return table
def test_add_foreign_key(self):
"""Tests adding foreign keys."""
table = Table(table_name="table1", primary_key="pk1")
table.add_foreign_key(
ForeignKey(
name="fk1",
from_table="table1",
from_keys="col1",
to_table="table2",
to_keys="col2",
)
)
table.add_foreign_key(
name="fk2", from_keys="col3", to_table="table3", to_keys="col4"
)
fk = table.foreign_keys["fk1"]
self.assertEquals("table1", fk.from_table)
fk = table.get_foreign_key("fk2")
self.assertEqual("table1", fk.from_table)
self.assertEqual(["col3"], fk.from_keys)
self.assertEqual("table3", fk.to_table)
self.assertEqual(["col4"], fk.to_keys)
fk = table.get_foreign_key("fkx")
self.assertIsNone(fk)
def test_add_relationship(self):
"""Tests adding relationships."""
table = Table(table_name="table1", primary_key="pk1")
table.add_relationship(
GenericRelationship(
from_table="table1",
to_table="table2",
conditions="table1.col1 = table2.col2",
name="rel1",
)
)
table.add_relationship(
to_table="table3",
conditions="table1.col1 = table3.col3",
name="rel2",
)
rel = table.get_relationship("rel1")
self.assertEqual("rel1", rel.name)
rel = table.relationships["rel2"]
self.assertEqual("rel2", rel.name)
self.assertEqual("table1", rel.from_table)
self.assertEqual("table3", rel.to_table)
self.assertEqual("table1.col1 = table3.col3", rel.conditions)
rel = table.get_relationship("foo")
self.assertIsNone(rel)
def parse_create_table(self, buff):
"""
Parses a create table statement.
:param buff: The buffer read in.
:type buff: str
:return:
"""
buff = buff.replace("[", "\"").replace("]",
"\"") # for SQL Server quotes
table_name = self.get_table_name(buff)
columns = self.get_columns(buff)
table = Table(table_name=table_name, schema_name=self.schema_name)
table.add_columns(columns)
self.database.add_table(table)
def test_add_and_drop_fk(self):
"""Tests adding / dropping a column from a table."""
dc = DDLCompare()
db1 = Database(database_name="database1")
db2 = Database(database_name="database2")
t1 = Table(table_name="table1", primary_key="column1")
db1.add_table(t1)
t2 = Table(table_name="table1")
db2.add_table(t2)
diff1, diff2 = dc.compare_databases(db1=db1, db2=db2)
self.assertTrue(type(diff1[0] is PrimaryKeyDroppedDifference))
self.assertEqual(diff1[0].table_name, "table1")
self.assertTrue(type(diff2[0] is PrimaryKeyAddedDifference))
self.assertEqual(diff2[0].table_name, "table1")
def _read_tables_from_workbook(self):
"""
Reads the databases and tables from Excel. These are used to populate from the remaining sheets.
"""
# "Tables": ["Database", "Schema", "Table", "Updated", "Update Type", "# Rows", "# Columns",
# "Primary Key", "Shard Key", "# Shards", "RLS Column"],
table_sheet = self.workbook.sheet_by_name("Tables")
indices = self.indices["Tables"]
for row_count in range(1, table_sheet.nrows):
row = table_sheet.row_values(rowx=row_count, start_colx=0)
database_name = row[indices["Database"]]
database = self.databases.get(database_name, None)
if database is None:
database = Database(database_name=database_name)
self.databases[database_name] = database
pk = row[indices["Primary Key"]].strip()
if pk == "":
pk = None
else:
pk = [x.strip() for x in pk.split(",")]
sk_name = row[indices["Shard Key"]].strip()
sk_nbr_shards = row[indices["# Shards"]]
if (sk_name == "" and sk_nbr_shards != "") or (
sk_name != "" and sk_nbr_shards == ""
):
eprint(
"ERROR: %s need to provide both a shard key name and number of shards."
% row[indices["Table"]]
)
if sk_name == "":
sk = None
else:
sk = [x.strip() for x in sk_name.split(",")]
shard_key = None
if sk_name != "" and sk_nbr_shards != "":
shard_key = ShardKey(
shard_keys=sk, number_shards=sk_nbr_shards
)
table = Table(
table_name=row[indices["Table"]],
schema_name=row[indices["Schema"]],
primary_key=pk,
shard_key=None,
)
database.add_table(table)
def get_simple_db():
"""
Returns a simple database with one table for testing.
:return: Database with one table.
:rtype: Database
"""
database = Database(database_name="database1")
table = Table(table_name="table1")
table.add_column(Column(column_name="col1", column_type="INT"))
table.add_column(Column(column_name="Col2", column_type="DOUBLE"))
table.add_column(Column(column_name="COL3", column_type="FLOAT"))
database.add_table(table)
return database
def test_add_and_drop_rel(self):
"""Tests adding / dropping a column from a table."""
dc = DDLCompare()
db1 = Database(database_name="database1")
db2 = Database(database_name="database2")
t1 = Table(table_name="table1")
t1.add_relationship(relationship=GenericRelationship(from_table="table_1", to_table="table_2",
conditions="table1.col1 = table2.col2"))
db1.add_table(t1)
t2 = Table(table_name="table1")
db2.add_table(t2)
diff1, diff2 = dc.compare_databases(db1=db1, db2=db2)
self.assertTrue(type(diff1[0] is GenericRelationshipDroppedDifference))
self.assertEqual(diff1[0].table_name, "table1")
self.assertTrue(type(diff2[0] is GenericRelationshipAddedDifference))
self.assertEqual(diff2[0].table_name, "table1")
def test_set_primary_key(self):
"""Tests setting primary keys."""
table = Table(table_name="table1", primary_key="pk1")
self.assertEqual(["pk1"], table.primary_key)
table.set_primary_key("pk2")
self.assertEqual(["pk2"], table.primary_key)
table.set_primary_key(["pk1", "pk2"])
self.assertEqual(["pk1", "pk2"], table.primary_key)
def test_new_and_drop_table(self):
"""Tests adding / dropping a table. It's added in one and dropped in the other."""
dc = DDLCompare()
db1 = Database(database_name="database1")
db2 = Database(database_name="database2")
db1.add_table(Table(table_name="table_from_1"))
diff1, diff2 = dc.compare_databases(db1=db1, db2=db2)
self.assertTrue(type(diff1[0]) is TableDroppedDifference)
self.assertEqual(diff1[0].table_name, "table_from_1")
self.assertTrue(type(diff2[0]) is TableCreatedDifference)
self.assertEqual(diff2[0].table_name, "table_from_1")
def create_test_database():
"""Creates a database for testing."""
database = Database("database1")
database.add_table(Table(table_name="table1", schema_name="schema1"))
database.add_table(Table(table_name="table2"))
return database
def get_complex_db():
"""
Returns a more complex database with two tables and keys for testing.
:return: Database with two tables and keys.
:rtype: Database
"""
database = Database(database_name="database2")
table1 = Table(table_name="table1",
primary_key="col1",
shard_key=ShardKey("col1", 128))
table1.add_column(Column(column_name="col1", column_type="INT"))
table1.add_column(Column(column_name="Col2", column_type="DOUBLE"))
table1.add_column(Column(column_name="COL3", column_type="FLOAT"))
database.add_table(table1)
table2 = Table(table_name="table2",
primary_key=["col4", "Col5"],
shard_key=ShardKey(["col4", "Col5"], 96))
table2.add_column(Column(column_name="col4", column_type="VARCHAR(0)"))
table2.add_column(Column(column_name="Col5", column_type="DATE"))
table2.add_column(Column(column_name="COL6", column_type="BOOL"))
database.add_table(table2)
table2.add_foreign_key(from_keys="Col5",
to_table="table1",
to_keys="COL3")
table1.add_relationship(
to_table="table2",
conditions='("table1"."col1" == "table2."COL6")')
return database
def test_create_excel(self):
"""Test writing to Excel. Only test is existance. Checks shoudl be made for validity."""
database = Database(database_name="xdb")
table = Table(table_name="table1",
schema_name="s1",
primary_key="column_1",
shard_key=ShardKey("column_1", 128))
table.add_column(Column(column_name="column_1", column_type="INT"))
table.add_column(Column(column_name="column_2", column_type="DOUBLE"))
table.add_column(Column(column_name="column_3", column_type="FLOAT"))
database.add_table(table)
table = Table(table_name="table2",
schema_name="s1",
primary_key="column_1")
table.add_column(Column(column_name="column_1", column_type="INT"))
table.add_column(Column(column_name="column_2",
column_type="DATETIME"))
table.add_column(Column(column_name="column_3", column_type="BOOL"))
table.add_column(Column(column_name="column_4", column_type="DOUBLE"))
table.add_foreign_key(from_keys="column_1",
to_table="table_1",
to_keys="column_1")
table.add_relationship(to_table="table1",
conditions="table2.column_4 = table1.column_2")
database.add_table(table)
writer = XLSWriter()
writer.write_database(database, "test_excel")
def test_with_csvfile(self):
"""test the tsload writer when the csv exists"""
# todo Create the csv file.
database = Database(database_name="xdb")
table = Table(table_name="table1",
schema_name="s1",
primary_key="column_1",
shard_key=ShardKey("column_1", 128))
table.add_column(Column(column_name="column_1", column_type="INT"))
table.add_column(Column(column_name="column_2", column_type="DOUBLE"))
table.add_column(Column(column_name="column_3", column_type="FLOAT"))
table.add_column(Column(column_name="column_3", column_type="DATE"))
database.add_table(table)
table = Table(table_name="table2",
schema_name="s1",
primary_key="column_1",
shard_key=ShardKey("column_1", 128))
table.add_column(Column(column_name="column_1", column_type="INT"))
table.add_column(Column(column_name="column_2", column_type="FLOAT"))
table.add_column(Column(column_name="column_3", column_type="DOUBLE"))
database.add_table(table)
table = Table(table_name="table3",
schema_name="s1",
primary_key="column_1",
shard_key=ShardKey("column_1", 128))
table.add_column(Column(column_name="column_1", column_type="INT"))
table.add_column(Column(column_name="column_2", column_type="FLOAT"))
table.add_column(Column(column_name="column_3", column_type="VARCHAR"))
database.add_table(table)
tsload_writer = TsloadWriter()
tsload_writer.write_tsloadcommand(database, "tsloadwriter_test")
with open("tsloadwriter_test", "r") as infile:
line = infile.readline()
self.assertTrue(line.startswith("tsload "))
self.assertTrue(line.index('--target_database "xdb"') > 0)
self.assertTrue(line.index('--target_schema "s1"'))
def test_create_table_no_errors(self):
"""Tests creating a table with the basics and no errors."""
table = Table(table_name="Table1")
self.assertEqual(table.table_name, "Table1")