def test_transactions1():
filename = get_fresh_filename()
db = ItemDB(filename)
db.ensure_table("items", "!id", "mt")
# Add items the easy way
with db:
db.put_one("items", id=1, mt=100)
db.put_one("items", id=2, mt=100)
assert db.count_all("items") == 2
# Add more items and raise after
with raises(RuntimeError):
with db:
db.put_one("items", id=3, mt=100)
db.put_one("items", id=4, mt=100)
raise RuntimeError("Transaction has been comitted")
assert db.count_all("items") == 4
# Again, but now raise within transaction
with raises(RuntimeError):
with db:
db.put_one("items", id=5, mt=100)
db.put_one("items", id=6, mt=100)
raise RuntimeError("Abort transaction!")
assert db.count_all("items") == 4
def test_rename_table():
db = ItemDB(":memory:")
db.ensure_table("persons", "!name")
with db:
db.put_one("persons", name="Jan", age=30)
db.put_one("persons", name="Henk", age=42)
db.put_one("persons", name="Takkie", age=30)
db.put_one("persons", name="Siepe", age=42)
assert db.count_all("persons") == 4
with raises(KeyError):
db.count_all("clients")
# Fails
with raises(IOError): # Need a transaction context
db.rename_table("persons", "clients")
with raises(TypeError): # not a str
with db:
db.rename_table("persons", 3)
with raises(TypeError): # not an identifier
with db:
db.rename_table("persons", "foo bar")
with db:
db.rename_table("persons", "clients")
assert db.count_all("clients") == 4
with raises(KeyError):
db.count_all("persons")
def test_table_fails():
db = ItemDB(":memory:")
for name in [(), 4, b"", [], {}]:
with raises(TypeError): # not a str
db.ensure_table(name)
db = ItemDB(":memory:")
for name in ["foo bar", "foo-bar", "33", "foo!", "!foo"]:
with raises(ValueError): # not an identifier
db.ensure_table(name)
def test_delete_items():
db = ItemDB(":memory:")
db.ensure_table("persons", "!name")
with db:
db.put_one("persons", name="Jan", age=30)
db.put_one("persons", name="Henk", age=42)
db.put_one("persons", name="Bart", age=19)
db.put_one("persons", name="Ivo", age=28)
assert db.select_one("persons", "name == ?", "Bart") == {
"name": "Bart",
"age": 19
}
# Delete fails
with raises(IOError): # Must be in a transaction!
db.delete("persons", "name == ?", "Bart")
with raises(IndexError): # No index for age
with db:
db.delete("persons", "age == 42")
with raises(sqlite3.OperationalError): # Malformed SQL
with db:
db.delete("persons", "age >>> 42")
with db:
db.delete("persons", "name == ?", "Bart")
assert db.count_all("persons") == 3
assert db.select_one("persons", "name == ?", "Bart") is None
# And that transaction can be cancelled
try:
with db:
db.delete("persons", "name > ''")
raise RuntimeError()
except RuntimeError:
pass
assert db.count_all("persons") == 3
# Just to show that without that raise, it would indeed clear the table
with db:
db.delete("persons", "name > ''")
assert db.count_all("persons") == 0
def __init__(self, filename, *, step=DEFAULT_STEP):
self._step = int(step)
# Prepare db
self._filename = filename
# Locks
self._tlocal = threading.local() # per-thread data
self._lock_current_aggr = threading.RLock()
# Init current aggregation
self._current_aggr = self._create_new_aggr()
self._current_time_stop = self._current_aggr["time_stop"]
# Keep track of ids for daily counters
self._daily_ids = {} # key -> set of ids, gets cleared each day
self._monthly_ids = {}
if os.path.isfile(self._filename):
db = ItemDB(self._filename)
try:
db.ensure_table("info", "!key")
daily_ids_info = db.select_one("info", "key == 'daily_ids'")
day_key = self._current_aggr["time_key"][:10]
if daily_ids_info and daily_ids_info[
"time_key"][:10] == day_key:
for key in daily_ids_info:
if key not in ("key", "time_key"):
self._daily_ids[key] = set(daily_ids_info[key])
monthly_ids_info = db.select_one("info",
"key == 'monthly_ids'")
month_key = self._current_aggr["time_key"][:7]
if monthly_ids_info and monthly_ids_info[
"time_key"][:7] == month_key:
for key in monthly_ids_info:
if key not in ("key", "time_key"):
self._monthly_ids[key] = set(monthly_ids_info[key])
except Exception as err:
logger.error(
f"Failed to restore daily_ids and monthly_ids from db: {err}"
)
# Setup our helper thread
_monitor_instances.add(self)
global _helper_thread
if _helper_thread is None:
_helper_thread = HelperThread()
_helper_thread.start()
def _write_aggr(self, aggr):
""" Write the given aggr to disk. Used by the helper thread to write
aggr's that we put on the _write_queue.
"""
for key in aggr.keys():
if not key.startswith("time_"):
break
else:
return # Nothing in here, return now
try:
db = ItemDB(self._filename)
# Write aggegation
db.ensure_table(TABLE_NAME, "!time_key")
with db:
x = db.select_one(TABLE_NAME, "time_key == ?",
aggr["time_key"])
if x is not None:
merge(x, aggr)
aggr = x
db.put(TABLE_NAME, aggr)
# Prepare daily ids info
daily_ids_info = {}
for key in self._daily_ids.keys():
daily_ids_info[key] = list(self._daily_ids[key])
daily_ids_info["key"] = "daily_ids"
daily_ids_info["time_key"] = self._current_aggr["time_key"][:10]
# Prepare montly ids info
monthly_ids_info = {}
for key in self._monthly_ids.keys():
monthly_ids_info[key] = list(self._monthly_ids[key])
monthly_ids_info["key"] = "monthly_ids"
monthly_ids_info["time_key"] = self._current_aggr["time_key"][:7]
# Write
db.ensure_table("info", "!key")
with db:
db.put("info", daily_ids_info)
db.put("info", monthly_ids_info)
except Exception as err:
logger.error("Failed to save aggregations: " + str(err))
def test_missing_values2():
filename = get_fresh_filename()
db = ItemDB(filename)
db.ensure_table("items", "!id", "mt")
# Keys that are not listed are NOT ignored
with db:
db.put("items", dict(id=1, mt=100))
db.put("items", dict(id=2, mt=100, value=6))
#
assert db.select_all("items") == [
dict(id=1, mt=100), dict(id=2, mt=100, value=6)
]
with raises(IndexError): # No index for value
db.select("items", "value == 6")
# When a column is added it gets NULL values in the db, and items stay as they are
db.ensure_table("items", "value")
with db:
db.put("items", dict(id=3, mt=100, value=41))
#
assert db.select_all("items") == [
dict(id=1, mt=100),
dict(id=2, mt=100, value=6),
dict(id=3, mt=100, value=41),
]
assert len(db.select("items", "value == 6")) == 1
assert len(db.select("items", "value > 0")) == 2
assert len(db.select("items", "value is NULL")) == 1
# When we don't specify a column, it still gets a value (not NULL)
db = ItemDB(filename)
with db:
db.put("items", dict(id=5, mt=100, value=999))
assert len(db.select("items", "value == 999")) == 1
def test_index_fails():
# Invalid index/table names - not str
db = ItemDB(":memory:")
for name in [(), 4, b"", [], {}]:
with raises(TypeError):
db.ensure_table("items", name)
# Invalid index/table names - not an identifier
db = ItemDB(":memory:")
for name in ["foo bar", "foo-bar", "33", "foo!"]:
with raises(ValueError):
db.ensure_table("items", name)
# Reserved
for name in ["!_ob", "_ob"]:
with raises(IndexError):
db.ensure_table("items", name)
# Cannot add a unique key
filename = get_fresh_filename()
db = ItemDB(filename).ensure_table("foo", "meh")
with closing(db):
assert "foo" in db.get_table_names()
with raises(IndexError):
db = ItemDB(filename).ensure_table("foo", "!key")
# Cannot use a normal key as a unique key
filename = get_fresh_filename()
db = ItemDB(filename).ensure_table("foo", "key")
with closing(db):
assert "foo" in db.get_table_names()
with raises(IndexError):
db = ItemDB(filename).ensure_table("foo", "!key")
# Cannot use a unique key as a normal key
filename = get_fresh_filename()
db = ItemDB(filename).ensure_table("foo", "!key")
with closing(db):
assert "foo" in db.get_table_names()
with raises(IndexError):
db = ItemDB(filename).ensure_table("foo", "key")
def test_change_unique_key():
db = ItemDB(":memory:")
db.ensure_table("persons", "!name")
with db:
db.put_one("persons", name="Jan", age=30)
db.put_one("persons", name="Henk", age=42)
assert db.count_all("persons") == 2
# Add a new person, who also happens to be named "Jan"
with db:
db.put_one("persons", name="Jan", age=72)
# Sorry, Jan
assert db.count_all("persons") == 2
# Let's fix this, we need a separate id, so we need to re-index.
# We cannot simply do this on an existing table. So we need some steps.
try:
with db:
db.ensure_table("persons2")
for i, person in enumerate(db.select_all("persons")):
person["id"] = i
db.put("persons2", person)
db.delete_table("persons")
raise RuntimeError("Oop! Something goes wrong in the process")
db.rename_table("persons2", "persons")
except RuntimeError:
pass
# Our little operation failed, but we did it in a transaction, so its fine!
assert db.count_all("persons") == 2
# Try again
with db:
db.ensure_table("persons2")
for i, person in enumerate(db.select_all("persons")):
person["id"] = i
db.put("persons2", person)
db.delete_table("persons")
db.rename_table("persons2", "persons")
# Now we're good
assert db.count_all("persons") == 2
with db:
db.put_one("persons", name="Jan", age=72, id=3)
assert db.count_all("persons") == 3
def test_delete_table():
db = ItemDB(":memory:")
db.ensure_table("persons", "!name")
db.ensure_table("animals", "!name")
with db:
db.put_one("persons", name="Jan", age=30)
db.put_one("persons", name="Henk", age=42)
db.put_one("animals", name="Takkie", age=30)
db.put_one("animals", name="Siepe", age=42)
assert db.count_all("persons") == 2
assert db.count_all("animals") == 2
with db:
db.delete_table("persons")
with raises(KeyError):
db.count_all("persons")
db.ensure_table("persons", "!name")
assert db.count_all("persons") == 0
assert db.count_all("animals") == 2
with db:
db.delete_table("animals")
with raises(KeyError):
db.count_all("animals")
db.ensure_table("animals", "!name")
assert db.count_all("persons") == 0
assert db.count_all("animals") == 0
# Need a transaction context
with raises(IOError):
db.delete_table("persons")
# This works
with db:
db.delete_table("persons")
# But this not because the table is gone
with raises(KeyError):
with db:
db.delete_table("persons")
def test_multiple_items():
filename = get_fresh_filename()
db = ItemDB(filename)
db.ensure_table("items", "!id")
assert len(db.select_all("items")) == 0
# Adding multiple
with db:
db.put("items", dict(id=1, mt=100), dict(id=2, mt=100))
assert len(db.select_all("items")) == 2
# Separate additions, one gets added
# These few tests here are a remnant of when itemdb was different, but lets
# not throw away precious testing code ...
with db:
db.put("items", dict(id=3, mt=100))
with raises(RuntimeError):
with db:
raise RuntimeError()
assert set(x["id"] for x in db.select_all("items")) == {1, 2, 3}
# Combined addition, none gets added
with raises(RuntimeError):
with db:
db.put("items", dict(id=4, mt=100), dict(id=5))
raise RuntimeError()
assert set(x["id"] for x in db.select_all("items")) == {1, 2, 3}
# Combined addition, none gets changed
with raises(RuntimeError):
with db:
db.put("items", dict(id=3, mt=102), dict(id=5))
raise RuntimeError()
assert set(x["id"] for x in db.select_all("items")) == {1, 2, 3}
x = db.select_all("items")[-1]
assert x["id"] == 3 and x["mt"] == 100
# Upgrades work too
db = ItemDB(filename)
with db:
db.put(
"items",
dict(id=1, mt=102),
dict(id=1, mt=102),
dict(id=2, mt=102),
dict(id=3, mt=102),
dict(id=4, mt=102),
)
assert set(x["id"] for x in db.select_all("items")) == {1, 2, 3, 4}
for x in db.select_all("items"):
x["mt"] == 102
# Lets take it further
with db:
db.put("items", *(dict(id=i, mt=104) for i in range(99)))
assert len(db.select_all("items")) == 99
