CN103678532B - Alternation statement reverse analysis method, database alternating and backspacing method and database alternating and backspacing system - Google Patents

Abstract

A change statement reverse analysis method, database change rollback method and system, the reverse analysis method includes: S1, determining the object involved in the change statement, the change instruction and the rollback instruction corresponding to the change instruction; S2, if the change statement is created/ Add operation, determine the rollback statement directly according to the object and the rollback instruction; if the change statement performs a change/delete operation, determine the changed/deleted source data/source content, and determine the changed/deleted source data/source content according to the object, source data/source content and rollback directive determines the fallback statement. The rollback method includes: S10, obtaining the input of the SQL change script, database version and database authorization; S20, performing analysis on the SQL change script, and generating a statement analysis result; S30, based on the statement analysis result, generating a rollback statement according to the reverse analysis method of the change statement And convert the rollback statement into an executable rollback SQL script file. The fallback method has the advantages of wide applicability, strong operability, safety, and high-efficiency automation.

Description

Change statement reverse analysis method, database change rollback method and system

technical field

The invention relates to the field of software system operation and maintenance, in particular to a reverse analysis method for a change statement, a database change rollback method and a system.

Background technique

During the operation of the software production system, with the development and changes of the business, it is often necessary to modify or upgrade the functions of the software system, and the modification and upgrade will bring changes to the software system itself. At the same time, in the process of system operation, system defects left over from the previous period are often exposed, and the repair of defects will also lead to system changes.

In most software production systems, the database system is an important component. Common database systems such as Oracle, MySQL, Sybase, MS SQL server, etc., have been widely used in software systems in various industries. Therefore, changes to the software system also involve changes to the database system in most cases.

System components can usually be divided into files and databases according to the storage form. The file includes a program file and a configuration file. The program file may be a binary file or a script file in a text format. The configuration file is usually a text file in a format such as ini or xml. A file change is generally done by replacing an old version file with a new version file. Database changes usually include data definition changes, data changes, and permission changes. When the database is changed, it is completed by executing the corresponding DDL (Data Definition Language, Data Definition Language), DML (Data Manipulation Language, Data Manipulation Language), DCL (Data Control Language, Data Control Language) statement.

System changes are inevitable, but for some reasons, the changed system may not meet user expectations. Common reasons may be that system functions are not consistent with user requirements, changes introduce new faults, and changes cannot be fully implemented. In order to ensure the correctness and availability of the system, in some cases, it is necessary to roll back the completed or unfinished changes to restore the system to the state before the change.

Similarly, change rollback also includes file rollback and database rollback. The rollback of the files is done by overwriting the new version files with the backed up old version files. As for the rollback of the database, there are usually two methods: manual rollback and full database backup and recovery rollback.

Manual rollback refers to the reverse analysis of each change statement, and then executes the corresponding rollback statement to achieve the purpose of database change rollback. For DDL and DCL statements, most databases do not have a rollback mechanism and can only be rolled back manually; for DML statements, because the data has been submitted after the change is executed, one method is to use the database mechanism such as the Oracle flashback mechanism to implement the rollback , this method has several obvious limitations. First, the applicable conditions are harsh. For example, the Oracle flashback mechanism itself has various constraints, and most other databases do not have similar mechanisms. Second, the operability is not high. Similar operations Usually, special database maintenance personnel are required. Third, similar rollback methods will affect other data changes during the period between the change and the rollback, unless the rollback is done immediately after the change is completed, so DML rollback is usually done manually. .

The database manual rollback method is suitable for the rollback task when the database changes are small, but it has insurmountable defects. First of all, the labor cost is high, because it is necessary to manually check each database change statement, and then write the corresponding reverse statement. In the case of relatively large or frequent changes, it takes a lot of manpower; secondly, the correctness cannot be guaranteed, because through The reverse is done manually, and there is no effective checking mechanism to ensure the correctness of the work. In the case of large changes, it is very easy to make mistakes; again, manual rollback needs to ensure that the table structure, data and permissions involved in the change are backed up before the change, otherwise Unable to roll back; finally, the operation efficiency is low. For large-scale changes, the manual operation efficiency is not high, resulting in a long rollback time, which will affect the availability of the system and cause other indirect losses.

In addition to manual rollback, changes can also be rolled back through full database backup and recovery, that is, the entire database is backed up before the change is implemented. If rollback is required after the change is completed, the database backup before the change is used for recovery.

As for the full database backup and recovery method, the advantage is that the database can be completely restored, but there are also obvious defects. First, the cost is huge. The backup and recovery of the whole database usually requires stopping the database, which is unacceptable for some systems, and if the changes are small, the backup and recovery of the whole database will lead to more resources and time consumption; the second is professionalism Strong, the entire database backup and recovery requires a dedicated database DBA to complete.

Therefore, there are obvious defects in the current rollback of database changes, whether manual rollback or full database rollback. So in the long run, there needs to be a solution that can solve the defects of manual rollback and full database rollback, and complete the automatic rollback of database changes.

Contents of the invention

The technical problem to be solved by the present invention is to provide a solution for the defects of low efficiency, high cost, and unguaranteed correctness of the above-mentioned manual rollback in the prior art, as well as the high cost and narrow scope of application of the whole inventory. A reverse analysis method for a change statement, a database change rollback method and a system with wide applicability, strong operability, safety, high efficiency and automation.

The technical solution adopted by the present invention to solve the technical problem is: to construct a method for reverse analysis of the change statement, which is used in the database to perform reverse analysis before the execution of the change statement to generate a rollback statement. The change statement is an SQL statement, so Said method comprises the following steps:

S1. Determine the object involved in the change statement, the change instruction, and the rollback instruction corresponding to the change instruction;

S2. If the change statement performs a create/add operation, then determine the rollback statement directly according to the object and the rollback instruction;

If the change statement performs a change/deletion operation, determine the changed/deleted source data/source content, and determine a rollback statement according to the object, source data/source content, and rollback instruction.

In the method for reverse analysis of a change statement according to the present invention, the object includes a direct object and an associated object associated with the direct object;

In the step S1, determining the object involved in the change statement includes: directly obtaining the direct object according to the change statement; and indirectly obtaining the associated object by querying the data dictionary according to the change statement; the query data dictionary It is to query the database system through the query statement;

In the step S2, determining the changed/deleted source data/source content includes: directly obtaining the source data/source content according to the change statement; and indirectly obtaining the source data by querying a data table according to the change statement /source content; the query data table is to query the database system and data table through a query statement.

The method for reverse analysis of the change statement of the present invention, wherein the change statement includes a DDL statement, a DML statement and a DCL statement;

If the change statement is a DDL statement, the method includes: the rollback of creating an object, the rollback of updating an object, and the rollback of deleting an object;

If the change statement is a DML statement, the method includes: the rollback of newly added data, the rollback of updated data, and the rollback of deleted data;

If the change statement is a DCL statement, the method includes: rolling back the authorization statement and rolling back the permission recovery statement.

In the method for reverse analysis of the change statement of the present invention, if the change statement is CREATEMM_name; wherein M represents the type of the created object, M_name represents the name of the created object, and the rollback of the created object includes:

S1a1. Determine that the object involved in the change statement is M_name, the change command is CREATE, and the rollback command corresponding to CREATE is DROP;

S2a1. The change statement only performs a creation operation, and directly determines that the rollback statement is: DROP M M_name;

