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