Index: openacs-4/packages/acs-content-repository/sql/acs-content-repository-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/acs-content-repository-create.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/acs-content-repository-create.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,12 +0,0 @@ --- Registration of ACS Content Repository System. - --- Copyright (C) 1999-2000 ArsDigita Corporation --- Author: Karl Goldstein (karlg@arsdigita.com) - --- $Id: acs-content-repository-create.sql,v 1.1 2001/03/13 22:59:26 ben 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 - -@@ content-create Index: openacs-4/packages/acs-content-repository/sql/content-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-create.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-create.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,798 +0,0 @@ --- Data model to support content repository of the ArsDigita Community --- System - --- Copyright (C) 1999-2000 ArsDigita Corporation --- Author: Karl Goldstein (karlg@arsdigita.com) - --- $Id: content-create.sql,v 1.1 2001/03/13 22:59:26 ben 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 - ----------------------------------- --- CMS datatypes ----------------------------------- - --- create ats datatypes for cms -begin - insert into acs_datatypes - (datatype, max_n_values) - values - ('text', null); - - insert into acs_datatypes - (datatype, max_n_values) - values - ('keyword', 1); - -end; -/ - - --------------------------------------------------------------- --- MIME TYPES --------------------------------------------------------------- - -create table cr_mime_types ( - label varchar2(200), - mime_type varchar2(200) - constraint cr_mime_types_pk - primary key, - file_extension varchar2(200) -); - - -comment on table cr_mime_types is ' - Standard MIME types recognized by the content management system. -'; - -comment on table cr_mime_types is ' - file_extension is not used to recognize MIME types, but to associate - a file extension to the file after its MIME type is specified. -'; - --- Common mime types (administered from admin pages) - -insert into cr_mime_types values ('Plain text', 'text/plain', 'txt'); -insert into cr_mime_types values ('HTML text', 'text/html', 'html'); -insert into cr_mime_types values ('Rich Text Format (RTF)', 'text/richtext', 'rtf'); - -create table cr_content_mime_type_map ( - content_type varchar2(100) - constraint cr_content_mime_map_ctyp_fk - references acs_object_types, - mime_type varchar2(200) - constraint cr_content_mime_map_typ_fk - references cr_mime_types, - constraint cr_content_mime_map_pk - primary key (content_type, mime_type) -); - -comment on table cr_content_mime_type_map is ' - A mapping table that restricts the MIME types associated with a - content type. -'; - - - --------------------------------------------------------------- --- LOCALES --------------------------------------------------------------- - -create table cr_locales ( - locale varchar2(4) - constraint cr_locale_abbrev_pk - primary key, - label varchar2(200) - constraint cr_locale_name_nil - not null - constraint cr_locale_name_unq - unique, - nls_language varchar2(30) - constraint cr_locale_nls_lang_nil - not null, - nls_territory varchar2(30), - nls_charset varchar2(30) -); - -comment on table cr_locales is ' - Locale definitions in Oracle consist of a language, and optionally - territory and character set. (Languages are associated with default - territories and character sets when not defined). The formats - for numbers, currency, dates, etc. are determined by the territory. -'; - -insert into cr_locales ( - locale, label, nls_language, nls_territory, nls_charset -) values ( - 'us', 'American', 'AMERICAN', 'AMERICA', 'WE8ISO8859P1' -); - --------------------------------------------------------------- --- CONTENT TYPES --------------------------------------------------------------- - -create table cr_type_children ( - parent_type varchar2(100) - constraint cr_type_children_parent_fk - references acs_object_types, - child_type varchar2(100) - constraint cr_type_children_child_fk - references acs_object_types, - relation_tag varchar2(100), - min_n integer, - max_n integer, - constraint cr_type_children_pk - primary key (parent_type, child_type, relation_tag) -); - -comment on table cr_type_children is ' - Constrains the allowable content types which a content type may - contain. -'; - -create table cr_type_relations ( - content_type varchar2(100) - constraint cr_type_relations_parent_fk - references acs_object_types, - target_type varchar2(100) - constraint cr_type_relations_child_fk - references acs_object_types, - relation_tag varchar2(100), - min_n integer, - max_n integer, - constraint cr_type_relations_pk - primary key (content_type, target_type, relation_tag) -); - -comment on table cr_type_relations is ' - Constrains the allowable object types to which a content type may - relate (see above). -'; - --------------------------------------------------------------- --- CONTENT ITEMS --------------------------------------------------------------- - --- Define the cr_items table - -create table cr_items ( - item_id integer - constraint cr_items_item_id_fk references - acs_objects on delete cascade - constraint cr_items_pk primary key, - parent_id integer - constraint cr_items_parent_id_nil - not null - constraint cr_items_parent_id_fk references - acs_objects on delete cascade, - name varchar2(400) - constraint cr_items_name_nil - not null, - locale varchar2(4) - constraint cr_items_locale_fk references - cr_locales, - live_revision integer, - latest_revision integer, - publish_status varchar2(40) - constraint cr_items_pub_status_chk - check (publish_status in - ('production', 'ready', 'live', 'expired') - ), - content_type varchar2(100) - constraint cr_items_rev_type_fk - references acs_object_types -); - -create index cr_items_by_locale on cr_items(locale); -create index cr_items_by_content_type on cr_items(content_type); -create unique index cr_items_by_live_revision on cr_items(live_revision); -create unique index cr_items_by_latest_revision on cr_items(latest_revision); -create unique index cr_items_unique_name on cr_items(parent_id, name); -create unique index cr_items_unique_id on cr_items(parent_id, item_id); -create index cr_items_by_parent_id on cr_items(parent_id); - -comment on table cr_items is ' - Each content item has a row in this table, as well as a row in - the acs_objects table. The parent_id is used to place an - item in a directory or place it within another container item. -'; - -comment on column cr_items.content_type is ' - The content type constrains the type of revision that may be - added to this item (an item should have revisions of only one type). - If null, then no revisions should be allowed. -'; - -create table cr_child_rels ( - rel_id integer - constraint cr_child_rels_rel_pk - primary key - constraint cr_child_rels_rel_fk - references acs_objects, - parent_id integer - constraint cr_child_rels_parent_nil - not null, - child_id integer - constraint cr_child_rels_child_nil - not null, - relation_tag varchar2(100), - order_n integer -); - -create index cr_child_rels_by_parent on cr_child_rels(parent_id); -create unique index cr_child_rels_unq_id on cr_child_rels(parent_id, child_id); - -comment on table cr_child_rels is ' - Provides for richer parent-child relationships than the simple - link encapsulated in the primary table. May be subclassed to provide - additional attributes. -'; - -create table cr_item_rels ( - rel_id integer - constraint cr_item_rels_pk - primary key - constraint cr_item_rels_fk - references acs_objects, - item_id integer - constraint cr_item_rels_item_fk - references cr_items, - related_object_id integer - constraint cr_item_rels_rel_obj__fk - references acs_objects, - relation_tag varchar2(100), - order_n integer -); - -create unique index cr_item_rel_unq on cr_item_rels ( - item_id, related_object_id, relation_tag -); - -comment on table cr_item_rels is ' - Describes all relations from one item to any number of other - objects. -'; - -comment on column cr_item_rels.relation_tag is ' - A token for lightweight classification of item relationships. - If additional attributes are required, then a subtype of - item_rel may be created. -'; - -comment on column cr_item_rels.order_n is ' - Optional column for specifying a sort order. Note that the ordering - method is application-dependent (it may be by relation type or - across multiple relation types). -'; - --------------------------------------------------------------- --- CONTENT REVISIONS --------------------------------------------------------------- - --- Define the cr_revisions table - -create table cr_revisions ( - revision_id constraint cr_revisions_rev_id_fk references - acs_objects (object_id) on delete cascade - constraint cr_revisions_pk primary key, - item_id constraint cr_revisions_item_id_nil - not null - constraint cr_revisions_item_id_fk references - cr_items on delete cascade, - title varchar2(1000), - description varchar2(4000), - publish_date date, - mime_type varchar2(200) default 'text/plain' - constraint cr_revisions_mime_type_ref - references cr_mime_types, - nls_language varchar2(50), - content BLOB -); - - -create index cr_revisions_by_mime_type on cr_revisions(mime_type); -create index cr_revisions_title_idx on cr_revisions(title); --- create index cr_revisions_lower_title_idx on cr_revisions(lower(title)); --- create index cr_revisions_title_ltr_idx on cr_revisions(substr(lower(title), 1, 1)); - -comment on table cr_revisions is ' - Each content item may be associated with any number of revisions. - The item_id is used to associate revisions with an item. -'; - -comment on column cr_revisions.nls_language is ' - NLS_LANGUAGE is required in the same table as the content column - for multi-lingual searching in Intermedia. -'; - -alter table cr_items add constraint cr_items_live_fk - foreign key (live_revision) references cr_revisions(revision_id); - -alter table cr_items add constraint cr_items_latest_fk - foreign key (latest_revision) references cr_revisions(revision_id); - -create table cr_revision_attributes ( - revision_id integer - constraint cr_revision_attributes_pk - primary key - constraint cr_revision_attributes_fk - references cr_revisions, - attributes clob -); - -comment on column cr_revision_attributes.attributes is ' - An XML document representing the compiled attributes for a revision -'; - -create global temporary table cr_content_text ( - revision_id integer primary key, - content CLOB -) on commit delete rows; - -comment on table cr_content_text is ' - A temporary table for holding text extracted from the content blob. - Provides a workaround for the fact that blob_to_string(content) has - 4000 character limit. -'; - --------------------------------------------------------------- --- CONTENT PUBLISHING --------------------------------------------------------------- - -create table cr_item_publish_audit ( - item_id integer - constraint cr_item_publish_audit_fk - references cr_items on delete cascade, - old_revision integer - constraint cr_item_pub_audit_old_rev_fk - references cr_revisions, - new_revision integer - constraint cr_item_pub_audit_new_rev_fk - references cr_revisions, - old_status varchar2(40), - new_status varchar2(40), - publish_date date - constraint cr_item_publish_audit_date_nil - not null -); - -create index cr_item_publish_audit_idx on cr_item_publish_audit(item_id); - -comment on table cr_item_publish_audit is ' - An audit table (populated by a trigger on cr_items.live_revision) - that is used to keep track of the publication history of an item. -'; - -create table cr_release_periods ( - item_id integer - constraint cr_release_periods_fk - references cr_items - constraint cr_release_periods_pk - primary key, - start_when date default sysdate, - end_when date default sysdate + (365 * 20) -); - -create table cr_scheduled_release_log ( - exec_date date default sysdate not null, - items_released integer not null, - items_expired integer not null, - err_num integer, - err_msg varchar2(500) -); - -comment on table cr_scheduled_release_log is ' - Maintains a record, including any exceptions that may - have aborted processing, for each scheduled update of live content. -'; - -create table cr_scheduled_release_job ( - job_id integer, - last_exec date -); - -comment on table cr_scheduled_release_job is ' - One-row table to track job ID of scheduled release update. -'; - -insert into cr_scheduled_release_job values (NULL, sysdate); - --------------------------------------------------------------- --- CONTENT FOLDERS --------------------------------------------------------------- - -create table cr_folders ( - folder_id integer - constraint cr_folder_id_fk references - cr_items on delete cascade - constraint cr_folders_pk - primary key, - label varchar2(1000), - description varchar2(4000), - has_child_folders char(1) - default 'f' - constraint cr_folder_child_chk - check (has_child_folders in ('t','f')), - has_child_symlinks char(1) - default 'f' - constraint cr_folder_symlink_chk - check (has_child_symlinks in ('t', 'f')) -); - -comment on table cr_folders is ' - Folders are used to support a virtual file system within the content - repository. -'; - - -create table cr_folder_type_map ( - folder_id integer - constraint cr_folder_type_map_fldr_fk - references cr_folders, - content_type varchar2(100) - constraint cr_folder_type_map_typ_fk - references acs_object_types, - constraint cr_folder_type_map_pk - primary key (folder_id, content_type) -); - -comment on table cr_folder_type_map is ' - A one-to-many mapping table of content folders to content types. - Basically, this table restricts the content types a folder may contain. - Future releases will add numeric and tagged constraints similar to - thos available for content types. -'; - - - - --------------------------------------------------------------- --- CONTENT TEMPLATES --------------------------------------------------------------- - -create table cr_templates ( - template_id integer - constraint cr_template_id_fk references - cr_items on delete cascade - constraint cr_templates_pk - primary key -); - -comment on table cr_templates is ' - Templates are a special class of text objects that are used for specifying - the layout of a content item. They may be mapped to content types for - defaults, or may be mapped to individual content items. -'; - -create table cr_template_use_contexts ( - use_context varchar2(100) - constraint cr_template_use_contexts_pk - primary key -); - -comment on table cr_template_use_contexts is ' - A simple table (for now) for constraining template use contexts. -'; - -insert into cr_template_use_contexts values ('admin'); -insert into cr_template_use_contexts values ('public'); - -create table cr_type_template_map ( - content_type varchar2(100) - constraint cr_type_template_map_typ_fk - references acs_object_types - constraint cr_type_template_map_typ_nil - not null, - template_id integer - constraint cr_type_template_map_tmpl_fk - references cr_templates, - use_context varchar2(100) - constraint cr_type_template_map_ctx_nil - not null - constraint cr_type_template_map_ctx_fk - references cr_template_use_contexts, - is_default char(1) - default 'f' - constraint cr_type_template_map_def_ck - check (is_default in ('t','f')), - constraint cr_type_template_map_pk - primary key (content_type, template_id, use_context) -); - -create index cr_ttmap_by_content_type on cr_type_template_map(content_type); -create index cr_ttmap_by_template_id on cr_type_template_map(template_id); -create index cr_ttmap_by_use_context on cr_type_template_map(use_context); - -comment on table cr_type_template_map is ' - A simple mapping template among content types and templates. - Used to determine the default template to use in any particular - context, as well as for building any UI that allows publishers - to choose from a palette of templates. -'; - -comment on column cr_type_template_map.use_context is ' - A token to indicate the context in which a template is appropriate, - such as admin or public. Should be constrained when it becomes - clearer how this will be used. -'; - -create table cr_item_template_map ( - item_id integer - constraint cr_item_template_map_item_fk - references cr_items - constraint cr_item_template_map_item_nil - not null, - template_id integer - constraint cr_item_template_map_tmpl_fk - references cr_templates - constraint cr_item_template_map_tmpl_nil - not null, - use_context varchar2(100) - constraint cr_item_template_map_ctx_nil - not null - constraint cr_item_template_map_ctx_fk - references cr_template_use_contexts, - constraint cr_item_template_map_pk - primary key (item_id, template_id, use_context) -); - -create index cr_itmap_by_item_id on cr_item_template_map(item_id); -create index cr_itmap_by_template_id on cr_item_template_map(template_id); -create index cr_itmap_by_use_context on cr_item_template_map(use_context); - -comment on table cr_item_template_map is ' - Allows a template to be assigned to a specific item. -'; - --------------------------------------------------------------- --- CONTENT SYMLINKS --------------------------------------------------------------- - -create table cr_symlinks ( - symlink_id integer - constraint cr_symlink_id_fk references - cr_items on delete cascade - constraint cr_symlinks_pk - primary key, - target_id integer - constraint cr_symlink_target_id_fk - references cr_items - constraint cr_symlink_target_id_nil - not null, - label varchar2(1000) -); - -create index cr_symlinks_by_target_id on cr_symlinks(target_id); - -comment on table cr_symlinks is ' - Symlinks are pointers to items within the content repository. -'; - --------------------------------------------------------------- --- CONTENT EXTLINKS --------------------------------------------------------------- - -create table cr_extlinks ( - extlink_id integer - constraint cr_extlink_id_fk references - cr_items on delete cascade - constraint cr_extlinks_pk - primary key, - url varchar2(1000) - constraint cr_extlink_url_nil - not null, - label varchar2(1000) - constraint cr_extlink_label_nil - not null, - description varchar2(4000) -); - -comment on table cr_extlinks is ' - Extlinks are pointers to items anywhere on the web which the publisher wishes - to categorize, index and relate to items in the content repository. -'; - --------------------------------------------------------------- --- CONTENT KEYWORDS --------------------------------------------------------------- - -create table cr_keywords ( - keyword_id integer - constraint cr_keywords_pk - primary key, - parent_id integer - constraint cr_keywords_hier - references cr_keywords, - heading varchar2(600) - constraint cr_keywords_name_nil - not null, - description varchar2(4000), - has_children char(1) - constraint cr_keywords_child_chk - check (has_children in ('t', 'f')) -); - -comment on table cr_keywords is ' - Stores a subject taxonomy for classifying content items, analogous - to the system used by a library. -'; - -comment on column cr_keywords.heading is ' - A subject heading. This will become a message ID in the next - release so it should never be referenced directly (only through - the API) -'; - -comment on column cr_keywords.description is ' - Description of a subject heading. This will be a message ID in the next - release so it should never be referenced directly (only through - the API) -'; - -create table cr_item_keyword_map ( - item_id integer - constraint cr_item_keyword_map_item_fk - references cr_items - constraint cr_item_keyword_map_item_nil - not null, - keyword_id integer - constraint cr_item_keyword_map_kw_fk - references cr_keywords - constraint cr_item_keyword_map_kw_nil - not null, - constraint cr_item_keyword_map_pk - primary key (item_id, keyword_id) -); - - --------------------------------------------------------------- --- TEXT SUBMISSION --------------------------------------------------------------- - -create table cr_text ( - text varchar2(4000) -); - -comment on table cr_text is ' - A simple placeholder table for generating input views, so that a - complete revision may be added with a single INSERT statement. -'; - -insert into cr_text values (NULL); - -create or replace trigger cr_text_tr -before insert on cr_text -for each row -begin - - raise_application_error(-20000, - 'Inserts are not allowed into cr_text.' - ); -end; -/ -show errors - - - --------------------------------------------------------------- --- DOCUMENT SUBMISSION WITH CONVERSION TO HTML --------------------------------------------------------------- - -create global temporary table cr_doc_filter ( - revision_id integer primary key, - content BLOB -) on commit delete rows; - - --- Source PL/SQL Definitions. - -@@ content-util -@@ content-xml - -prompt *** Creating packaged call specs for Java utility methods... -@@ content-package - -prompt *** Defining and compiling packages... -@@ packages-create - -prompt *** Creating object types... -@@ types-create - -prompt *** Preparing search indices... -@@ content-search - --- this index requires prefs created in content-search -create index cr_doc_filter_index on cr_doc_filter ( content ) - indextype is ctxsys.context - parameters ('FILTER content_filter_pref' ); - -comment on table cr_doc_filter is ' - A temporary table for holding binary documents that are to be converted - into HTML (or plain text) prior to insertion into the repository. -'; - - - -prompt *** Compiling documentation package... -@@ doc-package - -prompt *** Creating image content type... -@@ content-image - --- by default, map all MIME types to 'content_revision' - -begin - content_type.register_mime_type( - content_type => 'content_revision', mime_type => 'text/html'); - content_type.register_mime_type( - content_type => 'content_revision', mime_type => 'text/plain'); -end; -/ -show errors - -prompt *** Initializing content repository hierarchy... - --- Create the default folders -declare - v_id integer; -begin - - v_id := content_folder.new ( - name => 'pages', - label => 'Pages', - description => 'Site pages go here', - parent_id => 0, - folder_id => content_item.get_root_folder - ); - - content_folder.register_content_type( - folder_id => v_id, - content_type => 'content_revision', - include_subtypes => 't' - ); - - content_folder.register_content_type( - folder_id => v_id, - content_type => 'content_folder', - include_subtypes => 't' - ); - - content_folder.register_content_type( - folder_id => v_id, - content_type => 'content_symlink', - include_subtypes => 't' - ); - - v_id := content_folder.new ( - name => 'templates', - label => 'Templates', - description => 'Templates which render the pages go here', - parent_id => 0, - folder_id => content_template.get_root_folder - ); - - content_folder.register_content_type( - folder_id => v_id, - content_type => 'content_folder', - include_subtypes => 't' - ); - - content_folder.register_content_type( - folder_id => v_id, - content_type => 'content_symlink', - include_subtypes => 't' - ); - - content_folder.register_content_type( - folder_id => v_id, - content_type => 'content_template', - include_subtypes => 't' - ); - -end; -/ -show errors - - Index: openacs-4/packages/acs-content-repository/sql/content-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-drop.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-drop.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,213 +0,0 @@ --- Uninstall content repository tables of the ArsDigita Community --- System - --- Copyright (C) 1999-2000 ArsDigita Corporation --- Author: Karl Goldstein (karlg@arsdigita.com) - --- $Id: content-drop.sql,v 1.1 2001/03/13 22:59:26 ben 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 - -set serveroutput on - --- unregistering types, deleting the default folders -declare - v_id integer; -begin - - -- root folder for templates - v_id := content_template.get_root_folder; - - content_folder.unregister_content_type( - folder_id => v_id, - content_type => 'content_template', - include_subtypes => 't' - ); - - content_folder.unregister_content_type( - folder_id => v_id, - content_type => 'content_symlink', - include_subtypes => 't' - ); - - content_folder.unregister_content_type( - folder_id => v_id, - content_type => 'content_folder', - include_subtypes => 't' - ); - - content_folder.delete(v_id); - - - -- the root folder for content items - v_id := content_item.get_root_folder; - - content_folder.unregister_content_type( - folder_id => v_id, - content_type => 'content_symlink', - include_subtypes => 't' - ); - - content_folder.unregister_content_type( - folder_id => v_id, - content_type => 'content_folder', - include_subtypes => 't' - ); - - content_folder.unregister_content_type ( - folder_id => v_id, - content_type => 'content_revision', - include_subtypes => 't' - ); - - content_folder.delete (v_id); - -end; -/ -show errors - -begin - content_type.unregister_mime_type( - content_type => 'content_revision', - mime_type => 'text/html'); - content_type.unregister_mime_type( - content_type => 'content_revision', - mime_type => 'text/plain'); -end; -/ -show errors - - --- drop all extended attribute tables - ---declare --- cursor type_cur is --- select object_type, table_name --- from acs_object_types --- where table_name <> 'cr_revisions' --- connect by prior object_type = supertype --- start with object_type = 'content_revision' --- order by level desc; ---begin - --- for type_rec in type_cur loop --- dbms_output.put_line('Dropping ' || type_rec.table_name); --- execute immediate 'drop table ' || type_rec.table_name; --- end loop; - ---end; ---/ ---show errors - - --- dropping pl/sql definitions -prompt ** dropping content-image -@@ content-image-drop - --- doc-package-drop - --- content-search-drop -begin - ctx_ddl.drop_section_group('auto'); -end; -/ -show errors - -begin - ctx_ddl.drop_preference('CONTENT_FILTER_PREF'); -end; -/ -show errors - -prompt ** dropping object types -@@ types-drop - --- packages-drop - - --- content-package-drop - -prompt ** dropping lots of tables --- content-xml-drop -drop table cr_xml_docs; -drop sequence cr_xml_doc_seq; - --- content-util drop - --- document submission with conversion to html -drop index cr_doc_filter_index; -drop table cr_doc_filter; - - ---text submission -drop table cr_text; - --- content keywords -drop table cr_item_keyword_map ; -drop table cr_keywords ; - --- content extlinks -drop table cr_extlinks ; - --- content symlinks -drop table cr_symlinks ; - --- content templates -drop table cr_item_template_map ; -drop table cr_type_template_map ; -drop table cr_template_use_contexts ; -drop table cr_templates ; - --- content folders -drop table cr_folder_type_map ; -drop table cr_folders cascade constraints; - - -prompt ** dropping more tables --- content publishing -drop table cr_scheduled_release_job; -drop table cr_scheduled_release_log; -drop table cr_release_periods; -drop table cr_item_publish_audit; - --- content revisions -drop table cr_content_text; -drop table cr_revision_attributes; -drop table cr_revisions cascade constraints; - --- content_items -drop table cr_item_rels ; -drop table cr_child_rels ; -drop table cr_items cascade constraints; - --- content types -drop table cr_type_relations ; -drop table cr_type_children ; - --- locales -drop table cr_locales ; - --- mime types -drop table cr_content_mime_type_map ; -drop table cr_mime_types ; - - --- dropping ats datatypes for cms -begin - - delete from acs_datatypes where datatype in ('text'); - - delete from acs_datatypes where datatype in ('keyword'); - - delete from acs_datatypes where datatype in ('integer'); - -commit; -end; -/ -show errors - - - - Index: openacs-4/packages/acs-content-repository/sql/content-extlink.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-extlink.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-extlink.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,107 +0,0 @@ --- Data model to support content repository of the ArsDigita --- Community System - --- Copyright (C) 1999-2000 ArsDigita Corporation --- Author: Karl Goldstein (karlg@arsdigita.com) - --- $Id: content-extlink.sql,v 1.1 2001/03/13 22:59:26 ben 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 or replace package body content_extlink -as - -function new ( - name in cr_items.name%TYPE default null, - url in cr_extlinks.url%TYPE, - label in cr_extlinks.label%TYPE default null, - description in cr_extlinks.description%TYPE default null, - parent_id in cr_items.parent_id%TYPE, - extlink_id in cr_extlinks.extlink_id%TYPE default null, - creation_date in acs_objects.creation_date%TYPE - default sysdate, - creation_user in acs_objects.creation_user%TYPE - default null, - creation_ip in acs_objects.creation_ip%TYPE default null -) return cr_extlinks.extlink_id%TYPE is - - v_extlink_id cr_extlinks.extlink_id%TYPE; - v_label cr_extlinks.label%TYPE; - v_name cr_items.name%TYPE; - -begin - - if label is null then - v_label := url; - else - v_label := label; - end if; - - if name is null then - select acs_object_id_seq.nextval into v_extlink_id from dual; - v_name := 'link' || v_extlink_id; - else - v_name := name; - end if; - - v_extlink_id := content_item.new( - item_id => content_extlink.new.extlink_id, - name => v_name, - content_type => 'content_extlink', - creation_date => content_extlink.new.creation_date, - creation_user => content_extlink.new.creation_user, - creation_ip => content_extlink.new.creation_ip, - parent_id => content_extlink.new.parent_id - ); - - insert into cr_extlinks - (extlink_id, url, label, description) - values - (v_extlink_id, content_extlink.new.url, v_label, - content_extlink.new.description); - - return v_extlink_id; - -end new; - -procedure delete ( - extlink_id in cr_extlinks.extlink_id%TYPE -) is -begin - - delete from cr_extlinks - where extlink_id = content_extlink.delete.extlink_id; - - content_item.delete(content_extlink.delete.extlink_id); - -end delete; - -function is_extlink ( - item_id in cr_items.item_id%TYPE -) return char -is - v_extlink_p integer := 0; -begin - - select - count(1) into v_extlink_p - from - cr_extlinks - where - extlink_id = is_extlink.item_id; - - if v_extlink_p = 1 then - return 't'; - else - return 'f'; - end if; - -end is_extlink; - -end content_extlink; -/ -show errors - - Index: openacs-4/packages/acs-content-repository/sql/content-folder.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-folder.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-folder.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,629 +0,0 @@ --- Data model to support content repository of the ArsDigita Community --- System - --- Copyright (C) 1999-2000 ArsDigita Corporation --- Author: Karl Goldstein (karlg@arsdigita.com) - --- $Id: content-folder.sql,v 1.1 2001/03/13 22:59:26 ben 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 or replace package body content_folder -as - -function new ( - name in cr_items.name%TYPE, - label in cr_folders.label%TYPE, - description in cr_folders.description%TYPE default null, - parent_id in cr_items.parent_id%TYPE default null, - context_id in acs_objects.context_id%TYPE default null, - folder_id in cr_folders.folder_id%TYPE default null, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null -) return cr_folders.folder_id%TYPE is - v_folder_id cr_folders.folder_id%TYPE; - v_context_id acs_objects.context_id%TYPE; -begin - - -- set the context_id - if content_folder.new.context_id is null then - v_context_id := content_folder.new.parent_id; - else - v_context_id := content_folder.new.context_id; - end if; - - -- parent_id = 0 means that this is a mount point - if parent_id ^= 0 and - content_folder.is_registered(parent_id,'content_folder') = 'f' then - - raise_application_error(-20000, - 'This folder does not allow subfolders to be created'); - else - - v_folder_id := content_item.new( - item_id => folder_id, - name => name, - item_subtype => 'content_folder', - content_type => 'content_folder', - context_id => v_context_id, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip, - parent_id => parent_id - ); - - insert into cr_folders ( - folder_id, label, description - ) values ( - v_folder_id, label, description - ); - - -- inherit the attributes of the parent folder - if content_folder.new.parent_id is not null then - - insert into cr_folder_type_map ( - folder_id, content_type - ) select - v_folder_id, content_type - from - cr_folder_type_map - where - folder_id = content_folder.new.parent_id; - end if; - - -- update the child flag on the parent - update cr_folders set has_child_folders = 't' - where folder_id = content_folder.new.parent_id; - - return v_folder_id; - end if; - -end new; - - -procedure delete ( - folder_id in cr_folders.folder_id%TYPE -) is - - v_count integer; - v_parent_id integer; - -begin - - -- check if the folder contains any items - - select count(*) into v_count from cr_items where parent_id = folder_id; - - if v_count > 0 then - raise_application_error(-20000, - 'Folder ID ' || folder_id || ' (' || content_item.get_path(folder_id) || - ') cannot be deleted because it is not empty.'); - end if; - - content_folder.unregister_content_type( - folder_id => content_folder.delete.folder_id, - content_type => 'content_revision', - include_subtypes => 't' ); - - delete from cr_folder_type_map - where folder_id = content_folder.delete.folder_id; - - select parent_id into v_parent_id from cr_items - where item_id = content_folder.delete.folder_id; - - content_item.delete(folder_id); - - -- check if any folders are left in the parent - update cr_folders set has_child_folders = 'f' - where folder_id = v_parent_id and not exists ( - select 1 from cr_items - where parent_id = v_parent_id and content_type = 'content_folder'); - -end delete; - --- renames a folder, making sure the new name is not already in use -procedure rename ( - folder_id in cr_folders.folder_id%TYPE, - name in cr_items.name%TYPE default null, - label in cr_folders.label%TYPE default null, - description in cr_folders.description%TYPE default null -) is - v_name_already_exists_p integer := 0; -begin - - if name is not null then - content_item.rename(folder_id, name); - end if; - - if label is not null and description is not null then - - update cr_folders - set label = rename.label, - description = rename.description - where folder_id = rename.folder_id; - - elsif label is not null and description is null then - - update cr_folders - set label = rename.label - where folder_id = rename.folder_id; - - end if; - -end rename; - - --- 1) make sure we are not moving the folder to an invalid location: --- a. destination folder exists --- b. folder is not the webroot (folder_id = -1) --- c. destination folder is not the same as the folder --- d. destination folder is not a subfolder --- 2) make sure subfolders are allowed in the target_folder --- 3) update the parent_id for the folder - -procedure move ( - folder_id in cr_folders.folder_id%TYPE, - target_folder_id in cr_folders.folder_id%TYPE -) is - v_source_folder_id integer; - v_valid_folders_p integer := 0; -begin - - select - count(*) - into - v_valid_folders_p - from - cr_folders - where - folder_id = move.target_folder_id - or - folder_id = move.folder_id; - - if v_valid_folders_p ^= 2 then - raise_application_error(-20000, - 'content_folder.move - Not valid folder(s)'); - end if; - - if folder_id = content_item.get_root_folder or - folder_id = content_template.get_root_folder then - raise_application_error( -20000, - 'content_folder.move - Cannot move root folder'); - end if; - - if target_folder_id = folder_id then - raise_application_error(-20000, - 'content_folder.move - Cannot move a folder to itself'); - end if; - - if is_sub_folder(folder_id, target_folder_id) = 't' then - raise_application_error(-20000, - 'content_folder.move - Destination folder is subfolder'); - end if; - - if is_registered(target_folder_id,'content_folder') ^= 't' then - raise_application_error(-20000, - 'content_folder.move - Destination folder does not allow subfolders'); - end if; - - select parent_id into v_source_folder_id from cr_items - where item_id = move.folder_id; - - -- update the parent_id for the folder - update cr_items - set parent_id = move.target_folder_id - where item_id = move.folder_id; - - -- update the has_child_folders flags - - -- update the source - update cr_folders set has_child_folders = 'f' - where folder_id = v_source_folder_id and not exists ( - select 1 from cr_items - where parent_id = v_source_folder_id - and content_type = 'content_folder'); - - -- update the destination - update cr_folders set has_child_folders = 't' - where folder_id = target_folder_id; - -end move; - --- * make sure that subfolders are allowed in this folder --- * creates new folder in the target folder with the same attributes --- as the old one --- * copies all contents of folder to the new one -procedure copy ( - folder_id in cr_folders.folder_id%TYPE, - target_folder_id in cr_folders.folder_id%TYPE, - creation_user in acs_objects.creation_user%TYPE, - creation_ip in acs_objects.creation_ip%TYPE default null -) is - v_valid_folders_p integer := 0; - v_current_folder_id cr_folders.folder_id%TYPE; - v_name cr_items.name%TYPE; - v_label cr_folders.label%TYPE; - v_description cr_folders.description%TYPE; - v_new_folder_id cr_folders.folder_id%TYPE; - - -- cursor: items in the folder - cursor c_folder_contents_cur is - select - item_id - from - cr_items - where - parent_id = copy.folder_id; - -begin - - select - count(*) - into - v_valid_folders_p - from - cr_folders - where - folder_id = copy.target_folder_id - or - folder_id = copy.folder_id; - - select - parent_id - into - v_current_folder_id - from - cr_items - where - item_id = copy.folder_id; - - if folder_id = content_item.get_root_folder or folder_id = content_template.get_root_folder or target_folder_id = folder_id or v_current_folder_id = target_folder_id then - v_valid_folders_p := 0; - end if; - - if v_valid_folders_p = 2 then - if is_sub_folder(folder_id, target_folder_id) ^= 't' then - - -- get the source folder info - select - name, label, description - into - v_name, v_label, v_description - from - cr_items i, cr_folders f - where - f.folder_id = i.item_id - and - f.folder_id = copy.folder_id; - - -- create the new folder - v_new_folder_id := content_folder.new( - parent_id => copy.target_folder_id, - name => v_name, - label => v_label, - description => v_description, - creation_user => copy.creation_user, - creation_ip => copy.creation_ip - ); - - -- copy attributes of original folder - insert into cr_folder_type_map ( - folder_id, content_type - ) select - v_new_folder_id, content_type - from - cr_folder_type_map map - where - folder_id = copy.folder_id - and - -- do not register content_type if it is already registered - not exists ( select 1 from cr_folder_type_map - where folder_id = v_new_folder_id - and content_type = map.content_type ) ; - - -- for each item in the folder, copy it - for v_folder_contents_val in c_folder_contents_cur loop - - content_item.copy( - item_id => v_folder_contents_val.item_id, - target_folder_id => v_new_folder_id, - creation_user => copy.creation_user, - creation_ip => copy.creation_ip - ); - - end loop; - - end if; - end if; -end copy; - - - - - --- returns 1 if the item_id passed in is a folder -function is_folder ( - item_id in cr_items.item_id%TYPE -) return char is - - v_folder_p varchar2(1) := 'f'; - -begin - - select 't' into v_folder_p from cr_folders - where folder_id = item_id; - - return v_folder_p; - -exception - when NO_DATA_FOUND then - return 'f'; - -end is_folder; - --- target_folder_id is the possible sub folder -function is_sub_folder ( - folder_id in cr_folders.folder_id%TYPE, - target_folder_id in cr_folders.folder_id%TYPE -) return char -is - cursor c_tree_cur is - select - parent_id - from - cr_items - connect by - prior parent_id = item_id - start with - item_id = target_folder_id; - - v_parent_id integer := 0; - v_sub_folder_p char := 'f'; - -begin - - if folder_id = content_item.get_root_folder or - folder_id = content_template.get_root_folder then - v_sub_folder_p := 't'; - end if; - - -- Get the parents - open c_tree_cur; - while v_parent_id <> folder_id loop - fetch c_tree_cur into v_parent_id; - exit when c_tree_cur%NOTFOUND; - end loop; - close c_tree_cur; - - if v_parent_id ^= 0 then - v_sub_folder_p := 't'; - end if; - - return v_sub_folder_p; - -end is_sub_folder; - -function is_empty ( - folder_id in cr_folders.folder_id%TYPE -) return varchar2 -is - v_return varchar2(1); -begin - - select - decode( count(*), 0, 't', 'f' ) into v_return - from - cr_items - where - parent_id = is_empty.folder_id; - - return v_return; -end is_empty; - - -procedure register_content_type ( - folder_id in cr_folders.folder_id%TYPE, - content_type in cr_folder_type_map.content_type%TYPE, - include_subtypes in varchar2 default 'f' -) is - - v_is_registered varchar2(100); - -begin - - if register_content_type.include_subtypes = 'f' then - - v_is_registered := is_registered( - folder_id => register_content_type.folder_id, - content_type => register_content_type.content_type, - include_subtypes => 'f' ); - - if v_is_registered = 'f' then - - insert into cr_folder_type_map ( - folder_id, content_type - ) values ( - register_content_type.folder_id, - register_content_type.content_type - ); - - end if; - - else - - insert into cr_folder_type_map ( - folder_id, content_type - ) select - register_content_type.folder_id, object_type - from - acs_object_types - where - object_type ^= 'acs_object' - and - not exists (select 1 from cr_folder_type_map - where folder_id = register_content_type.folder_id - and content_type = acs_object_types.object_type) - connect by - prior object_type = supertype - start with - object_type = register_content_type.content_type; - - end if; - -end register_content_type; - -procedure unregister_content_type ( - folder_id in cr_folders.folder_id%TYPE, - content_type in cr_folder_type_map.content_type%TYPE, - include_subtypes in varchar2 default 'f' -) is -begin - - if unregister_content_type.include_subtypes = 'f' then - delete from cr_folder_type_map - where folder_id = unregister_content_type.folder_id - and content_type = unregister_content_type.content_type; - else - delete from cr_folder_type_map - where folder_id = unregister_content_type.folder_id - and content_type in (select object_type - from acs_object_types - where object_type ^= 'acs_object' - connect by prior object_type = supertype - start with - object_type = unregister_content_type.content_type); - - end if; - -end unregister_content_type; - - - - -function is_registered ( - folder_id in cr_folders.folder_id%TYPE, - content_type in cr_folder_type_map.content_type%TYPE, - include_subtypes in varchar2 default 'f' -) return varchar2 -is - v_is_registered integer; - cursor c_subtype_cur is - select - object_type - from - acs_object_types - where - object_type ^= 'acs_object' - connect by - prior object_type = supertype - start with - object_type = is_registered.content_type; - -begin - - if is_registered.include_subtypes = 'f' then - select - count(1) - into - v_is_registered - from - cr_folder_type_map - where - folder_id = is_registered.folder_id - and - content_type = is_registered.content_type; - - else - - v_is_registered := 1; - for v_subtype_val in c_subtype_cur loop - if is_registered(is_registered.folder_id, - v_subtype_val.object_type, 'f') = 'f' then - v_is_registered := 0; - end if; - end loop; - end if; - - if v_is_registered = 0 then - return 'f'; - else - return 't'; - end if; - -end is_registered; - -function get_label ( - folder_id in cr_folders.folder_id%TYPE -) return cr_folders.label%TYPE -is - v_label cr_folders.label%TYPE; -begin - - select - label into v_label - from - cr_folders - where - folder_id = get_label.folder_id; - - return v_label; -end get_label; - - -function get_index_page ( - folder_id in cr_folders.folder_id%TYPE -) return cr_items.item_id%TYPE -is - v_folder_id cr_folders.folder_id%TYPE; - v_index_page_id cr_items.item_id%TYPE; -begin - - -- if the folder is a symlink, resolve it - if content_symlink.is_symlink( get_index_page.folder_id ) = 't' then - v_folder_id := content_symlink.resolve( get_index_page.folder_id ); - else - v_folder_id := get_index_page.folder_id; - end if; - - select - item_id into v_index_page_id - from - cr_items - where - parent_id = v_folder_id - and - name = 'index' - and - content_item.is_subclass( - content_item.get_content_type( content_symlink.resolve(item_id) ), - 'content_folder') = 'f' - and - content_item.is_subclass( - content_item.get_content_type( content_symlink.resolve(item_id) ), - 'content_template') = 'f'; - - return v_index_page_id; - -exception when no_data_found then - return null; -end get_index_page; - -function is_root ( - folder_id in cr_folders.folder_id%TYPE -) return char is - v_is_root char(1); -begin - - select decode(parent_id, 0, 't', 'f') into v_is_root - from cr_items where item_id = is_root.folder_id; - - return v_is_root; -end is_root; - -end content_folder; -/ -show errors - Index: openacs-4/packages/acs-content-repository/sql/content-image-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-image-drop.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-image-drop.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,48 +0,0 @@ --- drop the content-image type from the data model - --- Copyright (C) 20000 ArsDigita Corporation - --- $Id: content-image-drop.sql,v 1.1 2001/03/13 22:59:26 ben 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 - --- unregister mime types from the image type -begin - - content_type.unregister_mime_type( - content_type => 'image', - mime_type => 'image/jpeg' - ); - - content_type.unregister_mime_type( - content_type => 'image', - mime_type => 'image/gif' - ); - -end; -/ -show errors - --- remove image mime types - -delete from cr_mime_types where mime_type like 'image%'; - - - --- this should remove the attributes and table related to the --- the image type - -begin - - content_type.drop_type ( - content_type => 'image', - drop_table_p => 't'); - -end; -/ -show errors - - - Index: openacs-4/packages/acs-content-repository/sql/content-image.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-image.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-image.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,90 +0,0 @@ --- Data model to support content repository of the ArsDigita --- Publishing System - --- Copyright (C) 1999-2000 ArsDigita Corporation --- Author: Hiro Iwashima (iwashima@mit.edu) - --- $ID$ - --- 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 - --- This is to handle images - -create table images ( - image_id integer - constraint images_image_id_fk - references cr_revisions - constraint images_pk - primary key, - width integer, - height integer -); - - -declare - attr_id integer; -begin - - content_type.create_type ( - content_type => 'image', - supertype => 'content_revision', - pretty_name => 'Image', - pretty_plural => 'Images', - table_name => 'images', - id_column => 'image_id' - ); - - attr_id := content_type.create_attribute ( - content_type => 'image', - attribute_name => 'width', - datatype => 'integer', - pretty_name => 'Width', - pretty_plural => 'Widths' - ); - - attr_id := content_type.create_attribute ( - content_type => 'image', - attribute_name => 'height', - datatype => 'integer', - pretty_name => 'Height', - pretty_plural => 'Heights' - ); - -end; -/ -show errors - - --- insert new MIME types -insert into cr_mime_types ( - label, mime_type, file_extension -) values ( - 'Image - Jpeg', 'image/jpeg','jpg' -); - -insert into cr_mime_types ( - label, mime_type, file_extension -) values ( - 'Image - Gif', 'image/gif','gif' -); - - - --- register MIME types to this content type -begin - - content_type.register_mime_type( - content_type => 'image', - mime_type => 'image/jpeg' - ); - - content_type.register_mime_type( - content_type => 'image', - mime_type => 'image/gif' - ); - -end; -/ -show errors Index: openacs-4/packages/acs-content-repository/sql/content-item.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-item.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-item.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,1682 +0,0 @@ --- Data model to support content repository of the ArsDigita --- Community System - --- Copyright (C) 1999-2000 ArsDigita Corporation --- Author: Karl Goldstein (karlg@arsdigita.com) - --- $Id: content-item.sql,v 1.1 2001/03/13 22:59:26 ben 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 or replace package body content_item -as - -function get_root_folder ( - item_id in cr_items.item_id%TYPE default null -) return cr_folders.folder_id%TYPE is - - v_folder_id cr_folders.folder_id%TYPE; - -begin - - if item_id is NULL then - - v_folder_id := c_root_folder_id; - - else - - select - item_id into v_folder_id - from - cr_items - where - parent_id = 0 - connect by - prior parent_id = item_id - start with - item_id = get_root_folder.item_id; - - end if; - - return v_folder_id; - -exception - when NO_DATA_FOUND then - raise_application_error(-20000, - 'Could not find a root folder for item ID ' || item_id || '. ' || - 'Either the item does not exist or its parent value is corrupted.'); -end get_root_folder; - -function new ( - name in cr_items.name%TYPE, - parent_id in cr_items.parent_id%TYPE default null, - item_id in acs_objects.object_id%TYPE default null, - locale in cr_items.locale%TYPE default null, - creation_date in acs_objects.creation_date%TYPE - default sysdate, - creation_user in acs_objects.creation_user%TYPE - default null, - context_id in acs_objects.context_id%TYPE - default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - item_subtype in acs_object_types.object_type%TYPE - default 'content_item', - content_type in acs_object_types.object_type%TYPE - default 'content_revision', - title in cr_revisions.title%TYPE default null, - description in cr_revisions.description%TYPE default null, - mime_type in cr_revisions.mime_type%TYPE default 'text/plain', - nls_language in cr_revisions.nls_language%TYPE default null, - text in varchar2 default null, - data in cr_revisions.content%TYPE default null, - relation_tag in cr_child_rels.relation_tag%TYPE default null, - is_live in char default 'f' -) return cr_items.item_id%TYPE -is - v_parent_id cr_items.parent_id%TYPE; - v_parent_type acs_objects.object_type%TYPE; - v_item_id cr_items.item_id%TYPE; - v_revision_id cr_revisions.revision_id%TYPE; - v_title cr_revisions.title%TYPE; - v_rel_id acs_objects.object_id%TYPE; - v_rel_tag cr_child_rels.relation_tag%TYPE; - v_context_id acs_objects.context_id%TYPE; -begin - - -- if content_item.is_subclass(item_subtype,'content_item') = 'f' then - -- raise_application_error(-20000, 'The object_type ' || item_subtype || - -- ' does not inherit from content_item.'); - -- end if; - - -- place the item in the context of the pages folder if no - -- context specified - - if parent_id is null then - v_parent_id := c_root_folder_id; - else - v_parent_id := parent_id; - end if; - - -- Determine context_id - if context_id is null then - v_context_id := v_parent_id; - else - v_context_id := context_id; - end if; - - if v_parent_id = 0 or - content_folder.is_folder(v_parent_id) = 't' then - - if v_parent_id ^= 0 and - content_folder.is_registered( - v_parent_id, content_item.new.content_type, 'f') = 'f' then - - raise_application_error(-20000, - 'This item''s content type ' || content_item.new.content_type || - ' is not registered to this folder ' || v_parent_id); - - end if; - - elsif v_parent_id ^= 0 then - - begin - - select object_type into v_parent_type from acs_objects - where object_id = v_parent_id; - - if is_subclass(v_parent_type, 'content_item') = 't' and - is_valid_child(v_parent_id, content_item.new.content_type) = 'f' then - - raise_application_error(-20000, - 'This item''s content type ' || content_item.new.content_type || - ' is not allowed in this container ' || v_parent_id); - - end if; - - exception when NO_DATA_FOUND then - - raise_application_error(-20000, - 'Invalid parent ID ' || v_parent_id || - ' specified in content_item.new'); - - end; - - end if; - - -- Create the object - - v_item_id := acs_object.new( - object_id => content_item.new.item_id, - object_type => content_item.new.item_subtype, - context_id => v_context_id, - creation_date => content_item.new.creation_date, - creation_user => content_item.new.creation_user, - creation_ip => content_item.new.creation_ip - ); - - -- Turn off security inheritance if there is no security context - --if context_id is null then - -- update acs_objects set security_inherit_p = 'f' - -- where object_id = v_item_id; - --end if; - - insert into cr_items ( - item_id, name, content_type, parent_id - ) values ( - v_item_id, content_item.new.name, - content_item.new.content_type, v_parent_id - ); - - -- if the parent is not a folder, insert into cr_child_rels - if v_parent_id ^= 0 and - content_folder.is_folder(v_parent_id) = 'f' and - content_item.is_valid_child(v_parent_id, - content_item.new.content_type) = 't' then - - v_rel_id := acs_object.new( - object_type => 'cr_item_child_rel', - context_id => v_parent_id - ); - - if content_item.new.relation_tag is null then - v_rel_tag := content_item.get_content_type(v_parent_id) - || '-' || content_item.new.content_type; - else - v_rel_tag := content_item.new.relation_tag; - end if; - - insert into cr_child_rels ( - rel_id, parent_id, child_id, relation_tag, order_n - ) values ( - v_rel_id, v_parent_id, v_item_id, v_rel_tag, v_item_id - ); - - end if; - - -- use the name of the item if no title is supplied - if content_item.new.title is null then - v_title := content_item.new.name; - else - v_title := content_item.new.title; - end if; - - -- create the revision if data or title or text is not null - -- note that the caller could theoretically specify both text - -- and data, in which case the text is ignored. - - if content_item.new.data is not null then - - v_revision_id := content_revision.new( - item_id => v_item_id, - title => v_title, - description => content_item.new.description, - data => content_item.new.data, - mime_type => content_item.new.mime_type, - creation_date => content_item.new.creation_date, - creation_user => content_item.new.creation_user, - creation_ip => content_item.new.creation_ip, - nls_language => content_item.new.nls_language - ); - - elsif content_item.new.title is not null or - content_item.new.text is not null then - - v_revision_id := content_revision.new( - item_id => v_item_id, - title => v_title, - description => content_item.new.description, - text => content_item.new.text, - mime_type => content_item.new.mime_type, - creation_date => content_item.new.creation_date, - creation_user => content_item.new.creation_user, - creation_ip => content_item.new.creation_ip - ); - - end if; - - -- make the revision live if is_live is 't' - if content_item.new.is_live = 't' then - content_item.set_live_revision(v_revision_id); - end if; - - -- Have the new item inherit the permission of the parent item - -- if no security context was specified - --if parent_id is not null and context_id is null then - -- content_permission.inherit_permissions ( - -- parent_id, v_item_id, creation_user - -- ); - --end if; - - return v_item_id; -end new; - -function is_published ( - item_id in cr_items.item_id%TYPE -) return char -is - v_is_published char(1); -begin - - select - 't' into v_is_published - from - cr_items - where - live_revision is not null - and - publish_status = 'live' - and - item_id = is_published.item_id; - - return v_is_published; - exception - when NO_DATA_FOUND then - return 'f'; -end is_published; - -function is_publishable ( - item_id in cr_items.item_id%TYPE -) return char -is - v_child_count integer; - v_rel_count integer; - v_template_id cr_templates.template_id%TYPE; - - -- get the child types registered to this content type - cursor c_child_types is - select - child_type, min_n, max_n - from - cr_type_children - where - parent_type = content_item.get_content_type( is_publishable.item_id ); - - -- get the relation types registered to this content type - cursor c_rel_types is - select - target_type, min_n, max_n - from - cr_type_relations - where - content_type = content_item.get_content_type( is_publishable.item_id ); - - -- get the publishing workflows associated with this content item - -- there should only be 1 if CMS exists, otherwise 0 - cursor c_pub_wf is - select - case_id, state - from - wf_cases - where - workflow_key = 'publishing_wf' - and - object_id = is_publishable.item_id; - -begin - - -- validate children - -- make sure the # of children of each type fall between min_n and max_n - for v_child_type in c_child_types loop - select - count(rel_id) into v_child_count - from - cr_child_rels - where - parent_id = is_publishable.item_id - and - content_item.get_content_type( child_id ) = v_child_type.child_type; - - -- make sure # of children is in range - if v_child_type.min_n is not null - and v_child_count < v_child_type.min_n then - return 'f'; - end if; - if v_child_type.max_n is not null - and v_child_count > v_child_type.max_n then - return 'f'; - end if; - - end loop; - - - -- validate relations - -- make sure the # of ext links of each type fall between min_n and max_n - for v_rel_type in c_rel_types loop - select - count(rel_id) into v_rel_count - from - cr_item_rels i, acs_objects o - where - i.related_object_id = o.object_id - and - i.item_id = is_publishable.item_id - and - nvl(content_item.get_content_type(o.object_id),o.object_type) = v_rel_type.target_type; - - -- make sure # of object relations is in range - if v_rel_type.min_n is not null - and v_rel_count < v_rel_type.min_n then - return 'f'; - end if; - if v_rel_type.max_n is not null - and v_rel_count > v_rel_type.max_n then - return 'f'; - end if; - end loop; - - -- validate publishing workflows - -- make sure any 'publishing_wf' associated with this item are finished - -- KG: logic is wrong here. Only the latest workflow matters, and even - -- that is a little problematic because more than one workflow may be - -- open on an item. In addition, this should be moved to CMS. - - for v_pub_wf in c_pub_wf loop - if v_pub_wf.state ^= 'finished' then - return 'f'; - end if; - end loop; - - return 't'; - exception - when NO_DATA_FOUND then - return 'f'; -end is_publishable; - - -function is_valid_child ( - item_id in cr_items.item_id%TYPE, - content_type in acs_object_types.object_type%TYPE -) return char -is - v_is_valid_child char(1); - v_max_children cr_type_children.max_n%TYPE; - v_n_children integer; -begin - - v_is_valid_child := 'f'; - - -- first check if content_type is a registered child_type - begin - select - max_n into v_max_children - from - cr_type_children - where - parent_type = content_item.get_content_type( is_valid_child.item_id ) - and - child_type = is_valid_child.content_type; - - exception - when NO_DATA_FOUND then - return 'f'; - end; - - -- if the max is null then infinite number is allowed - if v_max_children is null then - return 't'; - end if; - - -- next check if there are already max_n children of that content type - select - count(rel_id) into v_n_children - from - cr_child_rels - where - parent_id = is_valid_child.item_id - and - content_item.get_content_type( child_id ) = is_valid_child.content_type; - - if v_n_children < v_max_children then - v_is_valid_child := 't'; - end if; - - return v_is_valid_child; - exception - when NO_DATA_FOUND then - return 'f'; -end is_valid_child; - -/* delete a content item - 1) delete all associated workflows - 2) delete all symlinks associated with this object - 3) delete any revisions for this item - 4) unregister template relations - 5) delete all permissions associated with this item - 6) delete keyword associations - 7) delete all associated comments */ -procedure delete ( - item_id in cr_items.item_id%TYPE -) is - - cursor c_wf_cases_cur is - select - case_id - from - wf_cases - where - object_id = item_id; - - cursor c_symlink_cur is - select - symlink_id - from - cr_symlinks - where - target_id = content_item.delete.item_id; - - cursor c_revision_cur is - select - revision_id - from - cr_revisions - where - item_id = content_item.delete.item_id; - - cursor c_rel_cur is - select - rel_id - from - cr_item_rels - where - item_id = content_item.delete.item_id - or - related_object_id = content_item.delete.item_id; - - cursor c_child_cur is - select - rel_id - from - cr_child_rels - where - child_id = content_item.delete.item_id; - - cursor c_parent_cur is - select - rel_id, child_id - from - cr_child_rels - where - parent_id = content_item.delete.item_id; - - -- this is strictly for debugging - -- cursor c_error_cur is - -- select - -- object_id, object_type - -- from - -- acs_objects - -- where - -- context_id = content_item.delete.item_id; - -begin - - dbms_output.put_line('Deleting associated workflows...'); - -- 1) delete all workflow cases associated with this item - for v_wf_cases_val in c_wf_cases_cur loop - workflow_case.delete(v_wf_cases_val.case_id); - end loop; - - dbms_output.put_line('Deleting symlinks...'); - -- 2) delete all symlinks to this item - for v_symlink_val in c_symlink_cur loop - content_symlink.delete(v_symlink_val.symlink_id); - end loop; - - dbms_output.put_line('Unscheduling item...'); - delete from cr_release_periods - where item_id = content_item.delete.item_id; - - dbms_output.put_line('Deleting associated revisions...'); - -- 3) delete all revisions of this item - delete from cr_item_publish_audit - where item_id = content_item.delete.item_id; - for v_revision_val in c_revision_cur loop - content_revision.delete(v_revision_val.revision_id); - end loop; - - dbms_output.put_line('Deleting associated item templates...'); - -- 4) unregister all templates to this item - delete from cr_item_template_map - where item_id = content_item.delete.item_id; - - dbms_output.put_line('Deleting item relationships...'); - -- Delete all relations on this item - for v_rel_val in c_rel_cur loop - acs_rel.delete(v_rel_val.rel_id); - end loop; - - dbms_output.put_line('Deleting child relationships...'); - for v_rel_val in c_child_cur loop - acs_rel.delete(v_rel_val.rel_id); - end loop; - - dbms_output.put_line('Deleting parent relationships...'); - for v_rel_val in c_parent_cur loop - acs_rel.delete(v_rel_val.rel_id); - content_item.delete(v_rel_val.child_id); - end loop; - - dbms_output.put_line('Deleting associated permissions...'); - -- 5) delete associated permissions - delete from acs_permissions - where object_id = content_item.delete.item_id; - - dbms_output.put_line('Deleting keyword associations...'); - -- 6) delete keyword associations - delete from cr_item_keyword_map - where item_id = content_item.delete.item_id; - - dbms_output.put_line('Deleting associated comments...'); - -- 7) delete associated comments - journal_entry.delete_for_object( content_item.delete.item_id ); - - -- context_id debugging loop - --for v_error_val in c_error_cur loop - -- dbms_output.put_line('ID=' || v_error_val.object_id || ' TYPE=' - -- || v_error_val.object_type); - --end loop; - - dbms_output.put_line('Deleting content item...'); - acs_object.delete(content_item.delete.item_id); - -end delete; - - -procedure rename ( - item_id in cr_items.item_id%TYPE, - name in cr_items.name%TYPE -) is - cursor exists_cur is - select - item_id - from - cr_items - where - name = rename.name - and - parent_id = (select - parent_id - from - cr_items - where - item_id = rename.item_id); - - exists_id integer; -begin - - open exists_cur; - fetch exists_cur into exists_id; - - if exists_cur%NOTFOUND then - update cr_items - set name = rename.name - where item_id = rename.item_id; - else - if exists_id <> rename.item_id then - raise_application_error(-20000, - 'An item with the name ' || rename.name || - ' already exists in this directory.'); - end if; - end if; - -end rename; - -function get_id ( - item_path in varchar2, - root_folder_id in cr_items.item_id%TYPE default null, - resolve_index in char default 'f' -) return cr_items.item_id%TYPE is - - v_item_path varchar2(4000); - v_root_folder_id cr_items.item_id%TYPE; - parent_id integer; - child_id integer; - start_pos integer := 1; - end_pos integer; - counter integer := 0; - item_name varchar2(200); - -begin - - v_root_folder_id := nvl(root_folder_id, c_root_folder_id); - - -- If the request path is the root, then just return the root folder - if item_path = '/' then - return v_root_folder_id; - end if; - - -- Remove leading, trailing spaces, leading slashes - v_item_path := rtrim(ltrim(trim(item_path), '/'), '/'); - - parent_id := v_root_folder_id; - - -- if parent_id is a symlink, resolve it - parent_id := content_symlink.resolve(parent_id); - - loop - - end_pos := instr(v_item_path, '/', start_pos); - - if end_pos = 0 then - item_name := substr(v_item_path, start_pos); - else - item_name := substr(v_item_path, start_pos, end_pos - start_pos); - end if; - - select - item_id into child_id - from - cr_items - where - parent_id = get_id.parent_id - and - name = item_name; - - exit when end_pos = 0; - - parent_id := child_id; - - -- if parent_id is a symlink, resolve it - parent_id := content_symlink.resolve(parent_id); - - start_pos := end_pos + 1; - - end loop; - - if get_id.resolve_index = 't' then - - -- if the item is a folder and has an index page, then return - - if content_folder.is_folder( child_id ) = 't' and - content_folder.get_index_page( child_id ) is not null then - - child_id := content_folder.get_index_page( child_id ); - - end if; - - end if; - - return child_id; - -exception - when NO_DATA_FOUND then - return null; -end get_id; - -function get_path ( - item_id in cr_items.item_id%TYPE, - root_folder_id in cr_items.item_id%TYPE default null -) return varchar2 -is - - cursor c_abs_cur is - select - name, parent_id, level as tree_level - from - cr_items - where - parent_id <> 0 - connect by - prior parent_id = item_id - start with - item_id = get_path.item_id - order by - tree_level desc; - - v_count integer; - v_name varchar2(400); - v_parent_id integer := 0; - v_tree_level integer; - - v_resolved_root_id integer; - - cursor c_rel_cur is - select - parent_id, level as tree_level - from - cr_items - where - parent_id <> 0 - connect by - prior parent_id = item_id - start with - item_id = v_resolved_root_id - order by - tree_level desc; - - v_rel_parent_id integer := 0; - v_rel_tree_level integer := 0; - - v_path varchar2(4000) := ''; - -begin - - -- check that the item exists - select count(*) into v_count from cr_items where item_id = get_path.item_id; - - if v_count = 0 then - raise_application_error(-20000, 'Invalid item ID: ' || item_id); - end if; - - -- begin walking down the path to the item (from the repository root) - open c_abs_cur; - - -- if the root folder is not null then prepare for a relative path - - if root_folder_id is not null then - - -- if root_folder_id is a symlink, resolve it (child items will point - -- to the actual folder, not the symlink) - - v_resolved_root_id := content_symlink.resolve(root_folder_id); - - -- begin walking down the path to the root folder. Discard - -- elements of the item path as long as they are the same as the root - -- folder - - open c_rel_cur; - - while v_parent_id = v_rel_parent_id loop - fetch c_abs_cur into v_name, v_parent_id, v_tree_level; - fetch c_rel_cur into v_rel_parent_id, v_rel_tree_level; - exit when c_abs_cur%NOTFOUND or c_rel_cur%NOTFOUND; - end loop; - - -- walk the remainder of the relative path, add a '..' for each - -- additional step - - loop - exit when c_rel_cur%NOTFOUND; - v_path := v_path || '../'; - fetch c_rel_cur into v_rel_parent_id, v_rel_tree_level; - end loop; - - -- an item relative to itself is '../item' - if v_resolved_root_id = item_id then - v_path := '../'; - end if; - - else - - -- this is an absolute path so prepend a '/' - v_path := '/'; - - -- prime the pump to be consistent with relative path execution plan - fetch c_abs_cur into v_name, v_parent_id, v_tree_level; - - end if; - - -- loop over the remainder of the absolute path - - loop - - v_path := v_path || v_name; - - fetch c_abs_cur into v_name, v_parent_id, v_tree_level; - - exit when c_abs_cur%NOTFOUND; - - v_path := v_path || '/'; - - end loop; - - return v_path; - -end get_path; - - -function get_virtual_path ( - item_id in cr_items.item_id%TYPE, - root_folder_id in cr_items.item_id%TYPE default c_root_folder_id -) return varchar2 -is - v_path varchar2(4000); - v_item_id cr_items.item_id%TYPE; - v_is_folder char(1); - v_index cr_items.item_id%TYPE; -begin - - -- first resolve the item - v_item_id := content_symlink.resolve( get_virtual_path.item_id ); - - v_is_folder := content_folder.is_folder( v_item_id ); - v_index := content_folder.get_index_page( v_item_id ); - - -- if the folder has an index page - if v_is_folder = 't' and v_index is not null then - v_path := content_item.get_path( content_symlink.resolve( v_index )); - else - v_path := content_item.get_path( v_item_id ); - end if; - - return v_path; - exception - when NO_DATA_FOUND then - return null; -end get_virtual_path; - - - -procedure write_to_file ( - item_id in cr_items.item_id%TYPE, - root_path in varchar2 -)is - - blob_loc cr_revisions.content%TYPE; - v_revision cr_items.live_revision%TYPE; - -begin - - v_revision := get_live_revision(item_id); - select content into blob_loc from cr_revisions - where revision_id = v_revision; - - blob_to_file(root_path || get_path(item_id), blob_loc); - -exception when no_data_found then - - raise_application_error(-20000, 'No live revision for content item' || - item_id || ' in content_item.write_to_file.'); - -end write_to_file; - -procedure register_template ( - item_id in cr_items.item_id%TYPE, - template_id in cr_templates.template_id%TYPE, - use_context in cr_item_template_map.use_context%TYPE -) is - -begin - - -- register template if it is not already registered - insert into cr_item_template_map ( - template_id, item_id, use_context - ) select - register_template.template_id, - register_template.item_id, - register_template.use_context - from - dual - where - not exists ( select 1 - from - cr_item_template_map - where - item_id = register_template.item_id - and - template_id = register_template.template_id - and - use_context = register_template.use_context ); - -end register_template; - -procedure unregister_template ( - item_id in cr_items.item_id%TYPE, - template_id in cr_templates.template_id%TYPE default null, - use_context in cr_item_template_map.use_context%TYPE default null -) is - -begin - - if use_context is null and template_id is null then - - delete from cr_item_template_map - where item_id = unregister_template.item_id; - - elsif use_context is null then - - delete from cr_item_template_map - where template_id = unregister_template.template_id - and item_id = unregister_template.item_id; - - elsif template_id is null then - - delete from cr_item_template_map - where item_id = unregister_template.item_id - and use_context = unregister_template.use_context; - - else - - delete from cr_item_template_map - where template_id = unregister_template.template_id - and item_id = unregister_template.item_id - and use_context = unregister_template.use_context; - - end if; - -end unregister_template; - -function get_template ( - item_id in cr_items.item_id%TYPE, - use_context in cr_item_template_map.use_context%TYPE -) return cr_templates.template_id%TYPE is - - v_template_id cr_templates.template_id%TYPE; - v_content_type cr_items.content_type%TYPE; - - cursor item_cur is - select - template_id - from - cr_item_template_map - where - item_id = get_template.item_id - and - use_context = get_template.use_context; - -begin - - -- look for a template assigned specifically to this item - open item_cur; - fetch item_cur into v_template_id; - - -- otherwise get the default for the content type - if item_cur%NOTFOUND then - select - m.template_id - into - v_template_id - from - cr_items i, cr_type_template_map m - where - i.item_id = get_template.item_id - and - i.content_type = m.content_type - and - m.use_context = get_template.use_context - and - m.is_default = 't'; - end if; - - return v_template_id; - -exception - when NO_DATA_FOUND then - return null; -end get_template; - --- Return the object type of this item - -function get_content_type ( - item_id in cr_items.item_id%TYPE -) return cr_items.content_type%TYPE is - v_content_type cr_items.content_type%TYPE; -begin - - select - content_type into v_content_type - from - cr_items - where - item_id = get_content_type.item_id; - - return v_content_type; -exception - when NO_DATA_FOUND then - return null; -end get_content_type; - -function get_live_revision ( - item_id in cr_items.item_id%TYPE -) return cr_revisions.revision_id%TYPE is - - v_revision_id acs_objects.object_id%TYPE; - -begin - - select - live_revision into v_revision_id - from - cr_items - where - item_id = get_live_revision.item_id; - - return v_revision_id; - -exception - when NO_DATA_FOUND then - return null; -end get_live_revision; - -procedure set_live_revision ( - revision_id in cr_revisions.revision_id%TYPE, - publish_status in cr_items.publish_status%TYPE default 'ready' -) is -begin - - update - cr_items - set - live_revision = set_live_revision.revision_id, - publish_status = set_live_revision.publish_status - where - item_id = (select - item_id - from - cr_revisions - where - revision_id = set_live_revision.revision_id); - - update - cr_revisions - set - publish_date = sysdate - where - revision_id = set_live_revision.revision_id; - -end set_live_revision; - - -procedure unset_live_revision ( - item_id in cr_items.item_id%TYPE -) is -begin - - update - cr_items - set - live_revision = NULL - where - item_id = unset_live_revision.item_id; - - -- if an items publish status is "live", change it to "ready" - update - cr_items - set - publish_status = 'production' - where - publish_status = 'live' - and - item_id = unset_live_revision.item_id; - - -end unset_live_revision; - - -procedure set_release_period ( - item_id in cr_items.item_id%TYPE, - start_when date default null, - end_when date default null -) is - - v_count integer; - -begin - - select decode(count(*),0,0,1) into v_count from cr_release_periods - where item_id = set_release_period.item_id; - - if v_count = 0 then - - insert into cr_release_periods ( - item_id, start_when, end_when - ) values ( - item_id, start_when, end_when - ); - - else - - update cr_release_periods - set start_when = set_release_period.start_when, - end_when = set_release_period.end_when - where - item_id = set_release_period.item_id; - - end if; - -end set_release_period; - - -function get_revision_count ( - item_id in cr_items.item_id%TYPE -) return number is - - v_count integer; - -begin - - select - count(*) into v_count - from - cr_revisions - where - item_id = get_revision_count.item_id; - - return v_count; - -end get_revision_count; - -function get_context ( - item_id in cr_items.item_id%TYPE -) return acs_objects.context_id%TYPE is - - v_context_id acs_objects.context_id%TYPE; - -begin - - select - context_id - into - v_context_id - from - acs_objects - where - object_id = get_context.item_id; - - return v_context_id; - -exception when no_data_found then - - raise_application_error(-20000, 'Content item ' || item_id || - ' does not exist in content_item.get_context'); - - -end get_context; - --- 1) make sure we are not moving the item to an invalid location: --- that is, the destination folder exists and is a valid folder --- 2) make sure the content type of the content item is registered --- to the target folder --- 3) update the parent_id for the item -procedure move ( - item_id in cr_items.item_id%TYPE, - target_folder_id in cr_folders.folder_id%TYPE -) is -begin - - if content_folder.is_folder(item_id) = 't' then - content_folder.move(item_id, target_folder_id); - elsif content_folder.is_folder(target_folder_id) = 't' then - - - if content_folder.is_registered( move.target_folder_id, - get_content_type( move.item_id )) = 't' and - content_folder.is_registered( move.target_folder_id, - get_content_type( content_symlink.resolve( move.item_id)),'f') = 't' - then - - -- update the parent_id for the item - update cr_items - set parent_id = move.target_folder_id - where item_id = move.item_id; - end if; - - end if; -end move; - -procedure copy ( - item_id in cr_items.item_id%TYPE, - target_folder_id in cr_folders.folder_id%TYPE, - creation_user in acs_objects.creation_user%TYPE, - creation_ip in acs_objects.creation_ip%TYPE default null -) is - - copy_id cr_items.item_id%TYPE; - -begin - - copy_id := copy2(item_id, target_folder_id, creation_user, creation_ip); - -end copy; - --- copy a content item to a target folder --- 1) make sure we are not copying the item to an invalid location: --- that is, the destination folder exists, is a valid folder, --- and is not the current folder --- 2) make sure the content type of the content item is registered --- with the current folder --- 3) create a new item with no revisions in the target folder --- 4) copy the latest revision from the original item to the new item (if any) - -function copy2 ( - item_id in cr_items.item_id%TYPE, - target_folder_id in cr_folders.folder_id%TYPE, - creation_user in acs_objects.creation_user%TYPE, - creation_ip in acs_objects.creation_ip%TYPE default null -) return cr_items.item_id%TYPE is - v_current_folder_id cr_folders.folder_id%TYPE; - v_num_revisions integer; - v_name cr_items.name%TYPE; - v_content_type cr_items.content_type%TYPE; - v_locale cr_items.locale%TYPE; - v_item_id cr_items.item_id%TYPE; - v_revision_id cr_revisions.revision_id%TYPE; - v_is_registered char(1); - v_old_revision_id cr_revisions.revision_id%TYPE; - v_new_revision_id cr_revisions.revision_id%TYPE; -begin - - -- call content_folder.copy if the item is a folder - if content_folder.is_folder(copy2.item_id) = 't' then - content_folder.copy( - folder_id => copy2.item_id, - target_folder_id => copy2.target_folder_id, - creation_user => copy2.creation_user, - creation_ip => copy2.creation_ip - ); - -- call content_symlink.copy if the item is a symlink - elsif content_symlink.is_symlink(copy2.item_id) = 't' then - content_symlink.copy( - symlink_id => copy2.item_id, - target_folder_id => copy2.target_folder_id, - creation_user => copy2.creation_user, - creation_ip => copy2.creation_ip - ); - -- make sure the target folder is really a folder - elsif content_folder.is_folder(copy2.target_folder_id) = 't' then - - select - parent_id - into - v_current_folder_id - from - cr_items - where - item_id = copy2.item_id; - - -- can't copy to the same folder - if copy2.target_folder_id ^= v_current_folder_id then - - select - content_type, name, locale, - nvl(live_revision, latest_revision) - into - v_content_type, v_name, v_locale, v_revision_id - from - cr_items - where - item_id = copy2.item_id; - - -- make sure the content type of the item is registered to the folder - v_is_registered := content_folder.is_registered( - folder_id => copy2.target_folder_id, - content_type => v_content_type, - include_subtypes => 'f' - ); - - if v_is_registered = 't' then - -- create the new content item - v_item_id := content_item.new( - parent_id => copy2.target_folder_id, - name => v_name, - locale => v_locale, - content_type => v_content_type, - creation_user => copy2.creation_user, - creation_ip => copy2.creation_ip - ); - - -- get the latest revision of the old item - select - latest_revision into v_old_revision_id - from - cr_items - where - item_id = copy2.item_id; - - -- copy the latest revision (if any) to the new item - if v_old_revision_id is not null then - v_new_revision_id := content_revision.copy ( - revision_id => v_old_revision_id, - target_item_id => v_item_id, - creation_user => copy2.creation_user, - creation_ip => copy2.creation_ip - ); - end if; - end if; - - - end if; - end if; - - return v_item_id; - -end copy2; - --- get the latest revision for an item -function get_latest_revision ( - item_id in cr_items.item_id%TYPE -) return cr_revisions.revision_id%TYPE is - v_revision_id integer; - - cursor c_revision_cur is - select - r.revision_id - from - cr_revisions r, acs_objects o - where - r.revision_id = o.object_id - and - r.item_id = get_latest_revision.item_id - order by - o.creation_date desc; -begin - - open c_revision_cur; - fetch c_revision_cur into v_revision_id; - if c_revision_cur%NOTFOUND then - return null; - end if; - close c_revision_cur; - return v_revision_id; - -exception - when NO_DATA_FOUND then - return null; -end get_latest_revision; - - - -function get_best_revision ( - item_id in cr_items.item_id%TYPE -) return cr_revisions.revision_id%TYPE -is - v_revision_id cr_revisions.revision_id%TYPE; -begin - - select - NVL (live_revision, latest_revision ) - into - v_revision_id - from - cr_items - where - item_id = get_best_revision.item_id; - - return v_revision_id; -exception - when NO_DATA_FOUND then - return null; -end get_best_revision; - - - -function get_title ( - item_id in cr_items.item_id%TYPE, - is_live in char default 'f' -) return cr_revisions.title%TYPE is - - v_title cr_revisions.title%TYPE; - v_content_type cr_items.content_type%TYPE; - -begin - - select content_type into v_content_type from cr_items - where item_id = get_title.item_id; - - if v_content_type = 'content_folder' then - select label into v_title from cr_folders - where folder_id = get_title.item_id; - elsif v_content_type = 'content_symlink' then - select label into v_title from cr_symlinks - where symlink_id = get_title.item_id; - else - if is_live ^= 'f' then - select - title into v_title - from - cr_revisions r, cr_items i - where - i.item_id = get_title.item_id - and - r.revision_id = i.live_revision; - else - select - title into v_title - from - cr_revisions r, cr_items i - where - i.item_id = get_title.item_id - and - r.revision_id = i.latest_revision; - end if; - end if; - - return v_title; - -end get_title; - -function get_publish_date ( - item_id in cr_items.item_id%TYPE, - is_live in char default 'f' -) return cr_revisions.publish_date%TYPE -is - v_revision_id cr_revisions.revision_id%TYPE; - v_publish_date cr_revisions.publish_date%TYPE; -begin - - if is_live ^= 'f' then - select - publish_date into v_publish_date - from - cr_revisions r, cr_items i - where - i.item_id = get_publish_date.item_id - and - r.revision_id = i.live_revision; - else - select - publish_date into v_publish_date - from - cr_revisions r, cr_items i - where - i.item_id = get_publish_date.item_id - and - r.revision_id = i.latest_revision; - end if; - - return v_publish_date; - -exception when no_data_found then - return null; -end get_publish_date; - -function is_subclass ( - object_type in acs_object_types.object_type%TYPE, - supertype in acs_object_types.supertype%TYPE -) return char is - - v_subclass_p char; - - cursor c_inherit_cur is - select - object_type - from - acs_object_types - connect by - prior object_type = supertype - start with - object_type = is_subclass.supertype; - -begin - - v_subclass_p := 'f'; - - for v_inherit_val in c_inherit_cur loop - if v_inherit_val.object_type = is_subclass.object_type then - v_subclass_p := 't'; - end if; - end loop; - - return v_subclass_p; - -end is_subclass; - -function relate ( - item_id in cr_items.item_id%TYPE, - object_id in acs_objects.object_id%TYPE, - relation_tag in cr_type_relations.relation_tag%TYPE default 'generic', - order_n in cr_item_rels.order_n%TYPE default null, - relation_type in acs_object_types.object_type%TYPE default 'cr_item_rel' -) return cr_item_rels.rel_id%TYPE -is - v_content_type cr_items.content_type%TYPE; - v_object_type acs_objects.object_type%TYPE; - v_is_valid integer; - v_rel_id integer; - v_exists integer; - v_order_n cr_item_rels.order_n%TYPE; -begin - - -- check the relationship is valid - v_content_type := content_item.get_content_type ( relate.item_id ); - v_object_type := content_item.get_content_type ( relate.object_id ); - - select - decode( count(1),0,0,1) into v_is_valid - from - cr_type_relations - where - content_item.is_subclass( v_object_type, target_type ) = 't' - and - content_item.is_subclass( v_content_type, content_type ) = 't'; - - if v_is_valid = 0 then - raise_application_error(-20000, - 'There is no registered relation type matching this item relation.'); - end if; - - if relate.item_id ^= relate.object_id then - -- check that these two items are not related already - --dbms_output.put_line( 'checking if the items are already related...'); - begin - select - rel_id, 1 as v_exists into v_rel_id, v_exists - from - cr_item_rels - where - item_id = relate.item_id - and - related_object_id = relate.object_id - and - relation_tag = relate.relation_tag; - exception when no_data_found then - v_exists := 0; - end; - - - -- if order_n is null, use rel_id (the order the item was related) - if relate.order_n is null then - v_order_n := v_rel_id; - else - v_order_n := relate.order_n; - end if; - - - -- if relationship does not exist, create it - if v_exists <> 1 then - --dbms_output.put_line( 'creating new relationship...'); - v_rel_id := acs_object.new( - object_type => relation_type, - context_id => item_id - ); - insert into cr_item_rels ( - rel_id, item_id, related_object_id, order_n, relation_tag - ) values ( - v_rel_id, item_id, object_id, v_order_n, relation_tag - ); - - -- if relationship already exists, update it - else - --dbms_output.put_line( 'updating existing relationship...'); - update cr_item_rels set - relation_tag = relate.relation_tag, - order_n = v_order_n - where - rel_id = v_rel_id; - end if; - - end if; - return v_rel_id; -end relate; - - -procedure unrelate ( - rel_id in cr_item_rels.rel_id%TYPE -) is -begin - - -- delete the relation object - acs_rel.delete( unrelate.rel_id ); - - -- delete the row from the cr_item_rels table - delete from cr_item_rels where rel_id = unrelate.rel_id; - -end unrelate; - -function is_index_page ( - item_id in cr_items.item_id%TYPE, - folder_id in cr_folders.folder_id%TYPE -) return varchar2 -is -begin - if content_folder.get_index_page(folder_id) = item_id then - return 't'; - else - return 'f'; - end if; -end is_index_page; - - - -function get_parent_folder ( - item_id in cr_items.item_id%TYPE -) return cr_folders.folder_id%TYPE -is - v_folder_id cr_folders.folder_id%TYPE; - v_parent_folder_p char(1); -begin - v_parent_folder_p := 'f'; - - while v_parent_folder_p = 'f' loop - - select - parent_id, content_folder.is_folder( parent_id ) - into - v_folder_id, v_parent_folder_p - from - cr_items - where - item_id = get_parent_folder.item_id; - - end loop; - - return v_folder_id; - exception - when NO_DATA_FOUND then - return null; -end get_parent_folder; - - - -end content_item; -/ -show errors - - --- Trigger to maintain context_id in acs_objects -create or replace trigger cr_items_update_tr -after update of parent_id on cr_items for each row -begin - update acs_objects set context_id = :new.parent_id - where object_id = :new.item_id; -end cr_items_ins_update_tr; -/ -show errors - --- Trigger to maintain publication audit trail -create or replace trigger cr_items_publish_update_tr -before update of live_revision, publish_status on cr_items for each row -begin - insert into cr_item_publish_audit ( - item_id, old_revision, new_revision, old_status, new_status, publish_date - ) values ( - :new.item_id, :old.live_revision, :new.live_revision, - :old.publish_status, :new.publish_status, - sysdate - ); -end cr_items_publish_update_tr; -/ -show errors - - Index: openacs-4/packages/acs-content-repository/sql/content-keyword.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-keyword.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-keyword.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,308 +0,0 @@ --- Data model to support content repository of the ArsDigita --- Community System - --- Copyright (C) 1999-2000 ArsDigita Corporation --- Author: Stanislav Freidin (sfreidin@arsdigita.com) - --- 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 or replace package body content_keyword -as - -function get_heading ( - keyword_id in cr_keywords.keyword_id%TYPE -) return varchar2 -is - v_heading varchar2(4000); -begin - - select heading into v_heading from cr_keywords - where keyword_id = content_keyword.get_heading.keyword_id; - - return v_heading; -end get_heading; - -function get_description ( - keyword_id in cr_keywords.keyword_id%TYPE -) return varchar2 -is - v_description varchar2(4000); -begin - - select description into v_description from cr_keywords - where keyword_id = content_keyword.get_description.keyword_id; - - return v_description; -end get_description; - -procedure set_heading ( - keyword_id in cr_keywords.keyword_id%TYPE, - heading in cr_keywords.heading%TYPE -) -is -begin - - update cr_keywords set - heading = set_heading.heading - where - keyword_id = set_heading.keyword_id; - -end set_heading; - -procedure set_description ( - keyword_id in cr_keywords.keyword_id%TYPE, - description in cr_keywords.description%TYPE -) -is -begin - - update cr_keywords set - description = set_description.description - where - keyword_id = set_description.keyword_id; -end set_description; - -function is_leaf ( - keyword_id in cr_keywords.keyword_id%TYPE -) return varchar2 -is - v_leaf varchar2(1); - - cursor c_leaf_cur is - select - 'f' - from - cr_keywords k - where - k.parent_id = is_leaf.keyword_id; - -begin - - open c_leaf_cur; - fetch c_leaf_cur into v_leaf; - if c_leaf_cur%NOTFOUND then - v_leaf := 't'; - end if; - close c_leaf_cur; - - return v_leaf; -end is_leaf; - -function new ( - heading in cr_keywords.heading%TYPE, - description in cr_keywords.description%TYPE default null, - parent_id in cr_keywords.parent_id%TYPE default null, - keyword_id in cr_keywords.keyword_id%TYPE default null, - creation_date in acs_objects.creation_date%TYPE - default sysdate, - creation_user in acs_objects.creation_user%TYPE - default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - object_type in acs_object_types.object_type%TYPE default 'content_keyword' -) return cr_keywords.keyword_id%TYPE -is - v_id integer; -begin - - v_id := acs_object.new (object_id => keyword_id, - context_id => parent_id, - object_type => object_type, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip); - - insert into cr_keywords - (heading, description, keyword_id, parent_id) - values - (heading, description, v_id, parent_id); - - return v_id; -end new; - -procedure delete ( - keyword_id in cr_keywords.keyword_id%TYPE -) -is - v_item_id integer; - cursor c_rel_cur is - select item_id from cr_item_keyword_map - where keyword_id = content_keyword.delete.keyword_id; -begin - - open c_rel_cur; - loop - fetch c_rel_cur into v_item_id; - exit when c_rel_cur%NOTFOUND; - item_unassign(v_item_id, content_keyword.delete.keyword_id); - end loop; - close c_rel_cur; - - acs_object.delete(keyword_id); -end delete; - -procedure item_assign ( - item_id in cr_items.item_id%TYPE, - keyword_id in cr_keywords.keyword_id%TYPE, - context_id in acs_objects.context_id%TYPE default null, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null -) -is - v_dummy integer; -begin - - -- Do nothing if the keyword is assigned already - select decode(count(*),0,0,1) into v_dummy from dual - where exists (select 1 from cr_item_keyword_map - where item_id=item_assign.item_id - and keyword_id=item_assign.keyword_id); - - if v_dummy > 0 then - -- previous assignment exists - return; - end if; - - insert into cr_item_keyword_map ( - item_id, keyword_id - ) values ( - item_id, keyword_id - ); - -end item_assign; - -procedure item_unassign ( - item_id in cr_items.item_id%TYPE, - keyword_id in cr_keywords.keyword_id%TYPE -) is -begin - - delete from cr_item_keyword_map - where item_id = item_unassign.item_id - and keyword_id = item_unassign.keyword_id; - -end item_unassign; - -function is_assigned ( - item_id in cr_items.item_id%TYPE, - keyword_id in cr_keywords.keyword_id%TYPE, - recurse in varchar2 default 'none' -) return varchar2 -is - v_ret varchar2(1); -begin - - -- Look for an exact match - if recurse = 'none' then - declare - begin - select 't' into v_ret from cr_item_keyword_map - where item_id = is_assigned.item_id - and keyword_id = is_assigned.keyword_id; - return 't'; - exception when no_data_found then - return 'f'; - end; - end if; - - -- Look from specific to general - if recurse = 'up' then - begin - select 't' into v_ret from dual where exists (select 1 from - (select keyword_id from cr_keywords - connect by parent_id = prior keyword_id - start with keyword_id = is_assigned.keyword_id - ) t, cr_item_keyword_map m - where - t.keyword_id = m.keyword_id - and - m.item_id = is_assigned.item_id); - - return 't'; - - exception when no_data_found then - return 'f'; - end; - end if; - - if recurse = 'down' then - begin - select 't' into v_ret from dual where exists ( select 1 from - (select keyword_id from cr_keywords - connect by prior parent_id = keyword_id - start with keyword_id = is_assigned.keyword_id - ) t, cr_item_keyword_map m - where - t.keyword_id = m.keyword_id - and - m.item_id = is_assigned.item_id); - - return 't'; - - exception when no_data_found then - return 'f'; - end; - end if; - - -- Tried none, up and down - must be an invalid parameter - raise_application_error (-20000, 'The recurse parameter to ' || - 'content_keyword.is_assigned should be ''none'', ''up'' or ''down''.'); - -end is_assigned; - -function get_path ( - keyword_id in cr_keywords.keyword_id%TYPE -) return varchar2 -is - v_path varchar2(4000) := ''; - v_is_found varchar2(1) := 'f'; - - cursor c_keyword_cur is - select - heading - from ( - select - heading, level as tree_level - from cr_keywords - connect by prior parent_id = keyword_id - start with keyword_id = get_path.keyword_id - ) - order by - tree_level desc; - - v_heading cr_keywords.heading%TYPE; -begin - - open c_keyword_cur; - loop - fetch c_keyword_cur into v_heading; - exit when c_keyword_cur%NOTFOUND; - v_is_found := 't'; - v_path := v_path || '/' || v_heading; - end loop; - close c_keyword_cur; - - if v_is_found = 'f' then - return null; - else - return v_path; - end if; -end get_path; - -end content_keyword; -/ -show errors - - --- Ensure that the context_id in acs_objects is always set to the --- parent_id in cr_keywords - -create or replace trigger cr_keywords_update_tr -after update of parent_id on cr_keywords for each row -begin - update acs_objects set context_id = :new.parent_id - where object_id = :new.keyword_id; -end cr_keywords_update_tr; -/ -show errors Index: openacs-4/packages/acs-content-repository/sql/content-package.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-package.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-package.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,54 +0,0 @@ --- Data model to support content repository of the ArsDigita --- Publishing System - --- Copyright (C) 1999-2000 ArsDigita Corporation --- Author: Karl Goldstein (karlg@arsdigita.com) - --- $Id: content-package.sql,v 1.1 2001/03/13 22:59:26 ben 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 or replace package content is - -procedure string_to_blob( - s varchar2, blob_loc blob) -as language - java -name - 'com.arsdigita.content.Util.stringToBlob( - java.lang.String, oracle.sql.BLOB - )'; - -procedure string_to_blob_size( - s varchar2, blob_loc blob, blob_size number) -as language - java -name - 'com.arsdigita.content.Util.stringToBlob( - java.lang.String, oracle.sql.BLOB, int - )'; - -function blob_to_string( - blob_loc blob) return varchar2 -as language - java -name - 'com.arsdigita.content.Util.blobToString( - oracle.sql.BLOB - ) return java.lang.String'; - -procedure blob_to_file( -s varchar2, blob_loc blob) -as language - java -name - 'com.arsdigita.content.Util.blobToFile( - java.lang.String, oracle.sql.BLOB - )'; - -end content; -/ -show errors Index: openacs-4/packages/acs-content-repository/sql/content-perms.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-perms.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-perms.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,409 +0,0 @@ --------------------------------------------------------------- --- --- Set up the basic permissions used by the CMS, and invent some --- api in dealing with them --- --------------------------------------------------------------- - -declare - v_perms varchar2(1) := 'f'; -begin - - begin - select 't' into v_perms from dual - where exists (select 1 from acs_privileges - where privilege = 'cm_root'); - exception when no_data_found then - v_perms := 'f'; - end; - - if v_perms <> 't' then - - -- Dummy root privilege - acs_privilege.create_privilege('cm_root', 'Root', 'Root'); - -- He can do everything - acs_privilege.create_privilege('cm_admin', 'Administrator', 'Administrators'); - acs_privilege.create_privilege('cm_write', 'Write', 'Write'); - acs_privilege.create_privilege('cm_new', 'Create New Item', 'Create New Item'); - acs_privilege.create_privilege('cm_examine', 'Admin-level Read', 'Admin-level Read'); - acs_privilege.create_privilege('cm_read', 'User-level Read', 'User-level Read'); - acs_privilege.create_privilege('cm_item_workflow', 'Modify Workflow', 'Modify Workflow'); - acs_privilege.create_privilege('cm_perm_admin', 'Modify Any Permissions', 'Modify Any Permissions'); - acs_privilege.create_privilege('cm_perm', 'Donate Permissions', 'Donate Permissions'); - - acs_privilege.add_child('cm_root', 'cm_admin'); -- Do anything to an object - acs_privilege.add_child('cm_admin', 'cm_write'); -- Do anything to an object - acs_privilege.add_child('cm_write', 'cm_new'); -- Create subitems - acs_privilege.add_child('cm_new', 'cm_examine'); -- View in admin mode - acs_privilege.add_child('cm_examine', 'cm_read'); -- View in user mode - acs_privilege.add_child('cm_write', 'cm_item_workflow'); -- Change item workflow - - acs_privilege.add_child('cm_admin', 'cm_perm_admin'); -- Modify any permissions - acs_privilege.add_child('cm_perm_admin', 'cm_perm'); -- Modify any permissions on an item - - -- Proper inheritance - acs_privilege.add_child('admin', 'cm_root'); - - end if; - -end; -/ -show errors - -create or replace package body content_permission -is - - procedure inherit_permissions ( - parent_object_id in acs_objects.object_id%TYPE, - child_object_id in acs_objects.object_id%TYPE, - child_creator_id in parties.party_id%TYPE default null - ) - is - v_dummy integer; - begin - - -- Determine if the child is a direct descendant of the - -- parent - select 1 into v_dummy from acs_objects - where object_id = child_object_id - and context_id = parent_object_id; - - -- Copy everything one level down - insert into acs_permissions ( - object_id, grantee_id, privilege - ) ( - select - inherit_permissions.child_object_id as object_id, - grantee_id, - privilege - from - acs_permissions - where - object_id = parent_object_id - ); - - if child_creator_id is not null then - -- Grant cm_write and cm_perm to the child creator - if content_permission.permission_p ( - child_object_id, child_creator_id, 'cm_perm' - ) <> 't' then - -- Turn off inheritance and grant permission - update acs_objects set security_inherit_p = 'f' - where object_id = child_object_id; - acs_permission.grant_permission ( - child_object_id, child_creator_id, 'cm_perm' - ); - end if; - - if content_permission.permission_p ( - child_object_id, child_creator_id, 'cm_write' - ) <> 't' then - acs_permission.grant_permission ( - child_object_id, child_creator_id, 'cm_write' - ); - end if; - end if; - - exception when no_data_found then - raise_application_error(-20000, 'Child object is not actually a child -of the parent object in inherit_permissions'); - - end inherit_permissions; - - function has_grant_authority ( - object_id in acs_objects.object_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE - ) return varchar2 - is - begin - -- Can donate permission only if you already have it and you have cm_perm, - -- OR you have cm_perm_admin - if content_permission.permission_p (object_id, holder_id, 'cm_perm_admin')= 't' - or ( - content_permission.permission_p (object_id, holder_id, 'cm_perm') = 't' and - content_permission.permission_p (object_id, holder_id, privilege) = 't' - ) - then - return 't'; - else - return 'f'; - end if; - end has_grant_authority; - - function has_revoke_authority ( - object_id in acs_objects.object_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE, - revokee_id in parties.party_id%TYPE - ) return varchar2 - is - cursor c_perm_cur is - select 't' from - (select object_id from acs_objects - connect by prior context_id = object_id - start with object_id = has_revoke_authority.object_id) t, - (select privilege, child_privilege from acs_privilege_hierarchy - connect by prior privilege = child_privilege - start with child_privilege = 'cm_perm') h - where - content_permission.permission_p( - t.object_id, has_revoke_authority.holder_id, h.child_privilege - ) = 't' - and - content_permission.permission_p( - t.object_id, has_revoke_authority.revokee_id, h.privilege - ) = 'f'; - - v_ret varchar2(1); - begin - open c_perm_cur; - fetch c_perm_cur into v_ret; - if c_perm_cur%NOTFOUND then - v_ret := 'f'; - end if; - return v_ret; - end has_revoke_authority; - - - procedure grant_permission_h ( - object_id in acs_objects.object_id%TYPE, - grantee_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE - ) - is - cursor c_priv_cur is - select descendant from acs_privilege_descendant_map - where privilege = grant_permission_h.privilege - and descendant <> grant_permission_h.privilege; - - v_privilege acs_privilege_descendant_map.privilege%TYPE; - begin - - -- If the permission is already granted, do nothing - if content_permission.permission_p ( - object_id, grantee_id, privilege - ) = 't' then - return; - end if; - - -- Grant the parent, make sure there is no inheritance - update acs_objects set security_inherit_p = 'f' - where object_id = grant_permission_h.object_id; - acs_permission.grant_permission(object_id, grantee_id, privilege); - - -- Revoke the children - they are no longer relevant - open c_priv_cur; - loop - fetch c_priv_cur into v_privilege; - exit when c_priv_cur%NOTFOUND; - acs_permission.revoke_permission(object_id, grantee_id, v_privilege); - end loop; - close c_priv_cur; - end grant_permission_h; - - procedure grant_permission ( - object_id in acs_objects.object_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE, - recepient_id in parties.party_id%TYPE, - is_recursive in varchar2 default 'f', - object_type in acs_objects.object_type%TYPE default 'content_item' - ) - is - cursor c_object_cur is - select - o.object_id - from - (select object_id, object_type from acs_objects - connect by context_id = prior object_id - start with object_id = grant_permission.object_id) o - where - has_grant_authority ( - o.object_id, holder_id, grant_permission.privilege - ) = 't' - and - content_item.is_subclass (o.object_type, grant_permission.object_type) = 't'; - - v_object_id acs_objects.object_id%TYPE; - - begin - - open c_object_cur; - loop - - -- Determine if the grant is possible - fetch c_object_cur into v_object_id; - exit when c_object_cur%NOTFOUND; - - -- Grant the parent and revoke the children, since we don't need them - -- anymore - content_permission.grant_permission_h ( - v_object_id, recepient_id, privilege - ); - - exit when is_recursive = 'f'; - - end loop; - close c_object_cur; - - end grant_permission; - - procedure revoke_permission_h ( - object_id in acs_objects.object_id%TYPE, - revokee_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE - ) - is - cursor c_perm_child_cur is - select child_privilege from acs_privilege_hierarchy - where privilege = revoke_permission_h.privilege; - - v_privilege acs_privileges.privilege%TYPE; - begin - - -- Grant all child privileges of the parent privilege - open c_perm_child_cur; - loop - fetch c_perm_child_cur into v_privilege; - exit when c_perm_child_cur%NOTFOUND; - acs_permission.grant_permission ( - revoke_permission_h.object_id, - revoke_permission_h.revokee_id, - v_privilege - ); - end loop; - close c_perm_child_cur; - - -- Revoke the parent privilege - acs_permission.revoke_permission ( - revoke_permission_h.object_id, - revoke_permission_h.revokee_id, - revoke_permission_h.privilege - ); - end revoke_permission_h; - - procedure revoke_permission ( - object_id in acs_objects.object_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE, - revokee_id in parties.party_id%TYPE, - is_recursive in varchar2 default 'f', - object_type in acs_objects.object_type%TYPE default 'content_item' - ) - is - cursor c_object_cur is - select - o.object_id - from - (select object_id, object_type from acs_objects - connect by context_id = prior object_id - start with object_id = revoke_permission.object_id) o - where - has_revoke_authority (o.object_id, holder_id, privilege, revokee_id) = 't' - and - content_item.is_subclass(o.object_type, revoke_permission.object_type) = 't'; - - v_object_id acs_objects.object_id%TYPE; - - begin - - open c_object_cur; - loop - fetch c_object_cur into v_object_id; - exit when c_object_cur%NOTFOUND; - - content_permission.revoke_permission_h ( - v_object_id, revokee_id, privilege - ); - - exit when is_recursive = 'f'; - - end loop; - - end revoke_permission; - - function permission_p ( - object_id in acs_objects.object_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE - ) return varchar2 - is - -/* cursor c_perm_cur is - select 't' as truth from - acs_privilege_descendant_map pdm, - acs_permissions p, - (select - group_id as party_id from group_member_map - where member_id = permission_p.holder_id - union select - permission_p.holder_id as party_id from dual - ) gm - where - p.privilege = pdm.privilege - and - p.object_id = permission_p.object_id - and - p.grantee_id = gm.party_id - and - p.privilege = pdm.privilege - and - pdm.descendant = permission_p.privilege; - - v_perm varchar2(1);*/ - - begin - -/* open c_perm_cur; - fetch c_perm_cur into v_perm; - if c_perm_cur%NOTFOUND then - v_perm := 'f'; - end if; - close c_perm_cur; - return v_perm; */ - - return acs_permission.permission_p (object_id, holder_id, privilege); - - end permission_p; - - -- Determine if the CMS admin exists - function cm_admin_exists - return varchar2 - is - v_exists varchar2(1); - begin - - select 't' into v_exists from dual - where exists ( - select 1 from acs_permissions - where privilege in ('cm_admin', 'cm_root') - ); - - return v_exists; - - exception when no_data_found then - return 'f'; - end cm_admin_exists; - - -end content_permission; -/ -show errors - - - - - - - - - - - - - - - - - Index: openacs-4/packages/acs-content-repository/sql/content-revision.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-revision.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-revision.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,526 +0,0 @@ --- Data model to support content repository of the ArsDigita --- Community System - --- Copyright (C) 1999-2000 ArsDigita Corporation --- Author: Karl Goldstein (karlg@arsdigita.com) - --- $Id: content-revision.sql,v 1.1 2001/03/13 22:59:26 ben 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 or replace package body content_revision -as - -function new ( - title in cr_revisions.title%TYPE, - description in cr_revisions.description%TYPE default null, - publish_date in cr_revisions.publish_date%TYPE default sysdate, - mime_type in cr_revisions.mime_type%TYPE default 'text/plain', - nls_language in cr_revisions.nls_language%TYPE default null, - data in cr_revisions.content%TYPE, - item_id in cr_items.item_id%TYPE, - revision_id in cr_revisions.revision_id%TYPE default null, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null -) return cr_revisions.revision_id%TYPE is - - v_revision_id integer; - v_content_type acs_object_types.object_type%TYPE; - -begin - - v_content_type := content_item.get_content_type(item_id); - - v_revision_id := acs_object.new( - object_id => revision_id, - object_type => v_content_type, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip, - context_id => item_id - ); - - insert into cr_revisions ( - revision_id, title, description, mime_type, publish_date, - nls_language, content, item_id - ) values ( - v_revision_id, title, description, mime_type, publish_date, - nls_language, data, item_id - ); - - return v_revision_id; - -end new; - -function new ( - title in cr_revisions.title%TYPE, - description in cr_revisions.description%TYPE default null, - publish_date in cr_revisions.publish_date%TYPE default sysdate, - mime_type in cr_revisions.mime_type%TYPE default 'text/plain', - nls_language in cr_revisions.nls_language%TYPE default null, - text in varchar2 default ' ', - item_id in cr_items.item_id%TYPE, - revision_id in cr_revisions.revision_id%TYPE default null, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null -) return cr_revisions.revision_id%TYPE is - - v_revision_id integer; - blob_loc cr_revisions.content%TYPE; - -begin - - blob_loc := empty_blob(); - - v_revision_id := content_revision.new( - title => title, - description => description, - publish_date => publish_date, - mime_type => mime_type, - nls_language => nls_language, - data => blob_loc, - item_id => item_id, - revision_id => revision_id, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip - ); - - select - content into blob_loc - from - cr_revisions - where - revision_id = v_revision_id - for update; - - string_to_blob(text, blob_loc); - - return v_revision_id; - -end new; - -procedure copy_attributes ( - content_type in acs_object_types.object_type%TYPE, - revision_id in cr_revisions.revision_id%TYPE, - copy_id in cr_revisions.revision_id%TYPE -) is - - v_table_name acs_object_types.table_name%TYPE; - v_id_column acs_object_types.id_column%TYPE; - - cursor attr_cur is - select - attribute_name - from - acs_attributes - where - object_type = copy_attributes.content_type; - - cols varchar2(2000) := ''; - -begin - - select table_name, id_column into v_table_name, v_id_column - from acs_object_types where object_type = copy_attributes.content_type; - - for attr_rec in attr_cur loop - cols := cols || ', ' || attr_rec.attribute_name; - end loop; - - execute immediate 'insert into ' || v_table_name || - ' ( ' || v_id_column || cols || ' ) ( select ' || copy_id || cols || - ' from ' || v_table_name || ' where ' || v_id_column || ' = ' || - revision_id || ')'; - -end copy_attributes; - -function copy ( - revision_id in cr_revisions.revision_id%TYPE, - copy_id in cr_revisions.revision_id%TYPE default null, - target_item_id in cr_items.item_id%TYPE default null, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null -) return cr_revisions.revision_id%TYPE -is - v_copy_id cr_revisions.revision_id%TYPE; - v_target_item_id cr_items.item_id%TYPE; - - -- get the content_type and supertypes - cursor type_cur is - select - object_type - from - acs_object_types - where - object_type ^= 'acs_object' - and - object_type ^= 'content_revision' - connect by - prior supertype = object_type - start with - object_type = ( - select object_type from acs_objects where object_id = copy.revision_id - ) - order by - level desc; - -begin - -- use the specified item_id or the item_id of the original revision - -- if none is specified - if target_item_id is null then - select item_id into v_target_item_id from cr_revisions - where revision_id = copy.revision_id; - else - v_target_item_id := target_item_id; - end if; - - -- use the copy_id or generate a new copy_id if none is specified - -- the copy_id is a revision_id - if copy_id is null then - select acs_object_id_seq.nextval into v_copy_id from dual; - else - v_copy_id := copy_id; - end if; - - -- create the basic object - insert into acs_objects ( - object_id, object_type, context_id, security_inherit_p, - creation_user, creation_date, creation_ip, - last_modified, modifying_user, modifying_ip - ) ( select - v_copy_id, object_type, context_id, security_inherit_p, - copy.creation_user, sysdate, copy.creation_ip, - sysdate, copy.creation_user, copy.creation_ip from - acs_objects where object_id = copy.revision_id - ); - - -- create the basic revision (using v_target_item_id) - insert into cr_revisions ( - revision_id, title, description, publish_date, mime_type, - nls_language, content, item_id - ) ( select - v_copy_id, title, description, publish_date, mime_type, nls_language, - content, v_target_item_id - from - cr_revisions - where - revision_id = copy.revision_id - ); - - -- iterate over the ancestor types and copy attributes - for type_rec in type_cur loop - copy_attributes(type_rec.object_type, copy.revision_id, v_copy_id); - end loop; - - return v_copy_id; -end copy; - -procedure delete ( - revision_id in cr_revisions.revision_id%TYPE -) is - v_item_id cr_items.item_id%TYPE; - v_latest_revision cr_revisions.revision_id%TYPE; - v_live_revision cr_revisions.revision_id%TYPE; - -begin - - -- Get item id and latest/live revisions - select item_id into v_item_id from cr_revisions - where revision_id = content_revision.delete.revision_id; - - select - latest_revision, live_revision - into - v_latest_revision, v_live_revision - from - cr_items - where - item_id = v_item_id; - - -- Recalculate latest revision - if v_latest_revision = content_revision.delete.revision_id then - declare - cursor c_revision_cur is - select r.revision_id from cr_revisions r, acs_objects o - where o.object_id = r.revision_id - and r.item_id = v_item_id - and r.revision_id <> content_revision.delete.revision_id - order by o.creation_date desc; - begin - open c_revision_cur; - fetch c_revision_cur into v_latest_revision; - if c_revision_cur%NOTFOUND then - v_latest_revision := null; - end if; - close c_revision_cur; - - update cr_items set latest_revision = v_latest_revision - where item_id = v_item_id; - end; - end if; - - -- Clear live revision - if v_live_revision = content_revision.delete.revision_id then - update cr_items set live_revision = null - where item_id = v_item_id; - end if; - - -- Clear the audit - delete from cr_item_publish_audit - where old_revision = content_revision.delete.revision_id - or new_revision = content_revision.delete.revision_id; - - -- Delete the revision - acs_object.delete(revision_id); - -end delete; - -function get_number ( - revision_id in cr_revisions.revision_id%TYPE -) return number is - - cursor rev_cur is - select - revision_id - from - cr_revisions r, acs_objects o - where - item_id = (select item_id from cr_revisions - where revision_id = get_number.revision_id) - and - o.object_id = r.revision_id - order by - o.creation_date; - - v_number integer; - v_revision cr_revisions.revision_id%TYPE; - -begin - - open rev_cur; - loop - - fetch rev_cur into v_revision; - - if v_revision = get_number.revision_id then - v_number := rev_cur%ROWCOUNT; - exit; - end if; - - end loop; - close rev_cur; - - return v_number; - -end get_number; - -procedure index_attributes( - revision_id IN cr_revisions.revision_id%TYPE -) is - - clob_loc clob; - v_revision_id cr_revisions.revision_id%TYPE; - -begin - - insert into cr_revision_attributes ( - revision_id, attributes - ) values ( - revision_id, empty_clob() - ) returning attributes into clob_loc; - - v_revision_id := write_xml(revision_id, clob_loc); - -end index_attributes; - -function import_xml ( - item_id IN cr_items.item_id%TYPE, - revision_id IN cr_revisions.revision_id%TYPE, - doc_id IN number -) return cr_revisions.revision_id%TYPE is - - clob_loc clob; - v_revision_id cr_revisions.revision_id%TYPE; - -begin - - select doc into clob_loc from cr_xml_docs where doc_id = import_xml.doc_id; - v_revision_id := read_xml(item_id, revision_id, clob_loc); - - return v_revision_id; - -end import_xml; - -function export_xml ( - revision_id IN cr_revisions.revision_id%TYPE -) return cr_xml_docs.doc_id%TYPE is - - clob_loc clob; - v_doc_id cr_xml_docs.doc_id%TYPE; - v_revision_id cr_revisions.revision_id%TYPE; - -begin - - insert into cr_xml_docs (doc_id, doc) - values (cr_xml_doc_seq.nextval, empty_clob()) - returning doc_id, doc into v_doc_id, clob_loc; - - v_revision_id := write_xml(revision_id, clob_loc); - - return v_doc_id; - -end export_xml; - -procedure to_html ( - revision_id IN cr_revisions.revision_id%TYPE -) is - - tmp_clob clob; - blob_loc blob; - -begin - - ctx_doc.filter('cr_doc_filter_index', revision_id, tmp_clob); - - select - content into blob_loc - from - cr_revisions - where - revision_id = to_html.revision_id - for update; - - clob_to_blob(tmp_clob, blob_loc); - - dbms_lob.freetemporary(tmp_clob); - -end to_html; - -function is_live ( - revision_id in cr_revisions.revision_id%TYPE -) return varchar2 -is - v_ret varchar2(1); -begin - - select 't' into v_ret from cr_items - where live_revision = is_live.revision_id; - - return v_ret; - -exception when no_data_found then - return 'f'; -end is_live; - -function is_latest ( - revision_id in cr_revisions.revision_id%TYPE -) return varchar2 -is - v_ret varchar2(1); -begin - - select 't' into v_ret from cr_items - where latest_revision = is_latest.revision_id; - - return v_ret; - -exception when no_data_found then - return 'f'; -end is_latest; - -procedure to_temporary_clob ( - revision_id in cr_revisions.revision_id%TYPE -) is - b blob; - c clob; - -begin - - insert into cr_content_text ( - revision_id, content - ) values ( - revision_id, empty_clob() - ) returning content into c; - - select content into b from cr_revisions - where revision_id = to_temporary_clob.revision_id; - - blob_to_clob(b, c); - -end to_temporary_clob; - - - - --- revision_id is the revision with the content that is to be copied -procedure content_copy ( - revision_id in cr_revisions.revision_id%TYPE, - revision_id_dest in cr_revisions.revision_id%TYPE default null -) is - lobs blob; - lobd blob; - v_item_id cr_items.item_id%TYPE; - v_content_length integer; - v_revision_id_dest cr_revisions.revision_id%TYPE; -begin - - select - dbms_lob.getlength( content ), item_id - into - v_content_length, v_item_id - from - cr_revisions - where - revision_id = content_copy.revision_id; - - -- get the destination revision - if content_copy.revision_id_dest is null then - select - latest_revision into v_revision_id_dest - from - cr_items - where - item_id = v_item_id; - else - v_revision_id_dest := content_copy.revision_id_dest; - end if; - - - -- only copy the content if the source content is not null - if v_content_length is not null and v_content_length > 0 then - - /* The internal LOB types - BLOB, CLOB, and NCLOB - use copy semantics, as - opposed to the reference semantics which apply to BFILEs. - When a BLOB, CLOB, or NCLOB is copied from one row to another row in - the same table or in a different table, the actual LOB value is - copied, not just the LOB locator. */ - update cr_revisions - set content = ( select content from cr_revisions - where revision_id = content_copy.revision_id ) - where revision_id = v_revision_id_dest; - end if; - -end content_copy; - - - -end content_revision; -/ -show errors - --- Trigger to maintain latest_revision in cr_items - -create or replace trigger cr_revision_latest_tr -after insert on cr_revisions for each row -begin - update cr_items set latest_revision = :new.revision_id - where item_id = :new.item_id; -end cr_revision_latest_tr; -/ -show errors - Index: openacs-4/packages/acs-content-repository/sql/content-schedule.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-schedule.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-schedule.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,165 +0,0 @@ --- Data model to support release scheduling of items in the content --- repository of the ArsDigita Publishing System - --- Copyright (C) 1999-2000 ArsDigita Corporation --- Author: Karl Goldstein (karlg@arsdigita.com) - --- $Id: content-schedule.sql,v 1.1 2001/03/13 22:59:26 ben 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 - -prompt *** Preparing for scheduled updates to live content... - -create or replace trigger cr_scheduled_release_tr -before insert on cr_scheduled_release_job -for each row -begin - - raise_application_error(-20000, - 'Inserts are not allowed into cr_scheduled_release_job.' - ); -end; -/ -show errors - --- Update the publishing status for items that are due to be released --- or expired. - -create or replace procedure cr_scheduled_release_exec is - - last_exec date; - this_exec date := sysdate; - - cursor start_cur is - select - p.item_id, live_revision - from - cr_release_periods p, cr_items i - where - start_when between last_exec and sysdate - and - p.item_id = i.item_id; - - cursor end_cur is - select - p.item_id, live_revision - from - cr_release_periods p, cr_items i - where - end_when between last_exec and sysdate - and - p.item_id = i.item_id; - - items_released integer := 0; - items_expired integer := 0; - - err_num integer := sqlcode; - err_msg varchar2(500) := substr(sqlerrm, 1, 500); - -begin - - begin - - select last_exec into last_exec from cr_scheduled_release_job; - - for item_rec in start_cur loop - - -- update publish status - update cr_items - set publish_status = 'live' - where - item_id = item_rec.item_id; - - items_released := items_released + 1; - - end loop; - - for item_rec in end_cur loop - - -- update publish status - update cr_items - set publish_status = 'expired' - where - item_id = item_rec.item_id; - - items_expired := items_expired + 1; - - end loop; - - exception - - when others then - err_num := SQLCODE; - err_msg := substr(SQLERRM, 1, 500); - end; - - -- keep a record of the update - - insert into cr_scheduled_release_log ( - items_released, items_expired, err_num, err_msg - ) values ( - items_released, items_expired, err_num, err_msg - ); - - -- Reset the last time of execution to start of processing - update cr_scheduled_release_job set last_exec = this_exec; - - commit; - -end cr_scheduled_release_exec; -/ -show errors - --- initialize the scheduled publication job - -declare - - v_job_id integer; - interval integer := 15; - - cursor job_cur is - select job from user_jobs - where what = 'cr_scheduled_release_exec;'; - -begin - - open job_cur; - fetch job_cur into v_job_id; - - if job_cur%NOTFOUND then - - dbms_output.put_line(' - Submitting job to process scheduled updates to live content...'); - - dbms_job.submit( - job => v_job_id, - what => 'cr_scheduled_release_exec;', - next_date => sysdate, - interval => 'sysdate + ' || (interval/24/60) - ); - - update cr_scheduled_release_job set job_id = v_job_id; - - else - - dbms_job.change( - job => v_job_id, - what => 'cr_scheduled_release_exec;', - next_date => sysdate, - interval => 'sysdate + ' || (interval/24/60) - ); - - end if; - -end; -/ -show errors - - - - - - - Index: openacs-4/packages/acs-content-repository/sql/content-search.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-search.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-search.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,108 +0,0 @@ ------------------------------------------------------------- --- Set up an index with INSO filtering on the content column ------------------------------------------------------------- - -set serveroutput on - -declare - v_exists integer; -begin - - -- Check whether the preference already exists - select decode(count(*),0,0,1) into v_exists from ctx_user_preferences - where pre_name = 'CONTENT_FILTER_PREF'; - - if v_exists = 0 then - - dbms_output.put_line('Creating content filter preference...'); - - ctx_ddl.create_preference - ( - preference_name => 'CONTENT_FILTER_PREF', - object_name => 'INSO_FILTER' - ); - - end if; - -end; -/ - -create index cr_rev_content_index on cr_revisions ( content ) - indextype is ctxsys.context - parameters ('FILTER content_filter_pref' ); - -alter index cr_rev_content_index rebuild online parameters ('sync'); - ------------------------------------------------------------- --- Set up an XML index for searching attributes ------------------------------------------------------------- - --- To find the word company in the title only: - --- select revision_id,score(1) --- from cr_revision_attributes --- where contains(attributes, 'company WITHIN title', 1) > 0; - --- use a direct datastore rather than setting up a user datastore --- this avoids having to generate an XML document for every --- revision every time the index is rebuilt. It also avoids the --- cumbersome manual process of setting up a user datastore. - -create or replace package content_search is - - procedure update_attribute_index; - -end content_search; -/ -show errors - -create or replace package body content_search is - -procedure update_attribute_index is -begin - - for c1 in (select revision_id from cr_revisions r where not exists ( - select 1 from cr_revision_attributes a - where a.revision_id = r.revision_id)) loop - - content_revision.index_attributes(c1.revision_id); - commit; - - end loop; - -end update_attribute_index; - -end; -/ -show errors - -declare - v_exists integer; -begin - - -- Check whether the section group already exists - select decode(count(*),0,0,1) into v_exists from ctx_user_section_groups - where sgp_name = 'AUTO'; - - if v_exists = 0 then - - dbms_output.put_line('Creating auto section group for attribute index...'); - - ctx_ddl.create_section_group('auto', 'AUTO_SECTION_GROUP'); - - end if; -end; -/ - -create index cr_rev_attribute_index on cr_revision_attributes ( attributes ) - indextype is ctxsys.context - parameters ('filter ctxsys.null_filter section group auto' ); - -begin - content_search.update_attribute_index; -end; -/ -show errors - -alter index cr_rev_attribute_index rebuild online parameters ('sync'); - Index: openacs-4/packages/acs-content-repository/sql/content-symlink.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-symlink.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-symlink.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,260 +0,0 @@ --- Data model to support content repository of the ArsDigita --- Community System - --- Copyright (C) 1999-2000 ArsDigita Corporation --- Author: Karl Goldstein (karlg@arsdigita.com) - --- $Id: content-symlink.sql,v 1.1 2001/03/13 22:59:26 ben 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 or replace package body content_symlink -as - -function new ( - name in cr_items.name%TYPE default null, - label in cr_symlinks.label%TYPE default null, - target_id in cr_items.item_id%TYPE, - parent_id in cr_items.parent_id%TYPE, - symlink_id in cr_symlinks.symlink_id%TYPE default null, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null -) return cr_symlinks.symlink_id%TYPE is - v_symlink_id cr_symlinks.symlink_id%TYPE; - v_name cr_items.name%TYPE; - v_label cr_symlinks.label%TYPE; -begin - - -- SOME CHECKS -- - - -- 1) check that the target is now a symlink - if content_symlink.is_symlink( target_id ) = 't' then - raise_application_error(-20000, - 'Cannot create a symlink to a symlink ' || target_id); - end if; - - -- 2) check that the parent is a folder - if content_folder.is_folder(parent_id) = 'f' then - raise_application_error(-20000, - 'The parent is not a folder'); - end if; - - -- 3) check that parent folder supports symlinks - if content_folder.is_registered(parent_id,'content_symlink') = 'f' then - raise_application_error(-20000, - 'This folder does not allow symlinks to be created'); - end if; - - -- 4) check that the content folder supports the target item's content type - if content_folder.is_registered( - parent_id,content_item.get_content_type(target_id)) = 'f' then - - raise_application_error(-20000, - 'This folder does not allow symlinks to items of type ' || - content_item.get_content_type(target_id) || ' to be created'); - end if; - - -- PASSED ALL CHECKS -- - - - - -- Select default name if the name is null - begin - if name is null then - select - 'symlink_to_' || name into v_name - from - cr_items - where - item_id = target_id; - - else - v_name := name; - end if; - exception when no_data_found then - v_name := null; - end; - - -- Select default label if the label is null - if content_symlink.new.label is null then - v_label := 'Symlink to ' || v_name; - else - v_label := content_symlink.new.label; - end if; - - v_symlink_id := content_item.new( - item_id => content_symlink.new.symlink_id, - name => v_name, - content_type => 'content_symlink', - creation_date => content_symlink.new.creation_date, - creation_user => content_symlink.new.creation_user, - creation_ip => content_symlink.new.creation_ip, - parent_id => content_symlink.new.parent_id - ); - - insert into cr_symlinks - (symlink_id, target_id, label) - values - (v_symlink_id, content_symlink.new.target_id, v_label); - - return v_symlink_id; - -end new; - - -procedure delete ( - symlink_id in cr_symlinks.symlink_id%TYPE -) is -begin - - delete from cr_symlinks - where symlink_id = content_symlink.delete.symlink_id; - - content_item.delete(content_symlink.delete.symlink_id); -end delete; - - - -function is_symlink ( - item_id in cr_items.item_id%TYPE -) return char -is - v_symlink_p integer := 0; -begin - - - select - count(*) into v_symlink_p - from - cr_symlinks - where - symlink_id = is_symlink.item_id; - - if v_symlink_p = 1 then - return 't'; - else - return 'f'; - end if; - -end is_symlink; - - -procedure copy ( - symlink_id in cr_symlinks.symlink_id%TYPE, - target_folder_id in cr_folders.folder_id%TYPE, - creation_user in acs_objects.creation_user%TYPE, - creation_ip in acs_objects.creation_ip%TYPE default null -) is - v_current_folder_id cr_folders.folder_id%TYPE; - v_name cr_items.name%TYPE; - v_target_id cr_items.item_id%TYPE; - v_label cr_symlinks.label%TYPE; - v_symlink_id cr_symlinks.symlink_id%TYPE; -begin - - if content_folder.is_folder(copy.target_folder_id) = 't' then - select - parent_id - into - v_current_folder_id - from - cr_items - where - item_id = copy.symlink_id; - - -- can't copy to the same folder - if copy.target_folder_id ^= v_current_folder_id then - - select - i.name, content_symlink.resolve(i.item_id), s.label - into - v_name, v_target_id, v_label - from - cr_symlinks s, cr_items i - where - s.symlink_id = i.item_id - and - s.symlink_id = copy.symlink_id; - - - if content_folder.is_registered(copy.target_folder_id, - 'content_symlink') = 't' then - if content_folder.is_registered(copy.target_folder_id, - content_item.get_content_type(resolve(copy.symlink_id))) = 't' then - - v_symlink_id := content_symlink.new( - parent_id => copy.target_folder_id, - name => v_name, - label => v_label, - target_id => v_target_id, - creation_user => copy.creation_user, - creation_ip => copy.creation_ip - ); - - - end if; - end if; - end if; - end if; -end copy; - - -function resolve ( - item_id in cr_items.item_id%TYPE -) return cr_items.item_id%TYPE -is - v_target_id cr_items.item_id%TYPE; -begin - - select - target_id into v_target_id - from - cr_symlinks - where - symlink_id = resolve.item_id; - - return v_target_id; - -exception when no_data_found then - return resolve.item_id; -end resolve; - - -function resolve_content_type ( - item_id in cr_items.item_id%TYPE -) return cr_items.content_type%TYPE -is - v_content_type cr_items.content_type%TYPE; -begin - - select - content_item.get_content_type( target_id ) into v_content_type - from - cr_symlinks - where - symlink_id = resolve_content_type.item_id; - - return v_content_type; - exception - when NO_DATA_FOUND then - return null; -end resolve_content_type; - -end content_symlink; -/ -show errors - --- Convenience view to simply access to symlink targets - -create or replace view cr_resolved_items as - select - i.parent_id, i.item_id, i.name, - decode(s.target_id, NULL, 'f', 't') is_symlink, - nvl(s.target_id, i.item_id) resolved_id, s.label - from - cr_items i, cr_symlinks s - where - i.item_id = s.symlink_id (+); Index: openacs-4/packages/acs-content-repository/sql/content-template.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-template.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-template.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,122 +0,0 @@ --- Data model to support content repository of the ArsDigita Community --- System - --- Copyright (C) 1999-2000 ArsDigita Corporation --- Author: Karl Goldstein (karlg@arsdigita.com) - --- $Id: content-template.sql,v 1.1 2001/03/13 22:59:26 ben 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 or replace package body content_template -as - -function get_root_folder -return cr_folders.folder_id%TYPE -is -begin - return c_root_folder_id; -end get_root_folder; - -function new ( - name in cr_items.name%TYPE, - parent_id in cr_items.parent_id%TYPE default null, - template_id in cr_templates.template_id%TYPE default null, - creation_date in acs_objects.creation_date%TYPE - default sysdate, - creation_user in acs_objects.creation_user%TYPE - default null, - creation_ip in acs_objects.creation_ip%TYPE default null -) return cr_templates.template_id%TYPE -is - - v_template_id cr_templates.template_id%TYPE; - v_parent_id cr_items.parent_id%TYPE; - -begin - - if parent_id is null then - v_parent_id := c_root_folder_id; - else - v_parent_id := parent_id; - end if; - - -- make sure we're allowed to create a template in this folder - if content_folder.is_folder(parent_id) = 't' and - content_folder.is_registered(parent_id,'content_template') = 'f' then - - raise_application_error(-20000, - 'This folder does not allow templates to be created'); - - else - v_template_id := content_item.new ( - item_id => content_template.new.template_id, - name => content_template.new.name, - parent_id => v_parent_id, - content_type => 'content_template', - creation_date => content_template.new.creation_date, - creation_user => content_template.new.creation_user, - creation_ip => content_template.new.creation_ip - ); - - insert into cr_templates ( - template_id - ) values ( - v_template_id - ); - - return v_template_id; - end if; -end new; - --- delete all template relations -procedure delete ( - template_id in cr_templates.template_id%TYPE -) is -begin - - delete from cr_type_template_map - where template_id = content_template.delete.template_id; - - delete from cr_item_template_map - where template_id = content_template.delete.template_id; - - delete from cr_templates - where template_id = content_template.delete.template_id; - - content_item.delete(content_template.delete.template_id); - -end delete; - -function is_template ( - template_id in cr_templates.template_id%TYPE -) return varchar2 -is - v_ret varchar2(1); -begin - - select 't' into v_ret from cr_templates - where template_id = is_template.template_id; - - return v_ret; - -exception when no_data_found then - return 'f'; -end is_template; - -function get_path ( - template_id in cr_templates.template_id%TYPE, - root_folder_id in cr_folders.folder_id%TYPE default c_root_folder_id -) return varchar2 is - -begin - - return content_item.get_path(template_id, root_folder_id); - -end get_path; - -end content_template; -/ -show errors Index: openacs-4/packages/acs-content-repository/sql/content-test.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-test.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-test.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,313 +0,0 @@ -set serveroutput on - -declare - folder_id cr_folders.folder_id%TYPE; - folder_b_id cr_folders.folder_id%TYPE; - sub_folder_id cr_folders.folder_id%TYPE; - sub_sub_folder_id cr_folders.folder_id%TYPE; - item_id cr_items.item_id%TYPE; - simple_item_id cr_items.item_id%TYPE; - live_revision_id cr_revisions.revision_id%TYPE; - late_revision_id cr_revisions.revision_id%TYPE; - item_template_id cr_templates.template_id%TYPE; - type_template_id cr_templates.template_id%TYPE; - def_type_template_id cr_templates.template_id%TYPE; - dum_template_id cr_templates.template_id%TYPE; - - - symlink_a_id cr_symlinks.symlink_id%TYPE; - symlink_b_id cr_symlinks.symlink_id%TYPE; - - found_folder_id cr_folders.folder_id%TYPE; - -begin - - --- create folders and an item -folder_id := content_folder.new('grandpa', 'Grandpa', NULL, -1); -folder_b_id := content_folder.new('grandma', 'Grandma', NULL, -1); -sub_folder_id := content_folder.new('pa', 'Pa', NULL, folder_id); -sub_sub_folder_id := content_folder.new('me', 'Me', NULL, sub_folder_id); -item_id := content_item.new('puppy', sub_sub_folder_id); - -simple_item_id := content_item.new( - name => 'bunny', - title => 'Bugs Bunny', - description => 'Simple (Revisionless) Item Test', - text => 'Simple (Revisionless) Item Test Text', - parent_id => sub_sub_folder_id - ); - -live_revision_id := content_revision.new( - title => 'Live Revision of Puppy', - description => 'Live Revision of Puppy Description', - publish_date => to_date('1999-08-12','YYYY-MM-DD'), - mime_type => 'text/html', - text => 'Text for Live Revision of Puppy', - item_id => item_id - ); - -late_revision_id := content_revision.new( - title => 'Latest Revision of Puppy', - description => 'Latest Revision of Puppy Description', - publish_date => to_date('2001-09-22','YYYY-MM-DD'), - mime_type => 'text/html', - text => 'Text for Latest Revision of Puppy', - item_id => item_id - ); - -item_template_id := content_template.new( - name => 'Item Template' - ); - -type_template_id := content_template.new( - name => 'Type Template' - ); - -def_type_template_id := content_template.new( - name => 'Dumb Default Type Template' - ); - -dum_template_id := content_template.new( - name => 'Default Type Template' - ); - - -dbms_output.put_line('-------------------------------------'); -dbms_output.put_line('CREATING CONTENT FOLDERS AND ITEMS...'); -dbms_output.put_line('...all tests passed'); -dbms_output.put_line('Folder grandpa is ' || folder_id); -dbms_output.put_line('Folder grandma is ' || folder_b_id); -dbms_output.put_line('Sub folder pa is ' || sub_folder_id); -dbms_output.put_line('Sub sub folder me is ' || sub_sub_folder_id); -dbms_output.put_line('Added item puppy to sub sub folder me at ' || item_id); -dbms_output.put_line('Created simple item bunny to sub sub folder me at ' || simple_item_id); ---dbms_output.put_line('Added a revision to puppy at ' || live_revision_id); ---dbms_output.put_line('Added a revision to puppy at ' || late_revision_id); ---dbms_output.put_line('Created Item Template at ' || item_template_id); ---dbms_output.put_line('Created Type Template at ' || type_template_id); ---dbms_output.put_line('Created Def Type Template at ' || def_type_template_id); ---dbms_output.put_line('Created Dum Def Type Template at ' || dum_template_id); - - -dbms_output.put_line('-----------------------------------'); -dbms_output.put_line('FOLDERS AND EMPTY FOLDERS AND SUBFOLDERS'); -dbms_output.put_line('...all tests passed'); ---dbms_output.put_line('Is folder ' || folder_id || ' empty? ' || --- content_folder.is_empty(folder_id)); ---dbms_output.put_line('Is folder ' || sub_sub_folder_id || ' empty? ' || --- content_folder.is_empty(sub_sub_folder_id)); ---dbms_output.put_line('Is folder ' || sub_folder_id || ' empty? ' || --- content_folder.is_empty(sub_folder_id)); ---dbms_output.put_line('Is folder ' || folder_b_id || ' empty? ' || --- content_folder.is_empty(folder_b_id)); ---dbms_output.put_line('Is folder ' || folder_id || '? ' || --- content_folder.is_folder(folder_id)); ---dbms_output.put_line('Is folder ' || item_id || '? ' || --- content_folder.is_folder(item_id)); ---dbms_output.put_line('Is ' || folder_id || ' a subfolder of ' || --- sub_folder_id || '? ' || content_folder.is_sub_folder(sub_folder_id,folder_id)); ---dbms_output.put_line('Is ' || sub_folder_id || ' a subfolder of ' || --- folder_id || '? ' || content_folder.is_sub_folder(folder_id,sub_folder_id)); ---dbms_output.put_line('Is ' || sub_sub_folder_id || ' a subfolder of ' || --- folder_id || '? ' || content_folder.is_sub_folder(folder_id,sub_sub_folder_id)); ---dbms_output.put_line('Is ' || sub_folder_id || ' a subfolder of ' || --- -1 || '? ' || content_folder.is_sub_folder(-1,sub_folder_id)); - - - - -dbms_output.put_line('-------------------------------------'); -dbms_output.put_line('LIVE AND LATEST REVISIONS...'); -dbms_output.put_line('...all tests passed'); ---dbms_output.put_line('Get live_revision_id for item puppy ' || item_id || --- ' is ' || content_item.get_live_revision(item_id)); - -content_item.set_live_revision(live_revision_id); - - ---dbms_output.put_line('Set ' || live_revision_id || --- ' as the live revision for item puppy ' || item_id); ---dbms_output.put_line('Get live_revision_id for item puppy ' || item_id || --- ' is ' || content_item.get_live_revision(item_id)); ---dbms_output.put_line('Get live_revision_id for item kitty ' || --- simple_item_id || ' is ' || --- content_item.get_live_revision(simple_item_id)); ---dbms_output.put_line('Get late_revision_id for item puppy ' || item_id || --- ' is ' || content_item.get_latest_revision(item_id)); ---dbms_output.put_line('Get late_revision_id for item bunny ' || simple_item_id || --- ' is ' || content_item.get_latest_revision(simple_item_id)); - - - -content_item.register_template(item_id,item_template_id,'public'); -content_type.register_template('content_revision',type_template_id,'public'); -content_type.register_template('content_revision',def_type_template_id,'admin'); -content_type.register_template('content_revision',dum_template_id,'admin','t'); -content_type.set_default_template('content_revision',def_type_template_id,'admin'); - - -dbms_output.put_line('-------------------------------------'); -dbms_output.put_line('REGISTERING TEMPLATES TO ITEMS AND TYPES...'); -dbms_output.put_line('...all tests passed'); ---dbms_output.put_line('Registered Item Template ' || item_template_id || --- ' to item puppy ' || item_id || ' with public context'); ---dbms_output.put_line('Registered Type Template ' || type_template_id || --- ' to content_revision ' || item_id || ' with public context'); ---dbms_output.put_line('Registered Default Type Template ' || --- def_type_template_id || ' to content_revision ' || item_id || --- ' with admin context'); ---dbms_output.put_line('Get template id for item puppy ' || item_id || --- ' and context public is ' || content_item.get_template(item_id,'public')); ---dbms_output.put_line('Get template id for item puppy ' || item_id || --- ' and context admin is ' || content_item.get_template(item_id,'admin')); - - - - -found_folder_id := content_item.get_id('grandpa/pa/me', -1); - -dbms_output.put_line('-------------------------------------'); -dbms_output.put_line('LOCATING CONTENT FOLDERS AND ITEMS...'); -dbms_output.put_line('...all tests passed!'); ---dbms_output.put_line('Found me at grandpa/pa/me: ' || found_folder_id); ---dbms_output.put_line('Path for ' || found_folder_id || ' is ' || --- content_item.get_path(found_folder_id)); -dbms_output.put_line('Path for puppy ' || item_id || ' is ' || - content_item.get_path(item_id)); -dbms_output.put_line('Path for puppy ' || item_id || ' from folder_id: ' || - folder_id || ' is ' || - content_item.get_path(item_id,folder_id)); ---dbms_output.put_line('Path for puppy ' || item_id || --- ' from sub_folder_id: ' || sub_folder_id || ' is ' || --- content_item.get_path(item_id,sub_folder_id)); ---dbms_output.put_line('Path for puppy' || item_id --- || ' from sub_sub_folder_id: ' || sub_sub_folder_id || ' is ' || --- content_item.get_path(item_id,sub_sub_folder_id)); ---dbms_output.put_line('Get id of item with invalid path - shouldn''t return anything'); ---dbms_output.put_line('Found item at ' || content_item.get_id('grandpa/me', -200)); ---dbms_output.put_line('Get id of item using subpath'); ---dbms_output.put_line('Found item at ' || content_item.get_id('pa/me/puppy', folder_id)); ---dbms_output.put_line('This is the path to a folder from a subfolder'); ---dbms_output.put_line('Path for ' || sub_folder_id || ' from sub_sub_folder_id: ' || --- sub_sub_folder_id || ' is ' || --- content_item.get_path(sub_folder_id,sub_sub_folder_id)); ---dbms_output.put_line('This is a path to an item from a non-existant item'); ---dbms_output.put_line('Path for ' || item_id || ' from nonexistant_id: ' || --- -200 || ' is ' || --- content_item.get_path(item_id,-200)); ---dbms_output.put_line('This is a path to an item from a non-related branch'); ---dbms_output.put_line('Path for ' || item_id || ' from unrelated branch: ' || --- folder_b_id || ' is ' || --- content_item.get_path(item_id,folder_b_id)); - - -dbms_output.put_line('-------------------------------------'); -dbms_output.put_line('MOVING/RENAMING CONTENT FOLDERS...'); -dbms_output.put_line('...all tests passed'); ---dbms_output.put_line('Moving me from under pa to under grandpa'); -content_item.move(sub_sub_folder_id, folder_id); ---dbms_output.put_line('Path for ' || item_id || ' is ' || ---content_item.get_path(item_id)); ---dbms_output.put_line('Moving grandpa to pa - this should''nt work'); -content_folder.move(folder_id, sub_folder_id); ---dbms_output.put_line('Path for ' || item_id || ' is ' || --- content_item.get_path(item_id)); ---dbms_output.put_line('Renaming puppy to kitty...'); -content_item.rename(item_id, 'kitty'); ---dbms_output.put_line('Renaming me to aunty...'); -content_folder.rename(sub_sub_folder_id, 'aunty'); ---dbms_output.put_line('Path for ' || item_id || ' is ' || --- content_item.get_path(item_id)); ---dbms_output.put_line('Renaming kitty to pa -- this should work'); ---content_item.rename(item_id, 'pa'); ---dbms_output.put_line('Path for ' || item_id || ' is ' || ---content_item.get_path(item_id)); - - -dbms_output.put_line('-------------------------------------'); -dbms_output.put_line('SYMLINKS...'); ---dbms_output.put_line('...all tests passed'); - -symlink_a_id := content_symlink.new('link_a',sub_sub_folder_id,sub_folder_id); -dbms_output.put_line('Create a link in pa to aunty: Symlink is ' || symlink_a_id); - -dbms_output.put_line('Is ' || symlink_a_id || ' a symlink?: ' || content_symlink.is_symlink(symlink_a_id)); - -dbms_output.put_line('Is ' || folder_id || ' a symlink?: ' || content_symlink.is_symlink(folder_id)); - -dbms_output.put_line('Path for symlink ' || symlink_a_id || - ' is ' || content_item.get_path(symlink_a_id)); - -dbms_output.put_line('Resolving symlink ' || symlink_a_id || - ' is ' || content_symlink.resolve(symlink_a_id)); - -dbms_output.put_line('Resolved path for symlink ' || - symlink_a_id || ' is ' || - content_item.get_path(content_symlink.resolve(symlink_a_id))); - -dbms_output.put_line('Path to item ' || item_id || ' from symlink ' || - symlink_a_id || ' is ' || - content_item.get_path(item_id, symlink_a_id)); -dbms_output.put_line('Path to item ' || item_id || ' from aunty ' || - sub_sub_folder_id || ' is ' || - content_item.get_path(item_id, sub_sub_folder_id)); -dbms_output.put_line('Path to pa ' || sub_folder_id || ' from symlink ' || - symlink_a_id || ' is ' || - content_item.get_path(sub_folder_id, symlink_a_id)); - - -dbms_output.put_line('Found item ' || item_id || ' at ' || - content_item.get_id('/grandpa/aunty/kitty')); -dbms_output.put_line('Found item ' || item_id || ' at ' || - content_item.get_id('/grandpa/pa/link_a/kitty')); -dbms_output.put_line('Found item ' || item_id || ' starting at aunty ' || - sub_sub_folder_id || ' at ' || - content_item.get_id('kitty',sub_sub_folder_id)); -dbms_output.put_line('Found item ' || item_id || ' starting at symlink ' || - symlink_a_id || ' at ' || - content_item.get_id('kitty',symlink_a_id)); -dbms_output.put_line('Found item ' || item_id || ' starting at pa ' || - sub_folder_id || ' at ' || - content_item.get_id('link_a/kitty',sub_folder_id)); - - - - - -dbms_output.put_line('--------------------------------'); - - ---dbms_output.put_line('Moving item ' || item_id || ' to grandma ' || --- folder_b_id); ---content_item.move(item_id,folder_b_id); ---dbms_output.put_line('Path for item ' || item_id || ' is ' || --- content_item.get_path(item_id)); - - ---dbms_output.put_line('Moving folder ' || folder_b_id || ' to aunty ' || --- sub_sub_folder_id); -----content_item.move(folder_b_id,sub_sub_folder_id); ---dbms_output.put_line('Path for item ' || item_id || ' is ' || --- content_item.get_path(item_id)); - ---dbms_output.put_line('--------------------------------'); - --- symlinks/revisions should be deleted automatically -content_item.delete(simple_item_id); -content_template.delete(item_template_id); -content_item.delete(item_id); -content_template.delete(type_template_id); -content_template.delete(def_type_template_id); -content_template.delete(dum_template_id); -content_folder.delete(sub_sub_folder_id); -content_folder.delete(sub_folder_id); -content_folder.delete(folder_id); -content_folder.delete(folder_b_id); - -end; -/ -show errors - - - Index: openacs-4/packages/acs-content-repository/sql/content-type.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-type.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-type.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,908 +0,0 @@ --- Data model to support content repository of the ArsDigita Community --- System - --- Copyright (C) 1999-2000 ArsDigita Corporation --- Authors: Michael Pih (pihman@arsdigita.com) --- Karl Goldstein (karlg@arsdigita.com) - --- $Id: content-type.sql,v 1.1 2001/03/13 22:59:26 ben 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 a trigger to make sure that there will never be more than --- one default template for a given content type and use context - -set serveroutput on - -create or replace trigger cr_type_template_map_tr -before insert on cr_type_template_map -for each row -begin - - if :new.is_default = 't' then - update - cr_type_template_map - set - is_default = 'f' - where - content_type = :new.content_type - and - use_context = :new.use_context - and - template_id ^= :new.template_id - and - is_default = 't'; - end if; - - return; -end cr_type_template_map_tr; -/ -show errors - -create or replace package body content_type is - -procedure create_type ( - content_type in acs_object_types.object_type%TYPE, - supertype in acs_object_types.object_type%TYPE - default 'content_revision', - pretty_name in acs_object_types.pretty_name%TYPE, - pretty_plural in acs_object_types.pretty_plural%TYPE, - table_name in acs_object_types.table_name%TYPE default null, - id_column in acs_object_types.id_column%TYPE default 'XXX', - name_method in acs_object_types.name_method%TYPE default null -) is - - table_exists integer; - v_supertype_table acs_object_types.table_name%TYPE; - -begin - - -- create the attribute table if not already created - - select decode(count(*),0,0,1) into table_exists from user_tables - where table_name = upper(create_type.table_name); - - if table_exists = 0 then - select table_name into v_supertype_table from acs_object_types - where object_type = create_type.supertype; - - execute immediate 'create table ' || table_name || ' (' || - id_column || ' integer primary key references ' || - v_supertype_table || ')'; - end if; - - acs_object_type.create_type ( - supertype => create_type.supertype, - object_type => create_type.content_type, - pretty_name => create_type.pretty_name, - pretty_plural => create_type.pretty_plural, - table_name => create_type.table_name, - id_column => create_type.id_column, - name_method => create_type.name_method - ); - - refresh_view(content_type); - -end create_type; - -procedure drop_type ( - content_type in acs_object_types.object_type%TYPE, - drop_children_p in char default 'f', - drop_table_p in char default 'f' -) is - - - cursor attribute_cur is - select - attribute_name - from - acs_attributes - where - object_type = drop_type.content_type; - - cursor child_type_cur is - select - object_type - from - acs_object_types - where - supertype = drop_type.content_type; - - table_exists integer; - v_table_name varchar2(50); - is_subclassed_p char; - -begin - - - -- first we'll rid ourselves of any dependent child types, if any , along with their - -- own dependent grandchild types - select - decode(count(*),0,'f','t') into is_subclassed_p - from - acs_object_types - where supertype = drop_type.content_type; - - -- this is weak and will probably break; - -- to remove grand child types, the process will probably - -- require some sort of querying for drop_type - -- methods within the children's packages to make - -- certain there are no additional unanticipated - -- restraints preventing a clean drop - - if drop_children_p = 't' and is_subclassed_p = 't' then - - for child_rec in child_type_cur loop - drop_type( - content_type => child_rec.object_type, - drop_children_p => 't' ); - end loop; - - end if; - - -- now drop all the attributes related to this type - for attr_row in attribute_cur loop - drop_attribute( - content_type => drop_type.content_type, - attribute_name => attr_row.attribute_name - ); - end loop; - - -- we'll remove the associated table if it exists - select - decode(count(*),0,0,1) into table_exists - from - user_tables u, acs_object_types objet - where - objet.object_type = drop_type.content_type and - u.table_name = upper(objet.table_name); - - if table_exists = 1 and drop_table_p = 't' then - select - table_name into v_table_name - from - acs_object_types - where - object_type = drop_type.content_type; - - execute immediate 'drop table ' || v_table_name ; - - end if; - - acs_object_type.drop_type( - object_type => drop_type.content_type - ); - -end drop_type; - - -function create_attribute ( - content_type in acs_attributes.object_type%TYPE, - attribute_name in acs_attributes.attribute_name%TYPE, - datatype in acs_attributes.datatype%TYPE, - pretty_name in acs_attributes.pretty_name%TYPE, - pretty_plural in acs_attributes.pretty_plural%TYPE default null, - sort_order in acs_attributes.sort_order%TYPE default null, - default_value in acs_attributes.default_value%TYPE default null, - column_spec in varchar2 default 'varchar2(4000)' -) return acs_attributes.attribute_id%TYPE is - - v_attr_id acs_attributes.attribute_id%TYPE; - v_table_name acs_object_types.table_name%TYPE; - v_column_exists integer; - -begin - - -- add the appropriate column to the table - begin - select upper(table_name) into v_table_name from acs_object_types - where object_type = create_attribute.content_type; - exception when no_data_found then - raise_application_error(-20000, 'Content type ''' || content_type || - ''' does not exist in content_type.create_attribute'); - end; - - select decode(count(*),0,0,1) into v_column_exists from user_tab_columns - where table_name = v_table_name - and column_name = upper(attribute_name); - - if v_column_exists = 0 then - execute immediate 'alter table ' || v_table_name || ' add ' || - attribute_name || ' ' || column_spec; - end if; - - v_attr_id := acs_attribute.create_attribute ( - object_type => create_attribute.content_type, - attribute_name => create_attribute.attribute_name, - datatype => create_attribute.datatype, - pretty_name => create_attribute.pretty_name, - pretty_plural => create_attribute.pretty_plural, - sort_order => create_attribute.sort_order, - default_value => create_attribute.default_value - ); - - refresh_view(content_type); - - return v_attr_id; - -end create_attribute; - - -procedure drop_attribute ( - content_type in acs_attributes.object_type%TYPE, - attribute_name in acs_attributes.attribute_name%TYPE, - drop_column in varchar2 default 'f' -) -is - v_attr_id acs_attributes.attribute_id%TYPE; - v_table acs_object_types.table_name%TYPE; -begin - - -- Get attribute information - begin - select - upper(t.table_name), a.attribute_id - into - v_table, v_attr_id - from - acs_object_types t, acs_attributes a - where - t.object_type = drop_attribute.content_type - and - a.object_type = drop_attribute.content_type - and - a.attribute_name = drop_attribute.attribute_name; - exception when no_data_found then - raise_application_error(-20000, 'Attribute ' || content_type || ':' || - attribute_name || ' does not exist in content_type.drop_attribute'); - end; - - -- Drop the attribute - acs_attribute.drop_attribute(content_type, attribute_name); - - -- Drop the column if neccessary - if drop_column = 't' then - begin - execute immediate 'alter table ' || v_table || ' drop column ' || - attribute_name; - exception when others then - raise_application_error(-20000, 'Unable to drop column ' || - v_table || '.' || attribute_name || ' in content_type.drop_attribute'); - end; - end if; - - refresh_view(content_type); - -end drop_attribute; - -procedure register_template ( - content_type in cr_type_template_map.content_type%TYPE, - template_id in cr_templates.template_id%TYPE, - use_context in cr_type_template_map.use_context%TYPE, - is_default in cr_type_template_map.is_default%TYPE default 'f' -) is - v_template_registered integer; -begin - select - count(*) into v_template_registered - from - cr_type_template_map - where - content_type = register_template.content_type - and - use_context = register_template.use_context - and - template_id = register_template.template_id; - - -- register the template - if v_template_registered = 0 then - insert into cr_type_template_map ( - template_id, content_type, use_context, is_default - ) values ( - template_id, content_type, use_context, is_default - ); - - -- update the registration status of the template - else - - -- unset the default template before setting this one as the default - if register_template.is_default = 't' then - update cr_type_template_map - set is_default = 'f' - where content_type = register_template.content_type - and use_context = register_template.use_context; - end if; - - update cr_type_template_map - set is_default = register_template.is_default - where template_id = register_template.template_id - and content_type = register_template.content_type - and use_context = register_template.use_context; - - end if; -end register_template; - - -procedure set_default_template ( - content_type in cr_type_template_map.content_type%TYPE, - template_id in cr_templates.template_id%TYPE, - use_context in cr_type_template_map.use_context%TYPE -) is - -begin - - update cr_type_template_map - set is_default = 't' - where template_id = set_default_template.template_id - and content_type = set_default_template.content_type - and use_context = set_default_template.use_context; - - -- make sure there is only one default template for - -- any given content_type/use_context pair - update cr_type_template_map - set is_default = 'f' - where template_id ^= set_default_template.template_id - and content_type = set_default_template.content_type - and use_context = set_default_template.use_context - and is_default = 't'; - -end set_default_template; - -function get_template ( - content_type in cr_type_template_map.content_type%TYPE, - use_context in cr_type_template_map.use_context%TYPE -) return cr_templates.template_id%TYPE -is - v_template_id cr_templates.template_id%TYPE; -begin - select - template_id - into - v_template_id - from - cr_type_template_map - where - content_type = get_template.content_type - and - use_context = get_template.use_context - and - is_default = 't'; - - return v_template_id; - -exception - when NO_DATA_FOUND then - return null; -end get_template; - -procedure unregister_template ( - content_type in cr_type_template_map.content_type%TYPE default null, - template_id in cr_templates.template_id%TYPE, - use_context in cr_type_template_map.use_context%TYPE default null -) is -begin - - if unregister_template.use_context is null and - unregister_template.content_type is null then - - delete from cr_type_template_map - where template_id = unregister_template.template_id; - - elsif unregister_template.use_context is null then - - delete from cr_type_template_map - where template_id = unregister_template.template_id - and content_type = unregister_template.content_type; - - elsif unregister_template.content_type is null then - - delete from cr_type_template_map - where template_id = unregister_template.template_id - and use_context = unregister_template.use_context; - - else - - delete from cr_type_template_map - where template_id = unregister_template.template_id - and content_type = unregister_template.content_type - and use_context = unregister_template.use_context; - - end if; - -end unregister_template; - --- Helper function for refresh_trigger (below) to generate the --- insert statement for a particular content type; - -function trigger_insert_statement ( - content_type in acs_object_types.object_type%TYPE -) return varchar2 is - - v_table_name acs_object_types.table_name%TYPE; - v_id_column acs_object_types.id_column%TYPE; - - cursor attr_cur is - select - attribute_name - from - acs_attributes - where - object_type = trigger_insert_statement.content_type; - - cols varchar2(2000) := ''; - vals varchar2(2000) := ''; - -begin - - select table_name, id_column into v_table_name, v_id_column - from acs_object_types where - object_type = trigger_insert_statement.content_type; - - for attr_rec in attr_cur loop - cols := cols || ', ' || attr_rec.attribute_name; - vals := vals || ', :new.' || attr_rec.attribute_name; - end loop; - - return 'insert into ' || v_table_name || - ' ( ' || v_id_column || cols || ' ) values ( new_revision_id' || - vals || ')'; - -end trigger_insert_statement; - --- Create or replace a trigger on insert for simplifying addition of --- revisions for any content type - -procedure refresh_trigger ( - content_type in acs_object_types.object_type%TYPE -) is - - tr_text varchar2(10000) := ''; - v_table_name acs_object_types.table_name%TYPE; - - cursor type_cur is - select - object_type - from - acs_object_types - where - object_type ^= 'acs_object' - and - object_type ^= 'content_revision' - connect by - prior supertype = object_type - start with - object_type = refresh_trigger.content_type - order by - level desc; - -begin - - -- get the table name for the content type (determines view name) - - select table_name into v_table_name - from acs_object_types where object_type = refresh_trigger.content_type; - - -- start building trigger code - - tr_text := ' - -create or replace trigger ' || v_table_name || 't - instead of insert on ' || v_table_name || 'i - for each row -declare - new_revision_id integer; -begin - - if :new.item_id is null then - raise_application_error(-20000, ''item_id is required when inserting into ' || - v_table_name || 'i ''); - end if; - - if :new.text is not null then - - new_revision_id := content_revision.new( - revision_id => :new.revision_id, - title => :new.title, - description => :new.description, - mime_type => :new.mime_type, - nls_language => :new.nls_language, - item_id => content_symlink.resolve(:new.item_id), - creation_ip => :new.creation_ip, - creation_user => :new.creation_user, - text => :new.text - ); - - else - - new_revision_id := content_revision.new( - revision_id => :new.revision_id, - title => :new.title, - description => :new.description, - mime_type => :new.mime_type, - nls_language => :new.nls_language, - item_id => content_symlink.resolve(:new.item_id), - creation_ip => :new.creation_ip, - creation_user => :new.creation_user, - data => :new.data - ); - - end if;'; - - -- add an insert statement for each subtype in the hierarchy for this type - - for type_rec in type_cur loop - tr_text := tr_text || ' -' || trigger_insert_statement(type_rec.object_type) || '; -'; - - end loop; - - -- end building the trigger code - tr_text := tr_text || ' -end ' || v_table_name || 't;'; - - -- (Re)create the trigger - execute immediate tr_text; - -end refresh_trigger; - --- Create or replace a view joining all attribute tables - -procedure refresh_view ( - content_type in cr_type_template_map.content_type%TYPE -) is - - -- exclude the BLOB column because it will make it impossible - -- to do a select * - - cursor join_cur is - select - table_name, id_column, level - from - acs_object_types - where - object_type <> 'acs_object' - and - object_type <> 'content_revision' - start with - object_type = refresh_view.content_type - connect by - object_type = prior supertype; - - cols varchar2(1000); - tabs varchar2(1000); - joins varchar2(1000) := ''; - - v_table_name varchar2(40); - -begin - - for join_rec in join_cur loop - - cols := cols || ', ' || join_rec.table_name || '.*'; - tabs := tabs || ', ' || join_rec.table_name; - joins := joins || ' and acs_objects.object_id = ' || - join_rec.table_name || '.' || join_rec.id_column; - - end loop; - - select table_name into v_table_name from acs_object_types - where object_type = content_type; - - -- create the input view (includes content columns) - - execute immediate 'create or replace view ' || v_table_name || - 'i as select acs_objects.*, cr.revision_id, cr.title, cr.item_id, - cr.content as data, cr_text.text, - cr.description, cr.publish_date, cr.mime_type, cr.nls_language' || - cols || - ' from acs_objects, cr_revisions cr, cr_text' || tabs || ' where - acs_objects.object_id = cr.revision_id ' || joins; - - -- create the output view (excludes content columns to enable SELECT *) - - execute immediate 'create or replace view ' || v_table_name || - 'x as select acs_objects.*, cr.revision_id, cr.title, cr.item_id, - cr.description, cr.publish_date, cr.mime_type, cr.nls_language, - i.name, i.parent_id' || - cols || - ' from acs_objects, cr_revisions cr, cr_items i, cr_text' || tabs || - ' where acs_objects.object_id = cr.revision_id - and cr.item_id = i.item_id' || joins; - - refresh_trigger(content_type); - -exception - when others then - dbms_output.put_line('Error creating attribute view or trigger for ' || - content_type); -end refresh_view; - -procedure register_child_type ( - parent_type in cr_type_children.parent_type%TYPE, - child_type in cr_type_children.child_type%TYPE, - relation_tag in cr_type_children.relation_tag%TYPE default 'generic', - min_n in integer default 0, - max_n in integer default null -) is - - v_exists integer; - -begin - - select decode(count(*),0,0,1) into v_exists - from cr_type_children - where parent_type = register_child_type.parent_type - and child_type = register_child_type.child_type; - - if v_exists = 0 then - - insert into cr_type_children ( - parent_type, child_type, relation_tag, min_n, max_n - ) values ( - parent_type, child_type, relation_tag, min_n, max_n - ); - - else - - update cr_type_children set - min_n = register_child_type.min_n, - max_n = register_child_type.max_n - where - parent_type = register_child_type.parent_type - and - child_type = register_child_type.child_type - and - relation_tag = register_child_type.relation_tag; - - end if; - -end register_child_type; - -procedure unregister_child_type ( - parent_type in cr_type_children.parent_type%TYPE, - child_type in cr_type_children.child_type%TYPE, - relation_tag in cr_type_children.relation_tag%TYPE default null -) is - -begin - - delete from - cr_type_children - where - parent_type = unregister_child_type.parent_type - and - child_type = unregister_child_type.child_type - and - relation_tag = unregister_child_type.relation_tag; - -end unregister_child_type; - -procedure register_relation_type ( - content_type in cr_type_relations.content_type%TYPE, - target_type in cr_type_relations.target_type%TYPE, - relation_tag in cr_type_relations.relation_tag%TYPE default 'generic', - min_n in integer default 0, - max_n in integer default null -) is - v_exists integer; -begin - - -- check if the relation type exists - select - decode(count(*),0,0,1) into v_exists - from - cr_type_relations - where - content_type = register_relation_type.content_type - and - target_type = register_relation_type.target_type - and - relation_tag = register_relation_type.relation_tag; - - -- if the relation type does not exist, insert a row into cr_type_relations - if v_exists = 0 then - insert into cr_type_relations ( - content_type, target_type, relation_tag, min_n, max_n - ) values ( - content_type, target_type, relation_tag, min_n, max_n - ); - - -- otherwise, update the row in cr_type_relations - else - update cr_type_relations set - min_n = register_relation_type.min_n, - max_n = register_relation_type.max_n - where - content_type = register_relation_type.content_type - and - target_type = register_relation_type.target_type - and - relation_tag = register_relation_type.relation_tag; - - end if; -end register_relation_type; - -procedure unregister_relation_type ( - content_type in cr_type_relations.content_type%TYPE, - target_type in cr_type_relations.target_type%TYPE, - relation_tag in cr_type_relations.relation_tag%TYPE default null -) is - -begin - - delete from - cr_type_relations - where - content_type = unregister_relation_type.content_type - and - target_type = unregister_relation_type.target_type - and - relation_tag = unregister_relation_type.relation_tag; - -end unregister_relation_type; - -procedure register_mime_type ( - content_type in cr_content_mime_type_map.content_type%TYPE, - mime_type in cr_content_mime_type_map.mime_type%TYPE -) is - v_valid_registration integer; -begin - - -- check if this type is already registered - select - count(*) into v_valid_registration - from - cr_mime_types - where - not exists ( select 1 - from - cr_content_mime_type_map - where - mime_type = register_mime_type.mime_type - and - content_type = register_mime_type.content_type ) - and - mime_type = register_mime_type.mime_type; - - if v_valid_registration = 1 then - - insert into cr_content_mime_type_map ( - content_type, mime_type - ) values ( - register_mime_type.content_type, register_mime_type.mime_type - ); - - end if; - -end register_mime_type; - - -procedure unregister_mime_type ( - content_type in cr_content_mime_type_map.content_type%TYPE, - mime_type in cr_content_mime_type_map.mime_type%TYPE -) is -begin - - delete from cr_content_mime_type_map - where content_type = unregister_mime_type.content_type - and mime_type = unregister_mime_type.mime_type; - -end unregister_mime_type; - -function is_content_type ( - object_type in acs_object_types.object_type%TYPE -) return char is - - v_is_content_type char(1) := 'f'; - -begin - - if object_type = 'content_revision' then - - v_is_content_type := 't'; - - else - - select decode(count(*),0,'f','t') into v_is_content_type - from acs_object_type_supertype_map - where object_type = is_content_type.object_type - and ancestor_type = 'content_revision'; - - end if; - - return v_is_content_type; - -end is_content_type; - - - -procedure rotate_template ( - template_id in cr_templates.template_id%TYPE, - v_content_type in cr_items.content_type%TYPE, - use_context in cr_type_template_map.use_context%TYPE -) is - v_template_id cr_templates.template_id%TYPE; - - -- items that have an associated default template but not at the item level - cursor c_items_cursor is - select - item_id - from - cr_items i, cr_type_template_map m - where - i.content_type = rotate_template.v_content_type - and - m.use_context = rotate_template.use_context - and - i.content_type = m.content_type - and - not exists ( select 1 - from - cr_item_template_map - where - item_id = i.item_id - and - use_context = rotate_template.use_context ); -begin - - -- get the default template - select - template_id into v_template_id - from - cr_type_template_map - where - content_type = rotate_template.v_content_type - and - use_context = rotate_template.use_context - and - is_default = 't'; - - if v_template_id is not null then - - -- register an item-template to all items without an item-template - for v_items_val in c_items_cursor loop - - content_item.register_template ( - item_id => v_items_val.item_id, - template_id => v_template_id, - use_context => rotate_template.use_context - ); - end loop; - end if; - - -- register the new template as the default template of the content type - if v_template_id ^= rotate_template.template_id then - content_type.register_template( - content_type => rotate_template.v_content_type, - template_id => rotate_template.template_id, - use_context => rotate_template.use_context, - is_default => 't' - ); - end if; - -end rotate_template; - - -end content_type; -/ -show errors - --- Refresh the attribute views - -prompt *** Refreshing content type attribute views... - -begin - - for type_rec in (select object_type from acs_object_types - connect by supertype = prior object_type - start with object_type = 'content_revision') loop - content_type.refresh_view(type_rec.object_type); - end loop; - -end; -/ - - Index: openacs-4/packages/acs-content-repository/sql/content-update.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-update.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-update.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,227 +0,0 @@ --- Data model to support content repository of the ArsDigita Community --- System. This file contains DDL patches to the basic data model --- that were incorporated after the code freeze. It makes it easier for --- existing users to update their data models. - --- Copyright (C) 1999-2000 ArsDigita Corporation --- Authors: Karl Goldstein (karlg@arsdigita.com) - --- $Id: content-update.sql,v 1.1 2001/03/13 22:59:26 ben 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 - -set serveroutput on - -begin - -- altering the constraint on cr_type_template_map - dbms_output.put_line('Altering constraint on cr_type_template_map...'); - execute immediate 'alter table cr_type_template_map drop constraint cr_type_template_map_pk'; - execute immediate 'alter table cr_type_template_map add constraint cr_type_template_map_pk primary key (content_type, template_id, use_context)'; - execute immediate 'analyze table cr_type_template_map compute statistics for all indexes'; -end; -/ -show errors - -begin - - -- Set the workflow permission as child of admin - update acs_privilege_hierarchy - set privilege = 'cm_admin' - where - privilege = 'cm_write' - and - child_privilege = 'cm_item_workflow'; - - if not table_exists('cr_doc_filter') then - - dbms_output.put_line('Creating CR_DOC_FILTER table for converting - documents to HTML'); - - execute immediate 'create global temporary table cr_doc_filter ( - revision_id integer primary key, - content BLOB - ) on commit delete rows'; - - execute immediate 'create index cr_doc_filter_index - on cr_doc_filter ( content ) indextype is ctxsys.context - parameters (''FILTER content_filter_pref'' )'; - - end if; - - if not table_exists('cr_content_text') then - - dbms_output.put_line('Creating CR_CONTENT_TEXT table'); - - execute immediate 'create global temporary table cr_content_text ( - revision_id integer primary key, - content CLOB - ) on commit delete rows'; - - end if; - - - if not column_exists('cr_folders', 'has_child_folders') then - - dbms_output.put_line('Adding HAS_CHILD_FOLDERS column to CR_FOLDERS' || - ' and updating the column based on selection criteria.'); - - execute immediate 'create or replace view cr_resolved_items as - select - i.parent_id, i.item_id, i.name, - decode(s.target_id, NULL, ''f'', ''t'') is_symlink, - nvl(s.target_id, i.item_id) resolved_id, s.label - from - cr_items i, cr_symlinks s - where - i.item_id = s.symlink_id (+)'; - - execute immediate 'alter table cr_folders add - has_child_folders char(1) - default ''f'' - constraint cr_folder_child_chk - check (has_child_folders in (''t'',''f''))'; - - execute immediate 'update cr_folders f set has_child_folders = - nvl((select ''t'' from dual where exists - (select 1 from cr_folders f_child, cr_resolved_items r_child - where r_child.parent_id = f.folder_id - and f_child.folder_id = r_child.resolved_id)), ''f'')'; - end if; - - - - if not column_exists('cr_keywords', 'parent_id') then - - dbms_output.put_line('Adding PARENT_ID column to CR_KEYWORDS' || - ' and updating the parent id from the context id'); - - execute immediate 'alter table cr_keywords add - parent_id integer - constraint cr_keywords_hier - references cr_keywords'; - - execute immediate 'update cr_keywords set parent_id = ( - select context_id from acs_objects - where object_id = keyword_id)'; - - end if; - - if not table_exists('cr_text') then - - dbms_output.put_line('Creating CR_TEXT table ' || - 'for incoming text submissions...'); - - execute immediate 'create table cr_text ( text varchar2(4000) )'; - - -- For some reason a simple insert statement throws an error but this works - execute immediate 'insert into cr_text values (NULL)'; - - end if; - - if not column_exists('cr_items', 'publish_status') then - - dbms_output.put_line('Adding PUBLISH_STATUS column to CR_ITEMS ' || - 'for tracking deployment status...'); - - execute immediate 'alter table cr_items add - publish_status varchar2(40) - constraint cr_items_pub_status_chk - check (publish_status in - (''production'', ''ready'', ''live'', ''expired''))'; - - execute immediate 'update cr_items set publish_status = ''live'' - where live_revision is not null'; - - execute immediate 'alter table cr_item_publish_audit add - old_status varchar2(40)'; - execute immediate 'alter table cr_item_publish_audit add - new_status varchar2(40)'; - - end if; - - if not column_exists('cr_items', 'latest_revision') then - - dbms_output.put_line('Adding LATEST_REVISION column to CR_ITEMS ' || - 'for tracking revision status...'); - - execute immediate 'alter table cr_items add - latest_revision integer - constraint cr_items_latest_fk - references cr_revisions'; - - execute immediate 'update cr_items - set latest_revision = - content_item.get_latest_revision(item_id)'; - - end if; - - if not table_exists('cr_release_periods') then - - dbms_output.put_line('Creating CR_RELEASE_PERIODS table ' || - 'for scheduled publishing...'); - - execute immediate ' - create table cr_release_periods ( - item_id integer - constraint cr_release_periods_fk - references cr_items - constraint cr_release_periods_pk - primary key, - start_when date default sysdate, - end_when date default sysdate + (365 * 20) - )'; - - end if; - - if not table_exists('cr_scheduled_release_log') then - - dbms_output.put_line('Creating CR_SCHEDULED_RELEASE_LOG table ' || - 'for auditing of scheduled publishing...'); - - execute immediate ' - create table cr_scheduled_release_log ( - exec_date date default sysdate not null, - items_released integer not null, - items_expired integer not null, - err_num integer, - err_msg varchar2(500) - )'; - - end if; - - if not table_exists('cr_scheduled_release_job') then - - dbms_output.put_line('Creating CR_SCHEDULED_RELEASE_JOB table ' || - 'for tracking database job for scheduled publishing...'); - - execute immediate ' - create table cr_scheduled_release_job ( - job_id integer, - last_exec date - )'; - - execute immediate ' - insert into cr_scheduled_release_job values (NULL, sysdate)'; - - end if; - -end; -/ -show errors - -create or replace trigger cr_text_tr -before insert on cr_text -for each row -begin - - raise_application_error(-20000, - 'Inserts are not allowed into cr_text.' - ); -end; -/ -show errors - -@@ content-schedule.sql - Index: openacs-4/packages/acs-content-repository/sql/content-util.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-util.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-util.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,128 +0,0 @@ --- Data model to support content repository of the ArsDigita --- Publishing System - --- Copyright (C) 1999-2000 ArsDigita Corporation --- Author: Karl Goldstein (karlg@arsdigita.com) - --- $Id: content-util.sql,v 1.1 2001/03/13 22:59:26 ben 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 or replace function table_exists ( - table_name varchar2 -) return boolean is - - v_count integer; - v_exists boolean; - -begin - - select decode(count(*),0,0,1) into v_count - from user_tables where table_name = upper(table_exists.table_name); - - if v_count = 1 then - v_exists := true; - else - v_exists := false; - end if; - - return v_exists; - -end table_exists; -/ -show errors - -create or replace function column_exists ( - table_name varchar2, - column_name varchar2 -) return boolean is - - v_count integer; - v_exists boolean; - -begin - - select decode(count(*),0,0,1) into v_count from user_tab_columns - where table_name = upper(column_exists.table_name) - and column_name = upper(column_exists.column_name); - - if v_count = 1 then - v_exists := true; - else - v_exists := false; - end if; - - return v_exists; - -end column_exists; -/ -show errors - -create or replace procedure clob_to_blob( - clob_loc clob, blob_loc blob) -as language - java -name - 'com.arsdigita.content.Util.clobToBlob( - oracle.sql.CLOB, oracle.sql.BLOB - )'; -/ -show errors - -create or replace procedure blob_to_clob( - blob_loc blob, clob_loc clob) -as language - java -name - 'com.arsdigita.content.Util.blobToClob( - oracle.sql.BLOB, oracle.sql.CLOB - )'; -/ -show errors - - -create or replace procedure string_to_blob( - s varchar2, blob_loc blob) -as language - java -name - 'com.arsdigita.content.Util.stringToBlob( - java.lang.String, oracle.sql.BLOB - )'; -/ -show errors - -create or replace procedure string_to_blob_size( - s varchar2, blob_loc blob, blob_size number) -as language - java -name - 'com.arsdigita.content.Util.stringToBlob( - java.lang.String, oracle.sql.BLOB, int - )'; -/ -show errors - -create or replace function blob_to_string( - blob_loc blob) return varchar2 -as language - java -name - 'com.arsdigita.content.Util.blobToString( - oracle.sql.BLOB - ) return java.lang.String'; -/ -show errors - -create or replace procedure blob_to_file( -s varchar2, blob_loc blob) -as language - java -name - 'com.arsdigita.content.Util.blobToFile( - java.lang.String, oracle.sql.BLOB - )'; -/ -show errors Index: openacs-4/packages/acs-content-repository/sql/content-xml.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/content-xml.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/content-xml.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,28 +0,0 @@ --- Data model to support XML exchange with thecontent repository of --- the ArsDigita Community System - --- Copyright (C) 1999-2000 ArsDigita Corporation --- Author: Karl Goldstein (karlg@arsdigita.com) - --- $Id: content-xml.sql,v 1.1 2001/03/13 22:59:26 ben 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 - --- A sequence for uniquely identifying uploaded XML documents until --- they are inserted into the repository - -create sequence cr_xml_doc_seq; - -create global temporary table cr_xml_docs ( - doc_id integer primary key, - doc CLOB -) on commit delete rows; - -comment on table cr_xml_docs is ' - A temporary table for holding uploaded XML documents for the - duration of a transaction, until they can be inserted into - the content repository. -'; - Index: openacs-4/packages/acs-content-repository/sql/datatype-upgrade.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/datatype-upgrade.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/datatype-upgrade.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,37 +0,0 @@ - - - -update acs_attributes - set datatype = 'keyword' - where attribute_name = 'name' - and object_type = 'content_item'; - -update acs_attributes - set datatype = 'keyword' - where attribute_name = 'locale' - and object_type = 'content_item'; - -update acs_attributes - set datatype = 'text' - where attribute_name = 'title' - and object_type = 'content_revision'; - -update acs_attributes - set datatype = 'text' - where attribute_name = 'description' - and object_type = 'content_revision'; - -update acs_attributes - set datatype = 'text' - where attribute_name = 'mime_type' - and object_type = 'content_revision'; - -update acs_attributes - set datatype = 'integer' - where attribute_name = 'width' - and object_type = 'image'; - -update acs_attributes - set datatype = 'integer' - where attribute_name = 'height' - and object_type = 'image'; Index: openacs-4/packages/acs-content-repository/sql/doc-package.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/doc-package.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/doc-package.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,132 +0,0 @@ ----------------------------------------- --- Return function headers for packages ---------------------------------------- - -create or replace package doc -is - - function get_proc_header ( - proc_name in varchar2, - package_name in varchar2 - ) return varchar2; - - function get_package_header ( - package_name in varchar2 - ) return varchar2; - -end doc; -/ -show errors - -create or replace package body doc -is - - function get_proc_header ( - proc_name in varchar2, - package_name in varchar2 - ) return varchar2 - is - v_line integer; - v_result varchar2(4000); - v_text varchar2(4000); - v_started varchar2(1); - v_newline varchar2(10) := ' -'; - - cursor v_package_cur is - select line, text from user_source - where lower(name) = lower(package_name) - and type = 'PACKAGE' - order by line; - - begin - - v_result := ''; - v_started := 'f'; - - open v_package_cur; - loop - fetch v_package_cur into v_line, v_text; - exit when v_package_cur%NOTFOUND; - - -- Look for the function header - if v_started = 'f' then - if lower(v_text) like '%function%' || lower(proc_name) || '%' then - v_started := 't'; - elsif lower(v_text) like '%procedure%' || lower(proc_name) || '%' then - v_started := 't'; - end if; - end if; - - -- Process the header - if v_started = 't' then - v_result := v_result || v_text; - if v_text like '%;%' then - close v_package_cur; - return v_result; - end if; - end if; - end loop; - - -- Return unfinished result - return v_result; - end get_proc_header; - - function get_package_header ( - package_name in varchar2 - ) return varchar2 - is - v_line integer; - v_result varchar2(4000); - v_text varchar2(4000); - v_started varchar2(1); - v_newline varchar2(10) := ' -'; - - cursor v_package_cur is - select line, text from user_source - where lower(name) = lower(package_name) - and type = 'PACKAGE' - order by line; - - begin - - v_result := ''; - v_started := 'f'; - - open v_package_cur; - loop - fetch v_package_cur into v_line, v_text; - exit when v_package_cur%NOTFOUND; - - -- Look for the function header - if v_started = 'f' then - if v_text like '--%' then - v_started := 't'; - end if; - end if; - - -- Process the header - if v_started = 't' then - - if v_text not like '--%' then - close v_package_cur; - return v_result; - end if; - - v_result := v_result || v_text; - end if; - end loop; - - -- Return unfinished result - return v_result; - - end get_package_header; - -end doc; -/ -show errors - - - - Index: openacs-4/packages/acs-content-repository/sql/packages-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/Attic/packages-create.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/packages-create.sql 13 Mar 2001 22:59:26 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,1765 +0,0 @@ --- Ensure that the data model is up-to-date before compiling packages - -@@ content-util.sql -@@ content-update.sql - -create or replace package content_type AUTHID CURRENT_USER as ---/** This package is used to manipulate content types and attributes --- ---*/ - -procedure create_type ( - --/** Create a new content type. Automatically create the attribute table - -- for the type if the table does not already exist. - -- @author Karl Goldstein - -- @param content_type The name of the new type - -- @param supertype The supertype, defaults to content_revision - -- @param pretty_name Pretty name for the type, singular - -- @param pretty_plural Pretty name for the type, plural - -- @param table_name The name for the attribute table, defaults to - -- the name of the supertype - -- @param id_column The primary key for the table, defaults to 'XXX' - -- @param name_method As in acs_object_type.create_type - -- @see {acs_object_type.create_type} - --*/ - content_type in acs_object_types.object_type%TYPE, - supertype in acs_object_types.object_type%TYPE - default 'content_revision', - pretty_name in acs_object_types.pretty_name%TYPE, - pretty_plural in acs_object_types.pretty_plural%TYPE, - table_name in acs_object_types.table_name%TYPE, - id_column in acs_object_types.id_column%TYPE default 'XXX', - name_method in acs_object_types.name_method%TYPE default null -); - -procedure drop_type ( - --/** First drops all attributes related to a specific type, then drops type - -- the given type. - -- @author Simon Huynh - -- @param content_type The content type to be dropped - -- @param drop_children_p If 't', then the sub-types - -- of the given content type and their associated tables - -- are also dropped. - --*/ - content_type in acs_object_types.object_type%TYPE, - drop_children_p in char default 'f', - drop_table_p in char default 'f' - -); - - -function create_attribute ( - --/** Create a new attribute for the specified type. Automatically create - -- the column for the attribute if the column does not already exist. - -- @author Karl Goldstein - -- @param content_type The name of the type to alter - -- @param attribute_name The name of the attribute to create - -- @param pretty_name Pretty name for the new attribute, singular - -- @param pretty_plural Pretty name for the new attribute, plural - -- @param default_value The default value for the attribute, defaults to null - -- @return The id of the newly created attribute - -- @see {acs_object_type.create_attribute}, {content_type.create_type} - --*/ - content_type in acs_attributes.object_type%TYPE, - attribute_name in acs_attributes.attribute_name%TYPE, - datatype in acs_attributes.datatype%TYPE, - pretty_name in acs_attributes.pretty_name%TYPE, - pretty_plural in acs_attributes.pretty_plural%TYPE default null, - sort_order in acs_attributes.sort_order%TYPE default null, - default_value in acs_attributes.default_value%TYPE default null, - column_spec in varchar2 default 'varchar2(4000)' -) return acs_attributes.attribute_id%TYPE; - -procedure drop_attribute ( - --/** Drop an existing attribute. If you are using CMS, make sure to - -- call cm_form_widget.unregister_attribute_widget before calling - -- this function. - -- @author Karl Goldstein - -- @param content_type The name of the type to alter - -- @param attribute_name The name of the attribute to drop - -- @param drop_column If 't', will also alter the table and remove - -- the column where the attribute is stored. The default is 'f' - -- (leaves the table untouched). - -- @see {acs_object.drop_attribute}, {content_type.create_attribute}, - -- {cm_form_widget.unregister_attribute_widget} - --*/ - content_type in acs_attributes.object_type%TYPE, - attribute_name in acs_attributes.attribute_name%TYPE, - drop_column in varchar2 default 'f' -); - -procedure register_template ( - --/** Register a template for the content type. This template may be used - -- to render all items of that type. - -- @author Karl Goldstein - -- @param content_type The type for which the template is to be registered - -- @param template_id The ID of the template to register - -- @param use_context The context in which the template is appropriate, such - -- as 'admin' or 'public' - -- @param is_default If 't', this template becomes the default template for - -- the type, default is 'f'. - -- @see {content_item.register_template}, {content_item.unregister_template}, - -- {content_item.get_template}, {content_type.unregister_template}, - -- {content_type.set_default_template}, {content_type.get_template} - --*/ - content_type in cr_type_template_map.content_type%TYPE, - template_id in cr_templates.template_id%TYPE, - use_context in cr_type_template_map.use_context%TYPE, - is_default in cr_type_template_map.is_default%TYPE default 'f' -); - -procedure set_default_template ( - --/** Make the registered template a default template. The default template - -- will be used to render all items of the type for which no individual - -- template is registered. - -- @author Karl Goldstein - -- @param content_type The type for which the template is to be made default - -- @param template_id The ID of the template to make default - -- @param use_context The context in which the template is appropriate, such - -- as 'admin' or 'public' - -- @see {content_item.register_template}, {content_item.unregister_template}, - -- {content_item.get_template}, {content_type.unregister_template}, - -- {content_type.register_template}, {content_type.get_template} - --*/ - content_type in cr_type_template_map.content_type%TYPE, - template_id in cr_templates.template_id%TYPE, - use_context in cr_type_template_map.use_context%TYPE -); - -function get_template ( - --/** Retrieve the appropriate template for rendering items of the specified type. - -- @author Karl Goldstein - -- @param content_type The type for which the template is to be retrieved - -- @param use_context The context in which the template is appropriate, such - -- as 'admin' or 'public' - -- @return The ID of the template to use - -- @see {content_item.register_template}, {content_item.unregister_template}, - -- {content_item.get_template}, {content_type.unregister_template}, - -- {content_type.register_template}, {content_type.set_default_template} - --*/ - content_type in cr_type_template_map.content_type%TYPE, - use_context in cr_type_template_map.use_context%TYPE -) return cr_templates.template_id%TYPE; - -procedure unregister_template ( - --/** Unregister a template. If the unregistered template was the default template, - -- the content_type can no longer be rendered in the use_context, - -- @author Karl Goldstein - -- @param content_type The type for which the template is to be unregistered - -- @param template_id The ID of the template to unregister - -- @param use_context The context in which the template is to be unregistered - -- @see {content_item.register_template}, {content_item.unregister_template}, - -- {content_item.get_template}, {content_type.set_default_template}, - -- {content_type.register_template}, {content_type.get_template} - --*/ - content_type in cr_type_template_map.content_type%TYPE default null, - template_id in cr_templates.template_id%TYPE, - use_context in cr_type_template_map.use_context%TYPE default null -); - -procedure refresh_view ( - --/** Create a view for the type which joins all attributes of the type, - -- including the inherited attributes. The view is named - -- "