Derby
  1. Derby
  2. DERBY-4379

Let´s add comments to Derby SQL Syntax

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 10.8.2.2
    • Fix Version/s: 11.0.0.0
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      N/A
    • Urgency:
      Normal
    • Issue & fix info:
      Newcomer

      Description

      I could not find any previous issue about adding comments to Derby.
      I found one suggestion about it on the web somewhere but not here in Jira.

      DB2 and Oracle seem to have a separate COMMENT ON clause
      Eg.
      COMMENT ON TABLE EMPLOYEE IS 'Reflects first quarter 2000 reorganization'
      COMMENT ON COLUMN mytable.primarykey IS 'Unique ID from Sequence SEQ_MASTER'

      MySql on the other hand has a more compact syntax
      CREATE TABLE FOO (A COMMENT 'This col is A') COMMENT='And here is the table comment'

      I quess SQL standard does not talk about commenting objects like tables columns etc. (Although I am not sure, maybe someone could prove me wrong here).

      So I propose we start with syntax like

      CREATE TABLE TBL_NAME (<coldefinition> COMMENT 'colcomment' ...) COMMENT ' tablecomment'

      Column comment could appear anywhere where Column-level-constraint can and the same would apply for table comment.
      View comment could come after the query in view definition.

      We would only need to add reserved word COMMENT. (Although it is a common word and most certainly is used by someone as a column or tanle name).

      It might be that there is already a spot for comments (or should we say remarks) because the DatabaseMetadata returns a column with that name for every attribute.
      It is always empty now.

      This feature could take the self-documenting property of derby databases to the next level.

      I could code this feature but now I would like to know what people think about this issue in here and since I have not been coding Derby before then perhaps a few pointers would be helpful from someone who knows the soucecode of Derby well.

        Activity

        Hide
        Knut Anders Hatlen added a comment -

        It sounds like a useful feature to me. Adding reserved words that are not reserved in the standard sounds unattractive because it makes it harder to port applications to Derby and may also break existing applications using Derby. Perhaps we could add system procedures instead? Something like:

        CALL SYSCS_UTIL.SYSCS_COMMENT_ON_TABLE('MYSCHEMA', 'MYTABLE', '...');
        CALL SYSCS_UTIL.SYSCS_COMMENT_ON_COLUMN('MYSCHEMA', 'MYTABLE', 'COL1', '...');

        I don't think we have a free slot in which to store comments currently, but it should be easy enough to add comment columns to some of the system tables (like SYS.SYSTABLES and SYS.SYSCOLUMNS).

        To change the system tables and add new system procedures, these two classes should be a good starting point:

        org.apache.derby.catalog.SystemProcedures
        org.apache.derby.impl.sql.catalog.DataDictionaryImpl

        The different system tables and their columns are documented here: http://db.apache.org/derby/docs/dev/ref/rrefsistabs38369.html

        Show
        Knut Anders Hatlen added a comment - It sounds like a useful feature to me. Adding reserved words that are not reserved in the standard sounds unattractive because it makes it harder to port applications to Derby and may also break existing applications using Derby. Perhaps we could add system procedures instead? Something like: CALL SYSCS_UTIL.SYSCS_COMMENT_ON_TABLE('MYSCHEMA', 'MYTABLE', '...'); CALL SYSCS_UTIL.SYSCS_COMMENT_ON_COLUMN('MYSCHEMA', 'MYTABLE', 'COL1', '...'); I don't think we have a free slot in which to store comments currently, but it should be easy enough to add comment columns to some of the system tables (like SYS.SYSTABLES and SYS.SYSCOLUMNS). To change the system tables and add new system procedures, these two classes should be a good starting point: org.apache.derby.catalog.SystemProcedures org.apache.derby.impl.sql.catalog.DataDictionaryImpl The different system tables and their columns are documented here: http://db.apache.org/derby/docs/dev/ref/rrefsistabs38369.html
        Hide
        Lukas Eder added a comment -

        I am missing this feature as well. Many databases support the Oracle-style COMMENT ON syntax as originally suggested by Rami Ojares. MySQL's syntax seems a bit inconsistent to me, as the syntax for commenting on tables is quite different from the one for commenting on columns. That's not the case with

        COMMENT ON [object] IS [comment].

        Since the "reserved word" COMMENT is at the beginning of a new command, I don't see how that would interfere with the current Derby syntax definition. It should be possible to introduce that keyword without causing trouble to commands like:

        CREATE TABLE COMMENT (COMMENT VARCHAR(100));

        Show
        Lukas Eder added a comment - I am missing this feature as well. Many databases support the Oracle-style COMMENT ON syntax as originally suggested by Rami Ojares. MySQL's syntax seems a bit inconsistent to me, as the syntax for commenting on tables is quite different from the one for commenting on columns. That's not the case with COMMENT ON [object] IS [comment] . Since the "reserved word" COMMENT is at the beginning of a new command, I don't see how that would interfere with the current Derby syntax definition. It should be possible to introduce that keyword without causing trouble to commands like: CREATE TABLE COMMENT (COMMENT VARCHAR(100));
        Hide
        Rick Hillegas added a comment -

        Patrick Meyer reports on derby-user that this feature would be useful for tagging tables so that they can be organized into types: http://mail-archives.apache.org/mod_mbox/db-derby-user/201107.mbox/%3C4E3594C3.30804@gmail.com%3E

        Show
        Rick Hillegas added a comment - Patrick Meyer reports on derby-user that this feature would be useful for tagging tables so that they can be organized into types: http://mail-archives.apache.org/mod_mbox/db-derby-user/201107.mbox/%3C4E3594C3.30804@gmail.com%3E

          People

          • Assignee:
            Unassigned
            Reporter:
            Rami Ojares
          • Votes:
            3 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:

              Development