CN113779955B

CN113779955B - Method, device and storage medium for generating difference script

Info

Publication number: CN113779955B
Application number: CN202111080757.2A
Authority: CN
Inventors: 唐罡锋; 穆小亮; 杨蕾
Original assignee: Agricultural Bank of China
Current assignee: Agricultural Bank of China
Priority date: 2021-09-15
Filing date: 2021-09-15
Publication date: 2023-11-03
Anticipated expiration: 2041-09-15
Also published as: CN113779955A

Abstract

The application discloses a method, a device and a storage medium for generating a difference script, which acquire information of database objects contained in a source database and a target database respectively. Comparing the clusters of the source database with the clusters of the target database to obtain a cluster comparison result, and generating a cluster difference script corresponding to the cluster comparison result. And comparing the table of the source database with the table of the target database to obtain a table comparison result, and generating a table difference script corresponding to the table comparison result. And comparing the view of the source database with the view of the target database to obtain a view comparison result, and generating a view difference script corresponding to the view comparison result. By means of the scheme, the clustering difference script, the table difference script and the view difference script are obtained by comparing clusters, tables and views contained in the source database and the target database, workload of difference script generation work can be remarkably reduced, and manual work is assisted to generate the difference script rapidly and accurately.

Description

Method, device and storage medium for generating difference script

Technical Field

The present application relates to the field of databases, and in particular, to a method, an apparatus, and a storage medium for generating a difference script.

Background

For upgrade production of a produced system using a database, a differential DDL SQL script (hereinafter referred to as a differential script) for implementing database object changes needs to be prepared, where the differential script includes the following information: newly added clusters; deleted clusters; a newly added table and index information thereof; change information of the structure of the put-to-operate table; change information of indexes of the put-into-production table (including newly added indexes, modified indexes and deleted indexes); deleted table and index information thereof. To obtain the difference script, metadata of two databases (i.e., a database before upgrading and a database after upgrading) are compared to obtain metadata differences between the two databases, and the difference script is generated according to the metadata differences.

At present, the existing method for generating the difference script is manual operation, however, the workload of manually editing a difference script by manpower is acceptable for the scene with less variation of database objects, but for a data system with long development period and more variation of database objects, the obtained difference script is large in workload and low in speed by manually comparing metadata differences between two databases, and more errors exist in the manual processing mode (such as manual input errors, actually, information with differences is not manually compared, and the like).

Therefore, how to reduce the workload of the difference script generation work and assist the manual work to generate the difference script quickly and accurately becomes a problem to be solved in the field.

Disclosure of Invention

The application provides a method, a device and a storage medium for generating a difference script, which aim to reduce the workload of the generation work of the difference script and assist the manual work to quickly and accurately generate the difference script.

In order to achieve the above object, the present application provides the following technical solutions:

a method for generating a difference script includes:

acquiring information of database objects contained in a source database and a target database respectively; wherein the database object comprises clusters, tables, and views;

comparing the clusters of the source database with the clusters of the target database to obtain a cluster comparison result, and generating a cluster difference script corresponding to the cluster comparison result;

comparing the table of the source database with the table of the target database to obtain a table comparison result, and generating a table difference script corresponding to the table comparison result;

and comparing the view of the source database with the view of the target database to obtain a view comparison result, and generating a view difference script corresponding to the view comparison result.

A device for generating a difference script includes:

the acquisition unit is used for acquiring the information of the database objects contained in the source database and the target database respectively; wherein the database object comprises clusters, tables, and views;

the first generation unit is used for comparing the clusters of the source database with the clusters of the target database to obtain a cluster comparison result and generating a cluster difference script corresponding to the cluster comparison result;

the second generation unit is used for comparing the table of the source database with the table of the target database to obtain a table comparison result and generating a table difference script corresponding to the table comparison result;

and the third generation unit is used for comparing the view of the source database with the view of the target database to obtain a view comparison result, and generating a view difference script corresponding to the view comparison result.

A computer-readable storage medium including a stored program, wherein the program performs the method of generating a difference script.

According to the technical scheme provided by the application, the information of the database objects contained in the source database and the target database is obtained, wherein the database objects comprise clusters, tables and views. Comparing the clusters of the source database with the clusters of the target database to obtain a cluster comparison result, and generating a cluster difference script corresponding to the cluster comparison result. And comparing the table of the source database with the table of the target database to obtain a table comparison result, and generating a table difference script corresponding to the table comparison result. And comparing the view of the source database with the view of the target database to obtain a view comparison result, and generating a view difference script corresponding to the view comparison result. By means of the scheme, the clustering difference script, the table difference script and the view difference script are obtained by comparing clusters, tables and views contained in the source database and the target database, and compared with the prior art, the workload of difference script generation work can be remarkably reduced, and manual work is assisted to generate the difference script rapidly and accurately.

Drawings

In order to more clearly illustrate the embodiments of the application or the technical solutions in the prior art, the drawings that are required in the embodiments or the description of the prior art will be briefly described, it being obvious that the drawings in the following description are only some embodiments of the application, and that other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.

FIG. 1 is a schematic diagram of a method for generating a difference script according to an embodiment of the present application;

FIG. 2 is a schematic diagram of another method for generating a difference script according to an embodiment of the present application;

FIG. 3 is a schematic diagram of another method for generating a difference script according to an embodiment of the present application;

FIG. 4 is a schematic diagram of another method for generating a difference script according to an embodiment of the present application;

FIG. 5 is a schematic diagram of another method for generating a difference script according to an embodiment of the present application;

FIG. 6 is a schematic diagram of another method for generating a difference script according to an embodiment of the present application;

FIG. 7 is a schematic diagram of another method for generating a difference script according to an embodiment of the present application;

FIG. 8 is a schematic diagram of another method for generating a difference script according to an embodiment of the present application;

Fig. 9 is a schematic diagram of an architecture of a device for generating a difference script according to an embodiment of the present application.

Detailed Description

The following description of the embodiments of the present application will be made clearly and completely with reference to the accompanying drawings, in which it is apparent that the embodiments described are only some embodiments of the present application, but not all embodiments. All other embodiments, which can be made by those skilled in the art based on the embodiments of the application without making any inventive effort, are intended to be within the scope of the application.

The terms related to the present application are as follows:

oracle database: the database of Oracle company can be Oracle database.

Metadata: metadata is data describing information resources and their structures.

Database object: the database object shown in the embodiment of the application at least comprises: CLUSTER (CLUSTER), TABLE (TABLE), INDEX (INDEX), VIEW (VIEW).

Clustering: the Chinese translation of CLUSTER can store a plurality of data table data in the same segment according to a certain rule, and can realize quick retrieval according to a certain scene.

A set of database objects: i.e., a chinese translation of SCHEMA, a database typically corresponds to one SCHEMA, in which all database objects of the database are contained.

SQL: structured query language, abbreviated as Structured Query Language, is used to access data and query, update, and manage relational database systems.

DDL: the data definition language is an abbreviation of Data Definition Language, and main commands of DDL are new Creation (CREATE), change (ALTER), delete (DROP), etc., and DDL is mainly used for defining or changing the database object structure.

Differential DDL SQL script: and running a plurality of DDL SQL sentences (existing terms) on the source database, so that the structures of database objects contained in the SCHEMA corresponding to the source database (namely the database before upgrading) and the target database (namely the database after upgrading) are the same, and taking the set of the DDL SQL sentences as a difference DDL SQL script of the target database relative to the source database.

Source database: selected as the reference database, the application is used to refer to the database prior to upgrade.

Target database: the application is used for referring to the updated database.

Table field information: including the name of the table field, data type, length, precision, whether it can be empty, default values, etc.

