Multiple chado instances and ChadoConnection behavior

[laceysanderson]

Originally submitted by @guignonv
NOTE: BioDB Is now Tripal DBX

With BioDb layer, it is possible to work with multiple Chado instances at the same time (in a same SQL query for instance).
There are 2 ways to specify other Chado instances:

1. by code, using for instance $connection->addExtraSchema('chado2');
2. through settings.php (site config file) by specifying extra schema in the 'prefix' key of the database connection options array: 'prefix' => ['2' => 'chado2',].

I coded the default behavior but I think it should be discussed here and maybe changed.
The default behavior is that settings.php will override whatever extra schema name is specified by the code. So if you set 'prefix' => ['2' => 'chado2',] in settings.php and the use $connection->addExtraSchema('chado_private'); in your code, querying the second schema in a query (ie. SELECT * FROM {2:feature};) will query chado2 and not chado_private. The motivation of my choice was that people changing settings.php are advanced users/admins and they know what they do. If they want to force the use of a specific secondary schema, they can do it. However, it might be convenient to see it the other way: settings.php provides default extra schemas and the code should be able to override those choices.
I am mitigated and I think it needs to be discussed.

Note: changing default behavior from one to another is quite easy (swapping parts of code).

EDIT: by the way, the 'prefix' database option is a native Drupal feature and can be used to prefix specific tables. I don't know all the uses cases but I know an old one I used: it is useful when you have several Drupal websites (especially subsites) and want to share users across all sites without having your users to register on each on write an automated script to do that. All your Drupal sites would reside in a same database with specific table prefixes (ie. site1_, site2_, etc.) and you add a specific prefix for the "users" table(s) that is common to all your sites (ie. common_). In thoses cases, the 'prefix' parameter will be an array and not a string.
ex.:

• site 1: 'prefix' => ['default' => 'site1_', 'users' =>'common_', 'users_data' => 'common_', ...]
• site 2: 'prefix' => ['default' => 'site2_', 'users' =>'common_', 'users_data' => 'common_', ...] etc.
  It used to work well with Drupal 6 but I don't know if it still works though.

Another thing: if you want Drupal to reside in a different schema than 'public' in your PostgreSQL database, you can specify it using the prefix option followed by a dot. For instance, I want Drupal to use the 'drupal' postgreSQL schema, I would set: 'prefix' => 'drupal.' (it is a native supported feature of Drupal and not just a trick).

One last thing: the 'prefix' setting is changed by automated tests to isolate tests tables from the production tables in test environments.