CN111858645A

CN111858645A - Database object processing method, device, equipment and storage medium

Info

Publication number: CN111858645A
Application number: CN202010655890.5A
Authority: CN
Inventors: 尹淑彬; 杨超
Original assignee: Shanghai Dameng Database Co Ltd
Current assignee: Shanghai Dameng Database Co Ltd
Priority date: 2020-07-09
Filing date: 2020-07-09
Publication date: 2020-10-30

Abstract

The invention discloses a database object processing method, a database object processing device and a database object processing equipment and a storage medium. The method comprises the following steps: acquiring a grammar component of an original database object, a grammar rule of a target database and a corresponding relation between the grammar component of the original database object and the grammar component of the target database object; converting the grammar component of the original database object into the grammar component of the target database object according to the corresponding relation; splicing the grammar components of the target database object into a database statement of the target database object according to the grammar rule of the target database; by executing the database statement, the technical scheme of the invention can convert the compatibility problem of the object into the compatibility problem of each grammar component, and the processing is more flexible and the compatibility is higher.

Description

Database object processing method, device, equipment and storage medium

Technical Field

The embodiment of the invention relates to the technical field of databases, in particular to a database object processing method, a database object processing device and a storage medium.

Background

Migration of objects between heterogeneous databases often involves a compatibility problem, that is, definition statements of objects in an original database cannot be directly executed in a target database, and various corresponding conversions are required to be performed so as to be converted into definition statements of objects corresponding to the target database.

In the prior art, the migration of objects between heterogeneous databases is realized through manual modification, which not only wastes time, but also requires technicians to be skilled in the knowledge related to the databases, and has very high requirements on the technicians.

Disclosure of Invention

The embodiment of the invention provides a database object processing method, a database object processing device and a database object processing storage medium, so that the object migration between heterogeneous databases is automatically carried out, and the processing efficiency is improved.

In a first aspect, an embodiment of the present invention provides a database object processing method, including:

acquiring a grammar component of an original database object, a grammar rule of a target database and a corresponding relation between the grammar component of the original database object and the grammar component of the target database object;

converting the grammar component of the original database object into the grammar component of the target database object according to the corresponding relation;

Splicing the grammar components of the target database object into a database statement of the target database object according to the grammar rule of the target database;

and executing the database statement.

In a second aspect, an embodiment of the present invention further provides a database object processing apparatus, where the apparatus includes:

the system comprises an acquisition module, a storage module and a processing module, wherein the acquisition module is used for acquiring a grammar component of an original database object, a grammar rule of a target database and a corresponding relation between the grammar component of the original database object and the grammar component of the target database object;

the conversion module is used for converting the grammar component of the original database object into the grammar component of the target database object according to the corresponding relation;

the splicing module is used for splicing the grammar component of the target database object into a database statement of the target database object according to the grammar rule of the target database;

and the execution module is used for executing the database statement.

In a third aspect, an embodiment of the present invention further provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, where the processor executes the computer program to implement the method according to any one of the embodiments of the present invention.

In a fourth aspect, the present invention further provides a computer-readable storage medium, on which a computer program is stored, which when executed by a processor implements the method according to any one of the embodiments of the present invention.

The embodiment of the invention obtains the grammar component of the original database object, the grammar rule of the target database and the corresponding relation between the grammar component of the original database object and the grammar component of the target database object; converting the grammar component of the original database object into the grammar component of the target database object according to the corresponding relation; splicing the grammar components of the target database object into a database statement of the target database object according to the grammar rule of the target database; and executing the database statement to obtain the database object which is the same as the original database. The grammar component is the minimum component in the object definition, the compatibility problem of the object is converted into the compatibility problem of each grammar component, the processing is more flexible, and the compatibility is higher.

Drawings

In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings needed to be used in the embodiments will be briefly described below, it should be understood that the following drawings only illustrate some embodiments of the present invention and therefore should not be considered as limiting the scope, and for those skilled in the art, other related drawings can be obtained according to the drawings without inventive efforts.

