2a738ccf9a
git-svn-id: http://svn.osgeo.org/postgis/trunk@16327 b70326c6-7e19-0410-871a-916f4a2858ee
322 lines
7.4 KiB
MySQL
322 lines
7.4 KiB
MySQL
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|
|
--
|
|
--
|
|
-- PostGIS - Spatial Types for PostgreSQL
|
|
-- http://postgis.net
|
|
-- Copyright 2001-2003 Refractions Research Inc.
|
|
--
|
|
-- This is free software; you can redistribute and/or modify it under
|
|
-- the terms of the GNU General Public Licence. See the COPYING file.
|
|
--
|
|
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|
|
|
|
#include "sqldefines.h"
|
|
|
|
-----------------------------------------------------------------------
|
|
-- LONG TERM LOCKING
|
|
-----------------------------------------------------------------------
|
|
|
|
-- UnlockRows(authid)
|
|
-- removes all locks held by the given auth
|
|
-- returns the number of locks released
|
|
CREATE OR REPLACE FUNCTION UnlockRows(text)
|
|
RETURNS int
|
|
AS $$
|
|
DECLARE
|
|
ret int;
|
|
BEGIN
|
|
|
|
IF NOT LongTransactionsEnabled() THEN
|
|
RAISE EXCEPTION 'Long transaction support disabled, use EnableLongTransaction() to enable.';
|
|
END IF;
|
|
|
|
EXECUTE 'DELETE FROM authorization_table where authid = ' ||
|
|
quote_literal($1);
|
|
|
|
GET DIAGNOSTICS ret = ROW_COUNT;
|
|
|
|
RETURN ret;
|
|
END;
|
|
$$
|
|
LANGUAGE 'plpgsql' VOLATILE STRICT;
|
|
|
|
-- LockRow([schema], table, rowid, auth, [expires])
|
|
-- Returns 1 if successfully obtained the lock, 0 otherwise
|
|
CREATE OR REPLACE FUNCTION LockRow(text, text, text, text, timestamp)
|
|
RETURNS int
|
|
AS $$
|
|
DECLARE
|
|
myschema alias for $1;
|
|
mytable alias for $2;
|
|
myrid alias for $3;
|
|
authid alias for $4;
|
|
expires alias for $5;
|
|
ret int;
|
|
mytoid oid;
|
|
myrec RECORD;
|
|
|
|
BEGIN
|
|
|
|
IF NOT LongTransactionsEnabled() THEN
|
|
RAISE EXCEPTION 'Long transaction support disabled, use EnableLongTransaction() to enable.';
|
|
END IF;
|
|
|
|
EXECUTE 'DELETE FROM authorization_table WHERE expires < now()';
|
|
|
|
SELECT c.oid INTO mytoid FROM pg_class c, pg_namespace n
|
|
WHERE c.relname = mytable
|
|
AND c.relnamespace = n.oid
|
|
AND n.nspname = myschema;
|
|
|
|
-- RAISE NOTICE 'toid: %', mytoid;
|
|
|
|
FOR myrec IN SELECT * FROM authorization_table WHERE
|
|
toid = mytoid AND rid = myrid
|
|
LOOP
|
|
IF myrec.authid != authid THEN
|
|
RETURN 0;
|
|
ELSE
|
|
RETURN 1;
|
|
END IF;
|
|
END LOOP;
|
|
|
|
EXECUTE 'INSERT INTO authorization_table VALUES ('||
|
|
quote_literal(mytoid::text)||','||quote_literal(myrid)||
|
|
','||quote_literal(expires::text)||
|
|
','||quote_literal(authid) ||')';
|
|
|
|
GET DIAGNOSTICS ret = ROW_COUNT;
|
|
|
|
RETURN ret;
|
|
END;
|
|
$$
|
|
LANGUAGE 'plpgsql' VOLATILE STRICT;
|
|
|
|
-- LockRow(schema, table, rid, authid);
|
|
CREATE OR REPLACE FUNCTION LockRow(text, text, text, text)
|
|
RETURNS int
|
|
AS
|
|
$$ SELECT LockRow($1, $2, $3, $4, now()::timestamp+'1:00'); $$
|
|
LANGUAGE 'sql' VOLATILE STRICT;
|
|
|
|
-- LockRow(table, rid, authid);
|
|
CREATE OR REPLACE FUNCTION LockRow(text, text, text)
|
|
RETURNS int
|
|
AS
|
|
$$ SELECT LockRow(current_schema(), $1, $2, $3, now()::timestamp+'1:00'); $$
|
|
LANGUAGE 'sql' VOLATILE STRICT;
|
|
|
|
-- LockRow(schema, table, rid, expires);
|
|
CREATE OR REPLACE FUNCTION LockRow(text, text, text, timestamp)
|
|
RETURNS int
|
|
AS
|
|
$$ SELECT LockRow(current_schema(), $1, $2, $3, $4); $$
|
|
LANGUAGE 'sql' VOLATILE STRICT;
|
|
|
|
CREATE OR REPLACE FUNCTION AddAuth(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
|
|
CREATE TEMP TABLE temp_lock_have_table (transid xid, lockcode text);
|
|
-- this will only work from pgsql7.4 up
|
|
-- ON COMMIT DELETE ROWS;
|
|
END IF;
|
|
|
|
-- INSERT INTO mylock VALUES ( $1)
|
|
-- EXECUTE 'INSERT INTO temp_lock_have_table VALUES ( '||
|
|
-- quote_literal(getTransactionID()) || ',' ||
|
|
-- quote_literal(lockid) ||')';
|
|
|
|
INSERT INTO temp_lock_have_table VALUES (getTransactionID(), lockid);
|
|
|
|
RETURN true::boolean;
|
|
END;
|
|
$$
|
|
LANGUAGE PLPGSQL;
|
|
|
|
-- CheckAuth( <schema>, <table>, <ridcolumn> )
|
|
--
|
|
-- Returns 0
|
|
--
|
|
CREATE OR REPLACE FUNCTION CheckAuth(text, text, text)
|
|
RETURNS INT
|
|
AS $$
|
|
DECLARE
|
|
schema text;
|
|
BEGIN
|
|
IF NOT LongTransactionsEnabled() THEN
|
|
RAISE EXCEPTION 'Long transaction support disabled, use EnableLongTransaction() to enable.';
|
|
END IF;
|
|
|
|
if ( $1 != '' ) THEN
|
|
schema = $1;
|
|
ELSE
|
|
SELECT current_schema() into schema;
|
|
END IF;
|
|
|
|
-- TODO: check for an already existing trigger ?
|
|
|
|
EXECUTE 'CREATE TRIGGER check_auth BEFORE UPDATE OR DELETE ON '
|
|
|| quote_ident(schema) || '.' || quote_ident($2)
|
|
||' FOR EACH ROW EXECUTE PROCEDURE CheckAuthTrigger('
|
|
|| quote_literal($3) || ')';
|
|
|
|
RETURN 0;
|
|
END;
|
|
$$
|
|
LANGUAGE 'plpgsql';
|
|
|
|
-- CheckAuth(<table>, <ridcolumn>)
|
|
CREATE OR REPLACE FUNCTION CheckAuth(text, text)
|
|
RETURNS INT
|
|
AS
|
|
$$ SELECT CheckAuth('', $1, $2) $$
|
|
LANGUAGE 'sql';
|
|
|
|
CREATE OR REPLACE FUNCTION CheckAuthTrigger()
|
|
RETURNS trigger AS
|
|
'MODULE_PATHNAME', 'check_authorization'
|
|
LANGUAGE C;
|
|
|
|
CREATE OR REPLACE FUNCTION GetTransactionID()
|
|
RETURNS xid AS
|
|
'MODULE_PATHNAME', 'getTransactionID'
|
|
LANGUAGE C;
|
|
|
|
--
|
|
-- Enable Long transactions support
|
|
--
|
|
-- Creates the authorization_table if not already existing
|
|
--
|
|
CREATE OR REPLACE FUNCTION EnableLongTransactions()
|
|
RETURNS TEXT
|
|
AS $$
|
|
DECLARE
|
|
"query" text;
|
|
exists bool;
|
|
rec RECORD;
|
|
|
|
BEGIN
|
|
|
|
exists = 'f';
|
|
FOR rec IN SELECT * FROM pg_class WHERE relname = 'authorization_table'
|
|
LOOP
|
|
exists = 't';
|
|
END LOOP;
|
|
|
|
IF NOT exists
|
|
THEN
|
|
"query" = 'CREATE TABLE authorization_table (
|
|
toid oid, -- table oid
|
|
rid text, -- row id
|
|
expires timestamp,
|
|
authid text
|
|
)';
|
|
EXECUTE "query";
|
|
END IF;
|
|
|
|
exists = 'f';
|
|
FOR rec IN SELECT * FROM pg_class WHERE relname = 'authorized_tables'
|
|
LOOP
|
|
exists = 't';
|
|
END LOOP;
|
|
|
|
IF NOT exists THEN
|
|
"query" = 'CREATE VIEW authorized_tables AS ' ||
|
|
'SELECT ' ||
|
|
'n.nspname as schema, ' ||
|
|
'c.relname as table, trim(' ||
|
|
quote_literal(chr(92) || '000') ||
|
|
' from t.tgargs) as id_column ' ||
|
|
'FROM pg_trigger t, pg_class c, pg_proc p ' ||
|
|
', pg_namespace n ' ||
|
|
'WHERE p.proname = ' || quote_literal('checkauthtrigger') ||
|
|
' AND c.relnamespace = n.oid' ||
|
|
' AND t.tgfoid = p.oid and t.tgrelid = c.oid';
|
|
EXECUTE "query";
|
|
END IF;
|
|
|
|
RETURN 'Long transactions support enabled';
|
|
END;
|
|
$$
|
|
LANGUAGE 'plpgsql';
|
|
|
|
--
|
|
-- Check if Long transactions support is enabled
|
|
--
|
|
CREATE OR REPLACE FUNCTION LongTransactionsEnabled()
|
|
RETURNS bool
|
|
AS $$
|
|
DECLARE
|
|
rec RECORD;
|
|
BEGIN
|
|
FOR rec IN SELECT oid FROM pg_class WHERE relname = 'authorized_tables'
|
|
LOOP
|
|
return 't';
|
|
END LOOP;
|
|
return 'f';
|
|
END;
|
|
$$
|
|
LANGUAGE 'plpgsql';
|
|
|
|
--
|
|
-- Disable Long transactions support
|
|
--
|
|
-- (1) Drop any long_xact trigger
|
|
-- (2) Drop the authorization_table
|
|
-- (3) KEEP the authorized_tables view
|
|
--
|
|
CREATE OR REPLACE FUNCTION DisableLongTransactions()
|
|
RETURNS TEXT
|
|
AS $$
|
|
DECLARE
|
|
rec RECORD;
|
|
|
|
BEGIN
|
|
|
|
--
|
|
-- Drop all triggers applied by CheckAuth()
|
|
--
|
|
FOR rec IN
|
|
SELECT c.relname, t.tgname, t.tgargs FROM pg_trigger t, pg_class c, pg_proc p
|
|
WHERE p.proname = 'checkauthtrigger' and t.tgfoid = p.oid and t.tgrelid = c.oid
|
|
LOOP
|
|
EXECUTE 'DROP TRIGGER ' || quote_ident(rec.tgname) ||
|
|
' ON ' || quote_ident(rec.relname);
|
|
END LOOP;
|
|
|
|
--
|
|
-- Drop the authorization_table table
|
|
--
|
|
FOR rec IN SELECT * FROM pg_class WHERE relname = 'authorization_table' LOOP
|
|
DROP TABLE authorization_table;
|
|
END LOOP;
|
|
|
|
--
|
|
-- Drop the authorized_tables view
|
|
--
|
|
FOR rec IN SELECT * FROM pg_class WHERE relname = 'authorized_tables' LOOP
|
|
DROP VIEW authorized_tables;
|
|
END LOOP;
|
|
|
|
RETURN 'Long transactions support disabled';
|
|
END;
|
|
$$
|
|
LANGUAGE 'plpgsql';
|
|
|
|
---------------------------------------------------------------
|
|
-- END
|
|
---------------------------------------------------------------
|
|
|