Converting a MySQL statement into a SQLite statement in Java 11-CodePudding

I scoured Google for answers on this, but couldn't find any, which is why I'm asking here.

So, I have a MySQL statement,

INSERT INTO `db`.`table`('mycolumn') VALUES ('myvalue')

, etc. So, what I'd like to do is to convert this into a SQLite statement. If you didn't know, SQLite statements don't have the `s on them, and are just the table name. Is there some kind of regex I can use to convert a MySQL statement String into a SQLite statement String?

Thanks for reading and hopefully answering!

CodePudding user response：

Converting from one DB's SQL syntax to another is, effectively, impossible. Or at least involves a sizable SQL parsing engine (we're talking many many thousands of lines of code here).

The problem is: There are tons of ways one DB engine just isn't like the other. In most DB engines, if you only want 100 results, you slap a LIMIT 100; at the end of it. But in earlier versions of oracle, that didn't work, and, surprisingly, LIMIT is not in the SQL spec (nothing about limiting the amount of results returned is, in fact). In oracle you had to use the 'magic' field named ROWNUM: ... WHERE ROWNUM < 100 is how you did it.

So how would you go about writing an engine that would turn SELECT * FROM foo LIMIT 100; into SELECT * FROM foo WHERE ROWNUM < 100;? Before you answer consider what happens when you chuck a boatload of inner selects, joins, windowing functions, and some GROUP BY and HAVING clauses in there.

Some DB engines have windowing functions, and some don't, which means you can write SQL that runs in one DB engine and does a job that is impossible to write in terms of SQL in a lesser DB engine.

The list goes on and on. So, the general job of converting a statement that does X in a MySQL engine to a statement that does the identical thing in any other engine, is simply not a feasible task. It's way too difficult, and isn't worthwhile enough, so nobody's done it, and nobody is likely to.

Instead if there is an absolute need to write SQL statements that run in quite different engines, you write a higher-level SQL-like language which can be translated into every engine. This is more or less what Hibernate's HQL is all about.

If you can limit the scope of your requirements into just 'rewrite the refs to identifiers specifically, anything else MySQL specific, eh, that's fine it won't run on sqlite but that is fine with me', it's trivial:

mySqlStatement.replace("`", "\"");

Is all you need, although there are a million ways this can go wrong (for example, if a ` character shows up in a string constant).

But those are your only options: That simple code that works on the simplest things but will fail on anything more complex, or spend 4 years reverse engineering mysql's entire parser engine, recreate it in java, and find formulaic approaches to fitting the square peg that is mysql's specifics into the round hole that is sqlite's specifics.

NB: If you're specifically looking for a combination of 'server-based configurable somewhat powerful DB engine that is free' and 'file-based super simple DB engine that is free' and you want all that just for java, postgresql h2 is a much better combo. Those 2 DBs' respective SQL syntax is much, much more similar. Most statements 'just work' in either engine.