[SQL-Ledger-users] OT: Web-based Checking Software

Linas Vepstas sql-ledger-users@lists.sourceforge.net
Sat, 5 May 2001 23:17:14 -0500

Hi Don,
Good to hear from you, been a long time ...

On Sat, May 05, 2001 at 04:17:40PM -0500, Donald L Greer Jr. was heard to remark:
>   Linas,
>   Rather than keep a running total on a per-transaction basis, why not
> keep a daily balance.  Then if you change a transaction 2 months ago,
> you've got to apply one change to 60 entries and the change is always
> the same (+/- X).  

right.  This is what I called a 'balance checkpoint' in the original
note. (could be daily, could be every N transactions, whatever).

> A single query can do that lickity-split.

Umm. How good are you at sql?  I can't say that I know how to 
select 60 rows, add a value to a field, and update those rows,
in a single sql statement.   Have I just not mastered the finer points
of sql?  Can you sketch out that statement?

>   Then when you display, you calculate the running total from the
> starting balance of the first day displayed, allowing you to process
> (hopefully) very few transactions.  Alternately the running total could
> be based on some arbitrary number of transactions (say the balance every
> hundredth transaction to avoid problems with high-volumes of
> transactions (e.g. a video store or a fast-food resteraunt that does
> 1000+ transactions/day).
>   Just a thought

The problem with what you describe is this:
Say I want to display all transactions since noon, but the last
balance checkpoint was midnight.  Thus, I have to query all transactions
since midnight, compute the running blance, and display only those
since noon.   It requires a little extra work, but its doable.

Now imagine I want to show all transactions whose memo field starts
with the letter A...

see what the problem is? first I get all with letter A.  Then I need to
figure out what checkpoint lies before the earliest transaction, get
that, and then get all transactions since then, then compute the running
balance ...  Ugh.

Not too hard to explain, but rather tedious to code up.    Just tedious
enough that I haven't done it ...


> Linas Vepstas wrote:
> > 
> [...] 
> > Well, its 'almost' usable, except for one interesting technical problem
> > I'd like to tell you about.  GnuCash likes to display accounts in a
> > 'checkbook register style': that means that there is a running balance
> > displayed in one of the columns (far right column).
> > 
> > Say one has a millions transactions in the database. Clearly its insane
> > to fetch all of them to compute a running balance.  We could store a
> > running balance with each transaction, but this has a nasty update
> > semantic: changing one transaction would require updates to hundreds
> > or thousands of others that occur at a later date.  We talked about
> > storing 'balance checkpoints' to solve that problem.  But this
> > introduces another problem: I would need to query a contiguous set of
> > transactions between checkpoints.  At this point, the sql queries start
> > getting complex, there's a lot of traffic to the database, and yuck,
> > so we left it to ferment a bit more.    If you are willing to ignore
> > the running balance column, then I think the multi-user mode is not far
> > off.  (there's some minor misc stuff that needs to be brushed up, I
> > forget what).
> > 
> [...]
> -- 
> --------------------------------------------------------
> Donald L. Greer, Jr                  dgreer@AustinTX.COM
> System Administrator                 Voice: 512-835-8005
> AustinTX.COM                    http://www.AustinTX.COM/
>   All opinions are my own.  Flame me directly.
> "I don't necessarily believe software should be free...
> but if you pay for it, it should work!" -- Me

Linas Vepstas -- linas@gnumatic.com -- http://www.gnumatic.com/