def snapshot_materialization(operation,rel_name,timeline_table,query_time,materialized): snapshot_name,temp_snapshot_name = choose_names(operation,rel_name) attributes = [x for x in fcn.table_attribs('rating') if not x == 'id'] f_value_clause = create_first_value_clause(attributes) fcn.drop_table(temp_snapshot_name) # # creates a sapshot which contains the records of materialized snapshot and the new snapshot in a temporary table union_snapshot_and_query(temp_snapshot_name,timeline_table,f_value_clause,query_time,materialized) # # removes records that have been deleted from temporary table and creats a permenant table snap_query_table_creation(snapshot_name,f_value_clause,temp_snapshot_name) # # drops the temporary table fcn.drop_table(temp_snapshot_name) return snapshot_name
def random_update(): random_rate = randint(1,5) attribs = [x for x in fcn.table_attribs('rating') if not x == 'signature'] random_record_list = list(fcn.fetch_specific_record_list(attribs,'rating','ORDER BY RANDOM() LIMIT 1','one')) random_record_dict = fcn.fetch_specific_attribs_record(attribs,'rating', 'WHERE id ={0}'.format(random_record_list[0])) random_record_list[6] = random_rate signature = fcn.create_signature(random_record_list,random_record_dict['username']) user_location = fcn.fetch_specific_attribs_record(['lat','long'],'users','where user_id = {0}'.format(random_record_dict['user_id'])) sql = "UPDATE rating SET star = (%s), signature = (%s) WHERE id =(%s)" parameters = (random_rate,signature,random_record_dict['id']) db.command(sql,parameters) db.commit() print 'update' data = {'action':'update','user':random_record_dict['username'],'movie':random_record_dict['mv_name'],'rating':random_record_dict['star'],'position':[user_location['lat'], user_location['long']],'time':datetime.now()} return data
def random_insert(): #insrts random records to the main database random_rating = randint(1,5) user = fcn.random_user() movie = fcn.random_movie() rating_attribs = [x for x in fcn.table_attribs('rating')] record_id = fcn.get_nextval_counter('rating_id_seq') sql = "INSERT INTO rating VALUES(%s)"%",".join("%s" for i in range(len(rating_attribs))) parameters = fcn.insert_parameters(user,movie,random_rating) parameters.insert(0,record_id) signature = fcn.create_signature(parameters,user['username']) parameters.append(signature) db.insert(sql,parameters) db.commit() print 'insert' return {'action':'insert','user':user['username'],'movie':movie['mv_name'],'rating':random_rating,'position':[user['lat'],user['long']],'time':str(datetime.now())}
def query_snapshot(snapshot_name): dict = {} dict_list = [] data = fcn.fetch_table_records(snapshot_name) keys = fcn.table_attribs(snapshot_name) integerKeys = ['mov_id','rec_id','star','user_id','release_date'] forbiddenKeys = ['flag','__t__' ,'signer', 'snap_sign'] keys = [x for x in keys if x not in integerKeys+forbiddenKeys] for i in range(len(data)): temp_dict = {x :data[i][x] for x in keys} for integers in integerKeys: temp_dict[integers] = str(data[i][integers]) dict_list.append(temp_dict) dict['name'] = snapshot_name dict['data'] = dict_list dict['status'] = fcn.verify_snapshot_signature(snapshot_name) dict['keys'] = keys+integerKeys fcn.firebase_writing(dict,'snapshotQuery','snapshot')
def random_delete(): #deletes random number of records attribs = [x for x in fcn.table_attribs('rating') if not x == 'signature'] random_record_list = list(fcn.fetch_specific_record_list(attribs,'rating','ORDER BY RANDOM() LIMIT 1','one')) user_location = fcn.fetch_specific_attribs_record(['lat','long'],'users','where user_id = {0}'.format(random_record_list[7])) try: sql = "DELETE FROM rating WHERE id =(%s)" parameters = (random_record_list[0],) db.command(sql,parameters) db.commit() for i in range(1,len(random_record_list)-1): random_record_list[i] = None signature = fcn.create_signature(random_record_list,random_record_list[8]) record_id = fcn.fetch_specific_record_list(['last_value',],'id','','one')[0] sql = "UPDATE timeline SET signature = (%s) WHERE id = (%s)" parameters = [signature,record_id] db.command(sql,parameters) db.commit() print 'delete' data = {'action':'delete','user':random_record_list[8],'movie':random_record_list[2],'rating':random_record_list[6],'position':[user_location['lat'],user_location['long']],'time':str(datetime.now())} return data except Exception as e: print e return 'error'
def create_snapshots(rel_name,timestamp): snap_id = fcn.get_nextval_counter('snap_id') snapshot_name = "{0}__{1}".format(rel_name,str(snap_id)) attributes = [x for x in fcn.table_attribs(rel_name) if not x == 'id'] f_value_clause = create_first_value_clause(attributes) sql = ''' CREATE TABLE IF NOT EXISTS {snap_name} AS SELECT DISTINCT * FROM ( SELECT rec_id, {latest_attributes}, max(__t__) OVER w AS __t__, first_value(__flag__) over w AS flag FROM {timeline_table} WHERE __t__<= %s window w AS (partition by rec_id ORDER BY __t__ DESC)) T WHERE flag =0 '''.format(snap_name = snapshot_name, timeline_table = 'timeline',latest_attributes = f_value_clause) parameters = [timestamp,] db.command(sql,parameters) db.commit() return snapshot_name
def union_snapshot_and_query(temp_snapshot_name,timeline_table,f_value_clause,query_timestamp,materializing_snapshot): start_timestamp = fcn.table_duration(materializing_snapshot)['max'] materialized_attribs = fcn.table_attribs(materializing_snapshot) snap_attribs = ",\n".join("{c}".format(c=x) for x in materialized_attribs if not (x == 'signer' or x =='snap_sign')) sql = ''' CREATE TABLE IF NOT EXISTS {temp} AS SELECT DISTINCT * FROM ( SELECT rec_id, {latest_attributes}, max(__t__) OVER w AS __t__, first_value(__flag__) over w AS flag FROM {timeline_table} WHERE (__t__ BETWEEN %s AND %s) window w AS (partition by rec_id ORDER BY __t__ DESC)) T UNION ALL SELECT {snapshot_attributes} FROM {materialized_snapshot} '''.format(temp =temp_snapshot_name, timeline_table = 'timeline', latest_attributes = f_value_clause, snapshot_attributes = snap_attribs, materialized_snapshot = materializing_snapshot) attributes = [start_timestamp,query_timestamp] db.command(sql,attributes) db.commit()