Quote:
I understand your idea, but I think this will deflect the entire idea of the abstraction layer: to let the module developer work independently of the database used.

I actually don't know the difference between the various SQL databases - I mean, as long as you just have INSERT, UPDATE and SELECT statements, it should be plain SQL and thus possible to make other child classes to XoopsDatabase. Creating or altering tables should of course be database-specific since datatypes and syntax differ - but that is already possible, since the module installation takes a file called sql/[databasetype].sql (dunno about uninstallation, though)

Mithrandir,
Since an abstraction layer between objects always will use the lowest denominator of the objects, it's bound to be slower then raw communication. Thats why I would like to keep the most commonly used functions in the abstruction layer and then allow developers to optimize their database-access if needed.

But as of now, I don't realy need other databases then MySQL, I was just courious about how XOOPS actualy handled the database, and was concerned when I only found queries that looked like MySQL in the kernel.

SQL is SQL - where the databases differ (AFAIK) is in the datatypes. Datatypes are not relevant in normal selects, inserts or updates, but very important in table creation and altering.

The idea behind the raw communication is nice, but I don't like the idea of having modules running with "recommended databases"

It's not only the datatypes that differ.

Even if you only use MySQL, there have been changes in version upgrades.

For example, UNION and subqueries.

I don't know how a generic interface would handle that.

Daigoro, I understand what you mean. Using an abstraction layer can slow you down, and you want the raw sql queries optional so developers can speed up their modules if they want to. Well that is the trade off, speed for flexibility, in this case different database types. Allowing devs to use "raw" queries could break functionality in another environment. You either want a slightly slower database access mechanism with support for multiple database tpyes, or you can hard code everything and it'll be fast, but you're sacrificing flexiblity, in this case that being MySQL restricted. The current system is like a hybrid, it doesn't support other database types, but there is some abstraction there to make your life easier. Alot of queries are currently hard coded sql statements.

Besides a few, like union, I also don't know much about the differences between database types. I've mostly used mysql, access, and a little mssql.

I might still try to integrate MDB2 into XOOPS (at least partially), if anything to see if it makes a developer's life easier and if there's a big hit on performance.

Dave_L, mvandam, jackt, Mithrandir, thank you all for your comments. I now understand alot more about the database integration in Xoops. Or rather, I'm now confused on a higher level - so there are still lots for me to learn, yet.

Quote:
Exactly.

SQL (ANSI 92 or something like that) is supposed to be a 'standard' that all database engines are supposed to support. I must admit I do not know exactly what this standard defines, nor what the databases support exactly... But what I've heard is that practically NONE of the popular databases implement the full standard. Instead they have diverged to try to force 'vendor lock-in' among other things.

The divergence consists of:

- different levels of support (not all databases support subqueries or 'union' or triggers or stored procedures)
- different names for built in functions/operators (e.g. 'CONCAT' vs '+'; also the various databases apparently have different syntax for 'JOIN' operations)
- different datatypes

By restricting yourself to a small set of datatypes and simple queries then you can go a fair ways. This is how ADODB works I believe. There are a few wrapper functions to take care of major differences and if you use those religiously, you shouldn't have many problems. Of course, by restricting yourself, you often throw away a HUGE opportunity to do any database optimization. (Not sure how relevant this is in typical web apps though - maybe not a big deal.)

I may be blowing this issue out of proportion, not having first hand knowledge of these problems. However, I am on mailing lists for several open source projects, and it frequently occurs that even though some of these projects do use ADODB, people will report problems with MSSQL or ORACLE due to subtle syntax differences even though their abstraction library is supposed to provide 'database independence'.

I'm rather fond of the ezSQL class library:

justinvincent.com

There's even a great tutorial on how (and why) he designed it, and his documentation is fantastic.

Indeed looks nice.

I guess that was the aim of the XOOPS database structure, too. Might be able to jump a few corners by using this library - although it will need to be tested, how it performs with joins, subqueries etc.

Actually, he has made life harder for himself than he needed to.

He is using a different class for the different databases, but he himself says that 90% of the methods are the same - so why hasn't he put them in a superclass? (Rhetorical question, I'm not questioning you )

Also, in his select($db) method on the Oracle class, he has as far as I can see, an error:

but it may just be a typo, that the argument should have been $dbname and not $db...

Anyways, the caching and object orientation of his SQL results sure looks nice - quite possible that we can "steal" (and give credit to, naturally) some of his work for extending the XOOPS database classes, but it'll need to be tested properly with more advanced queries in order to ensure that it works as it should.