Legacy Database Replacement with LAMP

Some legacy database applications are prime candidates for replacement with LAMP-based Web applications. Richard Hulse explains how Radio New Zealand completed such a project.
The Query Compiler

At the core of BRAD is a term parser and a query compiler. The term parser takes a query, breaks it down and places the components into an array. The array contains a MySQL modifier, +, -, <, >, ~; an atom, a part of the query string—either a word or a phrase; and an optional field name.

The term parser automatically adds a + to each atom when no modifier is present, making all searches AND by default. This is a good thing because users expect that this is how a search engine will work—the more terms you add, the more refined the search.

The optional field is used to support advanced searches when particular words are required in a specific field. In BRAD we retained the . field search operator.

When a normal search is undertaken, the query compiler interrogates each table within the scope of the search and returns a list of full-text fields for each. These are used to compile a query that spans all the full-text fields.

The query compiler can manage a mix of full-text general terms and non-full-text, field-specific terms. The query compiler allows BRAD's data sources to be extended almost without limit and the generated queries to adapt dynamically—removing the need for static query boilerplates for each object class that represents real objects in the database.

A standard Web page form allows the user to control all aspects of the search (Figure 2). Users can select as few or as many zones or tables as they want. These can be customised to meet company and user requirements.

Figure 2. An Explanation of the BRAD Search Interface (See Table 2)

Table 2. Fields in the Search Interface

1a: Search AreaThis is where the search terms are entered.
1b: Search Button Press to search.
2a: Past SearchesOpens an area that shows searches made in the past. Items in this list can be clicked to do the search again.
2b: Latest Shows the latest entries in Works, CFMS and NATS data sources. Limited to 250 from each, and sorted with the latest at the top.
2c: Fewer This link (and the one on the next row) toggles between More and Fewer and reduces or increases the number of BRAD search options that show.
3a: ZoneBRAD divides its data into zones that relate to different parts of the company. Each zone has a number of different data sources. The zone selector allows you to choose the area of the company in which you'd like to search.
3b: InThis selector allows you to determine which data in the zone will be searched. You typically can search across all data in a zone or only one type of data.
4: Order by 
5: MediaYou can ask BRAD to search only for records that are stored on certain types of media.
6: CountThe number of items displayed per page.
7: Display ModeDisplay modes relate back to the old BRS system and allow the user to choose different summaries for the search results. Display modes can be customised, so if users need a special format it can be added.
8: Show ResultsIn tabs or as a list. BRAD's normal mode displays the results of each search under a tab-style interface. In list mode, it prints out a list under individual headings. List mode can be used to make lists for printing or pasting into e-mails.
9: Show Details inThis selector allows users to choose between viewing full-record data, by clicking on the link for an item, in a new window or the current browser window.
Extending BRAD

BRAD was written to be extensible in all respects. The data searching can be extended to any kind of data, and specific kinds of searches can be applied to that data.

One of the problems that existed in our company was the use of different database applications for different tasks, with different data being spread across several applications. Our two radio networks use an application called Selector to schedule music items for air. One database, with around 10,000 music tracks, is used for National Radio, while 100,000 music tracks spread across five databases are used for Concert FM. Five databases are used for Concert FM due to limitations in the size of data that Selector can handle.

If a staff member wanted to search for a piece of music, he or she would have to go to each of three applications—BRS, National Radio Selector and Concert FM Selector. There was no way to search all of these at one time.

Fortunately, Selector has a utility to export data in XML format. Although there is no documentation for this and none could be obtained, Bruce was able to determine how to run the export utility and FTP the data to the BRAD server from a Windows workstation. This is done each morning, and a Perl script is run on BRAD to import all the data. The five Concert FM databases are merged into one table, as all the data is unique.

The original search module was extended to search more than one table and return the results, regardless of the number or type of fields. Results are displayed in a tab-style manner (Figure 3).

Figure 3. A Truncated Page of Results

You can see the first results from the Works table. The other inactive tab shows the number of results in the CFM Selector table. Depending on the scope of the search and the results, any number of tabs might be showing. Producers now can search any of the music data from one simple interface.

Since the first Alpha version was released, many other new features have been added at the request of the staff. Among these are a search history and a shopping cart. The cart can hold items from any table. Carts can be saved and restored, and once created, a cart number also can be e-mailed to the librarian. This saves staff having to print or e-mail whole lists of material—they simply e-mail the cart number.