TLBB 0.85 Ubuntu 24.04 一键搭建环境脚本

#!/usr/bin/env bash

set -Eeuo pipefail
IFS=$'\n\t'
umask 027

SCRIPT_VERSION="2.0.0"
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
SQL_DIR="${SCRIPT_DIR}"
DB_USER="tlbbdb"
DB_PASSWORD=""
SKIP_IMPORT=0

usage()
{
    cat <<'EOF'
用法:
  sudo bash setup-ubuntu-24.04.sh [选项]

选项:
  --sql-dir DIR     SQL 文件目录，默认使用脚本所在目录
  --db-user USER    服务端数据库用户，默认 tlbbdb
  --skip-import     创建数据库，但不导入 tlbbdb.sql 和 web.sql
  -h, --help        显示帮助

脚本只安装和配置 MariaDB、unixODBC 及 MariaDB ODBC 驱动，
不会编译、部署或启动 TLBB 服务端。执行时只会隐藏输入一次数据库密码。
EOF
}

log()
{
    printf '[%s] %s\n' "$(date '+%F %T')" "$*"
}

die()
{
    log "错误: $*" >&2
    exit 1
}

need_value()
{
    test "$#" -ge 2 || die "参数 $1 缺少值"
}

while test "$#" -gt 0; do
    case "$1" in
        --sql-dir)
            need_value "$@"
            SQL_DIR="$2"
            shift 2
            ;;
        --db-user)
            need_value "$@"
            DB_USER="$2"
            shift 2
            ;;
        --skip-import)
            SKIP_IMPORT=1
            shift
            ;;
        -h|--help)
            usage
            exit 0
            ;;
        *)
            die "未知参数: $1"
            ;;
    esac
done

test "$(id -u)" -eq 0 || die "请使用 root 或 sudo 执行"
test -r /etc/os-release || die "无法读取 /etc/os-release"
# shellcheck disable=SC1091
. /etc/os-release
test "${ID:-}" = "ubuntu" || die "仅支持 Ubuntu 24.04"
test "${VERSION_ID:-}" = "24.04" || die "仅支持 Ubuntu 24.04，当前为 ${VERSION_ID:-unknown}"
test "$(getconf LONG_BIT)" = "64" || die "仅支持 64 位系统"

case "${DB_USER}" in
    *[!A-Za-z0-9_]*|'') die "数据库用户名只能包含字母、数字和下划线" ;;
esac

SQL_DIR="$(realpath -m "${SQL_DIR}")"
if test "${SKIP_IMPORT}" -eq 0; then
    test -f "${SQL_DIR}/tlbbdb.sql" || die "缺少 ${SQL_DIR}/tlbbdb.sql"
    test -f "${SQL_DIR}/web.sql" || die "缺少 ${SQL_DIR}/web.sql"
fi

read_database_password()
{
    test -r /dev/tty || die "当前终端不可交互，无法读取数据库密码"
    printf '请输入数据库用户 %s 的密码: ' "${DB_USER}" > /dev/tty
    if ! IFS= read -r -s DB_PASSWORD < /dev/tty; then
        printf '\n' > /dev/tty
        die "读取数据库密码失败"
    fi
    printf '\n' > /dev/tty
    test -n "${DB_PASSWORD}" || die "数据库密码不能为空"
}

read_database_password

LOG_DIR=/var/log/tlbb-db-setup
mkdir -p "${LOG_DIR}"
LOG_FILE="${LOG_DIR}/setup-$(date '+%Y%m%d-%H%M%S').log"
exec > >(tee -a "${LOG_FILE}") 2>&1
trap 'log "失败位置: 第 ${LINENO} 行，命令: ${BASH_COMMAND}"' ERR

log "TLBB Ubuntu 数据库环境脚本 ${SCRIPT_VERSION}"
log "SQL 目录: ${SQL_DIR}"
log "数据库用户: ${DB_USER}"

install_managed_file()
{
    local target="$1"
    local mode="$2"
    local temporary
    temporary="$(mktemp)"
    cat > "${temporary}"

    if test -f "${target}" && ! cmp -s "${temporary}" "${target}"; then
        cp -a "${target}" "${target}.bak.$(date '+%Y%m%d-%H%M%S')"
    fi
    install -D -m "${mode}" "${temporary}" "${target}"
    rm -f "${temporary}"
}

install_packages()
{
    local packages=(
        ca-certificates
        mariadb-client
        mariadb-server
        odbc-mariadb
        unixodbc
    )

    log "安装 MariaDB、unixODBC 和 MariaDB ODBC 驱动"
    export DEBIAN_FRONTEND=noninteractive
    apt-get update
    apt-get install -y --no-install-recommends "${packages[@]}"
}

configure_mariadb()
{
    log "配置 MariaDB，仅监听 127.0.0.1"
    install_managed_file /etc/mysql/mariadb.conf.d/60-tlbb.cnf 0644 <<'EOF'
[mysqld]
bind-address = 127.0.0.1
skip-name-resolve
character-set-server = latin1
collation-server = latin1_swedish_ci
sql-mode = NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION
max-allowed-packet = 64M
wait-timeout = 28800
interactive-timeout = 28800
EOF

    systemctl enable mariadb
    systemctl restart mariadb
    systemctl is-active --quiet mariadb || die "MariaDB 启动失败"
}

