Method and apparatus for data maintenance of a database

Abstract

The application discloses a kind of data maintenance method and equipment of database, the method includes: creating database automatic maintenance task, at least including one subtask, the information contained by each subtask at least includes: table name, KeepDays, KeepRowsMin, DeleteRowsMax, NextDelTime of the minimum number of rows of reservation days, maximum number of rows of single time deletion, expected next deletion time;Obtain the information contained by the subtask of the minimum expected next deletion time in database automatic maintenance task, when the expected next deletion time NextDelTime contained by this subtask is less than or equal to current system time DtNow, then according to the information contained by this subtask, execute data maintenance task to corresponding data table in database, when executing data maintenance task, the data in the data table before KeepDays is automatically deleted according to time field, guarantee the data table at least remaining KeepRowsMin row data, and single deletion cannot exceed DeleteRowsMax row data.The method of the application can complete process control system database maintenance automatically, and reduces the burden of artificial maintenance.

Description

Technical Field

[0001] This invention relates to the field of database management technology, and in particular to a data maintenance method and device for a process control system database. Background Technology

[0002] The process control system is a crucial management system in the steel industry, acting as a bridge between upstream and downstream systems. It acquires master data from the upstream manufacturing execution system (MAS), calculates production setpoints, and sends them to the downstream basic automation system for execution. During production, the process control system acquires measured process data from the downstream basic automation system, generates production data, and sends it back to the upstream MAS.

[0003] Essentially, a process control system is a system involving data exchange, management, and display. It includes master data tables, performance data tables, real-time production process data tables, log tables, process parameter configuration tables, historical process parameter tables, and so on. As the amount of data in these tables increases, it can affect the efficiency of program queries and writes. Typically, real-time data needs to be retained for at least six months, and material master data and performance data need to be retained for at least two years.

[0004] To ensure the stable and efficient operation of the system, maintenance personnel typically delete historical data periodically. However, this operation not only increases manual workload but also carries the risk of accidental errors.

[0005] Some process control systems use batch commands (.bat) to periodically delete historical data, but this method has the following problems: 1. Deleting a large amount of data may cause the database to lock temporarily, thus affecting the normal operation of the program. 2. If the unit is shut down for a long time, the data tables may be completely cleared. 3. Deleted data will not be viewable again. Summary of the Invention

[0006] The purpose of this invention is to overcome at least one defect in the prior art and to propose a data maintenance method and device for a database.

[0007] To achieve the above objectives, the present invention provides a database data maintenance method, comprising the following steps:

[0008] S1: Create an automatic database maintenance task. This automatic database maintenance task includes at least one subtask. Each subtask contains at least the following information: table name, KeepDays, minimum number of rows to retain (KeepRowsMin), maximum number of rows to delete in a single deletion (DeleteRowsMax), and expected next deletion time (NextDelTime).

[0009] S2: Obtain the information contained in the subtask with the smallest expected next deletion time in the database automatic maintenance task. If the expected next deletion time NextDelTime contained in the subtask is greater than the current system time DtNow, then re-execute step S2. If the expected next deletion time NextDelTime contained in the subtask is less than or equal to the current system time DtNow, then execute step S3.

[0010] S3: Perform a data maintenance task on the corresponding data table in the database according to the information contained in the subtask. When performing the data maintenance task, automatically delete the data in the data table that is more than KeepDays days ago according to the time field, ensuring that the data table still has at least KeepRowsMin rows of data, and that no more than DeleteRowsMax rows of data are deleted in a single deletion.

[0011] In some embodiments, establishing an automatic database maintenance task specifically includes: establishing an automatic database maintenance table, the fields of which include at least: table name, KeepDays (number of days to retain), KeepRowsMin (minimum number of rows to retain), DeleteRowsMax (maximum number of rows to delete in a single deletion), and NextDelTime (expected next deletion time).

[0012] The database automatically maintains a subtask corresponding to each row of the table.

[0013] In some embodiments, in step S2, if the expected next deletion time NextDelTime contained in the subtask is greater than the current system time DtNow, then wait for a time T0 or T1, and then re-execute step S2.

[0014] In some embodiments, T0 is equal to NextDelTime minus the total number of seconds of DtNow, rounded up to the nearest integer.

[0015] T1 is the set time.

[0016] In some embodiments, after each data maintenance task is executed in S3, the expected next deletion time NextDelTime of the subtask is updated, the waiting time is T1, and then the process proceeds to step S2.

[0017] In some embodiments, before performing the data maintenance task, the method further includes: determining whether there is a data table in the database corresponding to the table name contained in the subtask; if there is a corresponding data table in the database, then performing the data maintenance task on the data table; otherwise, updating the expected next deletion time NextDelTime of the subtask to the current system time DtNow plus T2 seconds, and waiting for T1 seconds before proceeding to step S2, where T2 is greater than T1.

[0018] In some embodiments, the time field in the data table is a single field. When performing a data maintenance task, data older than KeepDays days in the data table is automatically deleted based on the TimeFieldName field.

[0019] or,

[0020] The data table contains one or more time fields. Each subtask also includes a time field named TimeFieldName, which is one of the time fields in the data table. When performing a data maintenance task, data older than KeepDays days in the data table is automatically deleted based on this TimeFieldName.

[0021] In some embodiments, step S3 specifically includes:

[0022] S31: Obtain the total number of rows r1 in the data table where the TimeFieldName is less than deadLineTime, where deadLineTime is equal to the current system time DtNow minus the number of days to retain KeepDays. If r1 is greater than 0, proceed to step S32.

[0023] S32: Obtain the total number of rows r2 in the data table, and obtain the minimum value delTarget among the three data r1, r2-KeepRowsMin, and DeleteRowsMax. If delTarget is greater than 0, proceed to step S33.

[0024] S33: Obtain delTime, including: querying the data table in ascending order according to the time field TimeFieldName, skipping the delTarget row, and obtaining the value of the time field TimeFieldName of the first row, i.e., delTime;

[0025] S34: Delete all rows in the data table whose TimeFieldName is less than delTime, and obtain the actual number of rows deleted, delCnt. If delCnt is greater than or equal to DeleteRowsMax, then update the NextDelTime of this subtask to the current system time DtNow plus T1 seconds, and wait for T1 seconds before proceeding to step S2. If delCnt is less than DeleteRowsMax, then update the expected next deletion time NextDelTime of this subtask to the current system time DtNow plus T2 seconds, and wait for T1 seconds before proceeding to step S2, where T2 is greater than T1.

[0026] In some embodiments, after each data maintenance task is executed, if it is found that the data maintenance task has not been completed (i.e., when both r1 and r2-KeepRowsMin are greater than DeleteRowsMax), then the NextDelTime of the subtask is updated to the current system time DtNow plus T1 seconds, and the process proceeds to step S2 after waiting for T1 seconds; if it is found that the data maintenance task has been completed (i.e., when either r1 or r2-KeepRowsMin is less than or equal to DeleteRowsMax), then the expected next deletion time NextDelTime of the subtask is updated to the current system time DtNow plus T2 seconds, and the process proceeds to step S2 after waiting for T1 seconds, where T2 is greater than T1.

[0027] In some embodiments, if r1 equals 0 in step S31, the expected next deletion time NextDelTime of the subtask is updated to the current system time DtNow plus T2 seconds, and the process proceeds to step S2 after waiting for T1 seconds.

[0028] And / or,

[0029] If delTarget is less than or equal to 0 in step S32, then update the expected next deletion time NextDelTime of the subtask to the current system time DtNow plus T2 seconds, and wait for T1 seconds before proceeding to step S5.

[0030] In some embodiments, the information included in the subtask further includes: information indicating whether backup is required. The following steps are also included between steps S33 and S34: determine whether backup is required based on the information included in the subtask; if yes, perform the data backup step and proceed to step S34; if no, skip the data backup step and proceed to step S34.

[0031] The data backup process includes: obtaining all rows in the data table whose TimeFieldName is less than delTime, generating backup data, and then performing a cross-database backup.

[0032] In some embodiments, performing a data backup step specifically includes: obtaining all rows in the data table whose TimeFieldName is less than delTime, generating multiple rows of data for a backup table, and storing them in the backup table;

