[Typo3-dev] DBAL

[Karsten Dambekalns]

Hi,

On 2004-08-03, Martin T. Kutschker <Martin.no5pam.Kutschker at blackbox.n0spam.net> wrote:
> Yes. But additionaly you can mark CHAR/VARCHAR/TEXT as BINARY. So it's 
> case-insensitive as well. You can also force case-sensitivity in the query.
>
> "Standard text fields" are not case-insensitive in all RDBMS. At least 
> it used to be so in Oracle - years ago...

Well, PostgreSQL e.g. is case *sensitive*, unless one uses ILIKE for
example.

Example:
  test=# CREATE TABLE test (field TEXT);
  test=# INSERT INTO test VALUES('Bla');
  test=# INSERT INTO test VALUES('bla');
  test=# SELECT * FROM test WHERE field LIKE 'Bla';
   field
  -------
   Bla
  (1 row)

  test=# SELECT * FROM test WHERE field ILIKE 'Bla';
   field
  -------
   Bla
   bla
  (2 rows)

I'd have to check how Oracle behaves. MySQL handles this case
*insensitive*, as has already been said. You can change this, though:

  mysql> SELECT * FROM test WHERE field LIKE 'Bla';
  +-------+
  | field |
  +-------+
  | Bla   |
  | bla   |
  +-------+
  2 rows in set (0.00 sec)

  mysql> SELECT * FROM test WHERE field LIKE BINARY 'Bla';
  +-------+
  | field |
  +-------+
  | Bla   |
  +-------+
  1 row in set (0.00 sec)


What we can see here, is that switching to text fields instead of blob
fields will introduce the need to add the handling of the different
behaviours of case sensitivity to the abstraction layer. Whether this
is worth the hassle could only be decided by looking at how this might
speed up things - I could image that lookups on text field *might* be
faster than on blob fields (depends on how the DB handles indexing on
different field types).

Karsten