mvhub-dev team mailing list archive
-
mvhub-dev team
-
Mailing list archive
-
Message #00398
[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:
Dan MacNeil (omacneil)
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/29028
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-07-01 18:29:23 +0000
@@ -17,16 +17,9 @@
use MVHub::Common;
use MVHub::Reports qw/ print_report_list
- print_report
- get_sql_num_and_percent_up_to_date/;
+ print_report/;
my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
-
-# These date formulas cry out for a database function:
-# age formula is to account for DST anomalies
-my $age_formula = "age(last_updated + interval '2 hours')";
-my $month_formula
- = "( date_part('year', $age_formula) * 12 + date_part('month',$age_formula) ) AS months_since_update";
my $cfg
= MVHub::Utils::ConfigSimple::create_config_from( $ENV{MV_CONFIG_FILE} );
my $max_notifications = $cfg->param('NOTIFICATION.max_notifications');
@@ -43,71 +36,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-07-01 18:29:23 +0000
@@ -352,8 +352,9 @@
# difference is that for an update we re-use the existing category_id, and
# for a new category we'll make a new one.
if ( $values_href->{category_id} ) {
- $dbh->do( 'DELETE FROM category WHERE category_id = ?',
- undef, $values_href->{category_id} );
+ my $sql = MVHub::Utils::DB::get_sql_delete_statement(
+ 'CATEGORY_X_CATEGORY_ID');
+ $dbh->do( $sql, undef, $values_href->{category_id} );
}
else {
$values_href->{category_id}
@@ -470,14 +471,18 @@
# 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) );
+ my @bind_variables = ( lc($category_name) );
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'CATEGORY_X_EXISTS_ONE_CATEGORY_NAME');
+
if ($category_id) {
- $sql .= " AND category_id <> " . $dbh->quote($category_id);
+ @bind_variables = ( lc($category_name), $category_id );
+ $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'CATEGORY_X_EXISTS_ONE_CATEGORY_NAME_NOT_ID');
}
- $sql = "SELECT EXISTS($sql)";
- my @row = $dbh->selectrow_array($sql);
+ 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-07-01 18:29:23 +0000
@@ -259,52 +259,27 @@
my %args = @_;
my $agency_name = delete $args{'agency_name'};
my $agency_id = delete $args{'agency_id'};
- my $table = delete $args{'table'};
MVHub::Utils::assert( defined $agency_name && $agency_name ne "",
"Missing agency_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(agency_name) = "
- . $dbh->quote( lc($agency_name) );
+ my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
+
+ my @bind_variables = ($agency_name);
+ my $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'AGENCY_X_IS_NAME_AVAILABLE');
if ( $agency_id != $MVHub::Common::NO_AGENCY_ID ) {
- $stmt .= " AND agency_id <> " . $dbh->quote($agency_id);
+ @bind_variables = ( $agency_name, $agency_id );
+ $sql = MVHub::Utils::DB::get_sql_select_statement(
+ 'AGENCY_X_IS_NAME_AVAILABLE_NOT_ID');
}
- my @row = $dbh->selectrow_array($stmt);
+ my @row = $dbh->selectrow_array( $sql, undef, @bind_variables );
return ( $row[0] == 0 );
}
-sub is_valid_agency_id {
- my %args = @_;
- my $agency_id = delete $args{'agency_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 agency_id=?";
- my @row = $dbh->selectrow_array( $stmt, undef, $agency_id );
-
- 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 )
= @_;
@@ -367,7 +342,6 @@
if (!is_agency_name_unique(
agency_name => $form->field('agency_name'),
agency_id => $form->field('agency_id'),
- table => 'agency'
)
)
{
=== 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-07-01 18:29:23 +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
@@ -794,8 +796,8 @@
MVHub::Utils::assert( ( scalar keys %args ) == 0,
"Unknown arguments: @{[%args]}" );
- my $sql = "INSERT INTO program_category (program_id, category_id) "
- . "values (?, ?)";
+ my $sql = MVHub::Utils::DB::get_sql_insert_statement(
+ 'PROGRAM_CATEGORY_X_REQUIRED_FIELDS');
my $sth = $dbh->prepare($sql);
@@ -811,10 +813,11 @@
MVHub::Utils::assert( ( scalar keys %args ) == 0,
"Unknown arguments: @{[%args]}" );
- my $sql = "DELETE FROM program_category WHERE program_id = "
- . $dbh->quote($program_id);
+ my @bind_variables = ($program_id);
+ my $sql = MVHub::Utils::DB::get_sql_delete_statement(
+ 'PROGRAM_CATEGORY_X_PROGRAM_ID');
- $dbh->do($sql);
+ $dbh->do( $sql, undef, @bind_variables );
}
sub hide_program {
@@ -835,12 +838,11 @@
. (
$is_admin ? 'the MVHub Administrator' : 'the agency account holder' )
. "<BR>Reason: <i>$reason</i>";
- my $sql
- = "UPDATE program SET hidden_reason = "
- . $dbh->quote($reason)
- . "WHERE program_id = "
- . $dbh->quote($program_id);
- $dbh->do($sql);
+
+ my @bind_variables = ( $reason, $program_id );
+ my $sql = MVHub::Utils::DB::get_sql_update_statement(
+ 'PROGRAM_X_HIDDEN_REASON');
+ $dbh->do( $sql, undef, @bind_variables );
if ( !$is_admin ) {
send_admin_notification(
@@ -865,8 +867,10 @@
"Unknown arguments: @{[%args]}" );
my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
- my $sql = "UPDATE program SET hidden_reason = NULL WHERE program_id = "
- . $dbh->quote($program_id);
+
+ my @bind_variables = ($program_id);
+ my $sql = MVHub::Utils::DB::get_sql_update_statement(
+ 'PROGRAM_X_HIDDEN_REASON_IS_NULL');
# Send the e-mail before executing the db query, so we can get the hidden_reason
if ( !$is_admin ) {
@@ -877,7 +881,7 @@
);
}
- $dbh->do($sql);
+ $dbh->do( $sql, undef, @bind_variables );
MVHub::Common::display_agency_home_page_and_exit(
agency_id => $agency_id,
@@ -900,12 +904,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
@@ -917,9 +924,12 @@
);
}
- my $rows_affected
- = $dbh->do( "DELETE FROM program WHERE program_id = "
- . $dbh->quote($program_id) );
+ my @bind_variables = ($program_id);
+ my $sql = MVHub::Utils::DB::get_sql_delete_statement(
+ 'PROGRAM_X_PROGRAM_ID');
+
+ my $rows_affected = $dbh->do( $sql, undef, @bind_variables );
+
MVHub::Utils::assert( $rows_affected == 1,
"Expected 1 row removed; got $rows_affected" );
}
@@ -942,15 +952,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 +1282,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-29 18:34:47 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/reports.pl 2010-07-01 18:29:23 +0000
@@ -17,16 +17,9 @@
use MVHub::Utils::ConfigSimple qw/create_config_from/;
use MVHub::Common;
use MVHub::Reports qw/ print_report_list
- print_report
- get_sql_num_and_percent_up_to_date/;
+ print_report/;
my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
-
-# These date formulas cry out for a database function:
-# age formula is to account for DST anomalies
-my $age_formula = "age(last_updated + interval '2 hours')";
-my $month_formula
- = "( date_part('year', $age_formula) * 12 + date_part('month',$age_formula) ) AS months_since_update";
my $cfg
= MVHub::Utils::ConfigSimple::create_config_from( $ENV{MV_CONFIG_FILE} );
my $max_notifications = $cfg->param('NOTIFICATION.max_notifications');
@@ -41,22 +34,16 @@
# /
# },
{ title => 'Agency Addition Dates',
- comment => "Date when each agency was added to the site .",
- sql => qq/
- SELECT agency_name, date_created
- FROM agency
- ORDER BY date_created;
- /
+ comment => "This gives the date when each agency was added to MVHub.",
+ sql => MVHub::Utils::DB::get_sql_select_statement(
+ 'AGENCY_X_AGENCY_NAME_DATE_CREATED')
},
- { title => 'Program Addition Dates',
- comment => "Date when each program was added to the site.",
- 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;
- /
+ { title => 'Program Addition Dates',
+ comment =>
+ "This gives the date when each program was added to MVHub.",
+ sql => MVHub::Utils::DB::get_sql_select_statement(
+ 'AGENCY_X_PROGRAM_NAME_AGENCY_NAME_DATE_CREATED')
},
{ title => 'All Searches',
@@ -68,18 +55,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',
@@ -87,96 +64,42 @@
'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',
- sql => 'SELECT * FROM ('
- . MVHub::Reports::get_sql_num_and_percent_up_to_date( $dbh,
- 'agency', 'agencies', 6 )
- . ') as agency_query, ('
- . MVHub::Reports::get_sql_num_and_percent_up_to_date( $dbh,
- 'program', 'programs', 6 )
- . ') as program_query '
-
+ sql => MVHub::Utils::DB::get_sql_select_statement(
+ 'AGENCY_PROGRAM_X_SEARCHERS_PER_DAY')
},
{ title =>
'Age (in months) of Agency and Program Records since they were Last Updated',
- sql => qq/
- SELECT * FROM (
- ( SELECT $month_formula, COUNT(*) AS agencies
- FROM agency GROUP BY months_since_update
- ) AS ac_t
- FULL OUTER JOIN
- ( SELECT $month_formula, COUNT(*) AS programs
- FROM program GROUP BY months_since_update
- ) AS pc_t
- USING ( months_since_update )
- )
- /
+ sql => MVHub::Utils::DB::get_sql_select_statement(
+ 'MONTHS_X_AGENCY_PROGRAM_RECORD_LAST_UPDATE')
},
+
{ title =>
'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',
+ sql => MVHub::Utils::DB::get_sql_select_statement(
+ 'CATEGORY_PROGRAM_CATEGORY_X_NUMBER_OF_PROGRAMS_UNDER_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
- /
- },
{ title => 'Number of Categories used by each Program',
-
-# We use a left join below so that we can expose the (unlikely) event of a
-# a program with no categories. The nested query was necessary(?) to pull
-# in the agency name (since apparently you can't pull in 3 tables in a single
-# join). We include the agency name because the program name by itself often isn't
-# sufficiently identifying to the reader.
- sql => q/
- SELECT subq.program_and_agency_name, count(pc.category_id) AS categories
- FROM
- (SELECT p.program_id,
- p.program_name || ' (' || a.agency_name || ')' AS program_and_agency_name
- FROM program p, agency a
- WHERE p.agency_id = a.agency_id
- ) AS subq
- LEFT OUTER JOIN program_category pc
- ON (subq.program_id = pc.program_id)
- GROUP BY subq.program_and_agency_name
- ORDER BY categories DESC, subq.program_and_agency_name
- /
+ sql => MVHub::Utils::DB::get_sql_select_statement(
+ 'PROGRAM_X_NUMBER_OF_CATEGORIES_USED_BY_EACH_PROGRAM')
},
{ title => 'Program Names that Repeat the Agency Name',
- sql => q/
- SELECT program_name, agency_name FROM program p, agency a
- WHERE p.agency_id = a.agency_id
- and program_name ~ agency_name;
- /
+ sql => MVHub::Utils::DB::get_sql_select_statement(
+ 'PROGRAM_X_NAMES_THAT_REPEAT_AGENCY_NAME')
},
{ title => 'All Category Searches, in Order of Frequency Performed',
@@ -186,28 +109,16 @@
match with a score of .67. A high score of 0 means there were no matching categories. "
. "(Note: The search terms displayed are word stems (aka roots). A stem of 'hous' represents searches for 'house',
'housing' or 'houses', which we interpret here as being equivalent.)",
-
-# TODO: In the following query, max(top_score) tells us: among all the times
-# a particular phrase was searched for, what was the greatest score it yielded.
-# What we probably would prefer is the latest hit score it yielded.
- sql => qq/
- SELECT phrase_stems, count(*) as no_of_searches, max(top_score) as high_score
- FROM category_search_log
- GROUP BY phrase_stems ORDER BY no_of_searches DESC, high_score DESC
- /
+ sql => MVHub::Utils::DB::get_sql_select_statement(
+ 'CATEGORY_SEARCH_LOG_X_ORDER_OF_FREQUENCY_PERFORMED')
},
{ title => 'Category Searches Peformed that Return No Matches',
comment =>
"(Note: The search terms displayed are word stems (aka roots). A stem of 'hous' represents searches for 'house',
'housing' or 'houses', which we interpret here as being equivalent.)",
- sql => qq/
- SELECT phrase_stems, count(*) as no_of_searches
- FROM category_search_log
- GROUP BY phrase_stems
- HAVING max(top_score) = 0
- ORDER BY no_of_searches DESC
- /
+ sql => MVHub::Utils::DB::get_sql_select_statement(
+ 'CATEGORY_SEARCH_X_PERFORMED_THAT_RETURN_NO_MATCHES')
},
# {
@@ -223,12 +134,8 @@
{ title => 'Programs with Newsletters',
comment =>
'All of the following programs have newsletters (though only some provided descriptions).',
- sql => qq/
- SELECT get_agency_name(agency_id) as agency_name, program_name,
- email, main_phone, news_letter_description
- FROM program WHERE news_letter = 'Yes'
- ORDER BY agency_name, program_name;
- /
+ sql => MVHub::Utils::DB::get_sql_select_statement(
+ 'PROGRAM_X_HAS_NEWSLETTER')
}
); # @REPORTS
=== added file 'app-mvhub/conf/sql_delete.lib'
--- app-mvhub/conf/sql_delete.lib 1970-01-01 00:00:00 +0000
+++ app-mvhub/conf/sql_delete.lib 2010-07-01 18:29:23 +0000
@@ -0,0 +1,47 @@
+# contain all sql delete statements
+
+# queries should be named with this convention
+
+ # TABLE_NAME_TABLE_NAME_X_DESCRIPTION
+
+# If you can't think of a good description for the query, just list out
+# the 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_X_AGENCY_ID]
+DELETE FROM agency
+WHERE agency_id = ?
+
+[CATEGORY_X_CATEGORY_ID]
+DELETE FROM category
+WHERE category_id = ?
+
+[PROGRAM_CATEGORY_X_PROGRAM_ID]
+DELETE FROM program_category
+WHERE program_id = ?
+
+[PROGRAM_X_PROGRAM_ID]
+DELETE FROM program
+WHERE program_id = ?
+
+[SYNONYM_STEM_X_ALL]
+DELETE FROM ?
=== modified file 'app-mvhub/conf/sql_insert.lib'
--- app-mvhub/conf/sql_insert.lib 2010-04-01 18:19:48 +0000
+++ app-mvhub/conf/sql_insert.lib 2010-07-01 18:29:23 +0000
@@ -24,6 +24,18 @@
'contact_first_name','contact_last_name','contact_phone','contact_email_available'
);
+[PROGRAM_CATEGORY_X_REQUIRED_FIELDS]
+INSERT INTO program_category
+(
+ program_id,
+ category_id
+)
+VALUES
+(
+ ?,
+ ?
+);
+
[PROGRAM_X_REQUIRED_FIELDS]
INSERT INTO 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-07-01 18:29:23 +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,73 @@
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 ~* E'(unknown|contact|\\\\?)'
+ or p.email ~* 'unknown'
+ or p.contact_email ~* 'unknown'
+ or p.contact_first_name ~* E'(unknown|contact|\\\\?)'
+ or p.contact_last_name ~* E'(unknown|contact|\\\\?)'
+)
+and a.agency_id = p.agency_id
+
+[AGENCY_PROGRAM_X_SEARCHERS_PER_DAY]
+SELECT *
+FROM
+(
+ SELECT updated_records.num
+ || ' of '
+ || all_records.num
+ || ' ('
+ || ((updated_records.num * 100) / all_records.num)
+ || E'\% )' as agencies
+ FROM
+ ( SELECT count(*) as num
+ FROM agency
+ WHERE last_updated > (current_date - interval '6 months')
+ )as updated_records,
+
+ ( SELECT count(*) as num FROM agency
+ )as all_records
+
+) as agency_query,
+
+(
+ SELECT updated_records.num
+ || ' of '
+ || all_records.num
+ || ' ('
+ || ((updated_records.num * 100) / all_records.num)
+ || E'\% )' as programs
+ FROM
+ ( SELECT count(*) as num
+ FROM program
+ WHERE last_updated > (current_date - interval '$6 months')
+ )as updated_records,
+
+ ( SELECT count(*) as num FROM program
+ )as all_records
+) as program_query
+
+
+[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 +207,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 ~* E'(unknown|contact|\\\\?)'
+ or email ~* 'unknown'
+ or contact_email ~* 'unknown'
+ or contact_first_name ~* E'(unknown|contact|\\\\?)'
+ or contact_last_name ~* E'(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 +252,8 @@
FROM program
WHERE contact_email = ?
)
-
+
+
[AGENCY_X_ALL]
SELECT
*
@@ -198,6 +303,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,
@@ -217,11 +356,28 @@
AND
reminders_sent < ?
+[AGENCY_X_IS_NAME_AVAILABLE]
+SELECT COUNT(*)
+FROM agency
+WHERE LOWER(agency_name) = ?
+
+[AGENCY_X_IS_NAME_AVAILABLE_NOT_ID]
+SELECT COUNT(*)
+FROM agency
+WHERE LOWER(agency_name) = ?
+AND agency_id <> ?
+
[AGENCY_X_LAST_UPDATED]
SELECT last_updated
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 +392,7 @@
hidden=false
ORDER BY
agency_name
-
+
[AGENCY_X_WEBSITE]
SELECT
website
@@ -282,6 +438,14 @@
FROM category c, heading_category h
WHERE h.category_id = c.category_id
+
+# We use a left join below so that we also see which categories are unused.
+[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
@@ -315,6 +479,22 @@
phrase_stems = ?
)
+
+# TODO: In the following query, max(top_score) tells us: among all the times
+# a particular phrase was searched for, what was the greatest score it yielded.
+# What we probably would prefer is the latest hit score it yielded.
+[CATEGORY_SEARCH_LOG_X_ORDER_OF_FREQUENCY_PERFORMED]
+SELECT phrase_stems, count(*) as no_of_searches, max(top_score) as high_score
+FROM category_search_log
+GROUP BY phrase_stems ORDER BY no_of_searches DESC, high_score DESC
+
+[CATEGORY_SEARCH_X_PERFORMED_THAT_RETURN_NO_MATCHES]
+SELECT phrase_stems, count(*) as no_of_searches
+FROM category_search_log
+GROUP BY phrase_stems
+HAVING max(top_score) = 0
+ORDER BY no_of_searches DESC
+
[CATEGORY_X_CATEGORY_NAME]
SELECT
category_name
@@ -322,7 +502,21 @@
category
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
@@ -340,6 +534,74 @@
ORDER BY
heading_name
+[INFORMATION_SCHEMA_X_ONE]
+SELECT 1
+FROM information_schema.tables
+WHERE table_name='version_log'
+LIMIT 1
+
+[MONTHS_X_AGENCY_PROGRAM_RECORD_LAST_UPDATE]
+SELECT *
+FROM (
+ ( SELECT ( date_part('year', age(last_updated + interval '2 hours')) * 12 + date_part('month', age(last_updated + interval '2 hours')) ) AS months_since_update,
+ COUNT(*) AS agencies
+ FROM agency GROUP BY months_since_update
+ ) AS ac_t
+ FULL OUTER JOIN
+ ( SELECT ( date_part('year', age(last_updated + interval '2 hours')) * 12 + date_part('month', age(last_updated + interval '2 hours')) ) AS months_since_update,
+ COUNT(*) AS programs
+ FROM program GROUP BY months_since_update
+ ) AS pc_t
+ USING ( months_since_update )
+)
+
+[NEXTVAL_X_SEQUENCE]
+SELECT nextval(?)
+
+[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 +650,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
@@ -420,6 +709,12 @@
AND p.last_updated < ?
AND p.reminders_sent < ?
+[PROGRAM_X_HAS_NEWSLETTER]
+SELECT get_agency_name(agency_id) as agency_name, program_name,
+ email, main_phone, news_letter_description
+FROM program WHERE news_letter = 'Yes'
+ORDER BY agency_name, program_name;
+
[PROGRAM_X_LAST_UPDATED]
SELECT
last_updated
@@ -427,6 +722,30 @@
program
WHERE program_id = ?
+[PROGRAM_X_NAMES_THAT_REPEAT_AGENCY_NAME]
+SELECT program_name, agency_name FROM program p, agency a
+WHERE p.agency_id = a.agency_id
+ and program_name ~ agency_name;
+
+
+# We use a left join below so that we can expose the (unlikely) event of a
+# a program with no categories. The nested query was necessary(?) to pull
+# in the agency name (since apparently you can't pull in 3 tables in a single
+# join). We include the agency name because the program name by itself often isn't
+# sufficiently identifying to the reader.
+[PROGRAM_X_NUMBER_OF_CATEGORIES_USED_BY_EACH_PROGRAM]
+SELECT subq.program_and_agency_name, count(pc.category_id) AS categories
+FROM
+( SELECT p.program_id,
+ p.program_name || ' (' || a.agency_name || ')' AS program_and_agency_name
+ FROM program p, agency a
+ WHERE p.agency_id = a.agency_id
+) AS subq
+LEFT OUTER JOIN program_category pc
+ON (subq.program_id = pc.program_id)
+GROUP BY subq.program_and_agency_name
+ORDER BY categories DESC, subq.program_and_agency_name
+
[PROGRAM_X_PROGRAM_AGENCY_ID]
SELECT program_id,
get_agency_name(agency_id) || ': ' || program_name
@@ -458,17 +777,23 @@
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
+
+[SETVAL_X_SYNONYM_SEQ]
+SELECT setval(?, ?, false)
+
+[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 +816,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/conf/sql_update.lib'
--- app-mvhub/conf/sql_update.lib 2010-03-31 16:11:24 +0000
+++ app-mvhub/conf/sql_update.lib 2010-07-01 18:29:23 +0000
@@ -39,6 +39,16 @@
agency_id = ?
AND last_updated < ?
+[PROGRAM_X_HIDDEN_REASON_IS_NULL]
+UPDATE program
+SET hidden_reason = NULL
+WHERE program_id = ?
+
+[PROGRAM_X_HIDDEN_REASON]
+UPDATE program
+SET hidden_reason = ?
+WHERE program_id = ?
+
[PROGRAM_X_REMINDERS_SENT]
UPDATE
program
=== 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-07-01 18:29:23 +0000
@@ -40,13 +40,22 @@
'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_IS_NAME_AVAILABLE' => ['ymca'],
+ 'AGENCY_X_IS_NAME_AVAILABLE_NOT_ID' => [ 'ymca', 103553 ],
+ 'AGENCY_X_EXPIRED_RECORDS' => [ '01-01-2008', 0 ],
+ 'AGENCY_X_SEARCH_WITH_TERMS' => [
'[[:<:]](ymca|counsel|immi|shelter)[[:>:]]',
'[[:<:]](ymca|counsel|immi|shelter)[[:>:]]'
],
@@ -56,17 +65,29 @@
'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_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],
+ 'NEXTVAL_X_SEQUENCE' => ['program_id_sequence'],
+ '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 ],
@@ -75,8 +96,8 @@
'PROGRAM_X_PROGRAM_NAME' => [ '103553', 'fname', 'lname', 'a@xxxxx' ],
'PROGRAM_X_WEBSITE' => [509548],
'PROGRAM_X_LAST_UPDATED' => [509548],
+ 'SETVAL_X_SYNONYM_SEQ' => [ 'synonym_id_sequence', '900000' ],
'SYNONYM_STEM_X_WORD_STEM' => ['alcohol'],
-
);
return \%data;
}
=== modified file 'lib-mvhub/lib/MVHub/Reports.pm'
--- lib-mvhub/lib/MVHub/Reports.pm 2010-06-22 19:20:55 +0000
+++ lib-mvhub/lib/MVHub/Reports.pm 2010-07-01 18:29:23 +0000
@@ -16,7 +16,6 @@
our @EXPORT_OK = qw(
print_report_list
print_report
- get_sql_num_and_percent_up_to_date
);
sub print_report_list {
@@ -70,31 +69,3 @@
return $output;
}
-# Returns a SQL statement that selects
-# the number and percentage of records from table $table
-# updated in the past $prior_months, as a single column, named
-# $table_plural.
-sub get_sql_num_and_percent_up_to_date {
- my $dbh = shift;
- my $table = shift;
- my $table_plural = shift;
- my $prior_months = shift;
- my $sql = qq [
- SELECT updated_records.num
- || ' of '
- || all_records.num
- || ' ('
- || ((updated_records.num * 100) / all_records.num)
- || '\% )' as $table_plural
- FROM
- ( SELECT count(*) as num
- FROM $table
- WHERE last_updated > (current_date - interval '$prior_months months')
- )as updated_records,
-
- ( SELECT count(*) as num FROM $table
- )as all_records
- ];
- return $sql;
-}
-
=== 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-07-01 18:29:23 +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;
@@ -55,11 +56,14 @@
my $dbh = MVHub::Utils::DB::get_dbh();
$dbh->{AutoCommit} = 0;
- $dbh->do("DELETE FROM $SYNONYM_TABLE");
+ my @bind_variables = ($SYNONYM_TABLE);
+ my $sql
+ = MVHub::Utils::DB::get_sql_delete_statement('SYNONYM_STEM_X_ALL');
+ $dbh->do( $sql, undef, @bind_variables );
- $dbh->do(
- "SELECT setval('$SYNONYM_SEQUENCE', $SYNONYM_SEQUENCE_INIT_VAL, false)"
- );
+ @bind_variables = ( $SYNONYM_SEQUENCE, $SYNONYM_SEQUENCE_INIT_VAL );
+ $sql = MVHub::Utils::DB::get_sql_select_statement('SETVAL_X_SYNONYM_SEQ');
+ $dbh->do( $sql, undef, @bind_variables );
foreach my $synonym_line (@synonym_lines) {
$synonym_line = MVHub::Utils::trim($synonym_line);
=== 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-07-01 18:29:23 +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;
}
@@ -136,8 +124,9 @@
# worry about race conditions.
sub get_next_in_sequence {
my ( $dbh, $sequence_name ) = @_;
- my $sql = "SELECT nextval('$sequence_name')";
- my @row = $dbh->selectrow_array($sql);
+ my $sql
+ = MVHub::Utils::DB::get_sql_select_statement('NEXTVAL_X_SEQUENCE');
+ my @row = $dbh->selectrow_array( $sql, undef, $sequence_name );
MVHub::Utils::assert(
@row == 1,
"Expected one row from $sequence_name sequence selection; "
@@ -150,7 +139,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;
@@ -201,6 +191,20 @@
return $result;
}
+sub get_sql_delete_statement {
+ my $statement_name = shift
+ or croak "missing parameter: \$statement_name\n";
+ my $cfg = MVHub::Utils::ConfigSimple::create_config_from(
+ $ENV{MV_CONFIG_FILE} );
+ my $conf_path = $cfg->param('ABSOLUTE_PATH.conf_dir');
+ my $file = "$conf_path/sql_delete.lib";
+ my $sql_lib = new SQL::Library { lib => $file };
+ my $result = $sql_lib->retr($statement_name);
+ croak "sql_delete.lib: '$statement_name' query does not exist\n"
+ if !( defined $result );
+
+ return $result;
+}
#################################
# Insert a record in the given database table.
@@ -238,11 +242,12 @@
}
sub remove_agency {
- my $dbh = shift;
- my $agency_id = shift;
+ my $dbh = shift;
+ my $agency_id = shift;
+ my @bind_variables = ($agency_id);
my $sql
- = "DELETE FROM agency WHERE agency_id = " . $dbh->quote($agency_id);
- $dbh->do($sql);
+ = MVHub::Utils::DB::get_sql_delete_statement('AGENCY_X_AGENCY_ID');
+ $dbh->do( $sql, undef, @bind_variables );
}
# Returns programs for the given agency ordered by program_name
@@ -293,9 +298,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 +426,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 +614,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-30 17:11:20 +0000
+++ lib-mvhub/lib/MVHub/Utils/Setup.pm 2010-07-01 18:29:23 +0000
@@ -126,14 +126,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;
@@ -147,14 +141,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) );
@@ -168,14 +155,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) );
@@ -192,14 +173,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) );
@@ -433,12 +408,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
