Automated SQL Report Scripting (Draft)

This document is a scratchpad created quickly during the 2011 Evergreen Conference.

NOTE: Updates can be found at the permanent location - evergreen-reports:automated_sql_reports.

1. Setup or make use of a database user that can run SELECT queries against your database server.
2. Use a converter script to change .csv to .xls file formatting.
3. Install and configure Mutt for a text-based e-mail client to automatically send the finished reports to the library.

1. Run SQL query against database to retrieve data and produce output in CSV format.
2. Add title of report and edit report with additional elements.
3. Use converter script to change from CSV to XLS format.
4. Generate e-mail text to include, and then attach XLS to email to library.

The following sample scripts are used to provide daily e-mailed reports for the previous day's circulation for a given library.

• The directory where we store scripts is in /home/<user>/sql where <user> will be the name of the user you setup to run these scripts with.
• Change other related anonymized places within this script with the appropriate values for your organization. Someday these should be updated to use more variables to allow for further customization or simpler setup instead of hardcoded values.
  • <db_user> = user that can access postgres database where Evergreen is running
  • <db_hostname> = hostname for Evergreen database server, usually localhost if single-server
  • <db_name> = usually "evergreen" if using default configuration options
  • The RECIPIENT variable defines the e-mail address you will be sending to

#!/bin/sh
 
# TEST_daily_stats.sh
#
# Script for generating daily circ stats for TEST Library
 
# Define our variables
 
BASEDIR=/home/<user>/sql
DATE=$(date --date='1 day ago' '+%F')
FOLDER=TEST
ORG=TEST
RECIPIENT="test@library.org"
 
# Run the SQL report to get csv output
 
psql -U <db_user> -h <db_hostname> -f $BASEDIR/$FOLDER/$ORG'_daily_stats'.postgresql <db_name>
 
# Insert Title at beginning and Delete the last line from the csv (number of rows)
 
sed -e '1 i Daily Circ for '$DATE'|' -e '1 i \ |' -e '$d' $BASEDIR/$FOLDER/$ORG'_daily_stats2'.csv > $BASEDIR/$FOLDER/$ORG'_daily_stats'.csv
 
rm $BASEDIR/$FOLDER/$ORG'_daily_stats2'.csv
 
# Convert csv to xls
 
$BASEDIR/csv2xls.pl -s '|' -v 0 -o $BASEDIR'/'$FOLDER'/'$ORG'_daily_circ_'$DATE $BASEDIR/$FOLDER/$ORG'_daily_stats'.csv
 
# Create email text for the day
 
$BASEDIR/$FOLDER/$ORG'_daily_text'.sh > $BASEDIR/$FOLDER/$ORG'_email_text_'$DATE.txt
 
# E-mail out the report
 
mutt -s "Daily Circ for $DATE" -a $BASEDIR/$FOLDER/$ORG'_daily_circ_'$DATE.xls -- $RECIPIENT < $BASEDIR/$FOLDER/$ORG'_email_text_'$DATE.txt
 
# Remove leftovers
 
rm $BASEDIR/$FOLDER/$ORG'_daily_stats'.csv
rm $BASEDIR/$FOLDER/$ORG'_daily_circ_'$DATE.xls
rm $BASEDIR/$FOLDER/$ORG'_email_text_'$DATE.txt

This file is called TEST_daily_circ.postgresql. It creates an output file used by the control script above. The path for the output is hardcoded into the first line of the SQL with the \o. The \f | indicates that | will be used as the delimiter between columns.

\o /home/<user>/SQL/TEST/TEST_daily_stats2.csv
\a
\f |
SELECT acl.name AS "Shelving Location", COUNT(acirc.id) AS "# of Circs"
   FROM action.circulation acirc
   JOIN asset.copy acopy ON acopy.id = acirc.target_copy
   JOIN asset.copy_location acl ON acopy.location = acl.id
  WHERE acirc.circ_lib = '11'
   AND date_trunc('DAY'::text, acirc.create_time) = date_trunc('DAY'::text, now() - '1 day'::INTERVAL)
  GROUP BY acl.name
UNION 
 SELECT 'ZZZ Total' AS "Shelving Location", COUNT(acirc.id) AS "# of Circs"
   FROM action.circulation acirc
   JOIN asset.copy acopy ON acopy.id = acirc.target_copy
   JOIN asset.copy_location acl ON acopy.location = acl.id
  WHERE acirc.circ_lib = '11'
   AND date_trunc('DAY'::text, acirc.create_time) = date_trunc('DAY'::text, now() - '1 day'::INTERVAL)
ORDER BY 1;
\o

#!/bin/bash
 
# TEST_daily_text.sh
#
# Script for generating daily email body text for TEST Library
 
DATE=$(date --date='1 day ago' '+%A, %B %d, %Y')
 
echo "Hi,
 
Attached is your daily circ report for "$DATE". Please let us know if you have any questions.
 
Thank you,
 
Your Helpdesk"

• Have less hardcoded values in the script; customize variables that can be set or passed to the shell script during run.
• Add some additional options to the SQL query run:
  • Use \t to output tuples instead of ugly hack with sed removing the row count.
  • Make some components of the query into variables that can be passed via script.
  • Perhaps consider moving the query as a saved view in the database