JP2000029898A - Sql generation system - Google Patents

Abstract

PROBLEM TO BE SOLVED: To generate DML even in the case that the actual body of a database as an object is not present by preserving DML constitution information in a DML constitution information holding area by using DDL constitution information or the like and generating the DML from the respective preserved information of DBMS, DDL constitution and DML constitution. SOLUTION: For the data definition language(DDL) constitution information 101, a user inputs a column name, selects the column attribute of the input column name from DBMS information preserved in a DBMS information holding area 108 and preserves it in a DDL constitution information holding area 109 by a table constitution information registration processing 105. A data manipulation language(DML) generation processing part 114 buries structured query language(SQL) grammar information preserved in the DBMS information holding area 108, the DDL constitution information preserved in the DDL constitution information holding area 109 and the DML constitution information preserved in the DML constitution information holding area 110 in an SQL pattern file 111 for the DBMS to be the object and generates the DML.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

[0001] The present invention relates to the SQ of a database.
L generation.

[0002]

2. Description of the Related Art The function of generating DDL and DML of a database is realized by existing DB design support tools and client server system development tools.
Is generated using the table definition information defined in the schema of the target database. For this reason,
The generation of a DML according to the prior art requires an entity of a target database.

[0003] In addition, it is not uncommon for the definition of tables and indexes to be added or changed depending on the contents of the DML used in a program. The entity of the database is re-created by the procedure of deleting, re-creating and re-executing the DDL.

[0004]

As described above, in the prior art, the DDL of the table definition must be executed on the database before generating the DML to generate the entity of the database. However, optimization of the index definition and the like needs to consider the contents of the DML. For this reason, in order to create an optimal database entity for the DML generated later, the database entity is re-created by deleting the existing database entity, re-creating and re-executing the DDL. That means more work.

In the present invention, DDL configuration information such as table names and column names constituting DDL are stored in an SQL generation system and used for DML generation, so that the existence of a database entity is indispensable when DML is generated. To solve the problem. Also, a DML generated using the DDL configuration information
Is also stored in the SQL generation system as DML configuration information, and the DDL configuration information of the index definition is automatically created and stored from the contents of the search condition of the stored DML configuration information, thereby optimizing the index definition. Improve.

[0006]

Means for Solving the Problems DB such as DBMS grammar
MS information, DDL configuration information described in DDL, and D
By storing the DML configuration information described in the ML in the SQL generation system, the user inputs the DML configuration information using the DDL configuration information having the contents of the table definition instead of the table definition included in the schema of the database. DML configuration information and DBMS input by the user
Since the DML is generated from the information and the DDL configuration information, the DML can be generated in a state separated from the target database. Also, the content of the DML search condition (WHERE clause) is acquired from the DML configuration information, and DDL configuration information of an index definition is created for the corresponding column, thereby enabling generation of the DDL.

[0007]

Embodiments of the present invention will be described below in detail.

FIG. 1 shows an SQL generation system 10 according to the present invention.
4 is a functional configuration diagram of FIG. The repository 112 is a DBMS information holding area 10 for storing grammar information and the like for each DBMS.
8. DDL configuration information holding area 109 for storing information constituting DDL, DML for storing information constituting DML
It comprises a configuration information holding area 110 and a pattern file 111 serving as a template for SQL and programs. S
The QL pattern file 111 is prepared for each DBMS, and the program pattern file 111 is prepared for each language.

The DDL configuration information 101 of the table definition is
The user inputs a table name or a column name, selects a column attribute of the input column name from the DBMS information stored in the DBMS information storage area 108, and stores it in the DDL configuration information storage area 109 in the table configuration information registration processing 105. I do. The DML configuration information 102 is input by the user by a method of selecting the DDL configuration information of the table definition already stored in the DDL configuration information storage area 109 and the SQL grammar information stored in the DBMS information storage area 108.
In the ML configuration information registration processing 106, the data is stored in the DML configuration information holding area 110. The DDL configuration information 103 of the index definition is stored in a DML configuration information search area (SQL WHERE of SQL) stored in the DML configuration information holding area 110.
Phrase) and the SQL grammar information stored in the DBMS information storage area 108, automatically created by the SQL generation system, and stored in the DDL configuration information storage area 109 by the index configuration information registration processing 107.

