I use PostgreSQL exclusively. I have no plans to ever change this. However, I recognize that other people are not me, and they instead use MySQL, MS SQL, IBM SQL, SQLite SQL, Oracle SQL and ManyOthers SQL. I'm aware that they have different names in reality.
My queries look like:
SELECT * FROM table WHERE id = $1; UPDATE table SET col = $1 WHERE col = $2; INSERT INTO table (a, b, c) VALUES ($1, $2, $3);
My database wrapper functions currently support only PostgreSQL, by internally calling the
I wish to support "the other databases" too. This would involve (the trivial part) to make my wrapper functions able to interact with the other databases by using the PHP functions for those.
The difficult part is to reconstruct the PostgreSQL-flavor SQL queries from the application into something that works identically yet will be understood by the other SQL database in use, such as MySQL. This obviously involves highly advanced parsing, analysis and final creation of the final query string. For example, this PostgreSQL SQL query:
SELECT * FROM table WHERE col ILIKE $1 ORDER BY random() LIMIT 1;
... will be turned into WeirdSQL like this:
SELECT * FROM table WHERE col ISEQUALTOKINDA %1 ORDER BY rnd() LIMIT 1;
I don't require support from any other input SQL flavor than PostgreSQL, but the output must be "all the big SQL database vendors".
Has anyone even attempted this? Or is it something that is never gonna happen as free software but might exist as a commercial offering? It sounds like it would be a thing. It would be insanely useful, and "crazier" projects have been attempted.