Database abstraction layer

[heruan]

AFAIK Zabbix comes in two SQL flavours, PostreSQL and MySQL. Why not to adopt a database abstraction layer, to decouple source code from the SQL dialect?

P.S. I hardly think this wasn't discussed already, but I've searched for database abstraction and found nothing. Links to previous discussions on this would be very appreciated!

[kloczek]

AFAIK Zabbix comes in two SQL flavours, PostreSQL and MySQL.

Nope. Zabbix can be used as well with Oracle, DB2 and it can be used as well with SQLite DB backend.

https://www.zabbix.com/documentation/3.0/manual/installation/requirements

Why not to adopt a database abstraction layer, to decouple source code from the SQL dialect?

Because it was easier to do this way initially.
At the moment zabbix uses almost everywhere SQL92 queries and in all those points nothing needs to be changed to reuse code implementing DB backend interaction.

P.S. I hardly think this wasn't discussed already, but I've searched for database abstraction and found nothing. Links to previous discussions on this would be very appreciated!

IIRC there is only about 5 points in whole zabbix code which have conditional code depends on DB backend type so theoretically zabbix code is in perfect shape to implement DB backend support as loadable module.
Why no one is working on this?
Answer is very easy: because there are tons of other things which are far more important to implement
Feel free to start working on final zabbix code on final push on DBfication :P

[heruan]

Thank you kloczek for your reply! I didn't know about support for Oracle and DB2, I guess I was mislead by the Debian packages which are compiled for MySQL and PostgreSQL

If I understand correctly, make Zabbix support a new SQL backend it's "just" a matter of building a new module? I'd like to give a try on a module for SQL Server, can you point me to the right direction, like where do I start to look in the source?

Thank you very much!

[kloczek]

If I understand correctly, make Zabbix support a new SQL backend it's "just" a matter of building a new module? I'd like to give a try on a module for SQL Server, can you point me to the right direction, like where do I start to look in the source?

At the moment if you may want to add for example MSSQL support (which is SQL92 spec compliant as well) all what you need is to go add conditional part depends on MSSQL similar to those for MySQL.

[kloczek@domek src]$ grep -r HAVE_MYSQL * -l
libs/zbxdbupgrade/dbupgrade.c
libs/zbxdbupgrade/dbupgrade_2010.c
libs/zbxdbhigh/proxy.c
libs/zbxdbhigh/db.c
libs/zbxdb/db.c
zabbix_server/housekeeper/housekeeper.c

In case of rewriting zabbix code to provide DB backend type abstraction support as loadable module it will be necessary to replace those conditional parts by calling functions which code will be loaded from module.
Generally all DB engine dependent code is about:
- DB connection initialization and disconnection (on shutting down zabbix srv/prx)
- DB schema upgrade. SQL92 spec does not say to much about general queries which could be used on such operations and all ALTER queries used by different SQL engines have some own set of variations of those queries.
- housekeeper is last part because depends on some exact functionalities of DB engines sometimes it is possible to do HK using DB engine specific queries way aster than using just plain DELETE queries.
To write down DB engines abstation as module you need to have knowledge about how to write code of the elf DSO (Dynamic Shared Object) which will be loaded into process using ldopen(3). Such example you can find in zabbix code in src/modules/dumm. Module like that dummy one is loaded in zabbix code here:

[kloczej@domek src]$ grep -r dlopen * -l
libs/zbxmodules/modules.c

This code is common part of all zabbix components (server, proxy and agent).