Re: [GeneX-dev] Schema changes

[<ja...@op...>]

Hey Todd,

Thanks for doing this, it was long overdue...

"Todd Peterson" <tf...@nc...> writes:

I wanted to discuss this first, because I think others will want to
comment. 

> 38. UserSequenceFeature table in ErWin has NOT NULL constraints on
>     nonunique_flag, con_fk and putative_csf attributes, DB does
>     not. CSF's are no longer supported, so the putative_csf
>     attribute is meaningless.
> Solution: Add NOT NULL constraint to nonunique_flag in DB. 

No. nonunique_flag was for CSF, and it is now gone. It needs to be
removed from ERWin.

> Remove NOT NULL constraint from con_fk in ErWin model. 

USF table now has both con_fk and provider_con_fk. This is slightly
confusing, but I believe there are two distint needs. Originally we
were using con_fk to indicate who supplied the nucleic acid material
that the USF was based on. We attempted to use owner_us_fk to indicate who
provided the information. The problem was that we overloaded the use
of UserSec here, because it should have only been used for
security/ownership of data in the DB.

When the DB is installed somewhere besides NCGR, the owner_us_fk needs
to point to a valid UserSec account for *that* installation, and since
I don't think every site wants to grant Wes Hatfield a login that can
change their UserSec entries, the owner_us_fk needs to point to the
local superuser account.

So we have:
con_fk: nucleic_acid supplier
provider_con_fk: who told us the sequence info

Perhaps these are too close to be useful as separate items and con_fk
should be removed.

Contact FKEYs
=============
This brings up the related issue:
All con_fk entries in the DB want to be renamed so that they are more
indicative of their intended role.

USF
ArrayMeasurement
ExperimentSet
---
provider_con_fk: the contact for the researcher that *originally*
supplied the information. This is different than the UserSec entry
that has permission to modify the information in the DB.

Spotter
Scanner
Software
ArrayLayout
---
con_fk => supplier_con_fk or provider_con_fk to indicate the
individual or organization that created the item.

Sample
--
con_fk => ??? We had a big discussion on how this could be used. I
believe it was intended to be the supplier of the original nucleic
acid material. I think that we discovered that there could be many
suppliers for a given sample (seed supplier, others?). Do we just want
to leave the name con_fk, and add some more types to ContactType
(seed_supplier, nucleic_acid_supplier, etc.)?

Protocol
--
has no con_fk, I believe it wants one. supplier_con_fk?
provider_con_fk?

> 1. In most tables, the actual database uses a size for varchar
>    fields of 128. The ErWin model has a wide variety of field sizes.
> Solution: Change ErWin model varchar fields to match database where
> the database field is 128 characters wide.  Reason: To make ErWin
> character fields a standard length where length is not an important
> consideration.

Yes, we agreed a while back that all varchars be 128, unless we needed
them to be smaller for some reason. One example is ExperimentSet:name
which inherits a Windows file name length limitation.

Todd, are there any others in the DB that are not 128?

> 4. ErWin model has AM_Group table, DB does not.
> Solution: Remove the table from ErWin.
> Reason: The table is obsolete.

yes.

> 2. AL_Spots table in ErWin has attributes of 
> 3. AM_Factorvalues table in ErWin has attributes of
> 5. AM_Spots table in ErWin has attributes of
> 6. AM_SuspectSpots table in ErWin has attributes of
> 7. ArrayMeasurement table in ErWin has attributes of
> 10. BlastHits table in ErWin has attributes
> 15. ExperimentFactors table in ErWin model has attributes
> 17. ExperimentSet table in ErWin model has attribute
> 23. HotSpots table in ErWin model has attributes
> 26. SampleProtocols table in ErWin has attribute
> 29. SpotLink table in ErWin model has attributes
> 32.TL_FactorValues table in ErWin model has attributes
> 35. Treatment_AMs table in ErWin has attributes
> 36. TreatmentLevel table in ErWin has attributes
> 39. USF_ExternalDBLink table in ErWin has attributes