[0033] The backup table must include at least the following fields: unique ID, the original table's time, the original table's name, one row of the original table, and the current time of the backup.

[0034] The present invention also discloses a database data maintenance device, comprising: a memory, a processor, and a database data maintenance program stored on the memory and executable on the processor, wherein the database data maintenance program is configured to implement the database data maintenance method as described above.

[0035] The present invention has at least the following beneficial effects:

[0036] 1) This invention can automatically complete the maintenance of the process control system database, reducing the burden of manual maintenance.

[0037] 2) Before deleting a data table, this invention performs a cross-database backup. This not only prevents accidental data deletion but also provides the possibility of data backtracking later.

[0038] 3) This invention performs flow limiting during the data table deletion process, avoiding table deadlock and ensuring the normal operation of the process control system's business logic.

[0039] 4) The data table of this invention retains at least a certain amount of data to ensure that the HMI interface of the process control system is not empty. Attached Figure Description

[0040] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0041] Figure 1 A flowchart illustrating a data maintenance method for a process control system database provided in an embodiment of the present invention. Detailed Implementation

[0042] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0043] To address the problem of database bloat caused by prolonged operation of process control systems, which in turn affects operational efficiency, this invention proposes a method for maintaining historical data in a database. This method ensures that data tables contain at least a certain number of rows, avoids deadlocks by optimizing deletion operations, and backs up deleted data to improve database operational stability.

[0044] See Figure 1 This invention provides a data maintenance method for a process control system database, comprising the following steps:

[0045] S1: Create an automatic database maintenance task. This automatic database maintenance task includes at least one subtask. Each subtask contains at least the following information: table name, KeepDays, minimum number of rows to retain (KeepRowsMin), maximum number of rows to delete in a single deletion (DeleteRowsMax), and expected next deletion time (NextDelTime).

[0046] S2: Obtain the information contained in the subtask with the smallest expected next deletion time in the database automatic maintenance task. If the expected next deletion time NextDelTime contained in the subtask is greater than the current system time DtNow, wait for T0 or T1, and then re-execute step S2. If the expected next deletion time NextDelTime contained in the subtask is less than or equal to the current system time DtNow, execute step S3.

[0047] S3: Automatic data table maintenance, including performing data maintenance tasks on the data tables corresponding to the table names contained in the subtask in the database according to the information contained in the subtask. When performing data maintenance tasks, data older than KeepDays days in the data table is automatically deleted according to the time field, ensuring that the data table still has at least KeepRowsMin rows of data, and that no more than DeleteRowsMax rows of data are deleted in a single deletion.

[0048] In some embodiments, T0 is equal to NextDelTime minus the total number of seconds of DtNow, rounded up to the nearest integer.

[0049] In some embodiments, after each data maintenance task is executed in S3, the expected next deletion time NextDelTime of the subtask is updated, the waiting time is T1, and then the process proceeds to step S2.

[0050] T1 is the set time, which is usually a short time, such as 1 second.

[0051] This invention can run steps S2 and S3 periodically or periodically (e.g., at time intervals T1) to maintain the data in the database.

[0052] The present invention can be set to start running when the device is powered on, that is, to start executing steps S2 and S3, and to automatically end running the program when the device is powered off.

[0053] In some embodiments, establishing an automatic database maintenance task specifically includes: establishing an automatic database maintenance table, the fields of which include at least: table name, KeepDays (number of days to retain), KeepRowsMin (minimum number of rows to retain), DeleteRowsMax (maximum number of rows to delete in a single deletion), and NextDelTime (expected next deletion time); each row of the automatic database maintenance table corresponds to a subtask.

[0054] In some embodiments, the time field in the data table is a single field. When performing data maintenance tasks, data older than KeepDays days in the data table is automatically deleted based on the TimeFieldName field.

[0055] In other embodiments, the data table has one or more time fields, and each subtask includes information including a time field TimeFieldName, which is one of the time fields in the data table. When performing a data maintenance task, data older than KeepDays days in the data table is automatically deleted based on the time field TimeFieldName.

[0056] In some embodiments, the fields of the database automatic maintenance table include at least: table name, which is a string type and is the primary key; retention days, which is a single-precision floating-point type; minimum number of rows to retain, which is an integer type; maximum number of rows to delete, which is an integer type; time field, which is a string type; and expected next deletion time, which is a date and time type.

