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