embed youtobe videos

11 files changed, 67 insertions, 39 deletions

diff --git a/lisp/service/gallery-service.lisp b/lisp/service/gallery-service.lisp
index df709cd..b034075 100644
--- a/lisp/service/gallery-service.lisp
+++ b/lisp/service/gallery-service.lisp
@@ -47,11 +47,12 @@
                                      nil
                                      t
                                      `((:name "description" :label "Description" :field-type "textarea")
-                                       (:name "upload" :label "Image/Video" :field-type "file" :required "required")
+                                       (:name "video_embed_url" :label "Video embed URL" :field-type "text")
+                                       (:name "upload" :label "Image" :field-type "file")
                                        (:label "Add Gallery" :field-type "button" :onclick "on_gallery_add_submit_clicked()"))))))
 
-(defun gallery-add-submit-json (description upload)
-  (declare (special description))
+(defun gallery-add-submit-json (description video_embed_url upload)
+  (declare (special description video_embed_url))
   (with-auth (instance gallery/add-modify-delete-service "gallery-modify")
     (with-woodriverlessons-database
       (let ((general-pkg (make-instance 'general-pkg))
@@ -63,12 +64,13 @@
                                         (intersection `(,x) `(upload)))
                                       (sb-introspect:function-lambda-list #'gallery-add-submit-json))
               do (setf (slot-value gallery param) (symbol-value param)))
-        (let ((tmpdir (tmpdir:mkdtemp :prefix "woodriverlessons-gallery-")))
-          (setf new-filepath (format nil "~a~a" tmpdir (file-namestring tmp-filepath)))
-          (fad:copy-file tmp-filepath new-filepath)
-          (setf (filename gallery) orig-filename)
-          (setf (mime_type gallery) (org-ckons-file::get-mime-type new-filepath))
-          (setf (content gallery) (org-ckons-file::file-as-hex new-filepath)))
+        (when upload
+          (let ((tmpdir (tmpdir:mkdtemp :prefix "woodriverlessons-gallery-")))
+            (setf new-filepath (format nil "~a~a" tmpdir (file-namestring tmp-filepath)))
+            (fad:copy-file tmp-filepath new-filepath)
+            (setf (filename gallery) orig-filename)
+            (setf (mime_type gallery) (org-ckons-file::get-mime-type new-filepath))
+            (setf (content gallery) (org-ckons-file::file-as-hex new-filepath))))
         (insert-gallery general-pkg gallery)
         (setf (session-value :message) "Gallery added successfully.")))))
 
@@ -83,12 +85,13 @@
                                              t
                                              `((:name "id" :field-type "hidden" :value ,(id gallery) :required "required")
                                                (:name "description" :label "Description" :value ,(description gallery) :field-type "textarea")
+                                               (:name "video_embed_url" :label "Video embed URL" :field-type "text" :value ,(video_embed_url gallery))
                                                (:name "upload" :label "Image/Video" :field-type "file")
                                                (:label "Modify Gallery" :field-type "button" :onclick "on_gallery_modify_submit_clicked()"))))
             (setf (session-value :errormsg) "Error: could not modify gallery. Not found."))))))
 
