diff options
Diffstat (limited to 'example')
| -rw-r--r-- | example/README.md | 18 | ||||
| -rw-r--r-- | example/bugs.sql | 37 | ||||
| -rw-r--r-- | example/common.xsl | 40 | ||||
| -rw-r--r-- | example/list.xsl | 92 | ||||
| -rw-r--r-- | example/view.xsl | 26 |
5 files changed, 213 insertions, 0 deletions
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 @@ +<?xml version="1.0" encoding="UTF-8"?> + +<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" + xmlns:xhtml="http://www.w3.org/1999/xhtml" + xmlns="http://www.w3.org/1999/xhtml" + version="1.0"> + <xsl:output method="xml" indent="yes"/> + + <xsl:template match="/"> + <html xmlns="http://www.w3.org/1999/xhtml"> + <head> + <title>Bugs</title> + </head> + <body> + <xsl:apply-templates select="*" /> + </body> + </html> + </xsl:template> + + <xsl:template match="sql_error"> + <h1>SQL error</h1> + <dl> + <dt>State</dt> + <dd><xsl:copy-of select="@state/text()" /></dd> + <dt>Status</dt> + <dd><xsl:copy-of select="@status/text()" /></dd> + <dt>Message</dt> + <dd><xsl:copy-of select="@message/text()" /></dd> + <dt>Detail</dt> + <dd><xsl:copy-of select="@detail/text()" /></dd> + <dt>Hint</dt> + <dd><xsl:copy-of select="@hint/text()" /></dd> + <dt>Query template</dt> + <dd><xsl:copy-of select="@template/text()" /></dd> + <dt>Query parameters</dt> + <dd><xsl:copy-of select="@parameters/text()" /></dd> + </dl> + </xsl:template> + +</xsl:stylesheet> 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 @@ +<?xml version="1.0" encoding="UTF-8"?> + +<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" + xmlns:xhtml="http://www.w3.org/1999/xhtml" + xmlns="http://www.w3.org/1999/xhtml" + version="1.0"> + <xsl:output method="xml" indent="yes"/> + <xsl:include href="common.xsl"/> + <xsl:param name="project" /> + <xsl:param name="description" /> + <xsl:param name="limit" select="10" /> + <xsl:param name="offset" select="0" /> + + <xsl:template match="table"> + <!-- Report form --> + <h2>Report</h2> + <form method="post" action="view.xhtml?q=insert%20into%20bugs%20(%20:fields%20)%20values%20(%20:values%20)%20returning%20xmlelement(name%20table,xmlelement(name%20row,xmlelement(name%20id,id),xmlelement(name%20reported,reported),xmlelement(name%20reporter,reporter),xmlelement(name%20project,project),xmlelement(name%20description,description)))"> + <dl> + <dt><label for="report_project">Project</label></dt> + <dd> + <input type="text" name="project" id="report_project" + required="required" maxlength="128" + placeholder="Project name or URL" /> + </dd> + <dt><label for="report_description">Description</label></dt> + <dd> + <textarea name="description" required="required" + id="report_description" maxlength="10240" + placeholder="Issue description" /> + </dd> + </dl> + <input type="submit" value="Report" /> + </form> + + <!-- Search form --> + <h2>Search</h2> + <form method="get" action="list.xhtml"> + <dl> + <dt><label for="search_project">Project</label></dt> + <dd> + <input id="search_project" type="search" name="project" + value="{$project}" /> + </dd> + <dt><label for="search_description">Description</label></dt> + <dd> + <input id="search_description" type="search" name="description" + value="{$description}" /> + </dd> + <dt><label for="search_limit">Limit</label></dt> + <dd> + <input id="search_limit" type="number" name="limit" min="1" + value="{$limit}" /> + </dd> + <dt><label for="search_offset">Offset</label></dt> + <dd> + <input id="search_offset" type="number" name="offset" min="0" + value="{$offset}" /> + </dd> + <input type="hidden" name="q" + value="select bug_search( q:project , q:description , q:limit , q:offset )" /> + </dl> + <input type="submit" value="Search" /> + </form> + + <!-- Search results --> + <table> + <tr> + <th>Reported</th> + <th>Reporter</th> + <th>Project</th> + <th>Summary</th> + </tr> + <xsl:for-each select="row"> + <tr> + <td><xsl:copy-of select="reported/text()" /></td> + <td><xsl:copy-of select="reporter/text()" /></td> + <td> + <a href="list.xhtml?q=select%20bug_search('{project/text()}','',{$limit},{$offset})"> + <xsl:copy-of select="project/text()" /> + </a> + </td> + <td> + <a href="view.xhtml?q=select%20query_to_xml('select%20*%20from%20bugs%20where%20id=''{id}''',false,false,'foo')"> + <xsl:copy-of select="summary/text()" /> + </a> + </td> + </tr> + </xsl:for-each> + </table> + </xsl:template> + +</xsl:stylesheet> diff --git a/example/view.xsl b/example/view.xsl new file mode 100644 index 0000000..af0e090 --- /dev/null +++ b/example/view.xsl @@ -0,0 +1,26 @@ +<?xml version="1.0" encoding="UTF-8"?> + +<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" + xmlns:xhtml="http://www.w3.org/1999/xhtml" + xmlns="http://www.w3.org/1999/xhtml" + version="1.0"> + <xsl:output method="xml" indent="yes"/> + <xsl:include href="common.xsl"/> + + <xsl:template match="table/row"> + <a href="list.xhtml?q=select%20bug_search(%27%27,%20%27%27,%2010,%200)">back to listing</a> + <dl> + <dt>ID</dt> + <dd><xsl:copy-of select="id/text()" /></dd> + <dt>Reported</dt> + <dd><xsl:copy-of select="reported/text()" /></dd> + <dt>Reporter</dt> + <dd><xsl:copy-of select="reporter/text()" /></dd> + <dt>Project</dt> + <dd><xsl:copy-of select="project/text()" /></dd> + <dt>Description</dt> + <dd><pre><xsl:copy-of select="description/text()" /></pre></dd> + </dl> + </xsl:template> + +</xsl:stylesheet> |