Merge lp:~felmas/mvhub/structure_fix into lp:mvhub

[Ferhat Elmas]

fix of structure.pl and its related modules

[Ferhat Elmas (felmas)]

Changes getting bigger so I submitted to check whether I am on the right path.

[Dan MacNeil (omacneil)]

120 - my $invocant = shift;
121 - my $class = ref($invocant) || $invocant;
122 -
123 - my $dbh = MVHub::Utils::DB::get_dbh();
124 -
125 +
126 + my $class = shift;
127 my $self = { HEADING_CATEGORIES_HREF => _fetch_heading_categories() };
128 -

I'm honestly a little vague about what the original code does. I'm pretty sure it handles calling things in a class context vs a object context, in any event it doesn't need to change ( ? ) for getting the file working again.

[Dan MacNeil (omacneil)]

109 [SYNONYM_STEM_X_ALL]
110 -DELETE FROM ?
111 +DELETE FROM synonym_stem

Looks like this was broken in the move to SQL::Library which never really tested the synonyms code

Good catch / fix

[Dan MacNeil (omacneil)]

basically goods good on code inspection

[Ferhat Elmas (felmas)]

120 - my $invocant = shift;
121 - my $class = ref($invocant) || $invocant;
122 -
123 - my $dbh = MVHub::Utils::DB::get_dbh();
124 -
125 +
126 + my $class = shift;
127 my $self = { HEADING_CATEGORIES_HREF => _fetch_heading_categories() };
128 -

Line 120-121 is shortened to 126 because $invocant is always sufficient

Line 123 is unnecessary because _fetch_heading_catgories() creates its own handle.

[Dan MacNeil (omacneil)]

this is FYI / thing to think of in future only:

this code and the orginal code put the 'view' (HTML ) in the same file as the 'model' (database) and controller ( Perl glue between them). Ideally, maybe over-engineered, model/view/controller should be in separate files to help us think of keeping them separate.

[Dan MacNeil (omacneil)]

(optional / suggestion )

479 +[005_drop_category]
482 +[006_create_category]

maybe slightly clearer as
[005_drop_category_table]
[006_create_category_table]

also for other table dropping / creating

[Ferhat Elmas (felmas)]

I had thought firstly that I should get it worked then separation can be done, I am already here for especially refactor.

After finishing tests, I will do it.

> this is FYI / thing to think of in future only:
>
> this code and the orginal code put the 'view' (HTML ) in the same file as the
> 'model' (database) and controller ( Perl glue between them). Ideally, maybe
> over-engineered, model/view/controller should be in separate files to help us
> think of keeping them separate.

[Dan MacNeil (omacneil)]

(optional / suggested)

734 +# TODO:
[...]
773 +

A lot of these comments can safely be removed. particularly the ones marked DONE. this is only a a suggestion as leaving them in is no worse than the original.

[Dan MacNeil (omacneil)]

(suggestion / optional )

81 +# TODO: trouble installing:
782 +#use Text::Capitalize;

remove these lines as they have done nothing for years.

[Dan MacNeil (omacneil)]

(stronger suggestion / optional if you really want)

784 +use MVHub::Utils;
785 +use MVHub::HTMLTemplateUtils;
786 +
787 +use MVHub::Common;
788 +use MVHub::Utils::DB;
789 +use MVHub::Headings;
790 +use MVHub::HTML;
791 +use MVHub::Page;
792 +use MVHub::Synonyms;
793 +
794 +use MVHub::Wrap::ConfigSimple;

See:

http://wiki.thecsl.org/mediawiki/index.php/Frequently_flagged_code#use_.3Clibrary.3E_statements_ordered_by_type_.283rd_party_vs_ours.29_and_then_alphabetical

[Dan MacNeil (omacneil)]

comment / FYI only

728 === added file 'lib-mvhub/lib/MVHub/Structure.pm'

most of these routines don't:
  http://wiki.thecsl.org/mediawiki/index.php/Frequently_flagged_code#Params::Validate_for_sub_routine_parameter_validation

but this was the case for the original code, so it is no worse.

Also many routines don't sanitize / check $cgi input for evil. This is also the case in the old code, so no worse, no action required for now. Also to get to structure.pl people need to authenticate. Still in long term if their password is compromised or they put in stupid as opposed to evil input we are (potentially) screwed.

[Ferhat Elmas (felmas)]

I was wondering the authentication because on mvhub.com I can access
structure.pl which can be dangerous in some weird ways.

On 07/29/2011 07:37 PM, Dan MacNeil wrote:
> comment / FYI only
>
> 728 === added file 'lib-mvhub/lib/MVHub/Structure.pm'
>
> most of these routines don't:
> http://wiki.thecsl.org/mediawiki/index.php/Frequently_flagged_code#Params::Validate_for_sub_routine_parameter_validation
>
> but this was the case for the original code, so it is no worse.
>
> Also many routines don't sanitize / check $cgi input for evil. This is also the case in the old code, so no worse, no action required for now. Also to get to structure.pl people need to authenticate. Still in long term if their password is compromised or they put in stupid as opposed to evil input we are (potentially) screwed.

[Dan MacNeil (omacneil)]

> I had thought firstly that I should get it worked then separation can be done,
> I am already here for especially refactor.

yes. exactly.

[Dan MacNeil (omacneil)]

.t files should be executable so they can be run one at a time as scripts.

find . -name '*.t' -and -not -executable

./lib-mvhub/t/Headings/heading_names.t
./lib-mvhub/t/Headings/categories.t
./lib-mvhub/t/Structure/display_structure_menu.t
./lib-mvhub/t/Structure/process_synonym_form.t
./lib-mvhub/t/Structure/fetch_existing_category_and_dependent_data_from_db.t
./lib-mvhub/t/Structure/get_all_headings.t
./lib-mvhub/t/Structure/is_category_name_taken.t
./lib-mvhub/t/Structure/get_all_programs.t

# to fix in one go.
find . -name '*.t' -and -not -executable -exec chmod +x {} \;

[Dan MacNeil (omacneil)]

1309 +#save config file
1310 +my $config_file = $ENV{MV_CONFIG_FILE};
1311 +$ENV{MV_CONFIG_FILE} = $ENV{MV_TEST_CONFIG_FILE};

All this save/restore config file stuff is not needed. %ENV gets reset for each test

review: Needs Fixing

[Dan MacNeil (omacneil)]

More precisely, we need to set $MV_CONFIG_FILE but we don't need to restore it.

$ENV{MV_CONFIG_FILE} =$ENV{MV_TEST_CONFIG_FILE};

[Dan MacNeil (omacneil)]

lib-mvhub/t/Structure/display_structure_menu.t

lib-mvhub/t/Structure/process_synonym_form.t

should probably move to

  app-mvhub/t/mech/admin

They are more integration tests (test whole app like the user) than they are unit tests (test each sub separately )

These tests will not effect test coverage because the code isn't being instrumented ( modified by Devel::Cover to measure coverage) , the code runs on the webserver, which could be in Siberia

It should not be hard to get test coverage

both routines take a CGI object as a parameter. It is pretty straight forward to dummy up a CGI object to pass in. see:

lib-mvhub/t/Utils/clean_cgi_params.t

The problem is that both routines send to stdout instead of returning a value (ugly I know)

Two options:
 a. take as given that code is tested by integration tests

b. redirect stdout to a variable and then do pattern matching on that variable to check if the expected data is there.

http://perldoc.perl.org/functions/open.html

http://www.nntp.perl.org/group/perl.beginners/2007/05/msg91387.html

review: Needs Fixing

[Dan MacNeil (omacneil)]

Generally should get this stuff from config file $cfg object , see:

perldoc MVHub::Wrap::ConfigSimple

1381 +@site_codes = qw( mvh nsp );

# just use
my $site_code = $CFG->param('SITE.website_code');

no need to test both sites, code is same.

1382 +$domain = 'testing123.net';

review: Needs Fixing

[Dan MacNeil (omacneil)]

> 1382 +$domain = 'testing123.net';

# see:

TestHelper::get_test_website_name()

# aka:

lib-mvhub/t/lib/TestHelper.pm

[Dan MacNeil (omacneil)]

1623 +is( $result, 0, 'is_category_name_taken_with_category_name_and_id' );

1629 +is( $result, 1, 'is_category_name_taken_with_category_name_and_id' );

testing two different things, test name should be different. maybe:

$msg='YES is_category_name_taken_with_category_name_and_id '
$msg='NO is_category_name_taken_with_category_name_and_id '

review: Needs Fixing

[Dan MacNeil (omacneil)]

1384 +$username = 'test';
1385 +$password = 'test';

1658 +$username = 'test';
1659 +$password = 'test';

better:

my ($username, $password)=TestHelper::get_admin_username_password();

# now
sub get_admin_username_password {

return qw/test test/
}

# later
sub get_admin_username_password {

# code to stick temp values in
# /var/www/mvhub/omacneil/conf/auth_apache_users
# and code to remove them in cleanup
}

advantage is we don't have to change $user/$password in more than one place and can (eventually) get a more secure setup.

review: Needs Fixing

[lp:~felmas/mvhub/structure_fix]

632. By Ferhat Elmas <<email address hidden>> on 2011-08-03

new test for structure

[Ferhat Elmas (felmas)]

 In short changes;

 I moved tests that use Test::WWW::Mechanize into app-mvhub/t/mech/admin and modified them according to reviews.

 I added new unit tests into lib-mvhub/t/Structure.

 Now, display_structure_menu mode
  normal get ok
  success get ok

 process_synonym_form
  normal get written but doesn't work due to postgre specific query
  submit TODO

 display_category_chooser
  normal get ok
  success get ok

 process_category_form
  new category get ok
  edit category get ok
  new category submit TODO
  edit category submit TODO

[lp:~felmas/mvhub/structure_fix]

633. By Ferhat Elmas <<email address hidden>> on 2011-08-03

non-numeric category id test for edit category mode in process_category_form.t

634. By Ferhat Elmas <<email address hidden>> on 2011-08-10

edited mech/admin/ tests

635. By Ferhat Elmas <<email address hidden>> on 2011-08-10

edited lib-mvhub/t/Structure tests

636. By Ferhat Elmas <<email address hidden>> on 2011-08-10

merged trunk

637. By Ferhat Elmas <<email address hidden>> on 2011-08-15

added TODOs into new broken tests

638. By Ferhat Elmas <<email address hidden>> on 2011-08-15

merged trunk

639. By Ferhat Elmas <<email address hidden>> on 2011-08-16

refactor of new tests

640. By Ferhat Elmas <<email address hidden>> on 2011-08-16

merged trunk

641. By Ferhat Elmas <<email address hidden>> on 2011-08-17

removed todos, added new tests

642. By Ferhat Elmas <<email address hidden>> on 2011-08-17

merged trunk

643. By Ferhat Elmas <<email address hidden>> on 2011-08-18

made executable expand_synonyms.t

644. By Ferhat Elmas <<email address hidden>> on 2011-08-20

merged trunk

645. By Ferhat Elmas <<email address hidden>> on 2011-09-07

merged trunk

646. By Ferhat Elmas <<email address hidden>> on 2011-09-10

merged trunk

647. By Ferhat Elmas <<email address hidden>> on 2011-09-11

merged trunk

648. By Ferhat Elmas <<email address hidden>> on 2011-09-11

merged lp:~omacneil/mvhub/strucure_fix_dan

=== modified file 'app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/structure.pl'
--- app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/structure.pl	2011-01-01 04:43:01 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/structure.pl	2011-09-11 23:59:29 +0000
@@ -1,72 +1,12 @@
 #!/usr/bin/perl
 
-# TODO:
-# STRUCTURE MENU PAGE
-#    Create. DONE
-#    Beautify & Better Explanations.
-#    Link from Admin Menu Frame. DONE.
-# CATEGORY SELECTING & EDITING
-#    Selecting Functionality. DONE.
-#    Editing Functionality. DONE
-#    Use template for Form. DONE
-#    Test user errors and responses. DONE.
-#    Explain to the user what an alias is. Distinguish from synonym.
-#    Make sure they're not re-entering a category that already exists (case insensitive): DONE
-#    Possibly order the programs so that selected ones are first? HAVE ORDERED BY AGENCY NAME INSTEAD.
-#    Look into Sharing code with scripts/guide/category_loader.pl
-# SYNONYM SELECTING & EDITING:
-#    - Create Synonyms.pm from code out of synonym_loader.pl DONE
-#    - Refactor synonym_loader.pl to use MVHub::Synonyms.pm DONE
-#    - Use Synonyms.pm in this script. DONE
-#    - Create template form. DONE
-#    - Recreate synonym_stem table with submitted word column, and load data into it. DONE.
-#        - on dsiegal db. DONE.
-#        - on production db. DONE.
-#    - Create new function & aggregates on production database. DONE.
-#    - Check http://archives.postgresql.org/pgsql-sql/ for answer to question.
-#    - Possibly put in a check to catch (and drop) any duplicated stem within a stem set
-#    - Any limitations on input?
-#      Might want to drop at least commas, and possibly only keep alphas, hyphens, apostrophes.
-# HEADINGS
-#    Everything. TODO.
-#
-# MISC
-#    Possibly, edit the Admin Home Page with the same links as in the top frame, but also explanations.
-#    Check other TODOs.
-#    Change Common to get only a specific version of FormBuilder: DONE
-#    Add spell checking. Probably want to refactor how we incorporate it (here as well as in rest of application).
-#    Test on IE
-#    Throw out scripts/guide/add_category.pl.
-#    Document IN PROGRESS
-#    Usability Testing
-
 use strict;
 use warnings;
 
 use CGI;
