From fac6ba761f971deb77a5686ead99ec6534f931d2 Mon Sep 17 00:00:00 2001
From: Mischa POSLAWSKY <perl@shiar.org>
Date: Thu, 7 Nov 2019 05:51:30 +0100
Subject: [PATCH] issue: record row changes in journal

---
 widget/comments.sql |  9 +++++++++
 widget/reply.php    | 20 ++++++++++++++++++--
 2 files changed, 27 insertions(+), 2 deletions(-)

diff --git a/widget/comments.sql b/widget/comments.sql
index 8a06558..2a4993b 100644
--- a/widget/comments.sql
+++ b/widget/comments.sql
@@ -18,3 +18,12 @@ CREATE TABLE comments (
 	author     text,
 	id         serial      NOT NULL PRIMARY KEY
 );
+
+CREATE TABLE journal (
+	comment_id integer     NOT NULL REFERENCES comments (id),
+	property   text        NOT NULL DEFAULT 'attr',
+	col        text        NOT NULL,
+	old_value  text,
+	value      text,
+	id         serial      NOT NULL PRIMARY KEY
+);
diff --git a/widget/reply.php b/widget/reply.php
index 328a58f..ab04137 100644
--- a/widget/reply.php
+++ b/widget/reply.php
@@ -16,9 +16,10 @@ if ($_POST) {
 		if (!$query->rowCount()) {
 			throw new Exception('Fout bij opslaan');
 		}
+		$newcomment = $Db->dbh->lastInsertId('comments_id_seq');
 
 		if (isset($Issue)) {
-			$row = ['updated' => ['now()']];
+			$row = [];
 			foreach (['assign'] as $col) {
 				if (!isset($_POST[$col])) continue;
 				$row[$col] = $_POST[$col] ?: NULL;
@@ -29,7 +30,22 @@ if ($_POST) {
 					$row['closed'] = $reset ? ['now()'] : NULL;
 				}
 			}
-			$Db->set('issues', $row, ['id = ?', $Issue->id]);
+			$derived = ['updated' => ['now()']];
+			$filter = ['id = ? RETURNING *', $Issue->id];
+			$subquery = $Db->set('issues', $row + $derived, $filter);
+
+			if ($updated = $subquery->fetch()) {
+				foreach (array_keys($row) as $col) {
+					if ($updated->$col === $Issue->$col) continue; # unaltered
+					$Db->set('journal', [
+						'comment_id' => $newcomment,
+						'property'   => 'attr',
+						'col'        => $col,
+						'old_value'  => $Issue->$col,
+						'value'      => $updated->$col,
+					]);
+				}
+			}
 		}
 		$_POST['reply'] = NULL;
 	}
-- 
2.30.0