-2

There is an existing application that supports having its data in an Oracle database or a Postgres database, through JDBC. The tables and data are the same, but it can be installed in any of them, to the customer's choice.

For each SQL query for Oracle there is an equivalent one for Postgres, which does exactly the same but with minor SQL dialect differences. Now I have to provide a third set of SQL queries, in this case for SQL Server. Is it more convenient to base them on the Oracle ones or the Postgres ones? Which dialect is closer to the SQL Server one?

1
  • Can any downvoters give advice as to how to improve the question?
    – golimar
    Jun 16 at 12:05

2 Answers 2

2

From a code maintenance perspective, I would say neither.

Let each query [class?] stand on its own.
If you start inheriting one from the other and something changes in the base dialect, you'll inadvertently change any derived ones as well, which probably won't be appropriate.

Alternatively, you could have just a single set of queries that works with the Lowest Common Denominator, ODBC, thereby bypassing any dialect-specific issue (which will be handled by the ODBC driver).

2
  • The base dialect will only be used at first to build the queries for the other dialects, but when those are finished, both queries will be independent from each other
    – golimar
    Jun 15 at 14:10
  • 1
    Even with ODBC I think that dialect differences will still be there, like i.e. FROM DUAL which is sometimes needed in Oracle but is invalid in other DBMS
    – golimar
    Jun 15 at 14:14
2

My advice is to separate them out - completely separate your calling functions - specify the db as a global variable or store it in a file somewhere and read it on programme start up.

The reasons behind this approach are:

  • all of these servers have their strengths and weaknesses. Take PostgreSQL's GENERATE_SERIES() functionality - that will have to be (at least for the time being) emulated in the others - Oracle has CONNECT BY and/or you can use a RECURSIVE CTE (not sure what SQL Server has in this area, apart from RCTEs).

  • Oracle's PL/SQL is probably the most powerful of the procedural extensions - might require code in other systems? If you go down the Lowest Common Denominator route, you'll lose all the power of PL/SQL (and the capabilities of the other systems).

  • Same for T-SQL and SQL Server.

  • PostgreSQL's extension facilities are amazingly powerful and flexible and don't really have a counterpart in the other systems. Same goes to the many different index types available in PostgreSQL.

I worked for a provider of an ERP system to one of Europe's largest railway networks - literally billions of widgets, millions of lights, switches... the list is very long.

I was new on site and I ran a query looking for FOREIGN KEYs to get an idea of what was connected to what in terms of tables. I retrieved the empty set as a result. Puzzled by this (thought there was a mistake in my SQL), I asked the lead programmer and he told me (with pride) that the system was "designed such that all searches used the PK and that FKs therefore weren't needed".

After picking my jaw up from the floor, I was like (to myself) "So what's going on? Where's DRI (Declarative Referential Integrity) - what about orphaned records?") - but no, that wasn't a concern, apparently - until it was. I subsequently found out that they ran nighttime jobs to check for this!

The root of this was that they were trying to be "all things to all men" and run on any (of the major) systems - this led to the LCD (Least Common Denominator) approach. One is reminded of Aesop's fable - try to please everybody and you end up pleasing nobody! They could have stored the data in SQLite, or with a bit of programming, Notepad <sarcasm>with all of its power and flexibility!</sarcasm>.

Another factor to keep an eye on is that vendors are always upgrading and adding new features - you'll have to have a "rolling upgrade" policy of reexamining your code every so often to see if you can take advantage of these features and move (procedural - in your case Java) code into the db. IMHO, database code belongs, as far as reasonably possible, in the database, plus you benefit from fewer round trips to the server thrown in for free. Your RDBMS is your last line of defence for your data - make use of its capabilities!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.