gmod-schema Mailing List for Generic Model Organism Database Project

Oops.
Tuition for the European Summer School is 95ドル. I apologize for the confusion.
Dave C
On Sun, Mar 29, 2009 at 9:11 PM, Dave Clements, GMOD Help Desk
<gmo...@go...> wrote:
> Hello all,
>
> ***The application deadline for both GMOD summer schools is April 6,
> one week from now.***
>
> GMOD Summer School - Americas will be held 16-19 July at the National
> Evolutionary Synthesis Center (NESCent), in Durham, NC, USA. Student
> tuition is free. See
> http://gmod.org/wiki/2009_GMOD_Summer_School_-_Americas
>
> GMOD Summer School - Europe will be held 3-6 August at the University
> of Oxford, in Oxford, UK. This is a part of GMOD Europe 2009, which
> includes the next GMOD Meeting. Student tuition is 75ドル. See
> http://gmod.org/wiki/2009_GMOD_Summer_School_-_Europe
>
> Please contact the GMOD Help Desk (he...@gm...) if you have questions.
>
> We hope to see you in Durham or Oxford,
>
> Dave C.
>
> On Mon, Mar 16, 2009 at 10:46 AM, Dave Clements, GMOD Help Desk
> <gmo...@go...> wrote:
>> Hello all,
>>
>> GMOD is offering two Summer Schools in 2009, one on each side of the
>> Atlantic. GMOD Summer Schools
>> (http://gmod.org/wiki/GMOD_Summer_School) introduce new GMOD users to
>> the GMOD project and include several days of hands-on training on how
>> to install, configure and administer GMOD tools. The 2008 GMOD Summer
>> School was a great success with 25 students from 4 countries in
>> attendance.
>>
>> Americas, 16-19 July
>> - at the National Evolutionary Synthesis Center (NESCent), Durham, NC, USA
>> - Student tuition is free, thanks to NIH grant 1R01HG004483-01.
>> - http://gmod.org/wiki/2009_GMOD_Summer_School_-_Americas
>>
>> Europe, 3-6 August
>> - at the University of Oxford, Oxford, United Kingdom
>> - Part of GMOD Europe 2009, which includes the next GMOD Meeting
>> - Student tuition is 75ドル
>> - http://gmod.org/wiki/2009_GMOD_Summer_School_-_Europe
>>
>> The schools will feature training on these GMOD components:
>> * GBrowse - the most widely installed genome viewer on the web
>> * Chado - a modular and extensible database schema
>> * Apollo - genome annotation editor
>> * BioMart - biological data warehouse system
>> * GBrowse_syn - a GBrowse based synteny viewer
>> * JBrowse - a brand new Web 2.0 genome browser
>> * Artemis-Chado Integration (Europe only)
>> * MAKER - Genome annotation pipeline (Americas only)
>> * Tripal - Web front end for Chado (Americas only)
>>
>> Applications for the 2009 GMOD Summer Schools are now being accepted.
>> ***The deadline for applications is the end of the day, April 6.***
>> Enrollment is limited to 25 participants in each course. If
>> applications exceed capacity (and we expect they will) then applicants
>> will be picked based on the strength of their application. Applicants
>> will be notified of their admission status by mid April.
>>
>> Please contact the GMOD Help Desk if you have any questions.
>>
>> Thanks,
>>
>> Dave Clements
>> GMOD Help Desk
>> he...@gm...
>>
>> http://gmod.org/wiki/2009_GMOD_Summer_School_-_Americas
>> http://gmod.org/wiki/2009_GMOD_Summer_School_-_Europe
>> http://gmod.org/wiki/GMOD_Europe_2009
>>
>

Hello all,
***The application deadline for both GMOD summer schools is April 6,
one week from now.***
GMOD Summer School - Americas will be held 16-19 July at the National
Evolutionary Synthesis Center (NESCent), in Durham, NC, USA. Student
tuition is free. See
http://gmod.org/wiki/2009_GMOD_Summer_School_-_Americas
GMOD Summer School - Europe will be held 3-6 August at the University
of Oxford, in Oxford, UK. This is a part of GMOD Europe 2009, which
includes the next GMOD Meeting. Student tuition is 75ドル. See
http://gmod.org/wiki/2009_GMOD_Summer_School_-_Europe
Please contact the GMOD Help Desk (he...@gm...) if you have questions.
We hope to see you in Durham or Oxford,
Dave C.
On Mon, Mar 16, 2009 at 10:46 AM, Dave Clements, GMOD Help Desk
<gmo...@go...> wrote:
> Hello all,
>
> GMOD is offering two Summer Schools in 2009, one on each side of the
> Atlantic. GMOD Summer Schools
> (http://gmod.org/wiki/GMOD_Summer_School) introduce new GMOD users to
> the GMOD project and include several days of hands-on training on how
> to install, configure and administer GMOD tools. The 2008 GMOD Summer
> School was a great success with 25 students from 4 countries in
> attendance.
>
> Americas, 16-19 July
> - at the National Evolutionary Synthesis Center (NESCent), Durham, NC, USA
> - Student tuition is free, thanks to NIH grant 1R01HG004483-01.
> - http://gmod.org/wiki/2009_GMOD_Summer_School_-_Americas
>
> Europe, 3-6 August
> - at the University of Oxford, Oxford, United Kingdom
> - Part of GMOD Europe 2009, which includes the next GMOD Meeting
> - Student tuition is 75ドル
> - http://gmod.org/wiki/2009_GMOD_Summer_School_-_Europe
>
> The schools will feature training on these GMOD components:
> * GBrowse - the most widely installed genome viewer on the web
> * Chado - a modular and extensible database schema
> * Apollo - genome annotation editor
> * BioMart - biological data warehouse system
> * GBrowse_syn - a GBrowse based synteny viewer
> * JBrowse - a brand new Web 2.0 genome browser
> * Artemis-Chado Integration (Europe only)
> * MAKER - Genome annotation pipeline (Americas only)
> * Tripal - Web front end for Chado (Americas only)
>
> Applications for the 2009 GMOD Summer Schools are now being accepted.
> ***The deadline for applications is the end of the day, April 6.***
> Enrollment is limited to 25 participants in each course. If
> applications exceed capacity (and we expect they will) then applicants
> will be picked based on the strength of their application. Applicants
> will be notified of their admission status by mid April.
>
> Please contact the GMOD Help Desk if you have any questions.
>
> Thanks,
>
> Dave Clements
> GMOD Help Desk
> he...@gm...
>
> http://gmod.org/wiki/2009_GMOD_Summer_School_-_Americas
> http://gmod.org/wiki/2009_GMOD_Summer_School_-_Europe
> http://gmod.org/wiki/GMOD_Europe_2009
>

I have a number of questions about the Mage (formerly RAD) microarray 
module in Chado. I'd very much appreciate it if anyone has any answers 
to these questions.
1) Are there any loaders in existence for importing data into mage from 
GEO and ArrayExpress? In general, are there any data loaders for the 
MAGE module and what data formats do they support?
2) I am unclear about how the tables quantification, 
quantification_relationship and elementresult are used. How do you 
represent raw versus processed/normalized data? Is there an 
elementresult for each quantification or is the elementresult only 
attached to one quantification, in a group of quantifications connected 
via quantification_relationships? From a prior posting
(http://sourceforge.net/mailarchive/forum.php?thread_name=89B3ACCF-C9CB-4BA9-9E85-11A528F4B451%40genomics.princeton.edu&forum_name=gmod-schema) 
I got the impression that the elementresult only stored the most refined 
result and is connected to the final quantification? In this case, where 
are raw and intermediate results stored?
3) I'm not clear about the role of the table study. There is no comment 
on this table. Does this conform to an experiment? For example, using 
GEO data, would each Dataset/Series correspond to a study while each 
sample corresponded to an assay?
4) Could someone give me an example of how a probeset would be represented?
Thanks very much,
Jeff
-- 
Jeff Bowes M.Sc.
Technical Director, Xenbase
Department of Biological Sciences
University of Calgary
Calgary, Alberta T2N 1N4
CANADA
Tel: (403) 220-2824 
Fax: (403) 284-4707

