JP4112282B2 - Relational database construction apparatus and method - Google Patents

Description

[0001]
【Technical field】
The present invention relates to a script file generating apparatus for database construction, more specifically, an apparatus and method for generating a script file for constructing a database used in a database management system, and a generated script file. The present invention relates to an apparatus and method for constructing a database by executing
[0002]
BACKGROUND OF THE INVENTION
The database used in the database management system (database management system: DBMS) defines the data capacity of the database, the types of tables stored in the database, the items stored in each table, the record length for each item, etc. A script file is created based on the database design data, and is built based on the created script file.
[0003]
Database design data and script files are managed as separate files. For this reason, if the database design data is changed, the script file must also be changed. If the database design data is changed frequently, the modification work on the script file may be delayed or the script file may be changed with incorrect contents.
[0004]
DISCLOSURE OF THE INVENTION
An object of the present invention is to automatically generate a script file used for database construction from database design data.
[0005]
It is another object of the present invention to provide a database construction apparatus and method for constructing a database by executing automatically generated script files.
[0006]
Another object of the present invention is to make reference constraints unquestioned when creating a database, even if the database to be created includes data having referential constraints.
[0007]
The script file generation device according to the present invention includes at least a database size of a database to be created, a table name and a table size for each table constituting the database, and an item name for each item included in the table. Database design data including data size and item size, script creation macro including template data in which script data for constructing the database is configured by embedding predetermined data, and data included in the database design data Is written at a predetermined position of the template data in the script creation macro, and the script creation macro for database construction is executed by executing the script creation macro in which the data is described. And a script file generation means for generating yl.
[0008]
The script file generation method according to the present invention includes at least a database size of a database to be created, a table name and a table size for each table constituting the database, and an item name for each item included in the table. The database design data including the item size is input, and the predetermined data is embedded in the script creation macro including the template data in which the script file for constructing the database is embedded. Data included in the database design data is described, and a script file for database construction is generated by executing a script creation macro in which the data is described.
[0009]
According to the present invention, template data is prepared, and a script file is generated by describing (inserting) data in the database design data at a predetermined position of the template data. The generated script file reflects the contents of the database design data. By executing the generated script file, the database as described in the database design data is constructed.
[0010]
According to the present invention, even if a change occurs in the database design data, a script file reflecting the change is generated. Therefore, the database created by executing the script file is the database design data. It will be along. Database design data changes are reflected in the database without omission. The database design data changes can be reliably reflected in the database.
[0011]
In one embodiment, the database design data includes constraint data, and further includes check means for checking whether there is a logical error in the constructed database based on the constraint data.
[0012]
In general, in a database used in a database management system, when a certain data A refers to another data B, there is a constraint that ensures that the data B exists. When a certain data A refers to another data B, it is necessary to register the data A after registering the data B. According to the present invention, after the database is constructed, it is checked whether there is a logical error (in the above case, data B does not exist even though there is data A) for the constructed database. There is no need to consider that data B needs to be registered first when the database is constructed.
[0013]
[Explanation of Examples]
In a computer system equipped with a CPU, memory, hard disk, input device, output device, etc., the computer system functions as a database management system (database management system) by storing the database and database management program on the hard disk Can be made. In a database management system, even if multiple users update or search the database at the same time, they can manage it efficiently, permit or restrict access to important data stored in the database for each user, This is a system that realizes, for example, restoring the contents of a database to the state immediately before the failure when some kind of failure occurs.
[0014]
An apparatus (database construction apparatus) described in this embodiment is an apparatus that constructs a database used in the above-described database management system.
[0015]
FIG. 1 is a block diagram showing a hardware configuration of an apparatus (database construction apparatus) that constructs a database used in a database management system.
[0016]
The database construction device 1 is constituted by a computer system including a CPU 2, an input device 3, a display device 4, a communication device 5, a memory 6, an FD drive 7 and a hard disk 8. The hard disk 8 stores a database design data macro (program), an initial registration data macro (program), a production transfer data macro (program), a script batch execution program, and a data consistency check program.
[0017]
As will be described later, database design data, initial registration data, and production transfer data are input to the database construction device 1 through the input device 3, the communication device 5, and the like. From these data, a script file (file in which a script is described) is created. The created script file is executed, that is, the script described in the script file is executed by the CPU 2, whereby a database used in the database management system is constructed.
[0018]
FIG. 2 shows three types of data input to the database construction device 1 and these data as input data, and the macro processing (the above-mentioned database design data macro, initial registration data macro, and actual transfer) The type of script file that is output when the processing by the data macro) is performed.
[0019]
From the database design data, a table creation script file, a privilege design script file, a primary key setting script file, a trigger creation script file, and a constraint condition setting script file are created.
[0020]
From the initial registration data, an initial data (initial registration) script file is created.
[0021]
Initial data (production migration) script file is created from production migration data.
[0022]
These script files do not necessarily mean a single file. Some types of script files are created for each table or input data included in the database created by the database construction device 1. Details of these data and script files will be described later.
[0023]
The above script files except for the constraint condition setting script file are collectively (all) executed by the script file batch execution program. A database is constructed on the hard disk 8 by executing the script file. The constraint condition setting script file is executed by the data integrity check program.
[0024]
First, three types of data (database design data, initial registration data, and production transfer data) input to the database construction device 1 will be described.
[0025]
(1) Database design data
3 to 6 show an example of database design data. The database design data defines the basic structure (database structure) (number of bytes, access authority, etc.) of the database itself to be used (created) in the database management system (FIGS. 3 and 4), And a table (FIGS. 5 and 6) for defining a structure (table structure) for various tables constituting the database (data (actual data) is stored in this table). When a plurality of tables are created in the database, a plurality of database design data defining the table structure is created.
[0026]
FIG. 3 shows design data (database size design data) that defines the number of bytes of the database to be created (the size of the area that occupies the hard disk) (the size of the database). Has "Average" and "Maximum" items.
[0027]
The “average” column indicates the size that is expected to be required on average while the database management system that uses the database to be created is in operation. Similarly, the “Maximum” column indicates the size that is expected to be the maximum required during operation of the database management system.
[0028]
FIG. 4 shows design data (authority setting data) that defines the authority to use the created database.
[0029]
The authority setting data includes a “user name” field and an “authority” field. In the “user name” column, the user name of the user (database management system user) to whom the authority content is to be set is described. The authority content is described in the “authority” column. The example of FIG. 4 is a designation that all privileges are granted to the “public” user.
[0030]
FIG. 5 is an example of design data (table design data) that defines attributes of various tables stored in the database. FIG. 5 shows table design data of a table (bank table) for managing bank information.
[0031]
The table design data defines the table name, the number of records (average and maximum), the number of bytes in the table (average and maximum), and the number of bytes in one record.
[0032]
A table name is described in the “table name” column. The average of the “number of records” column describes the average number of records expected while the database management system using the database is operating. The maximum value of the expected number of records is described in the maximum of the “number of records” column.
[0033]
The average number of bytes expected in this table is described in the average of the “number of bytes” column, and the maximum number of bytes required in this table is described in the maximum of the “number of bytes” column. One record in the “number of bytes” column describes the number of bytes required for one record of this table.
[0034]
The total number of bytes (average) for all tables matches the number of bytes in the average column of the database size design data shown in FIG. The total number of bytes (maximum) for all tables matches the number of bytes in the maximum column of the database size design data shown in FIG.
[0035]
FIG. 6 is an example of design data (item design data) of items included in various tables stored in the database. FIG. 6 shows item design data for each item included in the bank table shown in FIG.
[0036]
Item design data includes data on item name, data type, integer byte count, decimal byte count, attribute, primary key, index, availability of zero data, item description, and constraint conditions.
[0037]
“Item name” is the name of each item.
[0038]
“Data type” is the type of data of each item. For example, char means the data type of fixed-length character string.
[0039]
“Integer byte count” is the number of digits in the integer part when the above data type is an exact data type (varchar). If the data type is not an exact data type or a character string type, enter the required number of bytes. For string types, this number of bytes means the number of characters that can be recorded.
[0040]
"Number of decimal bytes" is the number of digits in the decimal part when the data type is an exact data type. If the data type is not a true data type, 0 is described.
[0041]
In the “attribute” column, the type of data (half-width, full-width, etc.) is described.
[0042]
In the “primary key” column, items to be adopted as the primary key are shown. ○ mark or ◎ mark is shown. A circle indicates a non-clustered primary key. ◎ means clustered primary key.
[0043]
In the “index” column, items to be adopted as indexes are shown. ○ mark or ◎ mark is shown. A circle indicates a non-clustered index, and a circle indicates a clustered index.
[0044]
There can be only one clustered primary key or index per table. If there are two or more, one of them must be non-clustered (replace ◎ mark with ○ mark). There may be a table without a clustered primary key and a clustered index.
[0045]
Whether or not to allow a 0 (zero) value is set in the “zero enable / disable” column. Here, “No” is described when 0 is not allowed, and “Yes” is described when 0 is allowed.
[0046]
In the “item description” column, an item description is described.
[0047]
In the “constraint condition” column, the constraint condition for the item is described. The constraint conditions will be described later.
[0048]
(2) Initial registration data
FIG. 7 shows an example of the initial registration data.
[0049]
Initial registration data is created for each table included in the database to be created. The initial registration data includes a table name column, and the table name defined in the above-described database design data is described in this table name column. This clearly indicates which table is the initial registration data.
[0050]
The initial registration data is specific data for each item designed in the item design data (FIG. 6) created for each table. As mentioned above, when it is designed to provide the bank code, bank kanji, bank name kana, remarks column, update date and update program items in the bank table, the initial registration data of this bank table includes Specific data for each of these items is stored.
[0051]
(3) Production transfer data
It is basically the same as the initial registration data. The difference between the initial registration data and the production transfer data is as follows.
[0052]
Initial registration data is data that describes the minimum items necessary for the operation of a database management system that uses a database to be created. For this reason, in the initial registration data, codes and the like essential for the operation of the database management system are described. On the other hand, the actual migration data is data inherited from the old database management system. For a completely new database management system where there is no old database management system, production migration data is not always necessary.
[0053]
As described above, the database design data, the initial registration data, and the production transfer data are processed by the macros for each of these data, thereby creating various script files.
[0054]
FIG. 8 is a flowchart showing the flow of processing of a program for creating various script files. This program is a program (macro sequential execution program) for sequentially executing the above-described macros, that is, the database design data macro, the initial registration data macro, and the production transfer data macro.
[0055]
The user prepares database design data, initial registration data, and production transfer data. These data are stored in the hard disk 8 of the database construction device 1. Of course, you may use what was memorize | stored in the hard disk of the other computer connected to the database construction apparatus 1 by networks (LAN, WAN, etc.).
[0056]
A user of the database construction device 1 starts a script file creation program. First, a database design data macro (program) is executed (step 21).
[0057]
Database design data is read. For example, the address where the database design data for the database to be created is stored is designated by the user of the database construction device 1. When an address is designated, the database design data stored at the designated address is read into the memory 6 of the database construction device 1. The database design data read into the memory 6 is targeted for processing, and processing using the database design data macro is started (step 22).
[0058]
As described above, the macro for database design data is based on the database design data, the table creation script file, permission setting script file, primary key setting script file, trigger setting script file, and constraint condition setting script file. Create
[0059]
The macro for database design data is programmed so as to describe a part of the data in the database design data read into the memory 6 at a predetermined position of the template for each of the above script files.
[0060]
9 to 13 show an example of the contents of the script file. The leftmost number is the line number and is not included in the script itself. The first to eighth lines are all comments and not scripts.
[0061]
FIG. 9 shows an example of a table creation script file created based on the table design data relating to the bank table shown in FIG. 5 and the item design data relating to each item of the bank table shown in FIG.
[0062]
The 9th to 17th lines are scripts for building the “bank table” to be created. If design data (table design data and item design data) for other tables is prepared, scripts for the tables are sequentially described in the 18th and subsequent lines.
[0063]
For example, in the 9th line, “CREATE TABLE bank (” is described. This description is the table name that the macro for database design data reads the table name from the table design data in the database design data and reads the table name. Is a script description that is created as a result of the description between "CREATE TABLE" and "(". In this way, the database design data macro uses part of the data read from the database design data as a template The script file is sequentially created (embedded), thereby creating various script files.
[0064]
FIG. 10 is an example of an authority setting script file created based on the authority setting data (see FIG. 4).
[0065]
FIG. 11 is an example of a primary key setting script file created based on the item design data (see FIG. 6).
[0066]
FIG. 12 shows an example of a trigger creation script file. The trigger creation script file is a file for causing the database management system to perform processing for recording the date and time when data is added or updated when data in the table is added or updated.
[0067]
FIG. 13 is an example of a constraint condition setting script file created based on the constraint conditions described in the item design data (see FIG. 6).
[0068]
Returning to FIG. 8, when the database design data macro processing is completed, the initial registration data macro is activated next (step 23). Initial registration data is read into the memory 6.
[0069]
FIG. 14 shows a description example of an initial data (initial registration) batch file. The initial data (initial registration) batch file is included in the initial registration data. FIG. 15 shows an example of an initial data (initial registration) script file generated by the macro for initial registration data.
[0070]
A plurality of initial data (initial registration) script files generated by the macro for initial registration data are output when there are many initial registration data. In this case, a program for performing macro processing on the initial registration data without leakage is the initial data (initial registration) batch file shown in FIG. By executing the initial data (initial registration) batch file, macro processing by the initial registration data macro is performed on the initial registration data (see FIG. 7). An initial data (initial registration) script file as shown in FIG. 15 is generated (step 24). When there are a plurality of initial registration data, a plurality of initial data (initial registration) script files are generated (initial data (initial registration) script file group).
[0071]
Returning to FIG. 8, when the processing of the initial registration data macro is completed, the production transfer data macro is activated (step 25). Production transfer data is read into memory. Based on the production transfer data, initial data (production migration) script file similar to that shown in FIG. 15 is generated (step 26).
[0072]
Various script files created by the macro sequential execution program (macro) are stored in the hard disk 8 of the database construction device 1. Next, the process proceeds to the script file batch execution program.
[0073]
FIG. 16 is a flowchart showing the flow of processing of the script file batch execution program.
[0074]
When the user of the database construction device 1 inputs an instruction to start a script file batch execution program, the script file batch execution program is read from the hard disk 8 and read into the CPU 2 (step 31).
[0075]
"Table creation script", "Permission setting script", "Primary key setting script", and "Trigger creation script" are executed. A table is created in a database used in the database management system (step 32), access authority for each table is set (step 33), a primary key is set (step 34), and a trigger is created (step 35). ).
[0076]
It is determined whether the initial data (initial registration) script file (group) is stored in the hard disk 8 (step 36). If the processing by the initial registration data macro described above is successful (if it has been performed), the hard disk 8 stores initial data (initial registration) script file (s). If the initial data (initial registration) script file (group) is stored in the hard disk 8 ("exists" in step 36), the initial data (initial registration) script file (group) is executed. Data (bank name, etc.) stored in the initial data (initial registration) script file (group) is stored in the table created by the table creation script file (step 37).
[0077]
It is determined whether the initial data (production transfer) script file (group) is stored in the hard disk 8 (step 38). If the initial data (production transfer) script file (group) is stored in the hard disk 8 ("exists" in step 38), the initial data (production transfer) script file (group) is executed. Data (bank name, etc.) stored in the initial data (production transfer) script file (group) is stored in the table created by the table creation script (step 39).
[0078]
If the initial data (initial registration) script file or the initial data (production migration) script file is not stored in the hard disk 8, the processing of the script file batch execution program ends ("not present" in step 36). , "Not present" in step 38).
[0079]
The database used for the database management system is constructed by the above processing. Thereafter, the data integrity check program is started. FIG. 17 is a flowchart showing the processing of the data consistency check program.
[0080]
The data integrity check program uses the data integrity check mechanism of the database management system. This is a process for detecting if there is data that does not meet the constraints in the data described in the database design data.
[0081]
FIG. 18 shows specific program descriptions of the script file batch execution program and the data consistency check program.
[0082]
The first line is an instruction not to perform unnecessary display.
[0083]
The second to ninth lines are comments.
[0084]
The 10th to 13th lines are the variable settings that describe "server name", "database name", "user name", and "password".
[0085]
The 15th line is also a comment.
[0086]
The 17th line is initialized to use the variable FLG as a flag.
[0087]
Lines 18 and 19 are processes for deleting the existing database configuration once. Two scripts “07_drop_constraint.sql” and “10_Drop.sql” are used. The roles of the two scripts are “restriction condition deletion” and “table deletion”, respectively. The reason why the constraint condition is deleted before deleting the table is that the table may not be deleted if there is a constraint condition.
[0088]
On the 21st to 23rd lines, the “table creation script” is executed to create a table. The 21st line describes that “Create” is displayed on the screen. Indicates that the table creation process has been started. The 22nd line means that a record that the table creation processing has been started is left by writing “Create” in the log file “00_Create.log”.
[0089]
On the 23rd line, the “table creation script” with the file name “01_Create.sql” is actually executed. Hereinafter, with the same configuration, authority setting is performed in the 24th to 26th lines, primary key setting is performed in the 27th to 29th lines, and trigger generation is performed in the 30th to 32nd lines.
[0090]
Line 34 checks whether the initial data (initial registration) script file (group) exists, and if not, the process jumps to line 50.
[0091]
Lines 35 and 36 are recorded in the screen and log file, and line 37 indicates the execution of the program "05_default_0000.bat" as shown in FIG.
[0092]
In line 38, the FLG variable is changed to represent the state in which “initial data (initial registration)” is registered.
[0093]
Lines 40 to 44 perform the same process to register “initial data (production transfer)”.
[0094]
Line 46 determines whether the initial data has been registered. If initial data is not registered, jump to the 50th line. If initial data has been registered, data consistency check processing is performed.
[0095]
Data consistency check processing is described in the 47th to 49th lines.
[0096]
A constraint condition is added (FIG. 17: step 41).
[0097]
If there is an error when adding the constraint condition (FIG. 17: “failure” in step 42), a record remains in the log file “00_Create.log” (step 43). If it succeeds when the constraint condition is added, the data consistency check process ends (“success” in step 42).
[0098]
Through the above processing, a database used in the database management system is constructed as shown in FIG.
[Brief description of the drawings]
FIG. 1 is a block diagram showing a hardware configuration of a database construction device.
FIG. 2 shows the relationship between database design data, initial registration data, production transfer data, and various script files created by macro-processing these data.
FIG. 3 shows an example of database size design data.
FIG. 4 shows an example of authority setting data.
FIG. 5 shows an example of table design data.
FIG. 6 shows an example of item design data.
FIG. 7 shows an example of initial registration data.
FIG. 8 is a flowchart showing the flow of processing of a script file creation program.
FIG. 9 shows an example of the contents of a table creation script file.
FIG. 10 shows an example of the contents of an authority setting script file.
FIG. 11 shows an example of the contents of a primary key setting script file.
FIG. 12 shows an example of the contents of a trigger creation script file.
FIG. 13 shows an example of the contents of a constraint condition setting script file.
FIG. 14 shows an example of the contents of an initial data (initial registration) batch file.
FIG. 15 shows an example of the contents of an initial data (initial registration) script file.
FIG. 16 is a flowchart showing the flow of processing of a script / file batch execution program.
FIG. 17 is a flowchart showing the flow of processing of a data consistency check program.
FIG. 18 shows specific examples of a script file batch execution program and a data consistency check program.
FIG. 19 conceptually shows a constructed database.
[Explanation of symbols]
1 Database construction device
2 CPU
3 Input devices
8 Hard disk

