From 1b3ea5d48e049c595f90eb8b9fb11f8149927519 Mon Sep 17 00:00:00 2001 From: defanor Date: Sun, 30 Dec 2018 13:05:36 +0300 Subject: Initial commit The initial working version, an example, and brief description are included. Error handling and reporting, perhaps HTTP headers, CLI arguments, and documentation can still be improved, but that's for future commits. --- example/bugs.sql | 37 +++++++++++++++++++++++++++++++++++++ 1 file changed, 37 insertions(+) create mode 100644 example/bugs.sql (limited to 'example/bugs.sql') diff --git a/example/bugs.sql b/example/bugs.sql new file mode 100644 index 0000000..033a1f7 --- /dev/null +++ b/example/bugs.sql @@ -0,0 +1,37 @@ +-- pgcrypto is needed for UUIDs. +create extension pgcrypto; + +-- A table with defaults and constraints. +create table bugs ( + id uuid not null primary key default gen_random_uuid(), + reported timestamp with time zone not null default now(), + reporter varchar not null default current_user, + project varchar(256) not null check (project <> ''), + description varchar(10240) not null check (description <> '') +); + +-- No indexes, since they are irrelevant to this example (but normally +-- they should be there). + +-- Additional restrictions. +create policy bugs_select_policy on bugs for select using (true); +create policy bugs_insert_policy on bugs for insert + with check (reported = now() and reporter = current_user); +-- Update and delete policies can be added later. +alter table bugs enable row level security; + +-- A search function for convenience. +create or replace function bug_search (proj varchar, descr varchar, lim int, offs int) +returns xml +as $$ + select query_to_xml( + 'select id, date_trunc(''second'', reported) as reported, reporter, ' + || 'project, substring(description from ''[^\n\r]+'') as summary from bugs ' + || 'where (project like ' || quote_literal('%' || proj || '%') + || ') and (description like ' || quote_literal('%' || descr || '%') + || ') order by reported desc limit ' || lim || ' offset ' || offs, + false, false, 'bugs') +$$ language sql; + +-- Now users can be added with select and/or insert privileges, +-- including a guest user for unauthenticated requests. -- cgit v1.2.3