FIG. 1 is a flowchart of a database object processing method according to a first embodiment of the present invention;

FIG. 1a is a diagram illustrating database object transformation according to a first embodiment of the present invention;

FIG. 1b is a table definition of an oracle database according to the first embodiment of the present invention;

FIG. 1c is a table definition of mysql database in accordance with a first embodiment of the invention;

FIG. 1d is a table definition of a third party database in accordance with a first embodiment of the present invention;

FIG. 1e is a diagram illustrating splitting of an original table in the first embodiment of the present invention;

FIG. 1f is a split view of an original storage process according to a first embodiment of the present invention;

fig. 2 is a schematic structural diagram of a database object processing apparatus according to a second embodiment of the present invention;

fig. 3 is a schematic structural diagram of a computer device in a third embodiment of the present invention.

Detailed Description

The present invention will be described in further detail with reference to the accompanying drawings and examples. It is to be understood that the specific embodiments described herein are merely illustrative of the invention and are not limiting of the invention. It should be further noted that, for the convenience of description, only some of the structures related to the present invention are shown in the drawings, not all of the structures.

It should be noted that: like reference numbers and letters refer to like items in the following figures, and thus, once an item is defined in one figure, it need not be further defined and explained in subsequent figures. Meanwhile, in the description of the present invention, the terms "first", "second", and the like are used only for distinguishing the description, and are not to be construed as indicating or implying relative importance.

Example one

Fig. 1 is a flowchart of a database object processing method according to an embodiment of the present invention, where this embodiment is applicable to a database object processing situation, and the method may be executed by a database object processing apparatus according to an embodiment of the present invention, where the apparatus may be implemented in a software and/or hardware manner, as shown in fig. 1, the method specifically includes the following steps:

s110, obtaining the grammar component of the original database object, the grammar rule of the target database and the corresponding relation between the grammar component of the original database object and the grammar component of the target database object.

The grammar component is a minimum component in the object definition, and may be, for example, a specific component defining an object in an original library:

defining the components of the objects in the destination library:

specifically, a grammar component of the original database object, a grammar rule of the target database and a corresponding relation between the grammar component of the original database object and the grammar component of the target database object are obtained. For example, the original database object a may be composed of a1, a2 and A3, where a1, a2 and A3 are syntax components, the corresponding relationship between the syntax component of the original database object and the syntax component of the destination database object is a1 corresponding to B1, a2 corresponding to B2 and A3 corresponding to B3, and B1, B2 and B3 are syntax components of the destination database object.

Optionally, the original database object includes: view, stored procedure, table, or stored function.

And S120, converting the grammar component of the original database object into the grammar component of the target database object according to the corresponding relation.

Specifically, the method for converting the syntax component of the original database object into the syntax component of the destination database object according to the correspondence relationship may be to create a list in advance, where the list may be a map, store the correspondence relationship between the syntax component of the original database object and the syntax component of the destination database object in the list, obtain the syntax component of the destination database object corresponding to the syntax component of the original database object by querying the list, for example, as shown in fig. 1a, the original database object a may be composed of different syntax components, such as a1, a2, …, An, and convert a1, a2, …, An into the syntax component of the destination database object according to the correspondence relationship: b1, B2, …, Bn.

S130, according to the grammar rule of the target database, the grammar components of the target database object are spliced into a database statement of the target database object.

The grammar rule of the destination database may include a grammar component that must exist in the destination database, and may also include a defined position, which is not limited in the embodiment of the present invention.

S140, executing the database statement.

