CN115840589A - Publishing method supporting heterogeneous distributed database - Google Patents

Abstract

The invention discloses a publishing method supporting a heterogeneous distributed database, which is characterized by comprising the following steps: acquiring metadata of an Oracle database and storing the metadata into an analysis Map table; establishing and storing a mapping relation between table field types of an Oracle database and a MySQL database; generating a table field type and a table field length of MySQL: acquiring a field type of a corresponding MySQL database; acquiring information for constructing a MySQL database object; acquiring the type of a DDL SQL statement of an Oracle database; judging the type of the DDL SQL statement according to the DDL SQL statement header marker to generate the DDL SQL statement; and executing MySQL SQL statement audit. The effects of more convenient conversion process, low technical dependence, reduced use threshold of operation and maintenance personnel, simplified auditing process of the distributed database and improved conversion efficiency and accuracy between heterogeneous databases are achieved.

Description

Publishing method supporting heterogeneous distributed database

Technical Field

The invention belongs to the technical field of database operation and maintenance, and particularly relates to a publishing method supporting a heterogeneous distributed database.

Background

In a heterogeneous database architecture with both an Oracle database and a distributed MySQL database, database management personnel need to issue the Oracle database and the distributed MySQL database at the same time, but due to the problems that SQL conversion of the heterogeneous database is difficult and SQL check of the distributed database is difficult, difficulty is brought to the simultaneous check and issue of the heterogeneous distributed database.

The first difficulty of SQL conversion between heterogeneous databases is that SQL is difficult to analyze, similar open source items in the industry can be written by using Apache call, such as JAVA, however, the open source technologies are relatively complex and have high requirements on the use threshold of operation and maintenance personnel; or the regular expression is analyzed in a regular mode, but the regular expression is relatively complex, and the writing method is not fixed due to various syntaxes of the SQL in the heterogeneous database, so that all database scenes cannot be met, and the maintenance is very troublesome; secondly, no rules supporting SQL type conversion between heterogeneous databases exist in the market;

the SQL examination difficulty of the distributed database is as follows: the inclusion is used as an automatic operation and maintenance tool for the MySQL statement examination, and is widely applied to the MySQL statement examination due to the advantages of accuracy and automatic examination. However, the inclusion tool only supports statement auditing of single-instance MySQL, and is difficult to deal with a distributed database with a complex structure, so the inclusion cannot be applied to SQL auditing of the distributed database.

SQL auditing method, device and computer equipment based on distributed database (202110947420.0) provide that when responding to SQL request, system resource data set, database index data set and SQL execution plan data set of each computing node are obtained; determining the SQL execution time length of each computing node based on the SQL audit model configured by each computing node and the system resource data set, the database index data set and the SQL execution plan data set of each computing node; the auditing result is determined based on the SQL execution time of each computing node, so that the real-time auditing of the distributed database is realized, and the auditing reliability and accuracy are high. However, in the SQL examination of the technology, all the computing nodes need to execute SQL once and then summarize, which undoubtedly increases time consumption and is not beneficial to the operation and maintenance management of a complex large-scale database; secondly, the technology only focuses on the execution plan and execution time of a non-data definition language (DDL for short), and the limitation of an audit scene cannot meet the daily operation and maintenance requirements of database management personnel; moreover, DDL audits destroy the structure of the online database and cannot be performed online.

In summary, a method for supporting the release of heterogeneous distributed databases is urgently needed, which not only can effectively solve the problem of convenient conversion of SQL statements between heterogeneous databases, but also can efficiently complete the SQL audit in the distributed databases.

Disclosure of Invention

The invention provides a publishing method supporting a heterogeneous distributed database, which aims to solve the problem of simultaneously auditing and publishing an Oracle database and a distributed MySQL database, realizes SQL statement interchange between the Oracle database and the MySQL database, and supports SQL statement auditing of the distributed database.

The technical scheme includes that the release method supporting the heterogeneous distributed database specifically comprises the steps of heterogeneous database SQL statement conversion based on metadata and SQL statement auditing of the distributed database, wherein the heterogeneous database SQL conversion based on the metadata refers to SQL statement interchange between an Oracle database and a MySQL database.

The specific steps of the scheme of the invention comprise:

