Roy,
How embarrassing.
The simple answer is no.
The complex answer is:
(Q1) is there a standard SQL that all database systems understand...
NO.
There is an ISO Standard but all the implementations support an
extended subset of that standard (ie. Some overlap).
There was an Xopen attempt to define a portable SQL, but that was
long ago.
To make things worse, each system has different datatypes, different functions, different function names, ....
And there are some nasty corner cases, one popular vendor treats the empty string and the null string as the same, no other vendor does.
It's a mess.
To make things worse, MyDB requires that you be able to CREATE, DROP tables and INSERT and DELETE as well as SELECT.
So, VO needs a fairly full version of SQL.
We should probably write a parser for the subset that we can all agree to
and that maps to the 6 popular systems (DB2, Oracle, MySql, PostSQL, SqlServer, Sybase).
I think the JHU folks have a start on such a parser. We will then have to build the mapping matrix for each of the products.
This is a pain, but I do not know of an open source tool that does it.
Incidentally, contrary to Tony's experience "vectors, arrays, and structures" are not standard and are not in many of the SQL implementations.
And no two implementations have the same way of doing these types (sigh!)
It is non-trivial to add them
(Q2) If there is such a standard,....
Nope.
(Q3) Does the extension mechanism that you refer to exist within a
"standard" SQL?
The SQL Standard has extension mechanism (persistent stored modules),
but only 2 vendors observe that standard and it requires you to write in
a strange module language. The extensions to support the math library
and other functions is not standardized.
What I was advocating is something similar to what Clive Page started --
VOQL would be an extended subset of SQL, just like all the other SQL
variants.
I was thinking the extensions would be things like: (1) functions, (2)
qualifying the table names by the Archive name, (3) allowing inner and
outer spatial join, (4) support for MyDB naming, and perhaps a few more
essential features.
Jim Gray
Microsoft Research, Suite 1690, 455 Market, SF CA 94105,
tel: 415 778 8222 fax: 425 706 7329
Gray@Microsoft.com http://research.Microsoft.com/~gray
-----Original Message-----
From: Roy Williams [mailto:roy-at-cacr.caltech.edu]
Sent: Monday, February 02, 2004 8:17 AM
To: Jim Gray
Cc: voql-at-ivoa.net
Subject: Re: ADQL - Careful what you wish for
Jim
Here are some neophyte questions.
(1) Is there a "standard" SQL that all the databases understand, somthing that is least common denominator, somthing not tied to a vendor? I seem to recall SQL92, is that it? Is it ISO or ANSI approved? Is there a definition document?
(2) If there is such a standard, is it reasonably competent, or are there gaping holes? In other words, can I do unjoined select statements in full glory?
(3) Does the extension mechanism that you refer to exist within a
"standard"
SQL? Does it allow us extensions for sky regions, with things like
radius and fuzzy join?
Thank you
Roy
> sage advice:
> As one who has watched language designers struggle for many years
> with languages like Fortran, VisiCalc, SQL, and HTML, I have learned
> that it is not an occupation for dilettantes like us.
> So, to the extent that you can hijack an existing language like SQL,
> do it.
> There are no "minor" changes to these languages; they all have any
> tight interconnections.
> Adding XML data in the middle of some language is non trivial.
> If you really love XML, you might look at xQuery, but it has rather
> poor implementations so far, and it lacks update/inset/delete; and
> some describe the language as "ugly" .
> The Sky Query folks extended the table names with an extra level of
> qualification and added a function (cross match).
> That was safe and easy.
> I recommend you try to use SQL's built in extension mechanisms.
> It you really! want to do language design, do not couple that research
> with the main path of the VO effort.
> It would make a fine side-project for the computer science guys -but
> it should not be on the critical path for the VO
>
>
> Linde
> Sent: Sunday, February 01, 2004 2:28 AM
> To: 'Wil O'Mullane'
> Cc: voql-at-ivoa.net
> Subject: RE: ADQL - it aint so great
>
> > format. I made a stab at and XML format. Modified it
>
> I still think we need the xml version. Astronomy includes values which
> are not simple scalars; representing structures even as simple as a
> polar coordinate is messy in a textual language. As we add more
> complex features/funcitons to ADQL we don't want to have to invent
> text equivalents of vectors, arrays and more complex structured
values.
>
> you don't have to deconstruct the textual version first.
>
>
>> > Personally I also do not feel we need an XML form of ADQL.
> > -----Original Message-----
> > From: owner-voql-at-eso.org [mailto:owner-voql-at-eso.org] On Behalf Of
> > Wil O'Mullane
> > Sent: 31 January 2004 19:27
> > Cc: voql-at-ivoa.net
> > Subject: Re: ADQL - it aint so great
> >
> >
> > format. I made a stab at and XML format. Modified it etc ... This of
> > course may be discussed again at the meeting.
> > Even internal to SkyServer there is some parsing going on as Clive
> > suggests. People generally felt having a parsed version would help
> > in this area.
> >
> >
> > We are aware of some of the problems mentioned especially with java.
> > Vivek is currently working on this and I believe will post some new
> > tutorials etc in the coming days.
> >
> >
> > wil
> >
> >
> > On Sat, Jan 31, 2004 at 04:40:11PM +0000, Clive Page wrote:
> > > On Fri, 30 Jan 2004, Noel Winstanley wrote:
> > >
> > > > I tried and I failed.
> > > >
> > > > I can;t get any joy with the latest version of ADQL.
> > >
> > > > I'm really beginning to wonder whether ADQL is the most suitable
> > > > query language for astrogrid datacenters. What are its benefits?
> > >
> > > I agree with your comments, Noel, and I've been asking for
> > some time
> > > why we need ADQL. The only answer I was able to get was
> > that it made
> > > the checking of the query simpler and improves security. I am not
> > > convinced that we need to be all that fussy in checing
> > syntax: if the
> > > user generates the query from our menu-style registry-driven
> > > portal then it will be hard to make a syntax mistake; power users
> > > who generate their own SQL by typing it into a text box will
> > find out soon
> > > enough if they make a mistake: any DBMS parses the query
> > and returns
> > > an error message instantly. The experience of JHU with their
> > > skyserver was given at the last ADASS meeting by Wil
> > O'Mullane: indeed
> > > he made a mistake in his live demo and got a message back,
> > > allowing him to correct it. This isn't a perfectly user-friendly
> > system, but
> > > it seems adequate to me. As far as security goes, a simple
> > solution
> > > initially is to restrict the first keyword of any query to
> > be SELECT,
> > > and in particular prevent non-authenticated users from
> > issuing DROP or
> > > DELETE statements. Later, maybe, we can allow things like CREATE
> > > TABLE, INSERT, UPDATE, and so on. That shouldn't be all that hard
> > > even without ADQL.
> > >
> > >
> > > --
> > > Clive Page
> > > Dept of Physics & Astronomy,
> > > University of Leicester, Tel +44 116 252 3551
> > > Leicester, LE1 7RH, U.K. Fax +44 116 252 3311
> >
> > > >Received on 2004-02-03Z07:50:12