JP6986394B2 - Data maintenance system, data maintenance method - Google Patents

Description

The present invention relates to a data maintenance system and a data maintenance method.

Conventionally, there are various mechanisms for registering the data stored in the database and updating the data registered in the database. For example, in Patent Document 1, the first stage processing is performed in a program that refers to data in a table in a database, inserts new data into the table, updates data in the table, and deletes data in the table. It is a data processing program automatic generation system that automatically generates the entire program including a part, an iterative processing part, and a post-stage processing part based on the program skeleton and specifications, and has different specifications for each data processing program to be created. A system having a specification input means having a function of inputting a certain individual specification and a function of inputting a common specification which is a different specification for each group to which the data processing program to be created belongs is disclosed.

Japanese Unexamined Patent Publication No. 2012-048438

In Patent Document 1, the above configuration reduces the cost required for creating and storing specifications. However, for example, when the operation person on the user side who uses the system is in a remote place away from the system creator, the data processing program and the specifications of the table defined by the data processing program itself are created or updated. When doing so, the system creator needs to go to the user side for maintenance. Therefore, it takes time and labor from receiving the request from the user side to creating and updating the above programs and tables, and maintenance cannot be easily performed.

An object of the present invention is to provide a data maintenance system and a data maintenance method capable of easily maintaining data possessed by a remote system.

In order to solve the above problems, the data maintenance system according to the present invention is a data maintenance system that maintains data stored in a database, and is a pre-registered SQL (Structured Query Language) statement and after extraction. A data maintenance unit comprising a selection processing unit that selects format information indicating a layout, and an extraction processing unit that extracts desired data from the database based on the selected SQL statement and the format information. Configured as a system.

The present invention is also understood as a data maintenance method performed by the data maintenance system.

According to the present invention, it is possible to easily maintain the data possessed by the remote system.

It is a figure which shows the functional configuration of a data maintenance system. It is a figure which shows the example of the screen which the extraction system outputs. It is a sequence diagram which shows the processing procedure of the extraction process. It is a figure which shows the example of the screen which the maintenance system outputs. It is a sequence diagram which shows the processing procedure of maintenance processing. It is an image diagram of system processing.

Hereinafter, embodiments of the data maintenance system and the data maintenance method will be described in detail with reference to the attached drawings.

FIG. 1 is a diagram showing a functional configuration of the data maintenance system 1000.
As shown in FIG. 1, the data maintenance system 1000 includes an extraction system 1 and a maintenance system 10. Each system is composed of a computer such as one or a plurality of servers as hardware. Each system is provided with a PC (Personal Computer) 13 operated by an operation worker 13, and is connected to each system via a network. The server and the PC 30 are equipped with an input device such as a keyboard, a display device such as a display, and a communication device such as a NIC (Network Interface Card).

The extraction system 1 is a system that extracts data from a database (hereinafter referred to as DB) 12. As shown in FIG. 1, the extraction system 1 includes a DB information registration unit 2, an SQL (Structured Query Language) sentence registration unit 3, a template registration unit 4, and a data extraction unit 5. .. The DB information registration unit 2 is a processing unit that registers the DB information 6 input from the PC 30 in the DB 12. The SQL statement registration unit 3 is a processing unit that registers the SQL information 7 input from the PC 30 in the DB 12. The template registration unit 4 registers the template data (for example, Excel (registered trademark) file) 8 input from the PC 30 in the DB 12, and outputs the linking file 9 in which the registered SQL and the template data are associated with each other. Is. The data extraction unit 5 is a processing unit that extracts data from the DB 12 according to an instruction from the PC 30 and outputs the extracted data 15. The operation of each part of the extraction system 1 will be described later using a screen and a flowchart.

