{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5cc78f61-ffa5-4cdc-9825-4158f75b73d8",
   "metadata": {},
   "source": [
    "\n",
    "## Lecture 18: Procedural SQL and Database Program Complexity\n",
    "\n",
    "\n",
    "### Announcements\n",
    "\n",
    "1. Exam #2 is being graded, hw#5 will be graded afterwards.\n",
    "\n",
    "   - Some code just did not run at all, you need to be careful about\n",
    "     simple bugs like missing semicolons. \n",
    "   \n",
    "2. Hw#6 to be posted very soon, due next week thursday\n",
    "\n",
    "   - Expect one reasonable sized, or two smaller homeworks\n",
    "\n",
    "3. No lecture exercise today\n",
    "\n",
    "\n",
    "\n",
    "## PL/PGSQL\n",
    "\n",
    "```\n",
    "-- Running queries\n",
    "\n",
    "CREATE OR REPLACE FUNCTION allstars() RETURNS varchar AS $$\n",
    "DECLARE\n",
    "   outstr VARCHAR ;\n",
    "   myrow record ;\n",
    "   myname VARCHAR ;\n",
    "BEGIN\n",
    "   outstr = '';\n",
    "   FOR myrow IN\n",
    "        SELECT baker \n",
    "        FROM results\n",
    "        WHERE result = 'star baker'\n",
    "        ORDER BY episodeid ASC\n",
    "   LOOP\n",
    "      SELECT fullname INTO myname FROM bakers WHERE baker = myrow.baker ;\n",
    "      outstr = outstr || myname || E'\\n';\n",
    "   END LOOP ;\n",
    "   RETURN outstr ;\n",
    "\n",
    "END ;\n",
    "$$ LANGUAGE plpgsql ;\n",
    "```\n",
    "\n",
    "```\n",
    "-- returning a set of tuples\n",
    "\n",
    "create table summary (\n",
    "      baker varchar(10)\n",
    "      , num  int\n",
    ") ;      \n",
    "\n",
    "\n",
    "CREATE OR REPLACE FUNCTION get_summary() RETURNS SETOF summary AS $$\n",
    "DECLARE\n",
    "    myrow summary%ROWTYPE ;\n",
    "BEGIN\n",
    "    FOR myrow in \n",
    "        select baker, count(*) as num\n",
    "        from results\n",
    "        where result = 'star baker'\n",
    "\tgroup by baker\n",
    "        order by baker\n",
    "    LOOP\n",
    "        RETURN NEXT myrow ;\n",
    "    END LOOP ;\n",
    "    RETURN  ;\n",
    "END ;\n",
    "$$ LANGUAGE plpgsql ;\n",
    "```\n",
    "\n",
    "- Example use:\n",
    "\n",
    "```\n",
    "\n",
    "select g.baker, count(*) as numt, g.num as starbakercnt\n",
    "from get_summary() g, technicals t\n",
    "where g.baker = t.baker and t.rank=1\n",
    "group by g.baker, g.num ;\n",
    "```\n",
    "\n",
    "### Complex problem - use of procedures to reduce query complexity\n",
    "\n",
    "### Example: Degree K of an input actor X:\n",
    "\n",
    "\n",
    "- Degree 0: actor themselves\n",
    "\n",
    "- Degree 1: all actors who played in a movie with X\n",
    "\n",
    "- Degree 2: all actors who played in a movie\n",
    "          with a degree 1 actor (but are not in degree 0 or 1)\n",
    "\n",
    "- Degree k: all actors who played in a movie\n",
    "          with a degree k-1 actor (but are not in degree 0 - (k-1) )\n",
    "\n",
    "How can we do with queries?\n",
    "\n",
    "- Degree 0:\n",
    "\n",
    "```\n",
    "select id\n",
    "from actors\n",
    "where name like 'Patrick%'  and surname = 'Stewart';\n",
    "```\n",
    "\n",
    "- Degree 1:\n",
    "\n",
    "\n",
    "```\n",
    "select count(distinct mr2.actorid)\n",
    "from\n",
    "   movieroles mr1\n",
    "   , actors a\n",
    "   , movieroles mr2\n",
    "where\n",
    "   a.name like 'Patrick%'\n",
    "   and a.surname = 'Stewart'\n",
    "   and mr1.actorid = a.id\n",
    "   and mr2.movieid = mr1.movieid\n",
    "   and mr2.actorid <> a.id;\n",
    "```\n",
    "\n",
    "- Degree 2:\n",
    "\n",
    "```\n",
    "select count(distinct mr4.actorid)\n",
    "from\n",
    "   movieroles mr1\n",
    "   , actors a\n",
    "   , movieroles mr2\n",
    "   , movieroles mr3\n",
    "   , movieroles mr4\n",
    "where\n",
    "   a.name like 'Patrick%'\n",
    "   and a.surname = 'Stewart'\n",
    "   and mr1.actorid = a.id\n",
    "   and mr2.movieid = mr1.movieid\n",
    "   and mr2.actorid <> a.id\n",
    "   and mr3.actorid = mr2.actorid --- movies for degree 1 people\n",
    "   and mr4.movieid = mr3.movieid --- actors for mr3.movieid\n",
    "   and mr4.actorid not in (  --- degree 1 people\n",
    "         select distinct mr2.actorid\n",
    "         from\n",
    "           movieroles mr1\n",
    "           , movieroles mr2\n",
    "        where\n",
    "          mr1.actorid = a.id\n",
    "          and mr2.movieid = mr1.movieid) ;\n",
    "\n",
    "\n",
    " count  \n",
    "--------\n",
    " 101682\n",
    "\n",
    "```\n",
    "\n",
    "Now, let's instead create a function for any k:\n",
    "\n",
    "```\n",
    "CREATE OR REPLACE FUNCTION degree_of(fname VARCHAR, lname VARCHAR, k int)\n",
    "      RETURNS INT AS $$\n",
    "DECLARE\n",
    "   curdegree int ;\n",
    "   outnum int ;\n",
    "BEGIN\n",
    "   DROP TABLE IF EXISTS tmpdegree ;\n",
    "   CREATE TABLE tmpdegree (actorid int, degree int) ;\n",
    "\n",
    "   INSERT INTO tmpdegree\n",
    "   select id, 0 from actors\n",
    "   where name like fname || '%'\n",
    "   and surname = lname ;\n",
    "\n",
    "   curdegree = 1 ;\n",
    "   loop\n",
    "      insert into tmpdegree\n",
    "       select\n",
    "           distinct mr2.actorid, curdegree\n",
    "       from\n",
    "           movieroles mr1, tmpdegree a, movieroles mr2\n",
    "       where\n",
    "          mr1.actorid = a.actorid\n",
    "          and a.degree = curdegree-1\n",
    "          and mr2.movieid = mr1.movieid\n",
    "          and mr2.actorid not in (select actorid from tmpdegree);\n",
    "\n",
    "       curdegree = curdegree + 1 ;\n",
    "       IF curdegree > k THEN\n",
    "           EXIT ;\n",
    "       END IF ;\t     \n",
    "   end loop  ;\n",
    "\n",
    "   SELECT count(*) INTO outnum from tmpdegree WHERE degree = k ;\n",
    "   DROP TABLE IF EXISTS tmpdegree ;\n",
    "\n",
    "   RETURN outnum ;\n",
    "END ;\n",
    "$$ LANGUAGE plpgsql ;\n",
    "```\n",
    "\n",
    "### Object-relational Extensions of SQL\n",
    "\n",
    "-----\n",
    "\n",
    "```\n",
    "CREATE TABLE cities (\n",
    "     name            text\n",
    "     , population      float\n",
    "     , altitude        int     -- in feet\n",
    ");\n",
    "\n",
    "CREATE TABLE capitals (\n",
    "     state           char(2)\n",
    ") INHERITS (cities);\n",
    "\n",
    "\n",
    "insert into cities values('New York City', 8175133, 33);\n",
    "insert into cities values('Syracuse', 145170, 380);\n",
    "insert into capitals values('Albany', 97856, 312, 'NY');\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33479137-0e87-4ee3-a3c9-97479d504e18",
   "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
}
