Index: openacs-4/packages/acs-kernel/acs-kernel.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/acs-kernel.info,v diff -u -N -r1.136.2.25 -r1.136.2.26 --- openacs-4/packages/acs-kernel/acs-kernel.info 20 Dec 2016 20:28:49 -0000 1.136.2.25 +++ openacs-4/packages/acs-kernel/acs-kernel.info 21 Dec 2016 00:14:56 -0000 1.136.2.26 @@ -9,15 +9,15 @@ f t - + OpenACS Core Team Routines and data models providing the foundation for OpenACS-based Web services. 2016-09-06 OpenACS The OpenACS kernel contains the core datamodel create and drop scripts for such things as objects, groups, partiies and the supporting PL/SQL and PL/pgSQL procedures. 3 - + 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.3.2.1 -r1.3.2.2 --- openacs-4/packages/acs-kernel/sql/oracle/utilities-create.sql 20 Dec 2016 20:28:49 -0000 1.3.2.1 +++ openacs-4/packages/acs-kernel/sql/oracle/utilities-create.sql 21 Dec 2016 00:14:56 -0000 1.3.2.2 @@ -64,6 +64,13 @@ table IN varchar2, column IN varchar2) return LONG; + + TYPE primary_keys IS TABLE OF varchar2; + + function get_primary_keys(table IN varchar2) + return primary_keys + PIPELINED; + end util; / show errors @@ -225,8 +232,24 @@ FROM ALL_TAB_COLUMNS WHERE table_name = table AND column_name = column); - END get_default; + END get_default; + function get_primary_keys(table IN varchar2) + return primary_keys IS + rec primary_keys; + BEGIN + SELECT cols.column_name + BULK COLLECT INTO rec + FROM all_constraints cons, all_cons_columns cols + WHERE cols.table_name = table + AND cons.constraint_type = 'P' + AND cons.constraint_name = cols.constraint_name + AND cons.owner = cols.owner + ORDER BY cols.table_name, cols.position; + + RETURN rec; + END get_primary_keys; + end util; / show errors Index: openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.9.1d19-5.9.1d20.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.9.1d19-5.9.1d20.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.9.1d19-5.9.1d20.sql 21 Dec 2016 00:14:56 -0000 1.1.2.1 @@ -0,0 +1,245 @@ + +create or replace package util +as + function multiple_nextval( + v_sequence_name in varchar2, + v_count in integer) + return varchar2; + + function logical_negation ( + true_or_false IN varchar2) + return varchar2; + + function table_exists ( + name in varchar2) + return boolean; + + function table_column_exists ( + t_name in varchar2, + c_name in varchar2) + return boolean; + + function view_exists ( + name in varchar2) + return boolean; + + function index_exists ( + name in varchar2) + return boolean; + + function foreign_key_exists ( + table IN varchar2, + column IN varchar2, + reftable IN varchar2, + refcolumn IN varchar2) + return boolean; + + function unique_exists ( + table IN varchar2, + column IN varchar2, + single_p IN boolean default true) + return boolean; + + function primary_key_exists ( + table IN varchar2, + column IN varchar2, + single_p IN boolean default true) + return boolean; + + function not_null_exists ( + table IN varchar2, + column IN varchar2) + return boolean; + + function get_default ( + table IN varchar2, + column IN varchar2) + return LONG; + + TYPE primary_keys IS TABLE OF varchar2; + + function get_primary_keys(table IN varchar2) + return primary_keys; + +end util; +/ +show errors + +create or replace package body util +as + -- Retrieves v_count (not necessarily consecutive) nextval values from the + -- sequence named v_sequence_name. + function multiple_nextval( + v_sequence_name in varchar2, + v_count in integer + ) + return varchar2 + is + a_sequence_values varchar2(4000); + begin + execute immediate ' + declare + a_nextval integer; + begin + for counter in 1..:v_count loop + select ' || v_sequence_name || '.nextval into a_nextval from dual; + :a_sequence_values := :a_sequence_values || '','' || a_nextval; + end loop; + end; + ' using in v_count, in out a_sequence_values; + return substr(a_sequence_values, 2); + end; + + function logical_negation ( + true_or_false IN varchar2) + return varchar2 + as + begin + IF true_or_false is null THEN + return null; + ELSIF true_or_false = 'f' THEN + return 't'; + ELSE + return 'f'; + END IF; + END logical_negation; + + function table_exists ( + name IN varchar2) + return boolean + as + begin + return exists (select 1 from user_tables where table_name = t_name); + END table_exists; + + function table_column_exists ( + t_name IN varchar2, + c_name IN varchar2) + return boolean + as + begin + return exists (select 1 from user_tab_columns where c.table_name = t_name and c.column_name = c_name); + END table_column_exists; + + function view_exists ( + name IN varchar2) + return boolean + as + begin + return exists (select 1 from user_views where view_name = name); + END view_exists; + + function index_exists ( + name IN varchar2) + return boolean + as + begin + return exists (select 1 from user_indexes where index_name = name); + END index_exists; + + function foreign_key_exists ( + table IN varchar2, + column IN varchar2, + reftable IN varchar2, + refcolumn IN varchar2) + return boolean + as + begin + return exists ( + SELECT 1 FROM USER_CONSTRAINTS CONS + LEFT JOIN USER_CONS_COLUMNS COLS ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME + 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 = table + AND COLS.COLUMN_NAME = column + AND CONS_R.TABLE_NAME = reftable + AND COLS_R.COLUMN_NAME = refcolumn); + END foreign_key_exists; + + function unique_exists ( + table IN varchar2, + column IN varchar2, + single_p IN boolean default true) + return boolean + as + begin + return exists ( + SELECT 1 + 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 = table + AND cc.column_name = column + and (not single_p or ( + select count(*) from all_cons_columns + where constraint_name = c.constraint_name) = 1)); + END unique_exists; + + function primary_key_exists ( + table IN varchar2, + column IN varchar2, + single_p IN boolean default true) + return boolean + as + begin + return exists ( + SELECT 1 + 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 = table + AND cc.column_name = column + and (not single_p or ( + select count(*) from all_cons_columns + where constraint_name = c.constraint_name + and owner = c.owner) = 1)); + END primary_key_exists; + + function not_null_exists ( + table IN varchar2, + column IN varchar2) + return boolean + as + begin + return ( + SELECT nullable = 'N' + FROM ALL_TAB_COLUMNS + WHERE table_name = table + AND column_name = column); + END not_null_exists; + + function get_default ( + table IN varchar2, + column IN varchar2) + return LONG + as + begin + return ( + SELECT DATA_DEFAULT + FROM ALL_TAB_COLUMNS + WHERE table_name = table + AND column_name = column); + END get_default; + + function get_primary_keys(table IN varchar2) + return primary_keys IS + rec primary_keys; + BEGIN + SELECT cols.column_name + BULK COLLECT INTO rec + FROM all_constraints cons, all_cons_columns cols + WHERE cols.table_name = table + AND cons.constraint_type = 'P' + AND cons.constraint_name = cols.constraint_name + AND cons.owner = cols.owner + ORDER BY cols.table_name, cols.position; + + RETURN rec; + END get_primary_keys; + +end util; +/ +show errors Index: openacs-4/packages/acs-kernel/sql/postgresql/utilities-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/utilities-create.sql,v diff -u -N -r1.8.2.1 -r1.8.2.2 --- openacs-4/packages/acs-kernel/sql/postgresql/utilities-create.sql 20 Dec 2016 20:28:49 -0000 1.8.2.1 +++ openacs-4/packages/acs-kernel/sql/postgresql/utilities-create.sql 21 Dec 2016 00:14:56 -0000 1.8.2.2 @@ -282,3 +282,20 @@ and column_name = p_column); END; $$ LANGUAGE plpgsql; + +-- added +select define_function_args('util__get_primary_keys','table'); + +-- +-- procedure util__get_primary_keys/1 +-- +CREATE OR REPLACE FUNCTION util__get_primary_keys( + p_table text +) RETURNS SETOF pg_attribute.attname%TYPE AS $$ + SELECT a.attname + FROM pg_index i + JOIN pg_attribute a ON a.attrelid = i.indrelid + AND a.attnum = ANY(i.indkey) + WHERE i.indrelid = p_table::regclass + AND i.indisprimary; +$$ LANGUAGE sql; Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d19-5.9.1d20.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d19-5.9.1d20.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d19-5.9.1d20.sql 21 Dec 2016 00:14:56 -0000 1.1.2.1 @@ -0,0 +1,21 @@ + +begin; + +-- added +select define_function_args('util__get_primary_keys','table'); + +-- +-- procedure util__get_primary_keys/1 +-- +CREATE OR REPLACE FUNCTION util__get_primary_keys( + p_table text +) RETURNS SETOF pg_attribute.attname%TYPE AS $$ + SELECT a.attname + FROM pg_index i + JOIN pg_attribute a ON a.attrelid = i.indrelid + AND a.attnum = ANY(i.indkey) + WHERE i.indrelid = p_table::regclass + AND i.indisprimary; +$$ LANGUAGE sql; + +end;