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 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 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 save_job_result(job_result): """Saves a job result @param job_result: JobResult object containing information about the job and its result @return: Returns the identifier of the job result """ #Save results in output directory _save_job_result_in_output_directory(job_result) # insert information about the job result in # expJOBRESULT table job_id = job_result.get_job().get_id() execution_time = convert_datestruct_to_datetext(job_result.get_execution_date_time()) status = job_result.get_status() status_message = job_result.get_status_message() job_result_id = run_sql("""INSERT INTO "expJOBRESULT"("id_expJOB", execution_time, status, status_message) VALUES(%s, %s, %s, %s)""", (job_id, execution_time, status, status_message )) query_results = job_result.get_query_results() for current_query_result in query_results: _insert_query_result(current_query_result, job_result_id) return job_result_id
def insert_new_group(uid, new_group_name, new_group_description, join_policy, login_method='INTERNAL'): """Create a new group and affiliate a user.""" query1 = """INSERT INTO usergroup (id, name, description, join_policy, login_method) VALUES (NULL,%s,%s,%s,%s) """ params1 = (new_group_name, new_group_description, join_policy, login_method) res1 = run_sql(query1, params1) date = convert_datestruct_to_datetext(localtime()) uid = int(uid) query2 = """INSERT INTO user_usergroup (id_user, id_usergroup, user_status, user_status_date) VALUES (%s,%s,'A',%s) """ params2 = (uid, res1, date) res2 = run_sql(query2, params2) return res1
def _insert_job(user_id, job): """Inserts new job into database. @param user_id: identifier of the user owning the job @param job: Job object containing information about the job @return: Returns the identifier of the job""" job_id = run_sql("""INSERT INTO "expJOB"(jobname, jobfreq, output_format, lastrun, output_directory) VALUES(%s, %s, %s, %s, %s)""", (job.get_name(), job.get_frequency(), job.get_output_format(), convert_datestruct_to_datetext(job.get_last_run()), job.get_output_directory() )) # create relation between job and user run_sql("""INSERT INTO "user_expJOB"(id_user, "id_expJOB") VALUES(%s, %s)""", (user_id, job_id )) return job_id
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 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 save_job_result(job_result): """Saves a job result @param job_result: JobResult object containing information about the job and its result @return: Returns the identifier of the job result """ #Save results in output directory _save_job_result_in_output_directory(job_result) # insert information about the job result in # expJOBRESULT table job_id = job_result.get_job().get_id() execution_time = convert_datestruct_to_datetext( job_result.get_execution_date_time()) status = job_result.get_status() status_message = job_result.get_status_message() job_result_id = run_sql( """INSERT INTO expJOBRESULT(id_expJOB, execution_time, status, status_message) VALUES(%s, %s, %s, %s)""", (job_id, execution_time, status, status_message)) query_results = job_result.get_query_results() for current_query_result in query_results: _insert_query_result(current_query_result, job_result_id) return job_result_id
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 perform_update_alert(alert_name, frequency, notification, id_basket, id_query, old_id_basket, uid, ln = CFG_SITE_LANG): """update alert settings into the database input: the name of the new alert; alert frequency: 'month', 'week' or 'day'; setting for e-mail notification: 'y' for yes, 'n' for no; new basket identifier: 'no' for no basket; new basket name for this alert; identifier of the query to be alerted old identifier of the basket associated to the alert output: confirmation message + the list of alerts Web page""" out = '' # sanity check if (None in (alert_name, frequency, notification, id_basket, id_query, old_id_basket, uid)): return out # load the right language _ = gettext_set_language(ln) # security check: if not check_user_can_add_alert(uid, id_query): raise AlertError(_("You do not have rights for this operation.")) # check the alert name is not empty if alert_name.strip() == "": raise AlertError(_("The alert name cannot be empty.")) # check if the alert can be created sql = """select alert_name from user_query_basket where id_user=%s and id_basket=%s and id_query=%s""" try: old_alert_name = run_sql(sql, (uid, old_id_basket, id_query))[0][0] except IndexError: # FIXME: I18N since this technique of the below raise message, # since this technique (detecting old alert IDs) is not nice # and should be replaced some day soon. raise AlertError("Unable to detect old alert name.") if old_alert_name.strip()!="" and old_alert_name != alert_name: check_alert_name( alert_name, uid, ln) if id_basket != old_id_basket: check_alert_is_unique( id_basket, id_query, uid, ln) # update a row into the alerts table: user_query_basket query = """UPDATE user_query_basket SET alert_name=%s,frequency=%s,notification=%s, date_creation=%s,date_lastrun='',id_basket=%s WHERE id_user=%s AND id_query=%s AND id_basket=%s""" params = (alert_name, frequency, notification, convert_datestruct_to_datetext(time.localtime()), id_basket, uid, id_query, old_id_basket) run_sql(query, params) out += _("The alert %(x_name)s has been successfully updated.", x_name=("<b>" + cgi.escape(alert_name) + "</b>",)) out += "<br /><br />\n" + perform_list_alerts(uid, ln=ln) return out
def insert_new_member(uid, grpID, status): """Insert new member.""" query = """INSERT INTO user_usergroup (id_user, id_usergroup, user_status, user_status_date) VALUES (%s,%s,%s,%s) """ date = convert_datestruct_to_datetext(localtime()) res = run_sql(query, (uid, grpID, status, date)) return res
def add_pending_member(grpID, member_id, user_status): """Change user status: Pending member becomes normal member""" date = convert_datestruct_to_datetext(localtime()) res = run_sql("""UPDATE user_usergroup SET user_status = %s, user_status_date = %s WHERE id_usergroup = %s AND id_user = %s""", (user_status, date, grpID, member_id)) return res
def clean_sessions(): """ Deletes expired sessions only. """ if op.has_table('session') and not CFG_INSPIRE_SITE: deleted_sessions = 0 timelimit = convert_datestruct_to_datetext(time.gmtime()) write_message("Deleting expired sessions since %s" % (timelimit, )) query = "DELETE FROM session WHERE session_expiry < %s" write_message(query % (timelimit, ), verbose=9) deleted_sessions += run_sql(query, (timelimit, )) write_message("Deleted %d sessions" % (deleted_sessions, ))
def clean_sessions(): """ Deletes expired sessions only. """ if op.has_table('session') and not CFG_INSPIRE_SITE: deleted_sessions = 0 timelimit = convert_datestruct_to_datetext(time.gmtime()) write_message("Deleting expired sessions since %s" % (timelimit,)) query = "DELETE FROM session WHERE session_expiry < %s" write_message(query % (timelimit,), verbose=9) deleted_sessions += run_sql(query, (timelimit,)) write_message("Deleted %d sessions" % (deleted_sessions,))
def perform_add_alert(alert_name, frequency, notification, id_basket, id_query, uid, ln=CFG_SITE_LANG): """add an alert to the database input: the name of the new alert; alert frequency: 'month', 'week' or 'day'; setting for e-mail notification: 'y' for yes, 'n' for no; basket identifier: 'no' for no basket; new basket name for this alert; identifier of the query to be alerted output: confirmation message + the list of alerts Web page""" # sanity check if (None in (alert_name, frequency, notification, id_basket, id_query, uid)): return '' # load the right language _ = gettext_set_language(ln) # security check: if not check_user_can_add_alert(uid, id_query): raise AlertError(_("You do not have rights for this operation.")) # check the alert name is not empty alert_name = alert_name.strip() if alert_name == "": raise AlertError(_("The alert name cannot be empty.")) # check if the alert can be created check_alert_name(alert_name, uid, ln) check_alert_is_unique(id_basket, id_query, uid, ln) if id_basket != 0 and not check_user_owns_baskets(uid, id_basket): raise AlertError(_("You are not the owner of this basket.")) # add a row to the alerts table: user_query_basket query = """INSERT INTO user_query_basket (id_user, id_query, id_basket, frequency, date_creation, date_lastrun, alert_name, notification) VALUES (%s,%s,%s,%s,%s,'',%s,%s)""" params = (uid, id_query, id_basket, frequency, convert_datestruct_to_datetext(time.localtime()), alert_name, notification) run_sql(query, params) out = _("The alert %(x_name)s has been added to your profile.", x_name='<b>' + cgi.escape(alert_name) + '</b>') # out %= '<b>' + cgi.escape(alert_name) + '</b>' out += perform_list_alerts(uid, ln=ln) return out
def save_in_storage(self, sid, session_object, timeout, uid): session_key = sid session_expiry = time.time() + timeout + CFG_WEBSESSION_ONE_DAY session_expiry = convert_datestruct_to_datetext(time.gmtime(session_expiry)) run_sql("""INSERT INTO session( session_key, session_expiry, session_object, uid ) VALUES (%s, %s, %s, %s) ON DUPLICATE KEY UPDATE session_expiry=%s, session_object=%s, uid=%s """, (session_key, session_expiry, session_object, uid, session_expiry, session_object, uid))
def _update_job(job): """Updates data about existing job in the database. @param job: Object containing information about the job. """ run_sql( """UPDATE expJOB SET jobname = %s, jobfreq = %s, output_format = %s, lastrun = %s, output_directory = %s WHERE id=%s""", (job.get_name(), job.get_frequency(), job.get_output_format(), convert_datestruct_to_datetext( job.get_last_run()), job.get_output_directory(), job.get_id())) return job.get_id()
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)
def _update_job(job): """Updates data about existing job in the database. @param job: Object containing information about the job. """ run_sql("""UPDATE "expJOB" SET jobname = %s, jobfreq = %s, output_format = %s, lastrun = %s, output_directory = %s WHERE id=%s""", (job.get_name(), job.get_frequency(), job.get_output_format(), convert_datestruct_to_datetext(job.get_last_run()), job.get_output_directory(), job.get_id() )) return job.get_id()
def perform_add_alert(alert_name, frequency, notification, id_basket, id_query, uid, ln = CFG_SITE_LANG): """add an alert to the database input: the name of the new alert; alert frequency: 'month', 'week' or 'day'; setting for e-mail notification: 'y' for yes, 'n' for no; basket identifier: 'no' for no basket; new basket name for this alert; identifier of the query to be alerted output: confirmation message + the list of alerts Web page""" # sanity check if (None in (alert_name, frequency, notification, id_basket, id_query, uid)): return '' # load the right language _ = gettext_set_language(ln) # security check: if not check_user_can_add_alert(uid, id_query): raise AlertError(_("You do not have rights for this operation.")) # check the alert name is not empty alert_name = alert_name.strip() if alert_name == "": raise AlertError(_("The alert name cannot be empty.")) # check if the alert can be created check_alert_name(alert_name, uid, ln) check_alert_is_unique(id_basket, id_query, uid, ln) if id_basket != 0 and not check_user_owns_baskets(uid, id_basket): raise AlertError( _("You are not the owner of this basket.") ) # add a row to the alerts table: user_query_basket query = """INSERT INTO user_query_basket (id_user, id_query, id_basket, frequency, date_creation, date_lastrun, alert_name, notification) VALUES (%s,%s,%s,%s,%s,'',%s,%s)""" params = (uid, id_query, id_basket, frequency, convert_datestruct_to_datetext(time.localtime()), alert_name, notification) run_sql(query, params) out = _("The alert %(x_name)s has been added to your profile.", x_name='<b>' + cgi.escape(alert_name) + '</b>') # out %= '<b>' + cgi.escape(alert_name) + '</b>' out += perform_list_alerts(uid, ln=ln) return out
def _insert_job(user_id, job): """Inserts new job into database. @param user_id: identifier of the user owning the job @param job: Job object containing information about the job @return: Returns the identifier of the job""" job_id = run_sql( """INSERT INTO expJOB(jobname, jobfreq, output_format, lastrun, output_directory) VALUES(%s, %s, %s, %s, %s)""", (job.get_name(), job.get_frequency(), job.get_output_format(), convert_datestruct_to_datetext( job.get_last_run()), job.get_output_directory())) # create relation between job and user run_sql( """INSERT INTO user_expJOB(id_user, id_expJOB) VALUES(%s, %s)""", (user_id, job_id)) return job_id
def perform_update_alert(alert_name, frequency, notification, id_basket, id_query, old_id_basket, uid, ln=CFG_SITE_LANG): """update alert settings into the database input: the name of the new alert; alert frequency: 'month', 'week' or 'day'; setting for e-mail notification: 'y' for yes, 'n' for no; new basket identifier: 'no' for no basket; new basket name for this alert; identifier of the query to be alerted old identifier of the basket associated to the alert output: confirmation message + the list of alerts Web page""" out = '' # sanity check if (None in (alert_name, frequency, notification, id_basket, id_query, old_id_basket, uid)): return out # load the right language _ = gettext_set_language(ln) # security check: if not check_user_can_add_alert(uid, id_query): raise AlertError(_("You do not have rights for this operation.")) # check the alert name is not empty if alert_name.strip() == "": raise AlertError(_("The alert name cannot be empty.")) # check if the alert can be created sql = """select alert_name from user_query_basket where id_user=%s and id_basket=%s and id_query=%s""" try: old_alert_name = run_sql(sql, (uid, old_id_basket, id_query))[0][0] except IndexError: # FIXME: I18N since this technique of the below raise message, # since this technique (detecting old alert IDs) is not nice # and should be replaced some day soon. raise AlertError("Unable to detect old alert name.") if old_alert_name.strip() != "" and old_alert_name != alert_name: check_alert_name(alert_name, uid, ln) if id_basket != old_id_basket: check_alert_is_unique(id_basket, id_query, uid, ln) # update a row into the alerts table: user_query_basket query = """UPDATE user_query_basket SET alert_name=%s,frequency=%s,notification=%s, date_creation=%s,date_lastrun='',id_basket=%s WHERE id_user=%s AND id_query=%s AND id_basket=%s""" params = (alert_name, frequency, notification, convert_datestruct_to_datetext(time.localtime()), id_basket, uid, id_query, old_id_basket) run_sql(query, params) out += _("The alert %(x_name)s has been successfully updated.", x_name=("<b>" + cgi.escape(alert_name) + "</b>", )) out += "<br /><br />\n" + perform_list_alerts(uid, ln=ln) return out
def tmpl_display_jobs(self, jobs, language=CFG_SITE_LANG): """ Creates page for displaying of all the jobs. @param jobs: list of the jobs that have to be displayed @param language: language of the page """ _ = gettext_set_language(language) table_rows = "" for current_job in jobs: # convert last run date into text proper to be shown to the user datetext = convert_datestruct_to_datetext( current_job.get_last_run()) last_run = convert_datetext_to_dategui(datetext, language) # obtain text corresponding to the frequency of execution frequency = current_job.get_frequency() frequency_text = self._get_frequency_text(frequency) row = """<tr> <td><input type="checkbox" name="selected_jobs" value="%(job_id)s"></input></td> <td><a href="%(edit_job_url)s?id=%(job_id)s&ln=%(language)s">%(name)s</a></td> <td>%(frequency)s</td> <td>%(last_run)s</td> </tr>""" % self._html_escape_dictionary( { "edit_job_url": self._EDIT_JOB_URL, "job_id": current_job.get_id(), "name": current_job.get_name(), "frequency": frequency_text, "language": language, "last_run": last_run }) table_rows += row select_all_none_row = """ <tr><td colspan="4"> <small>%s</small><br><br> </td></tr>""" \ %(self._get_select_all_none_html("jobsForm", "selected_jobs", language)) table_rows += select_all_none_row buttons_row = """<tr> <td colspan="3"> <input type="Submit" name="run_button" value="%(label_run)s" class="formbutton"> <input type="Submit" name="delete_button" value="%(label_delete)s" class="formbutton"> </td> <td align="right"> <input type="Submit" name="new_button" value="%(label_new)s" class="formbutton"> </td> </tr>""" % { "label_run": _("Run"), "label_delete": _("Delete"), "label_new": _("New") } table_rows += buttons_row body = """ <form method="post" name="jobsForm"> <table class="spacedcells"> <th></th> <th>%(label_name)s</th> <th>%(label_frequency)s</th> <th>%(label_last_run)s</th> %(table_rows)s </table> </form> """ % { "table_rows": table_rows, "label_name": _("Name"), "label_frequency": _("Run"), "label_last_run": _("Last run") } return body
def edit_job(self, req, form): """Edits an existing job or creates a new one""" # Create an empty job and use its default values # to init missing parameters job = Job() argd = wash_urlargd( form, { "job_name": (str, job.get_name()), "output_directory": (str, job.get_output_directory()), "job_frequency": (int, job.get_frequency()), "output_format": (int, job.get_output_format()), "last_run": (str, convert_datestruct_to_datetext(job.get_last_run())), "id": (int, job.get_id()), "save_button": (str, ""), "cancel_button": (str, ""), "edit_queries_button": (str, "") }) language = argd["ln"] # load the right message language _ = gettext_set_language(language) self._check_user_credentials(req, language) user_id = self._get_user_id(req) job_id = argd["id"] job = Job(job_id=job_id, name=argd["job_name"], frequency=argd["job_frequency"], output_format=argd["output_format"], last_run=convert_datetext_to_datestruct(argd["last_run"]), output_directory=argd["output_directory"]) try: if argd["cancel_button"]: self._redirect_to_page(req, self._JOBS_URL, language) elif argd["save_button"]: perform_request_save_job(job=job, user_id=user_id, language=language) self._redirect_to_page(req, self._JOBS_URL, language) elif argd["edit_queries_button"]: result_job_id = perform_request_save_job(job=job, user_id=user_id, language=language) edit_queries_url = "%s?job_id=%s" % (self._JOB_QUERIES_URL, result_job_id) self._redirect_to_page(req, edit_queries_url, language) elif Job.ID_MISSING == job_id: title = _("New Export Job") body = perform_request_new_job(language=language) else: title = _("Edit Export Job") body = perform_request_edit_job(job_id=job_id, user_id=user_id, language=language) except AccessDeniedError: self._redirect_to_not_authorised_page(req, language) return page(title=title, metaheaderadd=get_css(), body=body, req=req, navmenuid="fieldexporter", titleprologue=get_navigation_menu(language), navtrail=self._NAVTRAIL_EXPORT, language=language)
def guest_user_garbage_collector(): """Session Garbage Collector program flow/tasks: 1: delete expired sessions 1b:delete guest users without session 2: delete queries not attached to any user 3: delete baskets not attached to any user 4: delete alerts not attached to any user 5: delete expired mailcookies 5b: delete expired not confirmed email address 6: delete expired roles memberships verbose - level of program output. 0 - nothing 1 - default 9 - max, debug""" # dictionary used to keep track of number of deleted entries delcount = {'session': 0, 'user': 0, 'user_query': 0, 'query': 0, 'mail_cookie': 0, 'email_addresses': 0, 'role_membership' : 0} write_message("CLEANING OF GUEST SESSIONS STARTED") # 1 - DELETE EXPIRED SESSIONS write_message("- deleting expired sessions") timelimit = convert_datestruct_to_datetext(time.gmtime()) write_message(" DELETE FROM session WHERE" " session_expiry < %s \n" % (timelimit,), verbose=9) delcount['session'] += run_sql("DELETE FROM session WHERE" " session_expiry < %s """, (timelimit,)) # 1b - DELETE GUEST USERS WITHOUT SESSION write_message("- deleting guest users without session") # get uids write_message(""" SELECT u.id\n FROM "user" AS u LEFT JOIN session AS s\n ON u.id = s.uid\n WHERE s.uid IS NULL AND u.email = ''""", verbose=9) result = run_sql("""SELECT u.id FROM "user" AS u LEFT JOIN session AS s ON u.id = s.uid WHERE s.uid IS NULL AND u.email = ''""") write_message(result, verbose=9) if result: # work on slices of result list in case of big result for i in range(0, len(result), CFG_MYSQL_ARGUMENTLIST_SIZE): # create string of uids uidstr = '' for (id_user,) in result[i:i + CFG_MYSQL_ARGUMENTLIST_SIZE]: if uidstr: uidstr += ',' uidstr += "%s" % (id_user,) # delete users write_message(""" DELETE FROM "user" WHERE""" " id IN (TRAVERSE LAST RESULT) AND email = '' \n", verbose=9) delcount['user'] += run_sql("""DELETE FROM "user" WHERE id IN (%s) AND email = '' """ % (uidstr,)) # 2 - DELETE QUERIES NOT ATTACHED TO ANY USER # first step, delete from user_query write_message("- deleting user_queries referencing" " non-existent users") # find user_queries referencing non-existent users users_with_queries = intbitset(run_sql("""SELECT DISTINCT id_user FROM user_query""")) existing_users = intbitset(run_sql("""SELECT id FROM "user" """)) users_with_queries_to_be_deleted = users_with_queries - existing_users write_message(" Users with queries: %s" % len(users_with_queries), verbose=9) write_message(" Existing users: %s" % len(existing_users), verbose=9) write_message(" Users with queries to be deleted: %s" % len(users_with_queries_to_be_deleted), verbose=9) # delete in user_query one by one write_message(" DELETE FROM user_query WHERE" " id_user = '******' \n", verbose=9) for id_user in users_with_queries_to_be_deleted: delcount['user_query'] += run_sql("""DELETE FROM user_query WHERE id_user=%s""", (id_user, )) # delete the actual queries write_message("- deleting queries not attached to any user") # select queries that must be deleted write_message(""" SELECT DISTINCT q.id\n FROM query AS q LEFT JOIN user_query AS uq\n ON uq.id_query = q.id\n WHERE uq.id_query IS NULL AND\n q.type <> 'p' """, verbose=9) result = run_sql("""SELECT DISTINCT q.id FROM query AS q LEFT JOIN user_query AS uq ON uq.id_query = q.id WHERE uq.id_query IS NULL AND q.type <> 'p'""") write_message(result, verbose=9) # delete queries one by one write_message(""" DELETE FROM query WHERE id = 'TRAVERSE LAST RESULT' \n""", verbose=9) for (id_user,) in result: delcount['query'] += run_sql("""DELETE FROM query WHERE id = %s""", (id_user,)) # 5 - delete expired mailcookies write_message("""mail_cookie_gc()""", verbose=9) delcount['mail_cookie'] = mail_cookie_gc() ## 5b - delete expired not confirmed email address write_message("""DELETE FROM "user" WHERE note='2' AND NOW()>ADDTIME(last_login, '%s 0:0:0')""" % CFG_WEBSESSION_NOT_CONFIRMED_EMAIL_ADDRESS_EXPIRE_IN_DAYS, verbose=9) delcount['email_addresses'] = run_sql("""DELETE FROM "user" WHERE note='2' AND NOW()>ADDTIME(last_login, '%s 0:0:0')""", (CFG_WEBSESSION_NOT_CONFIRMED_EMAIL_ADDRESS_EXPIRE_IN_DAYS,)) # 6 - delete expired roles memberships write_message("""DELETE FROM "user_accROLE" WHERE expiration<NOW()""", verbose=9) delcount['role_membership'] = run_sql("""DELETE FROM "user_accROLE" WHERE expiration<NOW()""") # print STATISTICS write_message("""- statistics about deleted data: """) write_message(""" %7s sessions.""" % (delcount['session'],)) write_message(""" %7s users.""" % (delcount['user'],)) write_message(""" %7s user_queries.""" % (delcount['user_query'],)) write_message(""" %7s queries.""" % (delcount['query'],)) write_message(""" %7s mail_cookies.""" % (delcount['mail_cookie'],)) write_message(""" %7s non confirmed email addresses.""" % delcount['email_addresses']) write_message(""" %7s role_memberships.""" % (delcount['role_membership'],)) write_message("""CLEANING OF GUEST SESSIONS FINISHED""")
def tmpl_edit_job(self, job, language = CFG_SITE_LANG): """ Creates page for editing of jobs. @param job: The job that will be edited @param language: language of the page """ _ = gettext_set_language(language) job_frequency = job.get_frequency() frequency_select_box_html = self._create_frequency_select_box("job_frequency", job_frequency, language) output_format_select_box_html = self._create_output_format_select_box(selected_value = job.get_output_format()) body = """ <form method="post"> <input type="Hidden" name="id" value="%(job_id)s"> <table> <tr> <td class = "label">%(name_label)s</td> <td colspan="2"><input type="text" name="job_name" class="textentry" value="%(job_name)s"></td> </tr> <tr> <td class = "label">%(frequency_label)s</td> <td colspan="2">%(frequency_select_box)s</td> </tr> <tr> <td class = "label">%(output_format_label)s</td> <td colspan="2">%(output_format_select_box)s</td> </tr> <tr> <td class = "label">%(start_label)s</td> <td colspan="2"><input type="text" name="last_run" class="textentry" value="%(job_last_run)s"></td> </tr> <tr> <td class = "label">%(output_directory_label)s</td> <td colspan="2"><input type="text" name="output_directory" class="textentry" value="%(output_directory)s"></td> </tr> <tr> <td></td> <td> <input type="Submit" name="save_button" value="%(save_label)s" class="formbutton"> <input type="Submit" name="cancel_button" value="%(cancel_label)s" class="formbutton"> </td> <td align="right"> <input type="Submit" name="edit_queries_button" value="%(edit_queries_label)s" class="formbutton"> </td> </tr> </table> </form> """ % { "name_label" : _("Name"), "frequency_label" : _("Frequency"), "output_format_label" : _("Output Format"), "start_label" : _("Start"), "output_directory_label" : _("Output Directory"), "save_label" : _("Save"), "cancel_label" : _("Cancel"), "edit_queries_label" : _("Edit Queries"), "job_id" : self._html_escape_content(job.get_id()), "job_name" : self._html_escape_content(job.get_name()), "frequency_select_box" : frequency_select_box_html, "output_format_select_box" : output_format_select_box_html, "job_last_run" : convert_datestruct_to_datetext(job.get_last_run()), "output_directory" : self._html_escape_content(job.get_output_directory()) } return body
def tmpl_display_jobs(self, jobs, language = CFG_SITE_LANG): """ Creates page for displaying of all the jobs. @param jobs: list of the jobs that have to be displayed @param language: language of the page """ _ = gettext_set_language(language) table_rows = "" for current_job in jobs: # convert last run date into text proper to be shown to the user datetext = convert_datestruct_to_datetext(current_job.get_last_run()) last_run = convert_datetext_to_dategui(datetext, language) # obtain text corresponding to the frequency of execution frequency = current_job.get_frequency() frequency_text = self._get_frequency_text(frequency) row = """<tr> <td><input type="checkbox" name="selected_jobs" value="%(job_id)s"></input></td> <td><a href="%(edit_job_url)s?id=%(job_id)s&ln=%(language)s">%(name)s</a></td> <td>%(frequency)s</td> <td>%(last_run)s</td> </tr>""" % self._html_escape_dictionary({ "edit_job_url" : self._EDIT_JOB_URL, "job_id" : current_job.get_id(), "name" : current_job.get_name(), "frequency" : frequency_text, "language" : language, "last_run" : last_run }) table_rows += row select_all_none_row = """ <tr><td colspan="4"> <small>%s</small><br><br> </td></tr>""" \ %(self._get_select_all_none_html("jobsForm", "selected_jobs", language)) table_rows += select_all_none_row buttons_row = """<tr> <td colspan="3"> <input type="Submit" name="run_button" value="%(label_run)s" class="formbutton"> <input type="Submit" name="delete_button" value="%(label_delete)s" class="formbutton"> </td> <td align="right"> <input type="Submit" name="new_button" value="%(label_new)s" class="formbutton"> </td> </tr>""" % { "label_run" : _("Run"), "label_delete" : _("Delete"), "label_new" : _("New") } table_rows += buttons_row body = """ <form method="post" name="jobsForm"> <table class="spacedcells"> <th></th> <th>%(label_name)s</th> <th>%(label_frequency)s</th> <th>%(label_last_run)s</th> %(table_rows)s </table> </form> """ % { "table_rows" : table_rows, "label_name" : _("Name"), "label_frequency" : _("Run"), "label_last_run" : _("Last run") } return body
def transform(webdoc_source, verbose=0, req=None, languages=CFG_SITE_LANGS): """ Transform a WebDoc into html This is made through a serie of transformations, mainly substitutions. Parameters: - webdoc_source : *string* the WebDoc input to transform to HTML """ parameters = {} # Will store values for specified parameters, such # as 'Title' for <!-- WebDoc-Page-Title: Title --> def get_param_and_remove(match): """ Analyses 'match', get the parameter and return empty string to remove it. Called by substitution in 'transform(...)', used to collection parameters such as <!-- WebDoc-Page-Title: Title --> @param match: a match object corresponding to the special tag that must be interpreted """ tag = match.group("tag") value = match.group("value") parameters[tag] = value return "" def translate(match): """ Translate matching values """ word = match.group("word") translated_word = _(word) return translated_word # 1 step ## First filter, used to remove comments ## and <protect> tags uncommented_webdoc = "" for line in webdoc_source.splitlines(True): if not line.strip().startswith("#"): uncommented_webdoc += line webdoc_source = uncommented_webdoc.replace("<protect>", "") webdoc_source = webdoc_source.replace("</protect>", "") html_texts = {} # Language dependent filters for ln in languages: _ = gettext_set_language(ln) # Check if translation is really needed ## Just a quick check. Might trigger false negative, but it is ## ok. if ( ln != CFG_SITE_LANG and translation_pattern.search(webdoc_source) is None and pattern_lang_link_current.search(webdoc_source) is None and pattern_lang_current.search(webdoc_source) is None and "<%s>" % ln not in webdoc_source and ("_(") not in webdoc_source ): continue # 2 step ## Filter used to translate string in _(..)_ localized_webdoc = translation_pattern.sub(translate, webdoc_source) # 3 step ## Print current language 'en', 'fr', .. instead of ## <lang:current /> tags and '?ln=en', '?ln=fr', .. instead of ## <lang:link /> localized_webdoc = pattern_lang_link_current.sub("?ln=" + ln, localized_webdoc) localized_webdoc = pattern_lang_current.sub(ln, localized_webdoc) # 4 step ## Filter out languages localized_webdoc = filter_languages(localized_webdoc, ln, defined_tags) # 5 Step ## Replace defined tags with their value from config file ## Eg. replace <CFG_SITE_URL> with 'http://cds.cern.ch/': for defined_tag, value in iteritems(defined_tags): if defined_tag.upper() == "<CFG_SITE_NAME_INTL>": localized_webdoc = localized_webdoc.replace(defined_tag, value.get(ln, value["en"])) else: localized_webdoc = localized_webdoc.replace(defined_tag, value) # 6 step ## Get the parameters defined in HTML comments, like ## <!-- WebDoc-Page-Title: My Title --> localized_body = localized_webdoc for tag, pattern in iteritems(pattern_tags): localized_body = pattern.sub(get_param_and_remove, localized_body) out = localized_body # Pre-process date last_updated = parameters.get("WebDoc-Page-Revision", "") last_updated = convert_datecvs_to_datestruct(last_updated) last_updated = convert_datestruct_to_datetext(last_updated) html_texts[ln] = ( ln, out, parameters.get("WebDoc-Page-Title"), parameters.get("WebDoc-Page-Keywords"), parameters.get("WebDoc-Page-Navtrail"), last_updated, parameters.get("WebDoc-Page-Description"), ) # Remove duplicates filtered_html_texts = [] if CFG_SITE_LANG in html_texts: filtered_html_texts = [ ( html_text[0], (html_text[1] != html_texts[CFG_SITE_LANG][1] and html_text[1]) or None, (html_text[2] != html_texts[CFG_SITE_LANG][2] and html_text[2]) or None, (html_text[3] != html_texts[CFG_SITE_LANG][3] and html_text[3]) or None, (html_text[4] != html_texts[CFG_SITE_LANG][4] and html_text[4]) or None, (html_text[5] != html_texts[CFG_SITE_LANG][5] and html_text[5]) or None, (html_text[6] != html_texts[CFG_SITE_LANG][6] and html_text[6]) or None, ) for html_text in html_texts.values() if html_text[0] != CFG_SITE_LANG ] filtered_html_texts.append(html_texts[CFG_SITE_LANG]) else: filtered_html_texts = html_texts.values() return filtered_html_texts
def transform(webdoc_source, verbose=0, req=None, languages=CFG_SITE_LANGS): """ Transform a WebDoc into html This is made through a serie of transformations, mainly substitutions. Parameters: - webdoc_source : *string* the WebDoc input to transform to HTML """ parameters = {} # Will store values for specified parameters, such # as 'Title' for <!-- WebDoc-Page-Title: Title --> def get_param_and_remove(match): """ Analyses 'match', get the parameter and return empty string to remove it. Called by substitution in 'transform(...)', used to collection parameters such as <!-- WebDoc-Page-Title: Title --> @param match: a match object corresponding to the special tag that must be interpreted """ tag = match.group("tag") value = match.group("value") parameters[tag] = value return '' def translate(match): """ Translate matching values """ word = match.group("word") translated_word = _(word) return translated_word # 1 step ## First filter, used to remove comments ## and <protect> tags uncommented_webdoc = '' for line in webdoc_source.splitlines(True): if not line.strip().startswith('#'): uncommented_webdoc += line webdoc_source = uncommented_webdoc.replace('<protect>', '') webdoc_source = webdoc_source.replace('</protect>', '') html_texts = {} # Language dependent filters for ln in languages: _ = gettext_set_language(ln) # Check if translation is really needed ## Just a quick check. Might trigger false negative, but it is ## ok. if ln != CFG_SITE_LANG and \ translation_pattern.search(webdoc_source) is None and \ pattern_lang_link_current.search(webdoc_source) is None and \ pattern_lang_current.search(webdoc_source) is None and \ '<%s>' % ln not in webdoc_source and \ ('_(') not in webdoc_source: continue # 2 step ## Filter used to translate string in _(..)_ localized_webdoc = translation_pattern.sub(translate, webdoc_source) # 3 step ## Print current language 'en', 'fr', .. instead of ## <lang:current /> tags and '?ln=en', '?ln=fr', .. instead of ## <lang:link /> localized_webdoc = pattern_lang_link_current.sub( '?ln=' + ln, localized_webdoc) localized_webdoc = pattern_lang_current.sub(ln, localized_webdoc) # 4 step ## Filter out languages localized_webdoc = filter_languages(localized_webdoc, ln, defined_tags) # 5 Step ## Replace defined tags with their value from config file ## Eg. replace <CFG_SITE_URL> with 'http://cds.cern.ch/': for defined_tag, value in iteritems(defined_tags): if defined_tag.upper() == '<CFG_SITE_NAME_INTL>': localized_webdoc = localized_webdoc.replace(defined_tag, \ value.get(ln, value['en'])) else: localized_webdoc = localized_webdoc.replace(defined_tag, value) # 6 step ## Get the parameters defined in HTML comments, like ## <!-- WebDoc-Page-Title: My Title --> localized_body = localized_webdoc for tag, pattern in iteritems(pattern_tags): localized_body = pattern.sub(get_param_and_remove, localized_body) out = localized_body # Pre-process date last_updated = parameters.get('WebDoc-Page-Revision', '') last_updated = convert_datecvs_to_datestruct(last_updated) last_updated = convert_datestruct_to_datetext(last_updated) html_texts[ln] = (ln, out, parameters.get('WebDoc-Page-Title'), parameters.get('WebDoc-Page-Keywords'), parameters.get('WebDoc-Page-Navtrail'), last_updated, parameters.get('WebDoc-Page-Description')) # Remove duplicates filtered_html_texts = [] if CFG_SITE_LANG in html_texts: filtered_html_texts = [(html_text[0], \ (html_text[1] != html_texts[CFG_SITE_LANG][1] and html_text[1]) or None, \ (html_text[2] != html_texts[CFG_SITE_LANG][2] and html_text[2]) or None, \ (html_text[3] != html_texts[CFG_SITE_LANG][3] and html_text[3]) or None, \ (html_text[4] != html_texts[CFG_SITE_LANG][4] and html_text[4]) or None, \ (html_text[5] != html_texts[CFG_SITE_LANG][5] and html_text[5]) or None, \ (html_text[6] != html_texts[CFG_SITE_LANG][6] and html_text[6]) or None) for html_text in html_texts.values() \ if html_text[0] != CFG_SITE_LANG] filtered_html_texts.append(html_texts[CFG_SITE_LANG]) else: filtered_html_texts = html_texts.values() return filtered_html_texts
def guest_user_garbage_collector(): """Session Garbage Collector program flow/tasks: 1: delete expired sessions 1b:delete guest users without session 2: delete queries not attached to any user 3: delete baskets not attached to any user 4: delete alerts not attached to any user 5: delete expired mailcookies 5b: delete expired not confirmed email address 6: delete expired roles memberships verbose - level of program output. 0 - nothing 1 - default 9 - max, debug""" # dictionary used to keep track of number of deleted entries delcount = { 'session': 0, 'user': 0, 'user_query': 0, 'query': 0, 'mail_cookie': 0, 'email_addresses': 0, 'role_membership': 0 } write_message("CLEANING OF GUEST SESSIONS STARTED") # 1 - DELETE EXPIRED SESSIONS write_message("- deleting expired sessions") timelimit = convert_datestruct_to_datetext(time.gmtime()) write_message(" DELETE FROM session WHERE" " session_expiry < %s \n" % (timelimit, ), verbose=9) delcount['session'] += run_sql( "DELETE FROM session WHERE" " session_expiry < %s " "", (timelimit, )) # 1b - DELETE GUEST USERS WITHOUT SESSION write_message("- deleting guest users without session") # get uids write_message( """ SELECT u.id\n FROM "user" AS u LEFT JOIN session AS s\n ON u.id = s.uid\n WHERE s.uid IS NULL AND u.email = ''""", verbose=9) result = run_sql("""SELECT u.id FROM "user" AS u LEFT JOIN session AS s ON u.id = s.uid WHERE s.uid IS NULL AND u.email = ''""") write_message(result, verbose=9) if result: # work on slices of result list in case of big result for i in range(0, len(result), CFG_MYSQL_ARGUMENTLIST_SIZE): # create string of uids uidstr = '' for (id_user, ) in result[i:i + CFG_MYSQL_ARGUMENTLIST_SIZE]: if uidstr: uidstr += ',' uidstr += "%s" % (id_user, ) # delete users write_message( """ DELETE FROM "user" WHERE""" " id IN (TRAVERSE LAST RESULT) AND email = '' \n", verbose=9) delcount['user'] += run_sql("""DELETE FROM "user" WHERE id IN (%s) AND email = '' """ % (uidstr, )) # 2 - DELETE QUERIES NOT ATTACHED TO ANY USER # first step, delete from user_query write_message("- deleting user_queries referencing" " non-existent users") # find user_queries referencing non-existent users users_with_queries = intbitset( run_sql("""SELECT DISTINCT id_user FROM user_query""")) existing_users = intbitset(run_sql("""SELECT id FROM "user" """)) users_with_queries_to_be_deleted = users_with_queries - existing_users write_message(" Users with queries: %s" % len(users_with_queries), verbose=9) write_message(" Existing users: %s" % len(existing_users), verbose=9) write_message(" Users with queries to be deleted: %s" % len(users_with_queries_to_be_deleted), verbose=9) # delete in user_query one by one write_message( " DELETE FROM user_query WHERE" " id_user = '******' \n", verbose=9) for id_user in users_with_queries_to_be_deleted: delcount['user_query'] += run_sql( """DELETE FROM user_query WHERE id_user=%s""", (id_user, )) # delete the actual queries write_message("- deleting queries not attached to any user") # select queries that must be deleted write_message( """ SELECT DISTINCT q.id\n FROM query AS q LEFT JOIN user_query AS uq\n ON uq.id_query = q.id\n WHERE uq.id_query IS NULL AND\n q.type <> 'p' """, verbose=9) result = run_sql("""SELECT DISTINCT q.id FROM query AS q LEFT JOIN user_query AS uq ON uq.id_query = q.id WHERE uq.id_query IS NULL AND q.type <> 'p'""") write_message(result, verbose=9) # delete queries one by one write_message( """ DELETE FROM query WHERE id = 'TRAVERSE LAST RESULT' \n""", verbose=9) for (id_user, ) in result: delcount['query'] += run_sql("""DELETE FROM query WHERE id = %s""", (id_user, )) # 5 - delete expired mailcookies write_message("""mail_cookie_gc()""", verbose=9) delcount['mail_cookie'] = mail_cookie_gc() ## 5b - delete expired not confirmed email address write_message( """DELETE FROM "user" WHERE note='2' AND NOW()>ADDTIME(last_login, '%s 0:0:0')""" % CFG_WEBSESSION_NOT_CONFIRMED_EMAIL_ADDRESS_EXPIRE_IN_DAYS, verbose=9) delcount['email_addresses'] = run_sql( """DELETE FROM "user" WHERE note='2' AND NOW()>ADDTIME(last_login, '%s 0:0:0')""", (CFG_WEBSESSION_NOT_CONFIRMED_EMAIL_ADDRESS_EXPIRE_IN_DAYS, )) # 6 - delete expired roles memberships write_message("""DELETE FROM "user_accROLE" WHERE expiration<NOW()""", verbose=9) delcount['role_membership'] = run_sql( """DELETE FROM "user_accROLE" WHERE expiration<NOW()""") # print STATISTICS write_message("""- statistics about deleted data: """) write_message(""" %7s sessions.""" % (delcount['session'], )) write_message(""" %7s users.""" % (delcount['user'], )) write_message(""" %7s user_queries.""" % (delcount['user_query'], )) write_message(""" %7s queries.""" % (delcount['query'], )) write_message(""" %7s mail_cookies.""" % (delcount['mail_cookie'], )) write_message(""" %7s non confirmed email addresses.""" % delcount['email_addresses']) write_message(""" %7s role_memberships.""" % (delcount['role_membership'], )) write_message("""CLEANING OF GUEST SESSIONS FINISHED""")
def tmpl_edit_job(self, job, language=CFG_SITE_LANG): """ Creates page for editing of jobs. @param job: The job that will be edited @param language: language of the page """ _ = gettext_set_language(language) job_frequency = job.get_frequency() frequency_select_box_html = self._create_frequency_select_box( "job_frequency", job_frequency, language) output_format_select_box_html = self._create_output_format_select_box( selected_value=job.get_output_format()) body = """ <form method="post"> <input type="Hidden" name="id" value="%(job_id)s"> <table> <tr> <td class = "label">%(name_label)s</td> <td colspan="2"><input type="text" name="job_name" class="textentry" value="%(job_name)s"></td> </tr> <tr> <td class = "label">%(frequency_label)s</td> <td colspan="2">%(frequency_select_box)s</td> </tr> <tr> <td class = "label">%(output_format_label)s</td> <td colspan="2">%(output_format_select_box)s</td> </tr> <tr> <td class = "label">%(start_label)s</td> <td colspan="2"><input type="text" name="last_run" class="textentry" value="%(job_last_run)s"></td> </tr> <tr> <td class = "label">%(output_directory_label)s</td> <td colspan="2"><input type="text" name="output_directory" class="textentry" value="%(output_directory)s"></td> </tr> <tr> <td></td> <td> <input type="Submit" name="save_button" value="%(save_label)s" class="formbutton"> <input type="Submit" name="cancel_button" value="%(cancel_label)s" class="formbutton"> </td> <td align="right"> <input type="Submit" name="edit_queries_button" value="%(edit_queries_label)s" class="formbutton"> </td> </tr> </table> </form> """ % { "name_label": _("Name"), "frequency_label": _("Frequency"), "output_format_label": _("Output Format"), "start_label": _("Start"), "output_directory_label": _("Output Directory"), "save_label": _("Save"), "cancel_label": _("Cancel"), "edit_queries_label": _("Edit Queries"), "job_id": self._html_escape_content(job.get_id()), "job_name": self._html_escape_content(job.get_name()), "frequency_select_box": frequency_select_box_html, "output_format_select_box": output_format_select_box_html, "job_last_run": convert_datestruct_to_datetext(job.get_last_run()), "output_directory": self._html_escape_content(job.get_output_directory()) } return body
def guest_user_garbage_collector(): """Session Garbage Collector program flow/tasks: 1: delete expired sessions 1b:delete guest users without session 2: delete queries not attached to any user 3: delete baskets not attached to any user 4: delete alerts not attached to any user 5: delete expired mailcookies 5b: delete expired not confirmed email address 6: delete expired roles memberships verbose - level of program output. 0 - nothing 1 - default 9 - max, debug""" # dictionary used to keep track of number of deleted entries delcount = {'session': 0, 'user': 0, 'user_query': 0, 'query': 0, 'bskBASKET': 0, 'user_bskBASKET': 0, 'bskREC': 0, 'bskRECORDCOMMENT': 0, 'bskEXTREC': 0, 'bskEXTFMT': 0, 'user_query_basket': 0, 'mail_cookie': 0, 'email_addresses': 0, 'role_membership' : 0} write_message("CLEANING OF GUEST SESSIONS STARTED") # 1 - DELETE EXPIRED SESSIONS write_message("- deleting expired sessions") timelimit = convert_datestruct_to_datetext(time.gmtime()) write_message(" DELETE FROM session WHERE" " session_expiry < %s \n" % (timelimit,), verbose=9) delcount['session'] += run_sql("DELETE FROM session WHERE" " session_expiry < %s """, (timelimit,)) # 1b - DELETE GUEST USERS WITHOUT SESSION write_message("- deleting guest users without session") # get uids write_message(""" SELECT u.id\n FROM user AS u LEFT JOIN session AS s\n ON u.id = s.uid\n WHERE s.uid IS NULL AND u.email = ''""", verbose=9) result = run_sql("""SELECT u.id FROM user AS u LEFT JOIN session AS s ON u.id = s.uid WHERE s.uid IS NULL AND u.email = ''""") write_message(result, verbose=9) if result: # work on slices of result list in case of big result for i in range(0, len(result), CFG_MYSQL_ARGUMENTLIST_SIZE): # create string of uids uidstr = '' for (id_user,) in result[i:i + CFG_MYSQL_ARGUMENTLIST_SIZE]: if uidstr: uidstr += ',' uidstr += "%s" % (id_user,) # delete users write_message(" DELETE FROM user WHERE" " id IN (TRAVERSE LAST RESULT) AND email = '' \n", verbose=9) delcount['user'] += run_sql("DELETE FROM user WHERE" " id IN (%s) AND email = ''" % (uidstr,)) # 2 - DELETE QUERIES NOT ATTACHED TO ANY USER # first step, delete from user_query write_message("- deleting user_queries referencing" " non-existent users") # find user_queries referencing non-existent users write_message(" SELECT DISTINCT uq.id_user\n" " FROM user_query AS uq LEFT JOIN user AS u\n" " ON uq.id_user = u.id\n WHERE u.id IS NULL", verbose=9) result = run_sql("""SELECT DISTINCT uq.id_user FROM user_query AS uq LEFT JOIN user AS u ON uq.id_user = u.id WHERE u.id IS NULL""") write_message(result, verbose=9) # delete in user_query one by one write_message(" DELETE FROM user_query WHERE" " id_user = '******' \n", verbose=9) for (id_user,) in result: delcount['user_query'] += run_sql("""DELETE FROM user_query WHERE id_user = %s""" % (id_user,)) # delete the actual queries write_message("- deleting queries not attached to any user") # select queries that must be deleted write_message(""" SELECT DISTINCT q.id\n FROM query AS q LEFT JOIN user_query AS uq\n ON uq.id_query = q.id\n WHERE uq.id_query IS NULL AND\n q.type <> 'p' """, verbose=9) result = run_sql("""SELECT DISTINCT q.id FROM query AS q LEFT JOIN user_query AS uq ON uq.id_query = q.id WHERE uq.id_query IS NULL AND q.type <> 'p'""") write_message(result, verbose=9) # delete queries one by one write_message(""" DELETE FROM query WHERE id = 'TRAVERSE LAST RESULT \n""", verbose=9) for (id_user,) in result: delcount['query'] += run_sql("""DELETE FROM query WHERE id = %s""", (id_user,)) # 3 - DELETE BASKETS NOT OWNED BY ANY USER write_message("- deleting baskets not owned by any user") # select basket ids write_message(""" SELECT ub.id_bskBASKET\n FROM user_bskBASKET AS ub LEFT JOIN user AS u\n ON u.id = ub.id_user\n WHERE u.id IS NULL""", verbose=9) try: result = run_sql("""SELECT ub.id_bskBASKET FROM user_bskBASKET AS ub LEFT JOIN user AS u ON u.id = ub.id_user WHERE u.id IS NULL""") except: result = [] write_message(result, verbose=9) # delete from user_basket and basket one by one write_message(""" DELETE FROM user_bskBASKET WHERE id_bskBASKET = 'TRAVERSE LAST RESULT' """, verbose=9) write_message(""" DELETE FROM bskBASKET WHERE id = 'TRAVERSE LAST RESULT' """, verbose=9) write_message(""" DELETE FROM bskREC WHERE id_bskBASKET = 'TRAVERSE LAST RESULT'""", verbose=9) write_message(""" DELETE FROM bskRECORDCOMMENT WHERE id_bskBASKET = 'TRAVERSE LAST RESULT' \n""", verbose=9) for (id_basket,) in result: delcount['user_bskBASKET'] += run_sql("""DELETE FROM user_bskBASKET WHERE id_bskBASKET = %s""", (id_basket,)) delcount['bskBASKET'] += run_sql("""DELETE FROM bskBASKET WHERE id = %s""", (id_basket,)) delcount['bskREC'] += run_sql("""DELETE FROM bskREC WHERE id_bskBASKET = %s""", (id_basket,)) delcount['bskRECORDCOMMENT'] += run_sql("""DELETE FROM bskRECORDCOMMENT WHERE id_bskBASKET = %s""", (id_basket,)) write_message(""" SELECT DISTINCT ext.id, rec.id_bibrec_or_bskEXTREC FROM bskEXTREC AS ext \nLEFT JOIN bskREC AS rec ON ext.id=-rec.id_bibrec_or_bskEXTREC WHERE id_bibrec_or_bskEXTREC is NULL""", verbose=9) try: result = run_sql("""SELECT DISTINCT ext.id FROM bskEXTREC AS ext LEFT JOIN bskREC AS rec ON ext.id=-rec.id_bibrec_or_bskEXTREC WHERE id_bibrec_or_bskEXTREC is NULL""") except: result = [] write_message(result, verbose=9) write_message(""" DELETE FROM bskEXTREC WHERE id = 'TRAVERSE LAST RESULT' """, verbose=9) write_message(""" DELETE FROM bskEXTFMT WHERE id_bskEXTREC = 'TRAVERSE LAST RESULT' \n""", verbose=9) for (id_basket,) in result: delcount['bskEXTREC'] += run_sql("""DELETE FROM bskEXTREC WHERE id=%s""", (id_basket,)) delcount['bskEXTFMT'] += run_sql("""DELETE FROM bskEXTFMT WHERE id_bskEXTREC=%s""", (id_basket,)) # 4 - DELETE ALERTS NOT OWNED BY ANY USER write_message('- deleting alerts not owned by any user') # select user ids in uqb that reference non-existent users write_message("""SELECT DISTINCT uqb.id_user FROM user_query_basket AS uqb LEFT JOIN user AS u ON uqb.id_user = u.id WHERE u.id IS NULL""", verbose=9) result = run_sql("""SELECT DISTINCT uqb.id_user FROM user_query_basket AS uqb LEFT JOIN user AS u ON uqb.id_user = u.id WHERE u.id IS NULL""") write_message(result, verbose=9) # delete all these entries for (id_user,) in result: write_message("""DELETE FROM user_query_basket WHERE id_user = '******'user_query_basket'] += run_sql("""DELETE FROM user_query_basket WHERE id_user = %s """, (id_user,)) # 5 - delete expired mailcookies write_message("""mail_cookie_gc()""", verbose=9) delcount['mail_cookie'] = mail_cookie_gc() ## 5b - delete expired not confirmed email address write_message("""DELETE FROM user WHERE note='2' AND NOW()>ADDTIME(last_login, '%s 0:0:0')""" % CFG_WEBSESSION_NOT_CONFIRMED_EMAIL_ADDRESS_EXPIRE_IN_DAYS, verbose=9) delcount['email_addresses'] = run_sql("""DELETE FROM user WHERE note='2' AND NOW()>ADDTIME(last_login, '%s 0:0:0')""", (CFG_WEBSESSION_NOT_CONFIRMED_EMAIL_ADDRESS_EXPIRE_IN_DAYS,)) # 6 - delete expired roles memberships write_message("""DELETE FROM user_accROLE WHERE expiration<NOW()""", verbose=9) delcount['role_membership'] = run_sql("""DELETE FROM user_accROLE WHERE expiration<NOW()""") # print STATISTICS write_message("""- statistics about deleted data: """) write_message(""" %7s sessions.""" % (delcount['session'],)) write_message(""" %7s users.""" % (delcount['user'],)) write_message(""" %7s user_queries.""" % (delcount['user_query'],)) write_message(""" %7s queries.""" % (delcount['query'],)) write_message(""" %7s baskets.""" % (delcount['bskBASKET'],)) write_message(""" %7s user_baskets.""" % (delcount['user_bskBASKET'],)) write_message(""" %7s basket_records.""" % (delcount['bskREC'],)) write_message(""" %7s basket_external_records.""" % (delcount['bskEXTREC'],)) write_message(""" %7s basket_external_formats.""" % (delcount['bskEXTFMT'],)) write_message(""" %7s basket_comments.""" % (delcount['bskRECORDCOMMENT'],)) write_message(""" %7s user_query_baskets.""" % (delcount['user_query_basket'],)) write_message(""" %7s mail_cookies.""" % (delcount['mail_cookie'],)) write_message(""" %7s non confirmed email addresses.""" % delcount['email_addresses']) write_message(""" %7s role_memberships.""" % (delcount['role_membership'],)) write_message("""CLEANING OF GUEST SESSIONS FINISHED""")
def edit_job(self, req, form): """Edits an existing job or creates a new one""" # Create an empty job and use its default values # to init missing parameters job = Job() argd = wash_urlargd(form, {"job_name": (str, job.get_name()), "output_directory": (str, job.get_output_directory()), "job_frequency": (int, job.get_frequency()), "output_format": (int, job.get_output_format()), "last_run": (str, convert_datestruct_to_datetext(job.get_last_run())), "id": (int, job.get_id()), "save_button": (str, ""), "cancel_button": (str, ""), "edit_queries_button": (str, "") }) language = argd["ln"] # load the right message language _ = gettext_set_language(language) self._check_user_credentials(req, language) user_id = self._get_user_id(req) job_id = argd["id"] job = Job(job_id = job_id, name = argd["job_name"], frequency = argd["job_frequency"], output_format = argd["output_format"], last_run = convert_datetext_to_datestruct(argd["last_run"]), output_directory = argd["output_directory"]) try: if argd["cancel_button"]: self._redirect_to_page(req, self._JOBS_URL, language) elif argd["save_button"]: perform_request_save_job(job = job, user_id = user_id, language = language) self._redirect_to_page(req, self._JOBS_URL, language) elif argd["edit_queries_button"]: result_job_id = perform_request_save_job(job = job, user_id = user_id, language = language) edit_queries_url = "%s?job_id=%s" % (self._JOB_QUERIES_URL, result_job_id) self._redirect_to_page(req, edit_queries_url, language) elif Job.ID_MISSING == job_id: title = _("New Export Job") body = perform_request_new_job(language = language) else: title = _("Edit Export Job") body = perform_request_edit_job(job_id = job_id, user_id = user_id, language = language) except AccessDeniedError: self._redirect_to_not_authorised_page(req, language) return page(title = title, metaheaderadd=get_css(), body = body, req = req, navmenuid = "fieldexporter", titleprologue = get_navigation_menu(language), navtrail = self._NAVTRAIL_EXPORT, language = language)