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