CN112445781A

CN112445781A - Method and device for managing data base DDL

Info

Publication number: CN112445781A
Application number: CN202011384391.3A
Authority: CN
Inventors: 涂霖英
Original assignee: CCB Finetech Co Ltd
Current assignee: CCB Finetech Co Ltd
Priority date: 2020-11-30
Filing date: 2020-11-30
Publication date: 2021-03-05

Abstract

The invention discloses a method and a device for managing database DDL, relating to the technical field of computers. One embodiment of the method comprises: obtaining a first DDL of a target component in a data dictionary at a first time; comparing the first DDL obtained at the first moment with a second DDL of the target component at a second moment; and generating version change information of the target component according to the comparison result. The implementation mode overcomes the technical problems of omission, inconvenience and the like caused by manual management of DDL, and further achieves the technical effect of automatic and accurate tracking of version management.

Description

Method and device for managing data base DDL

Technical Field

The invention relates to the technical field of computers, in particular to a method and a device for managing a database DDL.

Background

A relational database is a software system widely used in the IT construction process of technical personnel. Technicians create various tables, views, functions, stored procedures, etc. in a relational database for storing various data assets. The database schema Definition language ddl (data Definition language) generally includes statements to create tables, views, functions, and the like. In a large project, it is important to perform effective version management and tracking on the creation, modification and deletion actions of the DDLs. Better version management can enable a user to actively know the change of the related codes, and better project management can be carried out.

The following common schemes are used for version management of DDL of a relational database, and in the process of implementing the present invention, the inventors find that at least the following problems exist in the prior art:

1) and the developers add the DDL into version management tools such as Git, SVN and the like for management and maintenance.

By adopting the scheme, a technician completes the creation of the table, the view and the storage process through the database, then exports the DDL of the target assemblies through the database export tool, and uploads the Git and the SVN. Version management is performed by means of Git and SVN. The scheme requires developers to actively export DDL and actively upload to Git and SVN. If the developer forgets to export the DDL and uploads the Git and the SVN, the related target components in the database may have undergone multiple rounds of version changes, and the project manager cannot know the related version change information of the database, thereby resulting in information omission.

2) And performing database version management through open source tools such as Liquibase, Flyway and the like.

By adopting the scheme, technicians use open source tools such as liquid, Flyway and the like, developers need to maintain related DDLs through XML configuration files, and the creation of the table and the view is completed by actively triggering the execution button. The creation of database tables, views, depends strongly on the XML configuration file. The open source tool does not support the maintenance of the stored procedures and is not very friendly to enterprises still using more stored procedures.

Disclosure of Invention

In view of this, embodiments of the present invention provide a method for managing a database DDL, which can automatically identify a DDL version change by using a data dictionary of a relational database, analyze the version change, generate a version change profile and details, and then actively push DDL version evolution data.

To achieve the above object, according to an aspect of an embodiment of the present invention, a method for database DDL management is provided.

The method for managing the database DDL according to one aspect of the embodiment of the invention comprises the following steps:

obtaining a first DDL of a target component in a data dictionary at a first time;

comparing the first DDL obtained at the first time with a second DDL of the target component at a second time; and

and generating version change information of the target component according to the comparison result.

According to an aspect of the embodiment of the invention, the method for managing the database DDL comprises the following steps:

determining a target component among the one or more components associated with the data dictionary;

obtaining a first DDL associated with the target component;

storing the first DDL.

According to the method for managing the database DDL, the first DDL is stored in a specified directory in a text form.

According to an aspect of the embodiment of the present invention, the method for managing database DDL, wherein the first time is later than the second time.

The method for managing the database DDL according to one aspect of the embodiment of the present invention further includes, according to a scheduling rule, executing the obtaining of the first DDL of the target component in the data dictionary.

The method for managing the database DDL according to the embodiment of the invention comprises the following steps of:

presetting a scheduling task in a configuration file;

