def test_change_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")
t1.add_column(column=Column(column_name="column1", column_type="INT"))
db1.add_table(t1)
t2 = Table(table_name="table1")
t2.add_column(column=Column(column_name="column1", column_type="FLOAT"))
db2.add_table(t2)
diff1, diff2 = dc.compare_databases(db1=db1, db2=db2)
self.assertTrue(type(diff1[0] is ColumnModifiedDifference))
self.assertEqual(diff1[0].table_name, "table1")
self.assertEqual(diff1[0].column.column_name, "column1")
self.assertEqual(diff1[0].column.column_type, "FLOAT")
self.assertTrue(type(diff2[0] is ColumnModifiedDifference))
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 test_create_column(self):
"""Tests creating a column with all parameters. Just create with a few types."""
col1 = Column(column_name="column_1", column_type="INT")
self.assertEqual(col1.column_name, "column_1")
self.assertEqual(col1.column_type, "INT")
col2 = Column(column_name="column_2", column_type="DOUBLE")
self.assertEqual(col2.column_name, "column_2")
self.assertEqual(col2.column_type, "DOUBLE")
col3 = Column(column_name="column_3", column_type="DATETIME")
self.assertEqual(col3.column_name, "column_3")
self.assertEqual(col3.column_type, "DATETIME")
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 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 _read_columns_from_workbook(self):
"""
Reads the columns for the tables from Excel.
"""
# "Columns": ["Database", "Schema", "Table", "Column", "Name", "Type"],
column_sheet = self.workbook.sheet_by_name("Columns")
indices = self.indices["Columns"]
for row_count in range(1, column_sheet.nrows):
row = column_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:
eprint(
"ERROR: Database %s from the Columns tab is not known."
% database_name
)
else:
table_name = row[indices["Table"]]
table = database.get_table(table_name)
if table is None:
eprint(
"ERROR: Table %s from the Columns tab is not known."
% table_name
)
else:
table.add_column(
Column(
column_name=row[indices["Name"]],
column_type=row[indices["Type"]],
)
)
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_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 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_column_with_bad_type(self):
"""Tries to create a column with a bad data type."""
with self.assertRaises(ValueError):
Column(column_name="column_1", column_type="bit")
def test_create_column_without_parameters(self):
"""Tests creating columns with missing parameters."""
with self.assertRaises(AssertionError):
Column(column_name="column_1", column_type=None)
with self.assertRaises(AssertionError):
Column(column_name=None, column_type="INT")
def get_columns(self, buff):
"""
Get the columns from the table statement.
:param buff: The buffer with the create details.
:type buff: str
:return: A list of Columns
:rtype: list
"""
# The fields will be between the ( ).
columns = []
buff = buff[buff.find("(") + 1:buff.rfind(")")].strip()
# think all DBs use commas for field separators
# need to find the commas that are not inside of parents.
field_buff = ""
open_paren = False
raw_fields = []
for c in buff:
if open_paren:
field_buff += c
if c == ")":
open_paren = False
elif c == "(":
field_buff += c
open_paren = True
else:
if c == ",":
raw_fields.append(field_buff)
field_buff = ""
else:
field_buff += c
if field_buff != "":
raw_fields.append(field_buff)
for rf in raw_fields:
rfl = rf.lower()
# ignore key declarations.
if "key " in rfl:
continue
had_quote = False
if rfl[0] in "\"'`": # should be a quote or letter
had_quote = True
name = rf[1:rf.find(rf[0], 1)]
else:
name = rf[0:rf.find(" ")]
# The type comes after the name and goes up to the first of a
# space, close paren, or comma. Assuming no space in type.
start_idx = len(name) + (
3 if had_quote else 1
) # extra 1 for space
if rfl.find(")") > 0: # type with ()
data_type = rf[start_idx:rf.find(")") + 1]
else:
# either next space or comma.
space_end_idx = rf.find(" ", start_idx)
comma_end_idx = rf.find(",", start_idx)
if space_end_idx == -1: # not found
if comma_end_idx == -1: # neither found
end_idx = len(rf) # end of line
else:
end_idx = comma_end_idx
elif comma_end_idx == -1:
end_idx = space_end_idx
else:
end_idx = min(space_end_idx, comma_end_idx)
data_type = rf[start_idx:end_idx]
# print (" adding %s as %s" % (name, data_type))
columns.append(
Column(
column_name=name, column_type=self.convert_type(data_type)
)
)
return columns