System and method for applying user supplied relation definitions to application files for a relational database5504886Abstract Disclosed is a system and method for applying structured query language on user application files not conforming to the definition of a base relation of a relational database management system. A relational database management system has a runtime supervisor and a data manager. For the use of the data manager a plurality of library procedures generalized for manipulation of user application files are provided installed with the database management system. Responsive to user selection, a class for user application files is defined by naming members of a set of the library procedures for use with member application files of the class and by naming the class. Further responsive to user selection, column definitions on an application file within the class are made to create a user defined table. The user defined table corresponds to a specified user application file and to the definition. To create a class of user application files, a class control block is created in which the members of the set of library procedures are named for the class. For the user defined table, a user defined table control block is created in which the column definitions are stored. Both control blocks are accessible to the runtime supervisor for use with an access plan generated from SQL statements. Special structured query language statements are provided for user invocation to define a class of user application files and for providing column definitions, as well as for deleting a class control block and a user defined table control block. Claims What is claimed is: Description BACKGROUND OF THE INVENTION
TABLE 1
______________________________________
struct {
long appl.sub.-- id;
/* application ID */
long proc.sub.-- id;
/* process ID */
int rqst.sub.-- type;
/* request type */
char db.sub.-- pkg 128!;
/* database package name */
int tran.sub.-- id;
/* transaction ID */
int comp.sub.-- id;
/* software component ID */
int func.sub.-- id;
/* function ID */
struct timeval time.sub.-- stamp;
/* time stamp */
int error.sub.-- code;
/* error code */
char tracedata 128!;
/* trace event data */
};
______________________________________
The instructions collectively define the members, or the atomic data, of a record, which corresponds to a tuple in a relational database management system. For a plurality of records, each category of atomic data in the structure of Table 1 corresponds to a column in a base relation. To provide database management over the compound data formed by collections of records collected in accord with such a structure, a user defines a table type, here called "tracedata", followed by definition of a specific table instance to refer to the application file where the data has been collected. Creation of a user defined table type is provided by use of one of four structured query language statements, CREATE TABLETYPE, taught herein. These four statements constitute the only modifications required in the SQL query language. To define a table type the user completes the data structure of FIG. 2 by invoking CREATE TABLETYPE and setting forth a table type name and defining a set of data management functions. The syntax of CREATE TABLETYPE statement is:
______________________________________
CREATE TABLETYPE table-type-name
(dm-func-definition1, dm-func-definition2, . . .
______________________________________
).
The data management function definition, dm-func-definition, has the following format: dm func definition=function type stored procedure name The function type is defined as follows:
______________________________________
CT: create table FR: fetch record
DT: drop table IR: insert record
LT: close table DR: delete record
OT: open table UR: update record
KT: lock table VR: validate record
______________________________________
To create a specific instance of a table a new command CREATE UTABLE is provided. In using CREATE UTABLE the user names the table type to which the new table instance relates and provides a table name for the application file and a data stream name to the application file. The user also specifies column definitions, which allow relational data services 22 to act upon the access plan. The syntax of the CREATE UTABLE statement is as follows:
______________________________________
CREATE UTABLE table-type-name table-name data-stream-
name
(column defintion1, column defintion2, . . . ).
______________________________________
As an example of a user defined table, including the data required to fill an RDS user defined table instance control block 28, a spreadsheet type application file is considered. The spreadsheet type file is given the name "tproj" by the user and a path "D: project dept.sht". Table 2 sets forth a completed SQL statement using the statement is as follows:
TABLE 2
______________________________________
CREATE UTABLE spsheet tproj d:projectdept.shtt
(PROJNO CHAR(6) NOT NULL,.sup.
PROJNAME VARCHAR(24) NOT NULL,.sup.
DEPTNO CHAR(3) NOT NULL,.sup.
RESPEMP CHAR(6) NOT NULL,.sup.
PRSTAFF DEDIMAL(5,2) NOT NULL,.sup.
PRSTDATE DATE ,.sup.
PRENDATE DATE ,.sup.
MAJPROJ CHAR(6) NOT NULL);
______________________________________
Statements are also provided for destroying table instances and table types. The latter functionality is provided by a DROP TABLETYPE statement, which has the following syntax: DROP TABLETYPE table-type-name. Elimination of a tabletype also eliminates all dependent table instances. The last of the new statements is DROP UTABLE, which has the syntax: DROP UTABLE table-name. Use of the DROP UTABLE statement results in invalidating or deleting the control blocks associated with the application file identified by the table name. Once a table type is specified, a user need only identify an application file and define column types and widths using the CREATE UTABLE statement to enable the database management system to manipulate an application file. The database management system then handles application files as if they were base relations. Because application programs may not prevent duplication of records, and the ambiguity attendant the meaning of records resulting from duplication, these application files are not deemed true base relations, but rather user defined tables. The modification required of the SQL language processor is minor, involving the additional syntax defined above for the four new statements. A set of data management functions are provided for manipulation of an application file and the records stored therein. The tagging of these functions provides part of the definition of each user defined table type. The data management functions are stored procedures installed as library 32. Some ten data management function types are installed as stored procedures with the database management system 10 as function library 32 to carry out the functions needed to support the new and existing SQL statements with respect to a user defined table. The stored procedures are intended to be generic to any data format for the sake of general application. The functions are defined by C language statements in Tables 3-12. The procedure CREATE TABLE creates a relational table instance control block.
TABLE 3
__________________________________________________________________________
Function Name:
CREATE TABLE - cretbl
Definition:
int cretbl(char *table.sub.-- name, char *raw.sub.-- data.sub.-- stream.su
b.-- name,long
**dm.sub.-- table.sub.-- handle)
Definition of Parameters:
"Table.sub.-- name" is the user defined table name for the application
file of
interest; "raw.sub.-- data.sub.-- stream.sub.-- name" is a path and
"dm.sub.-- table.sub.-- handle" is
the storage that contains the returned table handle.
__________________________________________________________________________
The procedure DROP TABLE deletes an RMS data table instance control block.
TABLE 4
______________________________________
Function Name:
DROP TABLE - drptbl
Definition:
int drptbl(long *dm.sub.-- table.sub.-- handle)
Definition of Parameters:
"Dm.sub.-- table.sub.-- handle" is the data table
______________________________________
handle.
The procedure OPEN TABLE creates a data manager data table instance control block of the user defined table. The control block is a link to the data table control block. An RMS data table control block can have multiple data access control blocks. The user defined table must be created first before it can be opened.
TABLE 5
______________________________________
Function Name:
OPEN TABLE - opntbl
Definition:
int opntbl(long *dm.sub.-- table.sub.-- handle, long **dm.sub.-- acs.sub.-
- handle)
Definition of Parameters:
"Dm.sub.-- table.sub.-- handle" is the data table handle; and
"dm.sub.-- acs.sub.-- handle" is the storage that contains the returned
data
table access handle.
______________________________________
The procedure CLOSE TABLE closes the data table link and frees the user defined table access control block.
TABLE 6
______________________________________
Function Name:
CLOSE TABLE - clotbl
Definition:
int clotbl(long *dm.sub.-- acs.sub.-- handle)
Definition of Parameters:
"Dm.sub.-- acs.sub.-- handle" is the data table access handle.
______________________________________
The procedure LOCK TABLE is called to guarantee that a data table will be locked in either READ or EXCLUSIVE mode. READ mode allows the other applications to read data form a user defined table only. EXCLUSIVE mode prohibits the other applications from accessing the user defined table.
TABLE 7
______________________________________
Function Name:
LOCK TABLE - lcktbl
Definition:
int lcktbl(long *dm.sub.-- acs.sub.-- handle, char lock.sub.-- intent)
Definition of Parameters:
"Dm.sub.-- acs.sub.-- handle" is the data table access handle; and
"lock.sub.-- intent" is the lock intention. R: READ mode,
X: EXCLUSIVE mode.
______________________________________
The procedure FETCH RECORD fetches a record from a user defined table.
TABLE 8
______________________________________
Function Name:
FETCH RECORD - fchrec
Definition:
int fchrec(long *dm.sub.-- acs.sub.-- handle, long *recid, DM.sub.--
FIELD
*fld.sub.-- data !, int *num.sub.-- fld)
Definition of Parameters:
"Dm.sub.-- acs.sub.-- handle" is the data table access handle; "recid" is
the
storage that contains the record ID; "fld.sub.-- data" is an array of
DM.sub.-- FIELD structures. "DM.sub.-- FIELD" is a data structure
that is defined to store any type of data. Each DM.sub.-- FIELD
structure stores a column value of a record in the "fld.sub.-- data"
array; and "num.sub.-- fld" is the number of entries in the fld.sub.--
data
array.
______________________________________
The procedure INSERT RECORD inserts a record before the input record ID in the data table.
TABLE 9
______________________________________
Function Name:
INSERT RECORD - insrec
Definition:
int insrec(long *dm.sub.-- acs.sub.-- handle, long *recid, DM.sub.--
FIELD
*fld.sub.-- data !, int *num.sub.-- fld)
Definition of Parameters:
"Dm.sub.-- acs.sub.-- handle" is the user defined table access handle;
recid
is the storage that contains the record ID; "fld.sub.-- data" is an
array
of DM.sub.-- FIELD structures. "DM.sub.-- FIELD" is a data structure
that is defined to store any type of data. Each DM.sub.-- FIELD
structure stores a column value of a record in the fld.sub.-- data
array;
and "num.sub.-- fld" is the number of entries in the fld.sub.-- data
array.
______________________________________
The procedure DELETE RECORD deletes a record from a user defined table.
TABLE 10
______________________________________
Function Name:
DELETE RECORD - delrec
Definition:
int delrec(long *dm.sub.-- acs.sub.-- handle, long *recid)
Definition of Parameters:
"Dm.sub.-- acs.sub.-- handle" is the user defined table access handle;
and "recid" is the storage that contains the record ID.
______________________________________
The procedure UPDATE RECORD updates a record in a user defined table with a new record value.
TABLE 11
______________________________________
Function Name:
UPDATE RECORD - updrec
Definition:
int updrec(long *dm.sub.-- acs.sub.-- handle, long *recid, DM.sub.--
FIELD
*fld.sub.-- data !, int *num.sub.-- fld)
Definition of Parameters:
"Dm.sub.-- acs.sub.-- handle" is the user defined table access handle;
"recid"
is the storage that contains the record ID; "fld.sub.-- data" is an
array
of DM.sub.-- FIELD structures. DM.sub.-- FIELD is a data structure that
is defined to store any type of data. Each DM.sub.-- FIELD structure
stores a column value of a record in the fld.sub.-- data array; and
"num.sub.-- fld" is the number of entries in the fld.sub.-- data
______________________________________
array.
The procedure VALIDATE RECORD ID validates a record ID in a raw data table.
TABLE 12
______________________________________
Function Name:
VALIDATE RECORD ID - valrec
Definition:
int valrec(long *dm.sub.-- acs.sub.-- handle, long *recid)
Definition of Parameters:
"Dm.sub.-- acs.sub.-- handle" is the data table access handle; and
"recid" is the storage that contains the record ID.
______________________________________
Definition of a data management function for a table type is done using the following format: dm-func-defintion=function-type stored-procedure-name. All of the slots of a control block 26 for a table type are filled by use of the CREATE TABLETYPE statement with function definitions provided in accord with the above format. With reference now to FIG. 5, there is illustrated a high level flowchart of the method of the present invention for setting up for the manipulation of application files in accordance with the present invention. As illustrated, the process begins at block 70 and thereafter passes to block 72. Block 72 depicts the providing of a set of management functions for manipulating application files. This step shall include the naming of a table type and the naming of each data management function. Next, the process passes to block 74. Block 74 illustrates the defining of a table type which includes an application file on the data management function. Next, the process passes to block 76, block 76 illustrates the creation of a relational table control block in response to a user description of columns of an application file and specification of the table type for an application file and further includes the step of installing the column description's user defined table name and table type name in the table control block. Thereafter, the process passes to block 78 and returns. Referring now to FIG. 6, there is illustrated a high level logic flowchart for the process of manipulating application files in accordance with the method and system of the present invention. As depicted, this process begins at block 80 and thereafter passes to block 82. Block 82 illustrates the processing of a source file for user generated structured query language (SQL) statements. Thereafter, block 84 illustrates a determination of whether or not any of the user generated SQL statements are directed to an application file for which data management functions have been defined. If not, the process returns to block 82 in an iterative fashion. Still referring to block 84, in the event processed SQL statements are directed to such an application file the process passes to block 86. Block 86 illustrates the execution of those SQL statements by applying user definitions of the data management functions for the table type of the application file and the user descriptions of columns for the application file. The process then passes to block 88 and returns. The invention provides a simplified technique for applying database management to user application files without the duplication of data storage files. The functions of the technique are generalized to allow online treatment of data records as received over communication I/O ports. While the invention has been particularly shown and described with reference to a preferred embodiment, 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 |
||||||||||