scheduling periodically according to a preset time interval;

scheduling is performed based on user input.

According to an aspect of the embodiment of the invention, the method of database DDL management, wherein the target component comprises at least one or more of the following:

a database table;

a database view;

the database stores the procedures.

According to an aspect of the embodiment of the invention, the method for managing the database DDL comprises at least one or more of the following steps:

newly adding;

deleting;

adjusting the format;

and (5) modifying the version.

The method for managing the database DDL according to one aspect of the embodiment of the invention further comprises the following steps:

and displaying the version difference of the target component based on the version change information.

and pushing the version change information to a user.

and visually displaying the version change.

To achieve the above object, according to an aspect of an embodiment of the present invention, an apparatus for database DDL management is provided.

An apparatus for database DDL management according to an aspect of an embodiment of the present invention includes:

the acquisition module is used for acquiring a first DDL (distributed data link) of the target component in the data dictionary at a first moment;

a comparison module, configured to compare the first DDL obtained at the first time with a second DDL of the target component at a second time; and

and the analysis module generates version change information of the target component according to the comparison result.

The apparatus for database DDL management according to an aspect of an embodiment of the present invention further includes:

a location module to determine a target component among one or more components associated with the data dictionary.

and the storage module stores the first DDL.

and the scheduling module is used for executing the acquisition of the first DDL of the target component in the data dictionary according to a scheduling rule.

The apparatus for managing database DDL according to an aspect of the embodiment of the present invention, wherein the scheduling rule is any one or more of the following:

presetting a scheduling task in a configuration file;

scheduling periodically according to a preset time interval;

scheduling is performed based on user input.

To achieve the above object, according to an aspect of an embodiment of the present invention, there is provided an electronic device for database DDL management, including:

one or more processors;

a storage device for storing one or more programs,

when executed by the one or more processors, cause the one or more processors to perform the method of any one of the preceding claims.

To achieve the above object, according to one aspect of the embodiments of the present invention, there is provided a computer readable medium for database DDL management, having a computer program stored thereon, wherein the computer program is configured to implement any of the methods described above when executed by a processor.

One embodiment of the above invention has the following advantages or benefits: because the technical means of automatically identifying the DDL version change by means of the data dictionary of the relational database is adopted, the technical problems of omission, inconvenience and the like caused by manual management of DDL are solved, and the technical effect of automatically and accurately tracking version management is achieved. Therefore, project management personnel can conveniently follow up version change conditions such as database tables, views, storage processes and the like, and then can conveniently follow up work and output of related technical personnel. While supporting efficient and easy-to-use version management of such script type code for stored procedures.

Further effects of the above-mentioned non-conventional alternatives will be described below in connection with the embodiments.

Drawings

The drawings are included to provide a better understanding of the invention and are not to be construed as unduly limiting the invention. Wherein:

FIG. 1 is a schematic diagram of a main flow of a method of database DDL management according to an embodiment of the invention;

FIG. 2 is a schematic diagram of the main modules of an apparatus for database DDL management according to an embodiment of the present invention;

FIG. 3 is an exemplary system architecture diagram in which embodiments of the present invention may be employed;

fig. 4 is a schematic block diagram of a computer system suitable for use in implementing a terminal device or server of an embodiment of the invention.

Detailed Description

Exemplary embodiments of the present invention are described below with reference to the accompanying drawings, in which various details of embodiments of the invention are included to assist understanding, and which are to be considered as merely exemplary. Accordingly, those of ordinary skill in the art will recognize that various changes and modifications of the embodiments described herein can be made without departing from the scope and spirit of the invention. Also, descriptions of well-known functions and constructions are omitted in the following description for clarity and conciseness.

Fig. 1 is a basic flowchart of a method of database DDL management according to an embodiment of the present invention, and as shown in fig. 1, the method of database DDL management includes steps S101, S102, and S103.

