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"])