111 lines
3.1 KiB
Plaintext
111 lines
3.1 KiB
Plaintext
|
#!/usr/bin/env perl
|
||
|
|
#===============================================================================
|
||
|
|
#
|
||
|
|
# FILE: remodel_db_series_eps
|
||
|
|
#
|
||
|
|
# USAGE: ./remodel_db_series _eps
|
||
|
|
#
|
||
|
|
# DESCRIPTION: Script to perform the steps necessary to remodel the series
|
||
|
|
# information in the hpr_hpr database. We want to have
|
||
|
|
# a many-to-many relationship between episodes and series.
|
||
|
|
#
|
||
|
|
# OPTIONS: ---
|
||
|
|
# REQUIREMENTS: ---
|
||
|
|
# BUGS: ---
|
||
|
|
# NOTES: This code is extremely trivial. It could be done just as well
|
||
|
|
# with plain SQL. However, having a script will potentially
|
||
|
|
# allow other things to be done during the table load, such as
|
||
|
|
# reading other series associations from a file.
|
||
|
|
# AUTHOR: Dave Morriss (djm), Dave.Morriss@gmail.com
|
||
|
|
# VERSION: 0.0.1
|
||
|
|
# CREATED: 2015-06-26 12:30:27
|
||
|
|
# REVISION: 2015-06-26 15:28:12
|
||
|
|
#
|
||
|
|
#===============================================================================
|
||
|
|
|
||
|
|
use 5.010;
|
||
|
|
use strict;
|
||
|
|
use warnings;
|
||
|
|
use utf8;
|
||
|
|
|
||
|
|
use YAML::Syck;
|
||
|
|
use DBI;
|
||
|
|
|
||
|
|
#
|
||
|
|
# Version number (manually incremented)
|
||
|
|
#
|
||
|
|
our $VERSION = '0.0.1';
|
||
|
|
|
||
|
|
#
|
||
|
|
# Script and directory names
|
||
|
|
#
|
||
|
|
( my $PROG = $0 ) =~ s|.*/||mx;
|
||
|
|
( my $DIR = $0 ) =~ s|/?[^/]*$||mx;
|
||
|
|
$DIR = '.' unless $DIR;
|
||
|
|
|
||
|
|
#-------------------------------------------------------------------------------
|
||
|
|
# Declarations
|
||
|
|
#-------------------------------------------------------------------------------
|
||
|
|
#
|
||
|
|
# Constants and other declarations
|
||
|
|
#
|
||
|
|
my $basedir = "$ENV{HOME}/HPR/Database";
|
||
|
|
my $configfile = "$basedir/.hpr_db.yml";
|
||
|
|
|
||
|
|
my ( $dbh, $sth1, $sth2, $sth3, $sth4, $h1, $h2, $rv );
|
||
|
|
|
||
|
|
#
|
||
|
|
# Enable Unicode mode
|
||
|
|
#
|
||
|
|
binmode STDOUT, ":encoding(UTF-8)";
|
||
|
|
binmode STDERR, ":encoding(UTF-8)";
|
||
|
|
|
||
|
|
#
|
||
|
|
# Load configuration data
|
||
|
|
#
|
||
|
|
my %config = %{ LoadFile($configfile) };
|
||
|
|
|
||
|
|
#-------------------------------------------------------------------------------
|
||
|
|
# Connect to the database
|
||
|
|
#-------------------------------------------------------------------------------
|
||
|
|
my $dbname = $config{database}->{name};
|
||
|
|
my $dbuser = $config{database}->{user};
|
||
|
|
my $dbpwd = $config{database}->{password};
|
||
|
|
$dbh
|
||
|
|
= DBI->connect( "dbi:mysql:dbname=$dbname", $dbuser, $dbpwd,
|
||
|
|
{ AutoCommit => 1 } )
|
||
|
|
or die $DBI::errstr;
|
||
|
|
|
||
|
|
#
|
||
|
|
# Enable client-side UTF8
|
||
|
|
#
|
||
|
|
$dbh->{mysql_enable_utf8} = 1;
|
||
|
|
|
||
|
|
#-------------------------------------------------------------------------------
|
||
|
|
# Set up the SQL and query the 'eps' table since everything has a series, even
|
||
|
|
# if it's 0
|
||
|
|
#-------------------------------------------------------------------------------
|
||
|
|
$sth1 = $dbh->prepare(q{SELECT * FROM eps ORDER BY id DESC});
|
||
|
|
$sth2 = $dbh->prepare(q{INSERT INTO series_eps VALUES(?,?)});
|
||
|
|
|
||
|
|
$sth1->execute;
|
||
|
|
if ( $dbh->err ) {
|
||
|
|
die $dbh->errstr;
|
||
|
|
}
|
||
|
|
|
||
|
|
#-------------------------------------------------------------------------------
|
||
|
|
# Walk the entire 'eps' table simply adding rows into the 'series_eps' table
|
||
|
|
#-------------------------------------------------------------------------------
|
||
|
|
while ( $h1 = $sth1->fetchrow_hashref ) {
|
||
|
|
$sth2->execute($h1->{series},$h1->{id});
|
||
|
|
if ( $dbh->err ) {
|
||
|
|
die $dbh->errstr;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
exit;
|
||
|
|
|
||
|
|
# vim: syntax=perl:ts=8:sw=4:et:ai:tw=78:fo=tcrqn21:fdm=marker
|
||
|
|
|
||
|
|
|