Views and packages

[Claudio Valderrama C.]

People:
as part of the merge, an ambiguity has surfaced due to the introduction of
packages. Assuming views based on procedures is a working feature,
considering that now a procedure can live in the global namespace or inside
a package, it's impossible to determine accurately whether the source for a
view's field lies in a global or "packaged" procedure.

Personally, I think that packages are a redundant feature compared to
schemas but who am I to reject them.

Since we have a problem and we have to solve it, I'll explaint it and offer
a solution that needs (unfortunately) more code to handle it and revisiting
code here and there. The problem is that, given a view's field, in some
cases it's necessary to find the source (the field that originated it in the
query). This original field can be a physical field or another virtual field
(a view can be based on another view). So far so good, but let's introduce
procedures. If we don't find the field in rdb$relation_fields then it must
be located inside rdb$procedure_parameters or we raise an error. But
rdb$procedure_parameters holds now information about global and "packaged"
procedures. How can we tell? Currently we can't.

This is the table that lacks information:

SQL> show table rdb$view_relations;
RDB$VIEW_NAME                  
RDB$RELATION_NAME              
RDB$VIEW_CONTEXT                
RDB$CONTEXT_NAME            

We need to add two fields at least:
1.- A type to stop the guess. Type can be relation's field, procedure's
parameter, view's field or function's output param. For example, in
        create view v(res) as select myudf(z) from rdb$database;
clearly rdb$database is not the source for the field, but it's stored as the
context name. Maybe instead of function's result, the type can be
"expression", to cover all things that don't come from tables, views and
procedures (for example, a built-in function like current_user).

2.- A package name.

We need the code to populate and read these fields, of course.

I will leave to other developers to decide whether it's acceptable allowing
the engine guess the source field (in the case of procedures) or introduce
more code to handle the case accurately. I think the engine should be
accurate.

Example:

SQL> set term ^;
SQL> create procedure p returns(a int) as begin a = 3; suspend; end^
SQL> set term ;^
SQL> create view vp as select * from p;
SQL> commit;
SQL> select rdb$relation_name from rdb$view_relations
where rdb$view_name = 'VP';

RDB$RELATION_NAME
======================
P

C.
---
Claudio Valderrama C. - www.cvalde.net
Consultant, SW developer.



------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Martijn Tonies]

Hi,

> People:
> as part of the merge, an ambiguity has surfaced due to the introduction of
> packages. Assuming views based on procedures is a working feature,
> considering that now a procedure can live in the global namespace or
> inside
> a package, it's impossible to determine accurately whether the source for
> a
> view's field lies in a global or "packaged" procedure.
>
> Personally, I think that packages are a redundant feature compared to
> schemas but who am I to reject them.

The two are quite different.

> Since we have a problem and we have to solve it, I'll explaint it and
> offer
> a solution that needs (unfortunately) more code to handle it and
> revisiting
> code here and there. The problem is that, given a view's field, in some
> cases it's necessary to find the source (the field that originated it in
> the
> query). This original field can be a physical field or another virtual
> field
> (a view can be based on another view). So far so good, but let's introduce
> procedures. If we don't find the field in rdb$relation_fields then it must
> be located inside rdb$procedure_parameters or we raise an error. But
> rdb$procedure_parameters holds now information about global and "packaged"
> procedures. How can we tell? Currently we can't.

wouldn't rdb$procedure_parameters point to a procedure_id which can
be inside a package?

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com

> This is the table that lacks information:
>
> SQL> show table rdb$view_relations;
> RDB$VIEW_NAME
> RDB$RELATION_NAME
> RDB$VIEW_CONTEXT
> RDB$CONTEXT_NAME
>
> We need to add two fields at least:
> 1.- A type to stop the guess. Type can be relation's field, procedure's
> parameter, view's field or function's output param. For example, in
> create view v(res) as select myudf(z) from rdb$database;
> clearly rdb$database is not the source for the field, but it's stored as
> the
> context name. Maybe instead of function's result, the type can be
> "expression", to cover all things that don't come from tables, views and
> procedures (for example, a built-in function like current_user).
>
> 2.- A package name.
>
> We need the code to populate and read these fields, of course.
>
> I will leave to other developers to decide whether it's acceptable
> allowing
> the engine guess the source field (in the case of procedures) or introduce
> more code to handle the case accurately. I think the engine should be
> accurate.
>
> Example:
>
> SQL> set term ^;
> SQL> create procedure p returns(a int) as begin a = 3; suspend; end^
> SQL> set term ;^
> SQL> create view vp as select * from p;
> SQL> commit;
> SQL> select rdb$relation_name from rdb$view_relations
> where rdb$view_name = 'VP';
>
> RDB$RELATION_NAME
> ======================
> P
>
> C.
> ---
> Claudio Valderrama C. - www.cvalde.net
> Consultant, SW developer.
>
>
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008
> 30-Day
> trial. Simplify your report design, integration and deployment - and focus
> on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> Firebird-Devel mailing list, web interface at
> https://lists.sourceforge.net/lists/listinfo/firebird-devel 

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Dmitry Yemanov]

Claudio Valderrama C. wrote:
>
> as part of the merge, an ambiguity has surfaced due to the introduction of
> packages. Assuming views based on procedures is a working feature,
> considering that now a procedure can live in the global namespace or inside
> a package, it's impossible to determine accurately whether the source for a
> view's field lies in a global or "packaged" procedure.

AFAIU, you're not allowed (syntactically) to create a view based on
procedure from some package, neither you can create a view belonging to
some package. So if a view refers to a procedure P it always means a
global P, not one inside some package. I don't see any ambiguity here.

