CN114064601A

CN114064601A - Storage process conversion method, device, equipment and storage medium

Info

Publication number: CN114064601A
Application number: CN202010576991.3A
Authority: CN
Inventors: 李强周
Original assignee: Jinzhuan Xinke Co Ltd
Current assignee: Jinzhuan Xinke Co Ltd
Priority date: 2020-06-22
Filing date: 2020-06-22
Publication date: 2022-02-18
Anticipated expiration: 2040-06-22
Also published as: CN114064601B; WO2021259290A1

Abstract

The application provides a storage process conversion method, a device, equipment and a storage medium, comprising the following steps: acquiring a storage process of a source database; analyzing and translating the storage process to obtain a corresponding grammar block list; and processing the grammar block list to obtain a storage process meeting the requirement of a target database. According to the technical scheme, the labor and time cost in the database migration process are saved, and the migration efficiency in the storage process is improved.

Description

Storage process conversion method, device, equipment and storage medium

Technical Field

The application relates to the technical field of database storage, in particular to a storage process conversion method, a storage process conversion device, storage process conversion equipment and a storage medium.

Background

With the advent of the big data age, the traditional relational database has not been able to meet the demands of many enterprises, so more and more enterprises need to migrate the data stored in the traditional relational database to a big data platform. The Stored Procedure (Stored Procedure) is Stored in a large database system, a group of SQL statement sets for completing specific functions are Stored in the database, recompilation is not needed after secondary compilation, and a user executes the Stored Procedure by specifying the name of the Stored Procedure and giving parameters. Stored procedures are an important object in databases.

The storage process of the database has the characteristics of various grammars and complex logics, and particularly, based on the application of the oracle database, a great number of storage processes are used, even one storage process contains thousands of lines of codes, and a great amount of migration workload can be brought in the migration process of the heterogeneous database.

Currently, database migration is generally finished by considering operations, and each person can only reform about 5-10 pieces per day. Thus, a great deal of labor and time are wasted, and efficiency is low.

Disclosure of Invention

The storage process conversion method, the device, the equipment and the storage medium provided by the application can be used for saving the labor and time cost in the database migration process and improving the migration efficiency of the storage process.

In one aspect, an embodiment of the present application provides a storage process conversion method, including:

acquiring a storage process of a source database;

analyzing and translating the storage process to obtain a corresponding grammar block list;

and processing the grammar block list to obtain a storage process meeting the requirement of a target database.

In a second aspect, an embodiment of the present application provides a stored procedure conversion apparatus, including:

the acquisition module is configured to acquire a storage process of a source database;

the parsing translation module is configured to parse and translate the storage process to obtain a corresponding grammar block list;

and the processing module is configured to process the grammar block list to obtain a storage process meeting the requirement of a target database.

In a third aspect, an embodiment of the present application provides an apparatus, including:

one or more processors;

a memory for storing one or more programs;

when executed by the one or more processors, cause the one or more processors to implement the method as any one of the embodiments provides herein.

In a fourth aspect, the present application provides a storage medium storing a computer program, which when executed by a processor implements the method according to any one of the methods provided in the embodiments of the present application.

According to the storage process conversion method, the device, the equipment and the storage medium provided by the embodiment of the application, the storage process of the source database is obtained; analyzing and translating the storage process to obtain a corresponding grammar block list; and processing the grammar block list to obtain a storage process meeting the requirements of the target database, saving the time cost of migrating the heterogeneous database, saving the learning cost of operation and maintenance personnel of the database for learning grammars in the storage process of the database, and improving the migration accuracy and efficiency in the storage process of the database.

With regard to the above embodiments and other aspects of the present application and implementations thereof, further description is provided in the accompanying drawings description, detailed description and claims.

Drawings

FIG. 1 is a flow chart of a storage procedure transformation method according to an embodiment of the present disclosure;

FIG. 2 is a flowchart of Oracle database storage process transformation provided in an embodiment of the present application;