step 1: acquiring metadata of an Oracle database, storing the metadata into an analysis Map table, wherein the analysis Map table is a table constructed according to a Key-Value structure, the attribute name of the metadata is used as a Key Value, and the attribute Value of the metadata is used as a Value; the metadata comprises a table field, a primary key and an index, and the table field metadata attribute comprises a scheme name, a table field type, a table field length, whether the table field length can be empty or not and a field default value; the metadata attributes of the primary key comprise a primary key name and a table field name, and the metadata attributes of the index comprise: index name and table field name;

and 2, step: establishing and storing a mapping relation between table field types of an Oracle database and a MySQL database, wherein the mapping relation is stored in a mapping Map table, and the mapping Map table is a table constructed according to a Key-Value structure; taking the table field type of Oracle as Key of the Map table, and taking the table field type of MySQL as Value of the Map table;

and 3, step 3: generating a table field type and a table field length of MySQL: acquiring a field type of a corresponding MySQL database according to a table field type mapping relation defined by a mapping Map table by utilizing the Oracle table field type and the table field length generated in the step 1; taking the length of the Oracle table field as the length of the MySQL table field;

and 4, step 4: acquiring information for constructing a MySQL database object; acquiring metadata of table fields, primary keys and indexes from the analysis Map table; replacing the table field type and the table field length in the table with the table field type and the table field length of MySQL according to the step 3;

and 5: acquiring the type of a DDL SQL statement of an Oracle database; judging the type of the DDL SQL statement according to a statement header marker of the DDL SQL statement, and if the statement header contains a marker of a create table and comment, the statement type is a table building statement; if the sentence header contains a marker of the create index, the sentence type is an index building sentence; if the sentence header contains a marker of the alter table, the type of the sentence is a modified table language sentence;

step 6: generating a DDL SQL statement of the MySQL database; aiming at each Oracle DDL SQL statement, when the step 5 is executed, a table name in the DDL SQL statement is obtained; according to the table name and according to the step 4, acquiring information for constructing the MySQL database object from the analysis Map; calling a syntax format template of a DDL SQL statement of corresponding MySQL according to the statement type, and filling the table name and the information of the database object into corresponding positions in the template;

and 7: performing MySQL SQL statement review of the distributed database; acquiring a fragment library and a single library of a database scheme, and respectively establishing mapping relations between the name of the database scheme and the fragment library and the single library; the fragment library consists of at least 2 database fragments; the database shards are databases for storing shard table data; the table structures of the fragment table data stored in each database fragment are the same; the single database is a database for storing non-fragmentation table data; and acquiring the storage position of each table in the database scheme, wherein the storage position comprises a fragment library and a single library, and executing SQL statement examination on the fragment library and the single library according to the mapping relation.

In step 2, the established mapping relationship includes:

{varchar2:varchar,clob:text,char:char,number(10):bigint,number(2):tinyint,number(4):smallint,number(8):int,number:decimal,date:datetime,timestamp:datetime,clob:longtext,blob:longblob,raw:varbinary,binary_float:float,binary_double:double}。

in step 3, the table field type and the table field length are represented in pseudo code form; the pseudo code utilization refers to the fact that a format function is utilized to obtain the generated table field type and the table field length;

in step 4, the metadata of the table field, the metadata of the primary key and the metadata of the index are identified in a pseudo code form; the pseudo code is used for acquiring the metadata of the generated table field, the metadata of the primary key and the metadata of the index by using a format function.

In the step 5, the regular expression is used to search whether the sentence header of the DDL SQL sentence contains the marker of the create table, the create index, the alter table and the comment, and the type of the DDL SQL sentence is judged according to the search result.

In step 6, performing deduplication and verification on the generated DDL SQL statement, including: after table building sentences and table language sentence modification of MySQL are generated, table field types of character types are obtained from the table, and the sum of the corresponding table field lengths is counted; judging whether the sum of the lengths exceeds 65535 bytes or not, if so, selecting the table field with the longest length, and changing the corresponding table field type from a character type to a text type; and after the modified table sentences are generated, retrieving the modified table sentences with consistent table names according to the table names, and combining the contents of the modified table sentences with consistent table names.

The step 7 specifically includes:

step 7-1: acquiring a first database fragment in a fragment library, and establishing a mapping relation between the first database fragment and a database scheme;

