Update ViMbAdmin mail and home size using Dovecots quota dict

cron.d.vimbadmin-mb-size

#
# /etc/cron.d/vimbadmin-mb-size
# schedules periodic execution of vimbadmin-mb-size script
#

# m   h  d  m  w    user    command
*/5   *  *  *  *    root    [ -x /usr/local/bin/vimbadmin-mb-size ] && /usr/local/bin/vimbadmin-mb-size --database "vimbadmin" --quota-table "quota"

cron.hourly.vimbadmin

#!/bin/sh
export TERM="vt100"
VIMBADMIN_PATH="/var/www/html/vimbadmin"

# delete pending mailboxes
sudo -u mail -- /usr/bin/env php "$VIMBADMIN_PATH/bin/vimbtool.php" -a mailbox.cli-delete-pending

# archive pending mailboxes
sudo -u mail -- /usr/bin/env php "$VIMBADMIN_PATH/bin/vimbtool.php" -a archive.cli-archive-pendings
sudo -u mail -- /usr/bin/env php "$VIMBADMIN_PATH/bin/vimbtool.php" -a archive.cli-restore-pendings
sudo -u mail -- /usr/bin/env php "$VIMBADMIN_PATH/bin/vimbtool.php" -a archive.cli-delete-pendings

dovecot-90-quota.conf

dict {
  quota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
}

plugin {
  quota = dict:user::proxy::quota
}

dovecot-dict-sql.conf.ext

connect = host=localhost user=vimbadmin password=password dbname=vimbadmin

map {
  pattern = priv/quota/storage
  table = quota
  username_field = user
  value_field = bytes
}
map {
  pattern = priv/quota/messages
  table = quota
  username_field = user
  value_field = messages
}

install.sql

USE vimbadmin;

CREATE TABLE quota (
  user varchar(100) not null,
  bytes bigint not null default 0,
  messages integer not null default 0,
  primary key (username)
);

vimbadmin-mb-size

#!/bin/bash
# vimbadmin-mb-size
# Version 1.2 (build 20190411)
#
# SHORT DESCRIPTION:
#     Update ViMbAdmin mail and home size using a Dovecot quota SQL dict
#
# DEPENDENCIES:
#     - `iniparse-cli', see <https://github.com/MeraX/iniparse-cli>
#     - various common shell tools: bash, sed, tr, cut
#
# COPYRIGHT AND LICENSING:
#     Copyright (C) 2014-2019 Daniel Rudolf <http://www.daniel-rudolf.de/>
#
#     This program is free software: you can redistribute it and/or modify
#     it under the terms of the GNU General Public License as published by
#     the Free Software Foundation, version 3 of the License only.
#
#     This program is distributed in the hope that it will be useful,
#     but WITHOUT ANY WARRANTY; without even the implied warranty of
#     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
#     GNU General Public License for more details.
#
#     You should have received a copy of the GNU General Public License
#     along with this program. If not, see <http://www.gnu.org/licenses/>.

LC_ALL=C
APP_NAME="$(basename "$0")"

VERSION="1.2"
BUILD="20190411"

function showUsage() {
	echo "Usage:"
	echo " $APP_NAME [OPTION]..."
}

