Re: Migrate from MySQL to PostgreSQL

Andreas Korthaus wrote:

Hi Lukas! Lukas Smith wrote:

I know that I will have to do this also for Oracle, DB2 and MSSQL. And I know that some things in my Data-Definitions will change in future, and I will have to add more tables.

If that is the case you will really begin to benefit from the xml schema definition. But remember this will always means that you will reduce yourself to the lowest common demonimator (which is not that low because several things are emulated in MDB .. emulation slows down performance though).

How could I know if something is emulated? Only from source?

yes

I don't use db-specific features, no date/time columns/functions, no triggers, no views, I only use transactions as "advanced" feature. In my application I tried to use no specific functions or syntax, because the portability to the most important RDBMSs is very important.

well with MDB you can safely use date datatypes, even LOBs and booleans

At least I have to support Oracle, DB2, MSSQL and PostgreSQL. But I think I could support every DB PHP supports which supports transactions. If necessary I have to write another driver for (M)DB. But I think I will only have to support the DBs mentioned above.

The point being if you only use DB for the nice OO API its faster than a script that uses MDB to get full datatype abstraction.

hm? I use DB because I can switch to other RDBMS easyly.

Well you have limited yourself severely enough that DB will probably suffice. DB provides very few ways of abstracing anything (it abstracts sequences, prepare/execute and limits - dont remember if it does anything beyond that). Its main feature is having an OO API that is the same across all RDBMS - essentially its a unified OO API to all RDBMS ext.

However if you dont use the datatype abstraction than MDB is actually slightly faster than DB, mainly because I could do some changes to MDB based on the experiences that were made in the development of DB.

Hm, I don't need datatype-abstraction, because I only use "standard datatypes" as int, smallint, char, varchar and text. I know that I loose a lot of cool DB-Features, but DB Abstraction is more important for me, and to use unix-timestamp in INT-fields is really fast an nice to handle in PHP. Flags don't to be real bolean, could be smallint or char. Perhaps it is not nice, but it works everywhere.

Then you dont need datatype abstraction indeed.

And it would be really awesome if you could write an ODBC driver. I will help you where ever you may need help. There are skeleton drivers in the docs dir and there is a test suite to test large parts of the API.

You can be shure, I will write one, at the moment I don't know exactly when I will need it, but I know I will. Should I better write it for MDB or MDB2?

Either way is fine.

Will MDB2 be the "standard-PEAR-DB-absraction-layer" soon? I have seen that the translation package has MDB-support now.

Hard one to answer. I think the standard will be determined what people use. MDB has made little steps forward in that respect, but obviously its very hard to compete against DB because the main reason to use DB is simply because its been around longer.

Is there any advantage of using MDB instead of DB in my application itself (only SELECT, INSERT, UPDATE) ?

It also has an integrated debugging functionality. It lets you perform queries as a DBA without having to connect to a specific database. Connections are made on demand, which means you can switch around the dsn your instance uses very easily. And of course it has the datatype abstraction plus the schema manager.

AFAIK DB cannot work as singleton, right(why not? that forces bad design in other classes as using "global $db"...)? Can MDB (2) do so?

It should be fairly trivial to add a singleton to DB. You wouldnt even need to modify DB for that.

What would you suggest?

Move to MDB .. its where the future is :-)

MDB or MDB2?

If you are starting off now I would go with MDB2.

Datatype abstraction costs a bit performance at run time, but since you are trying to write an application that works with so many RDBMS you will start adding RDBMS specific hacks most likely. Why not skip that and use MDB instead?

I will see. But switching to MDB(2) is not that easy, my application has

100 scripts which all use PEAR::DB.

there is also a DB wrapper in MDB. I dont think it has gotten much real world use. However there shouldnt be too many issues with it. That may be an interim solution.

MDB2's API is simpler and easier to understand. Its also easer to port an app from DB to MDB2 than to MDB (not much easier though. MDB2 also has some nice new features like unbuffered queries, is even faster and at the same time more flexible. MDB2 is all around better :-)

but its "alpha" ;-)

The core is stable, the API is alpha.

The mysql driver is working really well for me and will be used in largest project todate which is going production in early feburary. However there might be changes and tweaks in the API.

what about the postgresql driver? that's the one I would need in next time.

The postgresql and the oracle driver have been tested and seem to work nicely.

Mostly I am waiting for the next release of PEAR::DB to port over a bunch of the fixes and improvements Daniel made.

Fixes to MDB2? AFAIK there are a lot of changes/fixes to DB in CVS which are not released yet, right?

Correct. I will port them into MDB and MDB2. Mostly regarding the error mapping and tableinfo methods.

What is needet to bring MDB2 to "beta" status?

DB release, port of the error maps and tableinfo methods. regards, Lukas