If the change statement is ALTER M M_name UD1, where M represents the type of the object to be updated, M_name represents the name of the object to be updated, and UD1 represents the operation of updating the content, and the change instructions involved in UD1 include: ADD, DROP and MODIFY, the update object The fallbacks include:

S1a2. Determine that the object involved in the change statement is M_name, and the change instructions include: the change instructions involved in CREATE and UD1, the rollback command corresponding to CREATE is DROP, and the rollback commands corresponding to ADD, DROP, and MODIFY are DROP, DROP, and MODIFY, respectively. ADD and MODIFY;

S2a2. The change statement performs a change operation, determines the changed source content corresponding to UD1, and determines that the rollback statement is: ALTER M_name UD2, and UD2 represents the implementation composed of the rollback command corresponding to UD1 and the source content Rollback operation;

If the change statement is DROP M M_name; where M represents the type of the object to be deleted, and M_name represents the name of the object to be deleted, the rollback of the deleted object includes:

S1a3. Determine that the object involved in the change statement is M_name, the change command is DROP, and the fallback command corresponding to DROP is CREATE;

S2a3. Perform a delete operation on the change statement, determine the deleted source content, and determine that the rollback statement is: CREATE M M_name UD3, where UD3 represents a limiting condition formed by the source content;

If the change statement is INSERT INTO tb_a VALUES v_a, where tb_a represents the object of the newly added data, and v_a represents the newly added data, the rollback of the newly added data includes:

S1b1. Determine that the object involved in the change statement is tb_a, the change command is: INSERT, and the corresponding rollback command is DELETE;

S2b1. Perform a change operation on the change statement, and directly determine that the rollback statement is: DELETE FROM M_name WHERE v_b, where v_b represents the field corresponding to the newly added data v_a;

If the change statement is UPDATE tb_a SET c, where tb_a represents the object of the updated data, and c represents the operation of updating the data, the rollback of the updated data includes:

S1b2. Determine that the object involved in the change statement is tb_a, the change command is UPDATE, and the rollback command corresponding to UPDATE is UPDATE;

S2b2. The change statement performs a change operation, determines the source data corresponding to c, and determines that the rollback statement is: UPDATE tb_a SET d WHERE e, wherein, d represents the specific data of the source data, and e represents the field corresponding to the source data ;

If the change statement is DELETE FROM tb_a WHERE c, where tb_a represents the object of the deleted data, and c represents the field corresponding to the deleted data, the rollback of the deleted data includes:

S1b3. Determine that the object involved in the change statement is tb_a, the change command is: DELETE, and the rollback command corresponding to DELETE is INSERT;

S2b3. The change statement performs a delete operation, determines the source data corresponding to c, and determines that the rollback statement is: INSERT INTO tb_a(col_a)VALUES d, wherein, d represents the specific data of the source data, and col_a represents the corresponding source data field;

If the change statement is GRANT A USER TO user_a, the fallback of the authorization statement includes:

S1c1. Determine that the object involved in the change statement is user_a, the change command is: GRANT, and the corresponding rollback command is REVOKE;

S2c1. The change statement performs an authorization operation, and directly determines that the rollback statement is: REVOKE A FROMuser_a;

If the change statement is REVOKE A ON tb_a FROM role_a; the rollback of the permission recovery statement includes:

S1c1. Determine that the object involved in the change statement is role_a, the change command is: REVOKE, and the corresponding rollback command is GRANT;

S2c1. The change statement performs a permission recovery operation, and directly determines that the rollback statement is: GRANT A ONtb_a TO role_a.

The present invention also discloses a database change rollback method based on the reverse analysis method of the change statement, and the method includes the following steps:

S10, obtaining SQL change script input, database version and database authorization;

S20. Perform parsing on the SQL change script to generate a statement parsing result;

S30. Based on the statement analysis result, generate a rollback statement according to the reverse analysis method for the change statement, and convert the rollback statement into an executable rollback SQL script file.

In the database change rollback method described in the present invention, the method further includes:

S40. Input a rollback execution command, and run the rollback SQL script file to roll back the database change.

In the database change rollback method of the present invention, in the step S10, the SQL change script is a text file containing the change statement, and the database authorization includes a database user name and password;

Performing the parsing in the step S20 is calling the SQL engine of the database version according to the SQL change script to perform Flex lexical analysis and Bison grammatical analysis.

The database change rollback method according to the present invention, wherein the rollback statement generated according to the reverse analysis method of the change statement in the step S30 is a logical rollback statement composed of several clauses, and the step S30 combines the The conversion of rollback statements into executable rollback SQL script files specifically includes:

S31. Reorganization: reorganize the clauses;

S32. Filling: filling the reorganized clauses to form a complete and legal SQL statement;

S33. Mapping: mapping the SQL statements with different implementations obtained in step S32 into statements of a specific database system;

S34. Merge: merge the data import command and the statement obtained in step S33 into the fallback SQL script file.

The present invention also provides a database change rollback system based on the database change rollback method, the system comprising:

User interface layer: used for interactive processing with users; said interactive processing includes obtaining SQL change script input, database version, database authorization and rollback execution commands;

Rollback processing layer: used to analyze the SQL change script, generate a statement analysis result, and generate a rollback statement according to the reverse analysis method of the change statement based on the statement analysis result, and convert the rollback statement into Executable fallback SQL script file;

Data service layer: for querying data from the database, backing up source data/source content, and executing the rollback SQL script file when receiving the rollback execution command;

Database layer: Provides a database system.

In the database change rollback system described in the present invention, wherein,

The functional components of the user interface layer include:

Command processing component: for receiving the SQL change script and rolling back the execution command;

Information interaction component: used to display the process and results of the database rollback to the user;

The functional components of the fallback processing layer include:

SQL engine: according to the lexical and grammatical rules of the database, complete the parsing of the SQL change script and generate statement parsing results. The parsing of the SQL change script includes lexical analysis and grammatical analysis. The lexical analysis uses the Flex tool, and the grammatical analysis uses the Bison tool ;

Script parsing component: used to call the corresponding SQL engine according to the database type to complete the parsing of the SQL change script;

Reverse analysis component: used to analyze the statement analysis result, and generate a rollback statement according to the reverse analysis method of the change statement;

Rollback statement generation component: used to convert the rollback statement into a SQL rollback statement related to the database type in combination with the data service layer, and provide the generated rollback SQL script file to the data service layer;

The data service layer includes:

Data query component: used to obtain the SQL rollback statement from the database and generate required data;

Data backup component: used to back up and manage the source data/source content that needs to be backed up during rollback;

Script execution component: used to obtain the rollback SQL script file, and complete the execution of the rollback SQL script file;

The database layer includes: Oracle, MySQL, Sybase, SQL Server systems.

Implementing the reverse analysis method for change statement, database change rollback method and system of the present invention has the following beneficial effects: the reverse analysis method for change statement of the present invention performs reverse analysis before the execution of the change statement to generate a rollback statement, and the rollback statement only Roll back the object involved in the change statement or the source data/source content of the object, with strong pertinence and small workload. The database change rollback method uses the rollback statement generated by this method to convert it into an executable rollback SQL script file. When it is necessary to roll back the database, just execute the rollback SQL script file, achieving wide applicability and reliability. The advantages of strong operability, safety, and high-efficiency automation.

Description of drawings

The present invention will be further described below in conjunction with accompanying drawing and embodiment, in the accompanying drawing:

Fig. 1 is the flow chart of the reverse analysis method of change sentence of the present invention;

FIG. 2A is a flow chart of the first embodiment of the database change rollback method of the present invention;

FIG. 2B is a flow chart of the second embodiment of the database change rollback method of the present invention;

Fig. 3 is the final syntax tree of the INSERT statement syntax analysis in the database change rollback method of the present invention;

FIG. 4 is a hierarchical diagram of the structure of the database change rollback system of the present invention;

Fig. 5 is a system component diagram of the database change rollback system of the present invention.

detailed description

In order to have a clearer understanding of the technical features, purposes and effects of the present invention, the specific implementation manners of the present invention will now be described in detail with reference to the accompanying drawings.

Referring to Fig. 1, it is a flow chart of the reverse analysis method for changing sentences of the present invention;

The reverse analysis method of the change statement of the present invention is used in the database to carry out reverse analysis before the execution of the change statement to generate a rollback statement, the change statement is an SQL statement, and the method includes the following steps:

S1. Determine the objects involved in the change statement, the change instruction and the rollback instruction corresponding to the change instruction;

Wherein, the object includes a direct object and an associated object associated with the direct object, and determining the object involved in the change statement includes: directly obtaining the direct object according to the change statement; and indirectly obtaining the associated object by querying the data dictionary according to the change statement;

S2. If the change statement performs a create/add operation, determine the rollback statement directly according to the object and the rollback instruction; if the change statement performs a change/delete operation, determine the changed/deleted source data/source content, and determine the changed/deleted source data/source content according to the object, The source data/source content and fallback instructions determine the fallback statement;

Determining the changed/deleted source data/source content includes: directly obtaining the source data/source content according to the change statement; and indirectly obtaining the source data/source content by querying the data table according to the change statement. Among them, the source content includes the detailed properties of the object and the data and status related to the object;

Querying the data dictionary refers to querying the database system through query statements, and querying data tables refers to querying the database system and data tables through query statements.

The present invention is mainly aimed at three types of change statements: DDL statement, DML statement and DCL statement:

DDL is a data definition language, which is mainly used to define, update, and delete database objects. Common database objects include tables, views, indexes, constraints, stored procedures, triggers, roles, users, etc.; DML is a data manipulation language, used to operate data in the database. Usually it is the query, insertion, update, and deletion of data; DCL is a data control language, which is used for system role control and authorization, usually authorization and deauthorization of user and role permissions.

If the change statement is a DDL statement, the method includes: rollback of creating an object, rollback of updating an object, and rollback of deleting an object;

If the change statement is a DML statement, the methods include: rollback of new data, rollback of updated data, rollback of deleted data, rollback of MERGE data change statements, rollback of data change statements with complex change conditions, Rollback of table data changes that can contain duplicate rows;

If the change statement is a DCL statement, the method includes: rolling back the authorization statement, and rolling back the permission recovery statement. The reverse analysis method of the above three types of change statements is introduced below with specific examples.

(a1) Fallback for object creation

The change statement creates a database object. Before the change, this object does not exist in the database. The rollback is relatively simple, just delete the created database object:

If the change statement is CREATE M M_name; where M represents the type of the object to be created, and M_name represents the name of the object to be created, the rollback of the object creation includes:

S1a1. Determine that the object involved in the change statement is M_name, the change command is CREATE, and the rollback command corresponding to CREATE is DROP;

S2a1. The change statement only performs the creation operation, and directly determines the rollback statement as: DROP M M_name;

E.g:

a1-1) Change statement: CREATE TABLE tb_a;

Analysis: The object involved is the direct object tb_a, the change command is CREATE, and the rollback command is DROP.

Corresponding rollback statement: DROP TABLE tb_a;

a1-2) Change statement: CREATE INDEX idx_a;

Analysis: The object involved is the direct object idx_a, the change command is CREATE, and the rollback command is DROP.

Corresponding rollback statement: DROP INDEX idx_a;

(a2) Fallback for updating objects

The rollback of the updated object changes the updated object, so it is necessary to back up the changed source content: for example, the detailed attributes of the previous database object. Because the object update may involve the change of the associated object related to the object, such as the change of the data constraint condition, because of the data constraint, it may cause the rollback to be unsuccessful. In this case, the source content also includes the original content related to the object The data. When backing up the data of the source content, if certain rows or columns are involved, only specific rows and columns can be backed up; otherwise, a full table backup may be required.

If the change statement is ALTER M M_name UD1, where M represents the type of the object to be updated, M_name represents the name of the object to be updated, and UD1 represents the operation of updating the content. The change commands involved in UD1 include: ADD, DROP, and MODIFY. Refund includes:

S1a2. Determine that the object involved in the change statement is M_name, and the change instructions include: the change instructions involved in CREATE and UD1, the rollback command corresponding to CREATE is DROP, and the rollback commands corresponding to ADD, DROP, and MODIFY are DROP, ADD, and MODIFY;

S2a2. Change the statement to perform the change operation, determine the changed source content corresponding to UD1, and determine the rollback statement as: ALTER M_name UD2, UD2 represents the rollback operation composed of the rollback instruction corresponding to UD1 and the source content ;

E.g:

a2-1) Change statement: ALTER TABLE tb_a ADD col_a INTEGER NOT NULL;

Analysis: The object involved is the direct object tb_a, the change command is ALTER and ADD, and the rollback command is ALTER and DROP, and col_a did not exist before, so this column needs to be deleted when rolling back.

Corresponding rollback statement: ALTER TABLE tb_a DROP COLUMN col_a;

a2-2) Change statement: ALTER TABLE tb_a MODIFY col_a VARCHAR2(32) DEFAULT ‘TEST’ NOT NULL;

Analysis: The object involved is tb_a, the change command is ALTER and MODIFY, then the rollback command is ALTER and MODIFY, and the changed source content is the attributes of col_a column: data type, default value and whether the field is not empty. Change the type of column col_a in the data table tb_a to VARCHAR232, the default value is the string 'TEST', and the field is not empty. In the change statement, the data type of the column col_a before the change is not known, so the data of the column col_a before the change needs to be obtained first Type, query the data table through the following query statement:

SELECT data_type, data_length, nullable, nvl2(data_default, 'N', 'Y') default_exist FROM user_tab_columns WHERE table_name=UPPER('tb_a')and column_name=upper('col_a');

Assuming that the result obtained by the above statement is: VARCHAR2, 16, 'N', 'Y', it means that the original data type of column col_a is VARCHAR2, the length is 16, it is a non-null field, and there is a default value, then you need to obtain Its original default value can be obtained through the following statement:

Assuming that the output of the above statement is: 'ABC', it means that the original default value of this field is the string 'ABC'.

It should also be noted here that because this change statement modifies the field length, the column value inserted after the change may use the new length, and during the database rollback process, it is usually not possible to roll back from a longer length to a shorter one. length, so the source content here also includes the original field value. Assume that the SQL file is backed up to import_data_tb_a.sql here.

The corresponding fallback statement:

UPDATE tb_a SET col_a='';

ALTER TABLE tb_a MODIFY col_a VARCHAR2(16) DEFAULT 'ABC' NOT NULL;

Then execute import_data_tb_a.sql to import data.

(a3) Fallback for deleting objects

