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/README.md | 18 +++++++++++ example/bugs.sql | 37 ++++++++++++++++++++++ example/common.xsl | 40 ++++++++++++++++++++++++ example/list.xsl | 92 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ example/view.xsl | 26 +++++++++++++++ 5 files changed, 213 insertions(+) create mode 100644 example/README.md create mode 100644 example/bugs.sql create mode 100644 example/common.xsl create mode 100644 example/list.xsl create mode 100644 example/view.xsl (limited to 'example') diff --git a/example/README.md b/example/README.md new file mode 100644 index 0000000..b6cfbfa --- /dev/null +++ b/example/README.md @@ -0,0 +1,18 @@ +# pg×html usage example + +Here is an example that implements a very basic bug reporting system: +users can just report, view, and list (search) the bugs. + +First of all, a database should be designed: `bugs.sql` contains +definitions and comments. + +A common template, `common.xsl`, includes error handling and some +shared HTML. One can choose to show error details to users, or to hide +them. + +`view.xsl` is a basic template for bug viewing. + +`list.xsl` includes report and search forms, and lists the bugs. + +To quickly try it, run `pgxhtml --devlogging` in this directory, +with database connection environment variables set if needed. 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. diff --git a/example/common.xsl b/example/common.xsl new file mode 100644 index 0000000..1db0229 --- /dev/null +++ b/example/common.xsl @@ -0,0 +1,40 @@ + + + + + + + + + Bugs + + + + + + + + +

SQL error

+
+
State
+
+
Status
+
+
Message
+
+
Detail
+
+
Hint
+
+
Query template
+
+
Query parameters
+
+
+ + + diff --git a/example/list.xsl b/example/list.xsl new file mode 100644 index 0000000..86c3150 --- /dev/null +++ b/example/list.xsl @@ -0,0 +1,92 @@ + + + + + + + + + + + + +

Report

+
+
+
+
+ +
+
+
+