JP2003345594A - Script file generating device and method for structuring data base - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To automatically create a script to be used for structuring a data base from data base design data. <P>SOLUTION: Data base design data including the size of a data base to be created, the table name and size of respective tables configuring the data base, and the item name and size of respective items included in the table or the like are inputted from an input device 3, communication equipment 5 or the like of a data base structuring device 1. A hard disk 8 of the data base structuring device 1 is stored with macro including template data in which a script file for constructing the data base is configured by embedding predetermined data. The data included in the data base design data are described at the predetermined position of the macro. When the macro in which the data are described is executed, a script file for constructing the data base is generated. <P>COPYRIGHT: (C)2004,JPO

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a script file generating apparatus for constructing a database, and more specifically, an apparatus and a method for generating a script file for constructing a database used in a database management system, and the generated script file. And a method for constructing a database by executing a script file.

[0002]

BACKGROUND OF THE INVENTION A database used in a database management system (database management system: DBMS) includes a data capacity of the database, types of tables stored in the database, items stored in each table, and records for each item. A script file is created based on the database design data defining the length and the like, and the script file is constructed based on the created script file.

[0003] Database design data and script files are managed in separate files. Therefore, when database design data is changed,
The files also need to be changed. If the database design data is frequently changed, the work of changing 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 constructing a database from database design data.

Another object of the present invention is to provide a database construction apparatus and method for constructing a database by executing a script file automatically generated.

A further object of the present invention is to make the referential constraints unquestionable when the database is created, even if the created database contains data having mutual referential constraints.

A script file generating apparatus according to the present invention provides at least a database size of a database to be created, a table name and a table size of each of the tables constituting the database, and an item included in the table. A script creation macro including template data that includes a script file for constructing the database by embedding predetermined data in the database design data including the item name and item size of the database, and the database design data The included data is described at a predetermined position of the template data of the script creation macro, and the script creation macro in which the data is described is executed to execute a process for constructing a database. And it includes a script file generation means for generating a script file.

[0008] The script file generating method according to the present invention provides at least a database size of a database to be created, a table name and a table size of each of the tables constituting the database, and an item included in the table. The database design data including the item name and the item size of the above is input, and the predetermined data is embedded to form a script file for constructing the database. The predetermined position of the script creation macro including the template data Then, a script file for constructing a database is generated by describing data included in the database design data and executing a script creation macro in which the data is described.

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 is constructed as described in the database design data.

According to the present invention, even if a change occurs in the database design data, a script file reflecting the change is generated.
The database created by executing the file conforms to the database design data. Changes in database design data are reflected in the database without omission. The contents of the change in the database design data can be reliably reflected in the database.

In one embodiment, the database design data includes constraint data, and further includes checking means for checking for a logical error in the constructed database based on the constraint data.

Generally, in a database used in a database management system, when a certain data A refers to another data B, a constraint is provided to guarantee 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 constructing the database, it is checked whether or not there is a logical error in the constructed database (in the above case, data B does not yet exist despite the existence of data A). There is no need to consider that data B needs to be registered first when constructing the database.

[0013]

DESCRIPTION OF THE PREFERRED EMBODIMENTS In a computer system provided with a CPU, a memory, a hard disk, an input device, an output device, and the like, a database and a database management program are stored on the hard disk, so that the computer system is connected to a database management system (database management system). Management system). The database management system manages the database efficiently even if multiple users update and search the database at the same time, permits or restricts access to important data stored in the database for each user, This is a system for restoring the contents of the database to the state immediately before the failure when any failure occurs.

An apparatus (database construction apparatus) described in this embodiment is an apparatus for constructing a database used in the above-described database management system.

FIG. 1 is a block diagram showing a hardware configuration of an apparatus (database construction apparatus) for constructing a database used in a database management system.

The database construction apparatus 1 is composed of 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 has macros (programs) for database design data,
A macro (program) for initial registration data, a macro (program) for production transfer data, a script batch execution program, and a data consistency check program are stored.

As will be described later, the input device 3 and the communication device 5
Through these operations, database design data, initial registration data, and actual migration data are input to the database construction device 1. A script file (a file in which the script is described) is created from these data. By executing the created script file, that is, by executing the script described in the script file by the CPU 2, a database used for the database management system is constructed.

FIG. 2 shows three types of data input to the database construction apparatus 1 and these data as input data, which are subjected to macro processing (the above-described macros for database design data, macros for initial registration data, And the type of the script file output by the execution of the process by the macro for production migration data).

