Subject: RE: [hackers-il] RFC: standard SQL syntax for querying database i nformation

From: Chen Shapira <[email-masked]>
Date: Mon, 23 Oct 2000 20:29:12 +0200

> If not, maybe it would be a good idea to form a working group 
> that will
> suggest a unified syntax for this kind of thing. It would 
> remove a lot of
> cross-database compatibility issues.

I agree 100%. One of the problems with the SQL standard is that its too
small.
I have to support 5 diffrent databases in my application
(Oracle,Mysql,SQLserver,Postgres and Access)
And I have large chunks of code dedicated to build a diffrent query for
each.
It doesn't cover meta-data like column names, table names, etc. (BTW. in
sqlserver this data is avaliable in a seperate table that you can query, but
you can't get the meta data about *that* table. chicken and egg problem)
It doesn't cover important things like date manipulation, standard supported
types, built in functions etc.

And the vendors do their worst on top of this.
SQL is never ever fully supported, and what they call "pure sql queries" are
never optimized (in oracle they don't even go into the query pool!) they
basicly force you to use their own special language features that are never
similar to anything else.

Yep, a change is defiantly in place.

Think of how much noise there is around web-standards and having browsers
fully support a good standard. This is getting to be quite an issue, and
rightly so, otherwise web developers have to work 3 times as hard writing
and maintaining cross-browser code.

There should be a similar amount of an issue around the productivity being
wasted and bugs being introduced by sql incompatability.

IMO the first step should be to have a good, usefull, all-covering standard,
not just an unusefull lowest common denominator.
Then vendors will have a reason to support it, or will at least be able to.

BTW, maybe most programmers/products just support one DB? perhaps there's
not enough need for sql?

Thanks,
Chen.

---

From: "Nadav Har'El" <[email-masked]>
Date: Mon, 23 Oct 2000 23:55:48 +0200

On Mon, Oct 23, 2000, Chen Shapira wrote about "RE: [hackers-il] RFC: standard SQL syntax for querying database i nformation":
>...

I'm no database expert (though I do know a little about SQL and MySQL), so
take what I say with a grain of salt (and educate me when necessary!):

How about inventing something, say SQL++, that looks like standard SQL with
any additions you think are worthwhile (e.g., commands to get metadata). Then
create a small library which converts SQL++ queries into queries suitable
(and optimized) for the various DB programs (MySQL, etc.). This should be
pretty easy to do (because all the varients are, after all, very similar to
the given SQL), and does not require any standartization: if you write
such a converter, and make it available, people may choose to use it. If
you make it good enough, vendors may even choose to support this SQL++
directly ;)

Of course, your converter may have some trouble deciding which SQL varient
to convert the queries into - whenever possible it will try to figure it out
itself (by querying the server), but if it's not possible, it will have to
be an option given by the user.

Does this idea make any sense?

It's like the idea of autoconf: instead of hoping (falsely) that all systems
will become alike by standartization, you create a tool that uses the
appropriate commands in each system. Of course, the fact that each system is
mostly similar and a Unix derivative (e.g., you don't want it to work on a
PDP-11 running Unix III and Commodore-64 running Basic) makes autoconf's task
very doable.

-- 
Nadav Har'El                        |      Monday, Oct 23 2000, 25 Tishri 5761
[email-masked]             |-----------------------------------------
Phone: +972-53-245868, ICQ 13349191 |You do not need a parachute to skydive.
http://nadav.harel.org.il           |You only need one to skydive twice.

---

From: Shlomi Fish <[email-masked]>
Date: Wed, 25 Oct 2000 09:07:31 +0200 (IST)

> Then vendors will have a reason to support it, or will at least be able to.

Well, judging from your letter, we can see that the standard needs to
cover the following aspects:
1. Querying the database for meta-data (column names, column types, tables
in a DB, etc.)
2. Standard way to do date manipulation.
3. Have a comprehensive list of standard types, from which DB vendors can
choose to support a subset. (a list of the supported types can be
retrieved by item #1)
4. Have a comprehensive list of standard built-in SQL functions, which DB
vendors implement a subset of. (again, a list of the supported functions
can be retrieved by item #1)
5. A standard syntax to create and drop indexes.
6. Did I miss anything?

Anyway, we could form a working group (i.e: mailing-list, web-site, etc.)
that will try to standarize that. If we put an announement for it on
Slashdot, we may get recognition and acceptance, at least from the free
software world. The way I see it, even if only MySQL, PostgreSQL and
InterBase support this standard initially, it may eventually mean that
commercial DB vendors will also support it.

What do you think of it?

Regards,

	Shlomi Fish

----------------------------------------------------------------------
Shlomi Fish        [email-masked] 
Home Page:         http://t2.technion.ac.il/~shlomif/
Home E-mail:       [email-masked]

The prefix "God Said" has the extraordinary logical property of 
converting any statement that follows it into a true one.

---

From: Shlomi Fish <[email-masked]>
Date: Thu, 26 Oct 2000 16:21:07 +0200 (IST)

> commercial DB vendors will also support it.

I did not receive any reply about this letter, so I'm asking you people
more precisely. Do you want me to take this idea forward. That is:
1. Form a mailing-list.
2. Create a preliminary web-page.
3. Post an announcement with the details of both those facilities here on
hackers-il.
4. Compose an article to be posted to Slashdot and post it on the
mailing-list.
5. After everybody are content with the article, send it to Slashdot.
6. Start discussing the standard.

I'd like to call this initiative "Extended-SQL" or "X-SQL" for short.

Now, that I am more decisive, what does everybody feel about it?

Regards,

	Shlomi Fish

> To unsubscribe from this group, send an email to:
> [email-masked]
> 
> 
> 
>