step 7-2: taking a database scheme as a unit, and if the number of the storage positions for acquiring the table of the database scheme is more than 1, storing the table of the database scheme in a fragment library; executing the step 7-3; if the storage positions of the obtained tables are only 1, the tables of the database schemes are stored in a single database, and the step 7-4 is executed

And 7-3: extracting a first database fragment in the fragment library according to the mapping relation in the step 7-1, and executing SQL statement examination on the first database fragment by utilizing an inclusion tool;

and 7-4: according to the mapping relation between the preset database scheme name and the single database, performing SQL statement examination on the single database by using an inclusion tool;

and 7-5: summarizing the auditing results of the fragment library and the single library, wherein the auditing results comprise auditing success and auditing failure; and if the checking result of any database is that the checking fails, judging that the SQL checking of the distributed database fails.

The invention achieves the following beneficial effects:

1) The method executes the analysis of the Oracle SQL statement and the automatic conversion to the MySQL statement according to the mapping relation of the table field type based on the generated metadata by executing the Oracle SQL statement; the conversion process is realized in a more convenient mode, and the grammar does not need to be analyzed by a complicated open source technology and a regular mode, so that the use threshold of operation and maintenance personnel is reduced, and the conversion efficiency and accuracy among heterogeneous databases are improved;

2) When the SQL of the distributed database is checked, the SQL checking of the splitting database and the single database is distinguished by the storage position through establishing the mapping relation between the database scheme and the table storage position, and the checking result of the first database splitting in the splitting database is used as the checking result of the splitting database, so that the checking disadvantage that the existing SQL checking tool is not suitable for the distributed database is solved, the complicated checking process of the distributed database is simplified, and the error rate is reduced.

Drawings

Fig. 1 is a flowchart of a publishing method supporting a heterogeneous distributed database according to an embodiment of the present invention;

FIG. 2 is a block diagram of the SQL distribution system in the heterogeneous distributed database environment according to the embodiment of the present invention.

Detailed Description

In order to make the objects, technical solutions and advantages of the present invention more apparent, the present invention will be described in detail with reference to the accompanying drawings and specific embodiments. Fig. 1 is a flowchart of a publishing method supporting a heterogeneous distributed database in an embodiment of the present invention, which includes the specific steps of:

step 101: acquiring metadata of an Oracle database and storing the metadata into an analysis Map table, wherein the analysis Map table is a table constructed according to a Key-Value structure; the method comprises the following steps: acquiring an sql statement in an Oracle script, executing the sql statement in a temporary database of Oracle, and generating metadata of an Oracle database object; obtaining metadata containing table fields, main keys and indexes from a data dictionary of an Oracle database and storing the metadata into an analysis Map table, wherein the attribute name of the metadata is used as a Key Value, and the attribute Value is used as a Value; the table field metadata attributes include: scheme name (owner), table name (table _ name), field name (column _ name), table field type (data _ type), field length (data _ length), nullable (nullable), field default (data _ default), and field sequence number (column _ id); the metadata attributes of the primary key include: a primary key name (constraint _ name) and a corresponding table field name (column _ name); metadata attributes of the index include: index name (index _ name) and corresponding table field name (column _ name);

in the embodiment of the present invention, the metadata stored in the parsing Map is obtained as follows:

Key	Value
		name of the scheme(owner)	schema_1
Table name (table _ name)	table_1
		Field name (column _ name)	Id
Table field type (data _ type)	number
		Field length (data _ length)	10
Whether it can be empty (nullable)	Whether or not
		Field default value (data _ default)	null
Field sequence number (column _ id)	1
		Field name (column _ name)	Name
Table field type (data _ type)	varchar2
		Field length (data _ length)	50
Whether it can be empty (nullable)	Whether or not
		Field default value (data _ default)	‘system’
Field sequence number (column _ id)	2
		Main key name (constraint _ name)	pk_tab1
Table field name (column _ name)	Id
		Index name (index _ name)	idx_tab1
Table field name (column _ name);	name

step 102: establishing and storing a mapping relation between table field types of an Oracle database and a MySQL database, wherein the mapping relation is stored in a mapping Map table, and the mapping Map table is a table constructed according to a Key-Value structure; the method comprises the following steps: and taking the table field type of Oracle as Key of the Map table, and taking the table field type of MySQL as Value of the Map table.

