mvhub-dev team mailing list archive
-
mvhub-dev team
-
Mailing list archive
-
Message #00437
[Merge] lp:~saroun-sek/mvhub/sql_change into lp:mvhub
Saroun Sek has proposed merging lp:~saroun-sek/mvhub/sql_change into lp:mvhub.
Requested reviews:
MVHub devs with commit rights (mvhub-commit)
Related bugs:
#396354 [f] refactor SQL into SQL::LIbrary
https://bugs.launchpad.net/bugs/396354
--
https://code.launchpad.net/~saroun-sek/mvhub/sql_change/+merge/29593
Your team MVHub Developers is subscribed to branch lp:mvhub.
=== modified file 'app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/structure.pl'
--- app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/structure.pl 2010-06-29 19:21:04 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/structure.pl 2010-07-09 17:46:42 +0000
@@ -300,11 +300,14 @@
my $category_id = shift;
my $dbh = MVHub::Utils::DB::get_dbh();
- my $category_href = MVHub::Utils::DB::select_unique_record(
- table => 'category',
- where_href => { category_id => $category_id }
- );
- my $category_name = $category_href->{category_name};
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'CATEGORY_X_CATEGORY_NAME');
+ my @bind_values = ($category_id);
+
+ my @result
+ = @{ $dbh->selectall_arrayref( $sql, { Slice => {} }, @bind_values )
+ };
+ my $category_name = $result[0]->{category_name};
my $sql
= MVHub::Utils::DB::get_sql_select_statement('ALIAS_X_ALIAS_NAME');
=== modified file 'app-mvhub/conf/sql_select.lib'
--- app-mvhub/conf/sql_select.lib 2010-07-03 16:28:30 +0000
+++ app-mvhub/conf/sql_select.lib 2010-07-09 17:46:42 +0000
@@ -678,6 +678,24 @@
WHERE LOWER(program_name) = ?
AND agency_id = ?
+[PROGRAM_X_AGENCY_ID]
+SELECT program_id, program_name, agency_id, last_updated,
+ contact_first_name, contact_last_name, hidden_reason
+FROM program
+WHERE agency_id = ?
+ORDER BY program_name
+
+[PROGRAM_X_AGENCY_ID_HIDDEN_REASON_IS_NULL]
+SELECT program_id, program_name, program_alias
+FROM program
+WHERE agency_id = ? AND hidden_reason IS NULL
+ORDER BY program_name
+
+[PROGRAM_X_AGENCY_PROGRAM_ID]
+SELECT *
+FROM program
+WHERE agency_id = ? AND program_id = ?
+
[PROGRAM_X_COUNT_ALL]
SELECT COUNT(*)
FROM program
=== modified file 'app-mvhub/t/sql_lib_sanity.t'
--- app-mvhub/t/sql_lib_sanity.t 2010-06-29 19:21:04 +0000
+++ app-mvhub/t/sql_lib_sanity.t 2010-07-09 17:46:42 +0000
@@ -86,8 +86,11 @@
'PROGRAM_CATEGORY_X_CATEGORY_ID' => [103553],
'PROGRAM_ID_X_PROGRAM_NAME' => [103553],
'PROGRAM_LOWER_PROGRAM_NAME_ID_X_COUNT' => [ 'name', 103553, 103553 ],
- 'PROGRAM_LOWER_PROGRAM_NAME_X_COUNT' => [ 'name', 103553 ],
- 'PROGRAM_X_COUNT_ALL' => [103553],
+ 'PROGRAM_LOWER_PROGRAM_NAME_X_COUNT' => [ 'name', 103553 ],
+ 'PROGRAM_X_AGENCY_ID' => [103553],
+ 'PROGRAM_X_AGENCY_ID_HIDDEN_REASON_IS_NULL' => [103553],
+ 'PROGRAM_X_AGENCY_PROGRAM_ID' => [ 103553, 509548 ],
+ 'PROGRAM_X_COUNT_ALL' => [103553],
'PROGRAM_X_DISTINCT_PROGRAM_CONTACT_FIRST_LAST_NAME_EMAIL' =>
[ '103553', 'fname', 'lname', 'a@xxxxx' ],
'PROGRAM_X_EXPIRED_RECORDS' => [ '01-01-2008', 0 ],
=== modified file 'lib-mvhub/lib/MVHub/AgencyAccount.pm'
--- lib-mvhub/lib/MVHub/AgencyAccount.pm 2010-07-09 16:17:31 +0000
+++ lib-mvhub/lib/MVHub/AgencyAccount.pm 2010-07-09 17:46:42 +0000
@@ -410,14 +410,18 @@
expiration_duration => "$expire_months months",
);
- my $programs_aref = MVHub::Utils::DB::select_agency_programs(
- dbh => $dbh,
- agency_id => $agency_id,
- columns_aref => [
- qw(program_id program_name agency_id last_updated contact_first_name
- contact_last_name hidden_reason)
- ]
- );
+ my @bind_variables = ($agency_id);
+ my $sql
+ = MVHub::Utils::DB::get_sql_select_statement('PROGRAM_X_AGENCY_ID');
+
+ my $programs_aref
+ = $dbh->selectall_arrayref( $sql, { Slice => {} }, @bind_variables );
+
+ foreach my $record_href (@$programs_aref) {
+ $record_href->{'last_updated'}
+ = MVHub::Utils::yyyy_mm_dd_to_mm_dd_yyyy(
+ $record_href->{'last_updated'} );
+ }
if ( scalar @$programs_aref ) {
=== modified file 'lib-mvhub/lib/MVHub/Guide.pm'
--- lib-mvhub/lib/MVHub/Guide.pm 2010-05-24 19:44:00 +0000
+++ lib-mvhub/lib/MVHub/Guide.pm 2010-07-09 17:46:42 +0000
@@ -457,12 +457,19 @@
$tmpl->param( $_, $agency_href->{$_} );
}
- my $programs_aref = MVHub::Utils::DB::select_agency_programs(
- dbh => $dbh,
- agency_id => $agency_id,
- columns_aref => [qw(program_id program_name program_alias)],
- show_hidden => 0
- );
+ my @bind_variables = ($agency_id);
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'PROGRAM_X_AGENCY_ID_HIDDEN_REASON_IS_NULL');
+
+ my $programs_aref
+ = $dbh->selectall_arrayref( $sql, { Slice => {} }, @bind_variables );
+
+ foreach my $record_href (@$programs_aref) {
+ $record_href->{'last_updated'}
+ = MVHub::Utils::yyyy_mm_dd_to_mm_dd_yyyy(
+ $record_href->{'last_updated'} );
+ }
+
if ( scalar @$programs_aref ) {
$tmpl->param( $SA_PROGRAMS_LOOP_TAG, $programs_aref );
}
=== modified file 'lib-mvhub/lib/MVHub/Utils/DB.pm'
--- lib-mvhub/lib/MVHub/Utils/DB.pm 2010-06-29 20:24:48 +0000
+++ lib-mvhub/lib/MVHub/Utils/DB.pm 2010-07-09 17:46:42 +0000
@@ -25,13 +25,11 @@
get_sql_insert_statement
get_sql_select_statement
get_sql_update_statement
+ get_sql_delete_statement
insert_one_record
remove_agency
- select_agency_programs
select_agency_record
select_program_record
- select_records
- select_unique_record
update_one_record
);
@@ -250,42 +248,6 @@
$dbh->do( $sql, undef, @bind_variables );
}
-# Returns programs for the given agency ordered by program_name
-# Date fields are converted to American format
-# Can optionally request specific fields, otherwise you get all of them.
-sub select_agency_programs {
- my %args = @_;
- my $dbh = delete $args{dbh}; # required
- my $agency_id = delete $args{agency_id}; # required
- my $columns_aref = delete $args{columns_aref}; # optional
- my $show_hidden = delete $args{show_hidden}; # optional
- MVHub::Utils::assert( ( scalar keys %args ) == 0,
- "Unknown arguments: @{[%args]}" );
-
- if ( !defined $show_hidden ) { $show_hidden = 1; }
-
- my $where_string = "agency_id = " . $dbh->quote($agency_id);
- if ( !$show_hidden ) {
- $where_string .= " AND hidden_reason IS NULL";
- }
-
- my $records_aref = select_records(
- dbh => $dbh,
- columns_aref => $columns_aref,
- table => 'program',
- where_string => $where_string,
- order_by => 'program_name'
- );
-
- foreach my $record_href (@$records_aref) {
- $record_href->{'last_updated'}
- = MVHub::Utils::yyyy_mm_dd_to_mm_dd_yyyy(
- $record_href->{'last_updated'} );
- }
-
- return $records_aref;
-}
-
# Return a hashref of agency data for the given agency id.
# If the given agency id does not exist in the database, and
# the allow_empty_result argument is:
@@ -334,14 +296,14 @@
my $agency_id = shift;
my $program_id = shift;
- my $record_href = select_unique_record(
- dbh => $dbh,
- table => 'program',
- where_href => {
- 'agency_id' => $agency_id,
- 'program_id' => $program_id
- }
- );
+ my @bind_values = ( $agency_id, $program_id );
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'PROGRAM_X_AGENCY_PROGRAM_ID');
+
+ my @result
+ = @{ $dbh->selectall_arrayref( $sql, { Slice => {} }, @bind_values )
+ };
+ my $record_href = $result[0];
# Make the date more readable
@@ -363,69 +325,6 @@
return $record_href;
}
-# Returns an array of rows from the given 'table', with the given database handle.
-# Each row is hashref whose keys are the column names in the given 'columns_aref'.
-#
-# If columns_aref is not passed in, all columns will be fetched.
-# A WHERE expression can be provided either with:
-# where_href - a hashref of columns to values that will be tested for equality OR
-# where_string - any WHERE clause (sans the 'WHERE')
-# The other optional args are self-explanatory
-sub select_records {
- my %args = @_;
- my $dbh = $args{dbh}; #req
- my $table = $args{table}; #req
- my $columns_aref = $args{columns_aref}; #req
- my $where_href = $args{where_href}; #opt
- my $where_string = $args{where_string}; #opt
- my $expected_row_count = $args{expected_row_count}; #opt
- my $order_by = $args{order_by}; #opt
-
- my $columns = '*';
- if ( defined $columns_aref && @$columns_aref ) {
- $columns = join( ', ', @$columns_aref );
- }
- my $sql = "SELECT $columns FROM $table ";
-
- if ( defined $where_href && %$where_href ) {
- my @where_cols = keys %$where_href;
- for ( my $i = 0; $i < @where_cols; $i++ ) {
- $sql .= ( $i == 0 ? 'WHERE ' : ' AND ' );
- my $col = $where_cols[$i];
- my $val = $dbh->quote( $$where_href{$col} );
- $sql .= "$col = $val";
- }
- }
- elsif ($where_string) {
- $sql .= "WHERE $where_string ";
- }
-
- if ( defined $order_by ) {
- $sql .= "ORDER BY $order_by ";
- }
-
- my $records_aref = $dbh->selectall_arrayref( $sql, { Slice => {} } );
-
- if ( defined $expected_row_count ) {
- MVHub::Utils::assert(
- @$records_aref == $expected_row_count,
- "Expected $expected_row_count rows selected for statement $sql; "
- . "got "
- . scalar @$records_aref
- );
- }
- return $records_aref;
-}
-
-# Returns a single row from the given 'table' with the given database handle,
-# as a hashref whose keys are the column names in the given 'columns_aref'.
-# See select_records for details on how to specify the query.
-# Dies if the returned row count is not 1.
-sub select_unique_record {
- my $records_aref = select_records( @_, expected_row_count => 1 );
- return $records_aref->[0];
-}
-
# Update a unique record in the given database table.
# Arguments:
# dbh - a database handle.
@@ -585,6 +484,14 @@
is: app-mvhub/conf/sql_update.lib, though you should not depend
on this location and you should not need to depend on this location
as finding the SQL::Library file is handled internally for you.
+
+=head2 get_sql_delete_statement
+
+ Given the name of a sql DELETE statement, returns the SQL for that
+ statement. Currently the file the SQL statements are stored in
+ is: app-mvhub/conf/sql_delete.lib, though you should not depend
+ on this location and you should not need to depend on this location
+ as finding the SQL::Library file is handled internally for you.
=head2 insert_one_record
@@ -594,10 +501,6 @@
TODO
-=head2 select_agency_programs
-
- TODO
-
=head2 select_agency_record
TODO
@@ -606,14 +509,6 @@
TODO
-=head2 select_records
-
- TODO
-
-=head2 select_unique_record
-
- TODO
-
=head2 update_one_record
TODO
Follow ups
