I do like it. When I'm throwing together websites and need a quick query or two, it looks nice. However, I would rather get back hashrefs instead of arrays. And are those arrays all in memory at once? Or are they tied to something that does incremental fetches?

And how about a convenience update_unless_insert which inserts a row if it can, otherwise, does an update on it....

Finally, I think the nomenclature for this module is a bit ... well... hmm. It should be DBI::EZ or DBIx::Easy shouldnt it? Just to show the closeness of it's implementation to DBI.

Also, fix the author part of the docs. It has the AU Thor business there.

princepawn wrote:

how would you hide the user and pass outside of an EZDBI script?

Put the username and password in another file that only the script owner has permissions to. You can just open and read it or use something like App::Config if your configuration information gets to be complicated. Using Storable for a quick and dirty solution is another option.

I agree that the namespace is a bit odd.

Cheers,
Ovid

Vote for paco!

Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

Says PrincePawn:

how would you hide the user and pass outside of an EZDBI script?

The same way you'd hide them outside of a DBI script, I guess.

I would rather get back hashrefs instead of arrays.

I was thinking of putting that in, but I thought that it might be better to release right away and see what people said. Thanks for your input!

And are those arrays all in memory at once? Or are they tied to something that does incremental fetches?

They're in memory. Incremental fetching would be slow, and it's better to squander space than time.

And how about a convenience update_unless_insert which inserts a row if it can, otherwise, does an update on it....

I thought there was an option to Update that would do that? I will put it on the to-do list.

--
Mark Dominus
Perl Paraphernalia

It's not always better to squander space: doing so may result in the program not working at all. Squandering time just makes it run slowly. But that's more of a philosophical point.

WRT the update_unless_insrt: there isn't such an option: PrincePawn's referring to the sort of situation where you've got enough information to create a record from scratch, or replace an existing one.

Thus (under Oracle) you get code similar to:

BEGIN
  INSERT INTO mytab (id, ...) VALUES (id_value, ...);
EXCEPTION
WHEN DUP_VAL THEN
  UPDATE mytab
  SET .. = ...
  WHERE id=id_value;
END;
[download]

. id would presumably be limited to the primary key, rather than any unique key. Personally, I think I'd prefer to see it as inserting if the update fails (which avoids the problem of duplicating a different column).

princepawn: Also, fix the author part of the docs. It has the AU Thor business there.

The profilic A. U. Thor, from such modules as Parse::RecDescent::Consumer - Perl extension for blah blah blah. :-)

--
John.

     use EZDBI 'mysql:test' => 'user', 'password';
[download]

Also, I am curious about connection caching. Does each different that use this get a new DB connection?

I'll be curious to see what you come up with. I'm sure you're much better at this than I :)

Says MikeB:

I went this route once, and while it worked nicely for Select, Update didn't seem to fit too well.

Hmm. It seems to me that it works better for update than for select, because with select there are a lot of different ways you might want to get the result back, but with update that is not an issue.

You probably know something that I need to know. Do you remember what the problem with update was?

--
Mark Dominus
Perl Paraphernalia

I always returned an array of hashrefs, figuring if that wasn't enough I'd be using the standard interface :)

It's been a while but I think the problem was that I was using Win32:ODBC, before I discovered DBI, so was trying to put the values directly in the SQL string rather than using placeholders. To get the data correctly escaped required finding the type of each field. IIRC, my approach was a little different in that I passed the hashref from the select back to the update.

Of course, there's also the issue of how to handle updates to complex queries.

When it got to the point where it seemed I was essentially recreating M$ ADO, with all it's problems, I decided it wasn't worth it to me. With DBI's RaiseError and fetchall_arrayref now available, I don't bother to use my old query module anymore even for simple selects.

With DBI and placeholders, you can probably handle simple updates without much problem.

I don't mean to sound discouraging. I'm rather hoping you find a way :)

#--------------------------------------------------------
# define "Person" objects
package Person;

use base qw(Class::Tangram);
$schema = {
    table => "names",
    fields => {
        string => [ qw(first last) ]
    }
};

#--------------------------------------------------------
# define which objects we have in our database
package Project;
use Tangram::Relational;
$dbschema = Tangram::Relational->schema
     ({ classes => [ 'Person' => $Person::schema ]});

sub schema { $dbschema };

#--------------------------------------------------------
#  the main program
package main;

# connect to the database.
my $storage = Tangram::Relational->connect(Project->schema,
                                           $dsn, $u, $p);

# insert a new Person
$storage->insert(Person->new( first => "Harry",
                              last => "Potter" ));

# $remote_person refers to a person in the database,
# for select queries.
my $remote_person = $storage->remote("Person");

if ($storage->select
       ( $remote_person,
         $remote_person->{first} eq "Harry" )) {
   print "Potter is IN THE HOUSE.\n";
}

# iterate through all rows
for my $person ($storage->select("Person")) {
    print $person->last.": ".$person->first."\n";
}

# delete every row matching "Potter"
$storage->erase
    ($storage->select
        ( $remote_person,
          $remote_person->{last} eq "Potter" ));
 
if ($storage->select
       ( $remote_person,
         $remote_person->{last} eq "Potter" )) {
   die "Can't get rid of that damn Harry Potter!";
}
[download]

Tangram is very cool. Check out the guided tour. Once you divorce yourself from the concept of tables and rows and start thinking in terms of objects, life becomes a lot easier...

mugwumpjism says:

Once you divorce yourself from the concept of tables and rows and start thinking in terms of objects, life becomes a lot easier...

Maybe you could elaborate on why you think so. From here, looking at the 30-line program that you wrote that is "equivalent code" for my 11-line program, the reasons are far from obvious.

What's the benefit that justifies a 2-3x increase in code size?

--
Mark Dominus
Perl Paraphernalia

Maybe you could elaborate on why you think so. From here, looking at the 30-line program that you wrote that is "equivalent code" for my 11-line program, the reasons are far from obvious.

What's the benefit that justifies a 2-3x increase in code size?

I agree that it doesn't necessarily make life any easier, and the code increase (line count-wise) is alot less extreme if you ignore the Tangram schema setup and the comments and the line breaks for readability. But the benefit is that its a tool for 'object persistence'. Which puts Tangram and EZDBI in entirely different categories anyway. But I do appreciate the examples of both :)

Update:Re: Dominus's reply - I also said 'ignoring the Tangram ... setup', which is everything up to the 'package main'. Which you can count or not. I figure there's an argument for not counting it since its a constant that, once set up, you can do as much inserting/deleting/updating/selecting as you like. So IF you don't count it, then the amount of code is not much greater.

Since the original program didn't have any objects, I don't see how that could be a benefit in this case.

That was my point, the two modules are for entirely different things :) Making DBI EZ or storing objects in a database. And how can you say its not a benefit in this case just because your script didn't have any objects. I kind of think of Harry Potter as an object :)
Besides, if you're going to make that sort of argument, then I submit that this script is far superior:

use HarryPotter;

InsertHarryPotter();
IsPotterInTheHouse();
PrintHarry() for SelectPotter();
DeletePotter();
SelectPotter() and DamnPotter();
[download]