Accès direct à la base de données de domoticz

Introduction

Depuis un certain temps, quelque chose me gênait dans l'affichage des courbes d'évolutions du niveau de ma cuve de récupération d'eau. J'avais fait une bidouille au mois d’août et cela avait eu pour effet d'injecter une valeur totalement disproportionnée par rapport aux valeurs possibles.

Du coups l'axe des ordonnées, au lieu d'être échelonné entre 0 et 200, avait une échelle de 200 à -1000 ! L'effet sur la courbe était qu'on en voyait presque plus les variations.

Voila ce que j'observais :

Pas très joli ! Il me fallait corriger cela. C'était l'occasion d'essayer d'accéder directement à la base de données du serveur domoticz pour en modifier des données.

Edit du 03/03/2018 : Tout nouveau ! Tout beau ! Pour ceux d'entre vous qui souhaitent ajouter de nouvelles pages personnalisées aux onglets de leur interface Web domoticz, vous pourrez consulter mon nouvel article "Ajout d'une page personnalisée à Domoticz". Nous y apprendrons également comment savoir si nos équipements sur notre réseau local sont bien connectés.

---

---

SQLite 

Toutes les données gérée par domoticz sont stockées dans une base de données de type SQLite. 
SQLite est un système simple d'utilisation qui permet de regrouper toutes les données dans un fichier unique. 

SQLite a évolué au fil du temps et la version actuellement utilisée par domoticz est la 3.

Mon objectif sera donc d'accéder à cette base, de retrouver la valeur erronée et de la modifier.

Comme à mon habitude de commence à me poser des questions :

1. Où se trouve ce fichier ? 
2. Comment me "connecter dessus" ? 
3. Comment retrouver mes données ? 
4. Comment modifier la donnée ? 

Les réponses aux questions 

Pour la suite de cet article il faudra se connecter en SSH sur le Raspberry PI.
Toutes les instructions se lancent en ligne de commande dans la console.
Il y a des outils graphiques qui permettent d'accéder à la base SQLite de domoticz mais je n'ai pas exploré cette piste.

Où se trouve ce fichier ?

Le fichier correspondant à cette base se situe sous : 

/home/domoticz/domoticz.db

Comment se "connecter dessus" ?

Pour se "connecter" sur le fichier et manipuler les données qui s'y trouvent il faut passer par le client SQLite. Ce client s'appelle "sqlite3". 
Si il n'est pas disponible il faudra l'installer tout simplement en faisant : 

sudo apt-get install sqlite3

Ensuite lancer la commande :

cd /home/domoticz/
sqlite3

Une fois sous sqlite3 il faudra lancer quelques instructions pour : 

• Ouvrir le fichier souhaité. 
• Activer l'affichage des headers
• Regler le mode colonne pour un affichage plus clair.

.open domoticz.db
.headers on 
.mode column

Comment retrouver des données ?

Idéalement il faut connaitre un peu le SQL et savoir ce qu'est une base de données relationnelle.

Mais le but de cet article est que chacun puisse le faire avec un minimum de connaissance je vais faire un petit rappel.

Une base de données comment ça marche ?

De manière archi simplifiée, une base de donnée contient des tables. On peut voir cela comme des tableaux excel.
Les tables contiennent des colonnes. Chaque colonne a un nom.
Les données sont donc rangées par tables et par colonnes. Cela permet d'ordonner de manière logique les données.
Par exemple une base de donnée de facturation aura une table nommée facture. Et dans cette table facture on pourra retrouver les colonnes : numéro_de_facture, date, montant , Nom, adresse ...
Une base de données sert donc à stocker des données en les regroupant de manière logiques.

La base de données domoticz.db contient des tables qui permettent de ranger de manière organisées toutes les données utilisées. Que ce soit les données sur le matériel ou alors les historiques des données reçues par les capteurs.

Explorer la structure d'une base de données 

Lorsque l'on est sous sqlite3, pour avoir la liste de toutes les tables de la base il vaut saisir la commande :

sqlite>.tables

En voici le résultat :

La commande .help permet d'avoir la liste de toutes les commandes de contrôle disponibles :
sqlite>.help

Pour explorer plus facilement la structure de la base, en visualisant la liste des tables et pour chaque table, le nom des colonnes qui la constituent, j'ai installé le logiciel SQLiteManager sur mon PC Windows. Je l'ai trouvé à l'adresse suivante : http://www.sqlabs.com/sqlitemanager.php

Au préalable j'ai récupéré une sauvegarde de la base domoticz sur mon pc ( sous domoticz aller dans réglages / paramètres / sauvegarde restauration ).

