Lecture 18
-----------------

- Object-relational extensions of SQL

- Problem solving using SQL

- No lecture exercises today

- Homework is due on thursday at midnight

- Note the reduced office hours on Submitty for this week only


Object-relational SQL
--------------------------


Hierarchy

Complex data types

Spatial data extension (PostGIS)

Data types: points, lines, polygons, collections of (points, lines, polygons)
Functions:

     distance(point, point)  - restaurants near me
     inside(point, polygon)  - place is in city/district/etc.
     distance(line_segment)  - directions


Fulltext

review = ''
review like '%good%'


fat cats ate fat rats

fat  1,4
cats 2
ate  3
rats 5

------

Problem solving with SQL + Procedural elements
-----------------------------------------------

actors(id, name, surname)
movieroles(actorid, movieid)


Degree of Kevin Bacon
-------------------------

Degree 0: Kevin Bacon (KB) himself!

Degree 1: People who have been in a movie with KB

Degree 2: People who have been in a movie with a degree 1 person

...

--- Degree 1

select DISTINCT
    m2.actorid
    , a2.name
    , a2.surname
from
    actors a1  --find KB id
    , movieroles m1  -- find movies for KB
    , movieroles m2
    , actors a2
where
    a1.id = m1.actorid
    and m1.movieid = m2.movieid
    and a1.name like 'Kevin%'
    and a1.surname = 'Bacon'
    and m2.actorid <> a1.id
    and a2.id = m2.actorid;

--- Degree 2

WITH degree1 as (
select DISTINCT
    m2.actorid
from
    actors a1  --find KB id
    , movieroles m1  -- find movies for KB
    , movieroles m2
where
    a1.id = m1.actorid
    and m1.movieid = m2.movieid
    and a1.name like 'Kevin%'
    and a1.surname = 'Bacon'
    and m2.actorid <> a1.id
)
select
    count(DISTINCT m4.actorid)
from
    actors a1  --find KB id
    , movieroles m1  -- find movies for KB
    , movieroles m2
    , movieroles m3
    , movieroles m4
where
    a1.id = m1.actorid
    and m1.movieid = m2.movieid
    and a1.name like 'Kevin%'
    and a1.surname = 'Bacon'
    and m2.actorid <> a1.id
    and m3.actorid = m2.actorid
    and m4.movieid = m3.movieid
    and m4.actorid <> a1.id
    and m4.actorid not in (SELECT actorid FROM degree1) ;


begin ;

   - store kb id in a table - degree 0
   - find all degree 1 and add to the table
   - find degree 2 by joining with this table

end ;


CREATE OR REPLACE FUNCTION degree(inputdegree int, inputname varchar, inputsurname varchar) RETURNS int AS $$
DECLARE
   curcount INT ;
   count INT;
   curdegree INT ;
BEGIN

   drop table if exists actordegree ;

   create table actordegree as
   select id, 0 as degree
   from actors
   where name like inputname|| '%' and surname like inputsurname|| '%' ;

   create index ad1idx on actordegree(degree, id) ;
   curcount = 1;
   curdegree = 0 ;

   LOOP
     INSERT INTO actordegree
      select DISTINCT
          m2.actorid
    	  , curdegree+1
       from
         actordegree a
        , movieroles m1  -- find movies for KB
        , movieroles m2
       where
         a.id = m1.actorid
         and m1.movieid = m2.movieid
         and a.degree = curdegree
         and m2.actorid not in (select id from actordegree) ;
      SELECT count(*) FROM actordegree INTO count ;
      IF count = curcount or curdegree=inputdegree THEN
          exit ;
      END IF ;
      curcount = count ;
      curdegree = curdegree+1;
    END LOOP ;
    -- drop table actordegree ;
    RETURN curcount ; 
END ;
$$ LANGUAGE plpgsql ;