[0010] The DDL generation processing 113 includes the SQL grammar information stored in the DBMS information holding area 108 and the DD grammar information.
The DDL configuration information stored in the L configuration information holding area 109 is embedded in the SQL pattern file 111 for the target DBMS to generate a DDL, and output as the SQL file 115. Also, DML generation processing 114
Is the SQ stored in the DBMS information holding area 108
The L grammar information, the DDL configuration information stored in the DDL configuration information storage area 109, and the DML configuration information stored in the DML configuration information storage area 110 are embedded in the SQL pattern file 111 for the target DBMS. The DML is generated, embedded in the pattern file 111 of the target language program, and output as the program source 116.

FIG. 4 is a data configuration diagram of the DDL configuration information holding area 109. The DDL configuration information holding area 109 is 5
It consists of two tables. The table DB 401 stores information on a database. A table TABLE 402 stores a list of tables to be created in the database, and information on columns of each table is stored in a table TABLE_COL.
Save with UMN403. Index information created for the table is stored in a table TABLE_INDEX 404 and a table TABLE_INDEX_COLUMN 405. In the table configuration information registration processing 105, the table TABL
DDL in E402 and table TABLE_COLUMN403
Save configuration information.

FIG. 5 is a data configuration diagram of the DML configuration information holding area 110. DML configuration information holding area 110 is 5
It consists of two tables. The table SQL501 stores a list of SQL. The table SQL_TABLE 502 indicates that each SQ
Save the target table of L, the target column is the table SQL_C
Save with OLUMN 503. The content of the search condition of each SQL is stored in a table SQL_WHERE 504, and the content of the sort condition is stored in a table SQL_ORDER 505.

FIG. 2 is a flowchart showing DML configuration information registration processing 106 using DDL configuration information.
The table definition 402 and the table TABLE_COLUMN 403 in the DDL configuration information holding area 109 store the DDL configuration information of the table definition. The DBMS information storage area 108 stores SQL grammar rules for each DBMS. The flowchart shows a process of registering DML configuration information in which data having a specific department code is retrieved from an employee table, and employee numbers and names are sorted and displayed in ascending order of employee numbers.

The user inputs DML configuration information in the order of target table selection 201, target column selection 202, search condition column selection 203, and sort column selection 204. Table TABL of DDL configuration information holding area 109
An employee table to be displayed is selected 201 from E402, and an employee number column and a name column to be displayed are selected 202 from columns linked to the employee table in the table TABLE_COLUMN403.

Next, the table TABLE_COLUMN 403
A section code column serving as a search condition is selected from the columns linked to the employee table in the table 203, and relational operators and logical operators of the selected columns are selected from the SQL grammar information stored in the DBMS information holding area 108. Selection 203
I do. Then, an employee number column to be used as a sort condition is selected 204 from the employee number column and the name column selected in the target column selection 202, and the sort condition of the employee number column is stored in the SQL information stored in the DBMS information holding area 108.
Select 204 from the grammar information.

The DM input by the user in the above procedure
The L configuration information is obtained by the DML configuration information registration process 205.
Table SQL501, table SQL_TABLE502, table SQL_COLUMN5 in the DML configuration information holding area 110
03, table SQL_WHERE 504, table SQL_ORD
Stored in ER505.

