-- /packages/photo-album/sql/postgresql/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.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	      text
);

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. In postgresql we use the text datatype
  and there is no limit on text length.  This differs from the varchar2 with
  Oracle.
';




 -- create the content type
 select content_type__create_type (
   'pa_album',		     -- content_type
   'content_revision',	     -- supertype	
   'Photo album',	     -- pretty_name 
   'Photo albums',	     -- pretty_plural
   'pa_albums',		     -- table_name 
   'pa_album_id',	     -- id_column 
   null			     -- name_method
 );

 -- create content type attributes
 select content_type__create_attribute (
   'pa_album',		     -- content_type
   'story',		     -- attribute_name
   'text',		     -- datatype
   'Story',		     -- pretty_name
   'Stories',		     -- pretty_plural
   null,		     -- sort_order
   null,		     -- default_value
   'text'		     -- column_spec
 );



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	      varchar(500),
    story	      text,
    user_filename     varchar(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.
';

 select content_type__create_type (
   'pa_photo',		     -- content_type
   'content_revision',	     -- supertype	
   'Album photo',	     -- pretty_name 
   'Album photos',	     -- pretty_plural
   'pa_photos',		     -- table_name 
   'pa_photo_id',	     -- id_column 
   null			     -- name_method
 );

 select content_type__create_attribute (
   'pa_photo',		     -- content_type
   'story',		     -- attribute_name
   'text',		     -- datatype
   'Story',		     -- pretty_name
   'Stories',		     -- pretty_plural
   null,		     -- sort_order
   null,		     -- default_value
   'text'		     -- column_spec
 );

 select content_type__create_attribute (
   'pa_photo',		     -- content_type
   'caption',		     -- attribute_name
   'text',		     -- datatype
   'Short caption for display under photo',		     -- pretty_name
   'Short captions for display under photos',		     -- pretty_plural
   null,		     -- sort_order
   null,		     -- default_value
   'varchar(500)'	     -- column_spec
 );

 select content_type__create_attribute (
   'pa_photo',		     -- content_type
   'user_filename',	     -- attribute_name
   'text',		     -- datatype
   'User filename',	     -- pretty_name
   'User filenames',	     -- pretty_plural
   null,		     -- sort_order
   null,		     -- default_value
   'varchar(250)'	     -- column_spec
 );

-- 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

  select content_type__register_child_type (
    'pa_album',		     -- parent_type 
    'pa_photo',		     -- child_type
    'generic',		     -- relation_tag
    0,			     -- min_n
    null		     -- max_n
  );

  select content_type__register_child_type (
    'pa_photo',		     -- parent_type
    'image',		     -- child_type
    'generic',		     -- relation_tag
    0,			     -- min_n
    null		     -- max_n
  );


create table pa_package_root_folder_map (
  package_id       integer
		   constraint pa_pack_fldr_map_pk
		   primary key
                   constraint pa_pack_fldr_map_pack_id_fk
		   references apm_packages,
  folder_id	   integer
		   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
-- drop function inline_0 ();

create function inline_0 () returns integer as '
declare
  v_count integer;
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;
  return 1;
end; ' language 'plpgsql';

select inline_0 ();

drop function inline_0 ();

\i permissions.sql
\i pl-pgsql.sql