def _add_parameters_to_layout_and_deps(
conn: ConnectionPlus, formatted_name: str,
*parameter: ParamSpec) -> sqlite3.Cursor:
# get the run_id
sql = f"""
SELECT run_id FROM runs WHERE result_table_name="{formatted_name}";
"""
run_id = one(transaction(conn, sql), 'run_id')
layout_args = []
for p in parameter:
layout_args.append(run_id)
layout_args.append(p.name)
layout_args.append(p.label)
layout_args.append(p.unit)
layout_args.append(p.inferred_from)
rowplaceholder = '(?, ?, ?, ?, ?)'
placeholder = ','.join([rowplaceholder] * len(parameter))
sql = f"""
INSERT INTO layouts (run_id, parameter, label, unit, inferred_from)
VALUES {placeholder}
"""
with atomic(conn) as conn:
c = transaction(conn, sql, *layout_args)
for p in parameter:
if p.depends_on != '':
layout_id = get_layout_id(conn, p, run_id)
deps = p.depends_on.split(', ')
for ax_num, dp in enumerate(deps):
sql = """
SELECT layout_id FROM layouts
WHERE run_id=? and parameter=?;
"""
c = transaction(conn, sql, run_id, dp)
dep_ind = one(c, 'layout_id')
sql = """
INSERT INTO dependencies (dependent, independent, axis_num)
VALUES (?,?,?)
"""
c = transaction(conn, sql, layout_id, dep_ind, ax_num)
return c
def fix_version_4a_run_description_bug(conn: ConnectionPlus) -> Dict[str, int]:
"""
Fix function to fix a bug where the RunDescriber accidentally wrote itself
to string using the (new) InterDependencies_ object instead of the (old)
InterDependencies object. After the first call, this function should be
idempotent.
Args:
conn: the connection to the database
Returns:
A dict with the fix results ('runs_inspected', 'runs_fixed')
"""
user_version = get_user_version(conn)
if not user_version == 4:
raise RuntimeError('Database of wrong version. Will not apply fix. '
'Expected version 4, found version {user_version}')
no_of_runs_query = "SELECT max(run_id) FROM runs"
no_of_runs = one(atomic_transaction(conn, no_of_runs_query), 'max(run_id)')
no_of_runs = no_of_runs or 0
with atomic(conn) as conn:
pbar = tqdm(range(1, no_of_runs+1))
pbar.set_description("Fixing database")
# collect some metrics
runs_inspected = 0
runs_fixed = 0
old_style_keys = ['paramspecs']
new_style_keys = ['parameters', 'dependencies', 'inferences',
'standalones']
for run_id in pbar:
desc_str = get_run_description(conn, run_id)
desc_ser = json.loads(desc_str)
idps_ser = desc_ser['interdependencies']
if list(idps_ser.keys()) == old_style_keys:
pass
elif list(idps_ser.keys()) == new_style_keys:
old_desc_ser = \
_convert_run_describer_v1_like_dict_to_v0_like_dict(
desc_ser)
json_str = json.dumps(old_desc_ser)
_update_run_description(conn, run_id, json_str)
runs_fixed += 1
else:
raise RuntimeError(f'Invalid runs_description for run_id: '
f'{run_id}')
runs_inspected += 1
return {'runs_inspected': runs_inspected, 'runs_fixed': runs_fixed}
def test_perform_actual_upgrade_5_to_6():
fixpath = os.path.join(fixturepath, 'db_files', 'version5')
db_file = 'empty.db'
dbname_old = os.path.join(fixpath, db_file)
if not os.path.exists(dbname_old):
pytest.skip("No db-file fixtures found. You can generate test db-files"
" using the scripts in the "
"https://github.com/QCoDeS/qcodes_generate_test_db/ repo")
with temporarily_copied_DB(dbname_old, debug=False, version=5) as conn:
perform_db_upgrade_5_to_6(conn)
assert get_user_version(conn) == 6
db_file = 'some_runs.db'
dbname_old = os.path.join(fixpath, db_file)
with temporarily_copied_DB(dbname_old, debug=False, version=5) as conn:
perform_db_upgrade_5_to_6(conn)
assert get_user_version(conn) == 6
no_of_runs_query = "SELECT max(run_id) FROM runs"
no_of_runs = one(
atomic_transaction(conn, no_of_runs_query), 'max(run_id)')
assert no_of_runs == 10
for run_id in range(1, no_of_runs + 1):
json_str = get_run_description(conn, run_id)
deser = json.loads(json_str)
assert deser['version'] == 0
desc = serial.from_json_to_current(json_str)
assert desc._version == 1
def set_run_timestamp(conn: ConnectionPlus, run_id: int) -> None:
"""
Set the run_timestamp for the run with the given run_id. If the
run_timestamp has already been set, a RuntimeError is raised.
"""
query = """
SELECT run_timestamp
FROM runs
WHERE run_id = ?
"""
cmd = """
UPDATE runs
SET run_timestamp = ?
WHERE run_id = ?
"""
with atomic(conn) as conn:
c = conn.cursor()
timestamp = one(c.execute(query, (run_id, )), 'run_timestamp')
if timestamp is not None:
raise RuntimeError('Can not set run_timestamp; it has already '
f'been set to: {timestamp}')
else:
current_time = time.time()
c.execute(cmd, (current_time, run_id))
log.info(f"Set the run_timestamp of run_id {run_id} to "
f"{current_time}")
def load_by_counter(counter: int,
exp_id: int,
conn: Optional[ConnectionPlus] = None) -> DataSet:
"""
Load a dataset given its counter in a given experiment
Lookup is performed in the database file that is specified in the config.
Args:
counter: counter of the dataset within the given experiment
exp_id: id of the experiment where to look for the dataset
conn: connection to the database to load from. If not provided, a
connection to the DB file specified in the config is made
Returns:
dataset of the given counter in the given experiment
"""
conn = conn or connect(get_DB_location())
sql = """
SELECT run_id
FROM
runs
WHERE
result_counter= ? AND
exp_id = ?
"""
c = transaction(conn, sql, counter, exp_id)
run_id = one(c, 'run_id')
d = DataSet(conn=conn, run_id=run_id)
return d
def load_by_counter(counter: int, exp_id: int,
conn: Optional[ConnectionPlus] = None) -> DataSet:
"""
Load a dataset given its counter in a given experiment
Lookup is performed in the database file that is specified in the config.
Note that the `counter` used in this function in not preserved when copying
data to another db file. We recommend using :func:`.load_by_run_spec` which
does not have this issue and is significantly more flexible.
Args:
counter: counter of the dataset within the given experiment
exp_id: id of the experiment where to look for the dataset
conn: connection to the database to load from. If not provided, a
connection to the DB file specified in the config is made
Returns:
:class:`.DataSet` of the given counter in the given experiment
"""
conn = conn or connect(get_DB_location())
sql = """
SELECT run_id
FROM
runs
WHERE
result_counter= ? AND
exp_id = ?
"""
c = transaction(conn, sql, counter, exp_id)
run_id = one(c, 'run_id')
d = DataSet(conn=conn, run_id=run_id)
return d
def get_layout_id(conn: ConnectionPlus, parameter: Union[ParamSpec, str],
run_id: int) -> int:
"""
Get the layout id of a parameter in a given run
Args:
conn: The database connection
parameter: A ParamSpec or the name of the parameter
run_id: The run_id of the run in question
"""
# get the parameter layout id
sql = """
SELECT layout_id FROM layouts
WHERE parameter = ?
and run_id = ?
"""
if isinstance(parameter, ParamSpec):
name = parameter.name
elif isinstance(parameter, str):
name = parameter
else:
raise ValueError('Wrong parameter type, must be ParamSpec or str, '
f'received {type(parameter)}.')
c = atomic_transaction(conn, sql, name, run_id)
res = one(c, 'layout_id')
return res
def upgrade_5_to_6(conn: ConnectionPlus) -> None:
"""
Perform the upgrade from version 5 to version 6.
The upgrade ensures that the runs_description has a top-level entry
called 'version'. Note that version changes of the runs_description will
not be tracked as schema upgrades.
"""
no_of_runs_query = "SELECT max(run_id) FROM runs"
no_of_runs = one(atomic_transaction(conn, no_of_runs_query), 'max(run_id)')
no_of_runs = no_of_runs or 0
# If one run fails, we want the whole upgrade to roll back, hence the
# entire upgrade is one atomic transaction
with atomic(conn) as conn:
pbar = tqdm(range(1, no_of_runs + 1))
pbar.set_description("Upgrading database, version 5 -> 6")
empty_idps_ser = InterDependencies()._to_dict()
for run_id in pbar:
json_str = get_run_description(conn, run_id)
if json_str is None:
new_json = json.dumps({
'version': 0,
'interdependencies': empty_idps_ser
})
else:
ser = json.loads(json_str)
new_ser = {'version': 0} # let 'version' be the first entry
new_ser['interdependencies'] = ser['interdependencies']
new_json = json.dumps(new_ser)
update_run_description(conn, run_id, new_json)
def test_perform_actual_upgrade_6_to_7():
fixpath = os.path.join(fixturepath, 'db_files', 'version6')
db_file = 'some_runs.db'
dbname_old = os.path.join(fixpath, db_file)
if not os.path.exists(dbname_old):
pytest.skip("No db-file fixtures found. You can generate test db-files"
" using the scripts in the "
"https://github.com/QCoDeS/qcodes_generate_test_db/ repo")
with temporarily_copied_DB(dbname_old, debug=False, version=6) as conn:
assert isinstance(conn, ConnectionPlus)
perform_db_upgrade_6_to_7(conn)
assert get_user_version(conn) == 7
no_of_runs_query = "SELECT max(run_id) FROM runs"
no_of_runs = one(atomic_transaction(conn, no_of_runs_query),
'max(run_id)')
assert no_of_runs == 10
columns = atomic_transaction(conn,
"PRAGMA table_info(runs)").fetchall()
col_names = [col['name'] for col in columns]
assert 'captured_run_id' in col_names
assert 'captured_counter' in col_names
for run_id in range(1, no_of_runs + 1):
ds1 = load_by_id(run_id, conn)
ds2 = load_by_run_spec(captured_run_id=run_id, conn=conn)
assert ds1.the_same_dataset_as(ds2)
assert ds1.run_id == run_id
assert ds1.run_id == ds1.captured_run_id
assert ds2.run_id == run_id
assert ds2.run_id == ds2.captured_run_id
exp_id = 1
for counter in range(1, no_of_runs + 1):
ds1 = load_by_counter(counter, exp_id, conn)
ds2 = load_by_run_spec(captured_counter=counter, conn=conn)
assert ds1.the_same_dataset_as(ds2)
assert ds1.counter == counter
assert ds1.counter == ds1.captured_counter
assert ds2.counter == counter
assert ds2.counter == ds2.captured_counter
def fix_version_4a_run_description_bug(conn: ConnectionPlus) -> Dict[str, int]:
"""
Fix function to fix a bug where the RunDescriber accidentally wrote itself
to string using the (new) InterDependencies_ object instead of the (old)
InterDependencies object. After the first run, this function should be
idempotent.
Args:
conn: the connection to the database
Returns:
A dict with the fix results ('runs_inspected', 'runs_fixed')
"""
user_version = get_user_version(conn)
if not user_version == 4:
raise RuntimeError('Database of wrong version. Will not apply fix. '
'Expected version 4, found version {user_version}')
no_of_runs_query = "SELECT max(run_id) FROM runs"
no_of_runs = one(atomic_transaction(conn, no_of_runs_query), 'max(run_id)')
no_of_runs = no_of_runs or 0
with atomic(conn) as conn:
pbar = tqdm(range(1, no_of_runs + 1))
pbar.set_description("Fixing database")
# collect some metrics
runs_inspected = 0
runs_fixed = 0
for run_id in pbar:
desc_str = get_run_description(conn, run_id)
desc_ser = json.loads(desc_str)
idps_ser = desc_ser['interdependencies']
if RunDescriber._is_description_old_style(idps_ser):
pass
else:
new_desc = RunDescriber.from_json(desc_str)
update_run_description(conn, run_id, new_desc.to_json())
runs_fixed += 1
runs_inspected += 1
return {'runs_inspected': runs_inspected, 'runs_fixed': runs_fixed}
def get_last_run(conn: ConnectionPlus, exp_id: int) -> Optional[int]:
"""
Get run_id of the last run in experiment with exp_id
Args:
conn: connection to use for the query
exp_id: id of the experiment to look inside
Returns:
the integer id of the last run or None if there are not runs in the
experiment
"""
query = """
SELECT run_id, max(run_timestamp), exp_id
FROM runs
WHERE exp_id = ?;
"""
c = atomic_transaction(conn, query, exp_id)
return one(c, 'run_id')
def perform_db_upgrade_0_to_1(conn: ConnectionPlus) -> None:
"""
Perform the upgrade from version 0 to version 1
Add a GUID column to the runs table and assign guids for all existing runs
"""
sql = "SELECT name FROM sqlite_master WHERE type='table' AND name='runs'"
cur = atomic_transaction(conn, sql)
n_run_tables = len(cur.fetchall())
if n_run_tables == 1:
with atomic(conn) as conn:
sql = "ALTER TABLE runs ADD COLUMN guid TEXT"
transaction(conn, sql)
# now assign GUIDs to existing runs
cur = transaction(conn, 'SELECT run_id FROM runs')
run_ids = [r[0] for r in many_many(cur, 'run_id')]
pbar = tqdm(range(1, len(run_ids) + 1), file=sys.stdout)
pbar.set_description("Upgrading database; v0 -> v1")
for run_id in pbar:
query = f"""
SELECT run_timestamp
FROM runs
WHERE run_id == {run_id}
"""
cur = transaction(conn, query)
timestamp = one(cur, 'run_timestamp')
timeint = int(np.round(timestamp * 1000))
sql = f"""
UPDATE runs
SET guid = ?
where run_id == {run_id}
"""
sampleint = 3736062718 # 'deafcafe'
cur.execute(
sql,
(generate_guid(timeint=timeint, sampleint=sampleint), ))
else:
raise RuntimeError(f"found {n_run_tables} runs tables expected 1")
def get_runid_from_expid_and_counter(conn: ConnectionPlus, exp_id: int,
counter: int) -> int:
"""
Get the run_id of a run in the specified experiment with the specified
counter
Args:
conn: connection to the database
exp_id: the exp_id of the experiment containing the run
counter: the intra-experiment run counter of that run
"""
sql = """
SELECT run_id
FROM runs
WHERE result_counter= ? AND
exp_id = ?
"""
c = transaction(conn, sql, counter, exp_id)
run_id = one(c, 'run_id')
return run_id
def add_parameter(conn: ConnectionPlus, formatted_name: str,
*parameter: ParamSpec):
"""
Add parameters to the dataset
This will update the layouts and dependencies tables
NOTE: two parameters with the same name are not allowed
Args:
conn: the connection to the sqlite database
formatted_name: name of the table
parameter: the list of ParamSpecs for parameters to add
"""
with atomic(conn) as conn:
p_names = []
for p in parameter:
insert_column(conn, formatted_name, p.name, p.type)
p_names.append(p.name)
# get old parameters column from run table
sql = f"""
SELECT parameters FROM runs
WHERE result_table_name=?
"""
with atomic(conn) as conn:
c = transaction(conn, sql, formatted_name)
old_parameters = one(c, 'parameters')
if old_parameters:
new_parameters = ",".join([old_parameters] + p_names)
else:
new_parameters = ",".join(p_names)
sql = "UPDATE runs SET parameters=? WHERE result_table_name=?"
with atomic(conn) as conn:
transaction(conn, sql, new_parameters, formatted_name)
# Update the layouts table
c = _add_parameters_to_layout_and_deps(conn, formatted_name,
*parameter)
def upgrade_2_to_3(conn: ConnectionPlus) -> None:
"""
Perform the upgrade from version 2 to version 3
Insert a new column, run_description, to the runs table and fill it out
for exisitng runs with information retrieved from the layouts and
dependencies tables represented as the json output of a RunDescriber
object
"""
no_of_runs_query = "SELECT max(run_id) FROM runs"
no_of_runs = one(atomic_transaction(conn, no_of_runs_query), 'max(run_id)')
no_of_runs = no_of_runs or 0
# If one run fails, we want the whole upgrade to roll back, hence the
# entire upgrade is one atomic transaction
with atomic(conn) as conn:
sql = "ALTER TABLE runs ADD COLUMN run_description TEXT"
transaction(conn, sql)
result_tables = _2to3_get_result_tables(conn)
layout_ids_all = _2to3_get_layout_ids(conn)
indeps_all = _2to3_get_indeps(conn)
deps_all = _2to3_get_deps(conn)
layouts = _2to3_get_layouts(conn)
dependencies = _2to3_get_dependencies(conn)
pbar = tqdm(range(1, no_of_runs + 1))
pbar.set_description("Upgrading database")
for run_id in pbar:
if run_id in layout_ids_all:
result_table_name = result_tables[run_id]
layout_ids = list(layout_ids_all[run_id])
if run_id in indeps_all:
independents = tuple(indeps_all[run_id])
else:
independents = ()
if run_id in deps_all:
dependents = tuple(deps_all[run_id])
else:
dependents = ()
paramspecs = _2to3_get_paramspecs(conn, layout_ids, layouts,
dependencies, dependents,
independents,
result_table_name)
interdeps = InterDependencies(*paramspecs.values())
desc_dict = {'interdependencies': interdeps._to_dict()}
json_str = json.dumps(desc_dict)
else:
desc_dict = {
'interdependencies': InterDependencies()._to_dict()
}
json_str = json.dumps(desc_dict)
sql = f"""
UPDATE runs
SET run_description = ?
WHERE run_id == ?
"""
cur = conn.cursor()
cur.execute(sql, (json_str, run_id))
log.debug(f"Upgrade in transition, run number {run_id}: OK")
def number_of_results(self):
sql = f'SELECT COUNT(*) FROM "{self.table_name}"'
cursor = atomic_transaction(self.conn, sql)
return one(cursor, 'COUNT(*)')
def get_paramspec(conn: ConnectionPlus, run_id: int,
param_name: str) -> ParamSpec:
"""
Get the ParamSpec object for the given parameter name
in the given run
Args:
conn: Connection to the database
run_id: The run id
param_name: The name of the parameter
"""
# get table name
sql = f"""
SELECT result_table_name FROM runs WHERE run_id = {run_id}
"""
c = conn.execute(sql)
result_table_name = one(c, 'result_table_name')
# get the data type
sql = f"""
PRAGMA TABLE_INFO("{result_table_name}")
"""
c = conn.execute(sql)
for row in c.fetchall():
if row['name'] == param_name:
param_type = row['type']
break
# get everything else
sql = f"""
SELECT * FROM layouts
WHERE parameter="{param_name}" and run_id={run_id}
"""
c = conn.execute(sql)
resp = many(c, 'layout_id', 'run_id', 'parameter', 'label', 'unit',
'inferred_from')
(layout_id, _, _, label, unit, inferred_from_string) = resp
if inferred_from_string:
inferred_from = inferred_from_string.split(', ')
else:
inferred_from = []
deps = get_dependencies(conn, layout_id)
depends_on: Optional[List[str]]
if len(deps) == 0:
depends_on = None
else:
dps: List[int] = [dp[0] for dp in deps]
ax_nums: List[int] = [dp[1] for dp in deps]
depends_on = []
for _, dp in sorted(zip(ax_nums, dps)):
sql = f"""
SELECT parameter FROM layouts WHERE layout_id = {dp}
"""
c = conn.execute(sql)
depends_on.append(one(c, 'parameter'))
parspec = ParamSpec(param_name, param_type, label, unit, inferred_from,
depends_on)
return parspec
def test_perform_actual_upgrade_6_to_newest_add_new_data():
"""
Insert new runs on top of existing runs upgraded and verify that they
get the correct captured_run_id and captured_counter
"""
from qcodes.dataset.measurements import Measurement
from qcodes.instrument.parameter import Parameter
import numpy as np
fixpath = os.path.join(fixturepath, 'db_files', 'version6')
db_file = 'some_runs.db'
dbname_old = os.path.join(fixpath, db_file)
if not os.path.exists(dbname_old):
pytest.skip("No db-file fixtures found. You can generate test db-files"
" using the scripts in the "
"https://github.com/QCoDeS/qcodes_generate_test_db/ repo")
with temporarily_copied_DB(dbname_old, debug=False, version=6) as conn:
assert isinstance(conn, ConnectionPlus)
perform_db_upgrade(conn)
assert get_user_version(conn) >= 7
no_of_runs_query = "SELECT max(run_id) FROM runs"
no_of_runs = one(
atomic_transaction(conn, no_of_runs_query), 'max(run_id)')
# Now let's insert new runs and ensure that they also get
# captured_run_id assigned.
params = []
for n in range(5):
params.append(Parameter(f'p{n}', label=f'Parameter {n}',
unit=f'unit {n}', set_cmd=None,
get_cmd=None))
# Set up an experiment
exp = new_experiment('some-exp', 'some-sample', conn=conn)
meas = Measurement(exp=exp)
meas.register_parameter(params[0])
meas.register_parameter(params[1])
meas.register_parameter(params[2], basis=(params[0],))
meas.register_parameter(params[3], basis=(params[1],))
meas.register_parameter(params[4], setpoints=(params[2], params[3]))
# Make a number of identical runs
for _ in range(10):
with meas.run() as datasaver:
for x in np.random.rand(10):
for y in np.random.rand(10):
z = np.random.rand()
datasaver.add_result((params[0], 0),
(params[1], 1),
(params[2], x),
(params[3], y),
(params[4], z))
no_of_runs_new = one(
atomic_transaction(conn, no_of_runs_query), 'max(run_id)')
assert no_of_runs_new == 20
# check that run_id is equivalent to captured_run_id for new
# runs
for run_id in range(no_of_runs, no_of_runs_new + 1):
ds1 = load_by_id(run_id, conn)
ds2 = load_by_run_spec(captured_run_id=run_id, conn=conn)
assert ds1.the_same_dataset_as(ds2)
assert ds1.run_id == run_id
assert ds1.run_id == ds1.captured_run_id
assert ds2.run_id == run_id
assert ds2.run_id == ds2.captured_run_id
# we are creating a new experiment into a db with one exp so:
exp_id = 2
# check that counter is equivalent to captured_counter for new
# runs
for counter in range(1, no_of_runs_new - no_of_runs + 1):
ds1 = load_by_counter(counter, exp_id, conn)
# giving only the counter is not unique since we have 2 experiments
with pytest.raises(NameError, match="More than one"
" matching dataset"):
load_by_run_spec(captured_counter=counter, conn=conn)
# however we can supply counter and experiment
ds2 = load_by_run_spec(captured_counter=counter,
experiment_name='some-exp',
conn=conn)
assert ds1.the_same_dataset_as(ds2)
assert ds1.counter == counter
assert ds1.counter == ds1.captured_counter
assert ds2.counter == counter
assert ds2.counter == ds2.captured_counter
def test_perform_upgrade_v3_to_v4():
"""
Test that a db upgrade from v2 to v4 works correctly.
"""
v3fixpath = os.path.join(fixturepath, 'db_files', 'version3')
dbname_old = os.path.join(v3fixpath, 'some_runs_upgraded_2.db')
if not os.path.exists(dbname_old):
pytest.skip("No db-file fixtures found. You can generate test db-files"
" using the scripts in the "
"https://github.com/QCoDeS/qcodes_generate_test_db/ repo")
with temporarily_copied_DB(dbname_old, debug=False, version=3) as conn:
assert get_user_version(conn) == 3
sql = f"""
SELECT run_description
FROM runs
WHERE run_id == 1
"""
perform_db_upgrade_3_to_4(conn)
c = atomic_transaction(conn, sql)
json_str = one(c, 'run_description')
unversioned_dict = json.loads(json_str)
idp = InterDependencies._from_dict(
unversioned_dict['interdependencies'])
assert isinstance(idp, InterDependencies)
p0 = [p for p in idp.paramspecs if p.name == 'p0'][0]
assert p0.depends_on == ''
assert p0.depends_on_ == []
assert p0.inferred_from == ''
assert p0.inferred_from_ == []
assert p0.label == "Parameter 0"
assert p0.unit == "unit 0"
p1 = [p for p in idp.paramspecs if p.name == 'p1'][0]
assert p1.depends_on == ''
assert p1.depends_on_ == []
assert p1.inferred_from == ''
assert p1.inferred_from_ == []
assert p1.label == "Parameter 1"
assert p1.unit == "unit 1"
p2 = [p for p in idp.paramspecs if p.name == 'p2'][0]
assert p2.depends_on == ''
assert p2.depends_on_ == []
assert p2.inferred_from == 'p0'
assert p2.inferred_from_ == ['p0']
assert p2.label == "Parameter 2"
assert p2.unit == "unit 2"
p3 = [p for p in idp.paramspecs if p.name == 'p3'][0]
assert p3.depends_on == ''
assert p3.depends_on_ == []
assert p3.inferred_from == 'p1, p0'
assert p3.inferred_from_ == ['p1', 'p0']
assert p3.label == "Parameter 3"
assert p3.unit == "unit 3"
p4 = [p for p in idp.paramspecs if p.name == 'p4'][0]
assert p4.depends_on == 'p2, p3'
assert p4.depends_on_ == ['p2', 'p3']
assert p4.inferred_from == ''
assert p4.inferred_from_ == []
assert p4.label == "Parameter 4"
assert p4.unit == "unit 4"
p5 = [p for p in idp.paramspecs if p.name == 'p5'][0]
assert p5.depends_on == ''
assert p5.depends_on_ == []
assert p5.inferred_from == 'p0'
assert p5.inferred_from_ == ['p0']
assert p5.label == "Parameter 5"
assert p5.unit == "unit 5"
def test_perform_actual_upgrade_2_to_3_some_runs():
v2fixpath = os.path.join(fixturepath, 'db_files', 'version2')
dbname_old = os.path.join(v2fixpath, 'some_runs.db')
if not os.path.exists(dbname_old):
pytest.skip("No db-file fixtures found. You can generate test db-files"
" using the scripts in the"
"https://github.com/QCoDeS/qcodes_generate_test_db/ repo")
with temporarily_copied_DB(dbname_old, debug=False, version=2) as conn:
assert get_user_version(conn) == 2
perform_db_upgrade_2_to_3(conn)
desc_query = 'SELECT run_description FROM runs'
c = atomic_transaction(conn, desc_query)
assert len(c.fetchall()) == 10
# retrieve the json string and recreate the object
sql = f"""
SELECT run_description
FROM runs
WHERE run_id == 1
"""
c = atomic_transaction(conn, sql)
json_str = one(c, 'run_description')
unversioned_dict = json.loads(json_str)
idp = InterDependencies._from_dict(
unversioned_dict['interdependencies'])
assert isinstance(idp, InterDependencies)
# here we verify that the dependencies encoded in
# tests/dataset/legacy_DB_generation/generate_version_2.py
# are recovered
p0 = [p for p in idp.paramspecs if p.name == 'p0'][0]
assert p0.depends_on == ''
assert p0.depends_on_ == []
assert p0.inferred_from == ''
assert p0.inferred_from_ == []
assert p0.label == "Parameter 0"
assert p0.unit == "unit 0"
p1 = [p for p in idp.paramspecs if p.name == 'p1'][0]
assert p1.depends_on == ''
assert p1.depends_on_ == []
assert p1.inferred_from == ''
assert p1.inferred_from_ == []
assert p1.label == "Parameter 1"
assert p1.unit == "unit 1"
p2 = [p for p in idp.paramspecs if p.name == 'p2'][0]
assert p2.depends_on == ''
assert p2.depends_on_ == []
assert p2.inferred_from == 'p0'
assert p2.inferred_from_ == ['p0']
assert p2.label == "Parameter 2"
assert p2.unit == "unit 2"
p3 = [p for p in idp.paramspecs if p.name == 'p3'][0]
assert p3.depends_on == ''
assert p3.depends_on_ == []
assert p3.inferred_from == 'p1, p0'
assert p3.inferred_from_ == ['p1', 'p0']
assert p3.label == "Parameter 3"
assert p3.unit == "unit 3"
p4 = [p for p in idp.paramspecs if p.name == 'p4'][0]
assert p4.depends_on == 'p2, p3'
assert p4.depends_on_ == ['p2', 'p3']
assert p4.inferred_from == ''
assert p4.inferred_from_ == []
assert p4.label == "Parameter 4"
assert p4.unit == "unit 4"
p5 = [p for p in idp.paramspecs if p.name == 'p5'][0]
assert p5.depends_on == ''
assert p5.depends_on_ == []
assert p5.inferred_from == 'p0'
assert p5.inferred_from_ == ['p0']
assert p5.label == "Parameter 5"
assert p5.unit == "unit 5"
def upgrade_3_to_4(conn: ConnectionPlus) -> None:
"""
Perform the upgrade from version 3 to version 4. This really
repeats the version 3 upgrade as it originally had two bugs in
the inferred annotation. inferred_from was passed incorrectly
resulting in the parameter being marked inferred_from for each char
in the inferred_from variable and inferred_from was not handled
correctly for parameters that were neither dependencies nor dependent on
other parameters. Both have since been fixed so rerun the upgrade.
"""
no_of_runs_query = "SELECT max(run_id) FROM runs"
no_of_runs = one(atomic_transaction(conn, no_of_runs_query), 'max(run_id)')
no_of_runs = no_of_runs or 0
# If one run fails, we want the whole upgrade to roll back, hence the
# entire upgrade is one atomic transaction
with atomic(conn) as conn:
result_tables = _2to3_get_result_tables(conn)
layout_ids_all = _2to3_get_layout_ids(conn)
indeps_all = _2to3_get_indeps(conn)
deps_all = _2to3_get_deps(conn)
layouts = _2to3_get_layouts(conn)
dependencies = _2to3_get_dependencies(conn)
pbar = tqdm(range(1, no_of_runs + 1), file=sys.stdout)
pbar.set_description("Upgrading database; v3 -> v4")
for run_id in pbar:
if run_id in layout_ids_all:
result_table_name = result_tables[run_id]
layout_ids = list(layout_ids_all[run_id])
if run_id in indeps_all:
independents = tuple(indeps_all[run_id])
else:
independents = ()
if run_id in deps_all:
dependents = tuple(deps_all[run_id])
else:
dependents = ()
paramspecs = _2to3_get_paramspecs(conn, layout_ids, layouts,
dependencies, dependents,
independents,
result_table_name)
interdeps = InterDependencies(*paramspecs.values())
desc_dict = {'interdependencies': interdeps._to_dict()}
json_str = json.dumps(desc_dict)
else:
desc_dict = {
'interdependencies': InterDependencies()._to_dict()
}
json_str = json.dumps(desc_dict)
sql = f"""
UPDATE runs
SET run_description = ?
WHERE run_id == ?
"""
cur = conn.cursor()
cur.execute(sql, (json_str, run_id))
log.debug(f"Upgrade in transition, run number {run_id}: OK")
def test_perform_upgrade_v2_v3_to_v4_fixes():
"""
Test that a db that was upgraded from v2 to v3 with a buggy
version will be corrected when upgraded to v4.
"""
v3fixpath = os.path.join(fixturepath, 'db_files', 'version3')
dbname_old = os.path.join(v3fixpath, 'some_runs_upgraded_2.db')
if not os.path.exists(dbname_old):
pytest.skip("No db-file fixtures found. You can generate test db-files"
" using the scripts in the"
" https://github.com/QCoDeS/qcodes_generate_test_db/ repo")
with temporarily_copied_DB(dbname_old, debug=False, version=3) as conn:
assert get_user_version(conn) == 3
sql = f"""
SELECT run_description
FROM runs
WHERE run_id == 1
"""
c = atomic_transaction(conn, sql)
json_str = one(c, 'run_description')
desc = RunDescriber.from_json(json_str)
idp = desc.interdeps
assert isinstance(idp, InterDependencies)
p0 = [p for p in idp.paramspecs if p.name == 'p0'][0]
assert p0.depends_on == ''
assert p0.depends_on_ == []
assert p0.inferred_from == ''
assert p0.inferred_from_ == []
assert p0.label == "Parameter 0"
assert p0.unit == "unit 0"
p1 = [p for p in idp.paramspecs if p.name == 'p1'][0]
assert p1.depends_on == ''
assert p1.depends_on_ == []
assert p1.inferred_from == ''
assert p1.inferred_from_ == []
assert p1.label == "Parameter 1"
assert p1.unit == "unit 1"
p2 = [p for p in idp.paramspecs if p.name == 'p2'][0]
assert p2.depends_on == ''
assert p2.depends_on_ == []
# the 2 lines below are wrong due to the incorrect upgrade from
# db version 2 to 3
assert p2.inferred_from == 'p, 0'
assert p2.inferred_from_ == ['p', '0']
assert p2.label == "Parameter 2"
assert p2.unit == "unit 2"
p3 = [p for p in idp.paramspecs if p.name == 'p3'][0]
assert p3.depends_on == ''
assert p3.depends_on_ == []
# the 2 lines below are wrong due to the incorrect upgrade from
# db version 2 to 3
assert p3.inferred_from == 'p, 1, ,, , p, 0'
assert p3.inferred_from_ == ['p', '1', ',', ' ', 'p', '0']
assert p3.label == "Parameter 3"
assert p3.unit == "unit 3"
p4 = [p for p in idp.paramspecs if p.name == 'p4'][0]
assert p4.depends_on == 'p2, p3'
assert p4.depends_on_ == ['p2', 'p3']
assert p4.inferred_from == ''
assert p4.inferred_from_ == []
assert p4.label == "Parameter 4"
assert p4.unit == "unit 4"
p5 = [p for p in idp.paramspecs if p.name == 'p5'][0]
assert p5.depends_on == ''
assert p5.depends_on_ == []
# the 2 lines below are wrong due to the incorrect upgrade from
# db version 2 to 3. Here the interdep is missing
assert p5.inferred_from == ''
assert p5.inferred_from_ == []
assert p5.label == "Parameter 5"
assert p5.unit == "unit 5"
perform_db_upgrade_3_to_4(conn)
c = atomic_transaction(conn, sql)
json_str = one(c, 'run_description')
desc = RunDescriber.from_json(json_str)
idp = desc.interdeps
assert isinstance(idp, InterDependencies)
p0 = [p for p in idp.paramspecs if p.name == 'p0'][0]
assert p0.depends_on == ''
assert p0.depends_on_ == []
assert p0.inferred_from == ''
assert p0.inferred_from_ == []
assert p0.label == "Parameter 0"
assert p0.unit == "unit 0"
p1 = [p for p in idp.paramspecs if p.name == 'p1'][0]
assert p1.depends_on == ''
assert p1.depends_on_ == []
assert p1.inferred_from == ''
assert p1.inferred_from_ == []
assert p1.label == "Parameter 1"
assert p1.unit == "unit 1"
p2 = [p for p in idp.paramspecs if p.name == 'p2'][0]
assert p2.depends_on == ''
assert p2.depends_on_ == []
assert p2.inferred_from == 'p0'
assert p2.inferred_from_ == ['p0']
assert p2.label == "Parameter 2"
assert p2.unit == "unit 2"
p3 = [p for p in idp.paramspecs if p.name == 'p3'][0]
assert p3.depends_on == ''
assert p3.depends_on_ == []
assert p3.inferred_from == 'p1, p0'
assert p3.inferred_from_ == ['p1', 'p0']
assert p3.label == "Parameter 3"
assert p3.unit == "unit 3"
p4 = [p for p in idp.paramspecs if p.name == 'p4'][0]
assert p4.depends_on == 'p2, p3'
assert p4.depends_on_ == ['p2', 'p3']
assert p4.inferred_from == ''
assert p4.inferred_from_ == []
assert p4.label == "Parameter 4"
assert p4.unit == "unit 4"
p5 = [p for p in idp.paramspecs if p.name == 'p5'][0]
assert p5.depends_on == ''
assert p5.depends_on_ == []
assert p5.inferred_from == 'p0'
assert p5.inferred_from_ == ['p0']
assert p5.label == "Parameter 5"
assert p5.unit == "unit 5"
def get_user_version(conn: ConnectionPlus) -> int:
curr = atomic_transaction(conn, 'PRAGMA user_version')
res = one(curr, 0)
return res
def get_parameter_data(conn: ConnectionPlus,
table_name: str,
columns: Sequence[str] = (),
start: Optional[int] = None,
end: Optional[int] = None) -> \
Dict[str, Dict[str, np.ndarray]]:
"""
Get data for one or more parameters and its dependencies. The data
is returned as numpy arrays within 2 layers of nested dicts. The keys of
the outermost dict are the requested parameters and the keys of the second
level are the loaded parameters (requested parameter followed by its
dependencies). Start and End allows one to specify a range of rows to
be returned (1-based indexing, both ends are included). The range filter
is applied AFTER the NULL values have been filtered out.
Be aware that different parameters that are independent of each other
may return a different number of rows.
Note that this assumes that all array type parameters have the same length.
This should always be the case for a parameter and its dependencies.
Note that all numeric data will at the moment be returned as floating point
values.
Args:
conn: database connection
table_name: name of the table
columns: list of columns. If no columns are provided, all parameters
are returned.
start: start of range; if None, then starts from the top of the table
end: end of range; if None, then ends at the bottom of the table
"""
sql = """
SELECT run_id FROM runs WHERE result_table_name = ?
"""
c = atomic_transaction(conn, sql, table_name)
run_id = one(c, 'run_id')
output = {}
if len(columns) == 0:
columns = get_non_dependencies(conn, run_id)
# loop over all the requested parameters
for output_param in columns:
# find all the dependencies of this param
paramspecs = get_parameter_dependencies(conn, output_param, run_id)
param_names = [param.name for param in paramspecs]
types = [param.type for param in paramspecs]
res = get_parameter_tree_values(conn,
table_name,
output_param,
*param_names[1:],
start=start,
end=end)
# if we have array type parameters expand all other parameters
# to arrays
if 'array' in types and ('numeric' in types or 'text' in types
or 'complex' in types):
first_array_element = types.index('array')
numeric_elms = [i for i, x in enumerate(types) if x == "numeric"]
complex_elms = [i for i, x in enumerate(types) if x == 'complex']
text_elms = [i for i, x in enumerate(types) if x == "text"]
for row in res:
for element in numeric_elms:
row[element] = np.full_like(row[first_array_element],
row[element],
dtype=np.float)
# todo should we handle int/float types here
# we would in practice have to perform another
# loop to check that all elements of a given can be cast to
# int without loosing precision before choosing an integer
# representation of the array
for element in complex_elms:
row[element] = np.full_like(row[first_array_element],
row[element],
dtype=np.complex)
for element in text_elms:
strlen = len(row[element])
row[element] = np.full_like(row[first_array_element],
row[element],
dtype=f'U{strlen}')
# Benchmarking shows that transposing the data with python types is
# faster than transposing the data using np.array.transpose
res_t = map(list, zip(*res))
output[output_param] = {
name: np.array(column_data)
for name, column_data in zip(param_names, res_t)
}
return output
def get_setpoints(conn: ConnectionPlus, table_name: str,
param_name: str) -> Dict[str, List[List[Any]]]:
"""
Get the setpoints for a given dependent parameter
Args:
conn: Connection to the database
table_name: Name of the table that holds the data
param_name: Name of the parameter to get the setpoints of
Returns:
A list of returned setpoint values. Each setpoint return value
is a list of lists of Any. The first list is a list of run points,
the second list is a list of parameter values.
"""
# TODO: We do this in no less than 5 table lookups, surely
# this number can be reduced
# get run_id
sql = """
SELECT run_id FROM runs WHERE result_table_name = ?
"""
c = atomic_transaction(conn, sql, table_name)
run_id = one(c, 'run_id')
# get the parameter layout id
sql = """
SELECT layout_id FROM layouts
WHERE parameter = ?
and run_id = ?
"""
c = atomic_transaction(conn, sql, param_name, run_id)
layout_id = one(c, 'layout_id')
# get the setpoint layout ids
sql = """
SELECT independent FROM dependencies
WHERE dependent = ?
"""
c = atomic_transaction(conn, sql, layout_id)
indeps = many_many(c, 'independent')
indeps = [idp[0] for idp in indeps]
# get the setpoint names
sql = f"""
SELECT parameter FROM layouts WHERE layout_id
IN {str(indeps).replace('[', '(').replace(']', ')')}
"""
c = atomic_transaction(conn, sql)
setpoint_names_temp = many_many(c, 'parameter')
setpoint_names = [spn[0] for spn in setpoint_names_temp]
setpoint_names = cast(List[str], setpoint_names)
# get the actual setpoint data
output: Dict[str, List[List[Any]]] = {}
for sp_name in setpoint_names:
sql = f"""
SELECT {sp_name}
FROM "{table_name}"
WHERE {param_name} IS NOT NULL
"""
c = atomic_transaction(conn, sql)
sps = many_many(c, sp_name)
output[sp_name] = sps
return output