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.
jeffa
L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
F--F--F--F--F--F--F--F--
(the triplet paradiddle)
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;
| [reply] [Watch: Dir/Any] [d/l] |
|
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__->set_up_table('Song');
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 ...
Tony | [reply] [Watch: Dir/Any] |
|
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 :-)
Chris
M-x auto-bs-mode | [reply] [Watch: Dir/Any] [d/l] |
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.
| [reply] [Watch: Dir/Any] [d/l] |
|
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. | [reply] [Watch: Dir/Any] [d/l] |
|
| [reply] [Watch: Dir/Any] |
|
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
| [reply] [Watch: Dir/Any] |
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 -----
| [reply] [Watch: Dir/Any] [d/l] |
|
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.
Chris
M-x auto-bs-mode | [reply] [Watch: Dir/Any] [d/l] |
|
|