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;