po - Project Open

righteousgaggleΔιαχείριση Δεδομένων

31 Ιαν 2013 (πριν από 4 χρόνια και 4 μήνες)

207 εμφανίσεις

The
]
po
[

Data Model

Frank Bergmann,
2006
-
05
-
22

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.

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.project
-
open.org/product/modules/


]
po
[

Developer Documentation: http://www.project
-
open.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

Data Model

TCL Library

TCL Pages

General Conventions


Tables names are always in plural form and written in lower case


Tables are held in general in
2
nd 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 relation
-
ship between
tables.

Big arrow tip means
inheritance relation
-
ship. An inheriting
"object" references
its parent class with
its object_id.

"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.

object_type_id

object_status_id

object

object_type_id

name

object_type

description

object_status_id

name

object_status

description

name

description

...

object_type_id

object_status_id

object

name

description

...

category_id

name

im_categories

description

category_type

The "Classical" DB
-
Design: Every table has
it's own tables for type, status and similar
information.

]
po
[

DB
-
Design: All type and status information
is stored in a single "im_categories" table.

parent_category_id

im_category_hierarchy

child_category_id

]
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.

object_type_id

object_status_id

object

name

description

...

category_id

name

im_categories

description

category_type

privilege_id

pretty_name

acs_privileges

object_id

privilege_id

acs_permissions

grantee_id

object_id

object_type

acs_objects

acs_object_types

object_type

supertype

table_name

id_column

name_method

attribute_id

table_name

acs_attributes

column_name

object_type

pretty_name

object_id

popularity

im_search_objects

full_text_index

]
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.

Main
Class
es

object_id

im_biz_objects

rel_id

object_id_one

acs_rels

object_id_two

object_id

object_type

acs_objects

group_id

group_name

groups

profile_id

profile_gif

im_profiles

users_contact

<Home Address>

<Work Address>

im_employees

<Payroll Info>

<Recruiting Info>

im_freelancers

<Freelance Info>

parties

party_id

email

url

persons

person_id

first_names

last_name

users

user_id

username

password

salt

auth_token

company
_id

im_companies

main_office_id

company_name

company
_
status_
id

company
_
type_
id

primary_contact
_id

accounting_contact
_id

note

vat_number

o
ffice
_id

<Address>

im_offices

office
_
status_
id

office
_
type_
id

company_id

project
_id

im_
projects

project_lead
_id

project_name

project
_
status_
id

project
_
type_
id

supervisor
_id

project_budget

budget_hours

budget_currency

project_nr

project_path

customer_id

description

note

start_date

end_date

Main
Class
es


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.

Financial
Class
es

cost_center_id

cost_center_name

im_cost_centers

cost_center_code

cost_center_type_id

cost_center_status_id

department_p

im_costs

cost_id

cost_name

cost_nr

project_id

customer_id

provider_id

investment_id

template_id

cost_status_id

cost_type_id

effective_date_id

payment_days

amount

currency

paid_amount

paid_currency

tax

vat

description

note

cost_center_id

invoice_id

company_contact_id

im_invoices

invoice_nr

invoice_office_id

item_id

item_name

im_invoice_items

project_id

invoice_id

item_units

item_uom_id

price_per_unit

currency

sort_order

item_type_id

item_status_id

description

payment_id

cost_id

im_payments

company_id

provider_id

received_date

payment_type_id

payment_status_id

amount

currency

note

object_id

object_type

acs_objects

Inheritance!

Financial
Class
es


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.

Relationship
Class
es

object_id

object_type

acs_objects

rel_id

im_biz_object_members

object_role_id

rel_id

acs_rels

object_id_one

object_id_two

rel_type

rel_id

membership_rels

member_state

rel_id

admin_rels

rel_id

composition_rels

rel_id

group_rels

rel_type

group_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.

rel_type

acs_rel_type

object_type_two

role_one

object_type_one

role_two

min_n_rels_one

max_n_rels_one

min_n_rels_two

max_n_rels_two

Relationship
Class
es


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

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.

Translation
Class
es

object_id

object_type

acs_objects

