Adams 12 Five Star Schools (K-12) Case Study

Adams 12 Five Star Schools is very pleased to announce our newly-redesigned, SQL Server-powered Drupal Web site! Our site exemplifies the power and flexibility of Drupal. It is also speaks to the quality of community that helps develop, instruct, and support the Drupal project. Without this exceptional and active community, our site would not have been possible. We're continually working to improve our site and welcome your feedback. (Sorry for the delay, I had promised this write up a while ago but have just now had the time to put it together).

Here's a brief overview of the work we've done thus far:

Background

Adams 12 Five Star Schools is located in the greater Denver, CO area and serves 40,000 K-12 Students. We employ over 5,000 school and administrative staff and currently have 52 schools. Our district site used to be ASP-driven with limited content management capabilities. The move to Drupal was slow due to the fact that we had to integrate with existing closed, Windows-based systems and software. Our District site as well as several school sites now run on Drupal 5.x. Both the District and school sites are managed by the district Webmaster (me). Site content is updated by various trained staff.

Sites

All Sites share the following:

• Drupal 5.x
• Windows Server 2003 / IIS 6 / SQL Server 2000
• Various modules

District Site

• http://www.adams12.org
• Separate SQL Server DB
• Custom theme based on Blue Breeze
• Updated by district staff

School Sites

• Example sites: http://hunters.adams12.org, http://artsmagnet.adams12.org, http://mcelwain.adams12.org
• 12 Elementary (soon to be 16)
• Stock Garland theme
• Multi-site Setup
• Custom Profile enabling us to set up new school sites quickly
• Updated by school staff

Backend

Database

Due to IT limitations, we've had to customize a database.mssql.inc abstraction layer to allow our site to work with SQL Server :( We're in the process of contributing back our work to the community, but more testing needs to be done. We're open to sharing it with others, but figure it's more useful to port everything to 6.x than release on 5.x. Ideally, we'd like to move toward MySQL.

For our school sites, we share the following tables in our settings.php for each site to minimize the amount of individual administration: access, filter_formats, filters, ldapauth, permission, role, sequences, term_*, tinymce_*, vocabulary_*.

Servers

We have one Windows server behind our firewall dedicated for development and two production servers (one for schools and the other for the District site). Production servers are updated via a SVN export from development.

School Install Profile

We developed a custom install profile for our multisite schools. When a new school site is installed, it automatically creates a standard set of menu items, nodes, admin user, blocks, modules, content types, theme settings, roles, permissions, variables, IMCE settings, LDAP settings, TinyMCE settings, and taxonomies. If something already exists, like a role, the install simply skips the creation of that item (since a lot of our tables are shared in our multisite setup). Some of our work here has been contributed back into the CRUD / Installation Profile Module.

Revision Control

We use SVN to manage our code base. Our SVN repository looks something like this:

/drupal
  /tags (snapshots of past code)
    /drupal_mssql-5.1
    /drupal_mssql-5.6
  /trunk (main development)
    /drupal_mssql
      /core (includes all core Drupal modules, includes, etc.)
      /district (includes custom theme, files, profiles, and sites folders as well as root files like index.php, cron.php, etc.)
      /schools (custom profile, sites, themes, and root folder files like index.php, cron.php, etc.)
      /modules (all modules that can be chosen to reside in respective /sites/all/modules folders)

Using svn:externals, I am able to assign which modules should be checked out along with either a district or school checkout. So, when I check out district, along with it comes /core and which ever /modules I assign. The root files have to be duplicated in each of the /district and /schools because you can't (please mark me wrong) check out individual files from SVN - just folders with files in them.

Modules

We have ported a number of modules to work with SQL Server (mostly adjusting each module's .install file to use MSSQL syntax).

District Site
Admin menu, ldapauth, audio, CCK, Views, node import, smtp, i18n, active select, date api, google analytics, imce, nice menus, nodeaccess, simplenews, slicedmenu, tinyMCE, webform, views group by, and some other sub modules of those mentioned.

School Sites
(some of those listed above) as well as user registration notification, rollout (although discontinued use of this and need to revisit - allows installation of modules over multisites). I know there are many more multisite modules we could be using and I need to see what the rest of you are using to manage your sites...

Clean URLS

Since we're using Windows machines, we use the ISAPI Rewrite pacakge. We had a lot of difficulty getting the URL rewriting to work correctly, so I'm posting our rewrite rules below in hopes of helping anyone else in our situation (pay particular attention to the first rewrite rule (modules/system and modules/build were needed to allow the site to work when using modules and adding them through the admin) and the QSA flag (needed to allow querystrings to work correctly)).

Files

We manage our files with IMCE. I patched it to allow for a role to have access to one, all, or some folders within our root /files folder. I assign each role access to certain folder(s) and instruct those with access to multiple folders (like a secretary responsible for multiple departments) to keep in mind which folder to upload files to based on what type of information that file contains (e.g., job_descriptions.pdf should go under "/files/hr" and not "/files/outdoor_education."

Our directory structure is somewhat flat, but works well for what we have - it looks something like this:

/files
  /hr
  /district
  /schools (refers to each school info page we have on the district site and not the individual school sites themselves)
    /images (images of what each school looks like for the school overview page)
    /documents (supply lists, state testing results, registration forms)
  /outdoor_ed
  /staff_development
  /lunch
  /superintendent
  /it
  /boundary_maps

Training

A training manual (attached) supplements individual one-on-one training. Mass training wasn't possible due to each department's distinct needs. We currently have at least one person at each school and over 20 people in the main district building who update content on a regular basis.

So far, I have had very few complains or calls asking how to edit text or upload documents. As long as things are explained clearly and the user is empowered, I think Drupal is fairly easy for people to use (as far as basic node editing goes).

Custom Modules

Boundary Locator
The Boundary Locator module (not released) provides separate tables to store over 200,000 address records for users to search to see which school their child will attend. It's very basic, but nothing existed on drupal.org that met our needs. Use the "example" address to try it out.

Internationalization

We have a very culturally diverse community and try our best to accommodate their needs. Currently we have some pages translated into Spanish with more to come. We only have one dedicated Spanish translator on staff, so their time limited for translating site content. They have been trained, however, to use i18n and i18n Menus to convert site content and menus to read in English and Spanish. Remembering to translate menu items in addition to the pages they link to is sometimes a pain, and I wish Drupal was a bit more integrated in this respect. We keep our nodes set to none/empty language until we have a translation ready, otherwise, if we click on a Spanish page, any node/page marked as "English" disappears, which causes some confusion. So, if a page is translated, it appears so, if it is not, it appears as its default English.

One think we realized is that if you have applied a URL path to a node, you need to qualify your translated page as well. For example, if node A has the URL path "nodeA," then the translated page needs to have something similar - "es/nodeA" (for Spanish). It would be nice if Drupal did this for you when a node translation was created, alas, it does not and I have to remind our translator to do this otherwise a menu item in Spanish will link to it's English text.

Future Enhancements

• We need to know when English pages are updated by various departments so that they can be updated into Spanish soon after. We are planning to do this using either an RSS-feed of recently updated nodes or some sort of Action.
• Image galleries
• Drupal 6.x (once all modules are available)
• Intranet
• Podcasts
• Fix design inconsistencies between Firefox, IE, and Safari

Questions/Comments?? Thanks for taking the time to read through this!