Versioner sa base avec LiquiBase

LiquiBase est un sytème de gestion de version permettant de gérer les changements à appliquer à une base de données. Il est écrit en Java et fonctionne avec des SGBDs hétérogènes tels qu'Oracle, MySQL et PostgreSQL.
Ce système propose un grand nombre de fonctions pour effectuer certains tâches comme supprimer une table, ajouter un index, une colonne, une contrainte ... Il est possible d'ajouter des tests et traitements en fonction des résultats obtenus, d'inclure des fichiers externes, d'ajouter des actions qui seront faites à chaque exécution etc...
Voici un exemple de fichier de changement qu'il est possible d'utiliser :

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">

<changeSet id="1" author="steve" runAlways="true">

<preConditions onFail="HALT"

onFailMessage="You're trying to apply changes on database qal or prd !!">

<sqlCheck expectedResult="1"> SELECT substring(database(),11) NOT IN ('qal','prd')  </sqlCheck>

</preConditions>

</changeSet>

<changeSet id="2" author="cyril">

<comment>Whole bd1_data_tst database regenerated </comment>

<sql>SET sql_mode=''</sql>

<sqlFile path="schema/bd1-schema.tmp-fix-drop-tables.sql" />

<sqlFile path="schema/bd1_struct_v41.sql" />

<sqlFile path="schema/bd1_data_v41.sql" />

<sql>SELECT sleep(1)</sql>

<sqlFile path="schema/bd1-schema.v41-v42.sql" />

<sql>SELECT sleep(1)</sql>

<sqlFile path="schema/bd1-schema.v42-v43.sql" />

<sql>SELECT sleep(1)</sql>

<sqlFile path="schema/bd1-schema.v43-v44.sql" />

<sqlFile path="routinesTriggersEvents/pre-util.sql" />

<sqlFile path="routinesTriggersEvents/util-debug.sql"

splitStatements="false" />

<sqlFile path="routinesTriggersEvents/pre-companySearch.sql" />

<sqlFile path="routinesTriggersEvents/companyDictionarySearchWithSoundexInTempTable.sql"

splitStatements="false" />

<sqlFile path="routinesTriggersEvents/companyDictionarySoundexSearchWithWhereClause.sql"

splitStatements="false" />

<sqlFile path="routinesTriggersEvents/validatedCompanyDictionarySearchWithSoundex.sql"

splitStatements="false" />

<sqlFile path="routinesTriggersEvents/pre-triggers.sql" />

<sql>

DROP procedure IF EXISTS clean_all_tables_from_current_db;

</sql>

<sqlFile path="routinesTriggersEvents/clean-test-tables.sql"

splitStatements="false" />

<sql>

TRUNCATE tx10;

</sql>

</changeSet>

<include file="./bd1-changelog.xml"

relativeToChangelogFile="true" />

</databaseChangeLog>

Dans ce fichier de changement, on peut voir qu'il y a 2 changements à appliquer (changeSet id="X") dont un qui sera appliqué à chaque fois (runAlways="true") , et que le premier changement effectue un test qui sera fatal à l'application des changements suivants s'il échoue (onFail="HALT").
On remarque aussi que chaque changement est associé à un utilisateur bien défini (author="cyril"). En plus des deux changements présents dans le fichier, les changements inclus dans le fichier ./bd1-changeog.xml seront aussi appliqués, ce qui permet par exemple d'observer un certain découpage et de ne pas avoir un fichier de changement qui pèse plusieurs Mo.


Concernant l'utilisation, il existe un plugin Maven afin de pouvoir piloter le système. Il est aussi possible d'écrire une petite application en Java pour appliquer un fichier de changement ou utiliser une commande en ligne si vous ne disposez pas de Maven.

Paralléliser une commande shell

  Je conseille fortement l'utilisation ou au moins de jeter un coup d'oeil sur un outil très intéressant "made by GNU“ qui se nomme Parallel.
En effet, il permet après avoir rapidement parcouru la documentation en ligne de paralléliser l'exécution d'une commande Shell même si elle est un peu complexe.
Une petite démonstration vous permettra de rapidement vous en rendre compte :)

Voici donc la commande shell que je désirais paralléliser :

for id in $(seq 1 4) do   for cf in t1 x34 j25 r32 r77   do     cat /data/d${id}/res/${cf}.csv | encb64 > /data/d${id}/res/${cf}-bs64.csv   done done

A priori ça semble un peu compliqué mais en fait Parallel supporte l'initialisation de listes pour justement mimer les boucles for. Ainsi la commande devient la suivante :

parallel cat /data/pns{1}/results/{2}.csv '|' encb64 '>' /data/pns{1}/results/{2}-bs64.csv ::: 1 2 3 4 ::: t1 x34 j25 r32 r77

Vous noterez qu'il est nécessaire de mettre entre quotes les caractères pouvant être interprétés par le shell comme dans mon exemple le caractère pipe.

Par défaut Parallel lancera autant de processus que de coeurs disponibles sur la machine. il est aussi possible de paralléliser des tâches sur plusieurs machines.

Concernant l'installation de l'outil, vous pouvez la faire très simplement avec le Homebrew de Mac ou en téléchargeant le paquet disponible pour votre distribution. Par exemple, pour ubuntu 10.04 vous pouvez le récupérer ici.

Doit-on utiliser le singulier ou le pluriel pour nommer ses tables ?

C'est une question qui revient souvent quand on décide de créer une nouvelle base de données. En effet, on peut avoir oublié la raison pour laquelle on a fait tel choix lors du design d'une ancienne base.Il se peut par exemple que vous utilisiez un ORM et que celui-ci ne supporte par exemple que le singulier sur le nom des tables. Mais dans le cas, où vous vous interrogez simplement sur la bonne marche à suivre je ne connais pas de règle ISO sur le sujet . Sachez cependant qu'Oracle utilise par exemple le pluriel pour les noms des tables et le singulier pour les noms des colonnes. MySQL en fait tout autant dans sa base de données information_schema. Cependant, j'ai trouvé une réponse assez argumentée que j'ai voulu partager.

Pour en savoir plus, allez lire la réponse sur stackoverflow. N'oubliez quand même pas qu'au final vous avez toujours le choix, mais mieux vaut considérer les avantages/inconvénients que peuvent vous apportez d'utiliser ou non le pluriel/singulier et je rajouterais aussi que peuvent vous apporter l'utilisations du CamelCase (passage de Windows à Unix par ex), des minuscules ou majuscules.

Indexation et booléens

Voilà encore une raison pour laquelle il faut toujours tester vos requêtes!
En effet, il s'avère que l'optimiseur de MySQL 5.5 (vérifié aussi en 5.1.60) ne fait pas le même choix selon qu'un filtre sur la valeur d'un booléen indexé utilise l'opérateur = ou IS. Voici un exemple extrait du rapport de Bug que j'ai posté sur bugs.mysql.com :

mysql> CREATE TABLE t(id INT, b BOOLEAN DEFAULT FALSE);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t(id) SELECT 1;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 32 rows affected (0.00 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 64 rows affected (0.00 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t UNION ALL SELECT id FROM t UNION ALL SELECT id FROM t;
Query OK, 387 rows affected (0.00 sec)
Records: 387 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t UNION ALL SELECT id FROM t UNION ALL SELECT id FROM t;
Query OK, 1548 rows affected (0.02 sec)
Records: 1548 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t UNION ALL SELECT id FROM t UNION ALL SELECT id FROM t;
Query OK, 6192 rows affected (0.03 sec)
Records: 6192 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t UNION ALL SELECT id FROM t UNION ALL SELECT id FROM t;
Query OK, 24768 rows affected (0.18 sec)
Records: 24768 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t UNION ALL SELECT id FROM t UNION ALL SELECT id FROM t;
Query OK, 99072 rows affected (0.43 sec)
Records: 99072 Duplicates: 0 Warnings: 0
mysql> alter table t add index(b);
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t values(10,TRUE);
Query OK, 1 row affected (0.00 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM t WHERE b IS TRUE\G
******** 1. row ********
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: b
key_len: 2
ref: NULL
rows: 131783
Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM t WHERE b = TRUE\G
******** 1. row ********
id: 1
select_type: SIMPLE
table: t
type: ref
possible_keys: b
key: b
key_len: 2
ref: const
rows: 1
Extra: Using where; Using index
1 row in set (0.00 sec)

Comme vous pouvez le voir, lorsque l'on utilise l'opérateur "=" , MySQL effectue un accès unique dans l'index b, alors qu'avec l'opérateur "IS" il effectue un parcours total de l'index... Il est donc très important pour l'instant de privilégier l'opérateur d'égalité en attendant la correction du bug.

Sauvegarder physiquement certaines tables InnoDB

Avec les tables MyISAM, il est possible de sauvegarder directement les fichiers MYD et MYI lorsque le serveur MySQL tourne, en supposant bien sûr que vous avez verrouillé les tables concernées avec la commande LOCK TABLES ou qu'il n'y a aucune requête en cours. Cependant, il n'en est pas de même avec le moteur InnoDB. En effet, à la différence de MyISAM, il y a des threads qui continue à s'exécuter après que les modifications aient été faites et qui modifient les fichiers de données. Seules les journaux de transactions (iblogfiles) sont réellement écrits à chaque fin de transaction (je considère que vous ne jouez pas avec le paramètre innodb_flush_log_at_trx_commit). De plus, il y a un fichier spécial, nommé par défaut ibdata1, qui contient le dictionnaire de données et toutes les tables (et index) InnoDB ayant été créées avec le paramètre innodb_file_per_table désactivé).
C'est la raison pour laquelle il n'est pas possible de copier un fichier innodb d'un serveur à un autre. Il est par contre tout a fait possible de sauvegarder un fichier innodb et de le restaurer plus tard sur le même serveur ou sur un serveur hébergeant une sauvegarde physique, sans avoir à restaurer la base dans son ensemble.
Il faut savoir qu'InnoDB associe un identifiant à chaque fichier innoDB nommé tablespace (oui cela n'a rien à voir avec les tablespaces d'Oracle). Cet id est stocké dans le catalogue InnoDB ainsi que dans le fichier concerné. C'est ce qui empêche de restaurer le fichier sur un autre serveur sur lequel le même tablespace n'existe pas ou a un identifiant différent, ou sur le même serveur sur lequel on aurait fait un TRUNCATE de la table, et donc généré implicitement un nouvel identifiant !

La méthode à suivre est la suivante, détaillée dans la document MySQL :

mysql> USE test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> CREATE TABLE t1(id int);
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT @i:=0;
+-------+
| @id:=0 |
+-------+
| 0 |
+-------+
1 row in set (0.00 sec
mysql> INSERT INTO t1 SELECT @id:=@id+1 FROM information_schema.TABLES;
Query OK, 198 rows affected (0.03 sec)
Records: 198 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT @id:=@id+1 FROM information_schema.TABLES;
Query OK, 198 rows affected (0.00 sec)
Records: 198 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT @id:=@id+1 FROM information_schema.TABLES;
Query OK, 198 rows affected (0.00 sec)
Records: 198 Duplicates: 0 Warnings: 0
mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 594 |
+----------+
1 row in set (0.00 sec)
mysql> SHOW ENGINE INNODB STATUS\G ... Main thread process no. 5109, id 140547993949952, state: waiting for server activity
Number of rows inserted 594, updated 0, deleted 0, read 1193
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 12.91 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)

Une fois que le statut du moteur InnoDB est bien à "waiting for server activity", On sauvegarde le fichier InnoDB

cp -p /var/lib/mysql/test/t1.ibd /tmp/

La sauvegarde effectuée, on simule la perte de données

mysql> DELETE FROM t1 WHERE id>400;
Query OK, 194 rows affected (0.04 sec)
mysql> SELECT MAX(id) FROM t1;
+---------+
| MAX(id) |
+---------+
| 400 |
+---------+
1 row in set (0.00 sec)

- On tente maintenant de restaurer nos données en prévenant MySQL

mysql> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)

- On restaure le fichier à sa place d'origine

root@lizzie:~# cp -p /tmp/t1.ibd /var/lib/mysql/test/t1.ibd

- On avertit MySQL qu'il peut utiliser à présent le fichier

mysql> ALTER TABLE t1 IMPORT TABLESPACE;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT MAX(id) FROM t1;
+---------+
| MAX(id) |
+---------+
| 594 |
+---------+
1 row in set (0.02 sec)

Et voilà !
Par contre, comme je vous l'ai dit ça ne fonctionne plus quand l'id du tablespace a été modifié dans le catalogue :

mysql> TRUNCATE TABLE t1;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)
root@lizzie:~# cp -p /tmp/t1.ibd /var/lib/mysql/test/t1.ibd
mysql> ALTER TABLE t1 IMPORT TABLESPACE;
ERROR 1030 (HY000): Got error -1 from storage engine

Vous trouverez un message plus explicite dans le fichier d'erreur :
grep -A 10 "InnoDB: Error" /var/lib/mysql/lizzie.err
111012 18:09:31 InnoDB: Error: tablespace id and flags in file './test/t1.ibd' are 209768 and 0, but in the InnoDB
InnoDB: data dictionary they are 209769 and 0.
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
111012 18:09:31 InnoDB: cannot find or open in the database directory the .ibd file of
InnoDB: table `test`.`t1`
InnoDB: in ALTER TABLE ... IMPORT TABLESPACE