> This is the table that lacks information:
>
> SQL> show table rdb$view_relations;
> RDB$VIEW_NAME                  
> RDB$RELATION_NAME              
> RDB$VIEW_CONTEXT                
> RDB$CONTEXT_NAME            
>
> We need to add two fields at least:
> 1.- A type to stop the guess. Type can be relation's field, procedure's
> parameter, view's field or function's output param. For example, in
> create view v(res) as select myudf(z) from rdb$database;
> clearly rdb$database is not the source for the field, but it's stored as the
> context name. Maybe instead of function's result, the type can be
> "expression", to cover all things that don't come from tables, views and
> procedures (for example, a built-in function like current_user).

This table lists contexts, not fields. There cannot be such a context as
UDF or output parameter. A type field (relation vs procedure) could be
added, but I don't think it's really necessary as AFAIK there cannot be
a table and a procedure sharing the same name, so it's actually
"iteration" rather than "guessing".


Dmitry

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Leyne, Sean]

Dmitry,

> ... I don't think it's really necessary as AFAIK there cannot be
> a table and a procedure sharing the same name...

Doesn't this rule change as a result of the introduction of packages and schemas?

1 - the same table name could exist in 2 schemas, no?

2 - the same procedure could exist in 2 packages, no?


Sean


------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Dmitry Yemanov]

Leyne, Sean wrote:
>
> Doesn't this rule change as a result of the introduction of packages and schemas?
>
> 1 - the same table name could exist in 2 schemas, no?

Nobody has implemented schemas yet.

> 2 - the same procedure could exist in 2 packages, no?

Yes, this is possible. Both RDB$PROCEDURES and RDB$PROCEDURE_PARAMETERS
have RDB$PACKAGE_NAME inside. Let's imagine this:

create procedure PROC as ...

create package PKG as
...
   procedure PROC as ...
   procedure PROC2 as ... select * from PROC ...

What PROC (global or local) should PROC2 use here?

Adriano, does the package always have the priority in the name
resolution? If so, it's not an ambiguity but an intended behavior ;-)


Dmitry

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Adriano dos Santos Fernandes-3]

Dmitry Yemanov escreveu:

> Claudio Valderrama C. wrote:
>  
>> as part of the merge, an ambiguity has surfaced due to the introduction of
>> packages. Assuming views based on procedures is a working feature,
>> considering that now a procedure can live in the global namespace or inside
>> a package, it's impossible to determine accurately whether the source for a
>> view's field lies in a global or "packaged" procedure.
>>    
>
> AFAIU, you're not allowed (syntactically) to create a view based on
> procedure from some package,

That is allowed, so it seems at least PACKAGE_NAME should be added to
VIEW_RELATIONS.


Adriano


------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Dmitry Yemanov]

Adriano dos Santos Fernandes wrote:
>
>> AFAIU, you're not allowed (syntactically) to create a view based on
>> procedure from some package,
>
> That is allowed

"table_proc" includes "symbol_procedure_name" which is a one-component
name. What do I miss?


Dmitry

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Dmitry Yemanov]

Dmitry Yemanov wrote:
>
> "table_proc" includes "symbol_procedure_name" which is a one-component
> name. What do I miss?

Shame on me. I was looking at the v2.5 parser :-D


Dmitry

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Adriano dos Santos Fernandes-3]

Dmitry Yemanov escreveu:

>> 2 - the same procedure could exist in 2 packages, no?
>>    
>
> Yes, this is possible. Both RDB$PROCEDURES and RDB$PROCEDURE_PARAMETERS
> have RDB$PACKAGE_NAME inside. Let's imagine this:
>
> create procedure PROC as ...
>
> create package PKG as
> ...
>    procedure PROC as ...
>    procedure PROC2 as ... select * from PROC ...
>
> What PROC (global or local) should PROC2 use here?
>
> Adriano, does the package always have the priority in the name
> resolution?

Yes, in this case. I mean, if PROC is declared (in the package header)
or previously implemented (as private procedure), PROC2 looks for PKG.PROC.

But PROC may be implemented as private procedure after PROC2, and before
a PROC3. That make each one get a different PROC. I think this is an
ambiguity that should be prevented. If a package body uses the global
PROC, any PROC defined inside it should cause an error.


Adriano


------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Adriano dos Santos Fernandes-3]

Dmitry Yemanov escreveu:

> Adriano dos Santos Fernandes wrote:
>  
>>> AFAIU, you're not allowed (syntactically) to create a view based on
>>> procedure from some package,
>>>      
>> That is allowed
>>    
>
> "table_proc" includes "symbol_procedure_name" which is a one-component
> name. What do I miss?
>  

In HEAD it is:

table_proc
    : symbol_procedure_name table_proc_inputs as_noise
symbol_table_alias_name
        { $$ = make_node (nod_rel_proc_name, (int) e_rpn_count, $1, $4,
$2, NULL); }
    | symbol_procedure_name table_proc_inputs
        { $$ = make_node (nod_rel_proc_name, (int) e_rpn_count, $1,
NULL, $2, NULL); }
    | symbol_package_name '.' symbol_procedure_name table_proc_inputs
as_noise symbol_table_alias_name
        { $$ = make_node (nod_rel_proc_name, (int) e_rpn_count, $3, $6,
$4, $1); }
    | symbol_package_name '.' symbol_procedure_name table_proc_inputs
        { $$ = make_node (nod_rel_proc_name, (int) e_rpn_count, $3,
NULL, $4, $1); }
    ;


Adriano


------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel