KR101063577B1 - Schema and stored procedure real-time management system and method of large database - Google Patents

Abstract

The present invention relates to a schema and stored procedure real-time management system and method of a large database for managing changes in the schema and stored procedures constituting a large database in real time and providing the developer.

Schema and stored procedure real-time management system of a large database in accordance with the present invention, the DB management table for managing the information about the schema and stored procedures of the large database by taking meta information from the large database, and connected to the large database If the meta information is obtained from the large database and the meta information stored in the DB management table is not matched, the schema and stored procedure constituting the large database are added, modified, or deleted. A database connection module for determining that the change has been made and updating the schema and stored procedure changes to the DB management table, and information on the schema and stored procedure of the large database stored in the DB management table. It is characterized in that it includes parts of available web services provided by the web screen.

Stored procedures, databases, web services, modernization, schemas, tables

Description

Real-time management of database schema and stored procedure in large volume database}             

1 is a functional block diagram showing a system for executing a schema and procedure real-time management method of a large database according to an embodiment of the present invention;

2 is a diagram showing information stored in a DB management table;

3 is a flowchart illustrating a method of managing a schema and a stored procedure real time of a large database in the database access module according to the present invention.

     BRIEF DESCRIPTION OF THE DRAWINGS FIG.

11; Large database 12; Database connection module

13; DB management table 14; Web Service Provider

15; Internet 16; Client system

17; operating system

The present invention relates to a large-scale database development method, and more particularly, to a system and method for managing a schema and stored procedures real-time management of a large database that provides changes to the database schema and stored procedures in real time and provides the developer.

Conventionally, in order to build a database, computer hardware such as a limitation of a processing speed of a central processing unit (CPU), a limitation of a hard disk capacity, a limitation of a bus speed serving as a path for exchanging information between a central processing unit and a hard disk, and the like. Many of the constraints of. In the related art, since a large database cannot be constructed due to such hardware limitations, a database that performs the same function is implemented in a local unit, that is, in a distributed system form. However, with the recent advances in hardware, these limitations have been overcome, resulting in projects worldwide to build a single large database.

The development of such a large database consists of several development modules, and each module shares a specific function and field. In order to develop such a large database, each development module should be accompanied by configuration management and securing the latest schema information. If different development teams have different schema information, it may not only make communication between development modules difficult, but also cause various adverse effects such as delay of integrated system development and redundant development or deterioration of reliability of development results. Therefore, in order to solve this problem, it is necessary to provide the same schema information to all development module members in real time by using meta information existing in the registry of the database.

An object of the present invention devised to meet the above-mentioned conventional needs is a system for managing the real-time variation of the schema and stored procedures generated when developing a large-capacity database using meta information of the database and providing the developer in real time; It is to provide a method.

Schema and stored procedure real-time management system of a large database according to the present invention for achieving the above object, DB management table for managing the information about the schema and stored procedures of the large database by taking meta information from the large database; If the meta information stored in the DB management table is not matched by comparing the meta information stored in the DB management table by accessing the large database, the schema and the stored procedure constituting the large database are added, modified or deleted. And a database connection module for determining that at least one of the above is changed and updating the schema and stored procedure changes in the DB management table, and the schema and stored program of the large database stored in the DB management table. Information relating to that service via the Internet Web service provided by the web screen is characterized in that it includes parts.

delete

delete

In addition, the schema and stored procedure real-time management method of a large database in accordance with the present invention, at least any one of the addition, modification or deletion of the schema and stored procedures constituting the large database is performed to receive the changes in real time DB A large capacity in a management system having a database access module for managing a management table and a web service providing unit that provides a web screen to a worker on the Internet through information on the schema and stored procedures of the large database stored in the DB management table. In the database schema and stored procedure real-time management method,
A first step of the database access module comparing the meta information of the mass database with the meta information stored in the DB management table; As a result of the comparison, if the meta information stored in the DB management table and the meta information received from the large database do not match, the difference between the meta information stored in the DB management table and the meta information received from the large database is determined. A second step of updating the DB management table and proceeding to the next step if there is no difference; A third step of stopping a thread after performing the second step, waiting for an operation period to be completed, and performing the operation again from the first step when the operation period is completed; If the worker is requested to query the management information of the schema and stored procedures of the large database through the Internet, the web service provider includes a fourth step of retrieving the information requested by the worker from the DB management table to the worker client system It is characterized by.

delete

delete

delete

delete

Hereinafter, with reference to the accompanying drawings will be described in detail the schema and stored procedure real-time management system and method of a large database according to an embodiment of the present invention.

1 is a functional block diagram showing a system for executing a schema and procedure real-time management method of a large database according to an embodiment of the present invention.

The system according to the present invention is connected to a large database 11, the database connection module 12 for receiving and managing in real time the changes of the schema and stored procedures constituting the large database 11 and the database connection module ( DB management table 13 for storing information about the schema and stored procedures of the large-scale database 11 managed by 12), and the schema and stored procedures of the large-scale database 11 stored in the DB management table 13 Web service provider 14 for web service information to the client system 16 of the worker via the Internet (15). The worker accesses the large-scale database 11 through the operating system 17 and adds, modifies, and deletes schemas and stored procedures.

