Last Update: 2003-12-04 Kara
These tables are used for auditing, value checking, user validation and linking to other databases.
TABLE acelink
=============
acelink_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
tab_name Required. Uppercase.
On insert or update, check if value is in ALL_TABLES.
VARCHAR2(20)
primary_key Required.
VARCHAR2(40)
col_name Optional. Uppercase.
On insert/update, check if value is in ALL_TAB_COLUMNS.
VARCHAR2(30)
primary_key_col Required. Uppercase.
VARCHAR2(100)
ace_class Required. Coded.
On insert or update, check if value is in code table.
VARCHAR2(40)
ace_object Required.
VARCHAR2(100)
Other rules:
------------
1. Note that this table was created to link items in Oracle to ACEDB
objects. We will eventually drop this table when we are completely in
Oracle.
2. For concatenated keys, separate multiple primary keys with :: (2
colons) in the primary_key and primary_key_col columns.
3. Do not write to the delete_log table when records are deleted from
acelink.
4. While references are still in SacchDB, we need to use acelink for the
following:
row go_locus_goev table -> SacchDB paper object
row go_feat_goev table -> SacchDB paper object
go_term column in the go table -> SacchDB paper object
go_definition column in the go table -> SacchDB paper object
5. Columns tab_name, primary_key, primary_key_col, ace_class,
ace_object, col_name together are unique.
TABLE code
==========
code_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
tab_name Required. Uppercase.
On insert or update, check if value is in ALL_TABLES.
VARCHAR2(20)
col_name Required. Uppercase.
On insert/update, check if value is in ALL_TAB_COLUMNS.
VARCHAR2(30)
code_value Required.
VARCHAR2(40)
description Optional.
VARCHAR2(240)
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
Other rules:
------------
1. Tab_name, col_name and code_value together are unique.
TABLE dbuser
============
userid Required.
Primary key.
Same as UNIX login name. Uppercase.
Cannot be updated.
VARCHAR2(12)
first_name Required.
VARCHAR2(40)
last_name Required.
VARCHAR2(40)
status Required. Coded.
On insert or update, check if value is in code table.
VARCHAR2(40)
email Required.
VARCHAR2(100)
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
TABLE delete_log
================
dlog_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
tab_name Required. Uppercase.
VARCHAR2(20)
deleted_row Required.
Concatenated values of deleted row, tab-delimited.
VARCHAR2(4000)
description Optional.
Can be entered only as an update.
VARCHAR2(240)
date_modified Required.
On insert or update, set to SYSDATE
modified_by Required.
On insert or update, set to USER
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
Other Rules:
------------
1. Records in the delete_log table cannot be deleted by CURATOR role.
2. Only the description column is modifiable.
TABLE ei_tu
===========
external_id_no Required. Part of primary key.
Foreign key to external_id table.
Cascade delete.
Cannot be updated.
NUMBER(10)
template_url_no Required. Part of primary key.
Foreign key to template_url table.
Cascade delete.
Cannot be updated
NUMBER(10)
Other rules:
-----------
1. If a row from the external_id or template_url tables is deleted,
delete the corresponding row in the ei_tu table.
TABLE external_id
=================
external_id_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
external_id Required.
VARCHAR2(40)
external_id_name Optional.
VARCHAR(120).
source Required. Coded.
On insert, check value against code table.
VARCHAR2(40)
tab_name Required. Uppercase.
VARCHAR2(20)
primary_key Required.
VARCHAR2(40)
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER.
Other rules:
------------
1. External_id, source, tab_name, primary_key together are unique.
TABLE state
===========
abbrev Required.
Primary key. Uppercase.
Cannot be updated.
VARCHAR2(2)
name Required.
VARCHAR2(40)
country Required. Coded.
On insert or update, check if value is in code table.
VARCHAR2(20)
TABLE template_url
==================
template_url_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
template_url Required. Unique.
VARCHAR2(240)
source Required. Coded.
On insert or update, check if value is in code table.
VARCHAR2(40)
description Optional.
VARCHAR2(240)
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER.
TABLE url_display
==================
url_display_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
web_page Required. Coded.
On insert or update, check if value is in code table.
VARCHAR2(40)
link_title Required.
VARCHAR2(100)
link_location Required. Coded.
On insert or update, check if value is in code table.
VARCHAR2(40)
template_url_no Required.
Foreign key to template_url table.
Other Rules/Notes:
------------------
TABLE update_log
================
ulog_no Reguired.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
tab_name Required. Uppercase.
VARCHAR2(20)
col_name Required. Uppercase.
VARCHAR2(30)
primary_key Required.
VARCHAR2(40)
old_value Required.
VARCHAR2(2000)
new_value Required.
VARCHAR2(2000)
is_public Required.
Allowable values: Y, N
On insert, set to N.
On update can be set to Y.
description Optional.
Can be entered only as an update.
VARCHAR2(240)
date_modified Required.
On insert or update, set to SYSDATE
modified_by Required.
On insert or update, set to USER
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
Other Rules:
------------
1. Records in the update_log table cannot be deleted by CURATOR role.
2. Only is_public and description columns are modifiable.
TABLE note
=============
note_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
note Required.
VARCHAR2(960)
note_type Required. Coded.
On insert or update, check if value is in the CODE table.
VARCHAR2(40)
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
Other Rules:
-----------
We considered adding note_type to the note_link table in case there is
an instance where the same note might be two different types,
depending on what it is linked to, but since we could not come
up with a real example and also think that the TYPE should go
with the NOTE rather than describe the association, we decided
to put it in the NOTE table itself.
TABLE note_link
=============
note_link_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
note_no Required.
Foreign key to NOTE table.
tab_name Required. Uppercase.
On insert or update, check if value is in ALL_TABLES.
VARCHAR2(30)
primary_key Required.
VARCHAR2(40)
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
Other Rules:
------------
The note_link table links a note with any other row in the database.
The combination of note_no, tab_name, and primary_key should be unique.