Index: openacs-4/packages/acs-kernel/sql/oracle/acs-relationships-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/acs-relationships-create.sql,v diff -u -N -r1.16 -r1.16.2.1 --- openacs-4/packages/acs-kernel/sql/oracle/acs-relationships-create.sql 1 Nov 2018 08:38:00 -0000 1.16 +++ openacs-4/packages/acs-kernel/sql/oracle/acs-relationships-create.sql 12 Feb 2022 20:13:54 -0000 1.16.2.1 @@ -52,7 +52,7 @@ max_n_rels_two integer constraint acs_rel_types_max_n_2_ck check (max_n_rels_two >= 0), - composable_p boolean default 't' not null, + composable_p char(1) default 't' not null, constraint acs_rel_types_n_rels_one_ck check (min_n_rels_one <= max_n_rels_one), constraint acs_rel_types_n_rels_two_ck Index: openacs-4/packages/acs-kernel/sql/oracle/utilities-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/utilities-create.sql,v diff -u -N -r1.4.2.1 -r1.4.2.2 --- openacs-4/packages/acs-kernel/sql/oracle/utilities-create.sql 14 Feb 2019 10:02:36 -0000 1.4.2.1 +++ openacs-4/packages/acs-kernel/sql/oracle/utilities-create.sql 12 Feb 2022 20:13:54 -0000 1.4.2.2 @@ -38,39 +38,39 @@ return boolean; function foreign_key_exists ( - table IN varchar2, + table_name IN varchar2, column IN varchar2, reftable IN varchar2, refcolumn IN varchar2) return boolean; function unique_exists ( - table IN varchar2, + table_name IN varchar2, column IN varchar2, single_p IN boolean default true) return boolean; function primary_key_exists ( - table IN varchar2, + table_name IN varchar2, column IN varchar2, single_p IN boolean default true) return boolean; function not_null_exists ( - table IN varchar2, + table_name IN varchar2, column IN varchar2) return boolean; function get_default ( - table IN varchar2, + table_name IN varchar2, column IN varchar2) return LONG; - TYPE primary_keys IS TABLE OF varchar2; + TYPE primary_keys IS TABLE OF varchar2(100); - function get_primary_keys(table IN varchar2) - return primary_keys - PIPELINED; + function get_primary_keys ( + table_name IN varchar2) + return primary_keys; end util; / @@ -205,7 +205,7 @@ END index_exists; function foreign_key_exists ( - table IN varchar2, + table_name IN varchar2, column IN varchar2, reftable IN varchar2, refcolumn IN varchar2) @@ -221,7 +221,7 @@ left join user_constraints cons_r on cons_r.constraint_name = cons.r_constraint_name left join user_cons_columns cols_r on cols_r.constraint_name = cons.r_constraint_name where cons.constraint_type = 'R' - and cons.table_name = foreign_key_exists.table + and cons.table_name = foreign_key_exists.table_name and cols.column_name = foreign_key_exists.column and cons_r.table_name = foreign_key_exists.reftable and cols_r.column_name = foreign_key_exists.refcolumn; @@ -236,25 +236,29 @@ end foreign_key_exists; function unique_exists ( - table IN varchar2, + table_name IN varchar2, column IN varchar2, single_p IN boolean default true) return boolean as v_count integer; v_exists boolean; + v_single integer; begin + v_single := case when unique_exists.single_p then 1 else 0 end; + select decode(count(*),0,0,1) into v_count from all_constraints c join all_cons_columns cc on (c.owner = cc.owner and c.constraint_name = cc.constraint_name) where c.constraint_type = 'U' - and c.table_name = unique_exists.table + and c.table_name = unique_exists.table_name and cc.column_name = unique_exists.column - and (not unique_exists.single_p or ( + and ((v_single = 0) or ( select count(*) from all_cons_columns where constraint_name = c.constraint_name) = 1); + if v_count = 1 then v_exists := true; else @@ -265,23 +269,26 @@ END unique_exists; function primary_key_exists ( - table IN varchar2, + table_name IN varchar2, column IN varchar2, single_p IN boolean default true) return boolean as v_count integer; v_exists boolean; + v_single integer; begin + v_single := case when primary_key_exists.single_p then 1 else 0 end; + select decode(count(*),0,0,1) into v_count from all_constraints c join all_cons_columns cc on (c.owner = cc.owner and c.constraint_name = cc.constraint_name) where c.constraint_type = 'P' - and c.table_name = primary_key_exists.table + and c.table_name = primary_key_exists.table_name and cc.column_name = primary_key_exists.column - and (not primary_key_exists.single_p or ( + and ((v_single = 0) or ( select count(*) from all_cons_columns where constraint_name = c.constraint_name and owner = c.owner) = 1); @@ -296,7 +303,7 @@ END primary_key_exists; function not_null_exists ( - table IN varchar2, + table_name IN varchar2, column IN varchar2) return boolean as @@ -306,7 +313,7 @@ begin select decode(count(*),0,0,1) into v_count from all_tab_columns - where table_name = not_null_exists.table + where table_name = not_null_exists.table_name and column_name = not_null_exists.column and nullable = 'N'; @@ -320,7 +327,7 @@ END not_null_exists; function get_default ( - table in varchar2, + table_name in varchar2, column in varchar2) return long as @@ -329,14 +336,14 @@ begin select data_default into v_value from all_tab_columns - where table_name = get_default.table + where table_name = get_default.table_name and column_name = get_default.column; return v_value; end get_default; function get_primary_keys( - table in varchar2) + table_name in varchar2) return primary_keys as v_rec primary_keys; @@ -345,7 +352,7 @@ select cols.column_name bulk collect into v_rec from all_constraints cons, all_cons_columns cols - where cols.table_name = get_primary_keys.table + where cols.table_name = get_primary_keys.table_name and cons.constraint_type = 'P' and cons.constraint_name = cols.constraint_name and cons.owner = cols.owner