FIG. 3 is a schematic structural diagram of a lexical parse tree of an Oracle database storage process according to an embodiment of the present application;

FIG. 4 is a schematic structural diagram of a stored procedure conversion apparatus according to an embodiment of the present application;

fig. 5 is a schematic structural diagram of an apparatus provided in an embodiment of the present application.

Detailed Description

To make the objects, technical solutions and advantages of the present application more apparent, embodiments of the present application will be described in detail below with reference to the accompanying drawings. It should be noted that the embodiments and features of the embodiments in the present application may be arbitrarily combined with each other without conflict.

The steps illustrated in the flow charts of the figures may be performed in a computer system such as a set of computer-executable instructions. Also, while a logical order is shown in the flow diagrams, in some cases, the steps shown or described may be performed in an order different than here.

In one embodiment, a stored procedure conversion method is provided, as shown in fig. 1, the stored procedure conversion method provided in the embodiment of the present application mainly includes steps S11, S12, and S13.

And S11, acquiring the storage process of the source database.

And S12, analyzing and translating the storage process to obtain a corresponding grammar block list.

And S13, processing the grammar block list to obtain a storage process meeting the requirement of the target database.

In this embodiment, the Stored Procedure is a computer term, corresponds to a code, is a set of SQL statements for completing a specific function, and is a program written by using the Transact-SQL language provided by the SQL Server. Wherein, the stored procedure in the source database may be one or more.

In the implementation process, the stored procedures of the source database are parsed, and before parsing, it is required to determine which database vendor's SQL syntax rules are adopted by the source database, for example: the source database may be a conventional database written using the Sql language for storing procedures, e.g., the ORACLE database; the target database may be a database written using the Hql language for stored procedures, such as Hive.

For example: the storage process of the source database is written by using Sql language, and the storage process meeting the requirement of the target database is written by Hql language.

In a specific implementation process, before the storage process of the source database is acquired, the storage process is created according to the JDBC protocol, and after the creation of the storage process is completed, a call may be initiated to the storage process. Initiating a call to a stored procedure may be the stored procedure loader loading the source database for the stored procedure.

Further, two ways of obtaining the storage process of the source database are provided.

In one embodiment, the process of obtaining the storage of the source database includes: and connecting the source database on line, and exporting the storage process of the source database.

And when the storage process loader is in an online mode, directly connecting the source database and exporting the storage process of the source database.

For example: when the stored procedure loader is in an online mode, the stored procedure loader is directly connected with the ORACLE database, and the stored procedure is exported from the ORACLE database.

In one embodiment, the process of obtaining the storage of the source database includes: reading a database schema Definition Language (ddl) file of the source database offline; and splitting the ddl file into a storage process of the source database.

Further, the retrieval of the stored procedures is accomplished using a stored procedure loader. The stored procedure loader supports both offline and online modules.

And when the storage process loader is in an offline mode, reading the ddl file and splitting the ddl file into storage processes.

For example: storing ddl files in a source database is a language that is used to describe real world entities to be stored in the database. Definition of database schema it is common to describe text definition schema through data description language ddL. That is, relevant contents of the storage process are encapsulated in the ddl file, and the storage process of the source database can be obtained by splitting the contents.

In one embodiment, the parsing and translating the stored procedure to obtain a corresponding list of syntax blocks includes: analyzing the storage process based on the grammatical structure of the storage process to generate an analyzed lexical tree; and for each grammar block in the lexical tree, translating in different database languages to obtain a corresponding grammar block list.

In this embodiment, the stored procedures correspond to different syntax structures based on the different programming languages used by the source database. And determining a grammatical structure in the source database, analyzing the storage process based on the determined grammatical structure, and generating a corresponding lexical tree. For example: definition module, grammar set, etc.

And after the parsed lexical tree is generated, translating each grammar block in the lexical tree by adopting different database languages to obtain a grammar block list corresponding to each grammar block. The grammar block list includes grammar blocks written in each database language.

In one embodiment, the parsing the stored procedure to generate a parsed lexical tree includes: analyzing each entry in the storage process; generating a statement part and a process body part of the storage process based on each parsed entry; and respectively analyzing the statement part and the process body part to generate an analyzed lexical tree.

The process body portion of the stored process is parsed. The process body part comprises a declaration part and a grammar part, wherein the declaration part is directly analyzed, and the grammar part comprises a single statement and a grammar block. The single grammar includes the parts of parameter definition, insert, delete, update, select. The grammar blocks comprise loop grammar blocks, case grammar blocks, while grammar blocks, if grammar blocks and the like, and the grammar blocks can be nested and combined to form new grammar blocks, so that lexical analysis is required to be circularly carried out until grammar block end marks appear.

In one embodiment, the declaration section includes storing a process name and a parameter list, wherein the parameter list includes one or more of: parameter type, parameter name, data type of the parameter.

In one embodiment, the procedural body portion includes a second declarative portion and a grammar portion, the grammar portion including a single statement and grammar blocks.

In one embodiment, the processing the list of syntax blocks to obtain a stored procedure that meets the requirement of the target database includes: determining a database type of a target database; obtaining a grammar block corresponding to the database type from the grammar block list; and combining the grammar blocks corresponding to the database types to obtain a storage process meeting the requirements of a target database.

In this embodiment, a target database type is determined, a language for writing a database is determined based on the database type, and a grammar block corresponding to the language is obtained. And combining the obtained language blocks to obtain a storage process meeting the requirement of the target database.

In one embodiment, migration of the Oracle database storage process to the MySQL database is taken as an example for explanation.

At present, MySQL databases are widely applied, and various databases based on MySQL grammar are also numerous. Migration of the storage process of the Oracle database to the MySQL database is a very widely used case.

As shown in fig. 2, the step of migrating the Oracle database storage process to the MySQL database mainly includes:

1. the storage process loader loads the Oracle database storage process.

As shown in fig. 3, loading the storage process, adding lexical analysis streams, can read the entries at once. For example, the term "create or place test" can be analyzed to be the term "create", the second term "or", the third term "place", the fourth term "test" as the storage process name, and the term analysis can identify a plurality of spaces.

2. And the lexical analyzer analyzes the storage process of the Oracle database into a corresponding lexical tree.

As shown in fig. 3, lexical parsing needs to parse information of a first declaration (declaration) part, where the first declaration part is in a special format and includes three dimensions, namely, a stored process name (name) and a parameter list (param list), where the param list includes a parameter name (param name), a parameter type (param type), and an input/output (in/out).

The lexical parser parses a process body (body) part, wherein the body part also comprises a second detail part and a statement set (statement), the detail part is a parameter list, and the parameter list comprises three dimensions of a parameter name (param name), a parameter type (param type) and a default value (default value). The single grammar includes the parts of parameter definition, insert, delete, update, select. The syntax block includes, for example, a loop syntax block, a case syntax block, a while syntax block, an if syntax block, a set syntax block, an exit syntax block, and the like.

3. The type that the grammar translator obtains currently needing translation is MySQL.

Each grammar type corresponds to the writing operation of different databases, such as definition statements:

4. the stored procedures generator will translate the grammar output into MySQL stored procedures.

And the storage process generator generates a corresponding storage process grammar frame according to the current database type, selects a corresponding grammar structure of the status for splicing, and finally generates the converted storage process grammar.

In an embodiment, a stored procedure conversion method is provided, and as shown in fig. 4, a stored procedure conversion apparatus provided in an embodiment of the present application mainly includes an obtaining module 41, an analysis translation module 42, and a processing module 43.

The obtaining module 41 is configured to obtain a storage procedure of the source database;

