[Dancer-users] Adding quick_insert / quick_update to Dancer::Plugin::Database - design feedback requested

[David Precious]

On Saturday 04 December 2010 14:17:52 Иван Бессарабов wrote:
> Now with the help of your plugin the user is staying along only with
> dbh and to execute sql and retrive data user still need to make too
> many actions. It is super great that you are planning to add the new
> way to work with data.

Indeed - Dancer makes many things so unbelievably easy, whilst still providing 
the flexibility to do whatever you want.  I think D::P::Database needs to 
follow this mantra :)


> Before this your letter I've thought of how to make life with sql
> easier and started writing small module with my vision of how to do it
> (my vision is very simple, but not common - not to write sql requests
> in perl syntax, but to write them in sql). You mail inspired me to
> make my module more or less complete, and to upload it to github and
> cpan.
> Actually the module is not ready to work with Dancer (it has no
> mechanism to renew connection), but this is a first step. The module
> is avaliable at github: https://github.com/bessarabov/SQL-Easy (I've
> uploaded it to cpan, but some time should pass before it is
> avaliable).

Interesting stuff.

I do think it replicates a fair amount of stuff that could be done easily 
enough just with core DBI methods, though.

For instance, from your examples, :

  my $posts_count = $se->return_one('select count(id) from posts');
  print Dumper $posts_count; # will print $VAR1 = 42;

Could be equally done with, e.g.:

  my ($posts_count) = $dbh->selectrow_array('select count(id) from posts');

Likewise,:

  my @a = $se->return_row("select dt_post, title from posts where id = ?", 1);

Could be done with:

  my @a = $dbh->fetchrow_array('select ... where id = ?', undef, 1);

(At least your modules avoids the need to pass undef (or an empty hashref, or 
something) to skip over the 2nd param, which always irritates me :) )


> Concerning your question: I do agree that changing the meaning of
> positional parameters is not the right thing. From the other side
> writing every time huge hash is also not a good solution. Maybe you
> should not use procedures, but to use methods (and the object is the
> db connection)?

Yeah, that's another approach I considered; the more I think about it, the 
more I think this could be the correct answer.

I think it would make sense to provide an option to enable/disable the extra 
stuff; with it disabled, the database() keyword would just return a DBI 
connection handle, as it does now.  With it enabled, database() would instead 
return an object which subclasses DBI::db, but provides the extra methods.  
E.g. Dancer::Plugin::Database::Handle.

That would make things much cleaner, as you could simply say:

  database->quick_insert($table_name, { field => 'value' });

and for a specific named database connection configuration,

  database('foodb')->quick_insert(...);

That would seem to be sane, is entirely backwards-compatible (existing Dancer 
apps using D::P::Database wouldn't have the new option enabled, so would 
continue to receive a standard DBI::db handle just as they did before), and 
new apps which choose to make use of this behaviour get a handle that can do 
all the cool new stuff, but is a subclass of DBI::db, so still does all the 
traditional DBI stuff as you'd expect, too.

I think I've convinced myself there, but would appreciate feedback from anyone 
else, too!

(Starting to wish I'd done this before today's advent calendar post, so it 
could have been mentioned there!)

Cheers

Dave P

-- 
David Precious <davidp at preshweb.co.uk> (bigpresh)
http://blog.preshweb.co.uk/    www.preshweb.co.uk/twitter
www.preshweb.co.uk/linkedin    www.preshweb.co.uk/facebook
www.preshweb.co.uk/identica    www.lyricsbadger.co.uk

  "Programming is like sex. One mistake and you have to support 
  it for the rest of your life". (Michael Sinz)