Specifically, according to the grammar component of the preset object of the original database, and according to the corresponding relationship between the preset grammar component of the original database object and the grammar component of the target database object, each grammar component of the original database object is converted into the grammar component of the target database object, and then the SQL sentences of the target database creation object are spliced according to the grammar rules of the target database. And executing the SQL statement on the target database to obtain a database object which is the same as the original database. For example, as shown in fig. 1a, the original database object a is composed of different syntax components, such as a1, a2, …, An, etc., and a1, a2, …, An is converted into the syntax component of the destination database object according to the correspondence relationship: b1, B2, …, Bn, then according to the destination database syntax rules, make B1, B2, …, Bn the destination database object B.

Optionally, the original database object is a table;

correspondingly, according to the grammar rule of the target database, the grammar components of the target database object are spliced into the database statement of the target database object, and the method comprises the following steps:

and creating a target table through the target statement of the target library to obtain the database statement of the target table, wherein the target statement comprises a creation statement.

Optionally, the destination statement further includes: at least one of a modification statement, an annotation statement, and an authorization statement.

In particular, it is common to know which grammar components each object contains by looking at the grammar rule definitions of the corresponding database. Database objects are tables, views, indexes, stored procedures, stored functions, and the like. The following takes a table as an example to illustrate the migration process of the database object. Generally, a table is composed of different syntax components such as a schema name, a table type, a column, a primary key, a foreign key, an index, a constraint, a comment, a permission, storage information, and other table attributes, wherein the table type can be continuously split into a specific common table, a temporary table, a global temporary table, and the like, and the primary key can be split into a primary key name, a primary key column, an aggregate attribute, and the like. These syntax elements of a table are generally specified when creating a table, but some need to be specified by an action of modifying a table, and the comment information of a table and a column is specified by a comment statement, and the authority information of a table is specified by an authority statement, so that the complete syntax element operations of a table generally include a create statement, an alter statement, a comment statement, and a grant statement. The concrete grammar rules of create statement, alter statement, comment statement and grant statement are different from database to database, but the basic grammar component of the table is roughly consistent. Therefore, if a table to be finally migrated (i.e., an original table) is formed after creating or modifying the table by the create statement, the alter statement, the comment statement, and the grant statement in the original database, each syntax component of the original table is acquired, converted into a corresponding syntax component of the destination table, and then the table is created or modified by the create statement, the alter statement, the comment statement, and the grant statement of the destination database, thereby forming the destination table.

Optionally, converting the syntax component of the original database object into the syntax component of the destination database object according to the correspondence includes:

pre-establishing a list of corresponding relations between grammar components of the original database object and grammar components of the target database object;

querying the list according to the syntactic component of the original database object;

if the grammar component of the target database object matched with the grammar component of the original database object is inquired, acquiring the grammar component of the target database object matched with the grammar component of the original database object and determining the grammar component as a target grammar component;

and converting the grammar component of the original database object into a target grammar component.

Optionally, the method further includes:

and if the grammar component of the target database object matched with the grammar component of the original database object is not inquired, generating warning information and displaying the warning information.

Specifically, there will be differences in the specific column definitions, table constraint definitions, table storage definitions, and table attribute definitions. For the grammar component existing in the original database but not supported in the destination database, the grammar component can be not migrated, and corresponding warning information is given. For those that are not supported in the original database but are supported and necessary in the destination database, default values or manual assignment are generally used.

In a specific example, the interaction of oracle, mysql and a third-party database is taken as an example for explanation, wherein the third-party database is a destination database. As shown in fig. 1b, fig. 1b is a definition of a relational basic table of an oracle database, as shown in fig. 1c, fig. 1c is a definition of a relational basic table of a mysql database, as shown in fig. 1d, and fig. 1d is a definition of a relational basic table of a third-party database. From the grammar definitions created in fig. 1b, 1c, and 1d, the basic components of the table are generally consistent (create, table type, table name (column definition, table constraint definition), ON _ COMMIT store information, table definition), there are few differences in the grammar components, and for migrating from ORACLE to a third party database, the grammar components that exist in ORACLE and are not supported by the third party database (e.g., the supplementary _ logging specified in the ORACLE created table described above, which is currently not supported in the third party database) are not migrated, the grammar components are not migrated, warning information is generated and displayed.

