PostgreSQL / Generic Database - PATCH

[night199uk]

i saw there was a generic database patch a while back

this no longer patches cleanly against the current version - i really need to get multiple database support (i.e. not MySQL) on Cacti for a deployment i'd like to do... therefore i have done a lot of work, basically rewriting this patch to get postgresql support into cacti & cactid...

so attached here are two patches:
patch for cacti-0.8.6j to add postgresql support
patch for cacti-cactid-0.8.6i to add postgresql support

the patch for 0.8.6j contains a 'postgres.sql' that contains the converted 'cacti.sql' for postgres, just dump it into your postgres database.

note the changes in include/config for port/dbtype parameters.
also note the changes in the cactid.conf to specify dbtype.

lots of changes on cactid especially to abstract the mysql commands out of the core code. i have tested with postgresql but i believe there may be a few bugs left in the mysql stuff, i'll work on tidying these up over the next few days. also the configure stuff and macros have changed including a new acinclude and some new mysql/postgresql macros to make life easy... you'll need to rebuild the configure script with aclocal/autoheader/autoconf/autmake. cactid is a WIP as yet, its not the cleanest abstraction so far just a first pitch.

in cacti there are some group by clauses that have been heavily modified, a couple need a little more work yet but i have tested quite extensively and it seems to be pretty bug free. Disclaimer, I have not modified export.php yet for XML exports, i'll take a look at this later. import.php is fixed up...

my eventual goal is to add sybase & oracle support also to both cacti and cactid.

TODO
fix up any last few bugs.
add a patch against cacti+plugin arch.
take a look at patches for mactrack and other modules.
start on sybase
start on oracle

comments & testers wanted please. please be aware it could ruin your life, so don't hold me responsible, it's very alpha still.

ciao

[rony]

That's a lot of work...

I will pass to the development team for discussions.

[night199uk]

just noticed the 0.8.7 beta 2 is out... heh...

will release an updated patch against the svn version in the next day or two and also finish up the mysql fixes in cactid.

[TheWitness]

night199uk,

can you please convert the following two queries to PG compatible format:

Code: Select all

replace into A (a,b) VALUES ('a1', 'b1'), ('a2', 'b2')

also

Code: Select all

insert into A (a,b,c) VALUES ('a1', 'b1', 'c1'), ('a2', 'b2', 'c2') ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c)

Thanks,

TheWitness

[rubentsantos]

Nice work

[night199uk]

hey thewitness,

replace into A (a,b) VALUES ('a1', 'b1'), ('a2', 'b2');

insert into A (a,b,c) VALUES ('a1', 'b1', 'c1'), ('a2', 'b2', 'c2') ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);

both of these are multi-row inserts or updates which aren't supported directly by postgresql other dbs. the first can be replicated with adodb with a few caveats:
$cnn_id->BeginTrans();
db_replace("A", array("a"=>"a1", "b"=>"b1"), array("primary key"), true);
db_replace("A", array("a"=>"a2", "b"=>"b2"), array("primary key"), true);
$cnn_id->CommitTrans();

notably you can't update the primary key as the query happens as update/insert instead of delete/insert. this happens in a few places in import.php so i have had to use a DELETE/INSERT which i'd like to put into transactions or tackle more elegantly at some point.

the second i would probably do in the same way at the moment, i'll have a think about a better solution later on and try a few ideas. they're both very mysql specific, so i'd avoid these completely where possibly and keep the queries simple, using transactions. also trying to use db_replace from adodb wherever you need these should keep you safe if you're happy to live with the restrictions.

obviously the transactions awareness lies outside the scope of lib/database.php. i'd probably like to add db_starttrans() and db_endtrans() or similar to lib/database.php and start adding transactions to some of the code so that things like data sources and graph sources could be rolled back (for databases that support it) if any queries fail, but the structure of the code doesn't necessarily lend itself to that very well right now. that would help maintain database integrity (my test database was all over the place while modifying this code).

i'll start taking a look at 0.8.7 from SVN now...

[TheWitness]

Good start. Ok, now let's talk about the Oracle standard (since there is no longer a SQL standard) "MERGE" command. I believe, with a few tweaks, that is the functional equivalent to the "INSERT INTO ... ON DUPLICATE KEY UPDATE ... " syntax.

Does PG support the MERGE syntax? Can you describe how you would convert the two examples I presented first using that syntax?

I think if PG supports MERGE, you can write two wrappers to account for the two very elegant MySQL commands. Doing this, we can reduce the number of transactions between the database server, web server, and poller, thus keeping throughput to the database server at much higher rates.

Let me/us know what you find and thanks for getting involved.

TheWitness

[night199uk]

hey...

i have a patch against 0.8.7 beta 2 now, will bring it into line with svn tonight and post up here.

while patching though i noticed some bugs in the new cmd.php:
the code references $item["availability_method"] and various ping status columns, however $item is a foreach on $polling_items which is just SELECT * FROM poller_item, which at the moment doesn't contain the ping columns. Look like they need to be added to poller_item, and the code that creates poller_item from host, or looked up in the $host structure instead...

second (this might not affect MySQL but it's bad on PostgresSQL) at the moment $cur_time / $max_time etc are set in cmd.php from ping->ping_status or similar, which is a string (always "down" in my case because of the above) yet $cur_time/$max_time etc are digit(10,5) in the db therefore the insert/update fails for me, at least on postgresql. I suspect the same will happen on MySQL.

[TheWitness]

I'm catching those two bug's now. Unfortunately, in 0.8.7 Beta3, I have introduced SnmpV3 contexts. Therefore, you have at least one more pass to make.

If would be nice if you could answer my prior questions as well...

TheWitness

[TheWitness]

They are fixed in SVN now. The reason that you need to answer the other question is that there are several plugins that rely on those two syntax's.

Also, instead of posting a patch, can you please post a tar.gz of the entire files.

TheWitness

[night199uk]

great. i'll take a look at 0.8.7beta3, been away for a day or two

while i'm doing that i'll probably try and come up something for those two calls. the biggest concern is the on duplicate key update which will be hard to replicate. can you let me know which plugins it's used in and in what context? there will be no way to replicate the performance of the mysql query but if it's only used in the GUI on small tables that shouldn't be a concern...

[night199uk]

as requested, .tar.gz...

this works with pgsql great now, and was taken from the SVN about an hour or two ago. there shouldn't be any fundamental differences that would break mysql and the original was tested against mysql so this should be good...

[TheWitness]

Good work. I will review today.

Larry

[TheWitness]

First observation is that you need to audit error codes that are considered harmless. You will note, that in lib/database.php, in db_fetch_assoc, I check and allow error code 1032. In mysql, this is an error code to say something like "no matching records". So, although an error code, it means nothing.

So, we need a specific error code handler function for each of the supported database types.

More to follow.

Larry

[TheWitness]

Next question, I am assuming, by your change to sql_save that the Replace ADDODB handles escaping automatically. Please confirm.

TheWitness