[icinga-devel] [RFC] SQL Queries

Hendrik Baecker andurin at process-zero.de
Sat May 16 15:35:26 CEST 2009


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi List,

"mastermind" brought us some thoughts on the icinga tracker (Bug #84),
which I would like to discuss with you.

C&P from the tracker and my first comments below:
- ----------------------------------------------------------------
well just by switching to some sort of abstraction layer you wont
magically get portable database code. From taking a quick look at the
code in say
https://dev.icinga.org/repositories/entry/icinga-core/module/idoutils/src/db.c
I would say that this code has no chance of working on anything EXCEPT
MySQL. A few comments on the code(note that I have not actually tested
idoutils though):

1) there is no such thing as INSERT INTO foo SET ... in the SQL Standard
(see INSERT INTO %s SET instance_name='%s' and others) - use INSERT INTO
foo (instance_name) VALUES for that

2) consider using CURRENT_TIMESTAMP instead of now() the former is
actually in the SQL Standard though still not universally portable

3) stuff like ndo2db_db_escape_string() should never ever been done by
hand, either use the escaping functionallity provided by the
corresponding database library or even better just use prepared
statements and out-of-line parameters. The PostgreSQL codepath there is
wrong under a number of circumstances and I would guess trivially
exploitable as well.

4) FROM_UNIXTIME/UNIX_TIMESTAMP are MySQL only - you would either have
to provide sql-level functions for other databases or getting rid of
them alltogether

5) stuff like ORDER BY %s DESC LIMIT 0,1 should be avoided (LIMIT foo
OFFSET bar is better as far as that it works in MySQL and PostgreSQL at
least but not in other major RDBMS)
- ----------------------------------------------------------------

1) I'm not enough a database guy to know the sql standard. So I would say:
"Wanted: Better common queries - dead or alive." ;)

2) If we get to the point to really speak/connect to different
databases, I would establish a seperate git branch with tweaked time
dealing queries so that users might test them in the wild.

3) there exist some escaping/quoting within the dbi library. I'll open a
feature request against the idoutils to test it.

4) Is there a hint for a sql-level function? On the other side it
shouldn't be a problem to use c based time() function.
I saw many queries using this and have to ask: What would be better for
performance? times from the code or from database?
When I think of different timezone support it could be nicer to handle
the time from the database. What do you think?

5) No idea.

- -
Hendrik
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkoOwR4ACgkQlI0PwfxLQjkw4gCeLhW+IEObQwt4VT7UV1nHUuMn
9ecAn2zVp59N6H4wHz2YPYdHsJj64ABh
=2l7x
-----END PGP SIGNATURE-----




More information about the icinga-devel mailing list