Image Explorer Integration

[This section is for advanced users.]

The Image Explorer image database system (IDS) can be integrated with any existing database-supported business system or the IDS can be utilized for serving images to a Unix-, Linux-, or NT-hosted web server. The Image Explorer application uses Microsoft's Open Database Connectivity (ODBC) to access a multitude of database management systems (DBMS) such as Access, SQLServer, Oracle, Sybase, Informix, etc. Systems integrated with Image Explorer can use any native connection method such as Oracle's OCI or Pro*C or Informix' ISQL or an open interface such as ODBC or Perl's DBI to manage image records in the IDS. Since the IDS can use any DBMS, the scalability of the IDS is directly dependent on the specific DBMS used.

When you create a table within a database for storing images, if you select the 'Store images on file' option, you will be able to Insert, Update, Delete, and Select image records into and from the IDS independent of the Image Explorer interface. You can also create the tables manually instead of using the Image Explorer user-interface. At any time, you can open Image Explorer using Windows NT/95/98/2000 for viewing and managing images added to the database outside of Image Explorer.

Integration consists of creating a valid IDS table and adding one column in the table that is a foreign-key to a related table in an existing system. When the IDS table is created, you will also elect to store images in the file system rather than storing the images as binary data within the IDS. Hence, integration is open and straightforward. Query your custom table and use the image_id column to query the r_images table to retrieve the location of an image.

Figure 1 - Database Schema

Image Records

An image record consists of an image and any additional user-defined data. User-defined data is data in columns defined by a user in a table also created by a user. For a flat-file database, you might add numerous columns for describing an image. When integrating the IDS into an existing system, you will probably only be interested in adding one additional column to an IDS table such as a foreign-key to a key field in another table.

The User-Defined Table

Each table created in the IDS for storing image records must have the following columns defined:

• id - image record ID.
• table_id - ID of this user table in the IDS table r_tables.
• image_id - ID of the image for this record in the IDS table r_images.
• thumb_id - ID of the thumbnail image for this record in the IDS table r_images.
• image_name - A non-unique name for the image record.
• ... plus optional columns added by the user

In the schema depicted in Figure 1, the Category_Photos and Employee_Photos tables are user-defined tables.

The r_images Table

The r_images table contains the actual images. When saving an image to the file system, this table contains the path and attributes for the image.

• id - image id.
• type - Image Explorer uses this information to determine which image library to use when opening an image. This value should be always be set to 2 when inserting or updating records.
• content- Specifies the image storage location - as binary data in the binary_data column or as a file on the file system. This value should always be set to 2 when inserting or updating records.
• binary_data - If the image is stored in a file system, this field will be NULL; otherwise, this field contains the image in binary form within the database.
• image_path - If the image is being stored in a file system, this field contains the path to the image.
• image_width - Specifies the image's width. Changing this field has no affect on the image.
• image_height - Specifies the image's height. Changing this field has no affect on the image.
• image_bpp - Specifies the image's bits-per-pixel as stored in the database. Changing this field has no affect on the image.

The r_tables Table

The r_tables table contains an entry for each user-defined table in the IDS.

• id - table ID for a user-defined table.
• table_name - table name of a user-defined table.
• thumb_loc - 1 if image is stored as binary data in the table column or 2 if the image is stored in a file system. This is always 2 when integrating. This is only used by the Image Explorer user-interface to the IDS.
• image_loc - same as thumb_loc.
• thumb_path - If thumb_loc is 2, this is the location where thumbnails are stored. This is only used by the Image Explorer user-interface.
• image_path - If image_loc is 2, this is the location where images are stored. Users will specify this location.
• description - This column is for information purposes only.

The r_queries Table

The r_queries table contains registered queries for use in the Image Explorer user-interface. You are free to submit any query you wish without registering the query in the r_queries table. If you add a query to the r_queries table, it will be visible with the Image Explorer user-interface.

• id - The query ID for the specific registered query.
• table_id - Each registered query is limited to a single-table query. table_id is the ID of the table being queried.
• query_name - Informational only.
• description - Informational only.

The r_col_defaults Table

