hi,

with something like that:

create view nextids (tablename, id)
as
select 'xyz', gen_id(gen_xyz, 0) from rdb$database
union
select 'abc', gen_id(gen_abc, 0) from rdb$database;

and use some triggers to make the view updatable:

on before update:

create trigger nextids_bu0 for nextids
active before update position 0
as
begin
execute statement 'set generator gen_' || new.tablename || ' to ' ||
new.id;
end


it is not really correct to use execute statement to run a "set
generator" but I think it could work.

for concurrent operations, the only way to know is to test i supose.


Jonas Olofsson a écrit :

>
> --- In firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>, Tetram Corp <dev@...> wrote:
> >
> > hi,
> >
> > couldn't you replace your table by a view which kowns its data from
> > generator?
> >
> > Thierry
> >
>
> Hi Thierry,
>
> I have thought of trying to use a view instead, but the problem is
> that it has to be compatible with legacy applications that most of the
> time do:
>
> SELECT ID FROM NEXTIDS WHERE TABLENAME = 'xyz'
> UPDATE NEXTIDS SET ID = ID+1 WHERE TABLENAME = 'xyz'
>
> Is it possible to create a view that handles the above and also is
> more robust when it comes to concurrent operations?
>
> Cheers
>
> Jonas
>
> > Jonas Olofsson a écrit :
> > >
> > > > I would add this to the UPDATE statement:
> > > > AND ID = <old-id-value>
> > > >
> > > > If this results in 0 records updated, it means someone else updated
> > > the record, this will make sure that concurrent updates know of
> eachother.
> > > >
> > > > Have you thought of wrapping the getting of IDs into a Stored
> Procedure
> > > > instead of doing direct updates? This way, you can easily switch
> to a
> > > > Generator instead of a table. There's no way of knowing a Generator
> > > > is used (like a table trigger), so they'll never be update to date
> > > unless
> > > > you create a trigger on each table that will automatically
> update your
> > > > NEXTIDs table with the last number inserted.
> > >
> > > Hi Martijn,
> > >
> > > yes, the thought was to wrap this functionality into a Stored
> > > Procedure to use generators for all new tables and then the existing
> > > next id table for old tables. The big thing is that all legacy
> > > applications use call SELECT and UPDATE from the application withoout
> > > a standard way of doing it and what I can see no thought of
> > > concurrentcy problems.
> > >
> > > My line of thought was to implement triggers on the some of the old
> > > tables that updates the generator if a row is inserted with a id
> > > higher than the generator.
> > >
> > > Will there be some issues with this if a legacy application gets an id
> > > from the next id table at the same time?
> > >
> > > Does it matter if I in my stored procedure calls:
> > >
> > > UPDATE NEXTIDS SET ID = ID+1 WHERE TABLENAME = 'xyz'
> > > SELECT ID FROM NEXTIDS WHERE TABLENAME = 'xyz'
> > >
> > > or
> > >
> > > SELECT ID FROM NEXTIDS WHERE TABLENAME = 'xyz'
> > > UPDATE NEXTIDS SET ID = ID+1 WHERE TABLENAME = 'xyz'
> > >
> > > 1. Will this lock the row in the database so I will always get an
> > > unique id?
> > > 2. Will the row be locked for editing until I commit my transaction
> > > after the select?
> > > 3. What happens if another application tries to access the nextid at
> > > the same time, will the get an exception?
> > > 4. But another application can still get the same id as I if it does
> > > the SELECT the id first and then update, or?
> > >
> > > I do not care if the id is contingous as long as it is unique.
> > >
> > > Cheers
> > >
> > > Jonas
> > >
> > >
> >
>
>