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