CASTing

This article is written in English and Portuguese
Este artigo está escrito em Inglês e Português

English version:

I recently exchange some information with a person who is deeply involved with Eclipse and Hybernate. This person complains (not necessarily without reason) that these tools support for Informix is poor (or that Informix doesn't support some things used by these frameworks that apparently are supported by other databases). In these cases, sometimes patches are built for the tools, or some improvements are made on the engine. But there is yet another option that may work sometimes.... The last interaction refers to the way we allow BOOLEAN literals to be written. By default we use "t' for TRUE and 'f' for FALSE. Other systems apparently support 1 and 0 for example.
So, what's the third alternative? Simply use Informix extensibility to fulfill some functionality that is missing. Let's see an example:

DROP TABLE IF EXISTS test_boolean;
CREATE TABLE test_boolean
(
        col1 BOOLEAN
);
INSERT INTO test_boolean VALUES (1);

If we run this on a v11.70.xC7 version we get:

9632: Value does not match the type of column (col1).

This happens because the INSERT expects a BOOLEAN and we're providing an INTEGER. But similar situations (data type conversion) sometimes happen without error. Why? Because the engine knows how to convert between those types. And we can "teach it" more casts. Let's change the test case a bit:

DROP TABLE IF EXISTS test_boolean;
DROP FUNCTION IF EXISTS cast_int_to_boolean;
DROP CAST IF EXISTS (INT AS BOOLEAN);

CREATE TABLE test_boolean
(
        col1 BOOLEAN
);

CREATE FUNCTION cast_int_to_boolean (i INT) RETURNING BOOLEAN;
IF i = 1 THEN
        RETURN 't';
ELSE
        RETURN 'f';
END IF;
END FUNCTION;

CREATE IMPLICIT CAST (INT AS BOOLEAN WITH cast_int_to_boolean);
INSERT INTO test_boolean VALUES (1);
INSERT INTO test_boolean VALUES (0);
SELECT * FROM test_boolean

Now, this returns

col1

   t
   f

Why? Because now the engine knows how to convert between the INTEGER we're providing and the BOOLEAN that it requires.
This also works when we run:

SELECT * FROM test_boolean WHERE col1 = 1;

The result is:

col1

   t

This happens because the engine tries to cast the integer we provide (1) to the column type. If it tried to do the opposite it would fail because it still doesn't know how to convert a BOOLEAN to INT.
This can be seen if we run:

SELECT col1::INT FROM test_boolean WHERE col1 = 1;

and we get:

9634: No cast from boolean to integer.

Naturally we can overcome this if we create a new cast (BOOLEAN to INT) like this:

CREATE FUNCTION cast_boolean_to_int (i BOOLEAN) RETURNING INT;
IF i  THEN
        RETURN 1;
ELSE
        RETURN 0;
END IF;
END FUNCTION;
CREATE IMPLICIT CAST (BOOLEAN AS INT WITH cast_boolean_to_int);

And then we get:

col1

   1

So, yet another example about how to take advantage of Informix extensibility to overcome some annoyances. In any case, just a few warnings:

• I used functions written in SPL. As I've shown before, this may not be the ideal in terms of performance. A C UDR would be better from that point of view
• The CAST will only allow the engine to understand other ways to represent the BOOLEAN values. It will not change the default representation when you do a SELECT. This may still confuse the tools (?)
• It would be better if the tools recognize the database particularities. This method would force the user (DBA) to create and possibly maintain functions in every database used by the tools
• For simplicity I assumed 1 as TRUE and anything else as FALSE. It would be better to accept only 1 and 0 and raise an exception on anything else

Some references:

• The eclipse bug:
  https://bugs.eclipse.org/bugs/show_bug.cgi?id=401746
• How to create a C UDR function:
  http://informix-technology.blogspot.pt/2010/11/udrs-in-transaction-em-transaccao.html

Versão Portuguesa:

Recentemente troquei impressões com alguém que está bastante envolvido com Eclipse e Hybernate. A pessoa em causa queixa-se (não necessariamente sem razão), que o suporte ao Informix destas ferramentas é pobre (ou que o Informix não suporta algumas coisas que estas frameworks usam e que são suportadas por outras bases de dados). Nestes casos, algumas vezes são criadas correções nas ferramentas ou alguns melhoramentos são feitos no motor. Mas existe ainda outra opção que pode funcionar em algumas situações... Na última interação discutimos a forma como aceitamos que expressões BOOLEAN sejam representadas. De base o Informix usa o "t" para verdadeiro (TRUE) e "f" para falso (FALSE). Aparentemente outros sistemas aceitam o 1 e o 0 por exemplo.
Portanto, qual é a terceira alternativa? Simplesmente usar a extensibilidade do Informix para preencher algum vazio de funcionalidade. Vejamos um exemplo:

DROP TABLE IF EXISTS test_boolean;
CREATE TABLE test_boolean
(
        col1 BOOLEAN
);
INSERT INTO test_boolean VALUES (1);

Se executarmos isto numa instância na versão 11.70.xC7 obtemos:

9632: Value does not match the type of column (col1).

Isto acontece porque o INSERT espera um BOOLEAN e estamos a fornecer um INTEGER. Mas situações semelhantes (conversão de dados) podem acontecer sem erros. Porquê? Porque o motor sabe como converter entre esses tipos de dados. E nós podemos "ensinar-lhe" mais CASTs. Vamos modificar o caso de teste um pouco:

DROP TABLE IF EXISTS test_boolean;
DROP FUNCTION IF EXISTS cast_int_to_boolean;
DROP CAST IF EXISTS (INT AS BOOLEAN);

CREATE TABLE test_boolean
(
        col1 BOOLEAN
);

CREATE FUNCTION cast_int_to_boolean (i INT) RETURNING BOOLEAN;
IF i = 1 THEN
        RETURN 't';
ELSE
        RETURN 'f';
END IF;
END FUNCTION;

CREATE IMPLICIT CAST (INT AS BOOLEAN WITH cast_int_to_boolean);
INSERT INTO test_boolean VALUES (1);
INSERT INTO test_boolean VALUES (0);
SELECT * FROM test_boolean

Agora, isto retorna
col1

   t
   f

Porquê? Porque agora o motor sabe como converter entre o INTEGER que estamos a fornecer e o BOOLEAN que está à espera.
Isto também funciona quando corremos:

SELECT * FROM test_boolean WHERE col1 = 1;

e obtemos:

col1

   t

Isto acontece porque o motor tenta converter o INTEGER que fornecemos (1) para o tipo de dados da coluna. Se tentasse fazer o oposto, falharia porque ainda não sabe como converter um BOOLEAN to INTEGER
Isto pode ser verificado se executar-mos:

SELECT col1::INT FROM test_boolean WHERE col1 = 1;

e obtemos

 9634: No cast from boolean to integer.

Naturalmente podemos ultrapassar isto de criar-mos um novo CAST (BOOLEAN para INTEGER) desta forma:

CREATE FUNCTION cast_boolean_to_int (i BOOLEAN) RETURNING INT;
IF i  THEN
        RETURN 1;
ELSE
        RETURN 0;
END IF;
END FUNCTION;
CREATE IMPLICIT CAST (BOOLEAN AS INT WITH cast_boolean_to_int);

E obtemos:

col1

   1

Temos assim mais um exemplo das vantagens da extensibilidade do Informix, e de como a podemos utilizar para ultrapassar algumas inconveniências. Em todo o caso, alguns avisos:

• Usei funções escritas em SPL. Conforme eu já mostrei antes, isto pode não ser o ideal em termos de performance. Um UDR em C seria melhor desse ponto de vista
• O CAST apenas permite ao motor entender outras representações para valores BOOLEAN. Não muda a forma como o motor representa esses valores por omissão, quando se faz um SELECT . Isto poderá confundir as ferramentas (?)
• Seria melhor que a ferramenta reconhecesse as particularidades de cada base de dados. Este método forçaria o utilizador (DBA) a criar e possivelmente manter funções em cada base de dados onde deseje utilizar as ferramentas
• Por simplicidade assumi o 1 como verdadeiro e qualquer outra coisa como falso. O mais correto seria assumir 1 como verdadeiro, 0 como falso e causar uma excepção para qualquer outro valor

Algumas referências:

• O bug no Eclipse:
  https://bugs.eclipse.org/bugs/show_bug.cgi?id=401746
• Como criar um função UDR em C:
  http://informix-technology.blogspot.pt/2010/11/udrs-in-transaction-em-transaccao.html