missing view organism_feature_count

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

missing view organism_feature_count

Michael Dondrup
Hi,
I have successfully installed Tripal 0.3.1b on my local computer (mac with Drupal 6, php5, postgres 9.1), created custom organism pages and imported features from gff. Everything works fine except the Organism page. Every time I open any organism page I get the following warnings:

        • warning: pg_query() [function.pg-query]: Query failed: ERROR: relation "organism_feature_count" does not exist LINE 3: FROM organism_feature_count OFC ^ in /opt/local/www/data/drupal6/includes/database.pgsql.inc on line 139.
        • user warning: query: SELECT OFC.num_features,OFC.feature_type,CVT.definition FROM organism_feature_count OFC INNER JOIN cvterm CVT on OFC.cvterm_id = CVT.cvterm_id WHERE organism_id = 13 ORDER BY num_features desc in /opt/local/www/data/drupal6/sites/all/modules/tripal-6.x-0.3.1b/tripal_feature/tripal_feature.module on line 1338.

However I can see the number of features in the organism list and the features appear in the feature browser, while the Organism data type summary page tells me "There are no features available".

The view "organism_feature_count" appears in the Materialized Views page, but is 'not yet populated' according to page Admin/Tripal administration/Materialized views. I have run the update job repeatedly which finished with the message:

php sites/all/modules/tripal-6.x-0.3.1b/tripal_core/tripal_launch_jobs.php mdondrup
Tripal Job Launcher
-------------------
Calling: tripal_update_mview(2, 56)

However when looking at the database with pgAdmin, there exist several views but no view "organism_feature_count" are found inside the chado schema of the drupa DB.

So what to do? Should I create a view in pgAdmin using the SQL command provided by the Materialized View?

Thank you very much for your help.

Best
Michael

Michael Dondrup
Postdoctoral researcher
The Sea Lice Research Centre
Department of Informatics
University of Bergen
Thormøhlensgate 55, N-5008 Bergen,
Norway

Michael Dondrup
Postdoctoral researcher
The Sea Lice Research Centre
Department of Informatics
University of Bergen
Thormøhlensgate 55, N-5008 Bergen,
Norway


------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Gmod-tripal mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal
Reply | Threaded
Open this post in threaded view
|

Re: missing view organism_feature_count

Stephen Ficklin-2
Hi Michael,

That's odd that the organism_feature_count view was not created during
your install.  Do you remember any error messages when you installed the
feature module?  If you do not have too much data loaded you could
uninstall the feature module completely and reinstall it and see if that
fixes the problem.  Or, you can manually run the following SQL
statements to create the view:

CREATE TABLE organism_feature_count (
     organism_id integer,
     genus character varying(255),
     species character varying(255),
     common_name character varying(255),
     num_features integer,
     cvterm_id integer,
     feature_type character varying(255)
);
ALTER TABLE ONLY organism_feature_count
     ADD CONSTRAINT organism_feature_count_index UNIQUE (organism_id,
cvterm_id, feature_type);

Let me know if that helps.

Stephen

On 4/24/2012 5:37 AM, Michael Dondrup wrote:

