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_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_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 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 read_something():
db = ItemDB(filename).ensure_table("settings", "!id")
xx.extend(db.select_all("settings"))
return "read 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