String lengths in the SQL backend

Donald Allen donaldcallen at gmail.com
Wed Nov 12 11:22:34 EST 2008


On Wed, Nov 12, 2008 at 10:24 AM, Phil Longstaff <plongstaff at rogers.com> 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?

What's the maximum length primary key in mysql? Is that where the 32 came from?

Why is mysql an issue? I thought the backend was going to be sqlite3?
Perhaps you are doing a generic sql layer, designed to talk any one of
many target databases, or the specific target database has changed?

/Don

>
> Phil
>
>
>
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>


More information about the gnucash-user mailing list