These are all inherited fkeys. That ERWin created bogusly.

> 8. ArrayMeasurement table in DB has attributes of
>     hybridization_name varchar(128)
>     channel_name varchar(128)
> which are not in the ErWin model.  Solution: Add the attributes to
> the ErWin model.  Reason: These attributes exist in the Curation
> Tool, DB and in GeneXML so they belong in ErWin as well.

yes. Also ArrayMeasurement table now has:

provider_con_fk int4 (references Contact con_pk)

> 9. ArrayMeasurement table in ErWin has NOT NULL constraint for
> 16. ExperimentSet table in ErWin model has NOT NULL constraint for
> 18. ExperimentSet table in ErWin model has NOT NULL constraint on

yes.

> 11. CanonicalSequenceFeature table exists in ErWin model but does
> 13. CSF_ExternalDBLinktable exists in ErWin model but does not exist in DB.
> 24. RelatedCSF table exists in ErWin, does not exist in DB.
> 37. UserCanonicalLink table exists in ErWin but does not exist in DB.

These tables were removed because CSF is no longer supported

> 12. CitationLink table in ErWin model has attributes

This table was removed and should be removed from ERWin.

> 14. ExperimentFactors table in ErWin has attribute of
>     measurement_units, DB calls this attribute factor_units.
> Solution: Change the measurement_units attribute in ErWin to be factor_units.
> Reason: ErWin attribute name does not match DB attribute name.

yes

> 19. ExperimentSet table in DB has cit_fk attribute, ErWin does not.
> Solution: add cit_fk attribute to ErWin model.
> Reason: attribute required.

> 20. ExternalDatabase table in DB has attribute of main_url, ErWin
> 21. ExternalDatabase table in DB has attribute of parameterized_url

Correct these were changed to generalize the table to allow both types
of URL.

> 22. ExternalDatabase table in DB has NOT NULL constraint on name
>     attribute, ErWin does not.
> Solution: add constraint to ErWin model.
> Reason: name is required.

name is the pkey for the table

> 25. Sample table in ErWin and DB do not have a name
>     attribute. Currently, the Curation Tool is using the cell_line
>     attribute to hold this information. This was a hack to avoid
>     changing DB, GeneXML, and translators.
> Solution: Add attribute to ErWin model, DB, GeneXML, XML2DB, DB2XML
> and any other affected tools and models.  Reason: Sample needs a
> unique identifier field.

OK. Thanks for catching that.

> 27. Scanner table in ErWin has attribute model_num, DB has attribute
>     model_description.
> Solution: change name in ErWin.
> Reason: to make DB and ErWin consistent.

yes.

> 28. Species table in DB and ErWin have attribute of num_chromosomes
>     which has type of char. This attribute should be int or varchar.
> Solution: change the type to int in DB, ErWin, and other affected
> tools and models.  Reason: char is not sufficient to represent this
> attribute.

I believe the type was originally intened to be 'byte' since no
organisms I know that are being studied have more than 255
chromosomes, but you're correct we should change it to int.

> 30. Spotter table in ErWin has attribute model_num, DB has attribute
>     model_description.
> Solution: change name in ErWin.
> Reason: to make DB and ErWin consistent.

yes.

> 31. Spotter table in ErWin has attribute of con_pk with a NOT NULL
>     constraint, DB defines this constraint as con_fk with no NOT
>     NULL constraint.
> Solution: rename constraint in ErWin and remove NOT NULL constraint.
> Reason: name is wrong and constraint is unecessary.

yes.

> 33. TL_FactorValues table in DB has attribute reference_value
>     varchar(128) NOT NULL which does not exist in ErWin model.
> Solution: add attribute to ErWin model.
> Reason: attribute required for TBD.

yes.

> 34. Treatment_AMs table in ErWin has attributes of am_pk and tl_pk,
>     DB calls these attributes am_fk and tl_fk, respectively.
> Solution: change attribute names in ErWin.
> Reason: names are incorrect.

yes.

jas.