The maintenance system 10 is a system for updating (or modifying) the data extracted by the extraction system 1. As shown in FIG. 1, the maintenance system 10 has an update file registration unit 11 and a consistency maintenance unit 16. The update file registration unit 11 is a processing unit that registers the update data 14 in which some items and values of the extracted data 15 are updated (or modified) in the DB 12. The consistency holding unit 16 is a processing unit that refers to the consistency file 17 and determines whether or not the updated data 14 is consistent with the data before the update. The operation of each part of the maintenance system 10 will be described later using screens and flowcharts.

FIG. 2 is a diagram showing an example of a screen output by the extraction system 1.
FIG. 2A is a registration screen for registering various information on the display device of the PC 30. FIG. 2B is a creation screen for registering the template data 8 as format information. FIG. 2C is an extraction screen for the data extraction unit 5 to extract data from the DB 12.

As shown in FIG. 2A, the DB information registration column 201 for registering the DB information, the SQL statement registration column 202 for registering the SQL statement, and the form information are registered on the registration screen. Includes the form registration field 207 for Each registration column includes a reference button B1 for calling up information that has already been registered, and a registration button B2 for newly registering information. Further, the registration screen includes a style creation button B3 for transitioning from the screen to the creation screen.

For example, the input device of the PC 30 is an access including a DB name, a URL (Uniform Resource Locator) in which the DB exists, and a password for accessing the DB from the DB information registration field 201 of the registration screen from the operation worker 13. Upon receiving the input of information, the DB information registration unit 2 receives the access information from the PC 30 and registers it in the extraction system 1. Actually, it is stored in the storage unit of the extraction system 1.

Further, for example, the input device of the PC 30 receives the input of the SQL information including the SQL statement (for example, the SELECT statement) from the SQL statement registration field 202 of the registration screen from the operation worker 13, and the SQL statement registration unit 3 receives the input. , The SQL information is received from the PC 30 and registered in the extraction system 1. Actually, it is stored in the storage unit of the extraction system 1.

Further, for example, the input device of the PC 30 may use the format information (for example, the title of the table) indicating the layout of the data extracted and output from the operation worker 13 from the format registration field 207 of the registration screen after the execution of the SQL statement. The input of the item name and the paper size) is accepted, and the template registration unit 4 receives the format information from the PC 30 and registers it in the extraction system 1. Actually, it is stored in the storage unit of the extraction system 1.

Further, as shown in FIG. 2B, the creation screen has an SQL selection field A for selecting an SQL statement and an extraction condition setting field B for setting conditions for extracting data from the DB 12. Includes. Further, the SQL selection field A includes a form ID input field 203 for inputting a form ID and an SQL statement selection field 204 for selecting an SQL statement, and a selection button for confirming the selected SQL statement. B4 is displayed. The extraction condition setting field B includes a condition input field 205 and an output button 206.

For example, when the input device of the PC 30 receives the pressing of the format creation button B3 on the registration screen from the operation worker 13, the template registration unit 4 calls the creation screen and outputs it to the display device of the PC 30. The input device of the PC 30 accepts the selection of the SQL statement to be registered in the form from the SQL statements displayed in the SQL statement selection field 204 of the SQL selection field A of the creation screen. The SQL statement displayed in the SQL statement selection field 204 is the SQL statement registered from the SQL statement registration field 202 of the registration screen shown in FIG. 2A. In FIG. 2B, it can be seen that the SQL statement 1 is selected.

Further, when the SQL statement is selected from the SQL statement selection field 204, the input device of the PC 30 accepts the input of the form ID from the operation worker 13. Further, the input device of the PC 30 accepts the input of the condition (for example, the value of the FROM clause or the WHERE clause) for the SQL statement selected from the SQL statement selection field 204 from the condition input field 205 of the extraction condition setting field B. .. After that, when the output button 206 is pressed, the SQL statement identified by the format ID specified on the above creation screen is executed according to the set extraction conditions and is output as the extraction data 15.

