def test_atomic():
sqlite_conn = sqlite3.connect(':memory:')
match_str = re.escape('atomic context manager only accepts ConnectionPlus '
'database connection objects.')
with pytest.raises(ValueError, match=match_str):
with atomic(sqlite_conn):
pass
conn_plus = ConnectionPlus(sqlite_conn)
assert False is conn_plus.atomic_in_progress
atomic_in_progress = conn_plus.atomic_in_progress
isolation_level = conn_plus.isolation_level
assert False is conn_plus.in_transaction
with atomic(conn_plus) as atomic_conn:
assert conn_plus_in_transaction(atomic_conn)
assert conn_plus_in_transaction(conn_plus)
assert isolation_level == conn_plus.isolation_level
assert False is conn_plus.in_transaction
assert atomic_in_progress is conn_plus.atomic_in_progress
assert isolation_level == conn_plus.isolation_level
assert False is atomic_conn.in_transaction
assert atomic_in_progress is atomic_conn.atomic_in_progress
def test_two_nested_atomics():
sqlite_conn = sqlite3.connect(':memory:')
conn_plus = ConnectionPlus(sqlite_conn)
atomic_in_progress = conn_plus.atomic_in_progress
isolation_level = conn_plus.isolation_level
assert False is conn_plus.in_transaction
with atomic(conn_plus) as atomic_conn_1:
assert conn_plus_in_transaction(conn_plus)
assert conn_plus_in_transaction(atomic_conn_1)
with atomic(atomic_conn_1) as atomic_conn_2:
assert conn_plus_in_transaction(conn_plus)
assert conn_plus_in_transaction(atomic_conn_1)
assert conn_plus_in_transaction(atomic_conn_2)
assert conn_plus_in_transaction(conn_plus)
assert conn_plus_in_transaction(atomic_conn_1)
assert conn_plus_in_transaction(atomic_conn_2)
assert conn_plus_is_idle(conn_plus, isolation_level)
assert conn_plus_is_idle(atomic_conn_1, isolation_level)
assert conn_plus_is_idle(atomic_conn_2, isolation_level)
assert atomic_in_progress == conn_plus.atomic_in_progress
assert atomic_in_progress == atomic_conn_1.atomic_in_progress
assert atomic_in_progress == atomic_conn_2.atomic_in_progress
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 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 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 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 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 test_atomic_on_connection_plus_that_is_in_progress(in_transaction):
sqlite_conn = sqlite3.connect(':memory:')
conn_plus = ConnectionPlus(sqlite_conn)
# explicitly set to True for testing purposes
conn_plus.atomic_in_progress = True
# implement parametrizing over connection's `in_transaction` attribute
if in_transaction:
conn_plus.cursor().execute('BEGIN')
assert in_transaction is conn_plus.in_transaction
isolation_level = conn_plus.isolation_level
in_transaction = conn_plus.in_transaction
with atomic(conn_plus) as atomic_conn:
assert True is conn_plus.atomic_in_progress
assert isolation_level == conn_plus.isolation_level
assert in_transaction is conn_plus.in_transaction
assert True is atomic_conn.atomic_in_progress
assert isolation_level == atomic_conn.isolation_level
assert in_transaction is atomic_conn.in_transaction
assert True is conn_plus.atomic_in_progress
assert isolation_level == conn_plus.isolation_level
assert in_transaction is conn_plus.in_transaction
assert True is atomic_conn.atomic_in_progress
assert isolation_level == atomic_conn.isolation_level
assert in_transaction is atomic_conn.in_transaction
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 write_metadata_to_db(self,
path_to_db: Optional[Union[str,
Path]] = None) -> None:
from .experiment_container import load_or_create_experiment
if self._dataset_is_in_runs_table(path_to_db=path_to_db):
return
if isinstance(path_to_db, Path):
path_to_db = str(path_to_db)
with contextlib.closing(
conn_from_dbpath_or_conn(conn=None,
path_to_db=path_to_db)) as conn:
with atomic(conn) as aconn:
exp = load_or_create_experiment(
conn=aconn,
experiment_name=self.exp_name,
sample_name=self.sample_name,
load_last_duplicate=True,
)
_add_run_to_runs_table(self,
aconn,
exp.exp_id,
create_run_table=False)
self._path_to_db = conn.path_to_dbfile
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 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 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 _add_to_dyn_column_if_in_db(self, tag: str, data: Any) -> None:
if self._dataset_is_in_runs_table():
with contextlib.closing(
conn_from_dbpath_or_conn(
conn=None, path_to_db=self._path_to_db)) as conn:
with atomic(conn) as aconn:
add_data_to_dynamic_columns(aconn, self.run_id,
{tag: data})
def perform_db_upgrade_7_to_8(conn: ConnectionPlus) -> None:
"""
Perform the upgrade from version 6 to version 7.
Add a new column to store the dataset's parents to the runs table.
"""
with atomic(conn) as conn:
insert_column(conn, 'runs', 'parent_datasets', 'TEXT')
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 unsubscribe(self, uuid: str) -> None:
"""
Remove subscriber with the provided uuid
"""
with atomic(self.conn) as conn:
sub = self.subscribers[uuid]
remove_trigger(conn, sub.trigger_id)
sub.schedule_stop()
sub.join()
del self.subscribers[uuid]
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 perform_db_upgrade_4_to_5(conn: ConnectionPlus) -> None:
"""
Perform the upgrade from version 4 to version 5.
Make sure that 'snapshot' column always exists in the 'runs' table. This
was not the case before because 'snapshot' was treated as 'metadata',
hence the 'snapshot' column was dynamically created once there was a run
with snapshot information.
"""
with atomic(conn) as conn:
insert_column(conn, 'runs', 'snapshot', 'TEXT')
def test_atomic_on_outmost_connection_that_is_in_transaction():
conn = ConnectionPlus(sqlite3.connect(':memory:'))
conn.execute('BEGIN')
assert True is conn.in_transaction
match_str = re.escape('SQLite connection has uncommitted transactions. '
'Please commit those before starting an atomic '
'transaction.')
with pytest.raises(RuntimeError, match=match_str):
with atomic(conn):
pass
def unsubscribe_all(self):
"""
Remove all subscribers
"""
sql = "select * from sqlite_master where type = 'trigger';"
triggers = atomic_transaction(self.conn, sql).fetchall()
with atomic(self.conn) as conn:
for trigger in triggers:
remove_trigger(conn, trigger['name'])
for sub in self.subscribers.values():
sub.schedule_stop()
sub.join()
self.subscribers.clear()
def test_integer_timestamps_in_database_are_supported():
ds = DataSet()
ds.mark_started()
ds.mark_completed()
with atomic(ds.conn) as conn:
_rewrite_timestamps(conn, ds.run_id, 42, 69)
assert isinstance(ds.run_timestamp_raw, float)
assert isinstance(ds.completed_timestamp_raw, float)
assert isinstance(ds.run_timestamp(), str)
assert isinstance(ds.completed_timestamp(), str)
def _both_zero(run_id: int, conn, guid_comps) -> None:
guid_str = generate_guid(timeint=guid_comps['time'],
sampleint=guid_comps['sample'])
with atomic(conn) as conn:
sql = f"""
UPDATE runs
SET guid = ?
where run_id == {run_id}
"""
cur = conn.cursor()
cur.execute(sql, (guid_str, ))
log.info(f'Succesfully updated run number {run_id}.')
def perform_db_upgrade_7_to_8(conn: ConnectionPlus) -> None:
"""
Perform the upgrade from version 6 to version 7.
Add a new column to store the dataset's parents to the runs table.
"""
with atomic(conn) as conn:
pbar = tqdm(range(1), file=sys.stdout)
pbar.set_description("Upgrading database; v7 -> v8")
# iterate through the pbar for the sake of the side effect; it
# prints that the database is being upgraded
for _ in pbar:
insert_column(conn, 'runs', 'parent_datasets', 'TEXT')
def _update_run_description(conn: ConnectionPlus, run_id: int,
description: str) -> None:
"""
Update the run_description field for the given run_id. The description
string is NOT validated.
"""
sql = """
UPDATE runs
SET run_description = ?
WHERE run_id = ?
"""
with atomic(conn) as conn:
conn.cursor().execute(sql, (description, 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 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 add_metadata(self, tag: str, metadata: Any):
"""
Adds metadata to the DataSet. The metadata is stored under the
provided tag. Note that None is not allowed as a metadata value.
Args:
tag: represents the key in the metadata dictionary
metadata: actual metadata
"""
self._metadata[tag] = metadata
# `add_meta_data` is not atomic by itself, hence using `atomic`
with atomic(self.conn) as conn:
add_meta_data(conn, self.run_id, {tag: metadata})
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 perform_db_upgrade_4_to_5(conn: ConnectionPlus) -> None:
"""
Perform the upgrade from version 4 to version 5.
Make sure that 'snapshot' column always exists in the 'runs' table. This
was not the case before because 'snapshot' was treated as 'metadata',
hence the 'snapshot' column was dynamically created once there was a run
with snapshot information.
"""
with atomic(conn) as conn:
pbar = tqdm(range(1), file=sys.stdout)
pbar.set_description("Upgrading database; v4 -> v5")
# iterate through the pbar for the sake of the side effect; it
# prints that the database is being upgraded
for _ in pbar:
insert_column(conn, 'runs', 'snapshot', 'TEXT')