-- 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.3 2003/09/30 12:10:00 mohanp 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 		blob,
    searchable_p	char(1) default 't'
	constraint acs_cont_searchable_p_ck check (searchable_p in ('t','f')),
    -- stores the language of the content
    nls_language	varchar2(50),
    -- mime type
    mime_type   	varchar2(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 't',
	content		in acs_contents.content%TYPE	    default empty_blob()    
    );

    procedure del (
	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;
/
show errors

create or replace package body 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 't',
	content		in acs_contents.content%TYPE	default empty_blob()    
    ) 
    is
	v_content_id	acs_contents.content_id%TYPE;
    begin
	insert into acs_contents (
	    content_id,
	    mime_type,
	    nls_language,
	    searchable_p,
	    content
	) values (
	    acs_content.new.content_id,
	    acs_content.new.mime_type,
	    acs_content.new.nls_language,
	    acs_content.new.searchable_p,
	    content
	);
    end new;	    
			
    procedure del (
	content_id 	in acs_contents.content_id%TYPE
    ) 
    is
    begin
	delete from acs_contents 
	where content_id = acs_content.del.content_id;
    end del;

    -- update language column
    procedure update_nls_language (
	content_id	in acs_contents.content_id%TYPE,
	nls_language	in acs_contents.nls_language%TYPE
    ) 
    is 
    begin
	update acs_contents
	set nls_language = acs_content.update_nls_language.nls_language
	where content_id = acs_content.update_nls_language.content_id;
    end update_nls_language;

    -- update mime type column
    procedure update_mime_type (
	content_id	in acs_contents.content_id%TYPE,
	mime_type	in acs_contents.mime_type%TYPE
    ) 
    is 
    begin
	update acs_contents
	set mime_type = acs_content.update_mime_type.mime_type
	where content_id = acs_content.update_mime_type.content_id;
    end update_mime_type;

    -- update searchable p column
    procedure update_searchable_p (
	content_id	in acs_contents.content_id%TYPE,
	searchable_p	in acs_contents.searchable_p%TYPE
    )
    is
    begin
	update acs_contents
	set searchable_p = acs_content.update_searchable_p.searchable_p
	where content_id = acs_content.update_searchable_p.content_id;
    end update_searchable_p;
end acs_content;
/	
show errors