Translating Database Queries

#database #esproc #opensource #sql

Did you ever wondered a tool that can be translating many database syntax's at the same time?

1. Prologue
2. Once upon a time
3. Translating SQL
4. Final Considerations

1. Prologue

I've been working professionally as a developer since 2018, and one of the hardest things to decide when I decided to start writing feature tests was: which database should I use as the base for our test cases?

Why do I say this? I mean, most of the tutorials on the internet at that time tell us to use SQLITE, which is strange since you probably don't run it in production.

As far as I can remember, videos and articles creating CRUD solutions using PHP, C#, Java and Python point directly to:

Oracle
MariaDB
MySQL
PostgreSQL

and of course that there's a lot of differentiation on these databases syntax.

2. Once upon a time

This is one of many scripts that I decided to convert between SQLITE and MySQL in a very specific project and I can tell you that is a PAIN IN THE ASS!

Being a junior/mid level developer getting weird tasks that might not be the best approach to solve a problem sucks totally, mostly because you have to stop and study the differences between databases and their syntax with a short deadline.

Since the ISO (International Organization for Standardization) and ANSI (American National Standards Institute) have decided that if you want to implement the SQL standard, you HAVE to implement the function, the name of the function itself doesn't really matter. Right? RIGHT??

Aggregators, functions and any other type of syntax related can be written in different styles and since it's implemented following the SQL-92 standards, will be recognized as an SQL database.

Take a look at the script below:

-- Enabling Foreign Keys in different databases
SET CONSTRAINTS ALL DEFERRED; -- PostgreSQL
SET FOREIGN_KEY_CHECKS = 1;   -- MySQL
PRAGMA foreign_keys = ON;     -- SQLite

-- Disabling Foreign Keys in different databases
PRAGMA foreign_keys = OFF;    -- SQLite
SET FOREIGN_KEY_CHECKS=0;     -- MySQL
COMMIT;                       -- PostgreSQL (after disabling it)

You can do exactly the same thing in different databases, but it's not that common to find a tool that will translate it from a single source.

3. Translating SQL

Imagine a tool that allows you to write a statement in SQL or even high order functions that are executed inside a database.

This tool can also be used to generate the same query that will be executed, but for different relational databases and even in other paradigms like Wide Column (ScyllaDB, CassandraDB).

Did you visualized it? No? So let me present you the esProc!

esProc is a powerful programming language that can work as a data warehouse, having many capabilities and one of them is to translate query statements to many different databases:

sql = select * from Orders where ADDDAYS(OrderDate,3) > ShipDate
sql.sqltranslate(MySQL) -> select * from Orders where (OrderDate + INTERVAL 3 DAY)>ShipDate
sql.sqltranslate(Oracle) -> select * from Orders where (OrderDate + NUMTODSINTERVAL(3, DAY)>ShipDate
sql.sqltranslate(DB2) -> select * from Orders where (OrderDate + 3 DAYS )>ShipDate