Claims (2)

At least database size design data including the database size of the relational database to be created, table design data including the table name and table size for each table constituting the relational database, and items included in the table Item design data including item name and item size, and constraint data to ensure that the data of the other item exists when the data of one item refers to the data of the other item An input device that accepts input of database design data and registration data to be registered in the created relational database ,
The database design data script creation macro including the template data having the description specifying the data to be described and the description position among the data included in the database design data , and the table constituting the relational database to be created A storage device storing a registration data script creation macro for creating a registration data script file for storing the registration data, among the included items ; and
A processing unit for executing a script creation macro for database design data and a script creation macro for registration data stored in the storage device, and executing a script file generated by executing the script creation macro;
The above processing equipment
By executing the database design script creation macro, in accordance with the description in the template data, the data included in the database design data is described at a specific location of the template data description , and a relational database is constructed. Generate a table creation script file and a constraint condition setting script file for checking the existence of reference data ,
By executing the above registered data script creation macro, a registration data script file for storing the registered data in the items included in the table constituting the created relational database is generated.
A relational database having a database size included in the database size design data by executing the generated table creation script file, and having a table name and a table size included in the table design data Build a relational database that has a table and the table has the item name and item size included in the item design data ,
By executing the generated registration data for the script file, the items included in the tables that comprise the constructed relational database to store the registration data,
By executing the generated constraint condition setting script file, the relational database storing the registration data is updated based on the constraint condition data included in the item design data of the database design data. in which items of data to check for the presence of data of the other items in the case of referring to the data of other items,
Relational database construction device. The input device includes at least the database size design data including the database size of the relational database to be created, the table design data including the table name and table size for each of the tables constituting the relational database, and included in the above table. Item design including item name and item size for each item, and constraint data to ensure that the other item's data exists when one item's data references other item's data and database design data having the data, the input of registration data to be registered in a relational database that is created reception,
A script for creating database design data including template data having a description for specifying the data to be described and the description position among the data included in the database design data and a relational database to be created are stored in the storage device. The registration data script creation macro for creating the registration data script file for storing the registration data is stored in the items included in the table to be configured .
The processing unit
To create a relational database by executing the database design script creation macro and describing the data included in the database design data at the specified location of the template data according to the description in the template data. Create a table creation script file and a constraint condition setting script file to check whether the reference data exists,
By executing the above registered data script creation macro, a registration data script file for storing the registered data in the items included in the table constituting the created relational database is generated.
A relational database having a database size included in the database size design data by executing the generated table creation script file, and having a table name and a table size included in the table design data Build a relational database that has a table and the table has the item name and item size included in the item design data,
By executing the generated registration data script file, the registration data is stored in the items included in the table constituting the constructed relational database ,
By executing the generated constraint condition setting script file, the relational database storing the registered data is changed based on the constraint condition data included in the item design data of the database design data. Check whether the data of the other item exists when the data of the other item refers to the data of the other item,
Relational database construction method.

Images