Codificación en BD (MySQL y Oracle)

Tanto en Oracle como en MySQL permiten establecer el conjunto de caracteres que pueden ser utilizados en los datos que se almacenan, esto es conocido como el CHARACTER SET o conjunto de caracteres permitidos. En el caso de que se intente introducir un carácter que no se encuentra en este conjunto, la BD devolvería un error indicando que el carácter no es permitido en la BD.

Este dato puede ser establecido a la hora de crear el esquema de la BD:

CREATE DATABASE <database_name>
USER SYS IDENTIFIED BY <password>
USER SYSTEM IDENTIFIED BY <password>
CHARACTER SET <character_set_name>
NATIONAL CHARACTER SET <character_set_name>
...

y

CREATE DATABASE <database_name>
CHARACTER SET charset_name,
COLLATE collation_name;

Lo ideal sería que nuestra BD soportara el mayor numero de caracteres, de esta forma evitamos una restricción desde la BD. Como solución podríamos establecer el CHARACTER SET = UTF-8, el cual permite la representación de todos los caracteres. En el caso de Oracle, no he detectado ningún problema, ya que tanto la comparación como la ordenación es correcta una vez que establecemos el CHARACTER SET = UTF-8. Los problemas surgieron a la hora de intentar lo mismo en MySQL.

Para facilitar las pruebas, las he realizado sobre una BD creada y lo único que he ido modificando es la codificación de la tabla que utilizaba para las pruebas.

Caso 1 (CHARACTER SET UTF-8)

Intentamos crear una tabla con el CHARACTER SET UTF-8.

create table test (cadena VARCHAR(10) CHARACTER SET binary);

En este caso se permite el conjunto de caracteres UTF-8, pero a la hora de comprobar la comparación entre cadenas detectamos que no es sensible a mayúsculas/minúsculas, además de no diferenciar entre caracteres diferentes como ‘á’ y ‘a’.

Caso 2 (CHARACTER SET binary)

Intentamos crear una tabla con el CHARACTER SET binary. En este caso al utilizar binary, sería parecido a la utilización de UTF-8 ya que se introduciría la codificación binaria del carácter.

create table test (cadena VARCHAR(10) CHARACTER SET binary);

En este caso la comparación era tanto sensible a mayúsculas/minúsculas y además se diferenciaba correctamente los caracteres diferentes (á != a). Pero en este caso se producían dos problemas:

• La información se almacenaba en binario, es decir, era la representación binaria correspondiente a los caracteres.  Ej: ef  bf  bd  41  ef  bf  bd  49  69.
• Debido a que la ordenación es mediante la representación binaria de los caracteres, esta ordenación era incorrecta y se podía producir el siguiente caso :  á > Á > b > a > B > A

Caso 3 (CHARACTER SET UTF-8 collate utf8_bin)

En este caso se permite el conjunto de caracteres UTF-8 en la representación y además se indica un collation utf8_bin, el cual provocará una comparación binaria a la hora de comparar los valores y también en la ordenación.

create table test (cadena VARCHAR(10) CHARACTER SET utf8 collate utf8_bin);

En este caso es sensible a mayúsculas/minúsculas, diferenciaba correctamente los caracteres diferentes (á != a) y la representación de los caracteres era correcta. En este caso el único problema era que la comparación era binaria y por ello la ordenación no era correcta.

Caso 4 (CHARACTER SET latin1 collate latin1_general_cs)

Por último, viendo que estableciendo CHARACTER SET=UTF8 no podíamos conseguir un comportamiento parecido al conseguido en Oracle, decidimos usar un conjunto de caracteres latin1, el cual contiene los caracteres utilizados en los países de Europa del Oeste, también añadimos el collation latin1_general_cs para permitir la sensibilidad a mayúscula/minúscula.

create table test (cadena VARCHAR(10) CHARACTER SET latin1 collate latin1_general_cs);

En este caso conseguimos un comportamiento igual que el obtenido en Oracle, pero con la diferencia que en este caso sólo permitiremos en nuestra BD aquellos caracteres de la codificación latin1 (http://www.collation-charts.org/mysql60/mysql604.latin1_general_cs.html)

Estas son las sentencias utilizadas a la hora de realizar las comparaciones:

drop table if exists test;

--- Aquí va el create table correspondiente

--- para realizar las comparaciones

INSERT INTO test VALUES ('ábecedario');

INSERT INTO test VALUES ('becedario');

INSERT INTO test VALUES ('abecedario');

INSERT INTO test VALUES ('nodo');

INSERT INTO test VALUES ('ñ');

INSERT INTO test VALUES ('oca');

SELECT * FROM test where cadena like 'á%';

SELECT * FROM test where cadena like 'Á%';

SELECT * FROM test where cadena like 'a%';

SELECT * FROM test where cadena like 'ñ';

SELECT * FROM test order by cadena;

Personalizar esquema con Hibernate

Las anotaciones JPA junto con el plugin hbm2ddl de Hibernate, proporcionan a los desarrolladores una fácil y rápida de generar el esquema de base de datos a partir del dominio. Muchas veces debido a los restricciones de un cliente es necesario seguir unas normas para la generación de los nombres. A continuación explicaremos un conjunto de herramientas y anotaciones de las que nos podemos valer para la generación de un esquema que siga las diferentes normas para la generación del esquema.

Borrar todas las tablas en Oracle

En oracle no existe ningún comando (parecido a drop all tables) que permita el borrado de todas las tablas de la BD.

Durante la fase de diseño o en los comienzos de la fase de desarrollo se produce asiduamente la creación y eliminación de las BD debido a numerosos cambios en el diseño del dominio que finalmente va a ser utilizado. Por ello habría que buscar algo parecido a drop all tables, el cual permitiera la eliminación de todas las tablas del esquema.

A continuación propongo las siguientes alternativas:

1.- Eliminación del esquema de BD.

En el caso de Oracle un esquema de BD tiene que estar asociado a un usuario de BD, por lo que esto puede ser un poco duro de ejecutar. En algunos casos los desarrolladores no tienen permisos para la creación o borrado de usuarios, y puede ser un poco molesto estar todos los días molestando al DBA para que nos elimine o cree el usuario.

DROP USER user_name CASCADE;

Una vez eliminado el usuario tenemos que volverlo a crear el mismo usuario, por ello deberemos tener la información relativa al DBA de la Base de datos.

2.- Eliminación mediante la modificación del script de generación de la Base de Datos.

Hoy en día para la creación de las BD nos ayudamos de diferentes aplicaciones/frameworks que nos permiten la generación de la BD a partir del diseño del dominio. Como ejemplo podemos poner el hibernate tools, el cual crea de forma automática el esquema de BD a partir del dominio anotado o a partir de los hbm. Este mismo framework nos permite generar el código SQL que crea/elimina las tablas de la BD.

La ejecución del script de eliminación de tablas generado por esta herramienta y ejecutarlo tiene algunos inconvenientes:

• Si se han producido modificaciones de forma externa al hibernate tools, no funciona correctamente
• Sólo eliminaría las tablas que han sido creadas con este plugin por lo que el resto de tablas habría que eliminarlas de forma manual
• No permite la eliminación de sinónimos, vistas o funciones.
• Dependemos de terceros.

3.- Eliminación mediante un script general.

Una alternativa a las soluciones anteriormente propuestas es la ejecución de una sentencia SELECT que nos proporcione las sentencias que hemos de ejecutar para la eliminación de las tablas relativas al usuario actual.

SELECT 'DROP TABLE '||table_name||' CASCADE CONSTRAINTS;' FROM user_tables

Como consecuencia de la ejecución de este select se devolverá todas las sentencias que hemos de ejecutar para eliminar las tablas del esquema actual.

Esta sentencia la podemos hacer más compleja para permitir la eliminación de otros elementos de la Base de Datos:

SELECT 'DROP TABLE '||table_name||' CASCADE CONSTRAINTS;' FROM user_tables UNION
SELECT 'DROP VIEW '||VIEW_NAME||';' FROM user_views UNION
SELECT 'DROP SEQUENCE '|| SEQUENCE_NAME||';' FROM user_sequences UNION
SELECT 'DROP SYNONYM ' || SYNONYM_NAME ||';' FROM user_synonyms UNION
SELECT 'DROP FUNCTION ' || OBJECT_NAME ||';' FROM user_procedures;

Hay que tener en cuenta que a la hora de eliminar un objeto en las versiones de oracle posteriores a la 9 se generan un un registro como copia de seguridad para la posible vuelta atrás. Por ello añadimos finalmente la sentencia ‘PURGE RECYCLEBIN;’ quedando el siguiente script:

SELECT * FROM (
SELECT 'DROP TABLE '||table_name||' CASCADE CONSTRAINTS;' FROM user_tables UNION
SELECT 'DROP VIEW '||VIEW_NAME||';' FROM user_views UNION
SELECT 'DROP SEQUENCE '|| SEQUENCE_NAME||';' FROM user_sequences UNION
SELECT 'DROP SYNONYM ' || SYNONYM_NAME ||';' FROM user_synonyms UNION
SELECT 'DROP FUNCTION ' || OBJECT_NAME ||';' FROM user_procedures UNION
SELECT 'PURGE RECYCLEBIN;' FROM dual)
ORDER BY 1 ASC;

Una vez ejecutada la sentencia, copiamos el resultado y lo ejecutamos. Como resultados obtendremos el esquema totalmente limpio.

Para evitar tener que copiar esta sentencia en un fichero y copiarlo cada vez que queramos eliminar el esquema de BD, nos podemos ayudar del cliente que utilicemos para acceder a la BD. En mi caso suelo utilizar SQL SQUIRREL y este me permite asociar una abreviación a un script, así que sólo tengo que asociar la sentencia DROPALL a la consulta anteriormente indicada.

Ejemplo:

DROP SEQUENCE HIBERNATE_SEQUENCE;
DROP SYNONYM PERSONA;
DROP SYNONYM CIUDADES;
DROP TABLE TABLA1 CASCADE CONSTRAINTS;
DROP TABLE TABLA2 CASCADE CONSTRAINTS;