| 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; |