Ah, that explains it. I can't think of a case where that would bite
me. I mostly bind variables so it looks like it is being handled for
me.
Thanks,
Scott
On Wed, Mar 18, 2009 at 10:12 AM, Hilmar Lapp <hl...@du...> wrote:
>
> On Mar 18, 2009, at 9:50 AM, Scott Cain wrote:
>
>> I'd read about changes in the way perl DBI worked with PostgreSQL
>> and feared the
>> worse. Thankfully those fears seem to have been unfounded.
>
>
> You can unit-test this. The incompatibility is not in perl DBI but in
> the way PostgreSQL auto-casts parameter values in where clauses - the
> auto-casts to string types (VARCHAR, TEXT) were removed in Pg 8.3+.
> What this means is that your application will crash if you interpolate
> a numeric variable for a parameter value in a where clause if the type
> of the column it is being compared to is not numeric.
>
> If you bind values it looks like you'll be fine. I added a unit test
> for this in BioSQL and it passes; the only test that failed was where
> an unquoted numeric value was interpolated in a SQL statement in a
> constraint on a VARCHAR column.
>
>    -hilmar
> --
> ===========================================================
> : Hilmar Lapp -:- Durham, NC -:- hlapp at duke dot edu :
> ===========================================================
>
>
>
>
>
> ------------------------------------------------------------------------------
> Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
> powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
> easily build your RIAs with Flex Builder, the Eclipse(TM)based development
> software that enables intelligent coding and step-through debugging.
> Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
> _______________________________________________
> 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

