Classes and tests Code Testing video

lock.py

class Lock():
    def __init__(self, door, location,securitylevel):
        self.door=door
        self.location=location
        self.securitylevel=securitylevel
        self.status='locked'

    def setStatus(self, status):
        self.status=status

    def getStatus(self):
        return self.status

    def getDoor(self):
        return self.door

    def getLoction(self):
        return self.location

    def getSecurityLevel(self):
        return self.securitylevel

    def __str__(self):
        return str(self.door) + " " + self.status

scan.py

from lock import Lock
import datetime

class Scan():
    def __init__(self, door, location, securitylevel, card):
        self.lock=Lock(door,location,securitylevel)
        self.card=card
        self.scantime=datetime.datetime.now()
    
    def getLock(self):
        return self.lock
    
    def getCard(self):
        return self.card

    def getScanTime(self):
        return self.scantime

The Tests: test.py

import unittest
from lock import Lock
from scan import Scan

class LockTest(unittest.TestCase):
    def setUp(self):
        self.lock=Lock('3176','BE', 'normal')

    def test_lockstring(self):
        self.assertEqual(str(self.lock), '3176 locked')

    def test_getStatus(self):
        self.assertEqual(self.lock.getStatus(), 'locked')

    def test_setStatus(self):
        self.lock.setStatus('unlocked')
        self.assertEqual(self.lock.getStatus(), 'unlocked')
    
    def test_GetDoor(self):
        self.assertEqual(self.lock.getDoor(), '3176')

class ScanTest(unittest.TestCase):
    def setUp(self):
        self.scan=Scan('3176', 'BE', 'normal', 315643)

    def test_GetCard(self):
        self.assertEqual(self.scan.getCard(), 315643)

System Queries

Select * from information_schema.tables;
Select Table_name from information_schema.tables
Where table_schema='public';
Select * from information_schema.columns;
Select column_name, data_type from information_schema.columns
where table_name='grantapplication';
Select sequence_name from information_schema.sequences;

Select column_name, data_type, constraint_name, constraint_Type
From information_schema.columns
Join information_schema.table_constraints
on information_schema.columns.table_name=information_schema.table_constraints.table_name
where information_schema.columns.table_name='grantapplication'
Order by column_name;

Select * from pg_catalog.pg_tablespace;
Select * from pg_catalog.pg_extension;

Advanced Query code

/******************************
* Set Operations
******************************/
CREATE TEMP TABLE email
(firstname text,
lastname text,
email text);

INSERT INTO email(firstname, lastname, email)
values('Jordan', 'Lawrence', 'jordanl@gmail.com'),
('Tammy', 'Standish', 'tstandish@msn.com'),
('Lester', 'Roberts', 'lr@yahoo.com'),
('Lynn', 'Kellerman', 'kellerman@gmail.com');

SELECT lastname, firstname, email, 'temptable' as tblSource from email
UNION
SELECT PersonLastname, personfirstname, personemail, 'Persontable'
FROM person
JOIN personaddress
USING (personkey)
WHERE personaddressCity='Bellevue';

SELECT Personlastname lastname,
personfirstname firstname,
personemail email,
'donor' "role"
FROM person
JOIN donation USING(personkey)
Where donationamount >=2000
UNION
SELECT Personlastname lastname,
personfirstname firstname,
personemail email,
'client'
FROM person
JOIN grantapplication USING(personkey)
WHERE granttypekey=2;

SELECT personkey, personlastname, personfirstname
FROM person
JOIN donation USING(personkey)
INTERSECT
SELECT personkey, personlastname, personfirstname
FROM person
JOIN grantapplication USING(personkey);

SELECT personaddresscity
FROM personaddress
JOIN person USING (personkey)
JOIN donation USING (personkey)
INTERSECT
SELECT personaddresscity
FROM personaddress
JOIN person USING (personkey)
JOIN grantapplication USING (personkey);

SELECT personaddresscity
FROM personaddress
JOIN person USING (personkey)
JOIN donation USING (personkey)
EXCEPT
SELECT personaddresscity
FROM personaddress
JOIN person USING (personkey)
JOIN grantapplication USING (personkey);