The term "RDBMS": a Relational Database Management System (RDBMS) refers to a set of programs (Database Management System software) that include interrelated logical organization and access to these data. A relational database management system is a system that manages a relational database and logically organizes data. Common relational database management system products are Oracle, IBM's DB2, and microsoft's SQL Server.

The term "DDL": a database schema Definition language ddl (data Definition language), which is a language for describing real-world entities to be stored in a database. Common DDL statements include creating database tables, views, storing procedures, and the like.

The term "version management": in order to meet different requirements, the change conditions of a product or system series generated by local improvement and modification of the same product or system are recorded, tracked, maintained and controlled.

The term "data dictionary": data of the management data in the relational database. Such as user _ tables in Oracle, stores the relevant information of the table of the current user, including table name, status, table row number, etc.

The term "storage procedure": a set of SQL statements to perform a specific function.

Step S101: a first DDL of a target component is obtained in a data dictionary at a first time.

The version management of the database may be database version management for the whole large project, or database version management for a certain component. In one embodiment, to manage DDL version changes for, for example, component A needs to be located first. The components may include tables, views, stored procedures, and the like. In one embodiment, component a may be determined from one or more components described in the data dictionary with the aid of the data dictionary. For example, it can be implemented with resource locator:

resource locator: resource identification name

For locating a particular one of the target components (table, view, stored procedure, etc.), e.g., component a. For example, "127.0.0.1: 1521: orcl: scott: table: emp" denotes 127.0.0.1 this ip, emp table in scott library of instances of port 1521.

In this example, the emp table is the target component a to be version change tracked.

Acquiring the first DDL of the target component may be performed in response to a variety of scheduling manners. For example, in one embodiment, the scheduling tasks may be read from the configuration file in a configurable manner, and each time a task is scheduled, DDL collection of the target component is performed. In another embodiment, the DDL acquisition may be performed at a predetermined timing period or predetermined time, with a predetermined period or predetermined time. Or may be performed at the request of the user in response to user input. The above modes can adopt one mode or adopt the modes simultaneously. For example, in addition to weekly acquisitions, acquisitions may be performed at specific times in response to scheduled task acquisitions, and may additionally be requested by a user as needed.

The version of the database is time-dependent as the database is operated and updated. The version may change over time, so each acquired DDL should be associated with the current acquisition time. For example, to distinguish from the historical DDLs or DDLs obtained at other times, we refer to the DDL obtained at the first time by the above steps as the first DDL. The obtained first DDL may be stored in various ways for subsequent use. Preferably, the text can be stored in the operating system specific folder store. For example, the related file directory storage can be designed according to the aforementioned resource locator plus a timestamp. The text form storage can accelerate the reading speed and reduce the occupation of system resources. The retrieved first DDL may also be stored in a database or any other manner as desired.

Step S102: comparing the first DDL obtained at the first time with a second DDL of the target component at a second time.

The DDL used for comparison with the first DDL may be any time, as desired. For example, it may be a DDL of a certain standard version stored in history, or may be a DDL obtained last time. For convenience of description, we may refer to the DDL used for comparison with the first DDL as the second DDL, which may also be referred to as the reference DDL. The second DDL is obtained at a second time different from the first time, wherein the second time may be any time earlier than the first time. For example, a first DDL is obtained at 12 o 'clock (first time) on day 1/month 2, and this first DDL is compared with a second DDL obtained at 12 o' clock (second time) on day 1/month, which is the previous version.

In one example, in connection with step S101, an original DDL may be set as a reference DDL. Then whatever way the acquired first DDL is scheduled may be compared to the reference DDL to learn the change in the current DDL version.

In another example, a standard version of DDL may be set as the reference DDL. The DDL of the standard version may be the DDL of the target component under a certain reference version of the entire system, or may be the DDL of a certain reference version of the target component. The first DDL obtained in step S101 is compared with the reference DDL to learn the current variation.

