issue: track messages in database

Replace file storage by PostgreSQL table, similar to comments in commit
v4.0-21-g0cc6251412 (2019-10-27) [nieuws/replies: read contents from database].

Existing files are imported similar to earlier comments:

	ls melding/2*/*.html | perl -MFile::Slurp -lnE ' #/
		my ($page, $date, $name) = split m{/};
		$name =~ s/(\d+-\d+)-// and $date .= "-$1";
		my $closed = qx(stat -c%y "$page/.tags/opgelost/$date-$name" 2>/dev/null);
		chomp $closed;
		$name =~ s/\.html$//;
		my $html = read_file($_);
		$html =~ s{<h2>(.*?)</h2>\n+}{}; my $title = $1;
		chomp, s/\\\K/\\/g, s/\n/\\n/g, s/\t/\\t/g for $html;
		say join "\t", $page, $date, $name, $title, $html, $closed || "\\N";
	' | psql lijtweg -c 'COPY issues (page, created, link, subject, body, closed) FROM STDIN'

Afterwards, the following queries can be executed to convert and clean up
these imported rows further:

	-- rename issue references to new page ids
	UPDATE comments c SET page = (SELECT coalesce((SELECT page||'/'||id
		FROM issues WHERE c.page LIKE page || '/%-' || link), c.page));

	-- move authorship contents to dedicated column
	SELECT id, regexp_matches(body, '<p class="right">(?:Ingediend door |<em>)?([^<]*)')
		FROM issues WHERE body ~ 'right';
	UPDATE issues SET author = 'cees' WHERE body ~ '<p class="right">.*Cees';
	UPDATE issues SET author = '301' WHERE body ~ '<p class="right">.*de Roos';
	UPDATE issues SET body = regexp_replace(body, E'\n+<p class="right">.*</p>$', '')
		WHERE body ~ 'right';

diff --git a/issue/index.html b/issue/index.html
new file mode 100644 (file)
index 0000000..3cea55d
--- /dev/null
+++ b/issue/index.html
@@ -0,0 +1,15 @@
+<h2>Issues</h2>
+
+[[issuelist]]
+
+<h3>Nieuw issue</h3>
+
+<form method="post" action=""><ul class="grid">
+<li><label for="subject">Onderwerp:</label>
+       <input type="text" id="subject" name="subject" size="60" value="" />
+</li>
+<li><label for="body">Beschrijving:</label>
+       <textarea id="body" name="body" cols="60" rows="3"></textarea>
+</li>
+</ul><p><input type="submit" value="Aanmaken" /></p>
+</ul></form>

diff --git a/issue/index.php b/issue/index.php
new file mode 100644 (file)
index 0000000..3d62ced
--- /dev/null
+++ b/issue/index.php
@@ -0,0 +1,55 @@
+<?php
+global $User, $Db;
+require_once 'database.inc.php';
+$id = trim($Args, '/');
+
+if ($id) {
+       $Article->title = "Issue #$id";
+
+       $row = $Db->query(
+               'SELECT * FROM issues WHERE id = ?', [$id]
+       )->fetch();
+       if (!$row) throw new Exception('Issuenummer niet gevonden');
+
+       $Article->title .= ': '.htmlspecialchars($row->subject);
+       print "<h2>{$Article->title}</h2>\n";
+       print $row->body;
+       if ($row->closed) {
+               printf('<p><strong>%s</strong> <small class=date>%s</small></p>'."\n",
+                       'Opgelost', showdate(preg_split('/\D/', $row->closed))
+               );
+       }
+       print placeholder_include('reply');
+       return;
+}
+
+if ($_POST) {
+               $html = nl2br(htmlspecialchars($_POST['body']));
+               $html = empty($html) ? NULL : "<p>$html</p>";
+               $query = $Db->insert('issues', [
+                       'page'    => $Page,
+                       'subject' => $_POST['subject'],
+                       'body'    => $html,
+                       'author'  => $User->login,
+               ]);
+               if (!$query->rowCount()) {
+                       throw new Exception('Issue niet opgeslagen.');
+               }
+               $_POST = [];
+}
+
+$query = $Db->query('SELECT * FROM issues ORDER BY created DESC');
+
+ob_start();
+print '<ul>';
+while ($row = $query->fetch()) {
+       printf('<li><a href="%s">%s <small class="date">%s</small></a>',
+               "/$Page/{$row->id}",
+               sprintf($row->closed ? '<strike>%s</strike>' : '%s',
+                       htmlspecialchars($row->subject)),
+               showdate(array_slice(preg_split('/\D/', $row->created), 0, 3))
+       );
+       print "</li>\n";
+}
+print "</ul>\n";
+$Place['issuelist'] = ob_get_clean();

diff --git a/widget/comments.sql b/widget/comments.sql
index ff797445ff1f81631afd74855ce0a2c99ac24b90..54088778108f5bbe4053b61b977bb838137e1e78 100644 (file)
--- a/widget/comments.sql
+++ b/widget/comments.sql
@@ -1,7 +1,17 @@
+CREATE TABLE issues (
+       page       text        NOT NULL DEFAULT 'issue',
+       subject    text,
+       body       text,
+       created    timestamptz          DEFAULT now(),
+       closed     timestamptz          DEFAULT now(),
+       author     text,
+       id         serial      NOT NULL PRIMARY KEY
+);
+
 CREATE TABLE comments (
        page       text,
        message    text,
        created    timestamptz DEFAULT now(),
        author     text,
-       id         serial      PRIMARY KEY
+       id         serial      NOT NULL PRIMARY KEY
 );

diff --git a/widget/reply.php b/widget/reply.php
index 6fc1fa569e88354becdac1d1093ce703518509d2..2bc9014944c69e2388905385db548943d2a21472 100644 (file)
--- a/widget/reply.php
+++ b/widget/reply.php
@@ -1,5 +1,5 @@
 <?php
-global $User;
+global $User, $Db;
 require_once 'database.inc.php';
 
 print '<h3>Reacties</h3>'."\n";
@@ -19,7 +19,7 @@ if ($_POST) {
                $_POST['reply'] = NULL;
        }
        catch (Exception $e) {
-               print '<p class=warn>Antwoord niet opgeslagen.</p>'."\n\n";
+               print "<p class=warn>Antwoord niet opgeslagen: {$e->getMessage()}.</p>\n\n";
        }
 }