Overview
Comment:Fix missing schema for upgrades from < 1.0.0
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: e26736bdec4a9ea53f2ee38bbb6f161fff251aea9a643196590fc4f68c8f0e15
User & Date: bohwaz on 2021-06-10 23:09:05
Other Links: manifest | tags
Context
2021-06-10
23:10
Fix bug [3747fb82b8b42ec6] trying to remove an index that does not exist check-in: 9714d59a06 user: bohwaz tags: trunk
23:09
Fix missing schema for upgrades from < 1.0.0 check-in: e26736bdec user: bohwaz tags: trunk
2021-06-07
17:01
Fix issues with out of sync files and web pages, and duplicate URIs in web pages check-in: cc5a0e1c1d user: bohwaz tags: trunk
Changes

Added src/include/data/1.0.0_schema.sql version [292ae06778].























































































































































































































































































































































































































1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
CREATE TABLE IF NOT EXISTS config (
-- Configuration de Garradin
    cle TEXT PRIMARY KEY NOT NULL,
    valeur TEXT
);

CREATE TABLE IF NOT EXISTS 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
);

-- Membres de l'asso
-- Table dynamique générée par l'application
-- voir Garradin\Membres\Champs.php

CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS services
-- Types de services (cotisations)
(
    id INTEGER PRIMARY KEY NOT NULL,

    label TEXT NOT NULL,
    description TEXT NULL,

    duration INTEGER NULL CHECK (duration IS NULL OR duration > 0), -- En jours
    start_date TEXT NULL CHECK (start_date IS NULL OR date(start_date) = start_date),
    end_date TEXT NULL CHECK (end_date IS NULL OR (date(end_date) = end_date AND date(end_date) >= date(start_date)))
);

CREATE TABLE IF NOT EXISTS services_fees
(
    id INTEGER PRIMARY KEY NOT NULL,

    label TEXT NOT NULL,
    description TEXT NULL,

    amount INTEGER NULL,
    formula TEXT NULL, -- Formule de calcul du montant de la cotisation, si cotisation dynamique (exemple : membres.revenu_imposable * 0.01)

    id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE,
    id_account INTEGER NULL REFERENCES acc_accounts (id) ON DELETE SET NULL CHECK (id_account IS NULL OR id_year IS NOT NULL), -- NULL si le type n'est pas associé automatiquement à la compta
    id_year INTEGER NULL REFERENCES acc_years (id) ON DELETE SET NULL -- NULL si le type n'est pas associé automatiquement à la compta
);

CREATE TABLE IF NOT EXISTS services_users
-- Enregistrement des cotisations et activités
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_user INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE,
    id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE,
    id_fee INTEGER NULL REFERENCES services_fees (id) ON DELETE CASCADE,

    paid INTEGER NOT NULL DEFAULT 0,
    expected_amount INTEGER NULL,

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

CREATE UNIQUE INDEX IF NOT EXISTS su_unique ON services_users (id_user, id_service, date);

CREATE INDEX IF NOT EXISTS su_service ON services_users (id_service);
CREATE INDEX IF NOT EXISTS su_fee ON services_users (id_fee);
CREATE INDEX IF NOT EXISTS su_paid ON services_users (paid);
CREATE INDEX IF NOT EXISTS su_expiry ON services_users (expiry_date);

CREATE TABLE IF NOT EXISTS services_reminders
-- Rappels de devoir renouveller une cotisation
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE,

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

    subject TEXT NOT NULL,
    body TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS services_reminders_sent
-- Enregistrement des rappels envoyés à qui et quand
(
    id INTEGER NOT NULL PRIMARY KEY,

    id_user INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE,
    id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE,
    id_reminder INTEGER NOT NULL REFERENCES services_reminders (id) ON DELETE CASCADE,

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

CREATE UNIQUE INDEX IF NOT EXISTS srs_index ON services_reminders_sent (id_user, id_service, id_reminder, date);

CREATE INDEX IF NOT EXISTS srs_reminder ON services_reminders_sent (id_reminder);
CREATE INDEX IF NOT EXISTS srs_user ON services_reminders_sent (id_user);

--
-- WIKI
--

CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS wiki_uri ON wiki_pages (uri);

CREATE VIRTUAL TABLE IF NOT EXISTS 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)
);

CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS wiki_revisions_id_page ON wiki_revisions (id_page);
CREATE INDEX IF NOT EXISTS wiki_revisions_id_auteur ON wiki_revisions (id_auteur);

-- Triggers pour synchro avec table wiki_pages
CREATE TRIGGER IF NOT EXISTS wiki_recherche_delete AFTER DELETE ON wiki_pages
    BEGIN
        DELETE FROM wiki_recherche WHERE id = old.id;
    END;

CREATE TRIGGER IF NOT EXISTS 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;

-- Trigger pour mettre à jour le contenu de la table de recherche lors d'une nouvelle révision
CREATE TRIGGER IF NOT EXISTS 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;

-- Si le contenu est chiffré, la recherche n'affiche pas de contenu
CREATE TRIGGER IF NOT EXISTS 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;

--
-- COMPTA
--

CREATE TABLE IF NOT EXISTS acc_charts
-- Plans comptables : il peut y en avoir plusieurs
(
    id INTEGER NOT NULL PRIMARY KEY,
    country TEXT NOT NULL,
    code TEXT NULL, -- NULL = plan comptable créé par l'utilisateur
    label TEXT NOT NULL,
    archived INTEGER NOT NULL DEFAULT 0 -- 1 = archivé, non-modifiable
);

CREATE TABLE IF NOT EXISTS acc_accounts
-- Comptes des plans comptables
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_chart INTEGER NOT NULL REFERENCES acc_charts ON DELETE CASCADE,

    code TEXT NOT NULL, -- peut contenir des lettres, eg. 53A, 53B, etc.

    label TEXT NOT NULL,
    description TEXT NULL,

    position INTEGER NOT NULL, -- position actif/passif/charge/produit
    type INTEGER NOT NULL DEFAULT 0, -- Type de compte spécial : banque, caisse, en attente d'encaissement, etc.
    user INTEGER NOT NULL DEFAULT 1 -- 1 = fait partie du plan comptable original, 0 = a été ajouté par l'utilisateur
);

CREATE UNIQUE INDEX IF NOT EXISTS acc_accounts_codes ON acc_accounts (code, id_chart);
CREATE INDEX IF NOT EXISTS acc_accounts_type ON acc_accounts (type);
CREATE INDEX IF NOT EXISTS acc_accounts_position ON acc_accounts (position);

CREATE TABLE IF NOT EXISTS acc_years
-- Exercices
(
    id INTEGER NOT NULL PRIMARY KEY,

    label TEXT NOT NULL,

    start_date TEXT NOT NULL CHECK (date(start_date) IS NOT NULL AND date(start_date) = start_date),
    end_date TEXT NOT NULL CHECK (date(end_date) IS NOT NULL AND date(end_date) = end_date),

    closed INTEGER NOT NULL DEFAULT 0,

    id_chart INTEGER NOT NULL REFERENCES acc_charts (id)
);

CREATE TRIGGER IF NOT EXISTS acc_years_delete BEFORE DELETE ON acc_years BEGIN
    UPDATE services_fees SET id_account = NULL, id_year = NULL WHERE id_year = OLD.id;
END;

CREATE INDEX IF NOT EXISTS acc_years_closed ON acc_years (closed);

