Show table descriptions in PHPMyAdmin and other tools

+1

If present, I could harvest this information in api.module to provide nicer developer documentation, too.

Moving from x.y.z queue to 6.x-dev.

From the issue description, this isn't fixed, as far as I can tell, but I would certainly like it to be! :) Fixing title appropriately.

In 6.x we spend a lot of time documenting each field in the database schema, which is great if you have a copy of the source code handy. However, nowhere does Schema API translate these into actual COMMENT statements so that they are displayed in PHPMyAdmin and other tools. Right now the only place to see them in a browser is with Schema module, which most people don't know about.

But, it would be awesome if it did translate these to the DB-specific COMMENT equivalents where applicable so that other tools could see this information on their own.

See also #28046: add comments to database.mysql

Here's a proof of concept. In mysql, table descriptions can only be 60 characters, but column descriptions can be up to 255 characters. For postgres there is a separate COMMENT command, http://www.postgresql.org/docs/8.3/static/sql-comment.html

I don't quite get the changes in DatabaseSchema::createTable(). What are you doing there?

update_sql() cannot handle placeholders (now needed for the comments) so I replaced it with db_query(). Hopefully this can be refactored, it's a hack :)

Not sure if postgres allows you to add comments in the schema definition for a column, or only with separate COMMENT command. If the latter, then creating or altering a column will need to return an array of multiple SQL statements and a placeholder, rather than just a string of SQL.

The $ret array is going to die as soon as I get around to overhauling the schema. The new query logger will replace it. At this point I'll say go ahead and ignore it, just make the queries do what you need to do.

In this patch, table and column comments should be working for both mysql and pgsql. I couldn't get db_query() placeholders to work in pgsql: prepared statements and COMMENT statements don't seem to play nice? But this was a good thing as I realized I should just use PDO::quote() to prepare the comments and add them directly to the query strings.

hm, we probably need a db_quote() wrapper if we don't have one already...

Let's see if this passes tests. Previous patch had a flaw where applying table prefixes to comments would cause them to exceed MySQL's max comment length.

I saw some weirdness with this patch on my own test environment and am guessing it's related to this bug: http://bugs.php.net/bug.php?id=41125 I'd like to know if anyone else is seeing segfaults and/or uncompleted tests, if so we might need a work-around.

Chasing head and testing a workaround for http://bugs.php.net/bug.php?id=41125

One note about this patch. I am converting schema descriptions from HTML format to plain text, because db tools like Phpmyadmin do not render comments as HTML. Specifically, a string such as <none> needs to be decoded to <none>.

Instead of doing the "if (driver == 'mysql')" inside DatabaseSchema::prepareComment(), I think it is cleaner to do the MySQL-specific stuff in DatabaseSchema_mysql::prepareComment().

Instead of replacing apostrophe with back-tick I suggest using U+2019. “This is the preferred character to use for apostrophe,” says Wikipedia.

Thanks, I was thinking there might be a better unicode solution :)

Looks good. I have tested this with MySQL+phpMyAdmin and PostgreSQL+pgAdmin.

(I hope we can get rid of the HTML in the schema descriptions - but that is separate project)

I'd also like to go with plain-text schema descriptions. This only involves patching ~3 lines in aggregator.install and block.install.

This is pretty cool. Should we write some tests for this?

I'm not sure if tests could be written in a DB-independent way. (I've not used table comments before on any DB.)

Yeah, we'd have to add some mysql- and pgsql-specific methods for getting the comments.

In MySQL we can easily get the table and column comments from the INFORMATION_SCHEMA views. in postgres, it's not in information_schema, we apparently have to use something like this instead: SELECT pg_description.description FROM pg_description, pg_class, pg_attribute WHERE pg_attribute.attrelid = pg_class.oid AND pg_description.objoid = pg_class.oid AND pg_description.objsubid = pg_attribute.attnum AND pg_class.relname = 'node' AND pg_attribute.attname = 'nid'

I added tests.

Thanks for the tests. Committed to CVS HEAD.

So do we need to be adding descriptions in all of our update functions that use db_add_table, db_add/change_field, etc?

(Sorry, remarking as fixed so it will show up and someone can answer it)

Yes, these update functions should get the full schema specification including description.

Could you please file a new issue to make sure all existing update functions in core get schema descriptions with them? There are a lot that do not. Post the link back here so I can followup. Thanks!

#423948: Update functions should include schema descriptions