149 lines
4.4 KiB
Bash
Executable File
149 lines
4.4 KiB
Bash
Executable File
#!/bin/bash
|
||
# sync_dev_to_public.sh — 比對 dev/public schema，同步 pipeline 資料
|
||
# Usage: ./sync_dev_to_public.sh [check|sync] [file_uuid]
|
||
|
||
PSQL="/opt/homebrew/opt/libpq/bin/psql"
|
||
|
||
set -euo pipefail
|
||
|
||
SCHEMA="${MOMENTRY_DB_SCHEMA:-dev}"
|
||
DB_URL="${DATABASE_URL:-postgres://accusys@localhost:5432/momentry}"
|
||
MODE="${1:-check}"
|
||
FILE_UUID="${2:-}"
|
||
|
||
TABLES=("videos" "chunk" "face_detections" "processor_results" "monitor_jobs"
|
||
"identities" "identity_bindings" "tkg_nodes" "tkg_edges")
|
||
|
||
TARGET="public"
|
||
|
||
if [ -z "$FILE_UUID" ]; then
|
||
echo "Usage: $0 [check|sync] <file_uuid>"
|
||
echo ""
|
||
echo "Examples:"
|
||
echo " $0 check bd80fec92b0b6963d177a2c55bf713e2"
|
||
echo " $0 sync bd80fec92b0b6963d177a2c55bf713e2"
|
||
exit 1
|
||
fi
|
||
|
||
echo "=== Schema Sync: $SCHEMA → $TARGET ==="
|
||
echo "File UUID: $FILE_UUID"
|
||
echo "Mode: $MODE"
|
||
echo ""
|
||
|
||
check_table() {
|
||
local table=$1
|
||
local col=$2
|
||
local src_count dev_count pub_count
|
||
|
||
dev_count=$($PSQL -At "$DB_URL" -c "SELECT COUNT(*) FROM ${SCHEMA}.${table} WHERE ${col} = '${FILE_UUID}';" 2>/dev/null || echo "ERROR")
|
||
pub_count=$($PSQL -At "$DB_URL" -c "SELECT COUNT(*) FROM ${TARGET}.${table} WHERE ${col} = '${FILE_UUID}';" 2>/dev/null || echo "ERROR")
|
||
|
||
if [ "$dev_count" = "ERROR" ] || [ "$pub_count" = "ERROR" ]; then
|
||
echo " ⚠️ $table — query error (table may not exist in $TARGET)"
|
||
return 1
|
||
fi
|
||
|
||
if [ "$dev_count" -eq "$pub_count" ]; then
|
||
echo " ✅ $table — $dev_count rows (match)"
|
||
return 0
|
||
else
|
||
echo " ❌ $table — dev=$dev_count pub=$pub_count (MISMATCH)"
|
||
return 1
|
||
fi
|
||
}
|
||
|
||
sync_table() {
|
||
local table=$1
|
||
local col=$2
|
||
local src_count dev_count pub_count
|
||
|
||
dev_count=$($PSQL -At "$DB_URL" -c "SELECT COUNT(*) FROM ${SCHEMA}.${table} WHERE ${col} = '${FILE_UUID}';" 2>/dev/null || echo "0")
|
||
pub_count=$($PSQL -At "$DB_URL" -c "SELECT COUNT(*) FROM ${TARGET}.${table} WHERE ${col} = '${FILE_UUID}';" 2>/dev/null || echo "0")
|
||
|
||
if [ "$dev_count" = "0" ]; then
|
||
echo " ⏭️ $table — dev has 0 rows, skipping"
|
||
return
|
||
fi
|
||
|
||
if [ "$dev_count" -eq "$pub_count" ]; then
|
||
echo " ✅ $table — already synced ($dev_count rows)"
|
||
return
|
||
fi
|
||
|
||
echo " 🔄 Syncing $table: dev=$dev_count → pub=$pub_count ..."
|
||
|
||
# Delete existing public rows, insert from dev
|
||
$PSQL "$DB_URL" -q -c "DELETE FROM ${TARGET}.${table} WHERE ${col} = '${FILE_UUID}';" 2>/dev/null || true
|
||
|
||
# Get columns list (excluding id for SERIAL)
|
||
COLS=$($PSQL -At "$DB_URL" -c "
|
||
SELECT string_agg(column_name, ', ' ORDER BY ordinal_position)
|
||
FROM information_schema.columns
|
||
WHERE table_schema='${SCHEMA}' AND table_name='${table}'
|
||
AND column_name != 'id'
|
||
AND is_updatable='YES';
|
||
")
|
||
|
||
$PSQL "$DB_URL" -q -c "
|
||
INSERT INTO ${TARGET}.${table} (${COLS})
|
||
SELECT ${COLS}
|
||
FROM ${SCHEMA}.${table}
|
||
WHERE ${col} = '${FILE_UUID}';
|
||
" 2>/dev/null && echo " ✅ $table synced" || echo " ❌ $table sync FAILED"
|
||
}
|
||
|
||
echo "=== Checking Tables ==="
|
||
echo ""
|
||
MISMATCH=0
|
||
for table in "${TABLES[@]}"; do
|
||
# Determine the UUID column name for each table
|
||
case "$table" in
|
||
videos) col="file_uuid" ;;
|
||
chunk) col="file_uuid" ;;
|
||
face_detections) col="file_uuid" ;;
|
||
processor_results) col="file_uuid" ;;
|
||
monitor_jobs) col="uuid" ;;
|
||
identities) col="uuid" ;; # identities.uuid is UUID type
|
||
identity_bindings) col="uuid" ;;
|
||
tkg_nodes) col="file_uuid" ;;
|
||
tkg_edges) col="file_uuid" ;;
|
||
*) col="file_uuid" ;;
|
||
esac
|
||
|
||
if ! check_table "$table" "$col"; then
|
||
MISMATCH=$((MISMATCH + 1))
|
||
fi
|
||
done
|
||
|
||
echo ""
|
||
if [ "$MISMATCH" -eq 0 ]; then
|
||
echo "✅ All tables in sync"
|
||
exit 0
|
||
fi
|
||
|
||
if [ "$MODE" != "sync" ]; then
|
||
echo "⚠️ $MISMATCH table(s) have mismatches. Run '$0 sync $FILE_UUID' to fix."
|
||
exit 1
|
||
fi
|
||
|
||
echo "=== Syncing Tables ==="
|
||
echo ""
|
||
for table in "${TABLES[@]}"; do
|
||
case "$table" in
|
||
videos) col="file_uuid" ;;
|
||
chunk) col="file_uuid" ;;
|
||
face_detections) col="file_uuid" ;;
|
||
processor_results) col="file_uuid" ;;
|
||
monitor_jobs) col="uuid" ;;
|
||
identities) col="uuid" ;;
|
||
identity_bindings) col="uuid" ;;
|
||
tkg_nodes) col="file_uuid" ;;
|
||
tkg_edges) col="file_uuid" ;;
|
||
*) col="file_uuid" ;;
|
||
esac
|
||
sync_table "$table" "$col"
|
||
done
|
||
|
||
echo ""
|
||
echo "✅ Sync complete"
|