Re: [Gmod-schema] Chado question/request

[Stephen F. <FI...@ex...>]

Hi Scott,

Thanks for the response.  

The primary reason for asking for a rank column is to remove the 'value' column from the index since we're trying to store some analysis results that can be rather large in the analysisprop table.  We would like to separate some of these into "chunks" but even some of those might be larger than the column width for an index. Another benefit would make this table more consistent with other prop tables like the featureprop, libraryprop, cvtermprop etc.

For the analysisfeatureprop table we're essentially storing analysis outputs for InterProScan (e.g. HTML output), KEGG mappings (HTML output), and detailed blast results (XML output) for each feature. On the webpage for a feature, we pull that data out of the analysisfeatureprop table and quickly parse and display.  Originally we had these items stored on the file system, but we wanted to simplify data management by storing everything in the database. This doesn't substitute for placing of CVterms mappings, etc, derived from these analysis into the CVterm table.  We do that as well.  We just wanted to show more information.

Thanks!
Stephen

-----Original Message-----
From: cai...@gm... [mailto:cai...@gm...] On Behalf Of Scott Cain
Sent: Wednesday, September 16, 2009 12:20 PM
To: Stephen Ficklin
Cc: gmo...@li...
Subject: Re: [Gmod-schema] Chado question/request

Hi Stephen,

So for the analysisprop table, the point in adding a rank column is so
that you could break whatever information you're inserting up into
chucks that can then be ordered with the rank?  Seems like a
reasonable idea.  The alternative you mentioned is getting rid of the
index, but I agree, I'd rather not do that.  Given that this will put
an implicit limit on the size of the value column, should we also
change value from a text field to a long varchar?  I think postgres
supports an 8k varchar.  Presumably, no one else has exceeded this
size, as they would have run into the same problem.

As for the analysisfeatureprop table, I'm a little surprised no one
has formally suggested it before now.  I believe other people have at
least thought about it, particularly for better typing of scores
associated with particular results, but this design is sufficiently
flexible that other things could be stored there as well.  What sorts
of things do you envision storing in this table?

Thanks,
Scott


On Mon, Sep 14, 2009 at 3:02 PM, Stephen Ficklin
<FI...@ex...> wrote:
> Hello,
>
>
>
> We're trying to get some KEGG/KAAS results into the analysisprop table and
> the table has a unique constraint named analysisprop_c1 that includes the
> analysis_id, type_id and value fields.   When inserting a record we're
> getting maximum size limit exceeded errors when inserting a record larger
> than 8Kb on the index.  If we remove the index there's no problem inserting,
> but we'd prefer not to remove the index.   Other "property"  tables have a
> "rank" column that is used in the unique index.  Can we request that the
> table be altered to add a rank column with an appropriate unique constraint?
>
>
>
> Also, I would like to propose the addition of a new table:
> analysisfeatureprop.  The schema would be:
>
>
>
> CREATE TABLE analysisfeatureprop (
>
>     analysisfeatureprop_id SERIAL PRIMARY KEY,
>
>     analysisfeature_id INTEGER NOT NULL REFERENCES
> analysisfeature(analysisfeature_id),
>
>     type_id INTEGER NOT NULL REFERENCES cvterm(cvterm_id),
>
>     value TEXT,
>
>     rank INTEGER NOT NULL,
>
>     CONSTRAINT analysisfeature_id_type_id_rank UNIQUE(analysisfeature_id,
> type_id, rank)
>
> );
>
>
>
> We would like to use this table for storing results from an analysis by
> feature.  We cannot currently store this in the featureprop table when we
> have more than one analysis on the same feature with the same type.   In
> development of Tripal we've had to manually create this table.
>
>
>
> Thanks,
>
> Stephen
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus
> on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Gmod-schema mailing list
> Gmo...@li...
> https://lists.sourceforge.net/lists/listinfo/gmod-schema
>
>



-- 
------------------------------------------------------------------------
Scott Cain, Ph. D.                                   scott at scottcain dot net
GMOD Coordinator (http://gmod.org/)                     216-392-3087
Ontario Institute for Cancer Research