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()