Index: openacs-4/packages/faq/faq.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/faq/faq.info,v
diff -u -r1.35 -r1.36
--- openacs-4/packages/faq/faq.info 18 Jan 2019 19:33:17 -0000 1.35
+++ openacs-4/packages/faq/faq.info 29 Jan 2019 17:10:34 -0000 1.36
@@ -7,7 +7,7 @@
f
f
-
+
Nima Mazloumi
Jennie Kim Housman
Elizabeth Wirth
@@ -18,7 +18,7 @@
2
#faq.FAQs#
-
+
Index: openacs-4/packages/faq/sql/oracle/faq-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/faq/sql/oracle/faq-create.sql,v
diff -u -r1.5 -r1.6
--- openacs-4/packages/faq/sql/oracle/faq-create.sql 1 Apr 2004 22:52:45 -0000 1.5
+++ openacs-4/packages/faq/sql/oracle/faq-create.sql 29 Jan 2019 17:10:34 -0000 1.6
@@ -21,6 +21,7 @@
create table faqs (
faq_id constraint faqs_faq_id_fk
references acs_objects (object_id)
+ on delete cascade
constraint faqs_pk
primary key,
faq_name varchar (250)
@@ -49,6 +50,7 @@
create table faq_q_and_as (
entry_id constraint faq_q_and_as_entry_id_fk
references acs_objects (object_id)
+ on delete cascade
constraint faq_q_sand_a_pk
primary key,
faq_id integer references faqs not null,
@@ -122,4 +124,4 @@
end;
/
-@@ faq-package-create.sql
\ No newline at end of file
+@@ faq-package-create.sql
Index: openacs-4/packages/faq/sql/oracle/upgrade/upgrade-5.10.0d5-5.10.0d6.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/faq/sql/oracle/upgrade/upgrade-5.10.0d5-5.10.0d6.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/faq/sql/oracle/upgrade/upgrade-5.10.0d5-5.10.0d6.sql 29 Jan 2019 17:10:34 -0000 1.1
@@ -0,0 +1,64 @@
+begin;
+
+alter table faqs drop constraint faqs_faq_id_fk;
+alter table faqs add constraint faqs_faq_id_fk
+ foreign key (faq_id) references acs_objects(object_id) on delete cascade;
+
+alter table faq_q_and_as drop constraint faq_q_and_as_entry_id_fk;
+alter table faq_q_and_as add constraint faq_q_and_as_entry_id_fk
+ foreign key (entry_id) references acs_objects(object_id) on delete cascade;
+
+-- Foreign key constraint on faq_q_and_as.faq_id was defined without
+-- and explicit name.
+-- In postgres we could do:
+-- DO
+-- $body$
+-- DECLARE
+-- v_constraint_name text := (
+-- select tc.constraint_name
+-- from information_schema.table_constraints AS tc,
+-- information_schema.key_column_usage AS kcu,
+-- information_schema.constraint_column_usage AS ccu
+-- where tc.constraint_name = kcu.constraint_name
+-- and tc.constraint_catalog = kcu.constraint_catalog
+-- and tc.constraint_schema = kcu.constraint_schema
+-- and tc.table_catalog = kcu.table_catalog
+-- and tc.table_schema = kcu.table_schema
+-- and ccu.constraint_name = tc.constraint_name
+-- and ccu.constraint_catalog = kcu.constraint_catalog
+-- and ccu.constraint_schema = kcu.constraint_schema
+-- and ccu.table_catalog = kcu.table_catalog
+-- and ccu.table_schema = kcu.table_schema
+-- and tc.constraint_type = 'FOREIGN KEY'
+-- and tc.table_name = 'faq_q_and_as'
+-- and kcu.column_name = 'faq_id'
+-- and ccu.table_name = 'faqs'
+-- and ccu.column_name = 'faq_id');
+-- BEGIN
+-- EXECUTE '
+-- ALTER TABLE faq_q_and_as DROP CONSTRAINT ' || v_constraint_name;
+-- EXECUTE '
+-- ALTER TABLE faq_q_and_as
+-- ADD CONSTRAINT ' || v_constraint_name || ' FOREIGN KEY (faq_id)
+-- REFERENCES faqs (faq_id)';
+-- END
+-- $body$;
+
+-- This is a solution for Oracle in plain SQL, which might sound a bit
+-- hamfisted...
+-- - create a temp column with the value of faq_id
+alter table faq_q_and_as add column tmp_faq_id integer;
+update faq_q_and_as set tmp_faq_id = faq_id;
+-- - drop faq_id column
+alter table faq_q_and_as drop column faq_id;
+-- - re-create it with values stored in temp column
+alter table faq_q_and_as add column faq_id integer;
+update faq_q_and_as set faq_id = tmp_faq_id;
+-- - update constraints
+alter table faq_q_and_as alter column faq_id set not null;
+alter table faq_q_and_as add constraint faq_q_and_as_faq_id_fkey
+ foreign key (faq_id) references faqs(faq_id) on delete cascade;
+-- - drop temp column
+alter table faq_q_and_as drop column tmp_faq_id;
+
+end;
Index: openacs-4/packages/faq/sql/postgresql/faq-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/faq/sql/postgresql/faq-create.sql,v
diff -u -r1.9 -r1.10
--- openacs-4/packages/faq/sql/postgresql/faq-create.sql 12 Jul 2018 08:51:04 -0000 1.9
+++ openacs-4/packages/faq/sql/postgresql/faq-create.sql 29 Jan 2019 17:10:34 -0000 1.10
@@ -30,6 +30,7 @@
create table faqs (
faq_id integer constraint faqs_faq_id_fk
references acs_objects(object_id)
+ on delete cascade
constraint faqs_pk
primary key,
faq_name varchar (250)
@@ -66,6 +67,7 @@
create table faq_q_and_as (
entry_id integer constraint faq_q_and_as_entry_id_fk
references acs_objects (object_id)
+ on delete cascade
constraint faq_q_sand_a_pk
primary key,
faq_id integer references faqs not null,
Index: openacs-4/packages/faq/sql/postgresql/upgrade/upgrade-5.10.0d5-5.10.0d6.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/faq/sql/postgresql/upgrade/upgrade-5.10.0d5-5.10.0d6.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/faq/sql/postgresql/upgrade/upgrade-5.10.0d5-5.10.0d6.sql 29 Jan 2019 17:10:34 -0000 1.1
@@ -0,0 +1,62 @@
+begin;
+
+alter table faqs drop constraint faqs_faq_id_fk;
+alter table faqs add constraint faqs_faq_id_fk
+ foreign key (faq_id) references acs_objects(object_id) on delete cascade;
+
+alter table faq_q_and_as drop constraint faq_q_and_as_entry_id_fk;
+alter table faq_q_and_as add constraint faq_q_and_as_entry_id_fk
+ foreign key (entry_id) references acs_objects(object_id) on delete cascade;
+
+-- Foreign key constraint on faq_q_and_as.faq_id was defined without
+-- and explicit name. One could save/drop/recreate the faq_id column
+-- or do something like this...
+DO
+$body$
+DECLARE
+ v_constraint_name text := (
+ select tc.constraint_name
+ from information_schema.table_constraints AS tc,
+ information_schema.key_column_usage AS kcu,
+ information_schema.constraint_column_usage AS ccu
+ where tc.constraint_name = kcu.constraint_name
+ and tc.constraint_catalog = kcu.constraint_catalog
+ and tc.constraint_schema = kcu.constraint_schema
+ and tc.table_catalog = kcu.table_catalog
+ and tc.table_schema = kcu.table_schema
+ and ccu.constraint_name = tc.constraint_name
+ and ccu.constraint_catalog = kcu.constraint_catalog
+ and ccu.constraint_schema = kcu.constraint_schema
+ and ccu.table_catalog = kcu.table_catalog
+ and ccu.table_schema = kcu.table_schema
+ and tc.constraint_type = 'FOREIGN KEY'
+ and tc.table_name = 'faq_q_and_as'
+ and kcu.column_name = 'faq_id'
+ and ccu.table_name = 'faqs'
+ and ccu.column_name = 'faq_id');
+BEGIN
+ EXECUTE '
+ ALTER TABLE faq_q_and_as DROP CONSTRAINT ' || v_constraint_name;
+ EXECUTE '
+ ALTER TABLE faq_q_and_as
+ ADD CONSTRAINT ' || v_constraint_name || ' FOREIGN KEY (faq_id)
+ REFERENCES faqs (faq_id) ON DELETE CASCADE';
+END
+$body$;
+
+-- -- - create a temp column with the value of faq_id
+-- alter table faq_q_and_as add column tmp_faq_id integer;
+-- update faq_q_and_as set tmp_faq_id = faq_id;
+-- -- - drop faq_id column
+-- alter table faq_q_and_as drop column faq_id;
+-- -- - re-create it with values stored in temp column
+-- alter table faq_q_and_as add column faq_id integer;
+-- update faq_q_and_as set faq_id = tmp_faq_id;
+-- -- - update constraints
+-- alter table faq_q_and_as alter column faq_id set not null;
+-- alter table faq_q_and_as add constraint faq_q_and_as_faq_id_fkey
+-- foreign key (faq_id) references faqs(faq_id) on delete cascade;
+-- -- - drop temp column
+-- alter table faq_q_and_as drop column tmp_faq_id;
+
+end;