On Mar 18, 2009, at 9:50 AM, Scott Cain wrote:
> I'd read about changes in the way perl DBI worked with PostgreSQL 
> and feared the
> worse. Thankfully those fears seem to have been unfounded.
You can unit-test this. The incompatibility is not in perl DBI but in 
the way PostgreSQL auto-casts parameter values in where clauses - the 
auto-casts to string types (VARCHAR, TEXT) were removed in Pg 8.3+. 
What this means is that your application will crash if you interpolate 
a numeric variable for a parameter value in a where clause if the type 
of the column it is being compared to is not numeric.
If you bind values it looks like you'll be fine. I added a unit test 
for this in BioSQL and it passes; the only test that failed was where 
an unquoted numeric value was interpolated in a SQL statement in a 
constraint on a VARCHAR column.
	-hilmar
-- 
===========================================================
: Hilmar Lapp -:- Durham, NC -:- hlapp at duke dot edu :
===========================================================

Hi Josh and Virpi,
While I can't comment on RHEL, I can say I've been using Pg 8.3 for a
while and haven't run into the problems I expected. I'd read about
changes in the way perl DBI worked with PostgreSQL and feared the
worse. Thankfully those fears seem to have been unfounded.
Scott
On Wed, Mar 18, 2009 at 9:29 AM, Josh Goodman <jog...@in...> wrote:
> Hi Virpi,
>
> Yes, PostgreSQL 8.3 does run on RHEL but you will not find packages in the official RedHat
> repositories. The most recent version they offer for RHEL 5 is 8.1.11. To get the recent versions
> you have to go to a site like http://yum.pgsqlrpms.org/. Be warned that this may void any support
> you have from RedHat. I also believe that GMOD has been discouraging use of 8.3 or higher because
> of some conflicts with it but I can't comment on the details or the status of this.
>
> Cheers,
> Josh
>
> Virpi Ahola wrote:
>> Which version of PostgreSQL runs on RedHat Enterprise server? Has
>> PostgreSQL 8.3 already been tested ?
>>
>> - Virpi
>>
>> ------------------------------------------------------------------------------
>> Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
>> powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
>> easily build your RIAs with Flex Builder, the Eclipse(TM)based development
>> software that enables intelligent coding and step-through debugging.
>> Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
>> _______________________________________________
>> Gmod-schema mailing list
>> Gmo...@li...
>> https://lists.sourceforge.net/lists/listinfo/gmod-schema
>
> ------------------------------------------------------------------------------
> Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
> powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
> easily build your RIAs with Flex Builder, the Eclipse(TM)based development
> software that enables intelligent coding and step-through debugging.
> Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
> _______________________________________________
> 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

Hi Virpi,
Yes, PostgreSQL 8.3 does run on RHEL but you will not find packages in the official RedHat
repositories. The most recent version they offer for RHEL 5 is 8.1.11. To get the recent versions
you have to go to a site like http://yum.pgsqlrpms.org/. Be warned that this may void any support
you have from RedHat. I also believe that GMOD has been discouraging use of 8.3 or higher because
of some conflicts with it but I can't comment on the details or the status of this.
Cheers,
Josh
Virpi Ahola wrote:
> Which version of PostgreSQL runs on RedHat Enterprise server? Has 
> PostgreSQL 8.3 already been tested ?
> 
> - Virpi
> 
> ------------------------------------------------------------------------------
> Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
> powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
> easily build your RIAs with Flex Builder, the Eclipse(TM)based development
> software that enables intelligent coding and step-through debugging.
> Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
> _______________________________________________
> Gmod-schema mailing list
> Gmo...@li...
> https://lists.sourceforge.net/lists/listinfo/gmod-schema

Which version of PostgreSQL runs on RedHat Enterprise server? Has 
PostgreSQL 8.3 already been tested ?
- Virpi

