Index: openacs-4/packages/acs-kernel/sql/postgresql/lob.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/lob.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-kernel/sql/postgresql/lob.sql	30 Mar 2001 03:15:14 -0000	1.1
@@ -0,0 +1,110 @@
+-- SQL support for fake lobs for ACS/Postgres.
+-- Don Baccus February 2000
+
+-- for each user table my_table in which you want to stuff large
+-- amounts of data:
+
+-- define a column "lob integer references lobs"
+-- do "create trigger my_table_lob_trig before delete or update or insert
+-- on my_table for each row execute procedure on_lob_ref()"
+
+-- to initialize a row's lob column,  use empty_lob():
+
+-- insert into my_table (lob) values(empty_lob());
+
+-- deletes and updates on my_table use reference count information
+-- to delete data from lobs and lob_data when appropriate.
+
+
+create sequence lob_sequence;
+
+create table lobs (
+	lob_id			integer not null primary key,
+	refcount		integer not null default 0
+);
+
+create function on_lobs_delete() returns opaque as '
+begin
+	delete from lob_data where lob_id = old.lob_id;
+	return old;
+end;' language 'plpgsql';
+
+create trigger lobs_delete_trig before delete on lobs
+for each row execute procedure on_lobs_delete();
+
+create table lob_data (
+	lob_id			integer not null references lobs,
+	segment			integer not null,
+	byte_len		integer not null,
+	data			bytea not null,
+	primary key (lob_id, segment)
+);
+
+create index lob_data_index on lob_data(lob_id);
+
+-- Note - race conditions might cause problems here, but I 
+-- couldn't get locking to work consistently between PG 6.5
+-- and PG 7.0.  The ACS doesn't share LOBs between tables
+-- or rows within a table anyway, I don't think/hope.
+
+create function on_lob_ref() returns opaque as '
+begin
+	if TG_OP = ''UPDATE'' then
+		if new.lob = old.lob then
+			return new;
+		end if;
+	end if;
+
+	if TG_OP = ''INSERT'' or TG_OP = ''UPDATE'' then
+		if new.lob is not null then
+			insert into lobs select new.lob, 0
+				where 0 = (select count(*) from lobs where lob_id = new.lob);
+			update lobs set refcount = refcount + 1 where lob_id = new.lob;
+		end if;
+	end if;
+
+	if TG_OP <> ''INSERT'' then
+		if old.lob is not null then
+			update lobs set refcount = refcount - 1 where lob_id = old.lob;
+			delete from lobs where lob_id = old.lob and refcount = 0;
+		end if;
+	end if;
+
+	if TG_OP = ''INSERT'' or TG_OP = ''UPDATE'' then return new;
+	else return old;
+	end if;
+
+end;' language 'plpgsql';
+
+create function empty_lob() returns integer as '
+begin
+	return nextval(''lob_sequence'');
+end;' language 'plpgsql';
+
+create function lob_get_data(integer) returns text as '
+declare
+        lob_id  integer;
+        v_rec   record;
+        v_data  text default '''';
+begin
+        for v_rec in select data, segment from lob_data order by segment;
+            v_data := v_data || v_rec.data;
+        end if;
+
+        return v_data;
+
+end;' language 'plpgsql';
+
+create function lob_copy(integer, integer) returns integer as '
+declare
+        from_id         alias for $1;
+        to_id           alias for $2;
+begin
+        insert into lob_data
+             select to_id as lob_id, segment, byte_len, data
+               from lob_data
+              where lob_id = from_id;
+
+        return null;
+
+end;' language 'plpgsql';