Requirements
- Carry over all data from the prior site, http://iacusn.org/schools/ (no longer in service)
- Searchable by country and U.S. state
- Unprivileged users (i.e. neither Editor nor Admin) may submit school listings
- But we must prevent them from posting spam or junk
ETL
Doug Lovell provided a MySQL dump of the schools and instructors tables from the iacusn database. DJ loaded that data into a new MySQL database (since deleted), then wrote a Ruby script that retrieved the records and output them as a single table in CSV format. A second script, written in Drupal PHP, builds Drupal Flight School nodes from the CSV file. These scripts and data files are stored on the IAC server in the ~webmaster/scripts/flight-schools directory.
Adding and Editing Schools
Anyone -- even anonymous users! -- may add a Flight School node. This was accomplished by configuring the Admin -> People -> Permissions page so that the anonymous user and authenticated user boxes are checked in the row labeled Flight School: Create new content.
By default, all Flight School nodes are saved in the Published state. Without any other safeguards this would be an open invitation for spammers to post their crap on our precious web site. To counter this, I created a set of Drupal rules (found here) that work as follows:
- Upon creation:
- Triggered when a Flight School node is created
- Unless the author has editor or administrator privileges
- Unpublish the node
- Send an email to Lynn Bowes and the Webmaster
- Explains that a non-privileged user added a new Flight School node
- Asks them to review the node, update it if necessary, then publish it
- Send an email to the author stating that their submission is under review
- Upon approval, send an email to the author stating that their submission is now visible to the world
As an additional precaution against spambots, the Flight School add/edit form now incorporates a honey pot.
Viewing and Filtering
The full listing of schools is implemented using a Drupal view: www.iac.org/aerobatic-flight-schools. The view consists of three preface blocks -- a disclaimer, a list of countries, and a list of U.S. states -- followed by the school listings.
The country and state blocks are themselves Drupal views that extract the unique country and state values from the Flight School nodes and format them as bullet-separated lists. Each country and state name is a hyperlink which points to aerobatic-flight-schools/<country> and aerobatic-flight-schools/usa/<state> respectively. The main view uses the <country> and usa/<state> values as contextual filters that limit the data that is displayed. If the filter values are not present (which is the default) then all schools are displayed.
Template
In order to spruce up the display of the Flight School listings, I added a custom template to our Drupal theme, stored in $D7/sites/www.iac.org/themes/iac3/node--flight-school.tpl.php.
