After a long time debugging, code re factoring, implementing and testing new stuff another success story for Icinga!
Let’s start with the details :-)
IDOUtils is a “simple” kind of INSERT, UPDATE and DELETE application so no worries about difficult SELECT and JOIN. Meanwhile the biggest problem are the queries which are not normalized and truly MySQL specific. Since Hendrik provided the normalization of the INSERT queries I stepped further into the INSERT OR UPDATE queries. Those are non-standard and i.e. Postgres or Oracle do not support them.
Oracle queries can be taken from NDOUtils Oracle mostly using a trick to MERGE statements (props to David Schmidt!), but the main goal is to rewrite all of them to fit parameter bindings. Check this article for more information.
Postgres is a bit more difficult because MERGE or UPSERT (another approach to INSERT OR UPDATE) is not yet supported. The documentation points to a function written in plpgsql.
The biggest problem – how to break up the code and create functions which are deciding upon RDBM which query to build and execute?
My first approach using on single function which gets different queries as string and decides what to do was a nice try but simply failed looking at the design – being generic and making the code more readable, too.
So I tried it the hard way and split the code for the SQL-queries from dbhandler.c into a new file called dbqueries.c – for the INSERT OR UPDATE queries for the first part.
One single function for each query has been implemented and performs then the RDBM specific query. For Postgres I decided to try an UPDATE in the first place and if there are now rows affected an INSERT will be issued instead. Unique constraints defined in table scheme are matching the UPDATE constraints.
Furthermore there were other issues to fix for Postgres – string escaping, Unix time conversion functions and the task of getting the last insert ID. MySQL doesn’t use defined sequence ids (dunno yet how last insert id works…) but Postgres does that for each table. After a hard night debugging I fixed that too – now the table relationship is working for Postgres too.
Concerning the fact that each value is stored in the data array it’s quite simple to implement more queries for more RDBMs – MySQL and Postgres will take care of giving you examples on how to build the queries :-)
You’re very welcome to test Postgresql in upcoming Icinga 0.8.3!
Looking forward to Oracle and ocilib =)