Patch to implement database independent Cacti

Templates, scripts for templates, scripts and requests for templates.

Moderators: Moderators, Developers

Post Reply
Author
Message
except10n

Patch to implement database independent Cacti

#1 Post by except10n » Fri Jan 14, 2005 8:47 pm

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
Attachments
cacti.patch
(216.31 KiB) Downloaded 1693 times

except10n

#2 Post by except10n » Fri Jan 14, 2005 8:49 pm

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
Attachments
cacti_oci8_sql.txt
(181.41 KiB) Downloaded 1299 times

except10n

#3 Post by except10n » Tue Jan 18, 2005 1:45 pm

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

User avatar
rony
Developer/Forum Admin
Posts: 6016
Joined: Mon Nov 17, 2003 6:35 pm
Location: Michigan, USA
Contact:

#4 Post by rony » Tue Jan 18, 2005 3:51 pm

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.
[size=117][i][b]Tony Roman[/b][/i][/size]
[size=84][i]Experience is what causes a person to make new mistakes instead of old ones.[/i][/size]
[size=84][i]There are only 3 way to complete a project: Good, Fast or Cheap, pick two.[/i][/size]
[size=84][i]With age comes wisdom, what you choose to do with it determines whether or not you are wise.[/i][/size]

except10n

#5 Post by except10n » Tue Jan 18, 2005 5:50 pm

rony wrote: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

User avatar
TheWitness
Developer
Posts: 14804
Joined: Tue May 14, 2002 5:08 pm
Location: MI, USA
Contact:

#6 Post by TheWitness » Tue Jan 18, 2005 5:57 pm

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
True understanding begins only when we realize how little we truly understand...

Life is an adventure, let yours begin with Cacti!

Author of MacTrack, Boost, CLog, SpikeKill, Platform RTM, DSStats, maintainer of Spine, lot's of unpublished work and most of Cacti's bugs.
_________________
Official Cacti Documentation
GitHub Repository with Supported Plugins
Central Plugin Repository
Central Templates Repository


I'm still out there people. Getting excited for Cacti 1.2. I think it will be a great release.

except10n

#7 Post by except10n » Tue Jan 18, 2005 6:08 pm

TheWitness wrote: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

User avatar
rony
Developer/Forum Admin
Posts: 6016
Joined: Mon Nov 17, 2003 6:35 pm
Location: Michigan, USA
Contact:

#8 Post by rony » Tue Jan 18, 2005 6:11 pm

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. :D
[size=117][i][b]Tony Roman[/b][/i][/size]
[size=84][i]Experience is what causes a person to make new mistakes instead of old ones.[/i][/size]
[size=84][i]There are only 3 way to complete a project: Good, Fast or Cheap, pick two.[/i][/size]
[size=84][i]With age comes wisdom, what you choose to do with it determines whether or not you are wise.[/i][/size]

User avatar
TheWitness
Developer
Posts: 14804
Joined: Tue May 14, 2002 5:08 pm
Location: MI, USA
Contact:

#9 Post by TheWitness » Tue Jan 18, 2005 6:12 pm

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
True understanding begins only when we realize how little we truly understand...

Life is an adventure, let yours begin with Cacti!

Author of MacTrack, Boost, CLog, SpikeKill, Platform RTM, DSStats, maintainer of Spine, lot's of unpublished work and most of Cacti's bugs.
_________________
Official Cacti Documentation
GitHub Repository with Supported Plugins
Central Plugin Repository
Central Templates Repository


I'm still out there people. Getting excited for Cacti 1.2. I think it will be a great release.

pbulteel
Cacti User
Posts: 150
Joined: Fri Sep 05, 2003 9:20 am
Location: London
Contact:

Reserved words

#10 Post by pbulteel » Wed Jan 19, 2005 4:57 am

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
uname -a

raptor555
Posts: 5
Joined: Sun Mar 20, 2005 1:11 pm

what about sqllite ?

#11 Post by raptor555 » Sun Mar 20, 2005 2:57 pm

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

Post Reply