mvhub-dev team mailing list archive
-
mvhub-dev team
-
Mailing list archive
-
Message #00356
[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/28658
Your team MVHub Developers is subscribed to branch lp:mvhub.
=== modified file 'app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/admin_reports.pl'
--- app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/admin_reports.pl 2010-06-22 19:00:02 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/admin_reports.pl 2010-06-28 17:27:29 +0000
@@ -43,71 +43,17 @@
{ title => 'Agencies not responding to email or with bad contact email',
comment =>
'If the agency or program contact does not update their records after 4 emails we pick up the phone',
- sql => q/
- SELECT
- a.agency_name,
- a.contact_phone,
- a.contact_first_name || ' '
- || a.contact_last_name AS contact,
- a.contact_email,
- ' MAIN AGENCY RECORD' AS program_name
- FROM
- agency a
- WHERE
- a.contact_email like '%bad@xxxxxxxxxxxxxx%'
- OR a.contact_email = 'none'
- OR a.contact_email = 'unknown'
- OR a.reminders_sent >= ?
- UNION
- SELECT
- a.agency_name,
- p.contact_phone,
- p.contact_first_name || ' '
- || p.contact_last_name AS contact,
- p.contact_email,
- p.program_name
- FROM
- program p
- JOIN agency a ON a.agency_id = p.agency_id
- WHERE
- p.contact_email like '%bad@xxxxxxxxxxxxxx%'
- OR p.contact_email = 'none'
- OR p.contact_email = 'unknown'
- OR p.reminders_sent >=?
- ORDER BY
- agency_name,program_name
- /,
+ sql => MVHub::Utils::DB::get_sql_select_statement(
+ 'AGENCY_X_DATE_OF_OLDEST_RECORD'),
bind_values => [ $max_notifications, $max_notifications ]
},
{ title => 'Agencies with Missing or Unknown Contact Information',
- sql => q/
- SELECT agency_name, address1, email, main_phone, contact_first_name,
- contact_last_name, contact_email, contact_phone
- FROM agency
- WHERE
- address1 ~* '(unknown|contact|\\\\?)'
- or email ~* 'unknown'
- or contact_email ~* 'unknown'
- or contact_first_name ~* '(unknown|contact|\\\\?)'
- or contact_last_name ~* '(unknown|contact|\\\\?)'
- /
+ sql => MVHub::Utils::DB::get_sql_select_statement(
+ 'AGENCY_X_AGENCIES_WITH_MISSING_CONTACT_INFO')
},
{ title => 'Programs with Missing or Unknown Contact Information',
- sql => q/
- SELECT p.program_name, a.agency_name, p.address1, p.email, p.main_phone,
- p.contact_first_name, p.contact_last_name, p.contact_email,
- p.contact_phone
- FROM program p, agency a
- WHERE
- (
- p.address1 ~* '(unknown|contact|\\\\?)'
- or p.email ~* 'unknown'
- or p.contact_email ~* 'unknown'
- or p.contact_first_name ~* '(unknown|contact|\\\\?)'
- or p.contact_last_name ~* '(unknown|contact|\\\\?)'
- )
- and a.agency_id = p.agency_id
- /
+ sql => MVHub::Utils::DB::get_sql_select_statement(
+ 'AGENCY_PROGRAM_X_PROGRAM_WITH_MISSING_CONTACT_INFO')
}
); # @REPORTS
=== modified file 'app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/structure.pl'
--- app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/structure.pl 2010-06-17 18:46:44 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/structure.pl 2010-06-28 17:27:29 +0000
@@ -470,14 +470,26 @@
# The SELECT EXISTS (SELECT 1 ...) syntax is generally faster than the
# SELECT count(*) syntax, since the former only scans until it finds
# the first match.
- my $sql = "SELECT 1 FROM category WHERE lower(category_name) = "
- . $dbh->quote( lc($category_name) );
- if ($category_id) {
- $sql .= " AND category_id <> " . $dbh->quote($category_id);
- }
- $sql = "SELECT EXISTS($sql)";
-
- my @row = $dbh->selectrow_array($sql);
+ my @bind_variables = ( lc($category_name) );
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'CATEGORY_X_ONE_CATEGORY_NAME');
+
+ if ($category_id) {
+ @bind_variables = ( lc($category_name), $category_id );
+ $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'CATEGORY_X_ONE_CATEGORY_NAME_NOT_ID');
+ }
+
+ $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'CATEGORY_X_EXISTS_ONE_CATEGORY_NAME');
+
+ if ($category_id) {
+ $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'CATEGORY_X_EXISTS_ONE_CATEGORY_NAME_NOT_ID');
+ }
+
+ my @row
+ = $dbh->selectall_arrayref( $sql, { Slice => {} }, @bind_variables );
return ( $row[0] );
}
=== modified file 'app-mvhub/DocumentRoot/cgi-bin/mvhub/agency_form.pl'
--- app-mvhub/DocumentRoot/cgi-bin/mvhub/agency_form.pl 2010-06-24 15:15:46 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/agency_form.pl 2010-06-28 17:27:29 +0000
@@ -291,20 +291,6 @@
return ( $row[0] != 0 );
}
-sub is_valid_agency_name {
- my %args = @_;
- my $agency_id = delete $args{'agency_id'};
- my $agency_name = delete $args{'agency_name'};
- my $table = delete $args{'table'};
-
- my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
- my $stmt
- = "SELECT COUNT(*) FROM $table WHERE agency_name=? AND agency_id=?";
- my @row = $dbh->selectrow_array( $stmt, undef, $agency_name, $agency_id );
-
- return ( $row[0] != 0 );
-}
-
sub prepare_record_for_db {
my ( $values_href, $username, $is_data_entry_operator, $update_status )
= @_;
=== modified file 'app-mvhub/DocumentRoot/cgi-bin/mvhub/program.pl'
--- app-mvhub/DocumentRoot/cgi-bin/mvhub/program.pl 2010-06-24 15:15:46 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/program.pl 2010-06-28 17:27:29 +0000
@@ -384,19 +384,20 @@
MVHub::Utils::assert( ( scalar keys %args ) == 0,
"Unknown arguments: @{[%args]}" );
my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
- my $stmt
- = 'SELECT COUNT(*) FROM program'
- . ' WHERE LOWER(program_name) = '
- . $dbh->quote( lc($program_name) )
- . ' AND agency_id = '
- . $dbh->quote($agency_id);
+
+ my @bind_variables = ( lc($program_name), $agency_id );
+
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'PROGRAM_LOWER_PROGRAM_NAME_X_COUNT');
# So they can update an existing program, and keep the same name
if ( $program_id != $MVHub::Common::NO_PROGRAM_ID ) {
- $stmt .= " AND program_id <> " . $dbh->quote($program_id);
+ $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'PROGRAM_LOWER_PROGRAM_NAME_ID_X_COUNT');
+ @bind_variables = ( lc($program_name), $agency_id, $program_id );
}
- my @row = $dbh->selectrow_array($stmt);
+ my @row = $dbh->selectrow_array( $sql, { Slice => {} }, @bind_variables );
return ( $row[0] == 0 );
}
@@ -455,10 +456,10 @@
my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
- my $stmt = MVHub::Utils::DB::get_sql_select_statement(
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
'CATEGORY_HEADING_CATEGORY_X_HEADING_CATEGORY_NAME');
- my $categories_aref = $dbh->selectall_arrayref( $stmt, { Slice => {} } );
+ my $categories_aref = $dbh->selectall_arrayref( $sql, { Slice => {} } );
# Fill a hash of: heading => { category_id => category_name, ... }
my %categories_hash;
@@ -471,11 +472,12 @@
# Get any category ID previously selected for this program, if any
my $preselected_category_ids_aref;
if ($existing) {
- my $stmt2
- = 'SELECT category_id FROM program_category '
- . 'WHERE program_id = '
- . $dbh->quote($program_id);
- $preselected_category_ids_aref = $dbh->selectcol_arrayref($stmt2);
+ my @bind_variables = ($program_id);
+ $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'PROGRAM_CATEGORY_X_CATEGORY_ID');
+ $preselected_category_ids_aref
+ = $dbh->selectcol_arrayref( $sql, { Slice => {} },
+ @bind_variables );
}
# Produce our page
@@ -900,12 +902,15 @@
# Make sure there is a program to be deleted (because someone might accidentally click
# the "Remove" link twice in a row)
- if (scalar $dbh->selectrow_array(
- "SELECT COUNT(*) FROM program WHERE program_id = "
- . $dbh->quote($program_id)
- )
+
+ my $sql
+ = MVHub::Utils::DB::get_sql_select_statement('PROGRAM_X_COUNT_ALL');
+ my @bind_variables = ($program_id);
+
+ if (scalar $dbh->selectrow_array( $sql, { Slice => {} }, @bind_variables )
)
{
+
if ( !$is_admin ) {
# We send the notification before doing the deletion since the following call
@@ -942,15 +947,13 @@
my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'AGENCY_PROGRAM_X_AGENCY_NAME');
+ my @bind_variables = ( $program_id, $agency_id );
+
# Regardless of mode, get the program info and prepare it for display
- my $result_aref = $dbh->selectall_arrayref(
- "SELECT a.agency_name, p.* FROM agency a, program p "
- . "WHERE p.program_id = "
- . $dbh->quote($program_id)
- . "AND a.agency_id = "
- . $dbh->quote($agency_id),
- { Slice => {} }
- );
+ my $result_aref
+ = $dbh->selectall_arrayref( $sql, { Slice => {} }, @bind_variables );
MVHub::Utils::assert( ( @$result_aref == 1 ),
"Expected 1 result, got " . scalar @$result_aref );
@@ -1274,56 +1277,6 @@
$values_href->{bus_lines} = $transit_routes_aref;
}
-sub is_program_name_unique {
- my %args = @_;
- my $program_name = delete $args{'program_name'};
- my $agency_id = delete $args{'agency_id'};
- my $table = delete $args{'table'};
- MVHub::Utils::assert( defined $program_name && $program_name ne "",
- "Missing program_name argument" );
- MVHub::Utils::assert( ( scalar keys %args ) == 0,
- "Unknown arguments: @{[%args]}" );
- my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
- my $stmt
- = "SELECT COUNT(*) FROM $table WHERE LOWER(program_name)= "
- . $dbh->quote( lc($program_name) )
- . "AND agency_id= "
- . $dbh->quote($agency_id);
-
- my @row = $dbh->selectrow_array($stmt);
-
- return ( $row[0] == 0 );
-}
-
-sub is_valid_program_id {
- my %args = @_;
- my $program_id = delete $args{'program_id'};
- my $table = delete $args{'table'};
- MVHub::Utils::assert( ( scalar keys %args ) == 0,
- "Unknown arguments: @{[%args]}" );
-
- my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
- my $stmt = "SELECT COUNT(*) FROM $table WHERE program_id=?";
- my @row = $dbh->selectrow_array( $stmt, undef, $program_id );
-
- return ( $row[0] != 0 );
-}
-
-sub is_valid_program_name {
- my %args = @_;
- my $program_id = delete $args{'program_id'};
- my $program_name = delete $args{'program_name'};
- my $table = delete $args{'table'};
-
- my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
- my $stmt
- = "SELECT COUNT(*) FROM $table WHERE program_name=? AND program_id=?";
- my @row
- = $dbh->selectrow_array( $stmt, undef, $program_name, $program_id );
-
- return ( $row[0] != 0 );
-}
-
sub send_confirmation_email {
my $values_href = shift;
my $agency_name
=== modified file 'app-mvhub/DocumentRoot/cgi-bin/mvhub/reports.pl'
--- app-mvhub/DocumentRoot/cgi-bin/mvhub/reports.pl 2010-06-25 14:58:28 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/reports.pl 2010-06-28 17:27:29 +0000
@@ -42,22 +42,15 @@
# },
{ title => 'Agency Addition Dates',
comment => "This gives the date when each agency was added to MVHub.",
- sql => qq/
- SELECT agency_name, date_created
- FROM agency
- ORDER BY date_created;
- /
+ sql => MVHub::Utils::DB::get_sql_select_statement(
+ 'AGENCY_X_AGENCY_NAME_DATE_CREATED')
},
{ title => 'Program Addition Dates',
comment =>
"This gives the date when each program was added to MVHub.",
- sql => qq/
- SELECT p.program_name, a.agency_name, p.date_created
- FROM agency a, program p
- WHERE p.agency_id = a.agency_id
- ORDER BY p.date_created;
- /
+ sql => MVHub::Utils::DB::get_sql_select_statement(
+ 'AGENCY_X_PROGRAM_NAME_AGENCY_NAME_DATE_CREATED')
},
{ title => 'All Searches',
@@ -69,18 +62,8 @@
. "was conducted. A referer with an external site address probably means that site "
. "has a link to a specific search. A blank referer probably means the user used a bookmark "
. "(of a previously conducted search).",
- sql => qq/
- SELECT to_char(timestamp, 'Dy, Mon DD, YYYY HH24:MI') as conducted_on, search_phrase, search_type, category_score, agencies_displayed, split_part(referer, '?', 1) as referer FROM (
- SELECT search_id, timestamp, search_phrase, 'agency' as search_type, NULL as category_score,
- hit_count as agencies_displayed, referer
- FROM agency_search_log
- UNION
- SELECT search_id, timestamp, search_phrase, 'category' as search_type, top_score as category_score,
- NULL as agencies_displayed, referer
- FROM category_search_log WHERE search_phrase IS NOT NULL
- ) as all_searches
- ORDER BY search_id DESC;
- /
+ sql => MVHub::Utils::DB::get_sql_select_statement(
+ 'AGENCY_SEARCH_LOG_X_ALL_SEARCHES')
},
{ title => 'Searchers per Day',
@@ -88,15 +71,8 @@
'The number of people conducting searches per day. Multiple searches '
. 'from the same IP address on the same day are attributed to the same '
. 'person. Only dates with one or more searches are listed.',
- sql => q/
- SELECT to_char(search_date, 'Dy, Mon DD, YYYY') as search_date, count FROM
- (
- SELECT date(timestamp) as search_date, count(distinct ip_address) as count
- FROM search_log
- GROUP BY search_date ORDER BY search_date DESC
- ) as preformatted_results
-
- /
+ sql => MVHub::Utils::DB::get_sql_select_statement(
+ 'SEARCH_LOG_X_SEARCHERS_PER_DAY')
},
{ title =>
'Number & percentage of Agencies & Programs updated in the Last 6 Months',
@@ -129,27 +105,14 @@
'Date of the Oldest Record belonging to each Agency (ordered by date)',
comment =>
"(An agency's oldest record may be its agency record &\/or any of its program record(s))",
- sql => q/
- SELECT a.agency_name,
- min(record_ages.last_updated) AS "Oldest Record (yyyy-mm-dd)"
- FROM (
- SELECT agency_id, last_updated from program
- UNION
- SELECT agency_id, last_updated from agency
- ) AS record_ages, agency a
- WHERE a.agency_id = record_ages.agency_id
- GROUP BY agency_name ORDER BY "Oldest Record (yyyy-mm-dd)", agency_name
- /
+ sql => MVHub::Utils::DB::get_sql_select_statement(
+ 'PROGRAM_AGENCY_ID_X_OLDEST_RECORD_DATE')
},
{ title => 'Number of Programs under each Category',
# We use a left join below so that we also see which categories are unused.
- sql => q/
- SELECT c.category_name, count(pc.program_id) AS programs
- FROM category c LEFT OUTER JOIN program_category pc
- ON (c.category_id = pc.category_id)
- GROUP BY c.category_name ORDER BY programs DESC, category_name
- /
+ sql => MVHub::Utils::DB::get_sql_select_statement(
+ 'CATEGORY_PROGRAM_CATEGORY_X_NUMBER_OF_PROGRAMS_UNDER_CATEGORY')
},
{ title => 'Number of Categories used by each Program',
=== modified file 'app-mvhub/conf/sql_select.lib'
--- app-mvhub/conf/sql_select.lib 2010-06-19 19:50:00 +0000
+++ app-mvhub/conf/sql_select.lib 2010-06-28 17:27:29 +0000
@@ -104,6 +104,12 @@
)
AND NOT agency.hidden
+[AGENCY_PROGRAM_X_AGENCY_NAME]
+SELECT a.agency_name, p.*
+FROM agency a, program p
+WHERE p.program_id = ?
+AND a.agency_id = ?
+
[AGENCY_PROGRAM_X_AGENCY_NAME_AGENCY_ALIAS]
SELECT
a.agency_name, a.agency_alias, p.*
@@ -121,6 +127,34 @@
USING ( agency_id )
WHERE agency_id = ?
+[AGENCY_PROGRAM_X_PROGRAM_WITH_MISSING_CONTACT_INFO]
+SELECT p.program_name, a.agency_name, p.address1, p.email, p.main_phone,
+ p.contact_first_name, p.contact_last_name, p.contact_email,
+ p.contact_phone
+FROM program p, agency a
+WHERE
+(
+ p.address1 ~* '(unknown|contact)'
+ or p.email ~* 'unknown'
+ or p.contact_email ~* 'unknown'
+ or p.contact_first_name ~* '(unknown|contact)'
+ or p.contact_last_name ~* '(unknown|contact)'
+)
+and a.agency_id = p.agency_id
+
+[AGENCY_SEARCH_LOG_X_ALL_SEARCHES]
+SELECT to_char(timestamp, 'Dy, Mon DD, YYYY HH24:MI') as conducted_on, search_phrase, search_type, category_score, agencies_displayed, split_part(referer, '?', 1) as referer
+FROM (
+ SELECT search_id, timestamp, search_phrase, 'agency' as search_type, NULL as category_score,
+ hit_count as agencies_displayed, referer
+ FROM agency_search_log
+ UNION
+ SELECT search_id, timestamp, search_phrase, 'category' as search_type, top_score as category_score,
+ NULL as agencies_displayed, referer
+ FROM category_search_log WHERE search_phrase IS NOT NULL
+) as all_searches
+ORDER BY search_id DESC;
+
[AGENCY_SEARCH_LOG_X_IF_RECORDED_RECENT_AGENCY_SEARCH_BY_USER]
SELECT
EXISTS
@@ -134,7 +168,38 @@
AND
search_phrase = ?
)
+
+[AGENCY_X_AGENCIES_WITH_MISSING_CONTACT_INFO]
+SELECT agency_name, address1, email, main_phone, contact_first_name,
+ contact_last_name, contact_email, contact_phone
+FROM agency
+WHERE
+ address1 ~* '(unknown|contact)'
+ or email ~* 'unknown'
+ or contact_email ~* 'unknown'
+ or contact_first_name ~* '(unknown|contact)'
+ or contact_last_name ~* '(unknown|contact)'
+
+[AGENCY_X_AGENCY_ID]
+SELECT *
+FROM agency
+WHERE agency_id = ?
+
+[AGENCY_X_AGENCY_ID_NAME]
+SELECT agency_id,agency_name
+FROM agency
+ORDER by agency_name
+
+[AGENCY_X_AGENCY_NAME]
+SELECT agency_name
+FROM agency
+WHERE agency_id=?
+[AGENCY_X_AGENCY_NAME_DATE_CREATED]
+SELECT agency_name, date_created
+FROM agency
+ORDER BY date_created;
+
[AGENCY_X_AGENCY_NAME_PASSWORD]
SELECT agency_name, password
FROM
@@ -148,7 +213,8 @@
FROM program
WHERE contact_email = ?
)
-
+
+
[AGENCY_X_ALL]
SELECT
*
@@ -198,6 +264,40 @@
FROM agency
WHERE NOT hidden
+[AGENCY_X_DATE_OF_OLDEST_RECORD]
+SELECT
+ a.agency_name,
+ a.contact_phone,
+ a.contact_first_name || ' '
+ || a.contact_last_name AS contact,
+ a.contact_email,
+ ' MAIN AGENCY RECORD' AS program_name
+FROM
+ agency a
+WHERE
+ a.contact_email like '%bad@xxxxxxxxxxxxxx%'
+ OR a.contact_email = 'none'
+ OR a.contact_email = 'unknown'
+ OR a.reminders_sent >= ?
+UNION
+SELECT
+ a.agency_name,
+ p.contact_phone,
+ p.contact_first_name || ' '
+ || p.contact_last_name AS contact,
+ p.contact_email,
+ p.program_name
+FROM
+ program p
+JOIN agency a ON a.agency_id = p.agency_id
+WHERE
+ p.contact_email like '%bad@xxxxxxxxxxxxxx%'
+ OR p.contact_email = 'none'
+ OR p.contact_email = 'unknown'
+ OR p.reminders_sent >=?
+ORDER BY
+ agency_name,program_name
+
[AGENCY_X_EXPIRED_RECORDS]
SELECT
agency_id,
@@ -222,6 +322,12 @@
FROM agency
WHERE agency_id = ?
+[AGENCY_X_PROGRAM_NAME_AGENCY_NAME_DATE_CREATED]
+SELECT p.program_name, a.agency_name, p.date_created
+FROM agency a, program p
+WHERE p.agency_id = a.agency_id
+ORDER BY p.date_created;
+
[AGENCY_X_SEARCH_WITH_TERMS]
SELECT
agency_id, agency_name, agency_alias, address1, address2,
@@ -236,7 +342,7 @@
hidden=false
ORDER BY
agency_name
-
+
[AGENCY_X_WEBSITE]
SELECT
website
@@ -282,6 +388,12 @@
FROM category c, heading_category h
WHERE h.category_id = c.category_id
+[CATEGORY_PROGRAM_CATEGORY_X_NUMBER_OF_PROGRAMS_UNDER_CATEGORY]
+SELECT c.category_name, count(pc.program_id) AS programs
+FROM category c LEFT OUTER JOIN program_category pc
+ON (c.category_id = pc.category_id)
+GROUP BY c.category_name ORDER BY programs DESC, category_name
+
[CATEGORY_PROGRAM_VIEW_HEADING_CATEGORY_X_ID_NAME_COUNT]
SELECT
c.category_id, c.category_name, c.program_count
@@ -323,10 +435,35 @@
WHERE
category_id = ?
+[CATEGORY_X_EXISTS_ONE_CATEGORY_NAME]
+SELECT EXISTS(
+ SELECT 1
+ FROM category
+ WHERE lower(category_name) = ?
+)
+
+[CATEGORY_X_EXISTS_ONE_CATEGORY_NAME_NOT_ID]
+SELECT EXISTS(
+ SELECT 1
+ FROM category
+ WHERE lower(category_name) = ?
+ AND category_id <> ?
+)
[CATEGORY_X_IN_CATEGORY_NAME]
SELECT category_name
FROM category
+[CATEGORY_X_ONE_CATEGORY_NAME]
+SELECT 1
+FROM category
+WHERE lower(category_name) = ?
+
+[CATEGORY_X_ONE_CATEGORY_NAME_NOT_ID]
+SELECT 1
+FROM category
+WHERE lower(category_name) = ?
+AND category_id <> ?
+
[HEADING_CATEGORY_X_HEADING_NAME]
SELECT heading_name
FROM heading_category
@@ -340,6 +477,56 @@
ORDER BY
heading_name
+[INFORMATION_SCHEMA_X_ONE]
+SELECT 1
+FROM information_schema.tables
+WHERE table_name='version_log'
+LIMIT 1
+
+[PG_DATABASE_X_DATNAME]
+SELECT
+ datname
+FROM
+ pg_database
+WHERE
+ datname = ?
+
+[PG_ROLES_X_ROLNAME_CREATEROLE_CREATEDB]
+SELECT
+ rolname,rolcreaterole,rolcreatedb
+FROM
+ pg_roles
+WHERE
+ rolname = ?
+
+[PG_ROLES_X_ROLNAME_ROLSUPER]
+SELECT
+ rolname,rolsuper
+FROM
+ pg_roles
+WHERE
+ rolname = ?
+
+[PG_USER_X_USENAME]
+SELECT
+ usename
+FROM
+ pg_user
+WHERE
+ usename = ?
+
+[PROGRAM_AGENCY_ID_X_OLDEST_RECORD_DATE]
+SELECT a.agency_name,
+ min(record_ages.last_updated) AS "Oldest Record (yyyy-mm-dd)"
+FROM (
+ SELECT agency_id, last_updated from program
+ UNION
+ SELECT agency_id, last_updated from agency
+) AS record_ages, agency a
+WHERE a.agency_id = record_ages.agency_id
+GROUP BY agency_name
+ORDER BY "Oldest Record (yyyy-mm-dd)", agency_name
+
[PROGRAM_AGENCY_PROGRAM_CATEGORY_X_AGENCY_NAME_ID_PROGRAM_NAME]
SELECT
a.agency_name, p.program_id, p.program_name,
@@ -388,17 +575,44 @@
WHERE
hc.category_id =pc.category_id
+[PROGRAM_CATEGORY_X_CATEGORY_ID]
+SELECT category_id
+FROM program_category
+WHERE program_id = ?
+
[PROGRAM_CATEGORY_X_PROGRAM_ID]
SELECT program_id
FROM program_category
WHERE category_id = ?
-
+
+[PROGRAM_ID_X_PROGRAM_NAME]
+SELECT program_name
+FROM program
+WHERE program_id=?
+
+[PROGRAM_LOWER_PROGRAM_NAME_ID_X_COUNT]
+SELECT COUNT(*)
+FROM program
+WHERE LOWER(program_name) = ?
+AND agency_id = ?
+AND program_id <> ?
+
+[PROGRAM_LOWER_PROGRAM_NAME_X_COUNT]
+SELECT COUNT(*)
+FROM program
+WHERE LOWER(program_name) = ?
+AND agency_id = ?
+
+[PROGRAM_X_COUNT_ALL]
+SELECT COUNT(*)
+FROM program
+WHERE program_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
@@ -458,17 +672,20 @@
WHERE
program_id = ?
-[SYNONYM_STEM_X_CONCAT_SUBMITTED_WORDS]
-SELECT
- our_aggregate_concat(submitted_word)
-AS
- submitted_words
-FROM
- synonym_stem
-GROUP BY
- synonym_id
-ORDER BY
- submitted_words
+[SEARCH_LOG_X_SEARCHERS_PER_DAY]
+SELECT to_char(search_date, 'Dy, Mon DD, YYYY') as search_date, count FROM
+(
+ SELECT date(timestamp) as search_date, count(distinct ip_address) as count
+ FROM search_log
+ GROUP BY search_date ORDER BY search_date DESC
+) as preformatted_results
+
+[SYNONYM_STEM_X_SUBMITTED_WORDS]
+SELECT our_aggregate_concat(submitted_word)
+AS submitted_words
+FROM synonym_stem
+GROUP BY synonym_id
+ORDER BY submitted_words
[SYNONYM_STEM_X_WORD_STEM]
SELECT DISTINCT
@@ -491,3 +708,7 @@
SELECT town_state
FROM town WHERE display = TRUE
ORDER BY town_state
+
+[VERSION_LOG_X_MAX]
+SELECT max(version)
+FROM version_log
=== modified file 'app-mvhub/t/sql_lib_sanity.t'
--- app-mvhub/t/sql_lib_sanity.t 2010-06-17 18:46:04 +0000
+++ app-mvhub/t/sql_lib_sanity.t 2010-06-28 17:27:29 +0000
@@ -40,13 +40,20 @@
'AGENCY_HEADING_PROGRAM_X_AGENCY_ID' => ['103553'],
'AGENCY_HEADING_PROGRAM_X_AGENCY_ID_EQUAL' => ['103553'],
'AGENCY_PROGRAM_X_ALL' => ['103553'],
+ 'AGENCY_PROGRAM_X_AGENCY_NAME' => [ 103553, 103553 ],
+ 'AGENCY_PROGRAM_X_PROGRAM_WITH_MISSING_CONTACT_INFO' => [],
+ 'AGENCY_SEARCH_LOG_X_ALL_SEARCHES' => [],
'AGENCY_SEARCH_LOG_X_IF_RECORDED_RECENT_AGENCY_SEARCH_BY_USER' =>
[ '66.92.77.160.5111109', 'nscap' ],
+ 'AGENCY_X_AGENCY_ID' => [103553],
+ 'AGENCY_X_AGENCY_NAME' => [103553],
'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_SEARCH_WITH_TERMS' => [
+ 'AGENCY_X_AGENCIES_WITH_MISSING_CONTACT_INFO' => [],
+ 'AGENCY_X_AUTH_AGENCY' => [ '103553', '@lunch4all' ],
+ 'AGENCY_X_DATE_OF_OLDEST_RECORD' => [ 103553, 103553 ],
+ 'AGENCY_X_EXPIRED_RECORDS' => [ '01-01-2008', 0 ],
+ 'AGENCY_X_SEARCH_WITH_TERMS' => [
'[[:<:]](ymca|counsel|immi|shelter)[[:>:]]',
'[[:<:]](ymca|counsel|immi|shelter)[[:>:]]'
],
@@ -56,17 +63,30 @@
'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_X_EXISTS_ONE_CATEGORY_NAME' => ['cat'],
+ 'CATEGORY_X_EXISTS_ONE_CATEGORY_NAME_NOT_ID' => [ 'cat', 103553 ],
+ 'CATEGORY_X_ONE_CATEGORY_NAME' => ['cat'],
+ 'CATEGORY_X_ONE_CATEGORY_NAME_NOT_ID' => [ 'cat', 103553 ],
+ '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],
- 'HEADING_CATEGORY_X_HEADING_NAME' => [103553],
+ 'CATEGORY_X_CATEGORY_NAME' => [800363],
+ 'HEADING_CATEGORY_X_HEADING_NAME' => [103553],
+ 'PG_DATABASE_X_DATNAME' => ['dat'],
+ 'PG_ROLES_X_ROLNAME_CREATEROLE_CREATEDB' => ['rol'],
+ 'PG_ROLES_X_ROLNAME_ROLSUPER' => ['rolname'],
+ 'PG_USER_X_USENAME' => ['use'],
'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_AGENCY_X_PROGRAM_AGENCY_NAME' => ['103553'],
+ 'PROGRAM_CATEGORY_X_PROGRAM_ID' => ['103553'],
+ '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_X_DISTINCT_PROGRAM_CONTACT_FIRST_LAST_NAME_EMAIL' =>
[ '103553', 'fname', 'lname', 'a@xxxxx' ],
'PROGRAM_X_EXPIRED_RECORDS' => [ '01-01-2008', 0 ],
@@ -76,7 +96,6 @@
'PROGRAM_X_WEBSITE' => [509548],
'PROGRAM_X_LAST_UPDATED' => [509548],
'SYNONYM_STEM_X_WORD_STEM' => ['alcohol'],
-
);
return \%data;
}
=== modified file 'lib-mvhub/lib/MVHub/Synonyms.pm'
--- lib-mvhub/lib/MVHub/Synonyms.pm 2010-06-09 15:43:40 +0000
+++ lib-mvhub/lib/MVHub/Synonyms.pm 2010-06-28 17:27:29 +0000
@@ -38,7 +38,8 @@
sub fetch_synonyms_from_db {
my $dbh = MVHub::Utils::DB::get_dbh();
my $sql = MVHub::Utils::DB::get_sql_select_statement(
- 'SYNONYM_STEM_X_CONCAT_SUBMITTED_WORDS');
+ 'SYNONYM_STEM_X_SUBMITTED_WORDS');
+
my $rows_aref = $dbh->selectcol_arrayref($sql);
foreach ( @{$rows_aref} ) {
s/,//g;
=== modified file 'lib-mvhub/lib/MVHub/Utils/DB.pm'
--- lib-mvhub/lib/MVHub/Utils/DB.pm 2010-06-10 19:10:03 +0000
+++ lib-mvhub/lib/MVHub/Utils/DB.pm 2010-06-28 17:27:29 +0000
@@ -32,8 +32,6 @@
select_program_record
select_records
select_unique_record
- translate_agency_name
- translate_program_name
update_one_record
);
@@ -95,19 +93,8 @@
my $username = shift;
my $is_data_entry_operator = shift;
- my $sql;
- if ( $username =~ /^3\d{5,6}$/ ) {
- $sql = "SELECT first_name, last_name FROM users WHERE user_id=?";
- }
- elsif ( $username =~ /^\d{6,7}$/ && $is_data_entry_operator ) {
- $sql = "SELECT agency_name FROM data_entry_agency WHERE agency_id=?";
- }
- elsif ( $username =~ /^\d{6,7}$/ ) {
- $sql = "SELECT agency_name FROM agency WHERE agency_id=?";
- }
- else {
- $sql = "SELECT first_name, last_name FROM users WHERE username=?";
- }
+ my $sql
+ = MVHub::Utils::DB::get_sql_select_statement('AGENCY_X_AGENCY_NAME');
my ( $first_name, $last_name )
= $dbh->selectrow_array( $sql, undef, $username );
@@ -120,9 +107,10 @@
sub get_hash_of_agencies {
my $dbh = shift;
- my $stmt
- = "SELECT agency_id,agency_name from agency order by agency_name";
- my $aref = $dbh->selectcol_arrayref( $stmt, { Columns => [ 1, 2 ] } );
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'AGENCY_X_AGENCY_ID_NAME');
+
+ my $aref = $dbh->selectcol_arrayref( $sql, { Columns => [ 1, 2 ] } );
my %agency_hash = @$aref;
return %agency_hash;
}
@@ -150,7 +138,8 @@
my $dbh = shift;
my $program_id = shift;
- my $sql = "SELECT program_name FROM program WHERE program_id=?";
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'PROGRAM_ID_X_PROGRAM_NAME');
my ($program_name) = $dbh->selectrow_array( $sql, undef, $program_id );
return $program_name;
@@ -293,9 +282,12 @@
my $agency_id = $args{agency_id};
my $allow_empty_result = $args{allow_empty_result};
- my $stmt
- = "SELECT * FROM agency WHERE agency_id = " . $dbh->quote($agency_id);
- my $records_aref = $dbh->selectall_arrayref( $stmt, { Slice => {} } );
+ my @bind_variables = ($agency_id);
+ my $sql
+ = MVHub::Utils::DB::get_sql_select_statement('AGENCY_X_AGENCY_ID');
+
+ my $records_aref
+ = $dbh->selectall_arrayref( $sql, { Slice => {} }, @bind_variables );
return if ( @$records_aref == 0 && $allow_empty_result );
MVHub::Utils::assert(
@@ -418,33 +410,6 @@
return $records_aref->[0];
}
-# Takes an agency name, returns an agency id
-sub translate_agency_name {
- my $dbh = shift;
- my $agency_name = shift;
-
- my $sql
- = "SELECT DISTINCT on (agency_id) agency_id FROM data_entry_agency WHERE agency_name = ?";
- my $agency_id_href
- = $dbh->selectrow_hashref( $sql, { Slice => {} }, $agency_name );
-
- return $agency_id_href->{agency_id};
-}
-
-# Takes an agency id and an program name, returns a program name
-sub translate_program_name {
- my $dbh = shift;
- my $agency_id = shift;
- my $program_name = shift;
-
- my $sql
- = "SELECT DISTINCT on (program_id) program_id FROM data_entry_program WHERE agency_id = ? AND program_name = ?";
- my $program_id_href = $dbh->selectrow_hashref( $sql, { Slice => {} },
- $agency_id, $program_name );
-
- return $program_id_href->{program_id};
-}
-
# Update a unique record in the given database table.
# Arguments:
# dbh - a database handle.
@@ -633,14 +598,6 @@
TODO
-=head2 translate_agency_name
-
- TODO
-
-=head2 translate_program_name
-
- TODO
-
=head2 update_one_record
TODO
=== modified file 'lib-mvhub/lib/MVHub/Utils/Setup.pm'
--- lib-mvhub/lib/MVHub/Utils/Setup.pm 2010-06-25 14:51:40 +0000
+++ lib-mvhub/lib/MVHub/Utils/Setup.pm 2010-06-28 17:27:29 +0000
@@ -122,14 +122,8 @@
{ type => SCALAR },
);
- my $sql = <<"SQL";
-SELECT
- datname
-FROM
- pg_database
-WHERE
- datname = ?
-SQL
+ my $sql
+ = MVHub::Utils::DB::get_sql_select_statement('PG_DATABASE_X_DATNAME');
my $matching_dbs_aref = $dbh->selectall_arrayref( $sql, {}, ($db_name) );
return scalar @$matching_dbs_aref;
@@ -143,14 +137,7 @@
{ type => SCALAR },
);
- my $sql = <<"SQL";
-SELECT
- usename
-FROM
- pg_user
-WHERE
- usename = ?
-SQL
+ my $sql = MVHub::Utils::DB::get_sql_select_statement('PG_USER_X_USENAME');
my $matching_db_users_aref
= $dbh->selectall_arrayref( $sql, {}, ($username) );
@@ -164,14 +151,8 @@
{ type => SCALAR },
);
- my $sql = <<"SQL";
-SELECT
- rolname,rolcreaterole,rolcreatedb
-FROM
- pg_roles
-WHERE
- rolname = ?
-SQL
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'PG_ROLES_X_ROLNAME_CREATEROLE_CREATEDB');
my $role_rows_aref
= $dbh->selectall_arrayref( $sql, { Slice => {} }, ($username) );
@@ -188,14 +169,8 @@
{ type => SCALAR },
);
- my $sql = <<"SQL";
-SELECT
- rolname,rolsuper
-FROM
- pg_roles
-WHERE
- rolname = ?
-SQL
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'PG_ROLES_X_ROLNAME_ROLSUPER');
my $role_rows_aref
= $dbh->selectall_arrayref( $sql, { Slice => {} }, ($username) );
@@ -429,12 +404,15 @@
my $version;
- my $exist_sql
- = "SELECT 1 FROM information_schema.tables WHERE table_name='version_log' LIMIT 1";
+ my $exist_sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'INFORMATION_SCHEMA_X_ONE');
+
my $table_exists = ( $dbh->selectrow_array($exist_sql) )[0];
if ($table_exists) {
- my $sql = "SELECT max(version) FROM version_log;";
+ my $sql
+ = MVHub::Utils::DB::get_sql_select_statement('VERSION_LOG_X_MAX');
+
$version = ( $dbh->selectrow_array($sql) )[0];
}
else {
Follow ups
