Author Dalton Calford
Ok, for the next installment....

> Ok, I have talked alot about bots, but I have not really defined them. A bot
> is short for 'robot' - a automated process that has very limited preprogramed
> responses to limited input. A Interbase bot is a bot that is totally
> controlled by database events and values kept in tables inside the core gdb's

It works just like a regular client and can be set up on any machine
with net access (or local access) to the gdb's it maintains/is
maintained by.

The reason I refer to the GDBCONTROL program as a daemon vs a bot is due
to the fact that is recieves its requests and setup commands via a UDP
interface and thus directly interfaces with the client software. A bot
does not do this. The reason I make this distinction is to separate the
bot style approach from the daemon based middleware solutions.

A bot should be configured so that it starts up, knows what kind of bot
it is, and contacts the GDBCONTROL program. Once it recieves it's
assigned server, database, login name and password from the GDBCONTROL,
it logs into the server and registers an interest into the event that
has it's same name as well as one that is called 'BOTSTYLE||SETUP' so
the ROLLFWD bot would be registered into the server for a event matching
it's unique name as well as for the event called ROLLFWD. (This was a
design decision, we went with the idea that only one of a type of bot
would be sitting at a particular ip address and every login has it's own
unique login name. This meant that although you could have any number
of bots running on a machine, they all have to be different types of
The bot then reads it's particular configuration settings from the
appropriate tables.
To change a bots settings, you alter the configuration settings and post
the setup event for the particular bot you want reconfigured.

Once a bot has it's particular event triggered, it follows it's
configuration settings and basic rules to perform the task assigned to

Bots are used to extend IB's basic functionality by allowing it to
perform tasks that normally only a client app can do (since a bot is a
client app, the only real difference is that the database controlls it
vs the way a normal client app controls the database)

So now that I have cleared up any misunderstandings about bots and how
they operate, we can go back to one particular bot that I think alot of
people are interested in.


The ROLLFWD bot is connected to a core gdb as well as a log gdb (the
connection is not constant and is only connected as needed) When a
timer on the ROLLFWD bot fires, the bot executes a procedure on the core
server that returns a formated series of data that is then inserted into
the log gdb. All this is done via single multi-gdb transaction because
once the data is posted into the destination, it is removed from the
source database. (and if a error occurs, all changes on both databases
gets rolled back)
Initially we used a hard coded SP name but later we found that due to
changing the SP code to get it working (on a database that was
operational) was a VERY BAD THING (tm).
So, what we then started doing was putting the SP name and returned
parameters (and destination settings) all into the configuration tables
for the bot. We then could create a new version of the SP with the
changes we wished to incorporate, then update the bots configuration
tables, then post the update event. This ensured that the new settings
would take place when all the client bots were ready instead of changing
the metadata as they were using it. It also allowed for very quick
configuration changes to many remote bots. We also started to implement
the storage of the actual program within the database as a blob field -
this way, when the bot connects it checks it's version and downloads a
new copy of itself when needed, I never really went into alot of detail
on the subject, but, after I am finished with these letters, others can
maybe look at the source code and come up with a method for doing it.
So far I have discussed how to save the actions of DML statements with
triggers into internal log tables, and how those log tables are then
moved into a remote gdb, but I never touched upon metadata changes.
At first I was building triggers on the system tables that would fire
when the change is commited. Those triggers would try to build the
entire DDL statement that caused the change. Later (and many
frustrating moments) I ended up putting up a flag in the variables
tables that would tell me what has changed and I would get the ROLLFWD
bot to read in the changes, create the proper DDL, and store it into the
log gdb for later application.

There are other BOTS, each with specific purposes, but I will wait for
further letters to go into proper details about them.

I want to finish the process of setting up a roll forward system.

If the basic design that the developer has in mind is not for a load
balanced system and there is only one core file (ie, no fail over
replication) then we are almost done.

Now, you have a database that has become corrupt in some fashion. You
have a backup that is working just fine. You also have a log of all the
changes to the database.
At this point, you can apply the log to the database with another client
program that would give you the prompts for start timestamp/end
timestamp - stop on first occurance of user X - step through changes....
I could go on for days for all the possible options you can put into
this program - I kept mine very simple because I did not have a need
that I could not get around by using WISQL or IBO.

I almost forgot. Since you are creating the log from triggers, you
have a choice, to capture the data on the before trigger or by using the
after trigger. The problem is this. If you do not change the data
using triggers, it does not matter where your data capture trigger sits,
but, if you change, modify or insert the values into another table, then
you need to think about a few things.
1.) if you capture the values on a insert trigger, then, you need to
think about the recieving server needing to process the values during
the LOGS insert. This could be a problem if you have values going into
secondary tables due to a insert trigger, but, that change also being in
the log (so the value gets inserted twice). This is a way for confusion
if you are not extreamly careful.
2.) if you capture the values after the dml has occured, then you need
to make sure that whatever triggers are on the table do not fire again
or you will corrupt your data....

a simple if then block around the body of your triggers that checks to
see if the trigger is supposed to fire for this user will save you lots
of grief in the long run.
(this is true even if not working with replication, you can have a
trigger not fire for a specific user for the duration of a transaction
and not affect other users or even future transactions of this user).

So there we have a simple roll forward system that could have many of
the extra's trimmed off. I left the extra details in just so that when
I go into the extra steps of replication and load balancing, the reader
will have a firm understanding of the tools needed.

Replication is far more complex than a simple application of a log. It
is also neccessary to add alot more maintenance code to ensure that it
does not hog all your network bandwidth. I have not gotten alot of
feedback from the list about these notes of mine. Since the origional
thread was asking for some sort of differential backup, and I have
covered multiple methods of doing different types of backups, I will now
stop for a few days and see if anyone is even reading all this.
If I get people on this thread asking for the next group of
installments, I will continue as I am onto the concepts of replication,
security, direct undo logs, system setup wizards, etc. Otherwise I will
just package up the source to what I have been describing and post it to
Helen and Ann (for them to do with as they wish).

Best regards