Garradin

Centos-8 - Installation de GARRADIN

Page mise à jour le 20/05/2020

SOMMAIRE

A - Installation

B - Passer Garradin en HTTPS

C - Paramètrage

D - Adaptation du logiciel

E - Mise à jour de Garradin

F - Utilisation de SQLITE3

 

Garradin est une application Web qui permet de gérer la comptabilité d'une association. L'application Web gère la comptabilité et les adhérents. Elle dispose aussi d'un wiki et d'un petit éditeur de pages web. Le produit est utilisable en ligne sur internet mais il peut aussi être installé sur un serveur privé. C'est l'option prise pour ce tutoriel.

Garradin sera installé dans la home directory de l'utilisateur en tant que virtualhost. Voir le paragraphe : Apache : Espace web privé (userdir). L'application utilise une base de données sqlite3.

Version qui sera installée : garradin-0.9.6 du 1er mai 2020
avec PHP : 7.3.17, SQLITE : 3.26.0, HTTPD : 2.4.37 sous centOS-8.1.1911

# php -v
->
PHP 7.3.17 (cli) (built: Apr 14 2020 08:29:22) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.3.17, Copyright (c) 1998-2018 Zend Technologies
    with Zend OPcache v7.3.17, Copyright (c) 1999-2018, by Zend Technologies

# httpd -v
->
Server version: Apache/2.4.37 (centos)
Server built:   Dec 23 2019 20:45:34

# sqlite3 -version
->
3.26.0 2018-12-01 12:34:55
Installation de SQLITE v3 (pour mémoire)
# dnf install sqlite

 

A - Installation

Téléchargement et décompression

On se positionne à la racine de l'espace web de l'utilisateur, on télécharge Garradin, on le décompresse puis on supprime le fichier téléchargé.

# cd /home/user1/public_html
# wget https://fossil.kd2.org/garradin/uv/garradin-0.9.6.tar.bz2
# tar xjf garradin-0.9.6.tar.bz2
# rm -f garradin-0.9.6.tar.bz2

Mise en place des fichiers

L'application sera installée dans la home directory de l'utilisateur, à la racine du site ( /home/user1/public_html/ ). Pour celà on déplace tous les fichiers qui figurent dans le répertoire garradin-0.9.6 créé par la décompression (tous les fichiers même les fichiers cachés -> .htaccess) à la racine du site web puis on supprime le répertoire garradin-0.9.6.

# mv garradin-0.9.6/* ./
# mv garradin-0.9.6/.h* ./
# rmdir garradin-0.9.6

Mise en place des droits sur les répertoires

Le répertoire "cache" doit être en "rwxrwxrwx" et Garradin doit pouvoir écrire via apache dans les autres répertoires et dans le fichier error.log.

# chmod 777 cache
# chmod 774 plugins
# chmod 770 www/squelettes
# touch error.log
# chmod ug=rw,o= error.log

Affectation au propriétaire de la home directory

Vu que Garradin est installé dans la home directory de l'utilisateur, il faut donner la propriété des fichiers à l'utilisateur (user1) puis ajouter apache au groupe user1.

# chown -R user1:user1 ./
# gpasswd -a apache user1

Paramètrage Selinux

"Chcon" modifie le contexte de façon temporaire. Pour fixer le contexte de façon permanente, il faudra utiliser "semanage". Ici, on donne le droit à apache de lire le contenu du répertoire de l'utilisateur, celui de lire et d'écrire sur le cache, sur le répertoire squelettes et sur le fichier error.log.

# chcon -R -t httpd_sys_content_t .
# chcon -R -t httpd_sys_rw_content_t cache
# chcon -R -t httpd_sys_rw_content_t www/squelettes
# chcon -t httpd_sys_rw_content_t error.log

Création du virtualhost
désactiver les options Indexes et Multiviews

Sur le serveur Web, il faut créer le fichier de configuration dans le répertoire /etc/httpd/conf.d/ et ajouter les lignes après "->" en adaptant l'adresse IP, l'utilisateur et le nom de domaine. Ne pas oublier de relancer le serveur apache.

# touch /etc/httpd/conf.d/garradin.conf
# vi /etc/httpd/conf.d/garradin.conf
->
# Garradin - Comptabilite - Production
# --------------------------------------------
<VirtualHost 192.168.1.233:80>
        ServerName www. garradin.vhost.fr
        ServerAlias garradin.vhost.fr

        DocumentRoot /home/user1/public_html/www/
        <Directory /home/user1/public_html/www/>
                Options FollowSymLinks
                AllowOverride All
                Require all granted
        </Directory>

        Errorlog /var/log/httpd/garradin.log
        LogLevel warn
</VirtualHost>

# systemctl restart httpd

Inhiber la directive Options du fichier .htaccess

# sed -i".ori" '/Options/d' .htaccess

ATTENTION : Cette action si elle n'est pas réalisée produit l'erreur serveur suivante :

Internal Server Error
The server encountered an internal error or misconfiguration and was unable \
to complete your request.
Please contact the server administrator to inform them of the time this error \
occurred, and the actions you performed just before this error.
More information about this error may be available in the server error log.

Garradin est maintenant installé.

 

B - Passer Garradin en HTTPS

Prés-requis :
Disposer d'un certificat installé sur le serveur Web pour le domaine considéré (pour moi, le domaine = garradin.vhost.fr).
Activer le flux HTTPS via le reverse proxy Apache.

Dans cette configuration, les flux sont chiffrés entre l'utilisateur et le reverse proxy, ce qui est suffisant pour sécuriser les accès via Internet. Derrière le reverse proxy, les flux transitent en clair sur le réseau local.

