[TYPO3-dam] "LIKE BINARY" in SQL-Queries forces errors on PostgreSQL

[Michiel Roos]

Andreas Wolf wrote:
>>> $where['file_path'] = 'tx_dam.file_path LIKE BINARY 
>>> '.$GLOBALS['TYPO3_DB']->fullQuoteStr($likeStr.'%', 'tx_dam');
> If I interpret the clause correctly, it casts the $likeStr to binary
> values, i.e. changes the representation to the character codes, thus
> making the comparison case-sensitive.
> 
>>> I wonder, if the "BINARY" operator is really mandatory to run DAM. What 
>>> do you think?
>>>
>>> I'd also really appreciate it, if someone could elaborate, why the 
>>> BINARY-Operator was implemented. Maybe we can find a DBAL-compliant 
>>> solution.
> I suppose it has been used to implement a case-sensitive comparison on
> MySQL < 4.1. At least that's what the comment by Paul Masri on [1] suggests.
> 
> I can't see a reason why this should be neccessary - but perhaps there
> are others with more experience in MySQL details who can shed some more
> light on this...

Hi Andreas,

It seems as if it is needed since paths may be similar in characters but 
different in case. This is not possible on mac but it is on Linux (maybe 
unix too?), I don't know about *BSD. It seems however that there is more 
than one system arount on which you can create the following dirs for 
example:
- tmp
- TMP
- Tmp
- . . . you get the idea . . .

So we do need the BINARY operator.

Clearly the dbal does not 'dig' this operator and makes postgres barf on 
  what it does produce.

I can run this by Karsten, our dbal king, he may be busy with 5.0 though.

If we want to have a dbal compliant dam and the BINARY was put in to do 
case sensitive comparison on MySQL < 4.1, then we can just 'pull' the 
BINARY from the dam code and put in MySQL > 4.1 in as a requirement for 
running dam.

Alas, the MySQL 5.0 manual states:
-----------------------------------------------------------------------
By default, MySQL searches are not case sensitive. This means that if 
you search with col_name LIKE 'a%', you get all column values that start 
with A or a. If you want to make this search case sensitive, make sure 
that one of the operands has a case sensitive or binary collation. For 
example, if you are comparing a column and a string that both have the 
latin1 character set, you can use the COLLATE operator to cause either 
operand to have the latin1_general_cs or latin1_bin collation. For example:

col_name COLLATE latin1_general_cs LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_general_cs
col_name COLLATE latin1_bin LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_bin

If you want a column always to be treated in case-sensitive fashion, 
declare it with a case sensitive or binary collation. See Section 
11.1.5, “CREATE TABLE Syntax”.
-----------------------------------------------------------------------

By default the tables will be created with the default collation or with 
the collation of the database. In most sane cases: utf8_general_ci.

We can make a small function that checks the collation of a table using:

SHOW CREATE TABLE `TBL_NAME`;

Then we can rewrite the BINARY statements to COLLATE statements and see 
how well that goes down through the dbal.

I have identified four files using BINARY in dam trunk:
- lib/class.tx_dam_selectionquery.php
- lib/class.tx_dam_db.php
- modfunc_tools_filerelcheck/class.tx_dam_tools_filerelcheck.php
- components/class.tx_dam_selectionFolder.php

I have a postgres install running.

Can you post some 'steps to reproduce an error'?

Thanks,


Michiel Roos



-- 
Netcreators BV :: creation and innovation
www.netcreators.com

Interesse in werken bij Netcreators?
http://www.netcreators.com/bedrijf/vacatures/