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 = connect_to_db(\%conf);
  88
  89 # cleanup seealso table first
  90 foreach $id (qw/id1 id2/) {
  91         $q = "SELECT id1,id2 FROM seealso LEFT JOIN song ON seealso.$id=song.artist_id".
  92                 " WHERE song.artist_id IS NULL";
  93         $sth = $dbh->prepare($q);
  94         $sth->execute();
  95
  96         while(($id1, $id2) = $sth->fetchrow_array) {
  97                 $dbh->do("DELETE FROM seealso WHERE id1=$id1 AND id2=$id2");
  98         }
  99         $sth->finish;
 100 }
 101
 102 # then cleanup unused artist entries
 103 $q = "SELECT artist.id FROM artist LEFT JOIN song ON artist.id=song.artist_id".
 104         " WHERE song.id IS NULL";
 105 $sth = $dbh->prepare($q);
 106 $sth->execute();
 107
 108 while(($id) = $sth->fetchrow_array) {
 109         $dbh->do("DELETE FROM artist WHERE id=$id");
 110 }
 111 $sth->finish;
 112
 113
 114 $ar1 = get_artist(1, "");
 115 $ar2 = get_artist(2, "(Artist1=$artistname{$ar1})\n");
 116
 117 print <<EOF;
 118 Adding SeeAlso entry for:
 119 - $artistname{$ar1}
 120 - $artistname{$ar2}
 121 EOF
 122
 123 # delete old entries
 124 $dbh->do("DELETE FROM seealso WHERE id1=$ar1 AND id2=$ar2") or die;
 125
 126 # add entry
 127 $dbh->do("REPLACE INTO seealso SET id1=$ar1, id2=$ar2") or die;
 128
 129 # delete other entry that means the same
 130 $dbh->do("DELETE FROM seealso WHERE id1=$ar2 AND id2=$ar1") or die;
 131
 132 $dbh->disconnect;
 133
 134