1°) Modifier le fichier de configuration

Se placer à la racine du site, dans le répertoire root du serveur web où est installé Garradin et ajouter les 2 lignes suivantes au fichier de configuration local.

# vi /home/user1/public_html/config.local.php
->
const PREFER_HTTPS = 3;
const WWW_URL = 'https://garradin.vhost.fr/';

(Voir aussi le fichier config.dist.php pour les autres possibilités)

Ajouter les 2 lignes suivantes au fichier .htaccess

# vi /home/user1/public_html/.htaccess
->
  SetEnv HTTPS "on"
  SetEnv HTTP_X_FORWARDED_PROTO "https"

Test du bon fonctionnement.

Dans la barre de navigation de votre navigateur saisissez : https:/ /garradin.vhost.fr
Votre navigation devrait maintenant être protégée (apparition du cadena vert devant l'URL du site et sur la page administration, en regard du champ "Mot de passe".

 

C - Paramètrage de Garradin

Répertoires et fichiers modifiés à l'installation

A la racine du site web, l'installation crée :
- le fichier de base de données : association.sqlite
- le fichier de configuration locale : config.local.php qui contient les paramètres locaux et notamment la clé de session
- le fichier d'erreur : error.log

Dans le répertoire cache,
- le répertoire compiled
- le répertoire static

Interface applicative d'installation

A partir d'un navigateur, http:// garradin.vhost.fr

Garradin - Installation

 

Garradin - Page de connexion

 

D - Adaptation du logiciel

Une fois le logiciel installé, il peut être paramétré. Un guide au format pdf est disponible sur le site de l'éditeur. On y trouve aussi toute la documentation du logiciel.

De façon générale, il faudra :

  1. mettre en place les catégories de membres

  2. mettre en place la cotisation annuelle

  3. saisir les éléments bancaires

  4. configurer la fiche de membres

  5. saisir les données des membres ou importer la liste des membres (si disponible au format excel)

Si vous disposez d'une ancienne version de Microsoft Excel (2003/2007/2010 ?), l'import des données pose problème pour les caractères accentués. En effet, Garradin ignore les cellules qui en comportent. Pour remédier à ce problème, il est préférable d'utiliser LibreOffice qui permet de formater les données selon le codage UTF-8.

 

E - Mise à jour de Garradin

Prérequis :
Créer un nouveau virtualHost au niveau du reverse proxy
Télécharger la dernière version stable.

# wget https://fossil.kd2.org/garradin/uv/garradin-0.9.6.tar.bz2

Effectuer une installation classique dans un nouveau virtualhost en suivant la procédure "A-installation" ci-dessus. L'utilisation d'un nouveau virtualhost permet de conserver intacte l'ancienne version, ce qui a pour avantage d'être en capacité de revenir à l'ancienne version si la migration à la nouvelle version se passe mal. Pour ma part, j'utilise deux VirtualHosts (le premier pour la production et le second pour les tests, former de nouveux utilisateurs, etc). A chaque mise à jour, je bascule de l'un sur l'autre.

Copier la base de données de la version précédente.

# cp /home/user1/public_html/association.sqlite /home/user2/public_html/
# chown user2:user2 association.sqlite

Restreindre l'accès à la base de données

# chmod o=-r association.sqlite

Copier les fichiers du site web si vous utilisez cette fonctionnalité

# cp /home/user1/public_html/www/squelettes/* /home/user2/public_html/www/squelettes/
# chown -R user2:user2 /home/user2/public_html/www/squelettes/*

Au niveau du reverse proxy, modifier le pointeur de sous-domaine pour qu'il corresponde au nouveau sous-domaine

 

Lancer l'interface web de Garradin pour qu'il convertisse la base de données

Dans votre navigateur, saisissez https://garradin.vhost.fr

Lorsque la base est convertie, il est possible de supprimer le fichier "association.pre-upgrade-0.9.6.sqlite" qui a été créé par garradin.

# rm -f  association.pre-upgrade-0.9.6.sqlite

Testez le bon fonctionnement de la mise à jour.

Si necessaire, supprimer l'ancien répertoire lorsque tous les tests sont corrects. L'idéal étant étant de disposer d'un virtualhost pour la version qui sera mise en production et un autre pour celle qui sera plutôt réservée aux tests d'utilisation et d'intégration.

 

F - Utilisation de Sqlite3

En mode console :

Se positionner dans le répertoire où est installé Garradin et lancer l'interprèteur SQL en chargeant la base de données utilisée par Garradin

# cd /home/user1/public_html/
# sqlite3 association.sqlite
->
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
# sqlite>

Pour quitter l'interpréteur SQLITE

# sqlite> .quit

Pour afficher les tables de la base de données association.sqlite

# sqlite> .table
compromised_passwords_cache         membres
compromised_passwords_cache_ranges  membres_categories
compta_categories                   membres_operations
compta_comptes                      membres_sessions
compta_comptes_bancaires            plugins
compta_exercices                    plugins_signaux
compta_journal                      rappels
compta_moyens_paiement              rappels_envoyes
compta_projets                      recherches
compta_rapprochement                wiki_pages
config                              wiki_recherche
cotisations                         wiki_recherche_content
cotisations_membres                 wiki_recherche_docsize
fichiers                            wiki_recherche_segdir
fichiers_compta_journal             wiki_recherche_segments
fichiers_contenu                    wiki_recherche_stat
fichiers_membres                    wiki_revisions
fichiers_wiki_pages

Pour afficher le schéma de la base de données

# sqlite> .schema

CREATE TABLE config (
-- Configuration de Garradin
    cle TEXT PRIMARY KEY NOT NULL,
    valeur TEXT
);
CREATE TABLE membres_categories
-- Catégories de membres
(
    id INTEGER PRIMARY KEY NOT NULL,
    nom TEXT NOT NULL,

    droit_wiki INTEGER NOT NULL DEFAULT 1,
    droit_membres INTEGER NOT NULL DEFAULT 1,
    droit_compta INTEGER NOT NULL DEFAULT 1,
    droit_inscription INTEGER NOT NULL DEFAULT 0,
    droit_connexion INTEGER NOT NULL DEFAULT 1,
    droit_config INTEGER NOT NULL DEFAULT 0,
    cacher INTEGER NOT NULL DEFAULT 0,

    id_cotisation_obligatoire INTEGER NULL REFERENCES cotisations (id) ON DELETE SET NULL
);
CREATE TABLE membres_sessions
-- Sessions
(
    selecteur TEXT NOT NULL,
    hash TEXT NOT NULL,
    id_membre INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE,
    expire INT NOT NULL,

    PRIMARY KEY (selecteur, id_membre)
);
CREATE TABLE cotisations
-- Types de cotisations et activités
(
    id INTEGER PRIMARY KEY NOT NULL,
    id_categorie_compta INTEGER NULL REFERENCES compta_categories (id) ON DELETE SET NULL, -- NULL si le type n'est pas associé automatiquement à la compta

    intitule TEXT NOT NULL,
    description TEXT NULL,
    montant REAL NOT NULL,

    duree INTEGER NULL, -- En jours
    debut TEXT NULL, -- timestamp
    fin TEXT NULL
);
CREATE TABLE cotisations_membres
-- Enregistrement des cotisations et activités
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_membre INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE,
    id_cotisation INTEGER NOT NULL REFERENCES cotisations (id) ON DELETE CASCADE,

    date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date)
);
CREATE UNIQUE INDEX cm_unique ON cotisations_membres (id_membre, id_cotisation, date);
CREATE TABLE membres_operations
-- Liaison des enregistrement des paiements en compta
(
    id_membre INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE,
    id_operation INTEGER NOT NULL REFERENCES compta_journal (id) ON DELETE CASCADE,
    id_cotisation INTEGER NULL REFERENCES cotisations_membres (id) ON DELETE SET NULL,

    PRIMARY KEY (id_membre, id_operation)
);
CREATE TABLE rappels
-- Rappels de devoir renouveller une cotisation
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_cotisation INTEGER NOT NULL REFERENCES cotisations (id) ON DELETE CASCADE,

    delai INTEGER NOT NULL, -- Délai en jours pour envoyer le rappel

    sujet TEXT NOT NULL,
    texte TEXT NOT NULL
);
CREATE TABLE rappels_envoyes
-- Enregistrement des rappels envoyés à qui et quand
(
    id INTEGER NOT NULL PRIMARY KEY,

    id_membre INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE,
    id_cotisation INTEGER NOT NULL REFERENCES cotisations (id) ON DELETE CASCADE,
    id_rappel INTEGER NULL REFERENCES rappels (id) ON DELETE CASCADE,

    date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date),

    media INTEGER NOT NULL -- Média utilisé pour le rappel : 1 = email, 2 = courrier, 3 = autre
);
CREATE TABLE wiki_pages
-- Pages du wiki
(
    id INTEGER PRIMARY KEY NOT NULL,
    uri TEXT NOT NULL, -- URI unique (équivalent NomPageWiki)
    titre TEXT NOT NULL,
    date_creation TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(date_creation) IS NOT NULL AND datetime(date_creation) = date_creation),
    date_modification TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(date_modification) IS NOT NULL AND datetime(date_modification) = date_modification),
    parent INTEGER NOT NULL DEFAULT 0, -- ID de la page parent
    revision INTEGER NOT NULL DEFAULT 0, -- Numéro de révision (commence à 0 si pas de texte, +1 à chaque changement du texte)
    droit_lecture INTEGER NOT NULL DEFAULT 0, -- Accès en lecture (-1 = public [site web], 0 = tous ceux qui ont accès en lecture au wiki, 1+ = ID de groupe)
    droit_ecriture INTEGER NOT NULL DEFAULT 0 -- Accès en écriture (0 = tous ceux qui ont droit d'écriture sur le wiki, 1+ = ID de groupe)
);
CREATE UNIQUE INDEX wiki_uri ON wiki_pages (uri);
CREATE VIRTUAL TABLE wiki_recherche USING fts4
-- Table dupliquée pour chercher une page
(
    id INT PRIMARY KEY NOT NULL, -- Clé externe obligatoire
    titre TEXT NOT NULL,
    contenu TEXT NULL, -- Contenu de la dernière révision
    FOREIGN KEY (id) REFERENCES wiki_pages(id)
)
/* wiki_recherche(id,titre,contenu,"FOREIGN") */;
CREATE TABLE IF NOT EXISTS 'wiki_recherche_content'(docid INTEGER PRIMARY KEY, 'c0id', 'c1titre', 'c2contenu', 'c3FOREIGN');
CREATE TABLE IF NOT EXISTS 'wiki_recherche_segments'(blockid INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'wiki_recherche_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx));
CREATE TABLE IF NOT EXISTS 'wiki_recherche_docsize'(docid INTEGER PRIMARY KEY, size BLOB);
CREATE TABLE IF NOT EXISTS 'wiki_recherche_stat'(id INTEGER PRIMARY KEY, value BLOB);
CREATE TABLE wiki_revisions
-- Révisions du contenu des pages
(
    id_page INTEGER NOT NULL REFERENCES wiki_pages (id) ON DELETE CASCADE,
    revision INTEGER NULL,

    id_auteur INTEGER NULL REFERENCES membres (id) ON DELETE SET NULL,

    contenu TEXT NOT NULL,
    modification TEXT NULL, -- Description des modifications effectuées
    chiffrement INTEGER NOT NULL DEFAULT 0, -- 1 si le contenu est chiffré, 0 sinon
    date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(date) IS NOT NULL AND datetime(date) = date),

    PRIMARY KEY(id_page, revision)
);
CREATE INDEX wiki_revisions_id_page ON wiki_revisions (id_page);
CREATE INDEX wiki_revisions_id_auteur ON wiki_revisions (id_auteur);
CREATE TRIGGER wiki_recherche_delete AFTER DELETE ON wiki_pages
    BEGIN
        DELETE FROM wiki_recherche WHERE id = old.id;
    END;