[0057] This automatic database maintenance table is typically created within the business database of the process control system. However, it can also be created in a separate database, or even in a text file. In one example, using a MySQL database, an automatic database maintenance table named `cfg_db_maintenance` is created. The table's field details are as follows:

[0058] TableName VARCHAR yes Table name KeepDays FLOAT Retention days KeepRowsMin INT Minimum number of rows to keep DeleteRowsMax INT Maximum number of rows to delete TimeFieldName VARCHAR Time field NextDelTime DATETIME Expected next deletion time

[0059] One line contains the following:

[0060] TableName=mill_operation_log

[0061] KeepDays=30

[0062] KeepRowsMin=100

[0063] DeleteRowsMax=1000

[0064] TimeFieldName = OperationTime

[0065] NextDelTime=2023-08-2920:28:03

[0066] It indicates that the program needs to automatically maintain the `mill_operation_log` table, automatically deleting data older than 30 days based on the `OperationTime` field. However, it must ensure that the `mill_operation_log` table has at least 100 rows remaining, and a single deletion cannot exceed 1000 rows. The next time this maintenance task will be executed is 2023-08-29 20:28:03.

[0067] In some embodiments, step S1 further includes creating a backup table, the fields of which include: a primary key field, which is guaranteed to be unique; the time of the original table, which is of type date and time; the table name of the original table, which is of type string; a row record of the original table, which is of type text; and the backup time, which is of type date and time.

[0068] The primary key field can use a long integer as an auto-incrementing ID or a string generated from a GUID. Preferably, it uses a BIGINT generated by the SnowFlake component as the primary key field to ensure its incrementability and uniqueness. Compared to GUIDs as strings, the SnowFlake component offers higher read and write efficiency, and unlike auto-incrementing IDs, it does not expose the progress of business data.

[0069] It should be noted that the backup table should not be placed in the business database of the process control system, but should be stored in a third-party database. Taking one embodiment as an example, it is stored in a local SQLite database for easy backup and management. The table name of this historical data backup table is HisDbBackup, and the table field details are as follows:

[0070] Id BIGINT yes Unique ID, generated by Snowflake DbTime DATETIME The time of the original table TableName VARCHAR The name of the original table RowJson TEXT A single line of data after JSON serialization BackupTime DATETIME Backup time

[0071] Undoubtedly, RowJson will consume a significant amount of space in the future. It is the true location for backing up historical data in the process control system's business database. This table design is highly versatile and can back up various table types.

[0072] In some embodiments, T0 is equal to NextDelTime minus the total number of seconds of DtNow, rounded up to the nearest integer.

[0073] In some embodiments, before performing the data maintenance task, the method further includes: determining whether there is a data table in the database corresponding to the table name contained in the subtask; if there is a data table in the database corresponding to the table name contained in the subtask, then performing the data maintenance task on the data table; otherwise, updating the expected next deletion time NextDelTime of the subtask to the current system time DtNow plus T2 seconds, and waiting for T1 seconds before proceeding to step S2, where T2 is greater than T1.

[0074] In some embodiments, step S3 specifically includes:

[0075] S31: Obtain the total number of rows r1 in the data table where the TimeFieldName is less than deadLineTime, where deadLineTime is equal to the current system time DtNow minus the number of days to retain KeepDays. If r1 is greater than 0, proceed to step S32.

[0076] S32: Obtain the total number of rows r2 in the data table, and obtain the minimum value delTarget among the three data r1, r2-KeepRowsMin, and DeleteRowsMax. If delTarget is greater than 0, proceed to step S33.

[0077] S33: Obtain delTime, including: querying the data table in ascending order according to the time field TimeFieldName, skipping the delTarget row, and obtaining the value of the time field TimeFieldName of the first row, i.e., delTime;

[0078] S34: Delete all rows in the data table where the TimeFieldName is less than delTime.

