script/hd_insert_bulk.pl untested

filter: Statementstmt 0.0% 0 / 88 Branchbran 0.0% 0 / 14 Conditioncond 0.0% 0 / 21 MC/DCmcdc 0.0% 0 / 12 Subroutinesub 0.0% 0 / 12 Podpod 0.0% 0 / 5 totaltotal 0.0% 0 / 152 CC 8 SCAR 21.9
CC
8
Cov
0%
main
20.0
insert_from_file
12.0
insert
12.0
CRAP 8.9
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__