-- Data Model to support content repository of the Arsdigita Community
-- System

-- Copyright (C) 1999-2000 ArsDigita Corporation
-- Author: Khy Huang (khy@arsdigita.com)

-- $Id: acs-content-create.sql,v 1.1 2001/04/03 05:18:30 danw 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


create table acs_contents (
    content_id 		integer
	constraint acs_cont_content_id_fk references acs_objects(object_id)
	constraint acs_cont_content_id_pk primary key,
    content 		integer,
    searchable_p	boolean default 't'
	constraint acs_cont_searchable_p_ck check (searchable_p in ('t','f')),
    -- stores the language of the content
    nls_language	varchar(50) default '' not null,
    -- mime type
    mime_type   	varchar(200) default 'text/plain'
);

comment on table acs_contents is 'Table for storing object content';
comment on column acs_contents.searchable_p is 'Include content for search?';

-- create or replace package acs_content 
-- as
--     procedure new (	
-- 	content_id	in acs_contents.content_id%TYPE	    ,
-- 	mime_type	in acs_contents.mime_type%TYPE	    default 'text/plain',
-- 	nls_language 	in acs_contents.nls_language%TYPE   default null,
-- 	searchable_p	in acs_contents.searchable_p%TYPE   default 'f',
-- 	content		in acs_contents.content%TYPE	    default empty_blob()    
--     );
-- 
--     procedure delete (
-- 	content_id 	in acs_contents.content_id%TYPE
--     );
-- 
--     procedure update_nls_language (
-- 	content_id	in acs_contents.content_id%TYPE default null,
-- 	nls_language	in acs_contents.nls_language%TYPE
--     );
-- 
--     procedure update_mime_type (
-- 	content_id	in acs_contents.content_id%TYPE default null,
-- 	mime_type	in acs_contents.mime_type%TYPE
--     );
-- 
--     procedure update_searchable_p (
-- 	content_id	in acs_contents.content_id%TYPE default null,
-- 	searchable_p	in acs_contents.searchable_p%TYPE
--     );
-- 
-- end acs_content;


-- create or replace package body acs_content 
-- procedure new
create function acs_content__new (integer,varchar,varchar,char,integer)
returns integer as '
declare
  new__content_id             alias for $1;  
  new__mime_type              alias for $2;  
  new__nls_language           alias for $3;  
  new__searchable_p           alias for $4;  
  new__content                alias for $5;  
begin
	insert into acs_contents (
	    content_id,
	    mime_type,
	    nls_language,
	    searchable_p,
	    content
	) values (
	    new__content_id,
	    new__mime_type,
	    new__nls_language,
	    new__searchable_p,
	    new__content
	);

        return 0; 
end;' language 'plpgsql';


-- procedure delete
create function acs_content__delete (integer)
returns integer as '
declare
  delete__content_id             alias for $1;  
begin
	delete from acs_contents 
	where content_id = delete__content_id;

        return 0; 
end;' language 'plpgsql';


-- procedure update_nls_language
create function acs_content__update_nls_language (integer,varchar)
returns integer as '
declare
  update_nls_language__content_id             alias for $1;  
  update_nls_language__nls_language           alias for $2;  
begin
	update acs_contents
	set nls_language = update_nls_language__nls_language
	where content_id = update_nls_language__content_id;

        return 0; 
end;' language 'plpgsql';


-- procedure update_mime_type
create function acs_content__update_mime_type (integer,varchar)
returns integer as '
declare
  update_mime_type__content_id             alias for $1;  
  update_mime_type__mime_type              alias for $2;  
begin
	update acs_contents
	set mime_type = update_mime_type__mime_type
	where content_id = update_mime_type__content_id;

        return 0; 
end;' language 'plpgsql';


-- procedure update_searchable_p
create function acs_content__update_searchable_p (integer,char)
returns integer as '
declare
  update_searchable_p__content_id             alias for $1;  
  update_searchable_p__searchable_p           alias for $2;  
begin
	update acs_contents
	set searchable_p = update_searchable_p__searchable_p
	where content_id = update_searchable_p__content_id;

        return 0; 
end;' language 'plpgsql';