Further, as shown in FIG. 2C, the extraction screen has a format selection column C for selecting a format and an extraction condition setting column for setting conditions for SQL statements corresponding to the selected format. Includes D. The style selection field C includes a selection style field 208 for selecting a style and a selection button B5 for determining the selected style. Further, in the extraction condition setting field D, the condition input field 205 shown in FIG. 2B, the condition input field 209 similar to the output button 206, and the output button 210 are displayed.

For example, the input device of the PC 30 accepts the selection of the format of the data to be output from the format displayed in the selection format column 208 of the format selection column C of the extraction screen from the operation worker 13. The form displayed in the selection form column 208 is the form registered from (a) and (b) of FIG. In (c) of FIG. 2, it can be seen that Form 1 is selected. When the format is selected by pressing the selection button B5, the input device of the PC 30 is selected from the condition input field 209 of the extraction condition setting field D and from the format selection field C, as in the case of (b) of FIG. The input of the condition (for example, the value of the FROM clause or the WHERE clause) for the SQL statement (or the SQL statement selected from the SQL statement selection field 204) corresponding to the selected format is accepted. After that, when the output button 210 is pressed, the SQL statement identified by the format ID of the format specified on the extraction screen is executed according to the set extraction conditions, and is output as the extraction data 15.

The format, format ID, and SQL statement are the format ID, SQL statement, and their conditions that are input or selected on the creation screen when the template registration unit 4 presses the selection button B4 or the output button 206 on the creation screen. Is stored in the storage unit of the extraction system 1 as the associated file 9 in association with the above. Therefore, when a format is selected from the selection format field 208 of the extraction screen, the template registration unit 4 reads the SQL statement and the condition corresponding to the selected format from the storage unit and displays them on the screen. The operation worker 13 can obtain the extracted data 15 by pressing the output button 210 after correcting or updating the read information. Next, the processing performed by this system will be described.

FIG. 3 is a sequence diagram showing a processing procedure of processing (extraction processing) performed in this system.
As shown in FIG. 3, when the PC 30 receives the DB information registration operation from the operation manager 13, the DB information registration unit 2 outputs the registration screen to the display unit of the PC 30, and (a), (a) of FIG. As described in b), registration of DB information is executed (step S301). Subsequently, as described in FIGS. 2A and 2B, the SQL statement registration unit 3 outputs the creation screen to the display unit of the PC 30 and executes the registration of the SQL statement (step S302).

The PC 30 accepts the creation of the template data from the operation worker 13 (step S303), and the template registration unit 4 registers the template data created by the PC 30 as described in FIGS. 2A and 2B. Is executed, and the above-mentioned association file 9 is generated and stored in the storage unit (step S304).

As described in FIG. 2C, the data extraction unit 5 outputs the extraction screen to the display unit of the PC 30, and accepts selection and input of a format and extraction conditions from the PC 30 via the screen (step S305). ), Data is extracted from the DB 12 according to the above format and extraction conditions, and the extracted data 15 is output (step S306). When the process of step S306 is completed, the extraction process shown in FIG. 3 is completed. By executing the process, the operation worker 13 can extract the data registered in the DB 12 according to a predetermined format and extraction conditions. Subsequently, the maintenance system 10 will be described.

FIG. 4 is a diagram showing an example of a screen output by the maintenance system 10. FIG. 4 is a maintenance screen for registering the updated data 14 in which the extracted data 15 is updated from the display device of the PC 30. As shown in FIG. 4, the maintenance screen includes an update file registration field 401 and a consistency file registration field 402. Further, each registration column includes a reference button B6 for calling up information that has already been registered, and an update button B7 for updating the information.

As shown in FIG. 4, the maintenance screen comprises an update file registration field 401 for registering update data 14 and an already registered extraction data 15 (for example, a table that is a source of extraction data). It includes a consistency file registration field 402 for registering key information (for example, table name, item name) for designating an item to be updated by the update data 14 among the items.

