CN111737227B - Data modification method and system - Google Patents
Data modification method and system Download PDFInfo
- Publication number
- CN111737227B CN111737227B CN202010577833.XA CN202010577833A CN111737227B CN 111737227 B CN111737227 B CN 111737227B CN 202010577833 A CN202010577833 A CN 202010577833A CN 111737227 B CN111737227 B CN 111737227B
- Authority
- CN
- China
- Prior art keywords
- target
- modification
- script
- database
- 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.)
- Active
Links
- 238000002715 modification method Methods 0.000 title claims abstract description 18
- 238000012986 modification Methods 0.000 claims abstract description 275
- 230000004048 modification Effects 0.000 claims abstract description 275
- 238000013515 script Methods 0.000 claims abstract description 259
- 238000000034 method Methods 0.000 claims description 17
- 238000004590 computer program Methods 0.000 claims description 10
- 238000012545 processing Methods 0.000 claims description 5
- 238000012423 maintenance Methods 0.000 description 17
- 238000011161 development Methods 0.000 description 8
- 238000004519 manufacturing process Methods 0.000 description 6
- 230000006870 function Effects 0.000 description 5
- 230000001360 synchronised effect Effects 0.000 description 4
- 238000012795 verification Methods 0.000 description 4
- 238000010586 diagram Methods 0.000 description 3
- 238000004422 calculation algorithm Methods 0.000 description 2
- 238000004891 communication Methods 0.000 description 2
- 230000000694 effects Effects 0.000 description 2
- 238000013507 mapping Methods 0.000 description 2
- 238000010295 mobile communication Methods 0.000 description 2
- 230000003287 optical effect Effects 0.000 description 2
- 230000003068 static effect Effects 0.000 description 2
- 230000005540 biological transmission Effects 0.000 description 1
- 238000004364 calculation method Methods 0.000 description 1
- 230000010365 information processing Effects 0.000 description 1
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 at least comprises a modification type, a database table name and a white 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 data modification efficiency 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 system, computer equipment and a storage medium.
Background
In the prior art, when a service person needs to modify service data, a modification flow is required 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 data from being deleted by mistake or being manipulated by mistake to be unrecoverable, a developer also needs to write a backup script corresponding to the modification script to backup the service data to be modified. It follows that in the prior art, a developer needs to manually write a backup script according to a modification script, and the modification script is manually executed by an operation and maintenance person, so that the implementation manner of data modification is inefficient.
Therefore, the scheme aims to solve the problem of low data modification efficiency in the prior art.
Disclosure of Invention
In view of the above, the 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 data modification method, 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 at least comprises a modification type, a database table name and a white 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 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 a database.
Further, after the modification script uploaded by the user is obtained, the method further includes:
analyzing the modification script, and acquiring the modification type, the database table name and the white condition according to a regular expression;
generating a query script according to the database table name and the Where condition, and executing a 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 the backup script is generated 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 database backup table name 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.
And inserting the target field value into the backup table.
Further, after the modifying operation is performed on the target database according to the data modification certificate 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 into a preset server.
Further, after the log, the problem management number, and the modification script are stored 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 into the script folder;
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 problem management number;
and responding to the query request, acquiring a target modification script and a target log from the script folder, and transmitting 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 system comprises an acquisition module, a modification module and a target database processing module, wherein the acquisition module is used for acquiring a modification script and target database information uploaded by a user, 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 white condition;
the generation 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.
To achieve the above object, an embodiment of the present invention also provides a computer apparatus including a memory, a processor, and a computer program stored on the memory and executable on the processor, the processor implementing the steps of the data modification method as described above when executing the computer program.
To achieve the above object, an embodiment of the present invention also provides a computer-readable storage medium having stored therein a computer program executable by at least one processor to cause the at least one processor to perform the steps of the data modification method as described above.
According to the data modification method, the system, the computer equipment and the computer readable storage medium, the backup script is automatically generated according to the modification script, and the modification and backup operations are respectively executed on the target data according to the modification script and the backup script, so that the data modification efficiency is improved.
The invention will now be described in more detail with reference to the drawings and specific examples, which are not intended to limit the invention thereto.
Drawings
FIG. 1 is a flowchart illustrating a data modification method according to a first embodiment of the present invention;
FIG. 2 is a schematic diagram illustrating a program module of a data modification system according to a second embodiment of the present invention;
fig. 3 is a schematic hardware structure of a computer device according to a third embodiment of the invention.
Detailed Description
The present invention will be described in further detail with reference to the drawings and examples, in order to make the objects, technical solutions and advantages of the present invention more apparent. It should be understood that the specific embodiments described herein are for purposes of illustration only and are not intended to limit the scope of the invention. All other embodiments, which can be made by those skilled in the art based on the embodiments of the invention without making any inventive effort, are intended to be within the scope of the invention.
The technical solutions between the embodiments may be combined with each other, but it is necessary to base the implementation on the basis of those skilled in the art that when the combination of technical solutions contradicts or cannot be implemented, it should be considered that the combination of technical solutions does not exist and is not within the scope of protection claimed by the present invention.
Example 1
Referring to fig. 1, a flowchart of steps of a data modification method according to a first embodiment of the present invention is shown. It will be appreciated that the flow charts in the method embodiments are not intended to limit the order in which the steps are performed. The following description is exemplary with a computer device as an execution subject, and specifically follows:
step S100, obtaining 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 at least comprises a modification type, a database table name and a white condition.
Specifically, when the business department needs to modify the data, the data modification flow is firstly reported through a preset business platform, after the modification flow is approved by related auditors, the data is transferred to an operation and maintenance personnel problem platform, and then the operation and maintenance personnel distribute the modification flow to corresponding developers. After receiving the modification flow, a developer firstly analyzes data to be modified in the modification flow, then writes a corresponding modification script, and uploads the modification script to an operation and maintenance personnel problem platform for verification by a development manager; after the development manager passes the verification, the operation and maintenance personnel downloads the modification script from the operation and maintenance personnel problem platform and uploads the modification script to the data modification platform, wherein the modification script is used for modifying the data needing to be modified in the modification flow. The type of modification of the data may include, for example: both types of updating and deleting.
It should be appreciated that the data generated by the production campaign is stored in a database, and that when modification of the data is required, it is naturally necessary to perform the modification operation by means of the relevant instructions of the database, the modification script being instructions for performing the modification operation. Typically, the modification script consists of SQL statements. Whether the data is updated or deleted, the corresponding SQL statement at least comprises three keywords of modification type, database table name and white condition. The SQL statement for updating data consists of update+table name+set+ "field name=value" +where conditions, and the SQL statement for deleting data consists of delete+from+table name+where conditions. In addition, the amount of data generated by the production activity is huge and needs to be stored in a plurality of databases, so in this embodiment, when a modification script written by a developer is acquired, target database information corresponding to the modification script is also acquired.
Illustratively, 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", a corresponding modification script for updating data is written as follows: update vit_life_voucher v set v. Voucher_status= '2'where v.voucher_code = 'KC9N894DJ725'; when a developer wants to delete the whole data corresponding to v.voucher_code= 'KC9N894DJ725' in the vit_life_voucher v table of the target data, a corresponding modification script for deleting the data is written as delete form vit_life_voucher v wherem v.voucher_code= 'KC9N894DJ725'.
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 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.
For example, when the service department reports the modification flow on the service platform, a corresponding problem management number is generated. The developer can obtain the problem management number from the service platform, and upload the problem management number and the corresponding modification script to the data modification platform together, so that the user can inquire the modification script in the subsequent step.
The production data are stored in a plurality of different databases, and in the prior process, after the developer uploads the modification script, the relevant operation and maintenance personnel need to log in the target database manually and execute the modification script on the target database. In order to perform unified management on the database and realize automatic login to the target database to execute the modification script, in the embodiment, the database information is uniformly recorded and configured by acquiring the database information uploaded by the operation and maintenance personnel, so that the modification script can be conveniently executed subsequently. The database information at least comprises the IP address, port, database type, database SID, user name and password of the database.
For example, when a new Oracle type database needs to be configured, the developer/operator only needs to click on a "new database information" button on the data modification platform and input the IP address, port, database type, database SID, user name and password of the Oracle database, so that the Oracle database can be added into the database set of the data modification platform.
In an exemplary embodiment, after the modification script uploaded by the user is obtained, a corresponding backup script is further generated according to the modification script, where the backup script is used for backing up the target data.
Specifically, in order to prevent the data loss caused by performing the update/delete operation on the target data by mistake, the developer writes a modification script, and further 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 the database. In this embodiment, after the modification script uploaded by the developer is obtained, a corresponding backup script is generated according to 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 parsed, and the modification type, database table name and white condition in the modification script are obtained according to a regular expression. And then, generating a query script according to the database table name and the white condition, and executing query operation on the target database according to the query script so as to acquire target data corresponding to the white 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 obtained, analyzing an SQL sentence of the modified script, and obtaining keywords such as update and/or delete, database table names, white conditions and the like according to a regular expression, wherein the regular expression is an algorithm mode for obtaining a text meeting the conditions from a character string. After the keywords in the modification script are obtained, a query script is generated according to the database table names and the Where condition in the keywords, wherein the query script consists of the select+from+table names+where condition, and therefore, the corresponding backup script can be generated according to the database table names and the Where condition in the modification script. And after the backup script is generated, inquiring the target database according to the inquiring script, so as to obtain target data corresponding to the where condition. It should be understood that the target data in this embodiment refers to a certain row of data in the database table, where 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 line of data, or may be to update the field values of the whole line of data; the deleting operation of the modification script is to delete the whole line of data. After the backup script is generated, the corresponding whole line of data, namely the target data, can be queried through a sphere condition, and then the whole line of data is backed up.
After the target data is acquired, 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 backed-up target data. And then generating a backup script according to the database backup table name, 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 grammar. Typically, an INSERT statement consists of insert+intotable name+ (field name) +values+ (field value), so that a corresponding INSERT statement, i.e., the backup script, can be generated from the field name and corresponding field value in the target data.
Illustratively, the modification type obtained from the SQL statement "update vit_life_voucher v set v.voucher_status= '2'where v.voucher_code = 'KC9N894DJ725'" of the modification script by the regular expression is "update", the database table is named "vit_life_voucher v", and the where condition is "v.voucher_code=KC9N 894DJ725". Then generating a corresponding query script according to the database table name 'vit_life_voucherv' and the where condition 'v.vouchercode=kc9n894dj725', wherein the query script is: selecting from_life_voucher v w here v.voucher_code=kc9n894dj725, and querying the obtained target data according to the query script as follows: v.voucher_code=kc9n894dj725, v.voucher_status=1, v.exchange_date=2019-10-25, v.branch_id=bjn 473, wherein "v.voucher_code", "v.voucher_status", "v.exchange_date", and "v.branch_id" are target fields in the target data, and "kc9n894dj725", "1", "2019-10-25", and "BJN473" 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 target fields and target field values in target data: insert into bk_vit_life_voucher v (v.voucher_code, v.voucher_status, v.exchange_date, v.branch_id) values (KC 9N894DJ725,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, so as to backup the target data into 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 operation authority on the database. In this embodiment, when a user wants to perform an update, insert, or delete operation on a certain database table of a target database, it is necessary to obtain the DML (Data Manipulation Language ) authority of the database table.
In the present embodiment, the target data is backed up by creating a backup database table in the target database, but the operation authority of the user is not limited, so that the developer can store the target data acquired from the target database in the backup database table.
In a preferred embodiment, when executing a backup operation on the target data according to the backup script, it is first determined whether a backup table corresponding to the database backup table name 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 or not; 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 inserting the target field value into the backup table.
Specifically, when executing a backup operation 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 first to determine whether a backup table corresponding to the database backup table names exists in the target database. If the backup table exists, comparing the field name of the backup table with the field name in the backup script to judge whether the table structure of the backup table is changed or not; if the field names of the backup table are different from the field names in the backup script, the field names of the backup table are replaced by the field names of the backup script, and the target field values corresponding to the target field names in the target data are inserted 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 a 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 comprises a target database type, a target user name and a target database SID, wherein the database SID is an identification code of a database, and the target user name is a user with DML operation authority of the corresponding database. In this embodiment, according to the target user name uploaded by the user, the corresponding password may be obtained from the database set; and obtaining the corresponding database IP address and port according to the target database SID. 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 port of the target database are obtained from the database collection according to the database SID in the modification certificate so as to be connected to the target database. And then obtaining a corresponding password from the database set according to the target user name in the modification certificate so as to log in to 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 under a wls path of a remote Linux server, and a mapping relation 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 and the log, the problem 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 keywords, wherein the keywords at least comprise a target script name and/or a target problem management number. And finally, responding to the query request, acquiring a target modification script and a target log from the script folder, and transmitting the target modification script and the target log to the user.
Specifically, a folder is created in a local development host, and scripts and logs are synchronized into the local folder through a scp synchronization task. The user can initiate a query request by inputting a problem management number or a script name, the data modification platform searches through the modification script file name and the file content in the folder in a fuzzy manner through java according to the keywords, and if the modification script name or the file content is matched, the corresponding modification script and the operation log are sent to the user.
According to the embodiment, the backup script is automatically generated according to the modification script, and the backup and modification operation is automatically executed on the target data, so that the data modification efficiency is improved.
Example two
Referring to fig. 2, a schematic diagram of a program module of a data modification system according to a second embodiment of the invention is shown. It will be appreciated that the flow charts in the method embodiments 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 stored in a storage medium and executed by one or more processors to accomplish the present invention and may implement the data modification methods described above. Program modules depicted in the embodiments of the present invention are directed to a series of computer program instruction segments capable of performing the specified functions, which are more suitable than the programs themselves for describing the execution of the data modification system 20 in a storage medium. The following description will specifically describe functions of each program module 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 configured 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 the business department needs to modify the data, the data modification flow is firstly reported through a preset business platform, after the modification flow is approved by related auditors, the data is transferred to an operation and maintenance personnel problem platform, and then the operation and maintenance personnel distribute the modification flow to corresponding developers. After receiving the modification flow, a developer firstly analyzes data to be modified in the modification flow, then writes a corresponding modification script, and uploads the modification script to an operation and maintenance personnel problem platform for verification by a development manager; after the development manager passes the verification, the operation and maintenance personnel downloads the modification script from the operation and maintenance personnel problem platform and uploads the modification script to the data modification platform, wherein the modification script is used for modifying the data needing to be modified in the modification flow. The type of modification of the data may include, for example: both types of updating and deleting.
It should be appreciated that the data generated by the production campaign is stored in a database, and that when modification of the data is required, it is naturally necessary to perform the modification operation by means of the relevant instructions of the database, the modification script being instructions for performing the modification operation. Typically, the modification script consists of SQL statements. Whether the data is updated or deleted, the corresponding SQL statement at least comprises three keywords of modification type, database table name and white condition. The SQL statement for updating data consists of update+table name+set+ "field name=value" +where conditions, and the SQL statement for deleting data consists of delete+from+table name+where conditions. In addition, the amount of data generated by the production activity is huge and needs to be stored in a plurality of databases, so in this embodiment, when a modification script written by a developer is acquired, target database information corresponding to the modification script is also acquired.
Illustratively, 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", a corresponding modification script for updating data is written as follows: update vit_life_voucher v set v. Voucher_status= '2'where v.voucher_code = 'KC9N894DJ725'; when a developer wants to delete the whole data corresponding to v.voucher_code= 'KC9N894DJ725' in the vit_life_voucher v table of the target data, a corresponding modification script for deleting the data is written as delete form vit_life_voucher v wherem v.voucher_code= 'KC9N894DJ725'.
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 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.
For example, when the service department reports the modification flow on the service platform, a corresponding problem management number is generated. The developer can obtain the problem management number from the service platform, and upload the problem management number and the corresponding modification script to the data modification platform together, so that the user can inquire the modification script in the subsequent step.
The production data are stored in a plurality of different databases, and in the prior process, after the developer uploads the modification script, the relevant operation and maintenance personnel need to log in the target database manually and execute the modification script on the target database. In order to perform unified management on the database and realize automatic login to the target database to execute the modification script, in the embodiment, the database information is uniformly recorded and configured by acquiring the database information uploaded by the operation and maintenance personnel, so that the modification script can be conveniently executed subsequently. The database information at least comprises the IP address, port, database type, database SID, user name and password of the database.
For example, when a new Oracle type database needs to be configured, the developer/operator only needs to click on a "new database information" button on the data modification platform and input the IP address, port, database type, database SID, user name and password of the Oracle database, so that the Oracle database can be added into the database set of the data modification platform.
In an exemplary embodiment, after the modification script uploaded by the user is obtained, a corresponding backup script is further generated according to the modification script, where the backup script is used for backing up the target data.
Specifically, in order to prevent the data loss caused by performing the update/delete operation on the target data by mistake, the developer writes a modification script, and further 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 the database. In this embodiment, after the modification script uploaded by the developer is obtained, a corresponding backup script is generated according to 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 parsed, and the modification type, database table name and white condition in the modification script are obtained according to a regular expression. And then, generating a query script according to the database table name and the white condition, and executing query operation on the target database according to the query script so as to acquire target data corresponding to the white 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 obtained, analyzing an SQL sentence of the modified script, and obtaining keywords such as update and/or delete, database table names, white conditions and the like according to a regular expression, wherein the regular expression is an algorithm mode for obtaining a text meeting the conditions from a character string. After the keywords in the modification script are obtained, a query script is generated according to the database table names and the Where condition in the keywords, wherein the query script consists of the select+from+table names+where condition, and therefore, the corresponding backup script can be generated according to the database table names and the Where condition in the modification script. And after the backup script is generated, inquiring the target database according to the inquiring script, so as to obtain target data corresponding to the where condition. It should be understood that the target data in this embodiment refers to a certain row of data in the database table, where 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 line of data, or may be to update the field values of the whole line of data; the deleting operation of the modification script is to delete the whole line of data. After the backup script is generated, the corresponding whole line of data, namely the target data, can be queried through a sphere condition, and then the whole line of data is backed up.
After the target data is acquired, 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 backed-up target data. And then generating a backup script according to the database backup table name, 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 grammar. Typically, an INSERT statement consists of insert+intotable name+ (field name) +values+ (field value), so that a corresponding INSERT statement, i.e., the backup script, can be generated from the field name and corresponding field value in the target data.
Illustratively, the modification type obtained from the SQL statement "update vit_life_voucherv set v.voucherstate= '2'where v.voucher_code = 'KC9N894DJ725'" of the modification script by the regular expression is "update", the database table is named "vit_life_voucherv", and the window condition is "v.voucherjcode=KC9N 894DJ725". Then generating a corresponding query script according to the database table name 'vit_life_voucherv' and the where condition 'v.vouchercode=kc9n894dj725', wherein the query script is: select from_life_voucher v w here v.voucher_code=kc9n894dj725, and the target data obtained by querying according to the query script are: v.voucher_code=kc9n894dj725, v.voucher_status=1, v.exchange_date=2019-10-25, v.branch_id=bjn 473, wherein "v.voucher_code", "v.voucher_status", "v.exchange_date", and "v.branch_id" are target fields in the target data, and "kc9n894dj725", "1", "2019-10-25", and "BJN473" 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 target fields and target field values in target data: insert into bk_vit_life_voucher v (v.voucher_code, v.voucher_status, v.exchange_date, v.branch_id) values (KC 9N894DJ725,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, so as to backup the target data into 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 operation authority on the database. In this embodiment, when a user wants to perform an update, insert, or delete operation on a certain database table of a target database, it is necessary to obtain the DML (Data Manipulation Language ) authority of the database table.
In the present embodiment, the target data is backed up by creating a backup database table in the target database, but the operation authority of the user is not limited, so that the developer can store the target data acquired from the target database in the backup database table.
In a preferred embodiment, when executing a backup operation on the target data according to the backup script, it is first determined whether a backup table corresponding to the database backup table name 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 or not; 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 inserting the target field value into the backup table.
Specifically, when executing a backup operation 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 first to determine whether a backup table corresponding to the database backup table names exists in the target database. If the backup table exists, comparing the field name of the backup table with the field name in the backup script to judge whether the table structure of the backup table is changed or not; and if the field names of the backup table are different from the field names in the backup script, replacing the field names of the backup table with the field names 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 a target field value is inserted into the backup table.
A generating module 202, configured to generate a data modification credential according to the target database information and the modification script.
Specifically, the target database information at least comprises a target database type, a target user name and a target database SID, wherein the database SID is an identification code of a database, and the target user name is a user with DML operation authority of the corresponding database. In this embodiment, according to the target user name uploaded by the user, the corresponding password may be obtained from the database set; and obtaining the corresponding database IP address and port according to the target database SID. 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 used for executing modification operation on the target database according to the data modification certificate and the modification script.
Specifically, the IP address and port of the target database are obtained from the database collection according to the database SID in the modification certificate so as to be connected to the target database. And then obtaining a corresponding password from the database set according to the target user name in the modification certificate so as to log in to 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 under a wls path of a remote Linux server, and a mapping relation 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 and the log, the problem 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 keywords, wherein the keywords at least comprise a target script name and/or a target problem management number. And finally, responding to the query request, acquiring a target modification script and a target log from the script folder, and transmitting the target modification script and the target log to the user.
Specifically, a folder is created in a local development host, and scripts and logs are synchronized into the local folder through a scp synchronization task. The user can initiate a query request by inputting a problem management number or a script name, the data modification platform searches through the modification script file name and the file content in the folder in a fuzzy manner through java according to the keywords, and if the modification script name or the file content is matched, the corresponding modification script and the operation log are sent to the user.
According to the embodiment, the backup script is automatically generated according to the modification script, and the backup and modification operation is automatically executed on the target data, so that the data modification efficiency is improved.
Example III
Referring to fig. 3, a hardware architecture diagram of a computer device according to a third embodiment of the present invention is shown. In this embodiment, the computer device 2 is a device capable of automatically performing numerical calculation and/or information processing according to 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 a stand-alone server, or a server cluster made up of multiple servers), or 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 are 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 flash memory, a hard disk, a multimedia card, a card 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 memory 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, which are provided on the computer device 2. Of course, the memory 21 may also include both internal storage units of the computer device 2 and external storage devices. In this embodiment, the memory 21 is typically used to store an operating system and various types of application software installed on the computer device 2, such as program codes of the data modification system 20 of the second embodiment. Further, the memory 21 may be used to temporarily store various types of data that have been output or are to be output.
The processor 22 may be a central processing unit (Central Processing Unit, CPU), controller, microcontroller, microprocessor, or other data processing chip in some embodiments. The processor 22 is typically used to control the overall operation of the computer device 2. In this embodiment, the processor 22 is configured to execute the program code stored in the memory 21 or process data, for example, execute the data modification system 20, to implement the data modification method of the first embodiment.
The network interface 23 may comprise a wireless network interface or a wired network interface, which network interface 23 is typically used for establishing a communication connection between the computer apparatus 2 and other electronic devices. 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 an Intranet (Intranet), the Internet (Internet), a global system for mobile communications (Global System of Mobile communication, GSM), a wideband code division multiple access (Wideband Code Division MultipleAccess, WCDMA), a 4G network, a 5G network, bluetooth (Bluetooth), wi-Fi, or other wireless or wired network.
It is noted that fig. 3 only shows a computer device 2 having components 20-23, but it is understood that not all of the illustrated components are required to be implemented, and that more or fewer components may alternatively be implemented.
In this embodiment, the data modification system 20 stored in the memory 21 may also be divided into one or more program modules, which are stored in the memory 21 and executed by one or more processors (the processor 22 in this embodiment) to complete the present invention.
For example, fig. 2 shows a schematic program module 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. Program modules in the present invention are understood to mean a series of computer program instruction segments, which perform a particular function, more appropriately than a program, for describing the execution of the data modification system 20 in the computer device 2. The specific functions of the program modules 200-204 are described in detail in the second embodiment, and are not described herein.
Example IV
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 store, etc., on which a computer program is stored, which when executed by a processor, performs the corresponding functions. The computer readable storage medium of the present embodiment is used to store the data modification system 20, and when executed by a processor, implements the data modification method of the first embodiment.
The foregoing embodiment numbers of the present invention are merely for the purpose of description, and do not represent the advantages or disadvantages of the embodiments.
From the above description of the embodiments, it will be clear to those skilled in the art that the above-described embodiment method may be implemented by means of software plus a necessary general hardware platform, but of course may also be implemented by means of hardware, but in many cases the former is a preferred embodiment.
The foregoing description is only of the preferred embodiments of the present invention, and is not intended to limit the scope of the invention, but rather is intended to cover any equivalents of the structures or equivalent processes disclosed herein or in the alternative, which may be employed directly or indirectly in other related arts.
Claims (7)
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 at least comprises a modification type, a database table name and a white condition;
generating a data modification certificate according to the target database information and the modification script, wherein the target database information comprises a target database type, a target user name and a target database SID;
Performing a modification operation on the target database according to the data modification certificate and the modification script;
after the modification script uploaded by the user is obtained, the method further comprises the following steps:
analyzing the modification script, and acquiring the modification type, the database table name and the white condition according to a regular expression;
generating a query script according to the database table name and the Where condition, and executing a 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;
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;
after the modifying operation is performed on the target database according to the data modifying certificate and the modifying script, the method further comprises: generating a corresponding log according to the modification operation, and storing the log, the problem management number and the modification script into a preset server;
After the log, the problem management number and the modification script are stored 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 into the script folder;
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 problem management number;
and responding to the query request, acquiring a target modification script and a target log from the script folder, and transmitting the target modification script and the target log to the user.
2. The method for modifying data according to claim 1, further comprising, before the step of obtaining the modification script uploaded by the user:
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 a database.
3. The data modification method of claim 1, wherein after generating a backup script from 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.
4. A data modification method as claimed in claim 3, wherein said performing a backup operation on said target data in accordance with said backup script comprises:
judging whether a backup table corresponding to the database backup table name 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;
and inserting the target field value into the backup table.
5. A data modification system, comprising:
the system comprises an acquisition module, a modification module and a target database processing module, wherein the acquisition module is used for acquiring a modification script and target database information uploaded by a user, 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 white condition;
The generation module is used for generating a data modification certificate according to the target database information and the modification script, wherein the target database information comprises a target database type, a target user name and a target database SID;
the modification module is used for executing modification operation on the target database according to the data modification certificate and the modification script;
the acquisition module is further used for analyzing the modification script and acquiring the modification type, the database table name and the white condition according to a regular expression; generating a query script according to the database table name and the Where condition, and executing a 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; 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;
the modification module is further configured to generate a corresponding log according to the modification operation, and store the log, the problem management number and the modification script into a preset server;
The modification module is further used for creating a script folder and synchronizing the log, the problem management number and the modification script from the server into the script folder; 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 problem management number; and responding to the query request, acquiring a target modification script and a target log from the script folder, and transmitting the target modification script and the target log to the user.
6. 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 4 when the computer program is executed by the processor.
7. 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 perform the steps of the data modification method according to any one of claims 1 to 4.
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 CN111737227A (en) | 2020-10-02 |
| CN111737227B true 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) |
Families Citing this family (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| 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 |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| 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 |
Family Cites Families (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CA2660748C (en) * | 2009-03-31 | 2016-08-09 | Trapeze Software Inc. | System for aggregating data and a method for providing the same |
| US10341274B2 (en) * | 2010-12-12 | 2019-07-02 | 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 |
| US10846665B2 (en) * | 2015-11-23 | 2020-11-24 | Switch, Inc. | Management of remotely stored data |
-
2020
- 2020-06-22 CN CN202010577833.XA patent/CN111737227B/en active Active
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| 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 |
|---|
| 数据库表定义的动态修改方法;张梁斌 等;计算机工程与设计;20050828;第26卷(第8期);第2161-2163页 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111737227A (en) | 2020-10-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 | |
| CN110069572B (en) | HIVE task scheduling method, device, equipment and storage medium based on big data platform | |
| CN109086382B (en) | Data synchronization method, device, equipment and storage medium | |
| CN110688378A (en) | Migration method and system for database storage process | |
| CN108763323B (en) | Meteorological grid point file application method based on resource set and big data technology | |
| CN108255735B (en) | Associated environment testing method, electronic device and computer readable storage medium | |
| CN110688305B (en) | Test environment synchronization method, device, medium and electronic equipment | |
| CN112416957A (en) | Data increment updating method and device based on data model layer and computer equipment | |
| CN110795431B (en) | Environment monitoring data processing method, device, equipment and storage medium | |
| CN115421968A (en) | Data recovery method and electronic equipment | |
| US20160378817A1 (en) | Systems and methods of identifying data variations | |
| CN112395307A (en) | Statement execution method, statement execution device, server and storage medium | |
| WO2014055438A1 (en) | Reference data segmentation from single to multiple tables | |
| CN110866007B (en) | Information management method, system and computer equipment for big data application and table | |
| CN114443294B (en) | Big data service component deployment method, system, terminal and storage medium | |
| CN106547756B (en) | Database creation method and device | |
| CN114625751A (en) | Data tracing query method and device based on block chain | |
| CN114819631A (en) | Multitask visualization method and device, computer equipment and storage medium | |
| CN114143308A (en) | File uploading information processing method and device, computer equipment and storage medium | |
| CN113204558A (en) | Method and device for automatically updating data table structure | |
| CN112486460A (en) | Method, system, device and medium for automatically importing interface document | |
| CN116302206B (en) | Presto data source hot loading method based on MQ | |
| CN112579705A (en) | Metadata acquisition method and device, computer equipment and storage medium | |
| CN113626409B (en) | Test data processing method, device, equipment and storage medium |
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 |