Bilans mensuels (variante)

Revenons sur le contenu de cet article. Pour la réalisation des rapports libreoffice, nous avions travaillé avec une vue qui fournissait le mois de l'opération à partir de la date d'exécution (date_exec). Mais cela n'est pas nécessaire. Nous pouvons par exemple prendre comme source de données la vue basée sur la table opérations et créée par cet instruction:

CREATE VIEW opérationsvs AS 
SELECT substr(a.reference::text, 1, 4) AS an,
a.reference,
a.date_exec, 
        CASE
            WHEN a.montant > 0::numeric THEN a.montant
            ELSE 0::numeric
        END AS crédit, 
        CASE
            WHEN a.montant < 0::numeric THEN - a.montant
            ELSE 0::numeric
        END AS débit, 
a.montant AS solde, 
a.contrepartie,
( SELECT SUM( montant )
 FROM opérations b
 WHERE b.reference <= a.reference
 AND extract(year FROM b.date_exec) = extract(year FROM a.date_exec)
 AND extract(month FROM b.date_exec) = extract(month FROM a.date_exec) 
) AS solde_c
   FROM opérations a

Là où on affichait le mois (comme en-tête de groupe, au dessus du trait rouge), on mettra maintenant la date

mais évidemment avec un format approprié:

Reste à s'occuper du groupement:

Et voilà!
Ci-dessous par exemple, les opérations de février (page 3 du rapport) avec le solde relatif aux opérations de février seulement:

Tableau croisé avec des données postgresql (suite)

Nous n'allons plus cette fois comme dans le billet précédent lier directement le tableau à une table postgresql, mais procéder de manière indirecte.
Après appui sur F4 nous choisissons la table "opérations" (de la base de données bdtest) comme source de données. Nous n'appliquons aucun filtre, aucun tri.
Après sélection de l'ensemble de la table (en cliquant sur le rectangle gris du coin supérieur gauche)

nous insérons les données dans le tableur via l'icône "Données dans le texte":

Ensuite comme auparavant nous passons par Données => Table du pilote => Créer, mais au moment de sélectionner la source nous optons pour "Sélection active":

Il reste à procéder en faisant glisser ce qui convient là où ça convient...

pour obtenir ceci:

Cette méthode présente deux avantages par rapport à la précédente:

• apparition de la cellule "Filtrer" (qui refusait obstinément d'apparaître en cas de lien direct)
• les dates sont directement au bon format (alors que précédemment 30/12/12 s'affichait 41273)

Cliquer sur "Filtrer" nous permet de ne retenir que les dates de 2013:

Il reste à grouper par mois (via F12):

Attention: en cas de modification des données, il faut cette fois actualiser la feuille qui contient les données (et qui elle est liée à la table postgresql):
Ouvrir le navigateur avec F5:

Double-cliquer sur la plage de donnée adéquate (par défaut Importer1 si on n'a effectué qu'une importation), puis actualiser la plage:

En principe le tableau croisé devrait être mis à jour automatiquement, ou sinon procéder comme précédemment: clic droit sur le tableau puis choisir 'Actualiser' dans le menu contextuel qui surgit.

Tableau croisé dynamique avec des données postgresql

Dans le billet précédent, nous nous sommes efforcés de construire dans Libreoffice Calc un rapport du même genre que celui que nous avions obtenu dans Libreoffice Base. Mais Libreoffice Calc permet beaucoup plus. Nous allons ici construire à partir des mêmes données postgresql un tableau croisé dynamique (pour la définition de la table et de la vue utilisée, ainsi que pour les données: voir ce billet).

On commence par utiliser le menu Données => Table de Pilote => Créer:

Nous supposons que le fichier bdtest.odb permettant la connexion à postgresql existe et qu'il a été enregistré:

Nous sélectionnons la source de données opérationsv qui correspond à une vue définie au niveau de postgresql:

Dans la fenêtre qui surgit, il suffit de faire glisser ce qui convient là où ça convient:

"an" placé dans la zone "Champs de page" nous permettra de filtrer le tableau suivant l'année:

Il reste à formater convenablement les cellules et c'est terminé:

Plutôt que de placer "mois-n" et "mois" dans la zone "Champs de colonne", on pourrait y placer "date_exec". On arriverait alors à un tableau tel que celui-ci (dont nous n'affichons qu'une partie):

(Il faut mettre la ligne "contrepartie" au format date)

Ensuite appuyant sur F12 après avoir sélectionné une date, nous avons la possibilité de grouper par mois:

ce qui transforme radicalement le tableau:

En plaçant "crédit" et "débit" dans la zone "Champs de données", on aboutirait  à ceci:

En cas de changement de données au niveau de postgresql, il suffit de cliquer droit sur le tableau qui nous intéresse et dans le menu contextuel qui surgit, choisir "Actualiser".

Utilisation données postgresql dans lbo calc

Dans le cadre de ce blog, nous avons toujours parlé de connexion entre Libreoffice Base et une base de données postgresql. Ainsi par exemple suite à une connexion vers la base de données bdtest, il en est résulté un fichier bdtest.odb qui contient non seulement les paramètres de connexion

mais aussi tout ce qui a pu être créé (requêtes, formulaires, rapport).
Ainsi on peut y obtenir ce rapport basé sur les données figurant dans une table opérations:

(voir ce billet) 

Nous allons montrer ici comment utiliser Libreoffice Calc pour réaliser un rapport de ce genre.

Ouvrons Libreoffice Calc et appuyons sur F4 (EDIT: CTRL-MAJ-F4) pour afficher les sources de données:

Si bdtest.odb n'y figure pas, il faut l'ajouter via Outils=> Options => Libreoffice Base => Base de données=> Nouveau

Après que bdtest ait été référencé, nous pouvons y choisir comme source de données pour ce rapport la requête opérationsr (la même qui a été utilisée dans ce billet) et nous filtrons sur l'année 2013:

Ensuite nous trions suivant mois_n et référence:

Après avoir sélectionné l'ensemble des données, il reste à les insérer dans le tableau avec l'icône "Données dans le texte":

Ensuite passant par le menu Données => Sous-totaux:

nous avons la possibilité de calculer les sous-totaux pour chaque mois:

Dans l'onglet "Options", nous n'oublions pas de décocher la case "Trier" car d'une part les données ont déjà été triées, d'autre part le tri positionnerait par exemple "Février" avant "Janvier".

Le reste est du peaufinage:

• nous formatons de manière adéquate les cellules numériques,
• déplaçons la colonne "contrepartie" après la colonne "solde_c",
• déplaçons la colonne "reference" après la colonne "mois",
• supprimons les colonnes "an" et "seq" et masquons la colonne "mois_n".

Et voici le résultat:

Notons que pour déplacer une colonne, il faut la sélectionner (en cliquant sur l'en-tête) et ensuite procéder avec la souris tout en maintenant la touche 'Alt Gr' enfoncée. Pour la colonne "reference", il faut au préalable insérer une colonne vide avant la colonne "date_exec".

Nous pouvons aussi décider de masquer les détails:

(ne pas sélectionner les deux dernières lignes)

Postgresql upgrade 2

Dans l'article Postgresql upgrade, nous avons traité de la migration des données postgresql lors du passage de Fedora 19 à Fedora 20.
Cette fois, nous allons considérer le même problème pour une autre distribution, à l’occurrence "Chakra". Cependant le contenu de cet article pourrait être utilisé dans d'autres cas.

En tant qu'utilisateur postgres, sauvegardons les anciennes données sous un nom qui rappelle l'ancienne version de postgresql:

[toto@rigel ~]$ su -
Mot de passe : 
[root@rigel ~]# su - postgres
[postgres@rigel ~]$ mv /var/lib/postgres/data/ /var/lib/postgres/data_9.2/

Dans la foulée nous pouvons créer le nouveau cluster:

[postgres@rigel]$ initdb --locale fr_FR.utf8 -E UTF8 -D /var/lib/postgres/data

Notons que au démarrage de postgresql.service, il est prévu le lancement d'un script qui initialise la database si celle-ci ne l'est pas encore:

[toto@rigel ~]$ grep "ExecStartPre" /usr/lib/systemd/system/postgresql.service 
ExecStartPre=/usr/lib/systemd/scripts/postgresql-initdb

Donc pour coller au plus près de ce qui a été fait auparavant, nous aurions pu, au lieu d'utiliser la commande initdb, procéder comme ceci:

[root@rigel ~]# /usr/lib/systemd/scripts/postgresql-initdb

à condition d'avoir pris quelques précautions.
La commande suivante:

[toto@rigel ~]$ head -5 /usr/lib/systemd/scripts/postgresql-initdb
#!/bin/sh

set -e

. /etc/conf.d/postgresql

montre que le script utilise /etc/conf.d/postgresql.
L'examen de ce fichier nous conduit à devoir y remplacer, avant l'exécution du script,  en_US par ce qui convient:

[root@rigel ~]# sed -i 's/en_US/fr_FR/' /etc/conf.d/postgresql

Quelle que soit la méthode employée pour initialiser la database, il reste ensuite à utiliser  pg_upgrade pour procéder à la migration. Avec Fedora, pg_upgrade venait avec le paquet postgresql-upgrade qui fournissait également les commandes relatives à l'ancienne version de postgresql.
Rien de tel cette fois, pg-upgrade vient avec le paquet postgresql:

[toto@rigel ~]$ pacman -Qo $(which pg_upgrade)
/usr/bin/pg_upgrade appartient à postgresql 9.3.2-1

Interrogeons le paquet à la recherche d'informations:

[toto@rigel ~]$ pacman -Qi postgresql 
Nom                   : postgresql
Version               : 9.3.2-1
Description           : A sophisticated object-relational DBMS
Architecture          : x86_64
URL                   : http://www.postgresql.org/
Licences              : custom:PostgreSQL
Groupes               : --
Fournit               : akonadi-backend
Dépend de             : postgresql-libs>=9.3.2  krb5  libxml2  readline>=6.0  openssl>=1.0.0
Dépendances opt.      : python3: for PL/Python support [installé]
                        perl: for PL/Perl support [installé]
                        tcl: for PL/Tcl support [installé]
                        postgresql-old-upgrade: upgrade from previous major version using pg_upgrade    

Nous avons besoin de postgresql-old-upgrade,  mais cette dépendance optionnelle semble ne pas être fournie.
Qu'à cela ne tienne: allons rechercher l'ancien paquet dans le cache de pacman:

[toto@rigel ~]$ cp /var/cache/pacman/pkg/postgresql-9.2.4-2-x86_64.pkg.tar.xz Downloads/
[toto@rigel ~]$ cd Downloads/
[toto@rigel Downloads]$ mkdir postgresql
[toto@rigel Downloads]$ tar xpvf postgresql-9.2.4-2-x86_64.pkg.tar.xz -C postgresql

Évidemment si le cache a été vidé, au lieu de simplement utiliser la commande cp, il faudra télécharger le paquet ad hoc (postgresql-9.2.4-2-x86_64.pkg.tar.xz)

Après extraction du paquet, exécutons pg_upgrade (en tant qu'utilisateur postgres) et puis recopions ce qui convient:

[postgres@rigel ~]$ pg_upgrade -b /home/toto/Downloads/postgresql/usr/bin -B /usr/bin/ -d data_9.2/ -D data
[postgres@rigel ~]$ cp data_9.2/pg_hba.conf data
[postgres@rigel ~]$ cp data_9.2/pg_ident.conf data

Il reste éventuellement à adapter le fichier /var/lib/postgres/data/postgresql.conf et en tout cas à décommenter la ligne

port = 5432  

avant de démarrer le serveur comme ceci:

[root@rigel ~]# systemctl start postgresql.service  

ou comme cela

[postgres@rigel ~]$ pg_ctl start -D /var/lib/postgres/data
serveur en cours de démarrage
[postgres@rigel ~]$ LOG:  le système de bases de données a été arrêté à 2014-01-04 16:15:19 CET
LOG:  lancement du processus autovacuum
LOG:  le système de bases de données est prêt pour accepter les connexions

La deuxième commande donne plus d'infos en cas de problème.
Par exemple on peut avoir:

[postgres@rigel ~]$ pg_ctl start -D /var/lib/postgres/data
serveur en cours de démarrage
[postgres@rigel ~]$ FATAL:  n'a pas pu créer le fichier verrou « /run/postgresql/.s.PGSQL.5432.lock » : Aucun fichier ou dossier de ce type

alors que la commande systemctl ne fournit aucun retour bien que le service ne soit pas démarré.
Dans ce cas, il faut automatiser la création du dossier /run/postgresql en procédant comme indiqué ici.
Le dossier sera disponible après redémarrage.

systemd: création répertoire dans /run

Nous voulons que notre serveur postgresql utilise un socket situé /run/postgresql.
Mais ce n'est pas ce qui est prévu au niveau de la distribution: /run/postgresql n'existe pas et donc notre serveur se plante.
Créer /run/postgresql par mkdir est d'une utilité toute relative car /run se trouve en mémoire vive et le répertoire aura disparu au prochain démarrage.
Il faudrait donc créer ce répertoire au démarrage.
Voici comment procéder si la distribution utilise systemd.
Dans /usr/lib/tmpfiles.d/, créer un fichier postgresql.conf contenant:

d /run/postgresql 2755 postgres postgres -

Par exemple comme ceci:

(Il reste à appuyer sur CTRL-D pour procéder)

Il faut ensuite s'assurer que le service postgresql sera bien activé au prochain démarrage:

[root@rigel ~]# systemctl enable postgresql.service

Bien sûr: ce est écrit ici peut être utilisé dans d'autre cas.
Il est également possible de créer des fichiers.

Pour plus de détails: man tmpfiles.d 

Postgresql upgrade

Lors du passage de Fedora 19 à Fedora 20, postgresql passe de la version 9.2 à la version 9.3
Aussi nous devons procéder manuellement à la migration des données postgresql.
Avant toute chose vérifions que la variable d'environnement PGDATA existe et qu'elle contient ce qui convient:

[toto@rigel ~]$ echo $PGDATA
/var/lib/pgsql/data

Ceci sera le cas si nous avons placé dans /etc/profile.d  un fichier pgsql.sh comme expliqué ici.

Installons postgresql-upgrade (si celui-ci ne l'est pas):

[toto@rigel ~]$ su -
Mot de passe :
[root@rigel ~]# yum install postgresql-upgrade

Nous serons amené à agir en tant qu'utilisateur postgres. Le plus simple pour ce faire est de procéder dans une autre fenêtre.

[toto@rigel ~]$ su -
Mot de passe :
[root@rigel ~]# su - postgres

-bash-4.2$ mv /var/lib/pgsql/data/ /var/lib/pgsql/data_9.2/

Nous avons renommé les anciennes données.
Ensuite (en tant que root):

[root@rigel ~]# postgresql-setup initdb

Puis dans la fenêtre où l'on est postgres:

-bash-4.2$ cp /var/lib/pgsql/data_9.2/pg_hba.conf /var/lib/pgsql/data/
-bash-4.2$ pg_upgrade -b /usr/lib64/pgsql/postgresql-9.2/bin/ -B /usr/bin/ -d data_9.2/ -D data

Tout semble se dérouler à merveille.
Et soudain l'erreur fatale:

lc_collate cluster values do not match:  old "fr_FR.UTF-8", new "fr_FR.utf8"

Il faut recommencer après avoir remplacé utf8 par UTF-8 dans /etc/locale.conf

[root@rigel ~]# sed -i 's/utf8/UTF-8/' /etc/locale.conf
[root@rigel ~]# rm -rf /var/lib/pgsql/data
[root@rigel ~]# postgresql-setup initdb

Cette fois, l'étape suivant ne pose aucun problème.
Ne pas oublier de recopier pg_ident.conf (si celui-ci est utilisé):

-bash-4.2$ cp /var/lib/pgsql/data_9.2/pg_ident.conf /var/lib/pgsql/data/

et aussi d'adapter éventuellement le fichier postgresql.conf suivant par exemple ce qui est expliqué ici.
Reste à démarrer le service:

[root@rigel]# systemctl start postgresql.service