Here, the stored procedure is a kind of program that provides an interface function between the schema of a large database and the user screen of a development program. Developers can execute query statements to exclude direct large database access, and change stored procedure programs that function as interfaces when schema changes are made to minimize the negative effects on previously developed functions due to inevitable schema changes in the future. To help. Stored procedures embedded in the database can directly access schemas (tables), views, and user-defined functions within themselves. Therefore, when schemas (tables), views, and user-defined functions are added, modified, or deleted, it necessarily involves adding, modifying, and deleting stored procedures that contain the information.

Information stored in the DB management table 13 is divided into reference information, relationship information between the reference information, and other information as shown in FIG.

The standard information consists of table / view list management information, stored procedure list management information, application list management information, table column information management constituting the table, and table classification code information for systematic management of the table. The relationship information consists of information that manages the association between table information, stored procedures, and application objects. The other information is stored procedure hierarchy information (relationship between the stored procedure and another stored procedure or function called within the stored procedure) for managing the stored procedure called in the stored procedure. Management of table / view usage information (association between tables / views extracted based on the information of individual applications (sub programs or classes) and stored procedures called by individual applications), errors in stored procedures, and Test information management to evaluate performance performance, various history management changed during the development of stored procedures (created, modified, deleted history of stored procedures and creation, modification, deleted information of tables called by stored procedures, and It refers to the creation, modification, and deletion of information such as the requirements of the developer.), The development team / user and the purpose of use of the stored procedure management It is composed of bulletin boards for the purpose of calling and using stored procedures from the developer through the web), processing the questions of each team developer, and notifying table schema / stored procedure changes (addition, modification, and deletion). do.

The table list management information and the stored procedure list management information among the standard information are periodically updated using the system object, that is, meta information, of the large-scale database management system. If the meta information changes at a specific point in time, only the changed meta information is retrieved from the large database after a certain period has elapsed and reflected in the reference information of the DB management table. In addition, the application list management information among the reference information is stored in a specific directory to be applied to the application object used in the entire development project to be reflected in the reference information of the DB management table.                     

The relationship information stores mutual information between information (table information, stored procedure information, application information) stored in the reference information. For example, a table managing a relationship between a stored procedure and a table inquires the body of the stored procedure and stores relationship information with the table information constituting the stored procedure. Stored procedures are a kind of program that prevents users from directly accessing tables, views, etc., and delivers the same information to the application by programming them regardless of table or view changes. Since these stored procedures are easy to change at the time of the user's request, they show the table information constituting the stored procedure in real time, so that related stored procedures can be easily made in the event of column addition or attribute change of the table in the future. Inquire and modify it. The internal contents of the stored procedure are scanned for these changes from time to time, and the real-time table information and attribute information related to the stored procedure are always displayed.

For other information, hierarchy information management of stored procedure, systematic management of related table / view information by querying application body, test sentence information and test result information of stored procedure, request / modification / result of stored procedure via web It consists of change history management composed of notifications. Hierarchical information management performs hierarchical information management because it calls another stored procedure or user-defined function within one stored procedure.

The table / view list management information includes the table / view identifier (ID) field, the name field, the delimiter (table or view) field, the table classification code field, the Korean name field, the table description field, the table hierarchy information field, and the table deletion field. Include. Here, the table classification code is a value of inputting the numbering value managed in the table specification.

The table column information management information includes a column unique number (CID) field, a meta object ID (OID) field, a table name field, a column English name field, a column Korean name field, a column type (attribute) field, a length field, and a description field. Here, the column unique number is a value that increases with the serial number, and the meta object ID (OID) is a serial number managed by a large database. The column Korean name, column attribute, length, and description are configured by the database access module by directly searching the metadata of the large database.

The stored procedure list management information includes the stored procedure identification number (ID) field, stored procedure name field, stored procedure type field, stored procedure creation date field, stored procedure request team field, stored procedure description field, and application. It contains the connection class name field, stored procedure alias field, table / view name field, tuning status separator field, and tuning related comparison field. Stored procedure names allow developers to find stored procedures to call when inputting, outputting, or querying tables in a database. The stored procedure request team refers to the development team that requested the stored procedure for the first time, and the stored procedure description describes the Hangul name of the stored procedure to help the developer understand. Application connection class and stored procedure alias are the information used in the application configuration file, and whether the tuning or not delimiter can degrade the performance of large database if the development of stored procedure is insufficient. Record whether or not.

The stored procedure table / view relationship information includes a corresponding relationship information unique number (ID) field, a stored procedure name field, and a table / view name field. Reads a stored procedure program in a large database and records all tables associated with that stored procedure. Based on this information, you can query all tables in a specific stored procedure and all stored procedures related to a specific table.

The relationship information between the stored procedures includes a corresponding relationship information unique number (ID) field, a stored procedure name field, and a lower stored procedure name field. Record any other stored procedure or user-defined function information contained within the stored procedure. It searches the contents of the stored procedure body, searches for names starting with a specific name (UP_ or UF_), and stores the retrieved information in the DB management table. In the case of a complex stored procedure, the hierarchical structure information between the stored procedures and the hierarchical structure information between the stored procedure and the table can be constructed based on the relationship information between the stored procedures.

3 is a flowchart illustrating a method of managing a schema and a stored procedure real time of a large database in the database access module according to the present invention.

The database access module operates at a predetermined operation cycle. When the operation cycle is reached, meta information of the large database is taken and the meta information of the large database stored in the DB management table is compared (S31 and S32). In this case, the meta information stored in the DB management table means meta information about reference information, relationship information, and other information. If the meta information stored in the DB management table is different from the meta information of the large database, that is, there is a difference (S33), the difference is reflected in the DB management table and updated (S34). The difference update information includes the relationship information between the table / view and the stored procedure, and the table change history information. If there is no difference between the DB management table and the large database (S33), the information stored in the DB management table is maintained.

When the information stored in the DB management table is current, the thread that executes the process is stopped (S35), and waiting until the operation cycle is completed, and when the operation cycle is completed (S36), it is executed again from step S31. .

The web service provider 14 retrieves the information requested by the worker from the DB management table 13 when a query for management information of the schema and stored procedure of a large database is requested from the worker client system 16 through the Internet 15. To the worker client system 16.

Although the technical spirit of the present invention has been described above with reference to the accompanying drawings, it is intended to exemplarily describe the best embodiment of the present invention, but not to limit the present invention. In addition, it is obvious that any person skilled in the art may make various modifications and imitations without departing from the scope of the technical idea of the present invention.

As described above, according to the present invention, there is an effect of automatically presenting information on a schema, stored procedures, and applications of a large database in real time, and allowing a worker to search at any time through the web.

Claims (5)

A DB management table that takes meta information from a large database and manages information about a schema and stored procedures of the large database; When meta data is obtained from the large database by accessing the large database and comparing the meta information stored in the DB management table does not match, the schema and the stored procedure configuring the large database are being added, modified or deleted. A database connection module for determining that at least one is performed and changing the schema and stored procedures to the DB management table; And Web service providing unit for providing information on the schema and stored procedures of the large database stored in the DB management table on the web screen over the Internet Schema and stored procedure real-time management system of a large database comprising a. The method according to claim 1, wherein the DB management table stores reference information, relationship information between the reference information, and other information. The reference information includes table / view list management information for storing schema and view information, stored procedure list management information for storing stored procedure information, and application list management for storing application object information. Table column information management for storing information about the columns constituting the schema, table classification code information for managing the schema, The relationship information is composed of interrelation information between table information, stored procedure information, and application information stored in the reference information. The other information includes information for managing stored procedures called in stored procedures, test information management for evaluating errors and performance of stored procedures, stored procedure use development team / user information, and questions of each team developer. Schema and stored procedure real-time management system of a large database, characterized by consisting of bulletin board for notification of processing and table schema / stored procedure changes. The system of claim 2, wherein the table / view list management information and the stored procedure list management information are periodically updated using meta information of the large database. At least one of adding, modifying, or deleting a schema and a stored procedure configuring a large database is performed, and a database access module that receives the changed information in real time and manages the DB management table, and the large database stored in the DB management table. In the real-time management method of the schema and stored procedures of a large database in a management system having a web service provider that provides information on the schema and stored procedures of the user to the web screen through the Internet, A first step of the database access module comparing the meta information of the mass database with the meta information stored in the DB management table; As a result of the comparison, if the meta information stored in the DB management table and the meta information received from the large database do not match, the difference between the meta information stored in the DB management table and the meta information received from the large database is determined. A second step of updating the DB management table and proceeding to the next step if there is no difference; A third step of stopping a thread after performing the second step, waiting for an operation period to be completed, and performing the operation again from the first step when the operation period is completed; If the worker is requested to query the management information of the schema and stored procedures of the large database through the Internet, the web service provider includes a fourth step of retrieving the information requested by the worker from the DB management table to the worker client system Schema and stored procedure real-time management method of a large database characterized in that. The method of claim 4, wherein the DB management table stores reference information, relationship information between the reference information, and other information, The reference information includes table / view list management information for storing schema and view information, stored procedure list management information for storing stored procedure information, and application list management for storing application object information. Table column information management for storing information about the columns constituting the schema, table classification code information for managing the schema, The relationship information is composed of interrelation information between table information, stored procedure information, and application information stored in the reference information. The other information includes information for managing stored procedures called in stored procedures, test information management for evaluating errors and performance of stored procedures, stored procedure use development team / user information, and questions of each team developer. Schema and stored procedure real-time management method of a large database, characterized by consisting of bulletin board for processing and notification of table schema / stored procedure changes.

Images