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')

SalesExample

Here is the class diagram for the salesExample

Here is the link to the diagram https://www.lucidchart.com/invitations/accept/409c3b2a-7232-4813-a321-d66c508d3392

Here is the github link for the sales classes

Here is the github link for the lock classes

Json and XML

Here is the script for sample Json and XML 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;

/****************************
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, '');

Sequence diagrams

Here is the link to the Sales Example code in github. https://github.com/spconger/SaleExamples

The Sales Example Sequence diagram

The login sequence

The login with alternatives sequence