Re: Recursive queries in slow database

From: Roy Hann <specially_at_processed.almost.meat>
Date: Thu, 13 Jan 2011 17:15:23 +0000 (UTC)
Message-ID: <ignbvb$nf1$1_at_speranza.aioe.org>


Dr. Coffee wrote:

>> When people start getting enthusiastic about EAV you usually start
>> hearing about how the database is "static" and "inflexible".  Well BS on
>> that.  There is nothing simpler or quicker than creating a new table in
>> an SQL database. Once the analysis is done, it takes seconds. Literally
>> seconds.
>
> Hah! It seems your point supports my main position that the
> vendors are amateurs who don't know hos to do their job...
> I like the way this is going!
>
>> The inflexibility is in the application programmers.  They won't get off
>> their backsides to learn how to access the meta data in the system
>> catalogues--which is *always* there.  They are perfectly willing to
>> write dynamic applications that use metadata at run-time, but they
>> insist it has to be their own metadata from their own zany homegrown
>> catalogues.  It seems to be some kind of mad "not invented here" kind of
>> mentality.
>
> Now this is becoming interesting. As an application programmer
> who craves for APIs - what are you talking about? Where do I
> look to learn more about this? (Yes, this is new to me.)

There are various proprietary APIs, but I was thinking specifically of JDBC.
>> There was some very slight reason to be wary of dynamic programming back
>> when we were using Cobol and C because dynamic SQL (which is a
>> legitimate part of the ANSI/ISO SQL standard) required a small amount of
>> understanding and was never (as far as I know) covered in any
>> introductory SQL books (e.g. SQL for Feral Idiots).  But today, when
>> everyone seems happy enough to grovel down to the level of API
>> programming it's all basically dynamic anyway, so all you need to do is
>> encourage them to get familiar with the SQL catalogues.
>
> SQL *catalogues*? As opposed to SQL what? Where do I learn more?

Every SQL database, regardless of DBMS vendor, contains a set of catalogue tables that describe the database. There is a table that tells you what all the tables are called, who owns them, when they were created, etc. etc. There will be another table that tells you what all the columns of all the tables are, and the type of each, and its size, etc. etc. There will be yet another table that tells you what all the indexes are, and what columns are included in the index key. And another telling you what the referential constraints are, and so on and so on. Everything you could ever want to know about the database is supposed to be documented in the database in tables that can be queried using SQL just like any other table. Most products do a pretty good job of fulfilling this requirement. Thus you can relatively easily write programs that discover what the database looks like so they can figure out how to use it. That's what EAV tries to re-invent.

One problem with the database catalogue tables is that all products do it differently because in spite of the existence of some standards for the catalogues I don't think too many products even attempt to comply. You might get tied to a specific DBMS product because of the way its catalogues are designed. However most of the APIs provide the same information in a product-independent form.

Here's a randonly chosen link to an article on DB2 UDB catalogues. Maybe not the most obvious choice but it supports the idea that all DBMSs provide (most) of this information, usually: http://www.ibm.com/developerworks/data/library/techarticle/dm-0411melnyk

-- 
Roy
Received on Thu Jan 13 2011 - 18:15:23 CET

Original text of this message