-(defun gallery-modify-submit-json (id description upload)
-  (declare (special description))
+(defun gallery-modify-submit-json (id description video_embed_url upload)
+  (declare (special description video_embed_url))
   (with-auth (instance gallery/add-modify-delete-service "gallery-modify")
     (with-woodriverlessons-database
       (let* ((general-pkg (make-instance 'general-pkg))
diff --git a/lisp/sql/gallery.lisp b/lisp/sql/gallery.lisp
index 2f4231d..501691d 100644
--- a/lisp/sql/gallery.lisp
+++ b/lisp/sql/gallery.lisp
@@ -16,6 +16,9 @@
    (mime_type :initarg :mime_type
               :initform nil
               :accessor mime_type)
+   (video_embed_url :initarg :video_embed_url
+                    :initform nil
+                    :accessor video_embed_url)
    (content :initarg :content
             :initform nil
             :accessor content)
diff --git a/lisp/sql/general-pkg.lisp b/lisp/sql/general-pkg.lisp
index 5a92ed4..0d1b3db 100644
--- a/lisp/sql/general-pkg.lisp
+++ b/lisp/sql/general-pkg.lisp
@@ -23,20 +23,22 @@
 
 (defmethod insert-gallery ((general-pkg general-pkg) (gallery gallery))
   (setf (*table gallery) (format nil
-                                 "general.insert_gallery('~a', '~a', '~a', '~a')"
-                                 (description gallery)
-                                 (filename gallery)
-                                 (mime_type gallery)
-                                 (content gallery)))
+                                 "general.insert_gallery(~a, ~a, ~a, ~a, ~a)"
+                                 (org-ckons-sql::sanitize gallery (description gallery))
+                                 (org-ckons-sql::sanitize gallery (filename gallery))
+                                 (org-ckons-sql::sanitize gallery (mime_type gallery))
+                                 (org-ckons-sql::sanitize gallery (video_embed_url gallery))
+                                 (org-ckons-sql::sanitize gallery (content gallery))))
   (setf (id gallery) (caar (call-pg-function general-pkg gallery)))
   (id gallery))
 
 (defmethod update-gallery ((general-pkg general-pkg) (gallery gallery))
   (setf (*table gallery) (format nil
-                                 "general.update_gallery(~a, '~a', '~a', '~a', '~a')"
+                                 "general.update_gallery(~a, ~a, ~a, ~a, ~a, ~a)"
                                  (id gallery)
-                                 (description gallery)
-                                 (filename gallery)
-                                 (mime_type gallery)
-                                 (content gallery)))
+                                 (org-ckons-sql::sanitize gallery (description gallery))
+                                 (org-ckons-sql::sanitize gallery (filename gallery))
+                                 (org-ckons-sql::sanitize gallery (mime_type gallery))
+                                 (org-ckons-sql::sanitize gallery (video_embed_url gallery))
+                                 (org-ckons-sql::sanitize gallery (content gallery))))
   (caar (call-pg-function general-pkg gallery)))
diff --git a/lisp/webapps/woodriverlessons/clojurescript/woodriverlessons/src/core.cljs b/lisp/webapps/woodriverlessons/clojurescript/woodriverlessons/src/core.cljs
index 8a8d325..cc898be 100644
--- a/lisp/webapps/woodriverlessons/clojurescript/woodriverlessons/src/core.cljs
+++ b/lisp/webapps/woodriverlessons/clojurescript/woodriverlessons/src/core.cljs
@@ -607,7 +607,7 @@
             [:tr
              [:th {:scope "col"} "ID"]
              [:th {:scope "col"} "Description"]
-             [:th {:scope "col"} "Image/Video"]
+             [:th {:scope "col"} "Content"]
              (when (get jsonobj "adminP")
                [:th {:scope "col" :style "text-align: right;"} "Del"])]]
            [:tbody
@@ -620,14 +620,17 @@
                  [:td {:onclick onclick} (get rec "id")]
                  [:td {:onclick onclick} (get rec "description")]
                  [:td
-                  (cond (str/includes? (get rec "mime_type") "image/")
-                        [:img {:height "400"
-                               :src (str "/gallery/file/view?id=" (get rec "id"))}]
-                        (str/includes? (get rec "mime_type") "video/")
-                        [:video {:height "400"
-                                 :controls "controls"}
-                         [:source {:src (str "/gallery/file/view?id=" (get rec "id"))
-                                   :type (get rec "mime_type")}]])]
+                  (cond (not (= (get rec "video_embed_url") "null"))
+                        [:iframe {:width "720"
+                                  :height "405"
+                                  :src (get rec "video_embed_url")
+                                  :frameborder "0"
+                                  :allow "accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share"
+                                  :referrerpolicy "strict-origin-when-cross-origin"
+                                  :allowfullscreen "allowfullscreen"}]
+                        :else
+                        [:img {:height "405"
+                               :src (str "/gallery/file/view?id=" (get rec "id"))}])]
                  (when (get jsonobj "adminP")
                    [:td {:style "text-align: right;"}
                     [:img {:src "/static/images/delete.png"
diff --git a/lisp/webapps/woodriverlessons/site.lisp b/lisp/webapps/woodriverlessons/site.lisp
index a1a6a82..d47b2b6 100644
--- a/lisp/webapps/woodriverlessons/site.lisp
+++ b/lisp/webapps/woodriverlessons/site.lisp
@@ -105,13 +105,13 @@
   `(gallery-add-json))
 
 (defmacro .gallery-add-submit ()
-  `(gallery-add-submit-json description upload))
+  `(gallery-add-submit-json description video_embed_url upload))
 
 (defmacro .gallery-modify ()
   `(gallery-modify-json id))
 
 (defmacro .gallery-modify-submit ()
-  `(gallery-modify-submit-json id description upload))
+  `(gallery-modify-submit-json id description video_embed_url upload))
 
 (defmacro .gallery-delete ()
   `(gallery-delete-json id))
@@ -205,9 +205,9 @@
 (define-endpoint :get "/gallery" () .gallery)
 (define-endpoint :get "/gallery/view" () .gallery-view)
 (define-endpoint :post "/gallery/add" () .gallery-add)
-(define-endpoint :post "/gallery/add/submit" ((description :parameter-type 'string) (upload :parameter-type 'string)) .gallery-add-submit)
+(define-endpoint :post "/gallery/add/submit" ((description :parameter-type 'string) (video_embed_url :parameter-type 'string) (upload :parameter-type 'string)) .gallery-add-submit)
 (define-endpoint :post "/gallery/modify" ((id :parameter-type 'integer)) .gallery-modify)
-(define-endpoint :post "/gallery/modify/submit" ((id :parameter-type 'integer) (description :parameter-type 'string) (upload :parameter-type 'string)) .gallery-modify-submit)
+(define-endpoint :post "/gallery/modify/submit" ((id :parameter-type 'integer) (description :parameter-type 'string) (video_embed_url :parameter-type 'string) (upload :parameter-type 'string)) .gallery-modify-submit)
 (define-endpoint :post "/gallery/delete" ((id :parameter-type 'integer)) .gallery-delete)
 (define-endpoint :get "/gallery/file/view" ((id :parameter-type 'integer)) .gallery-file-view)
 (define-endpoint :get "/testimonials" () .testimonials)
diff --git a/sql/functions/general.get_galleries.sql b/sql/functions/general.get_galleries.sql
index fc93095..db3ad09 100644
--- a/sql/functions/general.get_galleries.sql
+++ b/sql/functions/general.get_galleries.sql
@@ -9,7 +9,11 @@ begin
         g.description,
         g.filename,
         g.mime_type,
-        substring(g.content::text from 3) as content,
+        g.video_embed_url,
+        case
+          when g.content is null then null
+          else substring(g.content::text from 3)
+        end as content,
         g.created,
         g.modified
     from general.gallery g
diff --git a/sql/functions/general.get_gallery.sql b/sql/functions/general.get_gallery.sql
index 66c9fce..c0972f4 100644
--- a/sql/functions/general.get_gallery.sql
+++ b/sql/functions/general.get_gallery.sql
@@ -13,7 +13,11 @@ begin
         g.description,
         g.filename,
         g.mime_type,
-        substring(g.content::text from 3) as content,
+        g.video_embed_url,
+        case
+          when g.content is null then null
+          else substring(g.content::text from 3)
+        end as content,
         g.created,
         g.modified
     from general.gallery g
diff --git a/sql/functions/general.insert_gallery.sql b/sql/functions/general.insert_gallery.sql
index 0582c6b..26260fb 100644
--- a/sql/functions/general.insert_gallery.sql
+++ b/sql/functions/general.insert_gallery.sql
@@ -2,6 +2,7 @@ drop function general.insert_gallery (
     p_description text,
     p_filename character varying,
     p_mime_type character varying,
+    p_video_embed_url character varying,
     p_hex text
 );
 
@@ -9,6 +10,7 @@ create or replace function general.insert_gallery (
     p_description text,
     p_filename character varying,
     p_mime_type character varying,
+    p_video_embed_url character varying,
     p_hex text
 )
 returns bigint
@@ -20,11 +22,13 @@ begin
         description,
         filename,
         mime_type,
+        video_embed_url,
         content
     ) values (
         p_description,
         p_filename,
         p_mime_type,
+        p_video_embed_url,
         decode(p_hex, 'hex')
     ) returning id into l_id;
 
diff --git a/sql/functions/general.update_gallery.sql b/sql/functions/general.update_gallery.sql
index e8532db..95296c3 100644
--- a/sql/functions/general.update_gallery.sql
+++ b/sql/functions/general.update_gallery.sql
@@ -3,6 +3,7 @@ drop function general.update_gallery (
     p_description text,
     p_filename character varying,
     p_mime_type character varying,
+    p_video_embed_url character varying,
     p_hex text
 );
 
@@ -11,6 +12,7 @@ create or replace function general.update_gallery (
     p_description text,
     p_filename character varying,
     p_mime_type character varying,
+    p_video_embed_url character varying,
     p_hex text
 )
 returns bigint
@@ -27,6 +29,7 @@ begin
         set description = p_description,
             filename = p_filename,
             mime_type = p_mime_type,
+            video_embed_url = p_video_embed_url,
             content = decode(p_hex, 'hex'),
             modified = now()
         where id = p_id;
diff --git a/sql/tables/general.gallery.sql b/sql/tables/general.gallery.sql
index 63b3172..972ae67 100644
--- a/sql/tables/general.gallery.sql
+++ b/sql/tables/general.gallery.sql
@@ -3,9 +3,10 @@ drop table general.gallery cascade;
 create table general.gallery (
     id serial8 primary key,
     description text,
-    filename character varying(255) not null,
-    mime_type character varying(255) not null,
-    content bytea not null,
+    filename character varying(255),
+    mime_type character varying(255),
+    video_embed_url character varying(255),
+    content bytea,
     created timestamp without time zone not null default now(),
     modified timestamp without time zone not null default now()
 );
diff --git a/sql/types/general.gallery_t.sql b/sql/types/general.gallery_t.sql
index a8f2060..3ccb56c 100644
--- a/sql/types/general.gallery_t.sql
+++ b/sql/types/general.gallery_t.sql
@@ -5,6 +5,7 @@ create type general.gallery_t as (
     description text,
     filename character varying,
     mime_type character varying,
+    video_embed_url character varying,
     content text,
     created timestamp without time zone,
     modified timestamp without time zone