From the database design data, a table creation script file, an authority design script file, a primary key setting script file, a trigger creation script file, and a constraint condition setting script file
A file is created.

An initial data (initial registration) script file is created from the initial registration data.

An initial data (production migration) script file is created from the production migration data.

These script files do not necessarily mean one file. Depending on the type of script file, the database construction device 1
Some are created for each table or table included in the database created by. Details of these data and the script file will be described later.

The above-mentioned script files except the constraint condition setting script file are collectively (all) executed by the script file batch execution program. The database is constructed on the hard disk 8 by executing the script file. The constraint condition setting script file is executed by the data consistency check program.

First, three types of data (database design data, initial registration data, and actual transfer data) input to the database construction apparatus 1 will be described.

(1) Database Design Data FIGS. 3 to 6 show examples of database design data. The basic structure (database structure) of the database itself to be used (created) in the database management system is included in the database design data
(Number of bytes, access authority, etc.) (FIGS. 3 and 4), and various tables constituting a database (data (actual data) is stored in this table)
That defines the structure (table structure) for
And FIG. 6). When a plurality of tables are created in the database, a plurality of database design data defining a table structure is created.

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 occupying the hard disk) (the size of the database). "Average" and "Maximum"
With items.

The "average" column indicates the size which is considered to be required on average while the database management system using the database to be created is operating. The "maximum" column similarly indicates the size that is considered to be the maximum required during the operation of the database management system.

FIG. 4 shows design data (authority setting data) for defining the use authority of the created database.

The authority setting data includes a "user name" column and an "authority" column. The user name of the user (user of the database management system) for which the authority content is to be set is described in the “user name” column. Authority contents are described in the “Authority” column. In the example shown in FIG. 4, the "public" user is permitted to grant all the rights.

FIG. 5 shows an example of design data (table design data) that defines the attributes of various tables stored in the database. FIG. 5 shows table design data of a table (bank table) for managing bank information.

The table design data defines the table name, the number of records (average and maximum), the number of bytes of the table (average and maximum), and the number of bytes of one record.

In the "table name" column, a table name is described. The average of the "record number" 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.

The average in the “number of bytes” column indicates the average number of bytes expected in this table as the “number of bytes”.
The maximum of the column describes the maximum number of bytes required for this table. One record in the “number of bytes” column describes the number of bytes required for one record of this table.

The sum of the 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 sum of the 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.

FIG. 6 shows design data (item design data) of items included in various tables stored in the database.
This is an example. FIG. 6 shows item design data for each item included in the bank table shown in FIG.

The item design data includes item name, data type, integer byte number, decimal byte number, attribute, primary key, index, availability of zero data, item description, and data on constraints.

The "item name" is the name of each item.

"Data type" is the type of data of each item. For example, char means a data type of fixed-length character string.

The “number of integer bytes” is the number of digits of the integer part when the above data type is an exact data type (varchar). If the data type is not an exact data type and is not a string type, describe the required number of bytes. In the case of the string type, this number of bytes means the number of characters that can be recorded.

The "decimal byte number" is the number of digits of the decimal part when the data type is an exact data type. If the data type is not an exact data type, 0 is described.

The type of data (half-width, full-width, etc.) is described in the “attribute” column.

The "primary key" column shows items to be adopted as primary keys.印 or ◎ is shown. A circle means a non-clustered primary key. ◎ means the clustered primary key.

In the "index" column, items used as indexes are shown.印 or ◎ is shown. ○ means a non-clustered index, and ◎ means a clustered index.

Only one clustered primary key or index exists in one table. If there are two or more, decluster one of them (◎
(Replace the mark with a circle)). There may be tables without clustered primary keys and clustered indexes.

In the “zero availability” field, whether or not to allow 0 (zero) value is set. Here, “0” is described when “0” is not permitted, and “OK” is described when “0” is permitted.

In the "item description" column, the description of the item is described.

The "restriction conditions" column describes the restriction conditions for the items. The constraints will be described later.

(2) Initial registration data FIG. 7 shows an example of the initial registration data.

The initial registration data is created for each table included in the database to be created. The initial registration data includes a table name column, in which the table name defined in the database design data is described. This clearly indicates which table is the initial registration data.

The initial registration data is specific data for each item designed in the item design data (FIG. 6) created for each table described above. As described above, if it is designed that the bank table is provided with bank code, bank kanji, bank name kana, remarks column, update date and time, and update program, the initial registration data of this bank table includes Specific data for each of these items is stored.

(3) Production transfer data Basically the same as the initial registration data. The difference between the initial registration data and the production transfer data is as follows.