the parsing and translating module 42 is configured to parse and translate the stored procedures to obtain a corresponding grammar block list;

and the processing module 43 is configured to process the grammar block list to obtain a storage process meeting the requirement of the target database.

In one embodiment, the process of obtaining the storage of the source database includes: reading a database schema definition language ddl file of the source database offline; and splitting the ddl file into a storage process of the source database.

In one embodiment, the parsing and translating the stored procedure to obtain a corresponding list of syntax blocks includes: analyzing the storage process based on the grammatical structure of the storage process to generate an analyzed lexical tree; and for each grammar block in the grammar block list, translating in different database languages to obtain a corresponding grammar block list.

The storage process conversion device provided by the embodiment can execute the storage process conversion method provided by any embodiment of the invention, and has corresponding functional modules and beneficial effects for executing the method. For details of the storage procedure conversion method provided in any embodiment of the present invention, reference may be made to the technical details not described in detail in this embodiment.

It should be noted that, in the embodiment of the storage process conversion apparatus, the included units and modules are only divided according to functional logic, but are not limited to the above division as long as the corresponding functions can be implemented; in addition, specific names of the functional units are only used for distinguishing one functional unit from another, and are not used for limiting the protection scope of the application.

Fig. 5 is a schematic structural diagram of an apparatus provided in the embodiment of the present application, and as shown in fig. 5, the apparatus includes a processor 51, a memory 52, an input device 53, an output device 54, and a communication device 55; the number of the processors 51 in the device may be one or more, and one processor 51 is taken as an example in fig. 5; the processor 51, the memory 52, the input device 53 and the output device 54 in the apparatus may be connected by a bus or other means, which is exemplified in fig. 5.

The memory 52 is a computer-readable storage medium, and can be used for storing software programs, computer-executable programs, and modules, such as program instructions/modules corresponding to the stored procedure conversion method in the embodiment of the present application (for example, the obtaining module 41, the parsing and translation module 42, and the processing module 43 in the stored procedure conversion device). The processor 51 executes various functional applications of the device and data processing by executing software programs, instructions and modules stored in the memory 52, namely, implements any method provided by the embodiments of the present application.

The memory 52 may mainly include a storage program area and a storage data area, wherein the storage program area may store an operating system, an application program required for at least one function; the storage data area may store data created according to use of the device, and the like. Further, the memory 52 may include high speed random access memory, and may also include non-volatile memory, such as at least one magnetic disk storage device, flash memory device, or other non-volatile solid state storage device. In some examples, the memory 52 may further include memory located remotely from the processor 51, which may be connected to the device over a network. Examples of such networks include, but are not limited to, the internet, intranets, local area networks, mobile communication networks, and combinations thereof.

The input device 53 may be used to receive input numeric or character information and generate key signal inputs relating to user settings and function control of the apparatus. The output device 54 may include a display device such as a display screen.

The communication device 55 may include a receiver and a transmitter. The communication device 55 is configured to perform information transceiving communication according to the control of the processor 51.

In an exemplary embodiment, the present application also provides a storage medium containing computer-executable instructions, which when executed by a computer processor, perform a stored procedure transformation method, including;

acquiring a storage process of a source database;

Of course, the storage medium provided in the embodiments of the present application contains computer-executable instructions, and the computer-executable instructions are not limited to the method operations described above, and may also perform related operations in the stored procedure conversion method provided in any embodiment of the present application.

From the above description of the embodiments, it is obvious for those skilled in the art that the present application can be implemented by software and necessary general hardware, and certainly can be implemented by hardware, but the former is a better embodiment in many cases. Based on such understanding, the technical solutions of the present application may be embodied in the form of a software product, which may be stored in a computer-readable storage medium, such as a floppy disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a FLASH Memory (FLASH), a hard disk or an optical disk of a computer, and includes several instructions to enable a computer device (which may be a personal computer, a server, or a network device) to execute the storage process conversion method according to the embodiments of the present application.