Specifically, the definition of each grammar component is different.

For example, the column for mysql is defined as follows:

the column definitions:

col_name type[[NOT]NULL][DEFAULT default_value]

[AUTO_INCREMENT][UNIQUE[KEY]|[PRIMARY]KEY]

[COMMENT'string'][reference_definition]

and the columns of the third party database are defined as:

The column definitions:

It can be seen that the self-populating creation of mysql uses AUTO _ INCREMENT (default seed value of 1, INCREMENT of 1) and the third party database uses IDENTITY [ (< seed >, < INCREMENT >) ], mysql may specify comments in the tabular statement and the third party database may not, these differences preserving the migration of the grammatical component of the object by splitting and/or adjusting the combination of the destination table components with respect to the original table components.

In another specific example, a table is created in mysql:

CREATE TABLE TEST

(

ID INT PRIMARY KEY AUTO _ INCREMENT COMMENT 'SELF-INCREASING ID',

c1INT COMMENT 'C1 column'

)

Specific splitting as shown in fig. 1e, the table name (TEST), columns (ID INT auto-increment columns (1,1), C1INT), primary key (ID), column comments (ID 'auto-increment ID', C1'C1 column') can be split.

Conversion to the corresponding component of the third party database: table name (TEST), column (ID INT self-increment column (1,1), C1INT), primary key (ID), column comment (ID 'self-increment ID', C1'C1 column').

And (3) according to the grammar splicing of the third-party database, the splicing direction is opposite to that of the upper graph, and the splicing direction is just spliced into the following steps:

CREATE TABLE TEST

(

ID INT identity(1,1)，

C1 INT，

PRIMARY KEY(ID)

)

comment on column test. ID is 'self-increment ID';

comment on column test. C1 is column C1';

as long as the components are compatible, the grammar format compatible with the target database can be finally assembled, and the grammar components of the object are consistent with the original database.

The object splitting method provided by the embodiment of the invention has the advantages that the object which does not contain SQL in the component parts and PL/SQL statements is simple in adaptation and easy to split, such as tables, sequences and the like, the component parts of the objects in each database are simple in compatibility, and the conversion can be completed by direct mapping generally. For objects with views and stored procedures/functions comprising SQL and PL/SQL sentences, the PL/SQL syntax difference of each database is large, particularly PL/SQL is usually a sentence block and comprises a plurality of SQL sentences, and each sentence needs to be split into one sentence first and then is split according to the syntax rule of each sentence. The sentences of the original database correspond to the target database according to the sentence types. For example, the variable declaration of mysql, although defined in different positions, is compatible by statement component split, and the mysql variable statement decode var _ name [,. once. ] type [ DEFAULT value ], corresponds to the variable declaration statement vvar _ name [,. once. ] [ CONSTANT ] type [ NOT NULL ] [ DEFAULT value ] of the third-party database.

For example: create storage procedure in mysql:

as shown in fig. 1f, it can be split into: the process name (T _ PRO ()), variable declarations (name1varchar (64), done INT DEFAULT 0, cur _1 curser select name from demo), statements (open CURSOR (cur _1), loop (CURSOR value, IF decision (LEAVE)), close CURSOR (cur _ 1)).

Conversion to the corresponding component of the third party database: the process name (T _ PRO ()), variable declarations (name1varchar (64), done INT DEFAULT 0, cur _1 curser select name from demo), statements (open CURSOR (cur _1), loop (CURSOR value, IF judge (Exit)), close CURSOR (cur _ 1)).

Storage process of conversion into third party database:

acquiring object information from an original database, and filling each grammar component of an object; mapping the grammar component of the original database object to the grammar component of the corresponding object of the target database to form the corresponding object of the target database; splicing the SQL sentences of the corresponding created objects according to the components of the target database; and executing the SQL statement on the target database to obtain a database object which is the same as the original database.

