def update_user_inbox_for_reminders(uid):
"""
Updates user's inbox with any reminders that should have arrived
@param uid: user id
@return: integer number of new expired reminders
"""
now = convert_datestruct_to_datetext(localtime())
reminder_status = CFG_WEBMESSAGE_STATUS_CODE['REMINDER']
new_status = CFG_WEBMESSAGE_STATUS_CODE['NEW']
expired_reminders = db.session.query(UserMsgMESSAGE.id_msgMESSAGE).\
join(UserMsgMESSAGE.message).\
filter(db.and_(
UserMsgMESSAGE.id_user_to == uid,
UserMsgMESSAGE.status.like(reminder_status),
MsgMESSAGE.received_date <= datetime.now()
# MsgMESSAGE.received_date<=db.func.current_timestamp()
)).all()
if len(expired_reminders):
filter = db.and_(
UserMsgMESSAGE.id_user_to == uid,
UserMsgMESSAGE.id_msgMESSAGE.in_(
[i for i, in expired_reminders]))
res = UserMsgMESSAGE.query.filter(filter).\
update({UserMsgMESSAGE.status: new_status},
synchronize_session='fetch')
return res
def create_message(uid_from,
users_to_str="",
groups_to_str="",
msg_subject="",
msg_body="",
msg_send_on_date=datetext_default):
"""
Creates a message in the msgMESSAGE table. Does NOT send the message.
This function is like a datagramPacket...
@param uid_from: uid of the sender (int)
@param users_to_str: a string, with nicknames separated by semicolons (';')
@param groups_to_str: a string with groupnames separated by semicolons
@param msg_subject: string containing the subject of the message
@param msg_body: string containing the body of the message
@param msg_send_on_date: date on which message must be sent. Has to be a
datetex format (i.e. YYYY-mm-dd HH:MM:SS)
@return: id of the created message
"""
now = convert_datestruct_to_datetext(localtime())
msg_id = run_sql("""INSERT INTO msgMESSAGE(id_user_from,
sent_to_user_nicks,
sent_to_group_names,
subject,
body,
sent_date,
received_date)
VALUES (%s,%s,%s,%s,%s,%s,%s)""",
(uid_from,
users_to_str,
groups_to_str,
msg_subject,
msg_body,
now,
msg_send_on_date))
return int(msg_id)
def update_user_inbox_for_reminders(uid):
"""
Updates user's inbox with any reminders that should have arrived
@param uid: user id
@return: integer number of new expired reminders
"""
now = convert_datestruct_to_datetext(localtime())
reminder_status = CFG_WEBMESSAGE_STATUS_CODE['REMINDER']
new_status = CFG_WEBMESSAGE_STATUS_CODE['NEW']
query1 = """SELECT m.id
FROM msgMESSAGE m,
user_msgMESSAGE um
WHERE um.id_user_to=%s AND
um.id_msgMESSAGE=m.id AND
m.received_date<=%s AND
um.status like binary %s
"""
params1 = (uid, now, reminder_status)
res_ids = run_sql(query1, params1)
out = len(res_ids)
if (out > 0):
query2 = """UPDATE user_msgMESSAGE
SET status=%s
WHERE id_user_to=%s AND ("""
query_params = [new_status, uid]
for msg_id in res_ids[0:-1]:
query2 += "id_msgMESSAGE=%s OR "
query_params.append(msg_id[0])
query2 += "id_msgMESSAGE=%s)"
query_params.append(res_ids[-1][0])
run_sql(query2, tuple(query_params))
return out
def clean_messages():
""" Cleans msgMESSAGE table"""
current_time = localtime()
seconds = mktime(current_time)
seconds -= CFG_WEBMESSAGE_DAYS_BEFORE_DELETE_ORPHANS * 86400
sql_date = convert_datestruct_to_datetext(localtime(seconds))
deleted_items = 0
# find id and email from every user who has got an email
query1 = """SELECT distinct(umsg.id_user_to),
user.email
FROM user_msgMESSAGE umsg
LEFT JOIN user ON
umsg.id_user_to=user.id"""
res1 = run_sql(query1)
# if there is no email, user has disappeared
users_deleted = map(lambda u: int(
u[0]), filter(lambda x: x[1] is None, res1))
# find ids from messages in user's inbox
query2 = """SELECT distinct(umsg.id_msgMESSAGE),
msg.id
FROM user_msgMESSAGE umsg
LEFT JOIN msgMESSAGE msg ON
umsg.id_msgMESSAGE=msg.id"""
res2 = run_sql(query2)
# if there is no id, message was deleted from table msgMESSAGE...
messages_deleted = map(lambda u: int(
u[0]), filter(lambda x: x[1] is None, res2))
def tuplize(el1, el2):
return str(el1) + ',' + str(el2)
if len(users_deleted) or len(messages_deleted):
# Suppress every referential error from user_msgMESSAGE
query3 = "DELETE FROM user_msgMESSAGE WHERE "
query_params = []
if len(users_deleted):
query3 += "id_user_to IN (%s)"
query_params.append(reduce(tuplize, users_deleted))
if len(messages_deleted):
query3 += ' OR '
if len(messages_deleted):
query3 += "id_msgMESSAGE IN (%s)"
query_params.append(reduce(tuplize, messages_deleted))
deleted_items = int(run_sql(query3, tuple(query_params)))
# find every message that is nobody's inbox
query4 = """SELECT msg.id
FROM msgMESSAGE msg
LEFT JOIN user_msgMESSAGE umsg
ON msg.id=umsg.id_msgMESSAGE
WHERE msg.sent_date<%s
GROUP BY umsg.id_msgMESSAGE
HAVING count(umsg.id_msgMESSAGE)=0
"""
res4 = map(lambda x: x[0], run_sql(query4, (sql_date, )))
if len(res4):
# delete these messages
query5 = "DELETE FROM msgMESSAGE WHERE "
query5 += "id IN (%s)"
deleted_items += int(run_sql(query5, (reduce(tuplize, res4), )))
return deleted_items
def log_sql_query(dbhost, sql, param=None):
"""Log SQL query into prefix/var/log/dbquery.log log file.
In order to enable logging of all SQL queries, please uncomment one line
in run_sql() above. Useful for fine-level debugging only!
"""
from invenio_utils.date import convert_datestruct_to_datetext
from invenio_utils.text import indent_text
date_of_log = convert_datestruct_to_datetext(time.localtime())
message = date_of_log + '-->\n'
message += indent_text('Host:\n' + indent_text(str(dbhost), 2, wrap=True),
2)
message += indent_text('Query:\n' + indent_text(str(sql), 2, wrap=True), 2)
message += indent_text('Params:\n' + indent_text(str(param), 2, wrap=True),
2)
message += '-----------------------------\n\n'
if has_app_context():
current_app.logger.info(message)
else:
print(message, file=sys.stderr)
def log_sql_query(dbhost, sql, param=None):
"""Log SQL query into prefix/var/log/dbquery.log log file.
In order to enable logging of all SQL queries, please uncomment one line
in run_sql() above. Useful for fine-level debugging only!
"""
from invenio_utils.date import convert_datestruct_to_datetext
from invenio_utils.text import indent_text
date_of_log = convert_datestruct_to_datetext(time.localtime())
message = date_of_log + '-->\n'
message += indent_text('Host:\n' +
indent_text(str(dbhost), 2, wrap=True), 2)
message += indent_text('Query:\n' + indent_text(str(sql), 2, wrap=True), 2)
message += indent_text('Params:\n' +
indent_text(str(param), 2, wrap=True), 2)
message += '-----------------------------\n\n'
if has_app_context():
current_app.logger.info(message)
else:
print(message, file=sys.stderr)