Index field information: the method comprises the steps of index field names, index field composition sequences and index field ascending and descending sequence information.

Table partition information: the method comprises the steps of containing a partition field and partition key value information of a table partition.

Index partition information: the partition field containing the index, partition key value information.

Primary key information: namely, the primary key information of the table contains primary key component field names and primary key field component sequence number information.

Table space: is a collection of one or more data files that are used to store data for all data objects in the database, but primarily for tables, so-called tablespaces.

Partitioning: the data in the table is divided into a plurality of parts according to a certain rule and is respectively stored in a table space, which is called partition. Partitioning is one way to improve the performance of a table as the data in the table grows.

Partition type: the partitions of the table include a range partition, a list partition, and a hash partition.

Range partitioning: partitioning is performed according to a range of values for several fields in the table. For example, data with a value less than 100 is stored in partition 1, data with a value greater than or equal to 100 and less than 200 is stored in partition 2, and so on.

List partitioning: the partitioning is explicitly specified according to a specific value of a certain field, for example, data of peaches and oranges in fruits are stored in partition 1, data of watermelons in fruits are stored in partition 2, and so on, so as to obtain each list partition.

Hash partitioning: a method for partitioning according to hash values of specified fields.

Sub-partitions: when the data of the table is large, the data of each partition is still large after the table is partitioned, the data in each partition can be partitioned again according to a certain rule, the data is distributed into a plurality of table spaces, and the partitions under the partitions are called sub-partitions.

Combining partitions: the partition type with sub-partitions under the partition is referred to as a combined partition.

Table metadata: the metadata information of the table includes information such as table field information (all_tab_columns, and the like in parentheses for metadata table names storing corresponding information), table primary KEY information (all_control_columns), table primary KEY field information (all_control_columns), table partition type (all_part_tabs), table partition field (all_part_key_columns), table partition KEY value (all_tab_parameters), and the like.

Index metadata: the metadata information of the index includes information such as index basic information (all_indices), index field information (all_ind_indices), index partition information (all_part_indices), index partition field (all_part_key_indices), index partition KEY value (all_ind_parameters), and the like.

Whether to use a Character (CHAR): this is one attribute of the table field. For the field of the table holding character type data, if it is explicitly indicated whether or not CHAR is used, the data is held per CHAR, otherwise the data is held per BYTE (BYTE) length. For example, a field data type defined as CHAR (10 BYTE) indicates that the field can hold 10 BYTEs of data, 10 characters if English characters are stored, and possibly only 3 if Chinese characters are stored. If defined as CHAR (10 CHAR), this field indicates that 10 characters of data can be stored, which can be a combination of English letters and Chinese characters, with a maximum of 10 Chinese characters stored. The length of a Chinese character is calculated according to CHAR, the length is 1, and if the length is calculated according to BYTE, the length is between 2 and 4 BYTEs, and the specific length is determined by a character set.

All_tabs table: the metadata table for storing all the basic information of the table mainly comprises table names of the table, cluster names used, whether the table is partitioned, whether the table has self-increasing list and other information.

All_tab_columns table: the metadata table for storing column information of the table mainly comprises table names, field data types, field data lengths, field data precision, whether fields can be empty, field default values, whether fields are self-added or not, whether CHAR is used or not and other information. The table also stores column composition information of the aggregation object, including information such as the name of the aggregation object, the field name, the field data type, the field data length, the field data precision, the field sequence number, and the like.

All_structures table: the metadata table for storing constraint information of the table mainly comprises a table name, a constraint name, an index name and a constraint type (the constraint type P indicates that the constraint is a primary key constraint).

All_part_tabs table: the metadata table for storing the basic information of the table partition mainly comprises a table name, a partition type and a sub-partition type.

All_part_key_columns table: metadata tables holding table and index partition field information mainly contain information such as object type (for distinguishing whether a table or an index), object name, field sequence number, and the like.

All_tab_parameters table: the metadata table for storing the table partition key value information mainly comprises a table name, a partition key value and a partition sequence number.

All_subtart_key_columns table: the metadata table for storing the sub-partition field information mainly comprises table name, field sequence number and other information.

All_tab_subtartitons table: the metadata table for storing the sub-partition key value information mainly comprises a table name, a partition key value and a partition sequence number.

All_indices table: the metadata table for storing the index basic information mainly comprises table names, index names, whether unique indexes are used, whether partitions are used and the like.

All_ind_columns table: the metadata table for storing index field information mainly comprises table names, index names, field sequence numbers, field names, field ascending and descending order and other information; and the information of the primary key constraint component fields is also stored, and the ascending and descending order information of the primary key constraint component fields are all ascending and descending order.

All_part_indices table: the metadata table for storing the basic information of the partition index mainly comprises information such as index name, table name, partition type, sub-partition type, partition number, global partition identification and the like.

All_ind_parameters table: the metadata table for storing index partition key value information mainly comprises information such as index name, partition permission number, partition name, partition key value and the like.

All_clusters table: and storing metadata tables of all the clustered objects, wherein the metadata tables mainly comprise information such as clustered object names, clustered types and the like.

All_view table: the metadata table for storing view object definition mainly comprises information such as view names, view definition texts and the like.

As shown in fig. 1, a schematic diagram of a method for generating a difference script according to an embodiment of the present application includes the following steps:

s101: information of database objects contained in each of the source database and the target database, and a metadata table are acquired.

Wherein the database objects include clusters, tables, and views. The metadata table includes at least: all_tabs table, all_tab_columns table, all_structures table, all_part_tabs table, all_part_key_columns table, all_tab_parameters table, all_SUBPART_key_columns table, all_tab_SUBPARTATITITIONS table, all_INDEXES table, all_IND_COLUMNs table.

It should be emphasized that both the source database and the target database are Oracle databases.

For any one of the source database and the target database, the information (for example, cluster names) of each cluster included in the database object set corresponding to any one can be obtained by querying an all_clusters table of any one, and each cluster can be further ordered according to the cluster names, specifically, the information of each cluster included in the database is obtained, and implementation logic (namely, SQL statement) can be: SELECT cluse_name FROM all_ CLUSTERS WHERE OWNER =: schema_ NAME ORDER BY CLUSTER _NAME.

For any one of the source database and the target database, the information (for example, table name, whether partition table, cluster name of the cluster to which the partition table belongs, whether self-increment column exists, etc.) of each table included in the database object set corresponding to any one can be obtained by querying all_tabs table of any one, specifically, the information of each table included in any one can be obtained, and implementation logic (that is, SQL statement) can be: SELECT table_name, cluster_ NAME, PARTITIONED, HAS _ IDENTITY FROM ALL _ TABLES WHERE OWNER =: schema_name.

For any one of the source database and the target database, the information (for example, VIEW name, VIEW definition information, etc.) of each VIEW included in the database object set corresponding to any one can be obtained by querying the all_view table of any one, specifically, the information of each VIEW included in any one can be obtained, and implementation logic (i.e., SQL statement) can be: SELECT VIEW _NAME, TEXTFROM; all_ VIEWS WHERE owner=: schema_name.

S102: comparing the clusters of the source database with the clusters of the target database to obtain a cluster comparison result, and generating a cluster difference script corresponding to the cluster comparison result.

The specific implementation process of comparing the clusters of the source database with the clusters of the target database to obtain a cluster comparison result and generating a cluster difference script corresponding to the cluster comparison result can be seen in the steps shown in fig. 2 and the explanation of the steps.

S103: and comparing the table of the source database with the table of the target database to obtain a table comparison result, and generating a table difference script corresponding to the table comparison result.

