Problem/Motivation

There is currently no cross-compatible way to retrieve schema information from the database.

All database tables used to be defined via hook_schema(). Schema information could be determined via hook_schema(), although this only provides definitions, not what's actually in the database (hence the Drupal 6 schema module.

Additionally many services define their database schema lazily (like the cache system) outside hook_schema(), meaning the full database structure can't easily be recreated via those definitions anyway.

Proposed resolution

Add a schema introspection API. This would primarily be used for tests to verify that the schema changes in update functions result in the changes intended, but it may have auditing uses later/outside core.

Remaining tasks

User interface changes

API changes

Data model changes

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

hswong3i’s picture

subscript

moshe weitzman’s picture

Could we not require that the 3rd party DB be described using hook_schema or hook_schema_alter()? We might have enhance the schema APi to recognize multiple databases. Just a thought.

hswong3i’s picture

...to hit the database and derive a schema structure for a table on the fly as needed.

I don't really catch the idea much... Is that means fetching table/column structure from database on-the-fly? E.g. only fetch info with same $db_prefix from database though SQL?

Crell’s picture

Version: 7.x-dev » 8.x-dev
catch’s picture

Priority: Critical » Major

No such thing as a critical task.

catch’s picture

Category: task » feature
Priority: Major » Normal

As far as I can see there's no longer any direct dependency between postgres and the schema API now (which makes me wonder why drupal_get_schema() is in bootstrap.inc). Recategorising, if I missed something move it back.

jhedstrom’s picture

Version: 8.0.x-dev » 8.1.x-dev
Assigned: Unassigned » jhedstrom
Issue summary: View changes
jhedstrom’s picture

Here's a start, ran out of time. The pgsql schema info is coming back in such a way that I haven't figured out how to map it back to the Drupal schema types.

phenaproxima’s picture

Is it possible to use the information_schema views for this stuff? My low-level database-fu is not very strong, but I'm pretty sure MySQL and PostgreSQL both support information_schema, so we could support both of them with a trait.

Version: 8.1.x-dev » 8.2.x-dev

Drupal 8.1.0-beta1 was released on March 2, 2016, which means new developments and disruptive changes should now be targeted against the 8.2.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.2.x-dev » 8.3.x-dev

Drupal 8.2.0-beta1 was released on August 3, 2016, which means new developments and disruptive changes should now be targeted against the 8.3.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

claudiu.cristea’s picture

Status: Needs work » Needs review
claudiu.cristea’s picture

Assigned: jhedstrom » Unassigned

Status: Needs review » Needs work

The last submitted patch, 8: get-table-schema-301038-08.patch, failed testing.

phenaproxima’s picture

Status: Needs work » Needs review
FileSize
16.46 KB

Refactored and re-rolled for 8.3.x. This introduces a new SchemaIntrospectionInterface so that we can easily tell which database drivers support schema generation of this kind. Other than that, it just moves all of the logic from DbDumpCommand into the MySQL Schema implementation.

Status: Needs review » Needs work

The last submitted patch, 15: 301038-15.patch, failed testing.

Version: 8.3.x-dev » 8.4.x-dev

Drupal 8.3.0-alpha1 will be released the week of January 30, 2017, which means new developments and disruptive changes should now be targeted against the 8.4.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.4.x-dev » 8.5.x-dev

Drupal 8.4.0-alpha1 will be released the week of July 31, 2017, which means new developments and disruptive changes should now be targeted against the 8.5.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.5.x-dev » 8.6.x-dev

Drupal 8.5.0-alpha1 will be released the week of January 17, 2018, which means new developments and disruptive changes should now be targeted against the 8.6.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.6.x-dev » 8.7.x-dev

Drupal 8.6.0-alpha1 will be released the week of July 16, 2018, which means new developments and disruptive changes should now be targeted against the 8.7.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.7.x-dev » 8.8.x-dev

Drupal 8.7.0-alpha1 will be released the week of March 11, 2019, which means new developments and disruptive changes should now be targeted against the 8.8.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.8.x-dev » 8.9.x-dev

Drupal 8.8.0-alpha1 will be released the week of October 14th, 2019, which means new developments and disruptive changes should now be targeted against the 8.9.x-dev branch. (Any changes to 8.9.x will also be committed to 9.0.x in preparation for Drupal 9’s release, but some changes like significant feature additions will be deferred to 9.1.x.). For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

andypost’s picture

Issue tags: +Needs reroll
sokru’s picture

Just a reroll

sokru’s picture

Status: Needs work » Needs review
Issue tags: -Needs reroll
Ghost of Drupal Past’s picture

A blast from the past. When this was filed , dinosaurs still roamed the earth and MySQL was much less SQL compliant than it is today. Today, I believe there is no need for SHOW commands and information_schema can be used which is part of ANSI SQL 92 and could be used at least for MySQL, PostgreSQL, SQL Server, Sybase, ORACLE and IBM DB2.

https://dev.mysql.com/doc/refman/5.5/en/information-schema.html
https://www.postgresql.org/docs/9.1/information-schema.html

andypost’s picture

Status: Needs review » Needs work

The last submitted patch, 24: 301038-24.patch, failed testing. View results
- codesniffer_fixes.patch Interdiff of automated coding standards fixes only.

Version: 8.9.x-dev » 9.1.x-dev

Drupal 8.9.0-beta1 was released on March 20, 2020. 8.9.x is the final, long-term support (LTS) minor release of Drupal 8, which means new developments and disruptive changes should now be targeted against the 9.1.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Version: 9.1.x-dev » 9.2.x-dev

