def test_multiple_unique_keys():
db = ItemDB(":memory:").ensure_table("items", "!id1", "!id2")
with db:
db.put_one("items", id1=1, id2=1, value=1)
db.put_one("items", id1=1, id2=2, value=2)
db.put_one("items", id1=2, id2=2, value=3)
db.put_one("items", id1=2, id2=1, value=4)
assert db.count_all("items") == 1
assert db.select_one("items", "id1 == 1") is None
assert db.select_one("items", "id1 == 2")["value"] == 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_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_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_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 run_fast_transaction2():
db = ItemDB(filename)
time.sleep(0.1) # make sure that we're the waiting thread
with db:
db.put_one("items", id=3, value=30)
time.sleep(0.2)
def run_slow_transaction1():
db = ItemDB(filename)
with db:
db.put_one("items", id=3, value=20)
time.sleep(1.0)