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.150.2.43 -r1.150.2.44
--- openacs-4/packages/acs-kernel/acs-kernel.info 21 Feb 2022 17:57:38 -0000 1.150.2.43
+++ openacs-4/packages/acs-kernel/acs-kernel.info 23 Feb 2022 18:24:53 -0000 1.150.2.44
@@ -9,15 +9,15 @@
f
t
-
+
OpenACS Core Team
Routines and data models providing the foundation for OpenACS-based Web services.
2021-09-15
OpenACS
The OpenACS kernel contains the core datamodel create and drop scripts for such things as objects, groups, parties 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.4.2.6 -r1.4.2.7
--- openacs-4/packages/acs-kernel/sql/oracle/utilities-create.sql 17 Feb 2022 10:36:30 -0000 1.4.2.6
+++ openacs-4/packages/acs-kernel/sql/oracle/utilities-create.sql 23 Feb 2022 18:24:53 -0000 1.4.2.7
@@ -26,8 +26,8 @@
return char;
function table_column_exists (
- t_name in varchar2,
- c_name in varchar2)
+ table_name in varchar2,
+ column in varchar2)
return char;
function view_exists (
@@ -130,8 +130,8 @@
END table_exists;
function table_column_exists (
- t_name IN varchar2,
- c_name IN varchar2)
+ table_name IN varchar2,
+ column IN varchar2)
return char
as
v_exists char;
@@ -140,8 +140,8 @@
select decode(count(*),0,'f','t') into v_exists
from user_tab_columns
- where table_name = upper(table_column_exists.t_name)
- and column_name = upper(table_column_exists.c_name);
+ where table_name = upper(table_column_exists.table_name)
+ and column_name = upper(table_column_exists.column);
return v_exists;
Index: openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.10.1d6-5.10.1d7.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/upgrade/Attic/upgrade-5.10.1d6-5.10.1d7.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.10.1d6-5.10.1d7.sql 23 Feb 2022 18:24:53 -0000 1.1.2.1
@@ -0,0 +1,312 @@
+PROMPT starting utilities-create.sql....
+--
+-- PL/SQL utility routines for accessing schema information
+--
+-- @author Jon Salz (jsalz@mit.edu), Antonio Pisano, Gustaf Neumann
+--
+
+create or replace TYPE t_util_primary_keys IS TABLE OF varchar2(100);
+/
+show errors
+
+
+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 char;
+
+ function table_column_exists (
+ table_name in varchar2,
+ column in varchar2)
+ return char;
+
+ function view_exists (
+ name in varchar2)
+ return char;
+
+ function index_exists (
+ name in varchar2)
+ return char;
+
+ function foreign_key_exists (
+ table_name IN varchar2,
+ column IN varchar2,
+ reftable IN varchar2,
+ refcolumn IN varchar2)
+ return char;
+
+ function unique_exists (
+ table_name IN varchar2,
+ column IN varchar2,
+ single_p IN boolean default true)
+ return char;
+
+ function primary_key_exists (
+ table_name IN varchar2,
+ column IN varchar2,
+ single_p IN boolean default true)
+ return char;
+
+ function not_null_exists (
+ table_name IN varchar2,
+ column IN varchar2)
+ return char;
+
+ function get_default (
+ table_name IN varchar2,
+ column IN varchar2)
+ return LONG;
+
+ function get_primary_keys (
+ table_name IN varchar2)
+ return t_util_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 char
+ as
+ v_exists char;
+
+ begin
+
+ select decode(count(*),0,'f','t') into v_exists
+ from user_tables where table_name = upper(table_exists.name);
+
+ return v_exists;
+
+ END table_exists;
+
+ function table_column_exists (
+ table_name IN varchar2,
+ column IN varchar2)
+ return char
+ as
+ v_exists char;
+
+ begin
+
+ select decode(count(*),0,'f','t') into v_exists
+ from user_tab_columns
+ where table_name = upper(table_column_exists.table_name)
+ and column_name = upper(table_column_exists.column);
+
+ return v_exists;
+
+ END table_column_exists;
+
+ function view_exists (
+ name IN varchar2)
+ return char
+ as
+ v_exists char;
+
+ begin
+
+ select decode(count(*),0,'f','t') into v_exists
+ from user_views where view_name = upper(view_exists.name);
+
+ return v_exists;
+
+ END view_exists;
+
+ function index_exists (
+ name IN varchar2)
+ return char
+ as
+ v_exists char;
+
+ begin
+ select decode(count(*),0,'f','t') into v_exists
+ from user_indexes where index_name = upper(index_exists.name);
+
+ return v_exists;
+ END index_exists;
+
+ function foreign_key_exists (
+ table_name IN varchar2,
+ column IN varchar2,
+ reftable IN varchar2,
+ refcolumn IN varchar2)
+ return char
+ as
+ v_exists char;
+
+ begin
+ select decode(count(*),0,'f','t') into v_exists
+ 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 = upper(foreign_key_exists.table_name)
+ and cols.column_name = upper(foreign_key_exists.column)
+ and cons_r.table_name = upper(foreign_key_exists.reftable)
+ and cols_r.column_name = upper(foreign_key_exists.refcolumn);
+
+ return v_exists;
+ end foreign_key_exists;
+
+ function unique_exists (
+ table_name IN varchar2,
+ column IN varchar2,
+ single_p IN boolean default true)
+ return char
+ as
+ v_exists char;
+ v_single integer;
+
+ begin
+ v_single := case when unique_exists.single_p then 1 else 0 end;
+
+ select decode(count(*),0,'f','t') into v_exists
+ 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 = upper(unique_exists.table_name)
+ and cc.column_name = upper(unique_exists.column)
+ and ((v_single = 0) or (
+ select count(*) from all_cons_columns
+ where constraint_name = c.constraint_name) = 1);
+
+ return v_exists;
+ END unique_exists;
+
+ function primary_key_exists (
+ table_name IN varchar2,
+ column IN varchar2,
+ single_p IN boolean default true)
+ return char
+ as
+ v_exists char;
+ v_single integer;
+
+ begin
+ v_single := case when primary_key_exists.single_p then 1 else 0 end;
+
+ select decode(count(*),0,'f','t') into v_exists
+ 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 = upper(primary_key_exists.table_name)
+ and cc.column_name = upper(primary_key_exists.column)
+ and ((v_single = 0) or (
+ select count(*) from all_cons_columns
+ where constraint_name = c.constraint_name
+ and owner = c.owner) = 1);
+
+ return v_exists;
+ END primary_key_exists;
+
+ function not_null_exists (
+ table_name IN varchar2,
+ column IN varchar2)
+ return char
+ as
+ v_exists char;
+
+ begin
+ select decode(count(*),0,'f','t') into v_exists
+ from all_tab_columns
+ where table_name = upper(not_null_exists.table_name)
+ and column_name = upper(not_null_exists.column)
+ and nullable = 'N';
+
+ return v_exists;
+ END not_null_exists;
+
+ function get_default (
+ table_name in varchar2,
+ column in varchar2)
+ return long
+ as
+ v_value long;
+
+ begin
+ select data_default into v_value
+ from all_tab_columns
+ where table_name = upper(get_default.table_name)
+ and column_name = upper(get_default.column);
+
+ return v_value;
+
+ exception when no_data_found then
+ return null;
+ end get_default;
+
+ function get_primary_keys(
+ table_name in varchar2)
+ return t_util_primary_keys
+ as
+ v_ret t_util_primary_keys;
+
+ begin
+ select cols.column_name
+ bulk collect into v_ret
+ from all_constraints cons, all_cons_columns cols
+ where cols.table_name = upper(get_primary_keys.table_name)
+ 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 v_ret;
+ 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.13.2.3 -r1.13.2.4
--- openacs-4/packages/acs-kernel/sql/postgresql/utilities-create.sql 11 May 2020 19:10:38 -0000 1.13.2.3
+++ openacs-4/packages/acs-kernel/sql/postgresql/utilities-create.sql 23 Feb 2022 18:24:53 -0000 1.13.2.4
@@ -25,11 +25,11 @@
v_rec record;
BEGIN
for counter in 1..v_count loop
- for v_rec in EXECUTE 'select ' || quote_ident(v_sequence_name) || '.nextval as a_seq_val'
- LOOP
- a_sequence_values := a_sequence_values || '','' || v_rec.a_seq_val;
- exit;
- end loop;
+ for v_rec in EXECUTE 'select ' || quote_ident(v_sequence_name) || '.nextval as a_seq_val'
+ LOOP
+ a_sequence_values := a_sequence_values || '','' || v_rec.a_seq_val;
+ exit;
+ end loop;
end loop;
return substr(a_sequence_values, 2);
@@ -47,15 +47,15 @@
--
CREATE OR REPLACE FUNCTION util__logical_negation(
true_or_false boolean
-) RETURNS boolean AS $$
-DECLARE
+) RETURNS boolean
+AS $$
BEGIN
IF true_or_false is null THEN
- return null;
+ return null;
ELSE IF true_or_false = 'f' THEN
- return 't';
+ return 't';
ELSE
- return 'f';
+ return 'f';
END IF; END IF;
END;
$$ LANGUAGE plpgsql immutable strict;
@@ -69,68 +69,68 @@
--
CREATE OR REPLACE FUNCTION util__table_exists(
name text
-) RETURNS boolean AS $$
+) RETURNS boolean
+AS $$
DECLARE
v_schema varchar;
v_tablename varchar;
BEGIN
IF (position('.' in name) = 0) THEN
- --
- -- table without a schema name
- --
- return exists (
- select 1 from pg_class
- where relname = name
- and pg_table_is_visible(oid));
+ --
+ -- table without a schema name
+ --
+ return exists (
+ select 1 from pg_class
+ where relname = name
+ and pg_table_is_visible(oid));
ELSE
- --
- -- table with schema name
- --
- SELECT split_part(name, '.', 1) into v_schema;
- SELECT split_part(name, '.', 2) into v_tablename;
- return exists (
- select 1 from information_schema.tables
- where table_schema = v_schema
- and table_name = v_tablename);
+ --
+ -- table with schema name
+ --
+ SELECT split_part(name, '.', 1) into v_schema;
+ SELECT split_part(name, '.', 2) into v_tablename;
+ return exists (
+ select 1 from information_schema.tables
+ where table_schema = v_schema
+ and table_name = v_tablename);
END IF;
END;
$$ LANGUAGE plpgsql;
-
-
--
--- procedure util__table_column_exists/1
+-- procedure util__table_column_exists/2
--
-select define_function_args('util__table_column_exists','p_table,p_column');
+select define_function_args('util__table_column_exists','table_name,column');
CREATE OR REPLACE FUNCTION util__table_column_exists(
- p_table text,
+ p_table_name text,
p_column text
-) RETURNS boolean AS $$
+) RETURNS boolean
+AS $$
DECLARE
v_schema varchar;
v_tablename varchar;
BEGIN
- IF (position('.' in p_table) = 0) THEN
- --
- -- table without a schema name
- --
- return exists (
- select 1 from information_schema.columns c
- where table_name = lower(p_table)
- and column_name = lower(p_column));
+ IF (position('.' in p_table_name) = 0) THEN
+ --
+ -- table without a schema name
+ --
+ return exists (
+ select 1 from information_schema.columns c
+ where table_name = lower(p_table_name)
+ and column_name = lower(p_column));
ELSE
- --
- -- table with schema name
- --
- SELECT split_part(p_table, '.', 1) into v_schema;
- SELECT split_part(p_table, '.', 2) into v_tablename;
- return exists (
- select 1 from information_schema.columns
- where table_name = lower(v_tablename)
- and column_name = lower(p_column)
- and table_schema = v_schema);
+ --
+ -- table with schema name
+ --
+ SELECT split_part(p_table_name, '.', 1) into v_schema;
+ SELECT split_part(p_table_name, '.', 2) into v_tablename;
+ return exists (
+ select 1 from information_schema.columns
+ where p_table_name = lower(v_tablename)
+ and column_name = lower(p_column)
+ and table_schema = v_schema);
END IF;
END;
$$ LANGUAGE plpgsql;
@@ -144,27 +144,28 @@
--
CREATE OR REPLACE FUNCTION util__view_exists(
name text
-) RETURNS boolean AS $$
+) RETURNS boolean
+AS $$
DECLARE
v_schema varchar;
v_tablename varchar;
BEGIN
IF (position('.' in name) = 0) THEN
- --
- -- view without a schema name
- --
- return exists (
- select 1 from pg_views where viewname = name);
+ --
+ -- view without a schema name
+ --
+ return exists (
+ select 1 from pg_views where viewname = name);
ELSE
- --
- -- table with schema name
- --
- SELECT split_part(name, '.', 1) into v_schema;
- SELECT split_part(name, '.', 2) into v_tablename;
- return exists (
- select 1 from information_schema.views
- where table_name = lower(v_tablename)
- and table_schema = v_schema);
+ --
+ -- table with schema name
+ --
+ SELECT split_part(name, '.', 1) into v_schema;
+ SELECT split_part(name, '.', 2) into v_tablename;
+ return exists (
+ select 1 from information_schema.views
+ where table_name = lower(v_tablename)
+ and table_schema = v_schema);
END IF;
END;
$$ LANGUAGE plpgsql;
@@ -179,48 +180,47 @@
CREATE OR REPLACE FUNCTION util__index_exists(
name text
) RETURNS boolean AS $$
-DECLARE
BEGIN
return exists (
select 1 from pg_indexes where indexname = name);
END;
$$ LANGUAGE plpgsql;
--- added
-select define_function_args('util__foreign_key_exists','table,column,reftable,refcolumn');
--
-- procedure util__foreign_key_exists/4
--
+select define_function_args('util__foreign_key_exists','table_name,column,reftable,refcolumn');
+
CREATE OR REPLACE FUNCTION util__foreign_key_exists(
- p_table text,
+ p_table_name text,
p_column text,
p_reftable text,
p_refcolumn text
-) RETURNS boolean AS $$
-DECLARE
+) RETURNS boolean
+AS $$
BEGIN
return exists (
select 1 from
- information_schema.table_constraints AS tc,
- information_schema.key_column_usage AS kcu,
- information_schema.constraint_column_usage AS ccu
+ 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 = lower(p_table)
- and kcu.column_name = lower(p_column)
- and ccu.table_name = lower(p_reftable)
- and ccu.column_name = lower(p_refcolumn));
+ 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 = lower(p_table_name)
+ and kcu.column_name = lower(p_column)
+ and ccu.table_name = lower(p_reftable)
+ and ccu.column_name = lower(p_refcolumn));
END;
$$ LANGUAGE plpgsql;
@@ -234,119 +234,118 @@
p_table text,
p_column text,
p_single_p boolean default true
-) RETURNS boolean AS $$
-DECLARE
+) RETURNS boolean
+AS $$
BEGIN
return exists (select 1
from
- information_schema.table_constraints AS tc,
- information_schema.key_column_usage AS kcu
+ information_schema.table_constraints AS tc,
+ information_schema.key_column_usage AS kcu
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 tc.constraint_type = 'UNIQUE'
- and tc.table_name = lower(p_table)
- and kcu.column_name = lower(p_column)
- and (not p_single_p or (
- -- this to ensure the constraint involves only one
- -- column
- select count(*) from information_schema.key_column_usage
- where constraint_name = kcu.constraint_name
- and constraint_catalog = kcu.constraint_catalog
- and constraint_schema = kcu.constraint_schema) = 1));
+ 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 tc.constraint_type = 'UNIQUE'
+ and tc.table_name = lower(p_table)
+ and kcu.column_name = lower(p_column)
+ and (not p_single_p or (
+ -- this to ensure the constraint involves only one
+ -- column
+ select count(*) from information_schema.key_column_usage
+ where constraint_name = kcu.constraint_name
+ and constraint_catalog = kcu.constraint_catalog
+ and constraint_schema = kcu.constraint_schema) = 1));
END;
$$ LANGUAGE plpgsql;
--- added
-select define_function_args('util__primary_key_exists','table,column,single_p;true');
-
--
--- procedure util__unique_exists/3
+-- procedure primary_key_exists/3
--
+select define_function_args('util__primary_key_exists','table_name,column,single_p;true');
+
CREATE OR REPLACE FUNCTION util__primary_key_exists(
- p_table text,
- p_column text,
- p_single_p boolean default true
-) RETURNS boolean AS $$
-DECLARE
+ p_table_name text,
+ p_column text,
+ p_single_p boolean default true
+) RETURNS boolean
+AS $$
BEGIN
return exists (select 1
from
- information_schema.table_constraints AS tc,
- information_schema.key_column_usage AS kcu
+ information_schema.table_constraints AS tc,
+ information_schema.key_column_usage AS kcu
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 tc.constraint_type = 'PRIMARY KEY'
- and tc.table_name = lower(p_table)
- and kcu.column_name = lower(p_column)
- and (not p_single_p or (
- -- this to ensure the constraint involves only one
- -- column
- select count(*) from information_schema.key_column_usage
- where constraint_name = kcu.constraint_name
- and constraint_catalog = kcu.constraint_catalog
- and constraint_schema = kcu.constraint_schema) = 1));
+ 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 tc.constraint_type = 'PRIMARY KEY'
+ and tc.table_name = lower(p_table_name)
+ and kcu.column_name = lower(p_column)
+ and (not p_single_p or (
+ -- this to ensure the constraint involves only one
+ -- column
+ select count(*) from information_schema.key_column_usage
+ where constraint_name = kcu.constraint_name
+ and constraint_catalog = kcu.constraint_catalog
+ and constraint_schema = kcu.constraint_schema) = 1));
END;
$$ LANGUAGE plpgsql;
--- added
-select define_function_args('util__not_null_exists','table,column');
-
--
-- procedure util__not_null_exists/2
--
+select define_function_args('util__not_null_exists','table_name,column');
+
CREATE OR REPLACE FUNCTION util__not_null_exists(
- p_table text,
+ p_table_name text,
p_column text
-) RETURNS boolean AS $$
-DECLARE
+) RETURNS boolean
+AS $$
BEGIN
return (
- coalesce((
- select is_nullable = 'NO'
- from information_schema.columns
- where table_name = lower(p_table)
- and column_name = lower(p_column)), false));
+ coalesce((
+ select is_nullable = 'NO'
+ from information_schema.columns
+ where table_name = lower(p_table_name)
+ and column_name = lower(p_column)), false));
END;
$$ LANGUAGE plpgsql;
--- added
-select define_function_args('util__get_default','table,column');
-
--
-- procedure util__get_default/2
--
+select define_function_args('util__get_default','table_name,column');
+
CREATE OR REPLACE FUNCTION util__get_default(
- p_table text,
+ p_table_name text,
p_column text
-) RETURNS information_schema.columns.column_default%TYPE AS $$
-DECLARE
+) RETURNS information_schema.columns.column_default%TYPE
+AS $$
BEGIN
return (
- select column_default
- from information_schema.columns
- where table_name = lower(p_table)
- and column_name = lower(p_column));
+ select column_default
+ from information_schema.columns
+ where table_name = lower(p_table_name)
+ and column_name = lower(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(text) RETURNS SETOF pg_attribute.attname%TYPE AS $$
+select define_function_args('util__get_primary_keys','table_name');
+
+CREATE OR REPLACE FUNCTION util__get_primary_keys(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)
+ AND a.attnum = ANY(i.indkey)
WHERE i.indrelid = $1::regclass
AND i.indisprimary;
$$ LANGUAGE sql;
Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.10.1d6-5.10.1d7.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/Attic/upgrade-5.10.1d6-5.10.1d7.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.10.1d6-5.10.1d7.sql 23 Feb 2022 18:24:53 -0000 1.1.2.1
@@ -0,0 +1,167 @@
+--
+-- since argument names change, we have to drop + recreate
+--
+DROP FUNCTION util__table_column_exists(text, text);
+
+--
+-- procedure util__table_column_exists/2
+--
+select define_function_args('util__table_column_exists','table_name,column');
+
+CREATE OR REPLACE FUNCTION util__table_column_exists(
+ p_table_name text,
+ p_column text
+) RETURNS boolean
+AS $$
+DECLARE
+ v_schema varchar;
+ v_tablename varchar;
+BEGIN
+ IF (position('.' in p_table_name) = 0) THEN
+ --
+ -- table without a schema name
+ --
+ return exists (
+ select 1 from information_schema.columns c
+ where table_name = lower(p_table_name)
+ and column_name = lower(p_column));
+ ELSE
+ --
+ -- table with schema name
+ --
+ SELECT split_part(p_table_name, '.', 1) into v_schema;
+ SELECT split_part(p_table_name, '.', 2) into v_tablename;
+ return exists (
+ select 1 from information_schema.columns
+ where p_table_name = lower(v_tablename)
+ and column_name = lower(p_column)
+ and table_schema = v_schema);
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+
+
+DROP FUNCTION util__get_default(text, text);
+select define_function_args('util__get_default','table_name,column');
+
+CREATE OR REPLACE FUNCTION util__get_default(
+ p_table_name text,
+ p_column text
+) RETURNS information_schema.columns.column_default%TYPE
+AS $$
+BEGIN
+ return (
+ select column_default
+ from information_schema.columns
+ where table_name = lower(p_table_name)
+ and column_name = lower(p_column));
+END;
+$$ LANGUAGE plpgsql;
+
+--
+-- procedure util__get_primary_keys/1
+--
+select define_function_args('util__get_primary_keys','table_name');
+
+
+
+--
+-- procedure util__foreign_key_exists/4
+--
+DROP FUNCTION util__foreign_key_exists(text,text,text,text);
+select define_function_args('util__foreign_key_exists','table_name,column,reftable,refcolumn');
+
+CREATE OR REPLACE FUNCTION util__foreign_key_exists(
+ p_table_name text,
+ p_column text,
+ p_reftable text,
+ p_refcolumn text
+) RETURNS boolean
+AS $$
+BEGIN
+ return exists (
+ select 1 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 = lower(p_table_name)
+ and kcu.column_name = lower(p_column)
+ and ccu.table_name = lower(p_reftable)
+ and ccu.column_name = lower(p_refcolumn));
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+
+--
+-- procedure util__not_null_exists/2
+--
+DROP FUNCTION util__not_null_exists(text,text);
+
+select define_function_args('util__not_null_exists','table_name,column');
+
+CREATE OR REPLACE FUNCTION util__not_null_exists(
+ p_table_name text,
+ p_column text
+) RETURNS boolean
+AS $$
+DECLARE
+BEGIN
+ return (
+ coalesce((
+ select is_nullable = 'NO'
+ from information_schema.columns
+ where table_name = lower(p_table_name)
+ and column_name = lower(p_column)), false));
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+--
+-- procedure primary_key_exists/3
+--
+DROP FUNCTION util__primary_key_exists(text,text,boolean);
+select define_function_args('util__primary_key_exists','table_name,column,single_p;true');
+
+CREATE OR REPLACE FUNCTION util__primary_key_exists(
+ p_table_name text,
+ p_column text,
+ p_single_p boolean default true
+) RETURNS boolean
+AS $$
+DECLARE
+BEGIN
+ return exists (select 1
+ from
+ information_schema.table_constraints AS tc,
+ information_schema.key_column_usage AS kcu
+ 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 tc.constraint_type = 'PRIMARY KEY'
+ and tc.table_name = lower(p_table_name)
+ and kcu.column_name = lower(p_column)
+ and (not p_single_p or (
+ -- this to ensure the constraint involves only one
+ -- column
+ select count(*) from information_schema.key_column_usage
+ where constraint_name = kcu.constraint_name
+ and constraint_catalog = kcu.constraint_catalog
+ and constraint_schema = kcu.constraint_schema) = 1));
+END;
+$$ LANGUAGE plpgsql;