Lecture 16: Triggers, Views, Access Control

Lecture 16: Triggers, Views, Access Control#

Announcements#

  • Homework #4, please check your answers even if you have submitted against the expected answers.

  • Homework #5 to be out soon (later today or tomorrow morning), due next week.

  • Multiple lecture exercises this week to get you read for the next exam. Additional SQL exercises on monday and thursday.

  • Lecture Exercises 15 and 17 out today, due on thursday at 2pm.

  • Lecture Exercises 16 and 18 out on thursday, due on monday at 2pm.

  • We will drop the lowest 4 lecture exercises, use them to learn and practice.

  • Exam #2 next week thursday. It will be solely on SQL. It will incorporate everything we did in class, including access control (today or or thursday).

  • Procedural SQL that will be covered on Thursday will not be in Exam #2.

    • Look at last year’s exams to find examples of what to expect.

    • Exam questions will expect you to have learnt the types of skills we exercises in the homeworks this semester.

    • Make sure you know how to write correct SQL in paper and pencil, and in a limited amount of time.

      Solving past exams without looking at answers is the best way to do this.


Transactions#

begin ;
[[transaction block treated as a unit]]
commit ;

Recap of foreign keys#

drop table if exists tablec;
drop table if exists tableb;
drop table if exists tablea;

create table tablea (
       id     int primary key
       , name varchar(10)
) ;

create table tableb (
       id     int primary key
       , aid    int
       , name varchar(10)
       , foreign key (aid) references tablea(id)
         on delete set null on update set null
) ;       

create table tablec (
       id     int primary key
       , aid    int not null
       , bid    int not null
       , name varchar(10)
       , foreign key (aid) references tablea(id)
         on delete cascade on update cascade
       , foreign key (bid) references tableb(id)
         on delete set null on update cascade
) ;       

insert into tablea values(1,'a');
insert into tablea values(2,'b');
insert into tablea values(3,'c');
insert into tablea values(4,'d');
insert into tablea values(5,'e');

insert into tableb values(11,2,'aa');
insert into tableb values(12,2,'bb');
insert into tableb values(13,4,'cc');
insert into tableb values(14,4,'dd');
insert into tableb values(15,4,'ee');

insert into tablec values(111,2,11,'abc');
insert into tablec values(112,3,11,'def');
insert into tablec values(113,3,12,'fgh');
insert into tablec values(114,5,13,'ghi');
insert into tablec values(115,5,13,'hij');

Table structure with foreign key constraints

Triggers#

  • Programs that are activated when a database change happens (tables, views, etc.)

  • Execute either before or after any INSERT, UPDATE, or DELETE operation

    • INSERT: only has NEW value for row

    • DELETE: only have OLD value for row

    • UPDATE: have both OLD value and a NEW value

  • Two types of triggers:

    • BEFORE (the change)

    • AFTER (the change)

  • Trigger level:

    • Statement level (once per SQL statement)

    • Row level: for every tuple you are changing (once per modified row)

  • UPDATE triggers can moreover be set to fire only if certain columns are mentioned in the SET clause of the UPDATE statement

  • Statement-level BEFORE triggers naturally fire before the statement starts to do anything.

  • Row-level BEFORE triggers fire immediately before a particular row is operated on.

  • Statement-level AFTER triggers fire at the very end of the statement.

  • Row-level AFTER triggers fire at the end of the statement (but before any statement-level AFTER triggers).

Trigger functions invoked by per-statement triggers should always return NULL.

  • Trigger functions invoked by per-row triggers can return a table row.

  • A BEFORE trigger can return NULL to skip the operation for the current row.

  • For row-level INSERT and UPDATE triggers only, the returned row becomes the row that will be inserted or will replace the row being updated.

drop function tableafix_f() cascade;
drop function tablebfix_f() cascade;

drop table if exists diff;

drop table diff;
create table diff (
     whenchanged date
     , change text
) ;     

– For deletes from table a, add a tuple to table diff describing the change.

drop function tableafix_f() cascade ;

CREATE FUNCTION tableafix_f () RETURNS trigger AS $$
begin
   insert into diff
   values(now()::date, 'id: '||(old.id)::varchar||' name: '||old.name) ;
   return old ;
end ;
$$ language plpgsql ;

CREATE TRIGGER tableafix AFTER DELETE ON tablea
  FOR EACH ROW EXECUTE FUNCTION tableafix_f();

– For deletes from table b, first change the referencing tuples in table c to be lowest b id in the table.

drop function tablebfix_f() cascade;

create function tablebfix_f () returns trigger as $$
begin
  UPDATE tablec
  SET bid = (select min(id) from tableb)   
  WHERE bid = OLD.id ;
  RETURN OLD ;
end ;
$$ language plpgsql ;

create trigger tablebfix BEFORE delete on tableb
  for each row execute function tablebfix_f() ;

Table structure with foreign key constraints

VIEWS#

  • Anonymous views are only available when executing the query.

WITH starbakers AS
   (SELECT distinct b.baker, b.fullname, count(r.baker) as numstarbaker
    FROM  bakers b left join results r on
          r.result = 'star baker' and r.baker = b.baker
    GROUP BY b.baker)
SELECT
   s.baker
   , s.fullname
   , s.numstarbaker
   , count(*) as numrank1
FROM
   technicals t
   , starbakers s
WHERE
   s.baker = t.baker
   and t.rank = 1
GROUP BY
   s.baker
   , s.fullname
   , s.numstarbaker
ORDER BY
   numrank1 asc ;
  • (Named) views are persistent, they can be used in multiple queries like any other relation.


CREATE VIEW starbakers AS
SELECT distinct b.baker, b.fullname, count(r.baker) as numstarbaker
    FROM  bakers b left join results r on
          r.result = 'star baker' and r.baker = b.baker
    GROUP BY b.baker ;

– Views are just queries, not stored tuples!

– Let’s us create a view for older bakers.

drop view olderbakers ;

create view olderbakers(baker, age) as
select baker, age
from bakers
where age > 45
with check option;

create view ages(age) as
select distinct age from bakers ;
  • Updateable views should have no distinct and not multiple relations, so that an update can be unambiguously translated to a tuple in the base relations.

  • See course notes and the book for more details.


What now? Recursion?#

-- children

select parent, child from parents ;

-- grand children

select
   p1.parent
   , p2.child
from
   parents p1
   , parents p2
where
   p1.child = p2.parent;

-- great grand children

select
   p1.parent
   , p3.child
from
   parents p1
   , parents p2
   , parents p3
where
   p1.child = p2.parent
   and p2.child = p3.parent;


-- great great grand children

select
   p1.parent
   , p4.child
from
   parents p1
   , parents p2
   , parents p3
   , parents p4
where
   p1.child = p2.parent
   and p2.child = p3.parent
   and p3.child = p4.parent;

-- All ancestors

-- (parent, child) is an ancestor of level 1

-- (anc, desc) is an ancestor of level x
   (desc, child) is a parent relation

   (anc, child) is an ancestor of level x+1



WITH RECURSIVE anc AS
   (SELECT parent as anc, child as desc, 1 as level FROM parents
    union
    SELECT a.anc, p.child, a.level+1
    FROM  anc a, parents p
    WHERE a.desc = p.parent)
SELECT
    *
FROM
    anc
WHERE
    level > 2
;