def read_ddl(args):
"""
Reads database DDL and returns a database model.
:param args: The command line arguments.
:returns: The database read from the DDL.
:rtype: Database
"""
parser = DDLParser(args.database, args.schema)
return parser.parse_ddl(args.from_ddl)
def test_primary_keys(self):
"""Tests the review of primary keys."""
parser = DDLParser(database_name="review_pks")
database = parser.parse_ddl("no_pks.tql")
reviewer = DataModelReviewer()
issues = reviewer.review_model(database=database)
self.assertEqual(1, len(issues["review_pks"]))
def test_long_chain_relationships(self):
"""Tests if a relationship between two tables is longer than recommended."""
parser = DDLParser(database_name="test_db")
database = parser.parse_ddl("long_chain.tql")
reviewer = DataModelReviewer()
issues = reviewer.review_model(database=database)
self.assertEqual(6, len(issues["review_long_chain_relationships"]))
def test_db_circular_relationship(self):
"""Tests a database for issues."""
parser = DDLParser(database_name="test_db")
database = parser.parse_ddl("circular.tql")
reviewer = DataModelReviewer()
issues = reviewer.review_model(database=database)
self.assertEqual(6, len(issues["review_circular_relationships"]))
def main():
"""Main function for the script."""
args = parse_args()
if valid_args(args):
print(args)
sys.setrecursionlimit(10000) # expanding from the default of 1000. Might cause memory errors.
database = None
worksheet = None
rtql = None
if args.version:
version_path = os.path.dirname(os.path.abspath(__file__))
print(f"convert_ddl (v{VERSION}): {version_path}/convert_ddl")
exit(0) # just exit if printing the version -- similar behavior to help.
if args.debug:
logging.basicConfig(level=logging.DEBUG)
reviewer = DataModelReviewer()
if args.show_tests:
descriptions = reviewer.get_test_descriptions()
print(f"Found {len(descriptions)} tests.")
for test in descriptions.keys():
print("")
print(f"{test}:")
for desc in descriptions[test]:
print(f"\t{desc}")
print("")
# create the database.
if args.ts_ip:
database = read_from_ts(args)
elif args.database_file:
parser = DDLParser(database_name=args.database) # these tests ignore the schema name.
database = parser.parse_ddl(filename=args.database_file)
else: # only continue if there is a database.
exit(0)
# read the worksheet.
# TODO add logic for reading a worksheet.
# create an RTQL object
if args.ts_ip:
rtql = RemoteTQL(hostname=args.ts_ip, username=args.username, password=args.password)
reviewer = DataModelReviewer()
results = reviewer.review_model(database=database, worksheet=worksheet, rtql=rtql)
for test in results.keys():
issues = results[test]
print(f"{test}:")
for issue in issues:
print(f"\t{issue}")
def test_table_joins(self):
"""Tests the review of the joins between tables in a database."""
parser = DDLParser(database_name="table_join_test")
database = parser.parse_ddl("table_joins.tql")
rtql = RemoteTQL(hostname=TS_URL,
username=TS_USER,
password=TS_PASSWORD)
reviewer = DataModelReviewer()
issues = reviewer.review_model(test_names=["review_table_joins"],
database=database,
rtql=rtql)
self.assertEqual(2, len(issues['review_table_joins']))
def test_sharding(self):
"""Tests the review of sharding. This test assumes the sharding database has been loaded with data."""
parser = DDLParser(database_name="review_test_sharding")
database = parser.parse_ddl("test_sharding.tql")
rtql = RemoteTQL(hostname=TS_URL,
username=TS_USER,
password=TS_PASSWORD)
reviewer = DataModelReviewer()
issues = reviewer.review_model(database=database, rtql=rtql)
self.assertEqual(6, len(issues["review_sharding"]))
def test_worksheet_join_types(self):
"""Tests the review of the joins between tables in worksheet.."""
parser = DDLParser(database_name="golf_sales")
database = parser.parse_ddl("test_data/golf_sales/golf_sales.tql")
ws_reader = YAMLWorksheetReader()
worksheet = ws_reader.read_from_file(
"test_data/golf_sales/Golf Sales WS.yaml")
rtql = RemoteTQL(hostname=TS_URL,
username=TS_USER,
password=TS_PASSWORD)
reviewer = DataModelReviewer()
issues = reviewer.review_model(database=database,
worksheet=worksheet,
rtql=rtql)
self.assertEqual(3, len(issues["review_worksheet_joins"]))
def read_from_ts(args):
"""
Reads the database (from args) from TQL remotely.
:param args: The argument list. Must have the host, database and possibly user/password.
:return: A database that was read.
:rtype: Database
"""
rtql = RemoteTQL(hostname=args.ts_ip, username=args.username, password=args.password)
out = rtql.run_tql_command(f"script database {args.database};")
# The parser expects a file, so create a temp file, parse, then delete.
filename = f"{args.database}.tmp"
with open(filename, "w") as outfile:
for line in out:
outfile.write(line + "\n")
parser = DDLParser(database_name=args.database)
database = parser.parse_ddl(filename=filename)
os.remove(filename)
return database
def main():
"""Main function for the script."""
args = parse_args()
if valid_args(args):
print(args)
ddl_parser = DDLParser(database_name=args.database,
schema_name=args.schema)
db_1 = ddl_parser.parse_ddl(args.ddl1)
ddl_parser = DDLParser(database_name=args.database,
schema_name=args.schema)
db_2 = ddl_parser.parse_ddl(args.ddl2)
# Returns differences for each database as a tuple.
database_differences = DDLCompare.compare_databases(db_1, db_2)
if args.alter1:
logging.debug(
"generate alters for first schema to match the second")
print("-- changes needed for first schema to match the second")
TQLAlterWriter().write_alters(database_differences[0])
if args.alter2:
logging.debug(
"generate alters for second schema to match the first")
print("-- changes needed for second schema to match the first")
TQLAlterWriter().write_alters(database_differences[1])
if not args.alter1 and not args.alter2:
print("Database differences for DB 1:")
for db_diff in database_differences[0]:
print("\t%s" % db_diff)
print("Database differences for DB 2:")
for db_diff in database_differences[1]:
print("\t%s" % db_diff)
def test_column_types(self):
"""Tests converting various types of columns."""
self.assertEqual("BIGINT", DDLParser._convert_type("integer"))
self.assertEqual("INT", DDLParser._convert_type("rowversion"))
self.assertEqual("VARCHAR(0)",
DDLParser._convert_type("uniqueidentifier"))
self.assertEqual("INT", DDLParser._convert_type("serial"))
self.assertEqual("BOOL", DDLParser._convert_type("bit"))
self.assertEqual("UNKNOWN", DDLParser._convert_type("blob"))
self.assertEqual("UNKNOWN", DDLParser._convert_type("binary"))
self.assertEqual("BIGINT", DDLParser._convert_type("number"))
self.assertEqual("INT", DDLParser._convert_type("number(1)"))
self.assertEqual("INT", DDLParser._convert_type("NUMBER(1)"))
self.assertEqual("INT", DDLParser._convert_type("NUMBER(3,0)"))
self.assertEqual("BIGINT", DDLParser._convert_type("NUMBER(10,0)"))
self.assertEqual("BIGINT", DDLParser._convert_type("NUMBER(*,0)"))
self.assertEqual("DOUBLE", DDLParser._convert_type("NUMBER(4,2)"))
self.assertEqual("DOUBLE", DDLParser._convert_type("decimal"))
self.assertEqual("DOUBLE", DDLParser._convert_type("numeric"))
self.assertEqual("DOUBLE", DDLParser._convert_type("float"))
self.assertEqual("DOUBLE", DDLParser._convert_type("double"))
self.assertEqual("DOUBLE", DDLParser._convert_type("money"))
self.assertEqual("DOUBLE", DDLParser._convert_type("real"))
self.assertEqual("DATETIME", DDLParser._convert_type("datetime"))
self.assertEqual("TIME", DDLParser._convert_type("time"))
self.assertEqual("DATE", DDLParser._convert_type("date"))
self.assertEqual("VARCHAR(0)", DDLParser._convert_type("text"))
self.assertEqual("VARCHAR(0)", DDLParser._convert_type("varchar"))
self.assertEqual("VARCHAR(0)", DDLParser._convert_type("char"))
self.assertEqual("VARCHAR(0)", DDLParser._convert_type("varchar(88)"))
self.assertEqual("VARCHAR(0)", DDLParser._convert_type("long"))
self.assertEqual("VARCHAR(0)", DDLParser._convert_type("enum"))
self.assertEqual("VARCHAR(0)", DDLParser._convert_type("xml"))
self.assertEqual("UNKNOWN", DDLParser._convert_type("something_new"))
def test_create_ddlparser_with_all_params(self):
"""Test creating a parser with all values provided."""
dp = DDLParser("testdb", "testschema")
self.assertEqual(dp.database_name, "testdb")
self.assertEqual(dp.schema_name, "testschema")
def test_create_ddlparser_with_defaults(self):
"""Test creating a parser with defaults."""
dp = DDLParser("testdb")
self.assertEqual(dp.database_name, "testdb")
self.assertEqual(dp.schema_name, DatamodelConstants.DEFAULT_SCHEMA)