adql

XSLT transformation from ADQL/x to SQL dialects

by Ivan Zolotuhkin

ADQL, which stands for Astronomical Data Query Language, is an XML language for constructing queries, based on SQL[1]. It was introduced for the reasons of neecessity of unified queries intercommunication specially for astronomical purposes. It supports, for example, simplified expressions instead of complex SQL constructions for different types of spherical region search or functions which can be transformed into join between distributed tables. ADQL provides middle layer between SQL and future VOQL (Virtual Observatory Query Language) that will be high-level user-oriented language.

There are two forms of ADQL:

• ADQL/x, an XML document conforming to the special XSD[2]
• ADQL/s, a string form, based on SQL92[3] and conforming to the ADQL grammar

ADQL makes possible for intelligent agents to query astronomical databases.

This language does well for intercommunications and construction of astronomical queries but data access almost completely organized via DMBSes that understand their own specific SQL dialects. So one of the primary tasks is transformation from ADQL to DMBS-understandable format to retrieve the data to the end user, ADQL to SQL transformation in other words.

Such a transformation can be implemented in several ways. One can hardcode transformation rules into his own application. That leads to the absence of flexibility along ADQL modification and development which conducted actively on the current early stage. Another way is to move transformation logic outside of application and make transformation independent from programming platform and system architecture.

The last one can be achived by using XML Stylesheet Language for Transformations (XSLT) and conducted in this work. For the above reasons it is highly scalable and distributable method that is based on true XML technology and can be used on most of ADQL-compliant services.

XSLT is a very powerfull technolodgy that allows to transform XML documents almost in arbitrary way, producing any other document format, such as HTML, PDF or DOC, for example. Transformation itself is performed by XSL processor that is available in any programming language and system architecture. Processor only applies transformation rules that are contained in .xsl files to an input XML and outputs result of the transformation. So single .xsl file is suitable for every XSL processor and has therefore great portability.

XSLT transformation developed is easy to modify since ADQL changes frequently yet and author is planning to release new xsl files with transformation with every major ADQL release. Also basing on main transformation file (taken from Spanish tutorial on EuroVO Workshop 2005) we inherit xsl files with SQL dialects to use with common free DMBSes such as MySQL and PostgreSQL. Inherited files need not be modified, they are almost independent from ADQL since main transformation file contains all the stuff to abstract from current ADQL version. Such an XSL layout allows to minimize efforts to keep transformation in sync with ADQL versions development and distribute it rapidly through ADQL-compliant services that need it.

Here is an ADQL/x example:

<?xml version="1.0" encoding="utf-8"?>
<Select xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.ivoa.net/xml/ADQL/v0.9">
  <SelectionList>
    <Item xsi:type="columnReferenceType" Table="a" Name="objid" />
    <Item xsi:type="columnReferenceType" Table="a" Name="ra" />
  </SelectionList>
  <From>
    <Table xsi:type="archiveTableType" Archive="SDSSDR2" Name="Photoprimary"
Alias="a" />
  </From>
  <Where>
    <Condition xsi:type="regionSearchType">
      <Region xmlns:q1="http://www.ivoa.net/xml/STC/STCregion/v1.10" xsi:type="q1:circleType" unit="deg">
        <q1:Center>181.3 -0.76</q1:Center>
        <q1:Radius>6.5</q1:Radius>
      </Region>
    </Condition>
  </Where>
</Select>

which would be represented in ADQL/s as follows:

SELECT a.objid, a.ra, a.dec
FROM SDSSDR2:Photoprimary a
WHERE Region('CIRCLE J2000 181.3 -0.76 6.5')

that means "give me all objects' ids and their equatorial coordinates from SDSS catalogue in circle with radius 6.5 degrees centered at RA=181.3deg and Dec=-0.76deg". In PgSQL this looks like:

SELECT 
   a.objid, 
   a.ra 
FROM 
   Photoprimary AS a 
WHERE 
   2 * ASIN(SQRT(POW(SIN(RADIANS(-0.76 - dec) / 2), 2) + 
   COS(RADIANS(-0.76)) * COS(RADIANS(dec)) * 
   POW(SIN(PI() / 12 * (181.3 - ra) / 2), 2))) < 6.5 * PI() / (60 * 180)

ADQL to SQL web service usage example

Here is part of simple Perl script that can use our web service for transformation of ADQL/x query string to MySQL and PgSQL queries:

use SOAP::Lite;
my $service = SOAP::Lite->service("http://vo.astronet.ru/adql/adqltosqlservice.wsdl");
print "Transformed MySQL result:\n" . $service->adql_to_mysql($adqlx_query) . "\n";
print "Transformed PgSQL result:\n" . $service->adql_to_pgsql($adqlx_query) . "\n";

Links

1. http://www.ivoa.net/Documents/WD/ADQL/ADQL-20050602.pdf, IVOA ADQL Working Draft
2. http://www.ivoa.net/xml/ADQL/ADQL-v1.0.xsd, XML Schema Definiton for ADQL/x
3. http://www.contrib.andrew.cmu.edu/%7Eshadow/sql/sql1992.txt