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
