Storing query language in relations

Storing query language in relations

Post by Pau » Tue, 24 Sep 2002 18:46:29



This is just an idea I've been having, I'm not sure if it's been
visited before or if it's not worth pursuing.

If we accept that a relational database is the most logical way to
hold structured data, why not go a step further and store the actual
queries in relational format? I know some DBMSs store the text of
queries, view definitions, stored procedures etc. in tables but not in
a normalised fashion.

An advantage would be that the DB constraints would automatically
validate the query so no need for parsing SQL code for syntax. Maybe
it could be used for storing other (procedural) languages as well like
C - it would be useful for large software projects - no need for loads
of files and subdirectories and client apps could quickly find
function definitions etc.

Wasn't one of Codd's guidelines that metadata should also be stored
relationally? I know the table definitions (DDL) are in many DBMSs.
But generally a separation is made between the database and its query
language (DML).

I've not thought too deeply about how a schema for SQL queries (for
example) could be implemented but it must be possible (kind of like
the MS Access query grid but normalised).

Is this taking things too far or might this line of thinking produce
something useful? Has this been done already in some DBMS?

Paul.

 
 
 

Storing query language in relations

Post by Nathan All » Wed, 25 Sep 2002 09:36:24



> If we accept that a relational database is the
> most logical way to
> hold structured data, why not go a step further
> and store the actual
> queries in relational format?

What is "unstructured" data anyway?!  :-)

Quote:> ...why not go a step further and store the actual
> queries in relational format?

Sure... but there is no reason you couldn't do a whole imperative
language including queries (expressions).  It would only be a matter
of having a table for each language element.  Something like this:

create table InsertStatement
{
  Statement_ID : StatementID,
  SourceExpression : ExpressionID,
  TargetExpression : ExpressionID,
  key { Statement_ID },
  ...

Quote:};

create table Expression
{
  ID : ExpressionID,
  ResultType : TypeID,
  ExpressionType : ExpressionType,
  key { ID }

Quote:};

create table UnaryOperatorExpression
{
  Expression_ID : ExpressionID,
  Operand : ExpressionID,
  key { Expression_ID },
  ...

Quote:};

create table IntegerLiteralExpression
{
  Expression_ID : ExpressionID,
  Value : Integer,
  key { Expression_ID },
  ...

Quote:};

Of course you would want to build some supporting operators:

create operator EvaluateExpression(AExpressionID : ExpressionID) :
Generic
...

What would also be an interesting exercise would be to model a word
processor!  So much for "unstructured" data!

--
Nathan Allan

 
 
 

Storing query language in relations

Post by Paul Verno » Wed, 25 Sep 2002 18:15:43


Paul. You are raising the question of when to model non-scalar types as
single attribute values and when to model them as relation values.

The cheating answer is to point out that in the RM you can have your cake
and eat it.
All you need do is to create a set of virtual relvars over your non-scalar
attribute.

Quote:> a relational database is the most logical way to
>hold structured data

I would rather say, 'the relational model is the most logical general way
to structure and query persistent data'.

Quote:>why not go a step further and store the actual
>queries in relational format?

Also be careful with the word 'store'. To many it will suggests physical
storage mechanisms. However, it is a good word, and I use it below but do
not mean to imply anything about physical storage

Quote:>I know some DBMSs store the text of queries, view definitions,
>stored procedures etc. in tables but not in a normalised fashion.
>An advantage would be that the DB constraints would automatically
>validate the query so no need for parsing SQL code for syntax.

The downside being that you would need some horrendous multiple relvar
assignments to enter a new query.

Can I suggest that a more pragmatic (and human friendly) idea would be to
recognise the need for a new non-scalar type generator. Such a generator
would take a BNF grammar and create a non-scalar 'language' type for that
grammar. It would also create a parser to take a text string and return a
value of the new type for all valid inputs.

