mvhub-dev team mailing list archive
-
mvhub-dev team
-
Mailing list archive
-
Message #00314
[Branch ~mvhub-commit/mvhub/trunk] Rev 418: merged saroun-sek/mvhub/sql_change
Merge authors:
Saroun Sek <ssek@xxxxxxxxxx>
Related merge proposals:
https://code.launchpad.net/~saroun-sek/mvhub/sql_change/+merge/27870
proposed by: Saroun Sek (saroun-sek)
review: Approve - Dan MacNeil (omacneil)
------------------------------------------------------------
revno: 418 [merge]
committer: Dan MacNeil <dan@xxxxxxxxxx>
branch nick: trunk
timestamp: Sat 2010-06-19 15:50:00 -0400
message:
merged saroun-sek/mvhub/sql_change
modified:
app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/structure.pl
app-mvhub/DocumentRoot/cgi-bin/mvhub/agency_form.pl
app-mvhub/DocumentRoot/cgi-bin/mvhub/program.pl
app-mvhub/bin/generate_agency_program_pdf.pl
app-mvhub/bin/mv_dump.pl
app-mvhub/bin/welcome_email.pl
app-mvhub/conf/sql_select.lib
app-mvhub/t/sql_lib_sanity.t
--
lp:mvhub
https://code.launchpad.net/~mvhub-commit/mvhub/trunk
Your team MVHub Developers is subscribed to branch lp:mvhub.
To unsubscribe from this branch go to https://code.launchpad.net/~mvhub-commit/mvhub/trunk/+edit-subscription
=== modified file 'app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/structure.pl'
--- app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/structure.pl 2009-12-18 18:09:42 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/structure.pl 2010-06-17 18:46:44 +0000
@@ -270,7 +270,8 @@
# [ "Arts/Culture/Entertainment", "Financial Education/Credit", ... ]
sub get_all_headings {
my $dbh = MVHub::Utils::DB::get_dbh();
- my $sql = 'SELECT heading_name FROM heading ORDER BY heading_name';
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'HEADING_X_HEADING_NAME');
my $headings_aref = $dbh->selectcol_arrayref($sql);
return $headings_aref;
}
@@ -284,10 +285,9 @@
sub get_all_programs {
my $dbh = MVHub::Utils::DB::get_dbh();
- my $sql = "SELECT program_id,
- get_agency_name(agency_id) || ': ' || program_name
- AS agency_program_name
- FROM program ORDER BY agency_program_name";
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'PROGRAM_X_PROGRAM_AGENCY_ID');
+
my $result_aref = $dbh->selectall_arrayref($sql);
# see end of 'perldoc -f map' on why the '+' in the following:
@@ -306,23 +306,23 @@
);
my $category_name = $category_href->{category_name};
+ my $sql
+ = MVHub::Utils::DB::get_sql_select_statement('ALIAS_X_ALIAS_NAME');
my $aliases_aref
- = $dbh->selectcol_arrayref(
- 'SELECT alias_name FROM alias WHERE category_id = ?',
- undef, $category_id );
+ = $dbh->selectcol_arrayref( $sql, { Slice => {} }, $category_id );
my $aliases = join( "\n", @$aliases_aref );
+ $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'HEADING_CATEGORY_X_HEADING_NAME');
my $headings_aref
- = $dbh->selectcol_arrayref(
- 'SELECT heading_name FROM heading_category WHERE category_id = ?',
- undef, $category_id );
+ = $dbh->selectcol_arrayref( $sql, { Slice => {} }, $category_id );
assert( @$headings_aref,
"Expected at least one heading for this category (#$category_id)" );
+ $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'PROGRAM_CATEGORY_X_PROGRAM_ID');
my $programs_aref
- = $dbh->selectcol_arrayref(
- 'SELECT program_id FROM program_category WHERE category_id = ?',
- undef, $category_id );
+ = $dbh->selectcol_arrayref( $sql, { Slice => {} }, $category_id );
return {
category_name => $category_name,
aliases => $aliases,
=== modified file 'app-mvhub/DocumentRoot/cgi-bin/mvhub/agency_form.pl'
--- app-mvhub/DocumentRoot/cgi-bin/mvhub/agency_form.pl 2009-12-29 17:26:17 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/agency_form.pl 2010-06-17 18:46:44 +0000
@@ -249,7 +249,8 @@
# Get "last updated" date from production agency table so we can insert it into
# date_agency_created.
- my $sql = "SELECT last_updated FROM agency WHERE agency_id = ?";
+ my $sql
+ = MVHub::Utils::DB::get_sql_select_statement('AGENCY_X_LAST_UPDATED');
my $date_created = ( $dbh->selectrow_array( $sql, {}, $agency_id ) )[0];
# Now that record's inserted into production table, add entry to date_agency_created table
=== modified file 'app-mvhub/DocumentRoot/cgi-bin/mvhub/program.pl'
--- app-mvhub/DocumentRoot/cgi-bin/mvhub/program.pl 2010-03-31 16:11:24 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/program.pl 2010-06-17 18:46:44 +0000
@@ -471,10 +471,8 @@
my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
- my $stmt
- = "SELECT h.heading_name, c.category_id, c.category_name "
- . "FROM category c, heading_category h "
- . "WHERE h.category_id = c.category_id";
+ my $stmt = MVHub::Utils::DB::get_sql_select_statement(
+ 'CATEGORY_HEADING_CATEGORY_X_HEADING_CATEGORY_NAME');
my $categories_aref = $dbh->selectall_arrayref( $stmt, { Slice => {} } );
@@ -1164,8 +1162,7 @@
# the town table.
sub get_selectable_towns_from_db {
my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
- my $sql = "SELECT town_state FROM town WHERE display = TRUE "
- . "ORDER BY town_state";
+ my $sql = MVHub::Utils::DB::get_sql_select_statement('TOWN_X_TOWN_STATE');
my $towns_aref = $dbh->selectcol_arrayref($sql);
MVHub::Utils::assert( @$towns_aref > 0, "Expected at least one town." );
return $towns_aref;
=== modified file 'app-mvhub/bin/generate_agency_program_pdf.pl'
--- app-mvhub/bin/generate_agency_program_pdf.pl 2010-02-08 22:13:12 +0000
+++ app-mvhub/bin/generate_agency_program_pdf.pl 2010-06-17 18:46:44 +0000
@@ -74,11 +74,9 @@
my $dbh = MVHub::Utils::DB::get_dbh($config_file);
- my $sql = <<'SQL';
- SELECT heading_name
- FROM heading
- ORDER BY heading_name
-SQL
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'HEADING_X_HEADING_NAME');
+
my $headings_aref = $dbh->selectcol_arrayref($sql);
# keys are entry ids
@@ -271,29 +269,9 @@
my $heading = shift or croak 'missing Parameter:$heading';
$heading = MVHub::GeneratePDF::unclean($heading);
- my $sql = << 'SQL';
- SELECT
- program.*, agency_name
- FROM
- program JOIN agency USING ( agency_id )
- WHERE
- agency_id IN (
- SELECT a.agency_id
- FROM agency a, program p
- WHERE a.agency_id = p.agency_id
- AND p.hidden_reason IS NULL
- GROUP BY a.agency_id
- HAVING sum(1) >1
- )
- AND program_id IN (
- SELECT program_id
- FROM program_category
- NATURAL JOIN heading_category
- WHERE heading_name = ?
- )
- AND program.hidden_reason IS NULL
- AND NOT agency.hidden
-SQL
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'PROGRAM_AGENCY_X_PROGRAM_AGENCY_NAME');
+
return $dbh->selectall_arrayref( $sql, { Slice => {} }, ($heading) );
}
@@ -302,37 +280,8 @@
my $heading = shift or croak 'missing Parameter:$heading';
$heading = MVHub::GeneratePDF::unclean($heading);
- my $sql = <<'SQL';
- SELECT
- agency.*
- FROM
- agency
- WHERE
- agency_id IN (
- SELECT
- agency_id
- FROM agency
- JOIN program
- USING ( agency_id )
- WHERE hidden_reason IS NULL
- GROUP BY agency_id
- HAVING sum(1) >1
- )
- AND agency_id IN
- (
- SELECT agency_id
- FROM agency
- JOIN program
- USING ( agency_id )
- JOIN program_category
- USING ( program_id )
- JOIN heading_category
- USING ( category_id )
- WHERE hidden_reason IS NULL
- AND heading_name = ?
- )
- AND NOT agency.hidden
-SQL
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'AGENCY_HEADING_PROGRAM_X_AGENCY_ID');
return $dbh->selectall_arrayref( $sql, { Slice => {} }, ($heading) );
@@ -343,34 +292,9 @@
my $heading = shift or croak 'missing Parameter:$heading';
$heading = MVHub::GeneratePDF::unclean($heading);
- my $sql = << "SQL";
- SELECT
- agency.*
- FROM
- agency
- WHERE agency_id IN (
- SELECT agency_id
- FROM agency
- JOIN program
- USING ( agency_id )
- WHERE hidden_reason IS NULL
- GROUP BY agency_id
- HAVING sum(1) = 1
- )
- AND agency_id IN (
- SELECT agency_id
- FROM agency
- JOIN program
- USING ( agency_id )
- JOIN program_category
- USING ( program_id )
- JOIN heading_category
- USING ( category_id )
- WHERE hidden_reason IS NULL
- AND heading_name = ?
- )
- AND NOT agency.hidden
-SQL
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'AGENCY_HEADING_PROGRAM_X_AGENCY_ID_EQUAL');
+
my $result_aref
= $dbh->selectall_arrayref( $sql, { Slice => {} }, ($heading) );
return $result_aref;
@@ -380,21 +304,9 @@
sub get_agencies_without_programs {
my $dbh = shift or croak 'missing Parameter:$dbh';
- my $sql = <<'SQL';
- SELECT agency.*
- FROM agency
- WHERE agency_id NOT IN (
- SELECT agency_id
- FROM program
- )
- OR agency_id IN (
- SELECT agency_id
- FROM program
- WHERE hidden_reason IS NULL
- AND program_id NOT IN (SELECT program_id FROM program_category)
- )
- AND NOT agency.hidden
-SQL
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'AGENCY_PROGRAM_X_AGENCY_ID');
+
return $dbh->selectall_arrayref( $sql, { Slice => {} } );
}
@@ -407,9 +319,11 @@
my $dbh = shift;
# get leads from non-first words in categories to the category
- my $sql = "SELECT category_name FROM category";
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'CATEGORY_X_IN_CATEGORY_NAME');
+
my @categories = @{ $dbh->selectcol_arrayref( $sql, { Slice => {} } ) };
- my $ret = join '', map {
+ my $ret = join '', map {
my $category = MVHub::GeneratePDF::clean($_);
my @words = split( /\W/, $category );
$category =~ s/,.*//; # just point to the top level
@@ -420,8 +334,9 @@
# get leads from aliases to categories
# and get leads from non-first words in aliases to categories
- $sql
- = "SELECT alias_name, category_name FROM alias JOIN category USING (category_id)";
+ $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'ALIAS_CATEGORY_X_ALIAS_NAME_CATEGORY');
+
my $aref = $dbh->selectall_arrayref( $sql, { Slice => {} } );
$ret .= join '', map {
my $alias = MVHub::GeneratePDF::clean( $_->{'alias_name'} );
@@ -479,17 +394,13 @@
my $r = shift;
my $dbh = shift;
return () unless $r->{'program_id'};
- my $sql = <<END_SQL
- SELECT category_name
- FROM category
- JOIN program_category
- USING ( category_id )
- WHERE program_id = '$r->{'program_id'}'
-END_SQL
- ;
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'CATEGORY_PROGRAM_X_CATEGORY_NAME');
+ my @bind_variables = ( $r->{'program_id'} );
return
map { MVHub::GeneratePDF::clean($_) }
- @{ $dbh->selectcol_arrayref( $sql, { Slice => {} } ) };
+ @{ $dbh->selectcol_arrayref( $sql, { Slice => {} }, @bind_variables )
+ };
}
sub index_subheading {
@@ -568,13 +479,8 @@
return if $r_href->{'program_id'};
my $agency_id = $r_href->{'agency_id'};
- my $sql = <<END_SQL;
- SELECT *
- FROM agency
- JOIN program
- USING ( agency_id )
- WHERE agency_id = ?
-END_SQL
+ my $sql
+ = MVHub::Utils::DB::get_sql_select_statement('AGENCY_PROGRAM_X_ALL');
my $aref
= $dbh->selectall_arrayref( $sql, { Slice => {} }, ($agency_id) );
@@ -596,11 +502,8 @@
my $dbh = shift;
- my $sql = <<END_SQL
- SELECT *
- FROM agency
-END_SQL
- ;
+ my $sql = MVHub::Utils::DB::get_sql_select_statement('AGENCY_X_ALL');
+
my $aref = $dbh->selectall_arrayref( $sql, { Slice => {} } );
$aref = clean_merge_sort( $aref, [] );
=== modified file 'app-mvhub/bin/mv_dump.pl'
--- app-mvhub/bin/mv_dump.pl 2009-12-18 18:09:42 +0000
+++ app-mvhub/bin/mv_dump.pl 2010-06-17 18:46:44 +0000
@@ -23,7 +23,8 @@
my @columns = get_column_list();
my $columns = join ',', @columns;
- my $sql = "SELECT $columns FROM AGENCY WHERE NOT hidden";
+ my $sql = MVHub::Utils::DB::get_sql_select_statement('AGENCY_X_COLUMNS');
+
my $agencies_rayref = $dbh->selectall_arrayref($sql);
my $csv = Text::xSV->new(
=== modified file 'app-mvhub/bin/welcome_email.pl'
--- app-mvhub/bin/welcome_email.pl 2009-12-07 20:00:37 +0000
+++ app-mvhub/bin/welcome_email.pl 2010-06-17 18:46:44 +0000
@@ -38,7 +38,8 @@
my $dbh = MVHub::Utils::DB::get_dbh();
# Fetch all agencies.
- my $sql = "SELECT * from agency";
+ my $sql = MVHub::Utils::DB::get_sql_select_statement('AGENCY_X_ALL');
+
my $agencies_aref = $dbh->selectall_arrayref( $sql, { Slice => {} } );
# For each agency:
@@ -60,8 +61,9 @@
# GET THE DATA FOR THE AGENCY CONTACT EMAIL
# Get the program names for this agency that ARE owned by the agency contact
- my $sql
- = "SELECT program_name FROM program WHERE agency_id = ? AND contact_first_name = ? AND contact_last_name = ? AND contact_email = ?";
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'PROGRAM_X_PROGRAM_NAME');
+
my @names_of_programs_owned_by_ac = @{
$dbh->selectcol_arrayref(
$sql, undef, $agency_id,
@@ -70,8 +72,9 @@
};
# Get the programs for this agency that are NOT owned by the agency contact
- $sql
- = 'SELECT program_name, contact_first_name, contact_last_name, contact_email FROM program WHERE agency_id = ? AND NOT (contact_first_name = ? AND contact_last_name = ? AND contact_email = ?) ORDER BY contact_last_name, contact_first_name, program_name';
+ $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'PROGRAM_X_PROGRAM_CONTACT_FIRST_LAST_NAME_EMAIL');
+
my @programs_owned_by_pcs = @{
$dbh->selectall_arrayref(
$sql, { Slice => {} }, $agency_id,
@@ -93,15 +96,17 @@
next if !@programs_owned_by_pcs;
# Get the program contacts (who are not also the agency contact) for our agency
- $sql
- = 'SELECT DISTINCT contact_first_name, contact_last_name, contact_email FROM program WHERE agency_id = ? AND NOT (contact_first_name = ? AND contact_last_name = ? AND contact_email = ?)';
+ $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'PROGRAM_X_DISTINCT_PROGRAM_CONTACT_FIRST_LAST_NAME_EMAIL');
+
my $pcs_aref = $dbh->selectall_arrayref( $sql, { Slice => {} },
$agency_id, $ac_first_name, $ac_last_name, $ac_email );
# Get the program name(s) owned by this program contact
foreach my $pc_href (@$pcs_aref) {
- $sql
- = 'SELECT program_name FROM program WHERE agency_id = ? AND contact_first_name = ? AND contact_last_name = ? AND contact_email = ?';
+ $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'PROGRAM_X_PROGRAM_NAME');
+
my $program_names_aref = $dbh->selectall_arrayref(
$sql,
{ Slice => {} },
=== modified file 'app-mvhub/conf/sql_select.lib'
--- app-mvhub/conf/sql_select.lib 2010-06-09 14:25:30 +0000
+++ app-mvhub/conf/sql_select.lib 2010-06-19 19:50:00 +0000
@@ -30,6 +30,80 @@
# example:
# $dbi->selectallsomething('PROGRAM_X_LAST_UPDATED]',($progam _id);
+[AGENCY_HEADING_PROGRAM_X_AGENCY_ID]
+SELECT
+ agency.*
+FROM
+ agency
+WHERE
+ agency_id IN (
+ SELECT
+ agency_id
+ FROM agency
+ JOIN program
+ USING ( agency_id )
+ WHERE hidden_reason IS NULL
+ GROUP BY agency_id
+ HAVING sum(1) >1
+ )
+ AND agency_id IN
+ (
+ SELECT agency_id
+ FROM agency
+ JOIN program
+ USING ( agency_id )
+ JOIN program_category
+ USING ( program_id )
+ JOIN heading_category
+ USING ( category_id )
+ WHERE hidden_reason IS NULL
+ AND heading_name = ?
+ )
+ AND NOT agency.hidden
+
+[AGENCY_HEADING_PROGRAM_X_AGENCY_ID_EQUAL]
+ SELECT
+ agency.*
+ FROM
+ agency
+ WHERE agency_id IN (
+ SELECT agency_id
+ FROM agency
+ JOIN program
+ USING ( agency_id )
+ WHERE hidden_reason IS NULL
+ GROUP BY agency_id
+ HAVING sum(1) = 1
+ )
+ AND agency_id IN (
+ SELECT agency_id
+ FROM agency
+ JOIN program
+ USING ( agency_id )
+ JOIN program_category
+ USING ( program_id )
+ JOIN heading_category
+ USING ( category_id )
+ WHERE hidden_reason IS NULL
+ AND heading_name = ?
+ )
+ AND NOT agency.hidden
+
+[AGENCY_PROGRAM_X_AGENCY_ID]
+ SELECT agency.*
+ FROM agency
+ WHERE agency_id NOT IN (
+ SELECT agency_id
+ FROM program
+ )
+ OR agency_id IN (
+ SELECT agency_id
+ FROM program
+ WHERE hidden_reason IS NULL
+ AND program_id NOT IN (SELECT program_id FROM program_category)
+ )
+ AND NOT agency.hidden
+
[AGENCY_PROGRAM_X_AGENCY_NAME_AGENCY_ALIAS]
SELECT
a.agency_name, a.agency_alias, p.*
@@ -40,6 +114,13 @@
AND
a.agency_id = p.agency_id
+[AGENCY_PROGRAM_X_ALL]
+SELECT *
+FROM agency
+ JOIN program
+ USING ( agency_id )
+WHERE agency_id = ?
+
[AGENCY_SEARCH_LOG_X_IF_RECORDED_RECENT_AGENCY_SEARCH_BY_USER]
SELECT
EXISTS
@@ -71,7 +152,7 @@
[AGENCY_X_ALL]
SELECT
*
- FROM
+FROM
agency
@@ -101,6 +182,22 @@
AND
lower(password)=?
+[AGENCY_X_COLUMNS]
+SELECT
+ agency_name, agency_alias, last_updated, agency_id, address1,
+ address2, city, state, zip,
+ main_phone, toll_free_phone, hotline, fax,
+ website, email, mission,hours, other_info,
+ mail_address1, mail_address2,
+ mail_city, mail_state,
+ mail_zip, chief,
+ title, parent_organization,
+ resource_library, resource_library_description,
+ meeting_space, meeting_space_description,
+ staff_internet_access, tty_tdd
+FROM agency
+WHERE NOT hidden
+
[AGENCY_X_EXPIRED_RECORDS]
SELECT
agency_id,
@@ -120,6 +217,11 @@
AND
reminders_sent < ?
+[AGENCY_X_LAST_UPDATED]
+SELECT last_updated
+FROM agency
+WHERE agency_id = ?
+
[AGENCY_X_SEARCH_WITH_TERMS]
SELECT
agency_id, agency_name, agency_alias, address1, address2,
@@ -142,6 +244,16 @@
agency
WHERE
agency_id = ?
+
+[ALIAS_CATEGORY_X_ALIAS_NAME_CATEGORY]
+SELECT alias_name, category_name
+FROM alias
+JOIN category USING (category_id)
+
+[ALIAS_X_ALIAS_NAME]
+SELECT alias_name
+FROM alias
+WHERE category_id = ?
[ALL_NAMES_VIEW_CATS_WITH_VISIBLE_PROGRAMS_VIEW_X_ALL_FOR_SYNONYMS]
SELECT
@@ -154,6 +266,7 @@
WHERE
stems_name ~* ?
+
[CATEGORY_HEADING_CATEGORY_X_FETCH_HEADING_CATEGORY]
SELECT
h.heading_name, c.category_id, c.category_name
@@ -163,6 +276,11 @@
h.category_id = c.category_id
ORDER BY
h.heading_name, c.category_name
+
+[CATEGORY_HEADING_CATEGORY_X_HEADING_CATEGORY_NAME]
+SELECT h.heading_name, c.category_id, c.category_name
+FROM category c, heading_category h
+WHERE h.category_id = c.category_id
[CATEGORY_PROGRAM_VIEW_HEADING_CATEGORY_X_ID_NAME_COUNT]
SELECT
@@ -175,6 +293,13 @@
hc.heading_name = ?
ORDER BY
c.category_name
+
+[CATEGORY_PROGRAM_X_CATEGORY_NAME]
+SELECT category_name
+FROM category
+ JOIN program_category
+ USING ( category_id )
+WHERE program_id = ?
[CATEGORY_SEARCH_LOG_X_IF_RECORDED_RECENT_CATEGORY_SEARCH_BY_USER]
SELECT
@@ -189,7 +314,7 @@
AND
phrase_stems = ?
)
-
+
[CATEGORY_X_CATEGORY_NAME]
SELECT
category_name
@@ -198,6 +323,23 @@
WHERE
category_id = ?
+[CATEGORY_X_IN_CATEGORY_NAME]
+SELECT category_name
+FROM category
+
+[HEADING_CATEGORY_X_HEADING_NAME]
+SELECT heading_name
+FROM heading_category
+WHERE category_id = ?
+
+[HEADING_X_HEADING_NAME]
+SELECT
+ heading_name
+FROM
+ heading
+ORDER BY
+ heading_name
+
[PROGRAM_AGENCY_PROGRAM_CATEGORY_X_AGENCY_NAME_ID_PROGRAM_NAME]
SELECT
a.agency_name, p.program_id, p.program_name,
@@ -214,6 +356,29 @@
ORDER BY
p.program_name
+[PROGRAM_AGENCY_X_PROGRAM_AGENCY_NAME]
+SELECT
+ program.*, agency_name
+FROM
+ program JOIN agency USING ( agency_id )
+WHERE
+ agency_id IN (
+ SELECT a.agency_id
+ FROM agency a, program p
+ WHERE a.agency_id = p.agency_id
+ AND p.hidden_reason IS NULL
+ GROUP BY a.agency_id
+ HAVING sum(1) >1
+ )
+ AND program_id IN (
+ SELECT program_id
+ FROM program_category
+ NATURAL JOIN heading_category
+ WHERE heading_name = ?
+ )
+AND program.hidden_reason IS NULL
+AND NOT agency.hidden
+
[PROGRAM_CATEGORY_HEADING_CATEGORY_X_HEADING_NAME]
SELECT DISTINCT
hc.heading_name
@@ -223,6 +388,18 @@
WHERE
hc.category_id =pc.category_id
+[PROGRAM_CATEGORY_X_PROGRAM_ID]
+SELECT program_id
+FROM program_category
+WHERE category_id = ?
+
+[PROGRAM_X_DISTINCT_PROGRAM_CONTACT_FIRST_LAST_NAME_EMAIL]
+SELECT DISTINCT contact_first_name, contact_last_name, contact_email
+FROM program
+WHERE agency_id = ?
+AND NOT (contact_first_name = ? AND contact_last_name = ? AND contact_email = ?)
+
+
[PROGRAM_X_EXPIRED_RECORDS]
SELECT
a.agency_id,
@@ -250,6 +427,28 @@
program
WHERE program_id = ?
+[PROGRAM_X_PROGRAM_AGENCY_ID]
+SELECT program_id,
+ get_agency_name(agency_id) || ': ' || program_name
+AS agency_program_name
+FROM program ORDER BY agency_program_name
+
+[PROGRAM_X_PROGRAM_CONTACT_FIRST_LAST_NAME_EMAIL]
+SELECT program_name, contact_first_name, contact_last_name, contact_email
+FROM program
+WHERE agency_id = ?
+AND NOT (contact_first_name = ?
+AND contact_last_name = ?
+AND contact_email = ?)
+ORDER BY contact_last_name, contact_first_name, program_name
+
+[PROGRAM_X_PROGRAM_NAME]
+SELECT program_name
+FROM program
+WHERE agency_id = ?
+AND contact_first_name = ?
+AND contact_last_name = ?
+AND contact_email = ?
[PROGRAM_X_WEBSITE]
SELECT
@@ -287,3 +486,8 @@
word_stem
IN (?)
)
+
+[TOWN_X_TOWN_STATE]
+SELECT town_state
+FROM town WHERE display = TRUE
+ORDER BY town_state
=== modified file 'app-mvhub/t/sql_lib_sanity.t'
--- app-mvhub/t/sql_lib_sanity.t 2010-04-27 13:50:58 +0000
+++ app-mvhub/t/sql_lib_sanity.t 2010-06-17 18:46:04 +0000
@@ -37,6 +37,9 @@
sub get_test_placeholders {
my %data = (
+ 'AGENCY_HEADING_PROGRAM_X_AGENCY_ID' => ['103553'],
+ 'AGENCY_HEADING_PROGRAM_X_AGENCY_ID_EQUAL' => ['103553'],
+ 'AGENCY_PROGRAM_X_ALL' => ['103553'],
'AGENCY_SEARCH_LOG_X_IF_RECORDED_RECENT_AGENCY_SEARCH_BY_USER' =>
[ '66.92.77.160.5111109', 'nscap' ],
'AGENCY_X_AGENCY_NAME_PASSWORD' =>
@@ -49,19 +52,30 @@
],
'AGENCY_X_WEBSITE' => [103553],
'AGENCY_PROGRAM_X_AGENCY_NAME_AGENCY_ALIAS' => [510296],
+ 'AGENCY_X_LAST_UPDATED' => [103553],
+ 'ALIAS_X_ALIAS_NAME' => [1],
'ALL_NAMES_VIEW_CATS_WITH_VISIBLE_PROGRAMS_VIEW_X_ALL_FOR_SYNONYMS' =>
['[[:<:]](ymca|counsel|immi|shelter)[[:>:]]'],
+ 'CATEGORY_PROGRAM_X_CATEGORY_NAME' => [103553],
'CATEGORY_PROGRAM_VIEW_HEADING_CATEGORY_X_ID_NAME_COUNT' =>
['Arts/Culture/Entertainment'],
'CATEGORY_SEARCH_LOG_X_IF_RECORDED_RECENT_CATEGORY_SEARCH_BY_USER' =>
[ '66.92.77.160.5111109', 'test' ],
- 'CATEGORY_X_CATEGORY_NAME' => [800363],
+ 'CATEGORY_X_CATEGORY_NAME' => [800363],
+ 'HEADING_CATEGORY_X_HEADING_NAME' => [103553],
'PROGRAM_AGENCY_PROGRAM_CATEGORY_X_AGENCY_NAME_ID_PROGRAM_NAME' =>
[800363],
+ 'PROGRAM_AGENCY_X_PROGRAM_AGENCY_NAME' => ['103553'],
+ 'PROGRAM_CATEGORY_X_PROGRAM_ID' => ['103553'],
+ 'PROGRAM_X_DISTINCT_PROGRAM_CONTACT_FIRST_LAST_NAME_EMAIL' =>
+ [ '103553', 'fname', 'lname', 'a@xxxxx' ],
'PROGRAM_X_EXPIRED_RECORDS' => [ '01-01-2008', 0 ],
- 'PROGRAM_X_WEBSITE' => [509548],
- 'PROGRAM_X_LAST_UPDATED' => [509548],
- 'SYNONYM_STEM_X_WORD_STEM' => ['alcohol'],
+ 'PROGRAM_X_PROGRAM_CONTACT_FIRST_LAST_NAME_EMAIL' =>
+ [ '103553', 'fname', 'lname', 'a@xxxxx' ],
+ 'PROGRAM_X_PROGRAM_NAME' => [ '103553', 'fname', 'lname', 'a@xxxxx' ],
+ 'PROGRAM_X_WEBSITE' => [509548],
+ 'PROGRAM_X_LAST_UPDATED' => [509548],
+ 'SYNONYM_STEM_X_WORD_STEM' => ['alcohol'],
);
return \%data;
