summaryrefslogtreecommitdiff
path: root/example/bugs.sql
diff options
context:
space:
mode:
authordefanor <defanor@uberspace.net>2018-12-30 13:05:36 +0300
committerdefanor <defanor@uberspace.net>2018-12-30 13:05:36 +0300
commit1b3ea5d48e049c595f90eb8b9fb11f8149927519 (patch)
tree979b85238d5f40f05284993c40b98428ca63a8a8 /example/bugs.sql
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.
Diffstat (limited to 'example/bugs.sql')
-rw-r--r--example/bugs.sql37
1 files changed, 37 insertions, 0 deletions
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.