Project overview

TSQLParser is a Java based SQL parser with support for SQL-Temporal-Extension syntax. It is based on JSQLParser project,with the added syntax support. It also provides query rewrite capability from the temporal T-SQL form to standard SQL.

Temporal SQL Extension

The current supported SQL time extensions of the parser are:

AS OF SYSTEM TIME returns data as it was at specific point in time:

select * from foo as of system time 2011-08-12 13:02:00;

VERSIONS BETWEEN returns all versions of data between two timestamps:

select * from foo versions between system time 2011-08-12 13:02:00 and system time 2011-08-12 13:02:00;

select * from foo versions before system time 2011-08-12 13:02:00;

Rewrite capability

Beside parsing the TSQL, the the parser can rewrite the TSQL queries to a standard SQL form with added timestamps information. Example:

From:

 INSERT INTO tab VALUES(1,"hello world",'this is me') 

Into:

 INSERT INTO tab VALUES (1, "hello world",'this is me', now(), '2037-12-31 23:59:59.0') 

From:

 DELETE FROM tab WHERE b="7" 

 UPDATE tab SET tend=now() WHERE b = '7' 

From:

 SELECT * FROM tab t1, tab t2 WHERE c=7 AND t1.a=t2.a 

 SELECT * FROM tab AS t1 , tab AS t2 WHERE (c = 7 AND t1.a = t2.a)   
 AND t1.tend= '2037-12-31 23:59:59.0' AND t2.tend= '2037-12-31 23:59:59.0' 

Limitations

• Current supported DBMS are MySQL and PostgreSQL
• Depending on the DBMS, TSQL-Parser generates different formats of timestamps in order to have a common granularity of microseconds
• MySQL: Timestamps are in a Decimal(20,6) form, where a date 2011-12-31 23:59:59.123456 is translated into 201112312359.123456
• Postgres supports natively microseconds storage.

Contact

Carlo Curino

License and Restrictions

The TSQLParser is a free software released under the LGPL v.3 license.