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_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 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_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_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_create_tables(): # Empty database, zero tables db = ItemDB(":memory:") assert db.get_table_names() == [] # no tables # Two tables db = ItemDB(":memory:").ensure_table("foo", "key").ensure_table("bar") assert db.get_table_names() == ["bar", "foo"] assert db.count_all("foo") == 0 assert db.count_all("bar") == 0
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_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")