The r_col_defaults table contains user-specified default values for columns within user-defined tables. Image Explorer uses this table for storing user-specified default values or default values generated using an Image Explorer macro. See the Image Explorer documentation for the list of Image Explorer macros. Your application can elect to ignore this table or you can query this table and generate default values when inserting new records.

• id - The default value ID.
• table_id - The table ID containing the column for the default value.
• column_name - The column name within the table.
• col_default - The default value used when inserting new records in Image Explorer.
• description - This column is for information purposes only.

The r_db_version Table

The r_db_version table is used to synchronize and upgrade the IDS with new Image Explorer releases.

• db_version - An Image Explorer-defined value. When a database is opened within Image Explorer, the value in this field is checked against Image Explorer's required version and the database is upgraded automatically if the version requires it.

Frequently Asked Questions

Q. How do I retrieve images without using Image Explorer?

A. As an example, consider the schema depicted in Figure 1. The EmployeeID column in the

Employee_Photos table is a foreign key to the Employees table in the system.

The following query will return the image path, height, and width for all employees.:

SELECT image_path, image_width, image_height
FROM r_images
WHERE id IN (SELECT EmployeeID FROM Employees)

Or, a more efficient query that gets all information from the Employees, Employee_Photos, and r_images tables at once is the following query:

SELECT r_images.image_path, r_images.image_width, r_images.image_height,
Employees.LastName, Employees.FirstName, Employees.HomePhone
FROM r_images, Employees, Employee_Photos
WHERE Employees.EmployeeID = Employees_Photos.EmployeeID AND
Employee_Photos.image_id = r_images.id

The above query could be used to populate an intranet employee listing with employee data and photos.

Q. How can I create and manage varying-sized thumbnails in addition to my images?

A. Here's a suggested way although there are many solutions to this.

Image Explorer creates a 96 x 96 pixel thumbnail for each full-size image. The thumbnail image is stored in the r_images table and referenced by the thumb_id column in the user-defined table. DO NOT create thumbnail images in the r_images table and add a value to the thumb_id column in the user-defined table unless each thumbnail image is exactly 96 x 96 pixels. Failure to follow this will eliminate thumbnail rendering in Image Explorer for the specific user-defined table.

So, given the above restriction, perform the following to implement thumbnails with a size different than 96 x 96 pixels for use in a custom application using the IDS:

The thumbnails created using this method will not be visible in Image Explorer. Image Explorer will still create a 96 x 96 pixel thumbnail for managing the full-size images referenced from the user-defined table when you view the images in Image Explorer.

To add custom thumbnail support for the Employee_Photos table in the schema depicted in Figure 1, create a table called r_thumbs and add the following columns: id, image_path, image_width, and image_height, and EmployeePhotoID (foreign-key Employee_Photos).

To select the employee information, employee photo, and thumbnail, perform the following query:

SELECT r_images.image_path, r_images.image_width, r_images.image_height,
r_thumbs.image_path, r_thumbs.image_width, r_thumbs.image_height,
Employees.LastName, Employees.FirstName, Employees.HomePhone
FROM r_images, r_thumbs, Employees, Employee_Photos
WHERE Employees.EmployeeID = Employees_Photos.EmployeeID AND
Employee_Photos.image_id = r_images.id AND
Employee_Photos.id = r_thumbs.EmployeePhotoID

When you add records to the Employee_Photos and r_images tables, add a related record to the r_thumbs table as well.

Your software will need to generate the thumbnail referenced by the record in the r_thumbs table. You can also use Image Explorer to generate your custom thumbnails. Simply create a working table in Image Explorer. Copy the images from Employee_Photos table to the working table, re-size all the images in the working table, then export the images to the folder where your thumbnails will be stored.

Q. How do I insert new images and image records into the IDS without using Image Explorer?

A. For the schema depicted in Figure 1, the following SQL will insert new image records and images into the IDS:

INSERT INTO r_images (id,type,content,image_path, image_height, image_width)
VALUES (25,2,2,'//webserver/e/image_databases/employees/uniquename.jpg',640,480)

INSERT INTO Employee_Photos (id,table_id,image_id,image_name,EmployeeID)
VALUES (250,1,25,'My Image',249)

