Re: Updating DB & objectid.next from outside DODS

Hi,

I'm using database triggers for dealing with objectids. Your database
must support plpgsql. (use createlang for this).

The trigger code is like this:

create function getObjectId(bigint) RETURNS bigint AS '
DECLARE
   objectid_rec RECORD;
BEGIN
   SELECT INTO objectid_rec * FROM objectid;
   objectid_rec.next := objectid_rec.next + $1;
   UPDATE objectid SET next = objectid_rec.next;

   RETURN objectid_rec.next;
END;
' language 'plpgsql';

create function setoid() returns opaque as '
BEGIN
   if NEW.ObjectId ISNULL then
      SELECT INTO NEW.ObjectId getObjectId(1);
   end if;
   if NEW.ObjectVersion ISNULL then
      NEW.ObjectVersion := 0;
   end if;
   return NEW;
END;
' language 'plpgsql';

For each DODS-generated table that gets INSERTS from outside, create a
trigger like this.

create trigger trig_my_table1 BEFORE INSERT ON my_table
   FOR EACH ROW EXECUTE PROCEDURE setoid();

If you use DODS' caching, you might also need a "BEFORE UPDATE" trigger
that increments the row's objectversion.

Regards,
Stefan



Am Die, 2002-10-15 um 04.20 schrieb Sunil Vemuri:
> I have a Postgres database with a set of tables generated by DODS 
> (enhydra 3.1).  I would like to make updates to these tables from 
> outside of DODS (in the specific case, from a C++ program).  The DODS 
> generated tables include the objectid and objectversion columns. 
> Furthermore there is the objectid table whose "next" value needs 
> updating as well.
> 
> My intuition suggests having my C++ program use objectid.next for the 
> ObjectId in the newly inserted row and then increment objectid.next. 
> Wrap this up on a locked transaction.
> 
> However, according to the docs, the class 
> com.lutris.appserver.server.sql.standard.StandardObjectIdAllocator 
> handles the maitenance of object ids, keeps a cache of them, etc.  The 
> docs for this class further assert "Object ids can only be created via 
> this manager."  I fear any DB operation I perform on object ids outside 
> of DODS will screw things up.
> 
> Is there a more appropriate way to solve this problem?
> 
> Thanks,
> --Sunil
> 
> _______________________________________________
> Enhydra mailing list
> Enhydra@xxxxxxxxxxx
> http://www.enhydra.org/mailman/listinfo.cgi/enhydra