Thread: PHP Abstraction layers
I am seeking feedback/comments/recommendations on an abstraction layer to use for our development. So far I have found that the following layers exists:PHPLib, ADODB, PEAR DB, MetatData I have only seen examples from Pear and will try to see examples from the others, but I am wondering if anyone who has tried them could provide feedback.
I think ADODB kicks arse. Very fast, etc. Chris > -----Original Message----- > From: pgsql-php-owner@postgresql.org > [mailto:pgsql-php-owner@postgresql.org]On Behalf Of Francisco Reyes > Sent: Monday, 21 January 2002 12:39 AM > To: PostgresSQL PHP list > Subject: [PHP] PHP Abstraction layers > > > I am seeking feedback/comments/recommendations on an abstraction layer to > use for our development. So far I have found that the following layers > exists:PHPLib, ADODB, PEAR DB, MetatData > > I have only seen examples from Pear and will try to see examples from the > others, but I am wondering if anyone who has tried them could provide > feedback. > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Hello Francisco, I find "PEAR DB" particularly a wonderful data abstraction layer. I have been working with for a long time and so far I have had minor difficulties while porting from one database to another. I have worked with PEAR DB on three databases, MySQL with BDB tables, Postgresql and Oracle. It works fine with all the three, but I must confess my working with Oracle based data interface is limited. Just one small aspect to be taken care of while using PEAR DB is that, u need to use quotes to enclose the fields. Speed of operation is very good in terms that, user hardly notices the difference of data abstraction layer being present. -- Best regards, Gurudutt mailto:guru@indvalley.com Life is not fair - get used to it. Bill Gates Sunday, January 20, 2002, 10:09:21 PM, you wrote: FR> I am seeking feedback/comments/recommendations on an abstraction layer to FR> use for our development. So far I have found that the following layers FR> exists:PHPLib, ADODB, PEAR DB, MetatData FR> I have only seen examples from Pear and will try to see examples from the FR> others, but I am wondering if anyone who has tried them could provide FR> feedback. FR> ---------------------------(end of broadcast)--------------------------- FR> TIP 3: if posting/reading through Usenet, please send an appropriate FR> subscribe-nomail command to majordomo@postgresql.org so that your FR> message can get through to the mailing list cleanly
Le Lundi 21 Janvier 2002 03:14, Christopher Kings-Lynne a écrit : > I think ADODB kicks arse. Very fast, etc. ADODB is a good solution if you whish to use it 'alone', whithout any other libraries. If you need a framework of PHP objects go for PEAR DB (PEAR framework) or MetaData (BinaryCloud framework). BinaryCloud framework is much more advanced than PEARDB. Metadata can handle nearly all PostgreSQL objects. It has better design. Maybe you could have a try at it. Best regards, Jean-Michel POURE
On Mon, 21 Jan 2002, Gurudutt wrote: > Hello Francisco, > > I find "PEAR DB" particularly a wonderful data abstraction layer. I > have been working with for a long time and so far I have had minor > difficulties while porting from one database to another. Thanks for the feedback. I think so far I am leaning to PEAR or MetaData. > I have worked with PEAR DB on three databases, MySQL with BDB tables, > Postgresql and Oracle. It works fine with all the three, but I must > confess my working with Oracle based data interface is limited. So far my targers are PostgreSQL and Oracle. Although I actually don't expect a lot of apps moving from PostgreSQL to Oracle, there is a second part of abstraction layers that people miss. It helps in terms of learning and reducing production costs. If my team learns one good API which we can use with both PostgreSQL and Oracle then when we need to develop Oracle apps it would take us less time.
On Mon, 21 Jan 2002, Jean-Michel POURE wrote: > ADODB is a good solution if you whish to use it 'alone', whithout any other > libraries. Thanks for the feedback. This is along the lines of what I was looking for. From what I have read ADODB is best for people coming from a MS world. I wouldn't count myself on that crowd. >If you need a framework of PHP objects go for PEAR DB (PEAR > framework) or MetaData (BinaryCloud framework). That is certainly along the lines of what I am looking for. > BinaryCloud framework is much more advanced than PEARDB. Metadata can handle > nearly all PostgreSQL objects. It has better design. Maybe you could have a > try at it. Care to expand a little on this. What do you mean by "can handle nearly all pgsql objects"? Do you have an URL for MetaData? Is this the same as what is on the phpclasses site? (phpclasses.upperdesign.com)
On Mon, 21 Jan 2002, Christopher Kings-Lynne wrote: > I think ADODB kicks arse. Very fast, etc. > Chris I have got good feedback about ADODB, but has anyone used ADODB and PEAR DB? I would like some level of comparison.
--- Francisco Reyes <lists@natserv.com> wrote: > On Mon, 21 Jan 2002, Christopher Kings-Lynne wrote: > > > I think ADODB kicks arse. Very fast, etc. > > Chris > > I have got good feedback about ADODB, but has anyone used ADODB and > PEAR > DB? I would like some level of comparison. I find ADODB to be totally overkill and messy for just about any application. PEAR DB has a nice and clean interface but it is tied into the still rather immature PEAR classes which bothers me. I personally use the PHPLIB DB abstraction and I find it very fast and clean. Each database abstraction library is self-sufficient, unlike PEAR DB. Plus thousands of programmers have been using PHPLIB for years so it is mature and has a great user base. It is not perfect but it has functioned beautifully for several large projects I have worked on. Each DB layer has its advantages and disadvantages. My suggestion is to try each of them for a couple dozen lines of code and see which one you like. Brent __________________________________________________ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/
Hello, Francisco Reyes wrote: > > I am seeking feedback/comments/recommendations on an abstraction layer to > use for our development. So far I have found that the following layers > exists:PHPLib, ADODB, PEAR DB, MetatData > > I have only seen examples from Pear and will try to see examples from the > others, but I am wondering if anyone who has tried them could provide > feedback. If you are looking for a database abstraction package that provides true database independence to your applications or components, you may want to try Metabase (not Metadata). Metabase provides true abstraction of database types. This means in practice that from the point of view of the applications it always handles data formatted in the same way regardless of how it is represented in the database. For instance, if your application deals with dates, it only sees them formatted according to the ISO 9601 standard (YYYY-MM-DD) even if the underlying databases represents dates in other format. Metabase will take care of datatype conversions if necessary when storing or retrieving data from the database. A direct consequence of this, is that if you switch between databases you will not need to touch your application. Metabase was thought not only for database independence but also for Web database programming in particular, so it offers some features that take in consideration the constraints of Web programming. Metabase pioneered the abstraction of things like providing a way to offer the LIMIT clause functionality but in a way that works in all supported database besides MySQL ( PostgreSQL, Oracle, MS SQL server, Interbase, Informix, MiniSQL, ODBC, etc..). This is important for display query results split in multiple pages. This class is based on Metabase and takes advantage of this feature to display query results in HTML tables eventually with a navigation bar that lets you go back and forth between the different pages of the results. http://phpclasses.upperdesign.com/browse.html/package/130 Metabase also pioneered the use of sequences in such way that it works with databases that do not support sequences natively but support auto-incremented table fields. This is important to insert entries with unique id fields that are usually the table primary keys. The sequence generation is always an atomic process, so no matter how many entries may be entered into a table by simultaneous users, it is always done in a consistent manner. Another key feature of Metabase is the ability to install and maintain database schemas. All you need to do is to describe your database schemas in a custom XML file that specify the names and properties of your tables, fields, indexes and sequences, all in a database independent format. Metabase manager class is able to process that schema description and install it for you, no sweat. If some day you need to change that schema, and often you need to, you just need to change the schema description file. Then Metabase manager is able to process it and compare with the previously installed schema to figure the changes that were done and applying them safely to your schema. This is done without affecting the data that was added after the first time the schema was installed or updated for the last time. A new release of Metabase was made just yesterday. It provides some features that users were requesting like the ability to customize error handling. It also provides yet another innovating features, which is the SQL REPLACE implementation. This is a statement like SQL INSERT except that if the values of the primary keys match the ones of an existing row, it will update that row instead of inserting a new one. AFAIK, only MySQL provides SQL REPLACE command, but Metabase provides a suitable emulation that works the same way using transactions so it works also with PostgreSQL, Oracle, MS-SQL server, Informix, Interbase, etc.. One important new feature is a set of new functions to fetch result set data in a single call. These functions were contributed Lukas Smith and Christopher Linn and are meant to end the complaints of some users that claim that other PHP database abstraction packages are faster than Metabase because they can fetch whole rows of data at once. These new functions in Metabase should end those complaints. Anyway, it is better for you to try it yourself. Metabase documentation is through and very complete. There is also a small tutorial to get you started quickly. If for some reason Metabase does not offer some facility you need, you may want to contact me to tell about it, or better implement it yourself and contribute your work to features yourself in the hall of fame of Metabase contributors. Metabase is free and is distributed with a BSD Open Source licence. For those that don't know Metabase, you may find it here: http://phpclasses.UpperDesign.com/browse.html/package/20 Regards, Manuel Lemos
> A new release of Metabase was made just yesterday. It provides some > features that users were requesting like the ability to customize error > handling. It also provides yet another innovating features, which is the > SQL REPLACE implementation. This is a statement like SQL INSERT except > that if the values of the primary keys match the ones of an existing > row, it will update that row instead of inserting a new one. AFAIK, only > MySQL provides SQL REPLACE command, but Metabase provides a suitable > emulation that works the same way using transactions so it works also > with PostgreSQL, Oracle, MS-SQL server, Informix, Interbase, etc.. *cough* Callling it 'SQL REPLACE' is a bit of a misnomer. It is not specified anywhere in the SQL standard. (Like lots of MySQL stuff) Chris
Hello, Christopher Kings-Lynne wrote: > > > A new release of Metabase was made just yesterday. It provides some > > features that users were requesting like the ability to customize error > > handling. It also provides yet another innovating features, which is the > > SQL REPLACE implementation. This is a statement like SQL INSERT except > > that if the values of the primary keys match the ones of an existing > > row, it will update that row instead of inserting a new one. AFAIK, only > > MySQL provides SQL REPLACE command, but Metabase provides a suitable > > emulation that works the same way using transactions so it works also > > with PostgreSQL, Oracle, MS-SQL server, Informix, Interbase, etc.. > > *cough* Callling it 'SQL REPLACE' is a bit of a misnomer. It is not > specified anywhere in the SQL standard. (Like lots of MySQL stuff) Still it is a good thing (tm). SQL standard is also a bit misnomer because every database vendor ships its own extensions and its own non-standard compliant quirks. My favourite is that Oracle stores empty strings in VARCHAR fields as NULLs. :-) Regards, Manuel Lemos
On Tue, 22 Jan 2002, Christopher Kings-Lynne wrote: > > A new release of Metabase was made just yesterday. It provides some > > features that users were requesting like the ability to customize error > > handling. It also provides yet another innovating features, which is the > > SQL REPLACE implementation. This is a statement like SQL INSERT except > > that if the values of the primary keys match the ones of an existing > > row, it will update that row instead of inserting a new one. AFAIK, only > > MySQL provides SQL REPLACE command, but Metabase provides a suitable > > *cough* Callling it 'SQL REPLACE' is a bit of a misnomer. It is not > specified anywhere in the SQL standard. (Like lots of MySQL stuff) > Chris We can discuss names, till the cows come home, but basically Manuel described what he meant. The "replace" functionality is one of the few things I liked of MySQL when I researched which dB to go with. The fact that this layer can abstract the functionality is a great point in my opinion.
> We can discuss names, till the cows come home, but basically Manuel > described what he meant. The "replace" functionality is one of the few > things I liked of MySQL when I researched which dB to go with. The fact > that this layer can abstract the functionality is a great point in my > opinion. Here's an idea: look at their code to see what kind of SQL jiggery pokery they needed to implement it and then implement it ourselves... BTW, from my estimates you'd need at least 4 - 6 sql queries to simulate the REPLACE syntax for Postgres, so why on earth would you use it? Chris
On Wed, 23 Jan 2002, Christopher Kings-Lynne wrote: > Here's an idea: look at their code to see what kind of SQL jiggery pokery > they needed to implement it and then implement it ourselves... I wish PostgreSQL got this functionality. > > BTW, from my estimates you'd need at least 4 - 6 sql queries to simulate the > REPLACE syntax for Postgres, so why on earth would you use it? > Chris Why so many? It is simple enough to attempt a delete and then do an insert.
> Why so many? It is simple enough to attempt a delete and then do an > insert. DELETE WHERE what? Alternatively you can attempt and update then do an insert - question is UPDATE WHERE what? If the insert fails you need to select from the postgres system catalogs to find the primary key on the table (if there is one). Next, you need to find the fields that that primary key is defined over. Next, you need to parse the insert statement and find the fields being inserted that match the primary key. Lastly you need to modify the insert statment to an update statment, take out the values that are part of the primary key and make them part of the where clause of the update or delete statement. Chris
Hello, Christopher Kings-Lynne wrote: > > > We can discuss names, till the cows come home, but basically Manuel > > described what he meant. The "replace" functionality is one of the few > > things I liked of MySQL when I researched which dB to go with. The fact > > that this layer can abstract the functionality is a great point in my > > opinion. > > Here's an idea: look at their code to see what kind of SQL jiggery pokery > they needed to implement it and then implement it ourselves... > > BTW, from my estimates you'd need at least 4 - 6 sql queries to simulate the > REPLACE syntax for Postgres, so why on earth would you use it? Actually only two: a SELECT and a depending on that, a INSERT or an UPDATE. You may want to try to look at Metabase implementation to see how it works. Regards, Manuel Lemos
> Actually only two: a SELECT and a depending on that, a INSERT or an > UPDATE. You may want to try to look at Metabase implementation to see > how it works. Read my previous post. How does MetaBase know which fields are the primary key!?!?!?!? Chris
Hello, Christopher Kings-Lynne wrote: > > > Actually only two: a SELECT and a depending on that, a INSERT or an > > UPDATE. You may want to try to look at Metabase implementation to see > > how it works. > > Read my previous post. How does MetaBase know which fields are the primary > key!?!?!?!? Look at Metabase implementation. Metabase comes with a driver conformance test that verifies if features like this work properly in all supported databases. Don't you think that I would release a version of Metabase that would not pass that test if all databases? The application needs to hint it as it is something that does not change at runtime, so there is no penalty of figuring that by querying the database server and some database do not provide a way to tell you which fields are the primary key. Regards, Manuel Lemos
On Wed, 2002-01-23 at 16:56, Christopher Kings-Lynne wrote:
> > Why so many? It is simple enough to attempt a delete and then do an
> > insert.
>
> DELETE WHERE what? Alternatively you can attempt and update then do an
> insert - question is UPDATE WHERE what?
>
> If the insert fails you need to select from the postgres system catalogs to
> find the primary key on the table (if there is one). Next, you need to find
> the fields that that primary key is defined over. Next, you need to parse
> the insert statement and find the fields being inserted that match the
> primary key. Lastly you need to modify the insert statment to an update
> statment, take out the values that are part of the primary key and make them
> part of the where clause of the update or delete statement.
I realise this wanders far from the SQL standard, but it's one of those
areas where the standard is awkward from an application programmer's
point of view. I would certainly love to see some syntax of this kind
available. If having to work out the primary key from schema queries is
too much work, then why not have a syntax that explicitly coped - after
all the whole thing is non-standard, so we don't have to follow MySQL's
lead on it:
INSERT OR REPLACE <table> ( <field list> )
VALUES( <value list> ) WHERE <condition>
would be just fine by me.
There are many places in my programs where I want to do this sort of
INSERT OR REPLACE functionality and the ability to do so would make my
code more maintainable, and much less prone to stupid errors.
More simply (?) I want to be able to build the fields / values for my
query and then decide at the last minute whether I was doing an INSERT
or an UPDATE.
To do this (without throwing an error) I currently do a "SELECT ...
WHERE <condition>;" and then construct my SQL appropriately. When I add
a new column to a table (this is the real world :-) I frequently forget
to maintain one branch of the resulting code building the SQL statement.
It is this need that Manuel is catering for by implementing the
functionality in Metabase, but I expect it would be more efficient to
implement it in PostgreSQL natively.
Cheers,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?
On 23 Jan 2002, Andrew McMillan wrote: > I realise this wanders far from the SQL standard, but it's one of those > areas where the standard is awkward from an application programmer's > point of view. I would certainly love to see some syntax of this kind > available. If having to work out the primary key from schema queries is > too much work, then why not have a syntax that explicitly coped - after > all the whole thing is non-standard, so we don't have to follow MySQL's > lead on it: > > INSERT OR REPLACE <table> ( <field list> ) > VALUES( <value list> ) WHERE <condition> > > would be just fine by me. > > There are many places in my programs where I want to do this sort of > INSERT OR REPLACE functionality and the ability to do so would make my > code more maintainable, and much less prone to stupid errors. > > More simply (?) I want to be able to build the fields / values for my > query and then decide at the last minute whether I was doing an INSERT > or an UPDATE. > > To do this (without throwing an error) I currently do a "SELECT ... > WHERE <condition>;" and then construct my SQL appropriately. When I add > a new column to a table (this is the real world :-) I frequently forget > to maintain one branch of the resulting code building the SQL statement. > > It is this need that Manuel is catering for by implementing the > functionality in Metabase, but I expect it would be more efficient to > implement it in PostgreSQL natively. INSERT OR REPLACE table1(a,b,c) VALUES(1,2,3) WHERE a=3 and b=4 and c=5; On the surface it looks ok. But.. What happens if you have a table that isn't just a,b,c there are actually a,b,c,d,e,f and there are more than one tuple with a=3 and b=4 and c=5? Do you replace them all or throw an error? Isn't this what Chris was eluding to? BTW, INSERT OR UPDATE seems more appropriate since you'd be doing an UPDATE if the row already exists. I think that's also what Oracle uses. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
On Thu, 2002-01-24 at 00:24, Vince Vielhaber wrote:
>
> INSERT OR REPLACE table1(a,b,c) VALUES(1,2,3) WHERE a=3 and b=4 and c=5;
>
> On the surface it looks ok. But.. What happens if you have a table
> that isn't just a,b,c there are actually a,b,c,d,e,f and there are more
> than one tuple with a=3 and b=4 and c=5? Do you replace them all or
> throw an error? Isn't this what Chris was eluding to?
OK, so the implementor would have to decide what to do if the condition
matched more than one row, but I'd be happy with _whatever_ was decided
in that regard.
To be honest, I'd even pay the six-query price that Christopher talks
about for the improved ease of maintainability of my scripts that I
would gain. If query efficiency was a concern then I would code around
that as required, because that is usually the exception especially for
the sorts of situations where INSERT OR UPDATE syntax is useful.
> BTW, INSERT OR UPDATE seems more appropriate since you'd be doing an
> UPDATE if the row already exists. I think that's also what Oracle uses.
You're probably right. I do remember they use CREATE OR REPLACE for
procedures and such, but I don't use Oracle too much nowadays :-)
Cheers,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?
Hi, | | BTW, from my estimates you'd need at least 4 - 6 sql queries to simulate the | REPLACE syntax for Postgres, so why on earth would you use it? | I think Chris would be right. In my approach the true emulation of REPLACE syntax is the following: 1. BEGIN; 2. SELECT ... FOR UPDATE; 3a. UPDATE ...; 3b. INSERT ...; 4. COMMIT; I think each step is required to do a foolproof code. Transaction (BEGIN and COMMIT) guarantees the atomicity for so called "REPLACE", and the "FOR UPDATE" clause locks the corresponding row, if it exists. (AFAIR, it works only in transaction, obvoiusly.) So, it takes 4 SQL statements. Papp Gyozo - pgerzson@freestart.hu
Folks,
You can propose this syntax additon on the pgsql-sql list, which exists
to discuss these kinds of issues. However, I will warn you that the
core developers have already rejected the REPLACE syntax once as an
unnecessary deviation from the SQL92 standard.
There was a long discussion following that rejection, sometime last
summer I think, about how one could use triggers or rules to ape this
functionality on an INSERT statement. See the pgsql-sql list
archives.
You can, of course, also write the ANSI committee and propose that
REPLACE be added to SQL3. Good luck.
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
On Wed, 23 Jan 2002, Josh Berkus wrote: > Folks, > > You can propose this syntax additon on the pgsql-sql list, which exists > to discuss these kinds of issues. However, I will warn you that the > core developers have already rejected the REPLACE syntax once as an > unnecessary deviation from the SQL92 standard. > > There was a long discussion following that rejection, sometime last > summer I think, about how one could use triggers or rules to ape this > functionality on an INSERT statement. See the pgsql-sql list > archives. > > You can, of course, also write the ANSI committee and propose that > REPLACE be added to SQL3. Good luck. Has it ever been brought up on hackers? I remember the create or replace stuff conversation, but that was regarding functions. I can't remember anything about insert or update. Chris, maybe you wanna bring it up there? I'm going to be available only on a limited basis the next couple of weeks or so or I would. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
Hello, Vince Vielhaber wrote: > > On 23 Jan 2002, Andrew McMillan wrote: > > > I realise this wanders far from the SQL standard, but it's one of those > > areas where the standard is awkward from an application programmer's > > point of view. I would certainly love to see some syntax of this kind > > available. If having to work out the primary key from schema queries is > > too much work, then why not have a syntax that explicitly coped - after > > all the whole thing is non-standard, so we don't have to follow MySQL's > > lead on it: > > > > INSERT OR REPLACE <table> ( <field list> ) > > VALUES( <value list> ) WHERE <condition> > > > > would be just fine by me. > > > > There are many places in my programs where I want to do this sort of > > INSERT OR REPLACE functionality and the ability to do so would make my > > code more maintainable, and much less prone to stupid errors. > > > > More simply (?) I want to be able to build the fields / values for my > > query and then decide at the last minute whether I was doing an INSERT > > or an UPDATE. > > > > To do this (without throwing an error) I currently do a "SELECT ... > > WHERE <condition>;" and then construct my SQL appropriately. When I add > > a new column to a table (this is the real world :-) I frequently forget > > to maintain one branch of the resulting code building the SQL statement. > > > > It is this need that Manuel is catering for by implementing the > > functionality in Metabase, but I expect it would be more efficient to > > implement it in PostgreSQL natively. > > INSERT OR REPLACE table1(a,b,c) VALUES(1,2,3) WHERE a=3 and b=4 and c=5; REPLACE only uses the primary key to figure if a row exists, so it won't find more than one row. > BTW, INSERT OR UPDATE seems more appropriate since you'd be doing an > UPDATE if the row already exists. I think that's also what Oracle uses. That's what Metabase uses as default implementation, it works in PostgreSQL, Oracle, MS SQL server, Informix, Interbase and eventually with any ODBC data source that supports SQL and transactions. Regards, Manuel Lemos