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

On Tue, May 11, 2010 at 10:43 AM, Thomas Heute <theute(a)redhat.com&gt; wrote: > On 05/11/2010 10:38 AM, Nicolas Filotto wrote: > > According to this blog post > http://mysqldatabaseadministration.blogspot.com/2006/09/case-sensitive-my... > 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(a)redhat.com&gt; 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(a)redhat.com&gt;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(a)lists.jboss.org >>> > > https://lists.jboss.org/mailman/listinfo/gatein-dev >>> > >>> > >>> > _______________________________________________ >>> > gatein-dev mailing list >>> > gatein-dev(a)lists.jboss.org >>> > https://lists.jboss.org/mailman/listinfo/gatein-dev >>> >>> >>> _______________________________________________ >>> gatein-dev mailing list >>> gatein-dev(a)lists.jboss.org >>> https://lists.jboss.org/mailman/listinfo/gatein-dev >>> >> >> >> >> -- >> Nicolas Filotto >> JCR Product Manager >> Project Manager >> eXo Platform SAS >> nicolas.filotto(a)exoplatform.com >> +33 (0)6 31 32 92 19 >> >> >> > > > -- > Nicolas Filotto > JCR Product Manager > Project Manager > eXo Platform SAS > nicolas.filotto(a)exoplatform.com > +33 (0)6 31 32 92 19 > > > -- Nicolas Filotto JCR Product Manager Project Manager eXo Platform SAS nicolas.filotto(a)exoplatform.com +33 (0)6 31 32 92 19