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'

diff --git a/.gitignore b/.gitignore
index 33ea8fddd301c57d366d46468e8ff7a7901252fe..68854000b911b5e4b9ab14cf5649f5129fc7917c 100644 (file)
--- 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 (file)
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 (file)
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 06af5d3eea15cfed0b39468c29b8822f384b2a5e..54e552a0ff7cb86f7ade5fee3be6d7fcbd9a8777 100644 (file)
--- 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";
 }