refresh_sources.sh

#!/bin/bash
# ─────────────────────────────────────────────────────────────────────────────
#  refresh_sources.sh — Standalone refresh script
#
#  Can be called:
#    1. At boot (from init_clickhouse.sh)
#    2. On-demand (from gateway.py when you hit /api/refresh)
#    3. Periodically (from background loop)
#
#  Actions:
#    • git clone (first run) or git pull (subsequent) for each source
#    • CREATE OR REPLACE VIEW for each defined view
# ─────────────────────────────────────────────────────────────────────────────
set -e

CONFIG="/app/sources.yaml"
USER_FILES="/app/ch/user_files"
CH_URL="http://127.0.0.1:8123"

# Optional argument: "sync_only" = just git pull, no view creation
# (used by background loop to avoid hammering ClickHouse with DDL)
MODE="${1:-full}"

SOURCE_COUNT=$(yq '.sources | length' "$CONFIG")

echo "{"
echo "  \"mode\": \"$MODE\","
echo "  \"source_count\": $SOURCE_COUNT,"
echo "  \"sources\": ["

for i in $(seq 0 $((SOURCE_COUNT - 1))); do
  NAME=$(yq ".sources[$i].name" "$CONFIG")
  REPO_URL=$(yq ".sources[$i].repo_url" "$CONFIG")
  LOCAL_DIR=$(yq ".sources[$i].local_dir" "$CONFIG")
  CLONE_DEPTH=$(yq ".sources[$i].clone_depth // 1" "$CONFIG")
  BRANCH=$(yq ".sources[$i].branch // \"\"" "$CONFIG")

  TARGET="$USER_FILES/$LOCAL_DIR"

  BRANCH_FLAG=""
  if [ -n "$BRANCH" ] && [ "$BRANCH" != "null" ] && [ "$BRANCH" != "" ]; then
    BRANCH_FLAG="--branch $BRANCH"
  fi

  # ── Git sync ──
  GIT_STATUS="unknown"
  GIT_OUTPUT=""
  if [ ! -d "$TARGET" ]; then
    GIT_OUTPUT=$(git clone --depth "$CLONE_DEPTH" $BRANCH_FLAG "$REPO_URL" "$TARGET" 2>&1) || true
    GIT_STATUS="cloned"
  else
    GIT_OUTPUT=$(cd "$TARGET" && git fetch --depth 1 origin && git reset --hard origin/$(git rev-parse --abbrev-ref HEAD) 2>&1) || true
    GIT_STATUS="pulled"
  fi

  LATEST_COMMIT=$(cd "$TARGET" && git log -1 --format='%h %s' 2>/dev/null || echo "unknown")

  # ── Trailing comma handling ──
  COMMA=","
  if [ $i -eq $((SOURCE_COUNT - 1)) ] && [ "$MODE" = "sync_only" ]; then
    COMMA=""
  fi

  # ── View creation (only in "full" mode) ──
  VIEWS_JSON=""
  if [ "$MODE" = "full" ]; then
    NUM_VIEWS=$(yq ".sources[$i].views | length" "$CONFIG")
    VIEWS_JSON="\"views\": ["

    for v in $(seq 0 $((NUM_VIEWS - 1))); do
      VIEW_NAME=$(yq ".sources[$i].views[$v].view_name" "$CONFIG")
      FILE_GLOB=$(yq ".sources[$i].views[$v].file_glob" "$CONFIG")
      FORMAT=$(yq ".sources[$i].views[$v].format // \"Parquet\"" "$CONFIG")
      NUM_COLS=$(yq ".sources[$i].views[$v].columns | length // 0" "$CONFIG")

      FULL_GLOB="${LOCAL_DIR}/${FILE_GLOB}"

      # Build SELECT clause — explicit columns if defined, else SELECT *
      if [ "$NUM_COLS" -gt 0 ] 2>/dev/null; then
        COLS=""
        for c in $(seq 0 $((NUM_COLS - 1))); do
          COL=$(yq ".sources[$i].views[$v].columns[$c]" "$CONFIG")
          if [ -z "$COLS" ]; then
            COLS="$COL"
          else
            COLS="$COLS, $COL"
          fi
        done
        SELECT_CLAUSE="SELECT ${COLS}"
      else
        SELECT_CLAUSE="SELECT *"
      fi

      SQL="CREATE OR REPLACE VIEW ${VIEW_NAME} AS ${SELECT_CLAUSE} FROM file('${FULL_GLOB}', ${FORMAT})"

      VIEW_STATUS="ok"
      if ! curl -sf "$CH_URL" --data "$SQL" 2>/dev/null; then
        VIEW_STATUS="error"
      fi

      VIEW_COMMA=","
      if [ $v -eq $((NUM_VIEWS - 1)) ]; then
        VIEW_COMMA=""
      fi

      VIEWS_JSON="${VIEWS_JSON}{\"name\":\"${VIEW_NAME}\",\"glob\":\"${FULL_GLOB}\",\"select\":\"${SELECT_CLAUSE}\",\"status\":\"${VIEW_STATUS}\"}${VIEW_COMMA}"
    done

    VIEWS_JSON="${VIEWS_JSON}]"
  fi

  # Determine trailing comma for this source
  COMMA=","
  if [ $i -eq $((SOURCE_COUNT - 1)) ]; then
    COMMA=""
  fi

  if [ "$MODE" = "full" ]; then
    echo "    {\"name\":\"$NAME\",\"git_status\":\"$GIT_STATUS\",\"latest_commit\":\"$LATEST_COMMIT\",$VIEWS_JSON}$COMMA"
  else
    echo "    {\"name\":\"$NAME\",\"git_status\":\"$GIT_STATUS\",\"latest_commit\":\"$LATEST_COMMIT\"}$COMMA"
  fi
done

echo "  ]"
echo "}"