[gatein-dev] Portal and page names and case sensitivity

Tue May 11 04:43:04 EDT 2010

On 05/11/2010 10:38 AM, Nicolas Filotto wrote:
> According to this blog post 
> http://mysqldatabaseadministration.blogspot.com/2006/09/case-sensitive-mysql.html 
>
> for MySQL, we can specify a collation at database creation

It shows at *table* creation ;)

>
> On Tue, May 11, 2010 at 10:30 AM, Thomas Heute <theute at redhat.com 
> <mailto:theute at redhat.com>> wrote:
>
>     On 05/11/2010 10:25 AM, Nicolas Filotto wrote:
>>     Unfortunately, changing from  VARCHAR to  VARBINARY is just
>>     impossible at this stage since several versions of eXo JCR 1.12
>>     have already been released and those version would then be
>>     incompatible. Moreover, I'm pretty sure that it will create side
>>     effects more painful than case sensitivity issues.
>>
>>     The only way to solve this problem is to specify clearly in the
>>     doc that the database must be case insensitive.
>     Can this be set at DB level ? I thought it was at table level ?
>     And since tables are created by JCR it's a bit tricky.
>
>     We're going without any change, it's not as bad as I originally
>     thought it was, I'm still curious about the possibilities though.
>
>
>>
>>     On Mon, May 10, 2010 at 8:44 PM, Matthew Wringe
>>     <mwringe at redhat.com <mailto:mwringe at redhat.com>> wrote:
>>
>>         On Fri, 2010-05-07 at 19:16 -0400, Matthew Wringe wrote:
>>         > On Thu, 2010-05-06 at 22:49 +0200, Thomas Heute wrote:
>>         > >
>>         > > The great MySQL and MSSQL made that choice that tables
>>         (by default) are
>>         > > made case insensitive.
>>         > > Meaning that 'foobar' is the same as 'FooBar'.
>>         > > Today if you create a portal or a page 'toto' and then
>>         'tOtO' it will
>>         > > work for other databases but for MySQL or MSSQL it will fail.
>>         >
>>         > Page names that vary only in case work fine for me with
>>         MySQL, but I do
>>         > experience the problem with portal names.
>>         >
>>         > > We should prevent to have 2 portal names that are the
>>         same (case
>>         > > insensitive). We can't do a simple JCR query (AFAIK) to
>>         verify if 2
>>         > > portal names are the same (case insensitive), unless we
>>         try and catch an
>>         > > error. But the error would only happen for those 2 and
>>         would potentially
>>         > > make a migration from 1 DB to another difficult since
>>         they would react
>>         > > differently.
>>         >
>>         > The problem is that we have a field name which as part of
>>         it contains
>>         > the portal name, and MySQL is converting this into lower
>>         case. This
>>         > doesn't actually store the portal name, and its not used as
>>         a key (nor
>>         > is it set as unique).
>>         >
>>         > I created a quick hack in the jcr that would allow for it
>>         to get
>>         > multiple values back from the sql query and check them to
>>         return only
>>         > the correct result.
>>         >
>>         > This appears to work, and it gives the correct error
>>         messages about
>>         > portals existing or not. BUT, there is some unique index
>>         restraint setup
>>         > somewhere which is preventing me from saving the data back
>>         to the
>>         > database :(
>>         >
>>         > > The other option is to transform all ids to lowercase
>>         before storing in
>>         > > Database so we are safe with all database. I actually
>>         think it would be
>>         > > a good practice as those ids appear in the URL which are
>>         usually all in
>>         > > lowercase.
>>         >
>>         > The urls are not in lowercase though, if we want to access
>>         the 'classic'
>>         > or 'CLASSIC' portal, we can't have the url all in lowercase.
>>         >
>>         > > Or if anyone think about any other option ?
>>         >
>>         > Is there not some sort of case sensitive mode in MySQL that
>>         we can have
>>         > as a requirement for use with GateIn? The user already
>>         needs to setup
>>         > and configure the db anyways.
>>
>>         We can easily change this by making the table use VARBINARY
>>         for the
>>         field instead of VARCHAR. There is a jira for here
>>         https://jira.jboss.org/jira/browse/JBEPP-334 with a patch
>>         attached.
>>         Someone working on the JCR will need to make this change.
>>
>>         >
>>         > > Thomas.
>>         > > _______________________________________________
>>         > > gatein-dev mailing list
>>         > > gatein-dev at lists.jboss.org
>>         <mailto:gatein-dev at lists.jboss.org>
>>         > > https://lists.jboss.org/mailman/listinfo/gatein-dev
>>         >
>>         >
>>         > _______________________________________________
>>         > gatein-dev mailing list
>>         > gatein-dev at lists.jboss.org <mailto:gatein-dev at lists.jboss.org>
>>         > https://lists.jboss.org/mailman/listinfo/gatein-dev
>>
>>
>>         _______________________________________________
>>         gatein-dev mailing list
>>         gatein-dev at lists.jboss.org <mailto:gatein-dev at lists.jboss.org>
>>         https://lists.jboss.org/mailman/listinfo/gatein-dev
>>
>>
>>
>>
>>     -- 
>>     Nicolas Filotto
>>     JCR Product Manager
>>     Project Manager
>>     eXo Platform SAS
>>     nicolas.filotto at exoplatform.com
>>     <mailto:nicolas.filotto at exoplatform.com>
>>     +33 (0)6 31 32 92 19
>
>
>
>
> -- 
> Nicolas Filotto
> JCR Product Manager
> Project Manager
> eXo Platform SAS
> nicolas.filotto at exoplatform.com <mailto:nicolas.filotto at exoplatform.com>
> +33 (0)6 31 32 92 19

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/gatein-dev/attachments/20100511/28585c6a/attachment-0001.html