GDA save missing records

Mark Johnson mrj001 at shaw.ca
Sun Feb 17 16:00:55 EST 2008


Phil Longstaff wrote:
> Derek Atkins wrote:
>   
>> Mark Johnson <mrj001 at shaw.ca> writes:
>>
>>   
>>     
>>> Mark Johnson wrote:
>>>     
>>>       
>>>> This appears to be separate from the SERIAL problem of libgda's 
>>>> PostgreSQL provider as PostgreSQL has the highest number of splits.  
>>>> (Most complete?  Are there duplicates?)
>>>>
>>>>
>>>>   
>>>>       
>>>>         
>>> Oops, no it doesn't have the highest number of splits.  It is three less 
>>> than MySql.  Naturally, I should not expect duplicate records - the 
>>> primary key should prevent this.
>>>
>>> By comparing the splits in the PostgreSQL DB to the MySQL DB, I found 
>>> that three were missing from the PostgreSQL copy.  By searching the 
>>> PostgreSQL log file for errors inserting those guids, I found that, in 
>>> all three instances, I had entered text for the memo field that was 
>>> longer than the 50 characters allowed (by the DB schema).  PostgreSQL 
>>> responded by refusing to enter that record.  MySQL simply truncated the 
>>> data.
>>>
>>> So how many characters are allowed in a memo field?
>>>     
>>>       
>> All text fields are "unlimited".  This includes the description, the
>> memo, the action, the number column, etc.
>>   
>>     
>
> I am currently storing them as VARCHAR(n) fields where I just picked a 
> value of n (50 or 100 in most cases).  For the XML backend, there is no 
> problem storing an arbitrary-length string.  With a DB, mysql has 
> TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT which are stored as a 1/2/3/4 
> (respectively) byte length field followed by the string.  Postgres has 
> TEXT for arbitrary-length strings.   It looks as though sqlite strings 
> are arbitrary-length.
>
> Do we really want "unlimited"?  I've alluded to this question in the 
> past, but I don't know if there's been a definitive answer.
>
> Phil
>
>   
If string length limits are settled upon as the answer, I propose a 
4-step implementation:
1. Modify the DB to have those limits
2. Add error checking code so that gnucash identifies and reports errors 
in inserting data.
3. Do something about said errors, such as truncation of strings, 
possibly after asking the user (or just report to the user that this has 
happened.)
4. Modify the gnucash UI to enforce the string limits.

I think steps 1, 2, & 3 could be combined.  (So maybe it's really only a 
2-step implementation).  I do believe that step 4 should be separate.  
The reason is that I'd like to see functional,tested error-checking code 
in place prior to changing the UI so that those errors don't occur (in 
future, they're already going to occur when saving past data).

If TEXT fields are chosen as the answer, do they have 
performance/storage implications?

When I get some time, I'm going to try tracking down what happened with 
the SQLite data.  It will take more time as SQLite does not provide an 
error log (somebody please correct me if that is wrong!!).

Mark



More information about the gnucash-devel mailing list