Index: openacs-4/packages/acs-authentication/sql/postgresql/batch-job-tables-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-authentication/sql/postgresql/batch-job-tables-create.sql,v diff -u -r1.5 -r1.6 --- openacs-4/packages/acs-authentication/sql/postgresql/batch-job-tables-create.sql 16 Sep 2003 13:07:42 -0000 1.5 +++ openacs-4/packages/acs-authentication/sql/postgresql/batch-job-tables-create.sql 25 Sep 2006 20:15:49 -0000 1.6 @@ -3,7 +3,7 @@ create table auth_batch_jobs ( job_id integer - constraint auth_batch_jobs_pk + constraint auth_batch_jobs_job_id_pk primary key, job_start_time timestamptz default current_timestamp, job_end_time timestamptz, @@ -12,13 +12,13 @@ not null, snapshot_p boolean, authority_id integer - constraint auth_batch_jobs_auth_fk + constraint auth_batch_jobs_auth_id_fk references auth_authorities(authority_id) on delete cascade, message text, -- if interactive, by which user creation_user integer - constraint auth_batch_job_user_fk + constraint auth_batch_jobs_user_fk references users(user_id) on delete set null, -- status information for the GetDocument operation 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.50 -r1.51 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 4 Jun 2006 00:45:23 -0000 1.50 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 25 Sep 2006 20:25:19 -0000 1.51 @@ -45,7 +45,7 @@ create table cr_mime_types ( label varchar(200), mime_type varchar(200) - constraint cr_mime_types_pk + constraint cr_mime_types_mime_type_pk primary key, file_extension varchar(200) ); @@ -66,10 +66,10 @@ create table cr_extension_mime_type_map ( extension varchar(200) - constraint cr_mime_type_extension_map_pk + constraint cr_extension_mime_type_map_pk primary key, mime_type varchar(200) - constraint cr_mime_ext_map_mime_type_ref + constraint cr_mime_ext_map_mime_type_fk references cr_mime_types ); create index cr_extension_mime_type_map_idx on cr_extension_mime_type_map(mime_type); @@ -107,15 +107,15 @@ create table cr_locales ( locale varchar(4) - constraint cr_locale_abbrev_pk + constraint cr_locales_locale_pk primary key, label varchar(200) - constraint cr_locale_name_nil + constraint cr_locales_label_nn not null - constraint cr_locale_name_unq + constraint cr_locales_label_un unique, nls_language varchar(30) - constraint cr_locale_nls_lang_nil + constraint cr_locale_nls_language_nn not null, nls_territory varchar(30), nls_charset varchar(30) @@ -140,10 +140,10 @@ create table cr_type_children ( parent_type varchar(100) - constraint cr_type_children_parent_fk + constraint cr_type_children_parent_type_fk references acs_object_types, child_type varchar(100) - constraint cr_type_children_child_fk + constraint cr_type_children_child_type_fk references acs_object_types, relation_tag varchar(100), min_n integer, @@ -194,30 +194,30 @@ item_id integer constraint cr_items_item_id_fk references acs_objects on delete cascade - constraint cr_items_pk primary key, + constraint cr_items_item_id_pk primary key, parent_id integer - constraint cr_items_parent_id_nil + constraint cr_items_parent_id_nn not null constraint cr_items_parent_id_fk references acs_objects on delete cascade, name varchar(400) - constraint cr_items_name_nil + constraint cr_items_name_nn not null, locale varchar(4) constraint cr_items_locale_fk references cr_locales, live_revision integer, latest_revision integer, publish_status varchar(40) - constraint cr_items_pub_status_chk + constraint cr_items_publish_status_ck check (publish_status in ('production', 'ready', 'live', 'expired') ), content_type varchar(100) - constraint cr_items_rev_type_fk + constraint cr_items_content_type_fk references acs_object_types, storage_type varchar(10) default 'text' not null - constraint cr_items_storage_type + constraint cr_items_storage_type_ck check (storage_type in ('lob','text','file')), storage_area_key varchar(100) default 'CR_FILES' not null, tree_sortkey varbit not null, @@ -382,15 +382,15 @@ create table cr_child_rels ( rel_id integer - constraint cr_child_rels_rel_pk + constraint cr_child_rels_rel_id_pk primary key - constraint cr_child_rels_rel_fk + constraint cr_child_rels_rel_id_fk references acs_objects, parent_id integer - constraint cr_child_rels_parent_nil + constraint cr_child_rels_parent_id_nn not null, child_id integer - constraint cr_child_rels_child_nil + constraint cr_child_rels_child_id_nn not null, relation_tag varchar(100), order_n integer @@ -407,15 +407,15 @@ create table cr_item_rels ( rel_id integer - constraint cr_item_rels_pk + constraint cr_item_rels_rel_id_pk primary key - constraint cr_item_rels_fk + constraint cr_item_rels_rel_id_fk references acs_objects, item_id integer - constraint cr_item_rels_item_fk + constraint cr_item_rels_item_id_fk references cr_items, related_object_id integer - constraint cr_item_rels_rel_obj__fk + constraint cr_item_rels_rel_obj_fk references acs_objects, relation_tag varchar(100), order_n integer @@ -452,18 +452,18 @@ -- Define the cr_revisions table create table cr_revisions ( - revision_id integer constraint cr_revisions_rev_id_fk references + revision_id integer constraint cr_revisions_revision_id_fk references acs_objects (object_id) on delete cascade - constraint cr_revisions_pk primary key, - item_id integer constraint cr_revisions_item_id_nil + constraint cr_revisions_revision_id_pk primary key, + item_id integer constraint cr_revisions_item_id_nn not null constraint cr_revisions_item_id_fk references cr_items on delete cascade, title varchar(1000), description text, publish_date timestamptz, mime_type varchar(200) default 'text/plain' - constraint cr_revisions_mime_type_ref + constraint cr_revisions_mime_type_fk references cr_mime_types, nls_language varchar(50), -- lob_id if storage_type = lob. @@ -690,7 +690,8 @@ -- ) on commit delete rows; create table cr_content_text ( - revision_id integer primary key, + revision_id integer + constraint cr_content_text_revision_id_pk primary key, content integer ); @@ -711,7 +712,7 @@ old_status varchar(40), new_status varchar(40), publish_date timestamptz - constraint cr_item_publish_audit_date_nil + constraint cr_item_publish_audit_date_nn not null ); @@ -724,9 +725,9 @@ create table cr_release_periods ( item_id integer - constraint cr_release_periods_fk + constraint cr_release_periods_item_id_fk references cr_items - constraint cr_release_periods_pk + constraint cr_release_periods_item_id_pk primary key, start_when timestamptz default current_timestamp, end_when timestamptz default current_timestamp + interval '20 years' @@ -768,14 +769,14 @@ -- constraint cr_folder_id_fk references -- cr_items on delete cascade - constraint cr_folders_pk + constraint cr_folders_folder_id_pk primary key, label varchar(1000), description text, has_child_folders boolean default 'f', has_child_symlinks boolean default 'f', package_id integer - constraint cr_fldr_pkg_id_fk + constraint cr_folders_pkg_id_fk references apm_packages ); @@ -854,9 +855,9 @@ create table cr_templates ( template_id integer - constraint cr_template_id_fk references + constraint cr_templates_template_id_fk references cr_items on delete cascade - constraint cr_templates_pk + constraint cr_templates_template_id_pk primary key ); @@ -883,13 +884,13 @@ content_type varchar(100) constraint cr_type_template_map_typ_fk references acs_object_types - constraint cr_type_template_map_typ_nil + constraint cr_type_template_map_typ_nn not null, template_id integer constraint cr_type_template_map_tmpl_fk references cr_templates, use_context varchar(100) - constraint cr_type_template_map_ctx_nil + constraint cr_type_template_map_ctx_nn not null constraint cr_type_template_map_ctx_fk references cr_template_use_contexts, @@ -919,15 +920,15 @@ item_id integer constraint cr_item_template_map_item_fk references cr_items - constraint cr_item_template_map_item_nil + constraint cr_item_template_map_item_nn not null, template_id integer constraint cr_item_template_map_tmpl_fk references cr_templates - constraint cr_item_template_map_tmpl_nil + constraint cr_item_template_map_tmpl_nn not null, use_context varchar(100) - constraint cr_item_template_map_ctx_nil + constraint cr_item_template_map_ctx_nn not null constraint cr_item_template_map_ctx_fk references cr_template_use_contexts, @@ -949,14 +950,14 @@ create table cr_symlinks ( symlink_id integer - constraint cr_symlink_id_fk references + constraint cr_symlinks_symlink_id_fk references cr_items on delete cascade - constraint cr_symlinks_pk + constraint cr_symlinks_symlink_id_pk primary key, target_id integer - constraint cr_symlink_target_id_fk + constraint cr_symlinks_target_id_fk references cr_items - constraint cr_symlink_target_id_nil + constraint cr_symlinks_target_id_nn not null, label varchar(1000) ); @@ -973,15 +974,15 @@ create table cr_extlinks ( extlink_id integer - constraint cr_extlink_id_fk references + constraint cr_extlinks_extlink_id_fk references cr_items on delete cascade - constraint cr_extlinks_pk + constraint cr_extlinks_extlink_id_pk primary key, url varchar(1000) - constraint cr_extlink_url_nil + constraint cr_extlinks_url_nn not null, label varchar(1000) - constraint cr_extlink_label_nil + constraint cr_extlinks_label_nn not null, description text ); @@ -997,13 +998,13 @@ create table cr_keywords ( keyword_id integer - constraint cr_keywords_pk + constraint cr_keywords_keyword_id_pk primary key, parent_id integer - constraint cr_keywords_hier + constraint cr_keywords_parent_id_fk references cr_keywords, heading varchar(600) - constraint cr_keywords_name_nil + constraint cr_keywords_heading_nn not null, description text, has_children boolean, @@ -1129,14 +1130,14 @@ create table cr_item_keyword_map ( item_id integer - constraint cr_item_keyword_map_item_fk + constraint cr_item_keyword_map_item_id_fk references cr_items - constraint cr_item_keyword_map_item_nil + constraint cr_item_keyword_map_item_id_nn not null, keyword_id integer constraint cr_item_keyword_map_kw_fk references cr_keywords - constraint cr_item_keyword_map_kw_nil + constraint cr_item_keyword_map_kw_nn not null, constraint cr_item_keyword_map_pk primary key (item_id, keyword_id) @@ -1185,7 +1186,9 @@ --) on commit delete rows; create table cr_doc_filter ( - revision_id integer primary key, + revision_id integer + constraint cr_doc_filter_revision_id_pk + primary key, -- content BLOB -- need a blob trigger here content integer @@ -1393,9 +1396,15 @@ -- this was added for edit-this-page and others -- 05-Nov-2001 Jon Griffin jon@mayuli.com +---drop the previw constraint + alter table cr_folders -add constraint cr_flder_pkg_id_fk foreign key (package_id) references apm_packages (package_id); +drop constraint cr_folders_pkg_id_fk; +------- +alter table cr_folders +add constraint cr_folders_package_id_fk foreign key (package_id) references apm_packages (package_id); + --constraint cr_fldr_pkg_id_fk -- prompt *** Preparing search indices... Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-image.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-image.sql,v diff -u -r1.16 -r1.17 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-image.sql 12 Mar 2004 19:19:46 -0000 1.16 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-image.sql 25 Sep 2006 20:25:19 -0000 1.17 @@ -16,7 +16,7 @@ image_id integer constraint images_image_id_fk references cr_revisions - constraint images_pk + constraint images_image_id_pk primary key, width integer, height integer Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql,v diff -u -r1.47 -r1.48 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql 24 Sep 2006 11:37:05 -0000 1.47 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql 25 Sep 2006 20:25:19 -0000 1.48 @@ -79,8 +79,8 @@ where object_type = create_type__supertype; execute ''create table '' || create_type__table_name || '' ('' || - create_type__id_column || '' integer primary key references '' || - v_supertype_table || '')''; + create_type__id_column || '' integer constraint '' || create_type__table_name || ''_pk primary key '' || + '' constraint '' || create_type__table_name || ''_fk references '' || v_supertype_table || '')''; end if; PERFORM acs_object_type__create_type ( Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-xml.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-xml.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-xml.sql 28 Apr 2001 19:58:39 -0000 1.3 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-xml.sql 25 Sep 2006 20:25:19 -0000 1.4 @@ -21,7 +21,8 @@ -- ) on commit delete rows; create table cr_xml_docs ( - doc_id integer primary key, + doc_id integer constraint cr_xml_docs_doc_id_pk + primary key, doc text );