Re: svn commit: r478275 - in /incubator/roller/trunk/metadata/database: 310-to-320-migration.vm createdb.vm

Sun, 26 Nov 2006 13:58:18 -0800

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, [EMAIL PROTECTED] <[EMAIL PROTECTED]> 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,

























					Reply via email to