The ]po[ Data Model Frank Bergmann, 2006-05-22

1. The ]po[ Data Model Frank Bergmann, 2006-05-22

2. ]po[ Overview

3. Contents Other Documentation General Conventions "Categories" ]po[ Objects and Types Main Classes Financial Classes Auxilary Tables

4. Other Documentation This is a limited overview document. For more detailed information please see: ]po[ Overview Diagram: http://www.project-open.org/ OpenACS Developer Guide: http://www.openacs.org/doc/ ]po[ Whitepapers: http://www.project-open.com/whitepapers/ ]po[ List of modules: http://www.project-open.org/product/modules/ ]po[ Developer Documentation: http://www.project-open.org/doc/

5. General System Architecture ]po[ consists of three main parts: A powerful object-relational data model TCL pages that render the contents of the database and TCL libraries that provide auxilary functions to the TCL pages The Data Model is particularly important in ]po[ because: It is object-oriented The TCL layer does not contain objects. It merely renders the content of the Data Model The Data Model is the location where you need to go if you want to extract information from the system

6. General Conventions Tables names are always in plural form and written in lower case Tables are held in general in 2nd normal form Denormalized table columns are named with a "_cache" postfix. All main tables have an integer primary key <table_name>_id. This primary key references the table acs_objects which contains the type of the object. Most object->object mappings are handleled by the "acs_rels" table and its subclasses (see below). Other mapping tables are called something_something_map Boolean fields have a "_p" postfix and contain 't' or 'f' char values All main objects include "object_type_id" and "object_status_id" fields. "Type" is used to create minor subclasses of the objects, such as the distinction of im_companies such as "customer" and "provider". "Status" is used to deal with the object's lifecycle, such as "potential", "active", "closed" and "deleted".

7. �Business Objects�

8. Data Model Overview � Business Objects Most data in the ]po[ data model are structured as �Business Objects� (BizObj). A �BizObj� is just a database table following a number of conventions A integer primary key called <object>_id representing a unique object ID The primary key references the �acs_objects� table. acs_objects includes object meta-information such as object type, creation date, creation user etc. Every ]po[ BizObj includes a field �object_status_id� re

9. Data Model Overview � Business Objects The ]po[ data model is based on the notion of �Business Objects� A �BizObj� is just a database table following a number of conventions A primary key representing a unique object identifier The primary key references the �acs_objects� table. acs_objects includes object meta-information such as object type, creation date, creation user etc. Every ]po[ BizObj includes a field �object_status_id� re

10. Data Model Overview � Business Objects The ]po[ data model is based on the notion of �Business Objects� A �BizObj� is just a database table following a number of conventions A primary key representing a unique object identifier The primary key references the �acs_objects� table. acs_objects includes object meta-information such as object type, creation date, creation user etc. Every ]po[ BizObj includes a field �object_status_id� re

11. Hierarchical Queries

12. "Categories" Conventional/ traditional database design normally includes a lot of foreign key tables defining the type and status of an object. ]po[ takes a different approach in order to minimize the number of database tables and maintenance screens. We use a single "im_categories" table for all types of status and type information: The total number of DB tables is reduced to a third or fourth A single maintenance screen is responsible for managing categories. Built-in features: Localization Hierarchical categories Common GUI widgets Referential Integrity is enforced It is possible to assign the wrong Category to a field This has never happened in practice yet.

13. Categories Select Everything About Categories select c.*, im_category_from_id(aux_int1) as aux_int1_cat, im_category_from_id(aux_int2) as aux_int2_cat, h.parent_id, im_category_from_id(h.parent_id) as parent from im_categories c left outer join im_category_hierarchy h on (c.category_id = h.child_id) where c.category_type = 'Intranet Cost Types' order by category_type, category_id;

14. ]po[ Objects and Types All major ]po[ tables are "Objects" Objects are identified by an object_id. Information about object type is stored in the acs_objects table, together with metadata and creation information. Advantages of "being" an object: Permission system: Associates objects with "privileges", including all necessary maintenance screens SQL metadata system: Allows the administrators to dynamically extend object types with new attributes Full-Text search:Indexes all objects and produces generic URL for the search results Configurable workflow:Allows to manage the status of any object. The WF includes a graphical WF editor. Generic Object Relations:acs_rels allows you to define generic relationships between objects.

15. ]po[ Objects and Types acs_attributes: Each row in acs_attributes table defines an attribute of the specified object type. The contents of the table is managed by the "intranet-dynfield" package that contains a SQL metadata editor. acs_privileges: Contains permissions "tokens" (=> privileges). These privileges allow a user to perform a certain operation in the system. acs_permissions: Defines a mapping between acs_objects and acs_privileges. The mapping is per user group ("grantee_id"). Groups can be hierarchical.

16. Main Classes

17. Main Classes im_biz_objects: This abstract class defines a number of functions to manage "horizonal permissions" (=> Please see other ]po[ documentation) for project, companies and offices "Horizonal" permissions currently include roles such as "Project Manager" of "Full Member". parties, persons, users: These three tables form an inheritance hierarchy. There is a view "cc_users" that unifies the information from these tables. im_freelancers, im_employees, users_contact: These are "extension tables" for "persons" and define additional information for users with particular profiles. im_profiles: This table defines the groups that are relevant for ]po[, as opposed to potentially hundereds of groups of the underlying OpenACS system.

18. Financial Classes Cost Types All costs have an entry in im_costs, plus: Financial Docs(im_invoices) Customer Invoice Quote Provider Bill Purchase Order Delivery Note Simple Costs(only im_cost) Timesheet Cost Employee Salary Expense Item(im_expenses) Expense Bundle(im_expense_bundles)

19. Financial Classes (2) The Problem: Cost and Projects can have a N:M relationship: One project may have more then one invoice/cost item. One cost item may �relate� to more then one project (in particular: project + sub-project, but also the item may be split between two main projects). An invoice may actually not belong to any project at all. Solution: In general, the relationship between projects and costs is defined as an N:M relationship by acs_rel. im_costs.project_id points to im_projects ONLY in the case that one cost item relates to exactly one project Watch out: Cost Items can be related to a main-project or to a sub-project or any level. So in order to calculate the main project�s total Profit & Loss, you need to sum up the financial items through the full project tree (hierarchical query)

20. Financial Classes (3)

21. Financial Classes im_costs: This is the main table for all cost items. All financial elements in the system are stored in this table. The table also receives updates from timesheet (cost related to users working on a project) etc. im_cost_centers: Is currently not used. The table has been included in the current datamodel for smooth upgrade in future versions. im_invoices: This table contains financial documents such as "Invoices", "Quotes", "Bills" and "Purchase Orders" (the name "im_invoices" is a bit misleading). im_invoice_items: These are the individual lines of an invoice.

22. Relationship Classes

23. Relationship Classes acs-rels: im_biz_object_members: Defines the member of a im_biz_object and their roles (project manager of full member). Such a membership has an impact on the access rights of members to the business object. membership_rels: Defines group->supergroup relations

24. Translation Classes

25. Translation Classes im_trans_tasks: This tables contains the description of the main activity related to translation projects. Includes information about Translation Memory repetitions Inludes information about the translator, editor, proof-reader and "other" assigned to perform this task (static workflow) im_trans_invoices: This is currently just a flag (binary information) to identify invoices that have been created based on im_trans_tasks. Translation invoices are referenced by im_trans_tasks.invoice_id in order to make sure that every trans_task is included in exactly one invoice. These references need to be removed if a translation invoice is deleted. im_trados_matrix: Contains the discounts per customer and translation task type for different Translation Memory matches. The trados_matrix associated with the "internal" company contains the site-wide defaults for customer translation prices.

26. Timesheet Classes

27. Consulting Classes im_timesheet_tasks: This tables contains the description of the main activity related to projects. im_timesheet_invoices: This is currently just a flag (binary information) to identify invoices that have been created based on im_timesheet_tasks. Timesheet invoices are referenced by im_timesheet_tasks.invoice_id in order to make sure that every task is included in exactly one invoice. These references need to be removed if a timesheet invoice is deleted. im_timesheet_prices: Contains a price per material and customer. The timesheet_prices associated with the "internal" company contains the site-wide default price list.

28. Workflow

29. Workflow ]po[ uses a Petri-Net based workflow A workflow consists of �Places� (states) and �Transitions�, linked by �Arcs� that might carry �Guard� expressions. WF �Transitions� can be linked to pages acting on object�s fields

30. WF @ Runtime During runtime, a similar structure is created representing a specific �Case� (a specific instance of a WF): The �case� corresponds to a WF in action. Transitions become �tasks�, with specific assignments to users. Places may include �tokens� that move through transitions to other places. A single token in a WF place can be thought as �the WF is in status XXX�. Petri-Nets allow for more then one token and more then one token per place, but this option is rarely used in ]po[.

31. WF Tables

32. WF �Callbacks� �Callbacks� are Pl/SQL database procedures that determine the behavior of a transition: A number of �Outgoing Events� allow to customize the behavior of WF transitions: Enable: Is called when the transition is enabled (a token is placed in one of its input places) Unassigned: Is called if the transition hasn�t been statically (see below) assigned to a user or a group. Fire: Is called once the transition gets fired A number of transition�s parameters can be determined by external routines: Time: Deadline: Hold Timeout Notification

33. WF Assignments �Static Assignments�: Specified during the definition of the WF Specified using WF �roles�. Each role can be assigned to one or multiple parties (groups or users) Specified as �roles� using wf_transition.role_key and then mapped to parties using wf_context_assignments. The specific assignments at runtime are held in wf_task_assignments. This construction might seem a bit complex, but it is necessary, as you will find out when defining your first real WFs. �Dynamic Assignments� Use the �Unassigned� callback to define the assigned parties. This option allows you to specify the assignee as a function of object characteristics etc. The results of dynamic assignments are stored in wf_task_assignments.

34. Timesheet & Controlling

35. Timesheet & Controlling Controlling uses caches per project in order to maintain aggregated Profit & Loss (P&L) information. The project caches are updated via triggers Reporting on portfolios & customers is handled by the reporting engine.

36. Timesheet & Controlling Projects contain a set of �xxx_cache� fields that contain the aggregated cost elements per type. CUD (create, update, delete) operations on costs (timesheet hours, expenses, �) does NOT update the caches, for performance reasons. Instead, triggers reset the �cost_cache_dirty� to NULL. There is a �sweeper� process that periodically checks �cost_cache_dirty� and updates the cache. This might be a nightly activity, or every few minutes.

37. Timesheet & Controlling

38. Auxiliary Tables

39. Auxilary Tables GUI Define what "plugins" (these grey boxes) should appear on what page Defines the hierarchical menu structure of the system Define the columns for ListPages in the "core". The ListPages need to be extensible at runtime because they might have to accomodate new columns from add-on modules The list of all countries in the world The list of all currencies

40. Auxilary Tables GUI Define what "plugins" (these grey boxes) should appear on what page Defines the hierarchical menu structure of the system Define the columns for ListPages in the "core". The ListPages need to be extensible at runtime because they might have to accomodate new columns from add-on modules The list of all countries in the world The list of all currencies