X-Git-Url: https://git.distorted.org.uk/~mdw/dvddb/blobdiff_plain/75a5b9248f89fd40189035093b751a7bbcd0292d..b2a25885cb81761a2b33014a00f843e76f4ce83c:/cleanup

diff --git a/cleanup b/cleanup
new file mode 100755
index 0000000..7720ce0
--- /dev/null
+++ b/cleanup
@@ -0,0 +1,62 @@
+#! /usr/bin/perl -w
+
+use autodie qw{:all};
+use open ":utf8";
+use strict;
+
+use DBI;
+use Encode qw{encode_utf8 decode_utf8};
+use Getopt::Std;
+
+BEGIN {
+  binmode STDIN, ":utf8";
+  binmode STDOUT, ":utf8";
+  binmode STDERR, ":utf8";
+}
+
+(my $prog = $0) =~ s:^.*/::;
+sub HELP_MESSAGE ($;@) {
+  my ($fh) = @_;
+  print $fh "usage: $prog [-f]\n";
+}
+
+my $bogusp = 0;
+my %opt;
+getopts("hf", \%opt) or $bogusp = 1;
+if ($opt{"h"}) { HELP_MESSAGE \*STDOUT; exit 0; }
+@ARGV == 0 or $bogusp = 1;
+if ($bogusp) { HELP_MESSAGE \*STDERR; exit 2; }
+
+my $DB = DBI->connect("dbi:Pg:host=roadstar", "", "",
+		      { AutoCommit => 0,
+			RaiseError => 1 });
+
+for my $item (["media", "id", "title",
+	       "playlist_entry", "media_id", "list_name"],
+	      ["series", "name", "name",
+	       "media", "series_name", "id"],
+	      ["playlist", "name", "name",
+	       "playlist_entry", "list_name", "entry"],
+	      ["dvd_set", "id", "name",
+	       "dvd_disc", "set_id", "disc"]) {
+  my ($table, $key, $name, $reftable, $refcol, $testcol) = @$item;
+  my $st = $DB->prepare
+    ("SELECT t.$name FROM $table AS t
+      LEFT JOIN $reftable AS r ON r.$refcol = t.$key
+      WHERE r.$testcol IS NULL");
+  $st->execute;
+  my $any = 0;
+  while (my @r = $st->fetchrow_array)
+    { my ($rowname) = @r; print "$table: $rowname\n"; $any = 1; }
+
+  if ($any && $opt{"f"}) {
+    my $n = $DB->do
+      ("DELETE FROM $table WHERE $key IN
+		(SELECT t.$key FROM $table AS t
+		 LEFT JOIN $reftable AS r ON r.$refcol = t.$key
+		 WHERE r.$testcol IS NULL)");
+    print "$table: deleted $n\n";
+  }
+}
+
+$DB->commit; $DB->disconnect;