def perform_db_upgrade_1_to_2(conn: ConnectionPlus) -> None:
"""
Perform the upgrade from version 1 to version 2
Add two indeces on the runs table, one for exp_id and one for GUID
"""
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:
_IX_runs_exp_id = """
CREATE INDEX
IF NOT EXISTS IX_runs_exp_id
ON runs (exp_id DESC)
"""
_IX_runs_guid = """
CREATE INDEX
IF NOT EXISTS IX_runs_guid
ON runs (guid DESC)
"""
with atomic(conn) as conn:
transaction(conn, _IX_runs_exp_id)
transaction(conn, _IX_runs_guid)
else:
raise RuntimeError(f"found {n_run_tables} runs tables expected 1")
def perform_db_upgrade_1_to_2(conn: ConnectionPlus) -> None:
"""
Perform the upgrade from version 1 to version 2
Add two indeces on the runs table, one for exp_id and one for GUID
"""
sql = "SELECT name FROM sqlite_master WHERE type='table' AND name='runs'"
cur = atomic_transaction(conn, sql)
n_run_tables = len(cur.fetchall())
pbar = tqdm(range(1), file=sys.stdout)
pbar.set_description("Upgrading database; v1 -> v2")
if n_run_tables == 1:
_IX_runs_exp_id = """
CREATE INDEX
IF NOT EXISTS IX_runs_exp_id
ON runs (exp_id DESC)
"""
_IX_runs_guid = """
CREATE INDEX
IF NOT EXISTS IX_runs_guid
ON runs (guid DESC)
"""
with atomic(conn) as conn:
# iterate through the pbar for the sake of the side effect; it
# prints that the database is being upgraded
for _ in pbar:
transaction(conn, _IX_runs_exp_id)
transaction(conn, _IX_runs_guid)
else:
raise RuntimeError(f"found {n_run_tables} runs tables expected 1")
def insert_column(conn: ConnectionPlus,
table: str,
name: str,
paramtype: Optional[str] = None) -> None:
"""Insert new column to a table
Args:
conn: database connection
table: destination for the insertion
name: column name
type: sqlite type of the column
"""
# first check that the column is not already there
# and do nothing if it is
query = f'PRAGMA TABLE_INFO("{table}");'
cur = atomic_transaction(conn, query)
columns = many_many(cur, "name")
if name in [col[0] for col in columns]:
return
with atomic(conn) as conn:
if paramtype:
transaction(
conn, f'ALTER TABLE "{table}" ADD COLUMN "{name}" '
f'{paramtype}')
else:
transaction(conn, f'ALTER TABLE "{table}" ADD COLUMN "{name}"')
def load_experiment_by_name(
name: str,
sample: Optional[str] = None,
conn: Optional[ConnectionPlus] = None) -> Experiment:
"""
Try to load experiment with the specified name.
Nothing stops you from having many experiments with the same name and
sample_name. In that case this won't work. And warn you.
Args:
name: the name of the experiment
sample: the name of the sample
conn: connection to the database. If not supplied, a new connection
to the DB file specified in the config is made
Returns:
the requested experiment
Raises:
ValueError if the name is not unique and sample name is None.
"""
conn = conn or connect(get_DB_location())
if sample:
sql = """
SELECT
*
FROM
experiments
WHERE
sample_name = ? AND
name = ?
"""
c = transaction(conn, sql, sample, name)
else:
sql = """
SELECT
*
FROM
experiments
WHERE
name = ?
"""
c = transaction(conn, sql, name)
rows = c.fetchall()
if len(rows) == 0:
raise ValueError("Experiment not found")
elif len(rows) > 1:
_repr = []
for row in rows:
s = (f"exp_id:{row['exp_id']} ({row['name']}-{row['sample_name']})"
f" started at ({row['start_time']})")
_repr.append(s)
_repr_str = "\n".join(_repr)
raise ValueError(f"Many experiments matching your request"
f" found:\n{_repr_str}")
else:
e = Experiment(exp_id=rows[0]['exp_id'], conn=conn)
return e
def test_atomic_raises(experiment):
conn = experiment.conn
bad_sql = '""'
with pytest.raises(RuntimeError) as excinfo:
with mut_conn.atomic(conn):
mut_conn.transaction(conn, bad_sql)
assert error_caused_by(excinfo, "syntax error")
def test_atomic_creation(experiment):
""""
Test that dataset creation is atomic. Test for
https://github.com/QCoDeS/Qcodes/issues/1444
"""
def just_throw(*args):
raise RuntimeError("This breaks adding metadata")
# first we patch add_data_to_dynamic_columns to throw an exception
# if create_data is not atomic this would create a partial
# run in the db. Causing the next create_run to fail
with patch("qcodes.dataset.sqlite.queries.add_data_to_dynamic_columns",
new=just_throw):
x = ParamSpec("x", "numeric")
t = ParamSpec("t", "numeric")
y = ParamSpec("y", "numeric", depends_on=["x", "t"])
with pytest.raises(
RuntimeError,
match="Rolling back due to unhandled exception") as e:
mut_queries.create_run(
experiment.conn,
experiment.exp_id,
name="testrun",
guid=generate_guid(),
parameters=[x, t, y],
metadata={"a": 1},
)
assert error_caused_by(e, "This breaks adding metadata")
# since we are starting from an empty database and the above transaction
# should be rolled back there should be no runs in the run table
runs = mut_conn.transaction(experiment.conn,
'SELECT run_id FROM runs').fetchall()
assert len(runs) == 0
with shadow_conn(experiment.path_to_db) as new_conn:
runs = mut_conn.transaction(new_conn,
'SELECT run_id FROM runs').fetchall()
assert len(runs) == 0
# if the above was not correctly rolled back we
# expect the next creation of a run to fail
mut_queries.create_run(experiment.conn,
experiment.exp_id,
name='testrun',
guid=generate_guid(),
parameters=[x, t, y],
metadata={'a': 1})
runs = mut_conn.transaction(experiment.conn,
'SELECT run_id FROM runs').fetchall()
assert len(runs) == 1
with shadow_conn(experiment.path_to_db) as new_conn:
runs = mut_conn.transaction(new_conn,
'SELECT run_id FROM runs').fetchall()
assert len(runs) == 1
def test_atomic_raises(experiment):
conn = experiment.conn
bad_sql = '""'
# it seems that the type of error raised differs between python versions
# 3.6.0 (OperationalError) and 3.6.3 (RuntimeError)
# -strange, huh?
with pytest.raises((OperationalError, RuntimeError)):
with mut_conn.atomic(conn):
mut_conn.transaction(conn, bad_sql)
def remove_trigger(conn: ConnectionPlus, trigger_id: str) -> None:
"""
Removes a trigger with a given id if it exists.
Note that this transaction is not atomic!
Args:
conn: database connection object
name: id of the trigger
"""
transaction(conn, f"DROP TRIGGER IF EXISTS {trigger_id};")
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 get_runs(conn):
""" Get a list of runs.
Args:
conn: database connection
exp_id: id of the experiment to look inside.
if None all experiments will be included
Returns:
list of rows
"""
table_columns = [
"run_id",
"guid",
"exp_id",
"run_description",
"run_timestamp",
"completed_timestamp",
"result_table_name",
]
table_columns_str = ', '.join(table_columns)
with atomic(conn) as conn:
sql = f"SELECT {table_columns_str} FROM runs"
c = transaction(conn, sql)
return c.fetchall()
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 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 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 perform_db_upgrade_6_to_7(conn: ConnectionPlus) -> None:
"""
Perform the upgrade from version 6 to version 7
Add a captured_run_id and captured_counter column to the runs table and
assign the value from the run_id and result_counter to these columns.
"""
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 captured_run_id"
transaction(conn, sql)
sql = "ALTER TABLE runs ADD COLUMN captured_counter"
transaction(conn, sql)
sql = f"""
UPDATE runs
SET captured_run_id = run_id,
captured_counter = result_counter
"""
transaction(conn, sql)
else:
raise RuntimeError(f"found {n_run_tables} runs tables expected 1")
def perform_db_upgrade_6_to_7(conn: ConnectionPlus) -> None:
"""
Perform the upgrade from version 6 to version 7
Add a captured_run_id and captured_counter column to the runs table and
assign the value from the run_id and result_counter to these columns.
"""
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:
pbar = tqdm(range(1), file=sys.stdout)
pbar.set_description("Upgrading database; v6 -> v7")
# iterate through the pbar for the sake of the side effect; it
# prints that the database is being upgraded
for _ in pbar:
with atomic(conn) as conn:
sql = "ALTER TABLE runs ADD COLUMN captured_run_id"
transaction(conn, sql)
sql = "ALTER TABLE runs ADD COLUMN captured_counter"
transaction(conn, sql)
sql = f"""
UPDATE runs
SET captured_run_id = run_id,
captured_counter = result_counter
"""
transaction(conn, sql)
else:
raise RuntimeError(f"found {n_run_tables} runs tables expected 1")
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 _2to3_get_paramspecs(
conn: ConnectionPlus,
layout_ids: List[int],
layouts: Mapping[int, Tuple[str, str, str, str]],
dependencies: Mapping[int, Sequence[int]],
deps: Sequence[int],
indeps: Sequence[int],
result_table_name: str,
) -> Dict[int, ParamSpec]:
paramspecs: Dict[int, ParamSpec] = {}
the_rest = set(layout_ids).difference(set(deps).union(set(indeps)))
# We ensure that we first retrieve the ParamSpecs on which other ParamSpecs
# depend, then the dependent ParamSpecs and finally the rest
for layout_id in list(indeps) + list(deps) + list(the_rest):
(name, label, unit, inferred_from_str) = layouts[layout_id]
# get the data type
sql = f'PRAGMA TABLE_INFO("{result_table_name}")'
c = transaction(conn, sql)
paramtype = None
for row in c.fetchall():
if row['name'] == name:
paramtype = row['type']
break
if paramtype is None:
raise TypeError(f"Could not determine type of {name} during the"
f"db upgrade of {result_table_name}")
inferred_from: List[str] = []
depends_on: List[str] = []
# this parameter depends on another parameter
if layout_id in deps:
setpoints = dependencies[layout_id]
depends_on = [paramspecs[idp].name for idp in setpoints]
if inferred_from_str != '':
inferred_from = inferred_from_str.split(', ')
paramspec = ParamSpec(name=name,
paramtype=paramtype,
label=label, unit=unit,
depends_on=depends_on,
inferred_from=inferred_from)
paramspecs[layout_id] = paramspec
return paramspecs
def get_runs(conn: ConnectionPlus,
exp_id: Optional[int] = None) -> List[sqlite3.Row]:
""" Get a list of runs.
Args:
conn: database connection
Returns:
list of rows
"""
with atomic(conn) as conn:
if exp_id:
sql = """
SELECT * FROM runs
where exp_id = ?
"""
c = transaction(conn, sql, exp_id)
else:
sql = """
SELECT * FROM runs
"""
c = transaction(conn, sql)
return c.fetchall()
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 _create_run_table(conn: ConnectionPlus,
formatted_name: str,
parameters: Optional[List[ParamSpec]] = None,
values: Optional[VALUES] = None) -> None:
"""Create run table with formatted_name as name
Args:
conn: database connection
formatted_name: the name of the table to create
"""
_validate_table_name(formatted_name)
with atomic(conn) as conn:
if parameters and values:
_parameters = ",".join([p.sql_repr() for p in parameters])
query = f"""
CREATE TABLE "{formatted_name}" (
id INTEGER PRIMARY KEY,
{_parameters}
);
"""
transaction(conn, query)
# now insert values
insert_values(conn, formatted_name, [p.name for p in parameters],
values)
elif parameters:
_parameters = ",".join([p.sql_repr() for p in parameters])
query = f"""
CREATE TABLE "{formatted_name}" (
id INTEGER PRIMARY KEY,
{_parameters}
);
"""
transaction(conn, query)
else:
query = f"""
CREATE TABLE "{formatted_name}" (
id INTEGER PRIMARY KEY
);
"""
transaction(conn, query)
def init_db(conn: ConnectionPlus) -> None:
with atomic(conn) as conn:
transaction(conn, _experiment_table_schema)
transaction(conn, _runs_table_schema)
transaction(conn, _layout_table_schema)
transaction(conn, _dependencies_table_schema)
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 insert_many_values(
conn: ConnectionPlus,
formatted_name: str,
columns: Sequence[str],
values: Sequence[VALUES],
) -> int:
"""
Inserts many values for the specified columns.
Example input:
columns: ['xparam', 'yparam']
values: [[x1, y1], [x2, y2], [x3, y3]]
NOTE this need to be committed before closing the connection.
"""
# We demand that all values have the same length
lengths = [len(val) for val in values]
if len(np.unique(lengths)) > 1:
raise ValueError('Wrong input format for values. Must specify the '
'same number of values for all columns. Received'
f' lengths {lengths}.')
no_of_rows = len(lengths)
no_of_columns = lengths[0]
# The TOTAL number of inserted values in one query
# must be less than the SQLITE_MAX_VARIABLE_NUMBER
# Version check cf.
# "https://stackoverflow.com/questions/9527851/sqlite-error-
# too-many-terms-in-compound-select"
version_str = SQLiteSettings.settings["VERSION"]
# According to the SQLite changelog, the version number
# to check against below
# ought to be 3.7.11, but that fails on Travis
if version.parse(str(version_str)) <= version.parse("3.8.2"):
max_var = SQLiteSettings.limits["MAX_COMPOUND_SELECT"]
else:
max_var = SQLiteSettings.limits['MAX_VARIABLE_NUMBER']
rows_per_transaction = int(int(max_var) / no_of_columns)
_columns = ",".join(columns)
_values = "(" + ",".join(["?"] * len(values[0])) + ")"
a, b = divmod(no_of_rows, rows_per_transaction)
chunks = a * [rows_per_transaction] + [b]
if chunks[-1] == 0:
chunks.pop()
start = 0
stop = 0
with atomic(conn) as conn:
for ii, chunk in enumerate(chunks):
_values_x_params = ",".join([_values] * chunk)
query = f"""INSERT INTO "{formatted_name}"
({_columns})
VALUES
{_values_x_params}
"""
stop += chunk
# we need to make values a flat list from a list of list
flattened_values = list(
itertools.chain.from_iterable(values[start:stop]))
c = transaction(conn, query, *flattened_values)
if ii == 0:
return_value = c.lastrowid
start += chunk
return return_value
def _insert_run(
conn: ConnectionPlus,
exp_id: int,
name: str,
guid: str,
parameters: Optional[List[ParamSpec]] = None,
):
# get run counter and formatter from experiments
run_counter, format_string = select_many_where(conn,
"experiments",
"run_counter",
"format_string",
where_column="exp_id",
where_value=exp_id)
run_counter += 1
formatted_name = format_table_name(format_string, name, exp_id,
run_counter)
table = "runs"
parameters = parameters or []
desc_str = RunDescriber(InterDependencies(*parameters)).to_json()
with atomic(conn) as conn:
if parameters:
query = f"""
INSERT INTO {table}
(name,
exp_id,
guid,
result_table_name,
result_counter,
run_timestamp,
parameters,
is_completed,
run_description)
VALUES
(?,?,?,?,?,?,?,?,?)
"""
curr = transaction(conn, query, name, exp_id, guid, formatted_name,
run_counter, None,
",".join([p.name for p in parameters]), False,
desc_str)
_add_parameters_to_layout_and_deps(conn, formatted_name,
*parameters)
else:
query = f"""
INSERT INTO {table}
(name,
exp_id,
guid,
result_table_name,
result_counter,
run_timestamp,
is_completed,
run_description)
VALUES
(?,?,?,?,?,?,?,?)
"""
curr = transaction(conn, query, name, exp_id, guid, formatted_name,
run_counter, None, False, desc_str)
run_id = curr.lastrowid
return run_counter, formatted_name, run_id
