UNIQUE ATTRIBUTE CONSTRAINTS FOR VERSIONED DATABASE OBJECTS

• US 20130262403A1
• Filed: 04/23/2012
• Published: 10/03/2013
• 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
• Granted Patent: US 8,812,539 B2
• Time in Patent Office: Days
• US Class Current: 707/691
• 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);

determining whether uniqueness of a first subset of attributes of the versioned database object versus other versioned database objects in the main database table having different IDs than the first ID is required; and

in the event it is determined 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 other versioned database objects based on respective records for the other versioned database objects stored in a secondary database table.

2. The computer-implemented method of claim 1, wherein if, based on the evaluating, it is determined 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 if, based on the evaluating, it is determined that the first subset of attributes is unique, the method further comprises:

updating or inserting a 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 updating or inserting a record corresponding with the versioned database object with the first ID in the secondary database table comprises:

in the event a previously inserted record that includes the first ID and the first subset of attributes 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 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.

5. The computer-implemented method of claim 4, further comprising setting a record count of the new 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 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 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);

determining whether uniqueness of a first subset of attributes of the versioned database object versus other versioned database objects in the main database table having different IDs than the first ID is required; and

in the event it is determined 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.

10. The computer-implemented method of claim 9, wherein determining whether uniqueness of the first subset of attributes of the versioned database object is required comprises evaluating a Boolean expression based on a second subset 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 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 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

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 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 set 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 and the de including;

evaluating uniqueness of the first set 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;

in the event it is determined that the first set of attributes of the post-update versioned database object is not unique;

aborting the update; and

raising an exception in an application layer; and

in the event it is determined that the first set 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.

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 set 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;

in the event it is determined that the first set of attributes of the post-update versioned database object is not unique;

aborting the update; and

raising an exception in an application layer; and

in the event it is determined that the first set 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;

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, 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;

determine, 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, 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.