Archives de catégorie : SGBD

Android upgrade sqlite

Une action qui peux se révéler délicate dans une application android c’est la mise à jour de la base de données sqlite lors de la publication d’une nouvelle version de votre app.
Le but étant d’arriver à modifier votre schema sans pour autant perdre les données que vous utilisateurs pourraient avoir enregistré.

Le problème qui se pose , c’est que sqlite à une fonction ALTER TABLE très limitée.
Vous pouvez renommer une table :

ALTER TABLE table1 RENAME TO table2

ou ajouter une colonne à vote table :

ALTER TABLE table1 ADD COLUMN newcolonne

Mais si vous avez besoin de modifier une contrainte ou encore le type d’un champs et bien ce n’est pas possible. il faut donc sauvegarder les données et les réinjecter dans une autre table.

Voici un petit exemple :

public class DBHelper extends SQLiteOpenHelper
{
	public DbHelper(Context context, String name, CursorFactory factory,int version) 
	{
		super(context, name, factory, version);
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) 
	{
		//Création des tables
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
	{
		//Mise à jour de la bdd de v1 à v2
		if(oldVersion == 1 && newVersion == 2)
		{
			try {
				db.beginTransaction();
				//Création de la nouvelle table, avec toute les modifications
				db.execSQL("CREATE TABLE table1_tmp (a,b)"); 
				//Transfert des données de table1 vesr table1_tmp
				db.execSQL("INSERT INTO table1_tmp (a,b) SELECT (a,b) FROM table1");
				//Suppression de l'ancienne table
				db.execSQL("DROP TABLE IF EXISTS table1");
				//Renomage de la table nouvellement créée afin de prendre la place de celle supprimée
				db.execSQL("ALTER TABLE table1_tmp RENAME TO table1");
				db.setTransactionSuccessful();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				db.endTransaction();
			}
		}
	}
}

On utilise ici une transaction pour s’assurer de ne pas perdre de données au passage. Si jamais quelque chose se passe mal , les modifications seront rollback automatiquement.

Comparer deux intervalles de date

Un problème classique avec les bases de données est la comparaison de deux intervalles de date. Mon intervalle A est il compris dans mon intervalle B ?
Avec un peu de réflexion c’est un problème très simple a résoudre.

L’illustration précente définie un intervalle de base (le gris) et toute les possibilités de bons (vert) et mauvais (rouge) intervalles.
Mon intervalle B est donc compris dans A si il ne fini pas avant le début de A et qu’il ne commencent pas après la fin de A.

En supposant que votre intervalle de date est décrit par deux champs date , on pourrait traduire cela par :

SELECT * FROM maTable WHERE NOT(date_debut > 2011-12-31 OR date_fin < 2011-01-01);

Dans cet exemple je cherche donc toute les dates qui ne commencent pas (NOT) après le 31 décembre 2011 ou qui ne se finisse pas avant le 1er janvier 2011.

En modifiant un peu la logique on peut supprimer le NOT qui n’aide pas forcément à la compréhension :

SELECT * FROM maTable WHERE date_debut < 2011-12-31 AND date_fin > 2011-01-01;

Cette fois je récupère donc toutes les dates qui commencent avant le 31/12/2011 et qui se termine après le 01/01/2011

PDO et sql server

Le driver PDO pour sqlserver étant en phase expérimental et carrément non supporté en PHP 5.3.x comment profiter de PDO pour se connecter à des bases sql server ?
Et bien tout simplement en passant par le driver ODBC qui lui est pleinement fonctionnel :
$dsn = "odbc:Driver={SQL Server Native Client 10.0};Server=127.0.0.1;Database=myDB;Uid=myUser;Pwd=myPass;"
$sql = new PDO($dsn);
$sql->query(...);
Les identifiants de connexion étant inclus dans le DSN il ne faut pas les inclures dans l’instanciation de PDO.

Sqlite concat

Sqlite ne supporte pas la fonction concat() qui permet de concaténer plusieurs champs d’une table.

Cependant il est possible d’arriver au même résultat avec l’opérateur de concaténation de sqlite : le double pipe : ||

Par exemple :

SELECT champs1 || champs2 || 'texte perso' || champs5 as meschamps FROM ma_table

Retournera sous le nom « meschamps » tous les champs demandé ainsi que le texte manuellement saisie.

Sqlite ,Truncate et auto incrément

Sqlite ne supporte pas la commande TRUNCATE , c’est assez problématique lorsque l’on à une colonne en autoincrément que l’on veut réinitialiser.

Il existe cependant une solution pour contourner cette « limitation » :

On supprime dans un premier temps tous les enregistrements de la table

DELETE FROM maTable

Puis on remet à zéro l’autoincrément via :

DELETE FROM sqlite_sequence WHERE name='maTable'

Documentation sqlite sur l’autoincrément

Base de données multilingue

Lorsque l’on se lance dans un site multilingue, on pense tout de suite, à comment va être traduit l’interface. On opte en général pour gettext ou une solution reposant sur une bdd (pas top à mon gout).
Cependant traduire le contenu de la base de données est une tout autres histoire et demande un peu plus de réflexion.

Prenons par exemple la table suivante :

videos
-id
-fichier
-duree
-nbvu
-titre
-description

Pour traduire le contenu de cette table il existe 2 méthodes très répandues mais qui présentent beaucoup de désavantages :

1- Duplication des colonnes

Dans cette méthode on va dupliquer les colonnes ayant besoin d’être traduite. Ce qui donnerait pour 2 langues :

videos
-id
-fichier
-duree
-nbvu
-titre_fr
-titre_en
-description_fr
-description_en

Cette méthode à l’avantage d’éviter la duplication de contenu, cependant elle trouve très vite ses limites. Il est difficile d’y ajouter une langue et la maintenance est une horreur si vous avez beaucoup de langue différente. Cependant cette méthode est facile à implémenter et peut être une méthode viable si vous avez seulement deux langues et ne risquez pas d’en rajouter.

2- Duplication des lignes

Pour pallier au problème de l’ajout de nouvelle langue certains préfère dupliquer les lignes plutôt que les colonnes. On se retrouve alors avec une table du type :

videos
-id
-fichier
-duree
-nbvu
-titre
-description
-langue_id

langue
-id
-code

Vous aurez donc autant de lignes pour une vidéo que vous aurez de langue.
L’ajout de nouvelle langue est donc facilité mais la duplication de contenu que cette méthode induit est à mon avis une très mauvaise chose.

Comment peut-on traduire le contenu de notre table ?

J’ai opté pour une méthode différente. Elle oblige à créer une table supplémentaire pour chaque table ayant besoin d’être traduite mais à le mérite de combiner les avantages des deux méthodes précédentes :
– Pas de duplication de contenu
– Ajout de langue simple
– Facile à interroger

Nous allons donc nous retrouver avec trois tables :

Pour chaque vidéo on aura un enregistrement dans la table vidéo auquel on associera des enregistrements de la table videos_traduction.
La sélection des données reste simple :

SELECT v.id,v.fichierFROM videos vINNER JOIN videos_traduction vt ON vt.idVideo = v.idWHERE vt.idLangue = :langue

En plus de tous les avantages précédemment cités, cette méthode apporte un niveau de normalisation tout à fait satisfaisant.

Et vous quelle méthode utilisez vous ?

Sqlite Encryption Extension avec PHP

PHP intègre de base sqlite3 , cependant cette version ne supporte pas les bases cryptées avec SEE. La seule solution pour que PHP puisse intéragir avec des bases cryptée c’est de re-compiler l’extension sqlite3 avec l’extension SEE fournie par sqlite (après s’être acquitté de la licence à 2000$ ).
Nous verrons donc ici comment réaliser ceci sous Windows. Je n’ai effectuer la manipulation qu’avec un PHP VC9 , mais celà doit être plus ou moins équivalent avec un VC6.

Prérequis :

Divers outils sont nécessaire avant de commencer quoi que ce soit

