CODEWITHSUNDEEP
capipostgresqlrdbmslibpq
user1254893
user1254893

Reputation: 537

A good strategy for writing a database abstraction layer in C

I need to write a database abstraction layer in C for the database access API of PostgreSQL (libpq) and possibly some other relational database management system.

I am considering writing my own abstraction of the existing functions in pqsql and possibly other database systems. What would be the best strategy to consider for writing such a database abstraction API?

So far my prefered plan of writing this would probably be:

  1. Define headers, functions and wrappers for any particular database systems of interest.
  2. When the first connection call (function) is used, check and determine for which relational database management system it is suited for.
  3. Include the right set of methods and definitions for database abstraction.

But this way of handling things would probably only allow to use one relational database management system at a time. Is this common for a database abstraction layer?

What other options could be considered?

Upvotes: 3

Views: 2906

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324455

Don't reinvent this wheel.

It's been done already, repeatedly, and while the most popular system (ODBC) is undeniably ugly it already has abstractions written for every database you've ever heard of. With the UnixODBC project it exists on UNIXes and POSIX UNIX-alikes like Mac OS X, too.

A somewhat less ugly alternative with much narrower database support is libdbi. You won't get drivers from vendors for libdbi like you do for ODBC.

How repeatedly?

If C++ is OK, another DB abstraction layer I've heard very good things about is that offered by Nokia (once TrollTech), the Qt framework's database interface. It has the major advantage of being already *written* and tested. The main downside is that it's a bit clumsy if your app core isn't also C++ and Qt based.

Other C++ options, ones I haven't personally used, are sqlapi++, dtemplatelib and SOCI.

If by this point you're wondering why there are all these database abstraction layers written for and in C++, maybe that's a hint. If you write a DB abstraction layer in C, I bet you'll land up reinventing C++ virtual methods via structs of function pointers, and probably end up creating something almost as ugly as GObject, in which case you might as well use libgdb / gnome-db. To avoid the fake-virtual-method junk you can make your DB layer selectable at link time via the runtime library path and just provide a bunch of plain old functions, but I don't know why you'd do this when so many existing options are around.

BTW, if it turns out you don't really need DB abstraction at all, only a nicer interface (since you're mainly targeting Pg), check out libpqtypes (plain C, no C++, very portable), otherwise known as "what libpq should be". Even if you're going to add an abstraction layer it may be worth doing it over libpqtypes not just libpq.

If ODBC, libdbi, libpqtypes, Qt, etc aren't suitable for you, you need to define why, and create a more specific set of criteria to satisfy.

Alternately: If your design permits, you might want to consider doing your database access and related work in an embedded higher level language with a DB abstraction layer like Python (psycopg), Ruby, Perl, or Java (JDBC). All of these have their own widely adopted and well tested database abstraction layers that you can benefit from while still writing much of your program in C. Or, for that matter, you can just write performance-critical bits of your program in C as a library and use it from the language of your choice with a sane database abstraction layer already written for you.

Database abstraction is hard. Really hard. Database abstraction that isn't horrible to use, performs decently, and is flexible enough to use the database as more than a dumb row store is even harder - and sooner or later, you're going to need more than a dumb row store.

Upvotes: 12

dbrank0
dbrank0

Reputation: 9476

I'm not sure if you're looking for something more specific or just generic idea. The usual way I have seen similar things done is by defining a common struct containing function pointers to all relevant functions.

struct dbms {
    int (*connect)(const char *cs);
    int (*disconnect)(void);
    int (*query)(...);
    ...
}

Then for each DBMS you fill this array with pointers to functions relevant for that DBMS. You then save this pointer to propely initialized struct somewhere, and use it to call (directy or from some kind wrapper functions)...

Whenever I see that I wonder why not simply use c++ and virtual methods.

Upvotes: 3

Related Questions