lib/Data/HandyGen/mysql/TableDef.pm

filter: Statementstmt 13.6% 15 / 110 Branchbran 0.0% 0 / 42 Conditioncond 0.0% 0 / 9 MC/DCmcdc 0.0% 0 / 5 Subroutinesub 21.0% 4 / 19 Podpod 100% 10 / 10 totaltotal 14.8% 29 / 195 CC 26 SCAR 24.8
CC
26
Cov
9%
_fk
30.0
pk_columns
20.0
new
12.0
CRAP 11.9
« script/hd_delete_all.pl ↑ Summary script/hd_insert_bulk.pl »
1 package Data::HandyGen::mysql::TableDef;
2  
3 2 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 2 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
5  
6 2 our $VERSION = '0.0.5';
7 2 $VERSION = eval $VERSION;
8  
9 2 ▶use Carp;
Subroutine BEGIN: called CC=1 SCAR=0.0
10  
11 2 ▶use Data::HandyGen::mysql::ColumnDef;
Subroutine BEGIN: called CC=1 SCAR=0.0
12  
13  
14 =head1 NAME
15  
16 Data::HandyGen::mysql::TableDef - Manages table definition in mysql
17  
18  
19 =head1 VERSION
20  
21 This documentation refers to Data::HandyGen::mysql::TableDef version 0.0.5
22  
23  
24 =head1 SYNOPSIS
25  
26 use Data::HandyGen::mysql::TableDef;
27 use DBI;
28  
29 my $dbh = DBI->connect('dbi:mysql:dbname=testdb', 'username', 'password');
30 my $table_def = Data::HandyGen::mysql::TableDef->new( dbh => $dbh, table_name => 'table1');
31  
32  
33  
34 =head1 CAUTION
35  
36 This module is not intended for use outside Data::HandyGen. Its interface may be changed in the future.
37  
38  
39 =head1 DESCRIPTION
40  
41 This module manages a table definition in one table in Mysql.
42  
43  
44 =head1 METHODS
45  
46 =head2 new(%params)
47  
48 Constructor.
49  
50 =cut
51  
52 0 ▶sub new {
Subroutine new: not called CC=3 SCAR=24.8
Pod documented
53 0 my ($inv, @params) = @_;
54  
55 0 ▶ my %params = ( ref $params[0] eq 'HASH' ) ? %{ $params[0] } : @params;
Branch: 1 branch
BranchTrueFalseTotal
ref $params[0] eq 'HASH' ? : 0 0 0
56  
57 0 ▶ my $class = ref $inv || $inv;
Condition0%0/3
ref $inv || $inv
l!l&&r!l&&!r
000
MC/DC0%0/2
ref $inv || $inv
ref $inv $inv
Truth table0%0/3
A || B
A= ref $invB= $inv
ABresult
1X1
011
000
58 0 my $self = bless { %params }, $class;
59  
60 0 $self;
61 }
62  
63  
64  
65  
66 =head2 dbh($dbh)
67  
68 Setter/getter for database handle generated by DBI.
69  
70 =cut
71  
72 0 ▶sub dbh {
Subroutine dbh: not called CC=2 SCAR=17.9
Pod documented
73 0 my ($self, $dbh) = @_;
74  
75 defined $dbh
76 0 ▶ and $self->{dbh} = $dbh;
Branch: 1 branch
BranchTrueFalseTotal
defined $dbh and $self->{"dbh"} = $dbh 0 0 0
77  
78 0 return $self->{dbh};
79 }
80  
81  
82 # Returns dbh. Dies if $self->dbh is empty.
83 0 ▶sub _get_dbh {
Subroutine _get_dbh: not called CC=2 SCAR=17.9
84 0 my ($self) = @_;
85  
86 0 ▶ my $dbh = $self->dbh()
Branch: 1 branch
BranchTrueFalseTotal
my $dbh = $self->dbh or confess("dbh is empty. You should set dbh beforehand.") 0 0 0
87 or confess "dbh is empty. You should set dbh beforehand.";
88  
89 0 return $dbh;
90 }
91  
92  
93 =head2 table_name($name)
94  
95 Setter/getter for table name.
96  
97 =cut
98  
99 0 ▶sub table_name {
Subroutine table_name: not called CC=3 SCAR=24.8
Pod documented
100 0 my ($self, $name) = @_;
101  
102 0 ▶ if ( defined $name ) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $name) 0 0 0
103 $self->{table_name}
104 0 ▶ and confess "table_name is already set. You cannot change it.";
Branch: 1 branch
BranchTrueFalseTotal
$self->{"table_name"} and confess("table_name is already set. You cannot change it.") 0 0 0
105 0 $self->{table_name} = $name;
106 }
107  
108 0 return $self->{table_name};
109 }
110  
111  
112 # Returns table_name. Dies if $self->table_name is empty.
113 0 ▶sub _get_table_name {
Subroutine _get_table_name: not called CC=2 SCAR=17.9
114 0 my ($self) = @_;
115  
116 0 my $table_name = $self->table_name();
117 0 ▶ defined($table_name)
Branch: 1 branch
BranchTrueFalseTotal
defined $table_name or confess("table_name is empty. You should set table_name beforehand.") 0 0 0
118 or confess "table_name is empty. You should set table_name beforehand.";
119  
120 0 return $table_name;
121 }
122  
123  
124 =head2 colnames()
125  
126 Returns all columns in this table. If you have a table such as
127  
128 mysql> desc table1;
129 +-------------+-------------+------+-----+---------+----------------+
130 | Field | Type | Null | Key | Default | Extra |
131 +-------------+-------------+------+-----+---------+----------------+
132 | id | int(11) | NO | PRI | NULL | auto_increment |
133 | category_id | int(11) | YES | MUL | NULL | |
134 | name | varchar(20) | NO | | NULL | |
135 | price | int(11) | NO | | NULL | |
136 +-------------+-------------+------+-----+---------+----------------+
137  
138 colnames() returns an arrayref containing 'id', 'category_id', 'name' and 'price'. Order won't be guaranteed.
139  
140  
141 =cut
142  
143 0 ▶sub colnames {
Subroutine colnames: not called CC=2 SCAR=17.9
Pod documented
144 0 my ($self) = @_;
145  
146 0 my $def = $self->def();
147 0 ▶ return wantarray ? keys %$def : [ keys %$def ];
Branch: 1 branch
BranchTrueFalseTotal
wantarray ? : 0 0 0
148 }
149  
150  
151 =head2 def()
152  
153 Returns a table definition. It is a hashref which information is originally retrieved from information_schema.columns, which contains fields such as:
154  
155 +--------------------------+---------------------+------+-----+---------+-------+
156 | Field | Type | Null | Key | Default | Extra |
157 +--------------------------+---------------------+------+-----+---------+-------+
158 | TABLE_CATALOG | varchar(512) | NO | | | |
159 | TABLE_SCHEMA | varchar(64) | NO | | | |
160 | TABLE_NAME | varchar(64) | NO | | | |
161 | COLUMN_NAME | varchar(64) | NO | | | |
162 | ORDINAL_POSITION | bigint(21) unsigned | NO | | 0 | |
163 | COLUMN_DEFAULT | longtext | YES | | NULL | |
164 | IS_NULLABLE | varchar(3) | NO | | | |
165 | DATA_TYPE | varchar(64) | NO | | | |
166 | CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES | | NULL | |
167 | CHARACTER_OCTET_LENGTH | bigint(21) unsigned | YES | | NULL | |
168 | NUMERIC_PRECISION | bigint(21) unsigned | YES | | NULL | |
169 | NUMERIC_SCALE | bigint(21) unsigned | YES | | NULL | |
170 | CHARACTER_SET_NAME | varchar(32) | YES | | NULL | |
171 | COLLATION_NAME | varchar(32) | YES | | NULL | |
172 | COLUMN_TYPE | longtext | NO | | NULL | |
173 | COLUMN_KEY | varchar(3) | NO | | | |
174 | EXTRA | varchar(27) | NO | | | |
175 | PRIVILEGES | varchar(80) | NO | | | |
176 | COLUMN_COMMENT | varchar(1024) | NO | | | |
177 +--------------------------+---------------------+------+-----+---------+-------+
178  
179 Table definition returned by def() is like the following:
180  
181 $ret = {
182 'column_1' => {
183 TABLE_CATALOG => 'def',
184 TABLE_SCHEMA => 'test',
185 ...
186 },
187 'column_2' => {
188 TABLE_CATALOG => 'def',
189 TABLE_SCHEMA => 'test',
190 ...
191 },
192 ....
193 }
194  
195 Field names in the hashref are all converted to uppercase.
196  
197  
198 =cut
199  
200 0 ▶sub def {
Subroutine def: not called CC=2 SCAR=17.9
Pod documented
201 0 my ($self) = @_;
202  
203 0 ▶ unless ( $self->{definition} ) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($self->{"definition"}) 0 0 0
204 0 $self->{definition} = $self->_get_table_definition();
205 }
206 0 return +{ %{ $self->{definition} } };
207 }
208  
209  
210 0 ▶sub _fk {
Subroutine _fk: not called CC=5 SCAR=34.0
211 0 my ($self, $column_name) = @_;
212  
213 0 ▶ $self->{_fk} ||= {};
Condition0%0/2
$self->{"_fk"} ||= {}
l!l
00
MC/DC0%0/1
$self->{"_fk"} ||= {}
$self->{"_fk"}
Truth table0%0/2
A
A= $self->{"_fk"}
Aresult
11
00
214  
215 0 ▶ unless ( $self->{_fk}{$column_name} ) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($self->{"_fk"}{$column_name}) 0 0 0
216 0 my $sth = $self->_get_dbh->prepare(q{
217 SELECT referenced_table_name,
218 referenced_column_name
219 FROM information_schema.key_column_usage
220 WHERE table_schema = ?
221 AND table_name = ?
222 AND column_name = ?
223 AND referenced_table_schema IS NOT NULL
224 AND referenced_table_name IS NOT NULL
225 AND referenced_column_name IS NOT NULL
226 });
227 0 ▶ $sth->execute( $self->_dbname, $self->table_name, $column_name )
Branch: 1 branch
BranchTrueFalseTotal
$sth->execute($self->_dbname, $self->table_name, $column_name) or confess('Failed to retrieve foreign key info (' . $self->table_name . ", $column_name)") 0 0 0
228 or confess "Failed to retrieve foreign key info ("
229 . $self->table_name . ", $column_name)";
230  
231 0 my @res = ();
232 0 ▶ while ( my $row = $sth->fetchrow_arrayref() ) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $row = $sth->fetchrow_arrayref) 0 0 0
233 0 push @res, { table => $row->[0], column => $row->[1] };
234 }
235  
236 0 $self->{_fk}{$column_name} = [ @res ];
237 }
238  
239 0 return $self->{_fk}{$column_name};
240 }
241  
242  
243 =head2 is_pk($colname)
244  
245 Returns 1 if $colname is one of primary key columns. Otherwise returns 0.
246  
247 =cut
248  
249 0 ▶sub is_pk {
Subroutine is_pk: not called CC=2 SCAR=17.9
Pod documented
250 0 my ($self, $colname) = @_;
251  
252 return
253 0 ▶ ( grep { $_ eq $colname } @{ $self->pk_columns() } ) ? 1 : 0;
Branch: 1 branch
BranchTrueFalseTotal
grep({$_ eq $colname;} @{$self->pk_columns;}) ? : 0 0 0
254 }
255  
256  
257 =head2 is_fk($colname)
258  
259 If $colname is a foreign key, returns referenced table/column name like this:
260  
261 # In case only one foreign key found
262 $ret = {
263 table => 'table name',
264 column => 'column name'
265 }
266  
267 # In case multiple foreign keys found
268 $ret = [
269 { table => 'table1', column => 'column1' },
270 { table => 'table2', column => 'column2' },
271 { table => 'table3', column => 'column3' },
272 ]
273  
274 Otherwise, returns undef.
275  
276 =cut
277  
278 0 ▶sub is_fk {
Subroutine is_fk: not called CC=3 SCAR=24.8
Pod documented
279 0 my ($self, $colname) = @_;
280  
281 0 my $const_key = $self->_fk($colname);
282 0 ▶ if ( @$const_key == 1 ) {
Branch: 2 branches
BranchTrueFalseTotal
if (@$const_key == 1) { } 0 0 0
elsif (@$const_key == 0) { } 0 0 0
283 0 return { %{ $const_key->[0] } };
284 }
285 elsif ( @$const_key == 0 ) {
286 0 return undef;
287 }
288 else {
289 0 return $const_key;
290 }
291  
292 }
293  
294  
295 =head2 pk_columns()
296  
297 Returns arrayref of column names of primary keys.
298  
299  
300 =cut
301  
302 0 ▶sub pk_columns {
Subroutine pk_columns: not called CC=4 SCAR=30.0
Pod documented
303 0 my ($self) = @_;
304  
305 0 ▶ unless ( $self->{pk_columns} ) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($self->{"pk_columns"}) 0 0 0
306  
307 0 my $sth = $self->_get_dbh->prepare(q{
308 SELECT column_name FROM information_schema.key_column_usage
309 WHERE constraint_name = 'PRIMARY'
310 AND table_schema = ?
311 AND table_name = ?
312 ORDER BY ordinal_position
313 });
314 0 ▶ $sth->execute( $self->_dbname, $self->table_name )
Branch: 1 branch
BranchTrueFalseTotal
$sth->execute($self->_dbname, $self->table_name) or confess('Failed to retrieve primary key info (' . $self->table_name . ')') 0 0 0
315 or confess "Failed to retrieve primary key info (" . $self->table_name . ")";
316  
317 0 my @pk = ();
318 0 ▶ while ( my $row = $sth->fetchrow_arrayref() ) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $row = $sth->fetchrow_arrayref) 0 0 0
319 0 push @pk, $row->[0];
320 }
321  
322 0 $self->{pk_columns} = [ @pk ];
323 }
324  
325 0 return [ @{ $self->{pk_columns} } ];
326 }
327  
328  
329 =head2 column_def($column_name)
330  
331 Returns column definition (ColumnDef object)
332  
333  
334 =cut
335  
336 0 ▶sub column_def {
Subroutine column_def: not called CC=3 SCAR=24.8
Pod documented
337 0 my ($self, $column_name) = @_;
338  
339 0 ▶ defined $column_name
Branch: 1 branch
BranchTrueFalseTotal
defined $column_name or confess("Column name required.") 0 0 0
340 or confess "Column name required.";
341  
342 0 ▶ $self->{column_def} ||= {};
Condition0%0/2
$self->{"column_def"} ||= {}
l!l
00
MC/DC0%0/1
$self->{"column_def"} ||= {}
$self->{"column_def"}
Truth table0%0/2
A
A= $self->{"column_def"}
Aresult
11
00
343  
344 0 my $col_def = Data::HandyGen::mysql::ColumnDef->new($column_name, $self->def->{$column_name});
345 0 $self->{column_def}{$column_name} = $col_def;
346  
347 0 return $self->{column_def}{$column_name};
348 }
349  
350  
351 =head2 get_auto_increment_value()
352  
353 Returns a value which auto_increment will generate next time.
354  
355 =cut
356  
357 0 ▶sub get_auto_increment_value {
Subroutine get_auto_increment_value: not called CC=1 SCAR=6.9
Pod documented
358 0 my ($self) = @_;
359  
360 0 my $table = $self->table_name;
361  
362 0 my $sql = q{SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = ? AND table_name = ?};
363 0 my $sth = $self->_get_dbh()->prepare($sql);
364 0 $sth->bind_param(1, $self->_dbname);
365 0 $sth->bind_param(2, $self->table_name);
366 0 $sth->execute();
367  
368 0 my $ref = $sth->fetchrow_hashref();
369 0 my $ref_uc = { map { uc($_) => $ref->{$_} } keys %$ref };
370  
371 0 return $ref_uc->{AUTO_INCREMENT};
372 }
373  
374  
375 #
376 # Returns a set of a specified table definition.
377 # Result values is a hashref like:
378 #  $res = {
379 # (colname1) => (a record in information_schema),
380 # (colname2) => (same as above),
381 # ..
382 # }
383 #
384 0 ▶sub _get_table_definition {
Subroutine _get_table_definition: not called CC=3 SCAR=24.8
385 0 my ($self) = @_;
386  
387 0 my $sql = q{SELECT * FROM information_schema.columns WHERE table_schema = ? AND table_name = ?};
388 0 my $sth = $self->_get_dbh()->prepare($sql);
389 0 $sth->bind_param(1, $self->_dbname);
390 0 $sth->bind_param(2, $self->table_name);
391 0 $sth->execute();
392 0 my $res = {};
393 0 ▶ while ( my $ref = $sth->fetchrow_hashref ) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $ref = $sth->fetchrow_hashref) 0 0 0
394  
395 # Key name may be uppercase or lowercase depending on environment.
396 # So convert all key names into uppercase.
397 0 my $ref_uc = { map { uc($_) => $ref->{$_} } keys %$ref };
398  
399 0 ▶ my $column_name = $ref_uc->{COLUMN_NAME} || confess "Failed to retrieve column name.";
Condition0%0/2
$ref_uc->{'COLUMN_NAME'} || confess('Failed to retrieve column name.')
l!l
00
MC/DC0%0/1
$ref_uc->{'COLUMN_NAME'} || confess('Failed to retrieve column name.')
$ref_uc->{'COLUMN_NAME'}
Truth table0%0/2
A
A= $ref_uc->{'COLUMN_NAME'}
Aresult
11
00
400 0 $res->{$column_name} = $ref_uc;
401 }
402  
403 0 return $res;
404 }
405  
406  
407 0 ▶sub _dbname {
Subroutine _dbname: not called CC=3 SCAR=24.8
408 0 my ($self) = @_;
409  
410 0 ▶ unless ( $self->{_dbname} ) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($self->{"_dbname"}) 0 0 0
411 0 my $res = $self->_get_dbh()->selectall_arrayref('SELECT DATABASE()');
412 0 ▶ $self->{_dbname} = $res->[0]->[0]
Branch: 1 branch
BranchTrueFalseTotal
$self->{"_dbname"} = $res->[0][0] or confess("Failed to get dbname") 0 0 0
413 or confess "Failed to get dbname";
414 }
415  
416 0 return $self->{_dbname};
417 }
418  
419  
420  
421 2 1;
422  
423  
424  
425 __END__
« script/hd_delete_all.pl ↑ Summary script/hd_insert_bulk.pl »