# 生产环境推荐启动参数
rqlited \
    -node-id=<唯一节点ID> \
    -http-addr=0.0.0.0:4001 \
    -raft-addr=0.0.0.0:4002 \
    -http-cert=/etc/rqlite/certs/server.crt \
    -http-key=/etc/rqlite/certs/server.key \
    -http-ca-cert=/etc/rqlite/certs/ca.crt \
    -node-cert=/etc/rqlite/certs/server.crt \
    -node-key=/etc/rqlite/certs/server.key \
    -node-ca-cert=/etc/rqlite/certs/ca.crt \
    -auth=/etc/rqlite/auth.json \
    -disco-mode=off \
    -fk \
    /var/lib/rqlite/data

// /etc/rqlite/auth.json
[
    {
        "username": "admin",
        "password": "使用强密码（至少 16 字符，含大小写+数字+特殊字符）",
        "perm": "all"
    },
    {
        "username": "app_write",
        "password": "应用写入专用密码",
        "perm": "rw"
    },
    {
        "username": "app_read",
        "password": "应用只读密码",
        "perm": "ro"
    },
    {
        "username": "monitor",
        "password": "监控专用密码",
        "perm": "ro"
    }
]

# /etc/systemd/system/rqlited.service
[Unit]
Description=rqlite distributed SQLite
After=network-online.target
Wants=network-online.target

[Service]
Type=simple
User=rqlite
Group=rqlite
ExecStart=/opt/rqlite/start.sh
Restart=always
RestartSec=5
LimitNOFILE=65536

# 安全加固
NoNewPrivileges=true
ProtectSystem=strict
ProtectHome=true
PrivateTmp=true
ReadWritePaths=/var/lib/rqlite /var/log/rqlite

# 资源限制
MemoryMax=2G
CPUQuota=200%

[Install]
WantedBy=multi-user.target

-- ✅ 推荐：使用明确的主键和类型
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    status TEXT CHECK(status IN ('active', 'inactive', 'banned')) DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ✅ 推荐：外键约束
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    total REAL NOT NULL CHECK(total >= 0),
    status TEXT CHECK(status IN ('pending', 'paid', 'shipped', 'completed')) DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ✅ 推荐：为高频查询字段创建索引
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
CREATE INDEX idx_orders_created ON orders(created_at DESC);
CREATE INDEX idx_users_status ON users(status);

-- ✅ 使用参数绑定
-- ❌ 字符串拼接（SQL 注入风险）
-- ✅ 仅选择需要的列
SELECT id, username, email FROM users WHERE id = ?;

-- ✅ 使用 LIMIT 限制结果集
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 50;

-- ✅ 使用 EXPLAIN QUERY PLAN 分析查询
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';

# ✅ 推荐：批量写入（50-100 条/批）
def batch_insert(records: list[dict], batch_size: int = 100):
    """分批插入数据"""
    for i in range(0, len(records), batch_size):
        batch = records[i:i + batch_size]
        statements = [
            ["INSERT INTO logs (level, message, ts) VALUES (?, ?, ?)",
             r["level"], r["message"], r["ts"]]
            for r in batch
        ]
        client.execute(statements)

# ✅ 推荐：读写分离
def get_user(user_id: int) -> dict:
    """查询用户 — 弱一致性"""
    result = client.query(
        "SELECT * FROM users WHERE id = ?",
        params=[user_id],
        level="weak"  # 一般查询用 weak
    )
    return result

def create_order(order: dict) -> int:
    """创建订单 — 写入后读取用 strong"""
    client.execute(
        "INSERT INTO orders (user_id, product, total) VALUES (?, ?, ?)",
        params=[order["user_id"], order["product"], order["total"]]
    )
    # 写后读取用 strong 一致性
    result = client.query(
        "SELECT * FROM orders WHERE user_id = ? ORDER BY id DESC LIMIT 1",
        params=[order["user_id"]],
        level="strong"
    )
    return result["results"][0]["values"][0][0]

# 每月至少执行一次恢复演练
#!/bin/bash
# restore-drill.sh

BACKUP_FILE=$(ls -t /var/backup/rqlite/rqlite_*.sql.gz | head -1)
DRILL_DIR="/tmp/drill-$(date +%Y%m%d)"

mkdir -p "$DRILL_DIR/node1"

# 启动临时节点
rqlited -node-id=drill -disco-mode=off "$DRILL_DIR/node1" &
sleep 3

# 恢复数据
gunzip -c "$BACKUP_FILE" | curl -s -XPOST 'localhost:4001/db/load' \
    -H 'Content-Type: text/plain' --data-binary @-

# 验证数据
tables=$(curl -s -G 'localhost:4001/db/query' \
    --data-urlencode 'q=SELECT COUNT(*) FROM sqlite_master WHERE type="table"' \
    | python3 -c "import json,sys; print(json.load(sys.stdin)['results'][0]['values'][0][0])")

echo "恢复表数: $tables"

# 清理
kill %1
rm -rf "$DRILL_DIR"

#!/bin/bash
# health-check.sh — 生产环境健康检查

NODES=("node1:4001" "node2:4001" "node3:4001")
ERRORS=0

for node in "${NODES[@]}"; do
    host="${node%%:*}"
    port="${node##*:}"
    
    # 就绪检查
    code=$(curl -s -o /dev/null -w "%{http_code}" \
        "http://$node/status/ready" --connect-timeout 5)
    
    if [ "$code" != "200" ]; then
        echo "CRITICAL: $node not ready (HTTP $code)"
        ((ERRORS++))
    fi
done

if [ $ERRORS -gt 0 ]; then
    echo "CRITICAL: $ERRORS nodes unhealthy"
    exit 2
fi

echo "OK: All ${#NODES[@]} nodes healthy"
exit 0