im_costs

im_invoices

im_trans_invoices

price_id

uom_id

im_trans_prices

company_id

task_type_id

source_lang_id

target_lang_id

subject_area_id

currency

price

note

im_trans_tasks

task_id

task_name

task_filename

project_id

source_language_id

target_language_id

invoice_id

quote_id

task_status_id

task_type_id

task_units

billable_units

trans_id

edit_id

proof_id

other_id

match_x

match_rep

match_
100

match_
95

task_uom_id

match_
85

match_
75

match_
50

match_
0

end_date

note

subject_area_id

report_id

allowed_errors

im_trans_quality_reports

task_id

report_date

reviewer_id

sample_size

total_errors

comments

report_id

im_trans_quality_
entries

quality_category_id

minor_errors

major_errors

critical_errors

object_id

im_trans_
trados_matrix

match_x

match_rep

match_
100

match_
95

match_
85

match_
75

match_
50

match_
0

Translation
Class
es


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.

Consulting
Class
es

object_id

object_type

acs_objects

im_costs

im_invoices

im_ts_invoices

material_id

description

im_
materials

material_name

material_nr

material_type_id

material_status_id

material_uom_id

task_id_one

im_
ts_
task_deps
.

task_id_two

dependency_type_id

difference

hardness_type_id

project
_id

im_
projects

project_name

...

customer_id

start_date

end_date

task_id

invoice_id

im_
timesheet_tasks

material_id

uom_id

planned_units

billable_units

cost_center_id

priority

price_id

uom_id

im_timesheet_prices

company_id

task_type_id

material_id

currency

price

note

project_type_id

Consulting
Class
es


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.

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

View

view

ViewColumn

Currency

Country

im_component_plugins

Menu

Software

Development

Templates

Trans
-

lation

CRM

Finance

Controlling

HR

System

Collaboration,

Content & KM

OO

Frame


Security

Calendar

OpenACS

Permission

Web

Server

Database

Finance

Base

Payroll

Skill

Database

File

storage

Online

Discussions

Incident

Workflow

Basic

Authentication

LDAP

Authentication

Workflow

Engine

Chat

Package

Manager

Automatic

Software

Updates

PostgreSQL

Oracle
8
i
,
9
i
,
10
g

Page

Contracts

SQL

Templates

OO

Model

Object

Metadata

Localization

Framework

Contact

Mgmt.

Reporting

Engine

Wiki

Oracle Intermedia/Text

TSearch
2

Linux

Solaris

BSD

Windows

+
CygWin

Mac OS

Operating

System

Search

Engine

AOLServer

Pound

Revers Proxy

DynField

Object

Extensions

Platform

Services

Profiling &


Performance

Debugging

System

Application

Modules

Form

Builder

Portal &

Components

Mail Server

Integration

Content

Management

Application

Services

DB
-
API

TCL

Quotes &

Invoice

Payments

Financial

Reporting

Mail Server

Integration

Customer

Web Reg.

Marketing

Campaigns

CRM

Tracking

Automatic

Audits

ISDN Tel

Integration

Project

Mgmt.

Project &

Subprojects

Project

Controlling

Translation

Workflow

TM

Integration

Freelance

Invoicing

Timesheet

Invoicing

Available

Documentation

Timesheet

Mgmt.

Automatic

Invoicing

]
po
[


Overview

Finance

Guide

Unix Install

Guide

OpenACS

Inst. Guide

Operations

& Maint.

Guide

OpenACS

Developer

Guide

Configuration

Guide

Filestorage

Guide

Forum

Guide

Translation

Workflow

Guide

Full
-
Text

Search

Automatic

Testing

BigBrother

Sys Mgmt.

Other

Room

Reservation

E
-

Commerce

CMS

WebDAV

SOAP &

XML
-
RPC

Surveys

Glossary

Web
-
Mail

Blog

Recruiting

Workflow

CVS

Postfix
/

Sendmail

Database

Replication

Mondrian

Data
-

Warehouse

Frank Bergmann

frank.bergmann@project
-
open.com

www.project
-
open.com