[0079] In some embodiments, after deleting all rows in the data table whose TimeFieldName is less than delTime in step S34, the actual number of rows deleted is obtained as delCnt. If delCnt is greater than or equal to DeleteRowsMax, the NextDelTime of the subtask is updated to the current system time DtNow plus T1 seconds. After waiting for T1 seconds, the process proceeds to step S2. If delCnt is less than DeleteRowsMax, the expected next deletion time NextDelTime of the subtask is updated to the current system time DtNow plus T2 seconds. After waiting for T1 seconds, the process proceeds to step S2, where T2 is greater than T1.

[0080] In some embodiments, after deleting all rows in the data table whose TimeFieldName is less than delTime in step S34, if it is found that the data maintenance task has not been completed (i.e., when both r1 and r2-KeepRowsMin are greater than DeleteRowsMax), then the NextDelTime of the subtask is updated to the current system time DtNow plus T1 seconds, and the process proceeds to step S2 after waiting for T1 seconds; if it is found that the data maintenance task has been completed (i.e., when either r1 or r2-KeepRowsMin is less than or equal to DeleteRowsMax), then the expected next deletion time NextDelTime of the subtask is updated to the current system time DtNow plus T2 seconds, and the process proceeds to step S2 after waiting for T1 seconds, where T2 is greater than T1.

[0081] In some embodiments, if r1 equals 0 in step S31, the expected next deletion time NextDelTime of the subtask is updated to the current system time DtNow plus T2 seconds, and the process proceeds to step S2 after waiting for T1 seconds.

[0082] In some embodiments, if delTarget is less than or equal to 0 in step S32, the expected next deletion time NextDelTime of the subtask is updated to the current system time DtNow plus T2 seconds, and then the process proceeds to step S5 after waiting for T1 seconds.

[0083] In some embodiments, the information included in the subtask further includes: information indicating whether backup is required. The following steps are also included between steps S33 and S34: determine whether backup is required based on the information included in the subtask; if yes, perform the data backup step and proceed to step S34; if no, skip the data backup step and proceed to step S34.

[0084] The data backup process includes: obtaining all rows in the data table whose TimeFieldName is less than delTime, generating backup data, and then performing a cross-database backup.

[0085] In some embodiments, performing a data backup step specifically includes: obtaining all rows in the data table whose TimeFieldName is less than delTime, generating multiple rows of data for a backup table, and storing them in the backup table;

[0086] The backup table must include at least the following fields: unique ID, the original table's time, the original table's name, one row of the original table, and the current time of the backup.

[0087] In some embodiments, when creating an automatic database maintenance table is used when creating an automatic database maintenance task in step S1, step S2 specifically includes: obtaining the row in the automatic maintenance table with the smallest expected next deletion time. This row includes at least the table name TableName, the number of days to retain KeepDays, the minimum number of rows to retain KeepRowsMin, the maximum number of rows to delete DeleteRowsMax, the time field TimeFieldName, and the expected next deletion time NextDelTime.

[0088] If NextDelTime is greater than the current system time DtNow, then sleep for T0 seconds and then re-execute step S2, where T0 represents the total number of seconds (NextDelTime-DtNow) rounded up to the nearest integer.

[0089] If NextDelTime is less than or equal to the current system time DtNow, then proceed to step S3;

[0090] Step S3 specifically includes:

[0091] Step S31: If the table TableName does not exist in the database, update the Row's NextDelTime to the current system time DtNow plus T2 seconds, and proceed to step S4; otherwise, execute step S32.

[0092] Step S32: Obtain the total number r1 of rows in TableName where the TimeFieldName is less than deadLineTime, where deadLineTime equals DtNow minus KeepDays. If r1 equals 0, update the Row's NextDelTime to DtNow plus T2 seconds and proceed to step S4. If r1 is greater than 0, execute step S33.

[0093] Step S33: Obtain the total number of rows r2 in table TableName. Then, obtain the minimum value delTarget among r1, r2 - KeepRowsMin, and DeleteRowsMax. If delTarget is less than or equal to 0, update the NextDelTime of that row to DtNow plus T2 seconds, and proceed to step S4. If delTarget is greater than 0, execute step S34.

[0094] Step S34: Obtain delTime, that is, query the TableName table in ascending order by TimeFieldName, skip the delTarget row, and select the first row whose TimeFieldName value is delTime.

