External Entities Database storage

This module is now part of External Entities module v3 (as "xnttsql"). No further development will be done here (only security fixes for people still using External Entities v2 with this plugin).

Synopsis

Database plugin for External Entities module. It enables the use of external database/schema data into Drupal as external entities. It requires Database Cross-Schema queries module to query multiple databases from a same Drupal instance.

The principle is that it builds your external entity object from an SQL query: the selected columns are your entity field. It is possible to use simple queries as well as really complex queries even with multiple tables, joins and sub-queries or computed values in the SELECT clause.

If you use PostgreSQL, you can query other schemas in the Drupal database (which resides in the "public" schema) and if you use MySQL, you can query the other databases accessible with your Drupal connection. If you want to work with other databases, you will have to add the credentials to your "settings.php". A same external entity using xnttdb will only be able to work with one connection. If you want to mix in a same external entity, data from multiple databases connections, you will have to use External Entities Multiple Storages.

For documentation on how to fill the different fields of an xnttdb, please refer to this documentation (#External Entities Database storage).

Notes:

• For PostgreSQL databases that support arrays of values for a returned field, it is possible to auto-expand those fields to multiple values if the field alias starts with "array_". For instance, the xnttdb raw field "array_joined_ids" from the query "SELECT ..., ARRAY_AGG(some_joined_table.id) AS "array_joined_ids" FROM ..." will be automatically expanded as an array of "id" which may be directly mapped to a Drupal field with an unlimited number of values.
  To use the "array_" feature with MySQL, you will have to generate yourself the appropriate array string: it must be surrounded by curly braces "{}" and values should be coma-separated (quoted values are supported).
• JSON data is supported and can be automatically expanded if the raw field name begins with "json_". Then, instead of returning a string for that field, a complex structure will be generated from the string that will be parsed as JSON data. For instance "SELECT '{"toto": 42}' AS "json_data" ..." will return a PHP associative array with a "toto" key having a value of 42. Then such structure could be mapped using complex field mappers (JSON Path, etc.).
• You can use the Xntt Manager to inspect the raw values returned by your xnttdb queries.

Requirements

• External Entities
• Database Cross-Schema queries

Recommended modules

• External Entities Manager to check and manage external entities (list, synchronization, etc.)
• External Entities Multiple Storages to mix/merge multiple sources. It might be required if you want to work with more than one additional connection to the Drupal one.

Extensions

This module can be used as a basis to implement schema-specific extensions. Such extensions would implement the Drupal\xnttdb\Plugin\ExternalEntities\StorageClient\Database class and just override buildConfigurationForm() to change the user form, and validateConfigurationForm() to manage in back-end the query generation (CRUD+LIST/COUNT) used by this module. It would be similar to what is done with the Wiki storage client over the REST storage client.
Therefore, I pledge the current UI and field names (config structure) won't be changed in the future (for this version at least).
An example of an extension module that hides behind the scene all the SQL stuff is the Chado Light module.

Roadmap

• Stabilize code (tests)

Pledges

I pledge to do my best to have this module working with Drupal 9.4+/10, and 11 when released.
The current UI and field names (config structure) won't be changed in the future (for this version at least) to allow extension by other modules.

Credits

The Alliance Bioversity - CIAT.

Restrictions

The only way to add external database connections is to add them in your settings.php file. Otherwise, you can only use the credentials of you Drupal database user: for PostgreSQL, you may be able to query other schema of Drupal database if Drupal database user has the appropriate permissions, for MySQL you can query other database on the same MySQL service allowed to you Drupal database user.

Due to the use of dbxschema, per-table prefixing is not supported anymore. It was deprecated in Drupal 9 and has been removed in Drupal 10 anyway.