Object Tree Pattern

Many objects in ]project-open[ are designed to form a tree or a "hierarchy". Hierarchical structures provide a wealth of options to model the real world with varying degrees of details. This hierarchical model is one of the most unique characteristics of ]project-open[ and the source of its configuration flexibility.

Examples for hierarchical objects include:

• Projects can have sub-projects and tasks of any level.

• Customers may be part of a group structure and holdings.

• A company's departments and cost centers structure is hierarchical.

• Groups may have multiple subgroup levels. Members of subgroups automatically become members of the supergroups.

• The types of projects, customers, help desk tickets or other objects may contain subtypes of any level. For example, a “Provider” company might contain the subtypes “IT Provider,” “Office Equipment Provider” and so on. Establishing these subtypes make it possible to generate reports and analyze spending among the unique provider subtypes.

• The states of projects, customers, help desk tickets or other objects may contain substates of any level. For example, a “closed” project could be classified as “Deleted,” “Canceled by the Customer,” “Unpaid,” etc. Bear in mind, however, that timesheet hours should only be logged on “open” projects.

• Configuration items in the ]po[ configuration database (=inventory) may have sub-elements. A “Server” may be the home of several applications, which in turn consist of different modules, and so on.

• Discussions threads may contain replies at any level.

• Files may be located in folders and subfolders.

• Menus in ]po[ allow for any level of submenus.

• The object types in ]po[ themselves form a class hierarchy.

• Permissions in ]po[ are hierarchical. For example the “admin” permission of one object allows the admin to execute “read,” “write,” “create” and “delete,” which in turn may include additional subpermissions.

The Tree-Sortkey Mechanism

]po[ includes a number of advanced techniques to handle hierarchical data structures. The most frequently used patter is called the "tree-sortkey"

Here is an example on how to use tree_sortkey:

select	parent.project_name,
	(	select	sum(1)
		from	im_projects child
		where	child.tree_sortkey between
				parent.tree_sortkey and 
				tree_right(parent.tree_sortkey)
	) as num_sub_projects
from	im_projects parent
where 	parent.parent_id is null;

This SQL query returns thelist of all main (=top) project in the system, together with the number of sub-projects and tasks in these main projects.

Advantages of the tree_sortkey mechanism:

• Ease of use:
  A single query condition is capable to return the list of sub-projects of any depth.
  
  
• High performance:
  The tree_sortkey works is implemented using bit-sets, which PostgreSQL indexes very efficiently. Also the "between" operation is indexed.