[icinga-devel] Draft - making NDOutils/Idoutils obsolete

Rosecrans, William O william.rosecrans at alservices.com
Tue May 19 17:05:20 CEST 2009

Perhaps some of the existing Nagios philosophy could be used for defining aspects of the database interface in config files that keeps the actual compiled parts of the database interface layer relatively clean.  The definition for the generic sql interface would be as complete as convenient.  Then, definitions for specific sql interfaces would define additional queries, and replace some generic ones.  Straight from my imagination :

define sqlinterface{
    interfacename     mysql
    driver     mysql
    use     generic-sqlinterface
    some_specific_action    "Select %Foo% from %Bar% -- No Subselects!"
    something else     "Insert %Foo% into %someplace%"

define sqlinterface{
    interfacename     actual-mysql-special_table_type
    use     mysql
    some_specific_action    "Select %Foo% from %Bar% where Index = Potato"
    dblocation	dbserver.example.com
    dbuser		MRFANTASTIC
    dbpass		1234

Maybe the example explains better than the explanation.

-----Original Message-----
From: Axel Liljencrantz [mailto:axel.liljencrantz at freecode.no] 
Sent: Tuesday, May 19, 2009 1:33 AM
To: icinga-devel at lists.sourceforge.net
Subject: Re: [icinga-devel] Draft - making NDOutils/Idoutils obsolete

On Mon, 2009-05-18 at 17:59 +0200, mareadmin wrote:

> i think, there should be no non-standard-queries at all to keep 
> compatibility with as much rdbms as possible ... if this is possible, 
> i don't know.

Yes, it would be very desirable. But it is very close to impossible in practice. There are an amazing number of incompatibility issues between different databases:

* Oracle treats empty strings as nulls, other databases treat null and an empty string as two different concepts.

* Databases have extremely different performance characteristics, e.g.
you need subselects to get any work done in Oracle, but they are amazingly slow in MySQL.

* Different databases use different rules for case sensitivity, e.g.
oracle implicitly converts everything to upper case, mysql is case sensitive on table names but not on column names.

* Different databases use different quoting rules, both for string literals and for quoted field names.

* Most databases are typed, but some, like sqlite, are more or less untyped, which makes them behave subtly different in some situations where casting is involved.

* Bulk inserts (insert many lines into a table with a single query) are not covered by standards, but is a critical feature if you want to insert large chunks of data in semi-reasonable time.

* Inserting a column and getting back the row id of the column inserted is often useful, and also handled differently in many databases.

I could go on until tomorrow afternoon. Databases are a bit of a hopeless mess. Most of these issues can be somewhat papered over with a database abstraction layer, but at a cost in expressiveness and speed.
I've seen high-tech abstraction APIs like SQLAlchemy used in large, complex projects. SQLAlchemy allows you to write a database query as plain old Python code, and it then rewrites it into a hopeless mess of buggy, slow SQL in the dialect of your choice. In my opinion, the best trade off is a moderatly thin abstraction library and trying to write most queries in a database independent manner, but making special optimized versions of a few, special queries. I think SUN got it reasonably right with JDBC, though I'm sure others disagree.


Konsulent, Fri Programvare / Free Software Consultant
Cell: +47 - 473 44 003
Phone: +47 - 21 53 69 00, Fax: +47 - 21 53 69 09
Addr: Nydalsveien 30b, 0484 Oslo
Web: http://www.freecode.no/

«Some people have told me they don't think a fat penguin really embodies the grace of Linux, which just tells me they have never seen an angry penguin charging at them in excess of 100mph. They'd be a lot more careful about what they say if they had.» - Linus Torvalds

- - - - - - - - - - - - - - - - - - - - - - - - - -
This message is intended only for the personal and confidential use of the designated recipient(s) named. If you are not the intended recipient of this message, you are hereby notified that any review, dissemination, distribution or copying of this message is strictly prohibited. This communication is for information purposes only and should not be regarded as an offer to sell or as a solicitation of an offer to buy any financial product, an official confirmation of any transaction, or as an official statement of Aurora Loan Services. Email transmission cannot be guaranteed to be secure or error-free. Therefore, we do not represent that this information is complete or accurate and it should not be relied upon as such. All information is subject to change without notice.

More information about the icinga-devel mailing list