Migrate External Chado Database to Drupal

Tripal Version: 


These instructions are for migrating an external Chado database so that both the Drupal tables and Chado tables are in the same database, but in different schemas.  The Drupal tables will reside in the 'public' schema and the Chado tables will reside in the 'chado' schema.  Tripal supports Chado in an external database but prefers it to be local to the Drupal database.  This allows Tripal to take advantage of advanced features such as Drupal Views.   If you have an external Chado database and would like to use it for better functionality with Tripal follow these steps:

  1. Drupal must be installed.
  2. If you have any Tripal modules installed, you must first disable them all.  Note:  do not uninstall them, just disable them.
  3. If there is a 'chado' entry in the $db_url variable (Drupal 6) or the $database variable (Drupal 7) of the settings.php file you must first comment out or remove it.  (Note: you would only have a 'chado' entry if you previously used Tripal with Chado external to Drupal). if you do not have a 'chado' entry you can proceed.
  4. Drupal is housed in the 'public' schema of the Drupal database.  Temporarily rename the 'public' schema in the Drupal database to 'drupal'.
  5. Dump your Chado database to a file.  This should include the 'public', 'genetic_code', 'so', and  'frange' schemas.  An example command:
    pg_dump --no-owner --no-acl  -h [database host] -U [user] [chado database] > [chado database].sql

    where [database host] is the hostname of the database server, [user] is the login user used to dump the database, and [chado database] is the name of the chado database.

  6. Load the dumped file into your Drupal database.  This will load a new 'public' schema as well as the 'genetic_code', 'so' and 'frange' scheams.  The 'public' schema is where the tables of Chado reside.  An example command:
    psql -U [user] -h [database host] -d [drupal database] -f [chado database].sql 2>&1 | tee db_restore.log

    where [drupal database] is the name of the drupal database.  You can look at the db_restore.log file to check for any errors.

  7. Rename the new 'public' schema to 'chado'  (it must be named 'chado').
  8. Now, rename the 'drupal' schema back to 'public'.
  9. After loading your database run the following SQL command to fix a problem in the database restoral:
    SET search_path = frange,chado,pg_catalog;
    CREATE INDEX bingroup_boxrange ON featuregroup USING gist (chado.boxrange(fmin, fmax)) WHERE (is_root = 1);

    It is necessary to run these SQL commands because of an error that can be found in the db_restore.log file.  The error appears as follows: psql:tripal.sql:1932580: ERROR:  function create_point(integer, integer) does not exist LINE 1: SELECT box (create_point(0, $1), create_point($2,500000000)) HINT:  No function matches the given name and argument types. You might need to add explicit type casts. QUERY:  SELECT box (create_point(0, $1), create_point($2,500000000)) CONTEXT:  SQL function "boxrange" during inlining This error occurs when trying to create indexes for the frange.featuregroup table.  

    There are nested PSQL function calls needed to build one of the indexes for this table and the pg_dump utility does not set the necessary search_path.  Because this error is a problem with creation of an index there will be no data loss from the import.  But, the an index on the frange.featuregroup table will be missing.  The commands above restore that index.

  10. Re-enable the Tripal modules