Login Register Actian.com  

Actian Community Blogs

Rate this Entry

Checking the Existence of Database Assets

Submit "Checking the Existence of Database Assets" to Digg Submit "Checking the Existence of Database Assets" to del.icio.us Submit "Checking the Existence of Database Assets" to StumbleUpon Submit "Checking the Existence of Database Assets" to Google
Posted 2015-11-19 at 09:04 AM by rhann
Updated 2015-11-19 at 11:59 AM by rhann (Forgot to show the \macro command)

The Ingres tm terminal monitor—better known as sql—has a macro facility that provides, among other things, conditional execution.

The following example checks to see if the myproc procedure exists and if it does (tuplecount—that is, row count—is greater than 0), it skips everything between the \branch and the next-step label (\mark):
\macro
SELECT distinct procedure_name
FROM iiprocedures
WHERE procedure_name = 'myproc'
AND procedure_owner = dbmsinfo('username');
\p\g
\branch ?{ifgt;@{tuplecount};0;1;0}=1 next-step
-- code to skip if the procedure already exists
...
\mark next-step
I use this kind of branching a lot but I've become weary of writing the SCI queries for the different assets I want to enquire about. To make checking easier I've created a table procedure called exists that returns a single row if an asset exists and no rows if it doesn't.
* SELECT * FROM exists(procedure_name='myproc') \g
Executing . . .

+------+
|exists|
+------+
+------+
continue
* SELECT * FROM exists(table_name='customer') \g
Executing . . .

+------+
|exists|
+------+
+------+
continue
* SELECT * FROM exists(table_name='customer',schema_name='ingres') \g
Executing . . .

+------+
|exists|
+------+
|exists|
+------+
continue
Now I can rewrite the first example above more conveniently as:
\macro
SELECT * FROM exists(procedure_name='myproc');
\p\g
\branch ?{ifgt;@{tuplecount};0;1;0}=1 next-step
-- code to skip if the procedure already exists
...
\mark next-step
The code for the exists procedure follows:
Code:
-- create procedure to report whether a database asset exists
CREATE PROCEDURE exists
(
	IN table_name varchar(256) WITH NULL,
	IN view_name varchar(256) WITH NULL,
	IN index_name varchar(256) WITH NULL,
	IN synonym_name varchar(256) WITH NULL,
	IN procedure_name varchar(256) WITH NULL,
	IN rule_name varchar(256) WITH NULL,
	IN sequence_name varchar(256) WITH NULL,
	IN seq_name varchar(256) WITH NULL,
	IN schema_name varchar(256) WITH NULL
)
RESULT ROW exists ( exists varchar(6) NOT NULL )
AS
DECLARE
	argc integer NOT NULL;
	owner varchar(256) NOT NULL;
	is_extant integer NOT NULL;
BEGIN
	-- if no schema name is given use the implied schema
	IF schema_name IS NULL THEN
		owner=dbmsinfo('username');
	ELSE
		owner=schema_name;
	ENDIF;

	-- exit if more than one asset is named, ignoring schema_name 
	-- (NB we should RAISE an error but RPPs don't allow RAISE)
	argc = 0;
	IF table_name IS NOT NULL THEN argc = argc+1; ENDIF;
	IF view_name IS NOT NULL THEN argc = argc+1; ENDIF;
	IF index_name IS NOT NULL THEN argc = argc+1; ENDIF;
	IF synonym_name IS NOT NULL THEN argc = argc+1; ENDIF;
	IF procedure_name IS NOT NULL THEN argc = argc+1; ENDIF;
	IF rule_name IS NOT NULL THEN argc = argc+1; ENDIF;
	IF seq_name IS NOT NULL THEN argc = argc+1; ENDIF;
	IF sequence_name IS NOT NULL THEN argc = argc+1; ENDIF;
	IF argc > 1 THEN RETURN; ENDIF;

	-- allow sequence_name as a synonym for seq_name
	IF seq_name IS NULL THEN seq_name = sequence_name; ENDIF;

	IF table_name IS NOT NULL THEN
		SELECT any(table_name) INTO is_extant
		FROM iitables
		WHERE table_type ='T'
		  AND table_name = :table_name
		  AND table_owner = :owner;
	ELSEIF view_name IS NOT NULL THEN
		SELECT any(table_name) INTO is_extant
		FROM iitables
		WHERE table_type ='V'
		  AND table_name = :view_name
		  AND table_owner = :owner;
	ELSEIF index_name IS NOT NULL THEN
		SELECT any(index_name) INTO is_extant
		FROM iitables
		WHERE table_type ='I'
		  AND table_name = :index_name
		  AND table_owner = :owner;
	ELSEIF synonym_name IS NOT NULL THEN
		SELECT any(synonym_name) INTO is_extant
		FROM iisynonym
		WHERE synonym_name = :synonym_name
		  AND synonym_owner = :owner;
	ELSEIF procedure_name IS NOT NULL THEN
		SELECT any(procedure_name) INTO is_extant
		FROM iiprocedures
		WHERE procedure_name = :procedure_name
		  AND procedure_owner = :owner;
	ELSEIF rule_name IS NOT NULL THEN
		SELECT any(rule_name) INTO is_extant
		FROM iirules
		WHERE rule_name = :rule_name
		  AND rule_owner = :owner;
	ELSEIF sequence_name IS NOT NULL THEN
		SELECT any(seq_name) INTO is_extant
		FROM iisequence
		WHERE seq_name = :seq_name
		  AND seq_owner = :owner;
	ELSEIF schema_name IS NOT NULL THEN
		SELECT any(schema_name) INTO is_extant
		FROM iischema
		WHERE schema_name = :schema_name;
	ENDIF;

	IF is_extant = 1 THEN
		RETURN ROW ( 'exists' );
	ENDIF;
END;
\p\g

GRANT EXECUTE ON PROCEDURE exists TO PUBLIC;
\p\g

COMMIT;
\p\g
Now I just have to find an easier alternative to writing " ?{ifgt;@{tuplecount};0;1;0}=1"!
Posted in Ingres, Vectorwise
Views 1764 Comments 0 Edit Tags

« Prev     Main     Next »

Total Comments 0

Comments

 
© 2011 Actian Corporation. All Rights Reserved