soepkiptng_add_seealso

   1 #!/usr/bin/perl
   2 ############################################################################
   3 # soepkiptng (c) copyright 2000 Eric Lammerts <eric@lammerts.org>.
   4 ############################################################################
   5 # This program is free software; you can redistribute it and/or modify
   6 # it under the terms of the GNU General Public License, version 2, as
   7 # published by the Free Software Foundation.
   8 #
   9 # This program is distributed in the hope that it will be useful,
  10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12 # GNU General Public License for more details.
  13 #
  14 # A copy of the GNU General Public License is available on the World Wide Web
  15 # at `http://www.gnu.org/copyleft/gpl.html'.  You can also obtain it by
  16 # writing to the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
  17 # Boston, MA 02111-1307, USA.
  18 ############################################################################
  19
  20 use integer;
  21 use Cwd 'abs_path';
  22 use DBI;
  23 use Getopt::Std;
  24
  25 # find program directory
  26 $_ = $0;
  27 while(-l) {
  28         my $l = readlink or die "readlink $_: $!\n";
  29         if($l =~ m|^/|) { $_ = $l; } else { s|[^/]*$|/$l|; }
  30 }
  31 m|(.*)/|;
  32 my $progdir = abs_path($1);
  33
  34 require "$progdir/soepkiptng.lib";
  35
  36 getopts('c:');
  37
  38 read_configfile(\%conf, $opt_c);
  39
  40 ############################################################################
  41
  42
  43 sub get_artist($$) {
  44         my ($num, $preprompt) = @_;
  45         my $arid;
  46
  47         until($arid) {
  48                 print "\n${preprompt}Enter artist$num search terms [$input]: ";
  49                 $newinput = <STDIN>;
  50                 $newinput =~ s/(.\10|\s+$)//g;
  51                 $newinput =~ /\S/ and $input = $newinput;
  52                 my @a = split(/\s+/, $input);
  53                 scalar @a > 0 or next;
  54
  55                 my $q = "SELECT artist.id,artist.name FROM artist" .
  56                         " LEFT JOIN song ON song.artist_id=artist.id WHERE song.present" .
  57                         join("", map { " AND artist.name LIKE ?" } @a) .
  58                         " GROUP BY artist.id,artist.name ORDER BY artist.name";
  59                 my @q = map { "%$_%" } @a;
  60
  61                 $sth = $dbh->prepare($q);
  62                 $sth->execute(@q);
  63
  64                 my ($id, $name);
  65                 my $i = 0;
  66                 my @artistid;
  67                 while(($id, $name) = $sth->fetchrow_array) {
  68                         $i++;
  69                         print "$i) $name\n";
  70                         $artistid[$i] = $id;
  71                         $artistname{$id} = $name;
  72                 }
  73                 $i or do {
  74                         print "No matches.\n";
  75                         next;
  76                 };
  77
  78                 my $chosen;
  79                 print "Select one of the above [1..$i] or nothing to repeat search: ";
  80                 $chosen = <STDIN>;
  81                 $arid = $artistid[$chosen];
  82         }
  83 }
  84
  85
  86 $| = 1;
  87 $dbh = DBI->connect("DBI:$conf{db_type}:$conf{db_name}:$conf{db_host}",
  88         $conf{db_user}, $conf{db_pass}) or die "can't connect to database";
  89
  90 # cleanup seealso table first
  91 foreach $id (qw/id1 id2/) {
  92         $q = "SELECT id1,id2 FROM seealso LEFT JOIN song ON seealso.$id=song.artist_id".
  93                 " WHERE song.artist_id IS NULL";
  94         $sth = $dbh->prepare($q);
  95         $sth->execute();
  96
  97         while(($id1, $id2) = $sth->fetchrow_array) {
  98                 $dbh->do("DELETE FROM seealso WHERE id1=$id1 AND id2=$id2");
  99         }
 100         $sth->finish;
 101 }
 102
 103 # then cleanup unused artist entries
 104 $q = "SELECT artist.id FROM artist LEFT JOIN song ON artist.id=song.artist_id".
 105         " WHERE song.id IS NULL";
 106 $sth = $dbh->prepare($q);
 107 $sth->execute();
 108
 109 while(($id) = $sth->fetchrow_array) {
 110         $dbh->do("DELETE FROM artist WHERE id=$id");
 111 }
 112 $sth->finish;
 113
 114
 115 $ar1 = get_artist(1, "");
 116 $ar2 = get_artist(2, "(Artist1=$artistname{$ar1})\n");
 117
 118 print <<EOF;
 119 Adding SeeAlso entry for:
 120 - $artistname{$ar1}
 121 - $artistname{$ar2}
 122 EOF
 123
 124 # delete old entries
 125 $dbh->do("DELETE FROM seealso WHERE id1=$ar1 AND id2=$ar2") or die;
 126
 127 # add entry
 128 $dbh->do("REPLACE INTO seealso SET id1=$ar1, id2=$ar2") or die;
 129
 130 # delete other entry that means the same
 131 $dbh->do("DELETE FROM seealso WHERE id1=$ar2 AND id2=$ar1") or die;
 132
 133 $dbh->disconnect;
 134
 135