Re: Storing query language in relations

From: Paul Vernon <paul.vernon_at_ukk.ibmm.comm>
Date: Tue, 1 Oct 2002 19:12:06 +0100
Message-ID: <anconi$10l8$1_at_sp15at20.hursley.ibm.com>


>should it not be the other way round - at least on the logical level?
>i.e. have a view to display the non-scalar type as a single attribute
>but have it in relations in the database.

Remember the principle of interchangeability - views and relations are interchangeable.

In principle you can switch the set of views & relations that you consider to be the 'real' database at any given point in time.

>Well you might say: if all queries have to be stored in relations, how
>do you INSERT any data to begin with?

You might just as well ask, how do the system catalog tables get populated with the data that describes themselves?

It's interesting to note that most database designs consist entirely of empty relations. It seems to me that this is a massive restriction on the set of possible database designs. Many plausible constraints evaluate to false when a database is full only of empty relvars. I suggest that this is one reason why people find database design rather more difficult than needs be.

>But you wouldn't need SELECTs or DELETEs at all: just use the built-in
>"bootstrap" INSERT statement to put your SELECT or DELETE query into
>the appropriate relations and then maybe there would be a relation
>where you can enter the ID of a query and a time to execute it, and
>once this had been done the query would run.

Humm, I think you might have found a rather neat way of executing statements that are the result of generating statements. This is something that is difficult in SQL (well at least DB2).

E.g.

    SELECT 'DELETE FROM ' || TABNAME
    FROM SYSCAT.TABLES WHERE TABNAME LIKE '%TEMP%' If I had a pound for every time I'ld cut-and-pasted the output of a SQL statement to then run....

So if we had a 'meta relation' M that executes whatever value it is set to, then (mixing SQL and D syntax again) we could do

   M := SELECT CAST( TABNAME || ' := {}' AS Statement)     FROM SYSCAT.TABLES WHERE TABNAME LIKE '%TEMP%' Humm, I wonder if that is useful

>So does this mean for example that the generator knows the syntax
>for SQL statements, and when you feed it a string like this:
>
>"SELECT foo FROM bar WHERE foo > 1"
>
>it will "normalise" it and put it into the appropriate relations for
>you?

Yep. Well, the statment parts would be returned when you select from the appropriate relvars. If you choose to conceptualise those relvars as base (as apposed to virtual) then you could say that the parts have been 'put' there.

A good generator would create a single function (or maybe just a relational expression) to take all the normalised relations and return the statement string.
And more importantly it would create one function per normalised relation that accepts a statement string and returns the set of tuples relevant for that normalised relation. (Or, if we let functions return sets of relations, then we would need only one function)

Understand?

>The relational constraints would only need to be done once because you
>could store all your SQL statements in the same relations.

Agreed. The generator would build the constraints once, the constraints being exactly those that only allow grammatically valid SQL statements (or whoever's language BNF you fed in) to be assigned either to the single attribute relvar, or the 'normalised' relvars.

>For example you might want to know how many of your views use a
>certain relation or how often a particular column is used. Or
>changing a variable name.

Good point. In fact in the context of schema evolution this could be very valuable. Imagine a foreign key from the normalised 'query attribute name' relvar to the system catalog 'attributes' table. This (plus some more FKs, say to sysfunctions, systables, sysdatatypes,...) would then stop anyone altering the database schema in a way that would break any registered queries, unless those queries were modified or deleted at the same time.

Neat idea I reckon. Probably too good to be given away for free in this newsgroup. Oh well, I guess it's probably not original.

>So from this could you not argue that any DBMS that stores
>queries, view definitions, etc. in unnormalised text files
>without a corresponding relational interface is not a true
>RDMBS :)
I'd certainly argue that, a DBMS that stores more things relationally is better one that stores fewer things relationally (all else being equal).

I'd hope that even only this far into this thread, we have outlined the kind of benefits that can arise from expanding the scope of information that gets stored relationally in a RDBMS.

Just a shame that you can't add constraints onto any SQL system catalog (or is there a RDMBS that allows this??), otherwise even this example could have been half used today..

So next question. Anyone like to propose a mapping of a BNF grammar to a set of relvars and constraints?

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Tue Oct 01 2002 - 20:12:06 CEST

Original text of this message