async def db_get_autodaily_settings(guild_id: int = None, can_post: bool = None, only_guild_ids: bool = False, no_post_yet: bool = False) -> list: wheres = ['(dailychannelid IS NOT NULL or dailynotifyid IS NOT NULL)'] if guild_id is not None: wheres.append( util.db_get_where_string(_COLUMN_NAME_GUILD_ID, column_value=guild_id)) if can_post is not None: wheres.append( util.db_get_where_string(_COLUMN_NAME_DAILY_CAN_POST, column_value=can_post)) if no_post_yet is True: wheres.append( util.db_get_where_string( _COLUMN_NAME_DAILY_LATEST_MESSAGE_CREATED_AT, column_value=None)) if only_guild_ids: setting_names = [_COLUMN_NAME_GUILD_ID] else: setting_names = [ _COLUMN_NAME_GUILD_ID, _COLUMN_NAME_DAILY_CHANNEL_ID, _COLUMN_NAME_DAILY_CAN_POST, _COLUMN_NAME_DAILY_LATEST_MESSAGE_ID, _COLUMN_NAME_DAILY_DELETE_ON_CHANGE, _COLUMN_NAME_DAILY_NOTIFY_ID, _COLUMN_NAME_DAILY_NOTIFY_TYPE, _COLUMN_NAME_DAILY_LATEST_MESSAGE_CREATED_AT, _COLUMN_NAME_DAILY_LATEST_MESSAGE_MODIFIED_AT ] settings = await _db_get_server_settings(guild_id, setting_names=setting_names, additional_wheres=wheres) result = [] for setting in settings: if only_guild_ids: intermediate = [setting[0]] intermediate.extend([None] * (__AUTODAILY_SETTING_COUNT - 1)) result.append(tuple(intermediate)) else: result.append( (setting[0], setting[1], setting[2], setting[3], setting[4], setting[5], convert_to_autodaily_notify_type(setting[6]), setting[7], setting[8])) if not result: if guild_id: intermediate = [guild_id] intermediate.extend([None] * (__AUTODAILY_SETTING_COUNT - 1)) result.append(tuple(intermediate)) else: result.append(tuple([None] * __AUTODAILY_SETTING_COUNT)) return result
def db_get_autodaily_settings(guild_id: int = None, can_post: bool = None, only_guild_ids: bool = False, no_post_yet: bool = False) -> list: wheres = ['(dailychannelid IS NOT NULL or dailynotifyid IS NOT NULL)'] if guild_id is not None: wheres.append( util.db_get_where_string('guildid', util.db_convert_text(str(guild_id)))) if can_post is not None: wheres.append( util.db_get_where_string('dailycanpost', util.db_convert_boolean(can_post))) if no_post_yet is True: wheres.append( util.db_get_where_string('dailylatestmessagecreatedate', None)) if only_guild_ids: setting_names = ['guildid'] else: setting_names = [ 'guildid', 'dailychannelid', 'dailycanpost', 'dailylatestmessageid', 'dailydeleteonchange', 'dailynotifyid', 'dailynotifytype', 'dailylatestmessagecreatedate', 'dailylatestmessagemodifydate' ] settings = _db_get_server_settings(guild_id, setting_names=setting_names, additional_wheres=wheres) result = [] for setting in settings: if only_guild_ids: intermediate = [util.db_convert_to_int(setting[0])] intermediate.extend([None] * (__AUTODAILY_SETTING_COUNT - 1)) result.append(tuple(intermediate)) else: result.append((util.db_convert_to_int(setting[0]), util.db_convert_to_int(setting[1]), util.db_convert_to_boolean(setting[2]), util.db_convert_to_int(setting[3]), util.db_convert_to_boolean(setting[4]), util.db_convert_to_int(setting[5]), convert_to_autodaily_notify_type(setting[6]), setting[7], setting[8])) if not result: if guild_id: intermediate = [guild_id] intermediate.extend([None] * (__AUTODAILY_SETTING_COUNT - 1)) result.append(tuple(intermediate)) else: result.append(tuple([None] * __AUTODAILY_SETTING_COUNT)) return result
def db_try_delete_device(device: Device) -> bool: where = util.db_get_where_string('key', device.key, is_text_type=True) query = f'DELETE FROM devices WHERE {where}' success = core.db_try_execute(query) if success: DEVICES.remove_device_by_key(device.key) return success
def db_set_setting(setting_name: str, value: object, utc_now: datetime = None) -> bool: column_name = None if isinstance(value, bool): db_value = util.db_convert_boolean(value) column_name = 'settingboolean' elif isinstance(value, int): db_value = util.db_convert_to_int(value) column_name = 'settingint' elif isinstance(value, float): db_value = util.db_convert_to_float(value) column_name = 'settingfloat' elif isinstance(value, datetime): db_value = util.db_convert_to_datetime(value) column_name = 'settingtimestamptz' else: db_value = util.db_convert_text(value) column_name = 'settingtext' setting, modify_date = db_get_setting(setting_name) if utc_now is None: utc_now = util.get_utcnow() modify_date = util.db_convert_timestamp(utc_now) query = '' if setting is None and modify_date is None: query = f'INSERT INTO settings (settingname, modifydate, {column_name}) VALUES ({util.db_convert_text(setting_name)}, {modify_date}, {db_value})' elif setting != value: where_string = util.db_get_where_string('settingname', setting_name, is_text_type=True) query = f'UPDATE settings SET {column_name} = {db_value}, modifydate = {modify_date} WHERE {where_string}' success = not query or db_try_execute(query) return success
def db_get_setting(setting_name: str) -> (object, datetime): modify_date: datetime = None where_string = util.db_get_where_string('settingname', setting_name, is_text_type=True) query = f'SELECT * FROM settings WHERE {where_string}' try: results = db_fetchall(query) except: results = [] if results: result = results[0] modify_date = result[1] if result[2]: return (util.db_convert_to_boolean(result[2]), modify_date) elif result[3]: return (util.db_convert_to_float(result[3]), modify_date) elif result[4]: return (util.db_convert_to_int(result[4]), modify_date) elif result[5]: return (str(result[5]), modify_date) elif result[6]: return (result[6], modify_date) else: return (None, modify_date) else: return (None, modify_date)
def _db_update_server_setting(guild_id: int, settings: dict) -> bool: where = util.db_get_where_string('guildid', guild_id, is_text_type=True) set_string = ', '.join( [f'{key} = {value}' for key, value in settings.items()]) query = f'UPDATE serversettings SET {set_string} WHERE {where}' success = core.db_try_execute(query) return success
def _db_get_server_settings(guild_id: int = None, setting_names: list = None, additional_wheres: list = []) -> list: wheres = [] if guild_id is not None: wheres.append( util.db_get_where_string('guildid', guild_id, is_text_type=True)) if setting_names: setting_string = ', '.join(setting_names) else: setting_string = '*' if additional_wheres: wheres.extend(additional_wheres) where = util.db_get_where_and_string(wheres) if where: query = f'SELECT {setting_string} FROM serversettings WHERE {where}' else: query = f'SELECT {setting_string} FROM serversettings' rows = core.db_fetchall(query) if rows: return rows else: return []
def _db_try_update_device(device: Device) -> bool: key = util.db_convert_text(device.key) checksum = util.db_convert_text(device.checksum) can_login_until = util.db_convert_timestamp(device.can_login_until) where = util.db_get_where_string('key', device.key, is_text_type=True) query = f'UPDATE devices SET (key, checksum, loginuntil) = ({key}, {checksum}, {can_login_until}) WHERE {where}' success = core.db_try_execute(query) return success
def _db_get_device(device_key: str) -> Device: where = util.db_get_where_string('key', device_key, is_text_type=True) query = f'SELECT * FROM devices WHERE {where}' rows = core.db_fetchall(query) if rows: row = rows[0] result = Device(*row) else: result: Device = None return result
def db_get_schema_version() -> str: where_string = util.db_get_where_string('settingname', 'schema_version', is_text_type=True) query = f'SELECT * FROM settings WHERE {where_string}' try: results = db_fetchall(query) except: results = [] if results: return results[0][5] else: return ''
def db_try_set_schema_version(version: str) -> bool: prior_version = db_get_schema_version() utc_now = util.get_utcnow() modify_date_for_db = util.db_convert_timestamp(utc_now) version_for_db = util.db_convert_text(version) if prior_version == '': query = f'INSERT INTO settings (settingname, modifydate, settingtext) VALUES (\'schema_version\', {modify_date_for_db}, {version_for_db})' else: where_string = util.db_get_where_string('settingname', 'schema_version', is_text_type=True) query = f'UPDATE settings SET settingtext = {version_for_db}, modifydate = {modify_date_for_db} WHERE {where_string}' success = db_try_execute(query) return success
def db_delete_server_settings(guild_id: int) -> bool: where = util.db_get_where_string('guildid', guild_id, is_text_type=True) query = f'DELETE FROM serversettings WHERE {where}' success = core.db_try_execute(query) return success