Photo by Ehtiram Mammadov: https://www.pexels.com/photo/wooden-door-between-drawers-on-walls-23322329/
After integrating Isso into my Bludit blog, I am slowly starting to receive comments from my dear readers. As Bludit and Isso are separate, there is no easily visible notification of new comments that need to be approved.
As this is a fairly low traffic blog and I'm not constantly checking Isso manually, this has resulted in a comment being stuck in the moderation queue for 7 days. Only approved after the person contacted me directly to point this out.
Problem identified and the solution that immediately came to mind was the following:
- Write a script that will be executed every n minutes by a Systemd timer
- This script retrieves the number of Isso comments, pending approval, from the sqlite3 database file
- If the number is not zero, send a message via Telegram
This should be a feasible solution as long as currently I receive a somewhat low-amount of comments.
Database internals
Isso stores the comments in a sqlite3 database so our first task is to identify the structure (tables, columns) which store the data we need. With .table we get the tables and with .schema comments we get the SQL-Statement which was used to create the corresponding table.
However using PRAGMA table_info(comments); we get a cleaner list of all columns and the associated datatype. https://www.sqlite.org/pragma.html#toc lists all Pragmas in SQLite.
root@admin /opt/isso/db # cp comments.db testcomments.db
root@admin /opt/isso/db # sqlite3 testcomments.db
SQLite version 3.34.1 2021-01-20 14:10:07
Enter ".help" for usage hints.
sqlite> .table
comments preferences threads
sqlite> .schema comments
CREATE TABLE comments ( tid REFERENCES threads(id), id INTEGER PRIMARY KEY, parent INTEGER, created FLOAT NOT NULL, modified FLOAT, mode INTEGER, remote_addr VARCHAR, text VARCHAR, author VARCHAR, email VARCHAR, website VARCHAR, likes INTEGER DEFAULT 0, dislikes INTEGER DEFAULT 0, voters BLOB NOT NULL, notification INTEGER DEFAULT 0);
CREATE TRIGGER remove_stale_threads AFTER DELETE ON comments BEGIN DELETE FROM threads WHERE id NOT IN (SELECT tid FROM comments); END;
sqlite> PRAGMA table_info(comments);
0|tid||0||0
1|id|INTEGER|0||1
2|parent|INTEGER|0||0
3|created|FLOAT|1||0
4|modified|FLOAT|0||0
5|mode|INTEGER|0||0
6|remote_addr|VARCHAR|0||0
7|text|VARCHAR|0||0
8|author|VARCHAR|0||0
9|email|VARCHAR|0||0
10|website|VARCHAR|0||0
11|likes|INTEGER|0|0|0
12|dislikes|INTEGER|0|0|0
13|voters|BLOB|1||0
14|notification|INTEGER|0|0|0
From the first look the column mode looks like what we want. To test this I created a new comment which is pending approvement.
user@host /opt/isso/db # sqlite3 testcomments.db <<< 'select * from comments where mode == 2;'
5|7||1724288099.62894||2|ip.ip.ip.ip|etertert|test||https://admin.brennt.net|0|0||0
And we got confirmation as only the new comment is listed. After approving the comment's mode changes to 1. Therefore we found a way to identify comments pending approval.
The check-isso-comments.sh script
Adding a bit of fail-safe and output we hack together the following script.
If you want to use it you need to fill in the values for TELEGRAM_CHAT_ID & TELEGRAM_BOT_TOKEN. Where TELEGRAM_CHAT_ID is the ID of the person who shall receive the messages and TELEGRAM_BOT_TOKEN takes your Bot's Token for accessing Telegrams HTTP-API.
Please always check https://github.com/ChrLau/scripts/blob/master/check-isso-comments.sh for the current version. I'm not going to update the script in this blogpost anymore.
#!/bin/bash
# vim: set tabstop=2 smarttab shiftwidth=2 softtabstop=2 expandtab foldmethod=syntax :
# Bash strict mode
# read: http://redsymbol.net/articles/unofficial-bash-strict-mode/
set -euo pipefail
IFS=$'\n\t'
# Generic
VERSION="1.0"
#SOURCE="https://github.com/ChrLau/scripts/blob/master/check-isso-comments.sh"
# Values
TELEGRAM_CHAT_ID=""
TELEGRAM_BOT_TOKEN=""
ISSO_COMMENTS_DB=""
# Needed binaries
SQLITE3="$(command -v sqlite3)"
CURL="$(command -v curl)"
# Colored output
RED="\e[31m"
#GREEN="\e[32m"
ENDCOLOR="\e[0m"
# Check that variables are defined
if [ -z "$TELEGRAM_CHAT_ID" ] || [ -z "$TELEGRAM_BOT_TOKEN" ] || [ -z "$ISSO_COMMENTS_DB" ]; then
echo "${RED}This script requires the variables TELEGRAM_CHAT_ID, TELEGRAM_BOT_TOKEN and ISSO_COMMENTS_DB to be set. Define them at the top of this script.${ENDCOLOR}"
exit 1;
fi
# Test if sqlite3 is present and executeable
if [ ! -x "${SQLITE3}" ]; then
echo "${RED}This script requires sqlite3 to connect to the database. Exiting.${ENDCOLOR}"
exit 2;
fi
# Test if ssh is present and executeable
if [ ! -x "${CURL}" ]; then
echo "${RED}This script requires curl to send the message via Telegram API. Exiting.${ENDCOLOR}"
exit 2;
fi
# Test if the Isso comments DB file is readable
if [ ! -r "${ISSO_COMMENTS_DB}" ]; then
echo "${RED}The ISSO sqlite3 database ${ISSO_COMMENTS_DB} is not readable. Exiting.${ENDCOLOR}"
exit 3;
fi
COMMENT_COUNT=$(echo "select count(*) from comments where mode == 2" | sqlite3 "${ISSO_COMMENTS_DB}")
TEMPLATE=$(cat <<TEMPLATE
<strong>ISSO Comment checker</strong>
<pre>${COMMENT_COUNT} comments need approval</pre>
TEMPLATE
)
if [ "${COMMENT_COUNT}" -gt 0 ]; then
${CURL} --silent --output /dev/null \
--data-urlencode "chat_id=${TELEGRAM_CHAT_ID}" \
--data-urlencode "text=${TEMPLATE}" \
--data-urlencode "parse_mode=HTML" \
--data-urlencode "disable_web_page_preview=true" \
"https://api.telegram.org/bot${TELEGRAM_BOT_TOKEN}/sendMessage"
fi
Executing this script on the command-line will already sent a notification to me via Telegram. However I do want this to be automated hence I make use of a Systemd timer.
Systemd configuration
I use the following timer to get notified between 10 to 22 o'clock as there is no need to spam me with message when I can't do anything. I create the files as /etc/systemd/system/isso-comments.timer and /etc/systemd/system/isso-comments.service.
user@host ~ # systemctl cat isso-comments.timer
# /etc/systemd/system/isso-comments.timer
[Unit]
Description=Checks for new, unapproved Isso comments
[Timer]
# Documentation: https://www.freedesktop.org/software/systemd/man/latest/systemd.time.html#Calendar%20Events
OnCalendar=*-*-* 10..22:00:00
Unit=isso-comments.service
[Install]
WantedBy=default.target
The actual script is started by the following service unit file.
user@host ~ # systemctl cat isso-comments.service
# /etc/systemd/system/isso-comments.service
[Unit]
Description=Check for new unapproved Isso comments
After=network-online.target
Wants=network-online.target
[Service]
# Allows the execution of multiple ExecStart parameters in sequential order
Type=oneshot
# Show status "dead" after commands are executed (this is just commands being run)
RemainAfterExit=no
ExecStart=/usr/local/bin/check-isso-comments.sh
[Install]
WantedBy=default.target
After that it's the usual way of activating & starting a new Systemd unit and timer:
user@host ~ # systemctl daemon-reload
user@host ~ # systemctl enable isso-comments.service
Created symlink /etc/systemd/system/default.target.wants/isso-comments.service → /etc/systemd/system/isso-comments.service.
user@host ~ # systemctl enable isso-comments.timer
Created symlink /etc/systemd/system/default.target.wants/isso-comments.timer → /etc/systemd/system/isso-comments.timer.
user@host ~ # systemctl start isso-comments.timer
user@host ~ # systemctl status isso-comments.timer
● isso-comments.timer - Checks for new, unapproved Isso comments
Loaded: loaded (/etc/systemd/system/isso-comments.timer; enabled; vendor preset: enabled)
Active: active (waiting) since Mon 2024-09-09 19:33:45 CEST; 2s ago
Trigger: Mon 2024-09-09 20:00:00 CEST; 26min left
Triggers: ● isso-comments.service
Sep 09 19:33:45 admin systemd[1]: Started Checks for new, unapproved Isso comments.
user@host ~ # systemctl start isso-comments.service
user@host ~ # systemctl status isso-comments.service
● isso-comments.service - Check for new unapproved Isso comments
Loaded: loaded (/etc/systemd/system/isso-comments.service; enabled; vendor preset: enabled)
Active: inactive (dead) since Mon 2024-09-09 19:33:58 CEST; 14s ago
TriggeredBy: ● isso-comments.timer
Process: 421812 ExecStart=/usr/local/bin/check-isso-comments.sh (code=exited, status=0/SUCCESS)
Main PID: 421812 (code=exited, status=0/SUCCESS)
CPU: 23ms
Sep 09 19:33:58 admin systemd[1]: Starting Check for new unapproved Isso comments...
Sep 09 19:33:58 admin systemd[1]: isso-comments.service: Succeeded.
Sep 09 19:33:58 admin systemd[1]: Finished Check for new unapproved Isso comments.
user@host ~ # systemctl list-timers
NEXT LEFT LAST PASSED UNIT ACTIVATES
Mon 2024-09-09 20:00:00 CEST 22min left n/a n/a isso-comments.timer isso-comments.service
Tue 2024-09-10 00:00:00 CEST 4h 22min left Mon 2024-09-09 00:00:00 CEST 19h ago logrotate.timer logrotate.service
Tue 2024-09-10 00:00:00 CEST 4h 22min left Mon 2024-09-09 00:00:00 CEST 19h ago man-db.timer man-db.service
Tue 2024-09-10 06:10:34 CEST 10h left Mon 2024-09-09 06:06:10 CEST 13h ago apt-daily-upgrade.timer apt-daily-upgrade.service
Tue 2024-09-10 11:42:59 CEST 16h left Mon 2024-09-09 19:33:17 CEST 4min 28s ago apt-daily.timer apt-daily.service
Tue 2024-09-10 14:22:35 CEST 18h left Mon 2024-09-09 14:22:35 CEST 5h 15min ago systemd-tmpfiles-clean.timer systemd-tmpfiles-clean.service
Sun 2024-09-15 03:10:04 CEST 5 days left Sun 2024-09-08 03:10:27 CEST 1 day 16h ago e2scrub_all.timer e2scrub_all.service
Mon 2024-09-16 01:21:39 CEST 6 days left Mon 2024-09-09 00:45:54 CEST 18h ago fstrim.timer fstrim.service
9 timers listed.
Pass --all to see loaded but inactive timers, too.
And now I'm getting informed in time of new comments. Yai!
