FileMakerからのMigrationで改行とタイムスタンプの処理

[FileMakerからMySQLへのMigration](https://qiita.com/mackey1966/items/b22b6c3e2485363f43ab)、および、[FileMakerのオブジェクトをS3にアップロードする](https://qiita.com/mackey1966/items/c78f3d7383c598536b0a)の続きになる。

FileMakerのテキストフィールド内の改行は0x0B (VT:垂直タブ)で繰り返しフィールドの区切り文字は0x1D。このままでは困るので他システムに移行しても使えるよう0x0D, 0x0A (CR/LF)に変換する。また、クラウド用にタイムスタンプをUTCに変換する。

処理はMySQLにインポートする前にアップロードしたCSVファイルをコピーしているシェルスクリプトがあるのでその中で行う。

FileMakerの出力と同じダブルクォート付きCSVでタイムスタンプで0秒が省略されるところに対応している。大きなファイルでサーバーがハングアップするので改良しているがこれでも1ヶ月程度のデータが限界っぽい（Claude 3.5に聞いた結果を貼り付けてるだけですが検証済みです）。

```sh
#!/bin/bash

DATE=$(<date.txt)
sh find_new_file.sh -v "$DATE" temp/csv/ > result.txt
RESULT=$(<result.txt)
if [ -z "$RESULT" ]; then
  exit 0
fi

cd temp/csv

# 変換とコピーを行う関数
convert_and_copy() {
    local input_file="$1"
    local output_file="/var/lib/mysql-files/$(basename "$input_file")"
    
    LC_ALL=en_US.UTF-8 awk '
    BEGIN {
        FS=OFS=","
        FPAT="([^,]+)|(\"[^\"]+\")"
    }
    function is_timestamp(str) {
        gsub(/^"|"$/, "", str)
        return str ~ /^[0-9]{4}(\/|-)[0-9]{2}(\/|-)[0-9]{2} [0-9]{1,2}:[0-9]{2}(:[0-9]{2})?$/
    }
    function convert_timestamp(str) {
        orig_str = str
        gsub(/^"|"$/, "", str)
        gsub(/-/, "/", str)
        if (str !~ /:[0-9]{2}$/) {
            str = str ":00"
        }
        cmd = "date -d\"" str " JST\" -u \"+%Y-%m-%d %H:%M:%S\""
        cmd | getline utc_time
        close(cmd)
        return (orig_str ~ /^".*"$/) ? "\"" utc_time "\"" : utc_time
    }
    {
        for (i = 1; i <= NF; i++) {
            if (is_timestamp($i)) {
                $i = convert_timestamp($i)
            }
        }
        # 0x1Dが1つ以上連続する部分を1つの改行に置換
        gsub(/\x1D+/, "\r\n")
        # VTを改行に変換
        gsub(/\v/, "\r\n")
        print
    }
    ' "$input_file" > "$output_file"
    
    echo "Converted and copied: $input_file"
}

# 並列処理の管理
MAX_JOBS=4  # 同時に実行するジョブの最大数
CURRENT_JOBS=0

# 全ての.csvファイルに対して変換とコピーを実行
for file in *.csv; do
    if [ -f "$file" ]; then
        # 同時実行ジョブ数が最大数に達しているか確認
        while [ $CURRENT_JOBS -ge $MAX_JOBS ]; do
            sleep 1
            CURRENT_JOBS=$(jobs -r | wc -l)
        done
        
        # 新しいサブシェルでファイル処理を実行
        (
            convert_and_copy "$file"
        ) &
        
        CURRENT_JOBS=$((CURRENT_JOBS + 1))
    fi
done

# 全てのバックグラウンドジョブの完了を待つ
wait

echo "All files have been processed."

# MySQLにインポート
mysql --defaults-file=~/.my.cnf < csvimp.sql

# 現在のディレクトリをホームディレクトリに変更
cd ~/

# 日付を更新
date +%Y%m%d%H%M > date.txt
```
topで見たときにawkが複数あるのは並列処理をしてるからだと思ってたら、前の処理が終わらないうちに次のcronが実行されてるからだった。お恥ずかしい。ロックするように変更（これでハングアップすることはなくなるだろう）。
```sh
#!/bin/bash

# ロックファイルの設定
LOCKFILE="/var/run/csvupdate.lock"

# ログ機能
log_message() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') $1" >> /var/log/csvupdate.log
}

# ロックチェック
if [ -e "$LOCKFILE" ]; then
    pid=$(cat "$LOCKFILE")
    if kill -0 $pid 2>/dev/null; then
        log_message "Previous process is still running (PID: $pid)"
        exit 0
    else
        rm -f "$LOCKFILE"
    fi
fi

# ロックファイル作成
echo $$ > "$LOCKFILE"

# 終了時のクリーンアップ
trap 'rm -f "$LOCKFILE"' EXIT

# 以下、元のコード
DATE=$(<date.txt)
sh find_new_file.sh -v "$DATE" temp/csv/ > result.txt
RESULT=$(<result.txt)
if [ -z "$RESULT" ]; then
    log_message "No new files found"
    exit 0
fi

cd temp/csv || {
    log_message "Error: Cannot change directory to temp/csv"
    exit 1
}

# 変換とコピーを行う関数（元のまま）
convert_and_copy() {
    local input_file="$1"
    local output_file="/var/lib/mysql-files/$(basename "$input_file")"
    
    LC_ALL=en_US.UTF-8 awk '
    BEGIN {
        FS=OFS=","
        FPAT="([^,]+)|(\"[^\"]+\")"
    }
    function is_timestamp(str) {
        gsub(/^"|"$/, "", str)
        return str ~ /^[0-9]{4}(\/|-)[0-9]{2}(\/|-)[0-9]{2} [0-9]{1,2}:[0-9]{2}(:[0-9]{2})?$/
    }
    function convert_timestamp(str) {
        orig_str = str
        gsub(/^"|"$/, "", str)
        gsub(/-/, "/", str)
        if (str !~ /:[0-9]{2}$/) {
            str = str ":00"
        }
        cmd = "date -d\"" str " JST\" -u \"+%Y-%m-%d %H:%M:%S\""
        cmd | getline utc_time
        close(cmd)
        return (orig_str ~ /^".*"$/) ? "\"" utc_time "\"" : utc_time
    }
    {
        for (i = 1; i <= NF; i++) {
            if (is_timestamp($i)) {
                $i = convert_timestamp($i)
            }
        }
        gsub(/\x1D+/, "\r\n")
        gsub(/\v/, "\r\n")
        print
    }
    ' "$input_file" > "$output_file" || {
        log_message "Error processing file: $input_file"
        return 1
    }
    
    log_message "Converted and copied: $input_file"
}

# 並列処理の管理
MAX_JOBS=4
CURRENT_JOBS=0

# 全ての.csvファイルに対して変換とコピーを実行
for file in *.csv; do
    if [ -f "$file" ]; then
        while [ $CURRENT_JOBS -ge $MAX_JOBS ]; do
            sleep 1
            CURRENT_JOBS=$(jobs -r | wc -l)
        done
        
        (
            convert_and_copy "$file"
        ) &
        
        CURRENT_JOBS=$((CURRENT_JOBS + 1))
    fi
done

wait

log_message "All files have been processed"

mysql --defaults-file=~/.my.cnf < csvimp.sql || {
    log_message "Error: MySQL import failed"
    exit 1
}

cd ~/
date +%Y%m%d%H%M > date.txt

log_message "Processing completed successfully"
```
やはり大量のデータではハングアップするので環境変数で処理をON/OFFできるようにした。
```sh
# config.env
ENABLE_TIMESTAMP_CONVERSION=false
ENABLE_NEWLINE_CONVERSION=false
```

```sh
#!/bin/bash
# ロックファイルの設定
LOCKFILE="/var/run/csvupdate.lock"

# 設定ファイルの読み込み
if [ -f "config.env" ]; then
    source config.env
fi

# 環境変数でタイムスタンプと改行処理の制御を可能に
ENABLE_TIMESTAMP_CONVERSION=${ENABLE_TIMESTAMP_CONVERSION:-true}
ENABLE_NEWLINE_CONVERSION=${ENABLE_NEWLINE_CONVERSION:-true}

# ログ機能
log_message() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') $1" >> /var/log/csvupdate.log
}

# ロックチェック
if [ -e "$LOCKFILE" ]; then
    pid=$(cat "$LOCKFILE")
    if kill -0 $pid 2>/dev/null; then
        log_message "Previous process is still running (PID: $pid)"
        exit 0
    else
        rm -f "$LOCKFILE"
    fi
fi

# ロックファイル作成
echo $$ > "$LOCKFILE"

# 終了時のクリーンアップ
trap 'rm -f "$LOCKFILE"' EXIT

# 以下、元のコード
DATE=$(<date.txt)
sh find_new_file.sh -v "$DATE" temp/csv/ > result.txt
RESULT=$(<result.txt)

if [ -z "$RESULT" ]; then
    log_message "No new files found"
    exit 0
fi

cd temp/csv || {
    log_message "Error: Cannot change directory to temp/csv"
    exit 1
}

# 変換とコピーを行う関数
convert_and_copy() {
    local input_file="$1"
    local output_file="/var/lib/mysql-files/$(basename "$input_file")"
    
    # AWKスクリプトを環境変数に基づいて動的に構築
    local awk_script='
    BEGIN {
        FS=OFS=","
        FPAT="([^,]+)|(\"[^\"]+\")"
    }'
    
    # タイムスタンプ変換が有効な場合のみ追加
    if [ "$ENABLE_TIMESTAMP_CONVERSION" = true ]; then
        awk_script+='
        function is_timestamp(str) {
            gsub(/^"|"$/, "", str)
            return str ~ /^[0-9]{4}(\/|-)[0-9]{2}(\/|-)[0-9]{2} [0-9]{1,2}:[0-9]{2}(:[0-9]{2})?$/
        }
        function convert_timestamp(str) {
            orig_str = str
            gsub(/^"|"$/, "", str)
            gsub(/-/, "/", str)
            if (str !~ /:[0-9]{2}$/) {
                str = str ":00"
            }
            cmd = "date -d\"" str " JST\" -u \"+%Y-%m-%d %H:%M:%S\""
            cmd | getline utc_time
            close(cmd)
            return (orig_str ~ /^".*"$/) ? "\"" utc_time "\"" : utc_time
        }'
    fi

    awk_script+='
    {
        if ("'"$ENABLE_TIMESTAMP_CONVERSION"'" == "true") {
            for (i = 1; i <= NF; i++) {
                if (is_timestamp($i)) {
                    $i = convert_timestamp($i)
                }
            }
        }'

    # 改行処理が有効な場合のみ追加
    if [ "$ENABLE_NEWLINE_CONVERSION" = true ]; then
        awk_script+='
        gsub(/\x1D+/, "\r\n")
        gsub(/\v/, "\r\n")'
    fi

    awk_script+='
        print
    }'

    LC_ALL=en_US.UTF-8 awk "$awk_script" "$input_file" > "$output_file" || {
        log_message "Error processing file: $input_file"
        return 1
    }
    
    log_message "Converted and copied: $input_file"
}

# 並列処理の管理
MAX_JOBS=4
CURRENT_JOBS=0

# 全ての.csvファイルに対して変換とコピーを実行
for file in *.csv; do
    if [ -f "$file" ]; then
        while [ $CURRENT_JOBS -ge $MAX_JOBS ]; do
            sleep 1
            CURRENT_JOBS=$(jobs -r | wc -l)
        done
        
        (
            convert_and_copy "$file"
        ) &
        
        CURRENT_JOBS=$((CURRENT_JOBS + 1))
    fi
done

wait
log_message "All files have been processed"

mysql --defaults-file=~/.my.cnf < csvimp.sql || {
    log_message "Error: MySQL import failed"
    exit 1
}

cd ~/
date +%Y%m%d%H%M > date.txt
log_message "Processing completed successfully"
```