The Oracle database comprises 22 data table field types, the MySQL database comprises 19 table field types, the field types which are not commonly used are removed by summarizing the use frequency of the table field types in the actual application of the database, 15 table field types are respectively summarized from the Oracle database and the MySQL database, and the mapping relation finally stored in the Map is determined and analyzed according to the compatibility of the table field types of the Oracle database and the MySQL database and the database development specification, wherein the mapping relation comprises the following steps:

{varchar2:varchar,clob:text,char:char,number(10):bigint,number(2):tinyint,number(4):smallint,number(8):int,number:decimal,date:datetime,timestamp:datetime,clob:longtext,blob:longblob,raw:varbinary,binary_float:float,binary_double:double}。

the mapping relation in the invention provides basis for field type mapping between the existing heterogeneous databases, and reference is never provided for the complete, uniform and accurate mapping relation before, and the mapping relation is subjected to effect verification in the database of the internet service.

Step 103: table field type (column _ type) and table field length (column _ length) of MySQL are generated: acquiring the table field type and the table field length of Oracle generated in the step 101, and acquiring the corresponding table field type of MySQL according to the table field type mapping relation defined by the mapping Map table; taking the table field length of the Oracle as the table field length of MySQL; in the implementation of the invention, the table field type and the table field length of the generated MySQL are represented by a pseudo code, and the pseudo code is as follows:

"{}({})".format(column_type,column_length)；

step 104: acquiring information for constructing a MySQL database object, wherein the information comprises table fields, main keys and index information; specifically, metadata of table fields, main keys and indexes are obtained from a parsing Map table;

the metadata of the acquired table field comprises a table field name, a table field type, a default value and a mark indicating whether the table field is empty or not; replacing the table field type with the table field type generated in the step 103;

the metadata of the acquired primary key comprises a primary key name and a table field name;

the metadata of the obtained index comprises an index name and a table field name;

in the embodiment of the invention, the generated MySQL table field is represented by pseudo code. The pseudo code is as follows:

"{}{}{}{},".format(column_name,column_type,"default"+default if default else"",isnull)；

in the embodiment of the invention, the generated primary key is represented by a pseudo code, and the pseudo code is as follows: primary key ({ column _ name });

in the embodiment of the present invention, the generated index statement is represented by a pseudo code, and the pseudo code is as follows: key { index _ name } ({ column _ name });

step 105: acquiring a DDL SQL statement of an Oracle database, and searching whether a sentence header of the DDL SQL statement contains a marker of a create table, a create index, an alter table and comment by using a regular expression; judging the statement type of the DDL SQL according to the marker; the statement types comprise table building statements, index building statements and table modifying statements; wherein, the sentence header contains the marker of create table and comment, and the sentence type is a table-building sentence; the sentence header comprises a marker of the create index, and the sentence type is an index building sentence; the sentence header comprises a marker of the alter table, and the type of the sentence is a modified table language sentence;

in the embodiment of the invention, by writing a data script for query, an Oracle database is queried in a JDBC form, and a DDL SQL statement of the Oracle data is obtained, wherein the specific database script is as follows:

/>

taking the statement type of the search table as an example, in the embodiment of the present invention, the following regular expression is used to determine whether the statement header of the Oracle DDL SQL statement contains a marker of the create table.

I/remove SQL notes first

sql＝remove_comments(sql)

// re-determining whether sql is a table building statement

sql.lstrip().startswith("create table")

Returning to True, it is a table building statement, otherwise it is not.

Step 106: generating a MySQLde DDL SQL statement according to the statement type and the information of the database object; the method comprises the following steps: aiming at each Oracle DDL SQL statement, when the step 105 is executed, a table name in the DDL SQL statement is obtained; according to the table name, according to the step 104, acquiring information for constructing the MySQL database object from the analysis Map; and calling a syntax format template of the DDL SQL statement type of the corresponding MySQL according to the statement type, and filling the table name and the database object information into the positions of the table name and the database object in the template. Deleting the generated modification table statement when the statement type containing the marker comment exists; this is because the header of the table building statement of Oracle contains create and comment, but the header of the table building statement of MySQL only contains create; in the process of converting the Oracle DDL SQL statement into the MySQL DDL SQL statement, not only the Oracle table building statement is completely presented at the beginning of create, but also the comment at the beginning of comment is presented in the statement type of the alter modified table, so that the generated table building statement is deleted to avoid repetition;

