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 get_aggregations(self, first_day, last_day): """ Get aggregations between two given days (inclusive). If the last day is today, also include the current aggregation. """ assert isinstance(first_day, datetime.date) assert isinstance(last_day, datetime.date) today = time.gmtime() # UTC today = datetime.date(today.tm_year, today.tm_mon, today.tm_mday) one_day = datetime.timedelta(days=1) data = [] db = ItemDB(self.filename) try: data = db.select( TABLE_NAME, "time_key >= ? AND time_key < ?", first_day.strftime("%Y-%m-%d"), (last_day + one_day).strftime("%Y-%m-%d"), ) except KeyError: pass # Invalid table name if last_day == today: data.append(self.get_current_aggr()) return data
def test_init_read(): # Empty database, zero tables db = ItemDB(":memory:") assert db.get_table_names() == [] # no tables with raises(KeyError): db.select("foo", "key is NULL") with raises(KeyError): db.select_all("foo") with raises(KeyError): db.count_all("foo") # Two tables db = ItemDB(":memory:").ensure_table("foo", "key").ensure_table("bar") assert db.count_all("foo") == 0 assert db.count_all("bar") == 0
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_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 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