Drupal 9.1.0-alpha1 will be released the week of October 19, 2020, which means new developments and disruptive changes should now be targeted for the 9.2.x-dev branch. For more information see the Drupal 9 minor version schedule and the Allowed changes during the Drupal 9 release cycle.

Version: 9.2.x-dev » 9.3.x-dev

Drupal 9.2.0-alpha1 will be released the week of May 3, 2021, which means new developments and disruptive changes should now be targeted for the 9.3.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

guignonv’s picture

daffie’s picture

A blast from the past. When this was filed , dinosaurs still roamed the earth and MySQL was much less SQL compliant than it is today. Today, I believe there is no need for SHOW commands and information_schema can be used which is part of ANSI SQL 92 and could be used at least for MySQL, PostgreSQL, SQL Server, Sybase, ORACLE and IBM DB2.

This sounds great, only when I look the code:

$db_column_size = $this->connection->query("SELECT data_type FROM information_schema.columns WHERE table_schema = '$this->databaseName' and table_name='$table_name' and column_name='$column_name';")->fetchField();

which uses the information_schema and works fine for MySQL, it fails for PostgreSQL and SQLite. Therefor my conclusion is that we need to do this issue. Also other drupal code should use the new SchemaInspection API and not start querying the information_schema for schema data themselves.
This gives every database driver the possibility to implement their specific code for getting the schema data out of its database.

This issue is postponing #2885413: Timestamp field items are affected by 2038 bug and #3215062: Update hook_schema for Y2038. Both issues are critical, therefor this issue will also be critical. As those 2 issues are bug fixes, I am changing this issue to a task instead of a feature request.

A couple of changes that I would like to see in the patch are:

  1. Lets create a new service called SchemaInspection instead of more code into the Schema class. All the new methods are about getting schema data out of the database. Something that the Schema class does not do.
  2. Can we add an extra method to the class called getFieldSchema($table, $field) for getting the schema data for a single field.
daffie’s picture

Priority: Critical » Normal

Talked to @catch on Slack about this and his suggestion was to not postpone the issues #2885413: Timestamp field items are affected by 2038 bug and #3215062: Update hook_schema for Y2038, because they only need it in a test.

Ghost of Drupal Past’s picture

which uses the information_schema and works fine for MySQL, it fails for PostgreSQL and SQLite.

Could you share how it fails? I looked in PostgreSQL: https://www.db-fiddle.com/f/bePTbgB7RvVB3o1gddUiEr/0 and it seems working.

For SQLite, I think the driver on init could do a variant of attach database '' as information_schema; create view information_schema.columns as select * from sqlite_master; -- I verified this as valid syntax in SQLite.

daffie’s picture

Could you share how it fails? I looked in PostgreSQL: https://www.db-fiddle.com/f/bePTbgB7RvVB3o1gddUiEr/0 and it seems working.

I have updated the patch for #3215062: Update hook_schema for Y2038. Search for the test: Y2038TimestampUpdateTest.

Most regular databases work have the view information_schema. The problem is that between MySQL and PostgreSQL there are little differences. Like with a column of the type integer, MySQL returns 'int' and PostgreSQL returns 'integer'. The column: 'table_schema' holds for MySQL the database name and for PostgreSQL the schema name. There are probably a lot more of those differences. Not only between MySQL and PostgreSQL, but also with Oracle and SQL-server.

SQLite does not do information_schema, but it has sqlite_master. The view has the following columns: 'type', 'name', 'tbl_name', 'rootpage' and 'sql'. The first couple of column are good for selecting and the last column stores all the info that you need. Something like:

CREATE TABLE `test_people` (
	`name`	TEXT,
	`age`	INTEGER,
	`job`	TEXT NOT NULL,
	PRIMARY KEY(`job`)
)

You get the info, only in a different way than with information_schema.

When we do this issue we get a nice API for module developers to query the database for table/field/index meta data. It work than for all databases. When we do not do it we get solutions that only work for a single database. In core we get the class Drupal\Core\Command\DbDumpCommand which only works for MySQL. The same for #65474: [meta] Y2K38: Unix Millennium bug. It now work for MySQL and PostgreSQL. It does not need to work for SQLite as integer columns are already of the type bigint. I do not think that for contrib modules the situation is different. Most people working with Drupal do not care for any other database than the one they working with.

catch’s picture

Title: Support on-the-fly schema detection » Add a cross-compatible database schema introspection API
Issue summary: View changes

Tidying up the issue title and issue summary a bit.

Version: 9.3.x-dev » 9.4.x-dev

Drupal 9.3.0-rc1 was released on November 26, 2021, which means new developments and disruptive changes should now be targeted for the 9.4.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.4.x-dev » 9.5.x-dev

Drupal 9.4.0-alpha1 was released on May 6, 2022, which means new developments and disruptive changes should now be targeted for the 9.5.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.5.x-dev » 10.1.x-dev

Drupal 9.5.0-beta2 and Drupal 10.0.0-beta2 were released on September 29, 2022, which means new developments and disruptive changes should now be targeted for the 10.1.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 10.1.x-dev » 11.x-dev

Drupal core is moving towards using a “main” branch. As an interim step, a new 11.x branch has been opened, as Drupal.org infrastructure cannot currently fully support a branch named main. New developments and disruptive changes should now be targeted for the 11.x branch, which currently accepts only minor-version allowed changes. For more information, see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.