CREATE TABLE IF NOT EXISTS acc_transactions
-- Opérations comptables
(
    id INTEGER PRIMARY KEY NOT NULL,

    type INTEGER NOT NULL DEFAULT 0, -- Type d'écriture, 0 = avancée (normale)
    status INTEGER NOT NULL DEFAULT 0, -- Statut (bitmask)

    label TEXT NOT NULL,
    notes TEXT NULL,
    reference TEXT NULL, -- N° de pièce comptable

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

    validated INTEGER NOT NULL DEFAULT 0, -- 1 = écriture validée, non modifiable

    hash TEXT NULL,
    prev_hash TEXT NULL,

    id_year INTEGER NOT NULL REFERENCES acc_years(id),
    id_creator INTEGER NULL REFERENCES membres(id) ON DELETE SET NULL,
    id_related INTEGER NULL REFERENCES acc_transactions(id) ON DELETE SET NULL -- écriture liée (par ex. remboursement d'une dette)
);

CREATE INDEX IF NOT EXISTS acc_transactions_year ON acc_transactions (id_year);
CREATE INDEX IF NOT EXISTS acc_transactions_date ON acc_transactions (date);
CREATE INDEX IF NOT EXISTS acc_transactions_related ON acc_transactions (id_related);
CREATE INDEX IF NOT EXISTS acc_transactions_type ON acc_transactions (type, id_year);
CREATE INDEX IF NOT EXISTS acc_transactions_status ON acc_transactions (status);

CREATE TABLE IF NOT EXISTS acc_transactions_lines
-- Lignes d'écritures d'une opération
(
    id INTEGER PRIMARY KEY NOT NULL,

    id_transaction INTEGER NOT NULL REFERENCES acc_transactions (id) ON DELETE CASCADE,
    id_account INTEGER NOT NULL REFERENCES acc_accounts (id), -- N° du compte dans le plan comptable

    credit INTEGER NOT NULL,
    debit INTEGER NOT NULL,

    reference TEXT NULL, -- Référence de paiement, eg. numéro de chèque
    label TEXT NULL,

    reconciled INTEGER NOT NULL DEFAULT 0,

    id_analytical INTEGER NULL REFERENCES acc_accounts(id) ON DELETE SET NULL,

    CONSTRAINT line_check1 CHECK ((credit * debit) = 0),
    CONSTRAINT line_check2 CHECK ((credit + debit) > 0)
);

CREATE INDEX IF NOT EXISTS acc_transactions_lines_transaction ON acc_transactions_lines (id_transaction);
CREATE INDEX IF NOT EXISTS acc_transactions_lines_account ON acc_transactions_lines (id_account);
CREATE INDEX IF NOT EXISTS acc_transactions_lines_analytical ON acc_transactions_lines (id_analytical);
CREATE INDEX IF NOT EXISTS acc_transactions_lines_reconciled ON acc_transactions_lines (reconciled);

CREATE TABLE IF NOT EXISTS acc_transactions_users
-- Liaison des écritures et des membres
(
    id_user INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE,
    id_transaction INTEGER NOT NULL REFERENCES acc_transactions (id) ON DELETE CASCADE,
    id_service_user INTEGER NULL REFERENCES services_users (id) ON DELETE SET NULL,

    PRIMARY KEY (id_user, id_transaction)
);

CREATE INDEX IF NOT EXISTS acc_transactions_users_service ON acc_transactions_users (id_service_user);

CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS 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 IF NOT EXISTS 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 IF NOT EXISTS fichiers_date ON fichiers (datetime);

CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS fichiers_hash ON fichiers_contenu (hash);

CREATE TABLE IF NOT EXISTS fichiers_membres
-- Associations entre fichiers et membres (photo de profil par exemple)
(
    fichier INTEGER NOT NULL REFERENCES fichiers (id) ON DELETE CASCADE,
    id INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE,
    PRIMARY KEY(fichier, id)
);

CREATE TABLE IF NOT EXISTS fichiers_wiki_pages
-- Associations entre fichiers et pages du wiki
(
    fichier INTEGER NOT NULL REFERENCES fichiers (id) ON DELETE CASCADE,
    id INTEGER NOT NULL REFERENCES wiki_pages (id) ON DELETE CASCADE,
    PRIMARY KEY(fichier, id)
);

CREATE TABLE IF NOT EXISTS fichiers_acc_transactions
-- Associations entre fichiers et journal de compta (pièce comptable par exemple)
(
    fichier INTEGER NOT NULL REFERENCES fichiers (id) ON DELETE CASCADE,
    id INTEGER NOT NULL REFERENCES acc_transactions (id) ON DELETE CASCADE,
    PRIMARY KEY(fichier, id)
);

CREATE TABLE IF NOT EXISTS 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"
    type TEXT NOT NULL, -- "json" ou "sql"
    contenu TEXT NOT NULL
);


CREATE TABLE IF NOT EXISTS compromised_passwords_cache
-- Cache des hash de mots de passe compromis
(
    hash TEXT NOT NULL PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS compromised_passwords_cache_ranges
-- Cache des préfixes de mots de passe compromis
(
    prefix TEXT NOT NULL PRIMARY KEY,
    date INTEGER NOT NULL
);