data/scripts/schema.sql

   1 PRAGMA foreign_keys = ON;
   2
   3 /*
   4  * Utilisateur ayant un compte sur l'application
   5  */
   6 CREATE TABLE user (
   7     id INTEGER PRIMARY KEY AUTOINCREMENT,
   8         lastname VARCHAR(32) NOT NULL COLLATE NOCASE,
   9         firstname VARCHAR(32) NOT NULL COLLATE NOCASE,
  10         birth DATE NOT NULL,
  11         email VARCHAR(32) NOT NULL UNIQUE COLLATE NOCASE,
  12         UNIQUE (lastname, firstname, birth)
  13 );
  14
  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);
  19
  20 /*
  21  * Gestion du carnet de contacts entre utilisateurs
  22  * Non réciproque
  23  */
  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)
  28 );
  29
  30 CREATE INDEX idx_contact ON contact(id_user_owner, id_user_contact);
  31
  32 /*
  33  * Gallerie de photos (pas de workflow associé)
  34  */
  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
  42 );
  43
  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);
  48
  49 /*
  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)
  55  */
  56 CREATE TABLE photo (
  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
  65 );
  66
  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);
  71
  72 /*
  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
  80  */
  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
  88 );
  89
  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);
  93
  94 /*
  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
  98  */
  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)
 105 );
 106
 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);
 109
 110 /*
 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
 112  */
 113 CREATE TRIGGER gallery_update_management AFTER UPDATE ON gallery
 114 BEGIN
 115     UPDATE gallery SET updated = datetime('now') WHERE id = old.id;
 116 END;
 117
 118 /*
 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
 120  */
 121 CREATE TRIGGER photo_update_management AFTER UPDATE ON photo
 122 BEGIN
 123     UPDATE gallery SET updated = datetime('now') WHERE id = old.id_gallery;
 124     UPDATE photo SET updated = datetime('now') WHERE id = old.id;
 125 END;
 126
 127 /*
 128  * Gestion du workflow sur une commande: mise à jour de la date de fermeture au changement de statut open vers wait
 129  */
 130 CREATE TRIGGER order_form_update_management_closed AFTER UPDATE OF status ON order_form WHEN old.status='open' AND new.status='wait'
 131 BEGIN
 132     UPDATE order_form SET closed = datetime('now') WHERE id = old.id;
 133 END;
 134
 135 /*
 136  * Gestion du workflow sur une commande: mise à jour de la date de fermeture au changement de statut wait vers end
 137  */
 138 CREATE TRIGGER order_form_update_management_ended AFTER UPDATE OF status ON order_form WHEN old.status='wait' AND new.status='end'
 139 BEGIN
 140     UPDATE order_form SET ended = datetime('now') WHERE id = old.id;
 141 END;
 142
 143 /*
 144  * Gestion du workflow sur une commande: il doit être impossible d'aller en arrière
 145  */
 146 CREATE TRIGGER order_form_update_management_workflow BEFORE UPDATE OF status ON order_form
 147 BEGIN
 148     SELECT CASE
 149         WHEN (old.status='wait' AND new.status='open')
 150             OR (old.status='end' AND new.status<>'end')
 151         THEN RAISE(ABORT, 'new status do not respect workflow')
 152     END;
 153 END;
 154
 155 /*
 156  * Gestion d'un élément de commande: il doit être impossible de modifier uns des clés primaires
 157  */
 158 CREATE TRIGGER order_element_update_management_workflow BEFORE UPDATE OF status ON order_element
 159 BEGIN
 160     SELECT CASE
 161         WHEN old.id_order_form <> new.id_order_form
 162             OR old.id_user <> new.id_user
 163             OR old.id_photo <> new.id_photo
 164         THEN RAISE(ABORT, 'bad usage of order element')
 165     END;
 166 END;
 167
 168 /*
 169  * 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
 170  */
 171 CREATE TRIGGER order_element_update_management_when_closed BEFORE UPDATE ON order_element
 172 BEGIN
 173     SELECT CASE
 174         WHEN ((SELECT order_form.id FROM order_form WHERE order_form.id=old.id_order_form AND order_form.status='open') IS NULL)
 175         THEN RAISE(ABORT, 'try to update element in not opened order form')
 176     END;
 177 END;
 178
 179 /*
 180  * 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
 181  */
 182 CREATE TRIGGER order_element_insert_management_when_closed BEFORE INSERT ON order_element
 183 BEGIN
 184     SELECT CASE
 185         WHEN ((SELECT order_form.id FROM order_form WHERE order_form.id=new.id_order_form AND order_form.status='open') IS NULL)
 186         THEN RAISE(ABORT, 'try to insert element in not opened order form')
 187     END;
 188 END;
 189
 190 /*
 191  * 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.
 192  */
 193 CREATE TRIGGER order_element_insert_management_check_user_is_authored BEFORE INSERT ON order_element
 194 BEGIN
 195     SELECT CASE
 196         WHEN (
 197             (
 198                 SELECT contact.id_user_contact
 199                 FROM contact
 200                 JOIN gallery ON gallery.id_user=contact.id_user_owner
 201                 JOIN order_form ON order_form.id_gallery=gallery.id
 202                 WHERE (contact.id_user_contact=new.id_user OR contact.id_user_owner=new.id_user)
 203                 AND order_form.id=new.id_order_form
 204             ) IS NULL
 205         )
 206         THEN RAISE(ABORT, 'unauthorized user is ordering')
 207     END;
 208 END;
 209