These tables are used for storing gene summary paragraphs.
TABLE Paragraph
==================
paragraph_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
paragraph_text Required.
VARCHAR2(4000)
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
Other notes/rules:
===================
The paragraph table will store the preassembled paragraph text that is
assembled from the phrase table for better performance for the web
display. Any change (insert, update, or delete) to the phrase table
must trigger a change to the corresponding row in the paragraph table.
TABLE Phrase
==================
phrase_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
paragraph_no Required.
Foreign key to paragraph table. Oracle sequence number.
NUMBER(10)
phrase_text Required.
VARCHAR2(960)
phrase_order Required.
NUMBER(2)
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
Other notes/rules:
===================
The combination of the paragraph_no and phrase_text columns is unique.
All editing should be done via the phrase table, not the paragraph table.
TABLE Phrase_category
==========================
phrase_no Required.
Part of Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
phrase_category Required.
Coded.
On insert or update,
check if value is in 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
TABLE Colleague
==================
Table already exists.
Other notes/rules:
===================
Colleagues are associated with paragraphs because we want to keep
track of each colleague that gives us feedback.
TABLE Locus
===============
Table already exists.
Other notes/rules:
===================
The locus table is linked to its given paragraph via a foreign
key. One paragraph may summarize info about more than one locus.
However, it is important to note that this relationship only makes the
links between a paragraph and its primary locus (or loci). If a locus
is just mentioned in passing in a given paragraph, it is not linked
here. It is linked via the markup language used when constructing
phrases using the PHRASE_LINK table.
TABLE GO
===========
Table already exists.
Other notes/rules:
===================
GO terms can be marked up within the text of a phrase and is linked to
a given phrase in the PHRASE_LINK table.
TABLE Reflink
================
Table already exists.
Other notes/rules:
===================
Rows in the phrase table will be linked to references via rows in
the REFLINK table.
TABLE Phrase_link
================
phrase_link_no Required.
Part of 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(30)
primary_key Required.
VARCHAR2(40)
primary_key_col Required. Uppercase.
VARCHAR2(100)
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
Other notes/rules:
===================
Rows in the phrase table will be linked to rows in the LOCUS table or
the GO table via the PHRASE_LINK table. The rows in the LOCUS table
will be used to link a phrase with a gene mentioned in the paragraph
BUT IS NOT the primary locus that the paragraph is about. It can also
be used to link to other items in the database, for eg, the FEATURE
table.
Load/Interface notes
====================
Need to parse existing paragraphs from ACEDB to load the tables. Will
need a curator interface to load new ones. It would be nice to have
two ways to do this: via the flat file method as well as an clicky web
interface. Will also need to edit existing paragraphs; if sentences
are shared by multiple paragraphs, need to note that on the interface,
give option to edit sentence for one locus or for all loci that use
it. Before commiting, should have a preview button. Also need to
modify Kane's new paragraph script to check oracle for new paragraphs.
Upon loading, to make the secondary locus<->phrase link in the
phrase_link table, the script will convert the gene names to locus or
feature_no's. It would be good to have a script that periodically
checks the validity of rows in phrase_link (can modify
checkNonExistRows.pl to include this). Also, should check paragraphs
periodically for name changes involving genes mentioned in them.
Could do something like change the tag automatically upon loading from
<name>ACT1</name> to
<locus_no=111>ACT1</locus_no> to ensure that the link
will work consistently even if there is a name change.