Transcript ]project-open[ Data Model Overview

This guide contains overview
information that is useful if you
want to write reports or if you
need to extract information
from the API.
It is not ment as a developer
guide. Please check the
OpenACS manual for more
detailed information.
The ]po[ Data Model
Frank Bergmann, 2006-05-22
Contents







Other Documentation
General Conventions
"Categories"
]po[ Objects and Types
Main Classes
Financial Classes
Auxilary Tables
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.projectopen.org/product/modules/
]po[ Developer Documentation: http://www.projectopen.org/doc/

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
TCL Pages
TCL Library
Data Model
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".
Normal arrow
means referential
integrity relationship between
tables.
Big arrow tip means
inheritance relationship. An inheriting
"object" references
its parent class with
its object_id.
"Categories"
object
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.
object_type
object_type_id
object_status_id
name
description
...
object_type_id
name
description
object_status
object_status_id
name
description
The "Classical" DB-Design: Every table has
it's own tables for type, status and similar
information.
object
im_categories
object_type_id
object_status_id
name
description
...
category_id
category_type
name
description
im_category_hierarchy
parent_category_id
child_category_id
]po[ DB-Design: All type and status information
is stored in a single "im_categories" table.
]po[ Objects and Types
All major tables in ]po[ and OpenACS are held
as 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.
"Being" an object has many advantages:

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.
acs_attributes
acs_objects
object_id
object_type
object
object_type_id
object_status_id
name
description
...
attribute_id
pretty_name
object_type
table_name
column_name
acs_object_types
object_type
supertype
table_name
id_column
name_method
im_categories
category_id
category_type
name
description
acs_permissions
object_id
privilege_id
grantee_id
im_search_objects
object_id
popularity
full_text_index
acs_privileges
privilege_id
pretty_name
]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.
acs_objects
Main Classes
object_id
object_type
acs_rels
rel_id
object_id_one
object_id_two
groups
parties
group_id
group_name
party_id
email
url
im_profiles
profile_id
profile_gif
im_companies
company_id
company_name
company_status_id
company_type_id
main_office_id
primary_contact_id
accounting_contact_id
vat_number
note
im_biz_objects
object_id
im_offices
office_id
company_id
office_status_id
office_type_id
<Address>
persons
person_id
first_names
last_name
im_projects
project_id
project_name
project_nr
project_path
customer_id
project_status_id
project_type_id
project_lead_id
supervisor_id
project_budget
budget_currency
budget_hours
start_date
end_date
description
note
users
user_id
username
password
salt
auth_token
users_contact
<Home Address>
<Work Address>
im_employees
<Payroll Info>
<Recruiting Info>
im_freelancers
<Freelance Info>
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.
acs_objects
object_id
object_type
Inheritance!
im_invoices
invoice_id
company_contact_id
invoice_nr
invoice_office_id
im_invoice_items
item_id
item_name
project_id
invoice_id
item_units
item_uom_id
price_per_unit
currency
sort_order
item_type_id
item_status_id
description
im_costs
cost_id
cost_name
cost_nr
cost_status_id
cost_type_id
project_id
customer_id
provider_id
cost_center_id
investment_id
template_id
effective_date_id
payment_days
amount
currency
paid_amount
paid_currency
tax
vat
description
note
Financial Classes
im_payments
payment_id
cost_id
company_id
provider_id
received_date
payment_type_id
payment_status_id
amount
currency
note
im_cost_centers
cost_center_id
cost_center_name
cost_center_code
cost_center_type_id
cost_center_status_id
department_p
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.
acs_objects
Relationship Classes
object_id
object_type
acs_rel_type
rel_type
object_type_one
object_type_two
role_one
role_two
acs_rels
rel_id
rel_type
object_id_one
object_id_two
membership_rels
rel_id
member_state
composition_rels
rel_id
min_n_rels_one
max_n_rels_one
min_n_rels_two
max_n_rels_two
admin_rels
rel_id
group_rels
rel_id
rel_type
group_id
im_biz_object_members
rel_id
object_role_id
Defines the relationships between an
im_biz_object and some users. The membership
type corresponds to "horizontal permissions" and
usually includes "full member", "project manager",
"key account" and user-defined roles.
Relationship Classes