For objects that do not involve data and state, such as indexes, views, users, roles, stored procedures, triggers, functions, etc., the source content is the detailed properties of these database objects. For database objects related to data or state, such as tables, fields, sequences, etc., the source content includes not only object attributes, but also object-related data or object states. If a change statement deletes a data table, then when rolling back, not only the original table structure but also the original table data need to be obtained; if a change statement deletes a sequence, then when rolling back, not only the original table structure must be obtained Attributes such as initial, step size, maximum value, etc., also obtain the state of the sequence, that is, the current value.

Deleting a table may affect the associated objects associated with the table. For example, deleting a table object will delete the indexes, primary keys, constraints, and triggers on the table at the same time, and the stored procedures related to the data table will become invalid. Therefore, the source content also includes: the attributes of the associated object, so that it can be reconstructed and revalidated when it is rolled back.

If the change statement is DROP M M_name; where M represents the type of the object to be deleted, and M_name represents the name of the object to be deleted, the fallback of the deleted object includes:

S1a3. Determine that the object involved in the change statement is M_name, the change command is DROP, and the fallback command corresponding to DROP is CREATE;

S2a3. Change the statement to perform the delete operation, determine the source content to be deleted, and determine the rollback statement as: CREATE MM_name UD3, where UD3 represents the limiting condition formed by the source content;

E.g:

a3-1) Change statement: DROP INDEX idx_a;

Analysis: The object involved is the direct object idx_a, the change command is DROP, the rollback command is CREATE, the change statement deletes the index idx_a, and the feature of the index needs to be obtained first when rolling back, so the source content includes: table name, user name, Whether it is a unique index can be obtained through the following query statement:

SELECT index_name, uniqueness, table_name, table_owner FROM user_indexes WHERE index_name='idx_a';

Assuming that the query result of the above statement is: 'idx_a', 'UNIQUE', 'tb_a', 'user_a', it means that the index idx_a is a unique index, built on the table tb_a, and belongs to the user user_a.

The source content also includes the columns and order of its index, which can be obtained through the following query statement:

SELECT column_name,descend FROM user_ind_columns WHERE index_name ='idx_a'order by column_position;

Suppose the query result of the above statement is:

col_a,ASC

col_b, DESC

Indicates that the index idx_a contains two columns, the first column is col_a, in ascending order, and the second column is col_b, in descending order.

The corresponding fallback statement:

CREATE UNIQUE INDEX idx_a ON tb_a(col_a ASC, col_b DESC);

a3-2) Change statement: DROP TABLE tb_a;

Analysis: The objects involved are the direct object tb_a and the associated objects associated with the table: primary key, index, foreign key, constraint, trigger, etc., the change command is DROP, the rollback command is CREATE, and the source content includes all rows of the table And the data in columns and tables and the detailed properties of all objects, so if you want to roll back, you need to rebuild all objects and re-import the data.

First get all the columns and their attributes of the table, in order to query data and rebuild the table, you can use the following statement:

SELECT t.column_name, t.data_type, t.data_length, t.data_precision, t.data_scale, t.nullable, t.data_default, c.comments

FROM user_tab_columns t, user_col_comments c WHERE t.table_name=c.table_name AND t.column_name=c.column_name AND t.table_name='TB_A';

Export data according to the column name. For the tb_a table, execute the following statement to back up the data to the import_data_tb_a.sql file:

SELECT col_a, col_b, col_c FROM tb_a;

Query the primary key and constituent columns of the table in order to rebuild the primary key through the following statement:

SELECT t.constraint_name,c.column_name,c.position FROM user_constraints t,user_cons_columns c WHERE t.constraint_name=c.constraint_name AND t.constraint_type='P'AND t.table_name='TB_A';

Query the index and constituent columns of the table in order to rebuild the index, through the following statement:

SELECT i.index_name,c.column_name,c.column_position,c.descend FROMuser_indexes i,user_ind_columns c WHERE i.index_name=c.index_name ANDi.table_name='TB_A';

Query the unique constraints, foreign keys and constituent columns of the table, so as to rebuild the constraints and foreign keys, through the following statement:

SELECT t.constraint_name,c.column_name,c.position FROM user_constraints t,user_cons_columns c WHERE t.constraint_name=c.constraint_nameAND t.constraint_type IN('U','R')AND t.table_name='TB_A';

Query the trigger and code of the table, so as to rebuild the trigger, pass the following statement:

SELECT t.trigger_name,t.trigger_type,s.line,s.text FROM user_triggerst,user_sources WHERE t.trigger_name=s.name AND t.table_name='TB_A';

Corresponding rollback statement: Based on the above analysis results, rebuild the table creation statement, create the primary key, index, foreign key, constraint, and trigger statement. After the creation is successful, import the backup data. The specific creation statement is a well-known technology in the art. I won't repeat them here.

(b1) Rollback of new data

If the change statement is INSERT INTO tb_a VALUES v_a, where tb_a represents the object of the new data, and v_a represents the new data, the rollback of the new data includes:

S1b1. Determine that the object involved in the change statement is tb_a, the change command is: INSERT, and the corresponding rollback command is DELETE;

S2b1. Change the statement to perform the change operation, and directly determine the rollback statement as: DELETE FROM M_name WHEREv_b, where v_b represents the field corresponding to the newly added data v_a;

E.g:

b1-1) Change statement: INSERT INTO tb_a(col_a,col_b)VALUES(1,'v_a');

Analysis: The object involved is the direct object tb_a, the change command is INSERT, the rollback command is DELETE, and the field corresponding to the new data (1,'v_a') is: col_a=1AND col_b='v-a'.

Corresponding fallback statement: DELETE FROM tb_a WHERE col_a=1AND col_b=‘v-a’;

b1-2) Change statement: INSERT INTO tb_a VALUES(1,'v_a',v_b');

Analysis: The object involved is the direct object tb_a, the change command is INSERT, and the rollback command is DELETE. In order to determine the fields corresponding to the newly added data, it is necessary to obtain all the column names of the table, which can be obtained through the following query statement:

SELECT column_name FROM user_tab_columns WHERE table_name='tb_a' ORDER BY column_id;

Suppose the query result of the above statement is:

col_a

col_b

col_c

It indicates that the columns of the data table tb_a are col_a, col_b, and col_c in order.

Corresponding rollback statement: DELETE FROM tb_a WHERE col_a=1AND col_b='v_a'AND col_c='v_b';

(b2) Rollback of updated data

If the change statement is UPDATE tb_a SET c, where tb_a represents the object of the updated data, and c represents the operation of updating the data, the rollback of the updated data includes:

S1b2. Determine that the object involved in the change statement is tb_a, the change command is UPDATE, and the rollback command corresponding to UPDATE is UPDATE;

S2b2. Change the statement to perform a change operation, determine the source data corresponding to c, and determine the rollback statement as: UPDATEtb_a SET d WHERE e, where d represents the specific data of the source data, and e represents the field corresponding to the source data;

The UPDATE statement updates the data in the data table. To perform a rollback operation, it is first necessary to analyze whether the rollback statement can be deduced from the change statement. If not, the data before the update must be backed up. The following two examples respectively illustrate the analysis mechanism of non-derivable and derivable fallback statements.

E.g:

b2-1) Change statement: UPDATE tb_a SET col_c=10;

