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 -r1.136.2.24 -r1.136.2.25
--- openacs-4/packages/acs-kernel/acs-kernel.info 11 Nov 2016 00:09:20 -0000 1.136.2.24
+++ openacs-4/packages/acs-kernel/acs-kernel.info 20 Dec 2016 20:28:49 -0000 1.136.2.25
@@ -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 -r1.3 -r1.3.2.1
--- openacs-4/packages/acs-kernel/sql/oracle/utilities-create.sql 27 Oct 2014 16:39:35 -0000 1.3
+++ openacs-4/packages/acs-kernel/sql/oracle/utilities-create.sql 20 Dec 2016 20:28:49 -0000 1.3.2.1
@@ -35,6 +35,35 @@
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;
end util;
/
show errors
@@ -111,6 +140,93 @@
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;
+
end util;
/
show errors
Fisheye: Tag 1.1 refers to a dead (removed) revision in file `openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.9.1d18-5.9.1d19.sql'.
Fisheye: No comparison available. Pass `N' to diff?
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 -r1.8 -r1.8.2.1
--- openacs-4/packages/acs-kernel/sql/postgresql/utilities-create.sql 27 Oct 2014 16:39:36 -0000 1.8
+++ openacs-4/packages/acs-kernel/sql/postgresql/utilities-create.sql 20 Dec 2016 20:28:49 -0000 1.8.2.1
@@ -21,7 +21,7 @@
v_count integer
) RETURNS varchar AS $$
DECLARE
- a_sequence_values text default '';
+ a_sequence_values text default '';
v_rec record;
BEGIN
for counter in 1..v_count loop
@@ -33,7 +33,7 @@
end loop;
return substr(a_sequence_values, 2);
-
+
END;
$$ LANGUAGE plpgsql;
@@ -53,9 +53,9 @@
IF true_or_false is null THEN
return null;
ELSE IF true_or_false = 'f' THEN
- return 't';
- ELSE
- return 'f';
+ return 't';
+ ELSE
+ return 'f';
END IF; END IF;
END;
$$ LANGUAGE plpgsql immutable strict;
@@ -73,8 +73,8 @@
DECLARE
BEGIN
return exists (
- select 1 from pg_class
- where relname = name
+ select 1 from pg_class
+ where relname = name
and pg_table_is_visible(oid));
END;
$$ LANGUAGE plpgsql;
@@ -94,7 +94,7 @@
BEGIN
return exists (
select 1 from information_schema.columns c
- where c.table_name = t_name
+ where c.table_name = t_name
and c.column_name = c_name);
END;
$$ LANGUAGE plpgsql;
@@ -134,5 +134,151 @@
$$ LANGUAGE plpgsql;
+-- added
+select define_function_args('util__foreign_key_exists','table,column,reftable,refcolumn');
+--
+-- procedure util__foreign_key_exists/4
+--
+CREATE OR REPLACE FUNCTION util__foreign_key_exists(
+ p_table text,
+ p_column text,
+ p_reftable text,
+ p_refcolumn text
+) RETURNS boolean AS $$
+DECLARE
+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 = p_table
+ and kcu.column_name = p_column
+ and ccu.table_name = p_reftable
+ and ccu.column_name = p_refcolumn);
+END;
+$$ LANGUAGE plpgsql;
+-- added
+select define_function_args('util__unique_exists','table,column,single_p;true');
+
+--
+-- procedure util__unique_exists/3
+--
+CREATE OR REPLACE FUNCTION util__unique_exists(
+ p_table 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 = 'UNIQUE'
+ and tc.table_name = p_table
+ and kcu.column_name = 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
+--
+CREATE OR REPLACE FUNCTION util__primary_key_exists(
+ p_table 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 = p_table
+ and kcu.column_name = 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
+--
+CREATE OR REPLACE FUNCTION util__not_null_exists(
+ p_table text,
+ p_column text
+) RETURNS boolean AS $$
+DECLARE
+BEGIN
+ return (
+ coalesce((
+ select is_nullable = 'NO'
+ from information_schema.columns
+ where table_name = p_table
+ and column_name = p_column), false));
+END;
+$$ LANGUAGE plpgsql;
+
+-- added
+select define_function_args('util__get_default','table,column');
+
+--
+-- procedure util__get_default/2
+--
+CREATE OR REPLACE FUNCTION util__get_default(
+ p_table text,
+ p_column text
+) RETURNS information_schema.columns.column_default%TYPE AS $$
+DECLARE
+BEGIN
+ return (
+ select column_default
+ from information_schema.columns
+ where table_name = p_table
+ and column_name = p_column);
+END;
+$$ LANGUAGE plpgsql;
Fisheye: Tag 1.1 refers to a dead (removed) revision in file `openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d18-5.9.1d19.sql'.
Fisheye: No comparison available. Pass `N' to diff?