Lecture 16
--------------

Lecture 15 recording had to sound, not sure why. I notified the
mediasite.


--------------

Hw#5 is out! It is due after Exam #2, but it will count for Exam #2.

You will need to decide whether to submit or not before the Exam #2
grades and solutions are out!

I highly recommend you work on it before the exam as a way to
prepare. I will provide some partial solutions soon!

--------------

Exam #2 locations:

Last names: Akinyemi - Smith: Classroom (Academy Hall Aud)

Last names: Stadtler - Zhao: JROWL (Science Center) 1C13

Accommodations: you will be notified separately


Exam #2 open book and open notes

IPAD/Computers are allowed, but no typing or taking notes, working on
draft solutions. Open all relevant tabs beforehand.

--------------

WITH statement


WITH rel1 AS (SELECT .... ),
     rel2 AS (SELECT ....)
SELECT
FROM   rel1 r, bakers b
WHERE
     b.baker not in (select ,... from rel2)
GROUP BY
HAVING
    (SELECT.... FROM rel1 ...) = 2


VIEWS:

Views are stored query definitions.

CREATE VIEW winnerbakers(firstname, fullname, age, lastwinepid) AS
SELECT 
    b.baker
    , b.fullname
    , b.age
    , max(r.episodeid)
FROM
    bakers b
    , results r
where    
    b.baker = r.baker
    and r.result = 'star baker'
group by
    b.baker;


select * from winnerbakers ;

select w.firstname, e.firstaired
from winnerbakers w, episodes e
where w.lastwinepid = e.id ;

Updateable views:

A view is updatable is there is a one to one correspondance between
the tuples in the view and the table is derived from.

---> An updatable view should not have: distinct, group by,
     aggregates, joins

SELECT a,b FROM R ; -- updatable

SELECT DISTINCT a,b FROM R ; -- not updatable


CREATE VIEW olderbakers(baker, age)
AS SELECT baker, age FROM bakers WHERE age > 40
WITH CHECK OPTION;

baking=> insert into olderbakers values('River', 20) ;
ERROR:  new row violates check option for view "olderbakers"
DETAIL:  Failing row contains (River, null, 20, null, null).
baking=> insert into olderbakers values('River', 41) ;
INSERT 0 1

------------------------------

Access Control

Databases -  Users/Roles


Roles are sets of users or individual users

CREATE ROLE dbinstructor NOINHERIT;
CREATE ROLE dbstudent LOGIN INHERIT;

GRANT dbstudent TO adalis ;

SET ROLE dbinstructor ;

Privileges

Databases: connect to databases

Tables: query tables, insert/update/delete tables

grant select on <tablename> to <rolename> ;
revoke select on <tablename> from <rolename> ;

grant select on bakers to dbstudent ;
revoke delete on bakers from dbstudent ;

owner of objects have all the rights and can grant to others.

grant select on bakers to sheldon with grant option ;

revoke select on bakers from sheldon ;
revoke grant option on bakers from sheldon ;


Triggers
------------

- Triggered when an insert/update/delete happens

     INSERT OR UPDATE ON results

- Additional conditions using WHEN clause

- Trigger can be before or after the update/insert/delete







CREATE FUNCTION fix_values () RETURNS trigger AS $$
    BEGIN
        IF NEW.age > 80 THEN
	    UPDATE bakers SET age = 80 WHERE baker = NEW.baker ;
	END IF ;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fix_values AFTER INSERT ON bakers
    FOR EACH ROW EXECUTE FUNCTION fix_values ();


CREATE FUNCTION fix_values2() RETURNS trigger AS $$
    BEGIN
        IF NEW.age < 18 THEN
            NEW.age = 18;
	END IF ;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fix_values2 BEFORE INSERT ON bakers
    FOR EACH ROW EXECUTE FUNCTION fix_values2 ();