When you start your application, you should perform a query on the r_tables table and save the value for the table_id required by the second insert. You will also need to determine a unique id for the primary key fields, id, in both inserts. Use your DBMS' sequence generator or query the max value for these primary key fields and increment by 1.

Q. How do I delete images and image records from the IDS without using Image Explorer?

A. For the schema depicted in Figure 1, the following SQL will delete an image and image record from the IDS:

DELETE FROM Employee_Photos WHERE id = 2
DELETE FROM r_images WHERE id = 14 or id = 15

Before performing the first delete, query the Employee_Photos table for the image_id and thumb_id values and use these values in the second delete.

You will also need to query the r_images table for the image_path value prior to the second delete to get the paths of the image and thumbnail in order to delete the actual image files. If the image_path value is NULL, then the images are probably being stored as binary data in the binary_data column and not in a file system.

Q. How do I create new tables for storing image records in the IDS without using Image Explorer?

A. Perform the following SQL when adding new IDS tables:

CREATE TABLE MyTable (id INTEGER, table_id INTEGER, image_id INTEGER,
thumb_id INTEGER, image_name VARCHAR, EmployeeID INTEGER, [additional columns,]
CONSTRAINT pk_MyTable PRIMARY KEY(id),
CONSTRAINT fk_MyTable_r_tables_1 FOREIGN KEY (table_id) REFERENCES r_tables(id),
CONSTRAINT fk_MyTable_r_images_2 FOREIGN_KEY (image_id) REFERENCES r_images(id),
CONSTRAINT fk_MyTable_r_images_3 FOREIGN_KEY (thumb_id) REFERENCES r_images(id))

In the above Create Table statement, replace 'MyTable' with your specific table name. Be sure to specify the constraint names exactly as shown since these names are used for upgrading databases in future releases of Image Explorer. You will also need to replace the column data types with your specific DBMS' equivalent data types.

Q. How do I drop tables from the IDS without using Image Explorer?

A. Perform the following SQL when dropping IDS tables:

When dropping a table from the IDS, you must also delete records in other IDS tables that are related to the table being dropped.

First, perform a query on the r_images table to get and save a list of records referenced by records in the table we're dropping.

SELECT r_images.id
FROM r_images, MyTable
WHERE r_images.id = MyTable.image_id OR r_images.id = MyTable.thumbid

Next, drop the table:

DROP TABLE MyTable

Next, delete the records in r_images using the list created by the first query.

DELETE FROM r_images WHERE id IN (list from first query)

Next, get the id of the table we're dropping from the r_tables table and delete any queries in the r_queries table related to the table we're dropping. You should perform a query on the r_tables table first to get the id.

SELECT id FROM r_tables WHERE table_name = 'MyTable'
DELETE FROM r_queries WHERE table_id = [id from previous query]

Next, delete any records in the r_col_defaults table that are related to the table we're dropping.

DELETE FROM r_col_defaults WHERE table_id = [use the id previously selected]

Finally, delete the entry in the r_tables table for the table we're dropping.

DELETE FROM r_tables WHERE id = [use the id previously selected]

In the above Create Table statement, replace 'MyTable' with your specific table name.

Q. How do I insert records into the r_col_defaults table without using Image Explorer?

A. Perform the following SQL when adding new default values for columns:

First, perform a query on the r_tables table to get the id of the table the default value belongs to.

SELECT id FROM r_tables WHERE table_name = 'MyTable'

Next, insert a new default value into the r_col_defaults table:

INSERT INTO r_col_defaults (id, table_id, column_name, col_default, description)
VALUES (20,[id from previous query], 'TheColumnName','The default value','A description')

You must determine a unique primary key value for the id field. Also, your custom application is responsible for obtaining and utilizing and column defaults when inserting and updating records.

Q. Does the IDS support GIF images?

A. You can add GIF images to the IDS outside of Image Explorer. Image Explorer does not support GIF display; however, most browsers do support GIF display. The "No Image" thumbnail will be displayed for GIF images within Image Explorer.

If your custom application queries the IDS and generates HTML for display in a web browser, the GIF images in the IDS will display properly.