In yet another example, the DDL acquired one or several times before may be set as the reference DDL. For example, the obtained DDL is scheduled at a second time instant earlier than the first time instant. Then comparing the first DDL to the reference DDL, it can be known that the version of the target component has changed from the last time.

Step S103: and generating version change information of the target component according to the comparison result.

In one embodiment, the version change types are as follows:

1) newly adding: the DDL is the first occurrence

2) And (3) deleting: the DDL is deleted

3) And (3) format adjustment: the DDL is only modified in format, such as a certain segment of annotation in the storage process is wrapped

4) And (3) version modification: modification changes other than formatting changes, called versioning, such as data manipulation statements in the stored procedure from "from table 1" to "from table 2".

It should be noted that the above version change types are only examples, and any other version change types may also be implemented as needed, and do not constitute a limitation on the embodiments of the present invention.

Fig. 2 is a main block diagram of an apparatus for database DDL management according to an embodiment of the present invention, and as shown in fig. 2, the apparatus for database DDL management mainly includes

modules

201, 202, 203.

Module 201 obtains a module for obtaining a first DDL of a target component in a data dictionary at a first time.

Specifically, the obtaining module 201 collects relevant DDLs through a data dictionary of the RDBMS. The obtained DDL is stored in the operating system designated folder by the storage module in text form (the related file directory can be designed according to the aforementioned resource locator and the timestamp).

Module 202 a comparing module for comparing the first DDL obtained at the first time with a second DDL of the target component at a second time

The module 203 is a version module, configured to generate version change information of the target component according to the comparison result.

In parallel with the obtaining module 201, or as a sub-module of the obtaining module 201, a positioning module may be further included, configured to determine a target component among one or more components associated with the data dictionary.

In parallel with the obtaining module 201, or as a sub-module of the obtaining module 201, a storage module may be further included, configured to store the first DDL. In one embodiment, the text may be stored in the operating system specific folder.

Furthermore, the apparatus for database DDL management may further comprise at least the following modules:

a scheduling module: the first DDL used for obtaining the target component in the data dictionary is executed according to a scheduling rule. As described in the previous method embodiments, the scheduling rule may be, but is not limited to, one or more of the following: presetting a scheduling task in a configuration file, reading the scheduling task from the configuration file, and performing version change acquisition and subsequent analysis each time the task is scheduled; scheduling periodically according to a preset time interval; scheduling is performed based on user input.

An analysis module: and generating version change information by analyzing the difference between the DDL acquired this time and a previous version or a certain historical version in the version library.

A difference display module: the module is mainly used for front-end display and is used for comparing difference details among different versions of related target parts by a user.

The visual management analysis module: a user can check the version change scale, the past code change quantity and the like through the visualization module, so that the related output of the project can be conveniently followed.

The message pushing module: the module is used for pushing data, and pushing the obtained version change information to a user through short messages, mails and the like, so that the user is informed of the relevant version change information.

It should be noted that the above description of the apparatus for managing database DDL according to the embodiment of the present invention is only an example, and the apparatus for managing database DDL according to the embodiment of the present invention can implement any method in the foregoing method embodiments, and is not described herein again.

Fig. 3 illustrates an exemplary system architecture 300 of a method of database DDL management or an apparatus of database DDL management to which embodiments of the present invention may be applied.

As shown in fig. 3, the system architecture 300 may include

terminal devices

301, 302, 303, a network 304, and a server 305. The network 304 serves as a medium for providing communication links between the

terminal devices

301, 302, 303 and the server 305. Network 304 may include various connection types, such as wired, wireless communication links, or fiber optic cables, to name a few.

The user may use the

terminal device

301, 302, 303 to interact with the server 305 via the network 304 to receive or send messages or the like. The

terminal devices

301, 302, 303 may have installed thereon various client applications, such as a database application, a web browser application, a visualization application, and the like.

The

terminal devices

