Marshall Breeding
Report of the Director for Innovative Technologies and Research
December 2006 - January 2007
Vanderbilt Television News Archive
A statistical summary of the activities of the Vanderbilt Television News Archive is available here: Dec 2006, Jan 2007.
A cumulative statistical summary of the Archive's activities for 2006 calendar year is available here.
Videotape Loan Requests
In January 2007 the Archive set an all-time record in the number of videotape loan requests fulfilled and in the income received for loan service fees.
Contini-Volterra Project
In support of the project funded by the Kress Foundation to digitize the photographs from the Contini-Volterra collection, Marshall created a new version of the image management system. This version of the interface was significantly different than previous versions in that it uses MySql as the relational database instead of DB/TextWorks. This will be the first of the library projects to use this version of the infrastructure we use for digital projects.
Transition from DB/TextWorks to MySql
Over the last year, we have seen a significant increase in the activity on the servers that support the TV News Web interface and database. Increasingly, we are seeing the underlying database engine, based on DB/TextWorks unable to keep up with the load. Database transactions that involve updating records at time fail to respond quickly enough and can result in the queuing of multiple transactions. We see some episodes where a given transactions holds the indexes open improperly, causing other transactions to stack up. Overall, we have become a victim of our success and need to migrate the system to a faster database engine.
We have long planned to replace DB/TextWorks with MySQL, on open source relational database management system that is well proven for these type of applications. The TV News applications, along with the others that use the perl-based digital library software, were designed from the beginning to as independent of any given database as possible. The application separates the database from the application by using the Open Database Connectivity specification, and by passing all database transactions using standard SQL syntax.
All database transactions are defined in a single file of perl routines. This approach compartmentalizes the database-oriented functions into a single file that can be adjusted as necessary to accommodate a different back-end database.
The transition from DB/TextWorks toMySQL, involved several steps:
- Installing the ODBC connecter layer for MySQL, available as a free open-source application.
- Converting each of the DB/TextWorks databases into a MySQL table:
- tvn: the database that holds all the metadata, abstracts, and catalog records for the collection (about 840,000 records).
- tvncalendarnotes: Annotations for the calendar-based browsing system.
- tvnip: a database of all the IP addresses associated with subscribing institutions. (over 6,000,000 records)
- tvnorders: data related to videotape loan requests
- tvncustomers: records for each registered user of the archive (58,000 records)
- staff: the database of operators
- tvnusestats: records that hold the use statistics for each record in tvn
- tvnstats: records for monthly statistics
- tvnsubscribers: the records for each institutional subscriber
- transcode: the database that manages the automatic MPEG / RealMedia transcoding system
The conversion of each of these database involves several steps:
- Extraction of the data from the DB/TextWorks database using a pipe-delimited format.
- Writing a script to define all the fields of the table to MySQL
- Writing a script to load the data into the MySQL table.
Decisions had to be made whether to use the INNOB or the MyISAM storage engine for each of the tables. InnoDB, a faster database for rapid transaction was implemented for all the tables except for tvn. Only MyISAM supports the FULLTEXT indexing essential for searching the abstracts.
Indexes were created automatically in DB/TextWorks. Indexes needed to be specifically defined in MySQL and queries optimized to take advantage of them. A simple IP lookup, for example, takes 6 minutes to return against the tvnip table. Once indexed, results returned instantaneously. FULLTEXT indexes were created within tvn for the title and abstract columns as well as for Reporters, with BTREE indexes produced for Date, RecordHeader, HeaderLink, and other fixed-length fields.
Over 323 separate perl programs underly the TV News Web site. These programs produce the user interface for staff and public users of the site, management of digitization workflow, maintenance of the databases, and financial transcactions related to loan requests and subscriptions. Almost all of these programs were reviewed and modified in the process of migrating the Web site.
One of the significant differences between DB/TextWorks and MySQL involves dates. All dates in the original database have to be converted into the more standard form required by MySQL. MySQL performs more rigorous data typing and will not malformed data to be loaded. Several of the database required significant data clean-up as a prerequisite to conversion.
Once all the databases were created and loaded with the converted data, it was necessary to adjust almost all of the perl scripts that comprise the application. Only part of these changes were necessary to perform a lateral conversion, but we also wanted to take the opportunity to make a number of enhancements to the application. These enhancements included:
- Improved security. Under the previous version, data related to user sessions was passed as part of the cgi query string. All this data is now held in server-side session files that do not expose any session-oriented data. This involved a major re-write of the diglib-common.pl file used by all perl programs in the application. It also involved rewriting the perl routine that generates all html page headers. The &pagetop routine now requires four parameters that define the page title, the cursor focus, the type of users allowed to access the page, and whether the page should enforce SSL. The changes in security also involved the changes in the &getauthlevel which is called to control the functions available depending on the authorization level of the operator. The system now more systematically manages two types of logins: one for customers and one for staff operators. The credentials for these login's are managed through server-side session parameter files. The routines for IP authorization of users from institutional subscribers was completely reworked.
- In the user interface layer, all routines involving dates needed to be rewritten. DB/TextWorks offered a number of non-standard extensions for handling dates.
Other enhancements made during the transition included:
- Redesign of the top-level page. The page now offers a search box. If the user comes into the system from a Vanderbilt address or that of a subscriber, any search perform produces results displays in the usual way. If a user types in a search from a non-subscribing IP address, a login page appears along with the abbreviated results set originally created for users entering from the OpenWeb. Upon successful login, or completion of the new-user registration, the system displays the complete result set from the original query.
- A new logo was created. The new logo presents Archive's more correctly and includes a subtitle showing that the archive is "A unit of the Heard Libraries at Vanderbilt University."
- The top level page detects the origin of the user, and presents specialized text boxes depending on whether the user originates from a Vanderbilt IP address, from a subscriber, or from a non-subscribing address.
- User login and account features were moved to the top of the page, which is more consistent with how other Web-based systems position these features. Links for moving to previous and next pages in result lists are now presented at the top of the page in addition to the controls at the page bottom.
- All pages that require data entry were enhanced so that the focus of the cursor is always positioned in the proper place upon page load.
- Staff mode now always defaults to the Advanced Search
- A page for producers was created that provides the contact information for the person at each of the networks responsible for licensing of content.
- Several enhancements were made to the e-commerce system for creating videotape loan requests:
- A click-through page was introduced that requires the user to agree to the terms of the loan and associated fees. The breadcrumb links were adjust to reflect this new step in the ordering process
- Mod-10 checksum algorithm was implemented to eliminate typographical errors when entering credit card numbers.
- The ability to request loans on DVD was implemented.
- A cancelled status was created to allow us to suppress orders for which we never receive payment, or is cancelled for any other reason.
- The user login process was enhanced to better handle cases where an incorrect username or password is entered. For a bad password, the username is preserved eliminating the need to retype.
- The MyAccount page was enhanced to allow uses to attach a session with an order established in an earlier session. If multiple unfinished orders exist, the user can select one by using the radio buttons provided.
- The system now detects non-unique e-mail addresses provided in a new registration. This will prevent the problem that we have been seeing with users creating multiple accounts.
- The system that automatically manages the transfer of MPEG files from the recording systems in the TV News Off-air room to the staging servers and produces the RealMedia derivatives needed several changes as part of the transition. MySQL handles backslashes differently than did DB/TextWorks, and the filenames often contain this symbol. Accommodating this difference required changes in the submission process and in the database conversion.
The migration to the new database will occur in two phases. On Friday, Feb 9, 2007, all staff activity switched to the new system. We anticipate conversion of the public side by Feb 16, 2007. In the mean time changes made on the staff side will not show up on the public server.
Network Storage Issues
Marshall had previously met with Shanmuga Sundaram who is the person in ITS responsible for developing storage strategies and infrastructure. As a result of these conversations, ITS has agreed to provide TV News with 6TB of of storage to help use with our short-term storage needs. How longer term needs will be handled is still under discussion.
Primo
Marshall worked on some revisions to the Perl script that performs the extract of records from the TV News database in Dublin Core XML format. One of the problems that turned up involved ensuring that the records all passed XML validation as they were harvested into Primo. It took several tries, but Marshall was able to develop a perl script that performed the necessary transformations to ensure the production of valid XML documents.
Marshall participated in the site meetings with Ex Libris staff on Jan 15-16.
Office Rennovation
For part of December and January, Marshall's office was under rennovation to remove a wall that encased a 4ft X 10ft space, originally created for plumbing, that is no longer used. Removal of this wall allowed the creation of a new door opening into the OUL suite, which makes the adjacent office more usable. Marshall worked from an unoccupied office on the 9th level during the rennovation.
Meetings
Marshall participated in regular committee meetings, including Strategy and Planning Council, Metadata Committee, TV News Staff.
Professional Activities
Marshall attended the American Library Association Midwinter Meeting in Seattle, WA. Conference activities included participation on the LITA Top Technology Trends panel, convening the SirsiDynix Large Sites Users Meeting and many individual meetings with executives from library automation companies.
Marshall's regular column appeared in Computers in Libraries and he wrote several short articles for the October and November issues of ALA TechSource Smart Libraries Newsletter.