I have just begun to learn SPOPS by our own lachoy. Last night (after RTFM'ing for a good two hours), i finally made some progress. The result is a script that reads MP3 files in a given directory and stores their ID3 tag's into a database table. The table i used has an 'id' field which is an auto incremented primary key, and five other fields (title, artist, album, year, and genre) which can be any database type you want. Mine were just varchars that can be NULL, for simplicity's sake.

SPOPS stands for Simple Perl Object Persistence with Security. SPOPS is not one module, but instead a collection of almost FIFTY modules! There is a lot to learn up front, and this post will not go deep into SPOPS (it barely scratches the surface). This post only covers instantiating fairly simple objects that can communicate to a database, it doesn't cover persistence. A review and a tutorial for SPOPS are 'in the works'. Think of this as just a primer.

This code uses File::Find and MP3::Info to find the MP3 files and extract the tag info, respectively. With these two tools, it is easy to write DBI code to store the found tags into a database. SPOPS is used here to abstract the SQL completely away from the client. This is currently implemented in SPOPS by subclassing a SPOPS::DBI object:

package MySPOPS::DBI; use strict; use SPOPS::DBI; @MySPOPS_DBI::ISA = qw(SPOPS::DBI); use constant DBI_DSN => 'DBI:vendor:database:host'; use constant DBI_USER => 'user'; use constant DBI_PASS => 'pass'; my ($DB); sub global_datasource_handle { unless (ref $DB) { $DB = DBI->connect( DBI_DSN, DBI_USER, DBI_PASS, {RaiseError => 1} ); } return $DB; } 1;

This subclassed SPOPS::DBI class can be reused by different clients. The client that i wrote uses the SPOPS::Initialize object to create my MySPOPS::MP3 object.

use strict; use MP3::Info; use File::Find; use SPOPS::Initialize; $|++; @ARGV = ('.') unless @ARGV; # the configuration SPOPS::Initialize->process({ config => { myobject => { class => 'MySPOPS::MP3', isa => [qw( MySPOPS::DBI )], field => [qw(title artist album year genre)], id_field => 'id', object_name => 'mp3', base_table => 'songs', } }}); find sub { return unless m/\.mp3$/; my $tag = get_mp3tag($_) or return; my $mp3 = MySPOPS::MP3->new({map {lc($_)=>$tag->{$_}} keys %$tag}); print STDERR join(':',values %$mp3),"\n"; $mp3->save(); }, @ARGV;

I use File::Find in a similar manner as the code from the Perl Cookbook, recipe number 9.7. If no argument is supplied, then the current directory is recursively scanned.

MP3::Info is used to obtain the MP3 tag from the file. I have not bothered to validate in the interest of keeping the code simple. Adding validation should be trivial, see Identifying MP3 files with no MP3 tag for some tips on that. Another possiblity is to use CDDB.

The trick is the instantiation of the MySPOPS::MP3 object. If you compare the Data::Dumper outputs of a MP3::Info with the Dumper output of what a MySPOSP::MP3 object _should_ look like, you will see that they are very similar:

$VAR1 = bless( { 'ARTIST' => 'The Fixx', 'GENRE' => 'Pop', 'ALBUM' => 'Phantoms', 'TITLE' => 'Woman On A Train', 'YEAR' => '1984', 'COMMENT' => 'underrated band', }, 'MP3::Info' );
$VAR1 = bless( { 'artist' => '', 'genre' => '', 'album' => '', 'title' => '', 'year' => '', 'id' => , }, 'MySPOPS::MP3' );

The MySPOPS::MP3 constructor accepts a hash reference as an argument and will use that hash reference to define it's attributes. All that is needed is to lower case the keys of the MP3::Info object and the two will have virtually the same keys ('comment' will be ignored because it is not in the configuration and 'id' is not needed because it will be handled for you).

So, the MySPOPS::MP3 object is instantiated with a transformed copy of the MP3::Info's internal attribute hash. I could have have named my database table fields with all upper case letters and there would be no need for the transformation.

Finally, a message is printed to standard error and the MySPOPS::MP3::save() method is called, which stores the object's attributes in the database.

The next version of SPOPS (0.56) will allow you skip having to subclass a SPOPS::DBI object and simply pass the the connection credentials along with your configuration:

myobject => { class => 'MySPOPS::MP3', isa => [qw( MySPOPS_DBI )], field => [qw(title artist album year genre)], id_field => 'id', object_name => 'mp3', base_table => 'songs', dbi_config => { dsn => 'DBI:vendor:database:host', username => 'user', password => 'pass', }, }

But this should be used for 'one-offs' only. By subclassing SPOPS::DBI you allow other clients to share and have the databse connection code abstracted away.


(the triplet paradiddle)

Replies are listed 'Best First'.
Class::DBI example
by autarch (Hermit) on Jan 11, 2002 at 04:38 UTC
    Class::DBI is a nice module that I've used previously. Its similar to Alzabo in terms of what it does but it is much smaller (and has many fewer features) but its still quite useful.
    package Song; use base qw(Class::DBI); __PACKAGE__->set_db( 'Main', 'dbi:mysql', 'username', 'password' ); __PACKAGE__->table('Song'); __PACKAGE__->columns( All => qw( song_id title artist album year genre + ) ); __PACKAGE__->columns( Primary => 'song_id' ); package main; find sub { return unless m/\.mp3$/; my $tag = get_mp3tag($_) or return; Song->create($tag); }, @ARGV;
      And, if you're using MySQL then the example can be even smaller:
      package Song;
      use base qw(Class::DBI::mysql);
      __PACKAGE__->set_db( 'Main', 'dbi:mysql', 'username', 'password' );
      package main;
      No need to declare your columns, as it will go ask the database for them. I keep meaning to find out how to do this for other databases, and roll them in ...


        Hi Tony,

        I created a module to do this for SPOPS. I've tested this on MySQL, PostgreSQL and DB2/AS400 thru ODBC. Basically, all you need to do is issue a dummy query against a table so DBI can grab the metadata. Here's an example:

        my $dbh = DBI->connect( ... ) || die "Error connecting: $DBI::errstr"; $dbh->{RaiseError} = 1; my $sql = "SELECT * FROM $table where 1 = 0"; my ( $sth ); eval { $sth = $dbh->prepare( $sql ); $sth->execute; }; if ( $@ ) { die "Cannot fetch column info: $@" } my @column_names = @{ $sth->{NAME} }; my @column_types = @{ $sth->{TYPE} };

        I think most DBDs support this basic metadata. Whenever I've found one that didn't support both of these (like DBD::ASAny at one time), I've harrangued the author and always gotten a quick response :-)

        M-x auto-bs-mode

SPOPS compared with SQL::Catalog + DBIx
by princepawn (Parson) on Jan 08, 2002 at 12:55 UTC
    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.

      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.

        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.

      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.

      M-x auto-bs-mode

SPOPS, DBI, Business Objects
by princepawn (Parson) on Jan 08, 2002 at 12:43 UTC
    I have always wondered about why SPOPS business objects were limited to one table... what happens when you have a business concept which maps across several tables and you need to make a single commit to database, linking up all the foreign keys in all the tables?

    Also, some of the statements about SPOPS and it means of abstracting SQL sound like they will not be making use of DBIx::AnyDBD, which on the dbi-dev and dbi-users list, Tim Bunce has stated will be rolled into DBI proper.

    finally, read this recent post to dbi-users which explains that DBI itself will in the future have business-logic capabilities:

    From: Tim Bunce <Tim.Bunce@pobox.com> Date: Wed Jan 02, 2002 08:45:59 PM US/Eastern To: dbi-dev@perl.org Subject: (Fwd) Important: Subclassing and Merging DBIx::AnyDBD into th +e DBI FYI - This has just been sent to dbi-users@perl.org. Probably best to discuss it there in whatever threads it spawns (but do post here if you've a driver-development related question or you're not subscribed to dbi-users) Tim. ----- Forwarded message from Tim Bunce <Tim.Bunce@pobox.com> ----- Delivered-To: tim.bunce@pobox.com Date: Thu, 3 Jan 2002 01:25:03 +0000 From: Tim Bunce <Tim.Bunce@pobox.com> To: dbi-users@perl.org Cc: Matt Sergeant <matt@sergeant.org>, Tim Bunce <Tim.Bunce@pobox.com> Subject: Important: Subclassing and Merging DBIx::AnyDBD into the DBI Here's what I'm thinking, and developing, at the moment... [Please read and think about it all before commenting] [Also, I've CC'd Matt Sergeant <matt@sergeant.org>, please ensure that he's CC'd on any replies. Thanks.] Firstly, subclassing... (we'll use MyDBI as the example subclass here) The "mixed case with leading capital letter" method namespace will be reserved for use by subclassing of the DBI. The DBI will never have any methods of it's own in that namespace. (DBI method names are either all lower case or all upper case.) The need to call MyDBI->init_rootclass will be removed. Simply calling $dbh = MyDBI->connect(...) will be interpreted as a request to have the $dbh blessed into the MyDBI::db class (and a $dbh->prepare will return $sth blessed into MyDBI::st). A warning will be generated if @MyDBI::db::ISA is empty. Also, and this is where it gets interesting, calling: DBI->connect(,,, { RootClass => 'MyDBI' }) will have the same effect as above, with the added feature that the DBI will try to automatically load MyDBI.pm for you. It'll ignore a failure to load due to the file not existing if the MyDBI class already exists. This feature dramatically opens up the scope of DBI subclassing. The idea behind it is that the $dbh object is no longer 'just' encapsulating a simple database connection, it can now encapsulate a high-level information repository that can be 'queried' at a more abstract level. So instead of just calling low-level do/prepare/execute/fetch methods you can now call higher-level methods that relate to your own data and concepts. More below. Typically a 'Sales Order Handling' database could now be given a SalesOrderDBI::db class containing high-level methods that deal directly with Sales Order Handling concepts and could do things like automatically trigger re-ordering when stocks get low. Also consider, for example, that DBD::Proxy would be able to dynamic +ally load the subclass on the proxy server instead of the proxy client. The subclass can perform multiple DBI method calls before returning a result to the client. For example: $ok=$dbh->Check_Available($a,$b +) on the proxy client triggers a $dbh->Check_Available($a,$b) call on the proxy server and that method may perform many selects to gather the info before returning the boolean result to the client. Performing the selects on the proxy server is far far more efficient +. In terms of buzzwords, the dynamic loading of subclasses can translate into "Encapsulating Business Logic" and thowing in the proxy extends that to "3-Tier" :) Also, the ability to embed attributes into the DSN may lead to some interesting possibilities... DBI->connect("dbi:Oracle(PrintError=1,RootClass=OtherDBI):...",. +..) I'm not sure where that road leads but I suspect it'll be interesting, though I may disable it by default, or just provide a way to do so. Next, merging in DBIx::AnyDBD functionality... Rather than describe Matt Sergeant's excellent DBIx::AnyDBD module I'll just describe my plans. You can take a look at http://search.cpan.org/search?dist=DBIx-AnyDBD to see the obvious inspiration. Calling $dbh = DBI->connect("dbi:Oracle:foo",,, { DbTypeSubclass => 1 }) will return a $dbh that's blessed into a class with a name that depends on the type of database you've connected to. In this case 'DBI::Oracle::db'. @DBI::Oracle::db::ISA=('DBI::db') is automatically setup for you, if it's empty, so the inheritance works normally. For ODBC and ADO connections the underlying database type is determined and a class hierarchy setup for you. So an ODBC connection to an Informix database, for example, would be blessed into 'DBI::Informix::db' which would automatically be setup as a subclass of 'DBI::ODBC::db' which would be setup as a subclass of 'DBI::db'. The DBI will try to automatically load these classes for you. It'll ignore a failure to load caused by the file not existing. The idea behind this, if it's not dawned on you already, is to enable a simple way to provide alternate implementations of methods that require different SQL dialects for different database types. See below... Finally, putting it all together... These two mechanisms can be used together so $dbh = MyDBI->connect("dbi:Oracle:foo",,, { DbTypeSubclass=>1 }) will return a $dbh blessed into 'MyDBI::Oracle::db'. In fact users of DbTypeSubclass are strongly encouraged to also subclass into a non-'DBI' root class. They are a natural fit togethe +r. Imagine, for example, that you have a Sales Order Handling database and a SalesOrderDBI::db class containing high-level methods like automatically triggering re-ordering when stocks get low. Imagine you've implemented this, or just prototyped it, in Access and now want to port it to PostgreSQL... A typical porting process might now be... 1/ Break up any large methods that include both high-level business logic and low-level database interactions. Put the low-level database interactions into new methods called from the (now smaller) original method. 2/ Add { DbTypeSubclass=>1 } to your connect() method call. 3/ Move the low-level database interaction methods from the SalesOrderDBI::db class into the SalesOrderDBI::Access::db class. 4/ Implement and test alternate versions using PostgreSQL in the SalesOrderDBI::Pg::db class. Since PostgreSQL supports stored procedures you could move some of the business logic into stored procedures within the database. Thus your Access specific class may contains select statements but your PostgreSQL specific class may contain stored procedure call +s. Random extra thoughts... AUTOLOAD (ala Autoloader, SelfLoader etc) could be put to good and interesting uses in either handling large libraries of queries (load in demand) or even automatically generating methods with logic based on the method name: $dbh->Delete_from_table($table, $key) Oh, scary. The logic for mapping a connection into a hierarchy of classes will be extensible and overridable so that new or special cases can be handled, such as considering/including the database version. Comments welcome. (But please trim replies, and be brief and to the po +int!) Tim. p.s. Don't forget to CC Matt Sergeant <matt@sergeant.org> ----- End forwarded message -----

      Actually, some enterprising folks have already done this mapping of objects to multiple tables -- with inheritance -- using SPOPS. (Search the openinteract-dev mailing list for ESPOPS, or /msg me for a url.) I haven't been able to fold it into the main module yet but I hope to in the near future (~2 months).

      Also note that SPOPS doesn't prevent you from doing this, it's just not built-in. There's nothing stopping you from doing:

      My::News->global_datasource_handle->{AutoCommit} = 0; eval { $news_article->save; $news_category->save; $news_author->save; foreach my $news_topic ( @topics ) { $news_topic->save; } }; if ( $@ ) { My::News->global_datasource_handle->rollback; } else { My::News->global_datasource_handle->commit; } My::News->global_datasource_handle->{AutoCommit} = 1;

      I also have a tickling idea for transactions across multiple datasources, but that is much further in the future.

      And SPOPS may take advantage of the DBI features you mentioned when they exist and are stable. The SQL abstraction stuff is well-factored so if we wanted to plop something else in there it wouldn't be too difficult.

      M-x auto-bs-mode