db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-481) implement SQL generated columns
Date Fri, 24 Oct 2008 20:15:44 GMT

     [ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

Rick Hillegas updated DERBY-481:

    Attachment: derby-481-03-aa-grammar.diff

Attaching derby-481-03-aa-grammar.diff. I am running regression tests now.

This patch does the following:

1) Makes it possible to declare generated columns in CREATE TABLE statements.

2) Adjusts the dblook tool to emit the correct DDL for tables with generated columns.

3) Adds some basic syntax tests for CREATE TABLE statements which declare generated columns.

4) Verifies that you can declare generated columns only after hard-upgrading to 10.5.

I will file new JIRAs to track the following known deficiencies:

A) You cannot add generated columns using ALTER TABLE yet. This behavior can not be tested
until we check in the UPDATE logic for generated columns.

B) The ANSI/ISO grammar lets you omit the column datatype on columns with generation clauses.
The patch does not support this elegant shorthand.

Touches the following files:


M      java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj

Wires GENERATED ALWAYS AS (...) into the CREATE/ALTER TABLE grammar. This bit of grammar is
known in the SQL spec as a "generation clause".

M      java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
M      java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java
M      java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java
A      java/engine/org/apache/derby/impl/sql/compile/GenerationClauseNode.java
M     java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java

Adds a new kind of Abstract Syntax Tree (AST) node to represent generation clauses. In the
AST, generation clauses decorate column definitions.


M      java/engine/org/apache/derby/impl/sql/compile/TableElementList.java

Logic to drive the bind() phase for generation clauses.

M      java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java
M      java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java
M      java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
M      java/engine/org/apache/derby/impl/sql/compile/ValueNode.java

Bind-time logic to prevent non-deterministic functions from appearing in generation clauses.

M      java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java
M      java/engine/org/apache/derby/impl/sql/compile/DDLStatementNode.java
M      java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java

Factored some very similar bind-time logic out of the AST nodes for CREATE and ALTER TABLE.
Consolidated this logic in the superclass of these two nodes.


M      java/engine/org/apache/derby/catalog/types/DefaultInfoImpl.java
M      java/tools/org/apache/derby/impl/tools/dblook/DB_Table.java

Code to make the dblook utility correctly emit generation clauses when printing CREATE TABLE


M      java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java

Uncomments the basic grammar tests. Adds a test to verify that generation clauses are correctly

M      java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_5.java

Adds a test case to verify that you can declare generation clauses only after hard-upgrading
to 10.5.

M      java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB.sql
M      java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/dblook_test_net.out
M      java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/dblook_test_net_territory.out
M      java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
M      java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out
M      java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out
M      java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out

Beefs up the dblook test with some examples of CREATE TABLE statements which have generation

> implement SQL generated columns
> -------------------------------
>                 Key: DERBY-481
>                 URL: https://issues.apache.org/jira/browse/DERBY-481
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions:
>            Reporter: Rick Hillegas
>            Assignee: Rick Hillegas
>         Attachments: derby-481-00-aa-prototype.diff, derby-481-01-aa-catalog.diff, derby-481-02-aa-utilities.diff,
derby-481-03-aa-grammar.diff, GeneratedColumns.html
> Satheesh has pointed out that generated columns, a SQL 2003 feature, would satisfy the
performance requirements of Expression Indexes (bug 455). Generated columns may not be as
elegant as Expression Indexes, but they are easier to implement. We would allow the following
new kind of column definition in CREATE TABLE and ALTER TABLE statements:
>     columnName GENERATED ALWAYS AS ( expression )
> If expression were an indexableExpression (as defined in bug 455), then we could create
indexes on it. There is no work for the optimizer to do here. The Language merely has to compute
the generated column at INSERT/UPDATE time.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message