The specific implementation process of comparing the table of the source database with the table of the target database to obtain a table comparison result and generating a table difference script corresponding to the table comparison result can be referred to the steps shown in fig. 3 and the explanation of the steps.

S104: and comparing the view of the source database with the view of the target database to obtain a view comparison result, and generating a view difference script corresponding to the view comparison result.

Optionally, the cluster difference script, the table difference script and the view difference script are executed on the source database, so that the structure information of the clusters contained in the source database is the same as the structure information of the clusters contained in the target database, the structure information of the tables contained in the source database is the same as the structure information of the tables contained in the target database, and the structure information of the views contained in the source database is the same as the structure information of the views contained in the target database.

The specific implementation process of comparing the view of the source database with the view of the target database to obtain a view comparison result and generating the view difference script corresponding to the view comparison result can be referred to the steps shown in fig. 8 and the explanation of the steps.

Based on the flow shown in S101-S104, the following beneficial effects can be achieved in this embodiment:

1. the working intensity of programmers is reduced, and the working efficiency is improved.

Specifically, the conventional generation of the table and index difference DDL SQL scripts under the SCHEMA of two databases is to manually compare metadata information of the table and index in the two databases, and write the difference DDL SQL scripts of the two databases according to the found difference information. Because the metadata information of the table and the index is more and is stored in a plurality of metadata tables of the database, and the difference information of part of the table and the index is small in comparison and is not easy to identify, the manual comparison mode has high working strength and is likely to be in error. For upgrading a large system, the added tables and indexes are more, the original tables and indexes are not less optimized, and the workload of difference comparison is larger. The DDL SQL script generating tool can automatically generate corresponding sentences according to the difference information, does not miss any difference information, is high in speed and correct, and improves the working efficiency of programmers.

2. The requirements of programmers on the SQL grammar mastering degree are reduced.

Specifically, in the SQL statement, the grammar of the CREATE TABLE statement is relatively simple and commonly used, but the grammar of the ALTER TABLE statement is relatively complex, and the metadata information has more difference types, so that a programmer is required to write a complex and correct ALTER TABLE statement according to the difference information, and the requirement on the programmer is higher. The DDL SQL script generating tool can automatically generate complex and correct ALTER TABLE sentences, and can reduce the grasping degree requirement of programmers on SQL grammar.

3. The correctness of the script and the efficiency of the script verification test are improved.

Specifically, because manually generated DDL SQL scripts may have errors due to manual input, missing difference information, etc., which cause differences between DDL SQL and the correct script, it is necessary to verify the correctness thereof through testing. Each verification test takes some time. And the manually generated script, even if passing the verification test, is not necessarily the correct script that meets the expectations. For example, because of the index added or adjusted by the performance optimization, if the DDL SQL script does not contain the information, the functional verification test cannot find out, and the script has performance hidden trouble after being put into production. The script generated by the DDL SQL script generating tool contains all the difference information, no omission exists, the generated SQL script is correct, and the generated SQL script can pass once during verification test, so that the correctness of the script and the efficiency of verification test are improved.

In summary, by using the scheme shown in the embodiment, the clustered difference script, the table difference script and the view difference script are obtained by comparing the clusters, the tables and the views contained in the source database and the target database, and compared with the prior art, the workload of the difference script generation work can be significantly reduced, and the manual generation of the difference script can be assisted rapidly and accurately.

As shown in fig. 2, a schematic diagram of another method for generating a difference script according to an embodiment of the present application includes the following steps:

s201: a first set of clusters is constructed based on the clusters contained in the source database and a second set of clusters is constructed based on the clusters contained in the target database.

S202: comparing the first cluster set with the second cluster set to obtain a cluster comparison result.

The cluster comparison result comprises a first cluster, a second cluster, a third cluster and a fourth cluster; the first cluster is a cluster which is contained in the source database and is not contained in the target database; the second cluster is a cluster which is not contained in the source database and is contained in the target database; the third cluster belongs to the source database, the fourth cluster belongs to the target database, and the cluster name of the third cluster is the same as the cluster name of the fourth cluster.

S203: based on the first cluster, a first cluster difference script is generated.

The first CLUSTER difference script comprises a DROP CLUSTER command for deleting the first CLUSTER. Specifically, the first clustering script may be: DROP clutter: cluster_name.

S204: and reading the composition field information of the second cluster from an ALL_TAB_COLUMNS table of the target database, and generating a second cluster difference script based on the composition field information of the second cluster.

The second CLUSTER difference script comprises a CREATE CLUSTER command, and is used for creating a second CLUSTER. Specifically, the SQL statement for querying the information of the constituent fields of the second cluster may be:

SELECT COLUMN_NAME，DATA_TYPE，DATA_LENGTH，DATA_PRECISION FROM ALL_TAB_COLUMNS WHERE OWNER＝：schema_name AND TABLE_NAME＝：cluster_name ORDER BY COLUMN_ID。

the second cluster difference script may be:

CREATE CLUSTER:cluster_name(:column_name:data_type(:data_length))。

s205: and reading the composition field information of the third cluster from an ALL_TAB_COLUMNS table of the source database.

The SQL statement that queries the constituent field information of the third cluster may be:

after S205 is performed, S206 is continued.

S206: and reading the composition field information of the fourth cluster from an ALL_TAB_COLUMNS table of the target database.

Wherein, for any one of the third cluster and the fourth cluster, the composition field information of any one includes at least: the field name, data type, data length, and precision information that make up the field.

S207: comparing the composition field information of the third cluster with the composition field information of the fourth cluster.

S208: and generating a third cluster difference script and a fourth cluster difference script in the case that the composition field information of the third cluster is different from the composition field information of the fourth cluster.

The third CLUSTER difference script comprises a DROP CLUSTER command for deleting the third CLUSTER. The fourth CLUSTER difference script contains a CREATE CLUSTER command for creating a fourth CLUSTER.

Optionally, if the composition field information of the third cluster is the same as the composition field information of the fourth cluster, no difference script need be generated.

In summary, by using the scheme shown in the embodiment, the clusters of the source database and the clusters of the target database can be effectively compared to obtain a cluster comparison result, and a cluster difference script corresponding to the cluster comparison result is generated.

As shown in fig. 3, a schematic diagram of another method for generating a difference script according to an embodiment of the present application includes the following steps:

s301: the first set of tables is constructed based on the tables contained in the source database and the second set of tables is constructed based on the tables contained in the target database.

S302: and comparing the first table set with the second table set to obtain a table comparison result.

The table comparison result comprises a first table, a second table, a third table and a fourth table; the first table is a table which is contained in the source database and is not contained in the target database; the second table is a table which is not contained in the source database and is contained in the target database; the third table belongs to the source database, the fourth table belongs to the target database, and the table names of the third table are the same as those of the fourth table.

S303: based on the first table, a first table difference script is generated.

The first TABLE difference script comprises a DROP TABLE command for deleting the first TABLE. Specifically, the first table difference script may be: DROP TABLE: table_name.

S304: based on the second table, a second table difference script is generated.

The second TABLE difference script comprises a CREATE TABLE command for creating a second TABLE.

It should be noted that, for a specific implementation process of generating the second table difference script, reference may be made to the steps shown in fig. 4 and explanation of the steps.

S305: information of ALL indexes of the second table is read from the all_index table of the target database.

The information of the index at least comprises an index name, an index type, uniqueness, partition or the like. Specifically, reading information of all indexes of the second table, and implementing logic (i.e., SQL statement) may be:

SELECT INDEX_NAME，INDEX_TYPE，UNIQUENESS，PARTITIONED FROM ALL_INDEXES WHERE OWNER＝：schema_name AND TABLE_NAME＝：table_name。

