def alert_display():
"""
Display alert statistics.
"""
tbl_name = get_customevent_table("alerts")
if not tbl_name:
# custom event alerts not defined, so return empty output:
return []
try:
res = run_sql("SELECT creation_time FROM %s ORDER BY creation_time" % tbl_name)
days = (res[-1][0] - res[0][0]).days + 1
res = run_sql("SELECT COUNT(DISTINCT user),COUNT(*) FROM %s" % tbl_name)
users = res[0][0]
hits = res[0][1]
displays = run_sql("SELECT COUNT(*) FROM %s WHERE action = 'list'" % tbl_name)[0][0]
search = run_sql("SELECT COUNT(*) FROM %s WHERE action = 'display'" % tbl_name)[0][0]
average = hits / days
res = [("Alerts page hits", hits)]
res.append((" Average per day", average))
res.append((" Unique users", users))
res.append((" Displays", displays))
res.append((" Searches history display", search))
except IndexError:
res = []
return res
def destroy_customevent(id=None):
"""
Removes an existing custom event by destroying the MySQL tables and
the event data that might be around. Use with caution!
@param id: Human-readable id of the event to be removed.
@type id: str
@return: A status message
@type: str
"""
if id is None:
return "Please specify an existing event id."
# Check if the specified id exists
if len(run_sql("SELECT NULL FROM staEVENT WHERE id = %s", (id,))) == 0:
return "Event id [%s] doesn't exist! Aborted." % id
else:
tbl_name = get_customevent_table(id)
run_sql("DROP TABLE %s" % tbl_name)
run_sql("DELETE FROM staEVENT WHERE id = %s", (id,))
return ("Event with id [%s] was successfully destroyed.\n" + "Table [%s], with content, was destroyed.") % (
id,
tbl_name,
)
def basket_display():
"""
Display basket statistics.
"""
tbl_name = get_customevent_table("baskets")
if not tbl_name:
# custom event baskets not defined, so return empty output:
return []
try:
res = run_sql("SELECT creation_time FROM %s ORDER BY creation_time" % tbl_name)
days = (res[-1][0] - res[0][0]).days + 1
public = run_sql("SELECT COUNT(*) FROM %s WHERE action = 'display_public'" % tbl_name)[0][0]
users = run_sql("SELECT COUNT(DISTINCT user) FROM %s" % tbl_name)[0][0]
adds = run_sql("SELECT COUNT(*) FROM %s WHERE action = 'add'" % tbl_name)[0][0]
displays = run_sql("SELECT COUNT(*) FROM %s WHERE action = 'display' OR action = 'display_public'" % tbl_name)[0][0]
hits = adds + displays
average = hits / days
res = [("Basket page hits", hits)]
res.append((" Average per day", average))
res.append((" Unique users", users))
res.append((" Additions", adds))
res.append((" Public", public))
except IndexError:
res = []
return res
def alert_display():
"""
Display alert statistics.
"""
tbl_name = get_customevent_table("alerts")
if not tbl_name:
# custom event alerts not defined, so return empty output:
return []
try:
res = run_sql("SELECT creation_time FROM %s ORDER BY creation_time" %
tbl_name)
days = (res[-1][0] - res[0][0]).days + 1
res = run_sql("SELECT COUNT(DISTINCT user),COUNT(*) FROM %s" %
tbl_name)
users = res[0][0]
hits = res[0][1]
displays = run_sql("SELECT COUNT(*) FROM %s WHERE action = 'list'" %
tbl_name)[0][0]
search = run_sql("SELECT COUNT(*) FROM %s WHERE action = 'display'" %
tbl_name)[0][0]
average = hits / days
res = [("Alerts page hits", hits)]
res.append((" Average per day", average))
res.append((" Unique users", users))
res.append((" Displays", displays))
res.append((" Searches history display", search))
except IndexError:
res = []
return res
def basket_display():
"""
Display basket statistics.
"""
tbl_name = get_customevent_table("baskets")
if not tbl_name:
# custom event baskets not defined, so return empty output:
return []
try:
res = run_sql("SELECT creation_time FROM %s ORDER BY creation_time" %
tbl_name)
days = (res[-1][0] - res[0][0]).days + 1
public = run_sql(
"SELECT COUNT(*) FROM %s WHERE action = 'display_public'" %
tbl_name)[0][0]
users = run_sql("SELECT COUNT(DISTINCT user) FROM %s" % tbl_name)[0][0]
adds = run_sql("SELECT COUNT(*) FROM %s WHERE action = 'add'" %
tbl_name)[0][0]
displays = run_sql(
"SELECT COUNT(*) FROM %s WHERE action = 'display' OR action = 'display_public'"
% tbl_name)[0][0]
hits = adds + displays
average = hits / days
res = [("Basket page hits", hits)]
res.append((" Average per day", average))
res.append((" Unique users", users))
res.append((" Additions", adds))
res.append((" Public", public))
except IndexError:
res = []
return res
def destroy_customevent(id=None):
"""
Removes an existing custom event by destroying the MySQL tables and
the event data that might be around. Use with caution!
@param id: Human-readable id of the event to be removed.
@type id: str
@return: A status message
@type: str
"""
if id is None:
return "Please specify an existing event id."
# Check if the specified id exists
if len(run_sql("SELECT NULL FROM staEVENT WHERE id = %s", (id, ))) == 0:
return "Event id [%s] doesn't exist! Aborted." % id
else:
tbl_name = get_customevent_table(id)
run_sql("DROP TABLE %s" % tbl_name)
run_sql("DELETE FROM staEVENT WHERE id = %s", (id, ))
return ("Event with id [%s] was successfully destroyed.\n" +
"Table [%s], with content, was destroyed.") % (id, tbl_name)
def create_customevent(id=None, name=None, cols=[]):
"""
Creates a new custom event by setting up the necessary MySQL tables.
@param id: Proposed human-readable id of the new event.
@type id: str
@param name: Optionally, a descriptive name.
@type name: str
@param cols: Optionally, the name of the additional columns.
@type cols: [str]
@return: A status message
@type: str
"""
if id is None:
return "Please specify a human-readable ID for the event."
# Only accept id and name with standard characters
if not re.search("[^\w]", str(id) + str(name)) is None:
return "Please note that both event id and event name needs to be written without any non-standard characters."
# Make sure the chosen id is not already taken
if len(run_sql("SELECT NULL FROM staEVENT WHERE id = %s", (id,))) != 0:
return "Event id [%s] already exists! Aborted." % id
# Check if the cols are valid titles
for argument in cols:
if (argument == "creation_time") or (argument == "id"):
return "Invalid column title: %s! Aborted." % argument
# Insert a new row into the events table describing the new event
sql_param = [id]
if name is not None:
sql_name = "%s"
sql_param.append(name)
else:
sql_name = "NULL"
if len(cols) != 0:
sql_cols = "%s"
sql_param.append(cPickle.dumps(cols))
else:
sql_cols = "NULL"
run_sql("INSERT INTO staEVENT (id, name, cols) VALUES (%s, " + sql_name + ", " + sql_cols + ")",
tuple(sql_param))
tbl_name = get_customevent_table(id)
# Create a table for the new event
sql_query = ["CREATE TABLE %s (" % tbl_name]
sql_query.append("id MEDIUMINT unsigned NOT NULL auto_increment,")
sql_query.append("creation_time TIMESTAMP DEFAULT NOW(),")
for argument in cols:
arg = wash_table_column_name(argument)
sql_query.append("%s MEDIUMTEXT NULL," % arg)
sql_query.append("INDEX %s (%s(50))," % (arg, arg))
sql_query.append("PRIMARY KEY (id))")
sql_str = ' '.join(sql_query)
run_sql(sql_str)
# We're done! Print notice containing the name of the event.
return ("Event table [%s] successfully created.\n" +
"Please use event id [%s] when registering an event.") % (tbl_name, id)
def create_customevent(id=None, name=None, cols=[]):
"""
Creates a new custom event by setting up the necessary MySQL tables.
@param id: Proposed human-readable id of the new event.
@type id: str
@param name: Optionally, a descriptive name.
@type name: str
@param cols: Optionally, the name of the additional columns.
@type cols: [str]
@return: A status message
@type: str
"""
if id is None:
return "Please specify a human-readable ID for the event."
# Only accept id and name with standard characters
if not re.search("[^\w]", str(id) + str(name)) is None:
return "Please note that both event id and event name needs to be written without any non-standard characters."
# Make sure the chosen id is not already taken
if len(run_sql("SELECT NULL FROM staEVENT WHERE id = %s", (id, ))) != 0:
return "Event id [%s] already exists! Aborted." % id
# Check if the cols are valid titles
for argument in cols:
if (argument == "creation_time") or (argument == "id"):
return "Invalid column title: %s! Aborted." % argument
# Insert a new row into the events table describing the new event
sql_param = [id]
if name is not None:
sql_name = "%s"
sql_param.append(name)
else:
sql_name = "NULL"
if len(cols) != 0:
sql_cols = "%s"
sql_param.append(cPickle.dumps(cols))
else:
sql_cols = "NULL"
run_sql(
"INSERT INTO staEVENT (id, name, cols) VALUES (%s, " + sql_name +
", " + sql_cols + ")", tuple(sql_param))
tbl_name = get_customevent_table(id)
# Create a table for the new event
sql_query = ["CREATE TABLE %s (" % tbl_name]
sql_query.append("id MEDIUMINT unsigned NOT NULL auto_increment,")
sql_query.append("creation_time TIMESTAMP DEFAULT NOW(),")
for argument in cols:
arg = wash_table_column_name(argument)
sql_query.append("%s MEDIUMTEXT NULL," % arg)
sql_query.append("INDEX %s (%s(50))," % (arg, arg))
sql_query.append("PRIMARY KEY (id))")
sql_str = ' '.join(sql_query)
run_sql(sql_str)
# We're done! Print notice containing the name of the event.
return ("Event table [%s] successfully created.\n" +
"Please use event id [%s] when registering an event.") % (tbl_name,
id)
