--- In ib-support@y..., "Entrebytes S.L." <ebytes@t...> wrote:

> handicap. Moreover , some of the databases may be during long

periods

> isolated in networks without communication ..just a nigthmare. I'm

just

> doing a tailored solution for this one. But the question remains. I

can

> think of some application of transient one-sided and/or two-sided
> replication over two or more databases

Javi, from the sound of it the only way will be to log all changes
locally and then run the replication when the network is available.
This is similar to the situation of having laptops in the field then
synching the data when they get back to base.

Here's a tip for simple replication: use an integer surrogate
primary key on all tables. This makes logging primary keys easy. No
dealing with composite primary keys. You can still have your indexes
on candidate keys, like with an intersection table.

Then create triggers on all table inserts, deletes, and updates in
order to log the primary key and action to a log table. Now that you
know what records have changed, it's simple to replicate accordingly
via your own replication manager which handles the updating of the
databases involved.

You could modify the triggers as needed, only firing the 'log
changes' procedure when certain fields have changed, e.g.

CREATE TRIGGER TR_ADDRESS_LOG_UPDATE FOR T_ADDRESS
AFTER UPDATE
AS
BEGIN
IF
(OLD.FIELD_X <> NEW.FIELD_X)
AND
(OLD.FIELD_Y <> NEW.FIELD_Y)
THEN
EXECUTE PROCEDURE SP_LOG_CHANGES
(
'ADDRESS',
NEW.F_PK,
'U');
END
^

Anyway, here's how I'm doing simple replication (note, this is rough
code, but you get the idea!):

----------
The log table:

CREATE TABLE T_LOG1 (
LOG1_PK DOM_PK,
LOG1_RECORDKEY DOM_PK_LARGE,
LOG1_REPL_STATE_FK DOM_CODE1 DEFAULT 'N' NOT NULL,
LOG1_TABLE_FK DOM_FK_SMALL NOT NULL,
LOG1_GENSTAMP DOM_GENSTAMP,
LOG1_TYPE DOM_CODE1 NOT NULL,
LOG1_LUSER_FK DOM_FK NOT NULL
)^
ALTER TABLE T_LOG1
ADD PRIMARY KEY (LOG1_PK)^

The logging procedure:

CREATE PROCEDURE SP_LOG_CHANGES (
VAR_TABLENAME VARCHAR(30),
VAR_KEY NUMERIC(18,0),
VAR_CHANGETYPE DOM_CODE1 )
AS
BEGIN
IF
(UPPER (CURRENT_USER) <> 'REPLICANT1')
AND EXISTS
(
SELECT 1
FROM T_TABLE_OPTIONS
WHERE (F_TABLENAME = :VAR_TABLENAME)
AND (F_LOGGING = 'Y')
)
THEN
INSERT INTO T_LOG_CHANGES
(
F_TABLENAME,
F_RECORDKEY,
F_TYPE
)
VALUES
(
:VAR_TABLENAME,
:VAR_KEY,
:VAR_CHANGETYPE
);
END^

----------
The triggers for tables you need to log:

CREATE TRIGGER TR_ADDRESS_LOG_INSERT FOR T_ADDRESS
AFTER INSERT
AS
BEGIN
EXECUTE PROCEDURE SP_LOG_CHANGES
('ADDRESS',NEW.F_PK, 'I');
END
^

CREATE TRIGGER TR_ADDRESS_LOG_UPDATE FOR T_ADDRESS
AFTER UPDATE
AS
BEGIN
EXECUTE PROCEDURE SP_LOG_CHANGES
('ADDRESS',NEW.F_PK, 'U');
END
^

CREATE TRIGGER TR_ADDRESS_LOG_DELETE FOR T_ADDRESS
AFTER DELETE
AS
BEGIN
EXECUTE PROCEDURE SP_LOG_CHANGES
('ADDRESS',OLD.F_PK, 'D');
END
^


Hope this gives you some ideas! Your problem is definitely do-able
with Firebird!

Regards,
Andrew Ferguson