If called from a Relation Type generator (i.e. CREATE TABLE, VALUES in
SQL), such a language generator could, if desired, produce virtual relvars
that normalise any non-scalar language attributes. Along with such virtual
relvars, a mass of relational constraints corresponding to the particular
BNF grammar would be generated, thereby ensuring that the relvars could
only be set to values corresponding to valid paragraphs of the language.

But I would ask why? What would we gain? The usual answer for normalising
non-scalar types is that we want to be able to ad-hoc query the components
of the non-scalar types using the expressive power of relational algebra.
As an example, breaking written language attributes into their consistence
parts (e.g. words) is a sensible step if you are building a simple text
search database. This would allow you to say find the set of sentences
with 6 or more words in common, using a relational query.

In the general case of languages, (e.g. SQL, C, relational algebra, etc) I
find it hard to conceive of a common need to relationally query their
components.  I guess that a DIFF function could be coded relationally, but
frankly I think that traversing parse trees is going to be easier.

Having said that I think it would be a good test of a true relational
language. If such a language Type generator was provided, it would show
that they were eating their own food.

Quote:>Wasn't one of Codd's guidelines that metadata should also be
> stored relationally?

Indeed. I don't have the quote to hand, but the upshot is that EVERYTHING
that is needed to be stored persistently, should be stored relationally.

Quote:>I know the table definitions (DDL)are
>in many DBMSs. But generally a separation is made between the
>database and its query language (DML).
>I've not thought too deeply about how a schema for SQL queries (for
>example) could be implemented but it must be possible (kind of like
>the MS Access query grid but normalised).

So the MS Access query grid can specify all possible relational
expressions? Humm. As above, I would suggest using the grammar for the
language as your schema.  * hey?

Quote:>Is this taking things too far or might this line of thinking
>produce something useful? Has this been done already in some
>DBMS?

As long as the ideas hold water, you have to go a long way to go to far in
a theory newsgroup.

:-)

Regards
Paul Vernon
Business Intelligence, IBM Global Services

 
 
 

Storing query language in relations

Post by Pau » Wed, 25 Sep 2002 19:42:54


Quote:> > If we accept that a relational database is the
> > most logical way to
> > hold structured data, why not go a step further
> > and store the actual
> > queries in relational format?

> What is "unstructured" data anyway?!  :-)

OK, bad terminology, I guess by structured data I really mean data
that is held in a non-atomic form, so it's not really the data that is
unstructured; rather the logical storage format.

Quote:> > ...why not go a step further and store the actual
> > queries in relational format?

> Sure... but there is no reason you couldn't do a whole imperative
> language including queries (expressions).  It would only be a matter
> of having a table for each language element.  Something like this:

An imperative language is one like C or Java where you specify "how"
rather than "what"? So a query would just be an expression in the
imperative language of type "relation"? Do we make a distinction
between a table with a single row and column containing the integer
"3" and the integer "3" itself?

although SQL Server 7 doesn't have relation types as far as I know,
this:

IF (SELECT 3) = 3 PRINT "equal"

does print "equal", it must do an implicit type conversion.

What I'm thinking is that everything could be done in a declarative
language including the imperative parts. So rather than embedding SQL
within a procedural language, you do the opposite: store your
procedural language in relations within a RDBMS.

It wood be good to get the self-referentiality of C (compilers
compiling themselves) for a declarative query language. Imagine an
RDBMS that could compile itself. You might say how can a database
affect the underlying operating system but the basic C can't really do
anything other than program logic - it needs all the libraries to do
any sort of I/O. You could have the equivalent of I/O libraries for an
RDBMS which are implemented at the physical level but controlled
through relations at the logical level.

For the example of just storing SQL queries in relations a first naive
attempt to store a basic query that can select columns from a single
table but not restrict the rows (this is called projection in
relational algebra?) might be:

CREATE TABLE sqlselect (
selectID int,
PRIMARY KEY (selectID)
)

CREATE TABLE sqlcolumn (
selectID int,
colname char(20),
alias char(20),
PRIMARY KEY (selectID, alias),
FOREIGN KEY (selectID) REFERENCES sqlselect(selectID)
)