-use CGI::Carp;
-use DBI;
-
-# TODO: trouble installing:
-#use Text::Capitalize;
-
+
+use MVHub::Structure;
 use MVHub::Utils;
-use MVHub::HTMLTemplateUtils;
-
-use MVHub::Common;
-use MVHub::Utils::DB;
-use MVHub::Headings;
-use MVHub::HTML;
-use MVHub::Page;
-use MVHub::Synonyms;
-
-use MVHub::Wrap::ConfigSimple;
-
-my $cfg                      = MVHub::Wrap::ConfigSimple->new();
-my $template_dir             = $cfg->param('ABSOLUTE_PATH.template_html_dir');
-my $EDIT_SYNONYMS_TEMPLATE   = "$template_dir/edit_synonyms.tmpl";
-my $EDIT_CATEGORIES_TEMPLATE = "$template_dir/edit_categories_template";
-my $STRUCTURE_MENU_TEMPLATE  = "$template_dir/structure_menu.tmpl";
 
 {
     my $cgi = CGI->new();
@@ -74,422 +14,18 @@
     my $mode = $cgi->param('mode');
     $cgi->delete('mode');
     if ( !$mode || $mode eq 'structure_menu' ) {
-        display_structure_menu($cgi);
+        MVHub::Structure::display_structure_menu($cgi);
     }
     elsif ( $mode eq 'category_chooser' ) {
-        display_category_chooser($cgi);
+        MVHub::Structure::display_category_chooser($cgi);
     }
     elsif ( $mode eq 'edit_category' ) {
-        process_category_form($cgi);
+        MVHub::Structure::process_category_form($cgi);
     }
     elsif ( $mode eq 'edit_synonyms' ) {
-        process_synonym_form($cgi);
+        MVHub::Structure::process_synonym_form($cgi);
     }
     else {
         MVHub::Utils::assert( 0, "Unknown mode requested of $0: $mode" );
     }
 }
-
-sub process_synonym_form {
-    my $cgi = shift;
-
-    if ( $cgi->param('synonyms') ) {
-
-        # Form was submitted; update the database
-        my $synonyms_string = $cgi->param('synonyms');
-        my @synonyms = split( '\n', $synonyms_string );
-        Synonyms::submit_synonyms_to_db(@synonyms);
-        my $redirect_url
-            = $ENV{SCRIPT_NAME} . '?mode=structure_menu&success=1';
-        print $cgi->redirect($redirect_url);
-        exit(0);
-    }
-    else {
-
-        # Display the form with pre-existing values from database
-        my @synonyms = Synonyms::fetch_synonyms_from_db();
-        my $synonyms_string = join( "\n", @synonyms );
-
-     # FYI: Using Cgi::Formbuilder is overkill for a form with just one field,
-     # so we've just layed out the form explicitly in the template.
-        HTMLTemplateUtils::format_template(
-            filename   => $EDIT_SYNONYMS_TEMPLATE,
-            param_href => {
-                synonyms => $synonyms_string,
-                rows     => ( @synonyms + 2 )
-            },
-            print   => 1,
-            doctype => 1
-        );
-    }
-}
-
-sub display_structure_menu {
-    my $cgi = shift;
-    MVHub::HTMLTemplateUtils::format_template(
-        filename   => $STRUCTURE_MENU_TEMPLATE,
-        param_href => { $cgi->Vars() },
-        print      => 1,
-        doctype    => 1
-    );
-}
-
-sub display_category_chooser {
-    my $cgi           = shift;
-    my $headings      = new Headings();
-    my @heading_names = $headings->heading_names();
-
-    my @output = ('<br>');
-    if ( $cgi->param('success') ) {
-        push( @output,
-            CGI::p('<font color="red">Your changes have been saved.</font>')
-        );
-    }
-    push( @output,
-        '<p>',
-        '<button onclick=',
-        qq|location.href="$ENV{SCRIPT_NAME}?mode=edit_category"|,
-        '> <strong>Create a New Category</strong></button>',
-        '</p>',
-        '<p>',
-        '<strong>or Click a Category to Edit:</strong>',
-        '</p>' );
-
-    my $url = $ENV{SCRIPT_NAME} . '?mode=edit_category&category_id=';
-    foreach (@heading_names) {
-        push( @output, "<h2>$_</h2>" );
-        my $categories_aref = $headings->categories($_);
-
-        # Copied from Guide.pm. Consider refactoring.
-        # Generate an html table of category links
-        my @links;
-        foreach (@$categories_aref) {
-            my $link  = $url . $_->{category_id};
-            my $label = CGI::escapeHTML( $_->{category_name} );
-
-            # add space around forward slashes so a label like
-            # "Translating/Interpreting" will wrap nicely
-            $label =~ s|\s*/\s*| / |g;
-            push( @links, CGI::a( { href => $link }, $label ) );
-        }
-
-        my $table = HTML::make_html_table(
-            columns          => 4,
-            content_aref     => \@links,
-            table_attributes => 'width="100%" cellspacing="5"'
-        );
-        push( @output, $table );
-    }
-    print( $cgi->header('text/html'),
-        $cgi->start_html(
-            -style => { -src => '/guide/styles/admin.css' },
-            -title => 'Directory Structure Administration'
-        ),
-        join( "\n", @output ),
-        $cgi->end_html()
-    );
-}
-
-sub process_category_form {
-    my $cgi         = shift;
-    my $category_id = $cgi->param('category_id');
-    if ( defined $category_id ) {
-        MVHub::Utils::assert( $category_id !~ /\D/,
-            'Category id must be numeric only' );
-    }
-
-    my $preloaded_values_href;
-    my $form = make_category_editing_form($cgi);
-
-    if ( $form->submitted() ) {
-        if ( $form->validate() && validate_more($form) ) {
-            submit_category_form_data_to_db( scalar $form->field() );
-            my $redirect_url
-                = $ENV{SCRIPT_NAME} . '?mode=structure_menu&success=1';
-            print $cgi->redirect($redirect_url);
-            exit(0);
-        }
-        else {
-
-            # TODO: add announcement?
-        }
-    }
-    elsif ($category_id) {
-        $preloaded_values_href
-            = fetch_existing_category_and_dependent_data_from_db(
-            $category_id);
-    }
-    print $form->render( header => 1, values => $preloaded_values_href );
-}
-
-# Returns a CGI::Formbuilder object representing our form. The form is blank (i.e.
-# the fields are defined, but are not pre-populated with any data.)
-# This CGI::Formbuilder object has no associated page header.
-sub make_category_editing_form {
-    my ($cgi) = @_;
-
-    my %category_field_defs = (
-        category_id   => { type     => 'hidden' },
-        category_name => { required => 1 },
-        aliases       => {
-            type    => 'textarea',
-            rows    => 3,
-            comment => 'Optional. One per line.'
-        },
-        headings => {
-            type     => 'checkbox',
-            options  => get_all_headings(),
-            columns  => 3,
-            required => 1,
-            comment  => 'Select one or more.'
-        },
-        programs => {
-            type    => 'checkbox',
-            options => get_all_programs(),
-            columns => 1,
-            comment => "Optional. Alternatively, you can assign categories"
-                . " to programs via their program forms."
-        }
-    );
-
-    my $form = MVHub::Common::prepare_form(
-        form_attributes_href => {
-            name   => 'category_editor_form',
-            params => $cgi,
-
-# TODO: listing the columns here is only while we're not using an html template,
-# so that CGI:FB knows what order to display 'em in:
-# fields => [qw/category_name aliases headings programs/]
-# TODO: remove the above 3 lines.
-            template => $EDIT_CATEGORIES_TEMPLATE
-        },
-        fields_href => \%category_field_defs
-    );
-
-    return $form;
-}
-
-# Returns a reference to an array of headings, e.g.:
-# [ "Arts/Culture/Entertainment", "Financial Education/Credit", ... ]
-sub get_all_headings {
-    my $dbh = MVHub::Utils::DB::get_dbh();
-    my $sql = MVHub::Utils::DB::get_sql_select_statement(
-        'HEADING_X_HEADING_NAME');
-    my $headings_aref = $dbh->selectcol_arrayref($sql);
-    return $headings_aref;
-}
-
-# Returns a reference to an array of program hashrefs, e.g.:
-# [
-#   { 12345 => 'Youth Volleyball (YMCA)' },
-#   { 156745 => 'Building Blocks (North Shore Arc)' },
-#   ...
-# ]
-sub get_all_programs {
-    my $dbh = MVHub::Utils::DB::get_dbh();
-
-    my $sql = MVHub::Utils::DB::get_sql_select_statement(
-        'PROGRAM_X_PROGRAM_AGENCY_ID');
-
-    my $result_aref = $dbh->selectall_arrayref($sql);
-
-    # see end of 'perldoc -f map' on why the '+' in the following:
-    my @headings = map( +{ $_->[0] => $_->[1] }, @$result_aref );
-    return \@headings;
-}
-
-# The return statement tells all.
-sub fetch_existing_category_and_dependent_data_from_db {
-    my $category_id = shift;
-    my $dbh         = MVHub::Utils::DB::get_dbh();
-
-    my $sql = MVHub::Utils::DB::get_sql_select_statement(
-        'CATEGORY_X_CATEGORY_NAME');
-    my @bind_values = ($category_id);
-
-    my @result
-        = @{ $dbh->selectall_arrayref( $sql, { Slice => {} }, @bind_values )
-        };
-    my $category_name = $result[0]->{category_name};
-
-    $sql = MVHub::Utils::DB::get_sql_select_statement('ALIAS_X_ALIAS_NAME');
-    my $aliases_aref
-        = $dbh->selectcol_arrayref( $sql, { Slice => {} }, $category_id );
-    my $aliases = join( "\n", @$aliases_aref );
-
-    $sql = MVHub::Utils::DB::get_sql_select_statement(
-        'HEADING_CATEGORY_X_HEADING_NAME');
-    my $headings_aref
-        = $dbh->selectcol_arrayref( $sql, { Slice => {} }, $category_id );
-    MVHub::Utils::assert( @$headings_aref,
-        "Expected at least one heading for this category (#$category_id)" );
-
-    $sql = MVHub::Utils::DB::get_sql_select_statement(
-        'PROGRAM_CATEGORY_X_PROGRAM_ID');
-    my $programs_aref
-        = $dbh->selectcol_arrayref( $sql, { Slice => {} }, $category_id );
-    return {
-        category_name => $category_name,
-        aliases       => $aliases,
-        headings      => $headings_aref,
-        programs      => $programs_aref
-    };
-}
-
-sub submit_category_form_data_to_db {
-    my $values_href = shift;
-
-# Pre-processing data destined for database here:
-# TODO: get T:C installed or remove this line
-#$values_href->{category_name} = Text::Capitalize::$values_href->{category_name};
-
-    my $dbh = MVHub::Utils::DB::get_dbh();
-    $dbh->begin_work();
-
-# If we have a category id, we must be updating an existing category.
-# In this case, in addition to updating the category record, we need to
-# get rid of the dependent alias, heading_category and program_category records
-# the user wants removed, update any changed ones, and add any new ones.
-# That's a lot of work. The easiest way is to just delete the existing
-# category record, whcih will cascade to delete the dependent records (because
-# of the way the foreign keys are defined on the those tables).
-# Then we insert everything, just as if we were creating a new category. The only
-# 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} ) {
-        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}
-            = MVHub::Utils::DB::get_next_in_sequence( $dbh,
-            'category_id_sequence' );
-    }
-    insert_category_and_dependent_data( dbh => $dbh, %$values_href );
-    $dbh->commit();
-}
-
-sub insert_category_and_dependent_data {
-    my %args               = @_;
-    my $dbh                = delete $args{dbh};
-    my $category_id        = delete $args{category_id};
-    my $category_name      = delete $args{category_name};
-    my $aliases_string     = delete $args{aliases};
-    my $heading_names_aref = delete $args{headings};
-    my $program_ids_aref   = delete $args{programs};
-    MVHub::Utils::assert( ( scalar keys %args ) == 0,
-        "Unknown arguments: @{[%args]}" );
-
-    MVHub::Utils::DB::insert_one_record(
-        dbh         => $dbh,
-        table       => 'category',
-        values_href => {
-            'category_name' => $category_name,
-            'stems_name'    => Common::get_stems($category_name),
-            'category_id'   => $category_id
-        }
-    );
-
-    if ($aliases_string) {
-
-        # TODO: are we guaranteed that the line separator is a \n ?
-        my @aliases = split( '\n', $aliases_string );
-
-        foreach my $alias (@aliases) {
-            $alias = MVHub::Utils::trim($alias);
-            next unless $alias;    # ignore blank lines
-            Common::insert_one_record(
-                dbh         => $dbh,
-                table       => 'alias',
-                values_href => {
-                    'category_id' => $category_id,
-                    'alias_name'  => $alias,
-                    'stems_name'  => Common::get_stems($alias)
-                }
-            );
-        }
-    }
-
-    foreach my $heading_name (@$heading_names_aref) {
-        Common::insert_one_record(
-            dbh         => $dbh,
-            table       => 'heading_category',
-            values_href => {
-                'category_id'  => $category_id,
-                'heading_name' => $heading_name
-            }
-        );
-    }
-
-    foreach my $program_id (@$program_ids_aref) {
-        Common::insert_one_record(
-            dbh         => $dbh,
-            table       => 'program_category',
-            values_href => {
-                'category_id' => $category_id,
-                'program_id'  => $program_id
-            }
-        );
-    }
-}
-
-# This is the place for error checking that can't be done within
-# CGI::FormBuilder->validate(), e.g.:
-#   - complex validations that require access to other variables
-#   - validations that need to produce a custom error message
-# Returns 0 iff any validation fails, supplying an error message
-# for each failed validation in the custom_error-category_name template
-# variable on the form.
-sub validate_more {
-    my $form       = shift;
-    my $return_val = 1;
-
-    if (is_category_name_taken(
-            category_name => $form->field('category_name'),
-            category_id   => $form->field('category_id')
-        )
-        )
-    {
-        my $msg = "The category name you entered is "
-            . "already being used by another category. Please enter a different one:";
-        $form->tmpl_param( 'custom_error-category_name', $msg );
-
-        $return_val = 0;
-    }
-
-    return $return_val;
-}
-
-# Returns true iff the given category name is already in use
-# (without regard to case) in the database by another category. If a
-# a category_id argument is supplied, then any record with that ID
-# will be excluded from the search. Returns false otherwise.
-sub is_category_name_taken {
-    my %args          = @_;
-    my $category_name = delete $args{'category_name'};
-    my $category_id   = delete $args{'category_id'};
-    MVHub::Utils::assert( defined $category_name && $category_name ne "",
-        "Missing category_name argument" );
-    MVHub::Utils::assert( ( scalar keys %args ) == 0,
-        "Unknown arguments: @{[%args]}" );
-    my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
-
-    # 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 @bind_variables = ( lc($category_name) );
-    my $sql            = MVHub::Utils::DB::get_sql_select_statement(
-        'CATEGORY_X_EXISTS_ONE_CATEGORY_NAME');
-
-    if ($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');
-    }
-
-    my @row
-        = $dbh->selectall_arrayref( $sql, { Slice => {} }, @bind_variables );
-
-    return ( $row[0] );
-}

=== modified file 'app-mvhub/conf/create_sqlite.lib'
--- app-mvhub/conf/create_sqlite.lib	2011-08-25 15:05:33 +0000
+++ app-mvhub/conf/create_sqlite.lib	2011-09-11 23:59:29 +0000
@@ -1,4 +1,4 @@
-[001_drop_agency]
+[001_drop_agency_table]
     DROP TABLE IF EXISTS agency;
 
 --  This:
@@ -7,7 +7,7 @@
 -- postgresql CREATE TABLE agency
 -- but for existing tests it doesn't have to 
 
-[002_create_agency]
+[002_create_agency_table]
 CREATE TABLE agency (
     agency_id INTEGER PRIMARY KEY,
     last_updated DATE not null,
@@ -54,15 +54,14 @@
     date_created DATE,
     quick_login_passwd TEXT
 );
-SQL
 
-[003_drop_program]
+[003_drop_program_table]
     DROP TABLE IF EXISTS program;
 
-[004_create_program]
+[004_create_program_table]
 CREATE TABLE program (
     program_id INTEGER PRIMARY KEY,
-    agency_id INTEGER not null REFERENCES agency ON DELETE CASCADE,
+    agency_id INTEGER not null REFERENCES agency(agency_id) ON DELETE CASCADE,
     last_updated DATE not null,
     hidden_reason TEXT,
 
@@ -130,3 +129,67 @@
 
     CONSTRAINT area_check CHECK(((area_data IS not null) AND area_data <> '') or area_type = 'EVERYWHERE')
 );
+
+[005_drop_category_table]
+    DROP TABLE IF EXISTS category;
+
+[006_create_category_table]
+CREATE TABLE category (
+    category_id INTEGER PRIMARY KEY,
+    category_name TEXT not null CHECK(category_name <> '') UNIQUE,
+    stems_name TEXT not null CHECK(stems_name <> '')
+);
+
+[007_drop_program_category_table]
+    DROP TABLE IF EXISTS program_category;
+
+[008_create_program_category_table]
+CREATE TABLE program_category (
+    category_id INTEGER not null REFERENCES category(category_id) ON DELETE CASCADE,
+    program_id INTEGER not null REFERENCES program(program_id) on DELETE CASCADE,
+
+    PRIMARY KEY(category_id, program_id)
+);
+
+[009_drop_alias_table]
+    DROP TABLE IF EXISTS alias;
+
+[010_create_alias_table]
+CREATE TABLE alias (
+    category_id INTEGER not null REFERENCES category(category_id) ON DELETE CASCADE,
+    alias_name TEXT not null CHECK(alias_name <> ''),
+    stems_name TEXT not null CHECK(stems_name <> ''),
+
+    PRIMARY KEY(category_id, alias_name)
+);
+
+[011_drop_synonym_stem_table]
+    DROP TABLE IF EXISTS synonym_stem;
+
+[012_create_synonym_stem_table]
+CREATE TABLE synonym_stem (
+    synonym_id INTEGER not null,
+    word_stem TEXT not null CHECK(word_stem <> ''),
+    submitted_word TEXT not null CHECK(submitted_word <> ''),
+
+    PRIMARY KEY(synonym_id, word_stem)
+);
+
+[013_drop_heading_table]
+    DROP TABLE IF EXISTS heading;
+
+[014_create_heading_table]
+CREATE TABLE heading (
+    heading_name TEXT PRIMARY KEY
+);
+
+[015_drop_heading_category_table]
+    DROP TABLE IF EXISTS heading_category;
+
+[016_create_heading_category_table]
+CREATE TABLE heading_category (
+    category_id INTEGER not null REFERENCES category(category_id) ON DELETE CASCADE,
+    heading_name TEXT not null REFERENCES heading(heading_name) ON UPDATE CASCADE ON DELETE CASCADE,
+    
+    PRIMARY KEY(category_id, heading_name)
+);

=== modified file 'app-mvhub/conf/sql_delete.lib'
--- app-mvhub/conf/sql_delete.lib	2010-11-26 02:34:44 +0000
+++ app-mvhub/conf/sql_delete.lib	2011-09-11 23:59:29 +0000
@@ -44,4 +44,4 @@
 WHERE program_id = ?
 
 [SYNONYM_STEM_X_ALL]
-DELETE FROM ?
+DELETE FROM synonym_stem 

=== modified file 'app-mvhub/conf/sql_insert.lib'
--- app-mvhub/conf/sql_insert.lib	2010-06-29 18:02:33 +0000
+++ app-mvhub/conf/sql_insert.lib	2011-09-11 23:59:29 +0000
@@ -24,16 +24,30 @@
  'contact_first_name','contact_last_name','contact_phone','contact_email_available'
 );
 
+[CATEGORY_X_REQUIRED_FIELDS]
+INSERT INTO category
+(
+ category_id,
+ category_name,
+ stems_name
+)
+VALUES
+(
+ ?,
+ ?,
+ ?
+);
+
 [PROGRAM_CATEGORY_X_REQUIRED_FIELDS]
 INSERT INTO program_category 
 (
-	program_id, 
-	category_id
+ program_id, 
+ category_id
 ) 
 VALUES 
 (
-	?,
-	?
+ ?,
+ ?
 );
 		
 [PROGRAM_X_REQUIRED_FIELDS]
@@ -69,3 +83,53 @@
  'area_type','online_registration','area_type','area_data','resource library','meeting space',
  'contact_first_name','contact_last_name','contact_phone', 'contact_email_available'
 );
+
+[ALIAS_X_REQUIRED_FIELDS]
+INSERT INTO alias
+(
+ category_id,
+ alias_name,
+ stems_name
+)
+VALUES
+(
+ ?,
+ ?,
+ ?
+);
+
+[SYNONYM_STEM_X_REQUIRED_FIELDS]
+INSERT INTO synonym_stem
+(
+ synonym_id,
+ word_stem,
+ submitted_word
+)
+VALUES
+(
+ ?,
+ ?,
+ ?
+);
+
+[HEADING_X_REQUIRED_FIELDS]
+INSERT INTO heading
+(
+ heading_name
+)
+VALUES
+(
+ ?
+);
+
+[HEADING_CATEGORY_X_REQUIRED_FIELDS]
+INSERT INTO heading_category
+(
+ category_id,
+ heading_name
+)
+VALUES
+(
+ ?,
+ ?
+);

=== modified file 'app-mvhub/conf/sql_select.lib'
--- app-mvhub/conf/sql_select.lib	2011-07-12 20:50:24 +0000
+++ app-mvhub/conf/sql_select.lib	2011-09-11 23:59:29 +0000
@@ -629,6 +629,7 @@
 	WHERE lower(category_name) = ?
 	AND category_id <> ?
 )
+
 [CATEGORY_X_IN_CATEGORY_NAME]
 SELECT category_name 
 FROM category
@@ -958,7 +959,7 @@
 ORDER BY categories DESC, subq.program_and_agency_name
  
 [PROGRAM_X_PROGRAM_AGENCY_ID]
-SELECT p.program_id, a.agency_name || ': ' || program_name 
+SELECT p.program_id, a.agency_name || ': ' || p.program_name 
 	AS agency_program_name 
 FROM agency a,program p
 WHERE a.agency_id = p.agency_id 

=== modified file 'app-mvhub/conf/templates/html/admin.tmpl'
--- app-mvhub/conf/templates/html/admin.tmpl	2011-06-29 15:01:03 +0000
+++ app-mvhub/conf/templates/html/admin.tmpl	2011-09-11 23:59:29 +0000
@@ -35,8 +35,9 @@
 			<a target="main_frame" href="/html/admin/home.shtml">Home</a> | 
 			<a target="main_frame" href="/cgi-bin/mvhub/admin/admin_reports.pl">Reports</a> | 
 			<a target="main_frame" href="/cgi-bin/mvhub/agency_form.pl">Add a New Agency</a> | 
-			<a target="main_frame" href="/cgi-bin/mvhub/admin/call_manager.pl">Call Manager</a>
-			</td>
+			<a target="main_frame" href="/cgi-bin/mvhub/admin/call_manager.pl">Call Manager</a> | 
+			<a target="main_frame" href="/cgi-bin/mvhub/admin/structure.pl">Structure</a>
+		        </td>
 			<td align="right"><a href="/cgi-bin/mvhub/logout.pl" target="_top" onclick="alert('You must close all your browser windows to fully logout.')">Log out</a><br/>
 			</td></tr>
 		</table>

=== modified file 'app-mvhub/t/debian_packages_installed.t'
--- app-mvhub/t/debian_packages_installed.t	2011-08-15 08:04:54 +0000
+++ app-mvhub/t/debian_packages_installed.t	2011-09-11 23:59:29 +0000
@@ -47,6 +47,7 @@
     'libtest-mockobject-perl'         => [],
     'libtest-nowarnings-perl'         => [],
     'libtest-warn-perl'               => [],
+    'libtest-trap-perl'               => [],
     'libtext-template-perl'           => [],
     'liburi-perl'                     => [],
     'perltidy'                        => [],

=== added file 'app-mvhub/t/mech/admin/display_structure_menu.t'
--- app-mvhub/t/mech/admin/display_structure_menu.t	1970-01-01 00:00:00 +0000
+++ app-mvhub/t/mech/admin/display_structure_menu.t	2011-09-11 23:59:29 +0000
@@ -0,0 +1,79 @@
+#!/usr/bin/perl 
+
+use strict;
+use warnings;
+
+use Test::Deep;
+use Test::More tests => 20;
+use Test::WWW::Mechanize;
+
+use MVHub::Wrap::ConfigSimple;
+use TestHelper;
+
+my ( $cfg, $site_code, $website, $address, $mech );
+my ( $base, $mode, $success, $param_message );
+my ( $username, $password ) = TestHelper::get_admin_username_password();
+
+$cfg       = MVHub::Wrap::ConfigSimple->new();
+$site_code = $cfg->param('SITE.website_code');
+$website   = TestHelper::get_test_website_name($site_code);
+
+$base    = '/cgi-bin/mvhub/admin/structure.pl';
+$mode    = 'mode=structure_menu';
+$success = 'success=1';
+
+$mech = Test::WWW::Mechanize->new();
+$mech->credentials( $username, $password );
+
+$address       = "http://$website$base";
+$param_message = 'without mode and succes parameters';
+check_page_title_and_links( $mech, $address, $param_message );
+
+$address       = "$base?$mode";
+$param_message = 'with mode parameter';
+check_page_title_and_links( $mech, $address, $param_message );
+
+$address       = "$base?$success";
+$param_message = 'with success parameter';
+check_page_title_and_links( $mech, $address, $param_message );
+
+$address       = "$base?$mode&$success";
+$param_message = 'with mode and success parameters';
+check_page_title_and_links( $mech, $address, $param_message );
+
+sub check_page_title_and_links {
+
+    my $mech            = shift;
+    my $address         = shift;
+    my $param_message   = shift;
+    my $title           = 'Add/Edit Synonyms';
+    my $success_message = 'Your changes have been saved';
+    my ( @real_links, @content_links );
+
+    @content_links = ();
+    @real_links    = (
+        '/all/css/admin.css',
+        '/html/admin/help.shtml#search_config',
+        '/cgi-bin/mvhub/admin/structure.pl?mode=category_chooser',
+        '/cgi-bin/mvhub/admin/structure.pl?mode=edit_synonyms'
+    );
+
+    $mech->get_ok( $address, "page is got successfully $param_message" );
+    $mech->title_is( $title, "title is correct $param_message" );
+    foreach ( $mech->followable_links() ) {
+        push @content_links, $_->url();
+    }
+    if ( $param_message =~ /success/ ) {
+        $mech->text_contains( $success_message,
+            "success message is show successfully $param_message" );
+    }
+    else {
+        $mech->text_lacks( $success_message,
+            "success message is not shown $param_message" );
+    }
+
+    cmp_deeply( \@content_links, \@real_links,
+        "all links found are correct $param_message" );
+    $mech->page_links_ok("all links are checked $param_message");
+
+}