  • Visual Studio 2008 (nom de code Vc9) version pro ou express.
  • Le SDK Windows 6.1 : Voir ici
  • Les Binary tools de PHP : Télécharger
  • Les sources PHP : Voir ici
  • Les librairies PHP annexes. Dans le doute je les ai toutes prises, c’est pas très gros , autant pas se priver. Voir ici
  • Les sources de SEE téléchargées sur le dépôt sqlite. Faites attention à la version utiliser dans PHP pour être en adéquation (3.6.19 avec un PHP 5.3.1).
  • Apache version VC9 si besoin : Apachelounge

Procédure :

1- Dans un premier temps installer Visual Studio puis le SDK windows. C’et sans doute la partie la plus longue de l’opération….
2- Pendant ce temps préparez le dossier qui va recevoir les sources à compiler. J’ai personnellement crée un dossier D:/php-sdk.
3- Extraire les binary tools dans le dossier php-sdk. Vous devriez vous retrouver avec un dossier bin et script.

A partir de ce point l’installation du SDK et Visual doit être terminée.

4- Depuis le menu démarrer ouvrir le shell Windows SDK puis taper :
setenv /x86 /xp /release
cd d:\php-sdk\
bin\phpsdk_setvars.bat
bin\phpsdk_buildtree.bat php531

Le nom php531 est bien évidemment à choisir en fonction de votre version de php / humeur.
5- Extraire les sources PHP dans \php531\vc9\x86\
6- Modifiez L’extension sqlite de php (\php531\vc9\x86\ext\sqlite3\libsqlite\) avec la version SEE. Voir la doc SEE pour les lignes de code à copier et insérer.
7- Extraire les librairies annexes dans \php531\vc9\x86\deps\ si vous en avez besoin.
8- Lancez la commande : D:\php-sdk\php53dev\vc9\x86\php5.3.1\buildconf pour créer le fichier de configuration
9- configure –help . Vous donnera les différentes options possible pour votre compilation. Gardez en tête que pour obtenir une dll les extension doivent être compilée en shared.
Par exemple : configure –enable-pdo –with-pdo-sqlite3=shared
10- Une fois la configuration terminée (sans erreur) lancez : nmake
11- Une fois la compilation terminée lancez : nmake install
Par défaut vous trouverez les binaire et dll dans C:/php5/
12- Copiez la dll sqlite3 nouvellement compilé et remplacez là dans vos binaires de production.

Attention : Il est indispensable que vos binaires soit compilé avec le même compilateur que votre extension ! Extension VC9 = binaire VC9.

Après un redémarrage de php vous pouvez desormais profiter de la version cryptée de sqlite directement dans php.
On peut par exemple ouvrir une base cryptée comme ceci :

$sql->query('PRAGMA key=\''aes256:maclesecrete\'');

Mysql recherche fulltext sur table InnoDb

Le moteur de stockage InnoDb à de nombreux avantages mais pas celui de permettre la recherche fulltext pourtant extrêmement pratique lorsque l’on souhaite faire un petit moteur de recherche sur son application !
Ne pouvant me séparer de l’intégrité référentielle du moteur innodb, il m’a fallut trouver une solution.
Etant en mysql 5 le plus simple à été de créer une table de « recherche » avec le moteur myisam.
C’est une table allégée qui ne contient que les id et les champs textes à indexer.

Afin de garder cette table constamment à jour par rapport à la table originelle , il suffit de créer 3 triggers pour les 3 grandes opérations :

--Insertion CREATE TRIGGER bug_search_insert AFTER INSERT ON bugs FOR EACH ROW INSERT INTO bugs_search SET idBug = NEW.id, description = NEW.description
--Update CREATE TRIGGER bug_search_update AFTER UPDATE ON bugs FOR EACH ROW UPDATE bugs_search SET idBug = OLD.id, description = NEW.description WHERE id = OLD.id
--Delete CREATE TRIGGER bug_search_delete AFTER DELETE ON bugs FOR EACH ROW DELETE FROM bugs_search WHERE id = OLD.id

Ainsi chaque modification dans la table au moteur innodb sera répercutée dans la table au moteur myIsam. On perd évidemment en performance puisque que l’on doit travailler sur une table supplémentaire, mais on gagne la recherche fulltext sans sacrifier l’intégrité référentielle !

Détails de la création d’un trigger :

CREATE TRIGGER (1) (2) ON (3) FOR EACH ROW (4)

1- Nom du trigger , doit être unique
2- Moment de déclenchement, par exemple AFTER INSERT , BEFORE UPDATE ….
3- Table « déclenchante »
4- Requête à exécuter pour chaque ligne

Sqlite : optimiser un COUNT() long

Sqlite à pour politique de ne pas stocker de meta data à propos du nombre de ligne des tables. C’est un choix justifiable car garder ces infos pour toutes les tables est couteux en ressources et ralenti donc forcément chaque INSERT/DELETE.
Parcourir une table lors du COUNT n’est donc pas très problématique sauf quand les tables atteignent un niveau important de données.
Mauvaise surprise pour moi lorsque ma page à mis plus de 1 min à s’afficher (base de 7Go et 500k enregistrements).
Pour résoudre le problème il faut émuler cette meta data avec 2 triggers et une tables supplémentaire.

On pourra par exemple créer une table « totaux » avec 3 champs « id », »table » et « valeur ». Il y’aura donc un enregistrement par table avec pour valeur le nombre de lignes de cette table.

Ne reste qu’à creer les deux triggers :

CREATE TRIGGER "count_delete_matable" AFTER DELETE ON maTable BEGIN UPDATE totaux SET valeur = valeur-1 WHERE table='maTable'; END;CREATE TRIGGER "count_insert_matable" AFTER INSERT ON maTable BEGIN UPDATE totaux SET valeur = valeur+1 WHERE table='maTable'; END;

Ainsi après chaque enregistrement ajout ou supprimer le total sera automatiquement in/decrémenté.
Ne reste en suite qu’à remplacer les

SELECT COUNT(*) FROM maTable

par

SELECT valeur FROM totaux WHERE table='maTable'

Et là Ô magie , la page passe de plus d’1min à moins d’une demi seconde 🙂