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 get_dataset_raw(conn, exp_uuid):
data = select_elements_in_table(conn,
load_ds_queries.table_name,
var_names=('*', ),
where=("uuid", exp_uuid))[0]
if data['stop_time'] is None:
data['stop_time'] = data['start_time']
if data['snapshot'] is not None:
data['snapshot'] = json.loads(data['snapshot'].tobytes())
if data['metadata'] is not None:
data['metadata'] = json.loads(data['metadata'].tobytes())
ds = data_set_raw(
exp_id=data['id'],
exp_uuid=data['uuid'],
exp_name=data['exp_name'],
set_up=data['set_up'],
project=data['project'],
sample=data['sample'],
UNIX_start_time=data['start_time'].timestamp(),
UNIX_stop_time=data['stop_time'].timestamp(),
SQL_datatable=data['exp_data_location'],
snapshot=data['snapshot'],
metadata=data['metadata'],
keywords=data['keywords'],
completed=data['completed'],
)
ds.measurement_parameters_raw = load_ds_queries.__get_dataset_raw_dataclasses(
conn, ds.SQL_datatable)
return ds
def get_all_values(conn):
res = select_elements_in_table(conn, var_sql_queries.gen_table_content_name(), ('*',),
order_by=('id','DESC'), limit=100, dict_cursor=RealDictCursor)
if len(res) == 0:
return {}
return res[0]
def is_running(conn, exp_uuid):
return_data = select_elements_in_table(conn,
load_ds_queries.table_name,
var_names=('completed', ),
where=("uuid", exp_uuid))
return return_data[0][0]
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 _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_table(sync_agent, raw_data_table_name):
n_row_loc = select_elements_in_table(sync_agent.conn_local,
raw_data_table_name,
(psycopg2.sql.SQL('COUNT(*)'), ),
dict_cursor=False)[0][0]
table_name = execute_query(
sync_agent.conn_remote,
"SELECT to_regclass('{}.{}');".format('public',
raw_data_table_name))[0][0]
n_row_rem = 0
if table_name is not None:
n_row_rem = select_elements_in_table(
sync_agent.conn_remote,
raw_data_table_name, (psycopg2.sql.SQL('COUNT(*)'), ),
dict_cursor=False)[0][0]
if n_row_loc != n_row_rem or table_name == None:
get_rid_of_table = "DROP TABLE IF EXISTS {} ; ".format(
raw_data_table_name)
execute_statement(sync_agent.conn_remote, get_rid_of_table)
data_table_queries.generate_table(sync_agent.conn_remote,
raw_data_table_name)
res_loc = select_elements_in_table(sync_agent.conn_local,
raw_data_table_name, ('*', ),
order_by=('id', ''))
for result in res_loc:
lobject = sync_agent.conn_remote.lobject(0, 'w')
del result['id']
result['oid'] = lobject.oid
result['write_cursor'] = 0
result['depencies'] = json.dumps(result['depencies'])
result['shape'] = json.dumps(result['shape'])
insert_row_in_table(sync_agent.conn_remote,
raw_data_table_name, result.keys(),
result.values())
sync_agent.conn_remote.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 get_sync_items_raw_data(sync_agent):
'''
returns:
meaurments <list<long>> : list of uuid's where the data needs to be updated of.
'''
res = select_elements_in_table(sync_agent.conn_local,
"global_measurement_overview",
('uuid', ),
where=('data_synchronized', False),
dict_cursor=False)
uuid_entries = list(sum(res, ()))
uuid_entries.sort()
return uuid_entries
def get_sync_items_meas_table(sync_agent):
'''
returns:
meaurments <list<long>> : list of uuid's who's table entries need a sync
'''
res = select_elements_in_table(sync_agent.conn_local,
"global_measurement_overview",
('uuid', ),
where=("table_synchronized", False),
dict_cursor=False)
uuid_entries = list(sum(res, ()))
uuid_entries.sort()
return uuid_entries
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 __get_dataset_raw_dataclasses(conn, table_name):
var_names = ("param_id", "nth_set", "nth_dim", "param_id_m_param",
"setpoint", "setpoint_local", "name_gobal", "name",
"label", "unit", "depencies", "shape", "total_size",
"oid")
return_data = select_elements_in_table(conn,
table_name,
var_names,
dict_cursor=False)
data_raw = []
for row in return_data:
raw_data_row = m_param_raw(*row)
raw_data_row.data_buffer = buffer_reader(conn, raw_data_row.oid,
raw_data_row.shape)
data_raw.append(raw_data_row)
return data_raw
def get_all_specs(conn):
return select_elements_in_table(conn, var_sql_queries.gen_table_overview_name(), ('*', ), dict_cursor=RealDictCursor)
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()