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

Thu May 13 02:58:53 EDT 2010

On Wed, May 12, 2010 at 8:37 PM, Matthew Wringe <mwringe at redhat.com> wrote:
> On Wed, 2010-05-12 at 18:08 +0300, Peter Nedonosko wrote:
>> On Wed, May 12, 2010 at 4:16 PM, Nicolas Filotto
>> <nicolas.filotto at exoplatform.com> wrote:
>> > I created the related issue on your
>> > behalf https://jira.jboss.org/jira/browse/EXOJCR-725
>>
>> Guys, we need your feedback on the issue and questions asked there.
>
> replying in this email since trying to have a discussion in jira is
> difficult due to lack of threading.
>
> - I believe mysql has been case insensitive by default for a while:
> doc for 4.1 http://dev.mysql.com/doc/refman/4.1/en/case-sensitivity.html
> and is that way with the current 5.5
> http://dev.mysql.com/doc/refman/5.5/en/case-sensitivity.html
> I don't know about mssql.

thx, can you add this comment to the issue?

>
> I believe the issue here is that JCR creates the tables for the
> database. We are assuming that the database will be configured to be
> case sensitive (at least for one column, JCR_SITEM NAME). Since the jcr
> creates the table, and can make it case sensitive, then it should
> probably be setup this way so that it works out of the box.

problem that case-sensitivity doesn't managed themselves, it's a part
of collation.
if we'll do hardcode it in the JCR dialect script an user will not be
free to choose an another via a database setup - only via table
altering, what can be problematic in general case.
What is better, thoughts?

For a national content we can tell use "mysql-utf8" dialect only. Then
we can lock to smth like "latin1_general_cs". But "mysql-utf8" has
trimmed index on NAME column to 255 chars, it can impacts long-named
items read performance (get by name from DB, not from JCR cache).
WDYT?

>
> I am a complete db noob, but if the admin creates the database and
> forgets to make the database case sensitive, can they easily go back and
> make it case sensitive? or is that something that has to happen at
> database creation?

I hope DBA should not to forget - it's his responsibility. But even
he'll do, it's always possible to use ALTER TABLE to try to fix it.

>
>> >
>> > On Tue, May 11, 2010 at 5:01 PM, Nicolas Filotto
>> > <nicolas.filotto at exoplatform.com> wrote:
>> >>
>> >> 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
>> >
>> >
>> >
>> > --
>> > Nicolas Filotto
>> > JCR Product Manager
>> > Project Manager
>> > eXo Platform SAS
>> > nicolas.filotto at exoplatform.com
>> > +33 (0)6 31 32 92 19
>> >
>> > _______________________________________________
>> > 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
>
>
>