Re: Using the catalogue

amado.alves_at_netcabo.pt wrote:
> > > all triplets (field name, value, relation), from any relation, such
> > > that field name is in {Name, Designation}, field type is String, and
> > > field value = "King"
>
> > It seems that two relational operators would be sufficient. One to
> > return the catalogue and one to "evaluate" a union based on some
> > restriction of a catalogue relvar. A D language assumes an open ended
> > set of user-defined operators and types so I don't see why this should
> > be forbidden in Tutorial D for example.
> >
> > SQL has the catalogue but the "evaluation" part isn't in standard SQL
> > AFAIK. However, all SQL products that I'm familiar with implement some
> > form of dynamic SQL as an extension - that should be up to the task.
>
> I see.
>
> And the easiest way I envisage to endow a traditional language with the
> capability is to extend the language with two things
>
> (1) means to create strings by concatenating any part of the result of
> any query
>
> (2) an interpreter function
>
> I think many SQLs already have (1), and as for (2) clearly the DBMS has
> an SQL interpreter inside so it is just a matter of making it available
> in the language (as some programming languages do e.g. Clipper,
> Snobol).
>
> Ok, so I guess my question is not one of possible vs. impossible
> anymore but of "how well"---which is a very much harder question to
> answer altogether, so let me start again:
>
> (a) is this an interesting problem at all?
>
> (b) does it occur in the real world?

How about looking into the problem from the other end? Wouldn't having a single EAV table as RDBMS physical implementation make it easier? The catalog queries:

select distinct tableName from EAV -- get all the tables

select distinct tableName, columnName from EAV where tableName = 'Employees' -- get the Employees table schema

select distinct tableName, columnName from EAV where value = 'King' -- accomodates blind search easily

• Pivot the data to get the Employee relation: select min(case when columnName='NAME' then value else null end) as name, min(case when columnName='SAL' then TO_INTEGER(value) else null end) as sal, min(case when columnName='HIRED' then TO_DATE(value) else null end) as hired from EAV where tableName = 'Employees' group by rowId

The tricky part is allowing (composite) indexes and materialized views on (pivoted) relations. Received on Wed Nov 16 2005 - 23:01:23 CET