Accessing an Internal Chado via psql (postgreSQL Interactive Terminal) or other GMOD Applications

laceysanderson's picture

Audience: 

Tripal Version: 

Requirements: 

Tripal 0.3b or higher
Chado in the same database as drupal

Short Description: 

When you access the postgreSQL database where chado is installed in the "chado" schema, you need to change the default schema searched in order to access chado data. Additionally, if another GMOD application is trying to access your chado database, the default schema will need to be changed for that user in order for chado tables to be found.

When Chado is installed via Tripal it is installed within the "chado" schema. Typically, Drupal is installed inside of the 'public' schema.  By default, PostgreSQL will quality table names for querying, creation or deletion using a search path.  By defaual, the search path for any new PostgreSQL database contains only the 'public' schema.  Tripal knows to look for Chado tables in the 'chado' schema but other applications will not.  Below are instructions for accessing chado tables using other applications.

Writing your own SQL (e.g. inside psql or phppgadmin):

For a Single Query: Simply prepend "chado." to the beginning of your table name. This indicates to postgreSQL the location of the table.  For example:

psql=# SELECT * FROM chado.organism;

For all queries in a given session: Set the PostgreSQL variable named 'search_path' to include the "chado" schema. This changes the default behaviour within the current session and allows PostgreSQL to find tables that are not prefixed with the 'chado.' schema qualifier.  For example:

psql=# SET search_path TO chado;

Keep in mind this will cause a "table not found" error if you attempt to query a table in another schema (such as Drupal tables in the public schema).  If you want to include both the 'chado' and 'public' schemas you can set the search path to "chado,public" which will search first the chado schema and then the public schema.

psql=# SET search_path TO chado,public;

Make search_path permanent: You can alter a user account to have a given search path so that you do not have to set the search_path for every session.  This can be done for each user and remains persistent even when the session is closed:

psql=# ALTER USER [username] SET search_path TO chado,public;

Set [username] to the name of the PostgreSQL user to alter.

Note: Do not set the search path for the Drupal user account, only for other user accounts.

Providing access to other applications:

In order to provide access to the chado database for other applications you must first create a PostgreSQL user account for that application and set permissions so that the user can access the chado schema.  Then alter the search path for the user account to include 'chado' using either 'psql' or 'phpggadmin' or your favorite tool. This ensures that that user will always have a default schema of 'chado' and the tool can find all tables, sequences, views and prepared statements.

psql=# ALTER USER [username] SET search_path TO chado;

Set [username] to the name of the PostgreSQL user to alter.

Note: Do not set the search path for the Drupal user account, only for other user accounts.

Category: