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.63 -r1.64
--- openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql	3 May 2015 12:32:28 -0000	1.63
+++ openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql	7 Aug 2017 23:47:47 -0000	1.64
@@ -56,7 +56,7 @@
 \i ../common/mime-type-data.sql
 
 create table cr_content_mime_type_map (
-  content_type  varchar(100)
+  content_type  varchar(1000)
 		constraint cr_content_mime_map_ctyp_fk
 		references acs_object_types,
   mime_type	varchar(200)
@@ -115,10 +115,10 @@
 --------------------------------------------------------------
 
 create table cr_type_children (
-  parent_type   varchar(100)
+  parent_type   varchar(1000)
 		constraint cr_type_children_parent_type_fk
 		references acs_object_types,
-  child_type    varchar(100)
+  child_type    varchar(1000)
 		constraint cr_type_children_child_type_fk
 		references acs_object_types,
   relation_tag  varchar(100),
@@ -138,10 +138,10 @@
 
 
 create table cr_type_relations (
-  content_type  varchar(100)
+  content_type  varchar(1000)
 		constraint cr_type_relations_parent_fk
 		references acs_object_types,
-  target_type   varchar(100)
+  target_type   varchar(1000)
 		constraint cr_type_relations_child_fk
 		references acs_object_types,
   relation_tag  varchar(100),
@@ -163,6 +163,7 @@
 --------------------------------------------------------------
 -- CONTENT ITEMS
 --------------------------------------------------------------
+CREATE TYPE cr_item_storage_type_enum AS ENUM ('text', 'file', 'lob');
 
 -- Define the cr_items table
 
@@ -189,12 +190,10 @@
                       check (publish_status in 
                             ('production', 'ready', 'live', 'expired')
                             ),
-  content_type        varchar(100)
+  content_type        varchar(1000)
                       constraint cr_items_content_type_fk
                       references acs_object_types,
-  storage_type        varchar(10) default 'text' not null
-                      constraint cr_items_storage_type_ck
-                      check (storage_type in ('lob','text','file')),
+  storage_type        cr_item_storage_type_enum default 'text' not null,
   storage_area_key    varchar(100) default 'CR_FILES' not null,
   tree_sortkey        varbit not null,
   max_child_sortkey   varbit
@@ -232,7 +231,7 @@
 ';
 
 
--- DCW, this can't be defined in the apm_package_versions table defintion,
+-- DCW, this can't be defined in the apm_package_versions table definition,
 -- because cr_items is created afterwards.
 
 alter table apm_package_versions add
@@ -390,10 +389,14 @@
                      references acs_objects,
   parent_id          integer
                      constraint cr_child_rels_parent_id_nn
-                     not null,
+                     not null
+		     CONSTRAINT cr_child_rels_parent_id_fk
+                     REFERENCES acs_objects(object_id) ON DELETE CASCADE,
   child_id           integer
                      constraint cr_child_rels_child_id_nn
-                     not null,
+                     not null
+		     CONSTRAINT cr_child_rels_child_id_fk
+		     REFERENCES cr_items(item_id) ON DELETE CASCADE,
   relation_tag       varchar(100),
   order_n            integer
 );
@@ -416,8 +419,8 @@
                      constraint cr_item_rels_rel_id_fk
                      references acs_objects,
   item_id            integer
-                     constraint cr_item_rels_item_id_fk
-                     references cr_items,
+		     CONSTRAINT cr_item_rels_item_id_fk
+		     REFERENCES cr_items(item_id) ON DELETE CASCADE,
   related_object_id  integer
                      constraint cr_item_rels_rel_obj_fk
                      references acs_objects,
@@ -485,8 +488,8 @@
 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();
+CREATE TRIGGER cr_revisions_lob_trig AFTER UPDATE or DELETE or INSERT
+ON cr_revisions FOR EACH ROW EXECUTE PROCEDURE on_lob_ref();
 
 create index cr_revisions_by_mime_type on cr_revisions(mime_type);
 create index cr_revisions_title_idx on cr_revisions(title);
@@ -507,10 +510,10 @@
   for multi-lingual searching in Intermedia.
 ';
 
-alter table cr_items add constraint cr_items_live_fk 
+alter table cr_items add constraint cr_items_live_revision_fk 
       foreign key (live_revision) references cr_revisions(revision_id) on delete set null;
 
-alter table cr_items add constraint cr_items_latest_fk 
+alter table cr_items add constraint cr_items_latest_revision_fk 
      foreign key (latest_revision) references cr_revisions(revision_id) on delete set null;
 
 
@@ -827,7 +830,7 @@
   folder_id	integer
 		constraint cr_folder_type_map_fldr_fk
 		references cr_folders on delete cascade,
-  content_type  varchar(100)
+  content_type  varchar(1000)
 		constraint cr_folder_type_map_typ_fk
 		references acs_object_types on delete cascade,
   constraint cr_folder_type_map_pk
@@ -876,7 +879,7 @@
 insert into cr_template_use_contexts values ('public');
 
 create table cr_type_template_map (
-  content_type     varchar(100)
+  content_type     varchar(1000)
                    constraint cr_type_template_map_typ_fk
                    references acs_object_types
                    constraint cr_type_template_map_typ_nn
@@ -950,8 +953,8 @@
 		  constraint cr_symlinks_symlink_id_pk 
                   primary key,
   target_id       integer
-                  constraint cr_symlinks_target_id_fk
-		  references cr_items
+  		  CONSTRAINT cr_symlinks_target_id_fk
+		  REFERENCES cr_items(item_id) ON DELETE CASCADE
 		  constraint cr_symlinks_target_id_nn
 		  not null,
   label		  varchar(1000)
@@ -1375,16 +1378,19 @@
 
   v_revision_id := content_revision__new(
                'Template',
-               NULL,
-               now(),
-               'text/html',
-               null,
+               null,           -- description
+               now(),          -- publish_date
+               'text/html',    -- mime_type
+               null,           -- nls_language
                '<html><body>@text;noquote@</body></html>',
                v_item_id,
-               NULL,
-               now(),
-               null,
-               null);
+               null,           -- revision_id
+               now(),          -- creation_date
+               null,           -- creation_user
+               null,           -- creation_ip
+	       null,           -- content_length
+	       null            -- package_id
+	       );
 
   update 
     cr_revisions