in the embodiment of the present invention, 1 Oracle DDL SQL statement is obtained as follows: create table schema _1.Table _1

(

Id number(10)not null,

Name varchar2(50)default‘system’,

CONSTRAINT PK_TAB1 PRIMARY KEY(id)

)；

Comment on SCHEMA _1.Table _1.Name is 'name';

CREATE INDEX SCHEMA_1.IDX_TAB1 ON SCHEMA_1.TABLE_1(name)；

and judging the sentence type, wherein the sentence header contains a create table, and determining the sentence as a table building sentence. According to the table name and step 4, acquiring information for constructing a database object from the analysis Map, wherein the information comprises table fields, primary keys and indexed metadata; and the generated metadata of the table field, the primary key and the index are expressed in a pseudo code form;

“

Id bigint not null,

Name varchar(50)null default‘system’,

Primary key(id),

Key idx_tab1(name)

”

corresponding to the statement type of Oracle table building, the grammar format template of the table building statement of MySQL is obtained as follows:

create table name

(field name, field type)

Filling the table name identified by the pseudo code and the information of the database object into a syntax format template of the table building statement of MySQL, and the syntax format template comprises the following steps:

Create table table_1

(

Id bigint not null,

Name varchar(50)null default‘system’,

Primary key(id),

Key idx_tab1(name)

)；

step 107: executing deduplication and verification of the SQL statement, specifically comprising: and judging whether the sum of the lengths of all character type fields exceeds 65535 bytes after the newly-built table or the table structure is modified, if so, selecting a table field with the longest byte length, and converting the character type of the table field into a text type. Due to MySQL database restrictions, the sum of the length of the character field in the table cannot exceed 65535 bytes. Therefore, taking the table as a unit, the sum of the field lengths of all character types in the table is obtained, and if the sum exceeds 65535 bytes; and acquiring a field with the longest byte length, and converting the field type from a character type to a text type.

If a single statement modifies multiple fields, combining the multiple fields and generating an ALTER statement; therefore, the SQL execution times are reduced, and the SQL release time is shortened. In the embodiment of the invention, a MySQLde DDL SQL statement is generated according to the statement type and the information of the database object, and the statement is as follows:

alter table table_1 add sex varchar(50)not null；

alter table table_1 add score decimal(10,0)null；

the combined statements are as follows:

alter table table_1 add sex varchar(50)not null,add score decimal(10,0)null；

step 108: after SQL conversion between heterogeneous databases is finished, SQL statement audit of the distributed database is executed, and the method specifically comprises the following steps:

step 8-1: establishing a mapping relation between a database scheme and a fragment library, wherein the mapping relation refers to a mapping relation between a first database fragment in the fragment library and the database scheme; the fragment library consists of at least 2 database fragments; the database shards are databases for storing shard table data; the table structures of the shard table data stored in each database shard are the same; because the table structures of the database fragments are the same, any one of the database fragments can be selected to execute SQL statement audit; in order to improve the efficiency of selecting the database fragments, the invention defines that the first database fragment is selected to execute SQL statement audit; in practical implementation, one database scheme includes a plurality of tables, one table is divided into a plurality of fragment table data, and the fragment table data is stored in a fragment library composed of a plurality of database fragments; the database shards in the shard library are arranged in sequence, so that the first database shard is fixed and easy to select.

Step 8-2: judging the storage position of the table of the database scheme: taking a database scheme as a unit, and acquiring a storage position of a table of the database scheme; if the number of the acquired storage positions of the table is more than 1, the table of the database scheme is stored in a fragment library; performing step 8-3; if the storage positions of the obtained tables are only 1, judging that the tables are not fragmented, and storing the tables of the database scheme in a single database; the single database is used for storing the data of the non-fragmentation table, and the step 8-4 is executed

Step 8-3: extracting a first database fragment in the fragment library according to the mapping relation between the database scheme and the fragment library and the database fragment name recorded by the mapping relation; performing SQL statement audit on the first database fragment by utilizing an inclusion tool;

step 8-4: according to the mapping relation between the preset database scheme name and the single database, performing SQL statement examination on the single database by using an inclusion tool;

step 8-4: summarizing the auditing results of the fragment library and the single library, wherein the auditing results comprise auditing success and auditing failure; and if the checking result of any database is that the checking fails, judging that the SQL checking of the distributed database fails.

The invention does not distinguish whether the data type in the distributed environment is semi-structured data or unstructured data, and whether the fragmentation mode of the distributed database is vertical fragmentation or horizontal fragmentation, and completes the conversion and generation of the DDL SQL statement between heterogeneous databases by utilizing the mapping relation of the database metadata and the data type; when database audit is performed, although the inclusion tool is an SQL statement automatic audit tool, in practical application, the tool is difficult to deal with a distributed database with a complex structure and cannot support a heterogeneous database, in order to achieve the purpose that the inclusion tool is used by the distributed database, one database fragment is selected from the fragment library for audit, so that the complex operation of auditing all database fragments in the fragment library is reduced, and the usability of the inclusion tool in the distributed database is increased.

Fig. 2 is an architecture diagram of an SQL distribution system in an heterogeneous distributed database environment according to an embodiment of the present invention, which specifically includes a temporary database, an online database, and a database distribution platform connected to each other; the temporary database is deployed as an Oracle database; the online database is deployed as a heterogeneous distributed database and comprises an Oracle database and a MySQL database; the MySQL database is deployed as a distributed database and consists of a plurality of fragment libraries and a single library; the database release platform comprises a preprocessing module, an SQL conversion module, an SQL check module and an audit module which are connected in sequence;

the preprocessing module executes a preprocessing program before SQL conversion and is used for acquiring an Oracle SQL text and auditing SQL sentences in the text; after the examination is passed, subsequent SQL conversion is carried out;

the SQL conversion module is used for converting the SQL sentence of Oracle into the SQL sentence of MySQL; the SQL conversion module executes an Oracle SQL statement in the temporary database, and constructs and generates a MySQL statement based on the generated metadata;

the SQL check module is used for executing duplication removal and check processing aiming at the generated MySQL statement;

the checking module takes a database scheme as a unit, executes SQL statement checking aiming at a fragment library and a single library in the MySQL distributed database, and outputs a checking result.

The invention achieves the following beneficial effects:

1) The method executes the analysis of the Oracle SQL statement and the automatic conversion to the MySQL statement according to the mapping relation of the table field type based on the generated metadata by executing the Oracle SQL statement; the conversion process is realized in a more convenient mode, and the grammar does not need to be analyzed by a complicated open source technology and a regular mode, so that the use threshold of operation and maintenance personnel is reduced, and the conversion efficiency and accuracy among heterogeneous databases are improved;

2) When the SQL of the distributed database is checked, the SQL checking of the splitting database and the single database is distinguished by the storage position through establishing the mapping relation between the database scheme and the table storage position, and the checking result of the first database splitting in the splitting database is used as the checking result of the splitting database, so that the checking disadvantage that the existing SQL checking tool is not suitable for the distributed database is solved, the complicated checking process of the distributed database is simplified, and the error rate is reduced.

The above embodiments do not limit the present invention in any way, and all other modifications and applications that can be made to the above embodiments in equivalent ways are within the scope of the present invention.

Claims (6)

1. A publishing method supporting a heterogeneous distributed database is characterized by comprising the following steps:

step 1: acquiring metadata of an Oracle database, storing the metadata into an analysis Map table, wherein the analysis Map table is a table constructed according to a Key-Value structure, the attribute name of the metadata is used as a Key Value, and the attribute Value of the metadata is used as a Value; the metadata comprises a table field, a primary key and an index, and the metadata attributes of the table field comprise a scheme name, a table field type, a table field length, whether the table field can be empty or not and a field default value; the metadata attributes of the primary key comprise a primary key name and a table field name, and the metadata attributes of the index comprise: index name and table field name;

step 2: establishing and storing a mapping relation between table field types of an Oracle database and a MySQL database, wherein the mapping relation is stored in a mapping Map table, and the mapping Map table is a table constructed according to a Key-Value structure; taking the table field type of Oracle as Key of the Map table, and taking the table field type of MySQL as Value of the Map table;

and step 3: generating a table field type and a table field length of MySQL: acquiring a field type of a corresponding MySQL database according to a table field type mapping relation defined by a mapping Map table by utilizing the Oracle table field type and the table field length generated in the step 1; taking the length of the Oracle table field as the length of the MySQL table field;

