1

I am pretty new to databases; I am currently taking an online course from Stanford about relational algebra, database design, and fundamental and advanced features of the SQL language.

I am going to be writing an application that uses databases extensively (like most applications nowadays), and of course, I would like to abstract away the specifics of the database itself. Ideally, I'm hoping to build a module of my program with a public function that takes SQL code as input and modifies or returns data from the database as specified by that code. I don't want any of the rest of my program to care what database system I'm using (MySQL, SQLite, etc.) underneath. This obviously makes the lack of agreement between the different dialects of SQL used by various vendors a problem.

From what I can tell, this is currently impossible. It seems that my best option would to be to simply use a system like PostgreSQL that strives to follow the standard as closely as possible, but even then, it doesn't seem that PostgreSQL supports the complete standard exactly. Worse, I am then tied to the implementation (read: performance characteristics) of that one SQL system.

So what should I do? Is there a tool that can, at least somewhat reliably, convert between SQL dialects? I'm guessing that that won't even be enough, when you take into account things like triggers that are persistent in the database itself, and therefore affect more than the data and the code in which they are specified. Would I then need a tool that sits on top of the database system and stores its own persistent data? Again, does such a tool even exist? Or should I just suck it up, pick a particular system, and have my application code generate SQL code that that particular system accepts?

If the latter, I am interested in the possibility of later developing such a tool. I realize that it would be an absolutely monstrous task, so where should I go to gauge interest in such a project?

2

There is no DBMS that implements the entire SQL standard.

Depending in what programming language you are developing, there always are interfaces for communicating with a DBMS through SQL commands (e.g. OLEDB, ODBC).

The most important thing to keep in mind is that the "dialects" you are referring to are almost identical. That is, the difference between them is either very small or some don't implement a very unusual case and structure that is part of SQL standard.

Just because something is a part of the standard does not mean that you will ever need it. Unless you have very specific intentions and your applications needs exactly this one thing, but then your decision would already be obvious as you pick the one implementing it.

The performance difference is indeed an important part since SQL is declarative and the actual quality of the DBMS shows on how fast or with how much load request are executed. Here again it depends on your usage. PostgreSQL is a good DBMS, but for very large projects IBM's and Oracle's solutions might be needed. But judging on you starting to develop an application even MySQL should do the job.

Defining Triggers would need to be done in the DBMS, almost all have a good GUI that lets you easily set things up. There is no need for an additional tool.

In the end you indeed need to pick a DBMS an develop for that specific Database you are using. Though you application does not "generate SQL code that that particular system accepts" you will and always should use prepared statements that your applications simply fills with data. When talking about SELECT, CREATE, DROP statements there are likely no differences between most DBMS anyway (with a few small exceptions).

I have the feeling that you are still very new to databases and are viewing things at a very theoretical point of view. I think you should take look at what a DBMS actually has to do behind the scenes. This is why you cannot just implement a tool to put on top of a DBMS to suddenly create functionality that the SQL standard has or another vendor has implemented.

If you take a look at Wikibooks you can see the differences are not big, if even existing.

  • "I have the feeling that you are still very new to databases..." Yes, how did you know? ;) As for triggers and similar things: my application will be in charge of handling a local database for a particular user, so since I won't be able to use the DBMS GUI on each user's machine to set up their particular database, I'll need to do it from code. I'm guessing SQLite will be well-suited to this purpose? – Sam Estep Aug 1 '15 at 17:08
  • 1
    @RedRoboHood Well yes kind of obvious after your first sentence :D In case you want to create an application using a local database, then you can create one database with all triggers and ship it with the installation of your program. Then you don't need to configure thing manually for each client (unless they all need a different DB for some reason). BTW I'm still a computer science college student so no big expert :) – John Aug 1 '15 at 18:02
  • @John It seems I just need to learn more about the specifics of how to use SQLite and the JDBC (since I'm using Clojure for this application). Thanks so much for your help! :) – Sam Estep Aug 1 '15 at 18:42
  • @RedRoboHood No problem! Glad I could help! – John Aug 1 '15 at 18:53
  • I agree with this answer, the original poster has bigger things to worry about than the relatively minor differences between SQL databases. My only disagreement is the comment about needing IBM or Oracle databases rather than Postgres for bigger needs; those would have to be mighty big needs indeed as I know people running terabytes of data in Postgres to successfully keep afloat multi-million dollar businesses. I would certainly recommend Postgres to a serious beginner as Postgres is probably the most standard-compliant SQL database. – Basil Bourque Aug 1 '15 at 23:13

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.