At 10:30 PM 5/08/2004 +1000, you wrote:

>Hello Helen,
>
> >OK, I think the first thing you have to let go is your belief that a lookup
> >is the way to implement this 1:1 relationship. "Lookup" is really a
> >desktop-database thing, a Paradoxy thing, that's very costly to
> >client/server implementation. Although lookups have their uses in
> >client/server, this aint one of them. I won't explain that here, it's a
> >bridge you have yet to cross.
> >
> >
> >
>The lookup is simply a usability thing, so yep, it can be removed from
>equation for now.
>
> >There are plenty of real-world examples around but they won't mean much
> >until you've got the multi-user, transaction-isolation aspects on board.
> >
> >
> >
>I think I've got the transaction isolation under my belt. Commited = no
>one else can read the changes to data you make until you commit your
>transaction.

No, the opposite. A transaction in ReadCommitted can see all work committed
by other transactions, before it has committed its own work. That means it
is possible to maintain a dynamic view of the changing state of the database.

>Consistant = Snapshot of the requested data, useful for
>generating reports.

No. What you say is true for Concurrency, but that's only about a useful
way to use it. Some people use Concurrency for everything. Concurrency
maintains the view of database state as it was at the start of the
transaction, for as long as the transaction lasts. IOW, it can never see
the effects of work committed by other transactions - it's a static view of
a point in time.

>Then there's the other one.

The "other one" is Consistency, which you should ignore. It does full
table locking in a very unfriendly way. There is another transaction
parameter, Reserving, which is used in combination with Concurrency to
provide much more friendly table locking, should it ever be needed (very
rare!).

>I'm also clear on difference between Commit and Post.

OK. So you know that Post applies to statements, while Commit applies to
transactions, right?

> >So, let's look at this join. I'm totally guessing the columns in your
> >Product and Stocksheet tables but I hope you'll get the picture.
> >
> >select p.productid, p,productname, s.stockcount
> > from product p
> > join stocksheet s
> > on p.productid = s.productid
> >where....(whatever search criteria you need to drill down with)
> >
> >This set has no lookups and no need for any.
> >
> >Yes,*explicit* locking is something to avoid most of the time because, most
> >of the time, it's not necessary. Transactions have an attribute called
> >isolation and another called LockWait. By choosing the right combination
> >to suit what you need, you can get exactly the level of locking you need
> >performed by the database engine.
> >
> >However, in this case, you *do* need an explicit lock -- one that gets
> >applied immediately before the dataset allows you to begin editing. This
> >is because "editing" isn't a database activity, it's a client
> >activity. The database has no idea that a client is editing anything until
> >AFTER the user has finished and actually posts the changes to the database.
> >
> >So what happens with the explicit lock (the PessimisticLocking property of
> >the dataset set true) is that the dataset posts a dummy update to the
> >database *before* the user starts editing the row. It's all done in the
> >same, explicitly-controlled (not Autocommit) transaction. The dummy update
> >causes the row (or rows, in the case of a set that's not naturally
> >updatable) to be locked: no other transaction can then post an update to
> >it. That's all it takes.
> >
> >Now, it's really important to understand that Post and Commit are two
> >entirely separate things. Post creates a new record version on the server
> >but it is invisible to other transactions. You can post many DML statements
> >before you actually end the transaction. Commit, finally, makes all of
> >that the work visible to all transactions; rollback reverts database state
> >to what it was before the transaction began.
> >
> >Now, going back to our joined dataset. A joined dataset isn't naturally
> >updatable but, in IBO, you can make it so. If you need to update more than
> >one of the tables in the join, you write a parameterised, executable stored
> >procedure to do it. If you only have to update one table (your case here)
> >you set the dataset's KeyRelation property to be the table that is getting
> >the update. You will need a parameterised stored procedure for the
> >LockSQL. Basically, it takes the common key of the two tables and updates
> >each one by setting the primary key = the primary key.
> >
> >Now, the dummy update will either succeed (because no other transaction has
> >an update pending on that row) or fail (because another transaction has an
> >update pending). You intercept the error in the latter case and tell the
> >user she can't edit that record now because someone else is doing so.
> >
> >
> >
>That's okay for editing a record (I will delve into it over the
>weekend), but IBO doesn't attempt any locking (custom or not) unless the
>dataset is about to go into edit mode...so what happens when you create
>a new record?