=== added file 'app-mvhub/t/mech/admin/process_synonym_form.t'
--- app-mvhub/t/mech/admin/process_synonym_form.t	1970-01-01 00:00:00 +0000
+++ app-mvhub/t/mech/admin/process_synonym_form.t	2011-09-11 23:59:29 +0000
@@ -0,0 +1,111 @@
+#!/usr/bin/perl 
+
+use strict;
+use warnings;
+
+use Test::Deep;
+use Test::More tests => 13;
+use Test::WWW::Mechanize;
+
+use MVHub::Wrap::ConfigSimple;
+use TestHelper;
+
+my ( $cfg, $site_code, $website, $uri, $title, $mech );
+my ( $username, $password ) = TestHelper::get_admin_username_password();
+my ( $synonyms, $new_synonym, $success_message );
+
+$cfg         = MVHub::Wrap::ConfigSimple->new();
+$site_code   = $cfg->param('SITE.website_code');
+$website     = TestHelper::get_test_website_name($site_code);
+$uri         = '/cgi-bin/mvhub/admin/structure.pl?mode=edit_synonyms';
+$title       = 'Add/Edit Synonyms';
+$new_synonym = 'zoo animal park';
+$synonyms    = <<END;
+ADD ADHD HADD
+alcohol drinking drunk
+assist help
+cessation quit
+child children kid youth
+class lesson
+computer technology internet
+counseling therapy
+detoxification detox
+disabled handicapped
+drug substance
+elder elderly senior
+fitness exercise
+health medical healthcare
+infant baby
+job career vocation employment
+literacy reading writing language
+marital marriage
+methadone heroine
+money finance
+nurse rna
+nutrition nutritionist
+pregnant pregnancy
+register registration
+teen adolescent
+television tv
+tenant rent renter rental
+tobacco smoking
+utility electricity energy fuel heat gas oil
+vote voter elect
+END
+$success_message = 'Your changes have been saved';
+
+$mech = Test::WWW::Mechanize->new();
+$mech->credentials( $username, $password );
+
+#first get the synonyms in the database and test their existence and links
+$mech->get_ok( "http://$website$uri",
+    'page is got successfully before any processing' );
+$mech->title_is( $title, 'title is correct before any processing' );
+check_links($mech);
+$mech->text_contains( $synonyms,
+    'all synonyms are shown correctly before any processing' );
+$mech->text_lacks( $new_synonym,
+    'new synonym is not shown before any processing' );
+
+#second submit new synonym and then check whether it can be shown
+$mech->form_number(1);    #form number starts from 1
+$mech->field( 'synonyms', "$synonyms\n$new_synonym" );
+$mech->submit();
+$mech->text_contains( $success_message,
+    'new synonym is added, redirect is successful' );
+
+#third get the synonyms again to see new synonym and delete it
+$mech->get_ok( "http://$website$uri",
+    'page is got successfully after addition of new synonym' );
+$mech->text_contains( "$synonyms$new_synonym",
+    'new synonym is shown successfully after addition' );
+$mech->form_number(1);
+$mech->field( 'synonyms', $synonyms );
+$mech->submit();
+$mech->text_contains( $success_message,
+    'new synonym is deleted, redirect is successful' );
+
+#finally get the synonyms to see whether new synonyms is deleted
+$mech->get_ok( "http://$website$uri",
+    'page is got successfully after deletion of new synonym' );
+$mech->text_contains( $synonyms,
+    'all synonyms are shown correctly after deletion' );
+$mech->text_lacks( $new_synonym, 'new synonyms is deleted and not shown' );
+
+sub check_links {
+
+    my $mech = shift;
+    my ( @real_links, @content_links );
+
+    @content_links = ();
+    @real_links
+        = ( '/all/css/admin.css', '/html/admin/help.shtml#search_config', );
+    foreach ( $mech->followable_links() ) {
+        push @content_links, $_->url();
+    }
+
+    cmp_deeply( \@content_links, \@real_links,
+        'all links found are correct' );
+    $mech->page_links_ok('all links are checked');
+
+}

=== modified file 'lib-mvhub/lib/MVHub/Headings.pm'
--- lib-mvhub/lib/MVHub/Headings.pm	2010-09-17 16:42:35 +0000
+++ lib-mvhub/lib/MVHub/Headings.pm	2011-09-11 23:59:29 +0000
@@ -8,8 +8,6 @@
 
 use MVHub::Common;
 
-our ($VERSION) = '$Revision: 1215 $' =~ /([.\d]+)/;
-
 =head2 new()
 
 Initializes a Heading object from the database.
@@ -17,13 +15,9 @@
 =cut 
 
 sub new {
-    my $invocant = shift;
-    my $class = ref($invocant) || $invocant;
-
-    my $dbh = MVHub::Utils::DB::get_dbh();
-
+
+    my $class = shift;
     my $self = { HEADING_CATEGORIES_HREF => _fetch_heading_categories() };
-
     bless( $self, $class );
     return $self;
 }
@@ -76,7 +70,7 @@
 # }
 sub _fetch_heading_categories {
 
-    my $dbh = MVHub::Utils::DB::get_dbh();
+    my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
     my $sql = MVHub::Utils::DB::get_sql_select_statement(
         "CATEGORY_HEADING_CATEGORY_X_FETCH_HEADING_CATEGORY");
 
@@ -89,3 +83,6 @@
     }
     return \%heading_categories;
 }
+
+# all good modules return 1
+1;

=== added file 'lib-mvhub/lib/MVHub/Structure.pm'
--- lib-mvhub/lib/MVHub/Structure.pm	1970-01-01 00:00:00 +0000
+++ lib-mvhub/lib/MVHub/Structure.pm	2011-09-11 23:59:29 +0000
@@ -0,0 +1,471 @@
+package MVHub::Structure;
+
+# TODO:
+# STRUCTURE MENU PAGE
+#    Beautify & Better Explanations.
+# CATEGORY SELECTING & EDITING
+#    Possibly order the programs so that selected ones are first? HAVE ORDERED BY AGENCY NAME INSTEAD.
+#    Look into Sharing code with scripts/guide/category_loader.pl
+# SYNONYM SELECTING & EDITING:
+#    - Check http://archives.postgresql.org/pgsql-sql/ for answer to question.
+#    - Possibly put in a check to catch (and drop) any duplicated stem within a stem set
+#    - Any limitations on input?
+#      Might want to drop at least commas, and possibly only keep alphas, hyphens, apostrophes.
+# HEADINGS
+#    Everything. TODO.
+#
+# MISC
+#    Possibly, edit the Admin Home Page with the same links as in the top frame, but also explanations.
+#    Check other TODOs.
+#    Add spell checking. Probably want to refactor how we incorporate it (here as well as in rest of application).
+#    Test on IE
+#    Throw out scripts/guide/add_category.pl.
+#    Document IN PROGRESS
+#    Usability Testing
+
+use strict;
+use warnings;
+
+use CGI;
+use CGI::Carp;
+use DBI;
+
+use MVHub::Common;
+use MVHub::Headings;
+use MVHub::HTML;
+use MVHub::HTMLTemplateUtils;
+use MVHub::Page;
+use MVHub::Synonyms;
+use MVHub::Utils;
+use MVHub::Utils::DB;
+use MVHub::Wrap::ConfigSimple;
+
+use base 'Exporter';
+our @EXPORT_OK = qw(
+    display_structure_menu
+    display_category_chooser
+    process_category_form
+    process_synonym_form
+    make_categort_editing_form
+    get_all_headings
+    get_all_programs
+    fetch_existing_category_and_dependent_data_from_db
+    submit_category_form_data_to_db
+    insert_category_and_dependent_data
+    validate_more
+    is_category_name_taken
+);
+
+my $cfg                      = MVHub::Wrap::ConfigSimple->new();
+my $template_dir             = $cfg->param('ABSOLUTE_PATH.template_html_dir');
+my $EDIT_SYNONYMS_TEMPLATE   = "$template_dir/edit_synonyms.tmpl";
+my $EDIT_CATEGORIES_TEMPLATE = "$template_dir/edit_categories.tmpl";
+my $STRUCTURE_MENU_TEMPLATE  = "$template_dir/structure_menu.tmpl";
+
+sub process_synonym_form {
+    my $cgi = shift;
+
+    if ( $cgi->param('synonyms') ) {
+
+        # Form was submitted; update the database
+        my $synonyms_string = $cgi->param('synonyms');
+        my @synonyms = split( '\n', $synonyms_string );
+        MVHub::Synonyms::submit_synonyms_to_db(@synonyms);
+        my $redirect_url
+            = $ENV{SCRIPT_NAME} . '?mode=structure_menu&success=1';
+        print $cgi->redirect($redirect_url);
+        exit(0);
+    }
+    else {
+
+        # Display the form with pre-existing values from database
+        my @synonyms = MVHub::Synonyms::fetch_synonyms_from_db();
+        my $synonyms_string = join( "\n", @synonyms );
+
+     # FYI: Using Cgi::Formbuilder is overkill for a form with just one field,
+     # so we've just layed out the form explicitly in the template.
+        MVHub::HTMLTemplateUtils::format_template(
+            filename   => $EDIT_SYNONYMS_TEMPLATE,
+            param_href => {
+                synonyms => $synonyms_string,
+                rows     => ( @synonyms + 2 )
+            },
+            print   => 1,
+            doctype => 1
+        );
+    }
+}
+
+sub display_structure_menu {
+    my $cgi = shift;
+    MVHub::HTMLTemplateUtils::format_template(
+        filename   => $STRUCTURE_MENU_TEMPLATE,
+        param_href => { $cgi->Vars() },
+        print      => 1,
+        doctype    => 1
+    );
+}
+
+sub display_category_chooser {
+    my $cgi           = shift;
+    my $headings      = MVHub::Headings->new();
+    my @heading_names = $headings->heading_names();
+
+    my @output = ('<br>');
+    if ( $cgi->param('success') ) {
+        push( @output,
+            CGI::p('<font color="red">Your changes have been saved.</font>')
+        );
+    }
+    push( @output,
+        '<p>',
+        '<button onclick=',
+        qq|location.href="$ENV{SCRIPT_NAME}?mode=edit_category"|,
+        '> <strong>Create a New Category</strong></button>',
+        '</p>',
+        '<p>',
+        '<strong>or Click a Category to Edit:</strong>',
+        '</p>' );
+
+    my $url = $ENV{SCRIPT_NAME} . '?mode=edit_category&category_id=';
+    foreach (@heading_names) {
+        push( @output, "<h2>$_</h2>" );
+        my $categories_aref = $headings->categories($_);
+
+        # Copied from Guide.pm. Consider refactoring.
+        # Generate an html table of category links
+        my @links;
+        foreach (@$categories_aref) {
+            my $link  = $url . $_->{category_id};
+            my $label = CGI::escapeHTML( $_->{category_name} );
+
+            # add space around forward slashes so a label like
+            # "Translating/Interpreting" will wrap nicely
+            $label =~ s|\s*/\s*| / |g;
+            push( @links, CGI::a( { href => $link }, $label ) );
+        }
+
+        my $table = MVHub::HTML::make_html_table(
+            columns          => 4,
+            content_aref     => \@links,
+            table_attributes => 'width="100%" cellspacing="5"'
+        );
+        push( @output, $table );
+    }
+
+    print( $cgi->header('text/html'),
+        $cgi->start_html(
+            -style => { -src => '/all/css/admin.css' },
+            -title => 'Directory Structure Administration'
+        ),
+        join( "\n", @output ),
+        $cgi->end_html()
+    );
+}
+
+sub process_category_form {
+    my $cgi         = shift;
+    my $category_id = $cgi->param('category_id');
+
+    if ( defined $category_id ) {
+        MVHub::Utils::assert( $category_id !~ /\D/,
+            'Category id must be numeric only' );
+    }
+
+    my $preloaded_values_href;
+    my $form = make_category_editing_form($cgi);
+
+    if ( $form->submitted() ) {
+        if ( $form->validate() && validate_more($form) ) {
+            submit_category_form_data_to_db( scalar $form->field() );
+            my $redirect_url
+                = $ENV{SCRIPT_NAME} . '?mode=structure_menu&success=1';
+            print $cgi->redirect($redirect_url);
+            exit(0);
+        }
+        else {
+
+            # TODO: add announcement?
+        }
+    }
+    elsif ($category_id) {
+        $preloaded_values_href
+            = fetch_existing_category_and_dependent_data_from_db(
+            $category_id);
+    }
+    print $form->render( header => 1, values => $preloaded_values_href );
+}
+
+# Returns a CGI::Formbuilder object representing our form. The form is blank (i.e.
+# the fields are defined, but are not pre-populated with any data.)
+# This CGI::Formbuilder object has no associated page header.
+sub make_category_editing_form {
+    my ($cgi) = @_;
+
+    my %category_field_defs = (
+        category_id   => { type     => 'hidden' },
+        category_name => { required => 1 },
+        aliases       => {
+            type    => 'textarea',
+            rows    => 3,
+            comment => 'Optional. One per line.'
+        },
+        headings => {
+            type     => 'checkbox',
+            options  => get_all_headings(),
+            columns  => 3,
+            required => 1,
+            comment  => 'Select one or more.'
+        },
+        programs => {
+            type    => 'checkbox',
+            options => get_all_programs(),
+            columns => 1,
+            comment => "Optional. Alternatively, you can assign categories"
+                . " to programs via their program forms."
+        }
+    );
+
+    my $form = MVHub::Common::prepare_form(
+        form_attributes_href => {
+            name   => 'category_editor_form',
+            params => $cgi,
+
+# TODO: listing the columns here is only while we're not using an html template,
+# so that CGI:FB knows what order to display 'em in:
+# fields => [qw/category_name aliases headings programs/]
+# TODO: remove the above 3 lines.
+            template => $EDIT_CATEGORIES_TEMPLATE
+        },
+        fields_href => \%category_field_defs
+    );
+
+    return $form;
+}
+
+# Returns a reference to an array of headings, e.g.:
+# [ "Arts/Culture/Entertainment", "Financial Education/Credit", ... ]
+sub get_all_headings {
+    my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
+    my $sql = MVHub::Utils::DB::get_sql_select_statement(
+        'HEADING_X_HEADING_NAME');
+    my $headings_aref = $dbh->selectcol_arrayref($sql);
+    return $headings_aref;
+}
+
+# Returns a reference to an array of program hashrefs, e.g.:
+# [
+#   { 12345 => 'Youth Volleyball (YMCA)' },
+#   { 156745 => 'Building Blocks (North Shore Arc)' },
+#   ...
+# ]
+sub get_all_programs {
+    my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
+
+    my $sql = MVHub::Utils::DB::get_sql_select_statement(
+        'PROGRAM_X_PROGRAM_AGENCY_ID');
+
+    my $result_aref = $dbh->selectall_arrayref($sql);
+
+    # see end of 'perldoc -f map' on why the '+' in the following:
+    my @headings = map( +{ $_->[0] => $_->[1] }, @$result_aref );
+    return \@headings;
+}
+
+# The return statement tells all.
+sub fetch_existing_category_and_dependent_data_from_db {
+    my $category_id = shift;
+    my $dbh         = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
+
+    my $sql = MVHub::Utils::DB::get_sql_select_statement(
+        'CATEGORY_X_CATEGORY_NAME');
+    my @bind_values = ($category_id);
+
+    my @result
+        = @{ $dbh->selectall_arrayref( $sql, { Slice => {} }, @bind_values )
+        };
+    my $category_name = $result[0]->{category_name};
+
+    $sql = MVHub::Utils::DB::get_sql_select_statement('ALIAS_X_ALIAS_NAME');
+    my $aliases_aref
+        = $dbh->selectcol_arrayref( $sql, { Slice => {} }, $category_id );
+    my $aliases = join( "\n", @$aliases_aref );
+
+    $sql = MVHub::Utils::DB::get_sql_select_statement(
+        'HEADING_CATEGORY_X_HEADING_NAME');
+    my $headings_aref
+        = $dbh->selectcol_arrayref( $sql, { Slice => {} }, $category_id );
+    MVHub::Utils::assert( @$headings_aref,
+        "Expected at least one heading for this category (#$category_id)" );
+
+    $sql = MVHub::Utils::DB::get_sql_select_statement(
+        'PROGRAM_CATEGORY_X_PROGRAM_ID');
+    my $programs_aref
+        = $dbh->selectcol_arrayref( $sql, { Slice => {} }, $category_id );
+    return {
+        category_name => $category_name,
+        aliases       => $aliases,
+        headings      => $headings_aref,
+        programs      => $programs_aref
+    };
+}
+
+sub submit_category_form_data_to_db {
+    my $values_href = shift;
+
+# Pre-processing data destined for database here:
+# TODO: get T:C installed or remove this line
+#$values_href->{category_name} = Text::Capitalize::$values_href->{category_name};
+
+    my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
+    $dbh->begin_work();
+
+# If we have a category id, we must be updating an existing category.
+# In this case, in addition to updating the category record, we need to
+# get rid of the dependent alias, heading_category and program_category records
+# the user wants removed, update any changed ones, and add any new ones.
+# That's a lot of work. The easiest way is to just delete the existing
+# category record, whcih will cascade to delete the dependent records (because
+# of the way the foreign keys are defined on the those tables).
+# Then we insert everything, just as if we were creating a new category. The only
+# 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} ) {
+        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}
+            = MVHub::Utils::DB::get_next_in_sequence( $dbh,
+            'category_id_sequence' );
+    }
+    insert_category_and_dependent_data( dbh => $dbh, %$values_href );
+    $dbh->commit();
+}
+
+sub insert_category_and_dependent_data {
+    my %args               = @_;
+    my $dbh                = delete $args{dbh};
+    my $category_id        = delete $args{category_id};
+    my $category_name      = delete $args{category_name};
+    my $aliases_string     = delete $args{aliases};
+    my $heading_names_aref = delete $args{headings};
+    my $program_ids_aref   = delete $args{programs};
+    MVHub::Utils::assert( ( scalar keys %args ) == 0,
+        "Unknown arguments: @{[%args]}" );
+
+    MVHub::Utils::DB::insert_one_record(
+        dbh         => $dbh,
+        table       => 'category',
+        values_href => {
+            'category_name' => $category_name,
+            'stems_name'    => MVHub::Common::get_stems($category_name),
+            'category_id'   => $category_id
+        }
+    );
+
+    if ($aliases_string) {
+
+        # TODO: are we guaranteed that the line separator is a \n ?
+        my @aliases = split( '\n', $aliases_string );
+
+        foreach my $alias (@aliases) {
+            $alias = MVHub::Utils::trim($alias);
+            next unless $alias;    # ignore blank lines
+            MVHub::Utils::DB::insert_one_record(
+                dbh         => $dbh,
+                table       => 'alias',
+                values_href => {
+                    'category_id' => $category_id,
+                    'alias_name'  => $alias,
+                    'stems_name'  => MVHub::Common::get_stems($alias)
+                }
+            );
+        }
+    }
+
+    foreach my $heading_name (@$heading_names_aref) {
+        MVHub::Utils::DB::insert_one_record(
+            dbh         => $dbh,
+            table       => 'heading_category',
+            values_href => {
+                'category_id'  => $category_id,
+                'heading_name' => $heading_name
+            }
+        );
+    }
+
+    foreach my $program_id (@$program_ids_aref) {
+        MVHub::Utils::DB::insert_one_record(
+            dbh         => $dbh,
+            table       => 'program_category',
+            values_href => {
+                'category_id' => $category_id,
+                'program_id'  => $program_id
+            }
+        );
+    }
+}
+
+# This is the place for error checking that can't be done within
+# CGI::FormBuilder->validate(), e.g.:
+#   - complex validations that require access to other variables
+#   - validations that need to produce a custom error message
+# Returns 0 iff any validation fails, supplying an error message
+# for each failed validation in the custom_error-category_name template
+# variable on the form.
+sub validate_more {
+    my $form       = shift;
+    my $return_val = 1;
+
+    if (is_category_name_taken(
+            category_name => $form->field('category_name'),
+            category_id   => $form->field('category_id')
+        )
+        )
+    {
+        my $msg = "The category name you entered is "
+            . "already being used by another category. Please enter a different one:";
+        $form->tmpl_param( 'custom_error-category_name', $msg );
+
+        $return_val = 0;
+    }
+
+    return $return_val;
+}
+
+# Returns true iff the given category name is already in use
+# (without regard to case) in the database by another category. If a
+# a category_id argument is supplied, then any record with that ID
+# will be excluded from the search. Returns false otherwise.
+sub is_category_name_taken {
+    my %args          = @_;
+    my $category_name = delete $args{'category_name'};
+    my $category_id   = delete $args{'category_id'};
+    MVHub::Utils::assert( defined $category_name && $category_name ne "",
+        "Missing category_name argument" );
+    MVHub::Utils::assert( ( scalar keys %args ) == 0,
+        "Unknown arguments: @{[%args]}" );
+    my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
+
+    # 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 @bind_variables = ( lc($category_name) );
+    my $sql            = MVHub::Utils::DB::get_sql_select_statement(
+        'CATEGORY_X_EXISTS_ONE_CATEGORY_NAME');
+
+    if ($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');
+    }
+
+    my @row = $dbh->selectrow_array( $sql, { Slice => {} }, @bind_variables );
+
+    return ( $row[0] );
+}
+
+# all good modules return 1;
+1;

