| Subject | Re: [firebird-support] Re: Feature request : Generator tables |
|---|---|
| Author | Woody (TMW) |
| Post date | 2004-02-24T08:48:52Z |
<<Certainly, this is possible, but it would require that you create a new
generator periodically. This could rapidly get out of hand. In the
example I took for instance, you would have to create a new generator
every month. That means12 generators a year. Not only that, but in
practice, it's sometimes even worse. I have a customer that also wants
to have a separate counter for each distinct store (all using the same
database). Since they have about 5-6 stores, that's at least 60
generators a year! Also, I guess I would have to create them from within
my application, if it can't be done within a stored procedure.
It sounds like you need to implement a system like I have. I use an ID
table to maintain certain counter values for special purposes. I use a
stored procedure to drive it which will return the current value and
optionally increment it. Since I can use transaction deadlock situations to
make sure that only one user can get the value AND increment it, I can
almost guarantee (and that's good enough for most cases) that there are no
repeat or conflicting numbers. The table consists of 2 fields, an ID field
and a current value field. Passing in a non-existant ID creates a new entry
and starts the counter at 1. That let's me add new counters whenever they
are needed. You could use a combination of the month, year and store number
as counter IDs.
Reading the next value does not guarantee that the return value will
actually be the current one when you actually want to post and commit the
data. I don't ever actually work with it that way anyhow, I always get the
next value, increment it and commit. If that fails, I can loop again until
it succeeds. Since the table is relatively small, even using a few hundred
or thousand counters, the speed is negligible. The key is to use a common
routine that starts and commits it's own transaction. Anywhere in my program
that requires a counter from this table, a call is made to a common routine
with the counter ID and whether to increment it or just read it.
HTH
Woody (TMW)
generator periodically. This could rapidly get out of hand. In the
example I took for instance, you would have to create a new generator
every month. That means12 generators a year. Not only that, but in
practice, it's sometimes even worse. I have a customer that also wants
to have a separate counter for each distinct store (all using the same
database). Since they have about 5-6 stores, that's at least 60
generators a year! Also, I guess I would have to create them from within
my application, if it can't be done within a stored procedure.
>>Jonathan,
It sounds like you need to implement a system like I have. I use an ID
table to maintain certain counter values for special purposes. I use a
stored procedure to drive it which will return the current value and
optionally increment it. Since I can use transaction deadlock situations to
make sure that only one user can get the value AND increment it, I can
almost guarantee (and that's good enough for most cases) that there are no
repeat or conflicting numbers. The table consists of 2 fields, an ID field
and a current value field. Passing in a non-existant ID creates a new entry
and starts the counter at 1. That let's me add new counters whenever they
are needed. You could use a combination of the month, year and store number
as counter IDs.
Reading the next value does not guarantee that the return value will
actually be the current one when you actually want to post and commit the
data. I don't ever actually work with it that way anyhow, I always get the
next value, increment it and commit. If that fails, I can loop again until
it succeeds. Since the table is relatively small, even using a few hundred
or thousand counters, the speed is negligible. The key is to use a common
routine that starts and commits it's own transaction. Anywhere in my program
that requires a counter from this table, a call is made to a common routine
with the counter ID and whether to increment it or just read it.
HTH
Woody (TMW)