/opt/firebird/bin/isql -q -u sysdba -p phoenix << EOF

connect /opt/firebird/DeskShows.fdb;

-- *********************************************
-- Add a table which will holds the uuid of the last loaded show
-- *********************************************

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'LAST_LOADED_SHOW')) THEN 
BEGIN
EXECUTE STATEMENT 'CREATE TABLE last_loaded_show(show_uuid VARCHAR(36));';
EXECUTE STATEMENT 'ALTER TABLE last_loaded_show ADD CONSTRAINT fk_lastloadedshow_show FOREIGN KEY (show_uuid) 
  REFERENCES shows(uuid) ON UPDATE CASCADE ON DELETE CASCADE;';
END

END!!
SET TERM ; !!

-- *********************************************
-- commit the change here to allow the insert of data into the new table
-- *********************************************
COMMIT;

SET TERM !! ;

EXECUTE BLOCK AS
BEGIN
IF( NOT EXISTS ( SELECT * FROM LAST_LOADED_SHOW) ) THEN
EXECUTE STATEMENT 'INSERT INTO last_loaded_show SELECT uuid from shows WHERE last_saved = (SELECT MAX(last_saved) FROM shows WHERE default_show = 0);';
ELSE
EXECUTE STATEMENT 'UPDATE last_loaded_show SET show_uuid = 
  (SELECT uuid from shows WHERE last_saved = (SELECT MAX(last_saved) FROM shows WHERE default_show = 0)) WHERE show_uuid IS NULL;';
END!!

-- *********************************************
-- set the last loaded memory of a show to be null if that memory is deleted
-- *********************************************

CREATE OR ALTER TRIGGER clear_show_last_loaded_memory FOR memories
ACTIVE AFTER DELETE AS
BEGIN
  UPDATE shows set last_loaded_memory = null WHERE last_loaded_memory = old.uuid;
END!!

SET TERM ; !!

COMMIT;

EOF