-- /packages/photo-album/sql/photo-album-create.sql
--
-- data model to support ACS photo ablum application
--
-- Copyright (C) 2000-2001 ArsDigita Corporation
-- @author Tom Baginski (bags@arsdigita.com)
-- @creation-date 12/11/2000
--
-- @cvs-id $Id: photo-album-create.sql,v 1.1 2002/07/09 17:35:10 rmello Exp $
--
-- This is free software distributed under the terms of the GNU Public
-- License.  Full text of the license is available from the GNU Project:
-- http://www.fsf.org/copyleft/gpl.html

-- ported by Walter McGinnis (wtem@olywa.net), 2001-06-10
-- the content repository has been changed to be similar 
-- to the original photo-album's storage scheme 
-- (i.e. not using blobs in the database)
-- replaced photo-album's non-standard storage with
-- content-repository's standard one
-- one the key's to it is the adjustment to the content_item__new constructor

create table pa_albums (
    pa_album_id	      integer 
		      constraint pa_albums_id_fk
		      references cr_revisions on delete cascade
		      constraint pa_alubms_id_pk 
		      primary key,
    story	      varchar2(4000)
);

comment on table pa_albums is '
  Table for storing custom fields of albums within content repository.  
  A pa_album is used to group zero or more pa_photos.
';

comment on column pa_albums.story is '
  Story behind the album.
  This may be changed to a clob in the future if many people want to upload 
  stories longer than 4000 characters.  
  varchar2(4000) used for ease of development.
';


declare
 attr_id        acs_attributes.attribute_id%TYPE;
begin
 -- create the content type
 content_type.create_type (
   content_type  => 'pa_album',
   pretty_name   => 'Photo album',
   pretty_plural => 'Photo albums',
   table_name    => 'pa_albums',
   id_column     => 'pa_album_id'
 );

 -- create content type attributes
 attr_id := content_type.create_attribute (
   content_type   => 'pa_album',
   attribute_name => 'story',
   datatype       => 'text',
   pretty_name    => 'Story',
   pretty_plural  => 'Stories',
   column_spec    => 'varchar2(4000)'
 );
end;
/
show errors;


create table pa_photos (
    pa_photo_id	      integer
                      constraint pa_photos_id_fk
                      references cr_revisions on delete cascade
                      constraint pa_photo_pk
                      primary key,
    caption	      varchar2(500),
    story	      varchar2(4000),
    user_filename     varchar2(250)
);

comment on table pa_photos is '
   Table for storing custom fields of photos within content repository.
   A pa_photo stores descriptive attribute information about a user
   uploaded photo.  The actually binary image files are stored using
   associated images.
';

declare
 attr_id        acs_attributes.attribute_id%TYPE;
begin

 -- create the content type
 content_type.create_type (
   content_type  => 'pa_photo',
   pretty_name   => 'Album photo',
   pretty_plural => 'Album photos',
   table_name    => 'pa_photos',
   id_column     => 'pa_photo_id'
 );

 -- create content type attributes
 attr_id := content_type.create_attribute (
   content_type   => 'pa_photo',
   attribute_name => 'caption',
   datatype       => 'text',
   pretty_name    => 'Short caption for display under photo',
   pretty_plural  => 'Short captions for display under photos',
   column_spec    => 'varchar2(500)'
 );

 attr_id := content_type.create_attribute (
   content_type   => 'pa_photo',
   attribute_name => 'story',
   datatype       => 'text',
   pretty_name    => 'Story',
   pretty_plural  => 'Stories',
   column_spec    => 'varchar2(4000)'
 );

 attr_id := content_type.create_attribute (
   content_type   => 'pa_photo',
   attribute_name => 'user_filename',
   datatype       => 'text',
   pretty_name    => 'User filename',
   pretty_plural  => 'User filenames',
   column_spec    => 'varchar2(250)'
 );
end;
/
show errors;

-- wtem@olywa.net, 2001-08-3
  -- now that we are going with the new content-repository
  -- storage of binaries
  -- we no longer need a specialized pa_image content_type
     -- it is now replaced by cr_revisions.content (which holds file name)
     -- file_size is now under cr_revisions.content_size
  -- we use the standar image content_type instead

begin
  content_type.register_child_type (
    parent_type => 'pa_album',
    child_type => 'pa_photo'
  );

  content_type.register_child_type (
    parent_type => 'pa_photo',
    child_type => 'image'
  );
end;
/

create table pa_package_root_folder_map (
  package_id       constraint pa_pack_fldr_map_pk
		   primary key
                   constraint pa_pack_fldr_map_pack_id_fk
		   references apm_packages,
  folder_id	   constraint pa_pack_fldr_map_fldr_id_fk
		   references cr_folders
		   constraint pa_pack_fldr_map_fldr_id_unq
		   unique
);

create index pa_package_folder_map_by_pack on pa_package_root_folder_map (package_id, folder_id);

comment on table pa_package_root_folder_map is '
  Table to keep track of root folder for each instance of the photo-album
  Used for sub-site support.  Each instance of the photo album has a unique 
  root folder.  All folders, pa_albums, pa_photos, and images within the 
  package instance are descendants of the root folder.
  The one-to-one mapping is done using this table to avoid adding a column to the apm_packages
  that pertains only to the photo-album.
';

-- wtem@olywa.net, 2001-09-27
-- replaced non-standard pa_files_to_delete with new cr_files_to_delete calls

-- check for the two default acceptable mime_types in cr_mime_types
-- add them if necessary

declare
  v_count int;
begin
  select count(*) into v_count from cr_mime_types where mime_type = 'image/jpeg';

  if v_count = 0 then
    insert into cr_mime_types values ('JPEG image', 'image/jpeg', 'jpeg');
  end if;

  select count(*) into v_count from cr_mime_types where mime_type = 'image/gif';

  if v_count = 0 then
    insert into cr_mime_types values ('GIF image', 'image/gif', 'gif');
  end if;
end;
/
show errors;

@@ plsql-packages.sql

@@ permissions.sql