def test_getOrderPackingList(self):
""" getOrderPackingList should return a list of items to be packed for an order
"""
order = exampleOrder
# setup the store and items for the order
OnlineStoreDatabaseUnitTest.db.addOnlineStore(
storeName=order['storeID'])
location = 1
for item in order['items']:
OnlineStoreDatabaseUnitTest.db.addItem(name=item['name'],
stock=1,
location=location)
location += 1
# add the order and get the packing list
orderID = OnlineStoreDatabaseUnitTest.db.addOrder(order)
packingList = OnlineStoreDatabaseUnitTest.db.getOrderPackingList(
orderID)
# assert that the packing list contains the items in the order
self.assertEqual(2, len(packingList))
itemNameList = [item['name'] for item in order['items']]
for itemName in itemNameList:
self.assertTrue(
SQLiteDB.rowListContainsRow(packingList, {"name": itemName}))
def test_getUnprocessedOrders(self):
""" getUnprocessedOrders should return a list of unprocessed orders
"""
order = exampleOrder
# setup the store and items for the order
OnlineStoreDatabaseUnitTest.db.addOnlineStore(
storeName=order['storeID'])
location = 1
for item in order['items']:
OnlineStoreDatabaseUnitTest.db.addItem(name=item['name'],
stock=1,
location=location)
location += 1
# add some orders
orderOne = OnlineStoreDatabaseUnitTest.db.addOrder(order)
orderTwo = OnlineStoreDatabaseUnitTest.db.addOrder(order)
orderThree = OnlineStoreDatabaseUnitTest.db.addOrder(order)
# mark one of the orders as shipped
OnlineStoreDatabaseUnitTest.db.setOrderToShipped(orderOne)
# assert that all orders gotten are unprocessed
unprocessedOrders = OnlineStoreDatabaseUnitTest.db.getUnprocessedOrders(
)
for order in unprocessedOrders:
self.assertTrue(SQLiteDB.rowContainsColumnsWithValues(
order, {"status": 'unprocessed'}),
msg=order)
def test_select(self):
result = SQLiteDB_UnitTests.db.select(
OnlineStoreDatabase.customerTable, ["*"])
self.assertTrue(
SQLiteDB.rowListContainsRow(result, {
"name": "John",
"email": "*****@*****.**"
}))
def test_getRow(self):
# test getting a simple row
result = SQLiteDB_UnitTests.db.getRow(
OnlineStoreDatabase.customerTable, ["*"],
email="*****@*****.**")
self.assertTrue(
SQLiteDB.rowListContainsRow(result, {
"name": "John",
"email": "*****@*****.**"
}))
def test_addOnlineStore(self):
""" The addOnlineStore method should
- Add an online store to the online store table and return its ID
- Return None when unable to add an online store
"""
OnlineStoreDatabaseUnitTest.db.addOnlineStore("Ebay")
stores = OnlineStoreDatabaseUnitTest.db.getOnlineStores()
self.assertTrue(SQLiteDB.rowListContainsRow(stores, {"name": "Ebay"}))
storeID = OnlineStoreDatabaseUnitTest.db.addOnlineStore("Ebay")
self.assertEqual(None, storeID)
def test_update(self):
SQLiteDB_UnitTests.db.update(OnlineStoreDatabase.customerTable,
{"name": "John Smith"},
{"email": "*****@*****.**"})
result = SQLiteDB_UnitTests.db.getRow(
OnlineStoreDatabase.customerTable, ["*"],
email="*****@*****.**")
self.assertTrue(
SQLiteDB.rowListContainsRow(result, {
"name": "John Smith",
"email": "*****@*****.**"
}))
def test_addItem(self):
""" The addItem method should
- Add an item to the items table and return its ID
- Return None when unable to add an item
"""
OnlineStoreDatabaseUnitTest.db.addItem("item", 1, 11)
items = OnlineStoreDatabaseUnitTest.db.getItems()
self.assertTrue(
SQLiteDB.rowListContainsRow(items, {
"name": "item",
"stock": 1,
"location": 11
}))
itemID = OnlineStoreDatabaseUnitTest.db.addItem("item", 1, 11)
self.assertEqual(None, itemID)
def test_addCustomer(self):
""" The addCustomer method should
- Add a customer to the customer table and return its ID
- Return None when unable to add a customer
"""
OnlineStoreDatabaseUnitTest.db.addCustomer("John Doe",
"*****@*****.**")
customers = OnlineStoreDatabaseUnitTest.db.getCustomers()
self.assertTrue(
SQLiteDB.rowListContainsRow(customers, {
"name": "John Doe",
"email": "*****@*****.**"
}))
customerID = OnlineStoreDatabaseUnitTest.db.addCustomer(
"John Doe", "*****@*****.**")
self.assertEqual(None, customerID)
def setUp(self):
# create the testing database
SQLiteDB_UnitTests.db = SQLiteDB(":memory:")
for table in OnlineStoreDatabase.databaseDefinition['tables']:
SQLiteDB_UnitTests.db.createTable(
table, OnlineStoreDatabase.databaseDefinition['tables'][table])
# put some fake data in there
SQLiteDB_UnitTests.db.insert(OnlineStoreDatabase.customerTable, {
"name": "John",
"email": "*****@*****.**"
})
SQLiteDB_UnitTests.db.insert(OnlineStoreDatabase.customerTable, {
"name": "Jack",
"email": "*****@*****.**"
})
SQLiteDB_UnitTests.db.insert(OnlineStoreDatabase.customerTable, {
"name": "Jill",
"email": "*****@*****.**"
})
def test_addListing(self):
""" The addListing method should add a listing to the listing table and return its ID
- Return None when unable to add a listing
"""
storeID = OnlineStoreDatabaseUnitTest.db.addOnlineStore("Ebay")
itemID = OnlineStoreDatabaseUnitTest.db.addItem("door", 1, 11)
listingID = OnlineStoreDatabaseUnitTest.db.addListing(
"door", "Ebay", 9.99)
listings = OnlineStoreDatabaseUnitTest.db.getListings()
self.assertTrue(
SQLiteDB.rowListContainsRow(listings, {
"itemID": "door",
"storeID": "Ebay",
"price": 9.99
}))
listingID = OnlineStoreDatabaseUnitTest.db.addListing(
"incorrectKey", "incorrectKey", 9.99)
self.assertEqual(len(OnlineStoreDatabaseUnitTest.db.getListings()), 1)
class OnlineStoreDatabase:
storeTable = 'onlineStore'
listingTable = 'listing'
orderTable = 'orders'
customerTable = 'customer'
itemTable = 'items'
orderListingLinkTable = "orderListingLink"
databaseDefinition = {
"tables" : {
storeTable : {
"name" : "VARCHAR PRIMARY KEY"
},
customerTable : {
"name" : "VARCHAR",
"email" : "VARCHAR PRIMARY KEY"
},
itemTable : {
"name" : "VARCHAR PRIMARY KEY",
"stock" : "INTEGER",
"location" : "INTEGER UNIQUE"
},
listingTable : {
"itemID" : "VARCHAR",
"storeID" : f"VARCHAR",
"price" : "DOUBLE",
f"FOREIGN KEY (storeID) REFERENCES {storeTable}(name)" : "",
f"FOREIGN KEY (itemID) REFERENCES {itemTable}(name)" : "",
"PRIMARY KEY (itemID, storeID)" : ""
},
orderListingLinkTable : {
"linkID" : "INTEGER PRIMARY KEY AUTOINCREMENT",
"orderID" : "INTEGER",
"itemID" : "VARCHAR",
"storeID" : "VARCHAR",
f"FOREIGN KEY (orderID) REFERENCES {orderTable}(id)" : "",
f"FOREIGN KEY (itemID, storeID) REFERENCES {listingTable}(itemID, storeID)" : ""
},
orderTable : {
"id" : "INTEGER PRIMARY KEY AUTOINCREMENT",
"status": "VARCHAR",
"line1" : "VARCHAR",
"line2" : "VARCHAR",
"country" : "VARCHAR",
"streetNameAndNumber" : "VARCHAR",
"postcode" : "VARCHAR",
"customerEmail": f"VARCHAR",
f"FOREIGN KEY (customerEmail) REFERENCES {customerTable}(email)" : ""
}
}
}
def __init__(self, databaseFile):
self.db = SQLiteDB(databaseFile)
# setup the database with the tables
for table in OnlineStoreDatabase.databaseDefinition['tables']:
self.db.createTable(table, OnlineStoreDatabase.databaseDefinition['tables'][table])
# Adding, Updating and Getting Data
## Updating Data
def setOrderToShipped(self, orderID):
self.db.update(OnlineStoreDatabase.orderTable, {"status" : "shipped"}, {"id" : orderID})
## Adding Data
def addCustomer(self, name, email):
return self.db.add(OnlineStoreDatabase.customerTable, name=name, email=email)
def addItem(self, name, stock, location):
return self.db.add(OnlineStoreDatabase.itemTable, name=name, stock=stock, location=location)
def addOnlineStore(self, storeName):
return self.db.add(OnlineStoreDatabase.storeTable, name=storeName)
def addListing(self, itemID, storeID, price):
return self.db.add(OnlineStoreDatabase.listingTable, itemID=itemID, storeID=storeID, price=price)
def addOrderListingLink(self, orderID, itemID, storeID):
return self.db.add(OnlineStoreDatabase.orderListingLinkTable, orderID=orderID, itemID=itemID, storeID=storeID)
def addOrder(self, orderDict):
# if we don't have this customer yet, add them to the database
if not self.getCustomer(orderDict['user']['email']):
self.addCustomer(orderDict['user']['name'], orderDict['user']['email'])
# if we don't have this listing yet, add it to the database
for item in orderDict['items']:
self.addListing(item['name'], orderDict['storeID'], item['price'])
# add the order to the database
dbOrderDict = {
"status": "unprocessed",
"line1" : orderDict['address']['addressLineOne'],
"line2" : orderDict['address']['addressLineTwo'],
"country" : orderDict['address']['country'],
"streetNameAndNumber" : orderDict['address']["streetNameAndNumber"],
"postcode" : orderDict['address']['postcode'],
"customerEmail": orderDict['user']['email'],
}
orderRowID = self.db.add(OnlineStoreDatabase.orderTable, dictvalues=dbOrderDict)
# add the order-listing links
for item in orderDict['items']:
self.addOrderListingLink(orderRowID, item['name'], orderDict['storeID'])
return orderRowID
## Getting Data
def getUnprocessedOrders(self):
return [x for x in self.db.select(OnlineStoreDatabase.orderTable,
whereDict={"status" : "unprocessed"})]
def getOrderPackingList(self, orderID):
# get the item listings for the order
itemListingsForOrder = [x for x in self.db.select(OnlineStoreDatabase.orderListingLinkTable,
whereDict={ "orderID" : orderID })]
# get the actual items from the listings
itemIDs = [x['itemID'] for x in itemListingsForOrder]
items = []
for itemID in itemIDs:
items += self.db.select(OnlineStoreDatabase.itemTable, whereDict={ "name" : itemID })
return items
def getAllOrderListingLinks(self):
return [x for x in self.db.select(OnlineStoreDatabase.orderListingLinkTable)]
def getOrders(self):
return [x for x in self.db.select(OnlineStoreDatabase.orderTable)]
def getOrder(self, orderID):
return self.db.getRow(OnlineStoreDatabase.orderTable, id=orderID)
def getItems(self):
return [x for x in self.db.select(OnlineStoreDatabase.itemTable)]
def getCustomers(self):
return [x for x in self.db.select(OnlineStoreDatabase.customerTable)]
def getCustomer(self, email):
return self.db.getRow(OnlineStoreDatabase.customerTable, email=email)
def getOnlineStores(self):
return [x for x in self.db.select(OnlineStoreDatabase.storeTable)]
def getListings(self):
return [x for x in self.db.select(OnlineStoreDatabase.listingTable)]
def getOrdersListings(self, orderID):
return [x for x in self.db.select(OnlineStoreDatabase.orderListingLinkTable, whereDict={"orderID":orderID})]
def hasItem(self, name):
whereStr = f'name="{name}"'
return bool([x for x in self.executeQuery(SQL.select("*", OnlineStoreDatabase.itemTable, whereStr=whereStr))])
def hasListing(self, itemID : int, storeID : int):
whereStr = f'storeID={storeID}, itemID={itemID}'
return bool([x for x in self.executeQuery(SQL.select("*", OnlineStoreDatabase.listingTable, whereStr=whereStr))])
def has(self, table, whereValues={}, joinList=None):
for val in whereValues:
if type(whereValues[val]) == str:
whereValues[val] = f'"{whereValues[val]}"'
whereStr = ", ".join([f'{key}={whereValues[key]}' for key in whereValues])
return bool(self.select(table, whereStr=whereStr, joins=joinList))
def close(self):
self.connection.close()
def connect(self, filepath):
self.connection = sqlite3.connect(filepath)
self.cursor = self.connection.cursor()
def isConnected(self):
return True if self.connection != None else False
def tableHasColumns(self, table, columns):
tableColumns = self.cursor.execute(SQL.tableColumns(table))
for col in tableColumns:
if not col[1] in columns:
return False
return True
def tableHasRow(self, tableName, whereColumns):
return self.db.tableHasRow(tableName, whereColumns)
def hasTable(self, tableName):
try:
self.cursor.execute(SQL.numberRows(tableName))
return True
except sqlite3.OperationalError as e:
return False
def __init__(self, databaseFile):
self.db = SQLiteDB(databaseFile)
# setup the database with the tables
for table in OnlineStoreDatabase.databaseDefinition['tables']:
self.db.createTable(table, OnlineStoreDatabase.databaseDefinition['tables'][table])
def test_addOrder(self):
""" The addOrder method should add an order, customer, listing and order-listing link to their tables
"""
order = exampleOrder
# setup the store and items for the order
OnlineStoreDatabaseUnitTest.db.addOnlineStore(
storeName=order['storeID'])
location = 1
order = exampleOrder
for item in order['items']:
OnlineStoreDatabaseUnitTest.db.addItem(name=item['name'],
stock=1,
location=location)
location += 1
orderID = OnlineStoreDatabaseUnitTest.db.addOrder(order)
customers = OnlineStoreDatabaseUnitTest.db.getCustomers()
listings = OnlineStoreDatabaseUnitTest.db.getListings()
ordersListingLinks = OnlineStoreDatabaseUnitTest.db.getOrdersListings(
orderID)
orders = OnlineStoreDatabaseUnitTest.db.getOrders()
self.assertTrue(SQLiteDB.rowListContainsRow(
customers, {
"name": order['user']['name'],
"email": order['user']['email']
}),
msg=customers)
self.assertTrue(SQLiteDB.rowListContainsRow(
listings, {
"itemID": order['items'][0]['name'],
"storeID": order['storeID'],
"price": order['items'][0]['price']
}),
msg=listings)
self.assertTrue(SQLiteDB.rowListContainsRow(
orders, {
"status": "unprocessed",
"line1": order['address']['addressLineOne'],
"line2": order['address']['addressLineTwo'],
"country": order['address']['country'],
"streetNameAndNumber": order['address']['streetNameAndNumber'],
"postcode": order['address']['postcode'],
"customerEmail": order['user']['email']
}),
msg=orders)
linkID = 1
for item in order['items']:
self.assertTrue(SQLiteDB.rowListContainsRow(
ordersListingLinks, {
"linkID": linkID,
"orderID": orderID,
"itemID": item['name'],
"storeID": order['storeID']
}),
msg=ordersListingLinks)
linkID += 1