SELECT granttypename FROM granttype
EXCEPT
SELECT granttypename FROM grantapplication
JOIN granttype USING (granttypekey);
/*****************************
* Windows Functions
*****************************/

SELECT granttypename, grantapplicationkey, grantapplicationamount,
RANK() OVER (PARTITION BY granttypeName ORDER BY Grantapplicationamount DESC)
FROM grantapplication
JOIN granttype ON granttype.granttypekey=grantapplication.granttypekey
WHERE granttypename='Food';

SELECT granttypename, grantapplicationkey, grantapplicationamount,
DENSE_RANK() OVER (PARTITION BY granttypeName ORDER BY Grantapplicationamount
DESC)
FROM grantapplication
JOIN granttype ON granttype.granttypekey=grantapplication.granttypekey
WHERE granttypename='Food';

SELECT grantapplicationkey, granttypename, grantapplicationamount,
ROW_NUMBER() OVER(ORDER BY grantapplicationkey)
FROM grantapplication
JOIN granttype using(granttypekey);

SELECT grantapplicationkey, granttypename, grantapplicationamount,
ROW_NUMBER() OVER(ORDER BY grantapplicationamount)
FROM grantapplication
JOIN granttype using(granttypekey);

SELECT *
FROM
(SELECT grantapplicationkey, granttypename, grantapplicationamount,
ROW_NUMBER() OVER(ORDER BY grantapplicationamount)
FROM grantapplication
JOIN granttype using(granttypekey))grants
WHERE ROW_NUMBER BETWEEN 20 and 30;

SELECT *
FROM
(SELECT grantapplicationkey, granttypename, grantapplicationamount,
ROW_NUMBER() OVER(ORDER BY grantapplicationamount)
FROM grantapplication
JOIN granttype using(granttypekey))grants
WHERE ROW_NUMBER BETWEEN 20 and 30;

SELECT granttypename, grantapplicationamount,
LAST_VALUE(grantapplicationamount) OVER
(PARTITION BY granttypekey ORDER BY Grantapplicationamount
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM grantapplication
JOIN granttype using(granttypekey);

/**************************************
* Pivot table with CROSSTAB
*************************************/

CREATE TEMP TABLE applications2018
(
GranttypeName TEXT,
applicationdate DATE,
applciationamount NUMERIC
);



INSERT INTO applications2018
SELECT Granttypename, grantapplicationDate, grantapplicationamount
FROM grantapplication
JOIN granttype ON granttype.granttypekey = grantapplication.granttypekey
WHERE EXTRACT (YEAR FROM grantapplicationdate)=2018;

SELECT *
FROM CROSSTAB('SELECT EXTRACT(MONTH FROM applicationdate)::INTEGER,
granttypename, SUM(applciationamount)
FROM applications2018
GROUP BY 1,2 ORDER BY 1, 2')
FINAL_RESULT(Month INTEGER, Food NUMERIC, Rent NUMERIC, School NUMERIC,
Dental NUMERIC, Medical NUMERIC, Childcare NUMERIC ,Misc NUMERIC );

CREATE TEMP TABLE citydonations
(
"Month" Integer,
city text,
amount numeric
);

CREATE TEMP TABLE citydonations
(
"Month" Integer,
city text,
amount numeric
);

INSERT INTO citydonations
SELECT EXTRACT(MONTH FROM Donationdate), PersonaddressCity, donationamount
FROM (
SELECT DINSTINCT ON(donationkey)
donationkey,
donationdate,
personaddresscity,
donationamount
FROM donation
JOIN personaddress
USING (personkey)
) donations;


SELECT *
FROM CROSSTAB('SELECT "Month" :: INTEGER,

City, SUM(amount) FROM citydonations
GROUP BY 1,2
ORDER BY 1, 2')
FINAL_RESULT(Month INTEGER,
Seattle NUMERIC,
Redmond NUMERIC,
"New York" NUMERIC,
Bellevue NUMERIC,
Tukwilla NUMERIC,
Kent NUMERIC);

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;