Subject Re: [IB-Architect] DSQL, SPs/Triggers and Auditing
Author Dalton Calford
I have thought long and hard about this. I at first wanted every
function I needed built into the engine. Why should I have to write
UDF's, SP's or any other thing to do what I considered to be 'normal'
database operations. I then started writing down what was normal.
After 20 pages of "What if's" and "But, Maybe's" I decided that nothing
that was created by a single company or group of companies would cover
all the needed variations that developers may need. If we tried to make
a 'one size fits all' type of solution to different problems, we would
make such a convulated mess of code to support that we would never get a
release due to bugs.

I then decided that the best thing that IB could do, is make it possible
for all client style operations, to be performed by the server side
That means, all forms of data (string, array, blob, date) should have a
way of being manipulated/changed on the server. I am not meaning that
we have 2 million plus functions on the server, I mean very basic
manipulation and everything else can be written in server side code to
make the specific functions needed by the particular developer/job.
It also means that DDL, DML, API Calls etc, should all be supported by
the server side language.
With this, any functionality that is needed could be written into the
server side language and would eliminate alot of the need for middle
tier or multi-tier solutions.

Whether the server side language is SQL, BLR, GDML, JAVA, PASCAL, PROLOG
(for those of you who are old enough to remember when prolog was going
to BE the next language that takes the world by storm) does not matter.
It is the functionality that is important.

This means that some extensions to/or replacement of the current server
side SQL is neccesary if we are to do this.

Interbase should become the primary programing environment, client code
only used for data entry/display.

This would answer 99% of the feature requests by people and allow the
developer the freedom to add/modify IB to their hearts content without
touching the binaries.
It also would allow third parties to write entire add on products to
Interbase, while How-To books and code repositories would also florish.

<wild eyed dreamy look>
Interbase could become THE platform independant programing enviroment
for business,
creating dynamic XML within SP code to be sent down to browsers the
world over.
</wild eyed dreamy look>


best regards


Phil Shrimpton wrote:
> > From: Paul Reeves [mailto:paul@...]
> Hi,
> > Yes, I often think that ability would make me happy, too. But I
> > don't think this
> > example is a good one.
> It was just an example, but raises a point (see later)
> > Ideally, this sort of auditing should be built
> > into/extended from the existing engine. When a record is changed InterBase
> > already knows what the differences are, and writes the old ones
> > out as back record versions.
> I am a bit undecided on this one, as my example showed, one mans audit trail
> is different to another's. Those of us that have used Oracle, know that it
> contains 'built' in auditing/logging functionality, but as it is pretty
> generic, it never quite does what you want, and we normally end up
> implementing it ourselves.
> As for adding it to the engine, its another case of usefulness Vs bloat. If
> it is added, it needs to be done properly, not just for 'another check in
> the box'. Obviously we I don't know the 'inner bowels' of InterBase (yet),
> but if it is possible just write the changes to an external text file by the
> means of a system setting, this might be suitable for some situations.
> > It has to be more efficient to add the ability add an option to
> > write this out
> > to disc/external file/whatever at that point than to create
> > triggers on every
> > table that simply repeat something the engine already knows.
> Hands up for Global Triggers :-). If you could create a trigger that you
> could 'hook up' to a number of tables, you would be able to write once, run
> anywhere. This combined with DSQL, would save me so much time, I might be
> able to take a holiday.
> > I guess the questions that need to be answered are:
> >
> > o How important is an audit trail option?
> I work in the financial sector, and virtually every change to every field in
> every table needs to be stored, together with User, TimeStamp etc.
> Normally, I have a category and importance level for each field in a
> separate 'look-up' table, which allows different 'auditors' to review
> different changes to a record, and also be alerted of any 'serious' changes.
> We also have 'end-of-day'/'end-of-month' processes that reconcile the audit
> logs with the 'live' system, to make sure they add-up.
> Generally the 'audit' part of our system make up about 20-25% of the total
> functionality, but as I said before, we are a 'heavy' user of 'audits', and
> other people will only want a basic audit, or a just as 'complex', but
> completely different audit.
> > o Does dynamic SQL in SPs/Triggers have more utility
> > than just supporting auditing?
> We also use a similar method to my 'audit example' to create charges and
> fees for our clients using the system. We charge our clients for 'database
> activity' not a flat rate, so charges are generated when fields/records are
> added/amended etc. Obviously we don't want to charge for all field changes
> as some are for 'system' use, so again we use a look-up table to determine
> which fields to charge for. Again DSQL and/or a Global Trigger would be
> like winning the lottery.
> > It is not that the two are mutually opposing - maybe both are
> > needed. But if the
> > aim is to implement an audit trail, is the DSQL option the right
> > solution or
> > just a symptom that the engine doesn't surface auditing yet?
> I personally think that everyone's 'audit' requirements are different, so
> building it into the engine might be difficult, and pointless, we don't want
> to end up with the 'Audit' equivalent of the BDE. If it is simple to write
> IB's versioning information out to an external file, then that should be OK,
> but I think the ultimate solution is DSQL and 'Global Triggers', that way
> people can do what they want, how they want.
> Cheers
> Phil Shrimpton
> ------------------------------
> Project JEDI DCOM Team Captain
> Project JEDI Library Team
> <>
> Registered Linux User #155621
> ------------------------------------------------------------------------
> Never lose a file again. Protect yourself from accidental deletes,
> overwrites, and viruses with @Backup.
> Try @Backup it's easy, it's safe, and it's FREE!
> Click here to receive 300 MyPoints just for trying @Backup.
> ------------------------------------------------------------------------
> To unsubscribe from this group, send an email to: