def sync_raw_data(sync_agent, uuid, to_local=False):
if to_local:
conn_src = sync_agent.conn_remote
conn_dest = sync_agent.conn_local
else:
conn_src = sync_agent.conn_local
conn_dest = sync_agent.conn_remote
raw_data_table_name = select_elements_in_table(
conn_src,
'global_measurement_overview', ('exp_data_location', ),
where=("uuid", uuid),
dict_cursor=False)[0][0]
# data_table_queries.generate_table(sync_agent.conn_local, raw_data_table_name)
sync_mgr_queries._sync_raw_data_table(conn_src, conn_dest,
raw_data_table_name)
sync_mgr_queries._sync_raw_data_lobj(conn_src, conn_dest,
raw_data_table_name)
update_table(sync_agent.conn_local,
'global_measurement_overview', ('data_synchronized', ),
(True, ),
condition=("uuid", uuid))
sync_agent.conn_local.commit()
def star_measurement(uuid, state):
conn = SQL_database_manager().conn_local
update_table(conn,
'global_measurement_overview',
('starred', 'table_synchronized'), (state, False),
condition=('uuid', uuid))
conn.commit()
def update_name(uuid, name):
conn = SQL_database_manager().conn_local
update_table(conn,
'global_measurement_overview',
('exp_name', 'table_synchronized'), (name, False),
condition=('uuid', uuid))
conn.commit()
def update_measurement(conn, meas_uuid, meas_table_name=None, start_time=None, stop_time=None,
metadata=None, snapshot=None, keywords= None, data_size=None, data_synchronized=False, completed=False):
'''
fill in the addional data in a record of the measurements overview table.
Args:
meas_uuid (int) : record that needs to be updated
meas_table_name (str) : name of the table that contains the raw measurement data
start_time (long) : time in unix seconds since the epoch
stop_time (long) : time in unix seconds since the epoch
metadata (dict) : json string to be saved in the database
snapshot (dict) : snapshot of the exprimental set up
keywords (list) : keywords describing the measurement
completed (bool) : tell that the measurement is completed.
'''
var_names = ['exp_data_location','metadata', 'snapshot', 'keywords', 'data_size', 'data_synchronized', 'completed']
var_values = [meas_table_name, psycopg2.Binary(str(json.dumps(metadata)).encode('ascii')),
psycopg2.Binary(str(json.dumps(snapshot)).encode('ascii')), psycopg2.extras.Json(keywords),
data_size, str(data_synchronized), str(completed) ]
if start_time is not None:
var_names += ['start_time']
var_values += [psycopg2.sql.SQL("TO_TIMESTAMP({})").format(psycopg2.sql.Literal(start_time))]
if stop_time is not None:
var_names += ['stop_time']
var_values += [psycopg2.sql.SQL("TO_TIMESTAMP({})").format(psycopg2.sql.Literal(stop_time))]
condition = ('uuid', meas_uuid)
update_table(conn, measurement_overview_queries.table_name, var_names, var_values, condition)
def sync_table(sync_agent, uuid):
'''
syncs row in the table to the remote for the given uuid
'''
# check if uuid exists
entry_exists = select_elements_in_table(sync_agent.conn_remote,
"global_measurement_overview",
('uuid', ),
where=("uuid", uuid),
dict_cursor=False)
local_content = select_elements_in_table(sync_agent.conn_local,
"global_measurement_overview",
('*', ),
where=("uuid", uuid),
dict_cursor=True)[0]
sync_mgr_queries.convert_SQL_raw_table_entry_to_python(local_content)
del local_content['id']
local_content['table_synchronized'] = True
if len(entry_exists) == False:
insert_row_in_table(sync_agent.conn_remote,
'global_measurement_overview',
tuple(local_content.keys()),
tuple(local_content.values()))
else:
remote_content = select_elements_in_table(
sync_agent.conn_remote,
"global_measurement_overview", ('*', ),
where=("uuid", uuid),
dict_cursor=True)[0]
sync_mgr_queries.convert_SQL_raw_table_entry_to_python(
remote_content)
del remote_content['id']
content_to_update = dict()
for key in remote_content.keys():
if local_content[key] != remote_content[key]:
content_to_update[key] = local_content[key]
update_table(sync_agent.conn_remote,
'global_measurement_overview',
content_to_update.keys(),
content_to_update.values(),
condition=("uuid", uuid))
update_table(sync_agent.conn_local,
'global_measurement_overview', ('table_synchronized', ),
(True, ),
condition=("uuid", uuid))
sync_agent.conn_local.commit()
sync_agent.conn_remote.commit()
def add_variable(conn, name, unit, category, step, value=0):
# this will be the line where we set the value
vals, last_update_id = var_sql_queries.update_val(conn, name=None, value=None)
res = select_elements_in_table(conn, var_sql_queries.gen_table_overview_name(), ('name', ), where=('name', name))
if len(res) == 0:
insert_row_in_table(conn, var_sql_queries.gen_table_overview_name(), ('name', 'unit', 'category', 'step'), (name, unit, category, step))
alter_table(conn, var_sql_queries.gen_table_content_name(), (name, ), ('FLOAT8',))
update_table(conn, var_sql_queries.gen_table_content_name(), (name,), (value,), condition=('id', last_update_id))
conn.commit()
else:
print('Variable {} already present, skipping.'.format(name))
def sync_raw_data(sync_agent, uuid):
raw_data_table_name = select_elements_in_table(
sync_agent.conn_local,
'global_measurement_overview', ('exp_data_location', ),
where=("uuid", uuid),
dict_cursor=False)[0][0]
data_table_queries.generate_table(sync_agent.conn_local,
raw_data_table_name)
sync_mgr_queries._sync_raw_data_table(sync_agent, raw_data_table_name)
update_table(sync_agent.conn_local,
'global_measurement_overview', ('data_synchronized', ),
(True, ),
condition=("uuid", uuid))
sync_agent.conn_local.commit()
sync_mgr_queries._sync_raw_data_lobj(sync_agent, raw_data_table_name)
def _sync_raw_data_lobj(conn_src, conn_dest, raw_data_table_name):
res_src = select_elements_in_table(
conn_src,
raw_data_table_name, ('write_cursor', 'total_size', 'oid'),
order_by=('id', ''))
res_dest = select_elements_in_table(
conn_dest,
raw_data_table_name, ('write_cursor', 'total_size', 'oid'),
order_by=('id', ''))
print('update large object', raw_data_table_name)
for i in range(len(res_src)):
dest_cursor = res_dest[i]['write_cursor']
src_cursor = res_src[i]['write_cursor']
dest_oid = res_dest[i]['oid']
src_oid = res_src[i]['oid']
src_lobject = conn_src.lobject(src_oid, 'rb')
dest_lobject = conn_dest.lobject(dest_oid, 'wb')
while (dest_cursor != src_cursor):
src_lobject.seek(dest_cursor * 8)
dest_lobject.seek(dest_cursor * 8)
if src_cursor * 8 - dest_cursor * 8 < 2_000_000:
mybuffer = np.frombuffer(
src_lobject.read(src_cursor * 8 - dest_cursor * 8))
dest_cursor = src_cursor
else:
print(
f'large dataset, {(src_cursor*8-dest_cursor*8)*1e-9}GB'
)
mybuffer = np.frombuffer(src_lobject.read(2_000_000))
dest_cursor += int(2_000_000 / 8)
dest_lobject.write(mybuffer.tobytes())
dest_lobject.close()
src_lobject.close()
update_table(conn_dest,
raw_data_table_name, ('write_cursor', ),
(src_cursor, ),
condition=('oid', dest_oid))
conn_dest.commit()
def _sync_raw_data_lobj(sync_agent, raw_data_table_name):
res_loc = select_elements_in_table(
sync_agent.conn_local,
raw_data_table_name, ('write_cursor', 'total_size', 'oid'),
order_by=('id', ''))
res_rem = select_elements_in_table(
sync_agent.conn_remote,
raw_data_table_name, ('write_cursor', 'total_size', 'oid'),
order_by=('id', ''))
for i in range(len(res_loc)):
r_cursor = res_rem[i]['write_cursor']
l_cursor = res_loc[i]['write_cursor']
r_oid = res_rem[i]['oid']
l_oid = res_loc[i]['oid']
l_lobject = sync_agent.conn_local.lobject(l_oid, 'rb')
r_lobject = sync_agent.conn_remote.lobject(r_oid, 'wb')
while (r_cursor != l_cursor):
l_lobject.seek(r_cursor * 8)
r_lobject.seek(r_cursor * 8)
if l_cursor * 8 - r_cursor * 8 < 2_000_000:
mybuffer = np.frombuffer(
l_lobject.read(l_cursor * 8 - r_cursor * 8))
r_cursor = l_cursor
else:
print(f'large dataset, {(l_cursor*8-r_cursor*8)*1e-9}GB')
mybuffer = np.frombuffer(l_lobject.read(2_000_000))
r_cursor += int(2_000_000 / 8)
r_lobject.write(mybuffer.tobytes())
r_lobject.close()
l_lobject.close()
update_table(sync_agent.conn_remote,
raw_data_table_name, ('write_cursor', ), (l_cursor, ),
condition=('oid', r_oid))
sync_agent.conn_remote.commit()
def sync_table(sync_agent, uuid, to_local=False):
'''
syncs row in the table to the remote for the given uuid
Args:
sync_agent: class holding local and remote connection
uuid (int): unique id of measurement
to_local (bool): if True syncs from remote to local server
'''
if to_local:
conn_src = sync_agent.conn_remote
conn_dest = sync_agent.conn_local
else:
conn_src = sync_agent.conn_local
conn_dest = sync_agent.conn_remote
# check if uuid exists
entry_exists = select_elements_in_table(conn_dest,
"global_measurement_overview",
('uuid', ),
where=("uuid", uuid),
dict_cursor=False)
source_content = select_elements_in_table(
conn_src,
"global_measurement_overview", ('*', ),
where=("uuid", uuid),
dict_cursor=True)[0]
sync_mgr_queries.convert_SQL_raw_table_entry_to_python(source_content)
del source_content['id']
source_content['table_synchronized'] = True
if len(entry_exists) == 0:
print('create measurement row', uuid)
insert_row_in_table(conn_dest, 'global_measurement_overview',
tuple(source_content.keys()),
tuple(source_content.values()))
else:
print('update measurement row', uuid)
dest_content = select_elements_in_table(
conn_dest,
"global_measurement_overview", ('*', ),
where=("uuid", uuid),
dict_cursor=True)[0]
sync_mgr_queries.convert_SQL_raw_table_entry_to_python(
dest_content)
del dest_content['id']
content_to_update = dict()
for key in dest_content.keys():
if source_content[key] != dest_content[key]:
content_to_update[key] = source_content[key]
update_table(conn_dest,
'global_measurement_overview',
content_to_update.keys(),
content_to_update.values(),
condition=("uuid", uuid))
if not to_local:
update_table(sync_agent.conn_local,
'global_measurement_overview',
('table_synchronized', ), (True, ),
condition=("uuid", uuid))
conn_src.commit()
conn_dest.commit()