Re: Migrate from MySQL to PostgreSQL

Andreas Korthaus wrote:

Hi Lukas! Lukas Smith wrote:

MDB does support reverse engineering MySQL databases into the xml schema format. There is a script as part of the MDB install called reverse_engineer_schema.php or something like that. However note that this functionality is focused on mapping all of the databases to MDB datatypes. In case it cant do that 1-1 it will issue warnings you can fix manually. The result will be an xml schema file which you can then use to create the database in pgsql.

Thanks a lot. I will have a look at this. After a whole night of trying to convert the table-definitions by hand, I think I understood the advantage of MDB ;-)

:-)

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).

As said before I'm using PEAR::DB in my application, does it make sense zu switch to MDB? I Think there will be some changes necessary, right? And MDB was slower than DB, wasn't it? But that is not so important, but for example there is no ODBC driver for MDB, and I need ODBC für BD2 (perhaps I could write one and give it to you?).

MDB is faster than DB. But if you start using the advanced abstraction features of MDB it will be slower than writing a non abstracted script with DB. 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. 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. 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.

And there are missing some other packages, for example I am using DB_Pager, which only works with DB, or perhaps I want to try DB_DataObject, which also is only available for DB. `

True. There are only a few packages which have been ported. However porting is actually fairly trivial. See this article for reference: http://www.php-mag.net/itr/online_artikel/show.php3?id=283&nodeid=114

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.

So I think if it would be a better idea to leave my application using DB, and use MDB for creating and changing table-definitions in XML, and update the definitions in the database using MDB fuctions.

You can do that of course as well.

What would you suggest?

Move to MDB .. its where the future is :-) 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?

What about MDB2? I have read that it would be better than MDB, but what is better here? And when will it reach"stable"? Is there any advantage for me using MDB2? Or should I later switch fom MDB to MDB2?

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 :-) 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. Mostly I am waiting for the next release of PEAR::DB to port over a bunch of the fixes and improvements Daniel made. regards, Lukas Smith smith@backendmedia.com _______________________________ BackendMedia www.backendmedia.com berlin@backendmedia.com Linn Zwoch Smith GbR Pariser Str. 44 D-10707 Berlin Tel +49 30 83 22 50 00 Fax +49 30 83 22 50 07