#! /usr/bin/perl -w

use open ":utf8";
use strict;

use DBI;

BEGIN { binmode STDOUT, ":utf8"; }

my $DB = DBI->connect("dbi:Pg:host=roadstar", "", "",
		      { AutoCommit => 0, RaiseError => 1 });

my $st_outer = $DB->prepare
  ("SELECT title, COUNT(*) FROM old_dvd GROUP BY title");
my $st_inner = $DB->prepare
  ("SELECT disc, path, discid FROM old_dvd WHERE title = ?");
my $st_add_set = $DB->prepare
  ("INSERT INTO dvd_set (name, ndisc) VALUES (?, ?) RETURNING id");
my $st_add_disc = $DB->prepare
  ("INSERT INTO dvd_disc (set_id, disc, path, disc_id) VALUES (?, ?, ?, ?)");
$st_outer->execute;

OUTER: for (;;) {
  my @r = $st_outer->fetchrow_array; last OUTER unless @r;
  my ($title, $ndisc) = @r;

  $st_add_set->execute($title, $ndisc);
  my ($set_id) = $st_add_set->fetchrow_array;

  $st_inner->execute($title);
  INNER: for (;;) {
    my @r = $st_inner->fetchrow_array; last INNER unless @r;
    my ($disc, $path, $disc_id) = @r;
    $st_add_disc->execute($set_id, $disc, $path, $disc_id);
  }
}
$DB->commit; $DB->disconnect;