Image Class Data Loading: MySQL

Requirements

MySQL 3.23 or newer. MySQL 4.0.2 or newer is recommended for best searching functionality. Image Class uses the full text search capabilities of MySQL. MySQL version 3.23 has basic full text capability. Version 4.02 introduced several new features such as phrase searching, stemming with wildcard, and better boolean controls.
Using MySQL for Image Class content requires that MySQL also be used for the CollMgr Collection Database.
Also see: DLXS System Requirements

Introduction

Image Class version 3 (DLXS 11) introduces the use of MySQL for search and retrieval. Assuming DLXS has been installed and tested, the basic steps to bringing a collection online with Image Class and MySQL are as follows.

load image/media files to server, including the running of imageprep.
import descriptive metadata into MySQL as an Image Class Data Table.
import media file metadata into MySQL as an Image Class Media Table (sometimes optional).
use collmgr to create the colldb record for the collection and configure the collection for use. Documentation is provided contextually within Collmgr.
test in browser.

Note: Access restrictions for the collection must also be established / configured.

Data Table

A Data Table is required for each collection. The Data Table holds descriptive metadata. A collection can have any number of descriptive metadata fields, and for each there is a column in the Data Table.

Field names must be legal MySQL field names, otherwise there are no restrictions.

The Data Table must have a field with unique values that can be used as record identifiers.

A media filename field is necessary if image files or other types of media are to be displayed by Image Class. Filenames must have filename extensions. It is best if the filename extension matches that of the master image file, and not that of the file to be delivered online. Sometimes they do not differ.

If searching of the entire record at once is desired, an additional field concatenating the values of all other fields is needed. This is referred to as the "all" field.

The ID field must have a full text index.

Each additional field to be searchable in Image Class must have a full text index.

Media Table

A collection may require a Media Table. The Media Table contains detailed information about the media files ( i.e., most often images ) that are part of a collection.

In contrast to the Data Table, the Media Table has a required set of fields that do not vary.

A Media Table is required in the following situations:

A collection includes jp2 files.
A collection includes multiple media file formats. e.g., any collection simultaneously using sid and jp2 files will need a media table.
A collection includes multiple media files per metadata record.
Some records of a collection do not have media files.
Some images of the collection need to be restricted, while others not.

A Media Table is required in most situations. It is only when the collection follows the simple model of having image files in the sid format, with thumbnails as jpg, and only one image per record that a Media Table is not needed.

Note: The m_id and m_iid fields of the Media Table should be indexed. A full text index is not necessary.

View the detailed Media Table Definition.

Table Naming Conventions

It is recommended that Data and Media Table names relate to the collection ID. Name the Data Table exactly as the collid, and the Media Table as "collid_media". Attaching a simple version number to each will facilitate deployment and minimize interruption of service.

Initially,

collid_1
collid_media_1

and at the next data update,

collid_2
collid_media_2

The table names are stored in each collection's Collmgr Collection Database record. Therefore, the tables are not used by Image Class until the Collmgr record is updated and released. This facilitates switching from the old version to the new.

Field Naming Conventions

Field labels, as displayed to the end-user in Image Class are easily changed on a whim using Collmgr. Field names, on the other hand, are used behind the scenes for unique identification of fields.

Image Class has traditionally relied on abbreviated field names that follow the convention collid_fieldabbrev, e.g,. bhl_ti for the Title field of the Bentley Image Bank collection. There is nothing requiring this convention be followed. There is merit in using brief field names since they are carried around on the URL, entered multiple times in the Collmgr Collection Database record, and stored on the CGI session.

Field names must be legal MySQL field names.

Basic MySQL Commands

Please refer to MySQL documentation at www.mysql.com for full instruction on how to use the following MySQL commands on the MySQL command line.

Command: show

Purpose: list existing tables

Example:

show tables;

Command: create

Purpose: creating a new table

Example (using the collection id "art" using the conventions above):

create table ( art_id varchar(10), art_fn varchar(12), art_ti 
            text, art_cr text, art_su text, art_all text ) TYPE=MyISAM;

Command: describe

Purpose: show the field definitions for a table

Example:

describe art;

Command: drop

Purpose: drop/delete a table

Example:

drop table art;

Command: load

Purpose: load tab delimited ascii data into table

See also MySQL documentation: mysqlimport

Example:

load data local infile 'art.txt' into table art;

Command: select

Purpose: submit query and display results

Examples:

select count(*) from table art;
		  select art_ti, art_cr from table 
            art limit 10;
			

			select art_ti, art_cr from table art where art_cr = 
            "picasso";

Command: alter

Purpose: adding an index to a field

Examples:

alter table art add FULLTEXT art_all (art_all); 
		  
alter table art 
            add index art_id (art_id(10));

Command: show
Purpose: show definitions of database components
Example:

show create table art;