String lengths in the SQL backend

[Eric Anopolsky]

On Wed, 2008-11-12 at 10:24 -0500, Phil Longstaff wrote:
> For those on the gnucash user list, a new SQL backend is in development.  An 
> issue has arisen, and I need to get input from users, not just developers.
> 
> Rolf Leggewie has run into some problems with the SQL backend 
> (http://bugzilla.gnome.org/show_bug.cgi?id=560165) - he wants to add another 
> SQL table to key off the account 'code' field.  However, the 'code' field is 
> varchar(2048) which is too big to be a primary key in mysql.  I responded that 
> earlier discussion on this mailing list (mainly/only by Derek) had been that 
> strings should be unlimited, and since SQL requires *some* limit, I chose 
> 2048.
> 
> For those who don't know SQL, for a variable length string (varchar), the 
> database engine will only allocate enough space to hold the value, so allowing 
> 2048 and storing only 10 chars uses about the same disk space as allowing 128 
> and storing the same 10.  The problem in this case is that one user wants to 
> be able to index some more information by the account code, but the fields used 
> as the index is too large.
> 
> I'd have to look back, but I think Derek's reply was the only one.  I'd like 
> to open the topic again, because of Rolf's problem.  Can anyone think of a 
> reason that account code size limit cannot be reduced to a smaller value (e.g. 
> 32)?   Will anyone ever enter an account code longer than that?
> 
> Phil

I'm not familiar with gnucash's data structures so I'm not 100% sure
what a "code" is, but would it be possible to identify each account by
an int or a bigint instead of a code? Then this secondary table could
have the "account int" as its foreign primary key, and you could have
another table that maps "account int" to "code" with the code as a
varchar(2048) as desired.

Or put the "account int" and the "code" in the same table and make the
int the primary key instead.

Totally unreasonable?

Cheers,
Eric

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 489 bytes
Desc: This is a digitally signed message part
Url : http://lists.gnucash.org/pipermail/gnucash-devel/attachments/20081112/1d010bd8/attachment.bin