where is DBI::DWIM?

[Eric Wilhelm]

This is partly an SQL question but I'm also trying to find out if there is
a perl module that has already solved it. Searching CPAN, google, and
reading the DBI/DBD docs seems to indicate that this is just the status
quo.

I am new to SQL, but I'm shocked by the fact that there is no "set the
value of this column in this row" command. I can INSERT or UPDATE, but
first I must SELECT to see if the row already exists (unless I disable
some warnings, do with autocommit and just '$ins->(@vals) or
$upd->(@vals)', but this still requires me to write two SQL statements
which basically express the same desire and then prepare two statement
handles.

At best, it seems to double (or triple) the amount of SQL-related code,
but at worst, it inherently violates the "single point of truth" rule.

REPLACE is not a good solution, since the rest of the row's contents
should be left intact (but if it isn't there, we just want to create it
and get on with our lives.)

It seems like an UPDATE statement could easily enough be translated into
the required INSERT statement (but maybe I haven't thought forward far
enough in this idea.)

If anyone could give me a tip that would allow one "statement of intent"
to create whatever SQL statements are needed, that would be great.

Otherwise, tell me why this is crazy before I go write DBI:WIM

With all of the sql use via Perl and the tendency of Perl to make you
want to be consise, I would think that some kind of "make it so" function
would have been cooked up by now.

Thanks,
Eric

 

[Jeff Boes]

Otherwise, tell me why this is crazy before I go write DBI:WIM

Quite a few, actually. I think what you are looking for are the various things
in the DBIx::* module grouping. (Ironically, there's even a "DBIx:WIW" class.)

 

[Tore Aursand]

This is partly an SQL question [...]

Let me first of all say that - without not knowing anything about what
you're trying to solve - you might be asking about too much for the SQL
standard.

However; Your existing solution - or what you're thinking about - seems
to have a general flaw already. Let's think about "doing something with
something" OO-wise:

1. You establish an instance of a typed something
2. You work with, and relate to, that instance
3. You get rid of that instance

Everywhere on this way you know for sure what/who the instance is, so why
do you need to re-create your relationship with the instance for each of
the parts above?

More code-related, these three would look like this:

1. Read data from a database
2. Do something with the data
3. Get rid of the data (ie. save and dispose, or just dispose)

Where do _you_, in _your_ application, loose control over what you're
working with?

I am new to SQL, but I'm shocked by the fact that there is no "set the
value of this column in this row" command.

Despite your arguments, there is:

UPDATE user SET username = 'john_doe' WHERE user_id = 34

This one sets a column's value for a specific row in the database. No
hassle.

I can INSERT or UPDATE, but first I must SELECT to see if the row
already exists [...]

Exactly _why_ do you have to do that? This seems like a design flaw to
me.

It seems like an UPDATE statement could easily enough be translated into
the required INSERT statement (but maybe I haven't thought forward far
enough in this idea.)

You could - of course - subclass the DBI module and get what you want.

Otherwise, tell me why this is crazy before I go write DBI:WIM

AFAIK, there's a module on CPAN with that name already...?

 

[Ben Liddicott]

Not sure why you want to do this, but....

--given
create table myTable(
field1 type1,
field2 type2,
field3 type3
)


Below we assume you are using the standard-ish command parameter % syntax,
where % is replaced with parameters.
If you want to update/create a value and PK:


declare @val1 type1
declare @val2 type2
select @val1 = %
select @val2 = %

-- Imaginary syntax:
-- UPDATE OR REPLACE set field1 = @val1, field2 = @val2 from myTable where
field1 = @val1

if not exists (select 1 from myTable where field1 = @val1 and field2 =
@val2) begin
if exists (select 1 from myTable where field1 = @val1) begin
update myTable set field2 = @val2 where field1 = @val1
end else begin
insert myTable(field1, field2)
select @val1, @val2
end
end

If you are only interested in the one value:

-- UPDATE OR REPLACE set field1 = @val1 from myTable
declare @val1 type1
select @val1 = %
if not exists(select 1 from myTable where field1 = @val1) begin
insert myTable(field1)
select @val1
end


The above should be close to working in MS T/SQL. You will have to jiggle it
about a bit to make it work in PostgreSQL/PL/SQL or Oracle/PL/SQL. It should
give you the basic idea though.

Cheers,
Ben Liddicott

 

[Tore Aursand]

The situation that I have is that 3-4 programs are responsible for
creating one record in a table. The first application may set 2-3
columns, another application sets 1 column, etc.

Aha! Just what I needed to know! It seems, though, that this is a
special situation, and thus it requires a special solution. Perl can, of
course, help you with this.

As long as you don't want to - or can't - use REPLACE, I don't think you
can avoid having two (at least) SQL statements. You could, however, let
Perl do the job for you - under the hood.

I don't know enough about your application(s) to offer the best solution,
but it seems pretty clear that you need a function (in Perl) which does
all the dirty work for you;

1. Lookup record (if it exists)
2. UPDATE or INSERT, depending on #1

 

[Bill]

On Tue, 18 Nov 2003 05:12:33 -0600, Tore Aursand wrote:

The situation that I have is that 3-4 programs are responsible for
creating one record in a table. The first application may set 2-3
columns, another application sets 1 column, etc.

If there were a pre-ordained order in which they had to run, it would make
sense that the first to run sets the name and values with an INSERT,
except that it must also assume that it may have run before, therefore it
could use a REPLACE.

All other applications could then require that the row is already present
and they could use an UPDATE to set their column(s) (each column is the
responsibility of only one program.)

The problem is that all of the column values are independent of each
other, so if the programs are forced to run in the order C,D,E,A,B and C
always does a REPLACE, you would have to re-run D,E,A,B to get the
previous (unchanged) values back into the table.

Now, we could eliminate this "row-smashing" by adding code to C which did
UPDATE or INSERT, but that still leaves us tied to the order C,D,E,A,B
when the data does not logically require that order.

To allow the programs to run in any order, each one would have to UPDATE
or INSERT which means that each one needs twice as much SQL code.

Subclass DBIx:WIW (and add it to CPAN if it works well)

 

[Peter Marksteiner]

: I don't know enough about your application(s) to offer the best solution,
: but it seems pretty clear that you need a function (in Perl) which does
: all the dirty work for you;

: 1. Lookup record (if it exists)
: 2. UPDATE or INSERT, depending on #1

On databases with decent transaction handling, you could use this
brute-force approach:

$sth1 = $dbh->prepare("delete from my_table where pkey = ?");
$sth2 = $dbh->prepare("insert into my_table (pkey, other) values (?,?)");
$dbh->begin_work;
$sth1->execute($primary_key);
$sth2->execute($primary_key, $other_value);
$dbh->commit;

Peter

--

 

[Matthew Braid]

The situation that I have is that 3-4 programs are responsible for
creating one record in a table. The first application may set 2-3
columns, another application sets 1 column, etc.

BEGIN;
if (not UPDATE table SET column = value WHERE key = rowid) {
if (not INSERT INTO table (key, column) VALUES (rowid, value)) {
ROLLBACK;
die "FAILED TO UPDATE!\n";
}
}
COMMIT;

In other words:

1) Start a transaction
2) Try an update
-> If the update affects zero rows, try an insert
-> -> If the insert fails, rollback and die
3) Commit the transaction

Absolutely no select is necessary, and you don't need to use some kind
of bodgy roll-your-own SQL extension.

MB

 

[Ben Liddicott]

Sorry, should be ? for parameter, not %.


news:[email protected]...

 

[ctcgag]

To allow the programs to run in any order, each one would have to UPDATE
or INSERT which means that each one needs twice as much SQL code.

Presumably you already know the primary key which already exists or is to
be used in the insert if it doesn't exist.

If you are writing for MySQL only (I think Replace is MySQL specific), then
you could make a handle/sub which just does
"insert ignore into table_foo (primary_key) values (?)"

Call it before you do whatever update you need to do.
It will create the row if necessary, and silently do nothing if
it's already there. You don't need to explicitly check the outcome.

You can put the definition into a central module if you want.

It's simple and fairly clean.

Xho