Обсуждение: INSERT OR UPDATE?

Hello all,

I am writing an app in PHP that uses a PostGres database.
One thing i have noticed is that what should/could be a single line of
SQL code takes about 6 lines of PHP.  This seem wasteful and redundant
to me.

Here is a sample of what I'm talking about ($db is a PDO already
defined and created).

$query[1] = "UPDATE my.table, SET somefield = '$someval' WHERE
somecondition";
$query[2] = "INSERT INTO my.table (somefield) VALUES ('$someval')";
if(!$db->query($query[1])){
    $db->query($query[2]);
}

What I'm curious to know is if there is some way to simplify this,
either buy some PHP builtin or extension, or possibly something in SQL
I am missing.  It seems to me that "UPDATE OR INSERT", should be valid,
but I can't seem to find anything relevant at all about it.

Anyways I hope you don't mind, but I'm crossposting this to
pgsql.general and comp.lang.php to see if I can get some information on
the subject.

>I am writing an app in PHP that uses a PostGres database.
>One thing i have noticed is that what should/could be a single line of
>SQL code takes about 6 lines of PHP.  This seem wasteful and redundant
>to me.
>
>Here is a sample of what I'm talking about ($db is a PDO already
>defined and created).
>
>$query[1] = "UPDATE my.table, SET somefield = '$someval' WHERE
>somecondition";
>$query[2] = "INSERT INTO my.table (somefield) VALUES ('$someval')";
>if(!$db->query($query[1])){
>    $db->query($query[2]);
>}
>
>What I'm curious to know is if there is some way to simplify this,
>either buy some PHP builtin or extension, or possibly something in SQL
>I am missing.  It seems to me that "UPDATE OR INSERT", should be valid,
>but I can't seem to find anything relevant at all about it.

MySQL permits (but it's not standard, and available in MySQL 4.1.0
and later):

INSERT INTO my.table (somefield) VALUES ('$someval') ON DUPLICATE
KEY UPDATE somefield = '$someval';

This is very useful for times when you want to count something (e.g.
SPAM), and if a record doesn't exist, make one with a count of 1.

I don't know whether something similar is available in PostGres.

                    Gordon L. Burditt

I think is almost the same that in many other languages, and like in
many other with the time you can have function's libraries, or more
likely class libraries with the usefull stuff.

In desktop programming environments you have components, here you have
classes that are the same thing using it in another way. :) Watch out
the new auto-includes from PHP5 classes.

There are scripts with differents approaches, you can select one or
build your own for multiple proyects.
Personally I preffer not to build the SQL code in PHP. I allways have
the queries in XML files with an specific format I chose, to describe a
SQL Query with parameters. A class to handle the query can "tell me"
about the parameters or simply "receive" the parameters, build and
execute the query,  and return the results or making it browseable
(recordset).

A very usual and smart approach is to use clases in PEAR::DB.

Gordon Burditt wrote:
> [...stuff snipped...]
>
> MySQL permits (but it's not standard, and available in MySQL 4.1.0
> and later):
>
> INSERT INTO my.table (somefield) VALUES ('$someval') ON DUPLICATE
> KEY UPDATE somefield = '$someval';
>
> This is very useful for times when you want to count something (e.g.
> SPAM), and if a record doesn't exist, make one with a count of 1.
>
> I don't know whether something similar is available in PostGres.
>
>                     Gordon L. Burditt

In Postgres you'd probably create a server-side function to get that
effect.

Try (for simple cases):

  DELETE FROM my.table WHERE somecondition;
  INSERT INTO my.table (somefield) VALUES ('$someval');

In complex cases it may be necessary to INSERT the values into a
temporary table, which is then used to condition the DELETE before
INSERTing the temporary table into your permanent table.

One advantage of the above is that it works for multiple rows, whereas
your technique does not.

-- Dean

On 2005-10-08 23:15, smorrey@gmail.com wrote:

>Hello all,
>
>I am writing an app in PHP that uses a PostGres database.
>One thing i have noticed is that what should/could be a single line of
>SQL code takes about 6 lines of PHP.  This seem wasteful and redundant
>to me.
>
>Here is a sample of what I'm talking about ($db is a PDO already
>defined and created).
>
>$query[1] = "UPDATE my.table, SET somefield = '$someval' WHERE
>somecondition";
>$query[2] = "INSERT INTO my.table (somefield) VALUES ('$someval')";
>if(!$db->query($query[1])){
>    $db->query($query[2]);
>}
>
>What I'm curious to know is if there is some way to simplify this,
>either buy some PHP builtin or extension, or possibly something in SQL
>I am missing.  It seems to me that "UPDATE OR INSERT", should be valid,
>but I can't seem to find anything relevant at all about it.
>
>Anyways I hope you don't mind, but I'm crossposting this to
>pgsql.general and comp.lang.php to see if I can get some information on
>the subject.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>
>

On 09.10.2005 08:48, andrew wrote:
> A very usual and smart approach is to use clases in PEAR::DB.

Well, IMHO PEAR::DB is one of the worst classes of PEAR. Besides its
ugly code it's worth nothing. This is some incomplete "abstraction
layer" for kiddies, to make it easy for people coming from stupid
storage engines like MySQL or SqLite, to _store_ their data in another
DBMS.

I never really understood, what such a thing should be good for. SQL
implementations are not portable, nor are stored procedures, functions,
rules, triggers, etc. Every DBMS implements them in it's own way and
almost all professional devs will implement most, or at least the
critical parts, of the business logic directly inside DBMS and not at
application level. Thus making the application basically just a
frontend, calling stored procedures and writing to views, controlled by
triggers and strict permissions.

The only thing PEAR::DB might be usefull for is, writing those famous
cross-dbms-apps, which basically use the DBMS highly inefficient as a
better plaintext file (not even using foreign keys in most cases, so it
will work with MySql 3.x as well).

And if you don't what to write such bizarre cross-dbms-apps, what is the
point in using PEAR::DB at all? Those few incomplete methods wrapping
the pg_* functions in some highly debatable way?

Do yourself a favor and write your own slime and efficient set of
classes around the native API-functions of your DBMS. PEAR::DB and
others prevent you from using an advanced DBMS the way you should.


--
Regards,
Hannes Dorbath