> Hi,
> I have successfully installed Tripal 0.3.1b on my local computer (mac with Drupal 6, php5, postgres 9.1), created custom organism pages and imported features from gff. Everything works fine except the Organism page. Every time I open any organism page I get the following warnings:
>
> • warning: pg_query() [function.pg-query]: Query failed: ERROR: relation "organism_feature_count" does not exist LINE 3: FROM organism_feature_count OFC ^ in /opt/local/www/data/drupal6/includes/database.pgsql.inc on line 139.
> • user warning: query: SELECT OFC.num_features,OFC.feature_type,CVT.definition FROM organism_feature_count OFC INNER JOIN cvterm CVT on OFC.cvterm_id = CVT.cvterm_id WHERE organism_id = 13 ORDER BY num_features desc in /opt/local/www/data/drupal6/sites/all/modules/tripal-6.x-0.3.1b/tripal_feature/tripal_feature.module on line 1338.
>
> However I can see the number of features in the organism list and the features appear in the feature browser, while the Organism data type summary page tells me "There are no features available".
>
> The view "organism_feature_count" appears in the Materialized Views page, but is 'not yet populated' according to page Admin/Tripal administration/Materialized views. I have run the update job repeatedly which finished with the message:
>
> php sites/all/modules/tripal-6.x-0.3.1b/tripal_core/tripal_launch_jobs.php mdondrup
> Tripal Job Launcher
> -------------------
> Calling: tripal_update_mview(2, 56)
>
> However when looking at the database with pgAdmin, there exist several views but no view "organism_feature_count" are found inside the chado schema of the drupa DB.
>
> So what to do? Should I create a view in pgAdmin using the SQL command provided by the Materialized View?
>
> Thank you very much for your help.
>
> Best
> Michael
>
> Michael Dondrup
> Postdoctoral researcher
> The Sea Lice Research Centre
> Department of Informatics
> University of Bergen
> Thormøhlensgate 55, N-5008 Bergen,
> Norway
>
> Michael Dondrup
> Postdoctoral researcher
> The Sea Lice Research Centre
> Department of Informatics
> University of Bergen
> Thormøhlensgate 55, N-5008 Bergen,
> Norway
>
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> Gmod-tripal mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-tripal


------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Gmod-tripal mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal
Reply | Threaded
Open this post in threaded view
|

Re: missing view organism_feature_count

Michael Dondrup
Hi Stephen,

thank you very much for your reply, it worked. Using the SQL code below, I created the
table in the chado schema and the warnings disappeared. I wasn't aware of
the fact that materialized views were tables, not views, anyway the table had not
been created before.

As to what may have caused this I currently have no clue. I have not installed the
feature module separately, I have simply used the tripal administration to install chado,
then imported featured and ran sync features. I cannot remember error messages during setup
so I will re-run the complete drupal/tripal setup on an empty database to find out what went wrong.
At which stage is this table normally being created? Maybe I forgot to carry out an important step?

Best
Michael



On Apr 24, 2012, at 3:38 PM, Stephen Ficklin wrote:

> Hi Michael,
>
> That's odd that the organism_feature_count view was not created during
> your install.  Do you remember any error messages when you installed the
> feature module?  If you do not have too much data loaded you could
> uninstall the feature module completely and reinstall it and see if that
> fixes the problem.  Or, you can manually run the following SQL
> statements to create the view:
>
> CREATE TABLE organism_feature_count (
>     organism_id integer,
>     genus character varying(255),
>     species character varying(255),
>     common_name character varying(255),
>     num_features integer,
>     cvterm_id integer,
>     feature_type character varying(255)
> );
> ALTER TABLE ONLY organism_feature_count
>     ADD CONSTRAINT organism_feature_count_index UNIQUE (organism_id,
> cvterm_id, feature_type);
>
> Let me know if that helps.
>
> Stephen
>
> On 4/24/2012 5:37 AM, Michael Dondrup wrote:
>> Hi,
>> I have successfully installed Tripal 0.3.1b on my local computer (mac with Drupal 6, php5, postgres 9.1), created custom organism pages and imported features from gff. Everything works fine except the Organism page. Every time I open any organism page I get the following warnings:
>>
>> • warning: pg_query() [function.pg-query]: Query failed: ERROR: relation "organism_feature_count" does not exist LINE 3: FROM organism_feature_count OFC ^ in /opt/local/www/data/drupal6/includes/database.pgsql.inc on line 139.
>> • user warning: query: SELECT OFC.num_features,OFC.feature_type,CVT.definition FROM organism_feature_count OFC INNER JOIN cvterm CVT on OFC.cvterm_id = CVT.cvterm_id WHERE organism_id = 13 ORDER BY num_features desc in /opt/local/www/data/drupal6/sites/all/modules/tripal-6.x-0.3.1b/tripal_feature/tripal_feature.module on line 1338.
>>
>> However I can see the number of features in the organism list and the features appear in the feature browser, while the Organism data type summary page tells me "There are no features available".
>>
>> The view "organism_feature_count" appears in the Materialized Views page, but is 'not yet populated' according to page Admin/Tripal administration/Materialized views. I have run the update job repeatedly which finished with the message:
>>
>> php sites/all/modules/tripal-6.x-0.3.1b/tripal_core/tripal_launch_jobs.php mdondrup
>> Tripal Job Launcher
>> -------------------
>> Calling: tripal_update_mview(2, 56)
>>
>> However when looking at the database with pgAdmin, there exist several views but no view "organism_feature_count" are found inside the chado schema of the drupa DB.
>>
>> So what to do? Should I create a view in pgAdmin using the SQL command provided by the Materialized View?
>>
>> Thank you very much for your help.
>>
>> Best
>> Michael
>>
>> Michael Dondrup
>> Postdoctoral researcher
>> The Sea Lice Research Centre
>> Department of Informatics
>> University of Bergen
>> Thormøhlensgate 55, N-5008 Bergen,
>> Norway
>>
>> Michael Dondrup
>> Postdoctoral researcher
>> The Sea Lice Research Centre
>> Department of Informatics
>> University of Bergen
>> Thormøhlensgate 55, N-5008 Bergen,
>> Norway
>>
>>
>> ------------------------------------------------------------------------------
>> Live Security Virtual Conference
>> Exclusive live event will cover all the ways today's security and
>> threat landscape has changed and how IT managers can respond. Discussions
>> will include endpoint security, mobile security and the latest in malware
>> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
>> _______________________________________________
>> Gmod-tripal mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/gmod-tripal
>
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> Gmod-tripal mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-tripal