Dans le menu "Design", à gauche on obtient la liste de toutes les tables de la base. Et dans la fenetre centrale le nom des colonnes qui constituent la table.

J'ai entouré en rouge le champs DeviceRowID car ce dernier est à connaitre. On va le retrouver dans de nombreuses tables. Il s'agit du numéro du dispositif tel qu'il apparait dans domoticz dans le champ idx de l'onglet "dispositifs".

---

---

Lire et modifier une donnée 

Il est recommandé de sauvegarder la base et d'avoir stopé le serveur domoticz avant d'effectuer des accès en écriture dans la base.

A savoir 

Dans les tables on retrouve assez souvent les colonnes suivantes :

DeviceRowID : correspond à l'ID du capteur tel qu'il apparait dans l'onglet Reglages/Dispositifs de domoticz.
Date : correspond à la date de la mesure au format AAAA-MM-JJ HH:MM:SS ou encore AAAA-MM-JJ

Certaines tables portent un nom de type "NOM_Calendar". Ces dernières mémorisent l'historique au dela de 2 jours. Alors que dans la table "NOM" on trouvera toutes les mesures des deux derniers jours.

Pour modifier une donnée dans une table il faut identifier le nom de la table et la colonne dans laquelle est stockée cette donnée.
Pour cela je n'ai pas d'astuces. Il faut juste prendre chaque table, essayer de voir le nom des colonnes et dans chaque colonne le type de données.

Tips : La base étant organisée de manière plutot logique avec des noms qui doivent être parlant, si l'on recherche une température, en regardant la structure de la base, on se doute que l'on a plus de chance de trouver la valeur recherchée dans la table "Temperature" que dans la table "Camera".

select

Pour lire les valeurs dans une table c'est la commande SQL select que l'on va utiliser.
Elle s'utilise de la manière suivante :

SELECT colonne1, colonne2, etc FROM table WHERE condition;

Noter le caractère ";" pour finir la commande.

Avec :

• colonne : le nom des colonne que l'on souhaite lire. On peut remplacer le nom des colonnes par le symbole * pour dire que l'on veut toutes les colonnes.
• table : le nom de la table.
• condition : une condition logique sur la valeur d'une colone. Par exemple : WHERE Temperature > 10 and Temperature < 20. Les chaines de caractère doivent être encadrées par le caractère ". Exemple : date > "2017-11-11 02:10:00"

Le fait d'avoir activé le mode column devrait permettre d'afficher le nom de chaque colonne lorsque qu'une requete SQL est réalisée.

update 

Pour modifier une valeur il faut utiliser la commande SQL update de la manière suivante.

UPDATE table SET colonne=valeur WHERE condition1 AND condition2;

Revenons à nos moutons 

Dans mon cas j'ai trouvé la valeur de mon compteur de volume de cuve d'ean dans la table "Percentage_Calendar". La colonne qui contenait la valeur -1000 à la date du 10 aout 2017 était la colonne "Percentage_Min".

J'ai donc exécuté la commande suivante :

update Percentage_Calendar set Percentage_Min=40 where date ="2017-08-10" and DeviceRowID = 221;

Et obtenu le résultat suivant : 

Le résultat est parfait. Je peux maintenant distinguer l'évolution du niveau d'ean dans la cuve et l'axe des ordonnées a repris la bonne échelle. 

---

Soutenez la blogoculture ...

Le plus simplement du monde, si vous avez un achat à faire sur Amazon, accédez au site à partir de ce lien (que vous pouvez ajouter dans vos favoris) :  https://amzn.to/2nbe4sm

Soutenez la blogoculture ...

Le plus simplement du monde, si vous avez un achat à faire sur Amazon, accédez au site à partir de ce lien (que vous pouvez ajouter dans vos favoris) :  https://amzn.to/2nbe4sm

... mais aussi ...

Vous appréciez les articles frais et vitaminés de ce blog et vous voulez faire un geste pour encourager ce partage, saluer le travail, ou parce que vous y avez trouvé des choses utiles ( et que vous êtes sympa ) ?

... c'est possible et vous avez le choix !

Si vous avez un compte Paypal et quelques euros à offrir sans vous mettre sur la paille, subventionnez la culture domotique à l'ancienne !

Vous ne dépenserez pas un radis de plus en faisant un achat sur eBay à partir de ce lien.

Economisez du blé avec Amazon Prime ! Offre d'essais 1 mois gratuit (et renouvelable).

Soyez chou et aidez les petits producteurs de blog à se faire connaitre auprès de vos amis facebook !

Merci