String lengths in the SQL backend

[Phil Longstaff]

On November 12, 2008 12:11:13 pm Donald Allen wrote:
> On Wed, Nov 12, 2008 at 11:48 AM, Phil Longstaff <plongstaff at rogers.com> 
wrote:
> > On November 12, 2008 11:22:34 am Donald Allen wrote:
> >> 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?
> >
> > It uses a library which supports multiple databases.  Sqlite3 is the
> > default, but mysql and postgresql will also be supported.  With not too
> > much extra work, other databases could also be supported, but they are
> > not on the list for the first release.
>
> Ok, makes sense. Where did the 32 come from? Is that the actual
> maximum length of a varchar primary key in mysql? Or was that a number
> you just made up as an example?
>
> I ask because I actually do use the account code field to relate
> dividends to commodities for use by an external reporting package I
> wrote and a few of them are greater than 32 characters. If that field
> were limited to 32 characters, I'd work around it -- not a big deal --
> but I'd like to be sure that the choice of 32 is not arbitrary. Said
> another way, if it is arbitrary, I'd vote for making it
> min(max(primary_key_length[i])) where i ranges over the supported
> databases.

The value '32' is just made-up.  I picked a not-too-large, not-too-small power 
of 2.  What you suggest (min(max(primary_key_length[i])) makes sense.  I've 
seen an old post (circa 2004) which has 500 bytes as max key length for mysql.  
If this field is typed, it'll need to be utf8 which needs 3 bytes/char, so 
we're looking at 166 chars for mysql.  The max key length might be longer now, 
but I might round down to 150 chars.

Phil