For example, the input device of the PC 30 accepts the input of the update information including the name of the update data 14 and the URL in which the update data 14 exists from the update file registration field 401 of the maintenance screen from the operation worker 13, and registers the update file. The unit 11 receives the updated information from the PC 30 and registers it in the maintenance system 10. Actually, it is stored in the storage unit of the maintenance system 10.

Further, for example, the input device of the PC 30 receives the input of the key information (for example, the table name, WHERE clause) from the consistency file registration field 402 of the maintenance screen from the operation worker 13, and the consistency holding unit 16 However, the key information is received from the PC 30 and registered in the maintenance system 10. Actually, it is stored in the storage unit of the maintenance system 10.

When the update information and the key information are input from the update file registration field 401 and the consistency file registration field 402 of the maintenance screen, the update file registration unit 11 and the consistency maintenance unit 16 perform maintenance processing (FIG. 5). Run. Next, the maintenance process performed by this system will be described.

FIG. 5 is a sequence diagram showing a processing procedure of maintenance processing performed in this system.
As shown in FIG. 5, when the PC 30 receives the generation of the update data 14 (that is, the modification of the extracted data 15) and the generation of the consistency file 17 from the operation manager 13 (step S501), the update file registration unit 11 and the consistency are received. The sex holding unit 16 receives the registration operation of the update data 14 and the consistency file 17, respectively, outputs the maintenance screen to the display unit of the PC 30, and as described with reference to FIG. 4, the update information and the key information. Registration is executed (step S502).

Subsequently, the update file registration unit 11 determines whether or not the format ID of the modified data to be updated exists in the format ID registered (uploaded) in the extraction system 1 in the past (step S503). The reason for executing step S503 is to limit the target of the data to be updated or modified to the data uploaded in the past so that the update or modification is not executed for any data.

When the update file registration unit 11 determines that the format ID of the correction data to be updated does not exist in the format ID uploaded to the extraction system 1 in the past (step S503; No), the process ends for the above reason. do.

On the other hand, when the update file registration unit 11 determines that the format ID of the correction data to be updated exists in the format ID uploaded to the extraction system 1 in the past (step S503; Yes), the format ID is further changed to the format ID. Correspondingly, it is determined whether or not there is only one extraction data 15 stored in the storage unit of the extraction system 1 (step S504). The reason for executing step S504 is to avoid updating data that should not be updated by mistake.

When the update file registration unit 11 determines that there is not one extraction data 15 stored in the storage unit of the extraction system 1 corresponding to the format ID (step S504; No), the processing is terminated for the above reason. do.

On the other hand, when the update file registration unit 11 determines that there is only one extraction data 15 stored in the storage unit of the extraction system 1 corresponding to the format ID (step S504; Yes), the consistency maintenance unit 16 determines whether or not the key of the table to be updated exists (step S505). The reason for executing step 505 is to update the same item as the update data 14 among the items of the extracted data 15 that have already been registered. Further, the determination may be executed with reference to the update information, the key information, and the association file 9 registered in FIG.

When the consistency holding unit 16 determines that the key of the update target table does not exist (step S505; No), the consistency holding unit 16 ends the process for the above reason.

On the other hand, when the consistency holding unit 16 determines that the key of the update target table exists (step S505; Yes), the consistency holding unit 16 executes the data update process, and among the items of the extracted data 15, the same items as the update data 14 are displayed. It is updated and the updated data is registered in the maintenance system 10 (step S506). At this time, the updated data may be output to the display unit of the PC 30 so that the operation worker 13 can confirm the contents. When the process of step S506 is completed, the maintenance process shown in FIG. 5 is completed. By executing the process, the operation worker 13 extracts the data from the data registered in the DB 12, corrects the value of the item, and then based on the registered update information and the key information. The updated data 14 obtained by updating the extracted data 15 can be registered.