CREATE TRIGGER wiki_recherche_update AFTER UPDATE OF id, titre ON wiki_pages
    BEGIN
        UPDATE wiki_recherche SET id = new.id, titre = new.titre WHERE id = old.id;
    END;
CREATE TRIGGER wiki_recherche_contenu_insert AFTER INSERT ON wiki_revisions WHEN new.chiffrement != 1
    BEGIN
        UPDATE wiki_recherche SET contenu = new.contenu WHERE id = new.id_page;
    END;
CREATE TRIGGER wiki_recherche_contenu_chiffre AFTER INSERT ON wiki_revisions WHEN new.chiffrement = 1
    BEGIN
        UPDATE wiki_recherche SET contenu = '' WHERE id = new.id_page;
    END;
CREATE TABLE compta_exercices
-- Exercices
(
    id INTEGER NOT NULL PRIMARY KEY,

    libelle TEXT NOT NULL,

    debut TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(debut) IS NOT NULL AND date(debut) = debut),
    fin TEXT NULL DEFAULT NULL CHECK (fin IS NULL OR (date(fin) IS NOT NULL AND date(fin) = fin)),

    cloture INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE compta_comptes
-- Plan comptable
(
    id TEXT NOT NULL PRIMARY KEY, -- peut contenir des lettres, eg. 53A, 53B, etc.
    parent TEXT NOT NULL DEFAULT 0,

    libelle TEXT NOT NULL,

    position INTEGER NOT NULL, -- position actif/passif/charge/produit
    plan_comptable INTEGER NOT NULL DEFAULT 1, -- 1 = fait partie du plan comptable, 0 = a été ajouté par l'utilisateur
    desactive INTEGER NOT NULL DEFAULT 0 -- 1 = compte historique désactivé
);
CREATE INDEX compta_comptes_parent ON compta_comptes (parent);
CREATE TABLE compta_comptes_bancaires
-- Comptes bancaires
(
    id TEXT NOT NULL PRIMARY KEY,

    banque TEXT NOT NULL,

    iban TEXT NULL,
    bic TEXT NULL,

    FOREIGN KEY(id) REFERENCES compta_comptes(id) ON DELETE CASCADE
);
CREATE TABLE compta_projets
-- Projets (compta analytique)
(
    id INTEGER PRIMARY KEY NOT NULL,

    libelle TEXT NOT NULL
);
CREATE TABLE compta_journal
-- Journal des opérations comptables
(
    id INTEGER PRIMARY KEY NOT NULL,

    libelle TEXT NOT NULL,
    remarques TEXT NULL,
    numero_piece TEXT NULL, -- N° de pièce comptable

    montant REAL NOT NULL,

    date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date),
    moyen_paiement TEXT NULL,
    numero_cheque TEXT NULL,

    compte_debit TEXT NULL, -- N° du compte dans le plan, NULL est utilisé pour une opération qui vient d'un exercice précédent
    compte_credit TEXT NULL, -- N° du compte dans le plan

    id_exercice INTEGER NULL DEFAULT NULL, -- En cas de compta simple, l'exercice est permanent (NULL)
    id_auteur INTEGER NULL,
    id_categorie INTEGER NULL, -- Numéro de catégorie (en mode simple)
    id_projet INTEGER NULL,

    FOREIGN KEY(moyen_paiement) REFERENCES compta_moyens_paiement(code),
    FOREIGN KEY(compte_debit) REFERENCES compta_comptes(id),
    FOREIGN KEY(compte_credit) REFERENCES compta_comptes(id),
    FOREIGN KEY(id_exercice) REFERENCES compta_exercices(id),
    FOREIGN KEY(id_auteur) REFERENCES membres(id) ON DELETE SET NULL,
    FOREIGN KEY(id_categorie) REFERENCES compta_categories(id) ON DELETE SET NULL,
    FOREIGN KEY(id_projet) REFERENCES compta_projets(id) ON DELETE SET NULL
);
CREATE INDEX compta_operations_exercice ON compta_journal (id_exercice);
CREATE INDEX compta_operations_date ON compta_journal (date);
CREATE INDEX compta_operations_comptes ON compta_journal (compte_debit, compte_credit);
CREATE INDEX compta_operations_auteur ON compta_journal (id_auteur);
CREATE TABLE compta_moyens_paiement
-- Moyens de paiement
(
    code TEXT NOT NULL PRIMARY KEY,
    nom TEXT NOT NULL
);
CREATE TABLE compta_categories
-- Catégories pour simplifier le plan comptable
(
    id INTEGER NOT NULL PRIMARY KEY,
    type INTEGER NOT NULL DEFAULT 1, -- 1 = recette, -1 = dépense, 0 = autre (utilisé uniquement pour l'interface)

    intitule TEXT NOT NULL,
    description TEXT NULL,

    compte TEXT NOT NULL, -- Compte affecté par cette catégorie

    FOREIGN KEY(compte) REFERENCES compta_comptes(id) ON DELETE CASCADE
);
CREATE TABLE plugins
(
    id TEXT NOT NULL PRIMARY KEY,
    officiel INTEGER NOT NULL DEFAULT 0,
    nom TEXT NOT NULL,
    description TEXT NULL,
    auteur TEXT NULL,
    url TEXT NULL,
    version TEXT NOT NULL,
    menu INTEGER NOT NULL DEFAULT 0,
    menu_condition TEXT NULL,
    config TEXT NULL
);
CREATE TABLE plugins_signaux
-- Association entre plugins et signaux (hooks)
(
    signal TEXT NOT NULL,
    plugin TEXT NOT NULL REFERENCES plugins (id),
    callback TEXT NOT NULL,
    PRIMARY KEY (signal, plugin)
);
CREATE TABLE compta_rapprochement
-- Rapprochement entre compta et relevés de comptes
(
    id_operation INTEGER NOT NULL PRIMARY KEY REFERENCES compta_journal (id) ON DELETE CASCADE,
    date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(date) IS NOT NULL AND datetime(date) = date),
    id_auteur INTEGER NULL REFERENCES membres (id) ON DELETE SET NULL
);
CREATE TABLE fichiers
-- Données sur les fichiers
(
    id INTEGER NOT NULL PRIMARY KEY,
    nom TEXT NOT NULL, -- nom de fichier (par exemple image1234.jpeg)
    type TEXT NULL, -- Type MIME
    image INTEGER NOT NULL DEFAULT 0, -- 1 = image reconnue
    datetime TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(datetime) IS NOT NULL AND datetime(datetime) = datetime), -- Date d'ajout ou mise à jour du fichier
    id_contenu INTEGER NOT NULL REFERENCES fichiers_contenu (id) ON DELETE CASCADE
);
CREATE INDEX fichiers_date ON fichiers (datetime);
CREATE TABLE fichiers_contenu
-- Contenu des fichiers
(
    id INTEGER NOT NULL PRIMARY KEY,
    hash TEXT NOT NULL, -- Hash SHA1 du contenu du fichier
    taille INTEGER NOT NULL, -- Taille en octets
    contenu BLOB NULL
);
CREATE UNIQUE INDEX fichiers_hash ON fichiers_contenu (hash);
CREATE TABLE fichiers_membres
-- Associations entre fichiers et membres (photo de profil par exemple)
(
    fichier INTEGER NOT NULL REFERENCES fichiers (id),
    id INTEGER NOT NULL REFERENCES membres (id),
    PRIMARY KEY(fichier, id)
);
CREATE TABLE fichiers_wiki_pages
-- Associations entre fichiers et pages du wiki
(
    fichier INTEGER NOT NULL REFERENCES fichiers (id),
    id INTEGER NOT NULL REFERENCES wiki_pages (id),
    PRIMARY KEY(fichier, id)
);
CREATE TABLE fichiers_compta_journal
-- Associations entre fichiers et journal de compta (pièce comptable par exemple)
(
    fichier INTEGER NOT NULL REFERENCES fichiers (id),
    id INTEGER NOT NULL REFERENCES compta_journal (id),
    PRIMARY KEY(fichier, id)
);
CREATE TABLE recherches
-- Recherches enregistrées
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_membre INTEGER NULL REFERENCES membres (id) ON DELETE CASCADE, -- Si non NULL, alors la recherche ne sera visible que par le membre associé
    intitule TEXT NOT NULL,
    creation TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(creation) IS NOT NULL AND datetime(creation) = creation),
    cible TEXT NOT NULL, -- "membres" ou "compta_journal"
    type TEXT NOT NULL, -- "json" ou "sql"
    contenu TEXT NOT NULL
);
CREATE TABLE compromised_passwords_cache
-- Cache des hash de mots de passe compromis
(
    hash TEXT NOT NULL PRIMARY KEY
);
CREATE TABLE compromised_passwords_cache_ranges
-- Cache des préfixes de mots de passe compromis
(
[root@swb2 public_html]# sqlite3 association.sqlite
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE config (
-- Configuration de Garradin
    cle TEXT PRIMARY KEY NOT NULL,
    valeur TEXT
);
CREATE TABLE membres_categories
-- Catégories de membres
(
    id INTEGER PRIMARY KEY NOT NULL,
    nom TEXT NOT NULL,

    droit_wiki INTEGER NOT NULL DEFAULT 1,
    droit_membres INTEGER NOT NULL DEFAULT 1,
    droit_compta INTEGER NOT NULL DEFAULT 1,
    droit_inscription INTEGER NOT NULL DEFAULT 0,
    droit_connexion INTEGER NOT NULL DEFAULT 1,
    droit_config INTEGER NOT NULL DEFAULT 0,
    cacher INTEGER NOT NULL DEFAULT 0,

    id_cotisation_obligatoire INTEGER NULL REFERENCES cotisations (id) ON DELETE SET NULL
);
CREATE TABLE membres_sessions
-- Sessions
(
    selecteur TEXT NOT NULL,
    hash TEXT NOT NULL,
    id_membre INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE,
    expire INT NOT NULL,

    PRIMARY KEY (selecteur, id_membre)
);
CREATE TABLE cotisations
-- Types de cotisations et activités
(
    id INTEGER PRIMARY KEY NOT NULL,
    id_categorie_compta INTEGER NULL REFERENCES compta_categories (id) ON DELETE SET NULL, -- NULL si le type n'est pas associé automatiquement à la compta

    intitule TEXT NOT NULL,
    description TEXT NULL,
    montant REAL NOT NULL,

    duree INTEGER NULL, -- En jours
    debut TEXT NULL, -- timestamp
    fin TEXT NULL
);
CREATE TABLE cotisations_membres
-- Enregistrement des cotisations et activités
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_membre INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE,
    id_cotisation INTEGER NOT NULL REFERENCES cotisations (id) ON DELETE CASCADE,

    date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date)
);
CREATE UNIQUE INDEX cm_unique ON cotisations_membres (id_membre, id_cotisation, date);
CREATE TABLE membres_operations
-- Liaison des enregistrement des paiements en compta
(
    id_membre INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE,
    id_operation INTEGER NOT NULL REFERENCES compta_journal (id) ON DELETE CASCADE,
    id_cotisation INTEGER NULL REFERENCES cotisations_membres (id) ON DELETE SET NULL,

    PRIMARY KEY (id_membre, id_operation)
);
CREATE TABLE rappels
-- Rappels de devoir renouveller une cotisation
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_cotisation INTEGER NOT NULL REFERENCES cotisations (id) ON DELETE CASCADE,

    delai INTEGER NOT NULL, -- Délai en jours pour envoyer le rappel

    sujet TEXT NOT NULL,
    texte TEXT NOT NULL
);
CREATE TABLE rappels_envoyes
-- Enregistrement des rappels envoyés à qui et quand
(
    id INTEGER NOT NULL PRIMARY KEY,

    id_membre INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE,
    id_cotisation INTEGER NOT NULL REFERENCES cotisations (id) ON DELETE CASCADE,
    id_rappel INTEGER NULL REFERENCES rappels (id) ON DELETE CASCADE,

    date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date),

    media INTEGER NOT NULL -- Média utilisé pour le rappel : 1 = email, 2 = courrier, 3 = autre
);
CREATE TABLE wiki_pages
-- Pages du wiki
(
    id INTEGER PRIMARY KEY NOT NULL,
    uri TEXT NOT NULL, -- URI unique (équivalent NomPageWiki)
    titre TEXT NOT NULL,
    date_creation TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(date_creation) IS NOT NULL AND datetime(date_creation) = date_creation),
    date_modification TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(date_modification) IS NOT NULL AND datetime(date_modification) = date_modification),
    parent INTEGER NOT NULL DEFAULT 0, -- ID de la page parent
    revision INTEGER NOT NULL DEFAULT 0, -- Numéro de révision (commence à 0 si pas de texte, +1 à chaque changement du texte)
    droit_lecture INTEGER NOT NULL DEFAULT 0, -- Accès en lecture (-1 = public [site web], 0 = tous ceux qui ont accès en lecture au wiki, 1+ = ID de groupe)
    droit_ecriture INTEGER NOT NULL DEFAULT 0 -- Accès en écriture (0 = tous ceux qui ont droit d'écriture sur le wiki, 1+ = ID de groupe)
);
CREATE UNIQUE INDEX wiki_uri ON wiki_pages (uri);
CREATE VIRTUAL TABLE wiki_recherche USING fts4
-- Table dupliquée pour chercher une page
(
    id INT PRIMARY KEY NOT NULL, -- Clé externe obligatoire
    titre TEXT NOT NULL,
    contenu TEXT NULL, -- Contenu de la dernière révision
    FOREIGN KEY (id) REFERENCES wiki_pages(id)
)
/* wiki_recherche(id,titre,contenu,"FOREIGN") */;
CREATE TABLE IF NOT EXISTS 'wiki_recherche_content'(docid INTEGER PRIMARY KEY, 'c0id', 'c1titre', 'c2contenu', 'c3FOREIGN');
CREATE TABLE IF NOT EXISTS 'wiki_recherche_segments'(blockid INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'wiki_recherche_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx));
CREATE TABLE IF NOT EXISTS 'wiki_recherche_docsize'(docid INTEGER PRIMARY KEY, size BLOB);
CREATE TABLE IF NOT EXISTS 'wiki_recherche_stat'(id INTEGER PRIMARY KEY, value BLOB);
CREATE TABLE wiki_revisions
-- Révisions du contenu des pages
(
    id_page INTEGER NOT NULL REFERENCES wiki_pages (id) ON DELETE CASCADE,
    revision INTEGER NULL,

    id_auteur INTEGER NULL REFERENCES membres (id) ON DELETE SET NULL,

    contenu TEXT NOT NULL,
    modification TEXT NULL, -- Description des modifications effectuées
    chiffrement INTEGER NOT NULL DEFAULT 0, -- 1 si le contenu est chiffré, 0 sinon
    date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(date) IS NOT NULL AND datetime(date) = date),

    PRIMARY KEY(id_page, revision)
);
CREATE INDEX wiki_revisions_id_page ON wiki_revisions (id_page);
CREATE INDEX wiki_revisions_id_auteur ON wiki_revisions (id_auteur);
CREATE TRIGGER wiki_recherche_delete AFTER DELETE ON wiki_pages
    BEGIN
        DELETE FROM wiki_recherche WHERE id = old.id;
    END;
