Issue Details (XML | Word | Printable)

Key: CORE-770
Type: Improvement Improvement
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Claudio Valderrama C.
Reporter: Pavel Cisar
Votes: 1
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

More information about dependencies is needed

Created: 17/Sep/03 12:00 AM   Updated: 23/Feb/11 10:22 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 2.1 RC1

Time Tracking:
Not Specified

Issue Links:
Depend
 
Relate
 

SF_ID: 807869


 Description  « Hide
SFID: 807869#
Submitted By: pcisar

I've logged this issue as a request for enhancement and
I hope others will append what they think IB needs
about keeping dependencies.

Following with my example script described in:
[Bug #125219] isql -a: wrong order with domains based
on table's fields
I decided to drop the procedure named "valid16" that
handles the validation for the domain "dom16". No
problem. IB destroyed it. I still was able to select
from tab16 (the one that uses the domain and it's used
by the domain at the same time), since the proc was
loaded in memory. As soon as I disconnected and
reconnected, doing a simple
select * from tab16
caused the following (expected) message:
Invalid request BLR at offset 8
procedure id 28 is not defined
   I had to drop the constraint from the domain for IB
to allow me to define the proc again (or it kept
complaining about id 28), then I recreated the check
constraint.

IB keeps track of table2table dependencies through PKs
in rdb$ref_constraints. Procedures, views and triggers
that depends on tables are tracked at rdb$dependencies
and it tracks also procedure2procedure and
trigger2procedure dependencies.

What's missing, among others dependencies:
- domain using procedure
- domain using table
=> These two cases are legal despite what docos say.
- domain using generator
- domain using view
=> Subtle consequence of above findings.
- computed field using table
- computed field using procedure
- computed field using generator
- computed field using view
=> when I deleted a proc that was serving a computed
field and tried to do a SELECT from the affected table,
I got:
Invalid request BLR at offset 26
procedure <proc_name> is not defined
Interesting: in the case of a computed field, the
binding seems to be by name and not by proc number as
in a constraint, since I only defined again the proc
and it was ready, no need to drop and recreate the
computed field.
- procedure using generator
- trigger using generator
- view using generator

C.

----------------------
User: robocop
Logged In: YES
user_id=62823

Procedures using generators and UDFs are tracked now in
rdb$dependencies.

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Alice F. Bird added a comment - 14/Jun/06 09:42 AM
Date: 2006-01-17 14:06
Sender: dimitr
Logged In: YES
user_id=61270

Claudio, could you please post an update what's still not
tracked?

Claudio Valderrama C. added a comment - 13/Jan/08 02:51 AM
1.- Domain depends on table. Solved. See CORE-282.

SQL> create database 'depends.fdb';

2.- Domain depends on procedure. Solved. See here:
SQL> set term ^;
SQL> create procedure pd returns(rc int) as begin rc = 0; suspend; end^
SQL> create domain dd int check(value in (select rc from pd))^
SQL> drop procedure pd^
Statement failed, SQLCODE = -607
unsuccessful metadata update
-cannot delete
-PROCEDURE PD
-there are 1 dependencies

3.- Domain depends on view. Solved. See here:
SQL> create table t(a int)^
SQL> create view v as select a from t^
SQL> create domain d2 int check(value between (select first 1 a from v) and 0)^
SQL> drop view v^
Statement failed, SQLCODE = -607
unsuccessful metadata update
-cannot delete
-COLUMN A
-there are 1 dependencies
SQL>

4.- Domain depends on generator. Solved. See here:
SQL> create generator g^
SQL> create domain dg bigint not null check (value > next value for g)^
SQL> drop generator g^
Statement failed, SQLCODE = -607
unsuccessful metadata update
-cannot delete
-GENERATOR G
-there are 1 dependencies

Let's continue in another database to avoid name clashes:
SQL> create database 'depends2.fdb'^
Commit current transaction (y/n)?y
Committing.

5.- Computed field using table. Solved. See here:
SQL> create table t(a int)^
SQL> create table tc(b int, c computed by((select max(1) from t)))^
SQL> drop table t^
Statement failed, SQLCODE = -607
unsuccessful metadata update
-cannot delete
-TABLE T
-there are 1 dependencies

6.- Computed field using procedure. Solved. See here:
SQL> create procedure p returns(rc int) as begin rc = 1; suspend; end^
SQL> create table tp(a int, b computed by((select avg(rc) from p)))^
SQL> drop procedure p^
Statement failed, SQLCODE = -607
unsuccessful metadata update
-cannot delete
-PROCEDURE P
-there are 1 dependencies

7.- Computed field using view. Solved. See here:
SQL> create view v as select a from t^
SQL> create table tv(a int, b computed by((select min(a) from v)))^
SQL> drop view v^
Statement failed, SQLCODE = -607
unsuccessful metadata update
-cannot delete
-COLUMN A
-there are 1 dependencies

8.- Computed field using generator. Solved. See here:
SQL> create generator g^
SQL> create table tg(a int, b computed by(next value for g))^
SQL> drop generator g^
Statement failed, SQLCODE = -607
unsuccessful metadata update
-cannot delete
-GENERATOR G
-there are 1 dependencies

Again, avoiding name clashes:
SQL> create database 'depends3.fdb'^
Commit current transaction (y/n)?y
Committing.
SQL>

SQL> create generator g1^
SQL> create generator g2^
SQL> create generator g3^

9.- Procedure using generator. Solved. See here:
SQL> create procedure p as declare g int; begin g = next value for g1; end^
SQL> drop generator g1^
Statement failed, SQLCODE = -607
unsuccessful metadata update
-cannot delete
-GENERATOR G1
-there are 1 dependencies

10.- Trigger using generator. Solved. See here:
SQL> create table t(a int)^
SQL> create trigger tai for t after insert as declare dummy bigint; begin dummy = next value for g2; end^
SQL> drop generator g2^
Statement failed, SQLCODE = -607
unsuccessful metadata update
-cannot delete
-GENERATOR G2
-there are 1 dependencies

11.- View using generator in SELECT part. Solved. See here:
SQL> create view v as select a, next value for g3 as b from t^
SQL> drop generator g3^
Statement failed, SQLCODE = -607
unsuccessful metadata update
-cannot delete
-GENERATOR G3
-there are 1 dependencies

12.- View using generator in WHERE clause. Solved. See here:
SQL> create generator g4^
SQL> create view v2 as select a from t where 1 < next value for g4 with check option^
SQL> drop generator g4^
Statement failed, SQLCODE = -607
unsuccessful metadata update
-cannot delete
-GENERATOR G4
-there are 1 dependencies

13.- Views, triggers, computed fields and domains using udfs are working as expected. However, the message is wrong. See here:
SQL> declare external function getExactTimestamp
CON> timestamp
CON> returns parameter 1
CON> entry_point 'getExactTimestamp' module_name 'fbudf'^
SQL> create trigger tad for t after delete as declare dummy timestamp; begin dummy = getexacttimestamp(); end^
SQL> create view vudf(t) as select getexacttimestamp() from rdb$database^
SQL> create table tudf(a int, c computed by(getexacttimestamp()))^
SQL> create domain dud int check(value between extract(week from getexacttimestamp()) and 25)^
SQL> drop external function getexacttimestamp^
Statement failed, SQLCODE = -607
unsuccessful metadata update
-cannot delete
-UDF GETEXACTTIMESTAMP
-there are 1 dependencies
SQL> set term ;^
SQL> drop view vudf;
SQL> drop external function getexacttimestamp;
Statement failed, SQLCODE = -607
unsuccessful metadata update
-cannot delete
-UDF GETEXACTTIMESTAMP
-there are 1 dependencies
SQL> drop trigger tad;
SQL> drop external function getexacttimestamp;
Statement failed, SQLCODE = -607
unsuccessful metadata update
-cannot delete
-UDF GETEXACTTIMESTAMP
-there are 1 dependencies
SQL> drop table tudf;
SQL> drop external function getexacttimestamp;
Statement failed, SQLCODE = -607
unsuccessful metadata update
-cannot delete
-UDF GETEXACTTIMESTAMP
-there are 1 dependencies
SQL> drop domain dud;
SQL> drop external function getexacttimestamp;
SQL> Z

Usage of generators and udfs in procedures, views and triggers is mine.
Usage of tables, procedures, generators and views in computed fields may be mine, don't remember exactly.
Usage of tables, procedures, generators and views in domains (and generally speaking, everything that a domain may depend upon) is Adriano's work.

Will close this entry now. There are two issues only:
1.- Request that Jira allows to have more than one assignee to an item. Someone take care to contact Atlassian, please.
2.- Dependency tracking for UDFs is correct although the error message always says 1 dependencies. This may be a new, low priority item.