sql_escape_string()
{
    local escaped="$1"
    escaped="${escaped//\\/\\\\}"
    escaped="${escaped//\'/\'\'}"
    printf '%s' "${escaped}"
}

create_databases_and_user()
{
    local escaped_password
    escaped_password="$(sql_escape_string "${DB_PASSWORD}")"

    log "创建 tlbbdb、web 数据库和 ${DB_USER} 用户"
    mariadb --protocol=socket <<EOF
CREATE DATABASE IF NOT EXISTS tlbbdb CHARACTER SET latin1 COLLATE latin1_swedish_ci;
CREATE DATABASE IF NOT EXISTS web CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci;
CREATE USER IF NOT EXISTS '${DB_USER}'@'localhost' IDENTIFIED BY '${escaped_password}';
CREATE USER IF NOT EXISTS '${DB_USER}'@'127.0.0.1' IDENTIFIED BY '${escaped_password}';
ALTER USER '${DB_USER}'@'localhost' IDENTIFIED BY '${escaped_password}';
ALTER USER '${DB_USER}'@'127.0.0.1' IDENTIFIED BY '${escaped_password}';
GRANT ALL PRIVILEGES ON tlbbdb.* TO '${DB_USER}'@'localhost';
GRANT ALL PRIVILEGES ON tlbbdb.* TO '${DB_USER}'@'127.0.0.1';
GRANT ALL PRIVILEGES ON web.* TO '${DB_USER}'@'localhost';
GRANT ALL PRIVILEGES ON web.* TO '${DB_USER}'@'127.0.0.1';
FLUSH PRIVILEGES;
EOF
}

import_database_if_empty()
{
    local database="$1"
    local sql_file="$2"
    local table_count

    table_count="$(mariadb --protocol=socket --batch --skip-column-names \
        -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='${database}'")"
    if test "${table_count}" -eq 0; then
        log "导入 ${database}: ${sql_file}"
        mariadb --protocol=socket "${database}" < "${sql_file}"
    else
        log "${database} 已有 ${table_count} 个表，保留现有数据并跳过导入"
    fi
}

configure_odbc()
{
    local driver_name
    driver_name="$(odbcinst -q -d | sed -n 's/^\[\(MariaDB[^]]*\)\]$/\1/p' | head -n 1)"
    test -n "${driver_name}" || die "未找到 MariaDB ODBC 驱动"

    log "配置系统 ODBC DSN: tlbbdb、web，驱动 ${driver_name}"
    install_managed_file /etc/odbc.ini 0644 <<EOF
[tlbbdb]
Description = TLBB character database
Driver = ${driver_name}
SERVER = 127.0.0.1
PORT = 3306
USER = ${DB_USER}
DATABASE = tlbbdb
OPTION = 3
CHARSET = latin1

[web]
Description = TLBB billing database
Driver = ${driver_name}
SERVER = 127.0.0.1
PORT = 3306
USER = ${DB_USER}
DATABASE = web
OPTION = 3
CHARSET = utf8
EOF
}

verify_database_and_odbc()
{
    local table_summary
    log "验证 MariaDB TCP 登录和 ODBC DSN"

    MYSQL_PWD="${DB_PASSWORD}" mariadb --protocol=tcp -h 127.0.0.1 \
        -u "${DB_USER}" --batch --skip-column-names tlbbdb -e 'SELECT 1' | grep -qx 1
    MYSQL_PWD="${DB_PASSWORD}" mariadb --protocol=tcp -h 127.0.0.1 \
        -u "${DB_USER}" --batch --skip-column-names web -e 'SELECT 1' | grep -qx 1

    printf 'SELECT 1;\n' | isql -b tlbbdb "${DB_USER}" "${DB_PASSWORD}" >/dev/null
    printf 'SELECT 1;\n' | isql -b web "${DB_USER}" "${DB_PASSWORD}" >/dev/null

    table_summary="$(mariadb --protocol=socket --batch --skip-column-names -e \
        "SELECT CONCAT(table_schema, ':', COUNT(*)) FROM information_schema.tables WHERE table_schema IN ('tlbbdb','web') GROUP BY table_schema ORDER BY table_schema")"
    log "数据库表统计: ${table_summary//$'\n'/, }"
    log "MariaDB 和 ODBC 验证通过"
}

install_packages
configure_mariadb
create_databases_and_user
configure_odbc

if test "${SKIP_IMPORT}" -eq 0; then
    import_database_if_empty tlbbdb "${SQL_DIR}/tlbbdb.sql"
    import_database_if_empty web "${SQL_DIR}/web.sql"
else
    log "按参数跳过 SQL 导入"
fi

verify_database_and_odbc
DB_PASSWORD=""
unset DB_PASSWORD

log "Ubuntu 24.04 数据库与 ODBC 环境搭建完成"
log "安装日志: ${LOG_FILE}"