CREATE TRIGGER wiki_recherche_update AFTER UPDATE OF id, titre ON wiki_pages
    BEGIN
        UPDATE wiki_recherche SET id = new.id, titre = new.titre WHERE id = old.id;
    END;
CREATE TRIGGER wiki_recherche_contenu_insert AFTER INSERT ON wiki_revisions WHEN new.chiffrement != 1
    BEGIN
        UPDATE wiki_recherche SET contenu = new.contenu WHERE id = new.id_page;
    END;
CREATE TRIGGER wiki_recherche_contenu_chiffre AFTER INSERT ON wiki_revisions WHEN new.chiffrement = 1
    BEGIN
        UPDATE wiki_recherche SET contenu = '' WHERE id = new.id_page;
    END;
CREATE TABLE compta_exercices
-- Exercices
(
    id INTEGER NOT NULL PRIMARY KEY,

    libelle TEXT NOT NULL,

    debut TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(debut) IS NOT NULL AND date(debut) = debut),
    fin TEXT NULL DEFAULT NULL CHECK (fin IS NULL OR (date(fin) IS NOT NULL AND date(fin) = fin)),

    cloture INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE compta_comptes
-- Plan comptable
(
    id TEXT NOT NULL PRIMARY KEY, -- peut contenir des lettres, eg. 53A, 53B, etc.
    parent TEXT NOT NULL DEFAULT 0,

    libelle TEXT NOT NULL,

    position INTEGER NOT NULL, -- position actif/passif/charge/produit
    plan_comptable INTEGER NOT NULL DEFAULT 1, -- 1 = fait partie du plan comptable, 0 = a été ajouté par l'utilisateur
    desactive INTEGER NOT NULL DEFAULT 0 -- 1 = compte historique désactivé
);
CREATE INDEX compta_comptes_parent ON compta_comptes (parent);
CREATE TABLE compta_comptes_bancaires
-- Comptes bancaires
(
    id TEXT NOT NULL PRIMARY KEY,

    banque TEXT NOT NULL,

    iban TEXT NULL,
    bic TEXT NULL,

    FOREIGN KEY(id) REFERENCES compta_comptes(id) ON DELETE CASCADE
);
CREATE TABLE compta_projets
-- Projets (compta analytique)
(
    id INTEGER PRIMARY KEY NOT NULL,

    libelle TEXT NOT NULL
);
CREATE TABLE compta_journal
-- Journal des opérations comptables
(
    id INTEGER PRIMARY KEY NOT NULL,

    libelle TEXT NOT NULL,
    remarques TEXT NULL,
    numero_piece TEXT NULL, -- N° de pièce comptable

    montant REAL NOT NULL,

    date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date),
    moyen_paiement TEXT NULL,
    numero_cheque TEXT NULL,

    compte_debit TEXT NULL, -- N° du compte dans le plan, NULL est utilisé pour une opération qui vient d'un exercice précédent
    compte_credit TEXT NULL, -- N° du compte dans le plan

    id_exercice INTEGER NULL DEFAULT NULL, -- En cas de compta simple, l'exercice est permanent (NULL)
    id_auteur INTEGER NULL,
    id_categorie INTEGER NULL, -- Numéro de catégorie (en mode simple)
    id_projet INTEGER NULL,

    FOREIGN KEY(moyen_paiement) REFERENCES compta_moyens_paiement(code),
    FOREIGN KEY(compte_debit) REFERENCES compta_comptes(id),
    FOREIGN KEY(compte_credit) REFERENCES compta_comptes(id),
    FOREIGN KEY(id_exercice) REFERENCES compta_exercices(id),
    FOREIGN KEY(id_auteur) REFERENCES membres(id) ON DELETE SET NULL,
    FOREIGN KEY(id_categorie) REFERENCES compta_categories(id) ON DELETE SET NULL,
    FOREIGN KEY(id_projet) REFERENCES compta_projets(id) ON DELETE SET NULL
);
CREATE INDEX compta_operations_exercice ON compta_journal (id_exercice);
CREATE INDEX compta_operations_date ON compta_journal (date);
CREATE INDEX compta_operations_comptes ON compta_journal (compte_debit, compte_credit);
CREATE INDEX compta_operations_auteur ON compta_journal (id_auteur);
CREATE TABLE compta_moyens_paiement
-- Moyens de paiement
(
    code TEXT NOT NULL PRIMARY KEY,
    nom TEXT NOT NULL
);
CREATE TABLE compta_categories
-- Catégories pour simplifier le plan comptable
(
    id INTEGER NOT NULL PRIMARY KEY,
    type INTEGER NOT NULL DEFAULT 1, -- 1 = recette, -1 = dépense, 0 = autre (utilisé uniquement pour l'interface)

    intitule TEXT NOT NULL,
    description TEXT NULL,

    compte TEXT NOT NULL, -- Compte affecté par cette catégorie

    FOREIGN KEY(compte) REFERENCES compta_comptes(id) ON DELETE CASCADE
);
CREATE TABLE plugins
(
    id TEXT NOT NULL PRIMARY KEY,
    officiel INTEGER NOT NULL DEFAULT 0,
    nom TEXT NOT NULL,
    description TEXT NULL,
    auteur TEXT NULL,
    url TEXT NULL,
    version TEXT NOT NULL,
    menu INTEGER NOT NULL DEFAULT 0,
    menu_condition TEXT NULL,
    config TEXT NULL
);
CREATE TABLE plugins_signaux
-- Association entre plugins et signaux (hooks)
(
    signal TEXT NOT NULL,
    plugin TEXT NOT NULL REFERENCES plugins (id),
    callback TEXT NOT NULL,
    PRIMARY KEY (signal, plugin)
);
CREATE TABLE compta_rapprochement
-- Rapprochement entre compta et relevés de comptes
(
    id_operation INTEGER NOT NULL PRIMARY KEY REFERENCES compta_journal (id) ON DELETE CASCADE,
    date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(date) IS NOT NULL AND datetime(date) = date),
    id_auteur INTEGER NULL REFERENCES membres (id) ON DELETE SET NULL
);
CREATE TABLE fichiers
-- Données sur les fichiers
(
    id INTEGER NOT NULL PRIMARY KEY,
    nom TEXT NOT NULL, -- nom de fichier (par exemple image1234.jpeg)
    type TEXT NULL, -- Type MIME
    image INTEGER NOT NULL DEFAULT 0, -- 1 = image reconnue
    datetime TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(datetime) IS NOT NULL AND datetime(datetime) = datetime), -- Date d'ajout ou mise à jour du fichier
    id_contenu INTEGER NOT NULL REFERENCES fichiers_contenu (id) ON DELETE CASCADE
);
CREATE INDEX fichiers_date ON fichiers (datetime);
CREATE TABLE fichiers_contenu
-- Contenu des fichiers
(
    id INTEGER NOT NULL PRIMARY KEY,
    hash TEXT NOT NULL, -- Hash SHA1 du contenu du fichier
    taille INTEGER NOT NULL, -- Taille en octets
    contenu BLOB NULL
);
CREATE UNIQUE INDEX fichiers_hash ON fichiers_contenu (hash);
CREATE TABLE fichiers_membres
-- Associations entre fichiers et membres (photo de profil par exemple)
(
    fichier INTEGER NOT NULL REFERENCES fichiers (id),
    id INTEGER NOT NULL REFERENCES membres (id),
    PRIMARY KEY(fichier, id)
);
CREATE TABLE fichiers_wiki_pages
-- Associations entre fichiers et pages du wiki
(
    fichier INTEGER NOT NULL REFERENCES fichiers (id),
    id INTEGER NOT NULL REFERENCES wiki_pages (id),
    PRIMARY KEY(fichier, id)
);
CREATE TABLE fichiers_compta_journal
-- Associations entre fichiers et journal de compta (pièce comptable par exemple)
(
    fichier INTEGER NOT NULL REFERENCES fichiers (id),
    id INTEGER NOT NULL REFERENCES compta_journal (id),
    PRIMARY KEY(fichier, id)
);
CREATE TABLE recherches
-- Recherches enregistrées
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_membre INTEGER NULL REFERENCES membres (id) ON DELETE CASCADE, -- Si non NULL, alors la recherche ne sera visible que par le membre associé
    intitule TEXT NOT NULL,
    creation TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(creation) IS NOT NULL AND datetime(creation) = creation),
    cible TEXT NOT NULL, -- "membres" ou "compta_journal"
    type TEXT NOT NULL, -- "json" ou "sql"
    contenu TEXT NOT NULL
);
CREATE TABLE compromised_passwords_cache
-- Cache des hash de mots de passe compromis
(
    hash TEXT NOT NULL PRIMARY KEY
);
CREATE TABLE compromised_passwords_cache_ranges
-- Cache des préfixes de mots de passe compromis
(
    prefix TEXT NOT NULL PRIMARY KEY,
    date INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS "membres" (
        id INTEGER PRIMARY KEY, -- Numéro attribué automatiquement
        id_categorie INTEGER NOT NULL,
        date_connexion TEXT NULL CHECK (date_connexion IS NULL OR datetime(date_connexion) = date_connexion), -- Date de dernière connexion
        date_inscription TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date_inscription) IS NOT NULL AND date(date_inscription) = date_inscription), -- Date d'inscription
        secret_otp TEXT NULL, -- Code secret pour TOTP
        clef_pgp TEXT NULL, -- Clé publique PGP
        "numero" INTEGER, -- Numéro de membre
        "nom" TEXT, -- Nom & prénom
        "email" TEXT, -- Adresse E-Mail
        "passe" TEXT,
        "adresse" TEXT, -- Adresse postale
        "code_postal" TEXT, -- Code postal
        "ville" TEXT, -- Ville
        "pays" TEXT, -- Pays
        "telephone" TEXT, -- Numéro de téléphone
        "lettre_infos" INTEGER, -- Inscription à la lettre d'information
        FOREIGN KEY (id_categorie) REFERENCES membres_categories (id)
);
CREATE INDEX membres_id_categorie ON membres (id_categorie);
CREATE UNIQUE INDEX membres_numero ON membres (numero);
CREATE INDEX membres_liste_numero ON membres (numero);
CREATE INDEX membres_liste_nom ON membres (nom);
CREATE INDEX membres_liste_code_postal ON membres (code_postal);
CREATE INDEX membres_liste_ville ON membres (ville);
CREATE UNIQUE INDEX membres_identifiant ON membres (email);

Pour afficher le contenu d'une table

# sqlite> select * from compta_categories;

Quelques requêtes SQL

# sqlite> SELECT DISTINCT <Champ_à_rechercher> FROM <Table_n1>
      -> WHERE <Champ_à_rechercher> IN (
      -> SELECT <Champ_à_rechercher> FROM <Table_n2>;

° - °° - °