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_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_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 test_missing_values1(): filename = get_fresh_filename() db = ItemDB(filename).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 = ItemDB(filename).ensure_table("items", "!id", "mt", "value") with db: db.put("items", dict(id=3, mt=100, value=41)) # db = ItemDB(filename).ensure_table("items", "!id", "mt", "value") 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).ensure_table("items", "!id") with db: db.put("items", dict(id=5, mt=100, value=999)) assert len(db.select("items", "value == 999")) == 1
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_init_write(): db = ItemDB(":memory:").ensure_table("items", "!id", "mt") with raises(IOError): # Put needs to be used under a context db.put("items", dict(id=1, mt=100)) with raises(KeyError): # Invalid table with db: db.put("foo", dict(id=1, mt=100)) with raises(TypeError): # Note a dict with db: db.put("items", "not a dict") with raises(IndexError): # id is required but missing with db: db.put("items", dict(mt=100)) with raises(IOError): # Cant enter twice with db: with db: pass with db: db.put("items", dict(id=1, mt=100)) db.put("items", dict(id=2, mt=100, value=42)) db.put("items", dict(id=3, value=42)) assert len(db.select_all("items")) == 3 assert db.count_all("items") == 3 assert len(db.get_table_names()) == 1 assert len(db.select("items", "mt == 100")) == 2 assert len(db.select("items", "mt is NULL")) == 1 assert db.count("items", "mt == 100") == 2 assert db.count("items", "mt is NULL") == 1 with raises(IndexError): # No index for value db.select("items", "value == 42") with raises(IndexError): # No index for value db.count("items", "value == 42") with raises(sqlite3.OperationalError): # Malformed SQL db.select("items", "id >>> 42") with raises(sqlite3.OperationalError): # Malformed SQL db.count("items", "id >>> 42")
def write_something(): db = ItemDB(filename).ensure_table("settings", "!id") with db: db.put("settings", dict(id="server_reset", value="42", mt=42)) db.close() return "wrote something"
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