CREATE TABLE sqlfrom (
selectID int,
tablename char(20),
PRIMARY KEY (selectID),
FOREIGN KEY (selectID) REFERENCES sqlselect(selectID)
)

INSERT INTO sqlselect VALUES (1)
INSERT INTO sqlcolumn VALUES (1, 'a', 'a')
INSERT INTO sqlcolumn VALUES (1, 'b', 'b1')
INSERT INTO sqlcolumn VALUES (1, 'b', 'b2')
INSERT INTO sqlfrom VALUES (1, 'tbl')

this would represent the SQL query:
SELECT a, b AS b1, b AS b2 FROM tbl

Instead of having the actual table & column names stored you could
have the relevant keys from, and constraints with, the system
catalogue tables. Then if you changed a column name the "queries"
would still be valid. Also you'd have to delete all the "queries" that
referenced a table before you could drop the table.

Also to allow for joins, subqueries, expressions such as "a + b",
aggregates etc. you'd obviously need a lot more complicated schema.

Paul.

Quote:> create table InsertStatement
> {
>   Statement_ID : StatementID,
>   SourceExpression : ExpressionID,
>   TargetExpression : ExpressionID,
>   key { Statement_ID },
>   ...
> };

> create table Expression
> {
>   ID : ExpressionID,
>   ResultType : TypeID,
>   ExpressionType : ExpressionType,
>   key { ID }
> };

> create table UnaryOperatorExpression
> {
>   Expression_ID : ExpressionID,
>   Operand : ExpressionID,
>   key { Expression_ID },
>   ...
> };

> create table IntegerLiteralExpression
> {
>   Expression_ID : ExpressionID,
>   Value : Integer,
>   key { Expression_ID },
>   ...
> };

> Of course you would want to build some supporting operators:

> create operator EvaluateExpression(AExpressionID : ExpressionID) :
> Generic
> ...

> What would also be an interesting exercise would be to model a word
> processor!  So much for "unstructured" data!

 
 
 

Storing query language in relations

Post by Pau » Thu, 26 Sep 2002 19:08:14



> Paul. You are raising the question of when to model non-scalar types as
> single attribute values and when to model them as relation values.

> The cheating answer is to point out that in the RM you can have your cake
> and eat it.
> All you need do is to create a set of virtual relvars over your non-scalar
> attribute.

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. Maybe on the physical level
you could then store the non-scalar type attribute in a single
physical file.
Or are we just saying the same thing in different ways?

Quote:> >I know some DBMSs store the text of queries, view definitions,
> >stored procedures etc. in tables but not in a normalised fashion.
> >An advantage would be that the DB constraints would automatically
> >validate the query so no need for parsing SQL code for syntax.

> The downside being that you would need some horrendous multiple relvar
> assignments to enter a new query.

Well you might say: if all queries have to be stored in relations, how
do you INSERT any data to begin with? Instead of having a
non-relational query language like SQL sitting on top of the database,
you could have a method of entering data into relations *under* the
database i.e. at the physical level.

