01 #!/usr/local/bin/perl -w
02 use strict;
03 use Log::Log4perl qw(:easy);
04 Log::Log4perl->easy_init($DEBUG);
05 
06 use DBI qw(:sql_types);
07 use DBD::SQLite;
08 use Data::Dumper;
09 use YAML qw(LoadFile DumpFile);
10 use Getopt::Std;
11 
12 getopts( "r", \my %opts );
13 
14 my $db  = glob 
15           "~/.config/banshee-1/banshee.db";
16 my $dbh = DBI->connect( "dbi:SQLite:$db", 
17     "", "", { RaiseError => 1, 
18               AutoCommit => 1 });
19 
20 my $yml     = "banshee-ratings.yml";
21 my %ratings = ();
22 
23 if( $opts{ r } ) {
24     restore( $yml, $dbh );
25 } else {
26     backup( $dbh, $yml );
27 }
28 
29 $dbh->disconnect();
30 
31 ###########################################
32 sub backup {
33 ###########################################
34   my( $dbh, $yml ) = @_;
35 
36   my %ratings = ();
37 
38   my $sth = $dbh->prepare( 
39       "SELECT * FROM CoreTracks" );
40   $sth->execute();
41 
42   while( my $hash_ref = 
43            $sth->fetchrow_hashref() ) {
44       next if $hash_ref->{ Rating } == 0;
45 
46       $ratings{ $hash_ref->{ Uri } } = 
47         $hash_ref->{ Rating };
48   }
49 
50   DumpFile( $yml, \%ratings );
51 
52   $sth->finish();
53 }
54 
55 ###########################################
56 sub restore {
57 ###########################################
58   my( $yml, $dbh ) = @_;
59 
60   my $ratings = LoadFile( $yml );
61 
62   for my $song ( keys %$ratings ) {
63       DEBUG "Restoring $song";
64 
65       my $rating = $ratings->{ $song };
66 
67       my $sth = $dbh->prepare(
68        "UPDATE CoreTracks SET Rating = ?" .
69        "WHERE Uri = ?" );
70       $sth->execute( $rating, $song );
71       $sth->finish();
72   }
73 }