Disclosure of Invention
The technical problem to be solved by the invention is to overcome the defect of the field information management of the existing database and provide a method for automatically checking out-of-range of enumerated field values.
In order to solve the technical problems, the invention provides a method for automatically checking the value of an enumeration type field beyond the boundary, which is characterized by automatically checking the value of the enumeration type field in a current database according to rules agreed in remark information of the enumeration type field related to the database, so as to find out the value of the enumeration type field beyond the boundary abnormality and alarm, and specifically comprises the following steps:
step 1: defining a constraint value range and a field type checking object aiming at an enumeration type field in a service system;
step 2: converting the checking object into a character string description of a specific grammar structure;
Step 3: writing the character string description of the checking object into remark information of corresponding enumeration type fields in corresponding tables of the corresponding database;
Step 4: periodically reading the information of the checking object in the remark information of the current database table field through a custom timing scheduling task, comparing and checking the value information of the current database table field with the corresponding information of the checking object to judge whether the current database table field meets the preset condition or not, and judging that the current database table field is out of limit if the current database table field is not met;
step 5: when the border crossing is judged in the step 4, the warning related dry system person submits a new check object definition to check the inconsistent reason, and updates the new check object definition into the corresponding remark field of the database through the database manager;
Step 6: the checking of the timed scheduled task of step 4 is continued until the timed scheduled task is terminated.
The specific steps of the step 1 comprise:
step 1-1: defining a value range attribute in the checking object as FV, specifically, defining a value range of enumerated values as { V1…Vm } according to the selected enumerated fields;
Step 1-2: defining a logical meaning attribute in the checking object as FD, specifically defining a logical meaning description of an enumeration value as { D1…Dm }, according to the selected enumeration type field;
step 1-3: defining an inspection level attribute in the inspection object as CL, specifically, when CL is 0, the inspection object is not required to be inspected and notified at regular time; when CL is 1, the object to be inspected needs to be inspected at regular time, notification is not performed, and the inspection result is recorded in a log; when CL is 2, the object to be inspected needs to be checked and notified at regular time;
Step 1-4: defining a value type attribute in the checking object as FT, specifically defining the value type as follows according to the selected enumeration type field: floating point is 0, shaping is 1, character string or character type is 2;
step 1-5: defining a logical name attribute in the checking object as FL, specifically defining the logical name as FL according to the selected enumeration field, wherein the length n of the logical name is not more than 99 and is the minimum 00;
the specific steps of the step 2 include:
Step 2-1: for all the enumerated fields { F1…Fn } that need to be defined, a check object { CRO1…CROn } is constructed, and the attribute of each check object contains: value range, logical meaning, inspection level, value type, logical name.
Step 2-2: constructing an ordered empty string;
step 2-3: the value of the 1 st bit character of the ordered empty character string takes the inspection level attribute of the inspection object;
Step 2-4: the value of the 2 nd character of the ordered empty character string takes the value type attribute of the checked object;
step 2-5: the values of the 3 rd bit character and the 4 th bit character of the ordered empty character string are the character string length n of the logical name attribute of the checking object, and the values of the 5 th to n+3 th bit characters of the ordered empty character string are the actual character values of the logical name attribute;
Step 2-6: starting with the n+4th bit character of the ordered empty string, its value starts with "[", with "]; "end, { V1…Vm } and { D1…Dm } of logical meaning in the value range constitute [ Vi,Di ], i.epsilon {1 … m } key value pair.
In the step 4, the preset condition is that, for the value range set agreed in the checking object information and the value range set of the current enumeration value of the enumeration field, the value range set agreed in the checking object information must be completely included in the value range set of the current enumeration value of the enumeration field.
The specific steps of the step 4 include:
step 4-1: the self-defined timing scheduling task program is used for configuring a timing strategy for timing scheduling departure;
Step 4-2: when a timing strategy condition is triggered, the timing scheduling task program reads all enumeration fields with data writing behaviors in a timing range to form a { F1…Fm } set;
step 4-3: traversing all enumeration type field sets with data writing behaviors, and judging remark information of the enumeration type fields one by one, wherein the remark information comprises characters with {0,1,2} beginning and ending as "]; "character string, the enumeration field contains checking object character string remark information CRO, and adds the CRO object into { CRO1…CROn };
step 4-4: reading all the inspection objects { CRO1…CROn }, inspecting the first character of each inspection object one by one, and judging the inspection level attribute according to the definition rule of the inspection level attribute in the step 1-3;
Step 4-5: when the inspection level attribute is cl=0, the enumeration type field is not inspected, when the inspection level attribute is cl=1 or cl=2, the enumeration value of the enumeration type field is read, group grouping is carried out to obtain { V '1…V′k }, { V'1…V′k } is compared with { V1…Vm } in remark information of the enumeration type field, and if V 'i∈{V′1…V′k } is inspected but V'i∈{V1…Vm is not present, it can be determined that the enumeration value of the enumeration type field is out of range;
step 4-6: if the attribute of the checking level is cl=1, the checking time, the table name of the table where the enumerated field is located, the field name of the table where the enumerated field is located, and the field value out-of-range set are recorded in the system log file, where the field value out-of-range set is a set of V'i of the enumerated field found by the previous step and not in the value range { V1…Vm }.
The invention has the beneficial effects that:
(1) The usage scenario of database table field notes is extended from a general simple annotation to a rule definition with interpretability.
(2) The invention considers the scene especially suitable for small and medium-sized software development team, saves the document synchronization cost of the software development operation and maintenance, and can take effect for a long time based on one database configuration.
(3) The invention fully utilizes the long-term and relative stability characteristics of the database, namely, the application system is iterated and version improved anyway, but the table field structure based on the database is a relatively long-term and stable structure, and the later iteration and upgrading of the system can consider the compatibility and the DDL information of the current service database. The problems of lost and asynchronous document flows in different periods and among a plurality of systems are skillfully avoided.
(4) The enumeration type field commonly used in the design of the general service application system can be fully automatically executed by the timing software once the out-of-range of the field is well maintained according to the rule setting and the data structure, manual intervention is not needed, the operation and maintenance labor cost is saved, and the service system has certain automatic maintenance characteristics. And the pollution of system data caused by manual errors is avoided.
(5) The invention creatively provides the description of the automatic checking rule of the field by utilizing the serialization structure storage of the character string, the checking rule supports various types of expansion, the key value structure of the value and meaning is reserved, the visual interpretability of the meaning of the checking rule is realized, the checking rule is convenient to understand and maintain, the new addition and modification of the character string can be completed through the traditional database operation and maintenance tool, the checking rule is convenient and quick, and the practical value is high. The method has no invasive influence on the original system code structure, has wide applicability, and can be used for automatically checking and managing out-of-range enumeration fields for future service systems and existing service systems.
Detailed Description
The invention is further described with reference to fig. 1:
The method for automatically checking the value of the enumerated field out of range by using the remark information of the database field as shown in fig. 1 automatically checks the value of the field in the current database according to the rule agreed in the remark information of the related enumerated field of the database to find out the value out of range abnormality of the enumerated field, so that a database manager can find out the data abnormality in time to remind a system development team of repairing the problem that the constraint of the field is inconsistent with the actual value. It is mentioned here that the database table field contains an enumeration type field, but not all, for example, when the method monitors a database field with data writing variation (which may include various types of database fields, such as storing general text type, numerical type fields), and the value actually stored in the enumeration type field is also text type or numerical type, the enumeration type field refers to a service characteristic of a field in the database table, and the service characteristic is expressed as that the value of the field is enumerated, for example, type, sex, class.
The method specifically comprises the following steps:
Step 1: by means of a specific format, for a specific service system enumeration type field, a constraint value set and a field type check object CheckRuleObject (CRO for short) are defined, and the following steps are implemented in detail according to examples;
Step 1-1: for example, there are several fields in the order service table T, T in the current database, in which there is an order status field F as an enumeration field, a value range attribute FV of the CRO object corresponding to the field F is defined, an enumeration value set is defined as { '0', '1', '2', },
Step 1-2: defining a logical meaning attribute FD in the CRO object of field F, the logical meaning description set of enumeration values corresponding to FV attribute definitions being { 'unpaid', 'not shipped', 'shipped' };
Step 1-3: defining an inspection level attribute CL in the CRO object of the field F, wherein when CL is 0, it indicates that timing inspection is not required, when CL is 1, it indicates that timing inspection is required, but only the log is required to record inspection results, and when CL is 2, it indicates that timing inspection and notification are required, where for example, when field F is required to perform timing and notification, the CL attribute value of field F is 2;
step 1-4: a field value type attribute FT in the CRO object defining field F, whose value type definition may be selected as floating point type 0, shape 1, character string (including character type) 2, which is defined herein as shape by way of example, the FT attribute value of field F is 1;
step 1-5: a field logical name attribute FL in the CRO object defining field F, whose logical name is "order type" and whose length is 4 characters;
Step 2: converting the CRO object of the field F into a character string description of a specific grammar structure;
Step 2-1: constructing an ordered empty string S;
Step 2-2: the 1 st bit character S1 of S takes the inspection level attribute CL of the CRO object, and s= "2" at this time according to an example;
Step 2-3: the 2 nd character S2 of the S takes the field value type attribute FT of the CRO object, and S= "21" is taken as an example at the moment;
Step 2-4: the 3 rd bit character S3 and the 4 th bit character S4 of S are the string length n of the field logical name attribute FL of the CRO object, according to example, where n=4, where s= "2104 order type";
Step 2-5: the n+4th bit character Sn+4 of S starts with "[" starts with "]; "end, middle is composed with { V1…Vm } of FV and { D1…Dm } of FD [ Vi,Di ], key value pair of i ε {1 … m }, according to example, where n=4, m=3, S=" 2104 order type [0, unpaid ]; [1, not shipped ]; [2, shipped ]; ".
Step 3: writing S into remark information of a corresponding field F in a corresponding database table T;
Step 4: periodically reading CRO information in all remark information of a current database table field and value information of the current database table field through a custom timing scheduling task, comparing and checking whether the field is in line or not, and judging that the field is out of line if the field is not in line;
Step 4-1: a custom timing schedule task program that can configure the timing strategy of the timing schedule departure, for example, defined at this time to be executed once every 24 hours;
Step 4-2: every 24 hours, the program reads all the change modification values in the current target database within the period of time, the read information comprises a changed table name, a changed field, a value before the field and a value after the field is changed, if the order table T is newly written with data, an order type field F in one piece of data is written with the value of 3 by way of example, the database change information is expressed as { table: "T", field: "F", before_value: NULL, current_value: "3" };
Step 4-3: reading remark information of a field F with data change according to the data change information in the step 4-2;
Step 4-4: reading remark information of field F as "2104 order type [0, unpaid ]; [1, not shipped ]; [2, shipped ]; by the remark CRO object string format already agreed in the previous step 1, the field F can be judged to be an enumerated field. ;
step 4-5: reading the first character in the character string to obtain a check level cl=2, which means that the field F needs to be checked, and grouping all values of the field F in the current table T to obtain { '0', '1', '3', }, and according to the remark information character string s= "2104 order type [0, unpaid ]; [1, not shipped ]; [2, shipped ]; the value range of the field F in the table T is determined to be limited in the { '0', '1', '2' } set, because { '3' } does not belong to the subset of { '0', '1', '2' }, because the field F has a value out of range;
step 4-6: at this time, if CL is 2 according to the CRO object, the checking time and the corresponding table name, field name, and field value are recorded in the system log file;
step 5: because cl=2, determining that the boundary is exceeded, notifying the data manager and system development team related personnel in the form of a mail short message or the like, and if cl=0, skipping step 5;
Step 6: the relevant dry system person checks the inconsistent reasons, submits a new CRO definition of the field F by a system development team, updates the CRO definition into a corresponding remark field of the database by a database administrator, and changes remark information of the field F to 2104[0, unpaid ] at the moment under the assumption that check constraint support for the value should be added in CRO expression of the field F; [1, not shipped ]; [2 shipped, [3 returned ]; ", thereby realizing the update of the CRO object of the new version of the field F and enabling a database manager to know the service change condition of the table T in time;
Step 7: the timed scheduled task check of step4 continues until the timed task is terminated. The period continues to check whether a new value write or modification of field F has occurred, according to the timed task period. If the automatic out-of-range check terminates the operation, the automatic out-of-range check ends. Therefore, automatic inspection is realized, manual intervention is not needed, and the timing time can be set differently according to the characteristics of the project.
Compared with the prior art, the invention overcomes the defect of the field information management of the existing database, and provides a method for automatically checking out-of-range enumeration field values by using the remark information of the database field, which is used for finding out-of-range enumeration field values and abnormal, so that a database manager can find out data abnormality in time and remind a system development team of repairing field constraint and the problem of inconsistent actual values. The beneficial effects achieved are as follows:
(1) The usage scenario of database table field notes is extended from a general simple annotation to a rule definition with interpretability.
(2) The invention considers the scene especially suitable for small and medium-sized software development team, saves the document synchronization cost of the software development operation and maintenance, and can take effect for a long time based on one database configuration.
(3) The invention fully utilizes the long-term and relative stability characteristics of the database, namely, the application system is iterated and version improved anyway, but the table field structure based on the database is a relatively long-term and stable structure, and the later iteration and upgrading of the system can consider the compatibility and the DDL information of the current service database. The problems of lost and asynchronous document flows in different periods and among a plurality of systems are skillfully avoided.
(4) The enumeration type field commonly used in the design of the general service application system can be fully automatically executed by the timing software once the out-of-range of the field is well maintained according to the rule setting and the data structure, manual intervention is not needed, the operation and maintenance labor cost is saved, and the service system has certain automatic maintenance characteristics. And the pollution of system data caused by manual errors is avoided.
(5) The invention creatively provides the description of the automatic checking rule of the field by utilizing the serialization structure storage of the character string, the checking rule supports various types of expansion, the key value structure of the value and meaning is reserved, the visual interpretability of the meaning of the checking rule is realized, the checking rule is convenient to understand and maintain, the new addition and modification of the character string can be completed through the traditional database operation and maintenance tool, the checking rule is convenient and quick, and the practical value is high. The method has no invasive influence on the original system code structure, has wide applicability, and can be used for automatically checking and managing out-of-range enumeration fields for future service systems and existing service systems.
The above embodiments are not intended to limit the present invention in any way, and all other modifications and applications of the above embodiments which are equivalent to the above embodiments fall within the scope of the present invention.