Tim,

The folloing statement
CREATE TABLE "TestGen" (
 "ID" INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1)
)

produces generator, which has GeneratedWhen = 'By Default' instead of
'Always' as I'd expect.
Try this:
select * from Information.TableColumns where TableName = 'TestGen'

It seems like it either creates it incorrectly or information schema returns
incorrect value for the
GeneratedWhen field.

Regards,
Michael

<support@contextsoft.com> wrote in
news:D4DF2587-A7E1-48D2-BE6D-E30A6E6E8C4E@news.elevatesoft.com:

> Tim,
>
> The folloing statement
> CREATE TABLE "TestGen" (
>   "ID" INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY
>   1)

There is confusion here in our understanding of the word GENERATED.

IIUC, a Generated field does not actually exist in the data table, but is
calculated from other fields in the current row.

This can be extremely useful, not least as it allows us to create indexes
on bits of other fields concatenated together, for example.

What you hoped it meant, and what a number of us initially thought it
meant, was an equivalent to AUTOINC.

I'm not sure what the EDB version should be, but it's probably in the
docs somewhere.

Hope this helps.
--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 3 + EDB 1.00 build 6)

Hi Chris,

>> The folloing statement
>> CREATE TABLE "TestGen" (
>>   "ID" INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY
>>   1)
>
> There is confusion here in our understanding of the word GENERATED.
There's no confusion in *my* understanding of this word - GENERATED means
use of generators, which is similar to autoinc.

The concept you are referring to is usually called CALCULATED or COMPUTED
and AFAIK is not supported by EDB.

The problem wasn't related to functionality, but to a bug causing A/V
and incorrect meta-data view.

Regards,
Michael


>
> IIUC, a Generated field does not actually exist in the data table, but is
> calculated from other fields in the current row.
>
> This can be extremely useful, not least as it allows us to create indexes
> on bits of other fields concatenated together, for example.
>
> What you hoped it meant, and what a number of us initially thought it
> meant, was an equivalent to AUTOINC.
>
> I'm not sure what the EDB version should be, but it's probably in the
> docs somewhere.
>
> Hope this helps.

Michael Baytalsky <mike@contextsoft.com> wrote in
news:84328284-67BE-4717-97F2-E23CE25A7C5E@news.elevatesoft.com:

>> There is confusion here in our understanding of the word GENERATED.
> There's no confusion in *my* understanding of this word - GENERATED
> means use of generators, which is similar to autoinc.
>
> The concept you are referring to is usually called CALCULATED or
> COMPUTED and AFAIK is not supported by EDB.
>
> The problem wasn't related to functionality, but to a bug causing A/V
> and incorrect meta-data view.
>
> Regards,
> Michael
>
>
>>
>> IIUC, a Generated field does not actually exist in the data table,
>> but is calculated from other fields in the current row.
>>
>> This can be extremely useful, not least as it allows us to create
>> indexes on bits of other fields concatenated together, for example.
>>
>> What you hoped it meant, and what a number of us initially thought it
>> meant, was an equivalent to AUTOINC.
>>
>> I'm not sure what the EDB version should be, but it's probably in the
>> docs somewhere.
>>
>> Hope this helps.
>

Well Michael, I was right about one thing - there is confusion about
someone's understanding of this. I forgot to point out I was using the
Royal "We".

I don't remember where I got the impression GENERATED was not similar to
AUTOINC, but I thought I saw a message from Tim about these "virtual"
columns and the word "generated" somewhere...

Oh well, glad you fixed your problem, and thanks for putting me straight!

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 3 + EDB 1.00 build 6)

Chris

>I don't remember where I got the impression GENERATED was not similar to
>AUTOINC, but I thought I saw a message from Tim about these "virtual"
>columns and the word "generated" somewhere...

I also remember this, but not in this ng (or at least not using the word GENERATED). It was something to do with GUIDS changing every time the record was posted I think.

Roy Lambert

Here is the quote from Tim from that thread about GUIDs

"You're misunderstanding what the generated columns are.  In EDB generated
columns are just that - generated.  Meaning that they don't actually exist
anywhere and are calculated as needed.... "

Chris Holland


Roy Lambert wrote:
> Chris
>
>> I don't remember where I got the impression GENERATED was not similar to
>> AUTOINC, but I thought I saw a message from Tim about these "virtual"
>> columns and the word "generated" somewhere...
>
> I also remember this, but not in this ng (or at least not using the word GENERATED). It was something to do with GUIDS changing every time the record was posted I think.
>
> Roy Lambert
>

Hi Chris,

It appears, that the confusion was on MY part after all

I have just tested it and apparently GENERATED means different
things depending on how you use it.
CREATE TABLE T (
  ID GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1),
  Price Float,
  Qty Float,
  Amount GENERATED ALWAYS AS (Price * Qty)
)

ID will be autoinc, while Amount will be calculated field (and I guess
will not be stored, but evaluated every time you fetch it).
This is quite cool actually, except I'd prefer if it is called COMPUTED
in the second case!

Regards,
Michael

P.S. There's still a problem I mentioned in the first post. Statement
  ID GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1)
Produces same result as
  ID GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1)
I.e. meta-data is not updated properly.




Chris Holland wrote:
> Here is the quote from Tim from that thread about GUIDs
>
> "You're misunderstanding what the generated columns are.  In EDB generated
> columns are just that - generated.  Meaning that they don't actually exist
> anywhere and are calculated as needed.... "
>
> Chris Holland
>
>
> Roy Lambert wrote:
>> Chris
>>
>>> I don't remember where I got the impression GENERATED was not similar to
>>> AUTOINC, but I thought I saw a message from Tim about these "virtual"
>>> columns and the word "generated" somewhere...
>>
>> I also remember this, but not in this ng (or at least not using the
>> word GENERATED). It was something to do with GUIDS changing every time
>> the record was posted I think.
>>
>> Roy Lambert

Michael,

<< I have just tested it and apparently GENERATED means different things
depending on how you use it.
CREATE TABLE T (
  ID GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1),
  Price Float,
  Qty Float,
  Amount GENERATED ALWAYS AS (Price * Qty)
)

ID will be autoinc, while Amount will be calculated field (and I guess will
not be stored, but evaluated every time you fetch it). This is quite cool
actually, except I'd prefer if it is called COMPUTED in the second case! >>

We took some liberties with the standard in this respect.  The standard
actually assumes that the column value is stored in the row, but we figured
that since the column is defined as GENERATED ALWAYS, it's safe to just
compute it as needed and save the disk space.  Plus, it was hard to figure
out why someone would want a generated column that can be modified.

<< P.S. There's still a problem I mentioned in the first post. Statement
  ID GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1)
Produces same result as
  ID GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1)
I.e. meta-data is not updated properly. >>

Yes, this has been fixed for build 2.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


What do you think of Michael's idea of GENERATED / COMPUTED?


Roy Lambert

Roy,

<< What do you think of Michael's idea of GENERATED / COMPUTED? >>

I'm considering it, however it will require a version number changes to
1.01, and I'm not sure if I want to increment the minor version number
already.

--
Tim Young
Elevate Software
www.elevatesoft.com