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.

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 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.

Or if anyone think about any other option ?

Thomas. _______________________________________________ gatein-dev mailing list gatein-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/gatein-dev

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

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

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 ;)

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

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(a)exoplatform.com&gt; wrote: > > > 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 > -- Nicolas Filotto JCR Product Manager Project Manager eXo Platform SAS nicolas.filotto(a)exoplatform.com +33 (0)6 31 32 92 19

On Wed, May 12, 2010 at 4:16 PM, Nicolas Filotto <nicolas.filotto(a)exoplatform.com&gt; 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.

> > On Tue, May 11, 2010 at 5:01 PM, Nicolas Filotto > <nicolas.filotto(a)exoplatform.com&gt; 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(a)exoplatform.com&gt; wrote: >>> >>> >>> 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 >> >> >> >> -- >> 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 > > _______________________________________________ > 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

I created the related issue on your behalf https://jira.jboss.org/jira/browse/EXOJCR-725 On Tue, May 11, 2010 at 5:01 PM, Nicolas Filotto <nicolas.filotto(a)exoplatform.com <mailto:nicolas.filotto@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(a)exoplatform.com <mailto:nicolas.filotto@exoplatform.com>> wrote: On Tue, May 11, 2010 at 10:43 AM, Thomas Heute <theute(a)redhat.com <mailto:theute@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-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 <mailto:theute@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(a)redhat.com <mailto:mwringe@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(a)lists.jboss.org >> <mailto:gatein-dev@lists.jboss.org> >> > > >> https://lists.jboss.org/mailman/listinfo/gatein-dev >> > >> > >> > _______________________________________________ >> > gatein-dev mailing list >> > gatein-dev(a)lists.jboss.org >> <mailto:gatein-dev@lists.jboss.org> >> > >> https://lists.jboss.org/mailman/listinfo/gatein-dev >> >> >> _______________________________________________ >> gatein-dev mailing list >> gatein-dev(a)lists.jboss.org >> <mailto:gatein-dev@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 >> <mailto:nicolas.filotto@exoplatform.com> >> +33 (0)6 31 32 92 19 > > > > > -- > Nicolas Filotto > JCR Product Manager > Project Manager > eXo Platform SAS > nicolas.filotto(a)exoplatform.com > <mailto:nicolas.filotto@exoplatform.com> > +33 (0)6 31 32 92 19 -- Nicolas Filotto JCR Product Manager Project Manager eXo Platform SAS nicolas.filotto(a)exoplatform.com <mailto:nicolas.filotto@exoplatform.com> +33 (0)6 31 32 92 19 -- Nicolas Filotto JCR Product Manager Project Manager eXo Platform SAS nicolas.filotto(a)exoplatform.com <mailto:nicolas.filotto@exoplatform.com> +33 (0)6 31 32 92 19 -- Nicolas Filotto JCR Product Manager Project Manager eXo Platform SAS nicolas.filotto(a)exoplatform.com <mailto:nicolas.filotto@exoplatform.com> +33 (0)6 31 32 92 19