=== modified file 'lib-mvhub/lib/MVHub/Synonyms.pm'
--- lib-mvhub/lib/MVHub/Synonyms.pm	2010-09-13 00:58:44 +0000
+++ lib-mvhub/lib/MVHub/Synonyms.pm	2011-09-11 23:59:29 +0000
@@ -3,22 +3,10 @@
 use strict;
 use warnings;
 
-# ---------------------------------------------------------------
-# Loads synonyms from the $SYNONYM_FILE (defined below) into the
-# database.
-#
-# This script must be run from the installed site (i.e. not from
-# your cvs projects path).
-#
-# Each line in the synonym file is a space-delimited list of one-word
-# synonyms
-#
-# Since the $SYNONYM_TABLE (below) has no dependents (i.e.
-# other tables that reference its data), this script may be re-run on
-# on an existing site.
-# The $SYNONYM_SEQUENCE (below) is reset to its initial value.
-#
-# ---------------------------------------------------------------
+use base 'Exporter';
+our @EXPORT_OK = qw(
+    fetch_synonyms_from_db
+);
 
 use MVHub::Common;
 
@@ -34,7 +22,7 @@
 # Returns a list of lines, where each line is a space delimited list of
 # one word synonyms of each other.
 sub fetch_synonyms_from_db {
-    my $dbh = MVHub::Utils::DB::get_dbh();
+    my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
 
     my $sql = MVHub::Utils::DB::get_sql_select_statement(
         'SYNONYM_STEM_X_SUBMITTED_WORDS');
@@ -50,17 +38,19 @@
 # one word synonyms of each other.
 # Submits them into the database table, $SYNONYM_TABLE.
 # Dies on any database error.
+
+# **** DESTROYS ***** existing table
+
 sub submit_synonyms_to_db {
     my @synonym_lines = @_;
-    my $dbh           = MVHub::Utils::DB::get_dbh();
+    my $dbh           = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
     $dbh->{AutoCommit} = 0;
 
-    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( $sql, undef, () );
 
-    @bind_variables = ( $SYNONYM_SEQUENCE, $SYNONYM_SEQUENCE_INIT_VAL );
+    my @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 );
 

=== modified file 'lib-mvhub/t/00-setup.t'
--- lib-mvhub/t/00-setup.t	2010-10-08 19:44:05 +0000
+++ lib-mvhub/t/00-setup.t	2011-09-11 23:59:29 +0000
@@ -9,7 +9,14 @@
 
 use MVHub::Utils::DB qw/ get_dbh /;
 
-use TestData qw/@agency @program/;
+use TestData qw/@agency
+    @program
+    @category
+    @program_category
+    @alias
+    @synonym_stem
+    @heading
+    @heading_category/;
 use TestHelper qw/ add_test_data create_sqlite_db /;
 
 eval ' use Test::NoWarnings; ';

=== modified file 'lib-mvhub/t/GuideSearch/expand_synonyms.t' (properties changed: -x to +x)
=== added directory 'lib-mvhub/t/Headings'
=== added file 'lib-mvhub/t/Headings/categories.t'
--- lib-mvhub/t/Headings/categories.t	1970-01-01 00:00:00 +0000
+++ lib-mvhub/t/Headings/categories.t	2011-09-11 23:59:29 +0000
@@ -0,0 +1,31 @@
+#!/usr/bin/perl 
+
+use strict;
+use warnings;
+
+use Test::Deep;
+use Test::More tests => 3;
+
+use MVHub::Headings;
+
+use_ok('MVHub::Headings');
+
+$ENV{MV_CONFIG_FILE} = $ENV{MV_TEST_CONFIG_FILE};
+
+my $headings = MVHub::Headings->new();
+isa_ok( $headings, 'MVHub::Headings' );
+
+my $heading_name    = 'Arts/Culture/Entertainment';
+my $real_categories = [
+    { category_id => 1006, category_name => 'Arts, Youth' },
+    { category_id => 1003, category_name => 'Arts/Entertainment' },
+    { category_id => 1001, category_name => 'Cultural Heritage' },
+    { category_id => 1007, category_name => 'Culture' },
+    { category_id => 1005, category_name => 'Museum' },
+    { category_id => 1002, category_name => 'Music' },
+    { category_id => 1004, category_name => 'Theater/Performance Art' }
+];
+
+my $categories = $headings->categories($heading_name);
+
+cmp_deeply( $categories, $real_categories, 'category arrays are same' );

=== added file 'lib-mvhub/t/Headings/heading_names.t'
--- lib-mvhub/t/Headings/heading_names.t	1970-01-01 00:00:00 +0000
+++ lib-mvhub/t/Headings/heading_names.t	2011-09-11 23:59:29 +0000
@@ -0,0 +1,22 @@
+#!/usr/bin/perl 
+
+use strict;
+use warnings;
+
+use Test::Deep;
+use Test::More tests => 3;
+
+use MVHub::Headings;
+
+use_ok('MVHub::Headings');
+
+$ENV{MV_CONFIG_FILE} = $ENV{MV_TEST_CONFIG_FILE};
+
+my $headings = MVHub::Headings->new();
+isa_ok( $headings, 'MVHub::Headings' );
+
+my $real_heading_names
+    = [ 'Arts/Culture/Entertainment', 'City/Town Services' ];
+my @heading_names = $headings->heading_names();
+
+is_deeply( \@heading_names, $real_heading_names, 'heading arrays are same' );

=== modified file 'lib-mvhub/t/Notifications/set_reminder_level_in_db.t'
--- lib-mvhub/t/Notifications/set_reminder_level_in_db.t	2010-10-16 00:29:43 +0000
+++ lib-mvhub/t/Notifications/set_reminder_level_in_db.t	2011-09-11 23:59:29 +0000
@@ -64,7 +64,7 @@
     'PROGRAM_X_REMINDERS_SENT' );
 is_deeply(
     $dbh->selectcol_arrayref("SELECT reminders_sent FROM program"),
-    [ 1, 2, 3, 4 ],
+    [ 1, 2, 3, 4, 4 ],
     $test_message
 );
 

