Relational database extenders for handling complex data types5799310Abstract This invention is directed to relational extenders for a computer-based relational database. Each relational extender includes at least one column, in a first, business, table containing a user defined application database, dedicated to object handles for defining the complex data type of an object; a second, attribute, table containing at least one column defining a unique characteristic associated with the one object and one column dedicated to containing the object handle; and a third, metadata, table containing at least one column defining a common characteristic associated with all objects defined within the business table and one column dedicated to containing the object handle and at least one column dedicated to containing a reference to object data associated with the object. The relational extender further includes a fourth table containing a reference to each object handle column defined in the first table, and a fifth table containing the names of the second and third tables for each object defined in the first table. Claims Having thus described our invention, what we claim as new and desire to secure by Letters Patent is: Description TECHNICAL FIELD
______________________________________
CREATE TABLE Table.sub.-- A (
______________________________________
id CHAR, /* employee ID */
name VARCHAR(60), /* employee name */
picture DB2Image, /* employee picture, stored
as BLOB */
mgrPicture DB2Image /* manager picture, stored in
file server */
);
______________________________________
User.sub.-- B created Table.sub.-- B with these statements:
______________________________________
CREATE TABLE Table.sub.-- B (
______________________________________
id CHAR(6), /* employee ID */
name VARCHAR(60), /* employee name */
picture DB2Image, /* employee picture, stored
as BLOB */
);
______________________________________
Note that DB2Image is a User Defined Type (UDT) created by:
______________________________________
CREATE DISTINCT TYPE DB2Image AS VARCHAR(254) WITH
COMPARISONS;
______________________________________
1. Data Model Within a database, all extenders (image, audio, video) share a special qualifier, mmdbsys, all metadata tables and triggers are created and owned by this special qualifier. When a database is first enabled (for any extender), the base layer creates 2 administrative support tables:
______________________________________
mmdbsys.ExtenderInfo
mmdbsys.MetatableNames
______________________________________
For each extender, there are 4 additional administrative support tables:
______________________________________
mmdbsys.xxxColumns
mmdbsys.xxxBaseImport
mmdbsys.xxxAttrImport
mmdbsys.xxxDeleteLog
______________________________________
Where `xxx` is up to the first 8 characters of the extender name. For example, administrative support tables for Image Extender are named: ImageColumns, ImageBaseImport, ImageAttrImport, ImageDeleteLog. For each user table that is enabled for an extender, 2 corresponding metadata tables are created: xxxB+time() and xxxA+time(), where `xxx` is the extender name and time() is the current time expressed in number of elapsed seconds since 12:00 am Jan. 1, 1970. For example, ImageB12345678 and ImageA12345678. The ImageB12345678 table holds metadata that are similar for all extenders (e.g.: filename, size, remarks, etc.). The ImageA12345678 table holds metadata specific for Image Extender (e.g.: width, height, number of colors, etc.). For each image in the user table, there is 1 row in each of the 2 image metadata tables. There is no limit on the number of image columns a user can have on a table. The user can choose to store images in DB2 BLOB or in file system. Within a database, all handle values are unique, persistent tokens. The handle value serves as a key to identify an image. 2. Handle Encoding Handle value is encoded as follow:
______________________________________
Current time (number of
time() 8 characters
seconds since 1/1/1970)
delimiter 1 character
random() 8 characters
A random number
delimiter 1 character
database name
8 characters
CURRENT SERVER
delimiter 1 character
base metadata
18 characters
ImageB12345678
table name
delimiter 1 character
attr metadata
18 characters
ImageA12345678
table name
delimiter 1 character
qualifier 8 characters
Qualifier
(creator/owner) of the
user table.
delimiter 1 character
table name 18 characters
User table name.
delimiter 1 character
column name 18 characters
User column name.
Total: 111 characters
______________________________________
The ":" character is used as the delimiter. 3. Scenario The Image Relational Extender design is best illustrated using a given scenario. Assume that it is desired to create the personnel database mentioned earlier. This section explains events that happen at each of the steps (create database, create table, alter table, insert, select, update, delete). a. Create Database User Does:
______________________________________
CREATE DATABASE personnel
/* name of the database */
ON/mmdb /* what director */
WITH "MM personnel database
/* comment */
on /mmdb";
DBiEnableDatabase ();
______________________________________
Note that caller of DBiEnableDatabase() must have DBA or equivalent authority. Also, the application must establish a valid connection to the database prior to calling this API. DB2/CS Does: Create the database. Create the system tables etc. Image Extender Does: Inside DBiEnableDatabase(): Call a base layer function, .sub.-- bestEnbaleDatabase() which does among other things): Syntax for calling .sub.-- bestEnbaleDatabase():
______________________________________
.sub.-- bestEnbaleDatabase(
USER /* in, current user */
CURRENT SERVER /* in, database name */
"Image", /* in, extender name
"DB2Image", /* in, Image UDT name */
"createImage /* In, name of Image
UDF" callback stored procedure */
);
______________________________________
Check authority of USER using current connection. Create UDT, DB2Image:
______________________________________
CREATE DISTINCT TYPE DB2Image AS
VARCHAR(254) WITH COMPARISON;
______________________________________
Create these administrative support (metadata) tables:
______________________________________
mmdbsys.ExtenderInfo
/* contains one row for
each extender installed */
mmdbsys.MetatableNames
/* contains one row for
each user table that is MM
enabled */
mmdbsys.ImageColumns
/* contains one row for
each column of DB2Image
type */
mmdbsys.ImageBaseImport
/* contains one row for
each importing image */
mmdbsys.ImageAttrImport
/* contains one row for
each importing image */
mmdbsys.ImageDeleteLog
/* log external image
filename being deleted */
______________________________________
Add a row to ExtenderInfo table:
______________________________________
extender = "image" /* extender name */
udt = "DBTImage" /* UDT name */
______________________________________
Call the createImageUDF() callback routine which alter ImageAttrImport tabale to add image specific columns. Note that the UDT, all metadata tables and UDFs are created under mmdbsys userid. b. Create Table Now User.sub.-- A wants to create Table.sub.-- A. User Does:
______________________________________
EXEC SQL CREATE TABLE emp (
id CHAR(6), /* employee ID */
name VARCHAR(60), /* employee name */
picture DB2Image, /* employee picture, stored as
BLOB */
);
DBiEnableTable("Table.sub.-- A");
/* base table name, qualified
or unqualified */
DBiEnableColumn("Table.sub.-- A",
/* base table name, qualified
or unqualified */
"picture", /* image, column name */
DBI_INTERNAL, /* bitmask, storage type =
BLOB */
NULL, /* import action, not
applicable */
NULL); /* delete action, not
applicable */
______________________________________
Note that caller of these APIs must have "alter table" or higher authority on the subject table. Also, the application must establish a valid connection to the database prior to calling these APIs. MMDM Does: Inside DBiEnableTable(): Call a base layer function, .sub.-- bestEnableTable() which does (among other things): Syntax for calling .sub.-- bestEnbaleTable():
______________________________________
.sub.-- bestEnbaleTable(
USER /* in, current user */
CURRENT /* in, database name */
SERVER
"Image", /* in, extender name */
"User_A. /* in, fully qualified user
Table.sub.-- A",
table name */
attrTableName /* out, image specific metatable
name */
);
______________________________________
Check authority of USER using current connection. Generate metadata table names: "ImageB"+time() and "ImageA"+time() . Let's say the generated names are "ImageB12345678" and "ImageA12345678." Create 2 metadata tables: mmdbsys.ImageB12345678 and mmdbsys.ImageA12345678. Note that at this step, the Base Layer has enough information to create extender specific columns for mmdbsys.imageA12345678. A new row is added to mmdbsys.MetatableNames:
______________________________________
extender = "Image"
tQualifier = "User.sub.-- A"
tName = "Table.sub.-- A"
basemetatableName = "mmdbsys.ImageB12345678
attrMetatableName = "mmdbsys.ImageA12345678
______________________________________
Inside DBiEnable Column(): Call a base layer function, .sub.-- bestEnableColumno which does (among other things): Syntax for calling .sub.-- bestEnbaleColumn():
______________________________________
.sub.-- bestEnbaleColumn(
USER /* in, current user */
CURRENT SERVER /* in, database name */
"Image", /* in, extender name */
"User.sub.-- A. /* in, fully qualified user
Table.sub.-- A", table name */
"picture", /* column name */
DBI.sub.-- INTERNAL
/* bitmask, storage type =
BLOB */
NULL, /* import action, not
applicable */
NULL /* delete action, not
applicable */
);
______________________________________
Check authority of USER using current connection. A new row is added to mmdbsys.ImageColumns:
______________________________________
tQualifier = "User.sub.-- A"
tName = "Table A"
userColumn = "picture"
basemetatableName = "mmdbsys.ImageB12345678
attrMetatableName = "mmdbsys.ImageA12345678
storageType = DBI.sub.-- INTERNAL
controlSource = NULL
deleteSource = NULL
______________________________________
Create "insert triggers" on User.sub.-- A.Table.sub.-- A. See section "3.10 Trigger Definition" on page 37 for details. Create "update triggers" on User.sub.-- A.Table.sub.-- A. See section "3.10 Trigger Definition" on page 37 for details. Create "delete triggers" on User.sub.-- A.Table.sub.-- A. See section "3.10 Trigger Definition" on page 37 for details. c. Alter Table Later, User.sub.-- A wants to add a mgrPicture column.
______________________________________
EXEC SQL ALTER TABLE Table.sub.-- A
ADD mgrPicture DB2Image;
DBiEnableColumn("Table.sub.-- A",
/* base table name */
"mgrpicture", /* image, column name */
DBI.sub.-- INTERNAL,
/* bitmask, storage type = file
*/
`N`, /* on import, do not change
access rights of the file */
`Y`); /* on delete, remove the source
image file */
______________________________________
Image Extender Does: Inside DBiEnableColumn(), working together with .sub.-- bestENableColumn(): A new row is added to mmdbsys.ImageColumns:
______________________________________
tQualifier = "User.sub.-- A"
tName = "Table.sub.-- A"
userColumn = "picture"
basemetatableName = "mmdbsys.ImageB12345678
attrMetatableName = "mmdbsys.ImageA12345678
storageType = DBI.sub.-- EXTERNAL
controlSource = `N`
deleteSource = `Y`
______________________________________
Create the insert, update, delete triggers on User.sub.-- A.Table.sub.-- A just like those in section "2.3.2 Create Table" on page 21, substituting "mgrPicture" for "picture." d. Insert with VALUES Clause Suppose User.sub.-- A want to add a record for Tri Ha into Table.sub.-- A. User Does:
______________________________________
EXECT SQL INSERT INTO Table.sub.-- A
VALUES("148378", "Tri Q. Ha",
DB2Image(CURRENT SERVER, "hvFILE, "Tri's
picture without touchup");
D B 2 I m a g e ( C U R R E N T S E R V E R,
"/managers/fctung.bmp:, "manager = Frank
Tung")
);
______________________________________
Image Extender Does: Inside the first DB2Image(): Since it is not known what user table or what user column this image is for, a temporary row is added to the ImageBaseImport table. The "insert triggers" will move it to the correct metadata table later. Depending on what version of DB2Image() UDF is called, the storage type for this image can be deduced. A unique, persistent handle is returned which serves as the key to identify this object.
______________________________________
handle=time(): random():
/* sysmmdb is a dummy
CURRENT SERVER: sysmmdb
key */
validFlag = `N`
importTime = CURRENT TIMESTAMP
remarks = "Tri's picture
without touchup"
size = size of the image
storageType = DBI.sub.-- INTERNAL
filename = NULL
content = content of the image
BLOB
______________________________________
Another temporary row is added to the ImageAttrImport table. At this time, automatic processing is done to compute all image specific metadata (number of colors, format, size, thumbnail, etc.). These metadata (along with user marks) can be used later for queries.
______________________________________
handle=time(): random():
/* sysmmdb is a dummy
CURRENT SERVER: sysmmdb
key */
validFlag = `N`
width = width of the image
height, format, ...
thumbnail = thumb nail of the
image
______________________________________
Inside the second DB2Image(): A new row is added to the ImageBaseImport table:
______________________________________
handle=time(): random():
/* sysmmdb is a dummy
CURRENT SERVER: sysmmdb
key */
validFlag = `N`
importTime = CURRENT TIMESTAMP
remarks = "manager = Frank
Tung"
size = size of the image
storageType = DBI.sub.-- INTERNAL
filename =
"/managers/fctung.bmp"
content = NULL
______________________________________
Another new row is added to the ImageAttrImport table:
______________________________________
handle=time(): random():
/* sysmmdb is a dummy
CURRENT SERVER: sysmmdb
key */
validFlag = `N`
width = width of the image
height, format, ...
thumbnail = thumb nail of the
image
______________________________________
Once this is completed, two insert triggers (one for picture, one for mgrPicture) are fired. e. Insert with Full Select Suppose that Table.sub.-- A had been fully populated. User.sub.-- B created a test table, TB.sub.-- test, with the same schema as Table.sub.-- A. He also called DBiEnableTable()) and DBiEnableColumn()). Two metadata tables, mmdbsys.ImageB87654321 and mmdbsys.ImageA87654321 were created for TB.sub.-- test. Now User.sub.-- B wants to fill TB.sub.-- test using data stored in User.sub.-- A.Table.sub.-- A. User Does:
______________________________________
EXEC SQL INSERT INTO TB.sub.-- test
SELECT * FROM User.sub.-- A.Table.sub.-- A;
______________________________________
Image Extender Does: The SQL statement above would cause the "insert triggers" defined on TB.sub.-- test to fire. Note that there are now 2 copies of the same BLOB, one is User.sub.-- A.Table.sub.-- A.picture, another in User.sub.-- B.TB.sub.-- test.picture. Also note that even though User.sub.-- A.Table.sub.-- A.mgrPicture and User.sub.-- B.TB.sub.-- test.mgrPicture point to the same file, the handle value in Table.sub.-- A.mgrPicture is not the same as the handle value in TB.sub.-- test.mgrPicture because the table name is encoded in the handle. f. Select Suppose User.sub.-- B wants to see the thumbnail images of all employees whose pictures were taken sometime in 1994. User Does:
______________________________________
EXEC SQL SELECT name, Thumbnail(picture)
FROM TB.sub.-- test
WHERE Remarks(picture) LIKE `%taken%1995%;
______________________________________
Image Extender Does: For each row of TB-test, the DBMS invokes Remarks(), which returns the user remarks on that image. If any of them match the search pattern in the WHERE clause, DBMS invokes Thumbnail(), which returns bit stream of type LONG VARCHAR (sizes vary from 3K bytes to 9K bytes). Note that the user is still responsible for displaying the thumbnail using whatever operation system supports that he/she has. g. Update Design note Feb. 20, 1995: update comes in 3 flavors:
______________________________________
/* update remarks only*/
Remarks(DB2Image handle,
/* handle value */
LONG VARCHAR *newRemarks
/* replace the old
remarks */
/* update image content only
*/
Content(DB2Image handle,
/* handle value */
BLOB(2G) *imageContent /* replace the old
image */
);
______________________________________
These two cases are treated as get/set object attribute, thus the handle value does not change. The third case involves changing both image content and user remarks, this is treated as creating a new object. The only way a user can do this is by calling DB2Image(). In this case, a new handle value is returned. Suppose User.sub.-- B wants to update a person's ("NAME") picture and its associated remarks. User Does:
______________________________________
EXEC SQL UPDATE TB.sub.-- test
SET picture = DB2Image(:hvFILE, "NAME's touched-
up picture using Adobe Photo Shop")
WHERE name = `NAME`;
______________________________________
Image Extender Does: Inside DB2Image(): A new row is added to mmdbsys.ImageBaseImport:
______________________________________
handle=time( ): random( ):
/* sysmmdb is a dummy
CURRENT SERVER: sysmmdb
key */
validFlag = `N`
importTime = CURRENT TIMESTAMP
remarks = "NAME's touched-up
picture using Adobe Photo
Shop"
size = size of the image
storageType = DBI.sub.-- INTERNAL
filename = NULL
content = content of the image
BLOB
______________________________________
Another new row is added to the mmdsys.ImageAttrImport:
______________________________________
handle=time( ): random( ):
/* sysmmdb is a dummy
CURRENT SERVER: sysmmdb
key */
validFlag = `N`
width = width of the image
height, format, . . .
thumbnail = thumb nail of the
image
______________________________________
The old rows in the metadata tables are not changed. Note that the BLOB or the old metadata rows cannot be deleted because the current SQL statement may be rolled back. However, if a new row is added successfully, the triggers are fired. At this point, the old metadata rows are deleted. The external image files are only deleted by calling DBiReorgMetadata Tables( ). h. Delete User.sub.-- B wants to delete NAME's record from the TB.sub.-- test table. User Does:
______________________________________
EXEC SQL DELETE FROM TB.sub.-- test
WHERE name = `NAME`;
______________________________________
Image Extender Does: The "delete triggers" are fired. B. Metadata Table and Trigger Descriptions This section describes the table definition and table naming convention for all tables created by the extenders. It will also document the naming convention for triggers and handles. Following is a summary of all the tables created and used by the extenders:
______________________________________
Extender Information
one per database
Table
Metadata Information
one per database
Table
Delete Log Table one per extender
Column Information Table
one per extender
Import Base Metadata
one per extender
Table
Import Attribute one per extender
Metadata Table
Base Metadata Table one per user table and
extender
Attribute Metadata Table
one per user table and
extender
______________________________________
1. Multimedia Column (MM Column) A column is called MM Column if it is enabled for a multimedia extender. 2. Extender Information Table (one per database) There is only one such table for each database. It is used to record all the multimedia extenders that this database is enabled for. It also records the UDT associated with the extender. a. Naming Convention MMDBSYS.EXTENDERINFO b. Table Definition
______________________________________
EXTENDERNAME VARCHAR(8) NOT NULL
UDTNAME VARCHAR(18) NOT NULL
______________________________________
3. Metadata Information Table (one per database) There is only one such table for each database. It is used to record the name of base metadata table and attribute metadata table that are associated with a user table which has been enabled for a particular extender. a. Naming Convention
______________________________________
MMDBSYS.METATABLENAMES
______________________________________
b. Table Definition
__________________________________________________________________________
EXTENDER VARCHAR(8)
NOT NULL
TQUALIFIER CHAR(8) NOT NULL
TNAME VARCHAR(18)
NOT NULL
BASEMETATABLENAME
VARCHAR(27)
NOT NULL
/*qualified table
name*/
ATTRMETATABLENAME
VARCHAR(27)
NOT NULL
/*qualifier table
name*/
PRIMARY KEY (EXTENDER, TQUALIFIER, TNAME)
__________________________________________________________________________
4. Delete Log Table (one per extender) This table is to keep track of all the file names associated with MM columns in rows that are deleted. a. Naming Convention
______________________________________
MMDBSYS.xxxDELETELOG
______________________________________
where xxx is the up to 8 character extender name. Example: MMDBSYS.IMAGEDELETELOG is the delete log table name for IMAGE extender. b. Table Definition
______________________________________
HANDLE xxx NOT NULL PRIMARY KEY,
FILENAME LONG VARCHAR
where xxx is the up to 18 char UDT associated
with the extender.
______________________________________
where xxx is the up to 18 char UDT associated with the extender. Example, delete log table definition for Image extender with UDT DB2Image will be
______________________________________
HANDLE DB2Image NOT NULL PRIMARY KEY
FILENAME LONG VARCHAR
______________________________________
5. Column Information Table (one per extender) This table is to record the property of all the MM columns that are enabled for a particular extender. There is one row per MM column. a. Naming Convention
______________________________________
MMDBSYS.xxxCOLUMNS
______________________________________
where xxx is the up to 8 character extender name. For example, column information table name for image extender is MMDBSYS.IMAGEDELETELOG. b. Table Definition
______________________________________
TQUALIFER CHAR(8) NOT
NULL,
TNAME VARCHAR(18)
NOT NULL,
USERCOLUMN VARCHAR(18)
NOT NULL,
BASEMETATABLENAME
VARCHAR(18)
NOT NULL,
ATTRMETATABLENAME
VARCHAR(18)
NOT NULL,
STORAGETYPE CHAR, /*bitmask32:
DB.sub.-- INTERNAL,
DB.sub.-- EXTERNAL*/
CONTROLSOURCE CHAR, /*Y=> on import,
change file owner to
MMDBSYS */
DELETESOURCE CHAR, /*Y=> on delete,
remove the image
file */
PRIMARY KEY (TQUALIFIER; TNAME, USERCOLUMN)
______________________________________
6. Base Metadata Table (one per user table and extender) and Import Base Metadata Table (one per extender) There is one base metadata table for each user table and extender. If a user table has been enabled for image extender and audio extender, it will have 2 base metadata tables (one for image extender and one for audio extender). If a user table has been eneabled for image extender only, it will have only one base metadata table. Base metadata table is used to store information that is common to all extenders such as importer, import time, filename, size, . . . , etc. The base metadata table will have one row for each MM column in a row in the user table. For example, a user table is enabled for image extender and the user table has 10 MM columns. As rows being inserted to the user column, rows are also inserted into base metadata table. After successfully inserting 5 rows to user table, 50 rows will be inserted to the image base metadata table associated with this user table. The MM column value is used as a key to the base metadata table to retrieve all the related metadata. The rows in the base metadata table are inserted by the MM extender's insert trigger created for the MM column. The rows in the base metadata table are updated by the MM column's update UDF. The rows in the base metadata table are deleted by the MM columns' delete trigger. It would be ideal if the rows in the base metadata table can be inserted by the extender import UDF. However, the import UDF knows about database name and extender name but not the user table name. So the import UDF will insert rows to the import base metadata table. Since the after insert trigger know about the user table name, so it will then transfer the rows from import base metadata to the base metadata table associated with the user table. For this reason, the table definition for these 2 tables are identical. a. Naming Convention i. Import Base Metadata Table (one per extender)
______________________________________
MMDBSYS.xxxBASEIMPORT
______________________________________
where xxx is up to 8 character extender name For example, import base metadata table name for IMAGE extender MMDBSYS.IMAGEBASEIMPORT. ii. Base Metadata Table (one per user table and extender)
______________________________________
MMDBSYS.xxxBttt
______________________________________
where xxx is up to 8 character extender name and ttt is 9 character time stamp in seconds since 1980 For example, MMDBSYS.ImageB12345678 is a valid base metadata table name for Image extender. b. Table Definition
__________________________________________________________________________
HANDLE xxx NOT NULL
/* Holds same value as
PRIMARY KEY,
associated MMM */
/* column in a row in the
user table */
VALIDFALG
CHAR NOT NULL,
/* Y=> MM Column was importer
successfully */
IMPORTER
CHAR(8), /* user id by which import is
done */
IMPORTNAME
TIMESTAMP,
UPDATOR CHAR(8), /* userid by which MM colum
is updated */
UPDATETIME
TIMESTAMP,
REMARKS LONG VARCHAR, /* remarks about the MM
column */
SIZE LONG, /* size in bytes about MM
object */
STORAGETYPE
CHAR, /* bitmask32. DB.sub.-- INTERNAL,
DB.sub.-- EXTERNAL */
FILENAME
LONG VARCHAR, /* in reality the max allowed
is 255. Make long */
varchar to gain
performance */
CONTENT BLOB(2G) /* The actual content of the
MM object if */
/*STORAGETYPE is DB.sub.-- INTERNAL
*/
__________________________________________________________________________
For example, xxx will be sent to DB2Audio if this table is defined as the base metadata table for Audio extender with UDT defined as DB2Audio. 7. Attribute Metadata Table (one per user table and extender) and Import Attribute Metadata Table (one per extender) These tables are used to store extender specific information for each MM column. Again there is one row in the attribute metadata table for each MM column in a row in a user table. The MM column value is used as a key to the attribute metadata table to retrieve extender specific metadata. The reason for the existence of the import attribute metadata table is the same as that for import base metadata table. a. Naming Convention i. Import Attribute Metadata Table (one per extender)
______________________________________
MMDBSYS.xxxATTRIMPORT
______________________________________
where xxx is the up to 8 character extender name For example, import attribute metadata table name for IMAGE extender is MMDBSYS.IMAGEATTRIMPORT. ii. Attribute Metadata Table (one per user table and extender)
______________________________________
MMDBSYS.xxxAttt
______________________________________
where xxx is the up to 8 character extender name and ttt is 09 character time stamp in seconds since 1980 For example, MMDBSYS.ImageA123456789 is a valid attribute metadata table name for Image extender. b. Table Definition The minimum requirement for this table is to have the HANDLE column. It's extender developer's responsibility to complete this table definition.
______________________________________
HANDLE xxx NOT NULL PRIMARY KEY
______________________________________
where xxx is the UDT defined for the extender For example, xxx will be set to DB2Video if this table is defined as an attribute metadata table for Video extender with UDT set to DB2Video. 8. HANDLE column in Metadata table The handle in both base and attribute metadata tables holds the same values as the associated MM column in a user table. Most of the extender's UDFs take handle as one of the input argument. The handle values follows the following format so that UDF can take advantage of it. The handle is always a string of length 111 for now even though it's base type is indirectly defined as VARCHAR(254) in the metadata table.
______________________________________
TIME 8 character (# of seconds from
delimitor 1/1/1970)
1 character (:)
RANDONE NUMBER 8 character (blank filled)
delimitor 1 character (:)
DATABASE NAME 8 character (blank filled)
delimitor 1 character (:)
BASE METADATA TABLE NAME
8 character (blank filled)
delimitor 1 character (:)
ATTR METADATA TABLE NAME
8 character (blank filled)
delimitor 1 character (:)
QUALIFIER OF USER TABLE
8 character (blank filled)
delimitor 1 character (:)
USER TABLE NAME 18 character (blank filled)
delimitor 1 char(:)
MMCOLUMN NAME 18 characters
______________________________________
9. Trigger Naming Convention The only requirement is that the all upper case extender name should be the first 8 characters and then follow by a time stamp. The trigger name has extender name encoded so that we can disable a particular extender for a user table by dropping all the triggers associated with that user table and extender. 10. Trigger Definition Triggers are defined for each MM column. All triggers work in the same way regardless of which database, which user table, which column in the table, or even which extender. To make it easier to understand, all triggers in this section are documented using MM column PICTURE in user table "USERID.TABLENAME" within database `DATABASE` for IMAGE extender with UDT set to DB1Image. The base metadata table is `MMDBSYS.IMAGEB123456789` and the attribute metadata table is `MMDBSYS.IMAGEA124356789`. The extender, user table, and MM column dependent information in the trigger body is underlined and in italic style. The .sub.-- bestEnableColumn() API will create these triggers. a. Before Insert Trigger for Insert via Import
______________________________________
CREATE TRIGGER IMAGE123456789A
NO CASCADE BEFORE INSERT ON USERID.TABLENAME
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL
WHEN (NEW.PICTURE IN (SELECT HANDLE FROM
MMDBSYS.IMAGEBASEIMPORT)) BEGIN
NEW.PICTURE=DB2Image (CONCAT (CAST(NEW.PICTURE AS
VARCHAR(254))),
':IMAGEB123456789 :IMAGEA123456789
:USERID :TABLENAME :PICTURE));
END
______________________________________
b. After Insert Trigger for Invert Via Import
______________________________________
CREATE TRIGGER IMAGE123456789B AFTER INSERT ON
USERID.TABLENAME
REFERENCING NEW.sub.-- TABLE AS NEW
FOR EACH STATEMENT MODE DB2SQL
WHEN (EXISTS: (SELECT HANDLE FROM
MMDBSYS.IMAGEASEIMPORT
WHERE HANDLE IN
(SELECT DB2Image(SUBSTR(CAST(PICTURE AS
VARCHAR(254)),
1,26)) FROM NEW)))
BEGIN
UPDATE MMDBSYS.IMAGEBASEIMPORT
SET HANDLE=DB2Image (CONCAT (CAST(HANDLE AS
VARCHAR(254))
':IMAGEB123456789 :IMAGEA123456789
:USERID:TABLENAME:PICTURE),
IMPORTER=USER,
VALIDFALG=`Y`
WHERE HANDLE IN
(SELECT DBImage(SUBSTR(CAST(PICTURE AS VARCHAR(254)),
1,26)) FROM NEW);
UPDATE MMDBSYS.IMAGEATTRIMPORT
SET HANDLE=DBImage (CONCAT (CAST(HANDLE AS
VARCHAR(254)),
':IMAGEB123456789 :IMAGEA123456789
:USERID:TABLENAME:PICTURE),
IMPORTER=USER,
VALIDFALG=`Y`
WHERE HANDLE IN
(SELECT DBImage(SUBSTR(CAST(PICTURE AS
VARCHAR(254)),
1,26)) FROM NEW);
INSERT INTO MMDBSYS.IMAGEB123456789
SELECT * FROM MMDBSYS.IMAGEBASEIMPORT
WHERE (HANDLE IN (SELECT PICTURE FROM NEW));
INSERT INTO MMDBSYS.IMAGEA123456789
SELECT * FROM MMDBSYS.IMAGEBATTRIMPORT
WHERE (HANDLE IN (SELECT PICTURE FROM NEW));
DELETE FROM MMDBSYS.IMAGEBASEIMPORT
WHERE HANDLE IN (SECECT PICTURE FROM NEW);
DELETE FROM MMDBSTS.IMAGEATTRIMPORT
WHERE HANDLE IN (SELECT PICTURE FROM NEW);
END
______________________________________
c. Before Insert Trigger for Insert Via Full/Sub Select
______________________________________
CREATE TRIGGER IMAGE12345789C
NO CASCADE BEFORE INSERT ON USERID.TABLENAME
REFERENCING NEW AS NEW FOR EACH RO MODE DB2SQL
WHEN (NEW.PICTURE NOT IN (SELECT HANDLE FROM
MMDBSYS.IMAGEBASEIMPORT))
BEGIN
NEW.PICTURE=.sub.-- bestCopy (NEW.PICTURE,
`USERID`, `TABLENAME`,
`MMDBSYS.IMAGEB123456789`,
`MMDBSYS.IMAGEA123456789`);
END
______________________________________
d. After Insert Trigger for Insert Via Full/Sub Select
______________________________________
CREATE TRIGGER IMAGE123456789D AFTER INSERT ON
USERID.TABLENAME
REFERENCING NEW.sub.-- TABLE AS NEW
FOR EACH STATEMENT MODE DB2SQL
WHEN (NOT EXISTS (SELECT NEW.PICTURE FROM NEW,
MMDBSYS.IMAGEBASEIMPORT BASEIMPORT
WHERE (NEW.PICTURE=BASEIMPORT.HANDLE)))
BEGIN
UPDATE MMDBSYS.IMAGEB123456789 SET VALIDFLAG=`Y`
WHERE HANDLE IN (SELECT PICTURE FROM NEW);
UPDATE MMDBSYS.IMAGEA123456789 SET VALIDFLAG=`Y`
WHERE HANDLE IN (SELECT PICTURE FROM NEW);
END
______________________________________
e. After Delete Trigger froM MM COlumn with DeleteSource Set to
______________________________________
CREATE TRIGGER IMAGE123456789E AFTER DELETE ON
USERID.TABALENAME
REFERENCING NEW.sub.-- TABLE AS NEW
FOR EACH STATEMENT MODE DB2SQL
BEGIN
DELETE FROM MMDBSYS.IMAGEB123456789 WHERE
HANDLE=OLD.PICTURE;
DELETE FROM MMDDSYS.IMAGEA123456789 WHERE
HANDLE=OLD.HANDLE;
END
______________________________________
f. After Delete Trigger for MM COlumn with DeleteSource set to
__________________________________________________________________________
CREATE TRIGGER IMAGE123456789E AFTER DELETE ON
USERID.TABLENAME
REFERENCING NEW.sub.-- TABLE AS NEW
FOR EACH STATEMENT MODE DB2SQL
BEGIN
INSERT INTO MMDBSYS.IMAGEDELETELOG
SELECT HANDLE, FILENAME FROM MMDBSYS.IMAGEB123456789
WHERE HANDLE IN (SELECT PICTURE FROM OLD);
DELETE FROM MMDBSYS.IMAGEB123456789
WHERE HANDLE IN (SELECT PICTURE FROM OLD);
DELETE FROM MMDBSYS.IMAGEA123456789
WHERE HANDLE IN (SELECT PICTURE FROM OLD);
END
__________________________________________________________________________
C. Data Model MM objects consist of MM BLOBs that are the actual data of the text, image, etc., and MM Attributes that relate to the BLOB, such as "date of creation". MM objects are identified by a unique identifier called a handle, and business tables reference MM Objects through columns that contain these handles. The handle is the actual UDT defined to the database engine as the MM Object data type, and so a separate column in the business tables is required to represent each MM Object type. Non-BLOB MM Attributes are accessed through UDF's only. Some attributes, such as "date of creation", are common to all MM Object types, while other attributes, such as "number of frames", are expected to be dependent upon the particular type of MM Object. Furthermore, some MM Attributes may be system generated, e.g., date of creation, or automatically inferred from the contents of the BLOB or its header, while others will need to be specified when an MM Object is created. MM BLOBs may be stored in the business table in the database, or in a hidden table, or in an external server. The MM BLOBs (regardless of their location) may be accessed in the same manner as other MM Attributes, namely through the handle of the object via UDF's. If this is done, the application can be relatively insensitive to the physical location of the BLOB. BLOBs stored in the business table may also be accessed directly by applications. If this option is chosen, the application will be sensitive to the location of the BLOB. Streaming of MM BLOBs is accomplished by obtaining the access path (an MM Attribute) of an externally stored MM BLOB, and passing this filename to a service such as MMPM/2 that can handle streaming. D. Data Access Functions 1. Constructor
______________________________________
db2xxxxx (Source, option1, option2, . . .)
______________________________________
db2xxxxx() is the constructor function that creates new instances of MM Objects of type xxxxx. There is at least one different constructor function for each MM Object type, for example, db2Image() and db2Audio(). db2xxxxx() returns the value of an object handle of the appropriate type. To accommodate alternative combinations of optional parameters, there may be a family of similarly named, overloaded constructors for each MM data type. Source may be a DB2.TM. LOB type host variable, containing the incoming multimedia data, that may be represented by a LOB type buffer, a client file reference, or a LOB token. The data will be moved to a specified destination. Source may also specify, through a fully qualified path, a reference to a file that is accessible to the multimedia object server. The file will be "adopted" without copying it from its existing location. {Dest} may be an optional indicator of where the BLOB is to be stored, either in the business table, in a hidden table, or an external server. If an external server is indicated then a destination file path may be specified. The BLOB data will be copied from the source to the {dest} (unless the source file is to be "adopted" in its present location). If the parameter is absent, storage will be to a system wide default destination, either to a hidden table, or a directory path on an external server. {Source.sub.-- format} and {Dest.sub.-- format} may optionally be specified. {Source.sub.-- Format} indicates the format of the incoming BLOB. (Note that the source format may be implicitly defined by the BLOB itself). {Dest.sub.-- format} indicates the format of the BLOB as it is to be stored. If the formats are different the BLOB will be converted before it is stored. The extender should tolerate the absence of these parameters, basically by storing BLOBs as-is. {Index} may be an optional parameter(s) that controls indexing actions, if supported by the extender (for example, specification of which index to put this particular object in). System-wide defaults will apply if the parameter is absent. {Attr1, Attr2, . . . } may be a list of optional attribute values for this data type. Absent parameters will cause a suitable default value to be stored. Note that the values of some MM attributes may be generated automatically by the constructor. 2. Content
______________________________________
content (handle)
content (handle, option1, option2, .. )
content( handle, source)
______________________________________
The content() function provides applications with access to MM BLOBs, content(handle) takes a handle and returns a LOB data type, which may be fetched into a LOB host variable (buffer, client file type, or a LOB token). This simple form of the function is overloaded for all multimedia data types. There may be other forms of content() that are specific to particular data types, and include parameters specific to that type (option1, option2, etc.). The content(handle, source) form of the function updates the MM BLOB value of an object represented by handle (and any other attributes that are automatically inferred from the BLOB or its header). It is overloaded for all data types. 3. AccessPath
______________________________________
accessPath (handle)
______________________________________
This function returns a char variable containing the full pathname of BLOBs that are stored in external servers, or table and column names of BLOBs that are stored in the database. It is overloaded for all data types. 4. Attribute Access Functions
______________________________________
attribute.sub.-- name (handle)
attribute.sub.-- name (handle, value)
______________________________________
The attribute.sub.-- name(handle) functions are names for each attribute of the data type, and return the value of that MM attribute. For example, there may be DateofCreation, AttributeOwner, etc. Some functions will be MM Object type dependent (e.g., NumberVideoFrames()), and others will be type independent (e.g., DateofCreation). The latter functions should be overloaded for all data types. The attribute.sub.-- name(handle, value) form of the attribute functions will update the value of the named attribute of an existing MM Object. For example, owner(handle, "Don") changes the attribute "owner" of the object represented by "handle" to "Don". E. Content Search Functions
______________________________________
contains (col.sub.-- name, criteria)
______________________________________
col.sub.-- name identifies the objects to be searched. Criteria are the (type dependent) search criteria. The contains() function is overloaded for all data types. The content of Criteria parameter will differ between types, but is expected to take a common form that will be returned by all the GUI buildpredicatexxxx() functions (see below). This will allow the applications to set up searches on tables containing various data types without needing to use a different form of predicate for each data type. The contains() function returns a value that indicates how well the search criteria have been met. The form of the function will allow its use as a predicate in a WHERE clause, and/or as the subject of an ORDER BY clause to rank returned rows. Value is between 0 and 100.
______________________________________
Usage 1: SELECT Id, contains(text, search.sub.-- criteria)
AS relevance
FROM text.sub.-- table
ORDER BY relevance
Usage 2: SELECT TABLE
FROM text.sub.-- table
WHERE contains(text, search.sub.-- criteria) > 65
______________________________________
Note that search engines may, in addition, provide type dependent content search functions for more specialized forms of search. F. Content Functions The list of Content Functions is data type dependent. Some examples might be: (1) Content manipulation, e.g., rotate(). (2) Network traffic reduction, e.g., compress(). (3) Format transformation, e.g., TIFF.sub.-- to.sub.-- GIF. (4) Other, e.g., encrypt. (Note that format transformation might be a candidate for a content function, as well as being a "built-in" facility of the Import and Export functions described above. There may be other functions, as well as these conversion functions, where there is value in providing the functionality in both forms.) G. Support for SQL Operations All extenders are expected to handle the following forms of SQL operations on a subject business table: 1. Setting Extender data type column to NULL When the Extender data type column value is set to NULL, the data type instance should be deleted, in the sense that it is subsequently inaccessible by applications. Actual physical clean up is implementation dependent. 2. Row delete When a row of the business table is deleted, an extender data type instance in that row should be deleted. 3. Table drop When a table containing Extender data type columns is dropped, all extender data type instances in that table should be deleted. 4. Copies When an Extender data type is copied into another table location: a. A new logical copy of that data type instance should be created, in the sense that the original copy and the new copy will appear to applications as distinct instances, and all subsequent operations (update, delete, etc) to these two instances will be independent. (Whether the data is actually physically copied, and when, is implementation-dependent.) b. The data type instance that was previously held in the copy destination (if any) should be deleted. H. Client GUI Functions GUI "functions" are assumed to be in the form appropriate for the main programming environments on each client platforms. There are two GUI functions that are considered core functions, and should be provided by all extenders. 1. Build a SQL SELECT search predicate expression.
______________________________________
BuildPredicatexxxxx( )
______________________________________
This function will open a suitable basic dialog on the screen and elicit information from the user on the search criteria for the data type xxxxx. It will update a criteria variable that can be embedded in a content search "contains()" function in the WHERE clause of a SQL SELECT statement. The content of the criteria will differ between data types, but the declaration of the criteria should be common for all data types. Note that extenders may, in addition, provide type dependent GUI components that establish specialized dialogs with the user to perform specialized forms of content search. 2. Render the data type.
______________________________________
renderxxxxx()
______________________________________
The renderxxxxx() function will take an object of type xxxxx and render it on an output device in an appropriate way. I. API Usage Scenarios (Note that the syntax in these examples is approximate.)
______________________________________
1. Import
//
// Import some information about an X-ray (date etc), an image of
// the x.sub.-- ray and a textual transcript of a consultant's comments
// about the x-ray.
BEGIN DECLARE
char file.sub.-- var1/830'
END DECLARE
//
// Call a function that returns the name of a client file
// containing the X-ray.
//
call retrieve.sub.-- xray( file.sub.-- var )
//
// The DB2Image constructor returns handles for the text and the
// image and these are inserted into the xray.sub.-- analyses table.
// The image of the X-ray is passed to the constructor using a
// file reference host variable, the image is converted into a TIFF
// image during import(it was originally in PCX format), and a copy
// of it is stored in an external server called server1. The text
// is stored in local file, a copy is made on an external server,
// server2, and it is indexed by default (by the default search
// retrieval engine).
//
INSERT INTO xray-analyses( date, orientation, technician,
consultant, xray, transcript)
VALUES( 3/4/94, "saggital", "B.F.Grey", "C.G.Hardy",
DB2Image. :file.sub.-- var, server1,PCX,TIFF,NOINDEX,5/5/94)
DB2Image( C:\trans29.rtf, server2,,,,6/6/94) );
2. Search
//
// In this scenario, a search is being made for X-rays taken this
// year that contain a certain visible feature, and which are
// associated with a textual transcript that contains the word
// "unidentified".
//
BEGIN DECLARE
char visible features1/8100';
char orient1/810'
date date
char file.sub.-- var1/830+
ptr my.sub.-- buffer
END DECLARE
//
// Get the image search criteria from the user
//
call get.sub.-- image.sub.-- criteria( visible.sub.-- features )
//
// The query finds records in which the date of the X-ray is this year,
// year, there is a high probability (>80%) that the image
// specified by the user is in the X-ray, and the transcript
// contains the word "unidentified". The query results are
// written out to host variables, specifically, the access path
// (filename) of the X-ray image and the text of the transcript are
// returned.
//
DEFINE C1 AS CURSOR FOR
SELECT
orientation, date,
access.sub.-- path( xray ),
content( transcript )
FROM
xray.sub.-- analyses
WHERE
date > 1994 AND
contains( xray, :visible.sub.-- features ) > 80 AND
contains( transcript, "unidentified" ) = 1
//
// Loop used to read each record returned by the query.Orientation,
// date and the X-ray filename are returned into `normal` host
// variables, the transcript is written to a buffer. These are
// then passed to an application routine, display.sub.-- app, for
// selection/display to the user.
//
loop while true
FETCH C1 INTO :orient, :date, :file.sub.-- var, :my.sub.-- buffer
display.sub.-- app( orient, date, file.sub.-- year, my.sub.-- buffer)
end loop
______________________________________
While various embodiments of the present invention have been described above, it should be understood that they have been presented by way of example, and not limitation. Thus the breadth and scope of the present invention should not be limited by any of the above described exemplary embodiments, but should be defined only in accordance with the following claims and their equivalents. It will be understood by those skilled in the art that various changes in form and detail may be made therein without departing from the spirit and scope of the invention.
|
Same subclass Same class Consider this |
||||||||||