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

Class relations

Lock classes and relationships

Inheritance

User Class

class User():
    def __init__(self,name, email, securityLevel):
        self.name=name
        self.email=email
        self.securityLevel=securityLevel

    def getName(self):
        return self.name
    
    def getEmail(self):
        return self.email

    def getSecurityLevel(self):
        return self.securityLevel

    def __str__(self):
        return self.name + ", " + self.email

Staff Class

from user import User

class Staff(User):
    def __init__(self, name, email, securityLevel, position):
        self.name=name
        self.email=email
        self.securityLevel=securityLevel
        self.position=position

    def getPosition(self):
        return self.position

    def __str__(self):
        return super(Staff, self).__str__() + ", " + self.position

main

from staff import Staff
def setUser():
    s=Staff('Sue', 's@gmail.com', 'high', 'Janitor')
    print(s.getEmail())
    print(s.getName())
    print(s)

def main():
    setUser()

main()