#!/bin/bash
# VICIfast Research, Report 01: The Two Bottlenecks Inside a Busy VICIdial Server
# Test harness. FOR A TEST SERVER, NOT A LIVE FLOOR. Read it before you run it.
# The isolated local engine tests (section 2): run against a private MariaDB loaded with
# VICIdial's schema, never a server in use. It writes to that database.
#
# Local, network-free reproduction of the engine tests in ../FINDINGS.md
# (sections L1–L4). Needs mariadbd/mariadb 10.6 on PATH and the canonical
# VICIdial schema (extras/MySQL_AST_CREATE_tables.sql in VICIdial's SVN checkout).
#
#   ./local-engine-tests.sh /path/to/vicidial-schema.sql /path/to/empty/workdir
#
# Starts a private MariaDB on a unix socket only (--skip-networking), never
# touches a system database, and stops it at the end.
set -u
SCHEMA=${1:?schema.sql}; D=${2:?workdir}
mkdir -p "$D/data"
M="mariadb --no-defaults -S $D/s.sock -uroot"
ts(){ date +%s.%N; }
start(){ mariadbd --no-defaults --datadir="$D/data" --socket="$D/s.sock" --skip-networking \
  --pid-file="$D/m.pid" --log-error="$D/err.log" --innodb-buffer-pool-size=128M "$@" >/dev/null 2>&1 &
  for i in $(seq 1 60); do $M -e 'select 1' >/dev/null 2>&1 && return; sleep 1; done; echo "server did not start"; exit 1; }
stop(){ kill "$(cat "$D/m.pid")"; while pgrep -f "$D/data" >/dev/null; do sleep 1; done; }

[ -d "$D/data/mysql" ] || mariadb-install-db --no-defaults --datadir="$D/data" --auth-root-authentication-method=normal >/dev/null
start

echo "== L1: load the schema as MyISAM / InnoDB / Aria (MEMORY tables converted too)"
for e in MyISAM InnoDB Aria; do
  $M -e "drop database if exists v_$e; create database v_$e"
  sed -E "s/ENGINE=(MyISAM|MEMORY)/ENGINE=$e/" "$SCHEMA" | $M --force "v_$e" > "$D/load_$e.log" 2>&1
  echo "$e: $($M -N -e "select count(*) from information_schema.tables where table_schema='v_$e'") tables, $(grep -c 'ERROR 1118' "$D/load_$e.log") 'Row size too large'"
done

echo "== L2: 3M-row vicidial_log per engine, then UPDATE + 1-row read behind a ~5 s report"
for e in MyISAM Aria InnoDB; do
  $M "v_$e" -e "insert into vicidial_log (uniqueid,lead_id,call_date,status,length_in_sec,campaign_id,user)
    select concat(seq,'.1'), seq, now() - interval (seq mod 86400) second, elt(1+seq mod 4,'NA','A','SALE','DROP'),
    seq mod 300, 'C1', concat('u', seq mod 70) from seq_1_to_3000000"
done
for e in MyISAM Aria InnoDB; do
  a=$(ts); $M "v_$e" -e "select count(*) from vicidial_log where benchmark(8, sha2(uniqueid,512))=0" >/dev/null & R=$!
  sleep 1
  b=$(ts); $M "v_$e" -e "insert into vicidial_log (uniqueid,lead_id,call_date,status) values ('9.$RANDOM$RANDOM',9,now(),'NEW')"; c=$(ts)
  ( s=$(ts); $M "v_$e" -e "update vicidial_log set status='SALE', length_in_sec=61 where uniqueid='5.1'"; echo "$(ts)-$s" | bc > "$D/up.t" ) & U=$!
  sleep 0.3; d=$(ts); $M "v_$e" -e "select status from vicidial_log where uniqueid='7.1'" >/dev/null; f=$(ts)
  wait $U $R; g=$(ts)
  printf "%-7s report %.1fs | INSERT %.2fs | UPDATE %.2fs | next 1-row read %.2fs\n" "$e" \
    "$(echo "$g-$a"|bc)" "$(echo "$c-$b"|bc)" "$(cat "$D/up.t")" "$(echo "$f-$d"|bc)"
done

echo "== L3: per-user MAX_STATEMENT_TIME bounds how long a report can hold a MyISAM lock"
$M -e "create user if not exists 'stats'@'localhost' with max_statement_time 1; grant select on v_MyISAM.* to 'stats'@'localhost'"
for u in root stats; do
  a=$(ts); mariadb --no-defaults -S "$D/s.sock" -u$u v_MyISAM -e "select count(*) from vicidial_log where benchmark(8, sha2(uniqueid,512))=0" > "$D/r.$u" 2>&1 & P=$!
  sleep 0.3; b=$(ts); $M v_MyISAM -e "update vicidial_log set status='SALE' where uniqueid='5.1'"; c=$(ts); wait $P; e=$(ts)
  printf "report as %-5s ran %.1fs | UPDATE behind it waited %.2fs\n" $u "$(echo "$e-$a"|bc)" "$(echo "$c-$b"|bc)"
done

echo "== L4: kill -9 the server mid-write, three times; CHECK TABLE each engine"
stop; start --myisam-recover-options=OFF
for r in 1 2 3; do
  touch "$D/go"
  for e in MyISAM Aria InnoDB; do ( while [ -f "$D/go" ]; do timeout 20 $M "v_$e" -e "update vicidial_log set length_in_sec=length_in_sec+1 where call_date > now() - interval 3 hour; insert into vicidial_log (uniqueid,lead_id,call_date,status) select concat('k$r.',seq,'.',floor(rand()*1e9)), seq, now(), 'NEW' from seq_1_to_2000" >/dev/null 2>&1 || sleep 0.2; done ) & done
  sleep 5; rm -f "$D/go"; kill -9 "$(cat "$D/m.pid")"; wait
  start --myisam-recover-options=OFF
  printf "kill #%d: " $r; for e in MyISAM Aria InnoDB; do printf "%s=%s  " $e "$($M -N "v_$e" -e 'check table vicidial_log' 2>&1 | tail -1 | awk '{print $NF}')"; done; echo
done
stop