# mysql query helper (supports multiple querys)
function mysql_query() {
	IFS=';'; for MYSQL_QUERY in $1; do
		MYSQL_QUERY="${MYSQL_QUERY#"${MYSQL_QUERY%%[![:space:]]*}"}" # remove leading whitespace characters
		MYSQL_QUERY="${MYSQL_QUERY%"${MYSQL_QUERY##*[![:space:]]}"}" # remove trailing whitespace characters
		mysql --defaults-file="/etc/mysql/debian.cnf" --silent --skip-column-names --execute="$MYSQL_QUERY" "$MYSQL_DATABASE"

		if [ $? -ne 0 ]; then
			echo "ERROR: Invalid MySQL query \`$MYSQL_QUERY'" >&2
			exit 1
		fi
	done
}

# parse options
MYSQL_DATABASE="vimbadmin"
MYSQL_DATABASE_QUOTA_TABLE="quota"
VERBOSE=0

while [ $# -gt 0 ]; do
	if [ "$1" == "--quota-table" ]; then
		if [ $# -lt 2 ]; then
			echo "ERROR: You must specify a table name" >&2
			exit 1
		fi

		MYSQL_DATABASE_QUOTA_TABLE="$2"
		shift 2

	elif [ "$1" == "--database" ]; then
		if [ $# -lt 2 ]; then
			echo "ERROR: You must specify a database name" >&2
			exit 1
		fi

		MYSQL_DATABASE="$2"
		shift 2

	elif [ "$1" == "-v" ] || [ "$1" == "--verbose" ]; then
		VERBOSE=$(($VERBOSE + 1))
		shift

	elif [ "${1:0:2}" == "-v" ]; then
		VERBOSE=$(($VERBOSE + `echo "$1" | sed 's/[^v]*//g' | wc -c` - 1))
		shift

	elif [ "$1" == "--help" ] || [ "$1" == "-h" ]; then
		showUsage
		echo
		echo "Update ViMbAdmin mail and home size using a Dovecot quota SQL dict"
		echo
		echo "Application options:"
		echo "      --database      ViMbAdmin database name (default: vimbadmin)"
		echo "      --quota-table   Dovecots quota dict table name."
		echo "                      Use the \`database.table' syntax to query a"
		echo "                      table of a foreign database (default: \`quota')"
		echo "  -v, --verbose       increase verbosity"
		echo
		echo "Help options:"
		echo "	-h, --help          display this help and exit"
		echo "      --version       output version information and exit"
		echo
		echo "Please report bugs using the contact form at <http://www.daniel-rudolf.de/>"
		exit 0

	elif [ "$1" == "--version" ]; then
		echo "vimbadmin-mb-size $VERSION (build $BUILD)"
		echo "Copyright (C) 2014-2019 Daniel Rudolf"
		echo "License GPLv3: GNU GPL version 3 only <http://gnu.org/licenses/gpl.html>."
		echo "This is free software: you are free to change and redistribute it."
		echo "There is NO WARRANTY, to the extent permitted by law."
		echo
		echo "Written by Daniel Rudolf <http://www.daniel-rudolf.de/>"
		exit 0

	# last param should be VIMBADMIN_PATH
	elif [ $# -eq 1 ]; then
		VIMBADMIN_PATH="$1"
		shift

	else
		echo "ERROR: Unknown option \`$1'" >&2
		showUsage
		exit 1
	fi
done

# require root, necessary to read /etc/mysql/debian.cnf
if [ "$(id -u)" != "0" ]; then
	echo "ERROR: You must be root to run this script" >&2
	exit 1
fi

# some debug output
[ "$VERBOSE" -gt 3 ] && echo "Mail database name: $MYSQL_DATABASE"
[ "$VERBOSE" -gt 3 ] && echo "Dovecot quota table: $MYSQL_DATABASE_QUOTA_TABLE"
[ "$VERBOSE" -gt 3 ] && echo

# get list of mailboxes with quota
IFS=$'\n'; for MAILBOX in `mysql_query "SELECT user, bytes FROM $MYSQL_DATABASE_QUOTA_TABLE"`; do
	MAILBOX_NAME="$(echo "$MAILBOX" | cut -d $'\t' -f 1)"
	[ "$VERBOSE" -gt 0 ] && echo "Processing \`$MAILBOX_NAME'..."

	# skip unknown mailboxes
	if [ -z `mysql_query "SELECT 1 FROM mailbox WHERE username = '$MAILBOX_NAME'"` ]; then
		continue
	fi

	# get and validate maildir size
	MAILBOX_MAILDIR_SIZE="$(echo "$MAILBOX" | cut -d $'\t' -f 2)"
	if ! [[ "$MAILBOX_MAILDIR_SIZE" =~ ^[0-9]+$ ]] ; then
		echo "WARNING: Invalid mail size \`$MAILBOX_MAILDIR_SIZE' of mailbox \`$MAILBOX_NAME'" >&2

		MAILBOX_MAILDIR_SIZE="NULL"
		[ "$VERBOSE" -gt 1 ] && echo "	Mail size: unknown"
		
	else
		[ "$VERBOSE" -gt 1 ] && echo "	Mail size: $MAILBOX_MAILDIR_SIZE bytes"
	fi

	# get and validate homedir size
	MAILBOX_HOMEDIR_PATH="$(mysql_query "SELECT homedir FROM mailbox WHERE username = '$MAILBOX_NAME'")"
	if [ -z "$MAILBOX_HOMEDIR_PATH" ] || [ "$MAILBOX_HOMEDIR_PATH" == "NULL" ]; then
		MAILBOX_HOMEDIR_SIZE="NULL"
		[ "$VERBOSE" -gt 1 ] && echo "	Home size: unknown"
		
	elif ! [ -d "$MAILBOX_HOMEDIR_PATH" ]; then
		echo "WARNING: Invalid home \`$MAILBOX_HOMEDIR_PATH' of mailbox \`$MAILBOX_NAME'" >&2

		MAILBOX_HOMEDIR_SIZE="NULL"
		[ "$VERBOSE" -gt 1 ] && echo "	Home size: unknown"
		
	else
		MAILBOX_HOMEDIR_SIZE="$(du --bytes --summarize "$MAILBOX_HOMEDIR_PATH" | cut -d $'\t' -f 1)"

		if ! [[ "$MAILBOX_HOMEDIR_SIZE" =~ ^[0-9]+$ ]] ; then
			echo "WARNING: Invalid home size \`$MAILBOX_MAILDIR_SIZE' of mailbox \`$MAILBOX_NAME'"

			MAILBOX_HOMEDIR_SIZE="NULL"
			[ "$VERBOSE" -gt 1 ] && echo "	Home size: unknown"
		else
			[ "$VERBOSE" -gt 1 ] && echo "	Home size: $MAILBOX_HOMEDIR_SIZE bytes"
		fi
	fi

	[ "$VERBOSE" -gt 2 ] && echo -n "	Updating database... "
	mysql_query "UPDATE mailbox SET homedir_size = $MAILBOX_HOMEDIR_SIZE, maildir_size = $MAILBOX_MAILDIR_SIZE, size_at = NOW() WHERE username = '$MAILBOX_NAME'"
	[ "$VERBOSE" -gt 2 ] && echo "done"
	[ "$VERBOSE" -gt 1 ] && echo ""
done

exit 0