DDEV is the official local development tool of Drupal. And like Drupal, DDEV depends on the support of the open source community.Some notes on External Entities Database storage
Last updated on
20 January 2024
- External Entities Database storage (xnttdb) module works with SQL queries. If you're not familiar with SQL, it will be a problem.
- The SQL syntax used by the module is slightly different from the the real SQL: it uses a similar syntax as the one provided by Drupal for static SQL queries as it supports placeholders and table names should be surrounded by curly brackets (and unquoted identifiers by square brackets). The other difference with the Drupal syntax is that it is possible to work with multiple schemas (PostgreSQL) or databases (MySQL) by prefixing table names with a schema/database number followed by a colon (a feature from dbxschema module).
- MySQL and PostgreSQL have a different approach to handle multiple schemas. For PostgreSQL, a same database can hold multiple schemas and a same server can hold multiple databases. For MySQL, a database is a schema and a same server can hold multiple databases. In SQL, you can prefix a table by its schema name, for instance:
SELECT * FROM some_schema.some_table;. For PostgreSQL, "some_schema" will be a schema in current database (the one of current connection) while for MySQL, "some_schema" will be the name of another database (accessible through current connection on current server). However, even if it is possible in PostgreSQL to use the table name syntax "<database_name>.<schema_name>.<table_name>", current versions (up to 15) of PostgreSQL do not allow cross-database queries (all schemas must reside in a same database). So, basically, MySQL and PostgreSQL achieve the same kind of feature in different ways (in term of definitions) but with the same syntax. - Xnttdb allows to edit external database entities but it requires complex SQL queries to handle all cases. It might be wise to prefer writing SQL functions/procedures to handle entities creation, updating and deletion. Such function would receive all the field values and dispatch them in the appropriate tables or even translate their values (ie. string to integer for instance) when needed.
- It is wise to start with simple queries and then add complexity. One true power of External Entities is that it decouples data storage from the actual data model: while you can add and remove fields from the Drupal side and map them to SQL query fields, you can also add and remove SQL fields at will. In the tutorials provided here, the "Team" field is a good example as it could be returned by SQL as a string (team name) or an identifier (tid) and then mapped on the Drupal side as a text (team name) or an entity reference (from which the name can also be displayed).
- Since we are dealing with SQL queries, be careful what you are doing if you use edit features! Mistakes can be easily made in INSERT/UPDATE/DELETE queries, resulting to data losses! Backup your data and then experiment your queries! Once again, it might be wiser to write SQL functions/procedures in some cases.
- It is also possible to use xnttmuli module to work with 2 databases/schemas and use one for reading and the other one for writing. Many complex approaches can be considered!
Help improve this page
Page status: No known problems
You can:
You can:
- Log in, click Edit, and edit this page
- Log in, click Discuss, update the Page status value, and suggest an improvement
- Log in and create a Documentation issue with your suggestion
