Board index » delphi » TechTips: Designing safer apps using local tables

TechTips: Designing safer apps using local tables

The notion of workstation-local database files (e.g. Paradox-format
tables created through BDE) has been much maligned in some circles ...
but it can be an extremely effective way to design a robust application
that's easy on the network.

A traditional Paradox (or Delphi) application often works with the
tables on the server directly.  Or it uses a hybrid approach provided by
BDE (cached updates).  This leads to a lot of server-traffic and a lot
of open transactions.  This can be avoided.

How?  Use the local workstation "like the computer IT is, too."

Instead of using direct and constant connections to the live tables,
what if you retrieve the desired records to the local workstation,
manipulate them directly (essentially "off-line" from the server's point
of view), then, when the user presses OK, update them?

(This would consist of starting a transaction that deletes the old
records from the server, inserts the replacement set, and commits.
Say...)

(This involves having local tables that mirror the structure of the ones
on the server...  possibly without keys.)

Naturally you have to have some means of detecting when the records have
changed.  A change-counter in the master record (on the server) is a
simple way to do this.  Each time you update the records, you bump the
counter.  When you're ready to update, make sure (within the
transaction) that the value hadn't changed.

Sure, this requires a little more programming:  but it's a lot more
efficient in many typical order-handling (etc...) office situations.
Check it out!

------------------------------------------------------------------
Sundial Services :: Scottsdale, AZ (USA) :: (480) 946-8259
mailto:i...@sundialservices.com  (PGP public key available.)

Quote
> Fast(!), automatic table-repair with two clicks of the mouse!
> ChimneySweep(R):  "Click click, it's fixed!" {tm}
> http://www.sundialservices.com/products/chimneysweep

 

Re:TechTips: Designing safer apps using local tables


Ooops .. forgot a couple of points here.

(1)  When you design your app to use local tables for editing, then when
you do "press 'OK'" you have one very useful advantage:  a "before and
after picture."  In other words, the database presumably still contains
a copy of the [say] invoice before you started editing it; and your
local tables have a copy of the new version.  This makes it very easy to
compare the two, to archive a copy of the record "as it was," and so on.

(2)  When you design an application like this, locking can be a bit
tricky.  With an SQL database, you simply start a transaction (both for
updating =and= reading the data) with an appropriate isolation level
specified.  With Paradox or a fileserver system, you must use table
locks.  And here, you must be a bit careful to avoid deadlocks.

An "invoice," for example, normally consists of an Invoice table and an
Invoice Line Items table.  Both must be locked while storing -or-
retrieving an invoice.  "Both must be locked at the same time, or none
at all."  In Paradox, the "System" object has a "Lock" method that has
this capability.  But the same technique does not exist (AFAIK) in
Delphi.  The same effect can be simulated by adopting a locking
heirarchy; i.e. "always lock the Invoice table first."

(3)  To clarify what I meant about change-counters (yes, someone already
e-mailed me on that!), I'm referring to a long-integer field which your
application maintains in each master record.  This value is incremented
each time your application changes the record.  {Change-counters in
line-item records are probably overkill.}

So the computer performs this process:
        *  Lock the tables (or start a transaction)
        *  Locate and examine the master record
        *  Verify that the change counter has not changed
        *  Update the change counter in the private copy and post it
           to the database.
        *  To handle line-items, delete all existing line-items and then
           replace them with the new ones.
        *  Commit.

---

Re:TechTips: Designing safer apps using local tables


Quote
"Sundial Services" <info_...@sundialservices.com> wrote in message

news:3B191E3A.4E8F@sundialservices.com...

Quote
> Ooops .. forgot a couple of points here.

.....<Snipped>....
    > So the computer performs this process:
    > *  Lock the tables (or start a transaction)
    > *  Locate and examine the master record
    > *  Verify that the change counter has not changed
...<Snipped>....

Could you clearify what need to be done IF THE COUNTER HAS CHANGED between
the time you extracted and changed the data locally?

Re:TechTips: Designing safer apps using local tables


If you determine that the record-counter has changed, then you must:
        *  Remove the locks placed (rollback transaction)
        *  Report to the user that another user has changed the record

You do not make any changes to the records in the master database in
this case.

[It can be advantageous to store a "last modified by" and "last modified
timestamp" field in all master records.  This makes it easier for users
to know whom they should chew out.]  ;-)

Quote
Rodney Wise wrote:

> "Sundial Services" <info_...@sundialservices.com> wrote in message
> news:3B191E3A.4E8F@sundialservices.com...
> > Ooops .. forgot a couple of points here.

> .....<Snipped>....
>     > So the computer performs this process:
>     > *  Lock the tables (or start a transaction)
>     > *  Locate and examine the master record
>     > *  Verify that the change counter has not changed
> ...<Snipped>....

> Could you clearify what need to be done IF THE COUNTER HAS CHANGED between
> the time you extracted and changed the data locally?