acs-rels:
The acs_rels table is essentially a generic mapping table for acs_objects. Once we
come up with a way to associate attributes with relationship types, we could
replace many of the ACS 3.x mapping tables like user_content_map,
user_group_map, and user_group_type_modules_map with this one table. Much
application logic consists of asking questions like "Does object X have a relationship
of type Y to object Z?" where all that differs is X, Y, and Z. Thus, the value of
consolidating many mapping tables into one is that we can provide a generic API
for defining and querying relationships. In addition, we may need to design a way
to enable "type_specific" storage for relationships (i.e., foreign key columns for
one-to-many relationships and custom mapping tables for many-to-many
relationships), instead of only supporting "generic" storage in the acs_rels table.
This would parallel what we do with acs_attributes.

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
acs_objects
object_id
object_type
im_costs
im_invoices
im_trans_invoices
im_trans_tasks
task_id
task_name
task_filename
task_status_id
project_id
task_type_id
source_language_id
target_language_id
subject_area_id
task_uom_id
invoice_id
quote_id
end_date
task_units
billable_units
im_trans_trados_matrix
object_id
match_x
match_rep
match_100
match_95
match_85
match_75
match_50
match_0
Translation Classes
trans_id
edit_id
proof_id
other_id
match_x
match_rep
match_100
match_95
match_85
match_75
match_50
match_0
note
im_trans_prices
price_id
company_id
task_type_id
source_lang_id
target_lang_id
subject_area_id
uom_id
currency
price
note
im_trans_quality_reports
report_id
task_id
report_date
reviewer_id
sample_size
allowed_errors
total_errors
comments
im_trans_quality_entries
report_id
quality_category_id
minor_errors
major_errors
critical_errors
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 sitewide defaults for customer translation prices.
acs_objects
Consulting Classes
object_id
object_type
im_costs
im_invoices
im_projects
project_id
project_name
customer_id
project_type_id
start_date
end_date
im_ts_invoices
...
im_timesheet_tasks
im_materials
material_id
material_name
material_nr
material_type_id
material_status_id
material_uom_id
description
task_id
material_id
uom_id
planned_units
billable_units
cost_center_id
invoice_id
priority
im_timesheet_prices
price_id
company_id
task_type_id
material_id
uom_id
currency
price
note
im_ts_task_deps.
task_id_one
task_id_two
dependency_type_id
difference
hardness_type_id
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 sitewide default price list.
Auxilary Tables
im_component_plugins
GUI

Define what "plugins" (these grey boxes) should appear
on what page
Menu

Defines the hierarchical menu structure of the system
ViewColumn

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
Country

The list of all countries in the world
Currency

The list of all currencies
view
View
Payroll
HR
Skill
Database
Recruiting
Workflow
Application
Modules
Application
Services
Platform
Services
Web
Server
Project
Project &
Mgmt.
Subprojects
Project
Controlling
Timesheet
Mgmt.
Collaboration,
File
Calendar Content & KM
storage
Online
Wiki
Chat
Discussions
Mail Server WebDAV
Incident
Integration
Workflow
Content
Management
CMS
Blog
Full-Text
Search
Workflow
Engine
Reporting
Engine
Portal &
Components
Localization
Framework
OO
OO
Frame
Model
Object
Metadata
DynField
Object
Extensions
SOAP &
XML-RPC
DB-API
AOLServer
Search
Engine
TSearch2
Oracle Intermedia/Text
Database
PostgreSQL
Oracle 8i, 9i, 10g
Operating
System
Windows
+ CygWin
Linux
Solaris
Room
Reservation
ECommerce
BSD
Mac OS
Other
]po[
Overview
Surveys
Web-Mail
Finance
Finance
Base
Controlling
Quotes &
Invoice
Freelance
Invoicing
Payments
Timesheet
Financial
Invoicing
Reporting
Software
Package
Development
Manager
Profiling &
Templates
Performance
Debugging
Automatic
Automatic System
Testing
Software
Form
Updates
Builder
TCL
TransTranslation
lation
Workflow
TM
Integration
Automatic
Invoicing
Glossary
Available
Documentation
CRM
Contact
Mgmt.
Customer
Web Reg.
Marketing
Campaigns
CRM
Tracking
Mail Server
Integration
ISDN Tel
Integration
Security
OpenACS
Permission
Page
Basic
Contracts
Authentication
SQL
LDAP
Templates
Authentication
Automatic
Audits
Pound
Revers Proxy
BigBrother
Sys Mgmt.
Database
Replication
CVS
System
Mondrian
DataWarehouse
Postfix/
Sendmail
Finance
Guide
Translation
Workflow
Guide
Forum
Guide
Filestorage
Guide
Configuration
Guide
OpenACS
Developer
Guide
Operations
& Maint.
Guide
OpenACS
Inst. Guide
Unix Install
Guide
Frank Bergmann
[email protected]
www.project-open.com