Database definition language generator5732262Abstract A database definition language generator which can read input from database design document which exists in a structured file format and using this information can generate the database definition language for a specified database management system. Claims We claim: Description BACKGROUND OF THE INVENTION
______________________________________
:tcap.Actions Table
:tdesc.OMIN.ACTIONS
:etdesc.
:thd.
:c.Column Name
:c.Size
:c.Default
:c.Type
:c.Comments
:ethd.
:row.
:c.DATE.sub.-- OPENED
:c.4
:c.Required
:c.DATE
:c.
:row.
:c.ACTION.sub.-- #
:c.2
:c.Required
:c.SMALLINT
:c.01 to 99
.
.
:row.
:C.
:tnote text=' '.
:p.
Length = 3874
:p.Indexes
:ol.
:li.DATE.sub.-- OPENED + ACTION.sub.-- # + SOURCE.sub.-- BOARD (Unique)
:eol.
:etnote.
:etable.
:dl tsize=29
:dthd.Column Name
:ddhd.Element Description
:dt.DATE.sub.-- OPENED
:dd.
Date the action item is opened
:edl.
______________________________________
As shown, each tag (lines which start with a colon) represents input to be used in the generation of the DDL. For example, on line 2, :tdesc.OMIN.ACTIONS is the authorization id and name of the table, on line 12, :c. DATE.sub.-- OPENED is a column of the table and on line 13, :c.4 indicates that the column is four (4) bytes. The present invention provides a means for reading a structured file similar to the one illustrated above in its entirety and generating the DDL code for all of the objects specified. There is no limit on the size of the input document file. The example input is only a sample of the types of input which can be accepted by the generator program. Other configurations of formatted data can be used; however, it is required that the data be in a structured format so that the program can recognize the different parameters and objects which are necessary to create the DDL code. With reference back to FIG. 2, at least three other types of optional input information can be input into the data generator. If this information is not provided, either the default values specified by the user or those of the DBMS are used in the generation of the DDL code. The program can be easily modified to default to other criteria, as desired. The user can optionally specify default parameters for tablespaces, tables and indexes 52. Some of the default parameters which the user can enter for tablespaces or tables include: CLOSE, FREEPAGE, PCTFREE, LOCKSIZE, INITIAL, NEXT, and MINEXTENTS. In addition, the user can specify the following default parameters for indexes: CLOSE, FREEPAGE, PCTFREE, LOCKSIZE, CLUSTER, INITIAL, NEXT, and MAXEXTENTS. If necessary, the program will automatically generate DBMS parameters, such as PCTFREE, based on table statistics. If the user does not supply values for these variables, the standard defaults for the DBMS specified will be Used in the generation of the DDL code. The user can also choose whether standard DBMS storage groups or user defined datasets will be used by the DDL generator. If no specification is made, the program uses a generic profile of IDCAMS parameters. In the case of the DB2.RTM. DBMS, the user can specify whether to use the DB2.RTM. Storage Groups or User Defined DB2.RTM. datasets which use IDCAMS. Finally, the user has the option to enter the database and tablespace name for the application. If more than one tablespace exists in the application, the program will use the same tablespace name for each table object if no tablespace name is supplied with the table name. If the Oracle DBMS is used, the user will also have the option of entering a tablespace name for indexes as well as one for tablespaces. If no entry is made, the program will automatically generate a generic name for both the index and the tablespace name. The database definition language program generator of the present invention processes the structured file input, as described supra, according to the data type of each record and then generates the appropriate DDL code for the DBMS specified. The DDL program generator can generate most of the DDL statements necessary for an application, as shown in FIG. 2. These DDL statements define and describe the structure of the database application. An example of the DDL statements which would be generated to create a table for use with a DB2.RTM. database is shown below.
______________________________________
PROGRAM DDL CREATES FROM DB2
______________________________________
CREATE TABLE OMIN.ACTIONS
(
DATE.sub.-- OPENED
DATE NOT NULL ,
ACTION.sub.-- #
SMALLINT NOT NULL ,
SOURCE.sub.-- BOARD
CHAR(8 ) NOT NULL ,
ACTIONEE CHAR(20 ) NOT NULL WITH ,
DEFAULT
ORGANIZATION
CHAR(6 ) NOT NULL WITH ,
DEFAULT
CO.sub.-- ACTIONEE
CHAR(55 ) NOT NULL WITH ,
DEFAULT
STATUS CHAR(6 ) NOT NULL ,
DUE.sub.-- DATE
DATE ,
DATE.sub.-- CLOSED
DATE ,
CLOSURE.sub.-- RATIONALE VARCHAR(1800 ) NOT NULL WITH
DEFAULT
) IN OMINDOMN.
______________________________________
The above example shows the DDL statements for the creation of a table, OMIN.ACTIONS which has ten data fields. The names, format and requirements for each field are also specified. In addition to the information shown, the DDL program generator can produce other DDL statements as necessary, including those for referential integrity, referential integrity exception tables, tablespaces, indexes, and comments. A further example of the DDL statements which can be generated using the present invention is shown below. This example would be for an application using the Oracle.RTM. DBMS.
______________________________________
PROGRAM DDL CREATES FROM ORACLE
______________________________________
Table create statements
CREATE TABLE OMIN.ACTIONS
(
DATE.sub.-- OPENED
DATE NOT NULL ,
ACTION.sub.-- #
SMALLINT NOT NULL ,
SOURCE.sub.-- BOARD
CHAR(8 ) NOT NULL ,
ACTIONEE CHAR(20 ) ,
ORGANIZATION CHAR(6 ) ,
CO.sub.-- ACTIONEE
CHAR(55 ) ,
STATUS CHAR(6 ) NOT NULL ,
DUE.sub.-- DATE
DATE ,
DATE.sub.-- CLOSED
DATE ,
CLOSURE.sub.-- RATIONALE
VARCHAR
TABLESPACE
STORAGE (INITIAL NEXT
MINEXTENTS 1 MAXEXTENTS 99
PCTINCREASE 0)
PCTFREE
PCTUSED ;
Index create statements
CREATE UNIQUE INDEX
IDX.sub.-- ON OMIN.ACTIONS
DATE.sub.-- OPENED
,
ACTION.sub.-- # ,
SOURCE.sub.-- BOARD
)
TABLESPACE
STORAGE (INITIAL NEXT
MINEXTENTS 1 MAXEXTENTS 99
PCTINCREASE 0)
PCTFREE
NOSORT;
Synonym create statements
CREATE SYNONYM ACTIONS FOR OMIN.ACTIONS;
Grant statements
GRANT SELECT, INSERT, UPDATE, DELETE ON
OMIN.ACTIONS TO PUBLIC;
______________________________________
As shown, the DDL code has been generated for the creation of a table, OMIN.ACTIONS which has ten database fields. This is similar to the example shown previously using the DB2.RTM. DBMS. This example also shows the DDL code which would be generated to create indexed fields for the table. As illustrated, indexes would be created for the fields DATE.sub.-- OPENED, ACTION.sub.-- # and SOURCE.sub.-- BOARD. There are also shown DDL statements which would create a synonym of ACTIONS for the table OMIN.ACTIONS and would grant the ability to perform specified actions on the table to all users (PUBLIC). FIG. 3 is a flow diagram which details the program logic for the present invention. At block 100, the input file(s) and the output file(s) are opened. The input file contains the database design information and the output file will be written with the DDL code for the application. The DDL program generator then reads, from the input file, and stores, in variables, the default parameters which are to be used in instances where the information is not specified 110. Then, at block 120, the DDL data structures are initialized and the variables are reset. The program then reads the next data record in the source file 130. At block 140, the DDL program generator determines whether the end of the file has been reached. If so, the process terminates. If not, processing continues. The data line is then interpreted to determine if the data is a part of the DDL or if it is surrounding text which is to be ignored 150. If the data line does not contribute to the DDL, the DDL program generator returns to block 130 and reads the next line in the file. Otherwise, at block 160, it is determined whether the data type for the data read from the input file is supported by the DDL. The pseudocode for a DB2.RTM. database application for the evaluation of the data type is shown below.
______________________________________
Procedure : Main
.
.
if not end of file then
when (storage group)
invoke read storage group DDL
write create storage group statement to
DDL file
invoke reset storage group DDL
when (database)
invoke read database DDL
write create database statement to DDL
file
invoke reset database DDL
when (tablespace)
invoke read tablespace DDL
write create tablespace statement to DDL
file
invoke reset tablespace DDL
when (table) invoke read table DDL
write create table statement to DDL file
write create index statements to DDL file
write comment on statements to DDL file
write create synonym statements to DDL
file
write create alias statements to DDL file
write grant statements to DDL file
invoke reset table DDL
when (others) issue message -- invalid DDL data type
end if
end main;
______________________________________
As can be seen, other data types can be easily defined depending on the DBMS in use. As shown, an invalid data type causes a message to be issued to notify the user and the program returns to block 130 to read the next data record. On the other hand, if the data type is found for the DDL, then, as shown above, a determination of the data type is made at block 170. Each data type invokes different procedures which read the records and generate the necessary DDL statements. If the data type is table, therefore specifying the creation of a table, then as indicated in block 180, the program will read and write all of the information for the creation of the table until the end of the table information is reached. In addition to creating the columns, other table information is specified including, for example, the primary key, referential integrity, and free space parameters. The following pseudo code provides an illustration of the procedure used to read the table information from the input file and generate the appropriate DDL statements.
______________________________________
Procedure : Read Table DDL
if not end of table information
when (table name)
store table name
if tablespace name also provided then
store tablespace name
else
store default tablespace name
end if;
store synonym name
store alias name
store grant table name
when (column name)
allocate node in list
adjust column pointers
store column name
when (data type)
store column data type
when (length) store column length
when (required/default)
store column nullable indicator
when (fieldproc)
store fieldproc
when (fp parm list)
compress blanks from parameterlist
store field proc parameter list
when (primary key)
validate key list
.cndot. column defined in table
.cndot. no duplicates
.cndot. defined as not null or not null
.sup. with default
.cndot. max 64 columns
.cndot. max combined length 254
allocate node in list
adjust pointers
store primary key column list
when (unique) validate unique column list
.cndot. column defined in table
.cndot. no duplicates
.cndot. defined as not null or not null
.sup. with default
.cndot. max 64 columns
.cndot. max combined length 254
allocate node in list
adjust pointers
store unique column list
when (comment) store table comment
when (ref integrity)
allocate node in list
adjust pointers
store referential integrity constraint
set referential integrity flag
when (index) store index name
store index columns and
ascending/descending index in list
store index parameters
when (end index)
if free space parameters not provided
compute index length
compute free space
end if
when (end table)
if free space parameters not provided
compute table length
compute free space
end if
invoke Reset Table DDL Variables;
when (others) issue message -- invalid DDL data type
end Read Table DDL Variables;
______________________________________
The code provided above illustrates the logic of the program which has been implemented using the terminology for DB2.RTM. DBMS. It is understood that this program could be modified for other DBMSs. This code shows that for each type of statement specified in the structured data input file for the creation of a table, the information to generate one or more specified DDL statements is stored. Referring back to FIG. 3, after the table information has been read and stored and for data types other than table, at block 190, the create statements for the DDL data type are written to the output file. At this point, the data structures are initialized for the corresponding DDL data type 200. Finally, the control returns to block 130, where another line of the file is read from the input file. The DDL program generator provides a tool which enables a user with minimal knowledge of the DBMS language and required syntax to be able to implement the design of a database. In addition, since the input to the program is from a design document as input, individuals involved in the design who may not be familiar with the DBMS language would be able to review and easily comprehend the actual design of the database as it will be implemented. Moreover, the DDL program generator minimizes the work required by the database administrator when subsequent changes or corrections are made to the design document. Finally, database administrators can use the output generated to learn the language and syntax for a new DBMS. While the invention has been described in terms of a single preferred embodiment, those skilled in the art will recognize that the invention can be practiced with modification within the spirit and scope of the appended claims.
|
Same subclass Same class Consider this |
||||||||||