def test_setup_db(dbfile):
dbfile = pathlib.Path(dbfile).resolve()
if dbfile.exists(): dbfile.unlink()
with sql.Database(dbfile) as db:
with db.cursor() as c:
c.executescript("""
CREATE TABLE categories (
id INTEGER PRIMARY KEY AUTOINCREMENT,
category TEXT,
parent INTEGER REFERENCES categories,
UNIQUE (category,parent) ON CONFLICT IGNORE
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
category INTEGER REFERENCES categories,
type TEXT,
date timestamp,
num TEXT,
sourcename TEXT,
memo TEXT,
quantity REAL,
cost REAL,
total REAL,
dateadded timestamp /* To enforce unique values */
);
""")
def setUp(self):
self.db = sql.Database(":memory:")
self.db.addtables(
sql.Table("""CREATE TABLE test (a TEXT, b INT);"""),
sql.Table(
"""CREATE TABLE test2 (a INTEGER PRIMARY KEY AUTOINCREMENT, b INT);"""
))
self.table1 = self.db.getadvancedtable("test")
self.table2 = self.db.getadvancedtable("test2")
return super().setUp()
def test_update_db(file, dbfile):
""" TEST FUNCTION """
from alcustoms import IntegerPointer
totalitems = IntegerPointer()
today = datetime.datetime.today()
def recurse(category, categoryid=None):
for item in category['Items']:
v = {
"".join(k.lower().split()): item[k]
for k in ["Type", "Date", "Num", "Memo", "Source Name"]
}
for k, Key in [("quantity", "Qty"), ("cost", "Cost Price"),
("total", "Amount")]:
v[k] = item[Key]
v['date'] = datetime.datetime.strptime(v['date'], "%m/%d/%Y")
v['category'] = categoryid
## Have to check that we're not adding duplicates
## Unfortunately, there are no unique identifiers for lineitems
row = ordertable.quickselect(**v).first()
if row:
## Check if row was added during this run: if it wasn't
## then we are assuming that it's a duplicate
if row.dateadded != today:
continue
## If it was added as part of this loop, then it's a different
## lineitem, even if everything else is the same
v['dateadded'] = today
ordertable.addrow(**v)
totalitems.increment()
for cat, cdict in category['Categories'].items():
cid = categorytable.get_or_addrow(category=cat,
parent=categoryid).first()
recurse(cdict, cid)
with open(file, 'r') as f:
data = json.load(f)
with sql.Database(dbfile,
row_factory=sql.advancedrow_factory,
detect_types=sql.PARSE_DECLTYPES) as db:
ordertable = db.getadvancedtable("orders")
categorytable = db.getadvancedtable("categories")
with db.cursor() as c:
recurse(data)
def recurseitems(category, items=0):
items += len(category['Items'])
for cat in category['Categories'].values():
items = recurseitems(cat, items)
return items
items = recurseitems(data)
print("Data Total Items:", items)
print("Items Added:", totalitems)
print("Total Items in DB:", len(ordertable.selectall()))
def test_analyse_function(file, dbfile):
""" TEST FUNCTION """
with open(file, 'r') as f:
data = json.load(f)
db = sql.Database(dbfile,
row_factory=sql.advancedrow_factory,
detect_types=sql.PARSE_DECLTYPES)
try:
items = db.getadvancedtable("orders").selectall()
finally:
db.close()
items = {item.item: item for item in items}
inventory = data['Categories']['Inventory']
for itemcategory in inventory['Categories'].values():
for item, subdict in itemcategory['Categories'].items():
itemname = item.split("(")[0].strip()
if itemname in items:
print(itemname)
else:
print(">>>", itemname)
def loaddatabase(self, file):
askclose = tkmessagebox.askokcancel(
"Load New Database",
"We must close the current database to open the new one.\nProceed?"
)
if not askclose: return
self.commandqueue = []
self.commandqueueindex = 0
self.database.close()
self.showoutput(f">>>>>>>>>> Database Closed <<<<<<<<<<", "system")
if file != MEMORY:
shutil.copy(file, TEMPDB)
file = TEMPDB
self.database = sql.Database(file)
self.database.row_factory = sql.dict_factory
if file != MEMORY:
self.showoutput(f">>>>>>>>>> Database {file} Loaded <<<<<<<<<<",
"system")
else:
self.showoutput(">>>>>>>>>> Sample Database Loaded <<<<<<<<<<",
"system")
return True
def loaddb(self):
file = filedialog.askopenfilename()
if not file:
return
file = pathlib.Path(file).resolve()
if not file.exists():
messagebox.showerror("Invalid File", "File does not exist")
return
try:
file = shutil.copy(str(file),
str(pathlib.Path.cwd().resolve() / "temp"))
except Exception as e:
messagebox.showerror(
"Copy Failure",
f"Failed to create work-copy of database:\n{e}")
return
try:
db = sql.Database(file, row_factory=sql.dict_factory)
except:
messagebox.showerror("Invalid File", "Could not load Database")
return
return self.parent.loadexplorer(db)
def setUp(self):
self.connection = sql.Database(":memory:")