Diagram from the Use Case Video

Lock ERD from Video

Here is the screenshot for the ERD

Basic xml queries

here are the xml queries from the video.

/****************************
basic xml queries
***********************/

--create a table with an xml data type
Create table xmlTest
(
 xmltestId serial primary key,
 test xml
);

--insert some xml
Insert into xmlTest(test)
values('<person><name>Joe Smith</name><email>js@gmail.com</email><age>34</age></person>'),
('<person><name>Kelly Jones</name><email>kj@gmail.com</email><age>24</age></person>'),
('<person><name>Lila Bard</name><email>lb@gmail.com</email><age>25</age></person>');

Select * from xmltest;

Select xmlelement(name name, test) from xmltest;

--return emails
Select xpath('//email/text()', test) from xmltest;

--return ages
Select xpath('//age/text()', test)from xmltest;

--put xml tags around query results
Select xmlforest(personlastname, personemail)
from person;

 --output a table as xmlf= fragment
Select Table_to_xml('granttype',True, True, '');

--output query results as xml fragment
Select query_to_xml('Select personfirstname, personlastname, positionname
    from person
    join employee using (personkey)
    join employeeposition using (employeekey)
 join jobposition using (positionkey)'
 ,True, True, '') ;

Select table_to_xmlschema('granttype',True, True, '');

Basic JSON queries

Here is the code I used in the video on Basic JSON queries.

/*********************
basic JSON queries 
*********************/

--create a table with Json as a datatype
Create table JsonTest
(
  testID serial primary key,
  test Json
);

--insert some records
Insert into JsonTest(test)
Values('{"testname" : "testOne", "testnumber" : 123, "testdate" :"2019-05-02"}'),
('{"testname" : "testtwo", "testnumber" : 234, "testdate" :"2019-05-08"}'),
('{"testname" : "testthree", "testnumber" : 345, "testdate" :"2019-05-12"}');


--query the value of a field
Select test ->>'testname' as "name" from jsontest;

--creates an array of sorts putting each element on its own row
Select Json_each(test) from Jsontest;

--returns the keys in the Json record
Select Distinct Json_object_keys(test) from jsontest;

--gets tne count of tests
Select count(cast (test ->> 'testnumber' as integer)) from Jsontest;

--returns the results of a query as Json
--double click the results to get a little dialog box
--copy and paste to a text editor to see the full results
with j as
(
 Select personfirstname, personlastname, positionname
    from person
    join employee using (personkey)
    join employeeposition using (employeekey)
 join jobposition using (positionkey)
)
Select json_agg(j) from j;

Testing

here is the code for the tests. the tests and the classes being tested are available on github.com/spconger

import unittest
from item import Item
from orderitem import OrderItem
from order import Order
from payment import Payment

class ItemTest(unittest.TestCase):
    def setUp(self):
        self.item=Item(1,'item1', 30.00)
    
    def test_string(self):
        self.assertEqual(str(self.item), self.item.itemname)

    def test_GetItemNumber(self):
        self.assertEqual(self.item.getItemNumber(),1)

    def test_GetItemPrice(self):
        self.assertEqual(self.item.getItemPrice(),30.00)

class OrderItemTest(unittest.TestCase): 
    def setUp(self):
        self.item=Item(1,'item1', 30.00)
        self.oitem=OrderItem(self.item,2)

    def test_Quantity(self):
        self.assertEqual(self.oitem.getQuantity(),2)
    
    def test_item(self):
        item=self.oitem.getItem()
        self.assertEqual(str(item), 'item1')

class OrderTest(unittest.TestCase):
    def setUp(self):
        self.item1=Item(1,'beer',6.25)
        self.item2=Item(2,'chips', 4.50)

        self.orderitem1=OrderItem(self.item1,2)
        self.orderitem2=OrderItem(self.item2,1)

        self.order=Order()
        self.order.addOrderItems(self.orderitem1)
        self.order.addOrderItems(self.orderitem2)

    def test_CalculateTotal(self):
        payment=self.order.calcTotal()
        self.assertEqual(str(payment), 'Your payment today will be 17.0')