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';
--- /dev/null
+<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>
--- /dev/null
+<?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();
+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
);
<?php
-global $User;
+global $User, $Db;
require_once 'database.inc.php';
print '<h3>Reacties</h3>'."\n";
$_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";
}
}