Markus Ostenried a écrit :

>Hi,
>
>At 08:43 Friday, 20.02.2004 +0100, you wrote:
>
>
>>Generators are nice. The main thing that's good about them is that
>>they're transaction independant. Thus, they are perfect for generating a
>>sequence of consecutive interger values.
>>
>>
>If by "consecutive sequence" you mean a series of numbers with no spaces
>(missing numbers) in-between then you can *not* use a generator. Because
>-as you said- "they're transaction independant":
>If in a transaction a generator gets incremented and after that this
>transaction is rolled back then the generated number is "lost" and you have
>a hole in your sequence.
>(You could use the SET GENERATOR TO statment to adjust the value but that
>would get you into concurrency issues)
>

Yes, of course. However, there are ways to get around this problem. For
example, what I usually do, is first get the generator value and display
it, using "gen_id(gen_name, 0) + 1", and then, if and when the user
confirms, I actually increment the generator. This avoids all
concurrency issues in a simple way. However, this technique isn't
usuable if the end user wants anything other than a meaningless integer
as a PK. Hence my idea. What I would really like to do, would be to have
an integer field as PK, but have a calculated field that would
correspond to what the user wants to see. Thus, in my example, it would
have to be Primary key (COUNTER, YEAR, MONTH) (3 integers).

Currently, I instead have a separate field (not the PK), that represents
the unique code that the user wants to see. However, since the user
wants this to be 100% unique (obviously), I had to add a unique
constaint on that field. So now, it nearly as tedious to use as it would
be if I had made the display code the PK!

That's why I think the feature I suggested would be a very good thing.
It would make generators usuable for situations other than, as you say,
a meaningless sequence of unique numbers.

As for the problem of deleted records, I'm personally starting to think
that a good thing to do would be to never really delete anything at all,
but simply marking it as deleted. I guess there are probably situations
where this wouldn't be good, but in most cases I can think of and I have
experience with, this would be a very good thing, mostly for important
data such as invoices. Thus, auditing is very simple : there can never
be any gaps, because the code is assigned only when the user confirms,
and after that point, there's no way to delete the invoice. This is just
as well really, since the user can then check each deleted invoice, and
make sure they know why it was deleted. This is the whole idea behind
auditing as I understand it.

>>However, it's difficult to use
>>a generator as a primary key in many cases, because it is not related to
>>anything : it's just a sequence of numbers.
>>
>>
>Generators are fine for getting unique values - and for nothing else, e.g.
>they're fine to get meaningless values for a primary key. You're primary
>key should have no meaning, if you need a sequence number then just create
>an extra column for it.
>See http://www.ibobjects.com/TechInfo.html#ti_AuditableSeries for a nice
>example (have a look at it even if you're not using IBObjects, the zip file
>includes a nice text document and an example sql script).
>

Yes, I've downloaded this, and had a glance. It's interesting, but I
think that in most situations it would be simpler to not bother
recycling the codes. But of course, this depends on the requirements of
your customer... An interesting document.

So I maintain my feature request! :-)

Thanks!

Jonathan Neve.


[Non-text portions of this message have been removed]