=== added directory 'lib-mvhub/t/Structure'
=== added file 'lib-mvhub/t/Structure/display_category_chooser.t'
--- lib-mvhub/t/Structure/display_category_chooser.t	1970-01-01 00:00:00 +0000
+++ lib-mvhub/t/Structure/display_category_chooser.t	2011-09-11 23:59:29 +0000
@@ -0,0 +1,70 @@
+#!/usr/bin/perl 
+
+use strict;
+use warnings;
+
+use CGI;
+use Test::More tests => 4;
+
+my ( @subs, $out, $result, $cgi );
+
+@subs = qw ( display_category_chooser );
+
+use_ok( 'MVHub::Structure', @subs );
+can_ok( 'MVHub::Structure', 'display_category_chooser' );
+
+close(STDOUT);
+open( STDOUT, ">", \$out ) or die "Can't open STDOUT: $!";
+
+$ENV{MV_CONFIG_FILE} = $ENV{MV_TEST_CONFIG_FILE};
+$ENV{SCRIPT_NAME}    = 'structure.pl';
+
+$cgi = CGI->new();
+MVHub::Structure::display_category_chooser($cgi);
+
+#make output one line
+$out =~ s/\n//g;
+
+$result = check_categories_and_headings($out);
+ok( $result,
+    'found expected strings in category chooser without success parameter' );
+$out = '';
+
+$cgi->param( 'success', '1' );
+MVHub::Structure::display_category_chooser($cgi);
+
+#make output one line
+$out =~ s/\n//g;
+
+$result = check_categories_and_headings($out);
+$result = $result && $out =~ /Your changes have been saved\./;
+ok( $result,
+    'found expected strings in category chooser with success parameter' );
+
+sub check_categories_and_headings {
+
+    my $out = shift;
+
+    #categories
+    my $result = $out =~ /category_id=1001\">Cultural Heritage/;
+    $result = $result && $out =~ /category_id=1002\">Music/;
+    $result = $result && $out =~ /category_id=1003\">Arts \/ Entertainment/;
+    $result
+        = $result && $out =~ /category_id=1004\">Theater \/ Performance Art/;
+    $result = $result && $out =~ /category_id=1005\">Museum/;
+    $result = $result && $out =~ /category_id=1006\">Arts, Youth/;
+    $result = $result && $out =~ /category_id=1007\">Culture/;
+    $result = $result && $out =~ /category_id=1008\">Hotlines, City Services/;
+    $result = $result && $out =~ /category_id=1009\">Waste Management/;
+    $result = $result && $out =~ /category_id=1010\">Code Enforcement/;
+    $result = $result && $out !~ /Dance/;    #Nonexist category
+
+    #headings
+    $result = $result && $out =~ /<h2>Arts\/Culture\/Entertainment<\/h2>/;
+    $result = $result && $out =~ /<h2>City\/Town Services<\/h2>/;
+    $result
+        = $result && $out !~ /<h2>Community\/Civi<\/h2>/;    #Nonexist heading
+
+    return $result;
+
+}

=== added file 'lib-mvhub/t/Structure/display_structure_menu.t'
--- lib-mvhub/t/Structure/display_structure_menu.t	1970-01-01 00:00:00 +0000
+++ lib-mvhub/t/Structure/display_structure_menu.t	2011-09-11 23:59:29 +0000
@@ -0,0 +1,60 @@
+#!/usr/bin/perl 
+
+use strict;
+use warnings;
+
+use CGI;
+use Test::More tests => 4;
+
+my ( @subs, $out, $cgi, $result );
+
+@subs = qw ( display_structure_menu );
+
+use_ok( 'MVHub::Structure', @subs );
+can_ok( 'MVHub::Structure', 'display_structure_menu' );
+
+close(STDOUT);
+open( STDOUT, ">", \$out ) or die "Can't open STDOUT: $!";
+
+$cgi = CGI->new();
+MVHub::Structure::display_structure_menu($cgi);
+
+#make output one line
+$out =~ s/\n//g;
+
+$result = check_links_and_titles($out);
+ok( $result, 'found expected titles and links without success parameter' );
+$out = '';
+
+$cgi->param( 'success', '1' );
+MVHub::Structure::display_structure_menu($cgi);
+
+#make output one line
+$out =~ s/\n//g;
+
+$result = check_links_and_titles($out);
+$result = $result and $out =~ /Your changes have been saved\./;
+ok( $result, 'found expected titles and links with success parameter' );
+
+sub check_links_and_titles {
+
+    my $out = shift;
+
+    my $result = $out =~ /start structure_menu\.tmpl/;
+    $result = $result and $out =~ /Add\/Edit Synonyms/;
+    $result = $result and $out =~ /\/all\/css\/admin\.css/;
+    $result = $result and $out =~ /Configure the Directory\'s Structure/;
+    $result = $result and $out =~ /\/html\/admin\/help\/.shtml#search_config/;
+    $result = $result
+        and $out =~ /How to Configure the Program Category Search Engine/;
+    $result = $result
+        and $out
+        =~ /\/cgi-bin\/mvhub\/admin\/structure\.pl\?mode=category_chooser/;
+    $result = $result and $out =~ /Categories/;
+    $result = $result
+        and $out
+        =~ /\/cgi-bin\/mvhub\/admin\/structure\.pl\?mode=edit_synonyms/;
+    $result = $result and $out =~ /Headings/;
+
+    return $result;
+}

=== added file 'lib-mvhub/t/Structure/fetch_existing_category_and_dependent_data_from_db.t'
--- lib-mvhub/t/Structure/fetch_existing_category_and_dependent_data_from_db.t	1970-01-01 00:00:00 +0000
+++ lib-mvhub/t/Structure/fetch_existing_category_and_dependent_data_from_db.t	2011-09-11 23:59:29 +0000
@@ -0,0 +1,95 @@
+#!/usr/bin/perl 
+
+use strict;
+use warnings;
+
+use Test::Deep;
+use Test::More tests => 6;
+
+my @subs = qw( fetch_existing_category_and_dependent_data_from_db );
+
+use_ok( 'MVHub::Structure', @subs );
+can_ok( 'MVHub::Structure',
+    'fetch_existing_category_and_dependent_data_from_db' );
+
+$ENV{MV_CONFIG_FILE} = $ENV{MV_TEST_CONFIG_FILE};
+
+my $category_id = 1001;
+my $data
+    = MVHub::Structure::fetch_existing_category_and_dependent_data_from_db(
+    $category_id);
+
+my $category_name = 'Cultural Heritage';
+my $aliases       = join( "\n", (qw(History)) );
+my $headings      = [qw(Arts/Culture/Entertainment)];
+my $programs      = [qw(1001 1002 1003 1004 1005)];
+
+my $real_data = {
+    category_name => $category_name,
+    aliases       => $aliases,
+    headings      => $headings,
+    programs      => $programs
+};
+
+cmp_deeply( $data, $real_data,
+    'category and dependent data(with aliases and programs) hashes are same'
+);
+
+$category_id = 1010;
+$data = MVHub::Structure::fetch_existing_category_and_dependent_data_from_db(
+    $category_id);
+
+$category_name = 'Code Enforcement';
+$aliases       = join( "\n", ('Code Violations') );
+$headings      = ['City/Town Services'];
+$programs      = [];
+
+$real_data = {
+    category_name => $category_name,
+    aliases       => $aliases,
+    headings      => $headings,
+    programs      => $programs
+};
+
+cmp_deeply( $data, $real_data,
+    'category and dependent data(with only aliases) hashes are same' );
+
+$category_id = 1004;
+$data = MVHub::Structure::fetch_existing_category_and_dependent_data_from_db(
+    $category_id);
+
+$category_name = 'Theater/Performance Art';
+$aliases       = join( "\n", () );
+$headings      = ['Arts/Culture/Entertainment'];
+$programs      = [qw(1001 1002)];
+
+$real_data = {
+    category_name => $category_name,
+    aliases       => $aliases,
+    headings      => $headings,
+    programs      => $programs
+};
+
+cmp_deeply( $data, $real_data,
+    'category and dependent data(with only programs) hashes are same' );
+
+$category_id = 1008;
+$data = MVHub::Structure::fetch_existing_category_and_dependent_data_from_db(
+    $category_id);
+
+$category_name = 'Hotlines, City Services';
+$aliases       = join( "\n", () );
+$headings      = ['City/Town Services'];
+$programs      = [];
+
+$real_data = {
+    category_name => $category_name,
+    aliases       => $aliases,
+    headings      => $headings,
+    programs      => $programs
+};
+
+cmp_deeply( $data, $real_data,
+    'category and dependent data(without aliases and programs) hashes are same'
+);
+

=== added file 'lib-mvhub/t/Structure/get_all_headings.t'
--- lib-mvhub/t/Structure/get_all_headings.t	1970-01-01 00:00:00 +0000
+++ lib-mvhub/t/Structure/get_all_headings.t	2011-09-11 23:59:29 +0000
@@ -0,0 +1,19 @@
+#!/usr/bin/perl 
+
+use strict;
+use warnings;
+
+use Test::Deep;
+use Test::More tests => 3;
+
+my @subs = qw( get_all_headings );
+
+use_ok( 'MVHub::Structure', @subs );
+can_ok( 'MVHub::Structure', 'get_all_headings' );
+
+$ENV{MV_CONFIG_FILE} = $ENV{MV_TEST_CONFIG_FILE};
+
+my @true_heading = ( 'Arts/Culture/Entertainment', 'City/Town Services' );
+my $heading = MVHub::Structure::get_all_headings();
+
+cmp_deeply( $heading, \@true_heading, 'all headings are ok and in order' );

=== added file 'lib-mvhub/t/Structure/get_all_programs.t'
--- lib-mvhub/t/Structure/get_all_programs.t	1970-01-01 00:00:00 +0000
+++ lib-mvhub/t/Structure/get_all_programs.t	2011-09-11 23:59:29 +0000
@@ -0,0 +1,26 @@
+#!/usr/bin/perl 
+
+use strict;
+use warnings;
+
+use Test::Deep;
+use Test::More tests => 3;
+
+my @subs = qw( get_all_programs );
+
+use_ok( 'MVHub::Structure', @subs );
+can_ok( 'MVHub::Structure', 'get_all_programs' );
+
+$ENV{MV_CONFIG_FILE} = $ENV{MV_TEST_CONFIG_FILE};
+
+my @true_program = (
+    { 1001 => 'Test Agency 1: Test Program 1' },
+    { 1002 => 'Test Agency 2: Test Program 2' },
+    { 1003 => 'Test Agency 3: Test Program 3' },
+    { 1004 => 'Test Agency 4: Test Program 4' },
+    { 1005 => 'Test Agency 4: Test Program 5' }
+);
+my $program = MVHub::Structure::get_all_programs();
+
+cmp_deeply( $program, \@true_program, 'all programs are ok and in order' );
+

=== added file 'lib-mvhub/t/Structure/is_category_name_taken.t'
--- lib-mvhub/t/Structure/is_category_name_taken.t	1970-01-01 00:00:00 +0000
+++ lib-mvhub/t/Structure/is_category_name_taken.t	2011-09-11 23:59:29 +0000
@@ -0,0 +1,33 @@
+#!/usr/bin/perl 
+
+use strict;
+use warnings;
+
+use Test::More tests => 6;
+
+my @subs = qw( is_category_name_taken );
+
+use_ok( 'MVHub::Structure', @subs );
+can_ok( 'MVHub::Structure', 'is_category_name_taken' );
+
+$ENV{MV_CONFIG_FILE} = $ENV{MV_TEST_CONFIG_FILE};
+
+my $result
+    = MVHub::Structure::is_category_name_taken( category_name => 'Culture' );
+is( $result, 1, 'is_category_name_taken_by_only_existing_category_name' );
+
+$result = MVHub::Structure::is_category_name_taken(
+    category_name => 'Non_existing_category_name' );
+is( $result, 0, 'is_category_name_taken_by_only_nonexisting_category_name' );
+
+$result = MVHub::Structure::is_category_name_taken(
+    category_name => 'Culture',
+    category_id   => '1007'
+);
+is( $result, 0, 'NO, is_category_name_taken_with_category_name_and_id' );
+
+$result = MVHub::Structure::is_category_name_taken(
+    category_name => 'Culture',
+    category_id   => '1008'
+);
+is( $result, 1, 'YES, is_category_name_taken_with_category_name_and_id' );

