Author: spadkins
Date: Wed Sep 1 09:40:16 2010
New Revision: 14366
Modified:
p5ee/trunk/App-Repository/CHANGES
p5ee/trunk/App-Repository/lib/App/Repository.pm
p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
p5ee/trunk/App-Repository/t/DBI-insert.t
p5ee/trunk/App-Repository/t/DBI-select-join.t
Log:
add summary table support
Modified: p5ee/trunk/App-Repository/CHANGES
==============================================================================
--- p5ee/trunk/App-Repository/CHANGES (original)
+++ p5ee/trunk/App-Repository/CHANGES Wed Sep 1 09:40:16 2010
@@ -2,6 +2,9 @@
# CHANGE LOG
#########################################
+0.968
+ x summary_tables are now implemented
+
0.967
x App::Repository::get_index()/get_unique_index(): now work for arrays as
well as hashrefs
x App::Repository::_load_table_metadata(): can load single-table metadata
from external *.pl file
Modified: p5ee/trunk/App-Repository/lib/App/Repository.pm
==============================================================================
--- p5ee/trunk/App-Repository/lib/App/Repository.pm (original)
+++ p5ee/trunk/App-Repository/lib/App/Repository.pm Wed Sep 1 09:40:16 2010
@@ -621,11 +621,11 @@
@$cols = @$columns;
}
- my $cache_skip = $context_options->{cache_skip};
- $cache_skip = $options->{cache_skip} if (!defined $cache_skip);
- my $tabledef = $self->{table}{$table};
+ my $cache_skip = $options->{cache_skip};
+ $cache_skip = $context_options->{cache_skip} if (!defined $cache_skip);
+ my $table_def = $self->{table}{$table};
my ($sds, $hashkey, @cache_colidx_map);
- if ($tabledef->{cache_name} && !$cache_skip) {
+ if ($table_def->{cache_name} && !$cache_skip) {
my $cache_refresh = $context_options->{cache_refresh};
$cache_refresh = $options->{cache_refresh} if (!defined
$cache_refresh);
my $cache_exception_on_miss = $options->{cache_exception_on_miss};
@@ -633,7 +633,7 @@
$options = { %$options };
delete $options->{cache_exception_on_miss};
}
- my $cache_minimum_columns = $tabledef->{cache_minimum_columns};
+ my $cache_minimum_columns = $table_def->{cache_minimum_columns};
if ($cache_minimum_columns) {
my (%colidx, $col);
my $cache_columns = [ @$cache_minimum_columns ];
@@ -653,7 +653,7 @@
}
$cols = $cache_columns;
}
- $sds = $context->shared_datastore($tabledef->{cache_name});
+ $sds = $context->shared_datastore($table_def->{cache_name});
my ($hash_options);
if (defined $options) {
$hash_options = { %$options };
@@ -724,7 +724,7 @@
$context->log("Cache Save: $table $hashkey (get_row)\n") if
($log_cache);
}
}
- if ($sds && $tabledef->{cache_minimum_columns} && $row) {
+ if ($sds && $table_def->{cache_minimum_columns} && $row) {
$row = [ @{$ro...@cache_colidx_map] ];
}
}
@@ -942,12 +942,12 @@
@$cols = @$columns;
}
- my $cache_skip = $context_options->{cache_skip};
- $cache_skip = $options->{cache_skip} if (!defined $cache_skip);
+ my $cache_skip = $options->{cache_skip};
+ $cache_skip = $context_options->{cache_skip} if (!defined $cache_skip);
- my $tabledef = $self->{table}{$table};
+ my $table_def = $self->{table}{$table};
my ($sds, $hashkey, @cache_colidx_map);
- if ($tabledef->{cache_name} && !$cache_skip) {
+ if ($table_def->{cache_name} && !$cache_skip) {
my $cache_refresh = $context_options->{cache_refresh};
$cache_refresh = $options->{cache_refresh} if (!defined
$cache_refresh);
my $cache_exception_on_miss = $options->{cache_exception_on_miss};
@@ -955,7 +955,7 @@
$options = { %$options };
delete $options->{cache_exception_on_miss};
}
- my $cache_minimum_columns = $tabledef->{cache_minimum_columns};
+ my $cache_minimum_columns = $table_def->{cache_minimum_columns};
if ($cache_minimum_columns) {
my (%colidx, $col);
my $cache_columns = [ @$cache_minimum_columns ];
@@ -973,7 +973,7 @@
}
$cols = $cache_columns;
}
- $sds = $context->shared_datastore($tabledef->{cache_name});
+ $sds = $context->shared_datastore($table_def->{cache_name});
my ($hash_options);
if (defined $options) {
$hash_options = { %$options };
@@ -1039,31 +1039,74 @@
$cols = $new_cols; # then point to the new columns
regardless
}
- my ($summary_table, $summary_column_defs,
$summary_repository_name);
- #my $summary_tables = $table_def->{summary_tables};
- #if ($summary_tables) {
- # foreach my $summary_table_spec (@$summary_tables) {
- # ($summary_table, $summary_column_defs,
$summary_repository_name) = @$summary_table_spec; # assume this summary will
work
- # foreach $col (@$cols) {
- # if (!$column_defs->{$col}{expr} &&
!$summary_columns_defs->{$col}) { # the column doesn't exist on the summary
table
- # $summary_table = undef;
# so the summary won't work
- # last;
- # }
- # }
- # last if ($summary_table);
- # }
- #}
+ my ($summary_table, $summary_table_def, $summary_column_defs,
$summary_repository_name);
+ my $summary_tables = $table_def->{summary_tables};
+
+ my $summary_skip = $options->{summary_skip};
+ $summary_skip = $context_options->{summary_skip} if (!defined
$summary_skip);
+
+ if ($summary_tables && !$summary_skip) {
+ die "{summary_tables} must be an array ref in table [$table]
definition" if (ref($summary_tables) ne "ARRAY");
+ foreach my $summary_table_spec (@$summary_tables) {
+ ($summary_table, $summary_column_defs,
$summary_repository_name) = @$summary_table_spec; # assume this summary will
work
+ #print STDERR "get_rows($table) : checking
summary_table=[$summary_table]\n";
+ if (!$summary_column_defs) {
+ if ($summary_repository_name) {
+ my $rep =
$context->repository($summary_repository_name);
+ $summary_table_def =
$rep->get_table_def($summary_table);
+ $summary_column_defs =
$summary_table_def->{column};
+ }
+ else {
+ $summary_table_def =
$self->get_table_def($summary_table);
+ $summary_column_defs =
$summary_table_def->{column};
+ }
+ }
+ foreach $col (@$cols) {
+ #print STDERR "get_rows($table) : checking
summary_table=[$summary_table] : column=[$col]\n";
+ if (!$column_defs->{$col}{expr} &&
!$summary_column_defs->{$col}) { # the column doesn't exist on the summary
table
+ #print STDERR "get_rows($table) : checking
summary_table=[$summary_table] : column=[$col] : Not defined\n";
+ #print STDERR "get_rows($table) :
summary_columns=[", join(",", keys %$summary_column_defs), "]\n";
+ $summary_table = undef;
# so the summary won't work
+ last;
+ }
+ }
+ last if ($summary_table);
+ }
+ }
if ($summary_table) {
- if ($summary_repository_name) {
- my $rep = $context->repository($summary_repository_name);
- $rows = $rep->get_rows($summary_table, $params, $cols,
$options);
+ my $summary_ready = $self->_is_summary_ready($summary_table,
$params);
+ if (!defined $summary_ready || $summary_ready) {
+ if ($summary_repository_name) {
+ my $rep =
$context->repository($summary_repository_name);
+ $rows = $rep->get_rows($summary_table, $params, $cols,
$options);
+ }
+ else {
+ $rows = $self->get_rows($summary_table, $params,
$cols, $options);
+ }
+ if (!defined $summary_ready) { # We weren't sure if the
summary was ready, so we check the resulting rows.
+ if ($#$rows == -1) { # There were no summary
rows. The summary must not have been ready.
+ $summary_table = undef; # We fall back to
querying the detail table.
+ }
+ elsif ($#$rows == 0) { # There is 1 summary row.
Let's check to see if it is full of NULL's exclusively.
+ my $row = $rows->[0];
+ my $all_nulls = 1;
+ foreach my $value (@$row) {
+ if (defined $value) {
+ $all_nulls = 0;
+ last;
+ }
+ }
+ $summary_table = undef if ($all_nulls); # The
summary is not ready. We fall back to querying the detail table.
+ }
+ }
}
else {
- $rows = $self->get_rows($summary_table, $params, $cols,
$options);
+ # The summary is not ready. We fall back to querying the
detail table.
+ $summary_table = undef if ($#$rows == -1); # got no rows
}
- $summary_table = undef if ($#$rows == -1); # got no rows
}
+
if (!$summary_table) {
$rows = $self->_get_rows($table, $params, $cols, $options);
}
@@ -1078,7 +1121,7 @@
}
}
- if ($sds && $tabledef->{cache_minimum_columns}) {
+ if ($sds && $table_def->{cache_minimum_columns}) {
my $requested_rows = [];
foreach my $row (@$rows) {
push(@$requested_rows, [ @{$ro...@cache_colidx_map] ]);
@@ -1090,6 +1133,15 @@
return($rows);
}
+# This method exists so that it can be overridden in a subclass
+sub _is_summary_ready {
+ &App::sub_entry if ($App::trace);
+ my ($self, $summary_table, $params) = @_;
+ my $is_ready = undef;
+ &App::sub_exit($is_ready) if ($App::trace);
+ return($is_ready);
+}
+
sub _get_default_columns {
&App::sub_entry if ($App::trace);
my ($self, $table) = @_;
@@ -2246,7 +2298,12 @@
$self->_check_default_and_required_fields($object);
$options = $options ? { %$options } : {};
- $options->{last_inserted_id} = 1;
+ my $primary_key_auto_increment = $table_def->{primary_key_auto_increment};
+ $primary_key_auto_increment = 1 if (!defined $primary_key_auto_increment);
+
+ if ($primary_key_auto_increment) {
+ $options->{last_inserted_id} = 1;
+ }
if ($options->{temp}) {
my $constructor = ($class =~ /Moose/) ? "new" : undef; # TODO: I might
want to make this more general/configurable
if ($constructor) {
@@ -2263,11 +2320,12 @@
else {
my $retval = $self->insert_row($table, $object, undef, $options);
die "new($table) unable to create a new row" if (!$retval);
- my $params = $self->last_inserted_id($table);
+ my ($params);
+ $params = $self->last_inserted_id($table) if
($primary_key_auto_increment);
if (!$params) {
$params = {};
foreach my $col (keys %$object) {
- $params->{$col . ".eq"} = $object->{$col};
+ $params->{$col . ".eq"} = $object->{$col} if ($col !~ /^_/);
}
}
$object = $self->get_object($table, $params, undef, $options);
@@ -4369,6 +4427,74 @@
# load up all additional information from the native metadata
$self->_load_table_metadata_from_source($table);
+ if ($table_def->{overlay_from_table}) {
+ #print STDERR "load_table_metadata($table) :
OVERLAY=[$table_def->{overlay_from_table}]\n";
+
+ # overlay_from_table => "hotel_bkg_stay",
+ # overlay_sections => ["cache_name", "cache_minimum_columns", "alias",
"tablealiases", "tablealias", "column", ],
+ # overlay_column_exceptions =>
["^(pos_|agent_|los|guests|loyalty_|rm_type_|rate_level)"],
+
+ my $overlay_from_table = $table_def->{overlay_from_table};
+ my $overlay_sections = $table_def->{overlay_sections} ||
["tablealiases", "tablealias", "column"];
+ my $overlay_columns = $table_def->{overlay_columns};
+ my $overlay_column_exceptions =
$table_def->{overlay_column_exceptions};
+
+ my $overlay_from_table_def = $self->get_table_def($overlay_from_table);
+ foreach my $section (@$overlay_sections) {
+ #print STDERR "load_table_metadata($table) :
OVERLAY=[$table_def->{overlay_from_table}] : section=[$section]\n";
+ if ($section eq "column") {
+ #print STDERR "load_table_metadata($table) : columns=[",
join(",", keys %{$table_def->{column}}), "]\n";
+ #print STDERR "load_table_metadata($table) :
OVERLAY=[$table_def->{overlay_from_table}] : section=[$section] :
cols=[$overlay_columns] !cols=[$overlay_column_exceptions]\n";
+ my @overlay_columns = $overlay_columns ?
+ (ref($overlay_columns)
? @$overlay_columns : $overlay_columns) :
+ ();
+ my @overlay_column_exceptions = $overlay_column_exceptions ?
+
(ref($overlay_column_exceptions) ? @$overlay_column_exceptions :
$overlay_column_exceptions) :
+ ();
+ $table_def->{column} = {} if (!$table_def->{column});
+ COLUMN: foreach my $column (keys
%{$overlay_from_table_def->{column}}) {
+ if (!$table_def->{column}{$column}) {
+ if ($overlay_columns) {
+ foreach (@overlay_columns) {
+ next COLUMN if ($column !~ /$_/);
+ }
+ }
+ if ($overlay_column_exceptions) {
+ foreach (@overlay_column_exceptions) {
+ next COLUMN if ($column =~ /$_/);
+ }
+ }
+ $table_def->{column}{$column} =
$overlay_from_table_def->{column}{$column};
+ #print STDERR "load_table_metadata($table) :
OVERLAY=[$table_def->{overlay_from_table}] : section=[$section] :
column=[$column]\n";
+ }
+ }
+ #print STDERR "load_table_metadata($table) : columns=[",
join(",", keys %{$table_def->{column}}), "]\n";
+ }
+ elsif ($section eq "tablealiases") {
+ if ($table_def->{$section}) {
+ my (%array_element_seen);
+ foreach my $value (@{$table_def->{$section}}) {
+ $array_element_seen{$value} = 1;
+ }
+ foreach my $value (@{$overlay_from_table_def->{$section}})
{
+ push(@{$table_def->{$section}}, $value) if
(!$array_element_seen{$value});
+ }
+ }
+ else {
+ $table_def->{$section} =
$overlay_from_table_def->{$section};
+ }
+ }
+ else {
+ if ($table_def->{$section}) {
+ App::Reference->overlay($table_def->{$section},
$overlay_from_table_def->{$section});
+ }
+ else {
+ $table_def->{$section} =
$overlay_from_table_def->{$section};
+ }
+ }
+ }
+ }
+
$columns = $table_def->{columns};
if (! defined $columns) {
$columns = [];
Modified: p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
==============================================================================
--- p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm (original)
+++ p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm Wed Sep 1 09:40:16 2010
@@ -3268,6 +3268,7 @@
$table_def->{primary_key} =
$self->_get_primary_key_from_source($table) if (!$table_def->{primary_key});
$table_def->{alternate_key} =
$self->_get_alternate_keys_from_source($table) if
(!$table_def->{alternate_key});
}
+ $table_def->{primary_key_auto_increment} =
$self->_is_primary_key_auto_increment($table_def);
}
{
@@ -3502,6 +3503,25 @@
return($alternate_keys);
}
+sub _is_primary_key_auto_increment {
+ &App::sub_entry if ($App::trace);
+ my ($self, $table_def) = @_;
+ my $primary_key_auto_increment = 1;
+ my $primary_key = $table_def->{primary_key};
+ if (!$primary_key || $#$primary_key != 0) {
+ $primary_key_auto_increment = 0;
+ }
+ else {
+ my $pk_column = $primary_key->[0];
+ my $pk_column_type = $table_def->{column}{$pk_column}{type};
+ if (defined $pk_column_type && $pk_column_type ne "integer") {
+ $primary_key_auto_increment = 0;
+ }
+ }
+ &App::sub_exit($primary_key_auto_increment) if ($App::trace);
+ return($primary_key_auto_increment);
+}
+
sub _column_metadata_specifiers {
&App::sub_entry if ($App::trace);
my ($self, $table) = @_;
Modified: p5ee/trunk/App-Repository/t/DBI-insert.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-insert.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-insert.t Wed Sep 1 09:40:16 2010
@@ -256,12 +256,55 @@
EOF
$sql = $db->_mk_insert_rows_sql("test_person", ["person_id",
"age","first_name","gender","state"], $dup_rows, { update => 1 });
is($sql, $expect_sql, "_mk_insert_rows_sql(): 7 rows, bulk
insert/update");
+
+ $expect_sql = <<EOF;
+insert into test_person
+ (person_id, age, first_name, gender, state)
+values
+ (1, 39, 'stephen', 'M', 'GA'),
+ (2, 37, 'susan', 'F', 'GA'),
+ (3, 6, 'maryalice', 'F', 'GA'),
+ (4, 3, 'paul', 'M', 'GA'),
+ (5, 1, 'christine', 'F', 'GA'),
+ (6, 45, 'tim', 'M', 'GA'),
+ (7, 39, 'keith', 'M', 'GA')
+on duplicate key update
+ age = values(age),
+ gender = values(gender),
+ state = values(state)
+EOF
+ $sql = $db->_mk_insert_rows_sql("test_person", ["person_id",
"age","first_name","gender","state"], $dup_rows, { update => { age => 1, gender
=> 1, state => 1} });
+ is($sql, $expect_sql, "_mk_insert_rows_sql(): 7 rows, bulk
insert/update, with named columns");
+
+ $expect_sql = <<EOF;
+insert into test_person
+ (person_id, age, first_name, gender, state)
+values
+ (1, 39, 'stephen', 'M', 'GA'),
+ (2, 37, 'susan', 'F', 'GA'),
+ (3, 6, 'maryalice', 'F', 'GA'),
+ (4, 3, 'paul', 'M', 'GA'),
+ (5, 1, 'christine', 'F', 'GA'),
+ (6, 45, 'tim', 'M', 'GA'),
+ (7, 39, 'keith', 'M', 'GA')
+on duplicate key update
+ age = (case when values(gender) = 'F' then age-1 else values(age) end),
+ gender = values(gender),
+ state = values(state)
+EOF
+ $sql = $db->_mk_insert_rows_sql("test_person", ["person_id",
"age","first_name","gender","state"], $dup_rows, { update => { age => "(case
when values(gender) = 'F' then age-1 else values(age) end)", gender => 1, state
=> 1} });
+ is($sql, $expect_sql, "_mk_insert_rows_sql(): 7 rows, bulk
insert/update, with named columns and custom expressions");
+
$nrows = $db->insert_rows("test_person",
["age","first_name","gender","state"], $new_rows);
is($nrows, 7, "insert_rows(): 7 rows, bulk insert");
$nrows = $db->insert_rows("test_person",
["person_id","age","first_name","gender","state"], $dup_rows, { replace => 1 });
is($nrows, 7, "insert_rows(): 7 rows, bulk replace");
$nrows = $db->insert_rows("test_person", ["person_id",
"age","first_name","gender","state"], $dup_rows, { update => 1 });
is($nrows, 7, "insert_rows(): 7 rows, bulk insert/update");
+ $nrows = $db->insert_rows("test_person", ["person_id",
"age","first_name","gender","state"], $dup_rows, { update => { age => 1, gender
=> 1, state => 1} });
+ is($nrows, 7, "insert_rows(): 7 rows, bulk insert/update w/ named
columns");
+ $nrows = $db->insert_rows("test_person", ["person_id",
"age","first_name","gender","state"], $dup_rows, { update => { age => "(case
when values(gender) = 'F' then age-1 else values(age) end)", gender => 1, state
=> 1} });
+ is($nrows, 7, "insert_rows(): 7 rows, bulk insert/update w/ named
columns and custom expressions");
$nrows = $db->insert_rows("test_person",
["person_id","age","first_name","gender","state"], $dup_rows, { replace => 1,
maxrows => 4 });
is($nrows, 7, "insert_rows(): 7 rows, bulk replace (4 at a time)");
$nrows = $db->insert_rows("test_person", ["person_id",
"age","first_name","gender","state"], $dup_rows, { update => 1, maxrows => 4 });
Modified: p5ee/trunk/App-Repository/t/DBI-select-join.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-select-join.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-select-join.t Wed Sep 1 09:40:16 2010
@@ -1622,9 +1622,9 @@
&check_select($sql,0);
#################################################################################################
-# &drop_table($rep, "test_person");
-# &drop_table($rep, "test_country");
-# &drop_table($rep, "test_city");
-# &drop_table($rep, "test_hotel_prop");
-# &drop_table($rep, "test_hotel_bkg");
+&drop_table($rep, "test_person");
+&drop_table($rep, "test_country");
+&drop_table($rep, "test_city");
+&drop_table($rep, "test_hotel_prop");
+&drop_table($rep, "test_hotel_bkg");
