#!/usr/bin/env bash
# VICIfast Research, Report 01: "The Two Bottlenecks Inside a Busy VICIdial Server"
# Check 2: does VICIdial's lead search read your whole call log?
#
# READ-ONLY. Uses EXPLAIN, which plans the search without running it, so it
# costs nothing on a live floor.
# Usage:  sudo bash vicidial-check-lead-search.sh
#
# Admin > Search for a lead by phone number also lists that number's calls:
#   SELECT ... FROM vicidial_log WHERE phone_number = '...'
# Stock VICIdial has no index on vicidial_log.phone_number, so on MyISAM each
# search reads every row of the call log with the table locked.
set -u
# Shared by the report's database checks: connect the way VICIdial does,
# from /etc/astguiclient.conf, through a temporary client file only root can
# read, so the password never appears in `ps` or on screen.
CONF=${ASTGUICLIENT_CONF:-/etc/astguiclient.conf}
MYCNF=$(mktemp)
chmod 600 "$MYCNF"
trap 'rm -f "$MYCNF"' EXIT
conf() { awk -F'=>' -v k="$1" '{ gsub(/[[:space:]\r]/, "", $1) } $1 == k { gsub(/^[[:space:]]+|[[:space:]\r]+$/, "", $2); print $2; exit }' "$CONF"; }
if [ -r "$CONF" ]; then
  printf '[client]\nhost=%s\nuser=%s\npassword=%s\nport=%s\n' \
    "$(conf VARDB_server)" "$(conf VARDB_user)" "$(conf VARDB_pass)" "$(conf VARDB_port)" > "$MYCNF"
  DB=$(conf VARDB_database)
else
  echo "Cannot read $CONF: run this as root on a VICIdial database server." >&2
  exit 1
fi
M() { mysql --defaults-extra-file="$MYCNF" -N -B "$@"; }
M -e 'SELECT 1' >/dev/null 2>&1 || { echo "Cannot connect to MySQL with the credentials in $CONF." >&2; exit 1; }

echo "== Lead-search check on $(hostname -s), $(date '+%Y-%m-%d %H:%M:%S %Z')"
read -r rows mb engine <<<"$(M -e "SELECT table_rows, ROUND((data_length+index_length)/1048576), engine
  FROM information_schema.tables WHERE table_schema='$DB' AND table_name='vicidial_log'")"
oldest=$(M "$DB" -e "SELECT MIN(call_date) FROM vicidial_log")
echo "-- vicidial_log: ${rows} rows, ${mb} MB, ${engine}, oldest call ${oldest}"
idx=$(M -e "SELECT GROUP_CONCAT(DISTINCT index_name ORDER BY index_name) FROM information_schema.statistics
  WHERE table_schema='$DB' AND table_name='vicidial_log'")
echo "-- indexes: ${idx}"
plan=$(M "$DB" -e "EXPLAIN SELECT lead_id FROM vicidial_log WHERE phone_number='0000000000'" | awk -F'\t' '{ print "type=" $4 ", key=" $6 ", rows read=" $9 }')
echo "-- the lead search's plan: ${plan}"
if crontab -l 2>/dev/null | grep -q ADMIN_archive_log_tables; then
  echo "-- ADMIN_archive_log_tables.pl is scheduled (old calls are archived)"
else
  echo "-- ADMIN_archive_log_tables.pl is not in root's crontab (the call log keeps growing)"
fi
echo
echo "-- Verdict"
case "$plan" in
  *type=ref*key=phone_number*)
    echo "   Indexed: a lead search reads only that number's calls." ;;
  *)
    secs=$(( rows / 150000 + 1 ))
    echo "   Every lead search by phone reads all ${rows} rows of vicidial_log."
    [ "$engine" = "MyISAM" ] && echo "   On MyISAM the dialer's writes wait while it does."
    echo "   The fix (section 6 of the report), OFF-SHIFT: it locks the table while it builds,"
    echo "   roughly ${secs} s at ~150,000 rows a second (measured on a 2.37-million-row copy):"
    echo
    echo "     ALTER TABLE vicidial_log ADD INDEX phone_number (phone_number);"
    ;;
esac