Granted Patent B2

US 9,311,381 · App. 14/143,517 · Granted Apr 12, 2016

Abstract

Claims (79)

1. A computer-implemented method in a database partitioned based on a lifecycle state for database objects, each database object being a row in a table and the database objects being organized in a hierarchy, the method comprising:

receiving, from an application program, a new lifecycle state for a root database object in the hierarchy;

updating the lifecycle state for the root object, thereby causing a table row associated with the root object to change partitions in the database;

locating a first database object that is a child of the root object in the hierarchy;

applying an inheritance function associated with a class of the child object to determine a lifecycle state for the first database object;

when the determined lifecycle state differs from a current lifecycle state for the first database object, changing the current lifecycle state to the determined lifecycle state and moving a table row for the first database object to a partition associated with the determined lifecycle state; and

repeating the locating, applying, and changing for all children of the root object.

2. The computer-implemented method of claim 1 , wherein the first database object has at least two parent objects in the hierarchy and applying the inheritance function includes:

determining the parent objects using a parent retrieval query associated with the class of the first database object; and

determining a lifecycle state for the determined parent objects.

3. The computer-implemented method of claim 1 , wherein the inheritance function is stored as an attribute of the class in a metadata table.

4. The computer-implemented method of claim 1 , wherein the database does not support reference partitioning.

5. The computer-implemented method of claim 1 , wherein the new lifecycle state for the root object is a delete stage and the method further comprises:

traversing the hierarchy from the root object, depth first, avoiding cycles; and

at each leaf object of the hierarchy:

determining that a lifecycle state for the leaf object is a deleted stage, and

issuing a partitioning maintenance command for the database for the leaf object.

6. The computer-implemented method of claim 5 , wherein traversing the hierarchy is performed in accordance with a relation-join query associated with a class of the root object.

7. The computer-implemented method of claim 1 , wherein when the application program updates a foreign key of a second database object, the method further comprises:

applying an inheritance function associated with a class of the second database object to determine a lifecycle status of the second database object;

setting a current lifecycle status of the second database object to the determined lifecycle status;

locating a third database object that is a child of the second database object in the hierarchy;

applying an inheritance function associated with a class of the third database object to determine a lifecycle state for the third database object;

when the determined lifecycle state differs from a current lifecycle state for the third database object, changing the current lifecycle state to the determined lifecycle state and moving a table row for the third database object to a partition associated with the determined lifecycle state; and

repeating the locating, applying, and changing for all children of the second database object.

8. The computer-implemented method of claim 1 , wherein when the application program inserts a second database object into the hierarchy, the second database object being a non-root object for the hierarchy, the method further comprises:

applying an inheritance function associated with a class of the second database object to determine a lifecycle state for the second database object; and

wherein a table row for the second database object is stored in a partition associated with the determined lifecycle state for the second database object.

9. The computer-implemented method of claim 1 , wherein the hierarchy includes database objects stored in at least two databases from different vendors.

10. A system for reference partitioning database objects by lifecycle state, the system comprising:

at least one hardware processor;

at least one database environment, the database environment supporting triggers and partitioning;

at least one application program; and

memory storing:

a lifecycle metadata framework that:

identifies classes in a ragged hierarchy of database objects,

identifies at least one class as a root of the hierarchy,

identifies, for each non-root class, a lifecycle inheritance function for the class, and

identifies, for each parent class-child class pair in the hierarchy, a relation-join query, the relation-join query being a join between tables in the database environment onto which the parent class and child class are persisted, and

triggers that use the framework to maintain lifecycle states for non-root database objects, including at least a first trigger invoked after a lifecycle state of a database object in a root class is changed, a second trigger invoked when a non-root database object is inserted, and a third trigger invoked when a non-root database object has a change in parent.

11. The system of claim 10 , wherein the first trigger uses the lifecycle metadata framework to:

traverse the hierarchy from the database object in the root class downwards, avoiding cycles; and