According to the technical scheme of the embodiment, the grammar component of the original database object, the grammar rule of the target database and the corresponding relation between the grammar component of the original database object and the grammar component of the target database object are obtained; converting the grammar component of the original database object into the grammar component of the target database object according to the corresponding relation; splicing the grammar components of the target database object into a database statement of the target database object according to the grammar rule of the target database; and executing the database statement, and executing the database statement. The grammar component is the minimum component in the object definition, the compatibility problem of the object is converted into the compatibility problem of each grammar component, the processing is more flexible, and the compatibility is higher.

Example two

Fig. 2 is a schematic structural diagram of a database object processing apparatus according to a second embodiment of the present invention. The present embodiment may be applicable to the case of database object processing, where the apparatus may be implemented in a software and/or hardware manner, and the apparatus may be integrated in any device providing a database object processing function, as shown in fig. 2, where the database object processing apparatus specifically includes: an acquisition module 210, a conversion module 220, a concatenation module 230, and an execution module 240.

The obtaining module 210 is configured to obtain a syntax component of an original database object, a syntax rule of a destination database, and a corresponding relationship between the syntax component of the original database object and the syntax component of the destination database object;

a conversion module 220, configured to convert the syntax component of the original database object into a syntax component of a destination database object according to the correspondence;

a splicing module 230, configured to splice syntax components of the destination database object into a database statement of the destination database object according to a syntax rule of the destination database;

and an executing module 240, configured to execute the database statement.

The product can execute the method provided by any embodiment of the invention, and has corresponding functional modules and beneficial effects of the execution method.

EXAMPLE III

Fig. 3 is a schematic structural diagram of a computer device in a third embodiment of the present invention. FIG. 3 illustrates a block diagram of an exemplary computer device 12 suitable for use in implementing embodiments of the present invention. The computer device 12 shown in FIG. 3 is only an example and should not impose any limitation on the scope of use or functionality of embodiments of the present invention.

As shown in FIG. 3, computer device 12 is in the form of a general purpose computing device. The components of computer device 12 may include, but are not limited to: one or more processors or processing units 16, a system memory 28, and a bus 18 that couples various system components including the system memory 28 and the processing unit 16.

Bus 18 represents one or more of any of several types of bus structures, including a memory bus or memory controller, a peripheral bus, an accelerated graphics port, and a processor or local bus using any of a variety of bus architectures. By way of example, such architectures include, but are not limited to, Industry Standard Architecture (ISA) bus, micro-channel architecture (MAC) bus, enhanced ISA bus, Video Electronics Standards Association (VESA) local bus, and Peripheral Component Interconnect (PCI) bus.

Computer device 12 typically includes a variety of computer system readable media. Such media may be any available media that is accessible by computer device 12 and includes both volatile and nonvolatile media, removable and non-removable media.

The system memory 28 may include computer system readable media in the form of volatile memory, such as Random Access Memory (RAM)30 and/or cache memory 32. Computer device 12 may further include other removable/non-removable, volatile/nonvolatile computer system storage media. By way of example only, storage system 34 may be used to read from and write to non-removable, nonvolatile magnetic media (not shown in FIG. 3, and commonly referred to as a "hard drive"). Although not shown in FIG. 3, a magnetic disk drive for reading from and writing to a removable, nonvolatile magnetic disk (e.g., a "floppy disk") and an optical disk drive for reading from or writing to a removable, nonvolatile optical disk (e.g., a CD-ROM, DVD-ROM, or other optical media) may be provided. In these cases, each drive may be connected to bus 18 by one or more data media interfaces. Memory 28 may include at least one program product having a set (e.g., at least one) of program modules that are configured to carry out the functions of embodiments of the invention.

