Patch to implement database independent Cacti

[except10n]

Here is a patch that will modify Cacti to be database independent. As you might think, this was not an easy task. Several changes needed to be made to the sql schema as well as many php files. Attached is the unified diff patch. I worked off a cvs copy from 2005-01-13. Below is a list of changes needed....

Schema changes:
-rename all "hash" fields to "md5hash" (reserved word)
-rename all "sequence" fields to "seq" (reserved word)
-rename all "type" fields to "type_id" (reserved word)
-rename "poller_command.time" to "poller_command.runtime" (reserved word)
-rename "poller_output.time" to "poller_output.runtime" (reserved word)
-rename "rra.rows" to "rra.numrows" (reserved word)
-rename "user_auth.password" to "user_auth.passwd" (reserved word)
-rename "user_log.time" to "user_log.lastlogin" (reserved word)

Code changes:
-rename all instances of "sequence" to "seq"
-rename all instances of "hash" to "md5hash"
-rename misc instances of other field name changes
-replaced all "replace into" commands with db_replace() function call
-replaced all "truncate table" commands with "delete from"
-remove all limit clauses in sql and pass limit/offset as function args which will be sent to SelectLimit() by db_fetch_*() methods.
-replaced calls to db_insert_id() with manual retrieval of next id
-replaced mysql specific sql function calls (NOW, CONCAT, CONCAT_WS, REPLACE, SUBSTRING, DATE_FORMAT) with newly created wrapped methods sql_function_* located in lib/database.php.

Obviously there are a lot of changes which can potentially break the system. I was able to successfully install Cacti using an oracle database and perform basic functions, however more thorough testing is needed. Also, there may be an issue with sql statements using the "group by" clause. I didn't address that issue at this time, so I will leave it up to the developers. Further information regarding the issue can be found at http://dev.mysql.com/doc/mysql/en/GROUP ... ields.html.

-except10n

[except10n]

Here is the oracle schema file that I used. Oracle treats two single quotes as NULL. As such most of the columns that were specified "default '*' NOT NULL" have now been made nullable. Also, native sequences and triggers were created since Oracle doesn't have an "auto_increment" type.

-except10n

[except10n]

So, was just wondering if the developers had a chance to look at this since nobody has posted any comments. It would be kinda pointless to start using it if they decide not to implement the changes since the next version wouldn't support it. Any feedback?

-except10n

[rony]

Sorry, I some how past over this post.

But concerning multiple database support, yes we do have plans to support it. No, it will not be 0.8.7, we currently have multiple database support slated for 0.8.8.

You may ask why, it's really simple, 0.8.7 has a lot of changes, and the developers came to a decision that multiple database support would be deferred until 0.8.8. No need to introduce a new set of possible bugs with a new implementation of code.

As for moving forward in 0.8.7 with multiple database support. All of the developers are keeping it in mind when we write code. A lot of changes are needed for this to work. But mostly we will be relying on ADODB to help us with this task.

If you have specific questions, please don't hesitate to email me or post them here.

[except10n]

Sorry, I some how past over this post.

But concerning multiple database support, yes we do have plans to support it. No, it will not be 0.8.7, we currently have multiple database support slated for 0.8.8.

You may ask why, it's really simple, 0.8.7 has a lot of changes, and the developers came to a decision that multiple database support would be deferred until 0.8.8. No need to introduce a new set of possible bugs with a new implementation of code.

As for moving forward in 0.8.7 with multiple database support. All of the developers are keeping it in mind when we write code. A lot of changes are needed for this to work. But mostly we will be relying on ADODB to help us with this task.

If you have specific questions, please don't hesitate to email me or post them here.

I can understand not wanting to introduce a new factor which could be the cause of many new bugs. If however you are moving forward with multiple database support in 0.8.7, well then that's exactly what I've done. I worked off the latest CVS copy which is becoming 0.8.7. Also, pretty much 98% of the changes I've made are using ADODB's abstraction to implement database interoperability. I'm not sure if you've looked at the patch or not yet, but I think you will find that all that's changed is the sql queries (and any relavant column name changes). No major code changes have taken place. If you have looked at the code and still want to wait, then I respect your decision. If you do change your minds, I'd be happy to answer any questions you might have regarding the patch.

Thanks

-except10n

[TheWitness]

I suspect we have a candidate for a 4th developer if you are interested in making this a part of the 0.8.7 release. Are your interested?

TheWitness

[except10n]

I suspect we have a candidate for a 4th developer if you are interested in making this a part of the 0.8.7 release. Are your interested?

TheWitness

I'd be open to that. However, I must admit up front that the reason I am taking an interest in database interopability in Cacti is because I need it for my current work project. My project priorities change often and I have learned from the past that it probably isn't a good idea for me to commit myself as a fulltime developer to open source projects. I am however comfortable becoming a project contributor and focusing on database interopability in 0.8.7. I also have some other design ideas regarding authentication/authorization, external image serving, etc. I am fairly knowledgable regarding the internals of Cacti and have monitoring it's progress for a long time (probably a little bit before cactid was introduced).

-except10n

[rony]

When I said moving forward, I was refering to keeping in mind that we want to have multiple database support. I did not mean to imply that we where doing it.

That being said, I'm not discounting your effort, I think it's really cool. But there are factors which you are currently not aware of, like the upcoming commit of a very large change to the CVS tree from Ian concerning Templates and graph generation.

Which I have a strong feeling will conflict with your patch. Not your fault, you had no way of knowing of pending commits.

At this point, we have not done any extended research on ADODB and the functions, I haven't had time in the last few weeks.

We will probably wait until 0.8.8 for multiple database support, simply because there are to many unknowns and issues that can arise in addition to the amount we will already have with the fundimental changes to templates that are going to be present in 0.8.7.

This desision was made, mostly, so we would be able to release 0.8.7 this year.

[TheWitness]

Ok, good enough. Here is what I want you to do. Submit your e-mail to me and I will add it to the development list for beta and otherwise. Ian, author and otherwise his highness is planning major database changes that you will need to review and then re-commit your energies to.

It is important that he have the list of reserved words. Since he is making these major changes, it would be best to catch them now. Please forward that list to him.

Then, we will see where this thing goes. Thanks for all you effort.

TheWitness

[pbulteel]

I found this link and though it might be useful in this case.

http://www.macromedia.com/cfusion/knowl ... d=tn_18050

It lists reserved words for several databases, including:

* Microsoft Access (1.0, 1.1, 2.0 Access for Windows 95, 7.0, Access 97)
* Microsoft Access 2000
* Microsoft Access 2002
* Microsoft Excel (MS Query)
* Microsoft SQL Server 2000
* Visual FoxPro
* Sybase Adaptive Server Enterprise
* Oracle (This link requires a login to Oracle Technet, which is free.)
* PostgreSQL
* DB2 Universal Database
* MySQL

(Taken from the site...)

I thought it might be of some interest.

-P

[raptor555]

what about sqllite ?
it is easy to install, probably it can perform better than mysql on small setups..