sciSQL provides science-specific tools and extensions for SQL. Currently, the project contains user defined functions (UDFs) and stored procedures for MySQL or MariaDB in the areas of spherical geometry, statistics, and photometry. The project was motivated by the needs of the Rubin Observatory Legacy Survey of Space and Time (LSST) and has been sponsored by LSST and SLAC / DOE. sciSQL is distributed under the terms of the Apache License version 2.0.

• Source code
• Report a bug

sciSQL is also distributed with a pair of Javascript libraries. These are:

• jQuery, copyright John Resig and dual-licensed under the MIT and GPL version 2 licenses
• Google prettify, distributed under the Apache License version 2.0

Read on for instructions on how to configure, build, test, install and uninstall the sciSQL software.

Prerequisites

Python 2.5.x or later

Python future 0.16 or later

MySQL server 8.x -or- MariaDB server 10.x

mysqlclient 2.1.x or later

This is a Python DB API 2.0 implementation for MySQL/MariaDB, and is required when running the unit tests and uninstalling sciSQL.

Mako 0.4 or later

This Python templating library is required when rebuilding release documentation.

In order to install the UDFs, you will need write permission to the MySQL/MariaDB server plug-in directory, as well as a MySQL/MariaDB account with admin priviledges.

Databases reserved for sciSQL use

The following database names are reserved for use by sciSQL:

scisql

Contains sciSQL stored procedures.

scisql_test

Used by sciSQL unit tests.

scisql_demo

Contains sample data that can be used to exercise the sciSQL UDFs.

The scisql_demo database is dropped and re-created during installation. If you are using it for other things, you must migrate its contents to a different database prior to installing sciSQL. If you do not, YOU WILL LOSE DATA.

Even though the scisql and scisql_test databases are never automatically dropped, their use is STRONGLY DISCOURAGED.

Build configuration

Run ./configure from the top-level sciSQL directory. Passing --help will yield a list of configuration options. Here are the ones most likely to require tweaking if ./configure doesn't work straight out of the box on your system:

--mysql-dir

Set this to the top-level of the MySQL/MariaDB server install tree

--mysql-config

Point to mysql_config or mariadb_config configuration tool

--mysql-includes

Point to MySQL/MariaDB headers (mysql.h and dependents)

--scisql-prefix

This string will be used as a prefix for all sciSQL UDF and stored procedure names. You can specify an empty string, which will result in unprefixed names. The default is "scisql_".

If you wish to build/install only the sciSQL client utilities and documentation, run configure with the --client-only option. In this case, a MySQL/MariaDB server or client install is not required, the only executable generated is scisql_index (a utility which generates HTM indexes for tables of circles or polygons stored in tab-separated-value format).

Build

sciSQL is built and staged with the usual make and make install commands.

You may wish to regenerate the HTML documentation if you've chosen a non-default value for --scisql-prefix, as the HTML distributed with release tar-balls is built under the assumption that --scisql-prefix="scisql_". To do this, run make html_docs.

Deploying sciSQL in a MySQL/MariaDB instance

Assuming you've installed scisql in $PREFIX, update your PATH and PYTHONPATH as described below:

export PATH="$PREFIX/bin:$PATH"
export PYTHONPATH="$PREFIX/python:$PATH"

--mysql-user

Set this to the name of a MySQL/MariaDB admin user; defaults to root

--mysql-bin

Point to the mysql or mariadb command-line client

--mysql-socket

Point to the MySQL/MariaDB server UNIX socket file

--verbose

Verbosity level; possible value are FATAL, ERROR, WARN, INFO, DEBUG

You will be prompted for the MySQL/MariaDB admin user password during configuration. If you run scisql-deploy.py in a script, you can use standard input, for example via a pipe, for providing this password. Connection details, including this password, are stored in a temporary directory in a file named my-client.cnf using the MySQL options file format. This temporary file is removed at the end of the process, unless you set the verbose level to DEBUG.

The scisql-deploy.py command will CREATE the sciSQL UDFs, stored procedures, and databases (including the scisql_demo database). It will also automatically run the sciSQL integration tests, which check that sciSQL is correctly deployed. You can re-run the tests anytime by invoking scisql-deploy.py with the --test option.

Finally, note that after installation each UDF will be available under two names: one including a version number and one without. For example, assuming that --scisql-prefix="foo_" and that the sciSQL version number is 1.2.3, a hypothetical UDF named bar would be available as:

• foo_bar
• foo_bar_1_2_3

