Index: SQL/mysql/schema_clear.sql =================================================================== --- SQL/mysql/schema_clear.sql (revision 21314) +++ SQL/mysql/schema_clear.sql (working copy) @@ -3,6 +3,8 @@ -- Use DELETE instead of TRUNCATE, as TRUNCATE seems to need unlocked tables. DELETE FROM tracks; +UPDATE tracks_metadata set track=null; + DELETE FROM playlist_track; DELETE FROM albums; Index: SQL/mysql/schema_6_up.sql =================================================================== --- SQL/mysql/schema_6_up.sql (revision 0) +++ SQL/mysql/schema_6_up.sql (revision 0) @@ -0,0 +1,25 @@ +-- table to store tracks metadata information, e.g. ratings and playcounts +DROP TABLE IF EXISTS tracks_metadata; +CREATE TABLE tracks_metadata ( + id int(10) unsigned NOT NULL auto_increment, + url text NOT NULL, + musicbrainz_id varchar(40), -- musicbrainz uuid (36 bytes of text) + track int(10) unsigned, + added int(10) unsigned, + rating tinyint(1) unsigned, + playCount int(10) unsigned, + lastPlayed int(10) unsigned, + INDEX trackMusicBrainzIndex (musicbrainz_id), + INDEX trackUrlIndex (url(255)), + INDEX trackAddedIndex (added), + INDEX trackRatingIndex (rating), + INDEX trackPlayCountIndex (playCount), + INDEX trackLastPlayedIndex (lastPlayed), + PRIMARY KEY (id), + UNIQUE KEY (track) +) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; + +ALTER TABLE tracks DROP rating; +ALTER TABLE tracks DROP playCount; +ALTER TABLE tracks DROP lastPlayed; + Index: SQL/mysql/schema_6_down.sql =================================================================== --- SQL/mysql/schema_6_down.sql (revision 0) +++ SQL/mysql/schema_6_down.sql (revision 0) @@ -0,0 +1,11 @@ +DROP TABLE IF EXISTS tracks_metadata; + +ALTER TABLE tracks ADD rating tinyint(1) unsigned AFTER drm; +ALTER TABLE tracks ADD INDEX trackRatingIndex (rating); + +ALTER TABLE tracks ADD playCount int(10) unsigned AFTER disc; +ALTER TABLE tracks ADD INDEX trackPlayCountIndex (playCount); + +ALTER TABLE tracks ADD lastPlayed int(10) unsigned AFTER playCount; +ALTER TABLE tracks ADD INDEX trackLastPlayedIndex (lastPlayed); + Index: Slim/Player/Source.pm =================================================================== --- Slim/Player/Source.pm (revision 21314) +++ Slim/Player/Source.pm (working copy) @@ -2227,9 +2227,9 @@ # XXXX - this really needs to happen in the caller! # No database access here. - dsully # keep track of some stats for this track - $track->set('playcount' => ($track->playcount() || 0) + 1); - $track->set('lastplayed' => time()); - $track->update(); + $track->metadata->set('playcount' => ($track->metadata->playcount() || 0) + 1); + $track->metadata->set('lastplayed' => time()); + $track->metadata->update(); Slim::Schema->forceCommit(); Index: Slim/Schema/Track.pm =================================================================== --- Slim/Schema/Track.pm (revision 21314) +++ Slim/Schema/Track.pm (working copy) @@ -21,7 +21,7 @@ timestamp filesize disc remote audio audio_size audio_offset year secs cover vbr_scale bitrate samplerate samplesize channels block_alignment endian bpm tagversion drm musicmagic_mixable - musicbrainz_id playcount lastplayed lossless lyrics rating replay_gain replay_peak + musicbrainz_id lossless lyrics replay_gain replay_peak )); { @@ -36,6 +36,7 @@ # setup our relationships $class->belongs_to('album' => 'Slim::Schema::Album'); + $class->might_have('metadata' => 'Slim::Schema::TrackMetadata' => 'track'); $class->has_many('genreTracks' => 'Slim::Schema::GenreTrack' => 'track'); $class->has_many('comments' => 'Slim::Schema::Comment' => 'track'); @@ -439,6 +440,33 @@ } } +sub playcount { + my ($self,$val) = @_; + if(defined($val)) { + $self->metadata->set('playcount' => $val); + $self->metadata->update(); + } + return $self->metadata->playcount; +} + +sub rating { + my ($self,$val) = @_; + if(defined($val)) { + $self->metadata->set('rating' => $val); + $self->metadata->update(); + } + return $self->metadata->rating; +} + +sub lastplayed { + my ($self,$val) = @_; + if(defined($val)) { + $self->metadata->set('lastplayed' => $val); + $self->metadata->update(); + } + return $self->metadata->lastplayed; +} + 1; __END__ Index: Slim/Schema/TrackMetadata.pm =================================================================== --- Slim/Schema/TrackMetadata.pm (revision 0) +++ Slim/Schema/TrackMetadata.pm (revision 0) @@ -0,0 +1,53 @@ +package Slim::Schema::TrackMetadata; + +# $Id:$ + +use strict; +use base 'Slim::Schema::DBI'; + +use Scalar::Util qw(blessed); + +use Slim::Utils::DateTime; +use Slim::Utils::Log; +use Slim::Utils::Misc; +use Slim::Utils::Prefs; + +my $prefs = preferences('server'); + +our @allColumns = (qw( + id url musicbrainz_id track added playcount lastplayed rating +)); + +{ + my $class = __PACKAGE__; + + $class->table('tracks_metadata'); + + $class->add_columns(@allColumns); + + $class->set_primary_key('id'); + + # setup our relationships + $class->belongs_to('track' => 'Slim::Schema::Track'); + + $class->resultset_class('Slim::Schema::ResultSet::TrackMetadata'); +} + +sub attributes { + my $class = shift; + + # Return a hash ref of column names + return { map { $_ => 1 } @allColumns }; +} + +sub addedTime { + my $self = shift; + + my $time = $self->added; + + return join(', ', Slim::Utils::DateTime::longDateF($time), Slim::Utils::DateTime::timeF($time)); +} + +1; + +__END__ Index: Slim/Schema/ResultSet/TrackMetadata.pm =================================================================== --- Slim/Schema/ResultSet/TrackMetadata.pm (revision 0) +++ Slim/Schema/ResultSet/TrackMetadata.pm (revision 0) @@ -0,0 +1,14 @@ +package Slim::Schema::ResultSet::TrackMetadata; + +# $Id:$ + +use strict; +use base qw(Slim::Schema::ResultSet::Base); + +use Slim::Utils::Prefs; + +# XXX - These are wrappers around the methods in Slim::Schema, which need to +# be moved here. This is the proper API, and we want to have people using this +# now, and we can migrate the code underneath later. + +1; Index: Slim/Menu/TrackInfo.pm =================================================================== --- Slim/Menu/TrackInfo.pm (revision 21314) +++ Slim/Menu/TrackInfo.pm (working copy) @@ -1035,7 +1035,7 @@ my $item; - if ( my $rating = $track->rating ) { + if ( my $rating = $track->metadata->rating ) { $item = { type => 'text', name => cstring($client, 'RATING') . cstring($client, 'COLON') . ' ' . sprintf( "%d", $rating ) . ' /100', Index: Slim/Schema.pm =================================================================== --- Slim/Schema.pm (revision 21314) +++ Slim/Schema.pm (working copy) @@ -81,6 +81,7 @@ our $initialized = 0; my $trackAttrs = {}; +my $trackMetadataAttrs = {}; =head1 METHODS @@ -175,12 +176,13 @@ PlaylistTrack Rescan Track + TrackMetadata Year Progress /); # Build all our class accessors and populate them. - for my $accessor (qw(lastTrackURL lastTrack trackAttrs driver schemaUpdated)) { + for my $accessor (qw(lastTrackURL lastTrack trackAttrs trackMetadataAttrs driver schemaUpdated)) { $class->mk_classaccessor($accessor); } @@ -191,6 +193,7 @@ } $trackAttrs = Slim::Schema::Track->attributes; + $trackMetadataAttrs = Slim::Schema::TrackMetadata->attributes; $class->driver($driver); # Use our debug and stats class to get logging and perfmon for db queries @@ -840,6 +843,7 @@ # Creating the track only wants lower case values from valid columns. my $columnValueHash = {}; + my $metadataColumnValueHash = {}; # Walk our list of valid attributes, and turn them into something ->create() can use. $log->debug("Creating $source with columns:"); @@ -859,6 +863,18 @@ $columnValueHash->{$key} = $val; } + + # Metadata is only included if it contains a non zero value + if(defined $val && $val && exists $trackMetadataAttrs->{$key}) { + # Bug 7731, filter out duplicate keys that end up as array refs + if ( ref $val eq 'ARRAY' ) { + $val = $val->[0]; + } + + $log->debug(" metadata $key : $val"); + + $metadataColumnValueHash->{$key} = $val; + } } # Tag and rename set URL to the Amazon image path. Smack that. @@ -883,6 +899,41 @@ $log->info(sprintf("Created track '%s' (id: [%d])", $track->title, $track->id)); } + if($track->audio) { + # Pull the track metadata object for the DB + my $trackMetadata = $self->_retrieveTrackMetadata($url,$track->musicbrainz_id); + + # We only want to store real musicbrainz_id's (conversion programs sometimes generates non valid musicbrainz_id's during conversion) + if(exists $metadataColumnValueHash->{'musicbrainz_id'} && length($metadataColumnValueHash->{'musicbrainz_id'})!=36) { + delete $metadataColumnValueHash->{'musicbrainz_id'}; + } + + # _retrieveTrackMetadata will always return undef or a track metadata object + if (!blessed($trackMetadata)) { + $metadataColumnValueHash->{'added'} = $track->timestamp; + $metadataColumnValueHash->{'track'} = $track->id; + $metadataColumnValueHash->{'url'} = $track->url; + + # Create the track metadata object- or bail. ->throw_exception will emit a backtrace. + $trackMetadata = Slim::Schema->resultset('TrackMetadata')->create($metadataColumnValueHash); + + if ($@ || !blessed($trackMetadata)) { + + logError("Failed creating TrackMetadata for $url : $@"); + return; + } + }else { + while (my ($key, $val) = each %$metadataColumnValueHash) { + + $log->info("Updating metadata $url : $key to $val"); + $trackMetadata->set_column($key, $val); + } + $trackMetadata->set_column('track',$track->id); + $trackMetadata->set_column('url',$track->url); + $trackMetadata->update(); + } + } + # Now that we've created the track, and possibly an album object - # update genres, etc - that we need the track ID for. if (!$playlist) { @@ -989,6 +1040,14 @@ return $track; } + # Pull the track metadata object for the DB + my $trackMetadata = $self->_retrieveTrackMetadata($url,$track->musicbrainz_id); + + # _retrieveTrackMetadata will always return undef or a track metadata object + if (!blessed($trackMetadata)) { + $trackMetadata = undef; + } + # Bug: 2335 - readTags is set in Slim::Formats::Playlists::CUE - when # we create/update a cue sheet to have a CT of 'cur' if (defined $attributeHash->{'CONTENT_TYPE'} && $attributeHash->{'CONTENT_TYPE'} eq 'cur') { @@ -1020,6 +1079,14 @@ $track->set_column($key, $val); } + + # Metadata is only included if it contains a non zero value + if (defined $trackMetadata && defined $val && $val && exists $trackMetadataAttrs->{$key}) { + + $log->info("Updating metadata $url : $key to $val"); + + $trackMetadata->set_column($key, $val); + } } # _postCheckAttributes does an update @@ -1485,6 +1552,26 @@ return undef; } +sub _retrieveTrackMetadata { + my ($self, $url, $musicbrainz_id) = @_; + + return undef if !$url; + return undef if ref($url); + + my $trackMetadata; + + $trackMetadata = $self->resultset('TrackMetadata')->single({ 'url' => $url }); + + if (blessed($trackMetadata)) { + return $trackMetadata; + }elsif($musicbrainz_id) { + $trackMetadata = $self->resultset('TrackMetadata')->single({ 'musicbrainz_id' => $musicbrainz_id }); + return $trackMetadata if blessed($trackMetadata); + } + + return undef; +} + sub _checkValidity { my $self = shift; my $track = shift; Index: Slim/Web/Pages/History.pm =================================================================== --- Slim/Web/Pages/History.pm (revision 21314) +++ Slim/Web/Pages/History.pm (working copy) @@ -26,13 +26,13 @@ # Fetch 50 tracks that have been played at least once. # Limit is hardcoded for now.. my $rs = Slim::Schema->search('Track', - { 'playcount' => { '>' => 0 } }, - { 'order_by' => 'me.playcount desc' }, + { 'metadata.playcount' => { '>' => 0 } }, + { 'join' => qw(metadata), 'order_by' => 'metadata.playcount desc' }, )->slice(0, 49); while (my $track = $rs->next) { - my $playCount = $track->playcount; + my $playCount = $track->metadata->playcount; if ($maxPlayed == 0) { $maxPlayed = $playCount;