As described above, the data maintenance system 1000 includes the extraction system 1 and the maintenance system 10, and by executing each of the processes shown in FIGS. 3 and 5, as shown in the image diagram of the system process of FIG. In the extraction system 1, the data extraction process 601 is executed based on the DB information 6, the SQL information 7, and the template data 8 created by the operation worker 13, and the extraction data 15 extracted from the DB 12 (for example, the data is extracted). Form) is output. After that, the operation worker 13 executes the data correction process 602, uploads the update data 14 which is the extracted data 15 after the correction process 602 is executed to the maintenance system 10, and executes the DB update 604. Therefore, the data possessed by the remote system can be easily maintained. For example, an engineer in charge of a distant client who cannot perform remote maintenance due to security restrictions, etc., can send information necessary for extracting and updating data such as SQL and format information by e-mail without visiting the site. If it is sent to, arbitrary data can be extracted and maintained on the client side to which the operation worker 13 belongs, which can contribute to the reduction of man-hours for movement. In addition, SQL can be executed immediately after receiving a request from a client, and arbitrary data can be maintained and extracted immediately.

Each process performed by the extraction system 1 and the maintenance system 10 is actually realized by executing a program installed in each of these devices. The above program may be provided by being embedded in a ROM or the like in advance, or may be recorded and provided on a recording medium in an installable format or an executable format file, or may be distributed.

1000 ... Data maintenance system 1 ... Extraction system 2 ... DB information registration unit 3 ... SQL statement registration unit 4 ... Template registration unit 5 ... Data extraction unit 6 ... DB information 7 ... SQL information 8 ... Template data 9 ...・ ・ Linked file 10 ・ ・ ・ ・ ・ ・ Maintenance system 11 ・ ・ ・ ・ ・ ・ Update file registration unit 12 ・ ・ ・ ・ ・ ・ Extracted / updated target database (DB)
13 ... Operation worker 14 ... Update data 15 ... Extracted data 16 ... Consistency holding unit 17 ... Consistency file.

Claims (6)

A data maintenance system that maintains the data stored in the database.
A SQL (Structured Query Language) statement registered in advance, a selection processing unit that selects format information indicating the layout after extraction, and a selection processing unit.
An extraction processing unit that extracts desired data from the database based on the selected SQL statement and the format information.
An update processing unit that updates the data stored in the database based on the update data in which the value of the item included in the data extracted by the extraction processing unit is updated and the format information. ,
It is provided with a template registration unit that stores the association file in which the format information and the SQL are associated in the storage unit.
The update processing unit updates the data based on the association file.
A data maintenance system that features that. The update processing unit is the data to be updated based on the consistency file including the key information for designating the item to be updated by the update data among the items included in the data extracted by the extraction processing unit. determines the presence of, if it is determined that there is the data to be the update target, and updates the data,
The data maintenance system according to claim 1. The update processing unit determines whether or not the data is one corresponding to the format information, and when it is determined that the data is one corresponding to the format information, the data is used. Update,
The data maintenance system according to claim 1. A data maintenance method in which a computer maintains the data stored in a database.
Select the pre-registered SQL statement and the format information indicating the layout after extraction, and select
Based on the selected SQL statement and the format information, the desired data is extracted from the database .
The data stored in the database is updated based on the updated data in which the value of the item included in the extracted data is updated and the format information.
The association file in which the format information and the SQL are associated is stored in the storage unit, and the image is stored in the storage unit.
In the update, the data is updated based on the association file.
A data maintenance method characterized by that. In the update, the presence or absence of the data to be updated is determined based on the consistency file containing the key information for designating the item to be updated by the update data among the items included in the extracted data. If it is determined that there is the data to be the update target, and updates the data,
The data maintenance method according to claim 4 , wherein the data is maintained. In the update, it is determined whether or not the data is one corresponding to the format information, and when it is determined that the data is one corresponding to the format information, the data is updated. ,
The data maintenance method according to claim 4 , wherein the data is maintained.

Images