The above description is only exemplary embodiments of the present application, and is not intended to limit the scope of the present application.

It will be clear to a person skilled in the art that the term user terminal covers any suitable type of wireless user equipment, such as a mobile phone, a portable data processing device, a portable web browser or a car mounted mobile station.

In general, the various embodiments of the application may be implemented in hardware or special purpose circuits, software, logic or any combination thereof. For example, some aspects may be implemented in hardware, while other aspects may be implemented in firmware or software which may be executed by a controller, microprocessor or other computing device, although the application is not limited thereto.

Embodiments of the application may be implemented by a data processor of a mobile device executing computer program instructions, for example in a processor entity, or by hardware, or by a combination of software and hardware. The computer program instructions may be assembly instructions, Instruction Set Architecture (ISA) instructions, machine related instructions, microcode, firmware instructions, state setting data, or source code or object code written in any combination of one or more programming languages.

Any logic flow block diagrams in the figures of this application may represent program steps, or may represent interconnected logic circuits, modules, and functions, or may represent a combination of program steps and logic circuits, modules, and functions. The computer program may be stored on a memory. The memory may be of any type suitable to the local technical environment and may be implemented using any suitable data storage technology, such as, but not limited to, Read Only Memory (ROM), Random Access Memory (RAM), optical storage devices and systems (digital versatile disks, DVDs, or CD discs), etc. The computer readable medium may include a non-transitory storage medium. The data processor may be of any type suitable to the local technical environment, such as but not limited to general purpose computers, special purpose computers, microprocessors, Digital Signal Processors (DSPs), Application Specific Integrated Circuits (ASICs), programmable logic devices (FGPAs), and processors based on a multi-core processor architecture.

The foregoing has provided by way of exemplary and non-limiting examples a detailed description of exemplary embodiments of the present application. Various modifications and adaptations to the foregoing embodiments may become apparent to those skilled in the relevant arts in view of the following drawings and the appended claims without departing from the scope of the invention. Therefore, the proper scope of the invention is to be determined according to the claims.

Claims

1. A stored procedure translation method, comprising:

acquiring a storage process of a source database;

2. The method according to claim 1, wherein the obtaining of the storage procedure of the source database includes one of the following methods:

connecting the source database on line, and exporting a storage process of the source database;

and reading the database mode definition language ddl file of the source database in an off-line manner, and splitting the ddl file into a storage process of the source database.

3. The method of claim 1, wherein parsing and translating the stored procedure to obtain a corresponding list of grammar blocks comprises:

analyzing the storage process based on the grammatical structure of the storage process to generate an analyzed lexical tree;

and for each grammar block of the lexical tree, translating in different database languages to obtain a corresponding grammar block list.

4. The method of claim 3, wherein parsing the stored procedure to generate a parsed lexical tree comprises:

analyzing each entry in the storage process;

generating a statement part and a process body part of the storage process based on each parsed entry;

and respectively analyzing the statement part and the process body part to generate an analyzed lexical tree.

5. The method of claim 4, wherein the declaration section includes storing a process name and a parameter list, wherein the parameter list includes one or more of: parameter type, parameter name, data type of the parameter.

6. The method of claim 4, wherein the procedural body portion comprises a second declarative portion and a grammar portion, the grammar portion comprising a single statement and grammar blocks.

7. The method of claim 1, wherein the processing the list of grammar blocks to obtain a stored procedure that meets the requirements of a target database comprises:

determining a database type of a target database;

obtaining a grammar block corresponding to the database type from the grammar block list;

and combining the grammar blocks corresponding to the database types to obtain a storage process meeting the requirements of a target database.

8. A stored procedure conversion apparatus, comprising:

9. An apparatus, comprising:

one or more processors;

a memory 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-7.

10. A storage medium, characterized in that the storage medium stores a computer program which, when executed by a processor, implements the method of any one of claims 1-7.