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)