From 0cc6251412d85492cbd0af0d50176d3c70c25311 Mon Sep 17 00:00:00 2001
From: Mischa POSLAWSKY <perl@shiar.org>
Date: Thu, 17 Oct 2019 05:20:55 +0200
Subject: [PATCH] nieuws/replies: read contents from database

Replace file storage by a PostgreSQL table.  Resign to the additional
dependency for this kind of social feature since it's much more appropriate
(especially with upcoming extensions), and optional similar to external
comment services.

Identical results after importing existing files:

	ls melding/2*/*/*.html | perl -MFile::Slurp -lnE '
		my ($page, $meta) = m{(.*)/(.*)};
		my ($time, $author) = split /[:.]/, $meta;
		$time .= "00" while length($time) < 14;
		$time =~ s/^(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})/$1-$2-$3 $4:$5:/g;
		my $html = read_file($_);
		chomp, s/\\\K/\\/g, s/\n/\\n/g, s/\t/\\t/g for $html;
		say join "\t", $page, $author, $time, $html;
	' | psql -c 'COPY comments (page, author, created, message) FROM STDIN'
---
 .gitignore                |  3 +++
 database.inc.php          |  4 ++++
 widget/comments.sql       |  7 +++++++
 widget/nieuws/replies.php | 26 ++++++++++++--------------
 4 files changed, 26 insertions(+), 14 deletions(-)
 create mode 100644 database.inc.php
 create mode 100644 widget/comments.sql

diff --git a/.gitignore b/.gitignore
index 33ea8fd..6885400 100644
--- a/.gitignore
+++ b/.gitignore
@@ -1,3 +1,6 @@
+# local configuration
+/.dbconfig.inc.php
+
 # assets built by make rules
 /lib
 !/lib/photoswipe.html
diff --git a/database.inc.php b/database.inc.php
new file mode 100644
index 0000000..6e040d4
--- /dev/null
+++ b/database.inc.php
@@ -0,0 +1,4 @@
+<?php
+$dsn = require '.dbconfig.inc.php';
+$options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ];
+$Db = new PDO($dsn, NULL, NULL, $options);
diff --git a/widget/comments.sql b/widget/comments.sql
new file mode 100644
index 0000000..ff79744
--- /dev/null
+++ b/widget/comments.sql
@@ -0,0 +1,7 @@
+CREATE TABLE comments (
+	page       text,
+	message    text,
+	created    timestamptz DEFAULT now(),
+	author     text,
+	id         serial      PRIMARY KEY
+);
diff --git a/widget/nieuws/replies.php b/widget/nieuws/replies.php
index 06af5d3..54e552a 100644
--- a/widget/nieuws/replies.php
+++ b/widget/nieuws/replies.php
@@ -1,17 +1,16 @@
 <?php
 global $User;
+require_once 'database.inc.php';
 
 print '<h3>Reacties</h3>'."\n";
-$pagelink = $Page.$Args;
 
 if ($_POST) {
 	try {
-		@mkdir($pagelink);
-		$target = $pagelink.'/'.date('YmdHis').':'.$User->login.'.html';
 		$html = nl2br(htmlspecialchars($_POST['reply']));
-		$html = "<p>$html</p>\n";
-		$written = file_put_contents($target, $html);
-		if ($written === FALSE) {
+		$html = "<p>$html</p>";
+		$query = $Db->prepare('INSERT INTO comments (page, message, author) VALUES (?, ?, ?)');
+		$query->execute([ $Page, $html, $User->login ]);
+		if (!$query->rowCount()) {
 			throw new Exception('Fout bij opslaan');
 		}
 		$_POST['reply'] = NULL;
@@ -21,19 +20,18 @@ if ($_POST) {
 	}
 }
 
+$query = $Db->prepare('SELECT * FROM comments WHERE page = ? ORDER BY created');
+$query->execute([$Page]);
+
 print '<ul class="replies">';
 
-foreach (glob("$pagelink/*.html") as $reply) {
-	preg_match('</(\d{2,14}) : ([^:]*) [^/]* \.html$>x', $reply, $replymeta);
-	if (!$replymeta) continue;
-	$replydate = str_split($replymeta[1], 2);
-	$replydate[0] = array_shift($replydate) . $replydate[0];
-	$replyuser = new User("profile/{$replymeta[2]}");
+while ($row = $query->fetch(PDO::FETCH_OBJ)) {
+	$rowuser = new User("profile/{$row->author}");
 	print '<li>';
 	printf('<strong>%s</strong> <small class=date>%s</small>',
-		$replyuser->html, showdate($replydate)
+		$rowuser->html, showdate(preg_split('/\D/', $row->created))
 	);
-	printf("<blockquote>%s</blockquote>\n", file_get_contents($reply));
+	printf("<blockquote>%s</blockquote>\n", $row->message);
 	print "</li>\n";
 }
 
-- 
2.30.0