Maybe this is a GUI or maybe it is a conventional SQL INSERT statement
but as you type it (or once you've finished) the DBMS would be putting
it into relations for you. In a sense to the end user this is
indistinguishable from the usual practice I suppose.

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. The output would be built
in to the RDBMS and could be a text file or whatever.

Quote:> Can I suggest that a more pragmatic (and human friendly) idea would be to
> recognise the need for a new non-scalar type generator. Such a generator
> would take a BNF grammar and create a non-scalar 'language' type for that
> grammar. It would also create a parser to take a text string and return a
> value of the new type for all valid inputs.

> If called from a Relation Type generator (i.e. CREATE TABLE, VALUES in
> SQL), such a language generator could, if desired, produce virtual relvars
> that normalise any non-scalar language attributes. Along with such virtual
> relvars, a mass of relational constraints corresponding to the particular
> BNF grammar would be generated, thereby ensuring that the relvars could
> only be set to values corresponding to valid paragraphs of the language.

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?
The relational constraints would only need to be done once because you
could store all your SQL statements in the same relations.

Quote:> But I would ask why? What would we gain? The usual answer for normalising
> non-scalar types is that we want to be able to ad-hoc query the components
> of the non-scalar types using the expressive power of relational algebra.
> As an example, breaking written language attributes into their consistence
> parts (e.g. words) is a sensible step if you are building a simple text
> search database. This would allow you to say find the set of sentences
> with 6 or more words in common, using a relational query.

> In the general case of languages, (e.g. SQL, C, relational algebra, etc) I
> find it hard to conceive of a common need to relationally query their
> components.  I guess that a DIFF function could be coded relationally, but
> frankly I think that traversing parse trees is going to be easier.

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.
Or listing all the functions used in a C program.

Isn't normalization good just from a logical storage point of view
even before you begin wanting to write queries?

Quote:> Having said that I think it would be a good test of a true relational
> language. If such a language Type generator was provided, it would show
> that they were eating their own food.

> >Wasn't one of Codd's guidelines that metadata should also be
> > stored relationally?

> Indeed. I don't have the quote to hand, but the upshot is that EVERYTHING
> that is needed to be stored persistently, should be stored relationally.

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 :)

Quote:> >I've not thought too deeply about how a schema for SQL queries (for
> >example) could be implemented but it must be possible (kind of like
> >the MS Access query grid but normalised).

> So the MS Access query grid can specify all possible relational
> expressions? Humm. As above, I would suggest using the grammar for the
> language as your schema.  * hey?

Well the MS Access query grid is very limited but it can define basic
queries.
Joins are stored in the "diagram" part but if you stick to
single-table queries (projections and restrictions?) I think it gives
you a vague idea of what I'm driving at. (Not that I'm advocating
Access as a great relational product ;))
Actually I think Access may in fact store the queries as text but just
use the grid as a data entry mechanism. Though that would be OK if you
think of the text storage as physical and the grid as the logical
level.

Quote:> >Is this taking things too far or might this line of thinking
> >produce something useful? Has this been done already in some
> >DBMS?

> As long as the ideas hold water, you have to go a long way to go to far in
> a theory newsgroup.

I'm not sure if this is thinking along similar lines as mountain man's
thread or if this is taking it further to its logical absurdity (or
purity depending on your viewpoint).

Paul.

 
 
 

Storing query language in relations

Post by Mikito Haraki » Fri, 27 Sep 2002 04:32:18



> If we accept that a relational database is the most logical way to
> hold structured data, why not go a step further and store the actual
> queries in relational format? I know some DBMSs store the text of
> queries, view definitions, stored procedures etc. in tables but not in
> a normalised fashion.

It is difficult to define what normalized SQL query is.

For example, would you consider

select ename from emp where empno = 1000

and

select ename from emp where empno + 1 = 1001

as "equivalent" queries (then, you normalize them into identical
representation)?

If you have an answer for simple predicates, then how about nested
subqueries? Would query transformation that unnests subquery still
give you an "equivalent" query?

 
 
 

Storing query language in relations

Post by mountain ma » Fri, 27 Sep 2002 10:57:58



Quote:> This is just an idea I've been having, I'm not sure if it's been
> visited before or if it's not worth pursuing.

> If we accept that a relational database is the most logical way to
> hold structured data, why not go a step further and store the actual
> queries in relational format? I know some DBMSs store the text of
> queries, view definitions, stored procedures etc. in tables but not in
> a normalised fashion.

> An advantage would be that the DB constraints would automatically
> validate the query so no need for parsing SQL code for syntax. Maybe
> it could be used for storing other (procedural) languages as well like
> C - it would be useful for large software projects - no need for loads
> of files and subdirectories and client apps could quickly find
> function definitions etc.

> Wasn't one of Codd's guidelines that metadata should also be stored
> relationally? I know the table definitions (DDL) are in many DBMSs.
> But generally a separation is made between the database and its query
> language (DML).

> I've not thought too deeply about how a schema for SQL queries (for
> example) could be implemented but it must be possible (kind of like
> the MS Access query grid but normalised).

> Is this taking things too far or might this line of thinking produce
> something useful? Has this been done already in some DBMS?

http://www.mountainman.com.au/software/history/it7.html

--
Farmer Brown
Falls Creek, Australia
http://www.mountainman.com.au/software

 
 
 

Storing query language in relations

Post by Pau » Sat, 28 Sep 2002 17:56:24



> It is difficult to define what normalized SQL query is.

> For example, would you consider

> select ename from emp where empno = 1000

> and

> select ename from emp where empno + 1 = 1001

> as "equivalent" queries (then, you normalize them into identical
> representation)?

Well in this case it you would be able to put the expression in some
"canonical form" which would be analagous to, but separate from,
normalisation. I think maybe what confuses the issue is that we look
at not only relational theory here but also the theory of the
underlying domains. It might be clearer to consider the case where the
domains are just sets with no operators defined on them at all other
than equality. Then build up to think about domains which are ordered
sets of various categories, then groups, fields, algebras plus
whatever other mathematical structures there are. You could for
example have a database schema just for storing polynomial
expressions.

Actually, are subqueries only needed when you have aggregate
functions? And aggregate functions are only defined over domains with
the appropriate operators defined on them (like addition, comparison
etc.)

To go a step further you could also define your set operators in
relations. For example to store the facts that 1 + 1 = 2, 1 + 2 = 3,
etc.

You can see here how relational database theory converges with set
theory and information theory - e.g. the set {a, {a, {b, c}}, {b, c}}
could maybe be represented more succinctly because it contains {b, c}
twice.
Or the sequence "abbaabccbbaac" can be compressed by noting the
repeated subsequences. This would be analagous to normalisatin maybe?

Quote:> If you have an answer for simple predicates, then how about nested
> subqueries? Would query transformation that unnests subquery still
> give you an "equivalent" query?

Maybe the concept of "query equivalence" can't be well defined in SQL?
Or perhaps in the general case of a database, normalisation is as much
an art as a science since it depends on the interpretation given to
the data which is often business-oriented and not rigorously defined.
But if we consider a database to store some precisely defined
mathematical construct then maybe you could have some way of having a
unique way of storing each query. Or at least have some "canonical
form" that would show that two different queries are in fact
equivalent.

Is there a way in SQL of automating the process of transforming a
subquery into a join or vice versa?

Are there any query languages that have only one way of expressing any
query?

Paul.

 
 
 

Storing query language in relations

Post by Paul Verno » Thu, 03 Oct 2002 03:12:06


Quote:>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.

Quote:>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.

Quote:>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

Quote:>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?

Quote:>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.

Quote:>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.

Quote:>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

 
 
 

Storing query language in relations

Post by Pau » Fri, 04 Oct 2002 01:57:03



> 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.

I'm not totally clear on this - the DBMS I'm using here (SQL Server)
defines a view to basically be a query result that is treated as if it
were a "real" table - but I presume that this is not the definition
that you have. I've been looking at the other thread on view
updateability and I think this confusion is the heart of the problem.
If you have aggregates or derived columns or indeed anything that uses
the properties of the *domains* rather than the *relations* then
clearly my simple definition of a view is not always updateable and
hence not equivalent to the "real" tables.

So in a sense we are defining a view to be the subset of queries that
have inverses and are thus updateable. So views are updateable by
definition really.
Is this equivalent to the set of "pure" relational queries i.e.
queries not using domain properties at all?

Quote:> >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?

good question - I guess this is the exact analogy for DDL what my
thoughts were for DML. I guess the system table is "hardcoded" into
the database (like my hypothetical INSERT function).

I'm wondering why the requirement that metadata and data are both
stored relationally is there? Maybe a separation between the two is a
good thing - with too much self-referentiality might we get
Godel-style problems like trying to define a view of all tables/views
that don't contain definitions of themselves? i.e. does *this* view
contain itself or not?

