| 1 | #!/usr/local/bin/perl | ||
| 2 | |||
| 3 | use strict; | ||
| 4 | use warnings; | ||
| 5 | |||
| 6 | use DBI; | ||
| 7 | use YAML qw(LoadFile); | ||
| 8 | use Data::HandyGen::mysql::TableDef; | ||
| 9 | use Getopt::Long; | ||
| 10 | use SQL::Maker; | ||
| 11 | |||
| 12 | main(); | ||
| 13 | exit(0); | ||
| 14 | |||
| 15 | |||
| 16 | sub main { | ||
| 17 | my $infile; | ||
| 18 | my $noutf8 = 0; | ||
| 19 | my $verbose = 0; | ||
| 20 | my ($dbname, $host, $port, $user, $password); | ||
| 21 | GetOptions( | ||
| 22 | 'i|in|infile=s' => \$infile, | ||
| 23 | 'd|dbname=s' => \$dbname, | ||
| 24 | 'h|host=s' => \$host, | ||
| 25 | 'port=i' => \$port, | ||
| 26 | 'u|user=s' => \$user, | ||
| 27 | 'p|password=s' => \$password, | ||
| 28 | 'noutf8' => \$noutf8, | ||
| 29 | 'v|verbose' => \$verbose, | ||
| 30 | ); | ||
| 31 | |||
| 32 | $infile and $dbname or usage(); | ||
| 33 | |||
| 34 | my $dsn = "dbi:mysql:dbname=$dbname"; | ||
| 35 | $host and $dsn .= ";host=$host"; | ||
| 36 | $port and $dsn .= ";port=$port"; | ||
| 37 | |||
| 38 | my $dbh = DBI->connect($dsn, $user, $password, { RaiseError => 1, AutoCommit => 0 }) | ||
| 39 | or die $DBI::errstr; | ||
| 40 | $dbh->do("SET NAMES UTF8") unless $noutf8; | ||
| 41 | |||
| 42 | my $inserted = LoadFile($infile); | ||
| 43 | |||
| 44 | |||
| 45 | if ( ref $inserted eq 'HASH' ) { | ||
| 46 | eval { | ||
| 47 | |||
| 48 | my $builder = SQL::Maker->new( driver => 'mysql' ); | ||
| 49 | |||
| 50 | $dbh->do(q{SET FOREIGN_KEY_CHECKS = 0}); | ||
| 51 | for my $table ( keys %$inserted ) { | ||
| 52 | my $ids = $inserted->{$table}; | ||
| 53 | my $table_def = Data::HandyGen::mysql::TableDef->new( dbh => $dbh, table_name => $table ); | ||
| 54 | my $pk_column = $table_def->pk_columns()->[0]; | ||
| 55 | |||
| 56 | for my $id (@$ids) { | ||
| 57 | my ($sql, @binds) = $builder->delete($table, { $pk_column => $id }); | ||
| 58 | my $sth = $dbh->prepare($sql); | ||
| 59 | my $numrow = $sth->execute(@binds); | ||
| 60 | if ( $numrow >= 1 ) { | ||
| 61 | print "Deleted $table ($id)\n" if $verbose; | ||
| 62 | } | ||
| 63 | else { | ||
| 64 | print "No row affected. $table ($id)\n" if $verbose; | ||
| 65 | } | ||
| 66 | } | ||
| 67 | } | ||
| 68 | $dbh->do(q{SET FOREIGN_KEY_CHECKS = 1}); | ||
| 69 | |||
| 70 | }; | ||
| 71 | if ($@) { | ||
| 72 | $dbh->rollback(); | ||
| 73 | } | ||
| 74 | else { | ||
| 75 | $dbh->commit(); | ||
| 76 | } | ||
| 77 | |||
| 78 | } | ||
| 79 | else { | ||
| 80 | die "Invalid format : $infile\n"; | ||
| 81 | } | ||
| 82 | |||
| 83 | $dbh->disconnect(); | ||
| 84 | |||
| 85 | } | ||
| 86 | |||
| 87 | |||
| 88 | sub usage { | ||
| 89 | print <<USAGE; | ||
| 90 | Options: | ||
| 91 | -i(--in,--infile) : input file (YAML) | ||
| 92 | -d(--dbname) : database name | ||
| 93 | -h(--host) : host | ||
| 94 | --port : port no | ||
| 95 | -u(--user) : username | ||
| 96 | -p(--password) : password | ||
| 97 | |||
| 98 | USAGE | ||
| 99 | |||
| 100 | exit(-1); | ||
| 101 | } | ||
| 102 | |||
| 103 | |||
| 104 | __END__ |