async def async_main(): await create_database() namespace = create_database_config['namespace'] scope = create_database_config['scope'] cloud = create_database_config['cloud'] database_name = create_database_config['database_name'] admin_secret_name = f'sql-{database_name}-admin-config' out, _ = await check_shell_output(f''' kubectl -n {namespace} get -o json secret {shq(admin_secret_name)} ''') admin_secret = json.loads(out) with open('/sql-config.json', 'wb') as f: f.write(base64.b64decode(admin_secret['data']['sql-config.json'])) with open('/sql-config.cnf', 'wb') as f: f.write(base64.b64decode(admin_secret['data']['sql-config.cnf'])) os.environ['HAIL_DATABASE_CONFIG_FILE'] = '/sql-config.json' os.environ['HAIL_SCOPE'] = scope os.environ['HAIL_CLOUD'] = cloud db = Database() await db.async_init() rows = db.execute_and_fetchall( f"SHOW TABLES LIKE '{database_name}_migration_version';") rows = [row async for row in rows] if len(rows) == 0: await db.just_execute(f''' CREATE TABLE `{database_name}_migration_version` ( `version` BIGINT NOT NULL ) ENGINE = InnoDB; INSERT INTO `{database_name}_migration_version` (`version`) VALUES (1); CREATE TABLE `{database_name}_migrations` ( `version` BIGINT NOT NULL, `name` VARCHAR(100), `script_sha1` VARCHAR(40), PRIMARY KEY (`version`) ) ENGINE = InnoDB; ''') migrations = create_database_config['migrations'] for i, m in enumerate(migrations): await migrate(database_name, db, i, m)
async def instance_collections_from_db( db: Database, ) -> Tuple[Dict[str, PoolConfig], JobPrivateInstanceManagerConfig]: records = db.execute_and_fetchall(''' SELECT inst_colls.*, pools.* FROM inst_colls LEFT JOIN pools ON inst_colls.name = pools.name; ''') name_pool_config: Dict[str, PoolConfig] = {} jpim_config: Optional[JobPrivateInstanceManagerConfig] = None async for record in records: if record['is_pool']: config = PoolConfig.from_record(record) name_pool_config[config.name] = config else: config = JobPrivateInstanceManagerConfig.from_record(record) jpim_config = config assert jpim_config is not None return name_pool_config, jpim_config
async def create_database(): with open('/sql-config/sql-config.json', 'r') as f: sql_config = SQLConfig.from_json(f.read()) namespace = create_database_config['namespace'] database_name = create_database_config['database_name'] cant_create_database = create_database_config['cant_create_database'] if cant_create_database: assert sql_config.db is not None await write_user_config(namespace, database_name, 'admin', sql_config) await write_user_config(namespace, database_name, 'user', sql_config) return scope = create_database_config['scope'] _name = create_database_config['_name'] admin_username = create_database_config['admin_username'] user_username = create_database_config['user_username'] db = Database() await db.async_init() if scope == 'deploy': assert _name == database_name # create if not exists rows = db.execute_and_fetchall( f"SHOW DATABASES LIKE '{database_name}';") rows = [row async for row in rows] if len(rows) > 0: assert len(rows) == 1 return with open(create_database_config['admin_password_file']) as f: admin_password = f.read() with open(create_database_config['user_password_file']) as f: user_password = f.read() await db.just_execute(f''' CREATE DATABASE IF NOT EXISTS `{_name}`; CREATE USER IF NOT EXISTS '{admin_username}'@'%' IDENTIFIED BY '{admin_password}'; GRANT ALL ON `{_name}`.* TO '{admin_username}'@'%'; CREATE USER IF NOT EXISTS '{user_username}'@'%' IDENTIFIED BY '{user_password}'; GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `{_name}`.* TO '{user_username}'@'%'; ''') await write_user_config( namespace, database_name, 'admin', SQLConfig( host=sql_config.host, port=sql_config.port, instance=sql_config.instance, connection_name=sql_config.instance, user=admin_username, password=admin_password, db=_name, ssl_ca=sql_config.ssl_ca, ssl_cert=sql_config.ssl_cert, ssl_key=sql_config.ssl_key, ssl_mode=sql_config.ssl_mode, ), ) await write_user_config( namespace, database_name, 'user', SQLConfig( host=sql_config.host, port=sql_config.port, instance=sql_config.instance, connection_name=sql_config.instance, user=user_username, password=user_password, db=_name, ssl_ca=sql_config.ssl_ca, ssl_cert=sql_config.ssl_cert, ssl_key=sql_config.ssl_key, ssl_mode=sql_config.ssl_mode, ), )
async def refresh_resource_rates_from_db(db: Database) -> Dict[str, str]: records = db.execute_and_fetchall('SELECT resource, rate FROM resources') return {record['resource']: record['rate'] async for record in records}
async def refresh_product_versions_from_db(db: Database) -> Dict[str, str]: records = db.execute_and_fetchall( 'SELECT product, version FROM latest_product_versions') return {record['product']: record['version'] async for record in records}
async def product_versions_from_db(db: Database) -> Dict[str, str]: return { record['product']: record['version'] async for record in db.execute_and_fetchall( 'SELECT * FROM latest_product_versions;') }
async def resource_rates_from_db(db: Database) -> Dict[str, float]: return { record['resource']: record['rate'] async for record in db.execute_and_fetchall( 'SELECT * FROM resources;') }
async def create_database(): with open('/sql-config/sql-config.json', 'r') as f: sql_config = json.loads(f.read()) namespace = create_database_config['namespace'] database_name = create_database_config['database_name'] cant_create_database = create_database_config['cant_create_database'] if cant_create_database: assert sql_config.get('db') is not None await write_user_config(namespace, database_name, 'admin', sql_config) await write_user_config(namespace, database_name, 'user', sql_config) return scope = create_database_config['scope'] _name = create_database_config['_name'] admin_username = create_database_config['admin_username'] user_username = create_database_config['user_username'] db = Database() await db.async_init() if scope == 'deploy': assert _name == database_name # create if not exists rows = db.execute_and_fetchall( f"SHOW DATABASES LIKE '{database_name}';") rows = [row async for row in rows] if len(rows) > 0: assert len(rows) == 1 return with open(create_database_config['admin_password_file']) as f: admin_password = f.read() with open(create_database_config['user_password_file']) as f: user_password = f.read() await db.just_execute(f''' CREATE DATABASE IF NOT EXISTS `{_name}`; CREATE USER IF NOT EXISTS '{admin_username}'@'%' IDENTIFIED BY '{admin_password}'; GRANT ALL ON `{_name}`.* TO '{admin_username}'@'%'; CREATE USER IF NOT EXISTS '{user_username}'@'%' IDENTIFIED BY '{user_password}'; GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `{_name}`.* TO '{user_username}'@'%'; ''') await write_user_config( namespace, database_name, 'admin', { 'host': sql_config['host'], 'port': sql_config['port'], 'instance': sql_config['instance'], 'connection_name': sql_config['connection_name'], 'user': admin_username, 'password': admin_password, 'db': _name, 'ssl-ca': sql_config.get('ssl-ca'), 'ssl-cert': sql_config.get('ssl-cert'), 'ssl-key': sql_config.get('ssl-key'), 'ssl-mode': sql_config.get('ssl-mode') }) await write_user_config( namespace, database_name, 'user', { 'host': sql_config['host'], 'port': sql_config['port'], 'instance': sql_config['instance'], 'connection_name': sql_config['connection_name'], 'user': user_username, 'password': user_password, 'db': _name, 'ssl-ca': sql_config.get('ssl-ca'), 'ssl-cert': sql_config.get('ssl-cert'), 'ssl-key': sql_config.get('ssl-key'), 'ssl-mode': sql_config.get('ssl-mode') })