Bigtop/Backend/SQL/Postgres.pm

filter: Statementstmt 27.5% 64 / 232 Branchbran 0.0% 0 / 74 Conditioncond 0.0% 0 / 3 MC/DCmcdc 0.0% 0 / 2 Subroutinesub 57.1% 20 / 35 Podpod 100% 4 / 4 totaltotal 25.1% 88 / 350 CC 45 SCAR 40.8
CC
45
Cov
21%
output_sql
182.0
output_sql
132.0
output_sql
56.0
CRAP 59.1
« Bigtop/Backend/CGI/Gantry.pm ↑ Summary Bigtop/Backend/Init/Std.pm »
1 package Bigtop::Backend::SQL::Postgres;
2 1 ▶use strict; use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
Subroutine BEGIN: called CC=1 SCAR=0.0
3  
4 1 ▶use Bigtop::Backend::SQL;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 1 ▶use Inline;
Subroutine BEGIN: called CC=1 SCAR=0.0
6  
7 0 ▶sub what_do_you_make {
Subroutine what_do_you_make: not called CC=1 SCAR=6.9
Pod documented
8 return [
9 0 [ 'docs/schema.postgres' => 'Postgres database schema' ],
10 ];
11 }
12  
13 0 ▶sub backend_block_keywords {
Subroutine backend_block_keywords: not called CC=1 SCAR=6.9
Pod documented
14 return [
15 0 { keyword => 'no_gen',
16 label => 'No Gen',
17 descr => 'Skip everything for this backend',
18 type => 'boolean' },
19  
20 { keyword => 'template',
21 label => 'Alternate Template',
22 descr => 'A custom TT template.',
23 type => 'text' },
24 ];
25 }
26  
27 0 ▶sub gen_SQL {
Subroutine gen_SQL: not called CC=3 SCAR=24.8
Pod documented
28 0 shift;
29 0 my $base_dir = shift;
30 0 my $tree = shift;
31  
32 # walk tree generating sql
33 0 my $lookup = $tree->{application}{lookup};
34 0 my $sql = $tree->walk_postorder( 'output_sql', $lookup );
35 0 my $sql_output = join '', @{ $sql };
36  
37 # write the schema.postgres
38 0 my $docs_dir = File::Spec->catdir( $base_dir, 'docs' );
39 0 mkdir $docs_dir;
40  
41 0 my $sql_file = File::Spec->catfile( $docs_dir, 'schema.postgres' );
42  
43 0 ▶ open my $SQL, '>', $sql_file or die "Couldn't write $sql_file: $!\n";
Branch: 1 branch
BranchTrueFalseTotal
open my $SQL, ">", $sql_file or die "Couldn't write ${sql_file}: $!\n" 0 0 0
44  
45 0 print $SQL $sql_output;
46  
47 0 ▶ close $SQL or die "Couldn't close $sql_file: $!\n";
Branch: 1 branch
BranchTrueFalseTotal
close $SQL or die "Couldn't close ${sql_file}: $!\n" 0 0 0
48 }
49  
50 1 our $template_is_setup = 0;
51 1 our $default_template_text = <<'EO_TT_blocks';
52 [% BLOCK sql_block %]
53 CREATE [% keyword %] [% name %][% child_output %]
54  
55 [% END %]
56  
57 [% BLOCK table_body %]
58 (
59 [% FOREACH child_element IN child_output %]
60 [% child_element +%][% UNLESS loop.last %],[% END %]
61  
62 [% END %]
63 );
64 [% FOREACH uq_cons_name IN unique_name.keys.sort %]
65 ALTER TABLE [% name %] ADD CONSTRAINT [% uq_cons_name %] UNIQUE ( [% unique_name.${uq_cons_name}.join(', ') %] );
66 [% END %]
67 [% END %]
68  
69 [% BLOCK pk_text %]
70 PRIMARY KEY( [% FOREACH pk IN pks %][% pk %][% UNLESS loop.last %], [% END %][% END %] )
71 [%- END -%]
72  
73 [% BLOCK table_element_block %] [% name %] [% child_output %][% END %]
74  
75 [% BLOCK field_statement %]
76 [% keywords.join( ' ' ) %]
77 [% END %]
78  
79 [% BLOCK insert_statement %]
80 INSERT INTO [% table %] ( [% columns.join( ', ' ) %] )
81 VALUES ( [% values.join( ', ' ) %] );
82 [% END %]
83  
84 [% BLOCK three_way %]
85 CREATE TABLE [% table_name %] (
86 id SERIAL PRIMARY KEY,
87 [% FOREACH foreign_key IN foreign_keys %]
88 [% foreign_key.table %] int4 REFERENCES [% foreign_key.table %]([% foreign_key.pk %])[% IF ! loop.last || other_fields.0 %],[% END +%]
89 [% END %]
90 [%- FOREACH other_field IN other_fields %]
91 [% other_field %][% IF ! loop.last %],[% END +%]
92 [% END -%]
93 );
94 [% END %]
95 EO_TT_blocks
96  
97 0 ▶sub setup_template {
Subroutine setup_template: not called CC=3 SCAR=24.8
Pod documented
98 0 my $class = shift;
99 0 ▶ my $template_text = shift || $default_template_text;
Condition0%0/3
shift() || $Bigtop::Backend::SQL::Postgres::default_template_text
l!l&&r!l&&!r
000
MC/DC0%0/2
shift() || $Bigtop::Backend::SQL::Postgres::default_template_text
shift() $Bigtop::Backend::SQL::Postgres::default_template_text
Truth table0%0/3
A || B
A= shift()B= $Bigtop::Backend::SQL::Postgres::default_template_text
ABresult
1X1
011
000
100  
101 0 ▶ return if ( $template_is_setup );
Branch: 1 branch
BranchTrueFalseTotal
if $Bigtop::Backend::SQL::Postgres::template_is_setup 0 0 0
102  
103 0 Inline->bind(
104 TT => $template_text,
105 POST_CHOMP => 1,
106 TRIM_LEADING_SPACE => 0,
107 TRIM_TRAILING_SPACE => 0,
108 );
109  
110 0 $template_is_setup = 1;
111 }
112  
113  
114 package # table_block
115 table_block;
116 1 ▶use strict; use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
Subroutine BEGIN: called CC=1 SCAR=0.0
117  
118 0 ▶sub output_sql {
Subroutine output_sql: not called CC=5 SCAR=34.0
119 0 my $self = shift;
120 0 my $child_output = shift;
121 0 my $lookup = shift;
122  
123 0 ▶ return if ( $self->_skip_this_block );
Branch: 1 branch
BranchTrueFalseTotal
if $self->_skip_this_block 0 0 0
124  
125 0 my $child_out_str;
126  
127 my %output;
128 0 foreach my $statement ( @{ $child_output } ) {
129 0 my ( $type, $output ) = @{ $statement };
130 0 push @{ $output{ $type } }, $output;
131 }
132  
133 0 my $pks = $self->find_primary_key( $self->{__NAME__}, $lookup );
134  
135 0 ▶ if ( ref( $pks ) eq 'ARRAY' ) { # multi-column primary key
Branch: 1 branch
BranchTrueFalseTotal
if (ref $pks eq "ARRAY") 0 0 0
136 0 my $pk_text = Bigtop::Backend::SQL::Postgres::pk_text(
137 { pks => $pks, }
138 );
139 0 push @{ $output{ table_body } }, $pk_text;
140 }
141  
142 my $unique_name = $self->find_unique_name(
143 $self->{__NAME__},
144 0 $lookup,
145 );
146  
147 $child_out_str = Bigtop::Backend::SQL::Postgres::table_body(
148 {
149 child_output => $output{table_body},
150 0 unique_name => $unique_name,
151 name => $self->get_name()
152 }
153 );
154  
155 0 ▶ if ( defined $output{insert_statements} ) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $output{"insert_statements"}) 0 0 0
156 $child_out_str .= "\n"
157 0 . join "\n", @{ $output{insert_statements} };
158 }
159  
160 0 my $output = Bigtop::Backend::SQL::Postgres::sql_block(
161 {
162 keyword => $self->get_create_keyword(),
163 child_output => $child_out_str,
164 name => $self->get_name()
165 }
166 );
167  
168 0 return [ $output ];
169 }
170  
171 package # seq_block
172 seq_block;
173 1 ▶use strict; use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
Subroutine BEGIN: called CC=1 SCAR=0.0
174  
175 0 ▶sub output_sql {
Subroutine output_sql: not called CC=2 SCAR=17.9
176 0 my $self = shift;
177 0 my $child_output = shift;
178  
179 0 ▶ return if ( $self->_skip_this_block );
Branch: 1 branch
BranchTrueFalseTotal
if $self->_skip_this_block 0 0 0
180  
181 0 my $child_out_str;
182  
183 0 $child_out_str = join( "\n", @{ $child_output }) . ';';
184  
185 0 my $output = Bigtop::Backend::SQL::Postgres::sql_block(
186 {
187 keyword => $self->get_create_keyword(),
188 child_output => $child_out_str,
189 name => $self->get_name(),
190 }
191 );
192  
193 0 return [ $output ];
194 }
195  
196 package # schema_block
197 schema_block;
198 1 ▶use strict; use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
Subroutine BEGIN: called CC=1 SCAR=0.0
199  
200 0 ▶sub output_sql {
Subroutine output_sql: not called CC=1 SCAR=6.9
201 0 my $self = shift;
202  
203 0 my $output = Bigtop::Backend::SQL::Postgres::sql_block(
204 {
205 keyword => $self->get_create_keyword(),
206 child_output => ';',
207 name => $self->get_name(),
208 }
209 );
210  
211 0 return [ $output ];
212 }
213  
214 package # table_element_block
215 table_element_block;
216 1 ▶use strict; use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
Subroutine BEGIN: called CC=1 SCAR=0.0
217  
218 0 ▶sub output_sql {
Subroutine output_sql: not called CC=11 SCAR=48.8
219 0 my $self = shift;
220 0 my $child_output = shift;
221  
222 0 ▶ if ( defined $child_output) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $child_output) { } 0 0 0
223  
224 0 my %output_pieces;
225 0 foreach my $child_item ( @{ $child_output } ) {
226 0 my ( $type, $output ) = %{ $child_item };
227 0 $output_pieces{ $type } = $output;
228 }
229  
230 0 ▶ return if $output_pieces{ skip_column };
Branch: 1 branch
BranchTrueFalseTotal
if $output_pieces{"skip_column"} 0 0 0
231  
232 0 my $child_out_str = $output_pieces{ base_col_def };
233 0 ▶ if ( $output_pieces{ foreign_key_col } ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($output_pieces{"foreign_key_col"}) 0 0 0
234 0 ▶ unless ( $output_pieces{ foreign_table } ) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($output_pieces{"foreign_table"}) 0 0 0
235 0 die "field '" . $self->get_name . "' in table '"
236 . $self->get_table_name
237 . "' has a foreign_key_col, but no refers_to\n"
238 }
239 $child_out_str .= ' REFERENCES '
240 . $output_pieces{ foreign_table }
241 0 . "($output_pieces{ foreign_key_col })";
242  
243 0 ▶ if ( $output_pieces{ on_delete } ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($output_pieces{"on_delete"}) 0 0 0
244 0 $child_out_str .=
245 "\n ON DELETE $output_pieces{ on_delete }";
246 }
247 0 ▶ if ( $output_pieces{ on_update } ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($output_pieces{"on_update"}) 0 0 0
248 0 $child_out_str .=
249 "\n ON UPDATE $output_pieces{ on_update }";
250 }
251 }
252  
253 0 my $output = Bigtop::Backend::SQL::Postgres::table_element_block(
254 { name => $self->get_name(), child_output => $child_out_str }
255 );
256  
257 0 return [ [ table_body => $output ] ];
258 }
259 else {
260 0 ▶ return unless ( $self->{__TYPE__} eq 'data' );
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"__TYPE__"} eq "data" 0 0 0
261  
262 0 my @columns;
263 my @values;
264 0 foreach my $insertion ( @{ $self->{__ARGS__} } ) {
265 0 my ( $column, $value ) = %{ $insertion };
266  
267 0 ▶ $value = "'$value'" unless $value =~ /^\d+$/;
Branch: 1 branch
BranchTrueFalseTotal
unless $value =~ /^\d+$/ 0 0 0
268  
269 0 push @columns, $column;
270 0 push @values, $value;
271 }
272  
273 0 my $output = Bigtop::Backend::SQL::Postgres::insert_statement(
274 {
275 table => $self->get_table_name,
276 columns => \@columns,
277 values => \@values,
278 }
279 );
280 0 return [ [ insert_statements => $output ] ];
281 }
282 }
283  
284 package # field_statement
285 field_statement;
286 1 ▶use strict; use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
Subroutine BEGIN: called CC=1 SCAR=0.0
287  
288 my %code_for = (
289 primary_key => \&postgres_pk_text,
290 assign_by_sequence => \&gen_seq_text,
291 auto => \&gen_seq_text,
292 0 ▶ datetime => sub { 'TIMESTAMP WITH TIME ZONE' },
Subroutine __ANON__: not called CC=1 SCAR=6.9
293 1 );
294  
295 0 ▶sub postgres_pk_text {
Subroutine postgres_pk_text: not called CC=2 SCAR=17.9
296 0 my $self = shift;
297 0 my $lookup = shift;
298 0 my $table = $self->get_table_name();
299  
300 0 my $pks = table_block->find_primary_key( $table, $lookup );
301  
302 0 ▶ return ( ref( $pks ) eq 'ARRAY' ) ? '' : 'PRIMARY KEY';
Branch: 1 branch
BranchTrueFalseTotal
ref $pks eq 'ARRAY' ? : 0 0 0
303 }
304  
305 0 ▶sub gen_seq_text {
Subroutine gen_seq_text: not called CC=3 SCAR=24.8
306 0 my $self = shift;
307 0 my $lookup = shift;
308  
309 0 my $table = $self->get_table_name();
310  
311 0 my $sequence = $lookup->{tables}{$table}{sequence}{__ARGS__}[0];
312  
313 # Make sure a sequence block exists for the given sequence.
314 0 ▶ if ( defined $sequence ) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $sequence) { } 0 0 0
315 0 ▶ if ( defined $lookup->{sequences}{ $sequence }) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $lookup->{'sequences'}{$sequence}) { } 0 0 0
316 0 return "DEFAULT NEXTVAL( '$sequence' )";
317 }
318 else {
319 0 die "You requested and undefined sequence '$sequence' "
320 . "for table $table.\n";
321 }
322 }
323 else {
324 0 return 'SERIAL';
325 }
326  
327 }
328  
329 0 ▶sub output_sql {
Subroutine output_sql: not called CC=13 SCAR=52.0
330 0 my $self = shift;
331 0 shift; # there is no child output
332 0 my $lookup = shift;
333  
334 0 my $keyword = $self->get_name();
335  
336 0 ▶ if ($keyword eq 'pseudo_value') {
Branch: 5 branches
BranchTrueFalseTotal
if ($keyword eq 'pseudo_value') { } 0 0 0
elsif ($keyword eq 'is') { } 0 0 0
elsif ($keyword eq 'refers_to') { } 0 0 0
elsif ($keyword eq 'on_delete') { } 0 0 0
elsif ($keyword eq 'on_update') { } 0 0 0
337 0 ▶ if ($self->{__DEF__}{__ARGS__}[0]) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"__DEF__"}{"__ARGS__"}[0]) 0 0 0
338 0 return [ { skip_column => 1 } ];
339 }
340 }
341  
342 elsif ( $keyword eq 'is' ) {
343 0 my @keywords;
344 0 foreach my $arg ( @{ $self->{__DEF__}{__ARGS__} } ) {
345 0 my $code = $code_for{$arg};
346  
347 0 ▶ if ( defined $code ) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $code) { } 0 0 0
348 0 my $new_keyword = $code->( $self, $lookup );
349 0 ▶ if ( $new_keyword eq 'SERIAL' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($new_keyword eq 'SERIAL') { } 0 0 0
350 0 ▶ shift @keywords if ( $keywords[0] =~ /int4/ );
Branch: 1 branch
BranchTrueFalseTotal
if $keywords[0] =~ /int4/ 0 0 0
351 0 unshift @keywords, $new_keyword;
352 }
353 else {
354 0 ▶ push @keywords, $new_keyword if ( $new_keyword );
Branch: 1 branch
BranchTrueFalseTotal
if $new_keyword 0 0 0
355 }
356 }
357 else {
358 0 push @keywords, $arg;
359 }
360 }
361 0 my $output = Bigtop::Backend::SQL::Postgres::field_statement(
362 { keywords => \@keywords }
363 );
364  
365 0 return [ { base_col_def => $output } ];
366 }
367 elsif ( $keyword eq 'refers_to' ) {
368 0 my $foreign_info = $self->{__DEF__}{__ARGS__}[0];
369  
370 0 ▶ return unless ( ref( $foreign_info ) eq 'HASH' );
Branch: 1 branch
BranchTrueFalseTotal
unless ref $foreign_info eq "HASH" 0 0 0
371  
372 0 my ( $table, $col ) = %{ $foreign_info };
373  
374 return [
375 0 { foreign_table => $table },
376 { foreign_key_col => $col },
377 ];
378 }
379 elsif ( $keyword eq 'on_delete' ) {
380 0 return [ { on_delete => $self->{__DEF__}{__ARGS__}[0] } ];
381 }
382 elsif ( $keyword eq 'on_update' ) {
383 0 return [ { on_update => $self->{__DEF__}{__ARGS__}[0] } ];
384 }
385 }
386  
387 package # literal_block
388 literal_block;
389 1 ▶use strict; use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
Subroutine BEGIN: called CC=1 SCAR=0.0
390  
391 0 ▶sub output_sql {
Subroutine output_sql: not called CC=1 SCAR=6.9
392 0 my $self = shift;
393  
394 0 return $self->make_output( 'SQL' );
395 }
396  
397 package # join_table
398 join_table;
399 1 ▶use strict; use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
Subroutine BEGIN: called CC=1 SCAR=0.0
400  
401 0 ▶sub output_sql {
Subroutine output_sql: not called CC=7 SCAR=40.3
402 0 my $self = shift;
403 0 my $child_output = shift;
404 0 my $lookup = shift;
405  
406 0 my @foreign_keys;
407 my @other_fields;
408 0 my @inserts;
409  
410 0 foreach my $child_bit ( @{ $child_output } ) {
411 0 ▶ if ( ref $child_bit eq 'ARRAY' ) {
Branch: 1 branch
BranchTrueFalseTotal
if (ref $child_bit eq 'ARRAY') { } 0 0 0
412 0 my ( $type, $new_item ) = @{ $child_bit };
413  
414 0 ▶ if ( $type eq 'table_body' ) {
Branch: 2 branches
BranchTrueFalseTotal
if ($type eq 'table_body') { } 0 0 0
elsif ($type eq 'insert_statements') { } 0 0 0
415 0 push @other_fields, $new_item;
416 }
417 elsif ( $type eq 'insert_statements' ) {
418 0 push @inserts, $new_item;
419 }
420 }
421 else {
422 # find the foreign table's unique primary key
423 0 my $pk = $self->find_primary_key( $child_bit, $lookup );
424  
425 # if the pk is compound, scream and punt
426 0 ▶ if ( ref $pk eq 'ARRAY' ) {
Branch: 1 branch
BranchTrueFalseTotal
if (ref $pk eq "ARRAY") 0 0 0
427 warn 'join_table '
428 . $self->{__NAME__}
429 0 . " cannot join $child_bit,"
430 . " because it has a compound primary key\n";
431 0 $pk = 'id';
432 }
433  
434 0 push @foreign_keys, { table => $child_bit, pk => $pk };
435 }
436 }
437  
438 my $three_way = Bigtop::Backend::SQL::Postgres::three_way(
439 {
440 table_name => $self->{__NAME__},
441 0 foreign_keys => \@foreign_keys,
442 other_fields => \@other_fields,
443 }
444 );
445  
446 0 ▶ $three_way .= "\n" . join( "\n", @inserts ) . "\n" if @inserts;
Branch: 1 branch
BranchTrueFalseTotal
if @inserts 0 0 0
447  
448 0 return [ $three_way ];
449 }
450  
451 package # join_table_statement
452 join_table_statement;
453 1 ▶use strict; use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
Subroutine BEGIN: called CC=1 SCAR=0.0
454  
455 0 ▶sub output_sql {
Subroutine output_sql: not called CC=5 SCAR=34.0
456 0 my $self = shift;
457 0 my $child_output = shift;
458  
459 0 ▶ if ( $self->{__KEYWORD__} eq 'joins' ) {
Branch: 2 branches
BranchTrueFalseTotal
if ($self->{'__KEYWORD__'} eq 'joins') { } 0 0 0
elsif ($self->{'__KEYWORD__'} eq 'data') { } 0 0 0
460 0 my @tables = %{ $self->{__DEF__}->get_first_arg() };
461  
462 0 return \@tables;
463 }
464 elsif ( $self->{__KEYWORD__} eq 'data' ) {
465 0 my @columns;
466 my @values;
467 0 foreach my $insertion ( @{ $self->{__DEF__} } ) {
468 0 my ( $column, $value ) = %{ $insertion };
469  
470 0 ▶ $value = "'$value'" unless $value =~ /^\d+$/;
Branch: 1 branch
BranchTrueFalseTotal
unless $value =~ /^\d+$/ 0 0 0
471  
472 0 push @columns, $column;
473 0 push @values, $value;
474 }
475  
476 0 my $output = Bigtop::Backend::SQL::Postgres::insert_statement(
477 {
478 table => $self->get_join_table_name,
479 columns => \@columns,
480 values => \@values,
481 }
482 );
483 0 return [ [ insert_statements => $output ] ];
484 }
485 else {
486 0 return;
487 }
488  
489 }
490  
491 1 1;
492  
493 __END__
« Bigtop/Backend/CGI/Gantry.pm ↑ Summary Bigtop/Backend/Init/Std.pm »