set a lifecycle state of each child database object reached in the traversal according to the lifecycle inheritance function for a class of the child database object, wherein the child database object is assigned to a partition according to its lifecycle state.

12. The system of claim 10 , wherein the second trigger uses the lifecycle metadata framework to:

determine a lifecycle state for each parent database object of the inserted non-root database object;

apply the lifecycle inheritance function for the class of the inserted non-root database object to determine a lifecycle state for the non-root database object; and

assign the non-root database object to a partition according to the determined lifecycle state.

13. The system of claim 10 , wherein the third trigger uses the lifecycle metadata framework to:

determine a lifecycle state for each parent database object of the non-root database object;

apply the inheritance function for the class of the non-root database object to determine a lifecycle state of the non-root database object, wherein the database object is assigned to a partition according to the lifecycle state;

assign the non-root database object to a partition according to the determined lifecycle state;

traverse the hierarchy from the non-root database object downwards, avoiding cycles; and

set a lifecycle state of each object reached in the traversal according to the inheritance function for a class of the object reached in the traversal.

14. The system of claim 10 , wherein the memory further stores instructions that, when executed by the at least one processor, cause the system to perform operations comprising:

receiving a root class of the hierarchy to purge;

selecting a database object of the root class;

traversing, using the framework, the hierarchy downward from the database object, depth first, avoiding cycles; and

at a leaf database object in the hierarchy:

determining whether a lifecycle state for the leaf database object is a deleted stage, and

when the lifecycle state is a deleted stage, issuing a truncate partition command for the leaf database object, the truncate partition command being a command supplied by the database environment.

15. The system of claim 10 , wherein the framework further includes a hierarchy traversal query for at least some of the classes and a parent retrieval query for at least some of the classes.

16. The system of claim 15 , wherein the hierarchy traversal query for a first class is generated prior to the triggers being invoked and is associated with the first class and stored in the framework, and the generation of the hierarchy traversal query for the first class includes:

using a hierarchical query, avoiding cycles, find the parent class-child class pairs where the first class is the parent;

construct a union query of the relation-join queries for each parent class-child class pair found, the union query returning a result set of identifiers for the parent class and the child class; and

using an outer hierarchical query, avoiding cycles, to reach database objects in the tables identified in the union query using the result set of the union query.

17. The system of claim 15 , wherein the parent retrieval query for a first class is generated prior to the triggers being invoked and is associated with first class and stored in the framework, and the generation of the parent retrieval query for the first class includes:

using a hierarchical query, avoiding cycles, find the parent class-child class pairs where the first class is the child;

constructing a union query of the relation-join queries for each parent class-child class pair found, the union query returning a result set of identifiers for the parent class and the child class; and

using an outer hierarchical query, avoiding cycles, to reach database objects in the tables identified in the union query using the result set of the union query.

18. A method of database object lifecycle maintenance for database objects in a ragged hierarchy, the method comprising:

receiving a class, the class being a root in the ragged hierarchy, the hierarchy being defined through a lifecycle framework that defines classes in the hierarchy, parent-child relationships between classes, and, for each parent-child relationship, stores a join query;

selecting a database object instance of the class;

traversing the hierarchy downward from the database object, depth first, avoiding cycles, the traversal being accomplished via a hierarchical query generated at least by a union of the join queries for parent-child relationships related to the class; and

at a leaf database object reached by traversing the hierarchy:

determining whether the leaf database object is in a partition for deleted objects, and

when the leaf database object is in the partition for deleted objects, issuing a partition maintenance command for the leaf database object, the partition maintenance command being a database-provided command.

19. The method of claim 18 , wherein the hierarchical query generated is associated with the class and stored as part of the framework.

20. The method of claim 18 , wherein the maintenance command is a truncate partition command.

21. The method of claim 18 , wherein the maintenance command is a command that archives the partition.

Assignments (15)