async def update_schema_v_1_3_1_0() -> bool:
schema_version = await get_schema_version()
if schema_version:
compare_1300 = util.compare_versions(schema_version, '1.3.1.0')
compare_1290 = util.compare_versions(schema_version, '1.3.0.0')
if compare_1300 <= 0:
return True
elif compare_1290 > 0:
return False
print(
f'[update_schema_v_1_3_1_0] Updating database schema from v1.3.0.0 to v1.3.1.0'
)
query_add_column = f'ALTER TABLE serversettings ADD COLUMN useembeds BOOL;'
success_add_column = await try_execute(query_add_column)
if not success_add_column:
print(
f'[update_schema_v_1_3_1_0] ERROR: Failed to add column \'useembeds\' to table \'serversettings\'!'
)
return False
success = await try_set_schema_version('1.3.1.0')
return success
def db_update_schema_v_1_2_4_0():
column_definitions = [('dailydeleteonchange', 'BOOLEAN', False, False,
None)]
schema_version = db_get_schema_version()
if schema_version:
compare_1240 = util.compare_versions(schema_version, '1.2.4.0')
compare_1220 = util.compare_versions(schema_version, '1.2.2.0')
if compare_1240 <= 0:
return True
elif compare_1220 > 0:
return False
query_lines = []
for (column_name, column_type, column_is_primary, column_not_null,
column_default) in column_definitions:
column_definition = util.db_get_column_definition(
column_name,
column_type,
is_primary=column_is_primary,
not_null=column_not_null,
default=column_default)
query_lines.append(
f'ALTER TABLE serversettings ADD COLUMN IF NOT EXISTS {column_definition}'
)
query = '\n'.join(query_lines)
success = db_try_execute(query)
if success:
utc_now = util.get_utcnow()
daily_info = daily.get_daily_info()
success = daily.db_set_daily_info(daily_info, utc_now)
if success:
success = db_try_set_schema_version('1.2.4.0')
return success
async def update_schema_v_1_2_5_0() -> bool:
column_definitions = [('dailynotifyid', 'TEXT', False, False),
('dailynotifytype', 'TEXT', False, False)]
schema_version = await get_schema_version()
if schema_version:
compare_1250 = util.compare_versions(schema_version, '1.2.5.0')
compare_1240 = util.compare_versions(schema_version, '1.2.4.0')
if compare_1250 <= 0:
return True
elif compare_1240 > 0:
return False
print(
f'[update_schema_v_1_2_5_0] Updating database schema from v1.2.4.0 to v1.2.5.0'
)
query_lines = []
for (column_name, column_type, column_is_primary,
column_not_null) in column_definitions:
column_definition = util.db_get_column_definition(
column_name,
column_type,
is_primary=column_is_primary,
not_null=column_not_null)
query_lines.append(
f'ALTER TABLE serversettings ADD COLUMN IF NOT EXISTS {column_definition};'
)
query = '\n'.join(query_lines)
success = await try_execute(query)
if success:
success = await try_set_schema_version('1.2.5.0')
return success
def db_update_schema_v_1_2_6_0():
column_definitions_serversettings = [
('dailylatestmessagecreatedate', 'TIMESTAMPTZ', False, False),
('dailylatestmessagemodifydate', 'TIMESTAMPTZ', False, False)
]
schema_version = db_get_schema_version()
if schema_version:
compare_1260 = util.compare_versions(schema_version, '1.2.6.0')
compare_1250 = util.compare_versions(schema_version, '1.2.5.0')
if compare_1260 <= 0:
return True
elif compare_1250 > 0:
return False
query_lines = []
for (column_name, column_type, column_is_primary,
column_not_null) in column_definitions_serversettings:
column_definition = util.db_get_column_definition(
column_name,
column_type,
is_primary=column_is_primary,
not_null=column_not_null)
query_lines.append(
f'ALTER TABLE serversettings ADD COLUMN IF NOT EXISTS {column_definition};'
)
query = '\n'.join(query_lines)
success = db_try_execute(query)
if success:
success = db_try_set_schema_version('1.2.6.0')
return success
async def update_schema_v_1_2_9_0() -> bool:
schema_version = await get_schema_version()
if schema_version:
compare_1290 = util.compare_versions(schema_version, '1.2.9.0')
compare_1280 = util.compare_versions(schema_version, '1.2.8.0')
if compare_1290 <= 0:
return True
elif compare_1280 > 0:
return False
print(
f'[update_schema_v_1_2_9_0] Updating database schema from v1.2.8.0 to v1.2.9.0'
)
query_add_column = f'ALTER TABLE serversettings ADD COLUMN dailychangemode INT;'
success_add_column = await try_execute(query_add_column)
if not success_add_column:
print(
f'[update_schema_v_1_2_9_0] ERROR: Failed to add column \'dailychangemode\' to table \'serversettings\'!'
)
return False
query_lines_move_data = [
f'UPDATE serversettings SET dailychangemode = 1 WHERE dailydeleteonchange IS NULL;'
]
query_lines_move_data.append(
f'UPDATE serversettings SET dailychangemode = 2 WHERE dailydeleteonchange = {util.db_convert_boolean(True)};'
)
query_lines_move_data.append(
f'UPDATE serversettings SET dailychangemode = 3 WHERE dailydeleteonchange = {util.db_convert_boolean(False)};'
)
query_move_data = '\n'.join(query_lines_move_data)
success_move_data = await try_execute(query_move_data)
if not success_move_data:
print(
f'[update_schema_v_1_2_9_0] ERROR: Failed to convert and copy data from column \'dailydeleteonchange\' into column \'dailychangemode\'!'
)
return False
query_drop_column = f'ALTER TABLE DROP COLUMN IF EXISTS dailydeleteonchange;'
success_drop_column = await try_execute(query_drop_column)
if not success_drop_column:
print(
f'[update_schema_v_1_2_9_0] ERROR: Failed to drop column \'dailydeleteonchange\'!'
)
return False
success = await try_set_schema_version('1.2.9.0')
return success
def db_update_schema_v_1_2_7_0():
column_definitions_devices = [('key', 'TEXT', True, True),
('checksum', 'TEXT', False, False),
('loginuntil', 'TIMESTAMPTZ', False, False)]
schema_version = db_get_schema_version()
if schema_version:
compare_1270 = util.compare_versions(schema_version, '1.2.7.0')
compare_1260 = util.compare_versions(schema_version, '1.2.6.0')
if compare_1270 <= 0:
return True
elif compare_1260 > 0:
return False
success = db_try_create_table('devices', column_definitions_devices)
if success:
success = db_try_set_schema_version('1.2.7.0')
return success
async def update_schema_v_1_3_0_0() -> bool:
schema_version = await get_schema_version()
if schema_version:
compare_1300 = util.compare_versions(schema_version, '1.3.0.0')
compare_1290 = util.compare_versions(schema_version, '1.2.9.0')
if compare_1300 <= 0:
return True
elif compare_1290 > 0:
return False
print(
f'[update_schema_v_1_3_0_0] Updating database schema from v1.2.9.0 to v1.3.0.0'
)
query_add_column = f'ALTER TABLE serversettings ADD COLUMN botnewschannelid BIGINT;'
success_add_column = await try_execute(query_add_column)
if not success_add_column:
print(
f'[update_schema_v_1_3_0_0] ERROR: Failed to add column \'botnewschannelid\' to table \'serversettings\'!'
)
return False
column_definitions_sales = [
('id', 'SERIAL', True, True),
('limitedcatalogargument', 'INT', False, False),
('limitedcatalogtype', 'TEXT', False, False),
('limitedcatalogcurrencytype', 'TEXT', False, False),
('limitedcatalogcurrencyamount', 'INT', False, False),
('limitedcatalogmaxtotal', 'INT', False, False),
('limitedcatalogexpirydate', 'TIMESTAMPTZ', False, False)
]
success_create_table = await try_create_table('sales',
column_definitions_sales)
if not success_create_table:
print(
f'[update_schema_v_1_3_0_0] ERROR: Failed to add table \'sales\'!')
return False
success = await try_set_schema_version('1.3.0.0')
return success
async def db_update_schema_v_1_2_7_0() -> bool:
column_definitions_devices = [('key', 'TEXT', True, True),
('checksum', 'TEXT', False, False),
('loginuntil', 'TIMESTAMPTZ', False, False)]
schema_version = await get_schema_version()
if schema_version:
compare_1270 = util.compare_versions(schema_version, '1.2.7.0')
compare_1260 = util.compare_versions(schema_version, '1.2.6.0')
if compare_1270 <= 0:
return True
elif compare_1260 > 0:
return False
print(
f'[update_schema_v_1_2_8_0] Updating database schema from v1.2.6.0 to v1.2.7.0'
)
success = await try_create_table('devices', column_definitions_devices)
if success:
success = await try_set_schema_version('1.2.7.0')
return success
async def update_schema_v_1_2_8_0() -> bool:
column_definitions_serversettings = [
('guildid', 'BIGINT', True, True),
('dailychannelid', 'BIGINT', False, False),
('dailylatestmessageid', 'BIGINT', False, False),
('dailynotifyid', 'BIGINT', False, False),
('dailynotifytype', 'INT', False, False)
]
schema_version = await get_schema_version()
if schema_version:
compare_1280 = util.compare_versions(schema_version, '1.2.8.0')
compare_1270 = util.compare_versions(schema_version, '1.2.7.0')
if compare_1280 <= 0:
return True
elif compare_1270 > 0:
return False
print(
f'[update_schema_v_1_2_8_0] Updating database schema from v1.2.7.0 to v1.2.8.0'
)
query_lines = ['ALTER TABLE serversettings']
for column_name, new_column_type, _, _ in column_definitions_serversettings:
if new_column_type in USING_LOOKUP:
using = f' USING {column_name}::{USING_LOOKUP[new_column_type]}'
else:
using = ''
query_lines.append(
f'ALTER COLUMN {column_name} SET DATA TYPE {new_column_type}{using},'
)
query_lines[-1] = query_lines[-1].replace(',', ';')
query = '\n'.join(query_lines)
success = await try_execute(query)
if success:
success = await try_set_schema_version('1.2.8.0')
return success
async def create_schema() -> bool:
column_definitions_settings = [('settingname', 'TEXT', True, True),
('modifydate', 'TIMESTAMPTZ', False, True),
('settingboolean', 'BOOLEAN', False, False),
('settingfloat', 'FLOAT', False, False),
('settingint', 'INT', False, False),
('settingtext', 'TEXT', False, False),
('settingtimestamp', 'TIMESTAMPTZ', False,
False)]
column_definitions_daily = [('guildid', 'TEXT', True, True),
('channelid', 'TEXT', False, True),
('canpost', 'BOOLEAN')]
query_server_settings = 'SELECT * FROM serversettings'
schema_version = await get_schema_version()
if schema_version:
compare_1000 = util.compare_versions(schema_version, '1.0.0.0')
if compare_1000 <= 0:
return True
print(f'[create_schema] Creating database schema v1.0.0.0')
success_settings = await try_create_table('settings',
column_definitions_settings)
if not success_settings:
print(
'[create_schema] DB initialization failed upon creating the table \'settings\'.'
)
create_daily = False
try:
_ = await fetchall(query_server_settings)
except asyncpg.exceptions.UndefinedTableError:
create_daily = True
if create_daily:
success_daily = await try_create_table('daily',
column_definitions_daily)
else:
success_daily = True
if success_daily is False:
print(
'[create_schema] DB initialization failed upon creating the table \'daily\'.'
)
success = success_settings and success_daily
if success:
success = await try_set_schema_version('1.0.0.0')
return success
async def update_schema_v_1_2_2_0() -> bool:
query_lines = []
rename_columns = {'channelid': 'dailychannelid', 'canpost': 'dailycanpost'}
column_definitions = [('guildid', 'TEXT', True, True),
('dailychannelid', 'TEXT', False, False),
('dailycanpost', 'BOOLEAN', False, False),
('dailylatestmessageid', 'TEXT', False, False),
('usepagination', 'BOOLEAN', False, False),
('prefix', 'TEXT', False, False)]
schema_version = await get_schema_version()
if schema_version:
compare_1220 = util.compare_versions(schema_version, '1.2.2.0')
compare_1000 = util.compare_versions(schema_version, '1.0.0.0')
if compare_1220 <= 0:
return True
elif compare_1000 > 0:
return False
print(
f'[update_schema_v_1_2_2_0] Updating database schema from v1.0.0.0 to v1.2.2.0'
)
query = 'ALTER TABLE IF EXISTS daily RENAME TO serversettings'
try:
success = await try_execute(query, raise_db_error=True)
except Exception as error:
success = False
print_db_query_error('update_schema_v_1_2_2_0', query, None, error)
if success:
column_names = await get_column_names('serversettings')
column_names = [column_name.lower() for column_name in column_names]
for name_from, name_to in rename_columns.items():
if name_from in column_names:
query_lines.append(
f'ALTER TABLE IF EXISTS serversettings RENAME COLUMN {name_from} TO {name_to};'
)
for (column_name, column_type, column_is_primary,
column_not_null) in column_definitions:
if column_name in rename_columns.values(
) or column_name in column_names:
query_lines.append(
f'ALTER TABLE IF EXISTS serversettings ALTER COLUMN {column_name} TYPE {column_type};'
)
if column_not_null:
not_null_toggle = 'SET'
else:
not_null_toggle = 'DROP'
query_lines.append(
f'ALTER TABLE IF EXISTS serversettings ALTER COLUMN {column_name} {not_null_toggle} NOT NULL;'
)
query = '\n'.join(query_lines)
if query:
success = await try_execute(query)
else:
success = True
if success:
query_lines = []
column_names = await get_column_names('serversettings')
column_names = [
column_name.lower() for column_name in column_names
]
for (column_name, column_type, column_is_primary,
column_not_null) in column_definitions:
if column_name not in column_names:
query_lines.append(
f'ALTER TABLE IF EXISTS serversettings ADD COLUMN IF NOT EXISTS {util.db_get_column_definition(column_name, column_type, column_is_primary, column_not_null)};'
)
query = '\n'.join(query_lines)
if query:
success = await try_execute(query)
else:
success = True
if success:
success = await try_set_schema_version('1.2.2.0')
return success
def db_update_schema_v_1_2_2_0():
query_lines = []
rename_columns = {'channelid': 'dailychannelid', 'canpost': 'dailycanpost'}
column_definitions = [('guildid', 'TEXT', True, True),
('dailychannelid', 'TEXT', False, False),
('dailycanpost', 'BOOLEAN', False, False),
('dailylatestmessageid', 'TEXT', False, False),
('usepagination', 'BOOLEAN', False, False),
('prefix', 'TEXT', False, False)]
schema_version = db_get_schema_version()
if schema_version and util.compare_versions(schema_version,
'1.2.2.0') <= 0:
return True
db_try_execute('ALTER TABLE IF EXISTS daily RENAME TO serversettings')
column_names = db_get_column_names('serversettings')
column_names = [column_name.lower() for column_name in column_names]
for name_from, name_to in rename_columns.items():
if name_from in column_names:
query_lines.append(
f'ALTER TABLE IF EXISTS serversettings RENAME COLUMN {name_from} TO {name_to};'
)
for (column_name, column_type, column_is_primary,
column_not_null) in column_definitions:
if column_name in rename_columns.values(
) or column_name in column_names:
query_lines.append(
f'ALTER TABLE IF EXISTS serversettings ALTER COLUMN {column_name} TYPE {column_type};'
)
if column_not_null:
not_null_toggle = 'SET'
else:
not_null_toggle = 'DROP'
query_lines.append(
f'ALTER TABLE IF EXISTS serversettings ALTER COLUMN {column_name} {not_null_toggle} NOT NULL;'
)
query = '\n'.join(query_lines)
if query:
success = db_try_execute(query)
else:
success = True
if success:
query_lines = []
column_names = db_get_column_names('serversettings')
column_names = [column_name.lower() for column_name in column_names]
for (column_name, column_type, column_is_primary,
column_not_null) in column_definitions:
if column_name not in column_names:
query_lines.append(
f'ALTER TABLE IF EXISTS serversettings ADD COLUMN IF NOT EXISTS {util.db_get_column_definition(column_name, column_type, column_is_primary, column_not_null)};'
)
query = '\n'.join(query_lines)
if query:
success = db_try_execute(query)
else:
success = True
if success:
success = db_try_set_schema_version('1.2.2.0')
return success
