Reference Manual needs information on generated columns

• Key: DERBY-3959
• Type: Improvement
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: Kim Haase
• Reporter: Kim Haase
• Component/s: Documentation
• Affects Version/s: 10.5.1.1
• Fix Version/s: 10.5.1.1

Description

The Generated Columns specification attached to DERBY-481 describes changes that need to be made to the Derby Reference Manual to document this new feature. This issue covers the implementation of these changes.

Comments

[Kim Haase]

The GeneratedColumns.html spec shows the same column-definition syntax for both CREATE TABLE and ALTER TABLE:

column-definition ::= Simple-column-Name [ DataType ]
    [ Column-level-constraint ]*
    [ [ WITH ] DEFAULT DefaultConstantExpression
       | generated-column-spec
       | generation-clause
    ]
    [ Column-level-constraint ]*

Currently, though, the syntax described for CREATE TABLE in http://db.apache.org/derby/docs/dev/ref/rrefsqlj30540.html#rrefsqlj30540 is not quite the same as the syntax described for ALTER TABLE in http://db.apache.org/derby/docs/dev/ref/rrefsqlj81859.html. The syntax for CREATE TABLE is the same as what's in the Generated Columns spec except for the changes. But the syntax for ALTER TABLE looks different:

Simple-column-Name DataType
[ Column-level-constraint ]*
[ [ WITH ] DEFAULT DefaultConstantExpression ]

It lacks both the generated-column-spec and the additional Column-level-constraint at the end. Should it be changed to be the same?

[Kim Haase]

Working on the ALTER TABLE information, I noticed the following sentence under "Dropping columns":

"DROP COLUMN is not allowed if sqlAuthorization is true (see DERBY-1909)."

It appears that DERBY-1909 has been fixed, so that this restriction is no longer in force. Is that true? Can I cut this sentence?

[Rick Hillegas]

Hi Kim,

Thanks for looking at this issue.

1) You are right that you have fewer options with ALTER TABLE. The ALTER TABLE syntax should be changed as follows:

Simple-column-Name [ DataType ]
[ Column-level-constraint ]*
[ [ WITH ] DEFAULT DefaultConstantExpression
   | generation-clause
]

2) I agree that DERBY-1909 removes the restriction you found.

Thanks!

[Kim Haase]

Thanks very much, Rick.

I guess that means that in the ALTER TABLE topic, the following statement under "Adding columns" is not quite true:

"The syntax for the column-definition for a new column is the same as for a column in a CREATE TABLE statement."

The sentence after that is

"This means that a column constraint can be placed on the new column within the ALTER TABLE ADD COLUMN statement."

I suppose I should change the two sentences to something like this:

"The syntax for the column-definition for a new column is almost the same as for a column in a CREATE TABLE statement. This syntax allows a column constraint to be placed on the new column within the ALTER TABLE ADD COLUMN statement."

[Kim Haase]

Another question: for the syntax

  GENERATED ALWAYS AS ( values-expression )

how should I define "values-expression"? I can't find it elsewhere in the Reference Manual. Is it any of the expressions described in "SQL expressions" (http://db.apache.org/derby/docs/dev/ref/rrefsqlj19433.html) or are there limits?

[Rick Hillegas]

Hi Kim,

Your rewording of the ALTER TABLE material looks good to me. You could also say "The syntax for the column-definition for a new column is a subset of the syntax for a column in a CREATE TABLE statement..."

What I meant by value-expression is what the Reference Guide just calls an Expression. This syntax is mentioned in the Reference Guide in the topic titled "VALUES expression". But you are right, the Reference Guide doesn't define what an Expression is.

Thanks,
-Rick

[Kim Haase]

Attaching DERBY-3959.diff, DERBY-3959.stat, and DERBY-3959.zip, which make the Reference Manual fixes described in the Generated Columns specification.

One clarification: there are no changes to the CREATE TABLE topic itself, but only to the topic on column-definition that it references (which in turn references the new topic on the generation-clause).

Please let me know if changes are needed, or if I've missed anything.

[Rick Hillegas]

Thanks for the patch, Kim. Looks good. Some comments follow:

Two typos:

rrefsqlj81859
  "Column definition" section: "if" should be capitalized.

rrefsqlj30540
  1st paragraph: "if" should be capitalized.

Substantive issue:

Both rrefsqlj30540 and rrefsqljrevoke say that a table will be dropped if one of its generation clauses depends on a role-based EXECUTE privilege which is subsquently dropped. The functional spec says that this is the expected behavior but subsequent discussion on DERBY-481 has failed to find good motivation for this harsh behavior and, in fact, this is not how Derby behaves right now. A little more discussion may happen related to this issue on DERBY-3949. However, I suspect that I will end up removing this harsh assertion from the functional spec and we should therefore remove this from the user documentation too.

Thanks,
-Rick

[Kim Haase]

Thanks very much, Rick. Just to be clear -- I gather you're suggesting that I fix rrefsqlj30540 and rrefsqljrevoke now, rather than wait for the final decision on the behavior?

I'll get to these items pretty soon.

[Rick Hillegas]

Hi Kim,

Yes, I would vote for removing the harsh assertion from those two pages. I'd say there's a small chance that we will need to revisit this issue based on the discussion on DERBY-3949. Thanks.

[Kim Haase]

Thanks, Rick.

Filing a revised patch, DERBY-3959-2.diff, DERBY-3959-2.stat, and DERBY-3959-2.zip, which fixes the typos in rrefsqlj81859 and rrefsqlj30540 and removes the warning from rrefsqljrevoke and rrefsqljgenerationclause.

This means that rrefsqljrevoke is unchanged, so it's no longer part of the patch. The file changes are now as follows:

M src/ref/rrefsqljrenamecolumnstatement.dita
M src/ref/rrefsqlj26498.dita
M src/ref/rrefdropfunctionstatement.dita
M src/ref/rrefsqlj40774.dita
M src/ref/rrefsqlj81859.dita
M src/ref/rrefcreateprocedurestatement.dita
M src/ref/rrefsqlj30540.dita
A src/ref/rrefsqljgenerationclause.dita
M src/ref/refderby.ditamap
M src/ref/rrefcreatefunctionstatement.dita
M src/ref/rrefsqlj43125.dita

[Rick Hillegas]

Thanks, Kim. I've taken a look at the files you changed. They look good to me.

[Kim Haase]

Thanks very much, Rick!

Committed patch DERBY-3959-2.diff to documentation trunk at revision 733908.

[Kim Haase]

Closing, since the revised docs now appear in the Latest Alpha Docs.