Analysis: The object involved is tb_a, the change command is UPDATE, then the rollback command is UPDATE, the change statement updates the field col_c value of all rows in table tb_a to 10, so the source data is the data in col_c. For a data table with a primary key, you only need to back up the primary key field and the value of the changed field; for a data table without a primary key, you can back up system row identifiers such as Oracle ROWID and changed fields, or perform a full table backup. Querying whether a table has a primary key can be done through the following statement:

SELECT constraint_name FROM user_constraints WHERE table_name='tb_a'AND constraint_type='P';

Assuming that the query result of the above statement is: SYS_C0000001, it means that there is a primary key named SYS_C0000001 in the data table tb_a, continue to query the primary key field, and use the following statement:

SELECT column_name FROM user_cons_columns WHERE constraint_name= 'SYS_C0000001';

Suppose the query result of the above statement is:

col_a

col_b

It indicates that the primary key fields of the data table tb_a are col_a, col_b, then we only need to back up the values of col_a, col_b, and col_c to back up the data, and query the data table tb_a through the following statement:

SELECT col_a, col_b, col_c FROM tb_a;

Suppose the query result is:

1,10,100

2,20,200

3,30,300

The corresponding fallback statement:

UPDATE tb_a SET col_c=100WHERE col_a=1AND col_b=10;

UPDATE tb_a SET col_c=200WHERE col_a=2AND col_b=20;

UPDATE tb_a SET col_c=300WHERE col_a=3AND col_b=30;

b2-2) Change statement: UPDATE tb_a SET col_b=col_b+100WHERE col_b=10;

Analysis: The object involved is tb_a, the change command is UPDATE, then the rollback command is UPDATE, the change statement modifies the field col_b of the data table tb_a to increase the original value by 100, and the update condition is that the field col_b is equal to 10, so the specific source data Data can be derived directly without backup data.

Corresponding rollback statement: UPDATE tb_a SET col_b=col_b-100WHERE col_b=10;

(b3) Rollback of deleted data

If the change statement is DELETE FROM tb_a WHERE c, where tb_a represents the object of the deleted data, and c represents the field corresponding to the deleted data, the rollback of the deleted data includes:

S1b3. Determine that the object involved in the change statement is tb_a, the change command is: DELETE, and the rollback command corresponding to DELETE is INSERT;

S2b3. Change the statement to perform a delete operation, determine the source data corresponding to c, and determine the rollback statement as: INSERTINTO tb_a(col_a)VALUES d, where d represents the specific data of the source data, and col_a represents the field corresponding to the source data;

E.g:

b3-1) Change statement:

DELETE FROM tb_a WHERE col_a=1;

Analysis: The object involved is tb_a, the change command is DELETE, and the rollback command is INSERT. The change statement deletes all the rows in the data table tb_a with the col_a field equal to 1. The source data includes all the data in the rows with col_a equal to 1. First pass The following statement queries the columns and order of table tb_a:

SELECT column_name FROM user_tab_columns WHERE table_name='tb_a' ORDER BY column_id;

Suppose the query result of the above statement is:

col_a

col_b

col_c

It indicates that the fields of the data table tb_a are col_a, col_b, and col_c in order. Then query the data of the row to be deleted before the change, and obtain it through the following statement:

SELECT col_a,col_b,col_c FROM tb_a WHERE col_a=1;

Suppose the query result of the above statement is:

1,10,'A'

1,10,'B'

1,20,'C'

According to the queried column names and data, the final change rollback statement can be obtained.

The corresponding fallback statement:

INSERT INTO tb_a(col_a,col_b,col_c)VALUES(1,10,'A');

INSERT INTO tb_a(col_a,col_b,col_c)VALUES(1,10,'B');

INSERT INTO tb_a(col_a,col_b,col_c)VALUES(1,20,'C');

(b4) Rollback of MERGE data change statement

The MERGE statement processes data from the source table to the target table according to the conditions. The operation may be INSERT or UPDATE. Therefore, when backing up the source data, it is also necessary to judge how to back up and which data to back up according to the conditions.

E.g:

b4-1) Change statement:

Analysis: The change statement merges the data of the source table tb_b into the target table tb_a. If the value of the field col_a of the tb_b table is equal to the value of the field col_a of tb_a, the records of the tb_b table are used to update the records of the tb_a table, otherwise, the records of the source table tb_b are updated. Records are inserted into the target table tb_a.

When performing a change rollback, it is necessary to first determine whether there is a record in the target table tb_a with the same value as the col_a field in the source table tb_b. If it exists, it indicates that this part of the data row is updated by the source table data, so it is necessary to back up this part of the data row for To restore the update, you can use the following statement to query the updated data row:

SELECT a.col_a,a.col_b,a.col_c FROM tb_a a,tb_b b WHERE a.col_a=b.col_a;

Assume that the query result of the above statement is:

1,2,3

1,2,4,

1,3,4

Indicates that these data rows are updated. In order to roll back the source data, it is also necessary to know the updated value of the source data. The updated value of the updated row can be obtained through the following statement:

SELECT col_a,col_b,col_c FROM tb_b b WHERE EXISTS(SELECT1 FROM tb_a aWHERE a.col_a=b.col_a);

Suppose the query result is:

1, 5, 9

Indicates that the updated rows are all updated to 1, 5, and 9. According to the data before and after the change, the final rollback statement can be obtained.

In addition to updated rows, the source data also includes new rows in the target table. When rolling back, these new rows should be deleted. New rows can be queried by the following statement:

SELECT col_a,col_b,col_c FROM tb_b b WHERE NOT EXISTS(SELECT 1 FROM tb_a a WHERE a.col_a=b.col_a);

Assume that the query result of the above statement is:

3,3,6

Fallback statement:

DELETE FROM tb_a WHERE col_a=1AND col_b=5AND col_c=9;

INSERT INTO tb_a(col_a,col_b,col_c)VALUES(1,2,3);

INSERT INTO tb_a(col_a,col_b,col_c)VALUES(1,2,4);

INSERT INTO tb_a(col_a,col_b,col_c)VALUES(1,3,4);

DELETE FROM tb_a WHERE col_a=3AND col_b=3AND col_c=6;

(b5) Rollback of data change statements with complex change conditions

Among all SQL statements, DML statements can be expressed in the most complex form. DML statements can contain multiple operations such as table joins, subqueries, and set operations, which brings high complexity to the implementation of DML rollback. However, no matter how complex the DML statement is, the final operations that need rollback are still no more than INSERT, The complexity of UPDATE and DELETE is mainly reflected in the complex constraints. Therefore, as long as the DML statement structure is accurately analyzed and constraints are added when backing up the source data, the rollback statement can be accurately obtained. The following example illustrates the reverse analysis mechanism of complex DML statements.

E.g:

b5-1) Change statement:

Analysis: This statement is relatively complicated, including subqueries and set operations, but it can be seen from the body of the statement that its core operation is still the INSERT statement on the first line. From the SELECT statement on the second line to the end, all the data to be INSERT query statement, so when backing up source data, only the data processed by SELECT needs to be backed up. We execute the following SELECT statement to obtain the source data to be backed up:

Suppose the query result of the above statement is:

1,5,9

2,6,3

2,6,4

The corresponding fallback statement:

DELETE FROM tb_a WHERE col_a=1AND col_b=5AND col_c=9;

DELETE FROM tb_a WHERE col_a=2AND col_b=6AND col_c=3;

