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

[Thomas Heute]

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

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