strk@anonymised.com wrote:
Thank you Jody, I do have a better understanding of the problem.
BTW: do the pdf document existing or "intentional" stuff ?
Opps here is a bit more recent on the intentional stuff ...
By this time we had broken down and turned these into specific "feature locks", and fids are now strings etc.
psql locktest2
------------------------------------------------------------------------
In postgis.sql ---
create table authorization_table (tname text, fid text, expires date, authid text, PRIMARY KEY (tname,fid));
--- select AuthorizationUnLock ( <authid)
--- removes the lock from the authorization_table
create or replace function AuthorizationUnLock( TEXT )
RETURNS INT
AS '
BEGIN
EXECUTE ''DELETE FROM authorization_table where authid = '' || quote_literal($1);
RETURN 0;
END;
'
LANGUAGE 'plpgsql';--- select AuthorizationLock( text tname, text FID, date expires, text AuthID ) create or replace function AuthorizationLock (text, text, date, text)
RETURNS INT
AS '
DECLARE
tname alias for $1;
fid alias for $2;
expires alias for $3;
authid alias for $4;BEGIN
EXECUTE ''DELETE FROM authorization_table WHERE expires < now()''; EXECUTE ''INSERT INTO authorization_table VALUES (''||quote_literal(tname) ||'',''||quote_literal(fid) ||'',''||quote_literal(expires) ||'',''||quote_literal(authid) ||'')'';
RETURN 0;
END;'
LANGUAGE 'plpgsql';--- AuthroizationTriggerAttacher( text tname, text fidcolumn ) create or replace function AuthroizationTriggerAttacher( text, text ) returns INT
AS '
BEGIN EXECUTE '' CREATE TRIGGER authorizationlocktrigger BEFORE UPDATE OR DELETE ON '' ||$1 ||'' FOR EACH ROW EXECUTE PROCEDURE lockcheck(''|| quote_literal($2) || '') '';
RETURN 0;
END;
'
LANGUAGE 'plpgsql';CREATE FUNCTION lockcheck() RETURNS TRIGGER AS '/data1/Refractions/Projects/PostGIS/work_dave/postgis/libpostgis.so.0.8' LANGUAGE C;
CREATE FUNCTION getTransactionID() RETURNS XID AS '/data1/Refractions/Projects/PostGIS/work_dave/postgis/libpostgis.so.0.8','getTransactionID' LANGUAGE C;
CREATE OR REPLACE FUNCTION haveAuthorization(TEXT) RETURNS BOOLEAN AS
'
DECLARE
lockid alias for $1;
okay boolean;
myrec record;
BEGIN
-- check to see if table exists
-- if not, CREATE TEMP TABLE mylock (transid xid, lockcode text)okay := ''f'';
FOR myrec IN SELECT * FROM pg_class WHERE relname = ''temp_lock_have_table'' LOOP
okay := ''t'';
END LOOP; IF (okay <> ''t'') THEN EXECUTE ''CREATE TEMP TABLE temp_lock_have_table (transid xid, lockcode text)'';
END IF;-- delete everything in TEMP table with xid != current xid
EXECUTE ''DELETE FROM temp_lock_have_table WHERE not(xideq(transid , getTransactionID()))'';
-- INSERT INTO mylock VALUES ( getTransactionID(), $1)
EXECUTE ''INSERT INTO temp_lock_have_table VALUES ( getTransactionID(), ''|| quote_literal(lockid) ||'')'';
RETURN true::boolean;
END;
'
LANGUAGE PLPGSQL with (isstrict);CREATE TABLE geo_table (id int primary key,the_geom geometry, name text);
select AuthroizationTriggerAttacher('geo_table', 'id');insert into geo_table values (1, 'POINT(0 0)', 'dave');
insert into geo_table values (2, 'POINT(1 0)', 'jody');
insert into geo_table values (3, 'POINT(2 0)', 'brent');
insert into geo_table values (4, 'POINT(3 0)', 'justin');-- this will update one row
BEGIN;
select authorizationlock('geo_table','2','2004-01-01','XX22XX'); -- lock 'jody'
select haveAuthorization('XX22XX');
update geo_table set name = 'Jody' where id = 2;
END;-- this will try to update one row (and throw an error)
BEGIN;
select authorizationlock('geo_table','2','2004-01-01','XX22XX'); -- lock 'jody'
--- select haveAuthorization('XX22XX');
update geo_table set name = 'Jody' where id = 2;
END;