X-Git-Url: http://git.inspyration.org/?a=blobdiff_plain;f=data%2Fscripts%2Fschema.sql;h=ebf8a4b2a499182c7f044ff14481bb96d99ad316;hb=12557cded4e09175aab9f520c3a27f4a194fa6b8;hp=2a6a0b3132b4bc1387ad211bc454961e9fac8057;hpb=f9ec5ce875923e2c2ea23aa009515c6d3a98cdb6;p=zf2.biz%2Fgalerie.git

diff --git a/data/scripts/schema.sql b/data/scripts/schema.sql
index 2a6a0b3..ebf8a4b 100644
--- a/data/scripts/schema.sql
+++ b/data/scripts/schema.sql
@@ -125,6 +125,14 @@ BEGIN
 END;
 
 /*
+ * à chaque fois qu'une photographie est rajoutée, le champs contenant la date de mise la gallerie liée est mis à jour
+ */
+CREATE TRIGGER photo_insert_management AFTER UPDATE ON photo
+BEGIN
+    UPDATE gallery SET updated = datetime('now') WHERE id = old.id_gallery;
+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'
@@ -207,3 +215,10 @@ BEGIN
     END;
 END;
 
+CREATE VIEW gallery_info AS
+    SELECT gallery.id, gallery.name, gallery.description, user.firstname || ' ' || user.lastname AS username, count(photo.id) as nb
+    INNER JOIN user ON gallery.id_user = user.id
+    LEFT JOIN photo ON gallery.id = photo.id_gallery
+    GROUP BY user.lastname, user.firstname, gallery.name
+    ORDER BY user.lastname, user.firstname, gallery.name
+;