-- 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';