Re: 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(at)postgresql(dot)org
> [mailto:pgsql-php-owner(at)postgresql(dot)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(at)postgresql(dot)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(at)indvalley(dot)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(at)postgresql(dot)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, 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.

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

--- Francisco Reyes <lists(at)natserv(dot)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

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

OK, so it's not a simulation of the MySQL syntax - MySQL requires no hints.
I've looked thru the code and yeah, basically that's what you have to do, so
long as you can get the app programmer to tell you in the code what the
primary keys are. For a postgres implementation, it's more difficult.

BTW, there's no documentation anywhere in your code - makes it a bit
difficult to see what's going on...

Chris

Hello,

Christopher Kings-Lynne wrote:
>
> > 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.
>
> OK, so it's not a simulation of the MySQL syntax - MySQL requires no hints.

No, but the functionality is the same. MySQL ignores the hints because
SQL REPLACE works as intended without the hints.

> I've looked thru the code and yeah, basically that's what you have to do, so
> long as you can get the app programmer to tell you in the code what the
> primary keys are. For a postgres implementation, it's more difficult.

It is required. If the programmer does not tell which fields are the
keys, the function will fail with an error stating that.

> BTW, there's no documentation anywhere in your code - makes it a bit
> difficult to see what's going on...

Erm.... there is 248K HTML document that details all about that function
the whole Metabase API. :-)

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?

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(at)freestart(dot)hu

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(at)michvhf(dot)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?

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(at)agliodbs(dot)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(at)michvhf(dot)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