CN111737227A - Data modification method and system - Google Patents
Data modification method and system Download PDFInfo
- Publication number
- CN111737227A CN111737227A CN202010577833.XA CN202010577833A CN111737227A CN 111737227 A CN111737227 A CN 111737227A CN 202010577833 A CN202010577833 A CN 202010577833A CN 111737227 A CN111737227 A CN 111737227A
- Authority
- CN
- China
- Prior art keywords
- modification
- target
- database
- script
- data
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
- 238000002715 modification method Methods 0.000 title claims abstract description 23
- 238000012986 modification Methods 0.000 claims abstract description 264
- 230000004048 modification Effects 0.000 claims abstract description 264
- 238000013515 script Methods 0.000 claims abstract description 249
- 238000000034 method Methods 0.000 claims description 25
- 238000004590 computer program Methods 0.000 claims description 10
- 238000007726 management method Methods 0.000 description 23
- 238000012423 maintenance Methods 0.000 description 19
- 238000011161 development Methods 0.000 description 10
- 238000010586 diagram Methods 0.000 description 5
- 230000006870 function Effects 0.000 description 5
- 238000012217 deletion Methods 0.000 description 4
- 230000037430 deletion Effects 0.000 description 4
- 230000000694 effects Effects 0.000 description 4
- 238000004519 manufacturing process Methods 0.000 description 4
- 230000001360 synchronised effect Effects 0.000 description 4
- 238000004891 communication Methods 0.000 description 2
- 238000013523 data management Methods 0.000 description 2
- 238000003780 insertion Methods 0.000 description 2
- 230000037431 insertion Effects 0.000 description 2
- 238000013507 mapping Methods 0.000 description 2
- 230000003287 optical effect Effects 0.000 description 2
- 238000012545 processing Methods 0.000 description 2
- 238000012552 review Methods 0.000 description 2
- 230000003068 static effect Effects 0.000 description 2
- 238000012795 verification Methods 0.000 description 2
- 230000005540 biological transmission Effects 0.000 description 1
- 238000004364 calculation method Methods 0.000 description 1
- 238000010276 construction Methods 0.000 description 1
- 230000008094 contradictory effect Effects 0.000 description 1
- 230000010365 information processing Effects 0.000 description 1
- 238000010295 mobile communication Methods 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G06F16/211—Schema design and management
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/07—Responding to the occurrence of a fault, e.g. fault tolerance
- G06F11/14—Error detection or correction of the data by redundancy in operation
- G06F11/1402—Saving, restoring, recovering or retrying
- G06F11/1446—Point-in-time backing up or restoration of persistent data
- G06F11/1458—Management of the backup or restore process
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
- G06F16/2379—Updates performed during online database operations; commit processing
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
Abstract
The embodiment of the invention provides a data modification method, which comprises the following steps: acquiring a modification script and target database information uploaded by a user, wherein the modification script is used for modifying target data of a target database in a preset database set, and the modification script at least comprises a modification type, a database table name and a Where condition; generating a data modification certificate according to the target database information and the modification script; and executing modification operation on the target database according to the data modification certificate and the modification script. By the embodiment of the invention, the efficiency of data modification can be improved.
Description
Technical Field
The embodiment of the invention relates to the technical field of computers, in particular to a data modification method, a data modification system, computer equipment and a storage medium.
Background
In the prior art, when a service person needs to modify service data, a modification flow needs to be reported through a service platform, then a developer writes a corresponding modification script, the developer sends the modification script to an operation and maintenance person, and finally, the operation and maintenance person manually executes the modification script in a database. Meanwhile, in order to prevent the data from being deleted by mistake or being unrecoverable due to misoperation, the developer needs to write a backup script corresponding to the modification script to backup the service data to be modified. Therefore, in the prior art, developers need to write backup scripts on the construction site according to the modification scripts and operation and maintenance personnel manually execute the modification scripts, and the data modification implementation mode is inefficient.
Therefore, the scheme aims to solve the problem that the efficiency of data modification is low in the prior art.
Disclosure of Invention
In view of this, embodiments of the present invention provide a data modification method, system, computer device, and computer-readable storage medium, which can improve the efficiency of data modification.
The embodiment of the invention solves the technical problems through the following technical scheme:
a method of data modification, comprising:
acquiring a modification script and target database information uploaded by a user, wherein the modification script is used for modifying target data of a target database in a preset database set, and the modification script at least comprises a modification type, a database table name and a Where condition;
generating a data modification certificate according to the target database information and the modification script;
and executing modification operation on the target database according to the data modification certificate and the modification script.
Further, before the obtaining of the modification script uploaded by the user, the method further includes:
acquiring the problem management number and database information uploaded by the user;
and adding the database information into the preset database set, wherein the database information at least comprises an IP address, a port, a database type, a database SID, a user name and a password of the database.
Further, after the obtaining of the modification script uploaded by the user, the method further includes:
analyzing the modification script, and acquiring the modification type, the database table name and the Where condition according to a regular expression;
generating a query script according to the database table name and the Where condition, and executing query operation on the target database according to the query script to acquire target data corresponding to the Where condition from the target database, wherein the target data at least comprises a target field name and a target field value corresponding to the target field name;
and generating a corresponding database backup table name according to the database table name, and generating a backup script according to the target data and the database backup table name.
Further, after generating the backup script according to the target data and the database backup table name, the method further includes:
and executing backup operation on the target data according to the backup script so as to backup the target data into the target database.
Further, the performing a backup operation on the target data according to the backup script includes:
judging whether a backup table corresponding to the backup table name of the database exists in the target database, wherein the backup table at least comprises a backup field name;
when the backup table exists, judging whether the backup field name is the same as the target field name;
and when the backup field name is judged to be different from the target field name, replacing the backup field name with the target field name.
Inserting the target field value into the backup table.
Further, after the modification operation is performed on the target database according to the data modification credential and the modification script, the method further includes:
and generating a corresponding log according to the modification operation, and storing the log, the problem management number and the modification script in a preset server.
Further, after the storing the log, the question management number, and the modification script in a preset server, the method further includes:
creating a script folder, and synchronizing the log, the problem management number and the modification script from the server to the script folder;
receiving a query request initiated by the user through inputting keywords, wherein the keywords at least comprise a target script name and/or a target problem management number;
and responding to the query request, acquiring a target modification script and a target log from the script folder, and sending the target modification script and the target log to the user.
In order to achieve the above object, an embodiment of the present invention further provides a data modification system, including:
the database modification method comprises the steps that an obtaining module is used for obtaining modification scripts and target database information uploaded by a user, wherein the modification scripts are used for modifying target data of a target database in a preset database set, and the modification scripts at least comprise modification types, database table names and Where conditions;
the generating module is used for generating a data modification certificate according to the target database information and the modification script;
and the modification module is used for executing modification operation on the target database according to the data modification certificate and the modification script.
In order to achieve the above object, an embodiment of the present invention further provides a computer device, which includes a memory, a processor, and a computer program stored on the memory and executable on the processor, and the processor implements the steps of the data modification method as described above when executing the computer program.
In order to achieve the above object, an embodiment of the present invention further provides a computer-readable storage medium, in which a computer program is stored, the computer program being executable by at least one processor to cause the at least one processor to execute the steps of the data modification method as described above.
According to the data modification method, the data modification system, the computer device and the computer readable storage medium, the backup script is automatically generated according to the modification script, modification and backup operations are respectively executed on the target data according to the modification script and the backup script, and the data modification efficiency is improved.
The invention is described in detail below with reference to the drawings and specific examples, but the invention is not limited thereto.
Drawings
FIG. 1 is a flowchart illustrating steps of a data modification method according to a first embodiment of the present invention;
FIG. 2 is a block diagram of a data modification system according to a second embodiment of the present invention;
fig. 3 is a schematic hardware structure diagram of a computer device according to a third embodiment of the present invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the present invention more apparent, the present invention is described in further detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit the invention. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
Technical solutions between various embodiments may be combined with each other, but must be realized by those skilled in the art, and when the technical solutions are contradictory or cannot be realized, such a combination should not be considered to exist, and is not within the protection scope of the present invention.
Example one
Referring to fig. 1, a flowchart illustrating steps of a data modification method according to a first embodiment of the invention is shown. It is to be understood that the flow charts in the embodiments of the present method are not intended to limit the order in which the steps are performed. The following description is given by taking a computer device as an execution subject, specifically as follows:
step S100, acquiring a modification script and target database information uploaded by a user, wherein the modification script is used for modifying target data of a target database in a preset database set, and the modification script at least comprises a modification type, a database table name and a Where condition.
Specifically, when a business department needs to modify data, a data modification process needs to be reported through a preset business platform, the modification process is transferred to an operation and maintenance personnel problem platform after the approval of relevant auditors, and then the operation and maintenance personnel distribute the modification process to corresponding developers. After receiving the modification flow, developers firstly analyze the data to be modified in the modification flow, then compile corresponding modification scripts, and upload the modification scripts to a problem platform of operation and maintenance personnel for review by a development manager; after the development manager passes the verification, the operation and maintenance personnel download the modification script from the operation and maintenance personnel problem platform and upload the modification script to the data modification platform, wherein the modification script is used for modifying the data to be modified in the modification process. The type of modification of the data may include, for example: two types are update and delete.
It should be understood that data generated by a production activity is stored in a database, and when data needs to be modified, it is naturally necessary to perform a modification operation through an instruction related to the database, and the modification script is an instruction for performing the modification operation. Typically, the modification script is composed of SQL statements. Whether the data is updated or deleted, the corresponding SQL statement at least comprises three key words of modification type, database table name and Where condition. The SQL statement for updating data is composed of UPDATE + table name + set + "field name ═ value" + where condition, and the SQL statement for deleting data is composed of DELETE + from + table name + where condition. In addition, the amount of data generated by the production activity is huge, and the data needs to be stored in a plurality of databases, so in this embodiment, when the modification script written by the developer is acquired, the target database information corresponding to the modification script is also acquired.
For example, when a developer wants to update the value of the v.voucher _ status field corresponding to v.voucher _ code ═ KC9N894DJ725' in the vit _ life _ voucher v table of the target database to "2", the corresponding modified script for updating data is written as: update vit _ life _ pointer v set v. pointer _ status ═ 2'where v. pointer _ code ═ KC9N894DJ 725'; when a developer wants to delete the whole line of data corresponding to v.voucher _ code ═ KC9N894DJ725 'in the vit _ life _ voucher v table of the target data, the corresponding modified script for deleting the data is written as delete form vit _ life _ voucher v person v.voucher _ code ═ KC9N894DJ 725'.
In a preferred embodiment, before acquiring the modification script and the target database information uploaded by the user, the problem management number and the database information uploaded by the user are also acquired, and the database information is added to the preset database set, wherein the database information at least comprises an IP address, a port, a database type, a database SID, a user name and a password of the database.
For example, when a service department reports a modified flow on a service platform, a corresponding problem management number is generated. Developers can obtain the problem management numbers from the service platform and upload the problem management numbers and the corresponding modification scripts to the data modification platform together, so that users can inquire the modification scripts in the subsequent steps.
In the prior art, after a developer uploads a modification script, related operation and maintenance personnel need to manually log in a target database and execute the modification script on the target database. In order to uniformly manage the database and automatically log in the target database to execute the modification script, in this embodiment, the database information is uniformly recorded and configured by acquiring the database information uploaded by the operation and maintenance personnel, so that the subsequent execution of the modification script is facilitated. The database information at least comprises an IP address, a port, a database type, a database SID, a user name and a password of the database.
Illustratively, when a new Oracle-type database needs to be configured, the development/operation and maintenance personnel only need to click a button of 'newly added database information' on the data modification platform and input the IP address, the port, the database type, the database SID, the user name and the password of the Oracle database, so that the Oracle database can be added into the database collection of the data modification platform.
In an exemplary embodiment, after a modification script uploaded by a user is acquired, a corresponding backup script is generated according to the modification script, wherein the backup script is used for backing up the target data.
Specifically, in order to prevent data loss caused by mistakenly performing update/delete operations on target data, a developer writes a modification script, and also needs to write a corresponding backup script according to the modification script, where the backup script is used to backup the target data in a backup table of a database. In this embodiment, after the modification script uploaded by the developer is obtained, a corresponding backup script is generated according to the three keywords of the modification type, the database table name, and the where condition in the modification script. In a preferred embodiment, the modification script is analyzed, and the modification type, the database table name and the Where condition in the modification script are obtained according to the regular expression. Then, generating a query script according to the database table name and the Where condition, and executing query operation on the target database according to the query script to acquire target data corresponding to the Where condition from the target database, wherein the target data at least comprises a target field name and a target field value corresponding to the target field name. And finally, generating a corresponding database backup table name according to the database table name, and generating a backup script according to the target field name, the target field value and the database backup table name.
Specifically, after a development script uploaded by a developer is acquired, an SQL statement of the modification script is analyzed, and keywords such as update and/or delete, a database table name, Where the Where is a text that meets a condition is acquired from a character string are acquired according to a regular expression. After the keywords in the modification script are acquired, a query script is generated according to the database table name and Where conditions in the keywords, wherein the query script consists of select + from + table name + Where conditions, and therefore a corresponding backup script can be generated according to the database table name and Where conditions in the modification script. And after the backup script is generated, carrying out query operation on the target database according to the query script, thereby acquiring the target data corresponding to the where condition. It should be understood that the target data described in the present embodiment refers to a certain row of data in the database table, and a row of data is composed of several field names and corresponding field values. The updating operation of the modification script may be to update one or more field values in a certain row of data, or may be to update the field values of the whole row of data; the deletion operation of the modification script is to delete the entire line of data. After the backup script is generated, the corresponding whole line of data, namely the target data, can be inquired through the where condition, and then the whole line of data is backed up.
After the target data is acquired, firstly, a corresponding database backup table name is generated according to the database table name in the modification script, wherein the database backup table is used for storing the backup target data. And then generating a backup script according to the name of the database backup table, the field name and the field value in the target data, wherein the backup script INSERTs the acquired target data into the backup data through an INSERT statement of SQL syntax. Generally, an INSERT statement is composed of INSERT + into + table name + (field name) + values + (field value), so that a corresponding INSERT statement, i.e., the backup script, can be generated according to the field name and the corresponding field value in the target data.
Illustratively, the modification type obtained from the SQL statement "update view _ life _ pointer vset v. pointer _ status ═ 2'where v. pointer _ code ═ KC9N894DJ 725'" of the modification script through the regular expression is "update", the database table name is "view _ life _ pointer v", and the where condition is "v. And then generating a corresponding query script according to the database table name "vit _ life _ voucher v" and the where condition "v.voucher _ code ═ KC9N894DJ 725" as follows: selecting from vit _ life _ voucher v where, voucher _ code KC9N894DJ725, and obtaining the target data according to the query script: v.soucher _ code ═ KC9N894DJ725, v.soucher _ status ═ 1, v.exchange _ date ═ 2019-10-25, v.branch _ id ═ BJN473, where "v.soucher _ code," "v.voucher _ status," "v.exchange _ date," and "v.branch _ id" are target fields in the target data, and "KC 9N894 jn 725," "1," "2019-10-25," and "b473" are field values corresponding to the target fields. Then, generating a backup database table name bk _ vit _ life _ voucher v according to the database table name vit _ life _ voucher v, and generating a corresponding backup script according to the backup database table name bk _ vit _ life _ voucher v and a target field value in the target data: insert _ bk _ vit _ life _ pointer v (v.voucher _ code, v.voucher _ status, v.exchange _ date, v.branch _ id) values (KC9N894DJ725, 1, 2019-10-25, BJN 473).
In an exemplary embodiment, after the backup script is generated, a backup operation is performed on the target data according to the backup script to backup the target data in the target database.
Specifically, when the operation of the user on the database needs to be limited, the operation can be realized by giving the user the operation authority on the database. In this embodiment, when a user wants to perform an update, insertion, or deletion operation on a certain database table of a target database, the DML (Data management Language) authority of the database table needs to be obtained.
In this embodiment, the target data is backed up by establishing the backup database table in the target database, but the operation authority of the user is not limited, so that the developer can acquire the target data from the target database and store the target data in the backup database table.
In a preferred embodiment, when performing a backup operation on the target data according to the backup script, it is first determined whether a backup table corresponding to a backup table name of the database exists in the target database, where the backup table at least includes a backup field name. When the backup table exists, judging whether the backup field name is the same as the target field name; and when the backup field name is judged to be different from the target field name, replacing the backup field name with the target field name so as to update the table structure of the backup table and insert the target field value into the backup table.
Specifically, when the backup operation is performed on the target data according to the backup script, all database table names of the target database are compared with the backup database table names to determine whether a backup table corresponding to the database backup table name exists in the target database. If the backup table exists, comparing the field names of the backup table with the field names in the backup script to judge whether the table structure of the backup table is changed; and if the field name of the backup table is different from the field name in the backup script, replacing the field name of the backup table with the field name of the backup script, and inserting a target field value corresponding to the target field name in the target data into the backup table through the backup script.
In an exemplary embodiment, when the backup table does not exist, the backup table is created in the target database according to the target field, and then the target field value is inserted into the backup table.
And step S102, generating a data modification certificate according to the target database information and the modification script.
Specifically, the target database information at least includes a target database type, a target user name and a target database SID, where the database SID is an identity code of a database, and the target user name is a user having DML operation authority of a corresponding database. In this embodiment, according to a target user name uploaded by a user, a corresponding password can be acquired from a database set; and according to the target database SID, the corresponding database IP address and port can be obtained. And generating a corresponding modification certificate according to the modification script and the target database information, wherein the modification certificate is used for executing the modification script on the target database.
And step S104, executing modification operation on the target database according to the data modification certificate and the modification script.
Specifically, the IP address and the port of the target database are obtained from the database set according to the database SID in the modification certificate so as to be connected to the target database. And then acquiring a corresponding password from the database set according to the target user name in the modification certificate so as to log in the target database. And finally, executing modification operation on the target database according to the modification script so as to update or delete the target data.
In a preferred embodiment, after the modification operation is performed on the target database, a corresponding log is generated according to the modification operation, and the log, the problem management number and the modification script are stored in a preset server.
Specifically, after the modification operation is completed, a corresponding operation log is generated, the obtained problem management number, the operation log and the modification script are stored together in an wls path of a remote Linux server, and a mapping relationship among the problem number, the operation log and the modification script is established.
In another preferred embodiment, a script folder is also created in the local host computer and the log, the issue management number and the modification script are synchronized from the server into the script folder. And then receiving a query request initiated by the user through inputting a keyword, wherein the keyword at least comprises a target script name and/or a target question management number. And finally, responding to the query request, acquiring a target modification script and a target log from the script folder, and sending the target modification script and the target log to the user.
Specifically, a folder is created in the local development host, and the script and the log are synchronized into the local folder through the scp synchronization task. A user can initiate a query request by inputting a problem management number or a script name, the data modification platform traverses the file name and the file content of the modified script in the folder through java fuzzy search according to the keywords, and if the modified script name or the file content is matched, the corresponding modified script and the operation log are sent to the user.
The embodiment automatically generates the backup script according to the modification script, and automatically executes backup and modification operations on the target data, thereby improving the efficiency of data modification.
Example two
Referring to fig. 2, a schematic diagram of program modules of a data modification system according to a second embodiment of the present invention is shown. It is to be understood that the flow charts in the embodiments of the present method are not intended to limit the order in which the steps are performed. In this embodiment, the data modification system 20 may include or be divided into one or more program modules, which are stored in a storage medium and executed by one or more processors to implement the present invention and implement the above-described data modification method. The program modules referred to in the embodiments of the present invention refer to a series of computer program instruction segments that can perform specific functions, and are more suitable than the program itself for describing the execution process of the data modification system 20 in the storage medium. The following description will specifically describe the functions of the program modules of the present embodiment:
the obtaining module 200 is configured to obtain a modification script and target database information uploaded by a user, Where the modification script is used to modify target data of a target database in a preset database set, and the modification script at least includes a modification type, a database table name, and a Where condition.
Specifically, when a business department needs to modify data, a data modification process needs to be reported through a preset business platform, the modification process is transferred to an operation and maintenance personnel problem platform after the approval of relevant auditors, and then the operation and maintenance personnel distribute the modification process to corresponding developers. After receiving the modification flow, developers firstly analyze the data to be modified in the modification flow, then compile corresponding modification scripts, and upload the modification scripts to a problem platform of operation and maintenance personnel for review by a development manager; after the development manager passes the verification, the operation and maintenance personnel download the modification script from the operation and maintenance personnel problem platform and upload the modification script to the data modification platform, wherein the modification script is used for modifying the data to be modified in the modification process. The type of modification of the data may include, for example: two types are update and delete.
It should be understood that data generated by a production activity is stored in a database, and when data needs to be modified, it is naturally necessary to perform a modification operation through an instruction related to the database, and the modification script is an instruction for performing the modification operation. Typically, the modification script is composed of SQL statements. Whether the data is updated or deleted, the corresponding SQL statement at least comprises three key words of modification type, database table name and Where condition. The SQL statement for updating data is composed of UPDATE + table name + set + "field name ═ value" + where condition, and the SQL statement for deleting data is composed of DELETE + from + table name + where condition. In addition, the amount of data generated by the production activity is huge, and the data needs to be stored in a plurality of databases, so in this embodiment, when the modification script written by the developer is acquired, the target database information corresponding to the modification script is also acquired.
For example, when a developer wants to update the value of the v.voucher _ status field corresponding to v.voucher _ code ═ KC9N894DJ725' in the vit _ life _ voucher v table of the target database to "2", the corresponding modified script for updating data is written as: update vit _ life _ pointer v set v. pointer _ status ═ 2'where v. pointer _ code ═ KC9N894DJ 725'; when a developer wants to delete the whole line of data corresponding to v.voucher _ code ═ KC9N894DJ725 'in the vit _ life _ voucher v table of the target data, the corresponding modified script for deleting the data is written as delete form vit _ life _ voucher v person v.voucher _ code ═ KC9N894DJ 725'.
In a preferred embodiment, before acquiring the modification script and the target database information uploaded by the user, the problem management number and the database information uploaded by the user are also acquired, and the database information is added to the preset database set, wherein the database information at least comprises an IP address, a port, a database type, a database SID, a user name and a password of the database.
For example, when a service department reports a modified flow on a service platform, a corresponding problem management number is generated. Developers can obtain the problem management numbers from the service platform and upload the problem management numbers and the corresponding modification scripts to the data modification platform together, so that users can inquire the modification scripts in the subsequent steps.
In the prior art, after a developer uploads a modification script, related operation and maintenance personnel need to manually log in a target database and execute the modification script on the target database. In order to uniformly manage the database and automatically log in the target database to execute the modification script, in this embodiment, the database information is uniformly recorded and configured by acquiring the database information uploaded by the operation and maintenance personnel, so that the subsequent execution of the modification script is facilitated. The database information at least comprises an IP address, a port, a database type, a database SID, a user name and a password of the database.
Illustratively, when a new Oracle-type database needs to be configured, the development/operation and maintenance personnel only need to click a button of 'newly added database information' on the data modification platform and input the IP address, the port, the database type, the database SID, the user name and the password of the Oracle database, so that the Oracle database can be added into the database collection of the data modification platform.
In an exemplary embodiment, after a modification script uploaded by a user is acquired, a corresponding backup script is generated according to the modification script, wherein the backup script is used for backing up the target data.
Specifically, in order to prevent data loss caused by mistakenly performing update/delete operations on target data, a developer writes a modification script, and also needs to write a corresponding backup script according to the modification script, where the backup script is used to backup the target data in a backup table of a database. In this embodiment, after the modification script uploaded by the developer is obtained, a corresponding backup script is generated according to the three keywords of the modification type, the database table name, and the where condition in the modification script. In a preferred embodiment, the modification script is analyzed, and the modification type, the database table name and the Where condition in the modification script are obtained according to the regular expression. Then, generating a query script according to the database table name and the Where condition, and executing query operation on the target database according to the query script to acquire target data corresponding to the Where condition from the target database, wherein the target data at least comprises a target field name and a target field value corresponding to the target field name. And finally, generating a corresponding database backup table name according to the database table name, and generating a backup script according to the target field name, the target field value and the database backup table name.
Specifically, after a development script uploaded by a developer is acquired, an SQL statement of the modification script is analyzed, and keywords such as update and/or delete, a database table name, Where the Where is a text that meets a condition is acquired from a character string are acquired according to a regular expression. After the keywords in the modification script are acquired, a query script is generated according to the database table name and Where conditions in the keywords, wherein the query script consists of select + from + table name + Where conditions, and therefore a corresponding backup script can be generated according to the database table name and Where conditions in the modification script. And after the backup script is generated, carrying out query operation on the target database according to the query script, thereby acquiring the target data corresponding to the where condition. It should be understood that the target data described in the present embodiment refers to a certain row of data in the database table, and a row of data is composed of several field names and corresponding field values. The updating operation of the modification script may be to update one or more field values in a certain row of data, or may be to update the field values of the whole row of data; the deletion operation of the modification script is to delete the entire line of data. After the backup script is generated, the corresponding whole line of data, namely the target data, can be inquired through the where condition, and then the whole line of data is backed up.
After the target data is acquired, firstly, a corresponding database backup table name is generated according to the database table name in the modification script, wherein the database backup table is used for storing the backup target data. And then generating a backup script according to the name of the database backup table, the field name and the field value in the target data, wherein the backup script INSERTs the acquired target data into the backup data through an INSERT statement of SQL syntax. Generally, an INSERT statement is composed of INSERT + into + table name + (field name) + values + (field value), so that a corresponding INSERT statement, i.e., the backup script, can be generated according to the field name and the corresponding field value in the target data.
Illustratively, the modification type obtained from the SQL statement "update vit life vouchervset v. voucher status ═ 2' where v. voucher code ═ KC9N894DJ 725" of the modification script through the regular expression is "update", the database table name is "vit life voucher v", and the where condition is "v. voucher code ═ KC9N894DJ 725". And then generating a corresponding query script according to the database table name "vit _ life _ voucher v" and the where condition "v.voucher _ code ═ KC9N894DJ 725" as follows: selecting from vit _ life _ voucher v where, voucher _ code KC9N894DJ725, and obtaining the target data according to the query script: v.soucher _ code ═ KC9N894DJ725, v.soucher _ status ═ 1, v.exchange _ date ═ 2019-10-25, v.branch _ id ═ BJN473, where "v.soucher _ code," "v.voucher _ status," "v.exchange _ date," and "v.branch _ id" are target fields in the target data, and "KC 9N894 jn 725," "1," "2019-10-25," and "b473" are field values corresponding to the target fields. Then, generating a backup database table name bk _ vit _ life _ voucher v according to the database table name vit _ life _ voucher v, and generating a corresponding backup script according to the backup database table name bk _ vit _ life _ voucher v and a target field value in the target data: insert _ bk _ vit _ life _ pointer v (v.voucher _ code, v.voucher _ status, v.exchange _ date, v.branch _ id) values (KC9N894DJ725, 1, 2019-10-25, BJN 473).
In an exemplary embodiment, after the backup script is generated, a backup operation is performed on the target data according to the backup script to backup the target data in the target database.
Specifically, when the operation of the user on the database needs to be limited, the operation can be realized by giving the user the operation authority on the database. In this embodiment, when a user wants to perform an update, insertion, or deletion operation on a certain database table of a target database, the DML (Data management Language) authority of the database table needs to be obtained.
In this embodiment, the target data is backed up by establishing the backup database table in the target database, but the operation authority of the user is not limited, so that the developer can acquire the target data from the target database and store the target data in the backup database table.
In a preferred embodiment, when performing a backup operation on the target data according to the backup script, it is first determined whether a backup table corresponding to a backup table name of the database exists in the target database, where the backup table at least includes a backup field name. When the backup table exists, judging whether the backup field name is the same as the target field name; and when the backup field name is judged to be different from the target field name, replacing the backup field name with the target field name so as to update the table structure of the backup table and insert the target field value into the backup table.
Specifically, when the backup operation is performed on the target data according to the backup script, all database table names of the target database are compared with the backup database table names to determine whether a backup table corresponding to the database backup table name exists in the target database. If the backup table exists, comparing the field names of the backup table with the field names in the backup script to judge whether the table structure of the backup table is changed; and if the field name of the backup table is different from the field name in the backup script, replacing the field name of the backup table with the field name of the backup script, and inserting a target field value corresponding to the target field name in the target data into the backup table through the backup script.
In an exemplary embodiment, when the backup table does not exist, the backup table is created in the target database according to the target field, and then the target field value is inserted into the backup table.
And the generating module 202 is configured to generate a data modification credential according to the target database information and the modification script.
Specifically, the target database information at least includes a target database type, a target user name and a target database SID, where the database SID is an identity code of a database, and the target user name is a user having DML operation authority of a corresponding database. In this embodiment, according to a target user name uploaded by a user, a corresponding password can be acquired from a database set; and according to the target database SID, the corresponding database IP address and port can be obtained. And generating a corresponding modification certificate according to the modification script and the target database information, wherein the modification certificate is used for executing the modification script on the target database.
And the modification module 204 is configured to perform a modification operation on the target database according to the data modification credential and the modification script.
Specifically, the IP address and the port of the target database are obtained from the database set according to the database SID in the modification certificate so as to be connected to the target database. And then acquiring a corresponding password from the database set according to the target user name in the modification certificate so as to log in the target database. And finally, executing modification operation on the target database according to the modification script so as to update or delete the target data.
In a preferred embodiment, after the modification operation is performed on the target database, a corresponding log is generated according to the modification operation, and the log, the problem management number and the modification script are stored in a preset server.
Specifically, after the modification operation is completed, a corresponding operation log is generated, the obtained problem management number, the operation log and the modification script are stored together in an wls path of a remote Linux server, and a mapping relationship among the problem number, the operation log and the modification script is established.
In another preferred embodiment, a script folder is also created in the local host computer and the log, the issue management number and the modification script are synchronized from the server into the script folder. And then receiving a query request initiated by the user through inputting a keyword, wherein the keyword at least comprises a target script name and/or a target question management number. And finally, responding to the query request, acquiring a target modification script and a target log from the script folder, and sending the target modification script and the target log to the user.
Specifically, a folder is created in the local development host, and the script and the log are synchronized into the local folder through the scp synchronization task. A user can initiate a query request by inputting a problem management number or a script name, the data modification platform traverses the file name and the file content of the modified script in the folder through java fuzzy search according to the keywords, and if the modified script name or the file content is matched, the corresponding modified script and the operation log are sent to the user.
The embodiment automatically generates the backup script according to the modification script, and automatically executes backup and modification operations on the target data, thereby improving the efficiency of data modification.
EXAMPLE III
Fig. 3 is a schematic diagram of a hardware architecture of a computer device according to a third embodiment of the present invention. In the present embodiment, the computer device 2 is a device capable of automatically performing numerical calculation and/or information processing in accordance with a preset or stored instruction. The computer device 2 may be a rack server, a blade server, a tower server or a rack server (including an independent server or a server cluster composed of a plurality of servers), and the like. As shown in FIG. 3, the computer device 2 includes, but is not limited to, at least a memory 21, a processor 22, a network interface 23, and a data modification system 20, which may be communicatively coupled to each other via a system bus. Wherein:
in this embodiment, the memory 21 includes at least one type of computer-readable storage medium including a flash memory, a hard disk, a multimedia card, a card-type memory (e.g., SD or DX memory, etc.), a Random Access Memory (RAM), a Static Random Access Memory (SRAM), a Read Only Memory (ROM), an Electrically Erasable Programmable Read Only Memory (EEPROM), a Programmable Read Only Memory (PROM), a magnetic memory, a magnetic disk, an optical disk, and the like. In some embodiments, the storage 21 may be an internal storage unit of the computer device 2, such as a hard disk or a memory of the computer device 2. In other embodiments, the memory 21 may also be an external storage device of the computer device 2, such as a plug-in hard disk, a Smart Media Card (SMC), a Secure Digital (SD) Card, a Flash memory Card (Flash Card), or the like provided on the computer device 2. Of course, the memory 21 may also comprise both internal and external memory units of the computer device 2. In this embodiment, the memory 21 is generally used for storing an operating system installed in the computer device 2 and various application software, such as the program codes of the data modification system 20 of the second embodiment. Further, the memory 21 may also be used to temporarily store various types of data that have been output or are to be output.
The network interface 23 may comprise a wireless network interface or a wired network interface, and the network interface 23 is generally used for establishing communication connection between the computer device 2 and other electronic apparatuses. For example, the network interface 23 is used to connect the computer device 2 to an external terminal through a network, establish a data transmission channel and a communication connection between the computer device 2 and the external terminal, and the like. The network may be a wireless or wired network such as an Intranet (Intranet), the Internet (Internet), a Global System of Mobile communication (GSM), Wideband Code Division Multiple Access (WCDMA), a 4G network, a 5G network, Bluetooth (Bluetooth), Wi-Fi, and the like.
It is noted that fig. 3 only shows the computer device 2 with components 20-23, but it is to be understood that not all shown components are required to be implemented, and that more or less components may be implemented instead.
In this embodiment, the data modification system 20 stored in the memory 21 can be further divided into one or more program modules, and the one or more program modules are stored in the memory 21 and executed by one or more processors (in this embodiment, the processor 22) to complete the present invention.
For example, fig. 2 shows a schematic diagram of program modules of the data modification system 20, and in this embodiment, the data modification system 20 may be divided into an acquisition module 200, a generation module 202, and a modification module 204. Herein, the program module refers to a series of computer program instruction segments capable of performing specific functions, and is more suitable than a program for describing the execution process of the data modification system 20 in the computer device 2. The specific functions of the program modules 200 and 204 have been described in detail in the second embodiment, and are not described herein again.
Example four
The present embodiment also provides a computer-readable storage medium, such as a flash memory, a hard disk, a multimedia card, a card-type memory (e.g., SD or DX memory, etc.), a Random Access Memory (RAM), a Static Random Access Memory (SRAM), a read-only memory (ROM), an electrically erasable programmable read-only memory (EEPROM), a programmable read-only memory (PROM), a magnetic memory, a magnetic disk, an optical disk, a server, an App application mall, etc., on which a computer program is stored, which when executed by a processor implements corresponding functions. The computer readable storage medium of the embodiment is used for storing the data modification system 20, and when being executed by a processor, the data modification method of the first embodiment is implemented.
The above-mentioned serial numbers of the embodiments of the present invention are merely for description and do not represent the merits of the embodiments.
Through the above description of the embodiments, those skilled in the art will clearly understand that the method of the above embodiments can be implemented by software plus a necessary general hardware platform, and certainly can also be implemented by hardware, but in many cases, the former is a better implementation manner.
The above description is only a preferred embodiment of the present invention, and not intended to limit the scope of the present invention, and all modifications of equivalent structures and equivalent processes, which are made by using the contents of the present specification and the accompanying drawings, or directly or indirectly applied to other related technical fields, are included in the scope of the present invention.
Claims (10)
1. A method of modifying data, comprising:
acquiring a modification script and target database information uploaded by a user, wherein the modification script is used for modifying target data of a target database in a preset database set, and the modification script at least comprises a modification type, a database table name and a Where condition;
generating a data modification certificate according to the target database information and the modification script;
and executing modification operation on the target database according to the data modification certificate and the modification script.
2. The data modification method of claim 1, wherein before acquiring the modification script uploaded by the user, the method further comprises:
acquiring the problem management number and database information uploaded by the user;
and adding the database information into the preset database set, wherein the database information at least comprises an IP address, a port, a database type, a database SID, a user name and a password of the database.
3. The data modification method of claim 1, wherein after acquiring the modification script uploaded by the user, the method further comprises:
analyzing the modification script, and acquiring the modification type, the database table name and the Where condition according to a regular expression;
generating a query script according to the database table name and the Where condition, and executing query operation on the target database according to the query script to acquire target data corresponding to the Where condition from the target database, wherein the target data at least comprises a target field name and a target field value corresponding to the target field name;
and generating a corresponding database backup table name according to the database table name, and generating a backup script according to the target data and the database backup table name.
4. The data modification method of claim 3, wherein after generating the backup script according to the target data and the database backup table name, further comprising:
and executing backup operation on the target data according to the backup script so as to backup the target data into the target database.
5. The data modification method of claim 4, wherein the performing a backup operation on the target data according to the backup script comprises:
judging whether a backup table corresponding to the backup table name of the database exists in the target database, wherein the backup table at least comprises a backup field name;
when the backup table exists, judging whether the backup field name is the same as the target field name;
when the backup field name is judged to be different from the target field name, replacing the backup field name with the target field name;
inserting the target field value into the backup table.
6. The data modification method of claim 1, further comprising, after performing a modification operation on the target database according to the data modification credential and the modification script:
and generating a corresponding log according to the modification operation, and storing the log, the problem management number and the modification script in a preset server.
7. The data modification method according to claim 6, wherein after storing the log, the question management number, and the modification script in a preset server, the method further comprises:
creating a script folder, and synchronizing the log, the problem management number and the modification script from the server to the script folder;
receiving a query request initiated by the user through inputting keywords, wherein the keywords at least comprise a target script name and/or a target problem management number;
and responding to the query request, acquiring a target modification script and a target log from the script folder, and sending the target modification script and the target log to the user.
8. A data modification system, comprising:
the database modification method comprises the steps that an obtaining module is used for obtaining modification scripts and target database information uploaded by a user, wherein the modification scripts are used for modifying target data of a target database in a preset database set, and the modification scripts at least comprise modification types, database table names and Where conditions;
the generating module is used for generating a data modification certificate according to the target database information and the modification script;
and the modification module is used for executing modification operation on the target database according to the data modification certificate and the modification script.
9. A computer device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, characterized in that the processor implements the steps of the data modification method according to any one of claims 1 to 7 when executing the computer program.
10. A computer-readable storage medium, in which a computer program is stored which is executable by at least one processor for causing the at least one processor to carry out the steps of the data modification method as claimed in any one of claims 1 to 7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010577833.XA CN111737227B (en) | 2020-06-22 | 2020-06-22 | Data modification method and system |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010577833.XA CN111737227B (en) | 2020-06-22 | 2020-06-22 | Data modification method and system |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN111737227A true CN111737227A (en) | 2020-10-02 |
| CN111737227B CN111737227B (en) | 2024-04-02 |
Family
ID=72650697
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010577833.XA Active CN111737227B (en) | 2020-06-22 | 2020-06-22 | Data modification method and system |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111737227B (en) |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112256672A (en) * | 2020-10-22 | 2021-01-22 | 中国联合网络通信集团有限公司 | Database change approval method and device |
| CN112380239A (en) * | 2020-11-17 | 2021-02-19 | 平安普惠企业管理有限公司 | Script generation method, device, equipment and storage medium |
| CN112685077A (en) * | 2020-12-29 | 2021-04-20 | 平安普惠企业管理有限公司 | Data modification method, system, computer device and computer readable storage medium |
| CN112748909A (en) * | 2021-01-22 | 2021-05-04 | 平安普惠企业管理有限公司 | Data modification method, device, equipment and medium based on neural network model |
Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20100250485A1 (en) * | 2009-03-31 | 2010-09-30 | Trapeze Software Inc. | System for Aggregating Data and a Method for Providing the Same |
| US20150381593A1 (en) * | 2014-06-27 | 2015-12-31 | International Business Machines Corporation | Privileged access gateway for accessing systems and/or applications |
| US20170005960A1 (en) * | 2010-12-12 | 2017-01-05 | Pecan Technologies Inc | Systems methods and computer-readable storage media for messaging and presence modification |
| US20170148000A1 (en) * | 2015-11-23 | 2017-05-25 | Switch, Inc. | Management of remotely stored data |
| CN108427699A (en) * | 2017-09-22 | 2018-08-21 | 平安科技（深圳）有限公司 | The method, apparatus and storage medium of fast initialization system database |
| CN109471857A (en) * | 2018-09-25 | 2019-03-15 | 中国平安人寿保险股份有限公司 | Data modification method, device and storage medium based on SQL statement |
| CN110109897A (en) * | 2019-04-15 | 2019-08-09 | 深圳壹账通智能科技有限公司 | Database script generation method, device, computer equipment and storage medium |
-
2020
- 2020-06-22 CN CN202010577833.XA patent/CN111737227B/en active Active
Patent Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20100250485A1 (en) * | 2009-03-31 | 2010-09-30 | Trapeze Software Inc. | System for Aggregating Data and a Method for Providing the Same |
| US20170005960A1 (en) * | 2010-12-12 | 2017-01-05 | Pecan Technologies Inc | Systems methods and computer-readable storage media for messaging and presence modification |
| US20150381593A1 (en) * | 2014-06-27 | 2015-12-31 | International Business Machines Corporation | Privileged access gateway for accessing systems and/or applications |
| US20170148000A1 (en) * | 2015-11-23 | 2017-05-25 | Switch, Inc. | Management of remotely stored data |
| CN108427699A (en) * | 2017-09-22 | 2018-08-21 | 平安科技（深圳）有限公司 | The method, apparatus and storage medium of fast initialization system database |
| CN109471857A (en) * | 2018-09-25 | 2019-03-15 | 中国平安人寿保险股份有限公司 | Data modification method, device and storage medium based on SQL statement |
| CN110109897A (en) * | 2019-04-15 | 2019-08-09 | 深圳壹账通智能科技有限公司 | Database script generation method, device, computer equipment and storage medium |
Non-Patent Citations (1)
| Title |
|---|
| 张梁斌 等: "数据库表定义的动态修改方法", 计算机工程与设计, vol. 26, no. 8, 28 August 2005 (2005-08-28), pages 2161 - 2163 * |
Cited By (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112256672A (en) * | 2020-10-22 | 2021-01-22 | 中国联合网络通信集团有限公司 | Database change approval method and device |
| CN112256672B (en) * | 2020-10-22 | 2023-05-30 | 中国联合网络通信集团有限公司 | Database change approval method and device |
| CN112380239A (en) * | 2020-11-17 | 2021-02-19 | 平安普惠企业管理有限公司 | Script generation method, device, equipment and storage medium |
| CN112685077A (en) * | 2020-12-29 | 2021-04-20 | 平安普惠企业管理有限公司 | Data modification method, system, computer device and computer readable storage medium |
| CN112748909A (en) * | 2021-01-22 | 2021-05-04 | 平安普惠企业管理有限公司 | Data modification method, device, equipment and medium based on neural network model |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111737227B (en) | 2024-04-02 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111737227B (en) | Data modification method and system | |
| WO2022126974A1 (en) | Kafka-based incremental data synchronization method and apparatus, device, and medium | |
| CN110209650B (en) | Data normalization and migration method and device, computer equipment and storage medium | |
| CN107220142B (en) | Method and device for executing data recovery operation | |
| CN110063042B (en) | Database fault response method and terminal thereof | |
| CN111857777B (en) | System updating method and device based on double-core intelligent ammeter and computer equipment | |
| WO2021174817A1 (en) | Database automated auditing method and system, device, and storage medium | |
| CN110688305B (en) | Test environment synchronization method, device, medium and electronic equipment | |
| CN115858488A (en) | Parallel migration method and device based on data governance and readable medium | |
| CN110941629B (en) | Metadata processing method, apparatus, device and computer readable storage medium | |
| CN114443294B (en) | Big data service component deployment method, system, terminal and storage medium | |
| CN116070294A (en) | Authority management method, system, device, server and storage medium | |
| CN115129360A (en) | Code file management method and device, electronic equipment and storage medium | |
| CN106547756B (en) | Database creation method and device | |
| CN114816470A (en) | Metadata database management method and device, electronic equipment and medium | |
| CN114625751A (en) | Data tracing query method and device based on block chain | |
| CN114489674A (en) | Data verification method and device of dynamic data model | |
| CN114020840A (en) | Data processing method, device, server, storage medium and product | |
| CN112597023A (en) | Case management method and device based on guide picture, computer equipment and storage medium | |
| CN111897794A (en) | Database maintenance method and device, electronic equipment and storage medium | |
| CN112417012A (en) | Data processing method and system | |
| CN113626409B (en) | Test data processing method, device, equipment and storage medium | |
| CN116302206B (en) | Presto data source hot loading method based on MQ | |
| CN111522783B (en) | Data synchronization method, device, electronic equipment and computer readable storage medium | |
| CN116483926A (en) | Data table synchronization method and device, storage medium and computer equipment |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |