Chado Views Integration

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Chado Views Integration

Kucheran, Lacey Sanderson
I've created a drupal module that integrates chado fields with views (for those of you not familiar with drupal, the views module allows you to create filterable, sortable tables without programming). This module does not solve the problem of joining data between two databases. Instead, what it does is make chado fields available independent of drupal. Thus each table can only contain data from chado.

Tables that have been implemented: feature, organism, library, cvterm, cv, dbxref, db
There are also default views for feature, organism, library, cvterm, dbxref with sorting, filtering and access control built-in.

These tables do have access to the node ID (necessary to link these tables back to the drupal nodes) through means of a computed field. Essentially, each node ID is retrieved from the database individually using db_query and the chado ID (ie: feature_id, library_id, organism_id) during rendering of the table. Because this field doesn't exist in the database (chado) you can't sort or filter based on the node ID.

Implications of selecting data directly from chado independent of drupal:
  1. The views created select directly from chado and cannot include drupal content,                                                                                                                                                   
     this means:                                                                                                                                                                                                                       
     a) these views will contain data not sync'd with drupal                                                                                                                                                                           
     b) you cannot filter these views by any node fields such as published, user, etc.                                                                                                                                                 
     c) the node ID fields available are computed fields -meaning a database query is                                                                                                                                                  
        executed for every row of the table                                                                                                                                                                                            
  2. Default views are provided for features, organisms, libraries, cvterms, and dbxrefs                                                                                                                                               
  3. Currently only the base table (except in the case of cvterms and dbxrefs which also                                                                                                                                               
     implement cv and db respectively) are implemented. Thus you cannot currently access                                                                                                                                               
     properties, etc.                                                                                                                                                                                                                  
  4. For Database References, currently only the main feature database reference                                                                                                                                                       
     (ie: referenced by feature.dbxref_id) is being displayed.

Please comment if this is functionality that you want so that I know whether to implement more tables. Also any feedback would be welcome!

Thanks,
Lacey

PS. Stephen: Is it okay that I set the package=Tripal?

------------------------------------------------------
Lacey-Anne Sanderson
Bioinformaticist
Pulse Crop Breeding and Genetics
Phone: (306) 966-2430
Room 3D10 Argriculture
Department of Plant Sciences
University of Saskatchewan


------------------------------------------------------------------------------
This SF.net Dev2Dev email is sponsored by:

Show off your parallel programming skills.
Enter the Intel(R) Threading Challenge 2010.
http://p.sf.net/sfu/intel-thread-sfd
_______________________________________________
Gmod-tripal mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal

chado_views_integration_v1.0.tar.gz (12K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Chado Views Integration

Lacey-Anne Sanderson
Hi Stephen,

That was my first thought when trying to implement views for chado -each in their own schema in the same database. One thing I did come across is that Drupal doesn't support schema's out of the box. There is a patch that modifies core to add this functionality -so that you can set the search_path in settings.php. However, when two databases are defined in settings.php it sets the search_path for first one and then the other (overwriting the first). Thus we would need to set the search_path to drupal's schema and then prefix every chado query with the schema name... You're right, definitely not backwards compatible. However, I think having views is worth losing backwards compatibility.

I'm all for the two namespaces within one database. As for which of the two should be in the public namespace...
If drupal is:
- protect chado database from drupal
- work out of the box with drupal (no patching of core)
If chado is:
- ensures compatibility with other GMOD tools like apollo

I haven't used any of the other applications that can use chado as a backend thus don't really know how big a problem chado being in a separate schema would be. Are there ways to set the search path for many of the applications?

Lacey

------------------------------------------------------
Lacey-Anne Sanderson
Bioinformaticist
Pulse Crop Breeding and Genetics
Phone: (306) 966-2430
Room 3D10 Argriculture
Department of Plant Sciences
University of Saskatchewan

On 2010-09-07, at 3:56 PM, Stephen Ficklin wrote:


Hi Lacey,

Sorry for my slow response.   We've recently been struggling with how to implement Views in Tripal.  We recognize that others may not want to look at the data in the pre-canned way we have organized it..  Thanks for taking the plunge and doing some work here!  

The point has been raised that we should perhaps try and integrate Drupal and chado into the same database schema.  Perhaps into different namespaces in the same database to avoid name collisions.   My initial thought would be to install Drupal into the chado database rather than the other way around.

I wanted to throw out this thought to see what others think?  It would mean that version 3.0 would not be backwards compatible with previous installations.

Stephen

On Wed, Sep 1, 2010 at 5:07 PM, Stephen Ficklin <[hidden email]> wrote:


------------------------------------------- 
From: Lacey-Anne Sanderson[[hidden email]
Sent: Wednesday, September 01, 2010 4:16:38 PM 
To: GMOD Tripal; Stephen Ficklin 
Subject: [Gmod-tripal] Chado Views Integration 
Auto forwarded by a Rule I've created a drupal module that integrates chado fields with views (for those of you not familiar with drupal, the views module allows you to create filterable, sortable tables without programming). This module does not solve the problem of joining data between two databases. Instead, what it does is make chado fields available independent of drupal. Thus each table can only contain data from chado.

Tables that have been implemented: feature, organism, library, cvterm, cv, dbxref, db
There are also default views for feature, organism, library, cvterm, dbxref with sorting, filtering and access control built-in.

These tables do have access to the node ID (necessary to link these tables back to the drupal nodes) through means of a computed field. Essentially, each node ID is retrieved from the database individually using db_query and the chado ID (ie: feature_id, library_id, organism_id) during rendering of the table. Because this field doesn't exist in the database (chado) you can't sort or filter based on the node ID.

Implications of selecting data directly from chado independent of drupal:
  1. The views created select directly from chado and cannot include drupal content,                                                                                                                                                   
     this means:                                                                                                                                                                                                                       
     a) these views will contain data not sync'd with drupal                                                                                                                                                                           
     b) you cannot filter these views by any node fields such as published, user, etc.                                                                                                                                                 
     c) the node ID fields available are computed fields -meaning a database query is                                                                                                                                                  
        executed for every row of the table                                                                                                                                                                                            
  2. Default views are provided for features, organisms, libraries, cvterms, and dbxrefs                                                                                                                                               
  3. Currently only the base table (except in the case of cvterms and dbxrefs which also                                                                                                                                               
     implement cv and db respectively) are implemented. Thus you cannot currently access                                                                                                                                               
     properties, etc.                                                                                                                                                                                                                  
  4. For Database References, currently only the main feature database reference                                                                                                                                                       
     (ie: referenced by feature.dbxref_id) is being displayed.

Please comment if this is functionality that you want so that I know whether to implement more tables. Also any feedback would be welcome!

Thanks,
Lacey

PS. Stephen: Is it okay that I set the package=Tripal?


------------------------------------------------------------------------------
This SF.net Dev2Dev email is sponsored by:

Show off your parallel programming skills.
Enter the Intel(R) Threading Challenge 2010.
http://p.sf.net/sfu/intel-thread-sfd
_______________________________________________
Gmod-tripal mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal
Reply | Threaded
Open this post in threaded view
|

Re: Chado Views Integration

Scott Cain
Hi Lacey-Anne,

I'm not a Drupal expert, but doesn't Views 3 support multiple
databases?  Wouldn't that obviate the need for having the drupal and
chado schemas in the same database?  Of course, Views 3 is still early
in development.

Scott


On Wed, Sep 8, 2010 at 1:02 PM, Lacey-Anne Sanderson
<[hidden email]> wrote:

> Hi Stephen,
>
> That was my first thought when trying to implement views for chado -each in
> their own schema in the same database. One thing I did come across is that
> Drupal doesn't support schema's out of the box. There is a patch that
> modifies core to add this functionality -so that you can set the search_path
> in settings.php. However, when two databases are defined in settings.php it
> sets the search_path for first one and then the other (overwriting the
> first). Thus we would need to set the search_path to drupal's schema and
> then prefix every chado query with the schema name... You're right,
> definitely not backwards compatible. However, I think having views is worth
> losing backwards compatibility.
>
> I'm all for the two namespaces within one database. As for which of the two
> should be in the public namespace...
>
> If drupal is:
>
> - protect chado database from drupal
>
> - work out of the box with drupal (no patching of core)
>
> If chado is:
>
> - ensures compatibility with other GMOD tools like apollo
>
> I haven't used any of the other applications that can use chado as a backend
> thus don't really know how big a problem chado being in a separate schema
> would be. Are there ways to set the search path for many of the
> applications?
>
> Lacey
>
> ------------------------------------------------------
>
> Lacey-Anne Sanderson
>
> Bioinformaticist
>
> Pulse Crop Breeding and Genetics
>
> Phone: (306) 966-2430
>
> Room 3D10 Argriculture
>
> Department of Plant Sciences
>
> University of Saskatchewan
>
> On 2010-09-07, at 3:56 PM, Stephen Ficklin wrote:
>
>
> Hi Lacey,
>
> Sorry for my slow response.   We've recently been struggling with how
> to implement Views in Tripal.  We recognize that others may not want to look
> at the data in the pre-canned way we have organized it..  Thanks for taking
> the plunge and doing some work here!
>
> The point has been raised that we should perhaps try and integrate Drupal
> and chado into the same database schema.  Perhaps into different namespaces
> in the same database to avoid name collisions.   My initial thought would be
> to install Drupal into the chado database rather than the other way around.
>
> I wanted to throw out this thought to see what others think?  It would mean
> that version 3.0 would not be backwards compatible with previous
> installations.
>
> Stephen
>
> On Wed, Sep 1, 2010 at 5:07 PM, Stephen Ficklin
> <[hidden email]> wrote:
>
>
> -------------------------------------------
>
> From: Lacey-Anne Sanderson[SMTP:[hidden email]]
>
> Sent: Wednesday, September 01, 2010 4:16:38 PM
>
> To: GMOD Tripal; Stephen Ficklin
>
> Subject: [Gmod-tripal] Chado Views Integration
>
> Auto forwarded by a Rule I've created a drupal module that integrates chado
> fields with views (for those of you not familiar with drupal, the views
> module allows you to create filterable, sortable tables without
> programming). This module does not solve the problem of joining data between
> two databases. Instead, what it does is make chado fields available
> independent of drupal. Thus each table can only contain data from chado.
>
> Tables that have been implemented: feature, organism, library, cvterm, cv,
> dbxref, db
>
> There are also default views for feature, organism, library, cvterm, dbxref
> with sorting, filtering and access control built-in.
>
> These tables do have access to the node ID (necessary to link these tables
> back to the drupal nodes) through means of a computed field. Essentially,
> each node ID is retrieved from the database individually using db_query and
> the chado ID (ie: feature_id, library_id, organism_id) during rendering of
> the table. Because this field doesn't exist in the database (chado) you
> can't sort or filter based on the node ID.
>
> Implications of selecting data directly from chado independent of drupal:
>
>   1. The views created select directly from chado and cannot include drupal
> content,
>
>
>
>      this means:
>
>
>
>
>      a) these views will contain data not sync'd with drupal
>
>
>
>
>      b) you cannot filter these views by any node fields such as published,
> user, etc.
>
>
>
>      c) the node ID fields available are computed fields -meaning a database
> query is
>
>
>
>         executed for every row of the table
>
>
>
>
>   2. Default views are provided for features, organisms, libraries, cvterms,
> and dbxrefs
>
>
>
>   3. Currently only the base table (except in the case of cvterms and
> dbxrefs which also
>
>
>
>      implement cv and db respectively) are implemented. Thus you cannot
> currently access
>
>
>
>      properties, etc.
>
>
>
>
>   4. For Database References, currently only the main feature database
> reference
>
>
>
>      (ie: referenced by feature.dbxref_id) is being displayed.
>
> Please comment if this is functionality that you want so that I know whether
> to implement more tables. Also any feedback would be welcome!
>
> Thanks,
>
> Lacey
>
> PS. Stephen: Is it okay that I set the package=Tripal?
>
> ------------------------------------------------------------------------------
> This SF.net Dev2Dev email is sponsored by:
>
> Show off your parallel programming skills.
> Enter the Intel(R) Threading Challenge 2010.
> http://p.sf.net/sfu/intel-thread-sfd
> _______________________________________________
> Gmod-tripal mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-tripal
>
>



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

------------------------------------------------------------------------------
This SF.net Dev2Dev email is sponsored by:

Show off your parallel programming skills.
Enter the Intel(R) Threading Challenge 2010.
http://p.sf.net/sfu/intel-thread-sfd
_______________________________________________
Gmod-tripal mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal
Reply | Threaded
Open this post in threaded view
|

Re: Chado Views Integration

Lacey-Anne Sanderson
I guess I'm just too impatient to wait for Drupal 7 :) Drupal 7 is supposed to have a completely rewritten database API which is rumoured to support multiple databases, web services, and even imaginary fields.

We could simply stick with my not quite full integration (you can create views with any chado fields and the drupal nid but no other drupal fields) until drupal 7 and views 3 come out...

Maybe a more important issue is porting tripal to drupal 7?

------------------------------------------------------
Lacey-Anne Sanderson
Bioinformaticist
Pulse Crop Breeding and Genetics
Phone: (306) 966-2430
Room 3D10 Argriculture
Department of Plant Sciences
University of Saskatchewan

On 2010-09-08, at 12:27 PM, Scott Cain wrote:

Hi Lacey-Anne,

I'm not a Drupal expert, but doesn't Views 3 support multiple
databases?  Wouldn't that obviate the need for having the drupal and
chado schemas in the same database?  Of course, Views 3 is still early
in development.

Scott


On Wed, Sep 8, 2010 at 1:02 PM, Lacey-Anne Sanderson
<[hidden email]> wrote:
Hi Stephen,

That was my first thought when trying to implement views for chado -each in
their own schema in the same database. One thing I did come across is that
Drupal doesn't support schema's out of the box. There is a patch that
modifies core to add this functionality -so that you can set the search_path
in settings.php. However, when two databases are defined in settings.php it
sets the search_path for first one and then the other (overwriting the
first). Thus we would need to set the search_path to drupal's schema and
then prefix every chado query with the schema name... You're right,
definitely not backwards compatible. However, I think having views is worth
losing backwards compatibility.

I'm all for the two namespaces within one database. As for which of the two
should be in the public namespace...

If drupal is:

- protect chado database from drupal

- work out of the box with drupal (no patching of core)

If chado is:

- ensures compatibility with other GMOD tools like apollo

I haven't used any of the other applications that can use chado as a backend
thus don't really know how big a problem chado being in a separate schema
would be. Are there ways to set the search path for many of the
applications?

Lacey

------------------------------------------------------

Lacey-Anne Sanderson

Bioinformaticist

Pulse Crop Breeding and Genetics

Phone: (306) 966-2430

Room 3D10 Argriculture

Department of Plant Sciences

University of Saskatchewan

On 2010-09-07, at 3:56 PM, Stephen Ficklin wrote:


Hi Lacey,

Sorry for my slow response.   We've recently been struggling with how
to implement Views in Tripal.  We recognize that others may not want to look
at the data in the pre-canned way we have organized it..  Thanks for taking
the plunge and doing some work here!

The point has been raised that we should perhaps try and integrate Drupal
and chado into the same database schema.  Perhaps into different namespaces
in the same database to avoid name collisions.   My initial thought would be
to install Drupal into the chado database rather than the other way around.

I wanted to throw out this thought to see what others think?  It would mean
that version 3.0 would not be backwards compatible with previous
installations.

Stephen

On Wed, Sep 1, 2010 at 5:07 PM, Stephen Ficklin
<[hidden email]> wrote:


-------------------------------------------

From: Lacey-Anne Sanderson[SMTP:[hidden email]]

Sent: Wednesday, September 01, 2010 4:16:38 PM

To: GMOD Tripal; Stephen Ficklin

Subject: [Gmod-tripal] Chado Views Integration

Auto forwarded by a Rule I've created a drupal module that integrates chado
fields with views (for those of you not familiar with drupal, the views
module allows you to create filterable, sortable tables without
programming). This module does not solve the problem of joining data between
two databases. Instead, what it does is make chado fields available
independent of drupal. Thus each table can only contain data from chado.

Tables that have been implemented: feature, organism, library, cvterm, cv,
dbxref, db

There are also default views for feature, organism, library, cvterm, dbxref
with sorting, filtering and access control built-in.

These tables do have access to the node ID (necessary to link these tables
back to the drupal nodes) through means of a computed field. Essentially,
each node ID is retrieved from the database individually using db_query and
the chado ID (ie: feature_id, library_id, organism_id) during rendering of
the table. Because this field doesn't exist in the database (chado) you
can't sort or filter based on the node ID.

Implications of selecting data directly from chado independent of drupal:

  1. The views created select directly from chado and cannot include drupal
content,



     this means:




     a) these views will contain data not sync'd with drupal




     b) you cannot filter these views by any node fields such as published,
user, etc.



     c) the node ID fields available are computed fields -meaning a database
query is



        executed for every row of the table




  2. Default views are provided for features, organisms, libraries, cvterms,
and dbxrefs



  3. Currently only the base table (except in the case of cvterms and
dbxrefs which also



     implement cv and db respectively) are implemented. Thus you cannot
currently access



     properties, etc.




  4. For Database References, currently only the main feature database
reference



     (ie: referenced by feature.dbxref_id) is being displayed.

Please comment if this is functionality that you want so that I know whether
to implement more tables. Also any feedback would be welcome!

Thanks,

Lacey

PS. Stephen: Is it okay that I set the package=Tripal?

------------------------------------------------------------------------------
This SF.net Dev2Dev email is sponsored by:

Show off your parallel programming skills.
Enter the Intel(R) Threading Challenge 2010.
http://p.sf.net/sfu/intel-thread-sfd
_______________________________________________
Gmod-tripal mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal





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


------------------------------------------------------------------------------
This SF.net Dev2Dev email is sponsored by:

Show off your parallel programming skills.
Enter the Intel(R) Threading Challenge 2010.
http://p.sf.net/sfu/intel-thread-sfd
_______________________________________________
Gmod-tripal mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal