This table is used for generating and storing CGDIDs.
TABLE CGDID
==================
sgdid_no Required.
Oracle sequence number.
Primary key.
Cannot be updated.
NUMBER(10)
sgdid Required. Uppercase.
Unique.
VARCHAR2(10)
sgdid_type Required. Coded.
On insert, check value against code table.
VARCHAR2(40)
tab_name Optional. Uppercase.
VARCHAR2(30)
primary_key Optional.
VARCHAR2(40)
curator_note_no Optional.
NUMBER(10)
Foreign key to curator_note table.
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
=========================================
How will CGDIDs be assigned?? A proposal:
=========================================
The sgdid_no will be used to automatically generate the number part of
the CGDID. When a new row in the CGDID table is inserted, the next
val for the sgdid_no will be generated, then this next val will get an
"S" slapped on it, which will then be used for the new CGDID. Note
that for the earlier CGDIDs, the same "core number" was used for both
L and S CGDIDs. For example: L000111 and S000111 are
both valid CGDIDs. Because of this, the sgdid_no and the sgdid won't
match up for the pre-existing CGDIDs. When we go to add new ones
after these existing ones are added, we can just start with the next
available sgdid_no, which will create a gap in the CGDIDs, but that
shouldn't really cause a problem.
When a new row is inserted in the feature table, a new CGDID will be
assigned immediately via a trigger: a new row will be inserted into
the CGDID table, then that new CGDID will be used for the new feature.
When a new locus is assigned, the trigger WILL NOT fire. Instead, we
need to use a checking script to assign them. This is because of a
timing problem. If a new locus is created in the locus table, we do
not want to instantly assign an CGDID b/c chances are it will be
associated with a feature in a few seconds. In the cases where a new
locus is added but is not associated with a feature, the checking
script will assign the CGDID nightly. **Note that we will STRONGLY
encourage the use of the curator interface to do this; we'll then take
care of the CGDID assignment via software.
We also need a checking script that checks for loci and features that
are associated with each other and also have their own CGDIDs.
Actually, we already have this checking part of it, but what we then
need to add is a script that updates the CGDID in the CGDID table
to 'Secondary', and also updates the tab_name to FEATURE.
Another check: all features should have primary CGDIDs (this check already
exists).
**WE DECIDED NOT TO DENORMALIZE CGDIDs. Thus, we will remove CGDID
from the locus and feature tables; many scripts will need to be
changed, but this will be better/cleaner in the long run.
Annotation changes:
When two features are merged, nothing happens to their CGDIDs; each
retains its CGDID. One feature will become feature_type = 'Merged',
and that feature_name will be inserted into the alias table as an
alias for the new, longer feature.
In the rare case of a split (one ORF is split into two), the ORF with
the original name will keep the original CGDID, and the new ORF will
get a new CGDID.
When a feature is flagged as deleted ie. feature_type = 'Deleted' then
nothing happens (it keeps its CGDID).
If a feature is truly deleted from the database, then a trigger will
fire that updates its CGDID in the CGDID table to 'Deleted'.
Other notes/rules:
-----------------
1. In general, rows should never be deleted from the sgdid table,
since this will be used for archival purposes (ie. to store deleted
CGDID's and CGDID_Secondaries) as well as for generating new CGDIDs.
2. Tab_name and primary_key are optional because we have deleted or
unused CGDIDs that do not refer to anything.
3. Valid sgdid_types are: Primary, Secondary, Deleted, Unused
4. We do NOT want Shuai's script to remove rows where tab_name and
primary_key are no longer valid b/c the tab_name and primary_key could
be pointing to a row in locus or feature that was deleted. We want to
keep this info so we can trace it to the delete_log table. Checking
script: should check if locus_no or feature_no is null; if so, the
sgdid_type should be 'Deleted'.