You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@roller.apache.org by ag...@apache.org on 2006/11/22 19:33:35 UTC
svn commit: r478275 - in /incubator/roller/trunk/metadata/database:
310-to-320-migration.vm createdb.vm
Author: agilliland
Date: Wed Nov 22 10:33:34 2006
New Revision: 478275
URL: http://svn.apache.org/viewvc?view=rev&rev=478275
Log:
new path columns need to be of type TEXT because they could be fairly long in cases where people have a deep hierarchy.
Modified:
incubator/roller/trunk/metadata/database/310-to-320-migration.vm
incubator/roller/trunk/metadata/database/createdb.vm
Modified: incubator/roller/trunk/metadata/database/310-to-320-migration.vm
URL: http://svn.apache.org/viewvc/incubator/roller/trunk/metadata/database/310-to-320-migration.vm?view=diff&rev=478275&r1=478274&r2=478275
==============================================================================
--- incubator/roller/trunk/metadata/database/310-to-320-migration.vm (original)
+++ incubator/roller/trunk/metadata/database/310-to-320-migration.vm Wed Nov 22 10:33:34 2006
@@ -9,12 +9,12 @@
create index ws_parentid_idx on weblogcategory( parentid );
-- add new path column to weblogcategory table
-#addColumnNull("weblogcategory" "path" "varchar(255)")
-create index ws_path_idx on weblogcategory( path );
+#addColumnNull("weblogcategory" "path" $TEXT_SQL_TYPE)
+create index ws_path_idx on weblogcategory( path(255) );
-- need to add this index for existing folder.parentid
create index fo_parentid_idx on folder( parentid );
-- add new path column to folder table
-#addColumnNull("folder" "path" "varchar(255)")
-create index fo_path_idx on folder( path );
+#addColumnNull("folder" "path" $TEXT_SQL_TYPE)
+create index fo_path_idx on folder( path(255) );
Modified: incubator/roller/trunk/metadata/database/createdb.vm
URL: http://svn.apache.org/viewvc/incubator/roller/trunk/metadata/database/createdb.vm?view=diff&rev=478275&r1=478274&r2=478275
==============================================================================
--- incubator/roller/trunk/metadata/database/createdb.vm (original)
+++ incubator/roller/trunk/metadata/database/createdb.vm Wed Nov 22 10:33:34 2006
@@ -130,11 +130,11 @@
description varchar(255),
websiteid varchar(48) not null,
parentid varchar(48),
- path varchar(255)
+ path $TEXT_SQL_TYPE
);
create index fo_websiteid_idx on folder( websiteid );
create index fo_parentid_idx on folder( parentid );
-create index fo_path_idx on folder( path );
+create index fo_path_idx on folder( path(255) );
create table folderassoc (
id varchar(48) not null primary key,
@@ -166,12 +166,11 @@
websiteid varchar(48) not null,
image varchar(255),
parentid varchar(48),
- path varchar(255)
+ path $TEXT_SQL_TYPE
);
create index wc_websiteid_idx on weblogcategory( websiteid );
create index ws_parentid_idx on weblogcategory( parentid );
-create index ws_path_idx on weblogcategory( path );
--- alter table weblogcategory add unique category_nameparentid_uq (parentid, name(20));
+create index ws_path_idx on weblogcategory( path(255) );
create table weblogcategoryassoc (
id varchar(48) not null primary key,
Re: svn commit: r478275 - in
/incubator/roller/trunk/metadata/database: 310-to-320-migration.vm createdb.vm
Posted by Allen Gilliland <al...@sun.com>.
dear lord, you must have read my mind. i just sent out an email about
this a few seconds ago.
-- Allen
Dave wrote:
> What's the consensus here?
>
> Currently, the database creation scripts setup MySQL to continue to
> use TEXT for the category and folder path fields and and everybody
> else uses VARCHAR(1000). Not ideal, but it works.
>
> Should I change that and force all databases to use VARCHAR(255) for
> the path fields? That will work on all databases (MySQL 4 is the
> limiting factor).
>
> Either way, we'll have to add logic to prevent users from creating
> category hierarchies that will not fit in the path field.
>
> - Dave
>
>
>
> On 11/27/06, Elias Torres <el...@torrez.us> wrote:
>>
>> Dave wrote:
>> > On 11/27/06, Allen Gilliland <al...@sun.com> wrote:
>>
>> ...[snip]...
>>
>>
>> > 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
>> >
>>
>> I guess that as long as we stick to varchars we can also leave that up
>> to the installer to decide the needs of their site as a last resource.
>>
>> -Elias
>>
Re: svn commit: r478275 - in /incubator/roller/trunk/metadata/database: 310-to-320-migration.vm createdb.vm
Posted by Dave <sn...@gmail.com>.
What's the consensus here?
Currently, the database creation scripts setup MySQL to continue to
use TEXT for the category and folder path fields and and everybody
else uses VARCHAR(1000). Not ideal, but it works.
Should I change that and force all databases to use VARCHAR(255) for
the path fields? That will work on all databases (MySQL 4 is the
limiting factor).
Either way, we'll have to add logic to prevent users from creating
category hierarchies that will not fit in the path field.
- Dave
On 11/27/06, Elias Torres <el...@torrez.us> wrote:
>
> Dave wrote:
> > On 11/27/06, Allen Gilliland <al...@sun.com> wrote:
>
> ...[snip]...
>
>
> > 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
> >
>
> I guess that as long as we stick to varchars we can also leave that up
> to the installer to decide the needs of their site as a last resource.
>
> -Elias
>
Re: need column type & length for new 'path' columns
Posted by Allen Gilliland <al...@sun.com>.
Dave wrote:
> On 12/6/06, Allen Gilliland <al...@sun.com> wrote:
>> This is still unresolved, so we need to make a decision about it.
>>
>> I can't speak for all dbs, but MySQL 4.x and older only allow
>> varchar(255) so to be truly covered on those dbs would need to use a
>> TEXT column. It seems like many of the other dbs allow varchar columns
>> much longer, which would be more ideal.
>>
>> So, the options are ...
>>
>> 1. use varchar(1024) for all dbs except mysql and use TEXT for mysql.
>>
>> 2. use varchar(1024) for all dbs except mysql and use varchar(255) for
>> mysql.
>>
>> 3. use varchar(1024) for all dbs and just note in the script that for
>> MySQL 4.x and older the user needs to set the value to 255 or use a TEXT
>> column.
>>
>> 4. use varchar(255) for all dbs and just hope that nobody has a path
>> depth of more than 255 characters. NOTE: if we do this we may also want
>> to change the category.name and folder.name columns down to 128 chars
>> instead of 255 like they are now.
>>
>> and of course we don't need to use varchar(1024), i just made that up.
>> the current schema allows folder/category names of up to 255 characters,
>> so 1024 would technically be pretty safe for at least 4 levels.
>> However, of the 2500+ blogs on BSC there isn't a single one with a path
>> that goes over 255 characters, so maybe 255 or 512 is safe enough?
>>
>> in any case, i would favor #3 or #4 to keep things consistent. I don't
>> like the idea of having the sql script path having lots of conditionals
>> all over the place for the various dbs.
>
> I vote for option #4 because category path's over 255 are just silly,
> but we still need some sort of check or nice error message at category
> creation time for silly people who like deep hierarchies with
> long-winded category names.
>
> If I'm underestimating the number of silly people or the size of
> multi-byte category names, then we can always change it later because
> most databases allow ALTER TABLE to increase VARCHAR size.
I agree, #4 sounds like a good option to me and at the very least any
users who do happen to run into a problem with 255 characters being too
short will find out about it as part of the upgrade process and so they
could simply increase the column size themselves.
-- Allen
>
> - Dave
Re: need column type & length for new 'path' columns
Posted by Dave <sn...@gmail.com>.
On 12/6/06, Allen Gilliland <al...@sun.com> wrote:
> This is still unresolved, so we need to make a decision about it.
>
> I can't speak for all dbs, but MySQL 4.x and older only allow
> varchar(255) so to be truly covered on those dbs would need to use a
> TEXT column. It seems like many of the other dbs allow varchar columns
> much longer, which would be more ideal.
>
> So, the options are ...
>
> 1. use varchar(1024) for all dbs except mysql and use TEXT for mysql.
>
> 2. use varchar(1024) for all dbs except mysql and use varchar(255) for
> mysql.
>
> 3. use varchar(1024) for all dbs and just note in the script that for
> MySQL 4.x and older the user needs to set the value to 255 or use a TEXT
> column.
>
> 4. use varchar(255) for all dbs and just hope that nobody has a path
> depth of more than 255 characters. NOTE: if we do this we may also want
> to change the category.name and folder.name columns down to 128 chars
> instead of 255 like they are now.
>
> and of course we don't need to use varchar(1024), i just made that up.
> the current schema allows folder/category names of up to 255 characters,
> so 1024 would technically be pretty safe for at least 4 levels.
> However, of the 2500+ blogs on BSC there isn't a single one with a path
> that goes over 255 characters, so maybe 255 or 512 is safe enough?
>
> in any case, i would favor #3 or #4 to keep things consistent. I don't
> like the idea of having the sql script path having lots of conditionals
> all over the place for the various dbs.
I vote for option #4 because category path's over 255 are just silly,
but we still need some sort of check or nice error message at category
creation time for silly people who like deep hierarchies with
long-winded category names.
If I'm underestimating the number of silly people or the size of
multi-byte category names, then we can always change it later because
most databases allow ALTER TABLE to increase VARCHAR size.
- Dave
need column type & length for new 'path' columns
Posted by Allen Gilliland <al...@sun.com>.
This is still unresolved, so we need to make a decision about it.
I can't speak for all dbs, but MySQL 4.x and older only allow
varchar(255) so to be truly covered on those dbs would need to use a
TEXT column. It seems like many of the other dbs allow varchar columns
much longer, which would be more ideal.
So, the options are ...
1. use varchar(1024) for all dbs except mysql and use TEXT for mysql.
2. use varchar(1024) for all dbs except mysql and use varchar(255) for
mysql.
3. use varchar(1024) for all dbs and just note in the script that for
MySQL 4.x and older the user needs to set the value to 255 or use a TEXT
column.
4. use varchar(255) for all dbs and just hope that nobody has a path
depth of more than 255 characters. NOTE: if we do this we may also want
to change the category.name and folder.name columns down to 128 chars
instead of 255 like they are now.
and of course we don't need to use varchar(1024), i just made that up.
the current schema allows folder/category names of up to 255 characters,
so 1024 would technically be pretty safe for at least 4 levels.
However, of the 2500+ blogs on BSC there isn't a single one with a path
that goes over 255 characters, so maybe 255 or 512 is safe enough?
in any case, i would favor #3 or #4 to keep things consistent. I don't
like the idea of having the sql script path having lots of conditionals
all over the place for the various dbs.
-- Allen
Elias Torres wrote:
> Dave wrote:
>> On 11/27/06, Allen Gilliland <al...@sun.com> wrote:
>
> ...[snip]...
>
>
>> 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
>>
>
> I guess that as long as we stick to varchars we can also leave that up
> to the installer to decide the needs of their site as a last resource.
>
> -Elias
Re: svn commit: r478275 - in /incubator/roller/trunk/metadata/database:
310-to-320-migration.vm createdb.vm
Posted by Elias Torres <el...@torrez.us>.
Dave wrote:
> On 11/27/06, Allen Gilliland <al...@sun.com> wrote:
...[snip]...
> 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
>
I guess that as long as we stick to varchars we can also leave that up
to the installer to decide the needs of their site as a last resource.
-Elias
Re: svn commit: r478275 - in /incubator/roller/trunk/metadata/database: 310-to-320-migration.vm createdb.vm
Posted by Dave <sn...@gmail.com>.
On 11/27/06, Allen Gilliland <al...@sun.com> wrote:
> Dave wrote:
> > On 11/26/06, Allen Gilliland <al...@sun.com> 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
Re: svn commit: r478275 - in
/incubator/roller/trunk/metadata/database: 310-to-320-migration.vm createdb.vm
Posted by Allen Gilliland <al...@sun.com>.
Dave wrote:
> On 11/26/06, Allen Gilliland <al...@sun.com> 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.
-- Allen
>
> - Dave
Re: svn commit: r478275 - in /incubator/roller/trunk/metadata/database: 310-to-320-migration.vm createdb.vm
Posted by Dave <sn...@gmail.com>.
On 11/26/06, Allen Gilliland <al...@sun.com> 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).
- Dave
Re: svn commit: r478275 - in
/incubator/roller/trunk/metadata/database: 310-to-320-migration.vm createdb.vm
Posted by Allen Gilliland <al...@sun.com>.
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.
-- Allen
Dave wrote:
> Unfortunately, Derby doesn't allow comparison's to CLOB fields and
> throws an exception such as the one shown below when you try to
> compare cat paths. There may be a work around, but for now it may be
> safest to restrict path to 255 (but man'o'man that seems short).
>
> - Dave
>
>
>
> WARN 2006-11-24 21:37:04,372 JDBCExceptionReporter:logExceptions -
> SQL Error: -1, SQLState: 42818
> ERROR 2006-11-24 21:37:04,375 JDBCExceptionReporter:logExceptions -
> Comparisons between 'CLOB' and 'CLOB' are not supported.
> ERROR 2006-11-24 21:37:04,376 MetaWeblogAPIHandler:editPost - ERROR in
> MetaWeblogAPIHandler.editPost
> org.hibernate.exception.SQLGrammarException: could not execute query
> at
> org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
>
> at
> org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
>
> at org.hibernate.loader.Loader.doList(Loader.java:2148)
> at
> org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
> at org.hibernate.loader.Loader.list(Loader.java:2024)
> at
> org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
> at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1552)
> at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
> at
> org.hibernate.impl.CriteriaImpl.uniqueResult(CriteriaImpl.java:305)
> at
> org.apache.roller.business.hibernate.HibernateWeblogManagerImpl.getWeblogCategoryByPath(HibernateWeblogManagerImpl.java:929)
>
>
>
>
>
> On 11/22/06, agilliland@apache.org <ag...@apache.org> wrote:
>> Author: agilliland
>> Date: Wed Nov 22 10:33:34 2006
>> New Revision: 478275
>>
>> URL: http://svn.apache.org/viewvc?view=rev&rev=478275
>> Log:
>> new path columns need to be of type TEXT because they could be fairly
>> long in cases where people have a deep hierarchy.
>>
>>
>> Modified:
>> incubator/roller/trunk/metadata/database/310-to-320-migration.vm
>> incubator/roller/trunk/metadata/database/createdb.vm
>>
>> Modified:
>> incubator/roller/trunk/metadata/database/310-to-320-migration.vm
>> URL:
>> http://svn.apache.org/viewvc/incubator/roller/trunk/metadata/database/310-to-320-migration.vm?view=diff&rev=478275&r1=478274&r2=478275
>>
>> ==============================================================================
>>
>> --- incubator/roller/trunk/metadata/database/310-to-320-migration.vm
>> (original)
>> +++ incubator/roller/trunk/metadata/database/310-to-320-migration.vm
>> Wed Nov 22 10:33:34 2006
>> @@ -9,12 +9,12 @@
>> create index ws_parentid_idx on weblogcategory( parentid );
>>
>> -- add new path column to weblogcategory table
>> -#addColumnNull("weblogcategory" "path" "varchar(255)")
>> -create index ws_path_idx on weblogcategory( path );
>> +#addColumnNull("weblogcategory" "path" $TEXT_SQL_TYPE)
>> +create index ws_path_idx on weblogcategory( path(255) );
>>
>> -- need to add this index for existing folder.parentid
>> create index fo_parentid_idx on folder( parentid );
>>
>> -- add new path column to folder table
>> -#addColumnNull("folder" "path" "varchar(255)")
>> -create index fo_path_idx on folder( path );
>> +#addColumnNull("folder" "path" $TEXT_SQL_TYPE)
>> +create index fo_path_idx on folder( path(255) );
>>
>> Modified: incubator/roller/trunk/metadata/database/createdb.vm
>> URL:
>> http://svn.apache.org/viewvc/incubator/roller/trunk/metadata/database/createdb.vm?view=diff&rev=478275&r1=478274&r2=478275
>>
>> ==============================================================================
>>
>> --- incubator/roller/trunk/metadata/database/createdb.vm (original)
>> +++ incubator/roller/trunk/metadata/database/createdb.vm Wed Nov 22
>> 10:33:34 2006
>> @@ -130,11 +130,11 @@
>> description varchar(255),
>> websiteid varchar(48) not null,
>> parentid varchar(48),
>> - path varchar(255)
>> + path $TEXT_SQL_TYPE
>> );
>> create index fo_websiteid_idx on folder( websiteid );
>> create index fo_parentid_idx on folder( parentid );
>> -create index fo_path_idx on folder( path );
>> +create index fo_path_idx on folder( path(255) );
>>
>> create table folderassoc (
>> id varchar(48) not null primary key,
>> @@ -166,12 +166,11 @@
>> websiteid varchar(48) not null,
>> image varchar(255),
>> parentid varchar(48),
>> - path varchar(255)
>> + path $TEXT_SQL_TYPE
>> );
>> create index wc_websiteid_idx on weblogcategory( websiteid );
>> create index ws_parentid_idx on weblogcategory( parentid );
>> -create index ws_path_idx on weblogcategory( path );
>> --- alter table weblogcategory add unique category_nameparentid_uq
>> (parentid, name(20));
>> +create index ws_path_idx on weblogcategory( path(255) );
>>
>> create table weblogcategoryassoc (
>> id varchar(48) not null primary key,
>>
>>
>>
Re: svn commit: r478275 - in /incubator/roller/trunk/metadata/database: 310-to-320-migration.vm createdb.vm
Posted by Dave <sn...@gmail.com>.
Unfortunately, Derby doesn't allow comparison's to CLOB fields and
throws an exception such as the one shown below when you try to
compare cat paths. There may be a work around, but for now it may be
safest to restrict path to 255 (but man'o'man that seems short).
- Dave
WARN 2006-11-24 21:37:04,372 JDBCExceptionReporter:logExceptions -
SQL Error: -1, SQLState: 42818
ERROR 2006-11-24 21:37:04,375 JDBCExceptionReporter:logExceptions -
Comparisons between 'CLOB' and 'CLOB' are not supported.
ERROR 2006-11-24 21:37:04,376 MetaWeblogAPIHandler:editPost - ERROR in
MetaWeblogAPIHandler.editPost
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1552)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at org.hibernate.impl.CriteriaImpl.uniqueResult(CriteriaImpl.java:305)
at org.apache.roller.business.hibernate.HibernateWeblogManagerImpl.getWeblogCategoryByPath(HibernateWeblogManagerImpl.java:929)
On 11/22/06, agilliland@apache.org <ag...@apache.org> wrote:
> Author: agilliland
> Date: Wed Nov 22 10:33:34 2006
> New Revision: 478275
>
> URL: http://svn.apache.org/viewvc?view=rev&rev=478275
> Log:
> new path columns need to be of type TEXT because they could be fairly long in cases where people have a deep hierarchy.
>
>
> Modified:
> incubator/roller/trunk/metadata/database/310-to-320-migration.vm
> incubator/roller/trunk/metadata/database/createdb.vm
>
> Modified: incubator/roller/trunk/metadata/database/310-to-320-migration.vm
> URL: http://svn.apache.org/viewvc/incubator/roller/trunk/metadata/database/310-to-320-migration.vm?view=diff&rev=478275&r1=478274&r2=478275
> ==============================================================================
> --- incubator/roller/trunk/metadata/database/310-to-320-migration.vm (original)
> +++ incubator/roller/trunk/metadata/database/310-to-320-migration.vm Wed Nov 22 10:33:34 2006
> @@ -9,12 +9,12 @@
> create index ws_parentid_idx on weblogcategory( parentid );
>
> -- add new path column to weblogcategory table
> -#addColumnNull("weblogcategory" "path" "varchar(255)")
> -create index ws_path_idx on weblogcategory( path );
> +#addColumnNull("weblogcategory" "path" $TEXT_SQL_TYPE)
> +create index ws_path_idx on weblogcategory( path(255) );
>
> -- need to add this index for existing folder.parentid
> create index fo_parentid_idx on folder( parentid );
>
> -- add new path column to folder table
> -#addColumnNull("folder" "path" "varchar(255)")
> -create index fo_path_idx on folder( path );
> +#addColumnNull("folder" "path" $TEXT_SQL_TYPE)
> +create index fo_path_idx on folder( path(255) );
>
> Modified: incubator/roller/trunk/metadata/database/createdb.vm
> URL: http://svn.apache.org/viewvc/incubator/roller/trunk/metadata/database/createdb.vm?view=diff&rev=478275&r1=478274&r2=478275
> ==============================================================================
> --- incubator/roller/trunk/metadata/database/createdb.vm (original)
> +++ incubator/roller/trunk/metadata/database/createdb.vm Wed Nov 22 10:33:34 2006
> @@ -130,11 +130,11 @@
> description varchar(255),
> websiteid varchar(48) not null,
> parentid varchar(48),
> - path varchar(255)
> + path $TEXT_SQL_TYPE
> );
> create index fo_websiteid_idx on folder( websiteid );
> create index fo_parentid_idx on folder( parentid );
> -create index fo_path_idx on folder( path );
> +create index fo_path_idx on folder( path(255) );
>
> create table folderassoc (
> id varchar(48) not null primary key,
> @@ -166,12 +166,11 @@
> websiteid varchar(48) not null,
> image varchar(255),
> parentid varchar(48),
> - path varchar(255)
> + path $TEXT_SQL_TYPE
> );
> create index wc_websiteid_idx on weblogcategory( websiteid );
> create index ws_parentid_idx on weblogcategory( parentid );
> -create index ws_path_idx on weblogcategory( path );
> --- alter table weblogcategory add unique category_nameparentid_uq (parentid, name(20));
> +create index ws_path_idx on weblogcategory( path(255) );
>
> create table weblogcategoryassoc (
> id varchar(48) not null primary key,
>
>
>
Re: svn commit: r478275 - in /incubator/roller/trunk/metadata/database: 310-to-320-migration.vm createdb.vm
Posted by Dave <sn...@gmail.com>.
Unfortunately, Derby doesn't allow comparison's to CLOB fields and
throws an exception such as the one shown below when you try to
compare cat paths. There may be a work around, but for now it may be
safest to restrict path to 255 (but man'o'man that seems short).
- Dave
WARN 2006-11-24 21:37:04,372 JDBCExceptionReporter:logExceptions -
SQL Error: -1, SQLState: 42818
ERROR 2006-11-24 21:37:04,375 JDBCExceptionReporter:logExceptions -
Comparisons between 'CLOB' and 'CLOB' are not supported.
ERROR 2006-11-24 21:37:04,376 MetaWeblogAPIHandler:editPost - ERROR in
MetaWeblogAPIHandler.editPost
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1552)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at org.hibernate.impl.CriteriaImpl.uniqueResult(CriteriaImpl.java:305)
at org.apache.roller.business.hibernate.HibernateWeblogManagerImpl.getWeblogCategoryByPath(HibernateWeblogManagerImpl.java:929)
On 11/22/06, agilliland@apache.org <ag...@apache.org> wrote:
> Author: agilliland
> Date: Wed Nov 22 10:33:34 2006
> New Revision: 478275
>
> URL: http://svn.apache.org/viewvc?view=rev&rev=478275
> Log:
> new path columns need to be of type TEXT because they could be fairly long in cases where people have a deep hierarchy.
>
>
> Modified:
> incubator/roller/trunk/metadata/database/310-to-320-migration.vm
> incubator/roller/trunk/metadata/database/createdb.vm
>
> Modified: incubator/roller/trunk/metadata/database/310-to-320-migration.vm
> URL: http://svn.apache.org/viewvc/incubator/roller/trunk/metadata/database/310-to-320-migration.vm?view=diff&rev=478275&r1=478274&r2=478275
> ==============================================================================
> --- incubator/roller/trunk/metadata/database/310-to-320-migration.vm (original)
> +++ incubator/roller/trunk/metadata/database/310-to-320-migration.vm Wed Nov 22 10:33:34 2006
> @@ -9,12 +9,12 @@
> create index ws_parentid_idx on weblogcategory( parentid );
>
> -- add new path column to weblogcategory table
> -#addColumnNull("weblogcategory" "path" "varchar(255)")
> -create index ws_path_idx on weblogcategory( path );
> +#addColumnNull("weblogcategory" "path" $TEXT_SQL_TYPE)
> +create index ws_path_idx on weblogcategory( path(255) );
>
> -- need to add this index for existing folder.parentid
> create index fo_parentid_idx on folder( parentid );
>
> -- add new path column to folder table
> -#addColumnNull("folder" "path" "varchar(255)")
> -create index fo_path_idx on folder( path );
> +#addColumnNull("folder" "path" $TEXT_SQL_TYPE)
> +create index fo_path_idx on folder( path(255) );
>
> Modified: incubator/roller/trunk/metadata/database/createdb.vm
> URL: http://svn.apache.org/viewvc/incubator/roller/trunk/metadata/database/createdb.vm?view=diff&rev=478275&r1=478274&r2=478275
> ==============================================================================
> --- incubator/roller/trunk/metadata/database/createdb.vm (original)
> +++ incubator/roller/trunk/metadata/database/createdb.vm Wed Nov 22 10:33:34 2006
> @@ -130,11 +130,11 @@
> description varchar(255),
> websiteid varchar(48) not null,
> parentid varchar(48),
> - path varchar(255)
> + path $TEXT_SQL_TYPE
> );
> create index fo_websiteid_idx on folder( websiteid );
> create index fo_parentid_idx on folder( parentid );
> -create index fo_path_idx on folder( path );
> +create index fo_path_idx on folder( path(255) );
>
> create table folderassoc (
> id varchar(48) not null primary key,
> @@ -166,12 +166,11 @@
> websiteid varchar(48) not null,
> image varchar(255),
> parentid varchar(48),
> - path varchar(255)
> + path $TEXT_SQL_TYPE
> );
> create index wc_websiteid_idx on weblogcategory( websiteid );
> create index ws_parentid_idx on weblogcategory( parentid );
> -create index ws_path_idx on weblogcategory( path );
> --- alter table weblogcategory add unique category_nameparentid_uq (parentid, name(20));
> +create index ws_path_idx on weblogcategory( path(255) );
>
> create table weblogcategoryassoc (
> id varchar(48) not null primary key,
>
>
>