Re: Migrate from MySQL to PostgreSQL

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? 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. 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. > 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. > 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? Will MDB2 be the "standard-PEAR-DB-absraction-layer" soon? I have seen that the translation package has MDB-support now. >> 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 I will look at it, thanks. > >> 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? >> What would you suggest? > > Move to MDB .. its where the future is :-) MDB or 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. > 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 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. > 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? What is needet to bring MDB2 to "beta" status? kind regards, Andreas