301, 302, 303 may be various electronic devices having a display screen and supporting web browsing, including but not limited to smart phones, tablet computers, laptop portable computers, desktop computers, and the like.

The server 305 may be a server providing various services, such as a back-office management server (for example only) providing support for database management software browsed by users using the

terminal devices

301, 302, 303. The backend management server may analyze and perform other processing on the received data such as the product information query request, and feed back the processing result (e.g., push information, version information — just an example) to the terminal device.

It should be noted that the method for managing the database DDL provided by the embodiment of the present invention is generally executed by the server 305, and accordingly, the apparatus for managing the database DDL is generally disposed in the server 305.

It should be understood that the number of terminal devices, networks, and servers in fig. 3 is merely illustrative. There may be any number of terminal devices, networks, and servers, as desired for implementation. The system architecture for managing database DDL according to the embodiment of the present invention shown in fig. 3 can implement any one of the foregoing method embodiments, and details are not repeated here.

Referring now to FIG. 4, a block diagram of a computer system 400 suitable for use with a terminal device implementing an embodiment of the invention is shown. The terminal device shown in fig. 4 is only an example, and should not bring any limitation to the functions and the scope of use of the embodiments of the present invention.

As shown in fig. 4, the computer system 400 includes a Central Processing Unit (CPU)401 that can perform various appropriate actions and processes in accordance with a program stored in a Read Only Memory (ROM)402 or a program loaded from a storage section 408 into a Random Access Memory (RAM) 403. In the RAM 403, various programs and data necessary for the operation of the system 400 are also stored. The CPU 401, ROM 402, and RAM 403 are connected to each other via a bus 404. An input/output (I/O) interface 405 is also connected to bus 404.

The following components are connected to the I/O interface 405: an input section 406 including a keyboard, a mouse, and the like; an output section 407 including a display device such as a Cathode Ray Tube (CRT), a Liquid Crystal Display (LCD), and the like, and a speaker; a storage section 408 including a hard disk and the like; and a communication section 409 including a network interface card such as a LAN card, a modem, or the like. The communication section 409 performs communication processing via a network such as the internet. A driver 410 is also connected to the I/O interface 405 as needed. A removable medium 411 such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, or the like is mounted on the drive 410 as necessary, so that a computer program read out therefrom is mounted into the storage section 408 as necessary.

In particular, according to the embodiments of the present disclosure, the processes described above with reference to the flowcharts may be implemented as computer software programs. For example, embodiments of the present disclosure include a computer program product comprising a computer program embodied on a computer readable medium, the computer program comprising program code for performing the method illustrated in the flow chart. In such an embodiment, the computer program may be downloaded and installed from a network through the communication section 409, and/or installed from the removable medium 411. The computer program performs the above-described functions defined in the system of the present invention when executed by a Central Processing Unit (CPU) 401.

It should be noted that the computer readable medium shown in the present invention can be a computer readable signal medium or a computer readable storage medium or any combination of the two. A computer readable storage medium may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the foregoing. More specific examples of the computer readable storage medium may include, but are not limited to: an electrical connection having one or more wires, a portable computer diskette, a hard disk, a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. In the present invention, a computer readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device. In the present invention, however, a computer readable signal medium may include a propagated data signal with computer readable program code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated data signal may take many forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A computer readable signal medium may also be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device. Program code embodied on a computer readable medium may be transmitted using any appropriate medium, including but not limited to: wireless, wire, fiber optic cable, RF, etc., or any suitable combination of the foregoing.

The flowchart and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods and computer program products according to various embodiments of the present invention. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that, in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams or flowchart illustration, and combinations of blocks in the block diagrams or flowchart illustration, can be implemented by special purpose hardware-based systems which perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.

The modules described in the embodiments of the present invention may be implemented by software or hardware. The described modules may also be provided in a processor, which may be described as: a processor comprises an acquisition module, a comparison module and an analysis module. Where the names of these modules do not in some cases constitute a limitation on the module itself, for example, the obtaining module may also be described as a "module for obtaining the first DDL of the target component in the data dictionary at a first time".

