db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Army <qoz...@sbcglobal.net>
Subject Re: request for patch reviews
Date Mon, 20 Jun 2005 18:50:19 GMT
Jeff Levitt wrote:
> Hi all,
> 
> There are several JIRA issues relating to docs where I
> have created patches, but we don't know if anyone has
> looked at them to approve the changes.

[ snip ]

> The list below includes those issues with patches I
> created awaiting approval.  

[ snip ]

> http://issues.apache.org/jira/browse/DERBY-275 - doc
> support for BY DEFAULT option 

I'm not the reporter on this issue, but in looking at this patch and the 
surrounding documentation, my comments are as follow:

1) There's a sub-section called "Identity column attributes" under the "CREATE 
TABLE" section that talks about identity columns and the generation of automatic 
values.  It seems to me that either a) the doc for "BY DEFAULT" should go in 
that sub-section instead of after the "generated-column-spec" syntax, or b) the 
sub-section called "Identity column attributes" should be moved so that it comes 
immediately after the "generated-column-spec" syntax.  I don't know which is 
preferable overall, but my personal feeling is that option b would be most 
helpful since that would directly pair the syntax with its description.

2) I think the description of BY DEFAULT columns needs more detail.  The 
comments you have added say when BY DEFAULT columns are most useful, but they 
don't explain _why_ that's the case or even what a BY DEFAULT column does that 
makes it different from an ALWAYS column.  Examples would be nice, too.

Don't take this as "official" or anything, but here's how I would rewrite the 
"Identity column attributes" subsection to account for the "BY DEFAULT" option. 
  I just marked up the text with tags like "<begin_delete>", "<end_delete>", 
"<begin_add>", etc.

----

<begin army_suggestion>

Identity column attributes

For SMALLINT, INT, and BIGINT columns with identity attributes, Derby 
automatically assigns increasing integer values to the column. Identity column 
attributes behave like other defaults in that when an insert statement does not 
specify a value for the column, Derby automatically provides the value. However, 
the value is not a constant; Derby automatically increments the default value 
<begin_delete> every time a row is inserted. Also, unlike other defaults, you 
are not allowed to insert a value directly into or update an identity column. 
<end_delete> <begin_add> at insertion time. <end_add>

<begin_add>

There are two kinds of identity columns in Derby: those which are GENERATED 
ALWAYS and those which are GENERATED BY DEFAULT.

GENERATED ALWAYS:

An identity column that is GENERATED ALWAYS will increment the default value on 
_every_ insertion and will store the incremented value into the column.  Unlike 
other defaults, you are not allowed to insert a value directly into or update an 
identity column that is GENERATED ALWAYS.  Instead, you must either specify the 
DEFAULT keyword when inserting into the identity column, or else leave the 
identity column out of the insertion column list altogether.

For example:

   create table greetings (i int generated always as identity, ch char(50));
   insert into greetings values (DEFAULT, 'hello');
   insert into greetings(ch) values ('bonjour');

Automatically generated values in a GENERATED ALWAYS identity column are unique. 
Creating an identity column does not create an index on the column.

GENERATED BY DEFAULT:

An identity column that is GENERATED BY DEFAULT will only increment and use the 
default value on insertions where no explicit value is given.  This means that, 
unlike GENERATED ALWAYS columns, you can specify a particular value in an 
insertion statement and that value will be used instead of a generated default.

To use the generated default, either specify the DEFAULT keyword when inserting 
into the identity column, or else leave the identity column out of the insertion 
column list altogether.  To use a specific value, specify the desired value as 
part of the insertion statement.

For example:

   create table greetings (i int generated by default as identity, ch char(50));
   insert into greetings values (1, 'hi'); -- specify value "1"
   insert into greetings values (DEFAULT, 'salut'); -- use generated default
   insert into greetings(ch) values ('bonjour'); -- use generated default

Note that, unlike a GENERATED ALWAYS column, a GENERATED BY DEFAULT column does 
not guarantee uniqueness.  Thus, in the above example, the row for 'hi' and the 
row for 'salut' will both have an identity value of "1", because the generated 
column starts at "1" and the user-specified value was also "1".  To prevent 
this, you can use the "STARTS WITH" keyword described below.  To check for this 
condition and disallow it, you can use a primary key or unique constraint on the 
GENERATED BY DEFAULT identity column.

<end_add>

By default, the initial value of an identity column is 1, and the amount of the 
increment is 1. You can specify non-default values ... [ and so on ]

<end army_suggestion>

----

Of course, you should feel free to reword/rewrite any/all of that--all I'm 
really saying is that, to me at least, the documentation of BY DEFAULT needs 
more detail, and that the whole section on "Identity column attributes" might be 
more helpful if it was moved to the place where the actual syntax is described...

*shrug*
Army


Mime
View raw message