User directory
Sitewide administrator directory
Subsite administrator directory
Tcl script directory
Data model
-
PL/SQL file
ER diagram
Transaction flow diagram
Almost all database-backed websites have users, and need to model the grouping of users. The OpenACS 4 Parties and Groups system is intended to provide the flexibility needed to model complex real-world organizational structures, particularly to support powerful subsite services; that is, where one OpenACS installation can support what appears to the user as distinct web services for different user communities.
The primary limitation of the OpenACS 3.x user group system is
that it restricts the application developer to representing a
"flat group" that contains only users: The user_groups
table may contain the
group_id
of a parent group, but
parent-child relationship support is limited because it only allows
one kind of relationship between groups to be represented.
Moreover, the Oracle database's limited support for tree-like
structures makes the queries over these relationships
expensive.
In addition, the Module Scoping design in OpenACS 3.0 introduced
a party abstraction - a
thing that is a person or a group of people - though not in the
form of an explicit table. Rather, the triple of scope
, user_id
, and group_id
columns was used to identify the
party. One disadvantage of this design convention is that it
increases a data model's complexity by requiring the programmer
to:
add these three columns to each "scoped" table
define a multi-column check constraint to protect against data corruption (e.g., a row with a
scope
value of "group" but a nullgroup_id
)perform extra checks in
Tcl
andPL/SQL
functions and procedures to check both theuser_id
andgroup_id
values
The core of the Group Systems data model is quite simple, but it was designed in the hopes of modeling "real world" organizations which can be complex graph structures. The Groups System only considers groups that can be modeled using directed acyclic graphs, but queries over these structures are still complex enough to slow the system down. Since almost every page will have at least one membership check, a number of triggers, views, and auxiliary tables have been created in the hopes of increasing performance. To keep the triggers simple and the number of triggers small, the data model disallows updates on the membership and composition tables, only inserts and deletes are permitted.
The data model has tried to balance the need to model actual organizations without making the system too complex or too slow. The added triggers, views, and tables and will increase storage requirements and the insert and delete times in an effort to speed access time. The limited flexibility (no updates on membership) trades against the complexity of the code.
The Group System data model consists of the following tables:
parties
The set of all defined parties: any person, user, or group must have a corresponding row in this table.
persons
The set of all defined persons. To allow easy sorting of persons, the name requirement 30.10 is met by splitting the person's name into two columns:
first_names
andlast_name
.users
The set of all registered users; this table includes information about the user's email address and the user's visits to the site.
user_preferences
Preferences for the user.
groups
The set of all defined groups.
group_types
When a new type of group is created, this table holds additional knowledge level attributes for the group and its subtypes.
membership_rels
The set of direct membership relationships between a group and a party.
group_member_index
A mapping of a party P to the groups {Gi }the party is a member of; this mapping includes the type of relationship by including the appropriate
rel_id
from themembership_rels
table.composition_rels
The set of direct component relationships between a group and another group.
group_component_index
A mapping of a group Gto the set of groups {Gi } that G is a component of; this mapping includes the type of relationship by including the appropriate
rel_id
from thecomposition_rels
table.
New groups are created through the group.new
constructor. When a specialized
type of group is required, the group type can be extended by an
application developer. Membership constraints can be specified at
creation time by passing a parent group to the constructor.
The membership_rels
and
composition_rels
tables
indicate a group's direct members and direct components; these
tables do not provide a record of the members or components that
are in the group by virtue of being a member or component of one of
the group's component groups. Site pages will query group
membership often, but the network of component groups can become a
very complex directed acyclic graph and traversing this graph for
every query will quickly degrade performance. To make membership
queries responsive, the data model includes triggers (described in
the next paragraph) which watch for changes in membership or
composition and update tables that maintain the group party
mappings, i.e., group_member_index
and group_component_index
. One can think of
these tables as a manually maintained index.
The following triggers keep the group_*_index
tables up to date:
membership_rels_in_tr
Is executed when a new group/member relationship is created (an insert on
membership_rels
)membership_rels_del_tr
Is executed when a group/member relationship is deleted (a delete on
membership_rels
)composition_rels_in_tr
Is executed when a new group/component relationship is created (an insert on
composition_rels
)composition_rels_del_tr
Is executed when a group/component relationship is deleted (a delete on
composition_rels
)
The data model provides the following views onto the
group_member_index
and
group_component_index
tables.
No code outside of Groups System should modify the group_*_index
tables.
group_member_map
A mapping of a party to the groups the party is a member of; this mapping includes the type of relationship by including the appropriate
rel_id
from themembership_rels
table.group_approved_member_map
A mapping of a party to the groups the party is an approved member of (
member_state
is 'approved'); this mapping includes the type of relationship by including the appropriaterel_id
from themembership_rels
table.group_distinct_member_map
A person may appear in the group member map multiple times, for example, by being a member of two different groups that are both components of a third group. This view is strictly a mapping of approved members to groups.
group_component_map
A mapping of a group Gto the set of groups {Gi } group G is a component of; this mapping includes the type of relationship by including the appropriate
rel_id
from thecomposition_rels
table.party_member_map
A mapping of a party P to the set of parties {Pi } party P is a member of.
party_approved_member_map
A mapping of a party P to the set of parties {Pi } party P is an approved member of.
The API consists of tables and views and PL/SQL functions.
The group_types
table is
used to create new types of groups.
The group_member_map
,
group_approved_member_map
,
group_distinct_member_map
,
group_component_map
,
party_member_map
, and
party_approved_member_map
views
are used to query group membership and composition.
Person
person.new
creates a new
person and returns the person_id
. The function must be given the
full name of the person in two pieces: first_names
and last_name
. All other fields are optional
and default to null except for object_type
which defaults to person and
creation_date
which defaults to
sysdate
. The interface for this
function is:
function person.new ( person_id persons.person_id%TYPE, object_type acs_objects.object_type%TYPE, creation_date acs_objects.creation_date%TYPE, creation_user acs_objects.creation_user%TYPE, creation_ip acs_objects.creation_ip%TYPE, email parties.email%TYPE, url parties.url%TYPE, first_names persons.first_names%TYPE, last_name persons.last_name%TYPE ) return persons.person_id%TYPE;
person.delete
deletes the
person whose person_id
is
passed to it. The interface for this procedure is:
procedure person.delete ( person_id persons.person_id%TYPE );
person.name
returns the name
of the person whose person_id
is passed to it. The interface for this function is:
function person.name ( person_id persons.person_id%TYPE ) return varchar;
User
acs_user.new
creates a new
user and returns the user_id
.
The function must be given the user's email address and the
full name of the user in two pieces: first_names
and last_name
. All other fields are optional.
The interface for this function is:
function acs_user.new ( user_id users.user_id%TYPE, object_type acs_objects.object_type%TYPE, creation_date acs_objects.creation_date%TYPE, creation_user acs_objects.creation_user%TYPE, creation_ip acs_objects.creation_ip%TYPE, email parties.email%TYPE, url parties.url%TYPE, first_names persons.first_names%TYPE, last_name persons.last_name%TYPE password users.password%TYPE, salt users.salt%TYPE, password_question users.password_question%TYPE, password_answer users.password_answer%TYPE, screen_name users.screen_name%TYPE, email_verified_p users.email_verified_p%TYPE ) return users.user_id%TYPE;
acs_user.delete
deletes the
user whose user_id
is passed to
it. The interface for this procedure is:
procedure acs_user.delete ( user_id users.user_id%TYPE );
Use the procedures acs_user.approve_email
and acs_user.unapprove_email
to specify whether
the user's email address is valid. The interface for these
procedures are:
procedure acs_user.approve_email ( user_id users.user_id%TYPE ); procedure acs_user.unapprove_email ( user_id users.user_id%TYPE );
Group
acs_group.new
creates a new
group and returns the group_id
.
All fields are optional and default to null except for object_type
which defaults to
'group', creation_date
which defaults to sysdate
, and
group_name
which is required.
The interface for this function is:
function acs_group.new ( group_id groups.group_id%TYPE, object_type acs_objects.object_type%TYPE, creation_date acs_objects.creation_date%TYPE, creation_user acs_objects.creation_user%TYPE, creation_ip acs_objects.creation_ip%TYPE, email parties.email%TYPE, url parties.url%TYPE, group_name groups.group_name%TYPE ) return groups.group_id%TYPE;
acs_group.name
returns the
name of the group whose group_id
is passed to it. The interface for
this function is:
function acs_group.name ( group_id groups.group_id%TYPE ) return varchar;
acs_group.member_p
returns
't' if the specified party is a member of the specified
group. Returns 'f' otherwise. The interface for this
function is:
function acs_group.member_p ( group_id groups.group_id%TYPE, party_id parties.party_id%TYPE, ) return char;
Membership Relationship
membership_rel.new
creates a
new membership relationship type between two parties and returns
the relationship type's rel_id
. All fields are optional and default
to null except for rel_type
which defaults to membership_rel. The interface for this function
is:
function membership_rel.new ( rel_id membership_rels.rel_id%TYPE, rel_type acs_rels.rel_type%TYPE, object_id_one acs_rels.object_id_one%TYPE, object_id_two acs_rels.object_id_two%TYPE, member_state membership_rels.member_state%TYPE, creation_user acs_objects.creation_user%TYPE, creation_ip acs_objects.creation_ip%TYPE, ) return membership_rels.rel_id%TYPE;
membership_rel.ban
sets the
member_state
of the given
rel_id
to 'banned'. The
interface for this procedure is:
procedure membership_rel.ban ( rel_id membership_rels.rel_id%TYPE );
membership_rel.approve
sets
the member_state
of the given
rel_id
to 'approved'.
The interface for this procedure is:
procedure membership_rel.approve ( rel_id membership_rels.rel_id%TYPE );
membership_rel.reject
sets
the member_state
of the given
rel_id
to 'rejected. The
interface for this procedure is:
procedure membership_rel.reject ( rel_id membership_rels.rel_id%TYPE );
membership_rel.unapprove
sets the member_state
of the
given rel_id
to an empty string
''. The interface for this procedure is:
procedure membership_rel.unapprove ( rel_id membership_rels.rel_id%TYPE );
membership_rel.deleted
sets
the member_state
of the given
rel_id
to 'deleted'.
The interface for this procedure is:
procedure membership_rel.deleted ( rel_id membership_rels.rel_id%TYPE );
membership_rel.delete
deletes the given rel_id
. The
interface for this procedure is:
procedure membership_rel.delete ( rel_id membership_rels.rel_id%TYPE );
Composition Relationship
composition_rel.new
creates
a new composition relationship type and returns the
relationship's rel_id
. All
fields are optional and default to null except for rel_type
which defaults to composition_rel.
The interface for this function is:
function membership_rel.new ( rel_id composition_rels.rel_id%TYPE, rel_type acs_rels.rel_type%TYPE, object_id_one acs_rels.object_id_one%TYPE, object_id_two acs_rels.object_id_two%TYPE, creation_user acs_objects.creation_user%TYPE, creation_ip acs_objects.creation_ip%TYPE, ) return composition_rels.rel_id%TYPE;
composition_rel.delete
deletes the given rel_id
. The
interface for this procedure is:
procedure membership_rel.delete ( rel_id composition_rels.rel_id%TYPE );
- System creator
- System owner
- Documentation author
Mark Thomas
Document Revision # | Action Taken, Notes | When? | By Whom? |
---|---|---|---|
0.1 | Creation | 08/22/2000 | Rafael H. Schloming |
0.2 | Initial Revision | 08/30/2000 | Mark Thomas |
0.3 | Additional revisions; tried to clarify membership/compostion | 09/08/2000 | Mark Thomas |