[jira] [Commented] (DERBY-7008) Add support for COMMENT ON [ object ] IS '...' statements

["Rick Hillegas (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-7008?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16624386#comment-16624386 ] 

Rick Hillegas commented on DERBY-7008:
--------------------------------------

Some thoughts off the top of my head:

PostgreSQL supports the Oracle syntax on more schema objects: https://www.postgresql.org/docs/9.1/static/sql-comment.html.

An alternative syntax to consider would be one patterned on GRANT/REVOKE, requiring that the user declare what kind of object is being commented. This would disambiguate schema objects which have the same schema-qualified name but which live in different namespaces:

{noformat}
ADD COMMENT $commentString
  ON (COLUMN | TABLE | FUNCTION | PROCEDURE | TYPE | SEQUENCE | ROLE)
  $objectName

DROP COMMENT
  FROM (COLUMN | TABLE | FUNCTION | PROCEDURE | TYPE | SEQUENCE | ROLE)
  $objectName
{noformat}

The following java.sql.DatabaseMetaData methods (among others) return a REMARKS column for each schema object:

{noformat}
getColumns()
getFunctions()
getProcedures()
getTables()
getUDTs()
{noformat}

We would want the following behaviors:

1) When authorization is turned on, then only the object owner or DBO can issue the command.

2) There should be a way to alter/delete comments (see above).

3) The comment should be dropped when the object is dropped.

I think that we would need a new SYSCOMMENTS catalog.


> Add support for COMMENT ON [ object ] IS '...' statements
> ---------------------------------------------------------
>
>                 Key: DERBY-7008
>                 URL: https://issues.apache.org/jira/browse/DERBY-7008
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.14.2.0
>            Reporter: Lukas Eder
>            Priority: Major
>
> A lot of RDBMS support some way of commenting on stored objects. For instance, Oracle has this syntax:
> {{COMMENT ON <object> IS 'some comment'}}
> This is very useful when navigating an unknown schema, for instance to explain the meaning of a column with some encoded values, etc. Also, there is tool support when these comments are exposed through the dictionary views and/or through JDBC's DatabaseMetaData
> I personally prefer Oracle's syntax over e.g. MySQL's (inline comments in CREATE TABLE) or SQL Server's (some system function).
> Ideally, all objects can receive comments, including:
>  * Tables (and views, etc.)
>  * Columns
>  * Routines
>  * Schemas
>  * Indexes
>  * etc.
> But I think the most important ones are tables and columns



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)