On 11/27/06, Allen Gilliland <[EMAIL PROTECTED]> wrote:
Dave wrote:
> On 11/26/06, Allen Gilliland <[EMAIL PROTECTED]> wrote:
>> Well, we should see how other databases handle the same situation,
>> because technically there is no way we could support storing the path in
>> the database at a length of 255 if we continue to allow 255 chars for
>> node names and infinite hierarchy depth.
>>
>> I am fine with changing the column to a varchar(255), but that will mean
>> the users could potentially run into problems with creating hierarchies
>> that are too deep.
>
> Problem is that Derby (and I suspect other databases as well) do not
> support comparisons on CLOB fields. We can't use a big VARCHAR because
> MySQL 4 does support VARCHAR's longer than 255. So, the fix I
> committed was to allow MySQL to continue to use TEXT and everybody
> else uses VARCHAR(1000).
okay, well, i'm not sure that's necessarily the best way to handle it
then. if most dbs won't support the use of a BLOB/TEXT column because
you can't do comparisons against it then that's fine, but maybe we
should try and just use VARCHAR for all cases to be consistent and
notify people using MySQL 4.x or earlier that they need to handle the
situation on their own. i don't particularly like it when we have to be
the ones supporting these one-off deviations because over time we end up
with everyone having different dbs and that causes more headaches.
so we need to figure out what the max value for a varchar is for most
dbs and probably should set the column to that size, it looks like mysql
5 supports 65K for varchars now so it won't be the limiting db. we may
also want to impose some tighter restrictions on the name column and
shorten it below 255. if we consider BSC as an example i have noticed
that between our 2500 users & blogs *all* of the hierarchy paths have
fit within 255 characters, so that suggests that nobody is coming even
close to using 255 chars for the category/folder names. i don't
particularly like shortening columns, but if we set the name column down
to 128 chars and the path to 1024 then at least this way we are trying
to contain the problem.
Yeah, I don't like one-offs either. My preference is to use the
shortest varchar within reason. Unfortunately, thanks to MySQL 4.X,
that 255. Fortunately, as you point out, that's well within reason
considering our current category usage.
- Dave
