mvhub-dev team mailing list archive
-
mvhub-dev team
-
Mailing list archive
-
Message #00118
[Merge] lp:~priya/mvhub/move_sql_lib_guidesearch into lp:mvhub
Priya Ravindran has proposed merging lp:~priya/mvhub/move_sql_lib_guidesearch into lp:mvhub.
Requested reviews:
MVHub devs with commit rights (mvhub-commit)
Moved queries from GuideSearch.pm to sql_select.lib
--
https://code.launchpad.net/~priya/mvhub/move_sql_lib_guidesearch/+merge/24222
Your team MVHub Developers is subscribed to branch lp:mvhub.
=== modified file 'app-mvhub/conf/sql_select.lib'
--- app-mvhub/conf/sql_select.lib 2010-04-15 17:42:46 +0000
+++ app-mvhub/conf/sql_select.lib 2010-04-27 14:07:18 +0000
@@ -40,6 +40,20 @@
AND
a.agency_id = p.agency_id
+[AGENCY_SEARCH_LOG_X_IF_RECORDED_RECENT_AGENCY_SEARCH_BY_USER]
+SELECT
+EXISTS
+ (
+ SELECT
+ 1
+ FROM
+ agency_search_log
+ WHERE
+ session_id = ?
+ AND
+ search_phrase = ?
+ )
+
[AGENCY_X_AGENCY_NAME_PASSWORD]
SELECT agency_name, password
FROM
@@ -105,7 +119,22 @@
last_updated < ?
AND
reminders_sent < ?
-
+
+[AGENCY_X_SEARCH_WITH_TERMS]
+SELECT
+ agency_id, agency_name, agency_alias, address1, address2,
+ city, state, zip, main_phone, email
+FROM
+ agency
+WHERE
+ agency_name ~* ?
+OR
+ agency_alias ~* ?
+AND
+ hidden=false
+ORDER BY
+ agency_name
+
[AGENCY_X_WEBSITE]
SELECT
website
@@ -114,6 +143,17 @@
WHERE
agency_id = ?
+[ALL_NAMES_VIEW_CATS_WITH_VISIBLE_PROGRAMS_VIEW_X_ALL_FOR_SYNONYMS]
+SELECT
+ a.category_id, a.category_name, a.stems_name, c.program_count
+FROM
+ all_names_view a
+JOIN
+ cats_with_visible_programs_view c
+USING (category_id)
+WHERE
+ stems_name ~* ?
+
[CATEGORY_PROGRAM_VIEW_HEADING_CATEGORY_X_ID_NAME_COUNT]
SELECT
c.category_id, c.category_name, c.program_count
@@ -125,7 +165,21 @@
hc.heading_name = ?
ORDER BY
c.category_name
-
+
+[CATEGORY_SEARCH_LOG_X_IF_RECORDED_RECENT_CATEGORY_SEARCH_BY_USER]
+SELECT
+EXISTS
+ (
+ SELECT
+ 1
+ FROM
+ category_search_log
+ WHERE
+ session_id = ?
+ AND
+ phrase_stems = ?
+ )
+
[CATEGORY_X_CATEGORY_NAME]
SELECT
category_name
@@ -194,4 +248,21 @@
program
WHERE
program_id = ?
-
+
+[SYNONYM_STEM_X_WORD_STEM]
+SELECT
+DISTINCT
+ word_stem
+FROM
+ synonym_stem
+WHERE
+ synonym_id
+IN
+ (SELECT
+ synonym_id
+ FROM
+ synonym_stem
+ WHERE
+ word_stem
+ IN (?)
+ )
=== modified file 'app-mvhub/t/sql_lib_sanity.t'
--- app-mvhub/t/sql_lib_sanity.t 2010-04-15 17:42:46 +0000
+++ app-mvhub/t/sql_lib_sanity.t 2010-04-27 14:07:18 +0000
@@ -37,23 +37,36 @@
sub get_test_placeholders {
my %data = (
+ 'AGENCY_SEARCH_LOG_X_IF_RECORDED_RECENT_AGENCY_SEARCH_BY_USER' =>
+ [ '66.92.77.160.5111109', 'nscap' ],
'AGENCY_X_AGENCY_NAME_PASSWORD' =>
[ 'chester@xxxxxxxxxxxxxx', 'chester@xxxxxxxxxxxxxx' ],
- 'AGENCY_X_AUTH_AGENCY' => [ '103553', '@lunch4all' ],
- 'AGENCY_X_EXPIRED_RECORDS' => [ '01-01-2008', 0 ],
- 'AGENCY_X_WEBSITE' => [103553],
+ 'AGENCY_X_AUTH_AGENCY' => [ '103553', '@lunch4all' ],
+ 'AGENCY_X_EXPIRED_RECORDS' => [ '01-01-2008', 0 ],
+ 'AGENCY_X_SEARCH_WITH_TERMS' => [
+ '[[:<:]](ymca|counsel|immi|shelter)[[:>:]]',
+ '[[:<:]](ymca|counsel|immi|shelter)[[:>:]]'
+ ],
+ 'AGENCY_X_WEBSITE' => [103553],
'AGENCY_PROGRAM_X_AGENCY_NAME_AGENCY_ALIAS' => [510296],
+ 'ALL_NAMES_VIEW_CATS_WITH_VISIBLE_PROGRAMS_VIEW_X_ALL_FOR_SYNONYMS' =>
+ ['[[:<:]](ymca|counsel|immi|shelter)[[:>:]]'],
'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],
'PROGRAM_AGENCY_PROGRAM_CATEGORY_X_AGENCY_NAME_ID_PROGRAM_NAME' =>
[800363],
'PROGRAM_X_EXPIRED_RECORDS' => [ '01-01-2008', 0 ],
'PROGRAM_X_WEBSITE' => [509548],
'PROGRAM_X_LAST_UPDATED' => [509548],
+ 'SYNONYM_STEM_X_WORD_STEM' => ['alcohol'],
+
);
return \%data;
}
+my $search_terms_aref = [ 'ymca', 'counsel', 'immi', 'shelter' ];
sub statements_in_alphabetical_order_in {
my ($sql_lib_file) = @_;
=== modified file 'lib-mvhub/lib/MVHub/GuideSearch.pm'
--- lib-mvhub/lib/MVHub/GuideSearch.pm 2009-12-07 20:00:37 +0000
+++ lib-mvhub/lib/MVHub/GuideSearch.pm 2010-04-27 14:07:18 +0000
@@ -9,6 +9,7 @@
use DBI;
use MVHub::Utils qw(assert);
+use MVHub::Utils::DB qw/get_sql_select_statement/;
use MVHub::Common;
sub agency_search {
@@ -40,25 +41,12 @@
my $search_terms_aref = shift;
# Search like agency_name and agency_alias
- my $sql
- = "SELECT agency_id, agency_name, agency_alias, address1, address2, "
- . "city, state, zip, main_phone, email "
- . "FROM agency "
- . "WHERE ";
-
- my @list;
- foreach my $search_term (@$search_terms_aref) {
- push @list,
- "( agency_name ~* '[[:<:]]$search_term'" . " OR "
- . "agency_alias ~* '[[:<:]]$search_term' )" . " AND "
- . "hidden = false";
- }
-
- $sql .= join( " OR ", @list );
-
- $sql .= " ORDER BY agency_name";
-
- my $matches_aref = $dbh->selectall_arrayref( $sql, { Slice => {} } );
+ my $regex1 = '[[:<:]](' . join( '|', @$search_terms_aref ) . ')[[:>:]]';
+ my $regex2 = '[[:<:]](' . join( '|', @$search_terms_aref ) . ')[[:>:]]';
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'AGENCY_X_SEARCH_WITH_TERMS');
+ my $matches_aref
+ = $dbh->selectall_arrayref( $sql, { Slice => {} }, $regex1, $regex2 );
return $matches_aref;
}
@@ -159,10 +147,8 @@
# 'WHERE session_id = ? AND phrase_stems = ?';
# ...but probably not as fast as the following, which will stop as soon
# it finds one matching row:
- my $sql
- = 'SELECT EXISTS ('
- . ' SELECT 1 FROM category_search_log '
- . ' WHERE session_id = ? AND phrase_stems = ?)';
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'CATEGORY_SEARCH_LOG_X_IF_RECORDED_RECENT_CATEGORY_SEARCH_BY_USER');
my $rows_aref = $dbh->selectcol_arrayref( $sql, undef, $user_session_id,
$phrase_stems_string );
@@ -208,17 +194,15 @@
my $match_count = @$matching_agencies_aref;
- # Find out if we've recently recorded this search for this user.
+ # Find out if we've recently recorded this agency search for this user.
# FYI: The following SQL is relatively straightforward...
- # my $sql = 'SELECT count(*) FROM category_search_log ' .
+ # my $sql = 'SELECT count(*) FROM agency_search_log ' .
# 'WHERE session_id = ? AND phrase_stems = ?';
# ...but probably not as fast as the following, which will stop as soon
# it finds one matching row:
- my $sql
- = 'SELECT EXISTS ('
- . ' SELECT 1 FROM agency_search_log '
- . ' WHERE session_id = ? AND search_phrase = ?)';
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'AGENCY_SEARCH_LOG_X_IF_RECORDED_RECENT_AGENCY_SEARCH_BY_USER');
my $rows_aref
= $dbh->selectcol_arrayref( $sql, undef, $user_session_id, $phrase );
return if ( $rows_aref->[0] );
@@ -269,11 +253,10 @@
foreach (@$phrase_words_aref) { $word_list .= $dbh->quote($_) . ',' }
chop $word_list;
- my $sql
- = "SELECT DISTINCT word_stem FROM synonym_stem where synonym_id in "
- . "(SELECT synonym_id FROM synonym_stem WHERE word_stem IN ($word_list))";
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'SYNONYM_STEM_X_WORD_STEM');
- my @synonyms = @{ $dbh->selectcol_arrayref($sql) };
+ my @synonyms = @{ $dbh->selectcol_arrayref( $sql, undef, $word_list ) };
@$phrase_words_aref
= Set::Array->new( @synonyms, @$phrase_words_aref )->unique();
@@ -301,15 +284,11 @@
# "WHERE stems_name ~* '$regex'";
# TODO: option of LEFT JOIN vs JOIN
- my $sql
- = "SELECT a.category_id, a.category_name, a.stems_name, c.program_count "
- . "FROM "
- . "all_names_view a "
- . "JOIN cats_with_visible_programs_view c "
- . "USING (category_id) "
- . "WHERE stems_name ~* '$regex'";
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'ALL_NAMES_VIEW_CATS_WITH_VISIBLE_PROGRAMS_VIEW_X_ALL_FOR_SYNONYMS');
- my $matches_aref = $dbh->selectall_arrayref( $sql, { Slice => {} } );
+ my $matches_aref
+ = $dbh->selectall_arrayref( $sql, { Slice => {} }, $regex );
order_matches( $matches_aref, $search_terms_aref );
Follow ups
