Potential database portability layer

[Guest]

Nice stuff... Another one to consider is
http://php.weblogs.com/ADODB

[paul]

ADODB would be preferable over sql relay for the simple reason that SQL relay is only available for *nix variants.

ADODB is a collection of php include scripts, and as such should be completely cross-platform.

Paul

[raX]

I just downloaded the ADODB package and I am going to take a good look at it. I realize the need for such a database abstraction layer in cacti and will look into implimenting it if all goes well.

-Ian

[raX]

Let me ask a question about how you think we should go about implimenting this database compatability layer.

I took a good look at what ADODB can/cannot do this evening. Let me say that I was very impressed. I should be able port all of cacti's current database code without much of an issue.

The only thing that it will require is time. I am going to have to rewrite every line of code that is used to currently access a MySQL server.

My question is that version 0.6.6 is near release, I added the three main features I wanted to add and once I 'tweak' a few more things I was going to release it. Would you guys rather have me release 0.6.6 without the new database code, or wait a bit longer and release with it in? Also, before I release any version with this new database code, I will most likely make test version available just to make sure everything goes ok.

One last thing; to anyone who has used ADODB before; what kind of overhead does have over the standard PHP functions? I have yet to be able to test this since I don't have it fully implimented, but would like some sort of idea before going ahead with the whole thing.

Thanks in advance for everyone's input.

-Ian

[raX]

SQL Relay looks like a really good solution as well, mainly because it has a c API, which could be used for spine. The reason I wouldn't want to use SQL Relay for the PHP frontend is it would require every user to recompile PHP to support it. I would rather include ADODB with cacti for that purpose.

That said, spine will be a completly different beast. Obviously ADODB will not work with spine, so we will need something else. I wonder if it would be possible to link against SQL Relay under windows using its c API? If so, that would be an excellent option for spine.

-Ian

[paul]

I haven't actually used adodb that much, but i seem to remember reading that it is quite efficient compared to other php database abstraction classes.

iirc, there's a benchmark on the internet of it vs the pear code.

Paul

[paul]

Obviously need to decide on how impartial the author was, but the link for benchmarks is at:
http://phplens.com/lens/adodb/

[jwiegley]

To be fair I didn't look into either SQLrelay (at least anymore deeply than the link given) or ADODB. But I did catch the two comments that SQLrelay won't work for the Windows community and ADODB won't work with Spine.

I would suggest 0.6.6 is released before attempting to abstract the database layer.
I'm not a database wizard and I'm old-school so I may not be up-to-date on the latest items for providing abstract database interfaces.

We should try to support the Windows world so SQLrelay will not be a suitable choice. Spine, if it gets completed and works, will become the replacement for cmd.php at least on Un*x platforms and since the Un*x base for cacti is, I'm guessing, significantly larger than the Windows base we will need something with a C API and ADODB doesn't appear to be this.

The first implementation of Spine will most likely be built using the MySQL C API because I already know how to use that and I'm interested in getting something working as fast as possible in order to start shaking out bugs. Once it is working I will probably be switching to ODBC for the interface. As far as I know ODBC is the standard for abstract database interfaces. There is PHP support for ODBC. This would allow Cacti and Spine to use identical interfaces.

I found a lot of PHP/ODBC info at:

http://php.weblogs.com/odbc

http://www.zend.com/zend/tut/odbc.php

Spine won't need any connection pooling. Spine's interaction with the database will only be to read the datasource specifications and tasks when it is started and re-read them when it receives a HUP signal. Other than that it doesn't need the database at all.
(Remember, Spine will be running as a daemon whose sole job is to process tasks to fetch data and update the RRD files. I don't see a need for Spine to alter the MySQL database.) So at this time it doesn't look like Spine will need any more than one connection to the database and it only needs to be open long enough to retreive and construct the task definitions.

Any thoughts on the pros and cons of just selecting ODBC as our database interface?
I think the pros are:
1) Will be usefulfor both cacti and spine
2) is ubiquitous with database vendors.
I don't know of any cons other than maybe the ADODB API provides something ODBC cannot. Maybe the ODBC API isn't rich enough for our needs. Though I think it will suffice for Spine's purposes.

[raX]

I will definetly release 0.6.6 before I do any thing serious with ADODB. Hopefully I will get it out the door Friday if all goes well.

I'll have to agree that earlier versions of spine may not work on win32 because of the database support, even though I hope this will eventually change. The origional cmd.php script will have to be used a bit longer for win32 folks I guess.

I like the idea of ODBC since it would be much more universal than using the C MySQL API. It also appears that ADODB supports ODBC connections too.