def get_customevent_trend(args):
"""
Returns trend data for a custom event over a give
timestamp range.
@param args['id']: The event id
@type args['id']: str
@param args['t_start']: Date and time of start point
@type args['t_start']: str
@param args['t_end']: Date and time of end point
@type args['t_end']: str
@param args['granularity']: Granularity of date and time
@type args['granularity']: str
@param args['t_format']: Date and time formatting string
@type args['t_format']: str
@param args['cols']: Columns and it's content that will be include
if don't exist or it's empty it will include all cols
@type args['cols']: [ [ str, str ], ]
"""
# Get a MySQL friendly date
lower = _to_datetime(args['t_start'], args['t_format']).isoformat()
upper = _to_datetime(args['t_end'], args['t_format']).isoformat()
tbl_name = get_customevent_table(args['id'])
col_names = get_customevent_args(args['id'])
sql_query = ["SELECT creation_time FROM %s WHERE creation_time > '%s'" % (tbl_name, lower)]
sql_query.append("AND creation_time < '%s'" % upper)
sql_param = []
for col_bool, col_title, col_content in args['cols']:
if not col_title in col_names:
continue
if col_content:
if col_bool == "and" or col_bool == "":
sql_query.append("AND `%s`" % escape_string(col_title))
elif col_bool == "or":
sql_query.append("OR `%s`" % escape_string(col_title))
elif col_bool == "and_not":
sql_query.append("AND NOT `%s`" % escape_string(col_title))
else:
continue
sql_query.append(" LIKE %s")
sql_param.append("%" + col_content + "%")
sql_query.append("ORDER BY creation_time DESC")
sql = ' '.join(sql_query)
dates = [x[0] for x in run_sql(sql, tuple(sql_param))]
return _get_trend_from_actions(dates, 0, args['t_start'], args['t_end'], args['granularity'], args['t_format'])
def records_updater(record):
(bskid, id_owner, id_record, order, date_modification) = record
return "(%i,%i,%i,%i,'%s')" % (
int(id_record),
int(bskid),
int(id_owner),
int(order),
escape_string(date_modification),
)
def basket_updater(basket):
""""""
(bskid, name, is_public, id_owner, date_modification) = basket
try:
int(bskid)
int(id_owner)
except:
print "#####################"
print "id basket:"
print bskid
print "id user"
print id_owner
print "#########################"
return "(%i,'%s',%i,'%s')" % (
int(bskid),
escape_string(name),
int(id_owner),
escape_string(date_modification),
)
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 = escape_string(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 get_customevent_dump(args):
"""
Similar to a get_event_trend implemention, but NO refining aka frequency
handling is carried out what so ever. This is just a dump. A dump!
@param args['id']: The event id
@type args['id']: str
@param args['t_start']: Date and time of start point
@type args['t_start']: str
@param args['t_end']: Date and time of end point
@type args['t_end']: str
@param args['granularity']: Granularity of date and time
@type args['granularity']: str
@param args['t_format']: Date and time formatting string
@type args['t_format']: str
@param args['cols']: Columns and it's content that will be include
if don't exist or it's empty it will include all cols
@type args['cols']: [ [ str, str ], ]
"""
# Get a MySQL friendly date
lower = _to_datetime(args['t_start'], args['t_format']).isoformat()
upper = _to_datetime(args['t_end'], args['t_format']).isoformat()
# Get customevents
# events_list = [(creation_time, event, [arg1, arg2, ...]), ...]
event_list = []
event_cols = {}
for id, i in [ (args['ids'][i], str(i)) for i in range(len(args['ids']))]:
# Get all the event arguments and creation times
tbl_name = get_customevent_table(id)
col_names = get_customevent_args(id)
sql_query = ["SELECT * FROM %s WHERE creation_time > '%s'" % (tbl_name, lower)] # Note: SELECT * technique is okay here
sql_query.append("AND creation_time < '%s'" % upper)
sql_param = []
for col_bool, col_title, col_content in args['cols'+i]:
if not col_title in col_names: continue
if col_content:
if col_bool == "and" or col_bool == "":
sql_query.append("AND `%s`" % escape_string(col_title))
elif col_bool == "or":
sql_query.append("OR `%s`" % escape_string(col_title))
elif col_bool == "and_not":
sql_query.append("AND NOT `%s`" % escape_string(col_title))
else:
continue
sql_query.append(" LIKE %s")
sql_param.append("%" + col_content + "%")
sql_query.append("ORDER BY creation_time DESC")
sql = ' '.join(sql_query)
res = run_sql(sql, tuple(sql_param))
for row in res:
event_list.append((row[1],id,row[2:]))
# Get the event col names
try:
event_cols[id] = cPickle.loads(run_sql("SELECT cols FROM staEVENT WHERE id = %s", (id,))[0][0])
except TypeError:
event_cols[id] = ["Unnamed"]
event_list.sort()
output = []
for row in event_list:
temp = [row[1], row[0].strftime('%Y-%m-%d %H:%M:%S')]
arguments = ["%s: %s" % (event_cols[row[1]][i], row[2][i]) for i in range(len(row[2]))]
temp.extend(arguments)
output.append(tuple(temp))
return output
