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

[Nicolas Filotto]

Thomas, could you please a create an issue in the JCR project?

On Tue, May 11, 2010 at 10:44 AM, Nicolas Filotto <
nicolas.filotto at exoplatform.com> wrote:

>
>
> On Tue, May 11, 2010 at 10:43 AM, Thomas Heute <theute at redhat.com> wrote:
>
>>  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 ;)
>>
> What do you mean?
>
>>
>>
>>
>> On Tue, May 11, 2010 at 10:30 AM, Thomas Heute <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>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
>>>> > > https://lists.jboss.org/mailman/listinfo/gatein-dev
>>>> >
>>>> >
>>>> > _______________________________________________
>>>> > gatein-dev mailing list
>>>> > gatein-dev at lists.jboss.org
>>>> > https://lists.jboss.org/mailman/listinfo/gatein-dev
>>>>
>>>>
>>>> _______________________________________________
>>>> gatein-dev mailing list
>>>> 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
>>> +33 (0)6 31 32 92 19
>>>
>>>
>>>
>>
>>
>> --
>> Nicolas Filotto
>> JCR Product Manager
>> Project Manager
>> eXo Platform SAS
>> 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
> +33 (0)6 31 32 92 19
>



-- 
Nicolas Filotto
JCR Product Manager
Project Manager
eXo Platform SAS
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/a8fb728f/attachment-0001.html