Hello all,
GMOD is offering two Summer Schools in 2009, one on each side of the
Atlantic. GMOD Summer Schools
(http://gmod.org/wiki/GMOD_Summer_School) introduce new GMOD users to
the GMOD project and include several days of hands-on training on how
to install, configure and administer GMOD tools. The 2008 GMOD Summer
School was a great success with 25 students from 4 countries in
attendance.
Americas, 16-19 July
- at the National Evolutionary Synthesis Center (NESCent), Durham, NC, USA
- Student tuition is free, thanks to NIH grant 1R01HG004483-01.
 - http://gmod.org/wiki/2009_GMOD_Summer_School_-_Americas
Europe, 3-6 August
 - at the University of Oxford, Oxford, United Kingdom
 - Part of GMOD Europe 2009, which includes the next GMOD Meeting
 - Student tuition is 75ドル
 - http://gmod.org/wiki/2009_GMOD_Summer_School_-_Europe
The schools will feature training on these GMOD components:
* GBrowse - the most widely installed genome viewer on the web
* Chado - a modular and extensible database schema
* Apollo - genome annotation editor
* BioMart - biological data warehouse system
* GBrowse_syn - a GBrowse based synteny viewer
* JBrowse - a brand new Web 2.0 genome browser
* Artemis-Chado Integration (Europe only)
* MAKER - Genome annotation pipeline (Americas only)
* Tripal - Web front end for Chado (Americas only)
Applications for the 2009 GMOD Summer Schools are now being accepted.
***The deadline for applications is the end of the day, April 6.***
Enrollment is limited to 25 participants in each course. If
applications exceed capacity (and we expect they will) then applicants
will be picked based on the strength of their application. Applicants
will be notified of their admission status by mid April.
Please contact the GMOD Help Desk if you have any questions.
Thanks,
Dave Clements
GMOD Help Desk
he...@gm...
http://gmod.org/wiki/2009_GMOD_Summer_School_-_Americas
http://gmod.org/wiki/2009_GMOD_Summer_School_-_Europe
http://gmod.org/wiki/GMOD_Europe_2009

Hello,
In our SGD Lite / YFGdb database, we actually do not store the log 
ratio data in the database, but we store it externally in flat files 
and just store the file names as STUDY_PROP's.
Awhile back, we considered adding the data to the database, and we 
asked Allen Day, who I believe was one of the people who designed that 
part of the schema, for advice. Here is what he said when we asked 
how to store a PCL file (basically, the log ratios) in the database-- 
hope this helps!
-Kara
>> You minimally need the biomaterial, biomaterial_assay, assay,
>> >> acquisition, channel, quantification, and 
>> quantification_relationship
>> >> tables to store what you describe.
>> >>
>> >> * represent your WT and mutants as biomaterials 1 and 2
>> >> * represent hybridization of WT with assay 1
>> >> * represent hybridization of mutant with assay 2
>> >> * link assays 1 and 2 to biomaterials 1 and 2 via 
>> biomaterial_dbxref
>> >> * acquisition 1 is derived by scanning assay 1. the scan was 
>> done at
>> >> the frequency of channel 1
>> >> * acquisition 2 is derived by scanning assay 2. the scan was 
>> done at
>> >> the frequency of channel 2
>> >> * quantification 1 is derived from acquisition 1 using your
>> >> processing
>> >> algorithm (analysis table), etc
>> >> * quantification 2 is derived from acquisition 2 using your
>> >> processing
>> >> algorithm (analysis table), etc
>> >> * quantification 3 is the ratio
>> >> * quantification 3 (the ratio) is derived from quantifications 1 
>> and
>> >> 2. link them in the quantification_relationship table.
>> >> * elementresult data points back to quantification 3.
>> >>
On Mar 4, 2009, at 3:48 PM, JD Wong wrote:
> Hi, Im trying to add log ratio mircoarray data and the study that 
> produced it to SGDlite. So im trying to link the STUDY and 
> ELEMENTRESULT in such a way that the relationship between them is 
> maintained.
>
> Under the latest version of SGDlite, study is linked to 
> elementresult through:
> study_assay - assay <- acquitision <- quantification <- elementresult
> study_assay - assay -> arraydesign <- element - elementresult
> (<-) is a 1 to many relationship
> ( - ) is a 1 to 1
>
> The way this is set up doesnt make sense to me, the way it's set up 
> you have to have a 1:1 relationship in both acquisition and 
> quantification with the table above it. That means each time two or 
> more tables use the same quantification or acquisition procedure 
> that record must be repeated in order to maintain assay's 
> relationship with the data itself, which leads to redundancy.
>
> Does anyone else see a problem with this, or perhaps Im doing it 
> wrong?
>
> thanks,
> -JD Wong
> Database Administrator
> Canter for Bioinformatics and Life Sciences
> Buffalo, NY
> <chado-insertion- 
> schema 
> .pdf 
> > 
> ------------------------------------------------------------------------------
> Open Source Business Conference (OSBC), March 24-25, 2009, San 
> Francisco, CA
> -OSBC tackles the biggest issue in open source: Open Sourcing the 
> Enterprise
> -Strategies to boost innovation and cut costs with open source 
> participation
> -Receive a 600ドル discount off the registration fee with the source 
> code: SFAD
> http://p.sf.net/sfu/XcvMzF8H_______________________________________________
> Gmod-schema mailing list
> Gmo...@li...
> https://lists.sourceforge.net/lists/listinfo/gmod-schema

Hi Chris and EO,
What tools do you have already to manage the schemas? I don't want to
go and recreate the wheel, so if there are things that might be
generally useful, please do share.
Thanks,
Scott
On Mon, Mar 2, 2009 at 3:08 PM, Chris Mungall <cj...@be...> wrote:
>
> EO set up something similar for the modencode Chado instance
>
> - Each submission gets its own schema (namespace)
> - postgresql sequences (auto-increment counter for surrogate identifiers)
> are shared
> - There are no tables in the public namespace
> - There is one special namespace that has UNION views over all other
> namespaces
> - Possible gotcha: uniqueness constraints only hold in the context of
> individual namespaces
> - We use plpgsql rather than SQL::Translator to introspect the db and create
> the UNION views
>
> On Mar 2, 2009, at 8:35 AM, Scott Cain wrote:
>
>> Hello all,
>>
>> Last week I went traipsing around the country talking to people about
>> using Chado for community annotation efforts, specifically in an
>> educational setting, but I think the ideas may be more generally
>> useful. Today I am specifically going to write about support for
>> PostgreSQL schemas in Chado to help with data management when a
>> variety of people are using Chado to annotate a genome. The idea here
>> is pretty simple: we can create multiple schemas on a one per user
>> basis to give them a "private" work space in Chado to work on their
>> annotations before someone (the user or an authority of some sort)
>> promotes them to the main Chado schema (the "public" schema). I've
>> spent a little time on the details of making this work but would like
>> feedback on my thoughts so far as there are probably things I didn't
>> think of.
>>
>> The idea is pretty simple: when a user wants a private work space, a
>> schema is set up for him and it is populated with a ddl that mirrors
>> Chado. It is not exactly the same as Chado though, so that data can
>> be viewed in context of both the public and private data. It is still
>> fairly simple though: for each table in Chado, there are a few
>> relations created:
>>
>> 1. A table that has the same same structure as its corresponding
>> table in the main schema, but with an underscore at the beginning of
>> the name (ie, "feature" becomes "_feature"). It uses the public
>> sequence for generating new ids for the private table though, to avoid
>> conflicts when the private data is promoted to public. Other
>> conflicts are possible to, noteably unique constraints. I don't know
>> how to best handle this: throw it back to the user to resolve?
>>
>> 2. A view with the same name as the main tables name which is a
>> union of the main table and the private, underscore-named table.
>>
>> 3. Rules on the view to allow inserts, updates and deletes on the
>> view to affect (only) the private, underscore-named table.
>>
>> I have experimented with most of the above in the psql shell to make
>> sure I understand how it would work (the only thing I haven't tried
>> yet is the rule for deletes, but I'm hoping that won't be too
>> difficult to do either).
>>
>> What I am hoping to do now is also fairly straight forward:
>>
>> 1. Write SQL::Translator based scripts that will generate these
>> private schemas on demand.
>>
>> 2. Modify the GFF3 bulk loader to be aware of the private schemas,
>> adding command line flags to insert into private schemas when desired.
>> What I don't know is how easy it will be to make the queries in the
>> loader and other Chado aware tools compatible with this. What I am
>> hoping is that I can just do a "SET search_path" when the database
>> handle is created and be good to go. Does anybody have any experience
>> with this?
>>
>> 3. Modify the GBrowse Chado adaptor to be aware of the public schemas
>> as well (same caveat/question as in number 2 applies here as well)
>>
>> 4. Twist Ed Lee's arm to get him to do the same with Apollo.
>>
>> So, what haven't I thought of? And what do you think of this idea in
>> general?
>>
>> Thanks,
>> Scott
>>
>>
>> --
>> ------------------------------------------------------------------------
>> Scott Cain, Ph. D.                  scott at scottcain
>> dot net
>> GMOD Coordinator (http://gmod.org/)           216-392-3087
>> Ontario Institute for Cancer Research
>>
>>
>> ------------------------------------------------------------------------------
>> Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco,
>> CA
>> -OSBC tackles the biggest issue in open source: Open Sourcing the
>> Enterprise
>> -Strategies to boost innovation and cut costs with open source
>> participation
>> -Receive a 600ドル discount off the registration fee with the source code:
>> SFAD
>> http://p.sf.net/sfu/XcvMzF8H
>> _______________________________________________
>> 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

Hi Allen,
I found that subclassing via INHERITS specifically would not work
because it got the privacy stuff in exactly wrong. That is, consider
a database with two schemas, "public" and "private", and
private.feature inherits from public.feature. When I query
public.feature, I get everything that is in both public.feature and
private.feature and when I query private.feature, I only get what is
in private.feature. That's no good. That's why what I described uses
only tables, views and rules to implement the private spaces.
Your comment about foreign key constraints is a valid one, though I
don't know the best way to handle it. Other constraints suffer
similar problems, like unique constraints. My first inclination is to
catch the problem at the time that the data is promoted from public to
private and ask the user to fix the problem (assuming we can give the
user enough information to fix it). I'm not sure what this would be
like in practice though.
Scott
On Mon, Mar 2, 2009 at 3:02 PM, Allen Day <all...@gm...> wrote:
> We did something similar to this at UCLA, and it worked pretty well.
>
> One consideration is that you may want to know, when doing a recursive
> query from a table and all subclasses of that table, from which
> particular subclass a resulting row came. We solved this by first
> adding an additional column to every table in the base schema, then
> subclassing that table. The base table contained NULL in this field,
> while each subclass table contained a default value that uniquely
> identified the subclass. So, when we did retrieval from the base
> table it was immediately apparent which subclass was responsible for a
> particular row.
>
> You do run into one problem with subclassed tables like this in that
> indexes cannot span subclasses. For instance, you cannot enforce a
> unique constraint across a table hierarchy. One effect is that
> queries/subqueries that are expected to return at most one row can
> return multiple. Similarly, foreign key constraints won't look for
> IDs in the subclass tables. The latter can cause problems for feature
> graphs.
>
> -Allen
>
>
> On Mon, Mar 2, 2009 at 8:35 AM, Scott Cain <sc...@sc...> wrote:
>> Hello all,
>>
>> Last week I went traipsing around the country talking to people about
>> using Chado for community annotation efforts, specifically in an
>> educational setting, but I think the ideas may be more generally
>> useful. Today I am specifically going to write about support for
>> PostgreSQL schemas in Chado to help with data management when a
>> variety of people are using Chado to annotate a genome. The idea here
>> is pretty simple: we can create multiple schemas on a one per user
>> basis to give them a "private" work space in Chado to work on their
>> annotations before someone (the user or an authority of some sort)
>> promotes them to the main Chado schema (the "public" schema). I've
>> spent a little time on the details of making this work but would like
>> feedback on my thoughts so far as there are probably things I didn't
>> think of.
>>
>> The idea is pretty simple: when a user wants a private work space, a
>> schema is set up for him and it is populated with a ddl that mirrors
>> Chado. It is not exactly the same as Chado though, so that data can
>> be viewed in context of both the public and private data. It is still
>> fairly simple though: for each table in Chado, there are a few
>> relations created:
>>
>> 1. A table that has the same same structure as its corresponding
>> table in the main schema, but with an underscore at the beginning of
>> the name (ie, "feature" becomes "_feature"). It uses the public
>> sequence for generating new ids for the private table though, to avoid
>> conflicts when the private data is promoted to public. Other
>> conflicts are possible to, noteably unique constraints. I don't know
>> how to best handle this: throw it back to the user to resolve?
>>
>> 2. A view with the same name as the main tables name which is a
>> union of the main table and the private, underscore-named table.
>>
>> 3. Rules on the view to allow inserts, updates and deletes on the
>> view to affect (only) the private, underscore-named table.
>>
>> I have experimented with most of the above in the psql shell to make
>> sure I understand how it would work (the only thing I haven't tried
>> yet is the rule for deletes, but I'm hoping that won't be too
>> difficult to do either).
>>
>> What I am hoping to do now is also fairly straight forward:
>>
>> 1. Write SQL::Translator based scripts that will generate these
>> private schemas on demand.
>>
>> 2. Modify the GFF3 bulk loader to be aware of the private schemas,
>> adding command line flags to insert into private schemas when desired.
>> What I don't know is how easy it will be to make the queries in the
>> loader and other Chado aware tools compatible with this. What I am
>> hoping is that I can just do a "SET search_path" when the database
>> handle is created and be good to go. Does anybody have any experience
>> with this?
>>
>> 3. Modify the GBrowse Chado adaptor to be aware of the public schemas
>> as well (same caveat/question as in number 2 applies here as well)
>>
>> 4. Twist Ed Lee's arm to get him to do the same with Apollo.
>>
>> So, what haven't I thought of? And what do you think of this idea in general?
>>
>> Thanks,
>> Scott
>>
>>
>> --
>> ------------------------------------------------------------------------
>> Scott Cain, Ph. D.                  scott at scottcain dot net
>> GMOD Coordinator (http://gmod.org/)           216-392-3087
>> Ontario Institute for Cancer Research
>>
>> ------------------------------------------------------------------------------
>> Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA
>> -OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise
>> -Strategies to boost innovation and cut costs with open source participation
>> -Receive a 600ドル discount off the registration fee with the source code: SFAD
>> http://p.sf.net/sfu/XcvMzF8H
>> _______________________________________________
>> 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

EO set up something similar for the modencode Chado instance
- Each submission gets its own schema (namespace)
- postgresql sequences (auto-increment counter for surrogate 
identifiers) are shared
- There are no tables in the public namespace
- There is one special namespace that has UNION views over all other 
namespaces
- Possible gotcha: uniqueness constraints only hold in the context of 
individual namespaces
- We use plpgsql rather than SQL::Translator to introspect the db and 
create the UNION views
On Mar 2, 2009, at 8:35 AM, Scott Cain wrote:
> Hello all,
>
> Last week I went traipsing around the country talking to people about
> using Chado for community annotation efforts, specifically in an
> educational setting, but I think the ideas may be more generally
> useful. Today I am specifically going to write about support for
> PostgreSQL schemas in Chado to help with data management when a
> variety of people are using Chado to annotate a genome. The idea here
> is pretty simple: we can create multiple schemas on a one per user
> basis to give them a "private" work space in Chado to work on their
> annotations before someone (the user or an authority of some sort)
> promotes them to the main Chado schema (the "public" schema). I've
> spent a little time on the details of making this work but would like
> feedback on my thoughts so far as there are probably things I didn't
> think of.
>
> The idea is pretty simple: when a user wants a private work space, a
> schema is set up for him and it is populated with a ddl that mirrors
> Chado. It is not exactly the same as Chado though, so that data can
> be viewed in context of both the public and private data. It is still
> fairly simple though: for each table in Chado, there are a few
> relations created:
>
> 1. A table that has the same same structure as its corresponding
> table in the main schema, but with an underscore at the beginning of
> the name (ie, "feature" becomes "_feature"). It uses the public
> sequence for generating new ids for the private table though, to avoid
> conflicts when the private data is promoted to public. Other
> conflicts are possible to, noteably unique constraints. I don't know
> how to best handle this: throw it back to the user to resolve?
>
> 2. A view with the same name as the main tables name which is a
> union of the main table and the private, underscore-named table.
>
> 3. Rules on the view to allow inserts, updates and deletes on the
> view to affect (only) the private, underscore-named table.
>
> I have experimented with most of the above in the psql shell to make
> sure I understand how it would work (the only thing I haven't tried
> yet is the rule for deletes, but I'm hoping that won't be too
> difficult to do either).
>
> What I am hoping to do now is also fairly straight forward:
>
> 1. Write SQL::Translator based scripts that will generate these
> private schemas on demand.
>
> 2. Modify the GFF3 bulk loader to be aware of the private schemas,
> adding command line flags to insert into private schemas when desired.
> What I don't know is how easy it will be to make the queries in the
> loader and other Chado aware tools compatible with this. What I am
> hoping is that I can just do a "SET search_path" when the database
> handle is created and be good to go. Does anybody have any experience
> with this?
>
> 3. Modify the GBrowse Chado adaptor to be aware of the public schemas
> as well (same caveat/question as in number 2 applies here as well)
>
> 4. Twist Ed Lee's arm to get him to do the same with Apollo.
>
> So, what haven't I thought of? And what do you think of this idea 
> in general?
>
> Thanks,
> Scott
>
>
> -- 
> ------------------------------------------------------------------------
> Scott Cain, Ph. D. scott at 
> scottcain dot net
> GMOD Coordinator (http://gmod.org/) 216-392-3087
> Ontario Institute for Cancer Research
>
> ------------------------------------------------------------------------------
> Open Source Business Conference (OSBC), March 24-25, 2009, San 
> Francisco, CA
> -OSBC tackles the biggest issue in open source: Open Sourcing the 
> Enterprise
> -Strategies to boost innovation and cut costs with open source 
> participation
> -Receive a 600ドル discount off the registration fee with the source 
> code: SFAD
> http://p.sf.net/sfu/XcvMzF8H
> _______________________________________________
> Gmod-schema mailing list
> Gmo...@li...
> https://lists.sourceforge.net/lists/listinfo/gmod-schema
>

We did something similar to this at UCLA, and it worked pretty well.
One consideration is that you may want to know, when doing a recursive
query from a table and all subclasses of that table, from which
particular subclass a resulting row came. We solved this by first
adding an additional column to every table in the base schema, then
subclassing that table. The base table contained NULL in this field,
while each subclass table contained a default value that uniquely
identified the subclass. So, when we did retrieval from the base
table it was immediately apparent which subclass was responsible for a
particular row.
You do run into one problem with subclassed tables like this in that
indexes cannot span subclasses. For instance, you cannot enforce a
unique constraint across a table hierarchy. One effect is that
queries/subqueries that are expected to return at most one row can
return multiple. Similarly, foreign key constraints won't look for
IDs in the subclass tables. The latter can cause problems for feature
graphs.
-Allen
On Mon, Mar 2, 2009 at 8:35 AM, Scott Cain <sc...@sc...> wrote:
> Hello all,
>
> Last week I went traipsing around the country talking to people about
> using Chado for community annotation efforts, specifically in an
> educational setting, but I think the ideas may be more generally
> useful. Today I am specifically going to write about support for
> PostgreSQL schemas in Chado to help with data management when a
> variety of people are using Chado to annotate a genome. The idea here
> is pretty simple: we can create multiple schemas on a one per user
> basis to give them a "private" work space in Chado to work on their
> annotations before someone (the user or an authority of some sort)
> promotes them to the main Chado schema (the "public" schema). I've
> spent a little time on the details of making this work but would like
> feedback on my thoughts so far as there are probably things I didn't
> think of.
>
> The idea is pretty simple: when a user wants a private work space, a
> schema is set up for him and it is populated with a ddl that mirrors
> Chado. It is not exactly the same as Chado though, so that data can
> be viewed in context of both the public and private data. It is still
> fairly simple though: for each table in Chado, there are a few
> relations created:
>
> 1. A table that has the same same structure as its corresponding
> table in the main schema, but with an underscore at the beginning of
> the name (ie, "feature" becomes "_feature"). It uses the public
> sequence for generating new ids for the private table though, to avoid
> conflicts when the private data is promoted to public. Other
> conflicts are possible to, noteably unique constraints. I don't know
> how to best handle this: throw it back to the user to resolve?
>
> 2. A view with the same name as the main tables name which is a
> union of the main table and the private, underscore-named table.
>
> 3. Rules on the view to allow inserts, updates and deletes on the
> view to affect (only) the private, underscore-named table.
>
> I have experimented with most of the above in the psql shell to make
> sure I understand how it would work (the only thing I haven't tried
> yet is the rule for deletes, but I'm hoping that won't be too
> difficult to do either).
>
> What I am hoping to do now is also fairly straight forward:
>
> 1. Write SQL::Translator based scripts that will generate these
> private schemas on demand.
>
> 2. Modify the GFF3 bulk loader to be aware of the private schemas,
> adding command line flags to insert into private schemas when desired.
> What I don't know is how easy it will be to make the queries in the
> loader and other Chado aware tools compatible with this. What I am
> hoping is that I can just do a "SET search_path" when the database
> handle is created and be good to go. Does anybody have any experience
> with this?
>
> 3. Modify the GBrowse Chado adaptor to be aware of the public schemas
> as well (same caveat/question as in number 2 applies here as well)
>
> 4. Twist Ed Lee's arm to get him to do the same with Apollo.
>
> So, what haven't I thought of? And what do you think of this idea in general?
>
> Thanks,
> Scott
>
>
> --
> ------------------------------------------------------------------------
> Scott Cain, Ph. D.                  scott at scottcain dot net
> GMOD Coordinator (http://gmod.org/)           216-392-3087
> Ontario Institute for Cancer Research
>
> ------------------------------------------------------------------------------
> Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA
> -OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise
> -Strategies to boost innovation and cut costs with open source participation
> -Receive a 600ドル discount off the registration fee with the source code: SFAD
> http://p.sf.net/sfu/XcvMzF8H
> _______________________________________________
> Gmod-schema mailing list
> Gmo...@li...
> https://lists.sourceforge.net/lists/listinfo/gmod-schema
>

Hello all,
Last week I went traipsing around the country talking to people about
using Chado for community annotation efforts, specifically in an
educational setting, but I think the ideas may be more generally
useful. Today I am specifically going to write about support for
PostgreSQL schemas in Chado to help with data management when a
variety of people are using Chado to annotate a genome. The idea here
is pretty simple: we can create multiple schemas on a one per user
basis to give them a "private" work space in Chado to work on their
annotations before someone (the user or an authority of some sort)
promotes them to the main Chado schema (the "public" schema). I've
spent a little time on the details of making this work but would like
feedback on my thoughts so far as there are probably things I didn't
think of.
The idea is pretty simple: when a user wants a private work space, a
schema is set up for him and it is populated with a ddl that mirrors
Chado. It is not exactly the same as Chado though, so that data can
be viewed in context of both the public and private data. It is still
fairly simple though: for each table in Chado, there are a few
relations created:
 1. A table that has the same same structure as its corresponding
table in the main schema, but with an underscore at the beginning of
the name (ie, "feature" becomes "_feature"). It uses the public
sequence for generating new ids for the private table though, to avoid
conflicts when the private data is promoted to public. Other
conflicts are possible to, noteably unique constraints. I don't know
how to best handle this: throw it back to the user to resolve?
 2. A view with the same name as the main tables name which is a
union of the main table and the private, underscore-named table.
 3. Rules on the view to allow inserts, updates and deletes on the
view to affect (only) the private, underscore-named table.
I have experimented with most of the above in the psql shell to make
sure I understand how it would work (the only thing I haven't tried
yet is the rule for deletes, but I'm hoping that won't be too
difficult to do either).
What I am hoping to do now is also fairly straight forward:
1. Write SQL::Translator based scripts that will generate these
private schemas on demand.
2. Modify the GFF3 bulk loader to be aware of the private schemas,
adding command line flags to insert into private schemas when desired.
 What I don't know is how easy it will be to make the queries in the
loader and other Chado aware tools compatible with this. What I am
hoping is that I can just do a "SET search_path" when the database
handle is created and be good to go. Does anybody have any experience
with this?
3. Modify the GBrowse Chado adaptor to be aware of the public schemas
as well (same caveat/question as in number 2 applies here as well)
4. Twist Ed Lee's arm to get him to do the same with Apollo.
So, what haven't I thought of? And what do you think of this idea in general?
Thanks,
Scott
-- 
------------------------------------------------------------------------
Scott Cain, Ph. D. scott at scottcain dot net
GMOD Coordinator (http://gmod.org/) 216-392-3087
Ontario Institute for Cancer Research