Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql,v diff -u -r1.39 -r1.40 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 10 Feb 2003 17:38:38 -0000 1.39 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 17 May 2003 09:43:09 -0000 1.40 @@ -37,10 +37,10 @@ - -------------------------------------------------------------- -- MIME TYPES -------------------------------------------------------------- +-- Mime data for the following table is in mime-type-data.sql create table cr_mime_types ( label varchar(200), @@ -60,36 +60,27 @@ a file extension to the file after its MIME type is specified. '; --- Common mime types (administered from admin pages) +-- Currently file_extension is the pk although it seems likely someone +-- will want to support multiple mime types with the same extension. +-- Would need UI work however -insert into cr_mime_types(label, mime_type, file_extension) values ('Enhanced text', 'text/enhanced', 'etxt'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Plain text', 'text/plain', 'txt'); -insert into cr_mime_types(label, mime_type, file_extension) values ('HTML text', 'text/html', 'html'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Fixed-width text', 'text/fixed-width', 'ftxt'); +create table cr_extension_mime_type_map ( + extension varchar(200) + constraint cr_mime_type_extension_map_pk + primary key, + mime_type varchar(200) + constraint cr_mime_ext_map_mime_type_ref + references cr_mime_types +); +create index cr_extension_mime_type_map_idx on cr_extension_mime_type_map(mime_type); -insert into cr_mime_types(label, mime_type, file_extension) values ('Rich Text Format (RTF)', 'text/richtext', 'rtf'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Binary', 'application/octet-stream', 'bin'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Microsoft Word', 'application/msword', 'doc'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Microsoft Excel', 'application/msexcel', 'xls'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Microsoft PowerPoint', 'application/powerpoint', 'ppt'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Microsoft Project', 'application/msproject', 'mpp'); -insert into cr_mime_types(label, mime_type, file_extension) values ('PostScript', 'application/postscript', 'ps'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Adobe Illustrator', 'application/x-illustrator', 'ai'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Adobe PageMaker', 'application/x-pagemaker', 'p65'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Filemaker Pro', 'application/filemaker', 'fm'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Image Pict', 'image/x-pict', 'pic'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Photoshop', 'application/x-photoshop', 'psd'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Acrobat', 'application/pdf', 'pdf'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Video Quicktime', 'video/quicktime', 'mov'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Video MPEG', 'video/mpeg', 'mpg'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Audio AIFF', 'audio/aiff', 'aif'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Audio Basic', 'audio/basic', 'au'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Audio Voice', 'audio/voice', 'voc'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Audio Wave', 'audio/wave', 'wav'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Archive Zip', 'application/zip', 'zip'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Archive Tar', 'application/z-tar', 'tar'); -insert into cr_mime_types(label, mime_type, file_extension) values ('Unknown', '*/*', ''); +comment on table cr_extension_mime_type_map is ' + a mapping table for extension to mime_type in db version of ns_guesstype data +'; +-- Load the mime type data. +\i ../common/mime-type-data.sql + create table cr_content_mime_type_map ( content_type varchar(100) constraint cr_content_mime_map_ctyp_fk @@ -106,8 +97,10 @@ content type. '; +-- RI Index +-- fairly static, could probably omit this one. +create index cr_cont_mimetypmap_mimetyp_idx ON cr_content_mime_type_map(mime_type); - -------------------------------------------------------------- -- LOCALES -------------------------------------------------------------- @@ -164,6 +157,10 @@ contain. '; +-- RI Indexes +create index cr_type_children_chld_type_idx ON cr_type_children(child_type); + + create table cr_type_relations ( content_type varchar(100) constraint cr_type_relations_parent_fk @@ -183,6 +180,10 @@ relate (see above). '; +-- RI Indexes +create index cr_type_relations_tgt_typ_idx ON cr_type_relations(target_type); + + -------------------------------------------------------------- -- CONTENT ITEMS -------------------------------------------------------------- @@ -415,6 +416,9 @@ item_id, related_object_id, relation_tag ); +-- RI Indexes +create index cr_item_rels_rel_obj_id_idx ON cr_item_rels(related_object_id); + comment on table cr_item_rels is ' Describes all relations from one item to any number of other objects. @@ -448,7 +452,7 @@ cr_items on delete cascade, title varchar(1000), description text, - publish_date timestamp, + publish_date timestamptz, mime_type varchar(200) default 'text/plain' constraint cr_revisions_mime_type_ref references cr_mime_types, @@ -461,6 +465,10 @@ content_length integer ); +-- RI Indexes +create index cr_revisions_lob_idx ON cr_revisions(lob); +create index cr_revisions_item_id_idx ON cr_revisions(item_id); + create trigger cr_revisions_lob_trig before delete or update or insert on cr_revisions for each row execute procedure on_lob_ref(); @@ -688,7 +696,7 @@ new_revision integer, old_status varchar(40), new_status varchar(40), - publish_date timestamp + publish_date timestamptz constraint cr_item_publish_audit_date_nil not null ); @@ -706,12 +714,12 @@ references cr_items constraint cr_release_periods_pk primary key, - start_when timestamp default now(), - end_when timestamp default now() + interval '20 years' + start_when timestamptz default current_timestamp, + end_when timestamptz default current_timestamp + interval '20 years' ); create table cr_scheduled_release_log ( - exec_date timestamp default now() not null, + exec_date timestamptz default current_timestamp not null, items_released integer not null, items_expired integer not null, err_num integer, @@ -725,7 +733,7 @@ create table cr_scheduled_release_job ( job_id integer, - last_exec timestamp + last_exec timestamptz ); comment on table cr_scheduled_release_job is ' @@ -762,6 +770,8 @@ repository. '; +--RI Indexes +create index cr_folders_package_id_idx ON cr_folders(package_id); create function cr_folder_ins_up_ri_trg() returns opaque as ' declare @@ -821,9 +831,9 @@ thos available for content types. '; +-- RI Indexes +create index cr_folder_typ_map_cont_typ_idx ON cr_folder_type_map(content_type); - - -------------------------------------------------------------- -- CONTENT TEMPLATES -------------------------------------------------------------- @@ -986,6 +996,8 @@ tree_sortkey varbit ); +-- RI Indexes +create index cr_keywords_parent_id_idx ON cr_keywords(parent_id); create function cr_keywords_get_tree_sortkey(integer) returns varbit as ' declare @@ -1116,6 +1128,8 @@ primary key (item_id, keyword_id) ); +-- RI Indexes +create index cr_item_keyword_map_kw_id_idx ON cr_item_keyword_map(keyword_id); -------------------------------------------------------------- -- TEXT SUBMISSION @@ -1209,7 +1223,7 @@ PERFORM content_type__register_mime_type(''content_revision'', ''text/plain''); PERFORM content_type__register_mime_type(''content_revision'', - ''text/richtext''); + ''application/rtf''); v_id := content_folder__new ( ''pages'', @@ -1359,7 +1373,6 @@ add constraint cr_flder_pkg_id_fk foreign key (package_id) references apm_packages (package_id); --constraint cr_fldr_pkg_id_fk --- show errors -- prompt *** Preparing search indices... \i content-search.sql