The initial registration data is data describing minimum necessary items for operating a database management system using the created database. 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 production 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 necessarily required.

As described above, various script files are created by processing the database design data, the initial registration data, and the actual migration data by the macros for each of these data.

FIG. 8 is a flowchart showing the flow of processing of a program for creating various script files. This program uses the macro described above,
This is a program (macro sequential execution program) for sequentially executing a database design data macro, an initial registration data macro, and a production migration data macro.

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.
To be stored. Of course, database construction device 1
May be stored in a hard disk of another computer connected to the network by a network (LAN, WAN, etc.).

The user of the database construction device 1 starts the script file creation program. First, a macro (program) for database design data is executed (step 21).

The database design data is read. For example, an address at which database design data of a database to be created is stored is specified by the user of the database construction device 1. When the 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 processing using the database design data macro is started with the database design data read into the memory 6 as a processing target (step 2).
2).

As described above, the macro for database design data is based on the database design data, and the table creation script file, the authority setting script
Create a file, primary key setting script file, trigger setting script file, and constraint condition setting script file.

The macro for the database design data is programmed so that a part of the database design data read into the memory 6 is described in a predetermined position of the template for each of the above script files. I have.

FIGS. 9 to 13 show examples 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.

FIG. 9 shows an example of a table creation script file created based on the table design data for the bank table shown in FIG. 5 and the item design data for each item of the bank table shown in FIG.

Lines 9 to 17 are scripts for constructing a “bank table” to be created. If design data (table design data and item design data) for another table is prepared, a script for that table is sequentially described in the 18th line and thereafter.

For example, in the ninth line, “CREATE TABLE
Bank () is described. In this description, the database design data macro reads the table name from the table design data in the database design data, and replaces the read table name with “CREATE TABLE” and “(”. In this way, the macro for database design data describes (embeds) a part of the data read from the database design data in a predetermined location of the template. As a result, various script files are sequentially created.

FIG. 10 shows an example of an authority setting script file created based on the authority setting data (see FIG. 4).

FIG. 11 is an example of a primary key setting script file created based on the item design data (see FIG. 6).

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 in the table is added or updated when the data is added or updated.

FIG. 13 is an example of a constraint setting script file created based on the constraints described in the item design data (see FIG. 6).

Returning to FIG. 8, when the processing by the database design data macro is completed, the initial registration data macro is activated (step 23). Initial registration data is read into the memory 6.

FIG. 14 shows an initial data (initial registration) batch
It is a description example of a file. The initial data (initial registration) batch file is included in the initial registration data. Fig. 15
Is an example of an initial data (initial registration) script file generated by the initial registration data macro.

A plurality of initial data (initial registration) script files generated by the initial registration data macro are output when the initial registration data is large. In this case, a program for macro-processing the initial registration data without leakage is an initial data (initial registration) batch file shown in FIG. Initial data (initial registration) batch
When the file is executed, the initial registration data (see FIG. 7) is subjected to macro processing by the initial registration data macro. Initial data (initial registration) script file as shown in Fig. 15 is generated (step
twenty four). 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).

Returning to FIG. 8, when the processing of the macro for the initial registration data is completed, the macro for the actual migration data is activated (step 25). The production migration data is read into the memory. Based on the production migration data, an initial data (production migration) script file similar to the one shown in FIG. 15 is generated (step 26).

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.

FIG. 16 is a flowchart showing the flow of processing of the script file batch execution program.

When the user of the database construction apparatus 1 inputs a start command of the script file batch execution program, the script file batch execution program is read from the hard disk 8 and read by the CPU 2 (step 31).

The "table creation script", "authority setting script", "primary key setting script" and "trigger creation script" are executed. Tables are created in the database used in the database management system (step 32), access rights for each table are set (step 33), and primary keys are set (step 32).
34), a trigger is created (step 35).

It is determined whether or not the initial data (initial registration) script file (group) is stored in the hard disk 8 (step 36). If the processing by the above-described initial registration data macro is successful (if performed), the initial data (initial registration) is stored in the hard disk 8.
A script file (group) is stored. If an initial data (initial registration) script file (group) is stored on the hard disk 8 (“exists” in step 36), the initial data (initial registration) script file
The file (s) are executed. Initial data (initial registration)
The data (bank name, etc.) stored in the script file (group) is stored in the table created by the above-described table creation script file (step 37).

It is determined whether or not the initial data (production transfer) script file (group) is stored in the hard disk 8 (step 38). If the hard disk 8 stores the initial data (production migration) script file (group) ("exists" in step 38), the initial data (production migration) script file (group) is executed. The data (bank name, etc.) stored in the initial data (production transfer) script file (group) is stored in the table created by the above-described table creation script (step 39).

