The knowledge library is used for holding data for the two object types "Project description" and "Demand description". Demand descriptions can't be created without a Project description. When a Project description is created, the author can add an arbitrary number of Demand descriptions. To make a Project description public, at least one Demand description must exist and be made public at the same time since it doesn't make sense to present an understaffed project without presenting any open positions. After publishing of a Project, the Demands can be published an published seperately, but if the last public Demand gets private so does the Project. The permissions for the Demands can be set seperately. Each Project and each of its Demands has an end of publication date. If this end date is reached, the public Project/Demand will be made private automatically and the author will get an email notice. If a Project get private so do all its Demands. If the last public Demand of a Project gets private so does the Project itself. If a Project gets deleted, so do all its Demands. If the last public Demand of a Project gets deleted, the Project itself becomes private again.
Demand applications
Besides being able to browse Projects and Demands belonging to them, users can also Apply for a public Demand: They have to fill out an application form after being reminded that they have to make sure that their supervisor is aware of and complies with the application. The application can be saved to be completed later, in which case the list of the unfinished applications can be placed on the community portal. Users can attach files to the application that will be sent as email attachments.
Smaller features
In addition to these bigger features, People The relationship between Projects and Demands is something like what already
got implemented with the abstract data type Child Object, but not quite
the same. Therefore, a new abstract data type Nephew Object got added,
the relationship is called an uncle-nephew relationship to express that the
nephew's public status and permissions are not as strongly coupled to the uncle
object than it's with parent and child objects (child objects always have
the same permissions and public status as the parent object). On the other hand,
this naming is somewhat misleading since the nephew object is in some way
more dependend on its nephew objects than the parent object is on its child
objects (an uncle object can only be made public when at least one nephew object
is made public at the same time and the uncle object will loose the public
status if there's no longer at least one public nephew object).
To achieve this, the code for the parent-child relationships got copied and
slightly modified for uncle-nephew relationships. In total, the following procs
needed to be changed or newly added:
User Pages:
To specify whether users can create standalone objects of an object type - we
won't allow users to create Demand descriptions without linked Project description:
To have the hierarchy of parent-child and uncle-nephew relationships in the
easily accessed table km_flat_object_hierarchy, the table and triggers had to be updated:
Demand applications
The abstract data type nephew_object got two presentation types: Standard and
Application Form. The latter means, that on the page of a public nephew object there
will be button that will lead to the application form which can be found in the
resource-application* files.
The application form itself got implemented as a static form according to the Spec. In
the contact data textarea all answers to registration questions are provided as
default. Three different kind of categories are used in the application form. Since
they didn't get implemented as static categories (static selectboxes), an admin
has to assign category trees to the three magic application category objects that
are created for every library instance. The data the user enters gets inserted
into the database so that the user can come back, edit his application data and
finally send his application - he will receive a copy. To remind the user of
unfinished applications, a portal element showing these got added. The email
itself has to be MIME encoded because of the files that have to be send as email
attachments.
Tcl Procs:
Table to hold most of the users data in an application:
This table holds all Roles/Functions of the applicant - these are category ids:
In the application form are three different kinds of categories used:
Roles/Functions of the applicant, languages spoken and the proficiency in the
language. This table holds the object ids of the three magic objects for each
library package instance used to map category trees to be used in the application form:
This table holds all email-attachments to the application as blobs:
Creation of new object types for applications and magic tree objects:
Smaller features
#10: new date input widget
To support the displaying of a valid range of years in the selectboxes,
two columns needed to be added to the table sn_questions: year_from and
year_to describe the range of years (in respect to the year default) that
should be displayed in the year selectbox. Admin pages and the date widget
page dealing with the displaying of date entry fields needed to be updated to
reflect these changes and to deal with the additional presentation type.
User Pages:
Added two columns to have a varying range of years in the year selectbox.
Values are interpreted starting from the provided default.
#30: Input field if category 'Other' is selected
This was implemented with an extra presentation type of the category
questions 'other_category'. The content of the extra input-field is
stored in sn_content like any other text input.
Tcl Procs:
#40: Filtering of objects by start and end date
We added two columns to sn_object_types and added support for marking
date questions as start_date or end_date questions in the admin pages
(like public_until). The filter itself has been implemented in the user pages
- browse-one-type and browse-one-category.
Datamodel:
#70: Sweeper proc to check public status of uncle and nephew objects
That was done with just a tcl proc that gets scheduled to be run
every night or so. Email-templates are needed since the object author
needs to be notified if an object had to be made private. Six different
cases had to be considered: Single objects, parent objects, child objects
that are effected by the parent, nephew objects, uncle objects effected by
nephews, other descendants of uncles effected by nephews.
Tcl Procs:
Since the relationship between Projects and Demands got implemented
in the new abstract data type 'nephew_object' of the linking question,
the uncle object will be shown in object-view of a nephew - the same way
as i.e. 'Modified Date'.
User Pages:
#130: Contact data of the Project is shown as default in Demands
To be a little bit more general with this feature, we added a column
defaults_question_id to sn_questions which needs to be checked during editing
of an unanswered question. The admin pages will make sure that this column
points to a question_id of the same abstract_data_type.
Tcl Procs:
Maximum number of categories the user can select in a category question
We added a column max_categories to the sn_questions table which can be
changed with the admin pages.
Tcl Procs:
There might be a seperate table to store the headings since adding these
as a special abstract data type will result in special treatments in too
many places. The downside of a seperate table will be that we'll need to
join with this table for one-question-edit and possibly even for object-view
which is pretty bad. Maybe there'll be a better solutuon falling from the sky.
Archive, Sweeper, Copy
Tcl Procs:
Current status
All requirements already got implemented except for #20, #30, #40 and #130 on the list of
the smaller features. Also, the sweeper procedure that sets outdated public Projects
and Demands to private still needs to be written.
Implementation
Project and Demand descriptions
In addition to that, some user pages and some admin pages needed to be changed to support
the new abstract data type and the People
Admin Pages:
Datamodel:
alter table sn_object_types add (
-- to be able to prevent standalone objects of this object type
-- (meaning objects which didnt created as child or nephew)
-- needed this for people
alter table km_flat_object_hierarchy add (
-- we still have the unique (parent,child) constraint on this table
-- because we won't allow for two objects to be linked directly or
-- indirectly both as parent_child and uncle_nephew. That means that
-- one object tree in this hierarchy either only has parent_child links
-- or uncle_nephew links.
link_type
varchar2(100) default 'parent_child'
);
create or replace trigger km_flat_object_hier_insert_tr
before insert on sn_links for each row
begin
if :new.link_type = 'parent_child' or :new.link_type = 'uncle_nephew' then
insert into km_flat_object_hierarchy (object_hierarchy_id, parent, child, distance, link_type)
values (acs_object_id_seq.nextval, :new.object_id_a, :new.object_id_b, 1, :new.link_type);
insert into km_flat_object_hierarchy (object_hierarchy_id, parent, child, distance, link_type)
select acs_object_id_seq.nextval, parent, :new.object_id_b, distance+1, :new.link_type
from km_flat_object_hierarchy
where child = :new.object_id_a;
end if;
end ;
/
show errors
create or replace trigger km_flat_object_hier_delete_tr
before delete on sn_links for each row
begin
if :old.link_type = 'parent_child' or :old.link_type = 'uncle_nephew' then
delete from km_flat_object_hierarchy
where child = :old.object_id_b;
end if;
end ;
/
show errors
User Pages:
Admin Pages:
Datamodel:
create table psn_res_applications (
application_id integer primary key,
object_id constraint psn_res_app_object_id_fk
references sn_objects on delete cascade,
resource_req_id constraint psn_res_app_res_req_id_fk
references sn_objects on delete cascade,
user_id constraint psn_res_app_user_id_fk
references users,
creation_date date default sysdate,
application_date date default null,
sent_p char(1) default 'f'
constraint psn_res_app_sent_p_ck
check (sent_p in ('t','f')),
recipient varchar2(4000),
subject varchar2(1000),
contact_data varchar2(4000),
nationality varchar2(4000),
manager_email varchar2(4000),
working_area varchar2(4000),
role_other varchar2(1000),
first_language varchar2(1000),
second_language_id integer,
third_language_id integer,
first_language_prof_id integer,
second_language_prof_id integer,
third_language_prof_id integer,
other_language varchar2(4000),
from_date date,
to_date date,
strengths varchar2(4000),
leadership varchar2(4000),
intercultural varchar2(4000),
comments varchar2(4000),
conditions varchar2(4000)
);
create index psn_res_app_object_idx on psn_res_applications (object_id);
create index psn_res_app_res_idx on psn_res_applications (resource_req_id);
create index psn_res_app_user_idx on psn_res_applications (user_id);
create table psn_res_application_roles (
application_id integer
constraint psn_res_app_roles_app_fk
references psn_res_applications on delete cascade,
role_id integer,
primary key (application_id, role_id)
);
create table psn_category_trees (
package_id integer primary key,
role_magic_id constraint psn_cat_trees_role_fk
references acs_objects (object_id) on delete set null,
language_magic_id constraint psn_cat_trees_lang_fk
references acs_objects (object_id) on delete set null,
proficiency_magic_id constraint psn_cat_trees_prof_fk
references acs_objects (object_id) on delete set null
);
create table psn_attachments (
attachment_id integer primary key,
application_id constraint psn_attach_appl_id_fk
references psn_res_applications on delete cascade,
title varchar2(1000),
mime_type varchar2(200) default 'text/plain',
filename varchar2(200),
attachment blob default empty_blob()
);
create index psn_attach_appl_id_idx on psn_attachments(application_id);
create sequence psn_attachment_id_seq start with 1;
begin
acs_object_type.create_type (
supertype => 'acs_object',
object_type => 'psn_tree_object',
pretty_name => 'Demand Application Form',
pretty_plural => 'Demand Application Forms',
table_name => 'PSN_CATEGORY_TREES'
);
end;
/
show errors
begin
acs_object_type.create_type (
supertype => 'acs_object',
object_type => 'psn_application',
pretty_name => 'Demand Application',
pretty_plural => 'Demand Applications',
table_name => 'PSN_RES_APPLICATIONS',
id_column => 'APPLICATION_ID'
);
end;
/
show errors
Admin Pages:
Datamodel:
alter table sn_questions add (
-- this is only for abstract_data_type date to specify what years
-- should be in the select box in respect to the default date.
year_from
integer default -5,
year_to
integer default 5
);
User Pages:
Admin Pages:
Pl/Sql Packages:
alter table sn_object_types add (
-- these two links to date questions have been added for
-- people
#100: show linked Project on Demand page
User Pages:
Admin Pages:
Datamodel:
alter table sn_questions add (
defaults_question_id
integer default null
constraint sn_questions_defaults_qu_id_fk
references sn_questions(question_id)
);
Admin Pages:
Datamodel:
alter table sn_questions add (
max_categories
integer default null
);
Future Implementation
#20: headings to group related questions
User Pages:
Admin Pages:
Pl/Sql Packages:
Datamodel:
alter table sn_object_types add (
-- can objects of this type be archived?
archive_p
char(1)
default 't'
constraint sn_object_types_archive_p_nn not null,
constraint sn_object_types_archive_p_ck
check (archive_p in ('t', 'f')),
-- are you allowed to copy objects of this type?
copy_p
char(1)
default 't'
constraint sn_object_types_copy_p_nn not null,
constraint sn_object_types_copy_p_ck
check (copy_p in ('t', 'f')),
-- which sweeper should be checking for old objects?
-- outdated: objects haven't been modified for a certain time
-- expired: public_until date has been exceeded
sweeper
varchar(10)
default 'none'
constraint sn_object_types_sweeper_nn not null,
constraint sn_object_types_sweeper_ck
check (sweeper in ('none','outdated','expired')),
-- action to be performed on the objects by the sweeper
sweeper_action
varchar(10)
default 'private'
constraint sn_object_types_sw_action_nn not null,
constraint sn_object_types_sw_action_ck
check (sweeper_action in ('private','archive')),
-- if >0 a warning email will be sent if the object is outdated/expired
-- specifies the amount of days the action should be performed after
-- the warning email
sweeper_warning_time
integer
default 0
constraint sn_object_types_warning_nn not null,
-- specified the amount of days after which an unchanged objects is
-- regarded as outdated
sweeper_outdated_time
integer
default 30
);
alter table sn_objects add (
-- date the object got archived
archiving_date
date
default null,
-- date a warning email got sent that object is outdated
outdated_warning_date
date
default null,
-- date a warning email got sent that object is expired (public_until)
expired_warning_date
date
default null
);
create index sn_objects_archived_p_ix on sn_objects (archived_p);
create index sn_objects_o_warning_date_ix on sn_objects (outdated_warning_date);
create index sn_objects_e_warning_date_ix on sn_objects (expired_warning_date);
create table sn_object_archive_reasons (
object_id
integer
constraint sn_object_archive_re_obj_id_fk
references sn_objects(object_id),
reason_for_archiving
varchar(4000),
archived_on
date default sysdate,
constraint sn_object_archive_reasons_pk
primary key (object_id, archived_on)
);
alter table saved_searches add (
l_i_a_p varchar2(4000)
);
create table km_sweeper (
user_id
integer
constraint km_sweeper_user_id_fk
references users,
package_id
integer
constraint km_sweeper_package_id_fk
references apm_packages,
object_type_id
integer
constraint km_sweeper_obj_type_id_fk
references sn_object_types,
object_id
integer
constraint km_sweeper_object_id_fk
references sn_objects,
object_name
varchar2(4000),
content
varchar2(4000),
primary key (user_id, object_id)
);
timo@arsdigita.com