-- This file repeats the simple movies example database (with
-- movie_info and movie_actors), but showing what is necessary in
-- PostgreSQL 6.5 in order to enforce the referential integrity
-- constraint that the id column of movie_actors serve as a foreign
-- key into movie_info.  We could do that with the three clauses
--
--    references movie_info
--      on delete cascade 
--      on update cascade
--
-- in the creation of the movie_actors table, except PostgreSQL
-- doesn't support that yet.  On the other hand, doing this constraint
-- the hard way will let us see some more general mechanisms that can
-- be used to enforce other constraints as well.

-- First we drop everything we are going to create, in case there are old
-- versions.  We get error messages if there aren't old versions, but that
-- is no big deal.  This way we know we have a clean slate.  One exception
-- is that we don't drop the procedural language PL/pgSQL or its handler
-- function, plpgsql_call_handler(), because those are general purpose
-- mechanisms that may be in use for other purposes than just the trigger
-- we are writing in PL/pgSQL.  If some other trigger (or procedure) were
-- written in PL/pgSQL, and we dropped the language and recreated (reloaded)
-- it, the old triggers/procedures would no longer work.

drop trigger propagate_movie_id_change;
drop function propagate_id_change;
drop rule cascade_movie_deletion;
drop trigger check_actor_fk on movie_actors;
drop function check_actor_foreign_key();
drop table movie_actors;
drop table movie_info;

-- Next we create the tables themselves, much like earlier in the course.

create table movie_info (title varchar(40),
                         director varchar(40),
                         year_made int,
                         id int primary key);

create table movie_actors (id int not null,
                           actor varchar(40) not null);

-- Now to load in a little machinery we need in order to write our trigger:
-- PL/pgSQL, a procedural language based on SQL.  Note that these next
-- two create statements may result in errors if PL/pgSQL is already loaded
-- in, because we didn't drop the language or its call handler function
-- in the drop statements above, for the reason explained there.

create function plpgsql_call_handler() returns opaque as
       '/usr/local/pgsql/lib/plpgsql.so' language 'c';
          
create trusted procedural language 'plpgsql'
       handler plpgsql_call_handler
       lancompiler 'PL/pgSQL';

-- With this infrastructure in place, we can create a trigger to
-- maintain referential integrity in the face of insertions into the
-- movie_actors table, or updates of that table's id column.  We want
-- to check that each row in movie_actors has an id that really
-- corresponds with a row in movie_info.  If this integrity constraint
-- would be violated by an insertion into or update of movie_actors,
-- we prevent the operation from proceding.

-- The trigger is handled by the below function (procedure), written in
-- PL/pgSQL.

create function check_actor_foreign_key() returns opaque as '
declare
    info_row  movie_info%rowtype;
begin
    -- the below check for null, which duplicates what is already
    --  specified in the create table statement, seems to be necessary
    --  here to prevent PostgreSQL from bombing out
    if new.id isnull then
        raise exception ''id can not be null'';
    end if;

    select * into info_row from movie_info where id = new.id;
    if not found then
        raise exception ''id = % is not in table movie_info'' , new.id;
    end if;
    return new;
end;
' language 'plpgsql';


-- Now we install the above function as an actual trigger:

create trigger check_actor_fk before insert or update on movie_actors
 for each row execute procedure check_actor_foreign_key();

-- There is a flip side to referential integrity: we need to worry not only
-- about insertions into movie_actors, but also about deletions from
-- movie_info.  In this case, we'll take the "cascading" approach of also
-- deleting the movie_actors rows that correspond to the deleted movie.
-- It turns out we don't need the heavy artillery of a "trigger" written
-- in PL/pgSQL for this job: we can use PostgreSQL's "rule" mechanism:

create rule cascade_movie_deletion as on delete to movie_info
 do delete from movie_actors where id = old.id;

-- By the way, the "old.id" above refers to the id column in the old row
-- that just got deleted from movie_info.  The PostgreSQL documentation,
-- last I noticed, claimed that the correct syntax was "current.id."  But
-- that doesn't work.

-- A related problem to deletion is updating of the id column in
-- movie_info.  We'll handle this one again with the "cascade" option,
-- which in this case means to propagate the change in id number
-- through to the movie_actors, so that they stay associated with the
-- same movie, even though its id number has changed.  At first blush,
-- it appears that we can again do this with a rule:

-- create rule propagate_movie_id_change as on update to movie_info
--  do update movie_actors set id = new.id where id = old.id;

-- However, as you can guess by the commenting out of that rule, and
-- the words "at first blush", this doesn't work.  The problem is that
-- the rule tries to change the id numbers in the movie_actors table
-- *before* the id number is changed in the movie_info table.  This
-- update to the id in movie_actors is rejected by our earlier trigger.

-- Instead, we apparently need to go back to using the trigger
-- mechanism, which can be set to fire "after" an update.

create function propagate_id_change() returns opaque as '
declare
begin
    update movie_actors set id = new.id where id = old.id;
    return new;
end;
' language 'plpgsql';

create trigger propagate_movie_id_change after update on movie_info
 for each row execute procedure propagate_id_change();

-- Finally, with all the mechanisms in place to protect referential integrity,
-- we can load the data into the tables, just like in the old days.  One
-- key difference is now it really matters that we load the data into the
-- movie_info table first, and then the movie_actors table afterwards.

\copy movie_info from /Net/solen/home/m/a/max/MC39/movie_info
\copy movie_actors from /Net/solen/home/m/a/max/MC39/movie_actors