[icinga-devel] [RFC] SQL Queries

Michael Friedrich michael.friedrich at univie.ac.at
Sat May 16 21:28:58 CEST 2009


Hi there,

Hendrik Baecker wrote:
> -----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)
> - ----------------------------------------------------------------
>   
I am aware of those remarks, but I'm happy to see (more or less not) 
that even PostgreSQL doesn't really support the MySQL "easy to use" 
Queries, The NDOUtils Oracle are most of all completely different from 
the MySQL-Code, this is a by design fault not trying to be more generic 
(blame Ethan for that).

Thanks for giving the idea so clearly, I know that I have to review and 
rewrite and restest the IDOUtils after getting them to work with the 
Icinga Core (which so far is done using MySQL, but there's as you said 
more stuff to do).
After that, Query-Review, adding and setting common stuff and for what I 
can say, I think we need to use different #ifdef #else Statements for 
some Queries, als you mentioned the UNIX_TIMESTAMP, which is completely 
different in Oracle. But, and that's a good point, not the whole Code 
and every single Query. When I will get more time to hook up with those 
things, I am on it (currently I have to startup another project too).
> 1) I'm not enough a database guy to know the sql standard. So I would say:
> "Wanted: Better common queries - dead or alive." ;)
>   
You are the one hacking C in deep, I'll review it and can take most of 
the Queryparts or would you like to change? ;-) And for the DB-Design 
... well, we have to add some DB tests after releasing version x - 
should be possible (just take the crap design now, and build from gotten 
data a static new scheme with data, and test the performance with 
reading, writing, and so on).
> 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.
>   
Thats a good idea because I do not like the idea committing such stuff 
only tested on our boxes directly to the master.
> 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?
>   
I don't know how much time it will take to get time() in C (cpu cycles) 
in comparison to DB based time() (which is server#s unix time to read)
> When I think of different timezone support it could be nicer to handle
> the time from the database. What do you think?
>   
Taking the time from the DB is just getting the time from the server 
where the DB runs on. So if you use DB based time and 2 servers (1 in 
Europe with Icinga, 1 in America with the DB) it depends on what you 
prefer or which time is essential to you. This is also o good point to 
discuss :)
> 5) No idea.
>   
MySQL: LIMIT 0,1
Postgre: LIMIT 0 OFFSET 1
Oracle: WHERE ROWNUM BETWEEN 0 and 1

(correct me if I'm wrong - I hate those syntax differences, it will 
bloat the IDOUtils code a lot again)

Another idea which just popped into my head - what if we provide a full 
generic code with all different DB entities and by a grammar the Code is 
being matched by configure and "recompiled" only for the desired DB, 
only outputting the desired C code for example MySQL. I know for sure 
that this is against everything we have discussed so far and stands 
against the idea of the libdbi and abstraction but It just came to my 
mind and I wanted to tell you ;-)

Kind regards,
Michael
> - -
> Hendrik
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkoOwR4ACgkQlI0PwfxLQjkw4gCeLhW+IEObQwt4VT7UV1nHUuMn
> 9ecAn2zVp59N6H4wHz2YPYdHsJj64ABh
> =2l7x
> -----END PGP SIGNATURE-----
>
> ------------------------------------------------------------------------------
> Crystal Reports - New Free Runtime and 30 Day Trial
> Check out the new simplified licensing option that enables 
> unlimited royalty-free distribution of the report engine 
> for externally facing server and web deployment. 
> http://p.sf.net/sfu/businessobjects
> _______________________________________________
> icinga-devel mailing list
> icinga-devel at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/icinga-devel
>
>   




More information about the icinga-devel mailing list