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')
})
