First of all, since I became one of the maintainers for NDOUtils Oracle I’ve been looking forward to commit this project back to the upstream. It is a bit tricky because current code has been merged from NDOUtils 1.4.b8 (partly) and it is using a self written Library for OCI (Oracle Call Interface, liboci). Even though SQL-Queries and the DB layout are slightly modified to fit to Oracle.
Focusing resources on Icinga and IDOUtils with the best source base so far includes using the libdbi as a database abstraction layer.
libdbi
So far, libdbi is written in C and consists of two modules:
- libdbi (API which is called from inside IDOUtils)
- libdbi-drivers (driver modules for each RDBM desired, loaded by libdbi)
Currently, the libdbi contains stable drivers for
- Firebird/Interbase
- FreeTDS (provides access to MS SQL Server and Sybase)
- MySQL
- PostgreSQL
- SQLite/SQLite3
Not completed drivers are
Testing the Oracle driver
As a matter of fact, I wanted to test the Oracle driver and it turned out to be really “not completed”. What I did (a complete description can be found here):
First step was to download and install the Oracle Instantclient and the SDK (remote Oracle DB). Next step: Install the libdbi. The libdbi needs to be installed first – if you try the drivers first, configure will fail because of missing dbi.h!
And the biggest part: The libdbi driver for Oracle. While the source in the tar-ball contains a typo in dbd_oracle.c the CVS snapshot had problems with automake on RHEL x64. Nice isn’t it? After having that fixed configure needs to be pointed to the Oracle lib and inc dir from Instant Client. Then I slightly modified the DB scheme (kicked the table prefix, renamed one table and all primary keys to fit the maximum of 30 characters (Oracle!)). Also hacked some C-Code into IDOUtils to be ready for the first test (only a “Hello World”-SQL-Query).
Starting IDOUtils with Oracle config, getting nice error messages and not that good memory address exceptions on the Oracle server. Meaning, the libdbi driver for Oracle doesn’t even work with one single test query :-(
Future Outlook
For that reason I have been looking forward towards another solution. Reading the libdbi mailinglists there has been a discussion about rewriting the Oracle driver with ocilib. Concerning the fact that libdbi API doesn’t even support parameter bindings (which are essential for performance) it would be very tricky to get a working solution as soon as possible.
ocilib looks very nice and is a very mature and stable project which is why we are planning to build IDOUtils with Oracle support on that base. This will be less work than a libdbi driver but also enough to implement, test, debug and hopefully release soon.
Meanwhile our focus will relay on implementing IDOUtils support for the stable libdbi drivers just as Postgres or SQLite.
If anyone reading this and having knowledge of normalizing the SQL queries feel free to contact us! It will be very much appreciated getting more DB knowledge into this project! :-)
I know the problem with supporting multiple database systems from one application. We’re building an app which connects to SQLite, MySQL, Oracle and others. The core problem is (or was) that we want to avoid the usage of different SQL dialects within our source code.
Imagine a simple range query (not unusual for web applications). For a full-blown example, see http://www.sqlzoo.net/howto/source/z.dir/tip857849/oracle
So, instead of hard-coding error-prone SQL dialects, we decided to develop a SQL parser for converting our generic SQL queries into specific dialects. We embedded this conversion layer into our database drivers, so we now have something like ODBC – but much much smaller and faster.
In fact, the SQL conversion is just a (more or less) simple LR(1) parser (using yacc) for SQL syntax. We cache the converted/compiled SQL statements and are now working on a cache for prepared SQL statements to get a lightning-fast database interface. 8-)
Btw: when developing with OCI, just do some tests with Valgrind. :)
My latest experiences are some years back now, but at least OCI 9.2.0 had some serious memory leaks.
When one starts writing a database driver from sratch, chances for a memory leak are higher to be within the own code than in the underlying libs. Not so with OCI. ;-)
Yes, OCI had some leaks… but less now and however they are not formal memory leaks (but ‘still reachable’ data) !
Anyway, all oracle drivers (whatever the language or technology) are based on OCI (except Oracle JDBC thin driver and 2 commercial wire-protocol based drivers).
OCILIB does not have known memory leaks and it has been heavily tested under valgrind.
Thanks Vincent for your reply :-)
Recently I’ve tried only a short configure, make, make install on ocilib using the Instant Client and the SDK and it worked like a charm. This is something I cannot tell about the libdbi-drivers (of course working with the source and not installing prepared packages).
I am planning to migrate ocilib with an extra configure flag for Icinga (still –enable-idoutils hast to be set) and then point the code to ocilib, prepared statements and queries using parameter bindings and all the good stuff of ocilib.
Source will remain compatible with libdbi where I am testing Postgres right now and surely adapt this in a more or less ‘parallel way’. Simply because queries are different in any RDBM-language and therefore it makes sense to gather the best working solution for all of them.
Yep, i know what you mean – I have been thinking of applying some kind of EBNF for SQL and sending that query to a wrapper function which translates that to the SQL specific for each RDBM. Since the IDOUtils inherit from the original NDOUtils which is nothing else than an INSERT/UPDATE/DELETE module for Nagios/Icinga data database drivers will be defined during configure an then loaded at runtime.
Meaning that you will have prepared drivers, best case in a widely used repository and no need to code them yourself.
So another approach getting the different queries alltogether (I’m not talking of Oracle here, but also all the other RDBMs which should be supported and I’m currently working on):
Building extended working functions for the queries – some kind of bind params for all kind of modifying the database. This is a heavily must also for Oracle in the near future because of extendable code and getting more modular in the code. Regarding that most of the queries only use one table and its attributes it should be an easy thing to do.
Meanwhile thanks for your hints and the link – I will catch on that for sure making life easier. For gods sake the other guys from NDOUtils Oracle have done a lot of work getting Oracle to work, so the process will be more merging than recreating SQL queries. The tricky part will be seperating libdbi and ocilib and for sure, error matching what the user could do wrong ;-)
PS: I’ve talked to the creator of ocilib and I’m pretty sure that it will work fine regarding all the projects using the library.