Hi Saku!

The first script, although very slow, worked, but it left me with about 150 unsynchronized QSOs out of a total of 16,745. The second script you sent me was much faster and more accurate, reducing the unsynchronized QSOs to about 50. Then, using the AI, I was able to synchronize them all by querying the database and executing specific commands and scripts. Finally, I asked the AI ​​to adapt and improve your second script based on all the experience it gained working with my database. I'm attaching it for your evaluation. I haven't tested it from scratch yet with one of my unsynchronized backups, but I will soon.


#!/bin/bash
clear

if [ $# -ne 2 ]; then
echo "====================================================================="
echo " CQRLOG to QRZ.com - SMART SYNC SCRIPT (Enhanced Version 2026)"
echo "====================================================================="
echo "Usage: $0 "
echo "Example: $0 ik0dwj.adi cqrlog001"
echo
echo "Note: Start CQRLOG before running the script to activate the socket."
exit
fi

# Automatic socket configuration based on current user
sqlcmd="mysql -S /home/$USER/.config/cqrlog/database/sock $2 --skip-column-names -se "

qsook=0
qsofail=0
total=0

# Subroutine to clear variables for each record
function newqso(){
call=""
dat=""
tim_off=""
band=""
mode=""
qrzid=""
}

function idstoresize() {
local count=$($sqlcmd "SELECT count(id) FROM id_store" 2>/dev/null)
echo "${count:-0}"
}

# Initial database connection check
if ! $sqlcmd "SELECT 1" &>/dev/null; then
echo "❌ ERROR: Unable to connect to CQRLOG via socket."
echo "Verify that CQRLOG is open and that the database name ($2) is correct."
exit 1
fi

echo "====================================================================="
echo " Starting smart synchronization of ADIF records..."
echo "====================================================================="
echo "Reading ADIF file : $1"
echo "CQRLOG Database : $2"
echo "id_store records : $(idstoresize) (Current)"
echo "---------------------------------------------------------------------"
echo "Processing... Please wait until the process finishes."
echo "---------------------------------------------------------------------"

# Count total blocks for the graphical counter
total_lines=$(grep -c "" "$1" 2>/dev/null || echo "0")

newqso

while IFS= read -r line || [[ -n "$line" ]]; do

# Extract and normalize data from ADIF file
if [[ "$line" == *""}; call=${call^^}; fi
if [[ "$line" == *""}; band=${band^^}; fi
if [[ "$line" == *""}; mode=${mode^^}; fi
if [[ "$line" == *""}
dat="${dat:0:4}"-"${dat:4:2}"-"${dat:6:2}"
fi
if [[ "$line" == *""}
tim_off="${tim_off:0:2}":"${tim_off:2:2}:00"
fi
if [[ "$line" == *""}; fi

# Process at the end of each ADIF record ()
if [[ "$line" == *""* ]]; then
total=$((total+1))

# Show dynamic graphical counter on the same line
echo -ne " ⏳ Processing: QSO $total of $total_lines...\r"

cqid=""

# If the record already has an ID in id_store, skip it upfront (Speeds up the process)
# Prevents Duplicate Entry key conflicts
if [ -n "$qrzid" ]; then

# --- LEVEL 1: Strict Original Search (Exact Time and Mode) ---
cqid=$($sqlcmd "SELECT id_cqrlog_main FROM cqrlog_main WHERE qsodate='$dat' AND callsign='$call' AND time_off=LEFT('$tim_off',5) AND band='$band' AND mode='$mode' AND id_cqrlog_main NOT IN (SELECT id_cqrlog_main FROM id_store) LIMIT 1")

# --- LEVEL 2: Time Tolerance and Character Correction (O vs 0) ---
if [ -z "$cqid" ]; then
# Generates a variant replacing 'O' with '0' and vice versa to work around character encoding bugs
call_alt=$call
if [[ "$call" == *"O"* ]]; then call_alt="${call//O/0}"; fi
if [[ "$call" == *"0"* ]]; then call_alt="${call//0/O}"; fi

cqid=$($sqlcmd "SELECT id_cqrlog_main FROM cqrlog_main WHERE qsodate='$dat' AND (callsign='$call' OR callsign='$call_alt') AND band='$band' AND (mode='$mode' OR mode='DIGI' OR mode='DATA' OR mode='RTTY') AND ABS(TIME_TO_SEC(TIMEDIFF(CONCAT(time_off,':00'), '$tim_off'))) <= 180 AND id_cqrlog_main NOT IN (SELECT id_cqrlog_main FROM id_store) ORDER BY time_off ASC LIMIT 1")
fi

# --- LEVEL 3: Surgical Solution for Portable / Foreign Stations (Slashed /) ---
if [ -z "$cqid" ] && [[ "$call" == *"/"* ]]; then
p1=$(echo "$call" | cut -d'/' -f1); p2=$(echo "$call" | cut -d'/' -f2)
if [ ${#p1} -ge ${#p2} ]; then call_base="$p1"; else call_base="$p2"; fi

cqid=$($sqlcmd "SELECT id_cqrlog_main FROM cqrlog_main WHERE qsodate='$dat' AND callsign LIKE '%${call_base}%' AND band='$band' AND ABS(TIME_TO_SEC(TIMEDIFF(CONCAT(time_off,':00'), '$tim_off'))) <= 300 AND id_cqrlog_main NOT IN (SELECT id_cqrlog_main FROM id_store) ORDER BY time_off ASC LIMIT 1")
fi

fi

# Local database write phase
if [ -n "$qrzid" ] && [ -n "$cqid" ]; then
# Safe insertion protected against duplicates
$sqlcmd "INSERT INTO id_store (id_cqrlog_main, qrz_logid) SELECT $cqid, '$qrzid' WHERE NOT EXISTS (SELECT 1 FROM id_store WHERE id_cqrlog_main=$cqid)"

# Clears the counter line and prints the synchronized record to the screen
echo -ne "\033[K"
echo "✅ Synchronized: $call ($dat $band) -> Local ID: $cqid"
qsook=$((qsook+1))
else
qsofail=$((qsofail+1))
fi

newqso
fi

done < "$1"

# Final cleanup of the terminal interface and report
echo -ne "\033[K"
echo "---------------------------------------------------------------------"
echo " Synchronization Finished!"
echo "---------------------------------------------------------------------"
echo " Records successfully aligned in this session : $qsook"
echo " Records skipped (already aligned or not found) : $qsofail"
echo " Total ADIF records examined : $total"
echo " New total record count in id_store : $(idstoresize)"
echo "---------------------------------------------------------------------"

exit 0

Thank you for your time.

73 de IK0DWJ (Beppe)