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_database_race_conditions():
# This actually tests that a specific update scheme works with the
# itemdb. It should. In a previous version, itemdb was specifically
# designed for this syncing task. Now it's more general, but this
# is still a good use-case.
n_threads = 25
n_writes = 25
tracking = {}
for i in range(1, 11):
tracking[i] = []
# Create db and ensure it has tables
filename = get_fresh_filename()
ItemDB(filename).ensure_table("items", "!id")
def push_a_bunch():
for i in range(n_writes):
id = random.randint(1, 10)
mt = random.randint(1000, 2000)
tracking[id].append(mt)
with ItemDB(filename) as db:
x = db.select_one("items", "id == ?", id)
if not x or x["mt"] <= mt:
db.put_one("items", id=id, mt=mt)
# Prepare, start, and join threads
t0 = time.perf_counter()
threads = [threading.Thread(target=push_a_bunch) for i in range(n_threads)]
for t in threads:
t.start()
for t in threads:
t.join()
t1 = time.perf_counter()
# Evaluate the result
items = ItemDB(filename).select_all("items")
print(
f"{t1 - t0:0.2f} s for {n_threads * n_writes} writes saving {len(items)} items"
)
assert len(items) == 10 # that's the number of id's
#
for item in items:
id = item["id"]
assert item["mt"] == max(tracking[id])
return items
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 push_a_bunch():
for i in range(n_writes):
id = random.randint(1, 10)
mt = random.randint(1000, 2000)
tracking[id].append(mt)
with ItemDB(filename) as db:
x = db.select_one("items", "id == ?", id)
if not x or x["mt"] <= mt:
db.put_one("items", id=id, mt=mt)
def test_table_fails():
db = ItemDB(":memory:")
for name in [(), 4, b"", [], {}]:
with raises(TypeError): # not a str
db.ensure_table(name)
db = ItemDB(":memory:")
for name in ["foo bar", "foo-bar", "33", "foo!", "!foo"]:
with raises(ValueError): # not an identifier
db.ensure_table(name)
def get_user_db(user):
"""Open the user db and return the db and its mtime (which is -1 if the db did not yet exist)."""
# Get database name and its mtime
dbname = user2filename(user)
if os.path.isfile(dbname):
mtime = os.path.getmtime(dbname)
else:
mtime = -1
# Open the database, this creates it if it does not yet exist
db = ItemDB(dbname)
return db, mtime
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_transactions2():
filename = get_fresh_filename()
with ItemDB(filename).ensure_table("items", "!id") as db:
db.put_one("items", id=3, value=10)
# run transactions in threads while reading from other threads
def run_slow_transaction1():
db = ItemDB(filename)
with db:
db.put_one("items", id=3, value=20)
time.sleep(1.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_read():
db = ItemDB(filename)
for i in range(30):
time.sleep(0.05)
item = db.select_one("items", "id == 3")
read.append(item["value"])
read = []
threads = [
threading.Thread(target=run_slow_transaction1),
threading.Thread(target=run_fast_transaction2),
threading.Thread(target=run_read),
]
for t in threads:
t.start()
for t in threads:
t.join()
assert len(read) == 30
assert 5 <= read.count(10) <= 22
assert 2 <= read.count(20) <= 6
assert read.count(30) >= 5
def __init__(self, filename, *, step=DEFAULT_STEP):
self._step = int(step)
# Prepare db
self._filename = filename
# Locks
self._tlocal = threading.local() # per-thread data
self._lock_current_aggr = threading.RLock()
# Init current aggregation
self._current_aggr = self._create_new_aggr()
self._current_time_stop = self._current_aggr["time_stop"]
# Keep track of ids for daily counters
self._daily_ids = {} # key -> set of ids, gets cleared each day
self._monthly_ids = {}
if os.path.isfile(self._filename):
db = ItemDB(self._filename)
try:
db.ensure_table("info", "!key")
daily_ids_info = db.select_one("info", "key == 'daily_ids'")
day_key = self._current_aggr["time_key"][:10]
if daily_ids_info and daily_ids_info[
"time_key"][:10] == day_key:
for key in daily_ids_info:
if key not in ("key", "time_key"):
self._daily_ids[key] = set(daily_ids_info[key])
monthly_ids_info = db.select_one("info",
"key == 'monthly_ids'")
month_key = self._current_aggr["time_key"][:7]
if monthly_ids_info and monthly_ids_info[
"time_key"][:7] == month_key:
for key in monthly_ids_info:
if key not in ("key", "time_key"):
self._monthly_ids[key] = set(monthly_ids_info[key])
except Exception as err:
logger.error(
f"Failed to restore daily_ids and monthly_ids from db: {err}"
)
# Setup our helper thread
_monitor_instances.add(self)
global _helper_thread
if _helper_thread is None:
_helper_thread = HelperThread()
_helper_thread.start()
def write_something():
db = ItemDB(filename).ensure_table("settings", "!id")
with db:
db.put("settings", dict(id="server_reset", value="42", mt=42))
db.close()
return "wrote something"
def read_something():
db = ItemDB(filename).ensure_table("settings", "!id")
xx.extend(db.select_all("settings"))
return "read something"
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_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_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 run_slow_transaction():
db = ItemDB(filename)
with db:
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)
def get_from_db(what):
filename = apiserver.user2filename(USER)
return ItemDB(filename).select_all(what)
def test_index_fails():
# Invalid index/table names - not str
db = ItemDB(":memory:")
for name in [(), 4, b"", [], {}]:
with raises(TypeError):
db.ensure_table("items", name)
# Invalid index/table names - not an identifier
db = ItemDB(":memory:")
for name in ["foo bar", "foo-bar", "33", "foo!"]:
with raises(ValueError):
db.ensure_table("items", name)
# Reserved
for name in ["!_ob", "_ob"]:
with raises(IndexError):
db.ensure_table("items", name)
# Cannot add a unique key
filename = get_fresh_filename()
db = ItemDB(filename).ensure_table("foo", "meh")
with closing(db):
assert "foo" in db.get_table_names()
with raises(IndexError):
db = ItemDB(filename).ensure_table("foo", "!key")
# Cannot use a normal key as a unique key
filename = get_fresh_filename()
db = ItemDB(filename).ensure_table("foo", "key")
with closing(db):
assert "foo" in db.get_table_names()
with raises(IndexError):
db = ItemDB(filename).ensure_table("foo", "!key")
# Cannot use a unique key as a normal key
filename = get_fresh_filename()
db = ItemDB(filename).ensure_table("foo", "!key")
with closing(db):
assert "foo" in db.get_table_names()
with raises(IndexError):
db = ItemDB(filename).ensure_table("foo", "key")
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_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_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_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
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 _write_aggr(self, aggr):
""" Write the given aggr to disk. Used by the helper thread to write
aggr's that we put on the _write_queue.
"""
for key in aggr.keys():
if not key.startswith("time_"):
break
else:
return # Nothing in here, return now
try:
db = ItemDB(self._filename)
# Write aggegation
db.ensure_table(TABLE_NAME, "!time_key")
with db:
x = db.select_one(TABLE_NAME, "time_key == ?",
aggr["time_key"])
if x is not None:
merge(x, aggr)
aggr = x
db.put(TABLE_NAME, aggr)
# Prepare daily ids info
daily_ids_info = {}
for key in self._daily_ids.keys():
daily_ids_info[key] = list(self._daily_ids[key])
daily_ids_info["key"] = "daily_ids"
daily_ids_info["time_key"] = self._current_aggr["time_key"][:10]
# Prepare montly ids info
monthly_ids_info = {}
for key in self._monthly_ids.keys():
monthly_ids_info[key] = list(self._monthly_ids[key])
monthly_ids_info["key"] = "monthly_ids"
monthly_ids_info["time_key"] = self._current_aggr["time_key"][:7]
# Write
db.ensure_table("info", "!key")
with db:
db.put("info", daily_ids_info)
db.put("info", monthly_ids_info)
except Exception as err:
logger.error("Failed to save aggregations: " + str(err))
import requests
import json
import math
from itemdb import ItemDB
from build import Build
from build import Item
import build_types
import timeit
itemdb = ItemDB()
# importing wynn item DB for stashing
def get_items():
try:
with open('item_file.json', "r") as item_file:
item_list = json.load(item_file)
print("successfully loaded")
except:
request = requests.get(
'https://api.wynncraft.com/public_api.php?action=itemDB&category=all'
)
item_list = request.json()['items']
with open('item_file.json', "w") as item_file:
json.dump(item_list, item_file)
return item_list
itemdb.add_json(get_items())
epic = Build()
def test_missing_values2():
filename = get_fresh_filename()
db = ItemDB(filename)
db.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.ensure_table("items", "value")
with db:
db.put("items", dict(id=3, mt=100, value=41))
#
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)
with db:
db.put("items", dict(id=5, mt=100, value=999))
assert len(db.select("items", "value == 999")) == 1
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 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 run_read():
db = ItemDB(filename)
for i in range(30):
time.sleep(0.05)
item = db.select_one("items", "id == 3")
read.append(item["value"])
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")