mvhub-dev team mailing list archive
-
mvhub-dev team
-
Mailing list archive
-
Message #00008
[Branch ~mvhub-dev/mvhub/trunk] Rev 349: All queries from Guide.pm was moved to sql_select.lib and the branch is
Merge authors:
Priya Ravindran (priya)
Sijing (sshen)
Related merge proposals:
https://code.launchpad.net/~priya/mvhub/move_sql_into_sql_library/+merge/21562
proposed by: Priya Ravindran (priya)
review: Approve - Dan MacNeil (omacneil)
------------------------------------------------------------
revno: 349 [merge]
committer: Priya Ravindran <pthirumal@xxxxxxxxxx>
branch nick: trunk
timestamp: Tue 2010-03-23 12:57:19 -0400
message:
All queries from Guide.pm was moved to sql_select.lib and the branch is
merged with trunk
modified:
app-mvhub/DocumentRoot/cgi-bin/mvhub/program.pl
app-mvhub/conf/sql_select.lib
app-mvhub/t/sql_lib_sanity.t
lib-mvhub/lib/MVHub/Guide.pm
lib-mvhub/lib/MVHub/Utils/DB.pm
lib-mvhub/t/perltidy.t
--
lp:mvhub
https://code.launchpad.net/~mvhub-dev/mvhub/trunk
Your team mvhub-dev is subscribed to branch lp:mvhub.
To unsubscribe from this branch go to https://code.launchpad.net/~mvhub-dev/mvhub/trunk/+edit-subscription.
=== modified file 'app-mvhub/DocumentRoot/cgi-bin/mvhub/program.pl'
--- app-mvhub/DocumentRoot/cgi-bin/mvhub/program.pl 2010-03-09 18:49:56 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/program.pl 2010-03-17 14:33:14 +0000
@@ -367,7 +367,7 @@
);
my $sql = MVHub::Utils::DB::get_sql_select_statement(
- 'PROGRAM_LAST_UPDATED_BY_PROGRAM_ID');
+ 'PROGRAM_X_LAST_UPDATED');
my $date_created
= ( $dbh->selectrow_array( $sql, {}, $values_href->{'program_id'} ) )
=== modified file 'app-mvhub/conf/sql_select.lib'
--- app-mvhub/conf/sql_select.lib 2010-03-07 01:34:22 +0000
+++ app-mvhub/conf/sql_select.lib 2010-03-17 14:09:50 +0000
@@ -2,17 +2,48 @@
# LICENSE: AGPLv3
# CONTACT: joe@xxxxxxxxxx
-# Queries should be named
-# tablename_tablename_description
-# they should be in the file in alphabetical order
-
-[AGENCY_ALL]
+
+# queries should be named with this convention
+
+ # TABLE_NAME_TABLE_NAME_x_DESCRIPTION_OF_RETURNED_VALUES
+
+# The goals of this convention are:
+
+# When queries are in this file sorted
+# alphabetically, duplicate queries will
+# jump out
+
+# You should have a general idea of
+# what the query does by looking at the name
+
+# Keep query names relatively brief
+# It is pointless to the query name be the same
+# as the query sql
+
+# We deliberately do not include WHERE clause
+# info in the query name. Where clause
+# info will be relatively obvious from the names of
+# the variables passed in as placeholders to calls to DBI routines
+# example:
+# $dbi->selectallsomething('PROGRAM_X_LAST_UPDATED]',($progam _id);
+
+[AGENCY_PROGRAM_X_AGENCY_NAME_AGENCY_ALIAS]
+SELECT
+ a.agency_name, a.agency_alias, p.*
+FROM
+ agency a, program p
+WHERE
+ p.program_id = ?
+AND
+ a.agency_id = p.agency_id
+
+[AGENCY_X_ALL]
SELECT
*
FROM
agency
-[AGENCY_ALL_PUBLIC]
+[AGENCY_X_ALL_PUBLIC]
SELECT
agency_id, last_updated, hidden,
agency_name, agency_alias, address1,
@@ -27,27 +58,73 @@
staff_internet_access, contact_email_available, tty_tdd
FROM
agency
+
+[AGENCY_X_WEBSITE]
+SELECT
+ website
+FROM
+ agency
+WHERE
+ agency_id = ?
-[HEADING_NAMES_WITH_PROGRAMS]
- SELECT DISTINCT
- hc.heading_name
+[CATEGORY_PROGRAM_VIEW_HEADING_CATEGORY_X_ID_NAME_COUNT]
+SELECT
+ c.category_id, c.category_name, c.program_count
+FROM
+ cats_with_visible_programs_view c, heading_category hc
+WHERE
+ c.category_id = hc.category_id
+AND
+ hc.heading_name = ?
+ORDER BY
+ c.category_name
+
+[CATEGORY_X_CATEGORY_NAME]
+SELECT
+ category_name
+FROM
+ category
+WHERE
+ category_id = ?
+
+[PROGRAM_AGENCY_PROGRAM_CATEGORY_X_AGENCY_NAME_ID_PROGRAM_NAME]
+ SELECT
+ a.agency_name, p.program_id, p.program_name,
+ p.program_alias, p.address1, p.address2,
+ p.city, p.state, p.zip, p.main_phone,
+ p.short_program_description
FROM
- heading_category hc,
- program_category pc
+ program p, agency a, program_category pc
WHERE
- hc.category_id =pc.category_id
+ p.program_id = pc.program_id
+ AND pc.category_id = ?
+ AND a.agency_id = p.agency_id
+ AND p.hidden_reason IS NULL
+ ORDER BY
+ p.program_name
-[PROGRAM_LAST_UPDATED_BY_PROGRAM_ID]
+[PROGRAM_CATEGORY_HEADING_CATEGORY_X_HEADING_NAME]
+SELECT DISTINCT
+ hc.heading_name
+FROM
+ heading_category hc,
+ program_category pc
+WHERE
+ hc.category_id =pc.category_id
+
+[PROGRAM_X_LAST_UPDATED]
SELECT
last_updated
FROM
program
WHERE program_id = ?
-[PROGRAM_WEBSITE_BY_PROGRAM_ID]
+
+[PROGRAM_X_WEBSITE]
SELECT
website
FROM
program
WHERE
- program_id = ?
+ program_id = ?
+
=== modified file 'app-mvhub/t/sql_lib_sanity.t'
--- app-mvhub/t/sql_lib_sanity.t 2009-12-18 18:09:42 +0000
+++ app-mvhub/t/sql_lib_sanity.t 2010-03-17 14:09:50 +0000
@@ -37,8 +37,16 @@
sub get_test_placeholders {
my %data = (
- 'PROGRAM_WEBSITE_BY_PROGRAM_ID' => [509548],
- 'PROGRAM_LAST_UPDATED_BY_PROGRAM_ID' => [509548],
+ 'AGENCY_X_WEBSITE' => [103553],
+ 'AGENCY_PROGRAM_X_AGENCY_NAME_AGENCY_ALIAS' => [510296],
+ 'CATEGORY_PROGRAM_VIEW_HEADING_CATEGORY_X_ID_NAME_COUNT' =>
+ ['Arts/Culture/Entertainment'],
+ 'CATEGORY_X_CATEGORY_NAME' => [800363],
+ 'PROGRAM_AGENCY_PROGRAM_CATEGORY_X_AGENCY_NAME_ID_PROGRAM_NAME' =>
+ [800363],
+ 'PROGRAM_X_WEBSITE' => [509548],
+ 'PROGRAM_X_LAST_UPDATED' => [509548],
+
);
return \%data;
}
@@ -79,7 +87,8 @@
foreach my $statement_name ( $sql_lib->elements() ) {
my $sql = $sql_lib->retr($statement_name);
next if $sql !~ /\?/xmgs;
-
+ die "$statement_name is not in our test data"
+ if not defined $$placeholders_href{$statement_name};
my $placeholders_aref = $$placeholders_href{$statement_name};
ok( $dbh->do( $sql, {}, @$placeholders_aref ),
"do: $statement_name" );
=== modified file 'lib-mvhub/lib/MVHub/Guide.pm'
--- lib-mvhub/lib/MVHub/Guide.pm 2009-12-30 01:47:14 +0000
+++ lib-mvhub/lib/MVHub/Guide.pm 2010-03-17 14:09:50 +0000
@@ -73,7 +73,8 @@
my $self = shift;
my $dbh = $self->dbh();
- my $sql = $self->retr_select_sql('HEADING_NAMES_WITH_PROGRAMS');
+ my $sql = $self->retr_select_sql(
+ 'PROGRAM_CATEGORY_HEADING_CATEGORY_X_HEADING_NAME');
my $headings_aref = $dbh->selectcol_arrayref($sql);
my $html = << "HTML";
@@ -116,15 +117,11 @@
}
my $dbh = $self->dbh();
- my $sql
- = "SELECT c.category_id, c.category_name, c.program_count "
- . "FROM cats_with_visible_programs_view c, heading_category hc "
- . "WHERE c.category_id = hc.category_id "
- . "AND hc.heading_name = "
- . $dbh->quote($heading_name)
- . " ORDER BY c.category_name";
- my $categories_aref = $dbh->selectall_arrayref( $sql, { Slice => {} } );
+ my $sql = $self->retr_select_sql(
+ "CATEGORY_PROGRAM_VIEW_HEADING_CATEGORY_X_ID_NAME_COUNT");
+ my $categories_aref
+ = $dbh->selectall_arrayref( $sql, { Slice => {} }, $heading_name );
# 0 rows returned might or might not be a bug. So we don't test for it.
@@ -185,11 +182,11 @@
# return an error unless one of these is defined.
# use program_id if both are defined.
if ( defined $program_id ) {
- $sql = "SELECT website FROM program WHERE program_id = ?";
+ $sql = $self->retr_select_sql("PROGRAM_X_WEBSITE");
$url_aref = $dbh->selectrow_arrayref( $sql, undef, $program_id );
}
elsif ( defined $agency_id ) {
- $sql = "SELECT website FROM agency WHERE agency_id = ?";
+ $sql = $self->retr_select_sql("AGENCY_X_WEBSITE");
$url_aref = $dbh->selectrow_arrayref( $sql, undef, $agency_id );
}
else {
@@ -342,25 +339,11 @@
indicate whether the source is a bot.'
);
- my $dbh = $self->dbh();
- my $cat_id = $dbh->quote($category_id);
- my $sql = << "SQL";
- SELECT
- a.agency_name, p.program_id, p.program_name,
- p.program_alias, p.address1, p.address2,
- p.city, p.state, p.zip, p.main_phone,
- p.short_program_description
- FROM
- program p, agency a, program_category pc
- WHERE
- p.program_id = pc.program_id
- AND pc.category_id = $cat_id
- AND a.agency_id = p.agency_id
- AND p.hidden_reason IS NULL
- ORDER BY p.program_name;
-SQL
-
- my $programs_aref = $dbh->selectall_arrayref( $sql, { Slice => {} } );
+ my $dbh = $self->dbh();
+ my $sql = $self->retr_select_sql(
+ "PROGRAM_AGENCY_PROGRAM_CATEGORY_X_AGENCY_NAME_ID_PROGRAM_NAME");
+ my $programs_aref
+ = $dbh->selectall_arrayref( $sql, { Slice => {} }, $category_id );
$programs_aref = _add_program_urls_to($programs_aref);
# We should never have been called if there aren't any programs for this
@@ -392,11 +375,10 @@
$tmpl->param( $PR_PROGRAMS_LOOP_TAG => $programs_aref );
# Get the category name to show off in the result page.
- my $sql2
- = 'SELECT category_name FROM category '
- . 'WHERE category_id = '
- . $dbh->quote($category_id);
- my $category_name = @{ $dbh->selectrow_arrayref($sql2) }[0];
+
+ my $sql2 = $self->retr_select_sql("CATEGORY_X_CATEGORY_NAME");
+ my $category_name
+ = @{ $dbh->selectrow_arrayref( $sql2, undef, $category_id ) }[0];
$tmpl->param( $PR_PROGRAMS_LOOP_TAG => $programs_aref );
$tmpl->param( $PR_CRITERIA_TAG => $category_name );
@@ -487,12 +469,9 @@
my $dbh = $self->dbh();
my $sql
- = "SELECT a.agency_name, a.agency_alias, p.* FROM agency a, program p "
- . "WHERE p.program_id = "
- . $dbh->quote($program_id)
- . " AND a.agency_id = p.agency_id";
-
- my $programs_aref = $dbh->selectall_arrayref( $sql, { Slice => {} } );
+ = $self->retr_select_sql("AGENCY_PROGRAM_X_AGENCY_NAME_AGENCY_ALIAS");
+ my $programs_aref
+ = $dbh->selectall_arrayref( $sql, { Slice => {} }, $program_id );
exit_if_entity_count_not_one( 'program', scalar @$programs_aref );
=== modified file 'lib-mvhub/lib/MVHub/Utils/DB.pm'
--- lib-mvhub/lib/MVHub/Utils/DB.pm 2010-01-25 21:33:33 +0000
+++ lib-mvhub/lib/MVHub/Utils/DB.pm 2010-03-17 14:33:14 +0000
@@ -6,7 +6,10 @@
use Carp;
use DBI;
+
use MVHub::Utils qw/assert undelimit_field /;
+use SQL::Library;
+
use base 'Exporter';
our @EXPORT_OK = qw(
@@ -158,7 +161,9 @@
= "/var/www/mvhub/$ENV{USER}/link-to-live-code/app-mvhub/conf/sql_select.lib";
my $sql_lib = new SQL::Library { lib => $file };
- return $sql_lib->retr($statement_name);
+ my $result = $sql_lib->retr($statement_name)
+ or croak "Couldn't get sql for $statement_name";
+ return $result;
}
#################################
=== modified file 'lib-mvhub/t/perltidy.t'
--- lib-mvhub/t/perltidy.t 2009-09-15 01:12:17 +0000
+++ lib-mvhub/t/perltidy.t 2010-03-12 15:21:20 +0000
@@ -43,7 +43,7 @@
);
- if (!ok(no_difference( $file, $destination ),
+ if (!ok(TestHelper::no_difference( $file, $destination ),
"$file is all perltidy "
)
)
