Security Login, Posgresql

/*
Permision consist of SQL commands--
SELECT, INSERT, UPDATE, EXCECUTE, DELETE, DROP. CREATE, ALTER
Anything that is not granted is denied
A user should be given all the permissions necessary
to do what they need to do in the database
But no more.
*/
Create role tenantrole;
Grant SELECT on "Apartment" to tenantrole;
Grant SELECT on "Lease" to tenantrole;
Grant UPDATE on "Tenant" to Tenantrole;
Grant SELECT on "Tenant" to tenantrole;

Create role johnsmith with password 'password';
Grant tenantrole to johnsmith;
Alter role johnsmith with login;

The test after logging in as johnsmith

Select * from "Apartment";
Update "Apartment"
SET "Bedrooms"=3
WHERE "ApartmentNumber"=102;

SQL for Postgresql

/*
List all the tenants.
List all the current maintenance requests.
Which apartments are currently occupied? join apartment and lease
Which apartments are empty? Left outer join with Apartment and Lease
Update maintenance request 2 to give it a completion date of today.
Insert a new tenant: Tammy Benedict, tbenedict@gmail.com, phone: 2065551200. She has no sub tenants
Add a new 6 month lease starting today on apartment 205, standard deposit, no pets
Record her first rent payment
Turn in the SQL and screenshots of the the answers
You may have to put the column and table names in double quotes if you made your database
from lucidcharts
*/
Select * from "person";
Select * from donation;
Select personlastname, personfirstname, personaddressStreet, personaddressCity
From person
inner join personaddress
on person.personkey=personaddress.personkey;

--outer join
Select granttypename, grantapplication.granttypekey
From granttype
Left outer join grantapplication
On granttype.granttypekey=grantapplication.grantTypekey
Where grantapplication.granttypekey is null;

--updates are to change existing records
Select * from person where personkey=2;
Update person
Set Personlastname='Caroll',
personemail='lindaCaroll@gmail.com'
WHERE personkey=2;

Insert into person(personlastname, personfirstname, personemail, personprimaryphone, persondateadded)
Values('Nelson','Tom', 'tom.nelson@msn.com', '2065553233', current_date);

select * from person where personlastname='Nelson';

Insert into Donation(personkey, donationamount, donationDate)
Values(134, 500.00, current_date);
Select * from donation where personkey =134;

Code for Assignment 8 Video

Customer Class

'''
Class called Customer
Every customer has a name,
number, email, phone and they
can collect rewards points
An object is collection of related
functions. They all relate
to one topic like customer.
The idea is to make it easier
to manage complex code
by breaking it into the kinds
of objects that actually make up
the contents of the program
abstraction
encapsulation
inheritance
polymorphism
'''
class Customer():
    def __init__(self, number, name, phone, email, rewards):
        self.number=number
        self.name=name
        self.phone=phone
        self.email=email
        self.rewards=rewards

    def setPhone(self, phone):
        self.phone=phone

    def getPhone(self):
        return self.phone

    def getName(self):
        return self.name

    def getEmail(self):
        return self.email

    def getRewards(self):
        return self.rewards

    def addRewards(self, points):
        self.rewards += points

    def useRewards(self, points):
        self.rewards = self.rewards-points

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

Item Class

'''
class item
It will represent an item to purchase
It will have a number, a name and a price
'''
class Item():
    def __init__(self, number, name, price):
        self.number=number
        self.name=name
        self.price=price

    def getNumber(self):
        return self.number

    def getName(self):
        return self.name

    def getPrice(self):
        return self.price
    

Purchase Class

'''
Purchase class
to show purchase of an item
it will have a list of items
and methods for
Totaling the purchase
and totaling the points
str method that outputs
basically a receipt
'''
class Purchase():
    def __init__(self):
        self.items=[]

    def addItem(self, item):
        self.items.append(item)

    def totalItems(self):
        total=0
        for item in self.items:
            total += item.price
        return total

    def totalPoints(self):
        total=self.totalItems()
        points=int(total)
        return points

    def __str__(self):
        receipt=""
        for item in self.items:
            receipt =receipt + item.name +"\t\t" + str(item.price) + "\n"
        total = self.totalItems()
        receipt=receipt + "\t\t" + str(total)
        return receipt
    
    

Main

from customer import Customer
from item import Item
from purchase import Purchase

def main():
    cust=Customer(123, 'Steve', 'steve@spconger.com', '2065551234', 0)
    purch = Purchase()
    cont='y'
    while cont=='y':
        itemNumber=int(input('Enter item Number. '))
        itemName=input("Enter item name ")
        itemPrice=float(input("Enter item price "))
        item=Item(itemNumber, itemName, itemPrice)
        purch.addItem(item)
        cont=input("Add another item? y to continue. " )
        cont=cont.lower()
    print(purch)
    cust.addRewards(purch.totalPoints())
    print ("your total rewards are", cust.getRewards())

main()
                             

Customer and customer test code from peer 8 video

'''
Class called Customer
Every customer has a name,
number, email, phone and they
can collect rewards points
An object is collection of related
functions. They all relate
to one topic like customer.
The idea is to make it easier
to manage complex code
by breaking it into the kinds
of objects that actually make up
the contents of the program
abstraction
encapsulation
inheritance
polymorphism
'''
class Customer():
    def __init__(self, number, name, phone, email, rewards):
        self.number=number
        self.name=name
        self.phone=phone
        self.email=email
        self.rewards=rewards

    def setPhone(self, phone):
        self.phone=phone

    def getPhone(self):
        return self.phone

    def getName(self):
        return self.name

    def getEmail(self):
        return self.email

    def getRewards(self):
        return self.rewards

    def addRewards(self, points):
        self.rewards += points

    def useRewards(self, points):
        self.rewards = self.rewards-points

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

Customer test

from customer import Customer

def main():
    c1 = Customer(123, 'Joe Smith', '2065551234', 'js@gmail.com', 10)
    print(c1)
    c1.addRewards(20)
    print(c1.getRewards())
    c1.useRewards(13)
    print(c1.getRewards())
    print(c1.getEmail())
    c2=Customer(234, 'Lynn Jones', '2065553456', 'Lynn@gamail.com',100)
    print("******************")
    print(c1)
    print()
    print(c2)

main()