------------------------------------------------------------------
Sundial Services :: Scottsdale, AZ (USA) :: (480) 946-8259
mailto:i...@sundialservices.com  (PGP public key available.)
Quote
> Fast(!), automatic table-repair with two clicks of the mouse!
> ChimneySweep(R):  "Click click, it's fixed!" {tm}
> http://www.sundialservices.com/products/chimneysweep

Re:TechTips: Designing safer apps using local tables


Quote
"Sundial Services" <info_...@sundialservices.com> wrote in message

news:3B194104.433A@sundialservices.com...

Quote
> If you determine that the record-counter has changed, then you must:
> *  Remove the locks placed (rollback transaction)
> *  Report to the user that another user has changed the record

> You do not make any changes to the records in the master database in
> this case.

> [It can be advantageous to store a "last modified by" and "last modified
> timestamp" field in all master records.  This makes it easier for users
> to know whom they should chew out.]  ;-)

I've been thinking about this idea for a while now, and can see where a
problem could arise if the number of fields being changed were extensive....
but what about a condition where the only field that is being changed would
be a Quantity field. (Like in a POS scenario where the POS terminals are
merely decrementing the Qty number... or possibly incrementing it with a
returned item)....   I can see many instances where a remote client would
perform 'small' changes like this.

In this case, so long as the Server's data is being changed by adding the
"Variance" (ie; +1,  -3, etc.) to a field and not simply "updating" the
field with the actual field value,  it doesn't matter if someone else has
changed the data since you first extracted it or not.

However, after adding the "Variance" to the Server's Table, that field on
the Clients (duplicate) table would have to be "Updated" to reflect any
"Potential" changes caused by another user that might have occurred during
the original data acquisition and final change.

Now a problem arises which causes that the remote tables to develop an
out-of-sync condition  from the Server's table.... because remote Clients
would only be updating the fields that they have changed.... other fields
that they didn't change will never get updated in their remote tables.

At the end of the day, the Server's table would be accurate and the remote
clients would be out-of -sync.....

Now what?

Am I correct in this logic?

Re:TechTips: Designing safer apps using local tables


Quote
"Sundial Services" <info_...@sundialservices.com> wrote in message

news:3B194104.433A@sundialservices.com...

Quote
> If you determine that the record-counter has changed, then you must:
> *  Remove the locks placed (rollback transaction)
> *  Report to the user that another user has changed the record

> You do not make any changes to the records in the master database in
> this case.

> [It can be advantageous to store a "last modified by" and "last modified
> timestamp" field in all master records.  This makes it easier for users
> to know whom they should chew out.]  ;-)

I've been thinking about this idea for a while now, and can see where a
problem could arise if the number of fields being changed were extensive....
but what about a condition where the only field that is being changed would
be a Quantity field. (Like in a POS scenario where the POS terminals are
merely decrementing the Qty number... or possibly incrementing it with a
returned item)....   I can see many instances where a remote client would
perform 'small' changes like this.

In this case, so long as the Server's data is being changed by adding the
"Variance" (ie; +1,  -3, etc.) to a field and not simply "updating" the
field with the actual field value,  it doesn't matter if someone else has
changed the data since you first extracted it or not.

However, after adding the "Variance" to the Server's Table, that field on
the Clients (duplicate) table would have to be "Updated" to reflect any
"Potential" changes caused by another user that might have occurred during
the original data acquisition and final change.

Now a problem arises which causes that the remote tables to develop an
out-of-sync condition  from the Server's table.... because remote Clients
would only be updating the fields that they have changed.... other fields
that they didn't change will never get updated in their remote tables.

At the end of the day, the Server's table would be accurate and the remote
clients would be out-of -sync.....

Now what?

Am I correct in this logic?

( btw.. Sorry for sending this to you directly..... sometimes hit the wrong
button :{   )

Re:TechTips: Designing safer apps using local tables


Would it make sense to use a local tQuery to extract the data from the
server?

...Jim

Quote
Sundial Services wrote:
> The notion of workstation-local database files (e.g. Paradox-format
> tables created through BDE) has been much maligned in some circles ...
> but it can be an extremely effective way to design a robust application
> that's easy on the network.

> A traditional Paradox (or Delphi) application often works with the
> tables on the server directly.  Or it uses a hybrid approach provided by
> BDE (cached updates).  This leads to a lot of server-traffic and a lot
> of open transactions.  This can be avoided.

> How?  Use the local workstation "like the computer IT is, too."

> Instead of using direct and constant connections to the live tables,
> what if you retrieve the desired records to the local workstation,
> manipulate them directly (essentially "off-line" from the server's point
> of view), then, when the user presses OK, update them?

> (This would consist of starting a transaction that deletes the old
> records from the server, inserts the replacement set, and commits.
> Say...)

> (This involves having local tables that mirror the structure of the ones
> on the server...  possibly without keys.)

> Naturally you have to have some means of detecting when the records have
> changed.  A change-counter in the master record (on the server) is a
> simple way to do this.  Each time you update the records, you bump the
> counter.  When you're ready to update, make sure (within the
> transaction) that the value hadn't changed.