------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Gmod-tripal mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal
Reply | Threaded
Open this post in threaded view
|

Re: missing view organism_feature_count

Stephen Ficklin-2
Hi Michael,

I'm glad it helped. Yes, materialized views are simply tables that are
populated with a query which you can updated anytime you add new data.
It makes searches and other queries much faster than they normally would
be.

The organism_feature_count MView is created automatically when the
feature module is installed, so if there are any error messages they
would be visible just after the feature module is installed.

Cheers,
Stephen

On 4/25/2012 5:01 AM, Michael Dondrup wrote:

> Hi Stephen,
>
> thank you very much for your reply, it worked. Using the SQL code below, I created the
> table in the chado schema and the warnings disappeared. I wasn't aware of
> the fact that materialized views were tables, not views, anyway the table had not
> been created before.
>
> As to what may have caused this I currently have no clue. I have not installed the
> feature module separately, I have simply used the tripal administration to install chado,
> then imported featured and ran sync features. I cannot remember error messages during setup
> so I will re-run the complete drupal/tripal setup on an empty database to find out what went wrong.
> At which stage is this table normally being created? Maybe I forgot to carry out an important step?
>
> Best
> Michael
>
>
>
> On Apr 24, 2012, at 3:38 PM, Stephen Ficklin wrote:
>
>> Hi Michael,
>>
>> That's odd that the organism_feature_count view was not created during
>> your install.  Do you remember any error messages when you installed the
>> feature module?  If you do not have too much data loaded you could
>> uninstall the feature module completely and reinstall it and see if that
>> fixes the problem.  Or, you can manually run the following SQL
>> statements to create the view:
>>
>> CREATE TABLE organism_feature_count (
>>      organism_id integer,
>>      genus character varying(255),
>>      species character varying(255),
>>      common_name character varying(255),
>>      num_features integer,
>>      cvterm_id integer,
>>      feature_type character varying(255)
>> );
>> ALTER TABLE ONLY organism_feature_count
>>      ADD CONSTRAINT organism_feature_count_index UNIQUE (organism_id,
>> cvterm_id, feature_type);
>>
>> Let me know if that helps.
>>
>> Stephen
>>
>> On 4/24/2012 5:37 AM, Michael Dondrup wrote:
>>> Hi,
>>> I have successfully installed Tripal 0.3.1b on my local computer (mac with Drupal 6, php5, postgres 9.1), created custom organism pages and imported features from gff. Everything works fine except the Organism page. Every time I open any organism page I get the following warnings:
>>>
>>> • warning: pg_query() [function.pg-query]: Query failed: ERROR: relation "organism_feature_count" does not exist LINE 3: FROM organism_feature_count OFC ^ in /opt/local/www/data/drupal6/includes/database.pgsql.inc on line 139.
>>> • user warning: query: SELECT OFC.num_features,OFC.feature_type,CVT.definition FROM organism_feature_count OFC INNER JOIN cvterm CVT on OFC.cvterm_id = CVT.cvterm_id WHERE organism_id = 13 ORDER BY num_features desc in /opt/local/www/data/drupal6/sites/all/modules/tripal-6.x-0.3.1b/tripal_feature/tripal_feature.module on line 1338.
>>>
>>> However I can see the number of features in the organism list and the features appear in the feature browser, while the Organism data type summary page tells me "There are no features available".
>>>
>>> The view "organism_feature_count" appears in the Materialized Views page, but is 'not yet populated' according to page Admin/Tripal administration/Materialized views. I have run the update job repeatedly which finished with the message:
>>>
>>> php sites/all/modules/tripal-6.x-0.3.1b/tripal_core/tripal_launch_jobs.php mdondrup
>>> Tripal Job Launcher
>>> -------------------
>>> Calling: tripal_update_mview(2, 56)
>>>
>>> However when looking at the database with pgAdmin, there exist several views but no view "organism_feature_count" are found inside the chado schema of the drupa DB.
>>>
>>> So what to do? Should I create a view in pgAdmin using the SQL command provided by the Materialized View?
>>>
>>> Thank you very much for your help.
>>>
>>> Best
>>> Michael
>>>
>>> Michael Dondrup
>>> Postdoctoral researcher
>>> The Sea Lice Research Centre
>>> Department of Informatics
>>> University of Bergen
>>> Thormøhlensgate 55, N-5008 Bergen,
>>> Norway
>>>
>>> Michael Dondrup
>>> Postdoctoral researcher
>>> The Sea Lice Research Centre
>>> Department of Informatics
>>> University of Bergen
>>> Thormøhlensgate 55, N-5008 Bergen,
>>> Norway
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Live Security Virtual Conference
>>> Exclusive live event will cover all the ways today's security and
>>> threat landscape has changed and how IT managers can respond. Discussions
>>> will include endpoint security, mobile security and the latest in malware
>>> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
>>> _______________________________________________
>>> Gmod-tripal mailing list
>>> [hidden email]
>>> https://lists.sourceforge.net/lists/listinfo/gmod-tripal
>>
>> ------------------------------------------------------------------------------
>> Live Security Virtual Conference
>> Exclusive live event will cover all the ways today's security and
>> threat landscape has changed and how IT managers can respond. Discussions
>> will include endpoint security, mobile security and the latest in malware
>> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
>> _______________________________________________
>> Gmod-tripal mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/gmod-tripal


------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Gmod-tripal mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal
Reply | Threaded
Open this post in threaded view
|

Re: missing view organism_feature_count

Michael Dondrup
Hi,
now I know what happened. The cause was that I had messed with the configuration:
when I installed Tripal I configured it and tried it with a separate already populated chado database,
having a configuration
# $db_url = array(
#  'default' => 'pgsql://xxx:xxx@localhost/drupal',
#  'chado' => 'pgsql://xxx:xxx@localhost/lsalmonis_chado',
# );
and then activated the Tripal modules step by step.
But then I decided to try to start 'from scratch' and configured
a new empty database, and installed Tripal via the Tripal administrations,
but leaving all Tripal modules turned on during that.

In summary this approach of shifting databases doesn't work,
(it wasn't expected to because I tried undocumented stuff)
because some Tripal modules execute create table commands when they
are activated, something which I wasn't aware of. I assume they only do this with a
completely empty chado schema?

I guess that switching the instance to a new empty database might work if the tripal modules
are disabled before the switch and then turned on again after chado is installed in the empty db.

Is there a way to get a summary of SQL code executed by all Tripal modules after first activation
so I could prep my existing databases with it?

Thank you again
Michael



On Apr 25, 2012, at 12:57 PM, Stephen Ficklin wrote:

> Hi Michael,
>
> I'm glad it helped. Yes, materialized views are simply tables that are
> populated with a query which you can updated anytime you add new data.
> It makes searches and other queries much faster than they normally would
> be.
>
> The organism_feature_count MView is created automatically when the
> feature module is installed, so if there are any error messages they
> would be visible just after the feature module is installed.
>
> Cheers,
> Stephen
>
> On 4/25/2012 5:01 AM, Michael Dondrup wrote:
>> Hi Stephen,
>>
>> thank you very much for your reply, it worked. Using the SQL code below, I created the
>> table in the chado schema and the warnings disappeared. I wasn't aware of
>> the fact that materialized views were tables, not views, anyway the table had not
>> been created before.
>>
>> As to what may have caused this I currently have no clue. I have not installed the
>> feature module separately, I have simply used the tripal administration to install chado,
>> then imported featured and ran sync features. I cannot remember error messages during setup
>> so I will re-run the complete drupal/tripal setup on an empty database to find out what went wrong.
>> At which stage is this table normally being created? Maybe I forgot to carry out an important step?
>>
>> Best
>> Michael
>>
>>
>>
>> On Apr 24, 2012, at 3:38 PM, Stephen Ficklin wrote:
>>
>>> Hi Michael,
>>>
>>> That's odd that the organism_feature_count view was not created during
>>> your install.  Do you remember any error messages when you installed the
>>> feature module?  If you do not have too much data loaded you could
>>> uninstall the feature module completely and reinstall it and see if that
>>> fixes the problem.  Or, you can manually run the following SQL
>>> statements to create the view:
>>>
>>> CREATE TABLE organism_feature_count (
>>>     organism_id integer,
>>>     genus character varying(255),
>>>     species character varying(255),
>>>     common_name character varying(255),
>>>     num_features integer,
>>>     cvterm_id integer,
>>>     feature_type character varying(255)
>>> );
>>> ALTER TABLE ONLY organism_feature_count
>>>     ADD CONSTRAINT organism_feature_count_index UNIQUE (organism_id,
>>> cvterm_id, feature_type);
>>>
>>> Let me know if that helps.
>>>
>>> Stephen
>>>
>>> On 4/24/2012 5:37 AM, Michael Dondrup wrote:
>>>> Hi,
>>>> I have successfully installed Tripal 0.3.1b on my local computer (mac with Drupal 6, php5, postgres 9.1), created custom organism pages and imported features from gff. Everything works fine except the Organism page. Every time I open any organism page I get the following warnings:
>>>>
>>>> • warning: pg_query() [function.pg-query]: Query failed: ERROR: relation "organism_feature_count" does not exist LINE 3: FROM organism_feature_count OFC ^ in /opt/local/www/data/drupal6/includes/database.pgsql.inc on line 139.
>>>> • user warning: query: SELECT OFC.num_features,OFC.feature_type,CVT.definition FROM organism_feature_count OFC INNER JOIN cvterm CVT on OFC.cvterm_id = CVT.cvterm_id WHERE organism_id = 13 ORDER BY num_features desc in /opt/local/www/data/drupal6/sites/all/modules/tripal-6.x-0.3.1b/tripal_feature/tripal_feature.module on line 1338.
>>>>
>>>> However I can see the number of features in the organism list and the features appear in the feature browser, while the Organism data type summary page tells me "There are no features available".
>>>>
>>>> The view "organism_feature_count" appears in the Materialized Views page, but is 'not yet populated' according to page Admin/Tripal administration/Materialized views. I have run the update job repeatedly which finished with the message:
>>>>
>>>> php sites/all/modules/tripal-6.x-0.3.1b/tripal_core/tripal_launch_jobs.php mdondrup
>>>> Tripal Job Launcher
>>>> -------------------
>>>> Calling: tripal_update_mview(2, 56)
>>>>
>>>> However when looking at the database with pgAdmin, there exist several views but no view "organism_feature_count" are found inside the chado schema of the drupa DB.
>>>>
>>>> So what to do? Should I create a view in pgAdmin using the SQL command provided by the Materialized View?
>>>>
>>>> Thank you very much for your help.
>>>>
>>>> Best
>>>> Michael
>>>>
>>>> Michael Dondrup
>>>> Postdoctoral researcher
>>>> The Sea Lice Research Centre
>>>> Department of Informatics
>>>> University of Bergen
>>>> Thormøhlensgate 55, N-5008 Bergen,
>>>> Norway
>>>>
>>>> Michael Dondrup
>>>> Postdoctoral researcher
>>>> The Sea Lice Research Centre
>>>> Department of Informatics
>>>> University of Bergen
>>>> Thormøhlensgate 55, N-5008 Bergen,
>>>> Norway
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> Live Security Virtual Conference
>>>> Exclusive live event will cover all the ways today's security and
>>>> threat landscape has changed and how IT managers can respond. Discussions
>>>> will include endpoint security, mobile security and the latest in malware
>>>> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
>>>> _______________________________________________
>>>> Gmod-tripal mailing list
>>>> [hidden email]
>>>> https://lists.sourceforge.net/lists/listinfo/gmod-tripal
>>>
>>> ------------------------------------------------------------------------------
>>> Live Security Virtual Conference
>>> Exclusive live event will cover all the ways today's security and
>>> threat landscape has changed and how IT managers can respond. Discussions
>>> will include endpoint security, mobile security and the latest in malware
>>> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
>>> _______________________________________________
>>> Gmod-tripal mailing list
>>> [hidden email]
>>> https://lists.sourceforge.net/lists/listinfo/gmod-tripal
>
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> Gmod-tripal mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-tripal


------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Gmod-tripal mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal
Reply | Threaded
Open this post in threaded view
|

Re: missing view organism_feature_count

Stephen Ficklin-2
Hi Michael,

The only tables that Tripal creates in the chado database are the
materialized views.  However, there are no SQL statements in the code
for generating those tables.  The modules that add materialized views
call a Tripal API function call that dynamically constructs the SQLs for
creating the views.   If you wanted SQL for each materialized view, you
can use the pg_dump utility of postgres to dump the schema only of each
materialized view.  That was what I did to give you the SQL command for
the organism_feature_count table.

Tripal works best with Chaod & Drupal together in the same database.  
So, If you would like to use an existing Chado database with Tripal the
best way to do it is to follow these steps:

1.  Start with a fresh Drupal database. Do not install Tripal yet.
2.  Dump you existing Chado database using the pg_dump command from
postgres.  Chado has several schemas, but all of the common tables are
in the 'public' schema.  You'll want to dump the entire database to get
all of the schemas that come with Chado.
3.  Before importing the Chado schemas you need to rename the 'public'
schema to something temporary (e.g. 'public_temp').  By default, Drupal
installs it's primary tables into the 'public' schema.  We don't want to
mix the Drupal and Chado tables so 'public' needs to be renamed.
4.  Create a new 'public' schema and load your Chado database..
5.  After chado is loaded rename the 'public' schema (with the Chado
primary tables) to 'chado' and then rename your 'public_temp'  schema
(with Drupal tables) back to 'public'.
6.  Check the Drupal settings.php file to make sure there is no 'chado'
entry in the $db_url variable (perhaps left over from a previous setup).
7.  Now install/enable all the Tripal modules you like.

If you follow the instructions above the Drupal and Chado databases will
be merged, but with Chado tables in the 'chado' schema rather than the
public schema.  Step #7 will then create all materialized views in the
Chado schema as expected.

To take full advantage of the power of Drupal you will want the Chado &
Drupal schemas together in one database.  This is important if you
decide to start using Drupal Views to organize Chado data into custom
lists and tables.  The next version of Tripal (hopefully coming this
summer)  will have a new interface for creating custom search pages that
relies heavily on Drupal Views so to take advantage of the new features
you'll want the two together.

Stephen




On 4/26/2012 3:43 AM, Michael Dondrup wrote:

> Hi,
> now I know what happened. The cause was that I had messed with the configuration:
> when I installed Tripal I configured it and tried it with a separate already populated chado database,
> having a configuration
> # $db_url = array(
> #  'default' =>  'pgsql://xxx:xxx@localhost/drupal',
> #  'chado' =>  'pgsql://xxx:xxx@localhost/lsalmonis_chado',
> # );
> and then activated the Tripal modules step by step.
> But then I decided to try to start 'from scratch' and configured
> a new empty database, and installed Tripal via the Tripal administrations,
> but leaving all Tripal modules turned on during that.
>
> In summary this approach of shifting databases doesn't work,
> (it wasn't expected to because I tried undocumented stuff)
> because some Tripal modules execute create table commands when they
> are activated, something which I wasn't aware of. I assume they only do this with a
> completely empty chado schema?
>
> I guess that switching the instance to a new empty database might work if the tripal modules
> are disabled before the switch and then turned on again after chado is installed in the empty db.
>
> Is there a way to get a summary of SQL code executed by all Tripal modules after first activation
> so I could prep my existing databases with it?
>
> Thank you again
> Michael
>
>
>
> On Apr 25, 2012, at 12:57 PM, Stephen Ficklin wrote:
>
>> Hi Michael,
>>
>> I'm glad it helped. Yes, materialized views are simply tables that are
>> populated with a query which you can updated anytime you add new data.
>> It makes searches and other queries much faster than they normally would
>> be.
>>
>> The organism_feature_count MView is created automatically when the
>> feature module is installed, so if there are any error messages they
>> would be visible just after the feature module is installed.
>>
>> Cheers,
>> Stephen
>>
>> On 4/25/2012 5:01 AM, Michael Dondrup wrote:
>>> Hi Stephen,
>>>
>>> thank you very much for your reply, it worked. Using the SQL code below, I created the
>>> table in the chado schema and the warnings disappeared. I wasn't aware of
>>> the fact that materialized views were tables, not views, anyway the table had not
>>> been created before.
>>>
>>> As to what may have caused this I currently have no clue. I have not installed the
>>> feature module separately, I have simply used the tripal administration to install chado,
>>> then imported featured and ran sync features. I cannot remember error messages during setup
>>> so I will re-run the complete drupal/tripal setup on an empty database to find out what went wrong.
>>> At which stage is this table normally being created? Maybe I forgot to carry out an important step?
>>>
>>> Best
>>> Michael
>>>
>>>
>>>
>>> On Apr 24, 2012, at 3:38 PM, Stephen Ficklin wrote:
>>>
>>>> Hi Michael,
>>>>
>>>> That's odd that the organism_feature_count view was not created during
>>>> your install.  Do you remember any error messages when you installed the
>>>> feature module?  If you do not have too much data loaded you could
>>>> uninstall the feature module completely and reinstall it and see if that
>>>> fixes the problem.  Or, you can manually run the following SQL
>>>> statements to create the view:
>>>>
>>>> CREATE TABLE organism_feature_count (
>>>>      organism_id integer,
>>>>      genus character varying(255),
>>>>      species character varying(255),
>>>>      common_name character varying(255),
>>>>      num_features integer,
>>>>      cvterm_id integer,
>>>>      feature_type character varying(255)
>>>> );
>>>> ALTER TABLE ONLY organism_feature_count
>>>>      ADD CONSTRAINT organism_feature_count_index UNIQUE (organism_id,
>>>> cvterm_id, feature_type);
>>>>
>>>> Let me know if that helps.
>>>>
>>>> Stephen
>>>>
>>>> On 4/24/2012 5:37 AM, Michael Dondrup wrote:
>>>>> Hi,
>>>>> I have successfully installed Tripal 0.3.1b on my local computer (mac with Drupal 6, php5, postgres 9.1), created custom organism pages and imported features from gff. Everything works fine except the Organism page. Every time I open any organism page I get the following warnings:
>>>>>
>>>>> • warning: pg_query() [function.pg-query]: Query failed: ERROR: relation "organism_feature_count" does not exist LINE 3: FROM organism_feature_count OFC ^ in /opt/local/www/data/drupal6/includes/database.pgsql.inc on line 139.
>>>>> • user warning: query: SELECT OFC.num_features,OFC.feature_type,CVT.definition FROM organism_feature_count OFC INNER JOIN cvterm CVT on OFC.cvterm_id = CVT.cvterm_id WHERE organism_id = 13 ORDER BY num_features desc in /opt/local/www/data/drupal6/sites/all/modules/tripal-6.x-0.3.1b/tripal_feature/tripal_feature.module on line 1338.
>>>>>
>>>>> However I can see the number of features in the organism list and the features appear in the feature browser, while the Organism data type summary page tells me "There are no features available".
>>>>>
>>>>> The view "organism_feature_count" appears in the Materialized Views page, but is 'not yet populated' according to page Admin/Tripal administration/Materialized views. I have run the update job repeatedly which finished with the message:
>>>>>
>>>>> php sites/all/modules/tripal-6.x-0.3.1b/tripal_core/tripal_launch_jobs.php mdondrup
>>>>> Tripal Job Launcher
>>>>> -------------------
>>>>> Calling: tripal_update_mview(2, 56)
>>>>>
>>>>> However when looking at the database with pgAdmin, there exist several views but no view "organism_feature_count" are found inside the chado schema of the drupa DB.
>>>>>
>>>>> So what to do? Should I create a view in pgAdmin using the SQL command provided by the Materialized View?
>>>>>
>>>>> Thank you very much for your help.
>>>>>
>>>>> Best
>>>>> Michael
>>>>>
>>>>> Michael Dondrup
>>>>> Postdoctoral researcher
>>>>> The Sea Lice Research Centre
>>>>> Department of Informatics
>>>>> University of Bergen
>>>>> Thormøhlensgate 55, N-5008 Bergen,
>>>>> Norway
>>>>>
>>>>> Michael Dondrup
>>>>> Postdoctoral researcher
>>>>> The Sea Lice Research Centre
>>>>> Department of Informatics
>>>>> University of Bergen
>>>>> Thormøhlensgate 55, N-5008 Bergen,
>>>>> Norway
>>>>>
>>>>>
>>>>> ------------------------------------------------------------------------------
>>>>> Live Security Virtual Conference
>>>>> Exclusive live event will cover all the ways today's security and
>>>>> threat landscape has changed and how IT managers can respond. Discussions
>>>>> will include endpoint security, mobile security and the latest in malware
>>>>> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
>>>>> _______________________________________________
>>>>> Gmod-tripal mailing list
>>>>> [hidden email]
>>>>> https://lists.sourceforge.net/lists/listinfo/gmod-tripal
>>>> ------------------------------------------------------------------------------
>>>> Live Security Virtual Conference
>>>> Exclusive live event will cover all the ways today's security and
>>>> threat landscape has changed and how IT managers can respond. Discussions
>>>> will include endpoint security, mobile security and the latest in malware
>>>> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
>>>> _______________________________________________
>>>> Gmod-tripal mailing list
>>>> [hidden email]
>>>> https://lists.sourceforge.net/lists/listinfo/gmod-tripal
>>
>> ------------------------------------------------------------------------------
>> Live Security Virtual Conference
>> Exclusive live event will cover all the ways today's security and
>> threat landscape has changed and how IT managers can respond. Discussions
>> will include endpoint security, mobile security and the latest in malware
>> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
>> _______________________________________________
>> Gmod-tripal mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/gmod-tripal


------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Gmod-tripal mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal