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