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
Comment | File | Size | Author |
---|---|---|---|
#24 | 301038-24.patch | 17.53 KB | sokru |
Comments
Comment #1
hswong3i CreditAttribution: hswong3i commentedsubscript
Comment #2
moshe weitzman CreditAttribution: moshe weitzman commentedCould 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.
Comment #3
hswong3i CreditAttribution: hswong3i commentedI 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?
Comment #4
Crell CreditAttribution: Crell commentedComment #5
catchNo such thing as a critical task.
Comment #6
catchAs 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.
Comment #7
jhedstromComment #8
jhedstromHere'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.
Comment #9
phenaproximaIs 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.
Comment #12
claudiu.cristeaComment #13
claudiu.cristeaComment #15
phenaproximaRefactored 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.
Comment #23
andypostComment #24
sokru CreditAttribution: sokru as a volunteer commentedJust a reroll
Comment #25
sokru CreditAttribution: sokru as a volunteer commentedComment #26
Ghost of Drupal PastA 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
Comment #27
andypostComment #32
guignonv CreditAttribution: guignonv as a volunteer commentedComment #33
daffie CreditAttribution: daffie commentedThis sounds great, only when I look the code:
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:
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.getFieldSchema($table, $field)
for getting the schema data for a single field.Comment #34
daffie CreditAttribution: daffie commentedTalked 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.
Comment #35
Ghost of Drupal PastCould 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.Comment #36
daffie CreditAttribution: daffie commentedI 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 hassqlite_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: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.
Comment #37
catchTidying up the issue title and issue summary a bit.