S306: a third table difference script is generated corresponding to each index of the second table.

The third table difference script includes a CREATE INDEX command for creating an INDEX of the second table.

Generally, if the index of the second table is not recorded in the all_index table of the target database, the third table difference script does not need to be created.

The specific implementation process of generating the third table difference script corresponding to each index of the second table may refer to the steps shown in fig. 5 and the explanation of the steps.

S307: partition information of the third table is read from the metadata table of the source database, and partition information of the fourth table is read from the metadata table of the target database.

Wherein, for any one of the third table and the fourth table, the partition information of any one includes at least: whether partition attribute information (including any one of a partition table and a non-partition table), partition basic information, partition field basic information, partition key value information.

Generally, whether partition attribute information can be read from the all_tabs table, partition basic information can be read from the all_part_tabs table, partition field basic information can be read from the all_part_key_columns table, and partition KEY value information can be read from the all_tab_part_tabs table.

S308: the partition information of the third table is compared with the partition information of the fourth table.

S309: in the case where the partition information of the third table is not identical to the partition information of the fourth table, a fourth table difference script and a fifth table difference script are generated.

The fourth table difference script is used for deleting the third table, and the fifth table difference script is used for creating the fourth table.

S310: and under the condition that the partition information of the third table is the same as the partition information of the fourth table, comparing the fields of the third table with the fields of the fourth table to obtain a field comparison result, and generating a table difference script corresponding to the field comparison result.

After S310 is performed, S311 is continued.

The specific implementation process of comparing the fields of the third table with the fourth target fields to obtain the field comparison result and generating the table difference script corresponding to the field comparison result can be referred to the steps shown in fig. 6 and the explanation of the steps.

S311: and comparing the index of the third table with the index of the fourth table to obtain an index comparison result, and generating a table difference script corresponding to the index comparison result.

The specific implementation process of comparing the index of the third table with the index of the fourth table to obtain the index comparison result and generating the table difference script corresponding to the index comparison result can be referred to the steps shown in fig. 7 and the explanation of the steps.

In summary, by using the scheme shown in the embodiment, the table difference script corresponding to the table comparison result can be effectively generated.

As shown in fig. 4, a schematic diagram of another method for generating a difference script according to an embodiment of the present application includes the following steps:

s401: and reading the table basic information of the second table from the ALL_TABLES table of the target database, and judging whether the second table is a partition table according to the table basic information of the second table.

If the second table is a partition table, S402 is executed, otherwise S408 is executed.

S402: and reading partition basic information of the second table from the ALL_TABLES table of the target database.

Wherein the partition basic information of the second table at least includes: table name, partition type, sub-partition type, partition number, sub-partition number. Specifically, the partition basic information of the second table is read, and the implementation logic (i.e. SQL statement) may be:

SELECT TABLE_NAME，PARTITIONING_TYPE，SUBPARTITIONING_TYPE，PARTITION_COUNT，DEF_SUBPARTITION_COUNT FROM ALL_PART_TABLES WHERE OWNER＝：schema_name AND TABLE_NAME＝：table_name。

s403: the partition field information of the second table is read from the all_part_key_column table of the target database.

Wherein the partition field information of the second table includes at least: table name, partition field serial number, etc. Specifically, reading the partition field information of the second table, and implementing logic (i.e., SQL statement) may be:

SELECT NAME，COLUMN_NAME，COLUMN_POSITION FROM ALL_PART_KEY_COLUMNS WHERE OBJECT_TYPE＝'TABLE'AND OWNER＝：schema_name AND NAME＝：table_name；ORDER BY COLUMN_POSITION。

S404: and reading partition key value information of the second table from an ALL_TAB_PARTITITIONS table of the target database.

Wherein the partition key value information of the second table at least includes: table name, partition key value, partition sequence number. Specifically, reading partition key value information of the second table, and implementing logic (i.e., SQL statement) may be:

SELECT TABLE_NAME，PARTITION_NAME，HIGH_VALUE，PARTITION_POSITION FROM ALL_TAB_PARTITIONS；WHERE OWNER＝：schema_name AND TABLE_NAME＝：table_name。

s405: and judging whether the second table is a combined partition according to the partition basic information of the second table.

If the second table is a combined partition, S406 is performed, otherwise S408 is performed.

And if the type of the sub-partition shown by the partition basic information is not NONE, determining that the second table is a combined partition, otherwise, determining that the second table is not the combined partition.

S406: and reading the sub-partition field information of the second table from the ALL_SUBPART_KEY_COLUMNS table of the target database.

Wherein the sub-partition field information of the second table includes at least: table name, sub-partition field sequence number. Specifically, reading the sub-partition field information of the second table, the implementation logic (i.e., SQL statement) may be:

SELECT NAME，COLUMN_NAME，COLUMN_POSITION FROM ALL_SUBPART_KEY_COLUMNS WHERE OBJECT_TYPE＝'TABLE'AND OWNER＝：schema_name AND NAME＝：table_name；ORDER BY COLUMN_POSITION。

s407: and reading the sub-partition key value information of the second table from the ALL_TAB_SUBPARTATIONS table of the target database.

After S407 is performed, S408 is continued.

Wherein the sub-partition key value information of the second table at least includes: table name, sub-partition key value, sub-partition sequence number. Specifically, reading the sub-partition key information of the second table, and implementing logic (i.e., SQL statement) may be:

SELECT TABLE_NAME，PARTITION_NAME，SUBPARTITION_NAME，HIGH_VALUE，SUBPARTITION_POSITION；FROM ALL_TAB_SUBPARTITIONS WHERE；OWNER＝：schema_name AND TABLE_NAME＝：table_name；ORDER BY PARTITION_NAME，SUBPARTITION_POSITION。

s408: the field information of the second table is read from the all_tab_columns table of the target database.

Wherein the field information of the second table includes at least: table name, field data type, field data length, field data precision, whether a field is nullable, field default values, whether a field is self-increment, whether CHAR is used. Specifically, reading field information of the second table, and implementing logic (i.e., SQL statement) may be:

SELECT COLUMN_NAME，DATA_TYPE，DATA_LENGTH，DATA_PRECISION，NULLABLE，DATA_DEFAULT，CHAR_USED，IDENTITY_COLUMN，COLUMN_ID FROM ALL_TAB_COLUMNS；WHERE OWNER＝：schema_name AND TABLE_NAME＝：table_name；ORDER BY COLUMN_ID。

s409: and reading the primary key constraint information of the second table from the ALL_CONSTRATION table of the target database.

Wherein the primary key constraint information of the second table includes at least: table name, primary key constraint name, primary key index name. Specifically, reading the primary key constraint information of the second table, and implementing logic (i.e., SQL statement) may be:

SELECT CONSTRAINT_NAME，INDEX_NAME FROM ALL_CONSTRAINTS；WHERE OWNER＝：schema_name AND TABLE_NAME＝：table_name AND CONSTRAINT_TYPE＝'P'。

s410: and reading the main key composition field information of the second table from the ALL_IND_COLUMNS table of the target database.

Wherein the primary key composition field information of the second table includes at least: the main key composition field, the arrangement order of the main key composition field, the table name, the main key index name, the field sequence number and the field ascending and descending order. Specifically, reading the primary key composition field information of the second table, and implementing logic (i.e., SQL statement) may be:

SELECT COLUMN_NAME，DESCEND，COLUMN_POSITION；FROM ALL_IND_COLUMNS；WHERE OWNER＝：schema_name AND TABLE_NAME＝：table_name AND INDEX_NAME＝：index_name；ORDER BY COLUMN_POSITION。

S411: generating a second table differential script based on the table basic information, the partition field information, the partition key value information, the sub-partition field information, the sub-partition key value information, the field information, the primary key constraint information, and the primary key composition field information of the second table.

In summary, by using the scheme of the present embodiment, the second table difference script can be effectively generated.

As shown in fig. 5, a schematic diagram of another method for generating a difference script according to an embodiment of the present application includes the following steps:

s501: for each index, the constituent field information of the index is read from the all_ind_columns table of the target database.

Wherein, the composition field information of the index at least comprises: table name, index name, field sequence number, field ascending and descending order. Specifically, reading the constituent field information of the index, the implementation logic (i.e., SQL statement) may be:

SELECT COLUMN_NAME，DESCEND，COLUMN_POSITION；FROM ALL_IND_COLUMNS；WHERE OWNER＝：schema_name AND TABLE_NAME＝：table_name AND INDEX_NAME＝：index_name；ORDERBY COLUMN_POSITION。

s502: and judging whether the index is a global partition index according to the component field information of the index.

If the index is the global partition index, S503 is executed, otherwise S507 is executed.

S503: the indexed partition base information is read from the ALL_PART_INDEXES table of the target database.

Wherein, the partition basic information of the index at least comprises: index name, table name, partition type, partition number, global partition identification. Specifically, the partition basic information of the index is read, and the implementation logic (i.e. SQL statement) may be:

SELECT INDEX_NAME，PARTITIONING_TYPE，PARTITION_COUNT，LOCALITY FROM ALL_PART_INDEXES；WHERE OWNER＝：schema_name AND TABLE_NAME＝：table_name；AND INDEX_NAME＝：index_name。

s504: the partition field information of the index is read from the all_part_key_column table of the target database.

Wherein the partition field information of the index at least includes: table name, index name, partition field sequence number. Specifically, reading the partition field information of the index, the implementation logic (i.e., SQL statement) may be:

SELECT NAME，COLUMN_NAME,COLUMN_POSITION FROM ALL_PART_KEY_COLUMNS WHERE OBJECT_TYPE＝'INDEX'AND OWNER＝：schema_name AND NAME＝：index_name；ORDER BY COLUMN_POSITION。

s505: and reading the partition key value information of the index from an ALL_IND_PARTITITIONS table of the target database.

Wherein the indexed partition key value information at least comprises: index name, partition serial number, partition name, partition key value. Specifically, reading the partition key value information of the index, and implementing logic (i.e., SQL statement) may be:

SELECT INDEX_NAME，PARTITION_NAME，HIGH_VALUE，PARTITION_POSITION；FROM ALL_IND_PARTITIONS；WHERE OWNER＝：schema_name AND INDEX_NAME＝：index_name；ORDER BY PARTITION_POSITION。

s506: and generating a third table difference script corresponding to the index based on the composition field information, the partition basic information, the partition field information and the partition key value information of the index.

S507: and generating a third table difference script corresponding to the index based on the composition field information of the index.

In summary, with the scheme of the present embodiment, the third table difference script corresponding to each index can be effectively generated.

As shown in fig. 6, a schematic diagram of another method for generating a difference script according to an embodiment of the present application includes the following steps:

s601: information of ALL fields of the third table is read from the all_tab_columns table of the source database, and information of ALL fields of the fourth table is read from the all_tab_columns table of the target database.

The information of the field included in either one of the third table and the fourth table includes at least: field name, data type, length, precision, whether empty or not, default values.

S602: the first set of fields is constructed based on the respective fields contained in the third table, and the second set of fields is constructed based on the respective fields contained in the fourth table.

S603: and comparing the first field set with the second field set to obtain a field comparison result.

The field comparison result comprises a first field, a second field, a third field and a fourth field; the first field is a field which is contained in the third table and is not contained in the fourth table; the second field is a field which is not contained in the third table and is contained in the fourth table; the third field belongs to the third table, the fourth field belongs to the fourth table, and the field name of the third field is the same as the field name of the fourth field.

S604: based on the first field, a sixth table difference script is generated.

Wherein the sixth difference script comprises an ALTER TABLE command for deleting the first field.

S605: based on the second field, a seventh table difference script is generated.

The seventh TABLE difference script includes an ALTER TABLE command for creating a second field.

S606: information of the third field is obtained from the third table, and information of the fourth field is obtained from the fourth table.

S607: and generating an eighth table difference script when the information of the third field is different from the information of the fourth field.

After S607 is performed, S608 is continued.

Wherein the eighth TABLE differential script comprises an ALTER TABLE command for modifying information of the third field using information of the fourth field.

Optionally, if the information of the third field is the same as the information of the fourth field, a difference script does not need to be generated.

S608: the primary key CONSTRAINTS of the third table are read from the all_structures table of the source database, and the primary key CONSTRAINTS of the fourth table are read from the all_structures table of the target database.

S609: and comparing the primary key constraint of the third table with the primary key constraint of the fourth object to obtain a primary key constraint comparison result.

The main key constraint comparison result comprises a first main key constraint, a second main key constraint, a third main key constraint and a fourth main key constraint; the first primary key constraint is a primary key constraint that the third table contains and the fourth table does not contain; the second primary key constraint is a primary key constraint that the third table does not contain and the fourth table contains; the third main key constraint is attributed to the third table, the fourth main key constraint is attributed to the fourth table, and the constraint name of the third main key constraint is the same as the constraint name of the fourth main key constraint.

S610: a ninth table differential script is generated based on the first primary key constraint.

Wherein the ninth TABLE differential script comprises an ALTER TABLE command for deleting the first primary key constraint.

S611: a tenth table difference script is generated based on the second primary key constraint.

The tenth TABLE difference script comprises an ALTER TABLE command for creating a second primary key constraint.

S612: information of a field to which the third primary key constraint belongs is obtained from the third table, and information of a field to which the fourth primary key constraint belongs is obtained from the fourth table.

S613: and generating an eleventh table difference script and a twelfth table difference script in the case that the information of the field to which the third primary key constraint belongs is not identical to the information of the field to which the fourth primary key constraint belongs.

Wherein the eleventh TABLE difference script comprises an ALTER TABLE command for deleting the third primary key constraint. The twelfth TABLE difference script contains an ALTER TABLE command for creating a fourth primary key constraint.

Optionally, if the information of the field to which the third primary key constraint belongs is the same as the information of the field to which the fourth primary key constraint belongs, no difference script needs to be generated.

In summary, by using the scheme shown in the embodiment, the table difference script corresponding to the field comparison result can be effectively generated.

As shown in fig. 7, a schematic diagram of another method for generating a difference script according to an embodiment of the present application includes the following steps:

s701: information of ALL INDEXES of the third table is read from all_indexes tables of the source database, and information of ALL INDEXES of the fourth table is read from all_indexes tables of the target database.

S702: the first index set is constructed based on the respective indexes contained in the third table, and the second index set is constructed based on the respective indexes contained in the fourth table.

S703: and comparing the first index set with the second index set to obtain an index comparison result.

The index comparison result comprises a first index, a second index, a third index and a fourth index; the first index is an index that the third table contains and the fourth table does not contain; the second index is an index that the third table does not contain and the fourth table contains; the third index belongs to the third table, the fourth index belongs to the fourth table, and the index name of the third index is the same as the index name of the fourth index.

S704: a thirteenth table difference script is generated based on the first index.

Wherein the thirteenth table difference script includes a DROP INDEX command for deleting the first INDEX.

S705: a fourteenth table difference script is generated based on the second index.

The fourteenth table difference script includes a CREATE INDEX command for creating a second INDEX.

It should be noted that, for a specific implementation process of generating the sixteenth table difference script, reference may be made to the steps shown in fig. 5 and explanation of the steps.

S706: the component field information of the third index is obtained from the third table, and the component field information of the fourth index is obtained from the fourth table.

S707: in the case where the constituent field information of the third index is not identical to the constituent field information of the fourth index, a fifteenth table difference script is generated, and a sixteenth table difference script is generated.

Wherein the fifteenth table difference script includes a DROP INDEX command for deleting the third INDEX. The sixteenth table difference script contains a CREATE INDEX command for creating a fourth INDEX.

It should be noted that, for a specific implementation process of generating the eighteenth table difference script, reference may be made to the steps shown in fig. 5 and explanation of the steps.

Optionally, if the component field information of the third index is the same as the component field information of the fourth index, no difference script need be generated.

In summary, by using the scheme shown in the embodiment, the table difference script corresponding to the index comparison result can be effectively generated.

As shown in fig. 8, a schematic diagram of another method for generating a difference script according to an embodiment of the present application includes the following steps:

s801: the first set of views is constructed based on the respective views contained in the source database and the second set of views is constructed based on the respective views contained in the target database.

S802: and comparing the first view set with the second view set to obtain a view comparison result.

The view comparison result comprises a first view, a second view, a third view and a fourth view; the first view is a view which is contained in the source database and is not contained in the target database; the second view is a view which is not contained in the source database and is contained in the target database; the third view belongs to the source database, the fourth view belongs to the target database, and the view names of the third view and the fourth view are the same.

S803: based on the first view, a first view difference script is generated.

The first VIEW difference script comprises a DROP VIEW command for deleting the first VIEW.

S804: based on the second view, a second view difference script is generated.

The second view difference script comprises CREATE VIEW commands for creating a second view.

S805: view definition information of a third view is acquired from a source database, and view definition information of a fourth view is acquired from a target database.

S806: in case that the view definition information of the third view is not identical to the view definition information of the fourth view, a third view difference script is generated, and a fourth view difference script is generated.

Wherein the third VIEW difference script comprises a DROP VIEW command for deleting the third VIEW. The fourth view difference script contains CREATE VIEW commands for creating a fourth view.

Optionally, if the view definition information of the third view is the same as the view definition information of the fourth view, no difference script need be generated.

In summary, by using the scheme shown in the embodiment, the view difference script corresponding to the view comparison result can be effectively generated.

Corresponding to the method for generating the difference script provided by the embodiment of the application, the application also provides a device for generating the difference script.

As shown in fig. 9, an architecture diagram of a device for generating a difference script according to an embodiment of the present application includes:

an obtaining unit 100, configured to obtain information of database objects included in each of the source database and the target database; wherein the database objects include clusters, tables, and views.

The first generating unit 200 is configured to compare the cluster of the source database with the cluster of the target database to obtain a cluster comparison result, and generate a cluster difference script corresponding to the cluster comparison result.

The first generating unit 200 is specifically configured to: constructing a first cluster set based on each cluster contained in the source database, and constructing a second cluster set based on each cluster contained in the target database; comparing the first cluster set with the second cluster set to obtain a cluster comparison result; the cluster comparison result comprises a first cluster, a second cluster, a third cluster and a fourth cluster; the first cluster is a cluster which is contained in the source database and is not contained in the target database; the second cluster is a cluster which is not contained in the source database and is contained in the target database; the third cluster belongs to a source database, the fourth cluster belongs to a target database, and the cluster name of the third cluster is the same as the cluster name of the fourth cluster; generating a first cluster difference script based on the first cluster; the first cluster difference script is used for deleting the first clusters; reading the composition field information of the second cluster from the metadata table of the target database, and generating a second cluster difference script based on the composition field information of the second cluster; the second cluster difference script is used for creating a second cluster; reading the composition field information of the third cluster from the metadata table of the source database, and reading the composition field information of the fourth cluster from the metadata table of the target database; comparing the composition field information of the third cluster with the composition field information of the fourth cluster; generating a third cluster difference script and a fourth cluster difference script under the condition that the composition field information of the third cluster is different from the composition field information of the fourth cluster; the third cluster difference script is used for deleting the third cluster; the fourth cluster difference script is used for creating a fourth cluster.

The second generating unit 300 is configured to compare the table of the source database with the table of the target database, obtain a table comparison result, and generate a table difference script corresponding to the table comparison result.

The second generating unit 300 is specifically configured to: constructing a first table set based on each table contained in the source database, and constructing a second table set based on each table contained in the target database; comparing the first table set with the second table set to obtain a table comparison result; the table comparison result comprises a first table, a second table, a third table and a fourth table; the first table is a table which is contained in the source database and is not contained in the target database; the second table is a table which is not contained in the source database and is contained in the target database; the third table belongs to the source database, the fourth table belongs to the target database, and the table names of the third table are the same as those of the fourth table; generating a first table difference script based on the first table; the first table difference script is used for deleting the first table; generating a second table difference script based on the second table; the second table difference script is used for creating a second table; reading information of all indexes of a second table from a metadata table of a target database; generating a third table difference script corresponding to each index of the second table; the third table difference script is used for creating an index of a second table; reading partition information of a third table from a metadata table of a source database, and reading partition information of a fourth table from a metadata table of a target database; comparing the partition information of the third table with the partition information of the fourth table; generating a fourth table difference script and a fifth table difference script when the partition information of the third table is different from the partition information of the fourth table; the fourth table difference script is used for deleting the third table; the fifth table difference script is used for creating a fourth table; comparing the fields of the third table with the fields of the fourth table under the condition that the partition information of the third table is the same as the partition information of the fourth table, obtaining a field comparison result, and generating a table difference script corresponding to the field comparison result; and comparing the index of the third table with the index of the fourth table to obtain an index comparison result, and generating a table difference script corresponding to the index comparison result.

The second generating unit 300 specifically is configured to: reading the table basic information of the second table from the metadata table of the target database, and judging whether the second table is a partition table according to the table basic information of the second table; reading partition basic information of the second table from a metadata table of the target database under the condition that the second table is a partition table; reading partition field information of a second table from a metadata table of the target database; reading partition key value information of a second table from a metadata table of the target database; judging whether the second table is a combined partition according to the partition basic information of the second table; reading the sub-partition field information of the second table from the metadata table of the target database under the condition that the second table is a combined partition; reading sub-partition key value information of a second table from a metadata table of a target database; reading field information of the second table from a metadata table of the target database in the case that the second table is not a partition table and the second table is not a combined partition; reading primary key constraint information of a second table from a metadata table of a target database; reading the primary key composition field information of the second table from the metadata table of the target database; generating a second table differential script based on the table basic information, the partition field information, the partition key value information, the sub-partition field information, the sub-partition key value information, the field information, the primary key constraint information, and the primary key composition field information of the second table.

The second generating unit 300 specifically is configured to: for each index, reading the component field information of the index from the metadata table of the target database; judging whether the index is a global partition index according to the composition field information of the index; under the condition that the index is a global partition index, reading the partition basic information of the index from a metadata table of a target database; reading the partition field information of the index from a metadata table of the target database; reading the indexed partition key value information from a metadata table of a target database; generating a third table difference script corresponding to the index based on the composition field information, the partition basic information, the partition field information and the partition key value information of the index; and generating a third table difference script corresponding to the index based on the component field information of the index when the index is not the global partition index.

The second generating unit 300 specifically is configured to: reading information of all fields of the third table from a metadata table of the source database, and reading information of all fields of the fourth table from a metadata table of the target database; constructing a first field set based on each field contained in the third table, and constructing a second field set based on each field contained in the fourth table; comparing the first field set with the second field set to obtain a field comparison result; the field comparison result comprises a first field, a second field, a third field and a fourth field; the first field is a field which is contained in the third table and is not contained in the fourth table; the second field is a field which is not contained in the third table and is contained in the fourth table; the third field belongs to a third table, the fourth field belongs to a fourth table, and the field name of the third field is the same as the field name of the fourth field; generating a sixth table difference script based on the first field; the sixth difference script is used for deleting the first field; generating a seventh table difference script based on the second field; the seventh table difference script is used for creating a second field; acquiring information of a third field from a third table and acquiring information of a fourth field from a fourth table; generating an eighth table difference script when the information of the third field is different from the information of the fourth field; the eighth table difference script is used for changing the information of the third field by using the information of the fourth field; reading the primary key constraint of the third table from the metadata table of the source database, and reading the primary key constraint of the fourth table from the metadata table of the target database; comparing the primary key constraint of the third table with the primary key constraint of the fourth table to obtain a primary key constraint comparison result; the main key constraint comparison result comprises a first main key constraint, a second main key constraint, a third main key constraint and a fourth main key constraint; the first primary key constraint is a primary key constraint that the third table contains and the fourth table does not contain; the second primary key constraint is a primary key constraint that the third table does not contain and the fourth table contains; the third main key constraint is attributed to the third table, the fourth main key constraint is attributed to the fourth table, and the constraint name of the third main key constraint is the same as the constraint name of the fourth main key constraint; generating a ninth table difference script based on the first primary key constraint; the ninth table difference script is used for deleting the first primary key constraint; generating a tenth table difference script based on the second primary key constraint; the tenth table difference script is used for creating a second primary key constraint; acquiring information of a field to which the third primary key constraint belongs from a third table, and acquiring information of a field to which the fourth primary key constraint belongs from a fourth table; generating an eleventh table difference script and a twelfth table difference script under the condition that the information of the field to which the third main key constraint belongs is different from the information of the field to which the fourth main key constraint belongs; the eleventh table difference script is used for deleting the third primary key constraint; the twelfth table difference script is used for creating a fourth primary key constraint.

The second generating unit 300 specifically is configured to: reading information of all indexes of a third table from a metadata table of a source database, and reading information of all indexes of a fourth table from a metadata table of a target database; constructing a first index set based on each index contained in the third table, and constructing a second index set based on each index contained in the fourth table; comparing the first index set with the second index set to obtain an index comparison result; the index comparison result comprises a first index, a second index, a third index and a fourth index; the first index is an index that the third table contains and the fourth table does not contain; the second index is an index that the third table does not contain and the fourth table contains; the third index belongs to a third table, the fourth index belongs to a fourth table, and the index name of the third index is the same as the index name of the fourth index; generating a thirteenth table difference script based on the first index; the thirteenth table difference script is used for deleting the first index; generating a fourteenth table difference script based on the second index; the fourteenth table difference script is used for creating a second index; acquiring the component field information of the third index from the third table, and acquiring the component field information of the fourth index from the fourth table; generating a fifteenth table difference script and a sixteenth table difference script in case that the composition field information of the third index is not identical to the composition field information of the fourth index; the fifteenth table difference script is used for deleting the third index; the sixteenth table difference script is used to create a fourth index.

And the third generating unit 400 is configured to compare the view of the source database with the view of the target database, obtain a view comparison result, and generate a view difference script corresponding to the view comparison result.

The third generating unit 400 is specifically configured to: constructing a first view set based on each view contained in the source database, and constructing a second view set based on each view contained in the target database; comparing the first view set with the second view set to obtain a view comparison result; the view comparison result comprises a first view, a second view, a third view and a fourth view; the first view is a view which is contained in the source database and is not contained in the target database; the second view is a view which is not contained in the source database and is contained in the target database; the third view belongs to the source database, the fourth view belongs to the target database, and the view names of the third view and the fourth view are the same; generating a first view difference script based on the first view; the first view difference script is used for deleting the first view; generating a second view difference script based on the second view; the second view difference script is used for creating a second view; obtaining view definition information of a third view from a source database and view definition information of a fourth view from a target database; generating a third view difference script and a fourth view difference script in the case that the view definition information of the third view is different from the view definition information of the fourth view; the third view difference script is used for deleting the third view; the fourth view difference script is used to create a fourth view.

The application also provides a computer readable storage medium, which comprises a stored program, wherein the program executes the method for generating the difference script.

The functions of the methods of embodiments of the present application, if implemented in the form of software functional units and sold or used as a stand-alone product, may be stored on a computing device readable storage medium. Based on such understanding, a part of the present application that contributes to the prior art or a part of the technical solution may be embodied in the form of a software product stored in a storage medium, comprising several instructions for causing a computing device (which may be a personal computer, a server, a mobile computing device or a network device, etc.) to execute all or part of the steps of the method described in the embodiments of the present application. And the aforementioned storage medium includes: a U-disk, a removable hard disk, a Read-Only Memory (ROM), a random access Memory (RAM, random Access Memory), a magnetic disk, or an optical disk, or other various media capable of storing program codes.

In this specification, each embodiment is described in a progressive manner, and each embodiment is mainly described in a different point from other embodiments, so that the same or similar parts between the embodiments are referred to each other.

The previous description of the disclosed embodiments is provided to enable any person skilled in the art to make or use the present application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be applied to other embodiments without departing from the spirit or scope of the application. Thus, the present application is not intended to be limited to the embodiments shown herein but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. The method for generating the difference script is characterized by comprising the following steps:

constructing a first cluster set based on each cluster contained in the source database, and constructing a second cluster set based on each cluster contained in the target database;

Comparing the first cluster set with the second cluster set to obtain a cluster comparison result; the cluster comparison result comprises a first cluster, a second cluster, a third cluster and a fourth cluster; the first cluster is a cluster which is contained in the source database and is not contained in the target database; the second cluster is a cluster which is not contained in the source database and is contained in the target database; the third cluster belongs to the source database, the fourth cluster belongs to the target database, and the cluster name of the third cluster is the same as the cluster name of the fourth cluster;

generating a first cluster difference script based on the first cluster; the first cluster difference script is used for deleting the first cluster;

reading the composition field information of the second cluster from the metadata table of the target database, and generating a second cluster difference script based on the composition field information of the second cluster; the second cluster difference script is used for creating the second cluster;

reading the composition field information of the third cluster from the metadata table of the source database, and reading the composition field information of the fourth cluster from the metadata table of the target database;

Comparing the composition field information of the third cluster with the composition field information of the fourth cluster;

generating a third cluster difference script and a fourth cluster difference script under the condition that the composition field information of the third cluster is different from the composition field information of the fourth cluster; the third cluster difference script is used for deleting the third cluster; the fourth cluster difference script is used for creating the fourth cluster;

2. The method of claim 1, wherein the comparing the table of the source database with the table of the target database to obtain a table comparison result, and generating a table difference script corresponding to the table comparison result, comprises:

constructing a first table set based on each table contained in the source database, and constructing a second table set based on each table contained in the target database;

Comparing the first table set with the second table set to obtain a table comparison result; the table comparison result comprises a first table, a second table, a third table and a fourth table; the first table is a table which is contained in the source database and is not contained in the target database; the second table is a table which is not contained in the source database and is contained in the target database; the third table belongs to the source database, the fourth table belongs to the target database, and the table names of the third table are the same as the table names of the fourth table;

generating a first table difference script based on the first table; the first table difference script is used for deleting the first table;

generating a second table difference script based on the second table; the second table difference script is used for creating the second table;

reading information of all indexes of the second table from a metadata table of the target database;

generating a third table difference script corresponding to each index of the second table; the third table difference script is used for creating an index of the second table;

reading partition information of the third table from a metadata table of the source database, and reading partition information of the fourth table from a metadata table of the target database;

Comparing the partition information of the third table with the partition information of the fourth table;

generating a fourth table difference script and a fifth table difference script when the partition information of the third table is different from the partition information of the fourth table; the fourth table difference script is used for deleting the third table; the fifth table difference script is used for creating the fourth table;

comparing the fields of the third table with the fields of the fourth table under the condition that the partition information of the third table is the same as the partition information of the fourth table, obtaining a field comparison result, and generating a table difference script corresponding to the field comparison result;

and comparing the index of the third table with the index of the fourth table to obtain an index comparison result, and generating a table difference script corresponding to the index comparison result.

3. The method of claim 2, wherein generating the second table difference script comprises:

reading the table basic information of the second table from the metadata table of the target database, and judging whether the second table is a partition table according to the table basic information of the second table;

Reading partition basic information of the second table from a metadata table of the target database under the condition that the second table is the partition table;

reading partition field information of the second table from a metadata table of the target database;

reading partition key value information of the second table from a metadata table of the target database;

judging whether the second table is a combined partition according to the partition basic information of the second table;

reading the sub-partition field information of the second table from the metadata table of the target database under the condition that the second table is the combined partition;

reading the sub-partition key value information of the second table from the metadata table of the target database;

reading field information of the second table from a metadata table of the target database in the case that the second table is not the partition table and the second table is not the combined partition;

reading primary key constraint information of the second table from a metadata table of the target database;

reading the primary key composition field information of the second table from the metadata table of the target database;

And generating a second table difference script based on the table basic information, the partition field information, the partition key value information, the sub-partition field information, the sub-partition key value information, the field information, the primary key constraint information and the primary key composition field information of the second table.

4. The method of claim 2, wherein the generating a third table differential script corresponding to each index of the second table comprises:

for each index, reading the component field information of the index from a metadata table of the target database;

judging whether the index is a global partition index or not according to the composition field information of the index;

reading partition basic information of the index from a metadata table of the target database under the condition that the index is the global partition index;

reading the partition field information of the index from a metadata table of the target database;

reading the partition key value information of the index from a metadata table of the target database;

generating a third table difference script corresponding to the index based on the composition field information, the partition basic information, the partition field information and the partition key value information of the index;

And generating a third table difference script corresponding to the index based on the composition field information of the index when the index is not the global partition index.

5. The method of claim 2, wherein comparing the fields of the third table with the fields of the fourth table to obtain a field comparison result, and generating a table difference script corresponding to the field comparison result, comprises:

reading information of all fields of the third table from a metadata table of the source database, and reading information of all fields of the fourth table from a metadata table of the target database;

constructing a first field set based on each field contained in the third table, and constructing a second field set based on each field contained in the fourth table;

comparing the first field set with the second field set to obtain a field comparison result; the field comparison result comprises a first field, a second field, a third field and a fourth field; the first field is a field that the third table contains and the fourth table does not contain; the second field is a field that the third table does not contain and the fourth table contains; the third field belongs to the third table, the fourth field belongs to the fourth table, and the field name of the third field is the same as the field name of the fourth field;

Generating a sixth table difference script based on the first field; the sixth table difference script is used for deleting the first field;

generating a seventh table difference script based on the second field; the seventh table difference script is used for creating the second field;

acquiring information of the third field from the third table and acquiring information of the fourth field from the fourth table;

generating an eighth table difference script when the information of the third field is different from the information of the fourth field; the eighth table difference script is used for changing the information of the third field by using the information of the fourth field;

reading the primary key constraint of the third table from the metadata table of the source database, and reading the primary key constraint of the fourth table from the metadata table of the target database;

comparing the primary key constraint of the third table with the primary key constraint of the fourth table to obtain a primary key constraint comparison result; the main key constraint comparison result comprises a first main key constraint, a second main key constraint, a third main key constraint and a fourth main key constraint; the first primary key constraint is a primary key constraint that the third table contains and the fourth table does not contain; the second primary key constraint is a primary key constraint that the third table does not contain and the fourth table contains; the third main key constraint is attributed to the third table, the fourth main key constraint is attributed to the fourth table, and the constraint name of the third main key constraint is the same as the constraint name of the fourth main key constraint;

Generating a ninth table difference script based on the first primary key constraint; the ninth table difference script is used for deleting the first primary key constraint;

generating a tenth table difference script based on the second primary key constraint; the tenth table difference script is used for creating the second primary key constraint;

acquiring information of a field to which the third primary key constraint belongs from the third table, and acquiring information of a field to which the fourth primary key constraint belongs from the fourth table;

generating an eleventh table difference script and a twelfth table difference script under the condition that the information of the field to which the third main key constraint belongs is different from the information of the field to which the fourth main key constraint belongs; the eleventh table difference script is used for deleting the third primary key constraint; the twelfth table difference script is used for newly building the fourth primary key constraint.

6. The method of claim 2, wherein comparing the index information of the third table with the index information of the fourth table to obtain an index comparison result, and generating a table difference script corresponding to the index comparison result, comprises:

reading information of all indexes of the third table from a metadata table of the source database, and reading information of all indexes of the fourth table from a metadata table of the target database;

Constructing a first index set based on each index contained in the third table, and constructing a second index set based on each index contained in the fourth table;

comparing the first index set with the second index set to obtain an index comparison result; the index comparison result comprises a first index, a second index, a third index and a fourth index; the first index is an index that the third table contains and the fourth table does not contain; the second index is an index that the third table does not contain and the fourth table contains; the third index is attributed to the third table, the fourth index is attributed to the fourth table, and the index name of the third index is the same as the index name of the fourth index;

generating a thirteenth table difference script based on the first index; the thirteenth table difference script is configured to delete the first index;

generating a fourteenth table difference script based on the second index; the fourteenth table difference script is used for newly establishing the second index;

acquiring the component field information of the third index from the third table, and acquiring the component field information of the fourth index from the fourth table;

Generating a fifteenth table difference script and a sixteenth table difference script in case that the composition field information of the third index is not identical to the composition field information of the fourth index; the fifteenth table difference script is used for deleting the third index; the sixteenth table difference script is used to create the fourth index.

7. The method according to claim 1, wherein the comparing the view of the source database with the view of the target database to obtain a view comparison result, and generating a view difference script corresponding to the view comparison result, includes:

constructing a first view set based on each view contained in the source database, and constructing a second view set based on each view contained in the target database;

comparing the first view set with the second view set to obtain a view comparison result; the view comparison result comprises a first view, a second view, a third view and a fourth view; the first view is a view which is contained in the source database and is not contained in the target database; the second view is a view which is not contained in the source database and is contained in the target database; the third view belongs to the source database, the fourth view belongs to the target database, and the view names of the third view and the fourth view are the same;

Generating a first view difference script based on the first view; the first view difference script is used for deleting the first view;

generating a second view difference script based on the second view; the second view difference script is used for newly building the second view;

obtaining view definition information of the third view from the source database and view definition information of the fourth view from the target database;

generating a third view difference script and a fourth view difference script in the case that the view definition information of the third view is different from the view definition information of the fourth view; the third view difference script is used for deleting the third view; the fourth view difference script is used for newly building the fourth view.

8. A device for generating a difference script, wherein the device for generating a difference script is configured to execute the method for generating a difference script according to any one of claims 1 to 7.

9. A computer-readable storage medium, characterized in that the computer-readable storage medium comprises a stored program, wherein the program performs the method of generating a difference script according to any one of claims 1-7.