| 1 | #!/usr/local/bin/perl | ||
| 2 | |||
| 3 | use strict; | ||
| 4 | use warnings; | ||
| 5 | |||
| 6 | use Data::HandyGen::mysql; | ||
| 7 | use DBI; | ||
| 8 | use JSON qw(decode_json); | ||
| 9 | use YAML; | ||
| 10 | use Getopt::Long; | ||
| 11 | |||
| 12 | my %ids = (); | ||
| 13 | my $req; | ||
| 14 | |||
| 15 | main(); | ||
| 16 | exit(0); | ||
| 17 | |||
| 18 | =head1 NAME | ||
| 19 | |||
| 20 | hd_insert_bulk.pl - Inserts records into mysql tables, using Data::HandyGen. | ||
| 21 | |||
| 22 | |||
| 23 | =head1 VERSION | ||
| 24 | |||
| 25 | This documentation refers to hd_insert_bulk.pl 0.0.5 | ||
| 26 | |||
| 27 | =cut | ||
| 28 | |||
| 29 | sub main { | ||
| 30 | my $infile; | ||
| 31 | my $debug = 0; | ||
| 32 | my $noutf8 = 0; | ||
| 33 | my ($dbname, $host, $port, $user, $password); | ||
| 34 | my ($target_table, $number, $required_fields); | ||
| 35 | GetOptions( | ||
| 36 | 'i|in|infile=s' => \$infile, | ||
| 37 | 'd|dbname=s' => \$dbname, | ||
| 38 | 'h|host=s' => \$host, | ||
| 39 | 'port=i' => \$port, | ||
| 40 | 'u|user=s' => \$user, | ||
| 41 | 'p|password=s' => \$password, | ||
| 42 | 'debug' => \$debug, | ||
| 43 | 'noutf8' => \$noutf8, | ||
| 44 | 't|table=s' => \$target_table, | ||
| 45 | 'n|number=i' => \$number, | ||
| 46 | 'required=s' => \$required_fields, | ||
| 47 | ); | ||
| 48 | |||
| 49 | ($infile) or ($target_table and $number) or usage(); | ||
| 50 | |||
| 51 | my $dsn = "dbi:mysql:dbname=$dbname"; | ||
| 52 | $host and $dsn .= ";host=$host"; | ||
| 53 | $port and $dsn .= ";port=$port"; | ||
| 54 | |||
| 55 | my $dbh = DBI->connect($dsn, $user, $password, { RaiseError => 1, AutoCommit => 0 }) | ||
| 56 | or die $DBI::errstr; | ||
| 57 | $dbh->do("SET NAMES UTF8") unless $noutf8; | ||
| 58 | |||
| 59 | my $hd = Data::HandyGen::mysql->new( dbh => $dbh, fk => 1, debug => $debug ); | ||
| 60 | |||
| 61 | eval { | ||
| 62 | if ( $infile ) { | ||
| 63 | insert_from_file($hd, $infile); | ||
| 64 | } | ||
| 65 | else { | ||
| 66 | insert_to_table($hd, $target_table, $number, $required_fields); | ||
| 67 | } | ||
| 68 | }; | ||
| 69 | if ($@) { | ||
| 70 | $hd->dbh->rollback; | ||
| 71 | die "Failed to insert : $@"; | ||
| 72 | } | ||
| 73 | else { | ||
| 74 | $hd->dbh->commit; | ||
| 75 | } | ||
| 76 | |||
| 77 | print YAML::Dump($hd->inserted); | ||
| 78 | } | ||
| 79 | |||
| 80 | |||
| 81 | |||
| 82 | sub insert_from_file { | ||
| 83 | my ($hd, $infile) = @_; | ||
| 84 | |||
| 85 | open my $JSON, '<', $infile | ||
| 86 | or die "Failed to open infile : $infile : $!"; | ||
| 87 | my $json = do { local $/; <$JSON> }; | ||
| 88 | close $JSON; | ||
| 89 | |||
| 90 | $req = decode_json($json); | ||
| 91 | |||
| 92 | for my $table (keys %$req) { | ||
| 93 | |||
| 94 | my $list = $req->{$table}; | ||
| 95 | |||
| 96 | # When arrayref is passed instead of hashref, IDs in the arrayref will be assigned to each elements. | ||
| 97 | if ( ref $list eq 'ARRAY' ) { | ||
| 98 | $list = {}; | ||
| 99 | my $no = 1; | ||
| 100 | for ( @{ $req->{$table} } ) { | ||
| 101 | $list->{$no++} = $_; | ||
| 102 | } | ||
| 103 | } | ||
| 104 | |||
| 105 | for my $id ( keys %$list ) { | ||
| 106 | next if $ids{$table}{$id}; | ||
| 107 | my $real_id = insert($hd, $table, $list->{$id}); | ||
| 108 | $ids{$table}{$id} = $real_id; | ||
| 109 | } | ||
| 110 | } | ||
| 111 | } | ||
| 112 | |||
| 113 | |||
| 114 | sub insert_to_table { | ||
| 115 | my ($hd, $table, $num, $required) = @_; | ||
| 116 | |||
| 117 | my %user_val = (); | ||
| 118 | for ( split ',', $required ) { | ||
| 119 | $user_val{$_} = \'any'; | ||
| 120 | } | ||
| 121 | |||
| 122 | for ( 1 .. $num ) { | ||
| 123 | $hd->insert($table, \%user_val); | ||
| 124 | } | ||
| 125 | } | ||
| 126 | |||
| 127 | |||
| 128 | sub insert { | ||
| 129 | my ($hd, $table, $rec) = @_; | ||
| 130 | |||
| 131 | my %user_val = (); | ||
| 132 | for my $col ( keys %$rec ) { | ||
| 133 | |||
| 134 | if ( $rec->{$col} =~ /^##(\w+)\.(\d+)$/ ) { | ||
| 135 | my $ref_table = $1; | ||
| 136 | my $ref_id = $2; | ||
| 137 | unless ( $ids{$ref_table}{$ref_id} ) { | ||
| 138 | |||
| 139 | ref($req->{$ref_table}) eq 'HASH' | ||
| 140 | or die "Invalid ID reference. table = $ref_table, ID = $ref_id"; | ||
| 141 | |||
| 142 | my $real_id = insert($hd, $ref_table, $req->{$ref_table}{$ref_id}); | ||
| 143 | $ids{$ref_table}{$ref_id} = $real_id; | ||
| 144 | } | ||
| 145 | $user_val{$col} = $ids{$ref_table}{$ref_id}; | ||
| 146 | } | ||
| 147 | else { | ||
| 148 | $user_val{$col} = $rec->{$col}; | ||
| 149 | } | ||
| 150 | } | ||
| 151 | |||
| 152 | my $id = $hd->insert($table, \%user_val); | ||
| 153 | |||
| 154 | return $id; | ||
| 155 | } | ||
| 156 | |||
| 157 | |||
| 158 | |||
| 159 | |||
| 160 | sub usage { | ||
| 161 | print STDERR <<USAGE; | ||
| 162 | Options: | ||
| 163 | -d(--dbname) : database name | ||
| 164 | -h(--host) : host | ||
| 165 | --port : port no | ||
| 166 | -u(--user) : username | ||
| 167 | -p(--password) : password | ||
| 168 | |||
| 169 | Options reading config from file: | ||
| 170 | -i(--in,--infile) : input file (JSON) | ||
| 171 | |||
| 172 | Options simply inserting records to a table: | ||
| 173 | -t(--table) : table | ||
| 174 | -n(--number) : the number of inserted records | ||
| 175 | --required : a list of columns which require value | ||
| 176 | |||
| 177 | USAGE | ||
| 178 | |||
| 179 | exit(-1); | ||
| 180 | } | ||
| 181 | |||
| 182 | |||
| 183 | |||
| 184 | __END__ |