[0095] Step S35, Backup Data: Obtain all rows in TableName where TimeFieldName is less than delTime, and generate multiple rows of backup table data, including unique ID, original table time (the time corresponding to the TimeFieldName of the row to be backed up in the original table), original table name, and the string after JSON serialization of the current row, back up the current time, and store this data in the backup table.

[0096] Step S36, Delete data: Delete all rows in table TableName where TimeFieldName is less than delTime, and return the actual number of rows deleted, delCnt.

[0097] Step S37: If delCnt is greater than or equal to DeleteRowsMax, update Row's NextDelTime to DtNow plus T1 seconds and proceed to step S4; otherwise, update Row's NextDelTime to DtNow plus T2 seconds and proceed to step S4.

[0098] Step S4, sleep for T1 seconds, then proceed to step S2.

[0099] T1 is a shorter time interval, such as 1 second, and T2 is a longer time interval, such as 300 seconds. Specifically, when maintenance is detected as complete, the system will pause for a longer period (T2); if maintenance is detected as incomplete, it will pause for a shorter period (T1) before resuming automatic maintenance.

[0100] It should be noted that for some tables, if the historical data is not important, backup can be skipped. In this case, an additional field needs to be added to the automatic maintenance table to indicate whether backup is required. If backup is not needed, skip step S35.

[0101] Based on the same inventive concept, embodiments of the present invention also disclose a database data maintenance device, comprising: a memory, a processor, and a database data maintenance program stored on the memory and executable on the processor, wherein the database data maintenance program is configured to implement the database data maintenance method as described above.

[0102] In some embodiments, the processor may be a central processing unit (CPU), a controller, a microcontroller, a microprocessor, or other data processing chip. The processor is typically used to control the overall operation of the electronic device. In this embodiment, the processor is used to run program code stored in the memory or to process data, such as program code for a database data maintenance method.

[0103] The memory includes at least one type of readable storage medium, including flash memory, hard disk, multimedia card, card-type memory (e.g., SD or DX memory), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic memory, disk, optical disk, etc. In some embodiments, the memory may be an internal storage unit of the electronic device, such as the hard disk or memory of the electronic device. In other embodiments, the memory may also be an external storage device of the electronic device, such as a plug-in hard disk, smart media card (SMC), secure digital (SD) card, flash card, etc. Of course, the memory may include both internal storage units and external storage devices of the electronic device. In this embodiment, the memory is typically used to store operating methods and various application software installed on the electronic device, such as program code for database data maintenance methods. Furthermore, the memory can also be used to temporarily store various types of data that have been output or will be output.

[0104] Based on the same inventive concept, embodiments of the present invention also disclose a storage medium storing a database data maintenance program, which is used to enable a processor to implement the database data maintenance method as described above when executed.

[0105] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage and optical storage) containing computer-usable program code.

[0106] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1A device that provides the functions specified in one or more boxes.

[0107] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0108] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

Claims

