Roger,

> Could someone explain the inner workings of the schema table

I've never used it myself, but I will do my best...


Level 1: IBO is setup with a great deal of automation, some of which
depends on knowing certain things about the database structure. IBO
retrieves this information directly from IBs metadata tables.

The problem with this is that each query of the metadata tables adds
additional load and delay. IBO solves this problem by caching the
information internally. In this way IBO only needs to read the
metadata for a particular feature once in any given session.

The above happens regardless of SchemaCache settings.


Level 2: Since most production databases have fairly stable metadata,
it seemed reasonable to have a semi-permanent cache of the information
at the client, so that IBO did not even have to read the metadata
every session. So TIB_Connection got a SchemaCacheDir property which
allows you to tell IBO to save the cached metadata into a local
directory.

This can be set without any changes to the database. If you have some
way of telling the client that the database has been changed you can
use IB_Connection.SchemaCache.* functions to clear and refresh its
local cache on demand.


Level 3: Jason decided to try and provide a built in way of notifying
a client that the metadata had changed. To do this you can use the
IB_Connection.SchemaCache.CheckSchemaVersionTable function to
automatically create and initialise a table called IBO$SCHEMA_VERSION
in the database. This process builds the table and initialises it the
following records...
'DomainNames', 0
'GeneratorNames', 0
'TableNames', 0
'ViewNames', 0
'ProcedureNames', 0
'ConstraintNames', 0
'TriggerNames', 0
'IndexDefs', 0
'IndexNames', 0
'PrimaryKeys', 0
'ForeignKeys', 0
'Required', 0
'Defaults', 0
'Computed', 0
'FieldDomains', 0
'TableKeys', 0
'TableFields', 0
'ProcedureParams', 0

So when a developer changes something in the database, they can update
the appropriate record in this table to indicate a new version for
that set of data. For example; if you add a new domain then you would
increment the version field ofthe 'DomainNames' record.

When an IBO client starts up and loads its local SchemaCache files, it
will read the version information from these records and check that
its local cache is up to date. If not it will mark its cache as
invalid and it will be refreshed as the session progresses. CAUTION:
If the table/version details are not available IBO assumes its cache
is valid!


Hopefully you will see the potential problems associated with using
the cache, but as long as it is implemented carefully it should
improve performance significantly - particularly when forms are first
opened (where, without the cache, the metadata would have to be
retrieved).

In theory it would be possible to attach triggers to the meta-data
tables to automatically update the IBO$SCHEMA_VERSION records.
However IMO this is not entirely appropriate. Local SchemaCache is
only necessary in production environments, and it is simply a matter
of adding the IBO$SCHEMA_VERSION updates to your change control
procedures (we all have formal change control dont we :-)


I said at the top that I dont use this feature (or at least not Level2
and Level3, we all use Level1). This is because my major development
is used primarily on Terminal Server installations, where temporary
file storage is a little more complicated and besides which, the
servers are on a 100Mbs link to the database server so the performance
hit once a session is acceptable.

Since I dont use the feature, most of the above is gathered from the
source code. Hopefully someone will correct me if I made any
significant errors in what I have described.


HTH

Geoff Worboys
Telesis Computing