Index: openacs-4/packages/contacts/sql/postgresql/contacts-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/contacts/sql/postgresql/contacts-create.sql,v diff -u -N -r1.2 -r1.2.2.1 --- openacs-4/packages/contacts/sql/postgresql/contacts-create.sql 13 Jan 2005 13:57:00 -0000 1.2 +++ openacs-4/packages/contacts/sql/postgresql/contacts-create.sql 30 Nov 2005 15:31:01 -0000 1.2.2.1 @@ -7,70 +7,129 @@ -- -create table contact_archives ( - party_id integer - constraint contact_archives_party_id_fk references parties(party_id) - constraint contact_archives_party_id_pk primary key +-- all contacts are parties. we are making parties content repository items, +-- so we need party revisions + +create table contact_party_revisions ( + party_revision_id integer + constraint contact_party_revisions_id_fk references cr_revisions(revision_id) + constraint contact_party_revisions_id_pk primary key ); --- this is used to create forms for orgs and persons (since they need object_ids) to map to. -create table contact_object_types ( - object_id integer not null - constraint contact_object_type_object_id_fk references acs_objects(object_id) - constraint contact_object_type_object_id_pk primary key, - object_type varchar(100) not null, - UNIQUE(object_type) +-- create the content type +select content_type__create_type ( + 'contact_party_revision', -- content_type + 'content_revision', -- supertype + '#contacts.Party_Revision#', -- pretty_name + '#contacts.Party_Revisions#', -- pretty_plural + 'contact_party_revisions', -- table_name + 'party_revision_id', -- id_column + 'contact_party_revision__name' -- name_method ); +-- i need to create the content_folder contact_parties, it is not bound to a package_id +-- since the package_id is not used by contacts - which uses the groups system for package +-- maintenance. +-- contrary to "most" packages that use the content repository, we will not be +-- specifying new item_ids. Since all contacts are parties, we are going to set +-- all parties that use the contacts system to have an content_items(item_id) +-- that is equal to the parties(party_id). -\i contacts-package-create.sql +-- since all contacts are parties we already have good "group" mechanisms built into the core +-- however, we do not want people to view all groups at once, so the calendar instance +-- administrator can selectively give certain calendar instances access to certain groups +-- +-- by default each new contacts instance will be given access to its subsite's group. For +-- example: all users on a default openacs install are memembers of the "Main Site Members" +-- group. If a calendar instance were mounted under that subsite, all "Main Site Members" +-- would be accessible to that calendar instance. +-- +-- just as is the case with the calendar package all "users" of contacts (i.e. users that +-- have write access to at least one contacts instance will be assigned a private calendar) +-- +-- which calendars can be viewed by which calendar instance is handled via parameters - unlike +-- many packages. This allows for more flexable instance and sharing management - where +-- one instances shared calendar can also be accesible to another instance. -create view contacts as - select organization_id as party_id, - 'organization' as object_type, - name, - name as organization_name, - legal_name, - reg_number, - null as first_names, - null as last_name, - name as sort_first_names, - name as sort_last_name, - contact__party_email(organization_id) as email, - contact__party_url(organization_id) as url, - 'f' as user_p, - contact__status(organization_id) as status - from organizations -union - select person_id as party_id, - 'person' as object_type, - first_names || ' ' || last_name as name, - null as organization_name, - null as legal_name, - null as reg_number, - first_names as first_names, - last_name as last_name, - first_names || ' ' || last_name as sort_first_names, - last_name || ', ' || first_names as sort_last_name, - contact__party_email(person_id) as email, - contact__party_url(person_id) as url, - contact__person_is_user_p(person_id) as user_p, - contact__status(person_id) as status - from persons - where person_id != '0' +create table contact_groups ( + group_id integer + constraint contact_groups_id_fk references groups(group_id) + constraint contact_groups_id_nn not null, + default_p boolean default 'f' + constraint contact_groups_default_p_nn not null, + package_id integer + constraint contact_groups_package_id_fk references apm_packages(package_id) + constraint contact_groups_package_id_nn not null, + unique(group_id,package_id) +); + +create table contact_groups_allowed_rels ( + group_id integer + constraint contact_groups_id_fk references groups(group_id) + constraint contact_groups_id_nn not null, + rel_type varchar(100) + constraint contact_groups_allowed_rels_type_fk references acs_rel_types(rel_type), + package_id integer + constraint contact_groups_package_id_fk references apm_packages(package_id) + constraint contact_groups_package_id_nn not null, + unique(group_id,package_id) +); + + +create table contact_signatures ( + signature_id integer + constraint contact_signatures_id_pk primary key, + title varchar(255) + constraint contact_signatures_title_nn not null, + signature varchar(1000) + constraint contact_signatures_signature_nn not null, + default_p boolean default 'f' + constraint contact_signatures_default_p_nn not null, + party_id integer + constraint contact_signatures_party_id_fk references parties(party_id) + constraint contact_signatures_party_id_nn not null, + unique(party_id,title,signature) +); + +-- this view greatly simplifies getting available roles for various contact types +create view contact_rel_types as +( select rel_type, + object_type_one as primary_object_type, + role_one as primary_role, + object_type_two as secondary_object_type, + role_two as secondary_role + from acs_rel_types + where rel_type in ( select object_type from acs_object_types where supertype = 'contact_rel') +) +UNION +( select rel_type, + object_type_two as primary_object_type, + role_two as primary_role, + object_type_one as secondary_object_type, + role_one as secondary_role + from acs_rel_types + where rel_type in ( select object_type from acs_object_types where supertype = 'contact_rel') +) ; +create table contact_rels ( + rel_id integer + constraint contact_rels_rel_id_fk references acs_rels(rel_id) on delete cascade + constraint contact_rels_rel_id_pk primary key +); --- The attribute structure. -\i attributes-create.sql -\i attributes-package-create.sql +create table organization_rels ( + rel_id integer + constraint organization_rels_rel_id_fk references membership_rels(rel_id) on delete cascade + constraint organization_rels_rel_id_pk primary key +); -\i attributes-populate.sql +\i contacts-package-create.sql +\i contacts-search-create.sql +\i contacts-messages-create.sql -\i views-create.sql -\i telecom-number-missing-plsql.sql