Nothing. If you insert a new row into the row buffer, it exists nowhere
except in the row buffer. From the point of view of your requirements,
there is absolutely NO way to know that another transaction is inserting a
record until it actually commits the requested work. There is no "dirty
read". So, this "lookup" thing of yours (if it's vulnerable to change by
the addition of new Stocksheet rows) must find a way to a) get the latest
aggregate and b) freeze the aggregate, either *before* it goes into Insert
mode or before it calls Post.

>Or for appending would you just display the current stock
>count for the entered stocksheet, and attempt a commit with whatever
>values the user enters, and then deal with any incorrect constraints
>such as stock sold > stock count?

Yes: and you can do that with a trigger. But in no case can any
transaction of yours see or be affected by any unposted changes by another
transaction. So that's where transaction parameters come into play. If
your transaction succeeds in posting, others' unposted changes and
deletions will fail on post and their unposted inserts will succeed.

>More of a "try and see" kind of way of doing things? ie Even though the
>current stock count is 5 when you create a new sale entry, and you enter
>in a value of 5 items to be sold, but whilst your creating this sale,
>someone realizes the count on the stock sheet is incorrect and changes
>the count to 4. Hence when the new sale entry of 5 items is posted, some
>checking is done and then an error reported to the user.
>
>If the above is the way to go about these things, then it's quite
>non-user friendly...what if you've just promised 5 items to a chap over
>the phone whilst you were creating the sale entry, then commited the
>transaction after hanging up!?

Depending on how well your architecture fits with user behaviour, it could
be that way. You can't have butter on both sides of the bread. If you have
a system where stock is allowed to be wrong and to be corrected ad hoc,
without checking the effects, then no locking system in the world is going
to make that right.

You typically get this situation where stock items are counted in from
paper documents without checking the physical count. In some systems, it's
impossible. One big system I did for packaging materials was a
nightmare. Materials came in in huge rolls. Used but unfinished rolls
were returned into stock less the metrage recorded by the presses. The
rolls would be left about the factory in mobile cradles, waiting for the
store guys to collect them. Any stuff, especially foil, left about on
cradles, was subject to heavy pilfering between shifts. Nobody grassed on
anybody. Where they could, the supervisors "tamped" the metrage records by
guessing how much would be stolen that day and rocking the machine
counters. At Christmas and when a public holiday was coming up, it would go
through the roof and the system couldn't work at all. (Yeah, foil, paper
and plastic film are essential for big barbecue events and they cost a bomb
if you have to pay for them!). The flow-on effects from unexpectedly short
rolls are unbelievably costly. It would have actually been cheaper to
order in extra supplies that the staff could just help themselves from
since, short of employing vigilantes, there was no way the pilfering could
be controlled.

Stock accounting works both ways -- if you have a system where it's
possible to allocate quantities you don't really have, you must have
fallbacks in the scheme that can detect the effect of unnatural changes.
Some stock accounting systems let stock go negative and don't confirm
customer orders until physical allocation takes place.

Anyway, you aren't asking me to design your system for you. :-)

> >Now, are you saying that the stockcount is calculated from multiple
> >records, i.e. the relationship between the tables is 1:many and you're
> >updating a master field from summing the details? And that the stockcount
> >is modified by adding more records on the Many side?
>
> >
>No, the stock sheet contains a "delivered" qty if you like, and then (I
>had in mind) the sum of the sales entries would give you the total sold.
>To give some context to this, the turnover of stock (and hence sales) is
>a weekly basis. eg stock comes in in the morning, gets entered into a
>stock sheet, then through the course of the day (or over a day or so),
>various sales are made against the stocksheets - eg raise an invoice
>against stock sheet 1122, which happens to be 5 items of product A, and
>enter an invoice qty less than the remaining qty, which would be 5 minus
>the sum of current sales against stock sheet 1122.

It's still not clear whether the relationship between product and
stocksheet is 1:1 or 1:many. If an order for a particular product can be
allocated from multiple stocksheets, then the task is not the simple case
you describe above. Your description begs the question of what happens to
the "leftovers"... not my can o' worms though...

Helen