The table sn_objects holds data the knowledge objects in the system. The one_line_description for knowledge objects is taken with a trigger on sn_content- this column always holds the answer to the question that has been defined as the "short_description" for this object type. The column overview holds the long_description for the object type. The column public_until holds the value from sn_content that answers the question that has been defined as the public_until date. The public_until date holds the date on which this object is no longer visible in the system except for the owner and its group. This is a bit confusing, since you would think that that is what the expiration_date column should hold. In fact, the expiration_date shown when this object has been deleted. So,the object is deleted and visible to no one when the expiration_date is less than the curren date. The user_checkoff_date (should be called "publication_date") is the date that the user has published the object, i.e. made it visible to the public. public_p holds the information whether the object is publicly visible or not, archived_p whether the object is archived or not (not yet implemented). access_total and access_month are in this table for performance reasons: these counts are shown on almost every page, so we decided to store them in this table as well.
create table sn_objects (
object_id
integer
constraint sn_objects_id_pk
primary key,
object_type_id
integer
constraint sn_objects_object_type_id_fk
references sn_object_types (object_type_id),
-- objects should be subsite-aware as well
context_id
constraint sn_objects_context_id_fk
references apm_packages (package_id)
on delete cascade,
one_line_description
varchar(4000),
overview
varchar(4000),
overview_html_p
char(1)
constraint sn_objects_overview_html_p_ck
check (overview_html_p in ('t','f')),
creation_date
date,
original_author_id
integer
constraint sn_objects_orig_author_id_fk
references users (user_id),
last_modified
date,
last_modifying_user_id
integer
constraint sn_objects_last_mod_user_id_fk
references users (user_id),
user_checkoff_date date,
expiration_date
date default '9999-12-31'
constraint sn_objects_expiration_date_nn not null,
public_until date,
public_p
char(1) default 'f'
constraint sn_objects_public_p_ck
check (public_p in ('t','f')),
archived_p
char(1) default 'f'
constraint sn_objects_archived_p_ck
check (archived_p in ('t','f')),
access_total integer default 0,
access_month integer default 0
);
And we define sn_object as the acs_object_type:
begin
acs_object_type.create_type (
supertype => 'acs_object',
object_type => 'sn_object',
pretty_name => 'Library Object',
pretty_plural => 'Library Objects',
table_name => 'SN_OBJECTS',
id_column => 'OBJECT_ID'
); end;
/
show errors
Some indexes for performance:
create index sn_objects_expiration_date_ix on sn_objects (expiration_date); create index sn_objects_public_until_ix on sn_objects (public_until); create index sn_objects_checkoff_date_ix on sn_objects (user_checkoff_date); create index sn_objects_last_modified_ix on sn_objects (last_modified); create index sn_objects_new_stuff_ix on sn_objects (last_modified, user_checkoff_date, expiration_date, object_type_id); create index sn_objects_type_id_ix on sn_objects (object_type_id); create index sn_objects_browse_ix on sn_objects (object_type_id, context_id, expiration_date, last_modified, public_p); create index sn_objects_one_line_desc_ix on sn_objects (substr(upper(one_line_description),1,1));
The questions table holds all of the data about the questions that can be asked about knowledge objects.
For abstract data type category, category_id points to the root node of the category tree associated with this question. XXXTomislav For questions having the abstract data type object_object_link and child_object, target_object_type_id defines the object type the user may link to. Otherwise it has no meaning and does not map this question to an object type (that is in sn_question_object_type_map). The entry_explanation is the text that you see under the pretty_name in the display of a question. The column help_text is unused at this time, but can be used to link to a pop-up help text for any given question. references_question_id references the question to which an object-object link points to.
create table sn_questions (
question_id
integer
constraint sn_questions_question_id_fk
references acs_objects (object_id)
constraint sn_questions_question_id_pk
primary key,
pretty_name
varchar(4000),
abstract_data_type
varchar(50)
constraint sn_questions_abstract_data_nn not null,
presentation_type
varchar(100)
constraint sn_questions_presentation_t_nn not null,
-- for all questions which display lists
order_by
varchar(100),
-- default for input forms
default_value
varchar(4000),
entry_explanation
varchar(4000),
tag_width
integer,
tag_height
integer,
help_text
varchar(4000),
-- for object_link: which type to link to
target_object_type_id
integer,
-- that is merged in from a change I (and Carsten ;) already did in
-- 7.1
references_question_id
integer
constraint sn_questions_referenc_qu_id_fk
references sn_questions(question_id),
-- for question of data type category
tree_id
integer
constraint sn_questions_tree_ref references generic_trees,
node_id
integer
constraint sn_questions_node_ref references sw_category_dim,
-- This is only used during data migration from 7.
category_id
integer,
-- 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
);
begin
acs_object_type.create_type (
supertype => 'acs_object',
object_type => 'sn_question',
pretty_name => 'Question',
pretty_plural => 'Questions',
table_name => 'SN_QUESTIONS',
id_column => 'QUESTION_ID'
);
end;
/
show errors
the object name
The table sn_object_types defines the possible types of knowledge objects. The column long_description defines the overview. public_until can point to a question of abstract data type date, which will determine how long the object is publically visible (if public_p is set). If browse_p is true, than this object type is browsable throughout the system. Sometimes this is not desirable, as in the case of child_objects. In that case, browse_p is set to false and the object_type will only show up in a parent-child relationship to other object types. The default_age_filter sets the default filter in the browse pages for displaying this object type. Although the user can browse objects with any number of different age filters, it is nice for the admins to be able to decide what age is most appropriate as the default.
create table sn_object_types (
object_type_id
integer
constraint sn_object_types_pk
primary key,
context_id
constraint sn_object_types_context_id_fk
references apm_packages (package_id)
on delete cascade,
short_name
varchar(100),
pretty_name
varchar(100)
constraint sn_object_types_pretty_name_nn not null,
pretty_plural
varchar(100),
-- filename for the graphic icon. This is always
-- $object_type_id.extension
graphic
varchar(200),
graphic_p
char(1)
default 'f'
constraint sn_object_types_graphic_p_nn not null,
constraint sn_object_types_graphic_p_ck
check (graphic_p in ('t', 'f')),
deleted_p
char(1)
default 'f'
constraint sn_object_types_deleted_p_nn not null,
constraint sn_object_types_deleted_p_ck
check (deleted_p in ('t', 'f')),
browse_p
char(1)
default 't'
constraint sn_object_types_browse_p_nn not null,
constraint sn_object_types_browse_p_ck
check (browse_p in ('t', 'f')),
-- If the object type is not public, consult sn_object_type_access
-- to determine if a user may access this type.
public_p
char(1) default 't'
constraint sn_object_types_public_p_nn not null,
constraint sn_object_types_public_p_ck
check (public_p in ('t','f')),
-- to be able to prevent standalone objects of this object type
-- (meaning objects which didnt created as child or nephew)
-- needed this for people - demand descriptions
create_p
char(1) default 't'
constraint sn_object_types_create_p_nn not null,
constraint sn_object_types_create_p_ck
check (create_p in ('t','f')),
sort_key
integer,
-- The admin can define which question is the name, overview or
-- public_until date for this object type:
short_description
integer
constraint sn_object_typ_short_desc_id_fk
references sn_questions (question_id),
long_description
integer
constraint sn_object_type_long_desc_id_fk
references sn_questions (question_id),
public_until
integer
constraint sn_object_t_public_until_id_fk
references sn_questions (question_id),
-- that is merged in from a change I (and Carsten ;) already did in
-- 7.1
linked_question_id
integer
constraint sn_object_t_linked_quest_id_fk
references sn_questions(question_id),
default_age_filter
integer default 365
);
begin
acs_object_type.create_type (
supertype => 'acs_object',
object_type => 'sn_object_type',
pretty_name => 'Object Type',
pretty_plural => 'Object Types',
table_name => 'SN_OBJECT_TYPES',
id_column => 'OBJECT_TYPE_ID'
);
end;
/
show errors
-- We can only add this foreign key after creating the object_type_table.
alter table sn_questions
add constraint sn_questions_object_type_id_fk
foreign key (target_object_type_id)
references sn_object_types(object_type_id);
alter table sn_types_map_short_name
add constraint sn_types_map_object_type_id
foreign key (object_type_id)
references sn_object_types(object_type_id);
The table sn_types_map_short_name defines the name of an object. This data has to be stored in another table, because it is a 1:m relationship: an object type can have composite short names.It has to be maps short
create table sn_types_map_short_name (
object_type_id
integer,
short_description
integer
constraint sn_types_map_short_desc_id_fk
references sn_questions (question_id),
position
integer,
constraint sn_types_map_short_name_pk
primary key (object_type_id, short_description)
);
This defines the set of questions associated with a given object type. Questions can be shared among object types and grouped together under one parent_question_id for composite questions. We don't share questions at this point for any object types, but it is possible. The column form_number is something of a relic- if it is set to 1 then the question appears on the first form. The sort key sets the order of the questions outside of the branch hierarchies.Note that the system will have problems publishing objects if an object type has any questions that are mandatory but not visible.
create table sn_question_object_type_map (
question_id
integer
constraint sn_question_otm_question_id_fk
references sn_questions (question_id),
object_type_id
integer
constraint sn_question_otm_object_t_id_fk
references sn_object_types (object_type_id),
sort_key
integer,
form_number
integer,
mandatory_p
char(1)
constraint sn_question_otm_mandatory_p_ck
check (mandatory_p in ('t','f')),
question_state
varchar(100)
constraint sn_question_otm_question_st_ck
check(question_state in
('active','deprecated','read-only','invisible')),
-- used for composite and branches
-- -50 is the magic object from acs_magic_objects where name='km_dummy_object'. Don't change it!
parent_question_id
integer
default -50
constraint sn_question_otm_parent_q_id_nn not null
constraint sn_question_otm_parent_q_id_fk
references sn_questions (question_id),
-- t if this question itself is a branch, NOT if this question LEADS to branches
branch_p
char(1)
constraint sn_question_otm_branch_p_ck
check (branch_p in ('t','f')),
-- applies to the question as the parent node of a branch
branch_operator
varchar(4000),
-- the answers apply to the child nodes
branch_answer
varchar(4000),
-- can reference categories or sn_answer_options
branch_answer_foreign_key
integer,
default_branch
integer
constraint sn_question_otm_default_bra_fk
references sn_questions (question_id),
constraint sn_question_object_type_map_pk
primary key (question_id, object_type_id)
);
The above table, sn_question_object_type_map, has a column parent_question_id. If the abstract data type of this question_id is composite, then the children are not branches. They should not have branch_p='t' and the row for the question_id that is equal to the parent_question_id should not have a default branch. Composite questions can only go one level deep- one parent and n children. No questions in a composite relationship should have any branch column data whatsoever.
If a question leads to branches, then it will appear as the parent_question_id for other rows in sn_question_object_type_map. It should only have abstract data types of integer, option or category. It must have a question_id given as default branch. This question may itself be a branch, in which case branch_p='t', branch_operator and branch_answer/branch_answer_foreign_key will also be answered, but this is not necessarily so. This means that the field branch_p shows if a question is itself a branch and not if it leads to a branch. For the cases of options and categories, we use the ids from sn_answer_options or sn_categories as the answer in the column branch_answer_foreign_key (could be eliminated by also storing foreign key values in the branch_answer column). Integer answers and, perhaps in a future version, text answers are stored in branch_answer. We have not implemented branches for abstract data type text for this release, but it's doable.
Linking uses the site-wide linking service described somewhere else. Thise service supports only object-object linking though, so we had to add a table to the library datamodel to support linking on the question level:
create table sn_question_link_map (
link_id
integer
constraint sn_question_link_ma_link_id_fk
references sn_links (link_id)
on delete cascade
constraint sn_question_link_map_pk
primary key,
question_id
integer
constraint sn_question_lin_question_id_fk
references sn_questions (question_id)
constraint sn_question_lin_question_id_nn
not null
);
create index sn_question_link_ma_q_id_ix on sn_question_link_map (question_id);
This table stores the content of the one-to-one questions having abstract data type text for any given object. It also contains the abstract data types file, date and integer. The questions_id shows which question the content answers. If a single question has more than one answer, then that data belongs in the multiple-choice answers tables (sn_answer_options and sn_object_option_map) having the abstract data type option, mapped as a category in site_wide_category_map, stored as a link in sn_links (see above) or should be constructed as composite question having the abstract data type composite.
Here is where one-to-one OBJECT DATA lives:
create table sn_content (
object_id
integer
constraint sn_content_object_id_fk
references sn_objects(object_id),
question_id
integer
constraint sn_content_question_id_fk
references sn_questions(question_id),
content
clob,
html_p
char(1)
constraint sn_content_html_p_ck
check (html_p in ('t', 'f')),
constraint sn_content_pk
primary key (object_id, question_id)
);
This table holds the answers to multiple choice questions. The poorly named "answer_option" is the simply the pretty_name multiple-choice answer, the option_ids are the values in the HTML selects and checkboxes.
create table sn_answer_options (
option_id
integer
constraint sn_answer_options_option_id_pk
primary key,
question_id
integer
constraint sn_answer_options_questi_id_fk
references sn_questions(question_id),
answer_option
varchar(4000),
sort_key
integer
);
This table links multiple-choice answers to objects
create table sn_object_option_map (
object_id
integer
constraint sn_object_option_map_obj_id_fk
references sn_objects(object_id),
option_id
integer
constraint sn_object_option_map_opt_id_fk
references sn_answer_options(option_id)
);
Whenever the content of an object is changed then we store it here. We don't store changes in the categorization, linking or multiple choice questions.
create table sn_audit_table (
object_id
integer
constraint sn_audit_table_object_id_fk
references sn_objects(object_id),
question_id
integer
constraint sn_audit_table_question_id_fk
references sn_questions(question_id),
--this refers to the question being modified
last_modified
date
constraint sn_audit_table_last_modifie_nn not null,
last_modifying_user_id
integer
constraint sn_audit_table_last_mo_u_id_fk
references users (user_id),
content
varchar(4000)
);
Depending on sn_object_types.public_p, a Knowledge Object Type is either visible to all users with access to the Library instance, or only to those who have admin permission.
Depending on sn_objects.public_p, a Knowledge Object is visible for all users with access to the Library instance, or only for those who have read permission. public_p only exists for performance reasons. For public objects, read is granted to The Public, so a permission check works, although it can be skipped. For private objects, read as well as write can be granted to individual users and private user groups using the system-wide permissioning system.
We group object views by month, so the following table has a count and date column (allowing for even more flexible grouping).
create table sn_access_counts (
object_id
integer
constraint sn_access_counts_object_id_fk
references sn_objects(object_id),
access_count
integer,
access_date
date,
constraint sn_access_counts_pk
primary key (object_id,access_date)
);
There is no more special search handling for library. For a documentation of the search features see the documentation of site-wide-search.
In People This table holds all Roles/Functions of the applicant - these are categories.
In the application form are three different kinds of categories used:
Roles/Functions of the applicant, languages spoken and the proficiency in the
language. Therefore, three different category trees are needed. Since we don't
use static categories, an admin has to map these three trees to the application.
In order to do that and to distinguish between these three trees, three magic
objects have to be created to map the category trees to. This table holds the
object ids of these three magic objects for each library package instance.
This table holds all email-attachments to the application as blobs.
Creation of new object types for applications and magic tree objects.
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