From: Sébastien CHAZALLET Date: Sat, 3 Nov 2012 14:34:34 +0000 (+0100) Subject: Rajout d'un jeu d'essai X-Git-Url: http://git.inspyration.org/?p=zf2.biz%2Fgalerie.git;a=commitdiff_plain;h=f9ec5ce875923e2c2ea23aa009515c6d3a98cdb6 Rajout d'un jeu d'essai --- diff --git a/data/images/1/ama_dablam.jpg b/data/images/1/ama_dablam.jpg new file mode 100644 index 0000000..a2ee0f4 Binary files /dev/null and b/data/images/1/ama_dablam.jpg differ diff --git a/data/images/1/nupse.jpg b/data/images/1/nupse.jpg new file mode 100644 index 0000000..5b9a71c Binary files /dev/null and b/data/images/1/nupse.jpg differ diff --git a/data/images/1/panorama.jpg b/data/images/1/panorama.jpg new file mode 100644 index 0000000..bd3df02 Binary files /dev/null and b/data/images/1/panorama.jpg differ diff --git a/data/images/2/P9090395.jpg b/data/images/2/P9090395.jpg new file mode 100644 index 0000000..47e948b Binary files /dev/null and b/data/images/2/P9090395.jpg differ diff --git a/data/images/2/P9090577.jpg b/data/images/2/P9090577.jpg new file mode 100644 index 0000000..0dee226 Binary files /dev/null and b/data/images/2/P9090577.jpg differ diff --git a/data/images/2/P9110931.jpg b/data/images/2/P9110931.jpg new file mode 100644 index 0000000..3c29b7b Binary files /dev/null and b/data/images/2/P9110931.jpg differ diff --git a/data/images/2/P9110957.jpg b/data/images/2/P9110957.jpg new file mode 100644 index 0000000..eb7d9cf Binary files /dev/null and b/data/images/2/P9110957.jpg differ diff --git a/data/images/3/pelemele.jpg b/data/images/3/pelemele.jpg new file mode 100644 index 0000000..3f7f9f6 Binary files /dev/null and b/data/images/3/pelemele.jpg differ diff --git a/data/jeu_essai.db b/data/jeu_essai.db new file mode 100644 index 0000000..ca6418e Binary files /dev/null and b/data/jeu_essai.db differ diff --git a/data/scripts/data.sql b/data/scripts/data.sql new file mode 100644 index 0000000..d9a8da7 --- /dev/null +++ b/data/scripts/data.sql @@ -0,0 +1,56 @@ +PRAGMA foreign_keys = ON; + +INSERT INTO user (lastname, firstname, birth, email) + SELECT 'Finy', 'Alain', '1962-09-17', 'alain@example.com' +UNION SELECT 'Mulder', 'Fox', '1980-12-20', 'fox@ailleurs.com' +UNION SELECT 'Cah', 'Paul', '1972-10-19', 'paul.c@example.com' +UNION SELECT 'Satch', 'Joe', '1956-07-15', 'john.d@example.com'; + + +INSERT INTO contact + SELECT 1, 2 +UNION SELECT 1, 3 +UNION SELECT 1, 4 +UNION SELECT 2, 1 +UNION SELECT 2, 3 +UNION SELECT 3, 1; + + +INSERT INTO gallery (id_user, name, description) + SELECT 2, 'Pérou', 'Photos des vacances au Pérou, 2011' +UNION SELECT 1, 'Himalaya', 'Entrainement requis' +UNION SELECT 2, 'Parapente', 'Photos de vol en parapente'; + + +INSERT INTO photo (id_gallery, name, description, filename, extension) + SELECT 1, 'Machu Picchu 1', 'Vue d''ensemble du Machu Picchu', 'P9110931', 'jpg' +UNION SELECT 1, 'Machu Picchu 2', 'Vue depuis la ville', 'P9110957', 'jpg' +UNION SELECT 1, 'Paysages 1', 'Un peu désertique', 'P9090395', 'jpg' +UNION SELECT 1, 'Paysages 2', 'Un peu de verdure', 'P9090577', 'jpg' +UNION SELECT 2, 'Ama Dablam', 'Sommet à 6856m. Situé au pied de l''Everest et du Lhotse. Signifie "reliquaire de la mère" en référence au pendentif que portent les Sherpanis', 'ama_dablam', 'jpg' +UNION SELECT 2, 'Nupse', 'Sommet à 7864m', 'nupse', 'jpg' +UNION SELECT 2, 'Panorama', 'Vue vers l''est depuis le camp d''altitude', 'panorama', 'jpg' +UNION SELECT 1, 'Lac Titicaca', 'Lac Titicaca', 'lt', 'jpg' +UNION SELECT 3, 'Pêle-mêle', 'Pêle-mêle de photographies', 'pelemele', 'jpg'; + + +INSERT INTO order_form (id_gallery) + SELECT 1 +UNION SELECT 2 +UNION SELECT 2; + + +INSERT INTO order_element (id_order_form, id_user, id_photo, quantity) + SELECT 1, 1, 1, 1 +UNION SELECT 1, 1, 2, 1 +UNION SELECT 1, 1, 3, 1 +UNION SELECT 1, 1, 4, 1 +UNION SELECT 1, 2, 1, 2 +UNION SELECT 1, 2, 2, 4 +UNION SELECT 1, 1, 2, 1 +UNION SELECT 2, 1, 1, 1 +UNION SELECT 2, 1, 2, 1; + +UPDATE order_form SET status='wait' WHERE id IN (1, 2); +UPDATE order_form SET status='end' WHERE id=1; + diff --git a/data/scripts/schema.sql b/data/scripts/schema.sql new file mode 100644 index 0000000..2a6a0b3 --- /dev/null +++ b/data/scripts/schema.sql @@ -0,0 +1,209 @@ +PRAGMA foreign_keys = ON; + +/* + * Utilisateur ayant un compte sur l'application + */ +CREATE TABLE user ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + lastname VARCHAR(32) NOT NULL COLLATE NOCASE, + firstname VARCHAR(32) NOT NULL COLLATE NOCASE, + birth DATE NOT NULL, + email VARCHAR(32) NOT NULL UNIQUE COLLATE NOCASE, + UNIQUE (lastname, firstname, birth) +); + +CREATE INDEX idx_user_id ON user(id); +CREATE INDEX idx_user_unique_triptyque ON user(lastname, firstname, birth); +CREATE INDEX idx_user_email ON user(email); +CREATE INDEX idx_user_all ON user(id, lastname, firstname, birth, email); + +/* + * Gestion du carnet de contacts entre utilisateurs + * Non réciproque + */ +CREATE TABLE contact ( + id_user_owner INTEGER ASC REFERENCES user(id), + id_user_contact INTEGER ASC REFERENCES user(id), + PRIMARY KEY (id_user_owner, id_user_contact) +); + +CREATE INDEX idx_contact ON contact(id_user_owner, id_user_contact); + +/* + * Gallerie de photos (pas de workflow associé) + */ +CREATE TABLE gallery ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + id_user INTEGER REFERENCES user(id), + name VARCHAR(32) NOT NULL UNIQUE COLLATE NOCASE, + description TEXT NOT NULL, + created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE INDEX idx_gallery_id ON gallery(id); +CREATE INDEX idx_gallery_name ON gallery(name); +CREATE INDEX idx_gallery_user_fk ON gallery(id_user); +CREATE INDEX idx_gallery_all ON gallery(id, id_user, name, description, created, updated); + +/* + * Element de gallerie : photographie + * Plusieurs photographies sont stockées sur le disque dur, aux formats: + * - original (format de l'appareil photo) + * - écran (format d'affichage de la photo à l'écran) + * - miniature (format d'affichage pour les miniatures de la gallerie) + */ +CREATE TABLE photo ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + id_gallery INTEGER REFERENCES gallery(id), + name VARCHAR(16) NOT NULL UNIQUE COLLATE RTRIM, + description TEXT NOT NULL, + filename VARCHAR(12) NOT NULL COLLATE NOCASE, + extension VARCHAR(4) NOT NULL COLLATE NOCASE CHECK (extension IN ('jpg', 'png')), + created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE INDEX idx_photo_id ON photo(id); +CREATE INDEX idx_photo_name ON photo(name); +CREATE INDEX idx_photo_gallery_fk ON photo(id_gallery); +CREATE INDEX idx_photo_all ON photo(id, id_gallery, name, description, filename, extension, created, updated); + +/* + * Commande qui dispose d'un workflow: + * - le propriétaire de la gallerie ouvre une commande + * - tant que la commande est ouverte, les contacts du propriétaire peuvent demander des exemplaire de chaque photo + * - après une certaine période, la commande passé au statut en attente; les photos sont en cours de développement + * - lorsque la commande est reçue, elle est alors fermée + * - tous les contacts du propriétaires passent donc commande en même temps + * - le propriétaire peut réouvrir au besoin une nouvelle commande + */ +CREATE TABLE order_form ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + id_gallery UNSIGNED SMALLINT REFERENCES gallery (id), + status VARCHAR(4) NOT NULL COLLATE NOCASE CHECK (status IN ('open', 'wait', 'end')) DEFAULT 'open', + created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + closed TIMESTAMP NOT NULL DEFAULT (datetime('now', '+1 month')), + ended TIMESTAMP NULL DEFAULT NULL +); + +CREATE INDEX idx_order_form_id ON order_form(id); +CREATE INDEX idx_order_form_gallery_fk ON order_form(id_gallery); +CREATE INDEX idx_order_form_all ON order_form(id, id_gallery, status, created, closed, ended); + +/* + * élément de commande : + * - il n'est pas possible de créer un tel élément lorsque la commande n'est pas ouverte + * - il n'est pas possible de passer commande si l'on ne fait pas parti des contacts du propriétaire + */ +CREATE TABLE order_element ( + id_order_form INTEGER REFERENCES order_form(id), + id_user INTEGER REFERENCES user(id), + id_photo INTEGER REFERENCES photo(id), + quantity UNSIGNED SMALLINT DEFAULT 1, + PRIMARY KEY (id_user, id_order_form, id_photo) +); + +CREATE INDEX idx_order_element_primary ON order_element(id_order_form, id_user, id_photo); +CREATE INDEX idx_order_element_all ON order_element(id_order_form, id_user, id_photo, quantity); + +/* + * à chaque fois que les propriétés d'une gallerie sont modifiées, le champs contenant la date de mise à jour l'est également + */ +CREATE TRIGGER gallery_update_management AFTER UPDATE ON gallery +BEGIN + UPDATE gallery SET updated = datetime('now') WHERE id = old.id; +END; + +/* + * à 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 + */ +CREATE TRIGGER photo_update_management AFTER UPDATE ON photo +BEGIN + UPDATE gallery SET updated = datetime('now') WHERE id = old.id_gallery; + UPDATE photo SET updated = datetime('now') WHERE id = old.id; +END; + +/* + * Gestion du workflow sur une commande: mise à jour de la date de fermeture au changement de statut open vers wait + */ +CREATE TRIGGER order_form_update_management_closed AFTER UPDATE OF status ON order_form WHEN old.status='open' AND new.status='wait' +BEGIN + UPDATE order_form SET closed = datetime('now') WHERE id = old.id; +END; + +/* + * Gestion du workflow sur une commande: mise à jour de la date de fermeture au changement de statut wait vers end + */ +CREATE TRIGGER order_form_update_management_ended AFTER UPDATE OF status ON order_form WHEN old.status='wait' AND new.status='end' +BEGIN + UPDATE order_form SET ended = datetime('now') WHERE id = old.id; +END; + +/* + * Gestion du workflow sur une commande: il doit être impossible d'aller en arrière + */ +CREATE TRIGGER order_form_update_management_workflow BEFORE UPDATE OF status ON order_form +BEGIN + SELECT CASE + WHEN (old.status='wait' AND new.status='open') + OR (old.status='end' AND new.status<>'end') + THEN RAISE(ABORT, 'new status do not respect workflow') + END; +END; + +/* + * Gestion d'un élément de commande: il doit être impossible de modifier uns des clés primaires + */ +CREATE TRIGGER order_element_update_management_workflow BEFORE UPDATE OF status ON order_element +BEGIN + SELECT CASE + WHEN old.id_order_form <> new.id_order_form + OR old.id_user <> new.id_user + OR old.id_photo <> new.id_photo + THEN RAISE(ABORT, 'bad usage of order element') + END; +END; + +/* + * 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 + */ +CREATE TRIGGER order_element_update_management_when_closed BEFORE UPDATE ON order_element +BEGIN + SELECT CASE + WHEN ((SELECT order_form.id FROM order_form WHERE order_form.id=old.id_order_form AND order_form.status='open') IS NULL) + THEN RAISE(ABORT, 'try to update element in not opened order form') + END; +END; + +/* + * 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 + */ +CREATE TRIGGER order_element_insert_management_when_closed BEFORE INSERT ON order_element +BEGIN + SELECT CASE + WHEN ((SELECT order_form.id FROM order_form WHERE order_form.id=new.id_order_form AND order_form.status='open') IS NULL) + THEN RAISE(ABORT, 'try to insert element in not opened order form') + END; +END; + +/* + * 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. + */ +CREATE TRIGGER order_element_insert_management_check_user_is_authored BEFORE INSERT ON order_element +BEGIN + SELECT CASE + WHEN ( + ( + SELECT contact.id_user_contact + FROM contact + JOIN gallery ON gallery.id_user=contact.id_user_owner + JOIN order_form ON order_form.id_gallery=gallery.id + WHERE (contact.id_user_contact=new.id_user OR contact.id_user_owner=new.id_user) + AND order_form.id=new.id_order_form + ) IS NULL + ) + THEN RAISE(ABORT, 'unauthorized user is ordering') + END; +END; +