If the initial data (initial registration) script file or the initial data (production shift) script file is not stored in the hard disk 8, the processing of the script file batch execution program is terminated (step 36). Does not exist "and" does not exist "in step 38).

The database used in the database management system is constructed by the above processing. Thereafter, the data consistency check program starts. FIG. 17 is a flowchart showing the processing of the data consistency check program.

The data consistency check program utilizes the data consistency check mechanism of the database management system. This is a process for detecting if there is data that does not meet the constraint conditions in the data described in the database design data.

FIG. 18 shows specific program descriptions of the script file batch execution program and the data consistency check program.

The first line is an instruction for not performing unnecessary display.

The second to ninth lines are comments.

The tenth to thirteenth lines are for setting variables and describe “server name”, “database name”, “user name”, and “password”.

The 15th line is also a comment.

Line 17 is initialized to use the variable FLG as a flag.

The 18th and 19th lines are for deleting the existing database configuration once. "07_drop_cons
Two scripts, "traint.sql" and "10_Drop.sql" are used. The role of the two scripts is
These are "delete constraint conditions" and "delete table", respectively. Removing constraints before dropping a table
This is because a table may not be able to be deleted when there are constraints.

On the 21st to 23rd lines, a "table creation script" is executed to create a table. Line 21 describes that "Create" is displayed on the screen. Indicates that table creation processing has started. Line 22 is "00_C
By writing "Create" in a log file called "reate.log", it means that a record that the table creation process has started is left.

On the 23rd line, a “table creation script” having a file name “01_Create.sql” is actually executed. In the following, with the same configuration, set authority in lines 24 to 26,
The primary key setting is performed in the 27th to 29th lines, and the trigger creation processing is performed in the 30th to 32nd lines.

In the 34th line, it is checked whether or not the initial data (initial registration) script file (group) exists, and if not, the process jumps to the 50th line.

Lines 35 and 36 leave a record in the screen and log file, and line 37 indicates the execution of a program such as "05_default_0000.bat" as shown in FIG.

In the 38th line, “initial data (initial registration)”
Change the FLG variable so that it represents the state where is registered.

Lines 40 to 44 perform the same processing to register "initial data (production shift)".

In line 46, it is determined whether or not the initial data has been registered. If no initial data has been registered, jump to line 50. If the initial data has been registered, a data consistency check process will be performed.

The data consistency check process starts at line 47.
It is described on line 49.

A constraint condition is added (FIG. 17: Step 4)
1).

If there is an error when adding the constraint condition (FIG. 17: "failure" in step 42), a record is left in the log file "00_Create.log" (step 4).
3). If it succeeds when the constraint condition is added, the data consistency check process ends ("success" in step 42).

Through the above processing, a database used for the database management system is constructed as shown in FIG.

[Brief description of the drawings]

FIG. 1 is a block diagram illustrating a hardware configuration of a database construction device.

FIG. 2 shows the relationship between database design data, initial registration data, and production migration 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 a 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 device 8 Hard disk

Claims (6)

[Claims] At least a database size of a database to be created, a table name and a table name of each of the tables constituting the database.
Database design data including size, item name and item size for each of the items included in the table, template data that is embedded with predetermined data to form a script file for building the database The script creation macro including the data and the data included in the database design data are described at predetermined positions of the template data of the script creation macro, and the script creation macro in which the data is described is executed to construct the database. Script file generating means for generating a script file for
File generator. 2. A database construction means for constructing the database as described in the database design data by executing a script file generated by the script file generation device according to claim 1. , Database construction equipment. 3. The database design data includes constraint condition data, and further comprising a check unit for checking whether there is a logical error in the database constructed by the database construction unit based on the constraint condition data. Item 3. The database construction device according to item 2. 4. A database size of at least a database to be created, a table name and a table name for each of the tables constituting the database.
Enter the database design data including the size and the item name and item size of each of the items included in the table, and embed the predetermined data to create a script / script for building the database.
The data included in the database design data is described at a predetermined position of the script creation macro including the template data that constitutes the file, and the script creation macro in which the data is described is executed to execute the script creation macro. A script file generation method that generates a script file. 5. A database construction method for constructing the database as described in the database design data by executing a script file generated by the method according to claim 4. 6. The database construction method according to claim 5, wherein the database design data includes constraint condition data, and based on the constraint condition data, checks whether there is a logical error in the constructed database.