def format_as_sql_rows(matrices, connection):
"""
Given an iterable of MatrixRows (which contain a BNF code plus all
prescribing data for that presentation) yield tuples of values ready for
insertion into SQLite
"""
cursor = connection.cursor()
num_presentations = next(
cursor.execute("SELECT COUNT(*) FROM presentation"))[0]
count = 0
for row in matrices:
count += 1
# We make sure we have a row for every BNF code in the data, even ones
# we didn't know about previously. This is a hack that we won't need
# once we can use SQLite v3.24.0 which has proper UPSERT support.
cursor.execute(
"INSERT OR IGNORE INTO presentation (bnf_code) VALUES (?)",
[row.bnf_code])
if should_log_message(count):
logger.info("Writing data for %s (%s/%s)", row.bnf_code, count,
num_presentations)
yield (
serialize_compressed(row.items),
serialize_compressed(row.quantity),
serialize_compressed(row.actual_cost),
serialize_compressed(row.net_cost),
row.bnf_code,
)
logger.info("Finished writing data for %s presentations", count)
def create_fixture(cls):
temp_file = cls.fixture_path + '.tmp'
if os.path.exists(temp_file):
os.unlink(temp_file)
connection = sqlite3.connect(temp_file)
connection.executescript(
"""
CREATE TABLE data (key TEXT, value BLOB);
CREATE TABLE environment_metadata (key TEXT, value TEXT);
"""
)
for key, value in cls.get_expected_values():
if '.uncompressed' in key:
data = serialize(value)
elif '.compressed' in key:
data = serialize_compressed(value)
else:
raise RuntimeError('Invalid key')
connection.execute(
'INSERT INTO data VALUES (?, ?)',
[key, sqlite3.Binary(data)]
)
for key, value in cls.get_environment_metadata():
connection.execute(
'INSERT INTO environment_metadata VALUES (?, ?)',
[key, value]
)
connection.commit()
connection.close()
os.rename(temp_file, cls.fixture_path)
def create_fixture(cls):
temp_db_path = cls.fixture_db_path + ".tmp"
if os.path.exists(temp_db_path):
os.unlink(temp_db_path)
connection = sqlite3.connect(temp_db_path)
connection.executescript("""
CREATE TABLE data (key TEXT, value BLOB);
CREATE TABLE environment_metadata (key TEXT, value TEXT);
""")
json_data = {}
for key, value in cls.generate_test_values():
json_value = value
if hasattr(value, "todense"):
json_value = json_value.todense()
json_data[key] = {
"type": type(value).__name__,
"value": json_value.tolist(),
}
if ".uncompressed" in key:
data = serialize(value)
elif ".compressed" in key:
data = serialize_compressed(value)
else:
raise RuntimeError("Invalid key")
connection.execute("INSERT INTO data VALUES (?, ?)",
[key, sqlite3.Binary(data)])
for key, value in cls.get_environment_metadata():
connection.execute(
"INSERT INTO environment_metadata VALUES (?, ?)", [key, value])
connection.commit()
connection.close()
os.rename(temp_db_path, cls.fixture_db_path)
with open(cls.fixture_json_path, "w") as f:
json.dump(json_data, f, indent=2)
def format_values_for_sqlite(row):
"""
Accepts a list of matrices and formats them ready for insertion into SQLite
"""
return [
sqlite3.Binary(serialize_compressed(value))
for value in row
]
def write_practice_stats(connection, practice_statistics):
cursor = connection.cursor()
# Map practice codes and date strings to their corresponding row/column in
# the matrix
practices = dict(cursor.execute('SELECT code, offset FROM practice'))
dates = dict(cursor.execute('SELECT date, offset FROM date'))
matrices = build_matrices(practice_statistics, practices, dates)
for statistic_name, matrix in matrices:
# Once we can use SQLite v3.24.0 which has proper UPSERT support we
# won't need to do this
cursor.execute(
'INSERT OR IGNORE INTO practice_statistic (name) VALUES (?)',
[statistic_name])
cursor.execute(
"""
UPDATE practice_statistic SET value=? WHERE name=?
""",
[sqlite3.Binary(serialize_compressed(matrix)), statistic_name])
def test_sqlite_roundtrip_with_compression(self):
obj = {"hello": "world" * 256}
data = serialize_compressed(obj)
new_data = roundtrip_through_sqlite(sqlite3.Binary(data))
new_obj = deserialize(new_data)
self.assertEqual(new_obj, obj)
def test_simple_serialisation_with_compression(self):
obj = {"hello": "world" * 256}
data = serialize(obj)
compressed_data = serialize_compressed(obj)
self.assertLess(len(compressed_data), len(data))
self.assertEqual(deserialize(compressed_data), obj)
def prepare_matrix_value(matrix):
if is_integer(matrix):
matrix = convert_to_smallest_int_type(matrix)
data = serialize_compressed(matrix)
return sqlite3.Binary(data)
