CN115757339A - Method for automatically converting Oracle script into Postgresql script - Google Patents
Method for automatically converting Oracle script into Postgresql script Download PDFInfo
- Publication number
- CN115757339A CN115757339A CN202211432623.7A CN202211432623A CN115757339A CN 115757339 A CN115757339 A CN 115757339A CN 202211432623 A CN202211432623 A CN 202211432623A CN 115757339 A CN115757339 A CN 115757339A
- Authority
- CN
- China
- Prior art keywords
- script
- oracle
- database
- postgresql
- analyzer
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000013515 script Methods 0.000 title claims abstract description 66
- 238000000034 method Methods 0.000 title claims abstract description 32
- 238000006243 chemical reaction Methods 0.000 claims abstract description 25
- 230000005012 migration Effects 0.000 claims abstract description 15
- 238000013508 migration Methods 0.000 claims abstract description 14
- 230000006870 function Effects 0.000 claims abstract description 8
- 238000012545 processing Methods 0.000 claims abstract description 6
- 230000008569 process Effects 0.000 description 3
- 230000000694 effects Effects 0.000 description 2
- 238000012795 verification Methods 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 1
- 230000000295 complement effect Effects 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 238000012216 screening Methods 0.000 description 1
- 230000009466 transformation Effects 0.000 description 1
- 239000002699 waste material Substances 0.000 description 1
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a method for automatically converting an Oracle script into a Postgresql script, which comprises the following steps: s1, resolving a database data object definition statement into an application program object by using JAVACC; s2, processing mutual conversion of different database data objects by using the application program; s3, generating database object definition scripts which accord with respective database grammar rules; and S4, completing automatic conversion and migration of the data objects of the heterogeneous database. The method comprises the steps of constructing a JAVACC lexical grammar generator, generating a lexical analyzer and a grammar analyzer for SQL script analysis, analyzing a common SQL script into Java data objects, operating Java objects defined by different data, and realizing the mutual conversion of different database data types, keywords, system functions and system objects by mutual calling and recursive calling among the Java objects.
Description
Technical Field
The invention relates to the technical field of data migration, in particular to a method for automatically converting an Oracle script into a Postgresql script.
Background
There are many considerations to migrate a database from Oracle to PostqreSQL. But it is not changed or the purpose is to ensure that the system function can be used normally after the database is migrated, or the business logic is modified as little as possible (unexpected linkage problem may occur when the business logic is modified). Then, it is necessary to restore the whole structure of the original Oracle database on PostqreSQL as much as possible by a method, so as to ensure that it is not necessary to adjust a large amount of business logic.
Currently, the commonly used methods for migrating Oracle data to the Postgresql database are as follows:
1. and (4) manually migrating. The method is commonly used for migration projects with small engineering and simple data structures, and the principle is that object definition language and data (including objects, tables, triggers, indexes, storage processes, functions, sequences and the like) of an Oracle database are led out and then restored on a target Postgresql database.
2. The ora2pg tool was used. ora2pg is an item of opening software. Most Oracle data can be migrated to the pg database. For data which cannot be migrated, manual screening and manual migration are required.
3. Migrated using commercial tools such as navicat. Typically only table structures and data can be migrated.
The existing scheme of migrating Oracle to Postgresql has the phenomena of time and labor waste (manual migration), high error rate (third-party software migration), incomplete migration of business logic and the like.
An effective solution to the problems in the related art has not been proposed yet.
Disclosure of Invention
Aiming at the problems in the related art, the invention provides a method for automatically converting an Oracle script into a Postgresql script, so as to overcome the technical problems in the prior art.
Therefore, the invention adopts the following specific technical scheme:
the method for automatically converting the Oracle script into the Postgresql script comprises the following steps:
s1, resolving a database data object definition statement into an application program object by using JAVACC;
s2, processing mutual conversion of different database data objects by using the application program;
s3, generating database object definition scripts according with respective database grammar rules;
and S4, completing automatic conversion and migration of the data objects of the heterogeneous database.
Further, the parsing the database data object definition statements into the application object by using JAVACC includes the following steps:
s11, generating a lexical analyzer and a syntax analyzer of the SQL sentence by using JAVACC;
and S12, analyzing the Oracle database data object definition statement into an application program object through a lexical analyzer and a syntax analyzer.
Further, the lexical analyzer and the syntax analyzer for generating the SQL statement by using the JAVACC comprise the following steps of:
s111, loading a grammar rule file with the extension name of jj by JAVACC;
and S112, generating a lexical analyzer and a syntactic analyzer for SQL statement analysis.
Furthermore, the JAVACC supports the generation of a lexical analyzer and a syntactic analyzer of a self-defined lexical and syntactic rule suitable for various heterogeneous database data object definition sentences.
Further, the grammar rule file is a text file with the extension of jj ending, and the grammar rule file comprises attribute setting of a grammar analyzer, class statement of the grammar analyzer, lexical rule statement and grammar rule realization.
Furthermore, the grammar rule file defines the JAVA classes by referring to the data objects to complete the correlation between the data objects and the corresponding JAVA classes.
Further, the Oracle database object definition statement is an SQL statement script defined by the data object.
Further, the parsing the Oracle database data object definition statement into the application object through the lexical analyzer and the syntax analyzer comprises the following steps:
s121, analyzing the SQL statement script defined by the data object by using a lexical analyzer and a syntax analyzer;
and S122, converting the common SQL text statement in the SQL statement script defined by the data object into the JAVA object of the application program.
Further, the mutual conversion for processing different database data objects by using the application program comprises the following steps:
s21, converting the Oracle database object definition statements into application program objects through an SQL (structured query language) analysis engine, and generating Java data objects related to the Oracle data objects;
s22, converting the Java data object associated with the Oracle data object into a Java data object associated with Postgresql;
and S23, converting the Java data object associated with the Postgresql into a Postgresql database data object definition statement.
Further, the script conversion between the heterogeneous databases comprises database data types, keywords, system functions and system data objects.
The invention has the beneficial effects that: the method comprises the steps of generating a lexical analyzer and a syntax analyzer for SQL script analysis by constructing a JAVACC lexical grammar generator, analyzing a common SQL script into Java data objects, then operating Java objects defined by different data, and realizing the interconversion of different database data types, keywords, system functions and system objects through the intercall and recursive call among the Java objects to convert the Java objects into a database statement script recognized and compatible by a target database, namely realizing the effects of high efficiency, low error rate and perfect service logic migration when Oracle data is migrated to Postgresql; meanwhile, the fault tolerance of the conversion process is enhanced by utilizing the automatic conversion of the database script, the conversion time consumption is reduced, and the success rate is improved; and further completing the verification of the conversion result through technical means.
Drawings
In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings needed in the embodiments will be briefly described below, and it is obvious that the drawings in the following description are only some embodiments of the present invention, and it is obvious for those skilled in the art to obtain other drawings without creative efforts.
FIG. 1 is a flowchart of a method for automatically converting an Oracle script into a Postgresql script according to an embodiment of the invention;
FIG. 2 is a flowchart of parsing SQL text into Java classes by JAVACC in the method for automatically converting Oracle script into Postgresql script according to the embodiment of the invention;
fig. 3 is a flowchart of conversion of Oracle data object definition into Postgresql data object definition in the method for automatically converting Oracle script into Postgresql script according to the embodiment of the present invention.
Detailed Description
For further explanation of the various embodiments, the drawings which form a part of the disclosure and which are incorporated in and constitute a part of this specification, illustrate embodiments and, together with the description, serve to explain the principles of operation of the embodiments, and to enable one skilled in the art to understand the embodiments and advantages of the disclosure for reference and without scale, wherein elements are not shown in the drawings and like reference numerals are used to refer to like elements generally.
According to the embodiment of the invention, a method for automatically converting an Oracle script into a Postgresql script is provided.
Referring to the drawings and the detailed description, the invention will be further described, as shown in fig. 1, a method for automatically converting an Oracle script into a Postgresql script according to an embodiment of the invention includes the following steps:
s1, resolving a database data object definition statement into an application program object by using JAVACC;
the JAVACC supports the generation of a self-defined lexical method and grammar rules which are suitable for various heterogeneous type database data object definition sentence lexical analyzers and grammar analyzers.
As shown in fig. 2, the parsing the database data object definition statements into application objects by using JAVACC includes the following steps:
s11, generating a lexical analyzer and a syntax analyzer of the SQL statement by using JAVACC, wherein the lexical analyzer and the syntax analyzer comprise the following steps:
s111, loading a grammar rule file with the extension name of jj by JAVACC;
the grammar rule file is a text file with the extension name of jj ending, and the grammar rule file comprises attribute setting of a grammar analyzer, class statement of the grammar analyzer, lexical rule statement and grammar rule realization.
The JAVACC grammar rule file writing is complementary with the data object definition Java class, and the grammar rule file completes the correlation between the data object and the corresponding JAVA class by referring to the data object definition JAVA class.
And S112, generating a lexical analyzer and a syntactic analyzer for SQL statement analysis.
Jj, if the operation is successful, a file as shown in table 1 will be generated:
table 1: file name and description
And S12, analyzing the Oracle database data object definition sentence into an application program object through a lexical analyzer and a syntax analyzer.
The Oracle database object definition statements are SQL statement scripts defined by the data objects.
The method for analyzing the Oracle database data object definition statements into the application program objects through the lexical analyzer and the syntax analyzer comprises the following steps:
s121, analyzing the SQL statement script defined by the data object by using a lexical analyzer and a syntax analyzer;
and S122, converting the common SQL text statement in the SQL statement script defined by the data object into the JAVA object of the application program.
The generic SQL text is parsed into objects that the application recognizes.
Defining an SQL statement for parsing an Oracle object as follows:
the analysis of the SQL sentences defined by the database objects can be completed through the procedures, and the common SQL text sentences are converted into the Java objects of the application programs, so that a foundation is laid for the mutual conversion of the definitions of the heterogeneous database data objects.
S2, processing the mutual conversion of different database data objects by using the application program,
as shown in fig. 3, the mutual conversion of different database data objects processed by the application program includes the following steps:
s21, converting the Oracle database object definition statements into application program objects through an SQL analysis engine, and generating Java data objects related to the Oracle data objects;
s22, converting the Java data object associated with the Oracle data object into a Java data object associated with Postgresql;
and S23, converting the Java data object associated with the Postgresql into a Postgresql database data object definition statement.
S3, generating database object definition scripts according with respective database grammar rules;
and S4, completing automatic conversion and migration of the data objects of the heterogeneous database.
The migration of the Oracle database object to the Postgresql database is common heterogeneous data object migration, two different types of databases support different lexical and grammatical rules, the definition sentences of various data objects cannot be compatible, and the lexical and sentence data object definition sentences recognized by the Oracle database need to be converted into the lexical and sentence data object definition sentences recognized by the Postgresql database to complete the migration of the Oracle database object to the Postgresql database.
The script conversion between the heterogeneous databases comprises database data types, keywords, system functions and system data objects. Each database object is analyzed by SQL engine to generate an object defined by Java, and the core logic of conversion is completed by mutual calling and recursive calling between Java objects.
In summary, by means of the above technical solution of the present invention, a lexical analyzer and a syntax analyzer for SQL script parsing are generated by constructing a JAVACC lexical syntax generator, a common SQL script is parsed into Java data objects, then Java objects defined by different data are operated, and mutual transformation of different database data types, keywords, system functions, and system objects is realized through mutual calling and recursive calling between Java objects, and is converted into a target database recognition and compatible database statement script, that is, when Oracle data is migrated to Postgresql, an effect of high efficiency, low error rate, and perfect migration of business logic is achieved; meanwhile, the fault tolerance of the conversion process is enhanced by utilizing the automatic conversion of the database script, the conversion time consumption is reduced, and the success rate is improved; and further completing the verification of the conversion result through technical means.
The above description is only for the purpose of illustrating the preferred embodiments of the present invention and is not to be construed as limiting the invention, and any modifications, equivalents, improvements and the like that fall within the spirit and principle of the present invention are intended to be included therein.
Claims (10)
- The method for automatically converting the Oracle script into the Postgresql script is characterized by comprising the following steps of:s1, resolving a database data object definition statement into an application program object by using JAVACC;s2, processing mutual conversion of different database data objects by using the application program;s3, generating database object definition scripts which accord with respective database grammar rules;and S4, completing automatic conversion and migration of the data objects of the heterogeneous database.
- 2. The method for automatically converting Oracle script into Postgresql script according to claim 1, wherein the parsing database data object definition statements into application program objects by using JAVACC comprises the following steps:s11, generating a lexical analyzer and a syntactic analyzer of the SQL statement by using JAVACC;and S12, analyzing the Oracle database data object definition statement into an application program object through a lexical analyzer and a syntax analyzer.
- 3. The method for automatically converting Oracle script into Postgresql script according to claim 2, wherein the lexical analyzer and the syntax analyzer for generating SQL sentences by using JAVACC comprise the following steps:s111, loading a grammar rule file with the extension name of jj by JAVACC;and S112, generating a lexical analyzer and a syntactic analyzer for SQL statement analysis.
- 4. The method for automatically converting Oracle script into Postgresql script according to claim 3, wherein JAVACC supports custom lexical and grammatical rules suitable for the generation of lexical analyzers and syntax analyzers of various heterogeneous type database data object definition statements.
- 5. The method of claim 4, wherein the grammar rule file is a text file with jj end extension, and the grammar rule file includes property setting of a grammar analyzer, class declaration of the grammar analyzer, lexical rule declaration and grammar rule implementation.
- 6. The method for automatically converting Oracle script into Postgresql script according to claim 5, wherein the syntax rule file defines JAVA classes by referencing data objects to complete the correlation between the data objects and the corresponding JAVA classes.
- 7. The method for automatically converting Oracle script into Postgresql script according to claim 6, wherein the Oracle database object definition statement is a SQL statement script defined by data objects.
- 8. The method for automatically converting Oracle script into Postgresql script according to claim 7, wherein the parsing the Oracle database data object definition statement into the application object through the lexical analyzer and the syntax analyzer comprises the following steps:s121, analyzing the SQL statement script defined by the data object by using a lexical analyzer and a syntax analyzer;and S122, converting the common SQL text statement in the SQL statement script defined by the data object into the JAVA object of the application program.
- 9. The method for automatically converting the Oracle script into the Postgresql script according to claim 8, wherein the processing of the interconversion of different database data objects by the application program comprises the following steps:s21, converting the Oracle database object definition statements into application program objects through an SQL analysis engine, and generating Java data objects related to the Oracle data objects;s22, converting the Java data object associated with the Oracle data object into a Java data object associated with Postgresql;and S23, converting the Java data object associated with the Postgresql into a Postgresql database data object definition statement.
- 10. The method for automatically converting the Oracle script into the Postgresql script according to claim 9, wherein the script conversion between heterogeneous databases comprises database data types, keywords, system functions and system data objects.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211432623.7A CN115757339A (en) | 2022-11-16 | 2022-11-16 | Method for automatically converting Oracle script into Postgresql script |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211432623.7A CN115757339A (en) | 2022-11-16 | 2022-11-16 | Method for automatically converting Oracle script into Postgresql script |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN115757339A true CN115757339A (en) | 2023-03-07 |
Family
ID=85371666
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202211432623.7A Pending CN115757339A (en) | 2022-11-16 | 2022-11-16 | Method for automatically converting Oracle script into Postgresql script |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN115757339A (en) |
Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102073490A (en) * | 2009-11-25 | 2011-05-25 | 阿里巴巴集团控股有限公司 | Method and device for translating database language |
| CN102750374A (en) * | 2012-06-20 | 2012-10-24 | 深圳市远行科技有限公司 | Data tracing and influence relationship analysis method based on database script |
| US20150019488A1 (en) * | 2013-07-09 | 2015-01-15 | Oracle International Corporation | Automated database migration architecture |
| CN105787044A (en) * | 2016-02-26 | 2016-07-20 | 广州品唯软件有限公司 | MySQL based SQL parser and parsing method thereof |
| CN105868204A (en) * | 2015-01-21 | 2016-08-17 | 中国移动（深圳）有限公司 | Method and apparatus for converting script language SQL of Oracle |
| CN112965995A (en) * | 2021-04-19 | 2021-06-15 | 瀚高基础软件股份有限公司 | Data interaction method and device based on PSQL of postgreSQL client |
-
2022
- 2022-11-16 CN CN202211432623.7A patent/CN115757339A/en active Pending
Patent Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102073490A (en) * | 2009-11-25 | 2011-05-25 | 阿里巴巴集团控股有限公司 | Method and device for translating database language |
| CN102750374A (en) * | 2012-06-20 | 2012-10-24 | 深圳市远行科技有限公司 | Data tracing and influence relationship analysis method based on database script |
| US20150019488A1 (en) * | 2013-07-09 | 2015-01-15 | Oracle International Corporation | Automated database migration architecture |
| CN105868204A (en) * | 2015-01-21 | 2016-08-17 | 中国移动（深圳）有限公司 | Method and apparatus for converting script language SQL of Oracle |
| CN105787044A (en) * | 2016-02-26 | 2016-07-20 | 广州品唯软件有限公司 | MySQL based SQL parser and parsing method thereof |
| CN112965995A (en) * | 2021-04-19 | 2021-06-15 | 瀚高基础软件股份有限公司 | Data interaction method and device based on PSQL of postgreSQL client |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111061757B (en) | Language conversion method and device of database, electronic equipment and storage medium | |
| JP2602205B2 (en) | Database access control method | |
| CN110555032A (en) | Data blood relationship analysis method and system based on metadata | |
| US20040158820A1 (en) | System for generating an application framework and components | |
| EP0827072A1 (en) | Correcting program porting errors | |
| CN110209668B (en) | Dimension table association method, device and equipment based on stream calculation and readable storage medium | |
| US11995075B2 (en) | System and method for efficient transliteration of machine interpretable languages | |
| CN112765209A (en) | SQL statement syntax migration method and system between databases | |
| CN115809063B (en) | Storage process compiling method, system, electronic equipment and storage medium | |
| CN111309751A (en) | Big data processing method and device | |
| CN111143330A (en) | Method and device for realizing multi-mode database analysis engine | |
| CN117093599A (en) | Unified SQL query method for heterogeneous data sources | |
| CN111695002B (en) | Database-independent query method based on XML sentences | |
| CN110851514B (en) | ETL (extract transform load) processing method based on FLINK (Linear rotation injection) | |
| CN111694738B (en) | Method for generating SQL test script | |
| CN113934786A (en) | Implementation method for constructing unified ETL | |
| CN112347120B (en) | Automatic optimization method and device based on complex SQL | |
| CN113032366A (en) | SQL syntax tree analysis method based on Flex and Bison | |
| CN115757339A (en) | Method for automatically converting Oracle script into Postgresql script | |
| CN116204550A (en) | Database query statement optimization method, storage medium and device | |
| CN115576563A (en) | SQL script dynamic optimization method | |
| CN115407997A (en) | Agile development application method and system based on low codes | |
| CN113836164A (en) | Method, system, device and medium for unifying SQL | |
| CN113626465B (en) | Database and method for realizing session-level variables in postgresql database | |
| CN110968634B (en) | Method for realizing ETL conversion processing by utilizing programmable function based on XML description in big data scene |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20230307 |