mdw@git.distorted.org.uk Git - dvddb/blob - upgrade-v3

   1 #! /usr/bin/perl -w
   2
   3 use open ":utf8";
   4 use strict;
   5
   6 use DBI;
   7
   8 BEGIN { binmode STDOUT, ":utf8"; }
   9
  10 my $DB = DBI->connect("dbi:Pg:host=roadstar", "", "",
  11                       { AutoCommit => 0, RaiseError => 1 });
  12
  13 my $st_outer = $DB->prepare
  14   ("SELECT title, COUNT(*) FROM old_dvd GROUP BY title");
  15 my $st_inner = $DB->prepare
  16   ("SELECT disc, path, discid FROM old_dvd WHERE title = ?");
  17 my $st_add_set = $DB->prepare
  18   ("INSERT INTO dvd_set (name, ndisc) VALUES (?, ?) RETURNING id");
  19 my $st_add_disc = $DB->prepare
  20   ("INSERT INTO dvd_disc (set_id, disc, path, disc_id) VALUES (?, ?, ?, ?)");
  21 $st_outer->execute;
  22
  23 OUTER: for (;;) {
  24   my @r = $st_outer->fetchrow_array; last OUTER unless @r;
  25   my ($title, $ndisc) = @r;
  26
  27   $st_add_set->execute($title, $ndisc);
  28   my ($set_id) = $st_add_set->fetchrow_array;
  29
  30   $st_inner->execute($title);
  31   INNER: for (;;) {
  32     my @r = $st_inner->fetchrow_array; last INNER unless @r;
  33     my ($disc, $path, $disc_id) = @r;
  34     $st_add_disc->execute($set_id, $disc, $path, $disc_id);
  35   }
  36 }
  37 $DB->commit; $DB->disconnect;