Write Once, Run Everywhere - Rax SQL Backends

Rax is running atop an SQL-based relational database backend, translating data-heavy operations into SQL queries. Actually, there are several SQL backends that Rax can use. Rax syntax and functionality are not dependent on the backend used. In other words, Rax/Redshift can execute exactly the same Rax code as Rax/MySQL and Rax/Azure. The subtle differences between various SQL dialects are hidden from Rax uses. There might be differences in execution time, however. Both because of the differences between backends as well as in the way Rax uses the backend. Let's look at a simple Rax program:

// Make a set of five numbers.
{#}: N := {1..5};
// Make a set of strings based on N.
{$}: S := project[($)(((.#1 + 4) * 71) % 13)] N;
// Print the top three sorted alphabetically.
`print S[1..3];

When run on Rax/Azure, Rax executes the following SQL queries in the code in the SQL (Azure) backend:

CREATE TABLE RAX_TABLE (rownum BIGINT NOT NULL, C01 VARCHAR(3200) NOT NULL)
CREATE CLUSTERED INDEX idx0RAX_TABLE ON RAX_TABLE(rownum)
INSERT INTO RAX_TABLE
SELECT ROW_NUMBER() OVER(ORDER BY C01) AS rownum, C01
FROM
  (SELECT CAST((((C01) + (4)) * (71)) % (13) AS VARCHAR(3200)) AS C01
    FROM
      (SELECT 1 + C AS C01
        FROM
          (SELECT (0 + T_1.s + T_2.s + T_4.s) AS C
            FROM
              (SELECT 0 AS s
               UNION ALL SELECT 1 AS s) AS T_1
            CROSS JOIN
              (SELECT 0 AS s
               UNION ALL SELECT 2 AS s) AS T_2
            CROSS JOIN
              (SELECT 0 AS s
               UNION ALL SELECT 4 AS s) AS T_4) AS T
          WHERE C <= 4) AS A) AS A
ORDER BY C01 OFFSET 0 ROW FETCH NEXT 3 ROWS ONLY

On Rax/MySQL it generates:

CREATE TABLE RAX_TABLE (rownum BIGINT NOT NULL, C01 MEDIUMTEXT NOT NULL)
CREATE INDEX idx0RAX_TABLE ON RAX_TABLE(rownum)
INSERT INTO RAX_TABLE
SELECT (@rownum := @rownum + 1) AS rownum, C01
FROM
  (SELECT @rownum := 0) AS init,
  (SELECT CAST((((C01) + (4)) * (71)) % (13) AS CHAR) AS C01
   FROM
     (SELECT 1 + C AS C01
      FROM
        (SELECT (0 + T_1.s + T_2.s + T_4.s) AS C
         FROM
           (SELECT 0 AS s
            UNION ALL SELECT 1 AS s) AS T_1
         CROSS JOIN
            (SELECT 0 AS s
             UNION ALL SELECT 2 AS s) AS T_2
         CROSS JOIN
            (SELECT 0 AS s
             UNION ALL SELECT 4 AS s) AS T_4) AS T
      WHERE C <= 4) AS A) AS A
ORDER BY C01 LIMIT 3 OFFSET 0

Note how similar these SQL snippets look. However, there are many subtle differences. For example, note that strings are represented differently:

CREATE TABLE RAX_TABLE (rownum BIGINT NOT NULL, C01 VARCHAR(3200) NOT NULL)
CREATE TABLE RAX_TABLE (rownum BIGINT NOT NULL, C01 MEDIUMTEXT NOT NULL)

Azure needs a CLUSTERED index on each table, we want to insert to:

CREATE CLUSTERED INDEX idx0RAX_TABLE ON RAX_TABLE(rownum)
CREATE INDEX idx0RAX_TABLE ON RAX_TABLE(rownum)

Numbering rows differs even more radically:

SELECT ROW_NUMBER() OVER(ORDER BY C01) AS rownum, C01
SELECT (@rownum := @rownum + 1) AS rownum, C01

And on MySQL we need some extra code:

(SELECT @rownum := 0) AS init,

Casting to string in both SQL dialects differs subtly:

(SELECT CAST((((C01) + (4)) * (71)) % (13) AS VARCHAR(3200)) AS C01
(SELECT CAST((((C01) + (4)) * (71)) % (13) AS CHAR) AS C01

Even something as simple as getting the first three rows has to be expressed differently:

ORDER BY C01 OFFSET 0 ROW FETCH NEXT 3 ROWS ONLY
ORDER BY C01 LIMIT 3 OFFSET 0

All SQL dialects differ in what can be expressed and how it has to be expressed. Different keywords, different order of keywords, different types, different additional functionality.

Rax protects the coder from these differences. Also, internally, Rax has a fallback mechanism. Rax tries to push as much of the processing to the database server, but if some function has not been implemented in the given backend, Rax falls back on a (usually slower) row by row processing: Rax fetches the input data from the SQL backend, executes the function and inserts the resulting data back into the SQL backend. This means that Rax offers the same functionality independently of the backend used.