def install_db(database, db_filepath=None, schema_version=None):
"""Install database. If database already exists, it is first removed."""
# No need to specify database yet, since it needs to first check if the
# database exists.
alchemist1 = AlchemyHandler(database="")
alchemist1.connect(pipeline=True)
engine1 = alchemist1.engine
result = mysqldb_basic.drop_create_db(engine1, database)
if result != 0:
print("Unable to create new, empty database.")
else:
alchemist2 = AlchemyHandler(database=database,
username=engine1.url.username,
password=engine1.url.password)
alchemist2.connect(pipeline=True)
engine2 = alchemist2.engine
if engine2 is None:
print(f"No connection to the {database} database due "
"to invalid credentials or database.")
else:
if db_filepath is not None:
mysqldb_basic.install_db(engine2, db_filepath)
else:
mysqldb.execute_transaction(engine2, db_schema_0.STATEMENTS)
convert_args = [
"pdm_utils.run", "convert", database, "-s",
str(schema_version)
]
convert_db.main(convert_args, engine2)
# Close up all connections in the connection pool.
engine2.dispose()
# Close up all connections in the connection pool.
engine1.dispose()
def test_drop_create_db_2(self):
"""Verify non-existing database is not dropped but is created."""
before = test_db_utils.check_if_exists(DB2)
result = mysqldb_basic.drop_create_db(self.engine, DB2)
after = test_db_utils.check_if_exists(DB2)
after_tables = test_db_utils.execute(TABLES_QUERY.format(DB2), db=DB2)
with self.subTest():
self.assertFalse(before)
with self.subTest():
self.assertTrue(after)
with self.subTest():
self.assertEqual(result, 0)
with self.subTest():
self.assertTrue(len(after_tables) == 0)
def test_drop_create_db_1(self):
"""Verify already-existing database is dropped and created."""
before = test_db_utils.check_if_exists()
before_tables = test_db_utils.execute(TABLES_QUERY.format(DB))
result = mysqldb_basic.drop_create_db(self.engine, DB)
after = test_db_utils.check_if_exists()
after_tables = test_db_utils.execute(TABLES_QUERY.format(DB))
with self.subTest():
self.assertTrue(before)
with self.subTest():
self.assertTrue(after)
with self.subTest():
self.assertEqual(result, 0)
with self.subTest():
self.assertTrue(len(before_tables) > 0)
with self.subTest():
self.assertTrue(len(after_tables) == 0)
def test_drop_create_db_3(self, drop_mock):
"""Verify database is not created if there is an error during drop."""
drop_mock.return_value = 1
before = test_db_utils.check_if_exists()
before_tables = test_db_utils.execute(TABLES_QUERY.format(DB))
result = mysqldb_basic.drop_create_db(self.engine, DB)
after = test_db_utils.check_if_exists()
after_tables = test_db_utils.execute(TABLES_QUERY.format(DB))
with self.subTest():
self.assertTrue(before)
with self.subTest():
self.assertTrue(after)
with self.subTest():
self.assertEqual(result, 1)
with self.subTest():
self.assertTrue(len(before_tables) > 0)
with self.subTest():
self.assertTrue(len(after_tables) > 0)
with self.subTest():
self.assertEqual(len(before_tables), len(after_tables))
with self.subTest():
drop_mock.assert_called()
def main(unparsed_args_list):
"""Run main freeze database pipeline."""
args = parse_args(unparsed_args_list)
ref_database = args.database
reset = args.reset
new_database = args.new_database_name
prefix = args.prefix
# Filters input: phage.Status=draft AND phage.HostGenus=Mycobacterium
# Args structure: [['phage.Status=draft'], ['phage.HostGenus=Mycobacterium']]
filters = args.filters
# Create config object with data obtained from file and/or defaults.
config = configfile.build_complete_config(args.config_file)
mysql_creds = config["mysql"]
# Verify database connection and schema compatibility.
print("Connecting to the MySQL database...")
alchemist1 = AlchemyHandler(database=ref_database,
username=mysql_creds["user"],
password=mysql_creds["password"])
alchemist1.connect(pipeline=True)
engine1 = alchemist1.engine
mysqldb.check_schema_compatibility(engine1, "the freeze pipeline")
# Get SQLAlchemy metadata Table object
# table_obj.primary_key.columns is a
# SQLAlchemy ColumnCollection iterable object
# Set primary key = 'phage.PhageID'
alchemist1.build_metadata()
table = querying.get_table(alchemist1.metadata, TARGET_TABLE)
for column in table.primary_key.columns:
primary_key = column
# Create filter object and then add command line filter strings
db_filter = Filter(alchemist=alchemist1, key=primary_key)
db_filter.values = []
# Attempt to add filters and exit if needed.
add_filters(db_filter, filters)
# Performs the query
db_filter.update()
# db_filter.values now contains list of PhageIDs that pass the filters.
# Get the number of genomes that will be retained and build the
# MYSQL DELETE statement.
keep_set = set(db_filter.values)
delete_stmt = construct_delete_stmt(TARGET_TABLE, primary_key, keep_set)
count_query = construct_count_query(TARGET_TABLE, primary_key, keep_set)
phage_count = mysqldb_basic.scalar(alchemist1.engine, count_query)
# Determine the name of the new database.
if new_database is None:
if prefix is None:
prefix = get_prefix()
new_database = f"{prefix}_{phage_count}"
# Create the new database, but prevent overwriting of current database.
if engine1.url.database != new_database:
result = mysqldb_basic.drop_create_db(engine1, new_database)
else:
print(
"Error: names of the reference and frozen databases are the same.")
print("No database will be created.")
result = 1
# Copy database.
if result == 0:
print(f"Reference database: {ref_database}")
print(f"New database: {new_database}")
result = mysqldb_basic.copy_db(engine1, new_database)
if result == 0:
print(f"Deleting genomes...")
alchemist2 = AlchemyHandler(database=new_database,
username=engine1.url.username,
password=engine1.url.password)
alchemist2.connect(pipeline=True)
engine2 = alchemist2.engine
engine2.execute(delete_stmt)
if reset:
engine2.execute(RESET_VERSION)
# Close up all connections in the connection pool.
engine2.dispose()
else:
print("Unable to copy the database.")
# Close up all connections in the connection pool.
engine1.dispose()
else:
print(f"Error creating new database: {new_database}.")
print("Freeze database script completed.")
def install_db(alchemist,
database,
db_filepath=None,
config_file=None,
schema_version=None,
verbose=False,
pipeline=False):
"""
Install database. If database already exists, it is first removed.
:param database: Name of the database to be installed
:type database: str
:param db_filepath: Directory for installation
:type db_filepath: Path
"""
# No need to specify database yet, since it needs to first check if the
# database exists.
engine = alchemist.engine
result = mysqldb_basic.drop_create_db(engine, database)
engine.dispose()
if result != 0:
if pipeline:
print("Unable to create new, empty database.\nPlease "
"check SQL service status and/or database availability.")
sys.exit(1)
raise OSError("Unable to create new, empty database.\nPlease "
"check SQL service status and/or database availability.")
alchemist.database = database
try:
alchemist.validate_database()
except MySQLDatabaseError:
if pipeline:
print(f"No connection to database {database} due "
"to invalid credentials and/or database.")
sys.exit(1)
raise MySQLDatabaseError(f"No connection to database {database} due "
"to invalid credentials and/or database.")
alchemist.build_engine()
engine = alchemist.engine
if db_filepath is not None:
mysqldb_basic.install_db(engine, db_filepath)
else:
mysqldb.execute_transaction(engine, db_schema_0.STATEMENTS)
if schema_version is not None:
curr_schema_version = mysqldb.get_schema_version(engine)
if not curr_schema_version == schema_version:
if verbose:
print(f"Schema version {curr_schema_version} "
"database detected.\nBeginning database conversion to "
f"schema version {schema_version}...")
convert_args = [
"pdm_utils.run", "convert", database, "-s",
str(schema_version)
]
if verbose:
convert_args.append("-v")
if config_file is not None:
convert_args.extend(["-c", config_file])
convert_db.main(convert_args)
engine.dispose()
def main(unparsed_args_list):
"""Run main conversion pipeline."""
# Parse command line arguments
args = parse_args(unparsed_args_list)
config = configfile.build_complete_config(args.config_file)
mysql_creds = config["mysql"]
alchemist1 = AlchemyHandler(database=args.database,
username=mysql_creds["user"],
password=mysql_creds["password"])
alchemist1.connect(pipeline=True)
engine1 = alchemist1.engine
target = args.schema_version
actual = mysqldb.get_schema_version(engine1)
steps, dir = get_conversion_direction(actual, target)
# Iterate through list of versions and implement SQL files.
if dir == "none":
if args.verbose == True:
print("No schema conversion is needed.")
convert = False
else:
convert = True
if convert == True:
if (args.new_database_name is not None and
args.new_database_name != args.database):
result = mysqldb_basic.drop_create_db(engine1, args.new_database_name)
if result == 0:
result = mysqldb_basic.copy_db(engine1, args.new_database_name)
if result == 0:
# Create a new connection to the new database.
alchemist2 = AlchemyHandler(database=args.new_database_name,
username=engine1.url.username,
password=engine1.url.password)
alchemist2.connect(pipeline=True)
engine2 = alchemist2.engine
else:
print("Error: Unable to copy the database for conversion.")
convert = False
else:
print("Error: Unable to create the new database for conversion.")
convert = False
else:
engine2 = engine1
if convert == True:
stop_step, summary = convert_schema(engine2, actual, dir,
steps, verbose=args.verbose)
engine2.dispose()
if stop_step == target:
if args.verbose == True:
print("\n\nThe database schema conversion was successful.")
else:
print("\n\nError: "
"The database schema conversion was not successful. "
f"Unable to proceed past schema version {stop_step}.")
if args.verbose == True:
print_summary(summary)
engine1.dispose()