FIG. 3 shows an index configuration information registration process 10.
7 is a flowchart illustrating a process of automatically registering index configuration information for a column selected by a DML search condition. DML configuration information holding area 11
0 from the table SQL_WHERE 504, DB_I of the search condition column stored in the DML configuration information registration processing 106.
Get D, TABLE_ID and COLUMN_ID 3
01. Next, the DBMS of the DBMS information holding area 108
From the SQL grammar information of S, a code representing a sort condition necessary for creating an index and a code representing a type (unique / duplicate) of an index are acquired 302.

DB_ID and TAB obtained by the above procedure
LE_ID, COLUMN_ID, INDEX_ID and INDEX automatically assigned by the SQL generation system 104
_NAME, and sort conditions and index types, which are SQL grammar information, are stored in the table T of the DDL configuration information holding area 109.
ABLE_INDEX404 and table TABLE_INDE
Store in X_COLUMN 405. The sort condition is in ascending order, and the index type is duplicated because the column selected by the search condition may be other than the primary key.

In the index configuration information registration processing 107, D
When the DDL generation processing 113 is performed using the DDL configuration information automatically saved in the DL configuration information holding area 109,
A DDL of an index definition for the search condition column is generated.

[0020]

According to the present invention, it is possible to generate a DML even when the target database does not exist. In addition, by automatically generating the DDL of the index definition for the column specified by the DML search condition, the optimization of the index definition can be efficiently performed.

[Brief description of the drawings]

FIG. 1 is a configuration diagram showing functions of an SQL generation system according to the present invention.

FIG. 2 is a flowchart of DML configuration information registration processing using DDL configuration information.

FIG. 3 is a flowchart of index configuration information registration processing for a search condition column.

FIG. 4 is a data configuration diagram of a DDL configuration information holding area.

FIG. 5 is a data configuration diagram of a DML configuration information holding area.

[Explanation of symbols]

101 ... DDL configuration information (table definition), 102 ...
DML configuration information, 103: DDL configuration information (index definition), 104: SQL generation system, 105
... Table configuration information registration processing, 106 ... DML configuration information registration processing, 107 ... Index configuration information registration processing, 1
08: DBMS information holding area, 109: DDL configuration information holding area, 110: DML configuration information holding area, 1
11 ... pattern file, 112 ... repository,
113: DDL generation processing, 114: DML generation processing, 115: SQL file, 116: program source, 201: selection of target table,
202: Selection of target column, 203: Selection of search condition column, 204: Selection of sort column, 205: DM
L configuration information registration, 301 ... acquisition of search condition columns,
302: acquisition of SQL grammar information; 303: registration of index configuration information; 401: table DB;
402: Table TABLE, 403: Table TABLE_CO
LUMN, 404 ... Table TABLE_INDEX, 405
… Table TABLE_INDEX_COLUMN, 501…
Table SQL, 502 ... Table SQL_TAB
LE, 503: table SQL_COLUMN, 504: table SQL_WHERE, 505: table SQL_ORDER.

Claims (2)

[Claims] 1. A database (SQL: Structure: SQL)
ed Query Language A DBMS information holding area for storing grammar information and the like of structured query language, and a DDL (DDL: Data Def) for defining tables and indexes of the database.
DDL that constitutes inition Language (Data Definition Language)
DDL configuration information storage area for storing configuration information, and D for operating (searching, adding, deleting, and updating) table data
It has a DML configuration information storage area for storing DML configuration information that configures ML (Data Manipulation Language), and DDL configuration information and D that configure a table definition stored in the DDL configuration information storage area.
Using the grammar information stored in the BMS information storage area, the DML configuration information input by the user is stored in the DML configuration information storage area, and the stored DBMS information, DD
An SQL generation system for generating a DML from L configuration information and DML configuration information. 2. The DML configuration information according to claim 1, wherein the DML configuration information of the index definition for the column specified in the SQL search condition (WHERE clause) is stored in the DML configuration information holding area. An SQL generation system, which is created from information of a search condition (WHERE clause) of configuration information and grammatical information of a DBMS information storage area, and stores the generated information in a DDL configuration information storage area.