A program/utility 40 having a set (at least one) of program modules 42 may be stored, for example, in memory 28, such program modules 42 including, but not limited to, an operating system, one or more application programs, other program modules, and program data, each of which examples or some combination thereof may comprise an implementation of a network environment. Program modules 42 generally carry out the functions and/or methodologies of the described embodiments of the invention.

Computer device 12 may also communicate with one or more external devices 14 (e.g., keyboard, pointing device, display 24, etc.), with one or more devices that enable a user to interact with computer device 12, and/or with any devices (e.g., network card, modem, etc.) that enable computer device 12 to communicate with one or more other computing devices. Such communication may be through an input/output (I/O) interface 22. In the computer device 12 of the present embodiment, the display 24 is not provided as a separate body but is embedded in the mirror surface, and when the display surface of the display 24 is not displayed, the display surface of the display 24 and the mirror surface are visually integrated. Also, computer device 12 may communicate with one or more networks (e.g., a Local Area Network (LAN), a Wide Area Network (WAN), and/or a public network such as the Internet) via network adapter 20. As shown, network adapter 20 communicates with the other modules of computer device 12 via bus 18. It should be understood that although not shown in the figures, other hardware and/or software modules may be used in conjunction with computer device 12, including but not limited to: microcode, device drivers, redundant processing units, external disk drive arrays, RAID systems, tape drives, and data backup storage systems, among others.

The processing unit 16 executes various functional applications and data processing by executing programs stored in the system memory 28, for example, to implement the database object processing method provided by the embodiment of the present invention:

and executing the database statement.

Example four

A fourth embodiment of the present invention provides a computer-readable storage medium, on which a computer program is stored, where the computer program, when executed by a processor, implements the database object processing method provided in all the embodiments of the present invention:

and executing the database statement.

Any combination of one or more computer-readable media may be employed. The computer readable medium may be a computer readable signal medium or a computer readable storage medium. 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 (a non-exhaustive list) of the computer readable storage medium would include the following: 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 context of this document, 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.

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 any of a variety of 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, wireline, optical fiber cable, RF, etc., or any suitable combination of the foregoing.

Computer program code for carrying out operations for aspects of the present invention may be written in any combination of one or more programming languages, including an object oriented programming language such as Java, Smalltalk, C + + or the like and conventional procedural programming languages, such as the "C" programming language or similar programming languages. The program code may execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or server. In the case of a remote computer, the remote computer may be connected to the user's computer through any type of network, including a Local Area Network (LAN) or a Wide Area Network (WAN), or the connection may be made to an external computer (for example, through the Internet using an Internet service provider).

It is to be noted that the foregoing is only illustrative of the preferred embodiments of the present invention and the technical principles employed. It will be understood by those skilled in the art that the present invention is not limited to the particular embodiments described herein, but is capable of various obvious changes, rearrangements and substitutions as will now become apparent to those skilled in the art without departing from the scope of the invention. Therefore, although the present invention has been described in greater detail by the above embodiments, the present invention is not limited to the above embodiments, and may include other equivalent embodiments without departing from the spirit of the present invention, and the scope of the present invention is determined by the scope of the appended claims.

Claims

1. A database object processing method is characterized by comprising the following steps:

And executing the database statement.

2. The method of claim 1, wherein the original database object comprises: view, stored procedure, table, or stored function.

3. The method of claim 2, wherein the original database object is a table;

4. The method of claim 3, wherein the destination sentence further comprises: at least one of a modification statement, an annotation statement, and an authorization statement.

5. The method of claim 1, wherein converting the grammar component of the original database object to the grammar component of the destination database object according to the correspondence comprises:

6. The method of claim 5, further comprising:

7. A database object processing apparatus, comprising:

and the execution module is used for executing the database statement.

8. The apparatus of claim 7, wherein the raw database object comprises: view, stored procedure, table, or stored function.

9. A computer device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, characterized in that the processor implements the method according to any of claims 1-6 when executing the program.

10. A computer-readable storage 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-6.