#!/usr/bin/env bash
# What MySQL's and MariaDB's safe-updates mode (sql_safe_updates=1) blocks, and which
# full-table writes it lets through. For https://tabellum.dudko.dev/blog/update-without-where-mysql-safe-updates
# Needs Docker. Uses the official images; each case gets a fresh 1000-row table.
#   ./measure.sh mysql:8.4      ./measure.sh mariadb:11.4
set -u
IMG=${1:-mysql:8.4}; DIR=$(cd "$(dirname "$0")" && pwd); NAME=safeupd-$$
case $IMG in mariadb*) CLI=mariadb ;; *) CLI=mysql ;; esac
docker run -d --rm --name $NAME -e MYSQL_ROOT_PASSWORD=pw -e MARIADB_ROOT_PASSWORD=pw "$IMG" >/dev/null
q(){ docker exec $NAME $CLI -uroot -ppw -N -B "$@" 2>&1 | grep -v 'Using a password'; }
until q -e 'select 1' >/dev/null 2>&1 && [ "$(q -e 'select 1')" = 1 ]; do sleep 2; done
echo "server: $(q -e 'select version()')"
reset(){ q -e "DROP DATABASE IF EXISTS t; CREATE DATABASE t; USE t;
 CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(20));
 CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, status VARCHAR(10), total INT, KEY (customer_id));
 INSERT INTO customers WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM n WHERE i < 50) SELECT i, CONCAT('c', i) FROM n;
 INSERT INTO orders WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM n WHERE i < 1000)
   SELECT i, i % 50 + 1, IF(i % 10 = 0, 'new', 'paid'), i FROM n;" >/dev/null; }
printf '%-40s | %-9s | %s\n' "case" "result" "rows changed of 1000"
grep -v '^#' "$DIR/cases.sql.txt" | while IFS='|' read -r label setup stmt; do
  [ -z "$label" ] && continue
  reset; [ -n "$setup" ] && q t -e "$setup" >/dev/null
  out=$(q t -e "SET SESSION sql_safe_updates = 1; $stmt;")
  left=$(q t -e "SELECT COUNT(*) FROM orders"); x=$(q t -e "SELECT COUNT(*) FROM orders WHERE status = 'x'")
  changed=$(( 1000 - left + x ))
  if echo "$out" | grep -q 'ERROR'; then res=blocked; else res=ran; fi
  printf '%-40s | %-9s | %s\n' "$label" "$res" "$changed"
  [ "$res" = blocked ] && [ -n "${VERBOSE:-}" ] && echo "    $out"
done
# The check that does not depend on the WHERE clause: run it in a transaction and read the count first.
reset
echo "in a transaction: $(q t -e "START TRANSACTION; UPDATE orders SET status='x' WHERE id > 0; SELECT CONCAT('row_count=', ROW_COUNT()); ROLLBACK; SELECT CONCAT('after rollback x=', COUNT(*)) FROM orders WHERE status='x';" | tr '\n' ' ')"
docker stop $NAME >/dev/null
