Unique attribute constraints for versioned database objects

• US 8,812,539 B2
• Filed: 04/23/2012
• Issued: 08/19/2014
• Est. Priority Date: 03/31/2012
• Status: Active Grant

• Current Assignee: BMC Software Incorporated (KKR & Co., Inc.)
• Original Assignee: BMC Software Incorporated (KKR & Co., Inc.)
• Inventors: Milousheff, Zack, Hutchins, Mark
• Primary Examiner(s): Perveen, Rehana
• Assistant Examiner(s): HAVAN, HUNG T
• Application Number:
  US13/453,628
• Publication Number:
  US 20130262403A1
• Time in Patent Office: 848 Days
• Field of Search: None
• US Class Current: 707/769
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity

Abstract

Methods and apparatus for ensuring uniqueness of database object attributes are disclosed. An example computer-implemented method includes receiving a request to insert, update or delete a versioned database object having a first identifier (ID) in a main database table. The method further includes determining, based on the request, whether to fire an insert trigger, a delete trigger or an update trigger for the main database table. In the event an insert trigger is fired, the method includes performing, in a secondary database table, a record insertion process. In the event a delete trigger is fired, the method includes performing, in the secondary database table, a record deletion process. In the event an update trigger is fired, the method includes performing, in the secondary database table, at least one of the record insertion process for a post-update versioned database object and the record deletion process for a pre-update versioned database object.

Claims

1. A computer-implemented method including executing, using at least one processor, instructions recorded on a non-transitory computer-readable storage medium, the method comprising:

receiving a request to insert, in a main database table, a versioned database object having a first identifier (ID) and including a plurality of attributes;

determining whether uniqueness of a first subset of the plurality of attributes of the versioned database object having the first ID as compared to analogous attributes included in other versioned database objects in the main database table having different IDs than the first ID is required; and

based on determining that uniqueness of the first subset of attributes is required, evaluating uniqueness of the first subset of attributes of the versioned database object with the first ID as compared to the analogous attributes of the other versioned database objects based on respective records for the other versioned database objects stored in a secondary database table, wherein the secondary database table includes a plurality of records for versioned database objects, each record associated with a respective record count indicative of a number of records of the versioned database object included in the main database.

2. The computer-implemented method of claim 1, wherein based on determining that the first subset of attributes is not unique, the method further comprises:

aborting the insertion of the versioned database object with the first ID; and

raising an exception to an application layer.

3. The computer-implemented method of claim 1, wherein based on determining that the first subset of attributes is unique, the method further comprises:

determining whether a record for the versioned database object having the first ID is included in the secondary database table; and

based on determining that a record for the versioned database object having the first ID is not included in the secondary database table, the method further comprises;

inserting a record corresponding with the versioned database object with the first ID in the secondary database table; and

based on determining that a record for the versioned database object having the first ID is included in the secondary database table, the method further comprises;

updating a respective record count for the record corresponding with the versioned database object with the first ID in the secondary database table; and

completing the insertion of the versioned database object with the first ID in the main database table.

4. The computer-implemented method of claim 3, wherein the record for the versioned database object having the first ID included in the secondary database table includes the first ID and the first subset of attributes;

and

wherein updating the respective record count for the record corresponding with the versioned database object with the first ID comprises incrementing the respective record count.

5. The computer-implemented method of claim 3, further comprising setting a respective record count of the inserted record to one.

6. The computer-implemented method of claim 1, wherein determining whether uniqueness of the first subset of attributes is to be evaluated is based on a second subset of the plurality of attributes of the versioned database object with the first ID.

7. The computer-implemented method of claim 1, wherein determining whether uniqueness of the first subset of attributes is required comprises evaluating a Boolean expression based on a second subset of the plurality of attributes of the versioned database object with the first ID.

8. The computer-implemented method of claim 1, wherein evaluating uniqueness of the first subset of attributes of the versioned database object with the first ID includes evaluating uniqueness of the first ID in the secondary database table.

9. A computer-implemented method including executing, using at least one processor, instructions recorded on a non-transitory computer-readable storage medium, the method comprising:

receiving a request to delete, from a main database table, a versioned database object having a first identifier (ID) and including a plurality of attributes;

determining whether uniqueness of a first subset of the plurality of attributes of the versioned database object having the first ID as compared to analogous attributes included in other versioned database objects in the main database table having different IDs than the first ID is required; and

based on determining that uniqueness of the first subset of attributes is required, updating or deleting a record corresponding with the versioned database object with the first ID in a secondary database table, wherein the record is associated with a respective record count indicative of a number of records of the versioned database object with the first ID that are included in the main database.

10. The computer-implemented method of claim 9, wherein determining whether uniqueness of the first subset of attributes of the versioned database object with the first ID is required comprises evaluating a Boolean expression based on a second subset of the plurality of attributes of the versioned database object with the first ID.

11. The computer-implemented method of claim 9, wherein updating or deleting a record corresponding with the versioned database object with the first ID in the secondary database table comprises:

decrementing a respective record count of a previously inserted record in the secondary database table that includes the first ID and the first subset of attributes; and

in the event the decremented respective record count is zero, deleting the previously inserted record from the secondary database table.

12. The computer-implemented method of claim 9, further comprising deleting the versioned database object with the first ID from the main database table.

13. A computer-implemented method including executing, using at least one processor, instructions recorded on a non-transitory computer-readable storage medium, the method comprising:

receiving a request to update, in a main database table, a versioned database object having a first identifier (ID) from a pre-update versioned database object to a post-update versioned database object;

determining whether to fire an update trigger for the main database table based on uniqueness requirements for respective, analogous first subsets of attributes of the pre-update versioned database object and the post-update versioned database object, and based on evaluating trigger criteria for respective, analogous second subsets of attributes of the pre-update versioned database object and the post-update versioned database object; and

in the event that a determination to fire the update trigger is made, performing, in a secondary database table, at least one of;

a delete process for a record corresponding with the pre-update versioned database object; and

an insert process for a record corresponding with the post-update versioned database object.

14. The computer-implemented method of claim 13, wherein determining whether to fire the update trigger comprises evaluating one or more Boolean expressions based on the respective, analogous second subsets of attributes of the post-update versioned database object and the pre-update versioned database object.

15. The computer-implemented method of claim 14, wherein evaluating the one or more Boolean expressions comprises:

determining that uniqueness of the first subset of attributes for the post-update versioned database object is not to be evaluated; and

determining that uniqueness of the first subset of attributes for the pre-update versioned database object was evaluated,

wherein, in response, the method comprises performing the delete process including;

decrementing a record count of a previously inserted record in the secondary database table that includes the first ID and the first subset of attributes of the pre-update versioned database object; and

in the event the decremented record count is zero, deleting the previously inserted record from the secondary database table.

16. The computer-implemented method of claim 14, wherein evaluating the one or more Boolean expressions comprises determining that:

uniqueness of the first subset of attributes for the post-update versioned database object is to be evaluated;

uniqueness of the first subset of attributes for the pre-update versioned database object was evaluated; and

the first subset of attributes of the post-update versioned database object is different than the first subset of attributes of the pre-update versioned database object,

wherein, in response, the method comprises;

performing the delete process including;

decrementing a record count of a previously inserted record in the secondary database table that includes the first ID and the first subset of attributes of the pre-update versioned database object; and

in the event the decremented record count is zero, deleting the previously inserted record from the secondary database table; and

performing the insert process including;

evaluating uniqueness of the first subset of attributes of the post-update versioned database object as compared to other versioned database objects in the main database table having respective IDs that are different than the first ID, the evaluating being based on respective records for the other versioned database objects stored in the secondary database table;

based on determining that the first subset of attributes of the post-update versioned database object are not unique;

aborting the update; and

raising an exception in an application layer; and

based on determining that the first subset of attributes of the post-update versioned database object are unique;

updating or inserting a record corresponding with the post-update versioned database object in the secondary database table; and

completing the update of the versioned database object with the first ID in the main database table.

17. The computer-implemented method of claim 16, wherein evaluating uniqueness of the first subset of attributes of the post-update versioned database object includes evaluating uniqueness of the first ID in the secondary database table.

18. The computer-implemented method of claim 16, wherein updating or inserting a record in the secondary database table corresponding with the post-update versioned database object comprises:

in the event a previously inserted record that includes the first ID and the first subset of attributes of the post-update version of the versioned database object is present in the secondary database table, incrementing a record count of the previously inserted record; and

in the event the previously inserted record with the first ID and the first subset of attributes is of the post-update versioned database object is not present in the secondary database table, inserting a new record in the secondary database table that includes the first ID and the first subset of attributes of the post-update versioned database object.

19. The computer-implemented method of claim 18, further comprising setting a record count of the new record to one.

20. The computer-implemented method of claim 14, wherein evaluating the one or more Boolean expressions comprises determining that:

uniqueness of the first subset of attributes for the post-update versioned database object is to be evaluated; and

uniqueness of the first subset of attributes for the pre-update versioned database object was not evaluated,

wherein, in response, the method comprises performing the insert process including;

evaluating uniqueness of the first subset of attributes of the post-update versioned database object as compared to other versioned database objects in the main database table having respective IDs that are different than the first ID, the evaluating being based on respective records for the other versioned database objects stored in the secondary database table;

based on determining that the first subset of attributes of the post-update versioned database object is not unique;

aborting the update; and

raising an exception in an application layer; and

based on determining that the first subset of attributes of the post-update versioned database object is unique;

updating or inserting a record corresponding with the post-update versioned database object in the secondary database table; and

completing the update of the versioned database object with the first ID in the main database table.

21. The computer-implemented method of claim 20, wherein evaluating uniqueness of the first subset of attributes of the post-update versioned database object includes evaluating uniqueness of the first ID in the secondary database table.

22. The computer-implemented method of claim 20, wherein updating or inserting a record in the secondary database table corresponding with the post-update versioned database object comprises:

in the event a previously inserted record that includes the first ID and the first subset of attributes of the post-update version of the versioned database object is present in the secondary database table, incrementing a record count of the previously inserted record; and

in the event the previously inserted record with the first ID and the first subset of attributes is of the post-update versioned database object is not present in the secondary database table, inserting a new record in the secondary database table that includes the first ID and the first subset of attributes of the post-update versioned database object.

23. The computer-implemented method of claim 22, further comprising setting a record count of the new record to one.

24. A computer-implemented method including executing, using at least one processor, instructions recorded on a non-transitory computer-readable storage medium, the method comprising:

receiving a request to insert, update or delete a versioned database object having a first identifier (ID) in a main database table and including a plurality of attributes;

determining, based on the request and based on evaluating trigger criteria for a subset of the plurality of attributes, whether to fire an insert trigger, a delete trigger or an update trigger for the main database table;

in the event an insert trigger is fired, performing, in a secondary database table, a record insertion process for the versioned database object;

in the event a delete trigger is fired, performing, in the secondary database table, a record deletion process for the versioned database object; and

in the event an update trigger is fired, performing, in the secondary database table, at least one of;

the record insertion process for a post-update versioned database object; and

the record deletion process for a pre-update versioned database object.

25. A non-transitory computer-readable storage medium having instructions stored thereon that, when executed by one or more processors, cause a computing device to:

receive a request to insert, update or delete a versioned database object having a first identifier (ID) in a main database table and including a plurality of attributes;

determine, based on the request and based on evaluating trigger criteria for a subset of the plurality of attributes, whether to fire an insert trigger, a delete trigger or an update trigger for the main database table;

in the event an insert trigger is fired, perform, in a secondary database table, a record insertion process for the versioned database object;

in the event a delete trigger is fired, perform, in the secondary database table, a record deletion process for the versioned database object; and

in the event an update trigger is fired, perform, in the secondary database table, at least one of;

the record insertion process for a post-update versioned database object; and

the record deletion process for a pre-update versioned database object.