{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f9d35c77-081b-4c7a-8ef2-bfbe450d83d1",
   "metadata": {},
   "source": [
    "\n",
    "## Lecture 16: Triggers, Views, Access Control\n",
    "\n",
    "### Announcements\n",
    "\n",
    "- Homework #4, please check your answers even if you have submitted\n",
    "  against the expected answers.\n",
    "\n",
    "- Homework #5 to be out soon (later today or tomorrow morning), due\n",
    "  next week.\n",
    "\n",
    "- Multiple lecture exercises this week to get you read for the next\n",
    "  exam. Additional SQL exercises on monday and thursday.\n",
    "\n",
    "- Lecture Exercises 15 and 17 out today, due on thursday at 2pm.\n",
    "\n",
    "- Lecture Exercises 16 and 18 out on thursday, due on monday at\n",
    "  2pm.\n",
    "\n",
    "- We will drop the lowest 4 lecture exercises, use them to learn and\n",
    "  practice.\n",
    "\n",
    "\n",
    "- Exam #2 next week thursday. It will be solely on SQL. It will\n",
    "  incorporate everything we did in class, including access control\n",
    "  (today or or thursday).\n",
    "\n",
    "- Procedural SQL that will be covered on Thursday will not be in Exam #2.\n",
    "\n",
    "  - Look at last year's exams to find examples of what to expect.\n",
    "  \n",
    "  - Exam questions will expect you to have learnt the types of skills\n",
    "    we exercises in the homeworks this semester.\n",
    "\n",
    "  - Make sure you know how to write correct SQL in paper and pencil,\n",
    "    and in a limited amount of time.\n",
    "\n",
    "    Solving past exams without looking at answers is the best way to\n",
    "    do this.\n",
    "\n",
    "\n",
    "-------\n",
    "\n",
    "### Transactions\n",
    "\n",
    "```\n",
    "begin ;\n",
    "[[transaction block treated as a unit]]\n",
    "commit ;\n",
    "```\n",
    "\n",
    "### Recap of foreign keys\n",
    "\n",
    "```\n",
    "drop table if exists tablec;\n",
    "drop table if exists tableb;\n",
    "drop table if exists tablea;\n",
    "\n",
    "create table tablea (\n",
    "       id     int primary key\n",
    "       , name varchar(10)\n",
    ") ;\n",
    "\n",
    "create table tableb (\n",
    "       id     int primary key\n",
    "       , aid    int\n",
    "       , name varchar(10)\n",
    "       , foreign key (aid) references tablea(id)\n",
    "         on delete set null on update set null\n",
    ") ;       \n",
    "\n",
    "create table tablec (\n",
    "       id     int primary key\n",
    "       , aid    int not null\n",
    "       , bid    int not null\n",
    "       , name varchar(10)\n",
    "       , foreign key (aid) references tablea(id)\n",
    "         on delete cascade on update cascade\n",
    "       , foreign key (bid) references tableb(id)\n",
    "         on delete set null on update cascade\n",
    ") ;       \n",
    "\n",
    "insert into tablea values(1,'a');\n",
    "insert into tablea values(2,'b');\n",
    "insert into tablea values(3,'c');\n",
    "insert into tablea values(4,'d');\n",
    "insert into tablea values(5,'e');\n",
    "\n",
    "insert into tableb values(11,2,'aa');\n",
    "insert into tableb values(12,2,'bb');\n",
    "insert into tableb values(13,4,'cc');\n",
    "insert into tableb values(14,4,'dd');\n",
    "insert into tableb values(15,4,'ee');\n",
    "\n",
    "insert into tablec values(111,2,11,'abc');\n",
    "insert into tablec values(112,3,11,'def');\n",
    "insert into tablec values(113,3,12,'fgh');\n",
    "insert into tablec values(114,5,13,'ghi');\n",
    "insert into tablec values(115,5,13,'hij');\n",
    "```\n",
    "\n",
    "\n",
    "![Table structure with foreign key constraints](lecture16_figures/tablea_tableb_tablec_tuple_dependencies.png)\n",
    "\n",
    "### Triggers\n",
    "\n",
    "- Programs that are activated when a database change happens (tables,\n",
    "  views, etc.)\n",
    "\n",
    "- Execute either before or after any INSERT, UPDATE, or DELETE\n",
    "  operation\n",
    "\n",
    "  - INSERT: only has NEW value for row\n",
    "  - DELETE: only have OLD value for row\n",
    "  - UPDATE: have both OLD value and a NEW value\n",
    "\n",
    "- Two types of triggers:\n",
    "  - BEFORE (the change)\n",
    "  - AFTER (the change)\n",
    "\n",
    "- Trigger level:\n",
    "  - Statement level (once per SQL statement)\n",
    "  - Row level:  for every tuple  you are changing (once per modified row)\n",
    "\n",
    "- UPDATE triggers can moreover be set to fire only if certain columns\n",
    "are mentioned in the SET clause of the UPDATE statement\n",
    "\n",
    "- Statement-level BEFORE triggers naturally fire before the statement\n",
    "  starts to do anything.\n",
    "\n",
    "- Row-level BEFORE triggers fire immediately before a particular row\n",
    "is operated on.\n",
    "\n",
    "- Statement-level AFTER triggers fire at the very end of the\n",
    "  statement.\n",
    "\n",
    "- Row-level AFTER triggers fire at the end of the statement (but\n",
    "before any statement-level AFTER triggers).\n",
    "\n",
    "Trigger functions invoked by per-statement triggers should always\n",
    "return NULL.\n",
    "\n",
    "\n",
    "- Trigger functions invoked by per-row triggers can return a table row.\n",
    "\n",
    "- A BEFORE trigger can return NULL to skip the operation for the current\n",
    "row.\n",
    "\n",
    "- For row-level INSERT and UPDATE triggers only, the returned row\n",
    "becomes the row that will be inserted or will replace the row being\n",
    "updated.\n",
    "\n",
    "```\n",
    "drop function tableafix_f() cascade;\n",
    "drop function tablebfix_f() cascade;\n",
    "\n",
    "drop table if exists diff;\n",
    "\n",
    "drop table diff;\n",
    "create table diff (\n",
    "     whenchanged date\n",
    "     , change text\n",
    ") ;     \n",
    "```\n",
    "-- For deletes from table a, add a tuple to table diff describing the\n",
    "   change.\n",
    "   \n",
    "```\n",
    "drop function tableafix_f() cascade ;\n",
    "\n",
    "CREATE FUNCTION tableafix_f () RETURNS trigger AS $$\n",
    "begin\n",
    "   insert into diff\n",
    "   values(now()::date, 'id: '||(old.id)::varchar||' name: '||old.name) ;\n",
    "   return old ;\n",
    "end ;\n",
    "$$ language plpgsql ;\n",
    "\n",
    "CREATE TRIGGER tableafix AFTER DELETE ON tablea\n",
    "  FOR EACH ROW EXECUTE FUNCTION tableafix_f();\n",
    "```\n",
    "\n",
    "-- For deletes from table b, first change the referencing tuples in\n",
    "   table c to be lowest b id in the table.\n",
    "\n",
    " ```\n",
    "drop function tablebfix_f() cascade;\n",
    "\n",
    "create function tablebfix_f () returns trigger as $$\n",
    "begin\n",
    "   UPDATE tablec\n",
    "   SET bid = (select min(id) from tableb)   \n",
    "   WHERE bid = OLD.id ;\n",
    "   RETURN OLD ;\n",
    "end ;\n",
    "$$ language plpgsql ;\n",
    "\n",
    "create trigger tablebfix BEFORE delete on tableb\n",
    "   for each row execute function tablebfix_f() ;\n",
    "```\n",
    "\n",
    "![Table structure with foreign key constraints](lecture16_figures/tables_with_triggers.png)\n",
    "\n",
    "### VIEWS\n",
    "\n",
    "- Anonymous views are only available when executing the query.\n",
    "\n",
    "```\n",
    "WITH starbakers AS\n",
    "   (SELECT distinct b.baker, b.fullname, count(r.baker) as numstarbaker\n",
    "    FROM  bakers b left join results r on\n",
    "          r.result = 'star baker' and r.baker = b.baker\n",
    "    GROUP BY b.baker)\n",
    "SELECT\n",
    "   s.baker\n",
    "   , s.fullname\n",
    "   , s.numstarbaker\n",
    "   , count(*) as numrank1\n",
    "FROM\n",
    "   technicals t\n",
    "   , starbakers s\n",
    "WHERE\n",
    "   s.baker = t.baker\n",
    "   and t.rank = 1\n",
    "GROUP BY\n",
    "   s.baker\n",
    "   , s.fullname\n",
    "   , s.numstarbaker\n",
    "ORDER BY\n",
    "   numrank1 asc ;\n",
    "```\n",
    "\n",
    "- (Named) views are persistent, they can be used in multiple queries like any other relation. \n",
    "\n",
    "```\n",
    "\n",
    "CREATE VIEW starbakers AS\n",
    "SELECT distinct b.baker, b.fullname, count(r.baker) as numstarbaker\n",
    "    FROM  bakers b left join results r on\n",
    "          r.result = 'star baker' and r.baker = b.baker\n",
    "    GROUP BY b.baker ;\n",
    "```\n",
    "\n",
    "-- Views are just queries, not stored tuples!\n",
    "\n",
    "-- Let's us create a view for older bakers.\n",
    "\n",
    "```\n",
    "drop view olderbakers ;\n",
    "\n",
    "create view olderbakers(baker, age) as\n",
    "select baker, age\n",
    "from bakers\n",
    "where age > 45\n",
    "with check option;\n",
    "\n",
    "create view ages(age) as\n",
    "select distinct age from bakers ;\n",
    "```\n",
    "\n",
    "- Updateable views should have no distinct and not multiple relations,\n",
    "  so that an update can be unambiguously translated to a tuple in the base relations.\n",
    "\n",
    "- See course notes and the book for more details.\n",
    "\n",
    "----\n",
    "\n",
    "### What now? Recursion?\n",
    "\n",
    "```\n",
    "-- children\n",
    "\n",
    "select parent, child from parents ;\n",
    "\n",
    "-- grand children\n",
    "\n",
    "select\n",
    "   p1.parent\n",
    "   , p2.child\n",
    "from\n",
    "   parents p1\n",
    "   , parents p2\n",
    "where\n",
    "   p1.child = p2.parent;\n",
    "\n",
    "-- great grand children\n",
    "\n",
    "select\n",
    "   p1.parent\n",
    "   , p3.child\n",
    "from\n",
    "   parents p1\n",
    "   , parents p2\n",
    "   , parents p3\n",
    "where\n",
    "   p1.child = p2.parent\n",
    "   and p2.child = p3.parent;\n",
    "\n",
    "\n",
    "-- great great grand children\n",
    "\n",
    "select\n",
    "   p1.parent\n",
    "   , p4.child\n",
    "from\n",
    "   parents p1\n",
    "   , parents p2\n",
    "   , parents p3\n",
    "   , parents p4\n",
    "where\n",
    "   p1.child = p2.parent\n",
    "   and p2.child = p3.parent\n",
    "   and p3.child = p4.parent;\n",
    "\n",
    "-- All ancestors\n",
    "\n",
    "-- (parent, child) is an ancestor of level 1\n",
    "\n",
    "-- (anc, desc) is an ancestor of level x\n",
    "   (desc, child) is a parent relation\n",
    "\n",
    "   (anc, child) is an ancestor of level x+1\n",
    "\n",
    "\n",
    "\n",
    "WITH RECURSIVE anc AS\n",
    "   (SELECT parent as anc, child as desc, 1 as level FROM parents\n",
    "    union\n",
    "    SELECT a.anc, p.child, a.level+1\n",
    "    FROM  anc a, parents p\n",
    "    WHERE a.desc = p.parent)\n",
    "SELECT\n",
    "    *\n",
    "FROM\n",
    "    anc\n",
    "WHERE\n",
    "    level > 2\n",
    ";\n",
    "    \n",
    "```\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d654aa55-ba53-4ea4-a22f-fa57768b5413",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