> Sure, this requires a little more programming:  but it's a lot more
> efficient in many typical order-handling (etc...) office situations.
> Check it out!

> ------------------------------------------------------------------
> Sundial Services :: Scottsdale, AZ (USA) :: (480) 946-8259
> mailto:i...@sundialservices.com  (PGP public key available.)
> > Fast(!), automatic table-repair with two clicks of the mouse!
> > ChimneySweep(R):  "Click click, it's fixed!" {tm}
> > http://www.sundialservices.com/products/chimneysweep

Re:TechTips: Designing safer apps using local tables


Sundial Services <info_...@sundialservices.com> wrote

Quote
> Ooops .. forgot a couple of points here.

> An "invoice," for example, normally consists of an Invoice table and
an
> Invoice Line Items table.  Both must be locked while storing -or-
> retrieving an invoice.  "Both must be locked at the same time, or
none
> at all."

I do not see why both have to locked. Presumably, the line items
all 'belong' to a single invoice (let's call it the header). If the
header is locked then this is an indication that all the line items
are not accessible either.

In general, it all seems to be an awful lot of work with all kind of
pitfalls in order to save on network traffic. Just get the people
to buy a faster box! The above approach is, IMO just the wrong way
around because programming costs are always increasing whereas
hardware costs and the performance/cost factor is continually
falling. I say, stay away from it. You as the programmer will
have to sort out any problems and the customer is most likely
unwilling to fork out money to sort out problem you have
created, even if you want to argue that it is to his own
benefit. Bear in mind, that if this your way of approaching
a program he will have no comparative figures, so he will think
that you are just making up a story. If you really want to use
the approach, make him suffer first, then charge him for an upgrade.
And let me see your willingness to make (unnecessary) changes to
your program, creating new bugs, while the other work piles up.
What a programmer wants is simplicity not complications!

I have noticed this before, while Sundial's technical tips are
sound and useful, I find myself often at odds with their *approach*
to programming problems. This extends further to what I see as the
relationship between customers and programmers. You should not take
this as a comment about Sundials' actual customer relationships as I
have never had any dealings with them. It just seems to me that some
of the solutions or potential solutions offered *in this newsgroup*
forum seem to create or increase problems rather than decrease them.

Peter Reber

Re:TechTips: Designing safer apps using local tables


Quote
"peterr" <pet...@freemail.absa.co.za> wrote in message

news:3b1abeb4_3@batman.vip-

Quote
> I do not see why both have to locked. Presumably, the line items
> all 'belong' to a single invoice (let's call it the header). If the
> header is locked then this is an indication that all the line items
> are not accessible either.

It depends on the type of "order". I work on a system where multiple people
have to be allowed to concurrently view/update the same "order". (We
actually lost sales before this feature was added.) I'm not thrilled about
it, but one does have to provide those pesky users with the functionality
they require ;).

Quote

> In general, it all seems to be an awful lot of work with all kind of
> pitfalls in order to save on network traffic. Just get the people
> to buy a faster box! The above approach is, IMO just the wrong way
> around because programming costs are always increasing whereas
> hardware costs and the performance/cost factor is continually
> falling. I say, stay away from it. You as the programmer will
> have to sort out any problems and the customer is most likely
> unwilling to fork out money to sort out problem you have
> created, even if you want to argue that it is to his own
> benefit. Bear in mind, that if this your way of approaching

I tend to agree with you on this point. If the problem is network loading,
first consider maxing the hardware, using a client/server architecture,
re-engineering ones product in a different way, before trying to maintain
what is effectively a distributed db. ISTM that the programming effort to
reliably implement the scheme Sundial suggests could well exceed that of the
rest of the application. Naturally there may well be situations where this
is an ideal solution, and certainly its an option that should be in ones
tool box. IMO though it should be used with caution.

Re:TechTips: Designing safer apps using local tables


Bruce Roberts <b...@bounceitattcanada.xnet> wrote

Quote
> "peterr" <pet...@freemail.absa.co.za> wrote in message

> > I do not see why both have to locked. Presumably, the line items
> > all 'belong' to a single invoice (let's call it the header). If the
> > header is locked then this is an indication that all the line items
> > are not accessible either.

> It depends on the type of "order". I work on a system where multiple
people
> have to be allowed to concurrently view/update the same "order". (We
> actually lost sales before this feature was added.) I'm not thrilled
about
> it, but one does have to provide those pesky users with the
functionality
> they require ;).

I am interested to know where such conditions exists. What kind
of industry and maybe a few more details. I have never heard of
such a case and cannot think of one where this would be
necessary.

Peter Reber

Other Threads