Consistency checks between database systems

Techniques and solutions are described for determining whether an update to be sent to a production database system from a quality database system will provide consistent table relationships if applied at the production database system. These consistency checks can be carried out without transmitting entire table records from the quality system to the production system, which can reduce network traffic. These consistency checks can also reduce performance impacts at the production system, as test condition generation can be carried out at the quality database system. A consistency check method can include the quality database system determining values of updated or added records of a first table that should be consistent with a second table. The test conditions cause the production database system to execute a test to determine whether the values are present in the production system. Consistency violations can be remedied before the update is applied.

FIELD

The present disclosure generally relates to analyzing relationships between data in a database. Particular implementations relate to checking whether related data is consistent.

BACKGROUND

Database systems are increasingly complex. A database schema typically includes normalized tables, a significant portion of which can be interrelated. For example, a first database table may have a field that has a foreign key relationship with a second database table. Values for the foreign key field in the first table may only be valid if such values exist in the second database table. In part because of database complexity, changes to database schemas, or values (e.g., records) stored using the schema, are often made in a quality system before being transferred to a production system. However, determining whether foreign key relationships will be appropriate in the production system can be time consuming and resource intensive. Accordingly, room for improvement exists.

SUMMARY

Techniques and solutions are described for determining whether an update to be sent to a production database system from a quality database system will provide consistent table relationships if applied at the production database system. These consistency checks can be carried out without transmitting entire table records from the quality system to the production system, which can reduce network traffic. These consistency checks can also reduce performance impacts at the production system, as test condition generation can be carried out at the quality database system. A consistency check method can include the quality database system determining values of updated or added records of a first table that should be consistent with a second table. The test conditions cause the production database system to execute a test to determine whether the values are present in the production system. Consistency violations can be remedied before the update is applied.

In one embodiment, a method is provided that can be carried out by a first database system to perform a consistency check with respect to a second database system. At least a first table updated at the first database system is determined. The first table is a constrained table. At least one field of a constraining table is determined that constrains at least a first field, a constrained field, of the at least a first table. Values of the constrained field in records of the at least a first table are determined. A plurality of test conditions are generated for at least unique values of the determined values. The plurality of test conditions are sent to the second database system. Test results are received from the second database system. An update is sent from the first database system to the second database system based at least in part on the received test results. The update includes at least a portion of the values of the at least a first table.

In another embodiment, a method is provided that can be carried out by a second database system to perform a consistency check with respect to an update associated with a first database system. A plurality of test conditions are received by the second database system from the first database system. A test condition specifies at least one constraining table, at least one constraining field of the at least one constraining table, and at least one value for the at least one constraining field. The first database system does not send with the test condition values of a constrained table of the first database system that are not used in a test condition.

The plurality of test conditions are executed to produce test results, the test results indicating whether the plurality of test conditions succeeded or failed. At least a portion of the test results are sent from the second database system to the first database system. An update is received from the first database system, where the update includes at least one update value referencing a test value associated with at least one of the plurality of test conditions (e.g., the update includes a record having a foreign key value that referenced a primary key value of the second database system, the primary key value being tested for earlier during execution of the test condition). The update is applied, including, for a table of the second database system, adding or modifying at least one table value with the update value.

In a further embodiment, a method is provided that can be carried out by a first database system and a second database system to perform a consistency check. At the first database system, at least a first table to be updated at the first database system is determined. The at least a first table is a constrained table. At least a first constraining field of a constraining table that constrains at least a first field, a constrained field, of the at least a first table is determined. A plurality of test conditions are generated, at least for unique values of the determined values. At least a portion of the test conditions are sent by the first database system to the second database system.

At the second database system, a plurality of test conditions are received from the first database system. A test condition specifies at least one constraining table, at least one constraining field of the at least one constraining table, and at least one value for that at least one constraining field. The first database system does not send with the test condition values of a constrained table of the first database system that are not used in a test condition. The plurality of test conditions are executed to produce test results, indicating whether the plurality of tests succeeded or failed. At least a portion of the test results are sent to the first database system.

At the first database system, test results are received from the second database system. One or more changes are made to an update to be sent to the second database system based at least in part on the test results. The update is sent to the second database system, where the update includes at least one update value referencing a test value associated with at least one of the plurality of test conditions. At the second database system, the update is received from the first database system. The update is applied, which includes, for a table of the second database system, adding or modifying at least one table value with the update value.

The present disclosure also includes computing systems and tangible, non-transitory computer readable storage media configured to carry out, or including instructions for carrying out, an above-described method. As described herein, a variety of other features and advantages can be incorporated into the technologies as desired.

DETAILED DESCRIPTION

Overview

Database systems are increasingly complex. A database schema typically includes normalized tables, a significant portion of which can be interrelated. For example, a first database table may have a field that has a foreign key relationship with a second database table. Values for the foreign key field in the first table may only be valid if such values exist in the second database table. In part because of database complexity, changes to database schemas, or values (e.g., records) stored using the schema, are often made in a quality system before being transferred to a production system. However, determining whether foreign key relationships will be appropriate in the production system can be time consuming and resource intensive. Accordingly, room for improvement exists.

In some cases, consistency checks for a change in a database schema, or values stored using the schema, can be performed when the changes are imported into the production system. The entire production system, or a relevant portion (e.g., tables associated with schema or value changes) can be rendered unavailable until an update, upgrade, or similar change is made. Or, at least, the updated or upgraded system is not available until the consistency checks are carried out and any identified problems are resolved.

Attempts to run consistency checks prior to importing an update from the quality system to the production system can also suffer various drawbacks. For example, update information, including schema changes and table records, can be sent from the quality system to the production system. Consistency checks can then be performed at the production system before an import process is initiated. However, this approach is still resource intensive.

Sending update data from the quality system to the production system can be resource intensive, including sending duplicate data, in some cases (e.g., sending the data once for the check and then again for the update). Or, resource use can be wasted if data is sent, problems identified, and data then resent after any problems are addressed. Computing resources are also typically used when the results of consistency checks are sent from the production system to the quality system. Running the consistency checks at the production system can be complex and time consuming, which can affect usability of the production system. For instance, calculating whether the production system includes foreign key values for all records included in an update can result in a large number of database accesses and value comparisons.

Disclosed technologies provide improved methods for conducting consistency checks. In some aspects, the disclosed technologies can analyze update information to determine whether a consistency check is satisfied within the data to be transferred. That is, for example, if a value A of a Table 1 depends on a value B of a Table 2, no consistency check needs to be performed at the production system if value B in Table 2 is included in the data to be transferred as part of the update. Regardless of whether that data may currently exist at the production system, it will be preset after the update is imported, and thus the production system will be consistent as value A of Table 1 after the import.

In another aspect, values that should be present in the production system prior to import in order for consistency checks to be satisfied can be condensed and simplified, which can reduce the amount of data sent from the quality system to the production system, the amount and complexity of processing at the production system to carry out the consistency checks, and the amount of result data returned from the production system to the quality system.

Example Change Request Between Database Systems

FIG. 1illustrates a database environment100having a quality system104and a production system106. The quality system104can be a system where changes to the production system106can be tested before being finally deployed to the production system. The production system106is typically used by end users, such as in the day-to-day operation of an entity.

Once updates have been appropriately tested in the quality system104, they can be transported to the production system106, such as in a change request108. Change requests108can have a variety of implementations, but in a specific case can be implemented as a Transport Request, such as used in products of SAP SE of Walldorf, Germany. Transport Requests, or other types of change requests, can be managed by a change service, such as the Transport Management System of SAP SE of Walldorf, Germany. In other cases, change request108need not be centrally managed. For example, the production system106can include logic for reading and implementing instructions in a change request108.

A change request108typically includes a plurality of changes. The changes may be to tables, such as table record changes120that add to, remove from, or modify one or more tables in the production system106. In some cases, the table records changes120can be to standard or configuration tables. For instance, a particular software application, such as an ERP application or another application that might be used by an enterprise (e.g., for transaction processing, for manufacturing processes, supply chain management, human resources, etc.) might have standard tables, which may also have one or more standard values. As an example, a software application may use an employee table and a work location table. As described in Example 1, tables can be interrelated and such interrelations can be used to enforce data consistency. For instance, if an employee A is listed in the employee table as being employed in work location B (e.g., “shipping”), then work location B should exist in the work location table.

In at least some cases, the relationship between tables can be a foreign key relationship. In a foreign key relationship, one or more fields of a first table reference one or more fields of a second table that serve as a primary key for the second table. Thus, a particular foreign key value in the first table may not be unique in the first table, but is typically unique in the second table. Continuing the example above, multiple employees may be assigned to the “shipping” work location in the employee table, but “shipping” is typically a unique entry in the work location table. Because of this relationship, foreign keys typically serve as constraints in a database system. An error may be generated, for instance, if a database system determines that a value in the table with the foreign key does not correspond to a primary key value in the referenced table.

As an example of how problems can arise if inconsistencies are present, consider a JOIN operation between the employee table and the work location table. If the employee included a work location (say, “front desk”), that was not present in the work location table, the JOIN would fail to retrieve results, even though a user presumably intended data to be retrieved for employees listed as working at “front desk.” In the absence of consistency checks and enforcements, erroneous results could unintentionally be provided, and data entry errors not caught (e.g., the work location table might use a different term for “front desk” and a user or process entering data would not be notified of the error absent the foreign key check).

A change request108can include one or more components. A change request108, at least for purposes of disclosed technologies, typically includes the table record changes120. Table record changes120can include adding or removing one or more tables, or making changes to an existing table. In some implementations, changing an existing table can be carried out by replacing all or a portion of a table, such as writing over a file that contains all or a portion (e.g., selected rows or columns) of a table, or opening a file and making various changes to the file contents. The updated file can then be loaded into memory for use in processing. In other cases, changes can be made to an in-memory version of a table or portion thereof, and optionally persisted.

In some aspects, changes to tables in the production system106, including tables to be added or removed, or tables to be modified, can be carried out in conjunction with a RDBMS. For example, changes can be specified in a query language, such as SQL, or a programming language that can include query language instructions or can be translated into query language instructions, such as ABAP. Query language operations can specify whether to add or drop tables, add or remove fields, add or remove rows, or to update individual row elements (e.g., values for particular fields). Information sufficient to cause a change to table records can be included in the change request108, or can be generated by the production system106in response to the change request. For instance, the production system106can analyze the change request108to determine what changes are needed, how they should be effectuated, and produce and execute suitable commands (e.g., SQL statements) to accomplish the changes.

The change request108can optionally include other information, such as schema changes124(e.g., to add or drop a table, to add or remove fields from a table, change table partitioning, or change other table metadata, such as descriptive information for a table). Configuration, or profile, information128can be included in a change request. The configuration information128can include information regarding, including identifying, one or both of the quality system104and the production system106. The configuration information128can include one or more of version information (e.g., version information of various software components of the quality system104or expected to be present in the production system106), frequency information (e.g., if the change request is recurring, and, if so, a frequency), and other system information, including system maintenance information. System information can include identifying computing nodes that should be used by the production system106, credential information, or other types of configuration information.

The change request108can include installation instructions132. Installation instructions132can, for example, specify how changes, including table record changes120and schema changes124, should be made, including an order for such changes. That is, for example, a particular table record change may depend on another table record change or a schema change, such as updating a table to include a new primary key value before updating another table to include the value as a foreign key value.

One or more files136can be included in a change request108. The files136can be new or updated software to be executed or installed on the production system106, or particular software components or plugins. Information regarding how, and when, to install the files136can be included in the installation instructions132. Change requests108can include one or more logs140. Logs140can summarize information regarding various aspects of a change request108, such as tables, or components (e.g., fields) thereof, affected by a change request. Logs140can include summaries of actions to be taken, and which can be assigned return values once the change request108is imported into the production system106. Return values can include values, such as codes, indicating that an import action was successful, was successful but warnings were issued, warnings were issued and an action was not successful, or that an error occurred in the change request108that was not associated with a specific action or instruction.

The quality system104and the production system106can include a plurality of tables150. A portion of the tables150can have identical schemas and identical values between the quality system104and the production system106. For example, Table 1,150a, has fields156,1-5, with three rows158, having the same values160between the quality system104and the production system106. In some cases, the table150acan be omitted from the change request108, as not having changed data or obvious schema changes. However, other types of schema changes, such as metadata descriptions of the table150amay originally differ between the quality system104and the production system106, until updated by the change request108.

Table 2,150b, differs between the quality system104and the production system106. Typically, a table that differs between the quality system104and the production system106refers to a table that has one or both of different metadata or different values (e.g., records, or particular values of particular records). For example, table150bhas different values in the quality system104than in the production system106, and table150chas a different number of fields in the quality system than in the production system. However, in some cases a table that differs between the systems104,106can be a table that exists in the quality system but not the production system, or which exists in the production system but not the quality system. As shown, the table150dexists in the quality system104, but is not included in the production system106. The table150dcan be a table that is to be added to the production system106by being included as table record changes120(and possibly a schema change124) of the change request108.

As an example of how inconsistencies can arise as the result of applying a change request108, a first version of the table150b, located on the quality system104, has the same fields as a second version of the table on the production system106. However, the table150bon the quality system104has a record (with Field1=“4”) that is not present in the version on the production system106. Assume that Field1of table150bis a primary key, and that Field1of the table150aof the quality system104is a foreign key that references Field1of table150b. Accordingly, it can be seen that versions of tables150aand150bare consistent in the quality system104, as the foreign key value of the table150bexists as a primary key value in the table150a. However, if only table150ais updated in the production system106, an inconsistency will arise because the foreign key value is not present as a primary key value in the table150b.

Example Data Structures

In particular embodiments, disclosed technologies can summarize table dependency information, such as foreign key relationships, in tables present in a quality system, where at least some of the table data is desired to be sent to a production system. With reference toFIG. 2A, schema data for a table204can be stored in a table208. The table208includes a column providing field names210for the various fields of table204. The table208further includes a column providing a flag212or other indicator of whether the corresponding field (e.g., in the same row or tuple) serves as a primary key, alone or in conjunction with other fields of the table204.

As shown, a value of ‘X’ (or TRUE, 1, etc.) can be used for the flag212to indicate the that field name210of the current tuple is, or is part of, a primary key for the table204. No value being present for the flag212(or a value of FALSE, 0, etc.) can indicate that the corresponding field name210is not, or is not part of, a primary key for the table204. The table208can include a column providing a constraining table indicator214, indicating whether the corresponding field210is constrained by one or more other tables, such as having a foreign key relationship, or another association (e.g., to a table field that does not serve as a primary key for that table) to another table. If a constraining table exists, the indicator214can include a value that serves as an identifier for, and can be used to access, such other table. A value not being present for the constraining table indicator214can indicate that the particular field name210associated with the current tuple is not constrained by another table.

From Table208, it can be seen that fields F1and F2are both part of a primary key for the table204, but that field F1is constrained by a table220while the field F2is constrained by a table222. Field F4is not part of a primary key of Table204, but is constrained by a table224.

In some cases, the table208identifies when a field of the table204is constrained by another table, and optionally an identifier of such table. However, the table208may not indicate which field of the constraining table is referenced by the constrained field of the constrained table. Such information can be maintained in a table230, which can maintain such information for a single table or for plurality of tables, such as all tables associated with a particular database or database schema. That is, the table230is shown as maintaining information for a plurality of tables, including tables204and tables220,222,224, but could include information for fewer tables, including for a single table. In particular implementations, the table230can be, or be part of, a data dictionary or an information schema.

Table230has a constraining table attribute232indicating the name of a table which is constrained by the constraining table of the tuple. A referenced field attribute234indicates the name of a field of the constraining table that serves to constrain a field of the constrained table. A constrained table attribute236indicates the name of the table that is constrained by the constraining table attribute232of the current tuple. A referencing field attribute238indicates the name of the field of the constrained table that is constrained by the referenced field of the constraining table.

The table230can have an “other constraints” attribute240indicating other constraints that may be placed on a particular referenced field (and thus by relation on a referencing field). For instance, referenced field234ais associated with a value of ‘001’ for the “other constraints” field240a. Thus, the value of the other constraints field240aindicates the referencing field238ais constrained to values of ‘001.’ Referencing field238ais not constrained by whether a particular value exists in referenced field, but whether a condition, which can be a composite condition that includes additional conditions for other referenced fields234, includes ‘001’ as a value in the constraining field234a. Referenced field234a, table208may be constrained to only include values for field F4where corresponding values exist in fields C1and C3of table224, and where field C2of Table224has a value of ‘001.’ Note that fields C1, C2, and C3may serve as a composite primary key for the table224, whereas tables220and222have a primary key formed from a single table attribute of their respective tables.

Other possible values of the other constraints attribute240can be a wildcard value (e.g., “*”) or rules to determine whether a value (or collection of values, such as a composite value produced from multiple fields) satisfies a rule. For instance, a rule can be to check whether a value has a correct datatype, is within a certain range, is higher or lower than a threshold value, includes or does not include certain values, or has a correct length or formatting. As an example, it may be desirable to determine that a value corresponds to a phone number, but all possible phone number may not be known. Determining that a value consists of a series of 10 integers between 0 and 9 may be a condition useable to determine that a number satisfies a rule for a phone number.

To illustrate how the tables208,230can be used, the table204is a particular instance of the table schema208is shown, having values250. An update request table254can include a table identifier256indicating records to be included in an update, and a record key identifier258that specifies particular table records (e.g., primary key values) to be included in the update. Recall that in table208fields F1and F2, corresponding to fields260a,260bof the instance204, serve as a composite primary key. Thus, the values for the record key identifier258in the table254specify that any records having a value that begins with “1000” or with “1001” will be included in the update request.

Results of applying the constraints of the table230to the values250of the instance204are shown in Table272ofFIG. 2B. Table272includes a constrained table identifier attribute274identifying a constrained table, a record key identifier attribute276associated with the particular tuple (and, in the case of an update request, which corresponds to the records to be included in the update request as specified in the table254ofFIG. 2A, a field name identifier attribute278, corresponding to a constrained field of the instance204, a field value attribute280associated with the field name attribute278for the particular tuple taken from the instance204, a constraining table identifier attribute282identifying a particular table that serves to constrain the field associated with the particular tuple and the constrained table identified by the constrained table attribute274, and a condition attribute284to be satisfied for the respective tuple. A value of the condition attribute284can be constructed from the constraints of the table230and the values250of the instance204.

All or a portion of the table272can be sent to a production system, such as from a quality system, to determine whether the conditions284are satisfied prior to taking further steps to send and apply an update request to the production system. For instance, the conditions284alone can be sufficient for consistency checks to be conducted, where results can be recorded, at least a portion of the results sent from the production system to the quality system, and the results associated with particular conditions284. Sending at least the conditions284can be advantageous compared with prior approaches for carrying out consistency checks associated with an update. For example, sending the conditions284can result in less data being transferred between the quality and production systems, as entire records need not be sent between the quality and production systems. That is, while the conditions250include at least some of the values250, they do not include all of the values, such as values for fields210that are not associated with a constraint. Thus, even for the simple example ofFIG. 2, less computing resources are needed to transfer information between the quality and production systems. In actual database systems, an instance of a table may have tens or hundreds of fields, and may have thousands or millions of rows, and thus the disclosed technologies can realize significant computer resource savings.

The conditions284can also be easier for the production system to test, thus resulting in less computer resource use at the production system, and less logic being needed. That is, the production system need only test or execute a given condition; it need not determine the condition. Accordingly, update processes of the present disclosure can interfere less with normal operation of the production system, reducing any performance impact on end users.

Steps can be taken to further reduce the resources used by disclosed conditions284. For instance, it can be seen that, while individual tuples of the table272are unique, all of the conditions284are not. For instance, conditions284aand284bare identical, as are conditions284c-284e. Conditions284fand284iare identical, as are conditions284gand284j. Conditions284kand284lrepresent a single unique condition, as do conditions284m-284o. As long as any of a set of identical conditions284is satisfied, then all members of the set will be satisfied, regardless of other differences that may exist between tuples of the table272or the tuples of the instance204.

Accordingly, disclosed technologies can include a duplicate constraint or condition removal process. After removing duplicate constrains or conditions, and information not needed for evaluating consistency, the table290ofFIG. 2Ccan be produced. The table290includes a column that lists values for constraining table identifiers292and a column that includes conditions294to be tested. The table290can be sent to a production system and the conditions294evaluated.

Although the conditions294in table290, and in other tables ofFIGS. 2A-2C, are shown as particular field identifiers, particular values, and particular operators (e.g., “=”), the conditions can be specified in another manner, and can be included in a table, such as the table290, or maintained in another format. In a particular example, the conditions294can be expressed in a query language (or another language, such as ABAP, in which query language operations can also be specified), such as a SQL SELECT statement for the relevant constraining table292, field name, and field value. Taking condition294aas an example, a SELECT statement can be “SELECT * FROM220WHERE A1=‘1000’;”. If results are returned, then the condition can be indicated as satisfied, whereas no results can indicate that the condition is not satisfied (and thus an inconsistency may exist if the update is sent to the target system). As shown, a condition294with multiple constraints can include operators, such as Boolean operators, for the constraints, but can also can include other mathematical or logical operators or comparators (e.g., <, >, <=, >=, =, and the like).

However, other types of query language statements can be used. Another example query language statement is, in ABAP:

Results can be returned from the production system to the quality system, such as in the form of the table296, which can be identical to the table290except that the results table also includes a column for storing consistency check results298(e.g., TRUE or FALSE, YES or NO, “X” or “ ”, etc.).

The use of the table290can thus even further reduce computing resource use, as less data need be sent between a quality system and a production system, and fewer queries and calculations need be executed by the production system in evaluating consistency conditions.

Example Consistent Check Operations

FIG. 3is a flowchart depicting operations in a method300for performing consistency checks before an update is sent, or applied, to a database system. The method300can be implemented in the database environment100ofFIG. 1, and can include the data structures (e.g., tables) depicted inFIGS. 2A-2C.

The consistency check beings at304. In some cases, a consistency check beginning at304can include receiving an identifier of an update request to be evaluated, where the update request includes records from one or more tables to be sent from a source system (e.g., a quality system) to a target system (e.g., a production system). In some cases, the method300can be implemented in conjunction with an API, where a call to the API includes an identifier of an update request for which consistency checks are to be performed.

At308, table keys to be evaluated are retrieved. For instance, the table keys can be retrieved from an update specification, such as from a table having the form, or the information contained in, table254ofFIG. 2A. The table keys retrieved at308thus specify what tables are involved in a consistency check, as well as particular rows or tuples of such tables, and thus particular values that should be checked for consistency.

At312, it is determined if additional tables remain to be analyzed in the consistency check. If additional tables remain, the method300proceeds to316, and, if not, to344, as will be further described. For a selected table of the remaining tables to be analyzed, table metadata and metadata values are read at316, such as from a data dictionary or information schema. Reading metadata and metadata values at316can include determining whether the table has any constrained fields, and, if so, determining one or more conditions associated with the constrained field. Reading metadata and values at316can include reading information from a table having a form similar to the table208or the table230ofFIG. 2A.

At320, it is determined if any constrained fields remain to be analyzed of the table currently being analyzed. If not, the method300can return to312. Otherwise, the method300proceeds to324, where it is determined whether, for a current field being analyzed, any records remain to be analyzed. If no records remain to be analyzed for the current field, the method300can return to320. Otherwise, the method300gets a next record and proceeds to carry out a consistency check for the record at328. Carrying out the consistency check at328can include determining whether a constraining field of a constraining table, or another constraint or condition, that constrains the field currently being analyzed has a value consistent with a value of the record currently being analyzed. In a particular implementation, carrying out the consistency check can include executing a SQL SELECT statement for the value in the table being analyzed, such as SELECT * FROM CONSTRAINING_TABLE WHERE CONSTRAINING_FIELD=‘Value’.

A result of the consistency check, which can include an identifier of the constraining table and a value associated with a constraining field of the constraining table, can be collected at332. For example, it can be determined whether a SELECT statement executed at328returns one or more records. At336, it is determined whether the target record (e.g., a tuple of the constraining table having the value required for the consistency check) is present in an update request. If the target record is not present, a condition is collected at340, and the method300can return to324. The collected condition can have the form of a row of table272ofFIG. 2B. If the target record is determined at336to be present, no additional action needs to be taken, as, even if a target system (e.g., a production system) does not currently include a value needed for consistency, the target system will include such value after the update is processed. In such case, the method300can proceed from336to324.

After it is determined at312that all tables (at least those having one or more constraints) have been processed, the method proceeds to344, where duplicates can be removed from the collected check conditions.344can also include simplifying the check conditions, such as by summarizing information for a check condition as a constraining table and one or more field values for a constraining field of the constraining table. The result of the processing at344can be a table having the form of table290ofFIG. 2C, which table can be stored at348. The conditions stored at348(or the set of conditions collected at340) can be considered as intermediate results, and can represent processing that can be performed by the source system (e.g., the quality system) that need not be carried out by the target system (e.g., the production system), in addition to being in a form that requires less computing resources to send and process.

A consistency check request can be generated at352and sent to a target system in a communication356. The consistency check request can include consistency checks to be carried out, including in the form of the table290or the table296ofFIG. 2C. The consistency checks are executed in the target system at360. Execution of the consistency check at360can include storing an indication of whether the check was successful, such as by placing an entry in a table having the form of table296ofFIG. 2C. Consistency results can be sent from the target system to the source system (e.g., a quality system) in a communication364.

The consistency results are evaluated at368. Evaluating the consistency results at368can include identifying consistency check failures, and determining table, or records thereof (or individual attribute values thereof), that should be sent to the target system in order for the target system to be consistent after an update is applied. The records can be collected at372. In other aspects, records (or missing values) can be identified and sent to the target system to be created. That is, in some cases, permissions or other types of configuration settings or considerations may be such that data should be created or modified directly at the target system, rather than being included in an update.

As an example, the update may primarily update standard tables associated with an application, while the inconsistency may result from tables that are associated with particular end users. In some cases, an administrator of the production system causes the necessary data to be generated, or changed, in a target system. In other cases, a call, such as a remote function call or API, can be made by the source system to the target system to make appropriate changes to the target system before the update is sent. In yet further examples, rather than creating data in a constraining table, values of the constrained table can be modified to be consistent with the constraining table. For instance, if an employee is attempted to be assigned to a department that does not exist, the department can either be created in the constraining table, or the employee can be assigned to a department that is present in the constraining table. In some cases, a user can be presented with suggested or available options for changing a value in a record of a constrained table to be consistent with a constraining table, including through the use of machine learning or semantic analysis.

A log can be generated, and optionally stored, at376. The log can include a table having the form of table296ofFIG. 2C, or otherwise include information regarding at least failed consistency checks. When actions are automatically taken to fix a failed consistency check, such as collecting records at372, such actions can also be included in a log. The consistency check method300can end at380.

It should be appreciated that changes can be made to the method300without departing from the scope of the present disclosure. For example, one or more of evaluating consistency results at368, collecting records at372, or generating a log at376can be omitted. Although consistency checks are shown as being carried out on a record-by-record and field-by-field basis based on the determining at320and at324, in other cases, consistency checks can be run at328for all fields of a particular row, or for all implicated fields and rows for a particular table. Consistency checks for particular tables, fields, or records can also be parallelized, if desired.

In the discussion of328,332is assumed that the consistency check is satisfied for the source (e.g., quality) system. That is, typically, prior to304, consistency checks are executed in the source system, and any corrections are made prior to determining whether sending the update to a target system will cause inconsistencies. However, steps can be included in the method300to account for situations where consistency checks were not previously executed in the source system, or where data in the source system may have changed since a consistency check was carried out. For instance, if a consistency result332is that a condition being tested is not satisfied at the source system, the consistency failure can be logged, and flagged for review. After the inconsistency has been addressed (e.g., by adding a value to a constraining table corresponding to a foreign key value or by changing a foreign key value of a constrained table to a value that is present in a constraining table), the consistency check for the particular record can be re-executed and the method300can proceed to336. Note that processing of records that initially failed a consistency check at332can be processed without having to reprocess all of the records in an update, in at least some implementations of disclosed technologies.

In some cases, the operations328,332can be omitted. In some cases, the operations328,332can represent a consistency check at the source system. Such checks need not always be carried out, including if consistency checks at the source system were already carried out prior to300, or if consistency checks are otherwise not needed or desired. In such an implementation, values associated with the records analyzed at324can analyzed to see if they are in the update, and collected as conditions at340if not.

Note that while the present disclosure generally describes determining whether an update to a constrained table will produce a consistency violation, the disclosed techniques can be applied in a similar manner to determine whether changes to a constraining table will result in violations when applied to a target database system. For instance, an update may alter a table that is referenced by one or more tables through a foreign key relationship or other association. If the update removes a value from the constraining table that is present in a constrained table of the target system, a consistency violation can occur. Such conditions can be tested in a similar manner as for changes to constrained tables, such as by generating a test condition to determine whether a removed value exists at the target system. The test condition can include an identifier of one or more constrained tables, identifiers of one or more constrained fields, and values to be checked. If a value is present in the target database system, a consistency violation can be indicated. In a similar manner as336, if values of the constrained tables are removed in the update, the test condition need not be generated.

Example Embodiments

FIG. 4is a flowchart of an example method400of operations that can be carried out by a first database system to perform a consistency check with respect to a second database system. The method400can be implemented using the database environment100ofFIG. 1, and can include data structures shown inFIGS. 2A-2C. At404, at least a first table updated at the first database system is determined. The first table is a constrained table. At least one field of a constraining table is determined, at408, that constrains at least a first field, a constrained field, of the at least a first table.

At412, values of the constrained field in records of the at least a first table are determined. A plurality of test conditions are generated at416, at least for unique values of the determined values. At420, the plurality of test conditions are sent to the second database system. Test results are received from the second database system at424. At428, an update is sent from the first database system to the second database system based at least in part on the received test results. The update includes at least a portion of values of the at least a first table.

FIG. 5is a flowchart of an example method500of operations that can be carried out by a second database system to perform a consistency check with respect to an update associated with a first database system. The method500can be implemented using the database environment100ofFIG. 1, and can include data structures shown inFIGS. 2A-2C. At504, a plurality of test conditions are received by the second database system from the first database system. A test condition specifies at least one constraining table, at least one constraining field of the at least one constraining table, and at least one value for the at least one constraining field. The first database system does not send with the test condition values of a constrained table of the first database system that are not used in a test condition.

The plurality of test conditions are executed at508to produce test results, the test results indicating whether the plurality of test conditions succeeded or failed. At least a portion of the test results are sent from the second database system to the first database system at512. At516, an update is received from the first database system, where the update includes at least one update value referencing a test value associated with at least one of the plurality of test conditions (e.g., the update includes a record having a foreign key value that references a primary key value of the second database system, the primary key value being tested for earlier, during execution of the test condition). The update is applied at520, including, for a table of the second database system, adding or modifying at least one table value with the update value.

FIG. 6is a flowchart of an example method600of operations that can be carried out by a first database system and a second database system to perform a consistency check. The method600can be implemented using the database environment100ofFIG. 1, and can include data structures shown inFIGS. 2A-2C. At the first database system, at604, at least a first table to be updated at the first database system is determined. The at least a first table is a constrained table. At608, at least a first constraining field of a constraining table that constrains at least a first field, a constrained field, of the at least a first table is determined. A plurality of test conditions are generated at612, at least for unique values of the determined values. At least a portion of the test conditions are sent by the first database system to the second database system at616.

At the second database system, a plurality of test conditions are received from the first database system at620. A test condition specifies at least one constraining table, at least one constraining field of the at least one constraining table, and at least one value for that at least one constraining field. The first database system does not send with the test condition values of a constrained table of the first database system that are not used in a test condition. At624, the plurality of test conditions are executed to produce test results, indicating whether the plurality of tests succeeded or failed. At least a portion of the test results are sent to the first database system at628.

At the first database system, test results are received from the second database system at632. At636, one or more changes are made to an update to be sent to the second database system based at least in part on the test results. The update is sent to the second database system at640, where the update includes at least one update value referencing a test value associated with at least one of the plurality of test conditions.

At the second database system, at646, the update is received from the first database system. The update is applied at650, which includes, for a table of the second database system, adding or modifying at least one table value with the update value.

Computing Systems

FIG. 7depicts a generalized example of a suitable computing system700in which the described innovations may be implemented. The computing system700is not intended to suggest any limitation as to scope of use or functionality of the present disclosure, as the innovations may be implemented in diverse general-purpose or special-purpose computing systems.

With reference toFIG. 7, the computing system700includes one or more processing units710,715and memory720,725. InFIG. 7, this basic configuration730is included within a dashed line. The processing units710,715execute computer-executable instructions, such as for implementing components of the architecture100ofFIG. 1, including as described in Examples 1-5. A processing unit can be a general-purpose central processing unit (CPU), processor in an application-specific integrated circuit (ASIC), or any other type of processor. In a multi-processing system, multiple processing units execute computer-executable instructions to increase processing power. For example,FIG. 7shows a central processing unit710as well as a graphics processing unit or co-processing unit715. The tangible memory720,725may be volatile memory (e.g., registers, cache, RAM), non-volatile memory (e.g., ROM, EEPROM, flash memory, etc.), or some combination of the two, accessible by the processing unit(s)710,715. The memory720,725stores software780implementing one or more innovations described herein, in the form of computer-executable instructions suitable for execution by the processing unit(s)710,715.

A computing system700may have additional features. For example, the computing system700includes storage740, one or more input devices750, one or more output devices760, and one or more communication connections770. An interconnection mechanism (not shown) such as a bus, controller, or network interconnects the components of the computing system700. Typically, operating system software (not shown) provides an operating environment for other software executing in the computing system700, and coordinates activities of the components of the computing system700.

The tangible storage740may be removable or non-removable, and includes magnetic disks, magnetic tapes or cassettes, CD-ROMs, DVDs, or any other medium which can be used to store information in a non-transitory way and which can be accessed within the computing system700. The storage740stores instructions for the software780implementing one or more innovations described herein.

The input device(s)750may be a touch input device such as a keyboard, mouse, pen, or trackball, a voice input device, a scanning device, or another device that provides input to the computing system700. The output device(s)760may be a display, printer, speaker, CD-writer, or another device that provides output from the computing system700.

In various examples described herein, a module (e.g., component or engine) can be “coded” to perform certain operations or provide certain functionality, indicating that computer-executable instructions for the module can be executed to perform such operations, cause such operations to be performed, or to otherwise provide such functionality. Although functionality described with respect to a software component, module, or engine can be carried out as a discrete software unit (e.g., program, function, class method), it need not be implemented as a discrete unit. That is, the functionality can be incorporated into a larger or more general purpose program, such as one or more lines of code in a larger or general purpose program.

Cloud Computing Environment

FIG. 8depicts an example cloud computing environment800in which the described technologies can be implemented. The cloud computing environment800comprises cloud computing services810. The cloud computing services810can comprise various types of cloud computing resources, such as computer servers, data storage repositories, networking resources, etc. The cloud computing services810can be centrally located (e.g., provided by a data center of a business or organization) or distributed (e.g., provided by various computing resources located at different locations, such as different data centers and/or located in different cities or countries).

The cloud computing services810are utilized by various types of computing devices (e.g., client computing devices), such as computing devices820,822, and824. For example, the computing devices (e.g.,820,822, and824) can be computers (e.g., desktop or laptop computers), mobile devices (e.g., tablet computers or smart phones), or other types of computing devices. For example, the computing devices (e.g.,820,822, and882) can utilize the cloud computing services810to perform computing operators (e.g., data processing, data storage, and the like).

Implementations

The disclosed technology is not limited to any specific computer language or program. For instance, the disclosed technology can be implemented by software written in C, C++, C#, Java, Perl, JavaScript, Python, Ruby, ABAP, SQL, XCode, GO, Adobe Flash, or any other suitable programming language, or, in some examples, markup languages such as html or XML, or combinations of suitable programming languages and markup languages. Likewise, the disclosed technology is not limited to any particular computer or type of hardware.