Bug 6109 - Support column descriptions/comments
Summary: Support column descriptions/comments
Status: RESOLVED FIXED
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: 11.23.13 (Jun2016-SP2)
Hardware: All All
: Normal enhancement
Assignee: SQL devs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-10-26 23:45 CEST by Eyal Rozenberg
Modified: 2019-02-06 14:06 CET (History)
7 users (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Eyal Rozenberg 2016-10-26 23:45:30 CEST
User-Agent:       Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:49.0) Gecko/20100101 Firefox/49.0
Build Identifier: 

Some DBMSes, as well as applications dealing with tabular data, support descriptions/comments for table columns - essentially, a string associated with each column of a table (not each record). It's a usability feature that has no negative effect on query execution performance, but helps humans who work with the DB (e.g. by describing semantics, or units, or including a web link to other documentation etc.)

MonetDB seems not to support setting column comments/descriptions. It seems like it should not be very difficult to implement, considering how there's a sys.columns table - it would be another column in sys.column. There will also need to be a minor syntax extension; here:

https://www.monetdb.org/Documentation/SQLreference/TableIdentityColumn

there would be another variant for the column_option expansion, specifying a DESCRIPTION or a COMMENT.

Reproducible: Always
Comment 1 Eyal Rozenberg 2016-10-27 00:02:20 CEST
I might just do that some time, actually. Not until later this year, though.
Comment 2 Eyal Rozenberg 2016-10-27 00:03:56 CEST
(In reply to Eyal Rozenberg from comment #1)
Oh, sorry the comment spam; that was a reply to a comment by Martin asking whether I would like to add that feature. Like I said, I might.
Comment 3 Joeri van Ruth 2017-03-17 16:12:14 CET
Many databases allow one to attach a comment to various database
objects such as tables, columns, domains, functions, etc.  The
syntax varies between implementations: PostgreSQL, Oracle and
DB2 all have a "COMMENT ON <thing> IS <string>" statement.  MySQL
has "COMMENT [=] string" clauses in various parts of its CREATE
and ALTER statements.  MS SQL has this:

EXECUTE sp_addextendedproperty 'MS_Description',
     'This is my table comment',
     'user', @CurrentUser, 'table', 'TABLE_1'

SQLite does not have comments in the catalog at all but
preserves the regular -- comments found in the original CREATE
statement.

It might be easiest to begin with implementing 

    COMMENT ON <thing> IS <string>
    
and only as a second step implement the MySQL style extensions on the
CREATE and ALTER statements.

The following table first lists all suitable <thing>s available
in the MonetDB catalog, and whether or not the other databases
support a comment on it.  Below the dashes is a list of <thing>s
which other databases support but which do not have a suitable
counterpart in the MonetDB catalog:

| OBJECT                         | MONETDB | POSTGRES | ORACLE | DB2 | MYSQL |
|--------------------------------+---------+----------+--------+-----+-------|
| AGGREGATE [sc.] f (a...)       | y       | y        |        |     |       |
| COLUMN [sc.] r.c               | y       | y        | y      |     | y     |
| FILTER FUNCTION [sc.] f (a...) | y       |          |        |     |       |
| FUNCTION [sc.] f (a...)        | y       | y        |        | y   | y     |
| INDEX [ [sc.] r.] i            | y       | y        |        | y   |       |
| CONSTRAINT r.x                 | y       |          |        |     | y     |
| LOADER [sc.] f (a...)          | y       |          |        |     |       |
| PROCEDURE [sc.] f (a...)       | y       |          |        | y   | y     |
| ROLE ???                       | y       | y        |        | y   |       |
| SCHEMA sc                      | y       | y        |        |     |       |
| SEQUENCE [sc.] s               | y       | y        |        | y   |       |
| TABLE [sc.] r                  | y       | y        | y      | y   | y     |
| TRIGGER t ON [sc.] r           | y       | y        |        | y   |       |
| TYPE [sc.] t                   | y       | y        |        | y   |       |
| USER ???                       | y       |          |        |     |       |
| VIEW [sc.] v                   | y       | y        |        |     |       |
|--------------------------------+---------+----------+--------+-----+-------|
| ACCESS METHOD                  |         | y        |        |     |       |
| ALIAS                          |         |          |        | y   |       |
| CAST                           |         | y        |        |     |       |
| COLLATION                      |         | y        |        |     |       |
| CONSTRAINT                     |         | y        |        |     |       |
| CONVERSION                     |         | y        |        |     |       |
| DATABASE                       |         | y        |        |     |       |
| DOMAIN                         |         | y        |        |     |       |
| EXTENSION                      |         | y        |        |     |       |
| EVENT                          |         | y        |        |     | y     |
| FOREIGN DATA WRAPPER           |         | y        |        |     |       |
| FOREIGN TABLE                  |         | y        |        |     |       |
| INDEXTYPE                      |         |          | y      |     |       |
| LARGE OBJECT                   |         | y        |        |     |       |
| MATERIALIZED VIEW              |         | y        | y      |     |       |
| MASK                           |         |          |        | y   |       |
| OPERATOR [CLASS or FAMILY]     |         | y        | y      |     |       |
| PARTITION                      |         |          |        |     | y     |
| PACKAGE                        |         |          |        | y   |       |
| PERMISSION                     |         |          |        | y   |       |
| PLAN                           |         |          |        | y   |       |
| POLICY                         |         | y        |        |     |       |
| RULE                           |         | y        |        |     |       |
| SERVER                         |         | y        |        |     |       |
| SPECIFIC FUNCTION              |         |          |        | y   |       |
| TABLESPACE                     |         | y        |        |     | y     |
| TEXT SEARCH *                  |         | y        |        |     |       |
| TRANSFORM FOR                  |         | y        |        |     |       |
| TRUSTED CONTEXT                |         |          |        | y   |       |

DB2 seems to support commenting on a list of columns, at once.
This doesn't sound too useful.  However, DB2 also allows you to
leave out the parameter names of a function if there is no
ambiguity, which is a nice feature to have.

Comments can be dropped by setting them to NULL or ''.

I haven't yet checked exactly which privileges you need to be
able to set a comment.
Comment 4 Joeri van Ruth 2017-05-12 10:08:31 CEST
Pushed some code on a new branch named comment-on.

It includes some tests under sql/test/Tests/comment-on-*.sql

Still to do:

- add support for more database objects, this should be fairly straightforward

- extend mclient to display the comments

- extend msqldump to dump/restore them

- correctly deal with tmp tables

- verify error handling / error codes returned

- write documentation
Comment 5 Martin van Dinther cwiconfidential 2018-04-18 19:25:30 CEST
This enhancement request has been implemented in the Mar2018 release.
Extract from the Mar2018 release notes, SQL part:

Added support for COMMENT ON statements using SQL syntax:
COMMENT ON { SCHEMA | TABLE | VIEW | COLUMN | INDEX | SEQUENCE | FUNCTION | PROCEDURE | AGGREGATE | FILTER FUNCTION | LOADER } qname IS { 'my description text' | NULL | '' } ;

For COLUMN the qname can be "table_name"."column_name" or fully qualified as in: "schema_name"."table_name"."column_name".
For FUNCTION, PROCEDURE, AGGREGATE, FILTER FUNCTION and LOADER the qname may need to include the signature (argument types) to be able to differentiate between multiple overloaded functions which have the same name and schema.

By specifying IS NULL or IS '' you remove the comment for the database object.

If a database object is dropped, the associated comment is also removed.

Note: it is not allowed or possible to add comments for temporary tables or objects in schema "tmp".

The sql catalog has been extended with system table: sys.comments.
The keyword 'COMMENT' has now become a reserved keyword.