1 PRAGMA foreign_keys = ON;
4 * Utilisateur ayant un compte sur l'application
7 id INTEGER PRIMARY KEY AUTOINCREMENT,
8 lastname VARCHAR(32) NOT NULL COLLATE NOCASE,
9 firstname VARCHAR(32) NOT NULL COLLATE NOCASE,
11 email VARCHAR(32) NOT NULL UNIQUE COLLATE NOCASE,
12 UNIQUE (lastname, firstname, birth)
15 CREATE INDEX idx_user_id ON user(id);
16 CREATE INDEX idx_user_unique_triptyque ON user(lastname, firstname, birth);
17 CREATE INDEX idx_user_email ON user(email);
18 CREATE INDEX idx_user_all ON user(id, lastname, firstname, birth, email);
21 * Gestion du carnet de contacts entre utilisateurs
24 CREATE TABLE contact (
25 id_user_owner INTEGER ASC REFERENCES user(id),
26 id_user_contact INTEGER ASC REFERENCES user(id),
27 PRIMARY KEY (id_user_owner, id_user_contact)
30 CREATE INDEX idx_contact ON contact(id_user_owner, id_user_contact);
33 * Gallerie de photos (pas de workflow associé)
35 CREATE TABLE gallery (
36 id INTEGER PRIMARY KEY AUTOINCREMENT,
37 id_user INTEGER REFERENCES user(id),
38 name VARCHAR(32) NOT NULL UNIQUE COLLATE NOCASE,
39 description TEXT NOT NULL,
40 created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
41 updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
44 CREATE INDEX idx_gallery_id ON gallery(id);
45 CREATE INDEX idx_gallery_name ON gallery(name);
46 CREATE INDEX idx_gallery_user_fk ON gallery(id_user);
47 CREATE INDEX idx_gallery_all ON gallery(id, id_user, name, description, created, updated);
50 * Element de gallerie : photographie
51 * Plusieurs photographies sont stockées sur le disque dur, aux formats:
52 * - original (format de l'appareil photo)
53 * - écran (format d'affichage de la photo à l'écran)
54 * - miniature (format d'affichage pour les miniatures de la gallerie)
57 id INTEGER PRIMARY KEY AUTOINCREMENT,
58 id_gallery INTEGER REFERENCES gallery(id),
59 name VARCHAR(16) NOT NULL UNIQUE COLLATE RTRIM,
60 description TEXT NOT NULL,
61 filename VARCHAR(12) NOT NULL COLLATE NOCASE,
62 extension VARCHAR(4) NOT NULL COLLATE NOCASE CHECK (extension IN ('jpg', 'png')),
63 created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
64 updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
67 CREATE INDEX idx_photo_id ON photo(id);
68 CREATE INDEX idx_photo_name ON photo(name);
69 CREATE INDEX idx_photo_gallery_fk ON photo(id_gallery);
70 CREATE INDEX idx_photo_all ON photo(id, id_gallery, name, description, filename, extension, created, updated);
73 * Commande qui dispose d'un workflow:
74 * - le propriétaire de la gallerie ouvre une commande
75 * - tant que la commande est ouverte, les contacts du propriétaire peuvent demander des exemplaire de chaque photo
76 * - après une certaine période, la commande passé au statut en attente; les photos sont en cours de développement
77 * - lorsque la commande est reçue, elle est alors fermée
78 * - tous les contacts du propriétaires passent donc commande en même temps
79 * - le propriétaire peut réouvrir au besoin une nouvelle commande
81 CREATE TABLE order_form (
82 id INTEGER PRIMARY KEY AUTOINCREMENT,
83 id_gallery UNSIGNED SMALLINT REFERENCES gallery (id),
84 status VARCHAR(4) NOT NULL COLLATE NOCASE CHECK (status IN ('open', 'wait', 'end')) DEFAULT 'open',
85 created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
86 closed TIMESTAMP NOT NULL DEFAULT (datetime('now', '+1 month')),
87 ended TIMESTAMP NULL DEFAULT NULL
90 CREATE INDEX idx_order_form_id ON order_form(id);
91 CREATE INDEX idx_order_form_gallery_fk ON order_form(id_gallery);
92 CREATE INDEX idx_order_form_all ON order_form(id, id_gallery, status, created, closed, ended);
95 * élément de commande :
96 * - il n'est pas possible de créer un tel élément lorsque la commande n'est pas ouverte
97 * - il n'est pas possible de passer commande si l'on ne fait pas parti des contacts du propriétaire
99 CREATE TABLE order_element (
100 id_order_form INTEGER REFERENCES order_form(id),
101 id_user INTEGER REFERENCES user(id),
102 id_photo INTEGER REFERENCES photo(id),
103 quantity UNSIGNED SMALLINT DEFAULT 1,
104 PRIMARY KEY (id_user, id_order_form, id_photo)
107 CREATE INDEX idx_order_element_primary ON order_element(id_order_form, id_user, id_photo);
108 CREATE INDEX idx_order_element_all ON order_element(id_order_form, id_user, id_photo, quantity);
111 * à chaque fois que les propriétés d'une gallerie sont modifiées, le champs contenant la date de mise à jour l'est également
113 CREATE TRIGGER gallery_update_management AFTER UPDATE ON gallery
115 UPDATE gallery SET updated = datetime('now') WHERE id = old.id;
119 * à chaque fois que les propriétés d'une photographie sont modifiées, le champs contenant la date de mise à jour l'est également ainsi que celui de la gallerie liée
121 CREATE TRIGGER photo_update_management AFTER UPDATE ON photo
123 UPDATE gallery SET updated = datetime('now') WHERE id = old.id_gallery;
124 UPDATE photo SET updated = datetime('now') WHERE id = old.id;
128 * à chaque fois qu'une photographie est rajoutée, le champs contenant la date de mise la gallerie liée est mis à jour
130 CREATE TRIGGER photo_insert_management AFTER UPDATE ON photo
132 UPDATE gallery SET updated = datetime('now') WHERE id = old.id_gallery;
136 * Gestion du workflow sur une commande: mise à jour de la date de fermeture au changement de statut open vers wait
138 CREATE TRIGGER order_form_update_management_closed AFTER UPDATE OF status ON order_form WHEN old.status='open' AND new.status='wait'
140 UPDATE order_form SET closed = datetime('now') WHERE id = old.id;
144 * Gestion du workflow sur une commande: mise à jour de la date de fermeture au changement de statut wait vers end
146 CREATE TRIGGER order_form_update_management_ended AFTER UPDATE OF status ON order_form WHEN old.status='wait' AND new.status='end'
148 UPDATE order_form SET ended = datetime('now') WHERE id = old.id;
152 * Gestion du workflow sur une commande: il doit être impossible d'aller en arrière
154 CREATE TRIGGER order_form_update_management_workflow BEFORE UPDATE OF status ON order_form
157 WHEN (old.status='wait' AND new.status='open')
158 OR (old.status='end' AND new.status<>'end')
159 THEN RAISE(ABORT, 'new status do not respect workflow')
164 * Gestion d'un élément de commande: il doit être impossible de modifier uns des clés primaires
166 CREATE TRIGGER order_element_update_management_workflow BEFORE UPDATE OF status ON order_element
169 WHEN old.id_order_form <> new.id_order_form
170 OR old.id_user <> new.id_user
171 OR old.id_photo <> new.id_photo
172 THEN RAISE(ABORT, 'bad usage of order element')
177 * Gestion du workflow sur un élément de commande: il doit être impossible de modifier un élément de commande si la commande n'est pas ouverte
179 CREATE TRIGGER order_element_update_management_when_closed BEFORE UPDATE ON order_element
182 WHEN ((SELECT order_form.id FROM order_form WHERE order_form.id=old.id_order_form AND order_form.status='open') IS NULL)
183 THEN RAISE(ABORT, 'try to update element in not opened order form')
188 * Gestion du workflow sur un élément de commande: il doit être impossible de rajouter un élément de commande si la commande n'est pas ouverte
190 CREATE TRIGGER order_element_insert_management_when_closed BEFORE INSERT ON order_element
193 WHEN ((SELECT order_form.id FROM order_form WHERE order_form.id=new.id_order_form AND order_form.status='open') IS NULL)
194 THEN RAISE(ABORT, 'try to insert element in not opened order form')
199 * Gestion du contraintes sur un élément de commande: un utilisateur ne faisant pas partie des contacts du propriétaire de la gallerie concernée par la commande ne doit pas pouvoir créer un élément de commande.
201 CREATE TRIGGER order_element_insert_management_check_user_is_authored BEFORE INSERT ON order_element
206 SELECT contact.id_user_contact
208 JOIN gallery ON gallery.id_user=contact.id_user_owner
209 JOIN order_form ON order_form.id_gallery=gallery.id
210 WHERE (contact.id_user_contact=new.id_user OR contact.id_user_owner=new.id_user)
211 AND order_form.id=new.id_order_form
214 THEN RAISE(ABORT, 'unauthorized user is ordering')