and 4, step 4: acquiring information for constructing a MySQL database object; acquiring metadata of table fields, primary keys and indexes from the analysis Map table; replacing the table field type and the table field length in the table with the table field type and the table field length of MySQL according to the step 3;

and 5: acquiring the type of a DDL SQL statement of an Oracle database; judging the type of the DDL SQL statement according to the statement header marker of the DDL SQL statement, wherein if the statement header contains the marker of the create table and the comment, the statement type is a table building statement; if the sentence header contains a marker of the create index, the sentence type is an index building sentence; if the sentence header contains a marker of the alter table, the type of the sentence is a modified table language sentence;

step 6: generating a DDL SQL statement of a MySQL database; aiming at each Oracle DDL SQL statement, when the step 5 is executed, a table name in the DDL SQL statement is obtained; according to the table name and according to the step 4, acquiring information for constructing the MySQL database object from the analysis Map; calling a syntax format template of a DDL SQL statement of corresponding MySQL according to the statement type, and filling the table name and the information of the database object into corresponding positions in the template;

and 7: performing MySQL SQL statement review of the distributed database; acquiring a fragment library and a single library of a database scheme, and respectively establishing mapping relations between the name of the database scheme and the fragment library and the single library; the fragment library consists of at least 2 database fragments; the database shards are databases for storing shard table data; the table structures of the fragment table data stored in each database fragment are the same; the single database is a database for storing non-fragmentation table data; and acquiring the storage position of each table in the database scheme, wherein the storage position comprises a fragment library and a single library, and executing SQL statement examination on the fragment library and the single library according to the mapping relation.

2. The publishing method supporting heterogeneous distributed databases according to claim 1, wherein in the step 2, the established mapping relationship includes:

{varchar2:varchar,clob:text,char:char,number(10):bigint,number(2):tinyint,number(4):smallint,number(8):int,number:decimal,date:datetime,timestamp:datetime,clob:longtext,blob:longblob,raw:varbinary,binary_float:float,binary_double:double}。

3. the publishing method to support heterogeneous distributed databases, according to claim 2,

in step 3, the table field type and the table field length are represented in a pseudo code form; the pseudo code utilization refers to the fact that a format function is utilized to obtain the generated table field type and the table field length;

in step 4, the metadata of the table field, the metadata of the primary key and the metadata of the index are identified in a pseudo code form; the pseudo code is used for acquiring the metadata of the generated table field, the metadata of the primary key and the metadata of the index by using a format function.

4. The publishing method to support heterogeneous distributed databases, according to claim 3,

in the step 5, the regular expression is used to search whether the sentence header of the DDL SQL sentence contains the marker of the create table, the create index, the alter table and the comment, and the type of the DDL SQL sentence is judged according to the search result.

5. The publishing method to support heterogeneous distributed databases according to claim 4,

in step 6, performing deduplication and verification on the generated DDL SQL statement, including: after table building sentences and table language sentence modification of MySQL are generated, table field types of character types are obtained from the table, and the sum of the corresponding table field lengths is counted; judging whether the sum of the lengths exceeds 65535 bytes or not, if so, selecting the table field with the longest length, and changing the corresponding table field type from a character type to a text type; and after the modified table statement is generated, retrieving the modified table statement with the consistent table name according to the table name, and combining the contents of the modified table statement with the consistent table name.

6. The publishing method to support heterogeneous distributed databases, according to claim 5,

the step 7 specifically includes:

step 7-1: acquiring a first database fragment in a fragment library, and establishing a mapping relation between the first database fragment and a database scheme;

step 7-2: taking a database scheme as a unit, if the number of the storage positions for acquiring the table of the database scheme is more than 1, storing the table of the database scheme in a fragment library; performing step 7-3; if the storage positions of the obtained tables are only 1, the tables of the database schemes are stored in a single database, and the step 7-4 is executed

And 7-3: extracting a first database fragment in the fragment library according to the mapping relation in the step 7-1, and executing SQL statement examination on the first database fragment by utilizing an inclusion tool;

and 7-4: according to the mapping relation between the preset database scheme name and the single database, performing SQL statement examination on the single database by using an inclusion tool;

and 7-5: summarizing the auditing results of the fragment library and the single library, wherein the auditing results comprise auditing success and auditing failure; and if the checking result of any database is that the checking fails, judging that the SQL checking of the distributed database fails.

Images