Jump to content
  • Advertisement
Sign in to follow this  
Angelic Ice

Abstracting SQL-Syntax

This topic is 416 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hello forum!

Disclaimer: I just marked this as C# but this issue might very well apply to every other language.

I wanted to implement a SQL-component with following goal:

The component shall be able to switch between database-implementations: SQL, PostgreSQL, ... and hide SQL's specific syntax.

Therefore I thought going with an interface or aggregation would probably work. But there is one problem, that is constructing a proper query without the caller needing to know the specific database-software implemented by the callee.

E.g. a caller should be able to run something as this:

Select(string name); From(string tableName) instead of actually  building the query itself :"SELECT 'name' FROM 'table'".

There are multiple ways that came up to my mind, the SQL-component would carry a string that is being build up via calls. As shown in my example, Select(string name) would set the beginning of the build, From(string tableName) the next part of it. Finally, the Query() method would return true or false depending on whether the build query-message was successful.

The downside to this approach are surely possible limitations and a very sensible integrity (wrong order of calling destroys the built query). I thought about the SQL-component owning string-fragments that represent nothing but sole parts of the actual query. One string representing the SELECT column, the next string representing the scheme and table.

A bit as in the Model View Controller pattern, where the string is the model being altered via the caller.

On the one hand, this would be more stable, order stops mattering, as these strings will be automatically merged together in the right order upon calling Query(). The only issue is, what if someone calls Select("test") but also Update("test")? One string should probably be rather saving the initial operation. Select() would change it to "SELECT 'test'" and Update() overwrite this to "UPDATE 'test'.

Nonetheless, I assume this problem has been solved many ways and I would be really curious on the common practice. If there is a name to the pattern, hearing that instead would be enough as well : )

Share this post


Link to post
Share on other sites
Advertisement
Since you mentioned C#, have a look at LINQ. It neatly solves a slightly more general problem.

Share this post


Link to post
Share on other sites

Nonetheless, I assume this problem has been solved many ways

 Yup, what you're looking for is an ORM framework, object-relational mapping framework.
Most popular in C# is probably Microsoft's EntityFramework (which is pretty nice). With VS tooling you can generate classes from a schema, manipulate instances of them and EF will translate all the actions to SQL in the background and execute it.
There is also Hibernate's port for .NET, NHibernate.
EntityFramework code looks somewhat like this:
// This will use the DB specific driver underneath registered for the provider you selected in your connection string,
// might also do nifty things like connection pooling, but you wont directly handle any of that.
using(var ctx = new EntityContext())
{
  // This is translated to a ' select * from CLIENT where NAME = "Phillips" limit 1' query or something along those lines.  
  // 'First' call is terminating so it executes the query and fetches the result
  var phillips = ctx.CLIENT.First(e => e.LAST_NAME == "Phillips" );
  // This will be translated to an update.
  phillips.DELIVERIES_MADE += 1;
  // This will execute any update EntityFramework recognised in its entities.
  ctx.SaveChanges();
}

It also supports arbitrary select with annonymous objects like this:
// This will issue a 'select ID, DELIVERIES_MADE from CLIENT' kind of query. 
using(var ctx = new EntityContext())
{
  // Here the terminating operation is "ToDictionary", if it isn't called, the query isn't executed.
  var deliveriesById = ctx.CLIENT
    .Select(e => new { Id = e.ID, DeliveriesMade = e.DELIVERIES_MADE })
    .ToDictionary(e => e.Id, e => e.DeliveriesMade);
}
Edited by TheChubu

Share this post


Link to post
Share on other sites

Just to add to the above, a task like this isn't really something you solve with 'a common pattern' (but see below), but is in fact a ton of work that has been done in various libraries. The basic idea is to give you a set of simple relational building blocks to construct queries and then, when contacting the database, translate the query into the relevant dialect. Unfortunately that is much more complex than just swapping out a few names here and there, especially when you consider the need to support the data definition language part of SQL (create, drop, alter, rename) as well as the data manipulation language part (select, insert, update, delete).

If you really have to do this yourself and cannot use an existing ORM or wrapper to do this, I recommend sticking to standard SQL (as that is more likely to be portable) and don't build up SQL arguments from strings; use your library's parameterised query system instead.

If you're interested, ORMs usually implement one of 2 patterns:

  • Active Record - You write special classes which magically map onto database tables. These are very convenient, and therefore make for rapid development, but it mixes business logic with persistence logic and some don't think that is good. Copying objects can become awkward (should it create an extra row in the table, or just another copy of the data in that row?) and other code can be complicated by it (because your 'plain' data objects are now filled with DB wrapping fields).
  • Data Mapper - You have objects that will be stored in the database, but those objects contain no DB logic at all. Instead there is some other class or classes which takes these objects and reads or writes from the database accordingly. This is cleaner from the perspective of the main applicaton data since it is not mixed in with database code; but the flipside is that your database code needs to be able to write out your business/game objects, which usually means there needs to be a 3rd abstract layer to prevent the database having a dependency on your application, etc.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!