gallery

6 files changed, 142 insertions, 0 deletions

diff --git a/sql/functions/general.get_galleries.sql b/sql/functions/general.get_galleries.sql
new file mode 100644
index 0000000..fc93095
--- /dev/null
+++ b/sql/functions/general.get_galleries.sql
@@ -0,0 +1,20 @@
+drop function general.get_galleries ();
+
+create or replace function general.get_galleries ()
+returns setof general.gallery_t
+as $$
+begin
+    return query
+    select g.id,
+        g.description,
+        g.filename,
+        g.mime_type,
+        substring(g.content::text from 3) as content,
+        g.created,
+        g.modified
+    from general.gallery g
+    order by g.modified desc;
+
+    return;
+end;
+$$ language plpgsql;
diff --git a/sql/functions/general.get_gallery.sql b/sql/functions/general.get_gallery.sql
new file mode 100644
index 0000000..66c9fce
--- /dev/null
+++ b/sql/functions/general.get_gallery.sql
@@ -0,0 +1,24 @@
+drop function general.get_gallery (
+    p_id bigint
+);
+
+create or replace function general.get_gallery (
+    p_id bigint
+)
+returns setof general.gallery_t
+as $$
+begin
+    return query
+    select g.id,
+        g.description,
+        g.filename,
+        g.mime_type,
+        substring(g.content::text from 3) as content,
+        g.created,
+        g.modified
+    from general.gallery g
+    where g.id = p_id;
+
+    return;
+end;
+$$ language plpgsql;
diff --git a/sql/functions/general.insert_gallery.sql b/sql/functions/general.insert_gallery.sql
new file mode 100644
index 0000000..0582c6b
--- /dev/null
+++ b/sql/functions/general.insert_gallery.sql
@@ -0,0 +1,33 @@
+drop function general.insert_gallery (
+    p_description text,
+    p_filename character varying,
+    p_mime_type character varying,
+    p_hex text
+);
+
+create or replace function general.insert_gallery (
+    p_description text,
+    p_filename character varying,
+    p_mime_type character varying,
+    p_hex text
+)
+returns bigint
+as $$
+declare
+  l_id bigint;
+begin
+    insert into general.gallery (
+        description,
+        filename,
+        mime_type,
+        content
+    ) values (
+        p_description,
+        p_filename,
+        p_mime_type,
+        decode(p_hex, 'hex')
+    ) returning id into l_id;
+
+    return l_id;
+end;
+$$ language plpgsql;
diff --git a/sql/functions/general.update_gallery.sql b/sql/functions/general.update_gallery.sql
new file mode 100644
index 0000000..e8532db
--- /dev/null
+++ b/sql/functions/general.update_gallery.sql
@@ -0,0 +1,43 @@
+drop function general.update_gallery (
+    p_id bigint,
+    p_description text,
+    p_filename character varying,
+    p_mime_type character varying,
+    p_hex text
+);
+
+create or replace function general.update_gallery (
+    p_id bigint,
+    p_description text,
+    p_filename character varying,
+    p_mime_type character varying,
+    p_hex text
+)
+returns bigint
+as $$
+declare
+  l_id bigint;
+begin
+    select g.id into l_id
+    from general.gallery g
+    where g.id = p_id;
+
+    if(l_id is not null) then
+        update general.gallery
+        set description = p_description,
+            filename = p_filename,
+            mime_type = p_mime_type,
+            content = decode(p_hex, 'hex'),
+            modified = now()
+        where id = p_id;
+
+        select g.id into l_id
+        from general.gallery g
+        where g.id = p_id;
+
+        return l_id;
+    else
+        return null;
+    end if;
+end;
+$$ language plpgsql;
diff --git a/sql/tables/general.gallery.sql b/sql/tables/general.gallery.sql
new file mode 100644
index 0000000..63b3172
--- /dev/null
+++ b/sql/tables/general.gallery.sql
@@ -0,0 +1,11 @@
+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,
+    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
new file mode 100644
index 0000000..a8f2060
--- /dev/null
+++ b/sql/types/general.gallery_t.sql
@@ -0,0 +1,11 @@
+drop type general.gallery_t cascade;
+
+create type general.gallery_t as (
+    id bigint,
+    description text,
+    filename character varying,
+    mime_type character varying,
+    content text,
+    created timestamp without time zone,
+    modified timestamp without time zone
+);