SPOPS compared with SQL::Catalog + DBIx

[princepawn]

I am breaking out in a sweat just looking at all that work you did for such a simple single-table commit to database. Have a look at a SQL::Catalog + DBIx version of the same code

package DBH;

use strict;
use DBIx;

use constant DBI_DSN  => 'DBI:vendor:database:host';
use constant DBI_USER => 'user';
use constant DBI_PASS => 'pass';
my $DBH;

sub import {
   unless (ref $DBH) {
      $DBH = DBI->connect(
         DBI_DSN, DBI_USER, DBI_PASS,
         {RaiseError => 1}
      );
   }
   $DBIx::DBH = $DBH;
}

1;
package main;

use DBH; # creates database handle via import()

### initialize + config code un-necessary
### not with SQL::Catalog + DBIx
### and not, as mentioned in the conclusion
### with DBIx::Recordset
### and probably not Alzabo either



find sub {
   return unless m/\.mp3$/;
   
   my $tag = get_mp3tag($_) or return;
   my %tag = %$tag;
    # sql_do finds the $dbh for you...
    sql_do sql_lookup 'insert_mp3_tag_data', 
         @tag{title artist album year genre} ;
 
}, @ARGV;

Also, consider that your entire example would be a DBIx::Recordset one-liner. I think I will contact Dave Rolsky and coax him into posting an Alzabo version. Then every person who is actively working on database frameworks will have an example here.

[lachoy]

I was a little surprised it could be used in a one-off script like this, myself. (That said, it's a great simple example!) SPOPS wasn't really designed for this sort of thing -- it was originally designed for server applications using lots of objects related to one another. That's why I assumed creating a subroutine to fetch the database handle wouldn't be a big deal. (As jeffa mentioned, I've since added a helper class to the SPOPS distribution so you can specify the DBI or LDAP connection info in the object configuration. Feedback is good!)

So if you could do this more tersely (even one-line) in other tools, great! However, there are many features that SPOPS has that makes it more than just an object-relational (or DBI-wrapper) tool.

- It supports other datasources than DBI. You can have pretty much all the same functionality with objects in LDAP directories, including manipulating them across different directories. You can also create relationships between objects on different databases.

- You can add transparent behaviors to the persistence functionality. For instance, if we were going to create an application using jeffa's core idea, we might want to fill in holes in the ID info from the MP3 -- maybe the genre is left out or something else. It's very simple to create a 'rule' that gets triggered before the object is saved to check a CDDB source and find our missing information. Then in our application we never have to worry about having all the correct information -- the object will do it for us. (You can use these rules to do just about anything :-)

In any case, as always: use the tool most appropriate for the job. IMO, there's no competition here with any of the POOP stuff. I know I'm not bashful about swiping features from other modules :-) And what will be superfly is if the P5EE effort is able to create an API for object persistence. Then no matter what implementing module you used, your code would look the same.

Chris
M-x auto-bs-mode

[autarch]

Terrence asked me to post an Alzabo version of this code for the curious. So, assuming that you already have your schema object defined (which can be done in about 5 lines of code with reverse engineering or via the web app that comes with Alzabo)...

use strict;

use MP3::Info;
use File::Find;
use Alzabo::Runtime;

$|++;
@ARGV = ('.') unless @ARGV;

my $schema = Alzabo::Runtime::Schema->load_from_file( name => 'mp3' );

find sub {
   return unless m/\.mp3$/;
   
   my $tag = get_mp3tag($_) or return;
   my %mp3 = map {lc($_)=>$tag->{$_}} keys %$tag;

   print STDERR join(':',values %$mp3),"\n";
   $schema->table('Track')->insert( values => \%mp3 );

}, @ARGV;

Of course, that's a pretty weird table that has album, artist, etc. You'd probably want to do that relationally with multiple tables instead.

Terrence, you neglected to mention that 'sql_lookup' bit in your example actually requires some setup in advance.

[princepawn]

Terrence, you neglected to mention that 'sql_lookup' bit in your example actually requires some setup in advance.

Right you are, sql_lookup is a function in SQL::Catalog and SQL::Catalog archives all SQL for a project to a database for later use. Thus, the first step in designing a SQL-oriented database app is to create all your SQL along with labels and store it. then, you can retrieve it using SQL::Catalog.

and then use any DBI shortcut interface to execute the SQL for the desired results.