On Tue, 2 Dec 2003, John Good wrote:
> It isn't realistic to expect anyone to switch DBMSs based on
> what VO would like (we've got almost a terabyte of relational data).
Not generally realistic, but there may be a few cases (naming no names) which are using antique database software and may be looking to migrate to something more modern...
> It is also naive to base a VO standard on something which is not
> fully supported by any of the major DBMS vendors.
Actually the thing I said was not supported by any of the major DBMS vendors was _any_ Official SQL Standard. I think that's still true, but am willing to be corrected. So on that basis we are left with nothing. But there are JDBC drivers for almost all DBMS; whether they are all perfectly compliant with the JDBC Standard (written by Sun I think) is something I don't know. But in nearly all DBMS you can provide user-defined functions; maybe the JDBC layer does that to fill in any gaps? If not astronomers could write the necessary functions themselves.
My investigations this afternoon in DBMS manuals covering: JDBC and the following DBMS: MySQL, Postgres, DB2, SQL Server, Oracle, and Sybase. Sorry I didn't think of Informix, but it's now owned by IBM so if it doesn't support the same functions as DB2, maybe it soon will (or it could be that IBM will cease to support it?).
I don't have the time today to type in a nicely formatted table, but here below are the differences I discovered for the functions listed in the ADQL document. I think this is accurate and up-to-date, but may have missed something in quickly scanning the on-line manuals where I don't have printed copies. The entry "ok" means supported by all the above systems.
ABS ok
ACOS ok
ASIN ok
ATAN ok
CEILING called CEIL by Postgres and Oracle, DB2 supports both names.
COS ok
COT missing from Oracle
DEGREES missing from Oracle
EXP ok
FLOOR ok
LN/LOG supported by Postgres, DB2, Oracle; called LOG/LOG10 by JDBC,
MySQL, SQL Server, and Sybase.
MOD missing from SQL Server, and Sybase
PI missing from DB2, Oracle
POWER called POW by Postgres; MySQL supports both names.
RADIANS missing from Oracle
SIN ok
SQRT ok
TAN ok
There are 4 other math functions supported by JDBC which were not included in ADQL, which astronomers might need:
ATAN2 called ATN2 by SQL Server and Sybase
RAND called RANDOM by Postgres, missing from Oracle
ROUND ok
TRUNCATE called TRUNC by Postgres and Oracle, missing from SQL Server and
Sybase.
Maybe John Good could check his Informix manuals and update this pseudo-table. It may not leave us with all that many "standard" functions.
There are, of course, a lot of other functions supported by JDBC which astronomers might want to use, especially for string handling and for date/time manipulation. But given the problems found with simple math functions, maybe we ought not to venture in such wild and uncharted territory without a guide.
What's in ADQL seems sensible to me, except perhaps for the LOG/LN LOG/LOG10 problem. This is rather pernicious, since LOG to some SQL processors means log to base 10 and to others means log to base e.
I'd like to suggest that we change ADQL to use LOG and LOG10, to conform to JDBC. If we make no change, none of the DBMS listed above conforms to the ADQL spec; if we change this than MySQL is the only one conforming. Actually, that's rather remarkable, given that MySQL is often criticised for its lack of standards-conformance.
I'm grateful to Tony for reminding us that the ADQL spec also includes a
facility for cone-search (via the REGION keyword). I don't want to stir
up any more hornets nests, but I'd have thought that a simple numerical
function might be easier to use, i.e. instead of
REGION('CIRCLE J2000 19.5, -36.7 0.02')
we had something like
CIRCLE('J2000', 19.5, -36.7, 0.02)
but I guess the former gives more flexibility for using non-circular
regions.
-- Clive Page Dept of Physics & Astronomy, University of Leicester, Tel +44 116 252 3551 Leicester, LE1 7RH, U.K. Fax +44 116 252 3311Received on 2003-12-02Z19:10:09