You might want to review the technical requirements for MySQL and the DLXS System.
Detailed instructions for automatic generation of Data and Media Tables, including the loading of data, is found in Image Class Data Loading: MySQL. This document, on the other hand, aims to provide an overview of the role of the Data and Media Tables in the functioning of Image Class, as well as enough information to generate the tables through alternative methods if need be.
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. Name this field using the convention collid_all.
The ID field must have a full text index. It is also advisable to add a regular index to this field. Doing so will dramatically improve performance in certain situations. As of release 11a, the icsgml2mysql.pl script does not do add this index automatically.
IDs that include hyphens, periods, and basically anything other than alpha-numeric characters should have the ID field mapped to ic_exact in the Collmgr/Colldb field field_admin_maps, which forces it to be searched as an exact phrase. See also the contextual help in Collmgr.
Each additional field to be searchable in Image Class must have a full text index.
By default MySQL indexes words as short as 4 characters. To index shorter words, making them searchable, set the minimum word length to 2. This is done a the time MySQL is started. See the install documentation for details. Full text indexes must be rebuilt to take advantage of this change. The easiest way to do so for a MyISAM table is to use the following statement, which rebuilds the index file:
REPAIR TABLE tbl_name QUICK
It is recommended that there be a Media Table for every collection. Technically, the Media Table is optional. 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 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.
The data loading tools provided with Image Class always create a Media Table. This being the case, there is hardly a reason not to have a media table, unless alternative loading methods are in use.
Note: The m_id and m_iid fields of the Media Table should be indexed.
View the detailed Media Table Definition.
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".
The data loading tools provided with Image Class initially create "prep" tables, leaving production tables, if any exist, alone (refer to Loading Data Records: Detailed Steps). The prep tables are named collid_prep and collid_media_prep (substituting the actual collid). Prep tables can be tested with the middleware by adding prep=1 to the URL.
The table names must be entered 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.
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.
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.
The data loading tools provided with Image Class attempt to obviate the need for direct interaction with MySQL. Care must be used when working directly in the DLXS MySQL database because there are many system level tables present and a mistake could disable DLXS.
Author's Note: I know a guy who once deleted the entire DLXS database with two brain-dead clicks of a mouse. It took several hours on a Friday evening to recover from backup.
show tables;
create table ( art_id varchar(10), art_fn varchar(12), art_ti
text, art_cr text, art_su text, art_all text ) TYPE=MyISAM;
describe art;
drop table art;
load data local infile 'art.txt' into table art;
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";
alter table art add FULLTEXT art_all (art_all);
alter table art
add index art_id (art_id(10));
show create table art;