Invoking scisql-deploy.py with the --undeploy option will drop the versioned sciSQL UDFs and stored procedures. It will also drop the unversioned UDFs/procedures, but only if the unversioned UDF/procedure was created by the version of sciSQL being uninstalled. As a consequence, it is possible to have multiple versions of sciSQL installed at the same time, and the behavior of two versions can be compared from within a single MySQL/MariaDB instance. An unversioned name will resolve to the most recently installed versioned name.

An updeploy will also drop the scisql_demo database.

Rebuilding sciSQL

If you've already installed sciSQL, say using a UDF/procedure name prefix of "foo_", and then decide you'd like to change the prefix to "bar_", do the following from the top-level sciSQL directory:

scisql-deploy.py ... --undeploy

Uninstalls the UDFs and stored procedures named foo_*.

make distclean

Removes all build products and configuration files.

configure --scisql-prefix=bar_ ...

Reconfigures sciSQL (sets new name prefix).

make

Rebuilds sciSQL.

make install

Restages sciSQL.

scisql-deploy.py ...

Redeploys sciSQL

No MySQL/MariaDB restart is required. Note that multiple installations of the same version of sciSQL with different UDF/procedure name prefixes can coexist on a single MySQL/MariaDB instance.

MySQL/MariaDB server restarts

Installing different versions of sciSQL, or multiple copies of the same version with different UDF/procedure name prefixes, does not require a server restart.

Only sciSQL developers should need to perform restarts. They are required when changing the name of a UDF without changing the name of the shared library installed into the server plugin directory. In this case, an attempt to install the updated shared library will sometimes result in MySQL/MariaDB reporting that it cannot find symbol names that are actually present. This is presumably due to server and/or OS level caching effects, and restarting the server resolves the problem.

Reporting bugs and getting help

If you encounter test-case failures, or think you've identified a bug in the sciSQL code, or would just like to ask a question, please submit an issue.

The aim of the spherical geometry UDFs and stored procedures is to allow quick answers to the following sorts of questions:

1. Which points in a table lie inside a region on the sphere? For example, an astronomer might wish to know which stars and galaxies lie inside the region of the sky observed by a single camera CCD.
2. Which spherical regions in a table contain a particular point? For example, an astronomer might with to know which telescope images overlap the position of interesting object X.

HTM indexing

To accelerate these types of queries, sciSQL maps points/regions to the integer ID(s) of their containing/overlapping triangles in a Hierarchical Triangular Mesh (HTM). This is a decomposition of the unit sphere defined by A. Szalay, T. Budavari, G. Fekete at the Johns Hopkins University, and Jim Gray, Microsoft Research. See the following links for more information:

• http://voservices.net/spherical/
• http://adsabs.harvard.edu/abs/2010PASP..122.1375B

To accelerate spatial queries, standard B-tree indexes are created on the point/region HTM IDs and spatial constraints are expressed in terms of those IDs. This allows the database optimizer to restrict the rows that must be considered by a spatial query.

Read on to learn how to create and take advantage of HTM indexes on tables containing spatial data. The examples below can be run in the scisql_demo database, which contains all of the referenced tables and a tiny amount of sample data.

Supported region types

sciSQL supports 4 kinds of regions: longitude/latitude angle boxes, spherical circles (defined by a center and opening angle), spherical ellipses (the orthographic projection of a standard 2-d ellipse onto the sphere, where the 2-d ellipse is on a plane tangent to the unit sphere at the ellipse center), and spherical convex polygons (where polygon edges are great circles). Note also that spherical convex polygons have a binary representation, produced by scisql_s2CPolyToBin(), allowing them to be stored as values in a BINARY table column.

Points-in-region queries

sciSQL contains several UDFs for checking whether a point lies inside a region. These are: scisql_s2PtInBox(), scisql_s2PtInCircle(), scisql_s2PtInCPoly() and scisql_s2PtInEllipse(). They return 1 if the input point is inside the input region and 0 otherwise.

Given these UDFs, a simple way to answer question 1 is illustrated by the following example:

SELECT objectId
    FROM Object
    WHERE scisql_s2PtInCircle(ra_PS, decl_PS, 0, 0, 0.01) = 1;

This query returns all the objects within 0.01 degrees of (RA, Dec) = (0, 0). It is inefficient for small search regions because the scisql_s2PtInCircle() UDF must be called for every row in the Object table.

Lets assume that Object contains an indexed BIGINT column named htmId20. If it does not, the column and index can be added with ALTER TABLE. htmId20 can be populated with the subdivision-level 20 HTM IDs of object positions as follows:

ALTER TABLE Object DISABLE KEYS;
UPDATE Object
    SET htmId20 = scisql_s2HtmId(ra_PS, decl_PS, 20);
ALTER TABLE Object ENABLE KEYS;