DELETE FROM tb_a WHERE col_a=2AND col_b=6AND col_c=4;

(b6) Rollback of table data changes that can contain duplicate rows

Tables that can contain duplicate rows usually have no primary key or uniqueness constraints. In such a data table, there may be multiple record rows with completely consistent data. The rollback of such a table needs to consider the possible existence of duplicate rows, which will Affects fallback for INSERT and UPDATE statements.

As for the INSERT statement, if a row of records is inserted, and there is already a row of the same record in the table, then simply execute DELETE to roll back, and the original row of records will also be deleted. Therefore, in this case, we need to first determine whether the table has a primary key or a unique constraint, which can be queried by the following statement:

SELECT*FROM user_constraints WHERE constraint_type IN('P','U')ANDtable_name='tb_a';

If the above query does not return results, it indicates that there may be duplicate records in the table. At this time, the backup of the source data is a full table backup, not just the INSERT record rows. When performing a rollback, first delete the entire table and then back up the data The full table insert.

Regarding the UPDATE statement, for a table with repeatable rows, if the UPDATE statement updates multiple records in the table to exactly the same records, then the data may not be recoverable when the UPDATE is rolled back. Some databases have a fixed row identifier such as Oracle ROWID, and the rollback row can be determined through the backup system row identifier. If there is no row identifier field, the backup of the source data requires a full table backup.

(c1) Fallback of authorization statement

If the change statement is GRANT A USER TO user_a, the fallback of the authorization statement includes:

S1c1. Determine that the object involved in the change statement is user_a, the change command is: GRANT, and the corresponding rollback command is REVOKE;

S2c1. Change statements to perform authorization operations. Permission-related authorization operations or permission recovery operations are equivalent to creating/adding operations. Authorization operations are equivalent to adding or creating a permission, and permission recovery operations are equivalent to adding or creating a "negative" permission. , therefore, in both cases, there is no need to determine the source data or source content, and the rollback statement is directly determined as: REVOKE A FROM user_a;

E.g:

c1-1) Change statement: GRANT CREATE SESSION, CREATE USER TO user_a;

Corresponding rollback statement: REVOKE CREATE SESSION, CREATE USER FROM user_a;

(c2) Rollback of permission recovery statement

If the change statement is REVOKE A ON tb_a FROM role_a; the rollback of the permission recovery statement includes:

S1c1. Determine that the object involved in the change statement is role_a, the change command is: REVOKE, and the corresponding rollback command is GRANT;

S2c1. Change the statement to perform the authority recovery operation, and directly determine the rollback statement as: GRANT A ON tb_a TOrole_a.

E.g:

C2-1) Change statement: REVOKE SELECT, INSERT, UPDATE, DELETE ON tb_a FROM role_a;

Corresponding rollback statement: GRANT SELECT, INSERT, UPDATE, DELETE ON tb_a TO role_a;

To sum up, it can be seen that if the rollback analysis is performed manually, the complexity and workload will be very large. The present invention has established a set of automatic, intelligent, and accurate analysis methods and systems for the complex rollback analysis process. Using the above reverse analysis method, after obtaining the input of the change script, the entire analysis process can generate the final rollback script without manual intervention. When performing a rollback, the database change rollback can be automatically completed simply by calling the rollback script.

Referring to FIG. 2A is a flow chart of the first embodiment of the database change rollback method of the present invention; FIG. 2B is a flow chart of the second embodiment of the database change rollback method of the present invention;

The database change rollback method of the present invention comprises:

S10, obtaining SQL change script input, database version and database authorization;

The database authorization of the user who executes the SQL change script is less than the database authority, the SQL change script is a text file containing the change statement, and the database authorization includes the database user name and password;

S20, execute parsing to SQL change script, generate statement parsing result; For the SQL engine that calls described database version according to described SQL change script, perform Flex lexical analysis and Bison syntax analysis.

The traditional language parsing method is to first divide the strings in the text into tokens according to the rules. Tokens mainly have two attributes, the token name Token Name, and the token value Token Value. The continuous processing of the entire file will be continuous Token stream is output, and the whole process is called lexical analysis. In a nutshell, lexical analysis is the process of defining Token types and division rules, and performing analysis to obtain Token streams.

Grammatical analysis is the process of obtaining the input Token stream according to the defined grammatical rules, constructing a grammatical tree, and finally identifying sentences. The scheme of the present invention supports most common statements of Oracle, MySQL, Sybase and SQL Server, and can support new database types, extended lexical and grammatical rules, and support new statement types by extending the SQL engine.

The following uses common INSERT statements in SQL statements as an example to introduce the working method and process of the SQL engine.

(a) Lexical analysis

Suppose you want to insert a row of data (1,2,'test') into the data table tb_a, there are the following two ways of writing, and both are legal INSERT statements.

INSERT INTO tb_a VALUES(1,2,'test');

INSERT INTO tb_a(col_a,col_b)VALUES(1,'test');

In the above sentences, the following rules can be found:

1. The INSERT statement must start with the INSERT INTO keyword;

2. Next, you must keep up with the table name of the data to be inserted. Here, it is assumed that the table name can only be a combination of letters, numbers, and underscores, and must start with a letter;

3. The column name may or may not be included after the table name. Do not include the column name means all columns, if you include the column name, you must start with an opening bracket and end with a closing bracket, and the column names are separated by commas. It is also assumed here that the column name can only be a combination of letters, numbers, and underscores, and must start with a letter;

4, followed by the VALUES clause;

5. After the VALUES clause, it is the specific value to be inserted. It starts with a left parenthesis and ends with a right parenthesis. Multiple values are separated by commas. If the value is a string, it starts with a quotation mark and ends with a quotation mark.

According to the above analysis results, the Token types of INSERT statements as shown in Table 1 can be summarized.

Table 1

Flex uses regular expression-based lexical matching rules, so for the above INSERT statement, the defined Flex lexical file is as follows:

[Ii][Nn][Ss][Ee][Rr][Tt] {return INSERT;}

[Ii][Nn][Tt][Oo] {return INTO;}

[Vv][Aa][Ll][Uu][Ee][Ss] {return VALUES;}

"("|")"|","|";" {return yytext[0];}

[A-Za-z][A-Za-z0-9_]* {return NAME;}

[0-9]+{return NUMBER;}

"'"[^"'"\n]*"'" {return STRING;}

[\t]

.{yyerror("Match failed");}

According to the above lexical file, the lexical analysis results of the above first INSERT statement are shown in Table 2.

Table 2

(b) Syntactic analysis

Different languages have different syntax, and so does the SQL language. As far as the SQL language is concerned, although there is an international SQL standard, different database vendors often enhance and expand their own database products, so there are some differences in syntax for different database types. Before grammatical analysis, the grammatical rules of the language must be determined first, and then the input sentences can be analyzed according to the rules.

In Bison, the definition of grammatical rules is mainly done through the Token output by lexical analysis. Token is the basic element of grammatical rules. In the scope of grammatical analysis, Token is called a terminator, and the final semantic statement is established through the terminator. Also take the above INSERT statement as an example,

Explain the method and process of grammar analysis.

First, define the following INSERT syntax rules according to the SQL statement format:

According to the above grammatical rules, the grammatical parsing process of the statement "INSERT INTO tb_a(col_a,col_b)VALUES(1,'test');" is shown in Figure 3. The leaf nodes in the box are terminal symbols, and the terminal symbols are the output of lexical analysis. The oval nodes on the tree are all non-terminal symbols, and the tree structure shows the grammatical rules of the statement. Not only the INSERT statement, but any other SQL statement can complete semantic analysis by defining grammatical rules.

S30. Based on the statement analysis result, a rollback statement is generated according to the reverse analysis method of the change statement, and the rollback statement is converted into an executable rollback SQL script file.

SQL statements are usually executed sequentially and have statement-level dependencies. Therefore, when performing reverse analysis on a change statement, it is necessary to start from the last statement and perform reverse analysis until the first statement.

Assume that the current database environment is Oracle, the data table tb_a has no primary key and unique index, and there are duplicate rows with inserted data in the table. The reverse analysis process of the above INSERT statement is shown in Table 3.

table 3

The two columns of Token name and value in the above table identify the type and value of the input Token, and the fallback logic output column shows the results of the reverse analysis. Not only INSERT statements, other SQL statements are also suitable for similar analysis methods.

The above steps S10-S30 only generate the rollback SQL script file, but do not perform backup. When backup is required, the following steps are further included:

S40. Input a rollback execution command, and run the rollback SQL script file to roll back the database change.

Regarding the rollback statement generated according to the reverse analysis method of the change statement in step S30 is a logical rollback statement composed of several clauses, the step S30 converts the rollback statement into an executable rollback SQL script file specifically includes:

S31. Reorganization: reorganize the clauses;

A rollback statement usually has a different structure than an alter statement, so the clauses need to be reorganized.

S32. Filling: filling the reorganized clauses to form a complete and legal SQL statement;

The grammatical format of different statements is not the same, and some grammatical units cannot be obtained through reverse analysis, and need to be automatically supplemented by the system according to the syntax and semantics of the statement, such as the FROM of the DELETE statement, the WHERE of the conditional clause, and the AND or OR logical relationship, etc. The filled statement is a complete and legal fallback SQL statement.

S33. Mapping: mapping the SQL statements with different implementations obtained in step S32 into statements of a specific database system;

Different database systems have different grammars, and the same logic statement may have different implementations in different database systems. The system needs to map statements with different implementations to statements of a specific database system. For example, the statement delimiter is semicolon in Oracle and MySQL databases, and go in Sybase and SQLServer databases;

S34. Merge: merge the data import command and the statement obtained in step S33 into a fallback SQL script file.

When the above four steps are entity rollback, the rollback SQL script file generation process may be optional. In the actual processing process, specific processing will be performed according to the characteristics of the statement. The example in Table 4 shows the process of generating the INSERT statement rollback script file.

Table 4

The present invention also discloses a database change rollback system based on the above-mentioned database change rollback method. Referring to FIG. 4 is a hierarchical diagram of the database change rollback system of the present invention. The system includes:

User interface layer 100: used for interactive processing with users; interactive processing includes obtaining SQL change script input, database version, database authorization, and rollback execution commands;

Rollback processing layer 200: used to analyze the SQL change script, generate statement analysis results, and generate rollback statements based on the statement analysis results according to the reverse analysis method of the change statement, and convert the rollback statements into executable rollback SQL scripts document;

Data service layer 300: for querying data from the database, backing up source data/source content, and executing a rollback SQL script file when a rollback execution command is received;

Database layer 400: providing a database system.

Referring to FIG. 5 is a system component diagram of the database change rollback system of the present invention.

The functional components of the user interface layer 100 include:

Command processing component 101: used to receive SQL change scripts and rollback execution commands;

Information interaction component 102: used to display the process and results of database rollback to the user;

The functional components of the fallback processing layer 200 include:

SQL engine 201: according to the lexical and grammatical rules of the database, complete the parsing of the SQL change script and generate a statement parsing result, the parsing of the SQL change script includes lexical analysis and grammatical analysis, wherein the lexical analysis adopts the Flex tool, and the grammatical analysis adopts the Bison tool;

Script parsing component 202: used for invoking the corresponding SQL engine 201 according to the database type to complete the parsing of the SQL change script, and responsible for error handling during the parsing process;

Reverse analysis component 203: used for analyzing the statement analysis result, and generating a rollback statement according to the reverse analysis method of the change statement;

Rollback statement generating component 204: used to combine the data service layer 300, convert the rollback statement into an SQL rollback statement relevant to the database type, and provide the generated rollback SQL script file to the data service layer 300;

The data service layer 300 includes:

Data query component 301: used to obtain SQL rollback statements from the database and generate required data; data backup component 302: used to back up and manage source data/source content that needs to be backed up for rollback;

Script execution component 303: used to obtain the rollback SQL script file, and complete the execution of the rollback SQL script file;

The database layer 400 includes: Oracle, MySQL, Sybase, SQL Server systems.

To sum up, the reverse analysis method of the change statement of the present invention performs reverse analysis before the execution of the change statement to generate a rollback statement, and the rollback statement only rolls back the object involved in the change statement or the source data/source content of the object, Strong pertinence and low workload. The database change rollback method uses the rollback statement generated by this method to convert it into an executable rollback SQL script file. When it is necessary to roll back the database, just execute the rollback SQL script file, achieving wide applicability and reliability. The advantages of strong operability, safety, and high-efficiency automation.

Embodiments of the present invention have been described above in conjunction with the accompanying drawings, but the present invention is not limited to the above-mentioned specific implementations, and the above-mentioned specific implementations are only illustrative, rather than restrictive, and those of ordinary skill in the art will Under the enlightenment of the present invention, many forms can also be made without departing from the gist of the present invention and the protection scope of the claims, and these all belong to the protection of the present invention.

Claims (7)

1. A method for reversely analyzing a change statement is used for reversely analyzing the change statement in a database to generate a backspace statement before the change statement is executed, wherein the change statement is an SQL statement, and the method comprises the following steps:

s1, determining an object related to the change statement, a change instruction and a backspace instruction corresponding to the change instruction;

s2, if the change statement is created/added, determining a rollback statement directly according to the object and the rollback instruction;

if the change statement carries out change/deletion operation, determining changed/deleted source data/source content, and determining a rollback statement according to the object, the source data/source content and the rollback instruction;

wherein the object comprises a direct object and an associated object associated with the direct object;

in step S1, the determining the object related to the change statement includes: directly acquiring the direct object according to the change statement; and obtaining the associated object indirectly by querying a data dictionary according to the change statement; the query data dictionary is used for querying a database system through query sentences;

in step S2, the determining that the modified/deleted source data/source content includes: directly acquiring the source data/source content according to the change statement; and obtaining the source data/source content indirectly by querying a data table according to the change statement; the query data table is used for querying the database system and the data table through query sentences;

the change statement comprises a DDL statement, a DML statement and a DCL statement;

if the change statement is a DDL statement, the method comprises the following steps: creating a rollback of an object, updating a rollback of an object, and deleting a rollback of an object;

if the change statement is a DML statement, the method comprises the following steps: backspacing of newly added data, backspacing of updated data and backspacing of deleted data;

if the change statement is a DCL statement, the method comprises the following steps: the rollback of an authorization statement and the rollback of an authority recovery statement;

wherein,

if the change statement is CREATE M _ name; wherein M represents the type of the created object, M _ name represents the name of the created object, and the rollback of the created object comprises:

s1a1, determining that an object related to the change statement is M _ name, a change instruction is CREATE, and a rollback instruction corresponding to CREATE is DROP;

s2a1, the change statement only carries out creation operation, and the rollback statement is directly determined to be: DROP M _ name;

if the change statement is ALTER M _ name UD1, where M represents the type of the update object, M _ name represents the name of the update object, and UD1 represents the operation of updating the content, the change instruction involved in UD1 includes: ADD, DROP, and MODIFY, the rollback to update an object comprising:

s1a2, determining that an object related to the change statement is an M _ name, wherein the change instruction comprises: the CREATE and UD1 relate to change instructions, wherein the rollback instruction corresponding to CREATE is DROP, and the rollback instructions corresponding to ADD, DROP and MODIFY are DROP, ADD and MODIFY respectively;

s2a2, the change statement carries out change operation, changed source content corresponding to UD1 is determined, and the rollback statement is determined as follows: ALTER M _ name UD2, UD2 represents operations to implement fallback, consisting of a fallback instruction and source content corresponding to UD 1;

if the change statement is DROP M _ name; wherein M represents the type of the deleted object, M _ name represents the name of the deleted object, and the rollback of the deleted object comprises:

s1a3, determining that an object related to the change statement is M _ name, a change instruction is DROP, and a rollback instruction corresponding to DROP is CREATE;

s2a3, the change statement carries out deletion operation, the deleted source content is determined, and the rollback statement is determined to be: CREATE M _ name UD3, UD3 represents a constraint composed of source content;

if the change statement is INSERT INTO tb _ a VALUES v _ a, wherein tb _ a represents an object of newly added data, v _ a represents newly added data, and the rollback of the newly added data comprises the following steps:

s1b1, determining the object related to the change statement as tb _ a, and the change instruction is as follows: INSERT, wherein the corresponding backspacing instruction is DELETE;

s2b1, the change statement carries out change operation, and the rollback statement is directly determined to be: DELETE FROM M _ nameWHERE v _ b, wherein v _ b represents a corresponding field corresponding to the newly added data v _ a;

if the change statement is UPDATE tb _ a SET c, where tb _ a represents the object of the UPDATE data and c represents the operation of updating the data, the rollback of the UPDATE data comprises:

s1b2, determining that an object related to the change statement is tb _ a, the change instruction is UPDATE, and a rollback instruction corresponding to UPDATE is UPDATE;

s2b2, the change statement carries out change operation, the source data corresponding to c is determined, and the rollback statement is determined to be: UPDATE tb _ a SET d WHERE d represents specific data of the source data and e represents a corresponding field of the source data;

if the change statement is DELETE FROM tb _ a WHERE c, WHERE tb _ a represents the object of the deleted data and c represents the corresponding field of the deleted data, the rollback of the deleted data includes:

s1b3, determining the object related to the change statement as tb _ a, and the change instruction is as follows: DELETE, wherein a backspacing instruction corresponding to DELETE is INSERT;

s2b3, deleting the change statement, determining the source data corresponding to c, and determining the rollback statement as: INSERT INTO tb _ a (col _ a) VALUES d, wherein d represents specific data of the source data, and col _ a represents a field corresponding to the source data;

if the change statement is GRANT A USER TO USER _ a, the rollback of the GRANT statement comprises:

s1c1, determining that the object related to the change statement is user _ a, and the change instruction is as follows: GRANT, the corresponding backspacing instruction is REVOKE;

s2c1, performing authorization operation on the change statement, and directly determining that the rollback statement is: REVOKE A FROM user _ a;

if the change statement is REVOKE A ON tb _ a FROM role _ a; the rollback of the permission recovery statement comprises:

s1c1, determining that the object related to the change statement is role _ a, and the change instruction is as follows: REVOKE, wherein the corresponding rollback instruction is GRANT;

s2c1, performing authority recovery operation on the change statement, and directly determining that the rollback statement is: GRANT A ON tb _ aTO role _ a.

2. A database change rollback method based on the change statement reverse analysis method of claim 1, the method comprising the steps of:

s10, acquiring SQL change script input, database version and database authorization;

s20, analyzing the SQL change script to generate a statement analysis result;

and S30, generating a backspacing statement according to the reverse analysis method of the change statement based on the statement analysis result, and converting the backspacing statement into an executable backspacing SQL script file.

3. The database change rollback method of claim 2, further comprising:

and S40, inputting a rollback execution command, and operating the rollback SQL script file to realize database change rollback.

4. The database change rollback method of claim 2,

in step S10, the SQL change script is a text file containing the change statement, and the database authorization includes a database username and a password;

in the step S20, the analysis is performed by calling the SQL engine of the database version according to the SQL change script to perform Flex lexical analysis and Bison syntactic analysis.

5. The database change rollback method according to claim 2, wherein the rollback statement generated in step S30 according to the change statement reverse analysis method is a logical rollback statement composed of several clauses, and the step S30 converts the rollback statement into an executable rollback SQL script file specifically includes:

s31, recombination: reorganizing the clauses;

s32, filling: filling the recombined clauses to form a complete and legal SQL statement;

s33, mapping: mapping the SQL sentences with different implementations obtained in the step S32 into the sentences of a specific database system;

s34, merging: and merging the data import command and the statement obtained in the step S33 into the rollback SQL script file.

6. A database change rollback system based on the database change rollback method of claim 2, the system comprising:

user interface layer (100): the system is used for performing interactive processing with a user; the interactive processing comprises obtaining SQL change script input, database version, database authorization and backspacing execution command;

fallback processing layer (200): the SQL change script is used for analyzing the SQL change script, generating a statement analysis result, generating a backspacing statement according to a reverse analysis method of the change statement based on the statement analysis result, and converting the backspacing statement into an executable backspacing SQL script file;

data service layer (300): the system comprises a database, a rollback SQL script file and a rollback execution command, wherein the database is used for inquiring data from the database, backing up source data/source content and executing the rollback SQL script file when the rollback execution command is received;

database tier (400): a database system is provided.

7. The database change rollback system of claim 6,

the functional components of the user interface layer (100) include:

command processing component (101): the SQL change script and the backspace execution command are received;

information interaction component (102): the database rollback system is used for displaying the process and the result of the database rollback to a user; the functional components of the fallback processing layer (200) comprise:

SQL engine (201): according to the lexical and grammatical rules of the database, the SQL change script is analyzed and a statement analysis result is generated, the analysis of the SQL change script comprises lexical analysis and grammatical analysis, the lexical analysis adopts a Flex tool, and the grammatical analysis adopts a Bison tool;

script parsing component (202): the SQL engine (201) is used for calling the corresponding SQL engine according to the database type to complete the analysis of the SQL change script;

a reverse analysis component (203): the statement analysis result is analyzed, and a backspacing statement is generated according to the reverse analysis method of the changed statement;

a fallback statement generation component (204): the system is used for converting the backspacing statements into SQL backspacing statements related to the types of the databases by combining the data service layer (300), and providing the generated backspacing SQL script files to the data service layer (300);

the data service layer (300) comprises:

data query component (301): the SQL backspace statement is obtained from a database and required data is generated;

data backup component (302): the backup management system is used for backing up and managing source data/source content needing backup;

script execution component (303): the system is used for acquiring the backspacing SQL script file and finishing the execution of the backspacing SQL script file;

the database tier (400) comprises: oracle, MySQL, Sybase, SQL Server systems.