def test_multiple_unique_keys():
db = ItemDB(":memory:").ensure_table("items", "!id1", "!id2")
with db:
db.put_one("items", id1=1, id2=1, value=1)
db.put_one("items", id1=1, id2=2, value=2)
db.put_one("items", id1=2, id2=2, value=3)
db.put_one("items", id1=2, id2=1, value=4)
assert db.count_all("items") == 1
assert db.select_one("items", "id1 == 1") is None
assert db.select_one("items", "id1 == 2")["value"] == 4
def test_usage_settings():
db = ItemDB(":memory:").ensure_table("settings", "!id", "mt", "value")
# Need id
with raises(IndexError):
with db:
db.put("settings", dict(value="old", mt=100))
# Add three items
with db:
db.put("settings", dict(id="foo", value="old", mt=100))
db.put("settings", dict(id="bar", value="old", mt=100))
db.put("settings", dict(id="egg", value="old", mt=100))
assert len(db.select_all("settings")) == 3
assert len(db.select("settings", "mt > 100")) == 0
assert len(db.select("settings", "value == 'updated'")) == 0
# Update them, one using an older
for item in [
dict(id="foo", value="updated", mt=99),
dict(id="bar", value="updated", mt=100), # also updates
dict(id="egg", value="updated", mt=101),
dict(id="spam", value="updated", mt=101), # new
]:
with db:
cur = db.select("settings", "id == ?", item["id"])
if not cur or cur[0]["mt"] <= item["mt"]:
db.put("settings", item)
assert len(db.select_all("settings")) == 4
assert len(db.select("settings", "mt > 100")) == 2
assert len(db.select("settings", "value == 'updated'")) == 3
assert db.select_one("settings", "id=='egg'")["value"] == "updated"
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 test_usage_items():
db = ItemDB(":memory:").ensure_table("items", "!id", "mt", "value")
# Need id
with raises(IndexError):
with db:
db.put("items", dict(mt=100, value=1))
# Add three items
with db:
db.put("items", dict(id=1, mt=100, value=1))
db.put("items", dict(id=2, mt=100, value=1))
db.put("items", dict(id=3, mt=100, value=1))
assert len(db.select_all("items")) == 3
assert len(db.select("items", "value == 1")) == 3
assert len(db.select("items", "value == 2")) == 0
# Update them, one using an older mt
for item in [
dict(id=1, mt=99, value=2), # wont override
dict(id=2, mt=100, value=2), # will override - mt's are equal
dict(id=3, mt=101, value=2), # will override
dict(id=4, mt=101, value=2), # new
]:
with db:
cur = db.select("items", "id == ?", item["id"])
if not cur or cur[0]["mt"] <= item["mt"]:
db.put("items", item)
assert len(db.select_all("items")) == 4
assert len(db.select("items", "value == 1")) == 1
assert len(db.select("items", "value == 2")) == 3
x = db.select_one("items", "id == ?", 3)
assert x["mt"] == 101
db = ItemDB(":memory:").ensure_table("items", "!id", "mt", "value")
x = db.select_one("items", "id == ?", 3)
assert x is None
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 run_read():
db = ItemDB(filename)
for i in range(30):
time.sleep(0.05)
item = db.select_one("items", "id == 3")
read.append(item["value"])
