Re: Any Good Way To Do Sync DB's?

On 12 Oct 2001, Doug McNaught wrote:

> Probably the best thing to do is to export the data from Progress in a
> format that the PostgreSQL COPY command can read. See the docs for
> details.

Hi,

I wrote a quick and dirty function/trigger to sync two DBs - one
local and the other on the web. The method is quite simple. Any insert or
update fires a trigger that "serialises" the entries and stores it in a
log with the table name, the primary key and the timestamp. When an entry
is deleted, the same happens except that the serialised column contains a
null. So when I sync, I just need to upload the changes and not the entire
dump. I think this is a good opportunity to get some advice feedback on
the code, so here it is:

--------------------------------------------------------------------------------------------
drop function setuptriggers();
create function setuptriggers() returns int as '
declare
fb text;
tb text;
tresult record;
cresult record;
pkeyname name;
begin
for tresult in select * from pg_class
where relkind = ''r''
and relname !~ ''^pg_''
and relname !~ ''^Inv''
and relname !~ ''^pga_''
order by relname
loop
select into pkeyname c.attname from pg_class a, pg_index b, pg_attribute c
where a.relname = tresult.relname and
a.oid = b.indrelid and
a.oid = c.attrelid and
b.indkey[0] = c.attnum and
b.indisprimary=''t'';
if pkeyname is not null and tresult.relname != ''logtable'' then
fb := ''
create function logchange_'' || quote_ident(tresult.relname) || ''() returns opaque as ''''
declare
serialized text;
updatetime timestamp;
separator text;
begin
updatetime := ''''''''now'''''''';
separator := chr(178);
serialized := '''''''''''''''';
'';
for cresult in select * from pg_class a, pg_attribute b
where a.relname = tresult.relname and
a.oid = b.attrelid and
b.attnum > 0
order by b.attnum
loop
fb := fb || '' if NEW.'' || quote_ident(cresult.attname) || '' is not null then
serialized := serialized || separator || '''''''''' || quote_ident(cresult.attname) || ''='''''''' || NEW.'' || quote_ident(cresult.attname) || '';
end if;
'';
end loop;
fb := fb || '' insert into logtable values (NEW.''|| quote_ident(pkeyname) || '', '''''''''' || quote_ident(tresult.relname) || '''''''''', serialized, updatetime);
return new;
end;''''
language ''''plpgsql'''';'';
execute fb;
tb := ''create trigger fireon_'' || quote_ident(tresult.relname) || '' before insert or update on '' || quote_ident(tresult.relname) || ''
for each row execute procedure logchange_'' || quote_ident(tresult.relname) || ''();'';
execute tb;
end if;
end loop;
return 1;
end;'
language 'plpgsql';
-------------------------------------------------------------------------------------------------
I hope this is usefule