include/data/schema.sql

   1 CREATE TABLE config (
   2 -- Configuration de Garradin
   3     cle TEXT PRIMARY KEY,
   4     valeur TEXT
   5 );
   6
   7 -- On stocke ici les ID de catégorie de compta correspondant aux types spéciaux
   8 -- compta_categorie_cotisations => id_categorie
   9 -- compta_categorie_dons => id_categorie
  10
  11 CREATE TABLE membres_categories
  12 -- Catégories de membres
  13 (
  14     id INTEGER PRIMARY KEY,
  15     nom TEXT,
  16     description TEXT,
  17
  18     droit_wiki INT DEFAULT 1,
  19     droit_membres INT DEFAULT 1,
  20     droit_compta INT DEFAULT 1,
  21     droit_inscription INT DEFAULT 0,
  22     droit_connexion INT DEFAULT 1,
  23     droit_config INT DEFAULT 0,
  24     cacher INT DEFAULT 0,
  25
  26     id_cotisation_obligatoire INTEGER NULL REFERENCES cotisations (id)
  27 );
  28
  29 -- Membres de l'asso
  30 -- Table dynamique générée par l'application
  31 -- voir class.champs_membres.php
  32
  33 CREATE TABLE cotisations
  34 -- Types de cotisations et activités
  35 (
  36     id INTEGER PRIMARY KEY,
  37     id_categorie_compta INTEGER NULL, -- NULL si le type n'est pas associé automatiquement à la compta
  38
  39     intitule TEXT NOT NULL,
  40     description TEXT NULL,
  41     montant REAL NOT NULL,
  42
  43     duree INTEGER NULL, -- En jours
  44     debut TEXT NULL, -- timestamp
  45     fin TEXT NULL,
  46
  47     FOREIGN KEY (id_categorie_compta) REFERENCES compta_categories (id)
  48 );
  49
  50 CREATE TABLE cotisations_membres
  51 -- Enregistrement des cotisations et activités
  52 (
  53     id INTEGER NOT NULL PRIMARY KEY,
  54     id_membre INTEGER NOT NULL REFERENCES membres (id),
  55     id_cotisation INTEGER NOT NULL REFERENCES cotisations (id),
  56
  57     date TEXT NOT NULL DEFAULT CURRENT_DATE
  58 );
  59
  60 CREATE UNIQUE INDEX cm_unique ON cotisations_membres (id_membre, id_cotisation, date);
  61
  62 CREATE TABLE membres_operations
  63 -- Liaision des enregistrement des paiements en compta
  64 (
  65     id_membre INTEGER NOT NULL REFERENCES membres (id),
  66     id_operation INTEGER NOT NULL REFERENCES compta_journal (id),
  67     id_cotisation INTEGER NULL REFERENCES cotisations_membres (id),
  68
  69     PRIMARY KEY (id_membre, id_operation)
  70 );
  71
  72 CREATE TABLE rappels
  73 -- Rappels de devoir renouveller une cotisation
  74 (
  75     id INTEGER PRIMARY KEY,
  76     id_cotisation INTEGER NOT NULL REFERENCES cotisations (id),
  77
  78     delai INTEGER NOT NULL, -- Délai en jours pour envoyer le rappel
  79
  80     sujet TEXT NOT NULL,
  81     texte TEXT NOT NULL
  82 );
  83
  84 CREATE TABLE rappels_envoyes
  85 -- Enregistrement des rappels envoyés à qui et quand
  86 (
  87     id INTEGER PRIMARY KEY,
  88
  89     id_membre INTEGER NOT NULL REFERENCES membres (id),
  90     id_cotisation INTEGER NOT NULL REFERENCES cotisations (id),
  91
  92     date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  93
  94     media INTEGER NOT NULL -- Média utilisé pour le rappel : 1 = email, 2 = courrier, 3 = autre
  95 );
  96
  97 --
  98 -- WIKI
  99 --
 100
 101 CREATE TABLE wiki_pages
 102 -- Pages du wiki
 103 (
 104     id INTEGER PRIMARY KEY,
 105     uri TEXT, -- URI unique (équivalent NomPageWiki)
 106     titre TEXT,
 107     date_creation TEXT DEFAULT CURRENT_TIMESTAMP,
 108     date_modification TEXT DEFAULT CURRENT_TIMESTAMP,
 109     parent INTEGER DEFAULT 0, -- ID de la page parent
 110     revision INTEGER DEFAULT 0, -- Numéro de révision (commence à 0 si pas de texte, +1 à chaque changement du texte)
 111     droit_lecture INTEGER 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)
 112     droit_ecriture INTEGER DEFAULT 0 -- Accès en écriture (0 = tous ceux qui ont droit d'écriture sur le wiki, 1+ = ID de groupe)
 113 );
 114
 115 CREATE UNIQUE INDEX wiki_uri ON wiki_pages (uri);
 116
 117 CREATE VIRTUAL TABLE wiki_recherche USING fts4
 118 -- Table dupliquée pour chercher une page
 119 (
 120     id INT PRIMARY KEY NOT NULL, -- Clé externe obligatoire
 121     titre TEXT,
 122     contenu TEXT, -- Contenu de la dernière révision
 123     FOREIGN KEY (id) REFERENCES wiki_pages(id)
 124 );
 125
 126 CREATE TABLE wiki_revisions
 127 -- Révisions du contenu des pages
 128 (
 129     id_page INTEGER NOT NULL,
 130     revision INTEGER,
 131
 132     id_auteur INTEGER,
 133
 134     contenu TEXT,
 135     modification TEXT, -- Description des modifications effectuées
 136     chiffrement INTEGER DEFAULT 0, -- 1 si le contenu est chiffré, 0 sinon
 137     date TEXT DEFAULT CURRENT_TIMESTAMP,
 138
 139     PRIMARY KEY(id_page, revision),
 140     FOREIGN KEY (id_page) REFERENCES wiki_pages (id), -- Clé externe obligatoire
 141     FOREIGN KEY (id_auteur) REFERENCES membres (id)  -- Clé externe non-obligatoire (peut être supprimée après en cas de suppression de membre)
 142 );
 143
 144 CREATE INDEX wiki_revisions_id_page ON wiki_revisions (id_page);
 145 CREATE INDEX wiki_revisions_id_auteur ON wiki_revisions (id_auteur);
 146
 147 -- Triggers pour synchro avec table wiki_pages
 148 CREATE TRIGGER wiki_recherche_delete AFTER DELETE ON wiki_pages
 149     BEGIN
 150         DELETE FROM wiki_recherche WHERE id = old.id;
 151     END;
 152
 153 CREATE TRIGGER wiki_recherche_update AFTER UPDATE OF id, titre ON wiki_pages
 154     BEGIN
 155         UPDATE wiki_recherche SET id = new.id, titre = new.titre WHERE id = old.id;
 156     END;
 157
 158 -- Trigger pour mettre à jour le contenu de la table de recherche lors d'une nouvelle révision
 159 CREATE TRIGGER wiki_recherche_contenu_insert AFTER INSERT ON wiki_revisions WHEN new.chiffrement != 1
 160     BEGIN
 161         UPDATE wiki_recherche SET contenu = new.contenu WHERE id = new.id_page;
 162     END;
 163
 164 -- Si le contenu est chiffré, la recherche n'affiche pas de contenu
 165 CREATE TRIGGER wiki_recherche_contenu_chiffre AFTER INSERT ON wiki_revisions WHEN new.chiffrement = 1
 166     BEGIN
 167         UPDATE wiki_recherche SET contenu = '' WHERE id = new.id_page;
 168     END;
 169
 170 /*
 171 CREATE TABLE wiki_fichiers (
 172     id INTEGER PRIMARY KEY,
 173     id_page INTEGER NOT NULL,
 174     nom TEXT,
 175     hash TEXT,
 176
 177     FOREIGN KEY (id_page) REFERENCES wiki_pages (id) -- Clé externe obligatoire
 178 );
 179
 180 CREATE INDEX wiki_fichiers_id_page ON wiki_fichiers (id_page);
 181
 182 CREATE TABLE wiki_suivi
 183 -- Suivi des pages
 184 (
 185     id_membre INTEGER NOT NULL,
 186     id_page INTEGER NOT NULL,
 187
 188     PRIMARY KEY (id_membre, id_page),
 189
 190     FOREIGN KEY (id_page) REFERENCES wiki_pages (id), -- Clé externe obligatoire
 191     FOREIGN KEY (id_membre) REFERENCES membres (id) -- Clé externe obligatoire
 192 );
 193 */
 194
 195 --
 196 -- COMPTA
 197 --
 198
 199 CREATE TABLE compta_exercices
 200 -- Exercices
 201 (
 202     id INTEGER PRIMARY KEY,
 203
 204     libelle TEXT NOT NULL,
 205
 206     debut TEXT NOT NULL DEFAULT CURRENT_DATE,
 207     fin TEXT NULL DEFAULT NULL,
 208
 209     cloture INTEGER NOT NULL DEFAULT 0
 210 );
 211
 212
 213 CREATE TABLE compta_comptes
 214 -- Plan comptable
 215 (
 216     id TEXT PRIMARY KEY, -- peut contenir des lettres, eg. 53A, 53B, etc.
 217     parent TEXT NOT NULL DEFAULT 0,
 218
 219     libelle TEXT NOT NULL,
 220
 221     position INTEGER NOT NULL, -- position actif/passif/charge/produit
 222     plan_comptable INTEGER NOT NULL DEFAULT 1, -- 1 = fait partie du plan comptable, 0 = a été ajouté par l'utilisateur
 223     desactive INTEGER NOT NULL DEFAULT 0 -- 1 = compte historique désactivé
 224 );
 225
 226 CREATE INDEX compta_comptes_parent ON compta_comptes (parent);
 227
 228 CREATE TABLE compta_comptes_bancaires
 229 -- Comptes bancaires
 230 (
 231     id TEXT PRIMARY KEY,
 232
 233     banque TEXT NOT NULL,
 234
 235     iban TEXT,
 236     bic TEXT,
 237
 238     FOREIGN KEY(id) REFERENCES compta_comptes(id)
 239 );
 240
 241 CREATE TABLE compta_journal
 242 -- Journal des opérations comptables
 243 (
 244     id INTEGER PRIMARY KEY,
 245
 246     libelle TEXT NOT NULL,
 247     remarques TEXT,
 248     numero_piece TEXT, -- N° de pièce comptable
 249
 250     date TEXT DEFAULT CURRENT_DATE,
 251     moyen_paiement TEXT DEFAULT NULL,
 252     numero_cheque TEXT DEFAULT NULL,
 253
 254     id_exercice INTEGER NULL DEFAULT NULL, -- En cas de compta simple, l'exercice est permanent (NULL)
 255     id_auteur INTEGER NULL,
 256     id_categorie INTEGER NULL, -- Numéro de catégorie (en mode simple)
 257
 258     FOREIGN KEY(moyen_paiement) REFERENCES compta_moyens_paiement(code),
 259     FOREIGN KEY(id_exercice) REFERENCES compta_exercices(id),
 260     FOREIGN KEY(id_auteur) REFERENCES membres(id),
 261     FOREIGN KEY(id_categorie) REFERENCES compta_categories(id)
 262 );
 263
 264 CREATE TABLE compta_flux
 265 (
 266     id_journal INTEGER,
 267
 268     compte TEXT, -- N° du compte dans le plan
 269     montant REAL,
 270
 271     FOREIGN KEY(id_journal) REFERENCES compta_journal(id) ON DELETE CASCADE,
 272     FOREIGN KEY(compte) REFERENCES compta_comptes(id) ON DELETE CASCADE
 273 );
 274
 275 CREATE INDEX compta_operations_exercice ON compta_journal (id_exercice);
 276 CREATE INDEX compta_operations_date ON compta_journal (date);
 277 --CREATE INDEX compta_operations_comptes ON compta_journal (compte_debit, compte_credit);
 278 CREATE INDEX compta_operations_auteur ON compta_journal (id_auteur);
 279
 280 CREATE TABLE compta_moyens_paiement
 281 -- Moyens de paiement
 282 (
 283     code TEXT PRIMARY KEY,
 284     nom TEXT
 285 );
 286
 287 --INSERT INTO compta_moyens_paiement (code, nom) VALUES ('AU', 'Autre');
 288 INSERT INTO compta_moyens_paiement (code, nom) VALUES ('CB', 'Carte bleue');
 289 INSERT INTO compta_moyens_paiement (code, nom) VALUES ('CH', 'Chèque');
 290 INSERT INTO compta_moyens_paiement (code, nom) VALUES ('ES', 'Espèces');
 291 INSERT INTO compta_moyens_paiement (code, nom) VALUES ('PR', 'Prélèvement');
 292 INSERT INTO compta_moyens_paiement (code, nom) VALUES ('TI', 'TIP');
 293 INSERT INTO compta_moyens_paiement (code, nom) VALUES ('VI', 'Virement');
 294
 295 CREATE TABLE compta_categories
 296 -- Catégories pour simplifier le plan comptable
 297 (
 298     id INTEGER PRIMARY KEY,
 299     type INTEGER DEFAULT 1, -- 1 = recette, -1 = dépense, 0 = autre (utilisé uniquement pour l'interface)
 300
 301     intitule TEXT NOT NULL,
 302     description TEXT,
 303
 304     compte TEXT NOT NULL, -- Compte affecté par cette catégorie
 305
 306     FOREIGN KEY(compte) REFERENCES compta_comptes(id)
 307 );
 308
 309 CREATE TABLE plugins
 310 (
 311     id TEXT PRIMARY KEY,
 312     officiel INTEGER NOT NULL DEFAULT 0,
 313     nom TEXT NOT NULL,
 314     description TEXT,
 315     auteur TEXT,
 316     url TEXT,
 317     version TEXT NOT NULL,
 318     menu INTEGER NOT NULL DEFAULT 0,
 319     config TEXT
 320 );