1. A method for maintaining data in a database, characterized in that, Includes the following steps: S1: Create an automatic database maintenance task. This automatic database maintenance task includes at least one subtask. Each subtask contains at least the following information: table name, KeepDays, minimum number of rows to retain (KeepRowsMin), maximum number of rows to delete in a single deletion (DeleteRowsMax), and expected next deletion time (NextDelTime). S2: Obtain the information contained in the subtask with the smallest expected next deletion time in the database automatic maintenance task. If the expected next deletion time NextDelTime contained in the subtask is greater than the current system time DtNow, then re-execute step S2. If the expected next deletion time NextDelTime contained in the subtask is less than or equal to the current system time DtNow, then execute step S3. S3: Perform a data maintenance task on the corresponding data table in the database according to the information contained in the subtask. When performing the data maintenance task, automatically delete the data in the data table that is more than KeepDays days ago according to the time field, ensuring that the data table still has at least KeepRowsMin rows of data, and that a single deletion does not exceed DeleteRowsMax rows of data. The data table has a single time field. When performing data maintenance tasks, data older than KeepDays days in the data table is automatically deleted based on the TimeFieldName field. or, The data table contains one or more time fields. Each subtask also includes a time field named TimeFieldName, which is one of the time fields in the data table. When performing a data maintenance task, data older than KeepDays days in the data table is automatically deleted based on the TimeFieldName. Step S3 specifically includes: S31: Obtain the total number of rows r1 in the data table where the TimeFieldName is less than deadLineTime, where deadLineTime is equal to the current system time DtNow minus the number of days to retain KeepDays. If r1 is greater than 0, proceed to step S32. S32: Obtain the total number of rows r2 in the data table, and obtain the minimum value delTarget among the three data r1, r2-KeepRowsMin, and DeleteRowsMax. If delTarget is greater than 0, proceed to step S33. S33: Obtain delTime, including: querying the data table in ascending order according to the time field TimeFieldName, skipping the delTarget row, and obtaining the value of the time field TimeFieldName of the first row, i.e., delTime; S34: Delete all rows in the data table whose TimeFieldName is less than delTime, and obtain the actual number of rows deleted, delCnt. If delCnt is greater than or equal to DeleteRowsMax, then update the NextDelTime of this subtask to the current system time DtNow plus T1 seconds, and wait for T1 seconds before proceeding to step S2. If delCnt is less than DeleteRowsMax, then update the expected next deletion time NextDelTime of this subtask to the current system time DtNow plus T2 seconds, and wait for T1 seconds before proceeding to step S2, where T2 is greater than T1.

2. The database data maintenance method as described in claim 1, characterized in that: Establish an automatic database maintenance task, which specifically includes: creating an automatic database maintenance table. The fields of the automatic database maintenance table should include at least: table name, keep days KeepDays, minimum number of rows to keep KeepRowsMin, maximum number of rows to delete in a single deletion DeleteRowsMax, and expected next deletion time NextDelTime. The database automatically maintains a subtask corresponding to each row of the table.

3. The database data maintenance method as described in claim 1, characterized in that: In step S2, if the expected next deletion time NextDelTime contained in the subtask is greater than the current system time DtNow, then wait for T0 or T1, and then re-execute step S2; T0 is equal to the total number of seconds of NextDelTime minus DtNow and rounded up to the nearest integer, and T1 is the set time; After each data maintenance task is executed in S3, the expected next deletion time NextDelTime for that subtask is updated, the waiting time is T1, and then the process proceeds to step S2.

4. The database data maintenance method as described in claim 1, characterized in that: Before executing the data maintenance task, the process also includes: determining whether there is a data table in the database that corresponds to the table name contained in the subtask; if there is a corresponding data table in the database, then the data maintenance task is executed on the data table; otherwise, the expected next deletion time NextDelTime of the subtask is updated to the current system time DtNow plus T2 seconds, and after waiting for T1 seconds, the process proceeds to step S2, where T2 is greater than T1.

5. The database data maintenance method as described in claim 1, characterized in that: If r1 equals 0 in step S31, then update the expected next deletion time NextDelTime of the subtask to the current system time DtNow plus T2 seconds, and wait for T1 seconds before proceeding to step S2. And / or, If delTarget is less than or equal to 0 in step S32, then update the expected next deletion time NextDelTime of the subtask to the current system time DtNow plus T2 seconds, and wait for T1 seconds before proceeding to step S5.

6. The database data maintenance method as described in claim 1, characterized in that: The subtask also includes information indicating whether a backup is needed. Between steps S33 and S34, the following steps are also included: determine whether a backup is needed based on the information contained in the subtask. If yes, execute the data backup step and proceed to step S34. If no, skip the data backup step and proceed to step S34. The data backup process includes: obtaining all rows in the data table whose TimeFieldName is less than delTime, generating backup data, and then performing a cross-database backup.

7. The database data maintenance method as described in claim 6, characterized in that: The data backup process includes: obtaining all rows in the data table whose TimeFieldName is less than delTime, generating multiple rows of data for the backup table, and storing them in the backup table; The backup table must include at least the following fields: unique ID, the original table's time, the original table's name, one row of the original table, and the current time of the backup.

8. A database data maintenance device, characterized in that, include: A memory, a processor, and a database data maintenance program stored on the memory and executable on the processor, the database data maintenance program being configured to implement the database data maintenance method as described in any one of claims 1 to 7.

Images