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()