PostgreSQL / Generic Database - PATCH

[TheWitness]

Too many questions. We need a conference call. Please advise.

Larry

[TheWitness]

Since there are so many changes, and saw some of your comments. I would like to change all of the natural joins "FROM (table1, table2)" to "INNER JOIN"'s. It may not have any impact, but none the less. The other thing is that you should likely lock around the replace into's that have been converted to "delete/insert", although locking in the script worries the you know what out of me.

I guess that's why mysql came up with things like "replace into".

Larry

[night199uk]

yeah, adodb will do autoquoting, i've used this on db_replace and sql_save. there were a few other modifications needed around autoquoting. the main one is that pgsql throws up errors if you try to insert or replace into number columns with the empty string. I.e. '' is not the same as NULL and doesn't work for number columns which broke a few inserts.

therefore i've modified a few places in the code where you have defaults in the $save arrays to have default numbers instead of '' (see host.php i think, the snmp_timeout fields and version were examples), and also generally tried to take out the quoting around some strings and numbers but this would be a mammoth task to do across the board. adodb has some problems with autoquoting certain things (functions) but i don't think this is ever going to be a problem for cacti...

i think the ideal longer term rather than having defaults in the code and in the db which might get out of sync at some point might be to have the DB schema stored in meta language or arrays or similar with defaults that you could pull when creating the base object, and then use ADO-DBs schema functionality to create the underlying database, be it pgsql or mysql. they also have XML import/export functionality, but this doesn't seem to work very well (an export of the cacti into XML wouldn't work at all)... this would also make the db modification/upgrade code in the install/ dir a lot simpler and easier and portable.

[night199uk]

hey tw,

i can do a conf call if you want or irc - are you on the #cacti on freenode?

the locking around the delete / insert code is a good idea. as mentioned i think a db_lock and db_unlock could be good and would be beneficial longer term, and transactions in general so that we could do things like begin a transaction before creating a host and its graphs, data sources etc, and rollback if any fail. let me have a look at adding these this afternoon.

[rony]

We have had some interesting problems with letting ADODB auto quote anything. In the first 0.8.7 beta, we had an issue where foreign key primary key relationships where broken corrupting the database structure.

[Atlant]

i have no graphs on the tree visible after placing them on the tree...
it seems that i have some problems with html_tree.php.. at
if (($leaf_type == "header") (empty($leaf_id))) {
$heirarchy = db_fetch_assoc(.....<HERE> )
with error ERROR: SQL Assoc Failed!
and detailed: NOTICE: Adding missing FROM-clause entry for table "graph_local"
ERROR: JOIN/ON clause refers to "graph_local", which is not part of JOIN
can anyone help? will be very appreciate

PS i downloaded and installed postgresql version from link above. 0.8.7