Legacy Database Replacement with LAMP

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)

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.