SQL Woes under 1.6

[Linas Vepstas]

Hi,

On Mon, Jul 23, 2001 at 01:47:52PM -0400, Michael T. Garrison Stuber was heard to remark:
> > Hmm. Well, the sql backend is supposed to handle KVP frames. Did you
> > discover the name of this kvp frame by reading the source code, or by
> > seeing it in the  db?  Is it being stored in the db, but just not being
> > fetched correctly?
> 
> Digging through source.  Actually, I first found it by saving my data as a 
> file, changing the setting, saving it again, and doing a diff.  From there 
> I went digging throught the Account code.  <FLAMEBAIT> To be honest I find 
> the KVP stuff in the database mind-numbing.  I realize why it's there, and 
> on some level it's a really neato solution, but working on a database 
> application professionally, I find it frustrating that there isn't just a 
> table for accounts, with a column for the price-source.  I mean, what's the 
> point of having a relational database underneath GNUCash, if you can't 
> really use it for anything?  

That's OK, that's not flamebait. KVP is meant to solve a difficult problem.
One the one hand, one wants to specify every table, every column from
now to perpetuity. On the other hand, its hard to anticipate the future 
needs. There's a good case for programmers wanting to add some occasional
ad-hoc field, something that may even be termporary, used in only a few 
versions.  To add some minor ad-hoc field, its bad enough to have to 
add it to the API, but then, to also have to add it to the file format, to
add it to the sql tables, etc is just too painful.  

In one old vision of an 'ideal' world, one adds the definition of the new
field to some IDL (interface definition) file somewhere, press a button, 
and presto-chango, through the magic of 'object introspection' (a buzzword
in MS .net these days) the file backend, the sql backend, the API, and 
everything now supports this field.  Smalltalk kind-of worked this way.  
But the problem there becomes object versioning, you have all of these 
objects that are ostensibly 'the same object', but are slightly different: 
different versions, different fields.  Compatibility becomes a major 
headache.

With gnucash, we tried to walk a middle ground.  The 'important' stuff
is fixed, hard-coded into the API, hard-coded into the sql tables.  The
adhoc, changing, ill-defined, poorly-thought-out stuff goes into KVP frames. 
Someday, we may decide that some particular KVP thing is of general, global
significance, time-tested and proven, and can thus be 'elevated' to 
hard-coded status. 


> I have yet to figure out where 
> the keys get stored in the database.  It looks to me like they don't, they 
> are just connected through a GUID and an iPATH to the value for the key. 

Its a join between three tables.  If you know the guid, you look up the
iguid in gncGUIDCache  And you know the path "/quote-source", look up the
ipath for it in gncPathCache.   The use the iguid and ipath to look up 
gncKVPvalue_str 

vis 

select gncKVPvalue_str.data from gncKVPvalue_str, gncGUIDCache, gncPathCache
where gncKVPvalue_str.ipath=gncPathCache.ipath and 
gncKVPvalue_str.iguid=gncGUIDCache.iguid and 
gncPathCache.path='/quote-source' and
gncGUIDCache.guid='deadbeef';


> > I presume that the response time is bad for just one particular
> > operation,  (e.g. opening a register window or running a report) and  not
> > overall?  If so, then could you run gnucash --loglevel 4 and mail me the
> > output immediately leading up to the point where it hangs?
> 
> Opening, 

Opening a register window, you mean?


> closing and saving.  Normal operations are quite zippy.  Bringing 
> the thing up takes forever, saving takes 3 minutes (I'm running in 
> single-user update mode) 

saving is supposed to be a no-op in single-user update mode, since nothing
is supposed to be written to the db.  Which tells me that somehow you are in fact
running in single-file mode, which writes out *everything* to the database
everytime you hit save.

This may be a classic user error, or it may be that something in the code
is flipping it into the wrong mode.  If you turn on the error message logging,
and start from command line, then near the very begining, it should 
print out the mode that its in. 

> you'd think it would be instant -- calling a 
> commit or something.   Exiting can take 5 minutes. 

Likewise, in any mode other than single-user-file, exiting should be instant.

> any good.  I've tried running in multiuser mode, but every time I do it 
> hoses the something related to dividends, and my net worth goes up to $8 
> million.  (Wouldn't that be nice)

Hmmm.  But when you open the affected register, it should come back to 
a rational figure.   The problem with multi-user is that I have to figure
out what the balance is, and that turns out to be hard.  But I thought I'd
fixed all of those bugs ....

I wonder if the slow save/exit is somehow causing the balance to be recomputed, 
and that could chew up cpu. Yeah. maybe that's it ...

I really need the gnucash --loglevel 4 output to see where its hanging up.

I have some ideas of what it could be, but don't want to spend time fixing 
something that aint broke ...

> BTW, is there a 
> way to save report options without leaving the report open forever?  It 
> would be really nice for users to be able to define some standard customize 
> reports and recall later without leaving them open forever.

Separate issue, I suggest bringing this up in a separate email to
gnucash-devel@gnucash.org

--linas

-- 
I'm very PUBLIC-MINDED, I'm helping a NIGERIAN get his $25,000,000 back!