Drop dead simple MySQL interface?

jabowery has asked for the wisdom of the Perl Monks concerning the following question:

Is there a Perl module that provides a drop-dead simple interface to MySQL along the following lines:

$db=DDSMySQL->connect(...);
$object=$db->{table_foo}->{id}=$bar;
$object->{some_other_key}=$blah;
$baz=$object->{baz};
$qux=$object->{qux};

...etc...
[download]

It's OK (by me) if it generates accessors instead (or in addition to) hash keys from its interrogation of MySQL for the db's schema:

$object=$db->table_foo->id($bar);
$object->some_other_key($blah);
$baz=$object->baz;
$qux=$object->qux;

...etc...
[download]

PS: It might be entertaining to quibble over whether, in the tradition of Perl's tendency to default to allocate stuff it needs on demand, it should create a row with 'id' equal to $bar and 'some_other_key' equal to $blah if it doesn't find that composite key already in the database. But I digress...

Comment on Drop dead simple MySQL interface? Select or Download Code

Replies are listed 'Best First'.
Re: Drop dead simple MySQL interface? by jeffa (Bishop) on Apr 04, 2012 at 23:57 UTC
Take a look at DBIx::Class (and SQL::Translator for help with automating the necessary schema creation). jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply] [d/l] [select]
Re: Drop dead simple MySQL interface? by NetWallah (Canon) on Apr 05, 2012 at 02:59 UTC
The kind of syntax you are looking for seems closer to a 'Tied' interfce .. perhaps something like Tie::DBI would meet your needs. It provides syntax like: `# fetching keys and values @keys = keys %h; @fields = keys %{$h{$keys[0]}}; print $h{'id1'}->{'field1'}; while (($key,$value) = each %h) { print "Key = $key:\n"; foreach (sort keys %$value) { print "\t$_ => $value->{$_}\n"; } } # changing data $h{'id1'}->{'field1'} = 'new value'; $h{'id1'} = { field1 => 'newer value', field2 => 'even newer value', field3 => "so new it's squeaky clean" };` [download] All great truths begin as blasphemies. ― George Bernard Shaw, writer, Nobel laureate (1856-1950)	[reply] [d/l]
Re^2: Drop dead simple MySQL interface? by jabowery (Beadle) on Apr 05, 2012 at 14:23 UTC
Yes a Tied interface is what is called for here (two Tied interfaces? one for the database lookup of a table (since there can be only one table of a given name in a database) and one for a table lookup of a row) but the requirement for composite keys makes the behavior quite a bit different from Tie::DBI. An extreme simplification of the Tied interface would make the first level of hash specify the database server type (ie: server=>'dbi:mysql'), the second level hash specify the credentials (ie: {user=>..., password=>...}), a third level specifying the database name (ie: {$database_name=>{...}}) and then the rest of the hashes behave as previously described for tables and fields. Also, if something is underspecified, it might return an array; but if it worked that way, the row and field hash interfaces would have to always go through an array ref, ie: `$baz=$object->[0]->{baz}->[0]; $qux=$object->[0]->{qux}->[0];` [download] Blech... But I suppose the schema could restrict that mess to when that level of indirection was required. Finally, I suppose insertion of a row could also take the form of a 'new' method on the table: `$table->new({baz=>$baz,$qux=>$qux,...});` And if there is an autoincrement key (like 'id'), leaving it out would imply its value to be the next incremented value of that field for that table.	[reply] [d/l] [select]
Re^3: Drop dead simple MySQL interface? by NetWallah (Canon) on Apr 05, 2012 at 15:29 UTC
I'm not sure if you are looking for affirmation, comments, or direction. In any case, I don't feel qualified for any of these, since I have not used Tie::DBI - I proposed it because it just seemed a good fit, based on your initial description. Perhaps a more experienced monk would care to respond. All great truths begin as blasphemies. ― George Bernard Shaw, writer, Nobel laureate (1856-1950)	[reply]