The HTM subdivision level must be between 0 and 24. At subdivision level N, there are 8*4^N triangles in the mesh, so the higher subdivision levels correspond to finer tesselations of the unit sphere.

Now that HTM IDs for object positions are available and indexed, the query above can be made more efficient:

CALL scisql.scisql_s2CircleRegion(0, 0, 0.01, 20);

SELECT o.objectId
    FROM Object AS o INNER JOIN scisql.Region AS r
         ON (o.htmId20 BETWEEN r.htmMin AND r.htmMax)
    WHERE scisql_s2PtInCircle(o.ra_PS, o.decl_PS, 0, 0, 0.01) = 1;

What's going on here? The first line in the example calls the scisql_s2CircleRegion() stored procedure. This procedure creates a temporary table called scisql.Region with two BIGINT NOT NULL columns named htmMin and htmMax. It then stores the HTM IDs overlapping the search region in scisql.Region (as ranges).

Next, the original query is augmented with a join against scisql.Region. This limits the objects considered by scisql_s2PtInCircle() to those within the HTM triangles overlapping the search region; the index on htmId20 allows MySQL to retrieve these objects very quickly when the search region is small. Note that if the search region is large (meaning that a large fraction of the table being searched is inside the search region), then the original query may actually be faster.

Here is another example, this time with a search region taken from a table called Science_Ccd_Exposure. This table includes a a column named poly that contains polygonal approximations to the regions of the sphere observed by CCD exposures.

SELECT poly FROM Science_Ccd_Exposure
    WHERE scienceCcdExposureId = 43856062009
    INTO @poly;

CALL scisql.scisql_s2CPolyRegion(@poly, 20);

SELECT o.objectId
    FROM Object AS o INNER JOIN scisql.Region AS r
         ON (o.htmId20 BETWEEN r.htmMin AND r.htmMax)
    WHERE scisql_s2PtInCPoly(o.ra_PS, o.decl_PS, @poly) = 1;

The first statement stores the polygonal boundary of a particular CCD exposure into the user variable @poly, the second computes overlapping HTM IDs, and the third performs the points-in-region query as before.

Regions-containing-point queries

An example for this type of query is:

SELECT scienceCcdExposureId FROM Science_Ccd_Exposure
    WHERE scisql_s2PtInCPoly(0, 0, poly) = 1;

This query returns all the CCD exposures containing the point (RA, Dec) = (0, 0). To accelerate it using HTM indexing, an auxiliary table is introduced:

CREATE TABLE Science_Ccd_Exposure_HtmId10 (
    scienceCcdExposureId BIGINT  NOT NULL,
    htmId10              INTEGER NOT NULL,
    PRIMARY KEY (htmId10, scienceCcdExposureId),
    KEY (scienceCcdExposureId)
);

Science_Ccd_Exposure_HtmId10 will store the level 10 HTM ID of every triangle overlapping a CCD exposure. To populate it, start by dumping the primary key and polygon vertex colunms from Science_Ccd_Exposure:

rm -f /tmp/scisql_demo_ccds.tsv

SELECT scienceCcdExposureId,
       llcRa, llcDecl,
       ulcRa, ulcDecl,
       urcRa, urcDecl,
       lrcRa, lrcDecl
    FROM Science_Ccd_Exposure
    INTO OUTFILE '/tmp/scisql_demo_ccds.tsv';

Then, run the sciSQL region indexing utility:

sudo chmod a+r /tmp/scisql_demo_ccds.tsv
scisql_index -l 10 /tmp/scisql_demo_htmid10.tsv /tmp/scisql_demo_ccds.tsv

and load the results:

TRUNCATE TABLE Science_Ccd_Exposure_HtmId10;
LOAD DATA LOCAL INFILE '/tmp/scisql_demo_htmid10.tsv' INTO TABLE Science_Ccd_Exposure_HtmId10;

The example regions-containing-point query can now be expressed more efficiently as:

SELECT sce.scienceCcdExposureId
    FROM Science_Ccd_Exposure AS sce, (
             SELECT scienceCcdExposureId
             FROM Science_Ccd_Exposure_HtmId10
             WHERE htmId10 = scisql_s2HtmId(0, 0, 10)
         ) AS h
    WHERE sce.scienceCcdExposureId = h.scienceCcdExposureId AND
          scisql_s2PtInCPoly(0, 0, sce.poly) = 1;

These UDFs provide conversions between raw fluxes, calibrated (AB) fluxes and AB magnitudes.

These UDFs provide the ability to compute medians and percentiles. Averages and standard deviations can already be computed with the AVG and STDDEV SQL constructs.

These UDFs and stored procedures are either administrative, internal, or informational - they are not directly useful for scientific computation / queries.