Or maybe you'd be OK because of computers being finite and the fact
that you build relations constructively rather than non-constructively
(can't remember what the term is for this though vaguely remember
something about Axiom of Choise from set theory)

Maybe data and metadata should be in an (infinite?) hierarchy where
you can't "join" tables that are at different levels. Not that I can
think of a reason why you'd want to though.

Are there DBMSs where you can "create" new tables, views, indexes,
constraints, etc. just by inserting into the appropriate system
tables? So the DDL becomes redundant?

Just thinking as well - by storing queries in relational form you
remove the need for relation-valued domains because they could be
indexed by integers.

Quote:> 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.  

for example?

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

Maybe even you could have the BNF syntax itself stored relationally
(would this be meta-meta-data?) so that you could automatically
generate the schema for any particular BNF grammar you feed it?

You could also go the opposite way to metadata and think about storing
domains relationally as well. e.g. define the domain of integers (at
least up to a certain value) and operations like addition upon it.
Maybe just store numbers as binary and consider each binary digit to
be atomic. Is there a relational equivalent of the Turing machine?
Could you have a computer hardware architecture that was implemented
relationally instead of the traditional von Neuman architecture? I'm
just thinking aloud here so forgive me if some of these thoughts are
nonsense.

Paul.

 
 
 

Storing query language in relations

Post by Mikito Haraki » Fri, 04 Oct 2002 07:07:09



> Are there DBMSs where you can "create" new tables, views, indexes,
> constraints, etc. just by inserting into the appropriate system
> tables? So the DDL becomes redundant?

I also thought about it ~yr ago: it is certainly a nice idea. DBA have
to learn only catalog schema, no stupid DDL.
 
 
 

Storing query language in relations

Post by Tibor Karasz » Fri, 04 Oct 2002 07:38:37


Quote:> > Are there DBMSs where you can "create" new tables, views, indexes,
> > constraints, etc. just by inserting into the appropriate system
> > tables? So the DDL becomes redundant?

> I also thought about it ~yr ago: it is certainly a nice idea. DBA have
> to learn only catalog schema, no stupid DDL.

Perhaps I'm missing stuff at beginning of this thread, but isn't DDL
actually to be seen as a *good* thing?

By inserting stuff directly into catalog, you make that "DDL" highly product
specific. And DDL it is, although in the form of INSERT statements. All
current RDBMS's that I know of have their own layout of the system catalog.

ANSI has defined the INFORMATION_SCHEMA view, but these are but views of the
actual system tables. ANSI SQL has defined the DEFINTION_SCHEMA tables, but
this is, even in the ANSI papers, to be seen as an imaginary set of system
tables, for the sake of defining the INFORMATION_SCHEMA views (my own
words). I know of no product that has actually implemented the
DEFINITION_SCHEMA tables (although I wouldn't be awfully surprised if Ocelot
has).

Also, at least MS SQL Server has to do other stuff when you do DDL. For
example:
First, you have several tables. You have one catalog table for the table
existence. And another for column existence. And possible also for the
physical characteristics (where to start a scan, for instance).
Second, initial space allocation probably has to take place. Agreed, this
could be handled by a constraint of some sort, and above could be taken care
of by a deferred constraint. IMO, this wouldn't buy us much, though.
--
Tibor Karaszi



Quote:> > Are there DBMSs where you can "create" new tables, views, indexes,
> > constraints, etc. just by inserting into the appropriate system
> > tables? So the DDL becomes redundant?

> I also thought about it ~yr ago: it is certainly a nice idea. DBA have
> to learn only catalog schema, no stupid DDL.

 
 
 

Storing query language in relations

Post by Jonathan Leffle » Fri, 04 Oct 2002 14:10:49




>>Are there DBMSs where you can "create" new tables, views, indexes,
>>constraints, etc. just by inserting into the appropriate system
>>tables? So the DDL becomes redundant?

> I also thought about it ~yr ago: it is certainly a nice idea. DBA have
> to learn only catalog schema, no stupid DDL.

