--
-- $Id: photo-album-lite-create.sql,v 1.1 2002/07/09 17:35:10 rmello Exp $
--
-- We are going to subclass acs_object as opposed to using
-- the content repository.

create table pl_folders (
       folder_id	integer
			constraint pl_folders_id_nn
			not null
			constraint pl_folders_pk
			primary key
			constraint pl_folders_obj_fk
			references acs_objects(object_id),
       title		varchar(100)
			constraint pl_folders_title_nn
			not null,
       descr		varchar(4000),
       default_exp_date date,
       default_prefix   varchar(250),
       deleted_p        char(1) default 'f'
                        constraint pl_folders_deleted_p_ck
			check (deleted_p in ('t','f'))
);

create table pl_photos (
       photo_id	       integer
		       constraint pl_photos_id_nn
		       not null
		       constraint pl_photos_pk
		       primary key
		       constraint pl_photos_obj_fk
		       references acs_objects(object_id),
       folder_id       integer
                       constraint pl_photos_folder_nn
		       not null
		       constraint pl_photos_folder_fk
		       references pl_folders,
       exposure_date   date,
       sort_n	       integer
		       constraint pl_photos_sort_nn
		       not null,
       caption	       varchar(3000),
       client_filename varchar(256)
		       constraint pl_photos_file_nn
		       not null,
       file_extension  varchar(4)
                       constraint pl_photos_file_xtn_nn
		       not null,
       thumb_width     integer,
       thumb_height    integer,
       thumb_kbytes    integer,
       med_width       integer,
       med_height      integer,
       med_kbytes      integer,
       orig_width      integer,
       orig_height     integer,
       orig_kbytes     integer,
       indexable_stuff varchar(4000),
       deleted_p       char(1) default 'f'
                       constraint pl_photos_deleted_p_ck
		       check (deleted_p in ('t','f'))
);

-- we use this temp table to avoid the "table is mutating" problem
-- when re-calculating the sort_n's after a photo is deleted.
create table pl_reshuffle_queue (
   folder_id integer
);

-- We use this sequence to make sure we can give
-- photos a unique sort_n when we move them into
-- a new folder.  We assume no folder will have
-- more than a million photos
create sequence pl_moved_photo_sort_n_seq start with 1000000;

-- object system support

-- I. photos

begin
  acs_object_type.create_type (
    supertype     => 'acs_object',
    object_type   => 'pl_photo',
    pretty_name   => 'Photo',
    pretty_plural => 'Photos',
    table_name    => 'PL_PHOTOS',
    id_column     => 'PHOTO_ID'
  );
end;
/
show errors;

-- II. folders

begin
  acs_object_type.create_type (
    supertype     => 'acs_object',
    object_type	  => 'pl_folder',
    pretty_name   => 'Folder',
    pretty_plural => 'Folders',
    table_name    => 'PL_FOLDERS',
    id_column     => 'FOLDER_ID'
  );
end;
/
show errors;

@@ photo-album-lite-views
@@ photo-album-lite-plsql
@@ photo-album-lite-triggers

create index pl_photo_idx
on pl_photos(indexable_stuff)
indextype is ctxsys.context;

prompt **
prompt ** Setting up search...
prompt ** NOTE: this requires that you grant the execute privilege
prompt **       on ctx_ddl to your server user.
prompt ** For more information see 
prompt **  @yourserver/packages/photo-album-lite/sql/photo-album-lite-create.sql
prompt **
prompt ** Don't worry if this fails...the package will still work just fine
prompt ** except that the search index will not be rebuilt automatically.
prompt **

-- keep this index in sync with a dbms_job
-- WARNING: you need to explicitly grant execute
-- privileges on ctx_ddl to the aolserver database user
-- for this to work!!!!
--
-- Here's how:
--
-- 1. log in to sql*plus as ctxsys
-- 2. SQL> grant execute on ctx_ddl to yourservicename;
--

-- this table should have just one row
-- to hold the dbms job for rebuilding the
-- search index.
create table pl_jobs (
  job integer not null primary key
);

create or replace procedure pl_rebuild_idx
is
begin 
  ctx_ddl.sync_index('pl_photo_idx');
end;
/
show errors;

variable pl_rebuild_idx_job number;
begin 
  dbms_job.submit(:pl_rebuild_idx_job, 
                 'pl_rebuild_idx;', 
                 interval => 'sysdate + 1/6');
  insert into pl_jobs values (:pl_rebuild_idx_job);
end;
/
show errors;
print pl_rebuild_idx_job