As another aspect, the present invention also provides a computer-readable medium that may be contained in the apparatus described in the above embodiments; or may be separate and not incorporated into the device. The computer readable medium carries one or more programs which, when executed by a device, cause the device to:

Wherein obtaining the first DDL of the target component in the data dictionary comprises:

obtaining a first DDL associated with the target component;

storing the first DDL.

Wherein the first DDL is stored in a specified directory in a textual form.

Wherein the first time is later than the second time.

The device can also execute obtaining the first DDL of the target component in the data dictionary according to a scheduling rule.

Wherein the scheduling rule is any one or more of:

presetting a scheduling task in a configuration file;

scheduling periodically according to a preset time interval;

scheduling is performed based on user input.

Wherein the target component comprises at least one or more of:

a database table;

a database view;

the database stores the procedures.

Wherein the DDL alteration includes at least one or more of:

newly adding;

deleting;

adjusting the format;

and (5) modifying the version.

The device can also display the version difference of the target component based on the version change information;

pushing the version change information to a user;

and visually displaying the version change.

According to the technical scheme of the embodiment of the invention, because the technical means of automatically identifying the DDL version change by means of the data dictionary of the relational database is adopted, the technical problems of omission, inconvenience and the like caused by manual management of DDL are solved, and the technical effect of automatically and accurately tracking version management is further achieved. Therefore, project management personnel can conveniently follow up version change conditions such as database tables, views, storage processes and the like, and then can conveniently follow up work and output of related technical personnel. While supporting efficient and easy-to-use version management of such script type code for stored procedures.

The above-described embodiments should not be construed as limiting the scope of the invention. Those skilled in the art will appreciate that various modifications, combinations, sub-combinations, and substitutions can occur, depending on design requirements and other factors. Any modification, equivalent replacement, and improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.

Claims

1. A method for managing a database schema definition language (DDL), comprising:

2. The method of claim 1, wherein obtaining the first DDL for the target component in the data dictionary comprises:

obtaining a first DDL associated with the target component;

storing the first DDL.

3. The method of claim 2, wherein the first DDL is stored in a specified directory in a textual form.

4. The method of claim 1, wherein the first time is later than the second time.

5. The method of claim 1, wherein obtaining the first DDL for the target component in the data dictionary is performed according to a scheduling rule.

6. The method of claim 5, wherein the scheduling rule is any one or more of:

presetting a scheduling task in a configuration file;

scheduling periodically according to a preset time interval;

scheduling is performed based on user input.

7. The method of claim 1, wherein the target component comprises at least one or more of:

a database table;

a database view;

the database stores the procedures.

8. The method of claim 1, wherein the DDL changes comprise at least one or more of:

newly adding;

deleting;

adjusting the format;

and (5) modifying the version.

9. The method of claim 1, further comprising:

10. The method of claim 1, further comprising:

and pushing the version change information to a user.

11. The method of claim 1, further comprising:

and visually displaying the version change.

12. An apparatus for database schema definition language (DDL) management, comprising:

13. The apparatus of claim 12, further comprising:

14. The apparatus of claim 12, further comprising:

and the storage module stores the first DDL.

15. The apparatus of claim 12, further comprising:

16. The apparatus of claim 12, wherein the scheduling rule is any one or more of:

presetting a scheduling task in a configuration file;

scheduling periodically according to a preset time interval;

scheduling is performed based on user input.

17. An electronic device managed by a database schema definition language (DDL), comprising:

one or more processors;

a storage device for storing one or more programs,

when executed by the one or more processors, cause the one or more processors to implement the method of any one of claims 1-11.

18. A computer-readable medium, on which a computer program is stored, which, when being executed by a processor, carries out the method according to any one of claims 1-11.