And, if http://www.mcjones.org/System_R/bs12.html is to be believed
(and I've no reason not to believe it), it was thought of for Business
System 12 a lot longer than a year ago.

--
Jonathan Leffler                   #include <disclaimer.h>

Guardian of DBD::Informix 1.00.PC2 -- http://dbi.perl.org/

 
 
 

Storing query language in relations

Post by Mikito Haraki » Sat, 05 Oct 2002 03:50:00



> > > Are there DBMSs where you can "create" new tables, views, indexes,
> > > constraints, etc. just by inserting into the appropriate system
> > > tables? So the DDL becomes redundant?

> > I also thought about it ~yr ago: it is certainly a nice idea. DBA have
> > to learn only catalog schema, no stupid DDL.

> Perhaps I'm missing stuff at beginning of this thread, but isn't DDL
> actually to be seen as a *good* thing?

> By inserting stuff directly into catalog, you make that "DDL" highly product
> specific. And DDL it is, although in the form of INSERT statements. All
> current RDBMS's that I know of have their own layout of the system catalog.

> ANSI has defined the INFORMATION_SCHEMA view, but these are but views of the
> actual system tables. ANSI SQL has defined the DEFINTION_SCHEMA tables, but
> this is, even in the ANSI papers, to be seen as an imaginary set of system
> tables, for the sake of defining the INFORMATION_SCHEMA views (my own
> words). I know of no product that has actually implemented the
> DEFINITION_SCHEMA tables (although I wouldn't be awfully surprised if Ocelot
> has).

Why DEFINTION_SCHEMA tables are necessary in the vendor's
implementation? A vendor just need to implement INFORMATION_SCHEMA
views upon their (proprietory) catalog. Then, a user just updates
INFORMATION_SCHEMA views, which then triggers updates in the
underlying catalog.

- Show quoted text -

Quote:> Also, at least MS SQL Server has to do other stuff when you do DDL. For
> example:
> First, you have several tables. You have one catalog table for the table
> existence. And another for column existence. And possible also for the
> physical characteristics (where to start a scan, for instance).
> Second, initial space allocation probably has to take place. Agreed, this
> could be handled by a constraint of some sort, and above could be taken care
> of by a deferred constraint. IMO, this wouldn't buy us much, though.

 
 
 

Storing query language in relations

Post by Mikito Haraki » Sat, 05 Oct 2002 03:53:10





> >>Are there DBMSs where you can "create" new tables, views, indexes,
> >>constraints, etc. just by inserting into the appropriate system
> >>tables? So the DDL becomes redundant?

> > I also thought about it ~yr ago: it is certainly a nice idea. DBA have
> > to learn only catalog schema, no stupid DDL.

> And, if http://www.mcjones.org/System_R/bs12.html is to be believed
> (and I've no reason not to believe it), it was thought of for Business
> System 12 a lot longer than a year ago.

Got me;-) Trivial but important idea indeed. Actually, it was a user
who started asking the question "can I update system catalog", and
eliminating DDL is a natural way to make the matter straight.
 
 
 

1. Relations, Relation,s Relations 2.6

How should I programatically check to see if a table is already
engaged in a relation, like I do with used()?  And how can I
tell what the relation is?

Why do I need a SET SKIP TO when I already have done a
SET RELATION?  They seem to do the same thing, but SET SKIP
makes my parent table look messier.

How can I make my BROWSE window the same grey color as one of
the greys set in the ...color... section of the screen layout
menu?

--

                                      "This is the Jam of the Year!"

Steve Craft

2. HELP

3. Storing and Displaying data in Chinese (or other non-English languages) in the Query Analyzer

4. XML Parsers

5. References to Deductive Languages for Non-1NF Relations?

6. Crosstab Query?

7. Relations between tables n:m-relation

8. Looking for table size estimating tools.

9. Retreive data from a Relation on a Relation

10. Query and two tables with relations

11. How to query 1 to many relation to flat file

12. Can I have a relation between 2 tables that are stored in 2 different databases

13. query with primary & extern key relations