column

#!/usr/bin/env bash

# =========================
# CONFIG
# =========================
SYBASE_SERVER="SYB_SERVER"
SYBASE_USER="syb_user"
SYBASE_PASS="syb_pass"
SYBASE_DB="syb_db"

ORACLE_CONN="ora_user/ora_pass@ORADB"

TABLE="$1"

if [[ -z "$TABLE" ]]; then
  echo "Usage: $0 TABLE_NAME"
  exit 1
fi

WORKDIR="./compare_$TABLE"
mkdir -p "$WORKDIR"

SYB_FILE="$WORKDIR/sybase.cols"
ORA_FILE="$WORKDIR/oracle.cols"

# =========================
# SYBASE
# =========================
isql -S "$SYBASE_SERVER" -U "$SYBASE_USER" -P "$SYBASE_PASS" -D "$SYBASE_DB" -w 200 -b <<EOF > "$SYB_FILE"
SET NOCOUNT ON
GO
SELECT
  o.name,
  c.colid,
  c.name
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
WHERE o.type = 'U'
  AND o.name = '$TABLE'
ORDER BY c.colid
GO
EOF

sed -i 's/[[:space:]]\+/|/g' "$SYB_FILE"
tr '[:lower:]' '[:upper:]' < "$SYB_FILE" > "$SYB_FILE.tmp" && mv "$SYB_FILE.tmp" "$SYB_FILE"

# =========================
# ORACLE
# =========================
sqlplus -s "$ORACLE_CONN" <<EOF > "$ORA_FILE"
SET PAGESIZE 0 FEEDBACK OFF HEADING OFF TRIMSPOOL ON
SELECT
  table_name || '|' ||
  column_id || '|' ||
  column_name
FROM user_tab_columns
WHERE table_name = UPPER('$TABLE')
ORDER BY column_id;
EXIT;
EOF

tr '[:lower:]' '[:upper:]' < "$ORA_FILE" > "$ORA_FILE.tmp" && mv "$ORA_FILE.tmp" "$ORA_FILE"

# =========================
# COMPARISON
# =========================
echo
echo "=== Comparing table: $TABLE ==="
echo

awk -F'|' '
FNR==NR {
  syb_pos[$3]=$2
  syb_seen[$3]=1
  next
}
{
  ora_pos[$3]=$2
  ora_seen[$3]=1
}
END {
  for (c in syb_seen) {
    if (!(c in ora_seen)) {
      printf "❌ ONLY IN SYBASE     : %s (pos %s)\n", c, syb_pos[c]
    } else if (syb_pos[c] != ora_pos[c]) {
      printf "🔀 ORDER DIFF         : %s (SYB %s, ORA %s)\n", c, syb_pos[c], ora_pos[c]
    } else {
      printf "✅ MATCH              : %s (pos %s)\n", c, syb_pos[c]
    }
  }
  for (c in ora_seen) {
    if (!(c in syb_seen)) {
      printf "❌ ONLY IN ORACLE     : %s (pos %s)\n", c, ora_pos[c]
    }
  }
}
' "$SYB_FILE" "$ORA_FILE"

echo
echo "Done."