=== added file 'lib-mvhub/t/Structure/process_category_form.t'
--- lib-mvhub/t/Structure/process_category_form.t	1970-01-01 00:00:00 +0000
+++ lib-mvhub/t/Structure/process_category_form.t	2011-09-11 23:59:29 +0000
@@ -0,0 +1,152 @@
+#!/usr/bin/perl 
+
+use strict;
+use warnings;
+
+use CGI;
+use Test::Exception;
+use Test::More tests => 7;
+use Test::Trap;
+
+my ( $result, $cgi, $test_msg );
+
+use_ok( 'MVHub::Structure', qw/ process_category_form / );
+can_ok( 'MVHub::Structure', 'process_category_form' );
+
+$ENV{MV_CONFIG_FILE} = $ENV{MV_TEST_CONFIG_FILE};
+$ENV{SCRIPT_NAME}    = 'structure.pl';
+
+#####
+$test_msg
+    = 'process_category_form() with no inputs returns existing categories';
+#####
+
+$cgi = CGI->new();
+trap {
+    MVHub::Structure::process_category_form($cgi);
+};
+$result = category_page_has_existing_categories( $trap->stdout() );
+ok( $result, $test_msg );
+
+#####
+$test_msg
+    = 'process_category_form() gets category_id parameter returns expected result';
+#####
+
+$cgi->param( 'category_id', '1001' );
+trap { MVHub::Structure::process_category_form($cgi); };
+
+$result = category_page_has_existing_headings_and_programs_aliseses(
+    $trap->stdout() );
+ok( $result, $test_msg );
+
+#####
+$test_msg = 'category form dies with non-numeric id:';
+####
+
+$cgi->delete('category_id');
+$cgi->param( 'category_id', 'non-numeric id' );
+dies_ok( sub { MVHub::Structure::process_category_form($cgi) }, $test_msg );
+
+####
+$test_msg = 'found expected string in successful new category creation';
+####
+TODO: {
+    local $TODO = "wait to resolve SEQUENCE / nextval() issue in sqlite ";
+
+    $cgi = prepare_cgi_for_successful_creation($cgi);
+    trap { MVHub::Structure::process_category_form($cgi); };
+
+    ok( $trap->stdout() =~ /Your changes have been saved\./xms, $test_msg );
+}
+
+####
+$test_msg = 'found redirect URL after successful edit category';
+####
+$cgi = prepare_cgi_for_successful_edit($cgi);
+trap { MVHub::Structure::process_category_form($cgi) };
+
+my $expected = q{Location:\s+structure.pl\?mode=structure_menu&success=1};
+ok( $trap->stdout() =~ /$expected/xms, $test_msg );
+
+##########
+
+sub prepare_cgi_for_successful_edit {
+
+    my $cgi = shift;
+    $cgi->delete_all();
+    $cgi->param( 'category_id',   '1001' );
+    $cgi->param( 'category_name', 'Cultural Heritage' );
+    $cgi->param( 'aliases',       'History' );
+    $cgi->param( 'headings',      'Arts/Culture/Entertainment' );
+    $cgi->param( 'programs',      '1001', '1002' );
+    $cgi->param( '_submitted_category_editor_form', '2' );
+
+    return $cgi;
+}
+
+sub prepare_cgi_for_successful_creation {
+
+    my $cgi = shift;
+    $cgi->delete_all();
+    $cgi->param( 'category_name', 'Dance' );
+    $cgi->param( 'aliases',       'dance' );
+    $cgi->param( 'headings',      'Arts/Culture/Entertainment' );
+    $cgi->param( 'programs',      '1001', '1002' );
+    $cgi->param( '_submitted_category_editor_form', '2' );
+
+    return $cgi;
+}
+
+sub category_page_has_existing_headings_and_programs_aliseses {
+
+    my $out = shift;
+    my $match_str;
+    my $result = $out =~ /Edit a Category/;
+
+    #category
+    $result
+        &= $out =~ /category_name\" size.*type.*value=\"Cultural Heritage\"/;
+
+    #alias
+    $result &= $out =~ /History/;
+
+    #headings
+    $match_str = q{name="headings".*value="Arts/Culture/Entertainment};
+    $result &= $out =~ m|$match_str|xms;
+
+    #programs
+    $result &= $out
+        =~ /input checked=\"checked\" name=\"programs\".*value=\"1001\" \/> Test Agency 1: Test Program 1/;
+
+    $result &= $out
+        =~ /input checked=\"checked\" name=\"programs\".*value=\"1002\" \/> Test Agency 2: Test Program 2/;
+
+    $result &= $out =~ /name=\"programs\".*value=\"1003\"\s*\/>/xms;
+
+    $match_str = q{name="programs".*value="1004"\s*\/>\s*Test\s+Agency\s+4};
+    $result &= $out =~ /$match_str/xms;
+
+    return $result;
+
+}
+
+sub category_page_has_existing_categories {
+
+    my $out = shift;
+    my $result = $out =~ /Create a New Category/;
+
+    #headings
+    $result = $result && $out =~ /Arts\/Culture\/Entertainment/;
+    $result = $result && $out =~ /City\/Town Services/;
+
+    #programs
+    $result = $result && $out =~ /Test Program 1/;
+    $result = $result && $out =~ /Test Program 2/;
+    $result = $result && $out =~ /Test Program 3/;
+    $result = $result && $out =~ /Test Program 4/;
+    $result = $result && $out !~ /Test Porgram 5/;    # Nonexist program
+
+    return $result;
+
+}

=== added file 'lib-mvhub/t/Structure/process_synonym_form.t'
--- lib-mvhub/t/Structure/process_synonym_form.t	1970-01-01 00:00:00 +0000
+++ lib-mvhub/t/Structure/process_synonym_form.t	2011-09-11 23:59:29 +0000
@@ -0,0 +1,130 @@
+#!/usr/bin/perl 
+
+use strict;
+use warnings;
+
+use CGI;
+use Test::More tests => 7;
+
+my ( @subs, $out, $cgi, $result, @synonyms, $new_synonym, $success_message );
+
+@subs = qw ( process_synonym_form );
+
+use_ok( 'MVHub::Structure', @subs );
+can_ok( 'MVHub::Structure', 'process_synonym_form' );
+
+close(STDOUT);
+open( STDOUT, ">", \$out ) or die "Can't open STDOUT: $!";
+
+$ENV{MV_CONFIG_FILE} = $ENV{MV_TEST_CONFIG_FILE};
+$ENV{SCRIPT_NAME}    = 'structure.pl';
+
+@synonyms = (
+    'ADD ADHD HADD',
+    'alcohol drinking drunk',
+    'assist help',
+    'cessation quit',
+    'child children kid youth',
+    'class lesson',
+    'computer internet technology',
+    'counseling'
+);
+$new_synonym     = 'search bing google';
+$success_message = qr /Your changes have been saved/;
+$cgi             = CGI->new();
+
+MVHub::Structure::process_synonym_form($cgi);
+
+#make output one line
+$out =~ s/\n//g;
+$result = check_synonyms( $out, @synonyms );
+$result = $result && $out !~ qr/$new_synonym/;
+
+ok( $result, 'found expected synonyms before any processing' );
+
+TODO: {
+
+    local $TODO = 'Remove TODO block after sqlite sequence problem is solved';
+
+    $cgi = prepare_cgi( $cgi, @synonyms, $new_synonym );
+
+    eval {
+        MVHub::Structure::process_synonym_form($cgi);
+
+        $out =~ s/\n//g;
+        $result = $out =~ $success_message;
+    };
+    $TODO = 'fails because sequence query is used and not supported by sqlite'
+        if $@;
+    $result = 0 if $@;
+
+    ok( $result, 'submit synonyms with new synonym and see success message' );
+    $out = '';
+
+    $cgi->delete_all();
+
+    eval {
+        MVHub::Structure::process_synonym_form($cgi);
+
+        $out =~ s/\n//g;
+        $result = check_synonyms( $out, @synonyms );
+        $result = $result && $out =~ qr /$new_synonym/;
+    };
+
+    $TODO = 'fails because addition could not be done due to sequences';
+
+    ok( $result, 'found expected synonyms after addition' );
+    $out = '';
+
+    $cgi = prepare_cgi( $cgi, @synonyms );
+
+    eval {
+        MVHub::Structure::process_synonym_form($cgi);
+
+        $out =~ s/\n//g;
+        $result = $out =~ $success_message;
+    };
+    $TODO = 'fails because sequence query is used and not supported by sqlite'
+        if $@;
+
+    ok( $result,
+        'submit synonyms without new synonym and see success message' );
+    $out = '';
+}
+
+#####
+$cgi->delete_all();
+MVHub::Structure::process_synonym_form($cgi);
+
+$out =~ s/\n//g;
+$result = check_synonyms( $out, @synonyms );
+$result = $result && $out !~ qr/$new_synonym/;
+
+ok( $result, 'found expected synonyms after deletion' );
+
+sub prepare_cgi {
+
+    my $cgi         = shift;
+    my $new_synonym = shift;
+    $cgi->delete_all();
+    my $synonyms = join( "\n", @synonyms );
+    if ($new_synonym) {
+        $synonyms .= "\n" . $new_synonym;
+    }
+    $cgi->param( 'synonyms', $synonyms );
+    return $cgi;
+
+}
+
+sub check_synonyms {
+
+    my $out      = shift;
+    my @synonyms = shift;
+
+    my $result = 1;
+    foreach my $synonym (@synonyms) {
+        $result = $result && $out =~ qr/$synonym/;
+    }
+
+    return $result;
+}

=== added directory 'lib-mvhub/t/Synonyms'
=== added file 'lib-mvhub/t/Synonyms/fetch_synonyms_from_db.t'
--- lib-mvhub/t/Synonyms/fetch_synonyms_from_db.t	1970-01-01 00:00:00 +0000
+++ lib-mvhub/t/Synonyms/fetch_synonyms_from_db.t	2011-09-11 23:59:29 +0000
@@ -0,0 +1,29 @@
+#!/usr/bin/perl 
+
+use strict;
+use warnings;
+use Test::More tests => 3;
+use Test::Deep;
+
+use MVHub::Synonyms;
+
+my @subs = qw( fetch_synonyms_from_db );
+
+use_ok( 'MVHub::Synonyms', @subs );
+can_ok( __PACKAGE__, 'fetch_synonyms_from_db' );
+
+$ENV{MV_CONFIG_FILE} = $ENV{MV_TEST_CONFIG_FILE};
+
+my @real_synonyms = (
+    'ADD ADHD HADD',
+    'alcohol drinking drunk',
+    'assist help',
+    'cessation quit',
+    'child children kid youth',
+    'class lesson',
+    'computer internet technology',
+    'counseling',
+);
+my @synonyms = MVHub::Synonyms::fetch_synonyms_from_db();
+
+cmp_deeply( \@synonyms, \@real_synonyms, 'synonym arrays are same' );

=== modified file 'lib-mvhub/t/Utils/clean_cgi_params.t'
--- lib-mvhub/t/Utils/clean_cgi_params.t	2010-09-27 13:28:16 +0000
+++ lib-mvhub/t/Utils/clean_cgi_params.t	2011-09-11 23:59:29 +0000
@@ -1,8 +1,10 @@
 #!/usr/bin/perl -w
 
 use strict;
+
+use CGI;
 use Test::More tests => 7;
-use CGI;
+
 use MVHub::Utils;
 
 my $q = CGI->new();
@@ -64,7 +66,7 @@
 
 ####
 $test_name
-    = "multitple mixed parameters with same name are selectively cleaned";
+    = "multiple mixed parameters with same name are selectively cleaned";
 ####
 $q->param(
     mixed_multi => "clean",

=== modified file 'lib-mvhub/t/lib/TestData.pm'
--- lib-mvhub/t/lib/TestData.pm	2010-10-16 00:29:43 +0000
+++ lib-mvhub/t/lib/TestData.pm	2011-09-11 23:59:29 +0000
@@ -9,7 +9,16 @@
     use Exporter ();
     our (@EXPORT_OK);
 
-    @EXPORT_OK = qw(@agency @program notifications_href);
+    @EXPORT_OK = qw(@agency
+      @program
+      notifications_href
+      @category
+      @program_category
+      @alias
+      @synonym_stem
+      @heading
+      @heading_category);
+
 }
 our @EXPORT_OK;
 
@@ -17,7 +26,8 @@
 my $dev_user      = $test_data_cfg->param('NOTIFICATION.dev_email');
 
 our @agency = (
-    {   agency_id          => '1001',
+    {
+        agency_id          => '1001',
         agency_name        => 'Test Agency 1',
         record_name        => 'Test Agency 1 (Main Agency Record)',
         contact_first_name => 'Dan',
@@ -27,7 +37,8 @@
         last_updated       => '2009-02-15',
         reminders_sent     => '0',
     },
-    {   agency_id          => '1002',
+    {
+        agency_id          => '1002',
         agency_name        => 'Test Agency 2',
         record_name        => 'Test Agency 2 (Main Agency Record)',
         contact_first_name => 'Jane',
@@ -37,7 +48,8 @@
         last_updated       => '2008-12-02',
         reminders_sent     => '1',
     },
-    {   agency_id          => '1003',
+    {
+        agency_id          => '1003',
         agency_name        => 'Test Agency 3',
         record_name        => 'Test Agency 3 (Main Agency Record)',
         contact_first_name => 'Jack',
@@ -47,7 +59,8 @@
         last_updated       => '2008-06-05',
         reminders_sent     => '2',
     },
-    {   agency_id          => '1004',
+    {
+        agency_id          => '1004',
         agency_name        => 'Test Agency 4',
         record_name        => 'Test Agency 4 (Main Agency Record)',
         contact_first_name => 'Sue',
@@ -57,7 +70,8 @@
         last_updated       => '2007-08-20',
         reminders_sent     => '1',
     },
-    {   agency_id          => '1005',
+    {
+        agency_id          => '1005',
         agency_name        => 'Test Agency 5',
         record_name        => 'Test Agency 5 (Main Agency Record)',
         contact_first_name => 'Bob',
@@ -70,8 +84,9 @@
 );
 
 our @program = (
-    {   agency_id          => '1001',
-        agency_name        => 'Test Agency 1',
+    {
+        agency_id          => '1001',
+        program_id         => '1001',
         record_name        => 'Test Program 1',
         contact_first_name => 'Joe',
         contact_last_name  => 'Schmoe',
@@ -80,8 +95,9 @@
         last_updated       => '2009-02-15',
         reminders_sent     => '0',
     },
-    {   agency_id          => '1002',
-        agency_name        => 'Test Agency 2',
+    {
+        agency_id          => '1002',
+        program_id         => '1002',
         record_name        => 'Test Program 2',
         contact_first_name => 'Jane',
         contact_last_name  => 'Lane',
@@ -90,8 +106,9 @@
         last_updated       => '2008-12-02',
         reminders_sent     => '0',
     },
-    {   agency_id          => '1003',
-        agency_name        => 'Test Agency 3',
+    {
+        agency_id          => '1003',
+        program_id         => '1003',
         record_name        => 'Test Program 3',
         contact_first_name => 'Jack',
         contact_last_name  => 'Black',
@@ -100,8 +117,9 @@
         last_updated       => '2008-12-02',
         reminders_sent     => '2',
     },
-    {   agency_id          => '1004',
-        agency_name        => 'Test Agency 4',
+    {
+        agency_id          => '1004',
+        program_id         => '1004',
         record_name        => 'Test Program 4',
         contact_first_name => 'Sue',
         contact_last_name  => 'Blue',
@@ -110,6 +128,17 @@
         last_updated       => '2008-12-02',
         reminders_sent     => '3',
     },
+    {
+        agency_id          => '1004',
+        program_id         => '1005',
+        record_name        => 'Test Program 5',
+        contact_first_name => 'Lou',
+        contact_last_name  => 'Blue',
+        contact_email      => '[e-mail]',
+        email              => '[e-mail]',
+        last_updated       => '2008-12-02',
+        reminders_sent     => '2',
+    },
 );
 
 our $notifications_href = {
@@ -119,7 +148,7 @@
         'contact_last_name'  => 'Schmoe',
         'reminders_sent'     => 0,
         'expired_record_list' =>
-            [ 'Test Program 1', 'Test Agency 1 (Main Agency Record)' ],
+          [ 'Test Program 1', 'Test Agency 1 (Main Agency Record)' ],
         'agency_contact_email' => '[e-mail]',
         'contact_email'        => $dev_user,
         'public_email'         => '[e-mail]',
@@ -132,7 +161,7 @@
         'contact_last_name'  => 'Lane',
         'reminders_sent'     => 1,
         'expired_record_list' =>
-            [ 'Test Program 2', 'Test Agency 2 (Main Agency Record)' ],
+          [ 'Test Program 2', 'Test Agency 2 (Main Agency Record)' ],
         'agency_contact_email' => '[e-mail]',
         'contact_email'        => $dev_user,
         'public_email'         => '[e-mail]',
@@ -145,7 +174,7 @@
         'contact_last_name'  => 'Black',
         'reminders_sent'     => 2,
         'expired_record_list' =>
-            [ 'Test Program 3', 'Test Agency 3 (Main Agency Record)' ],
+          [ 'Test Program 3', 'Test Agency 3 (Main Agency Record)' ],
         'agency_contact_email' => '[e-mail]',
         'contact_email'        => $dev_user,
         'public_email'         => '[e-mail]',
@@ -158,7 +187,7 @@
         'contact_last_name'  => 'Blue',
         'reminders_sent'     => 3,
         'expired_record_list' =>
-            [ 'Test Program 4', 'Test Agency 4 (Main Agency Record)' ],
+          [ 'Test Program 4', 'Test Program 5', 'Test Agency 4 (Main Agency Record)' ],
         'agency_contact_email' => '[e-mail]',
         'contact_email'        => $dev_user,
         'public_email'         => '[e-mail]',
@@ -180,5 +209,286 @@
     },
 };
 
+our @category = (
+    {
+        category_id   => '1001',
+        category_name => 'Cultural Heritage',
+        stems_name    => 'cultur heritag',
+    },
+    {
+        category_id   => '1002',
+        category_name => 'Music',
+        stems_name    => 'music',
+    },
+    {
+        category_id   => '1003',
+        category_name => 'Arts/Entertainment',
+        stems_name    => 'entertain art',
+    },
+    {
+        category_id   => '1004',
+        category_name => 'Theater/Performance Art',
+        stems_name    => 'art perform theater',
+    },
+    {
+        category_id   => '1005',
+        category_name => 'Museum',
+        stems_name    => 'museum',
+    },
+    {
+        category_id   => '1006',
+        category_name => 'Arts, Youth',
+        stems_name    => 'youth art',
+    },
+    {
+        category_id   => '1007',
+        category_name => 'Culture',
+        stems_name    => 'cultur',
+    },
+    {
+        category_id   => '1008',
+        category_name => 'Hotlines, City Services',
+        stems_name    => 'servic hotlin citi',
+    },
+    {
+        category_id   => '1009',
+        category_name => 'Waste Management',
+        stems_name    => 'wast manag',
+    },
+    {
+        category_id   => '1010',
+        category_name => 'Code Enforcement',
+        stems_name    => 'enforc code',
+    },
+
+);
+our @program_category = (
+    {
+        category_id => '1001',
+        program_id  => '1001',
+    },
+    {
+        category_id => '1002',
+        program_id  => '1001',
+    },
+    {
+        category_id => '1003',
+        program_id  => '1001',
+    },
+    {
+        category_id => '1004',
+        program_id  => '1001',
+    },
+    {
+        category_id => '1005',
+        program_id  => '1001',
+    },
+    {
+        category_id => '1001',
+        program_id  => '1002',
+    },
+    {
+        category_id => '1002',
+        program_id  => '1002',
+    },
+    {
+        category_id => '1003',
+        program_id  => '1002',
+    },
+    {
+        category_id => '1004',
+        program_id  => '1002',
+    },
+    {
+        category_id => '1001',
+        program_id  => '1003',
+    },
+    {
+        category_id => '1002',
+        program_id  => '1003',
+    },
+    {
+        category_id => '1003',
+        program_id  => '1003',
+    },
+    {
+        category_id => '1001',
+        program_id  => '1004',
+    },
+    {
+        category_id => '1002',
+        program_id  => '1004',
+    },
+    {
+        category_id => '1001',
+        program_id  => '1005',
+    },
+);
+
+our @alias = (
+    {
+        category_id => '1001',
+        alias_name  => 'History',
+        stems_name  => 'histori',
+    },
+    {
+        category_id => '1010',
+        alias_name  => 'Code Violations',
+        stems_name  => 'code violat',
+    },
+);
+
+our @synonym_stem = (
+    {
+        synonym_id     => '1001',
+        word_stem      => 'add',
+        submitted_word => 'ADD',
+    },
+    {
+        synonym_id     => '1001',
+        word_stem      => 'adhd',
+        submitted_word => 'ADHD',
+    },
+    {
+        synonym_id     => '1001',
+        word_stem      => 'hadd',
+        submitted_word => 'HADD',
+    },
+    {
+        synonym_id     => '1002',
+        word_stem      => 'alcohol',
+        submitted_word => 'alcohol',
+    },
+    {
+        synonym_id     => '1002',
+        word_stem      => 'drink',
+        submitted_word => 'drinking',
+    },
+    {
+        synonym_id     => '1002',
+        word_stem      => 'drunk',
+        submitted_word => 'drunk',
+    },
+    {
+        synonym_id     => '1003',
+        word_stem      => 'assist',
+        submitted_word => 'assist',
+    },
+    {
+        synonym_id     => '1003',
+        word_stem      => 'help',
+        submitted_word => 'help',
+    },
+    {
+        synonym_id     => '1004',
+        word_stem      => 'cessat',
+        submitted_word => 'cessation',
+    },
+    {
+        synonym_id     => '1004',
+        word_stem      => 'quit',
+        submitted_word => 'quit',
+    },
+    {
+        synonym_id     => '1005',
+        word_stem      => 'child',
+        submitted_word => 'child',
+    },
+    {
+        synonym_id     => '1005',
+        word_stem      => 'children',
+        submitted_word => 'children',
+    },
+    {
+        synonym_id     => '1005',
+        word_stem      => 'kid',
+        submitted_word => 'kid',
+    },
+    {
+        synonym_id     => '1005',
+        word_stem      => 'youth',
+        submitted_word => 'youth',
+    },
+    {
+        synonym_id     => '1006',
+        word_stem      => 'class',
+        submitted_word => 'class',
+    },
+    {
+        synonym_id     => '1006',
+        word_stem      => 'lesson',
+        submitted_word => 'lesson',
+    },
+    {
+        synonym_id     => '1007',
+        word_stem      => 'comput',
+        submitted_word => 'computer',
+    },
+    {
+        synonym_id     => '1007',
+        word_stem      => 'technologi',
+        submitted_word => 'technology',
+    },
+    {
+        synonym_id     => '1007',
+        word_stem      => 'internet',
+        submitted_word => 'internet',
+    },
+    {
+        synonym_id     => '1008',
+        word_stem      => 'counsel',
+        submitted_word => 'counseling',
+    },
+);
+
+our @heading = (
+    { heading_name => 'Arts/Culture/Entertainment', },
+    { heading_name => 'City/Town Services', },
+);
+
+our @heading_category = (
+    {
+        category_id  => '1001',
+        heading_name => 'Arts/Culture/Entertainment',
+    },
+    {
+        category_id  => '1002',
+        heading_name => 'Arts/Culture/Entertainment',
+    },
+    {
+        category_id  => '1003',
+        heading_name => 'Arts/Culture/Entertainment',
+    },
+    {
+        category_id  => '1004',
+        heading_name => 'Arts/Culture/Entertainment',
+    },
+    {
+        category_id  => '1005',
+        heading_name => 'Arts/Culture/Entertainment',
+    },
+    {
+        category_id  => '1006',
+        heading_name => 'Arts/Culture/Entertainment',
+    },
+    {
+        category_id  => '1007',
+        heading_name => 'Arts/Culture/Entertainment',
+    },
+    {
+        category_id  => '1008',
+        heading_name => 'City/Town Services',
+    },
+    {
+        category_id  => '1009',
+        heading_name => 'City/Town Services',
+    },
+    {
+        category_id  => '1010',
+        heading_name => 'City/Town Services',
+    },
+
+);
+
 # all good modules return true 1;
 1

=== modified file 'lib-mvhub/t/lib/TestHelper.pm'
--- lib-mvhub/t/lib/TestHelper.pm	2011-03-31 16:31:33 +0000
+++ lib-mvhub/t/lib/TestHelper.pm	2011-09-11 23:59:29 +0000
@@ -20,6 +20,7 @@
     add_test_data
     create_sqlite_db
     create_temp_auth
+    get_admin_username_password();
     get_files
     get_files_from
     get_host_to_check
@@ -33,8 +34,6 @@
     submit_forms_ok
 );
 
-our ($VERSION) = '$Revision: 1647 $' =~ /([.\d]+)/;
-
 sub add_test_data {
     my $dbh = shift or croak "missing parameter: \$dbh\n";
     my $sql = MVHub::Utils::DB::get_sql_insert_statement(
@@ -59,6 +58,57 @@
         );
         $dbh->do( $sql, undef, @bind_variables );
     }
+    $sql = MVHub::Utils::DB::get_sql_insert_statement(
+        'CATEGORY_X_REQUIRED_FIELDS');
+    foreach my $test_data (@TestData::category) {
+        @bind_variables = (
+            $test_data->{'category_id'},
+            $test_data->{'category_name'},
+            $test_data->{'stems_name'}
+        );
+        $dbh->do( $sql, undef, @bind_variables );
+    }
+    $sql = MVHub::Utils::DB::get_sql_insert_statement(
+        'PROGRAM_CATEGORY_X_REQUIRED_FIELDS');
+    foreach my $test_data (@TestData::program_category) {
+        @bind_variables
+            = ( $test_data->{'program_id'}, $test_data->{'category_id'} );
+        $dbh->do( $sql, undef, @bind_variables );
+    }
+    $sql = MVHub::Utils::DB::get_sql_insert_statement(
+        'ALIAS_X_REQUIRED_FIELDS');
+    foreach my $test_data (@TestData::alias) {
+        @bind_variables = (
+            $test_data->{'category_id'},
+            $test_data->{'alias_name'},
+            $test_data->{'stems_name'}
+        );
+        $dbh->do( $sql, undef, @bind_variables );
+    }
+    $sql = MVHub::Utils::DB::get_sql_insert_statement(
+        'SYNONYM_STEM_X_REQUIRED_FIELDS');
+    foreach my $test_data (@TestData::synonym_stem) {
+        @bind_variables = (
+            $test_data->{'synonym_id'},
+            $test_data->{'word_stem'},
+            $test_data->{'submitted_word'}
+        );
+        $dbh->do( $sql, undef, @bind_variables );
+    }
+    $sql = MVHub::Utils::DB::get_sql_insert_statement(
+        'HEADING_X_REQUIRED_FIELDS');
+    foreach my $test_data (@TestData::heading) {
+        @bind_variables = ( $test_data->{'heading_name'} );
+        $dbh->do( $sql, undef, @bind_variables );
+    }
+    $sql = MVHub::Utils::DB::get_sql_insert_statement(
+        'HEADING_CATEGORY_X_REQUIRED_FIELDS');
+    foreach my $test_data (@TestData::heading_category) {
+        @bind_variables
+            = ( $test_data->{'category_id'}, $test_data->{'heading_name'} );
+        $dbh->do( $sql, undef, @bind_variables );
+    }
+
 }
 
 sub create_sqlite_db {
@@ -72,6 +122,7 @@
         my $sql = $sql_lib->retr($sql_label);
         $dbh->do($sql);
     }
+
     return 1;
 }
 
@@ -85,6 +136,12 @@
     return $auth;
 }
 
+sub get_admin_username_password {
+
+    return qw/ test test /;
+
+}
+
 # accept a list of wild card file patterns
 # return a list (array) of all files in project directory matching
 # examples: