sqlite2mysql.sh

sqlite2mysql.sh

#!/usr/bin/env bash

SCRIPT_PATH=${BASH_SOURCE[0]}
SCRIPT_NAME=${SCRIPT_PATH##*/}
# SCRIPT_DIR="$(cd "$(dirname "${SCRIPT_PATH:-$PWD}")" 2>/dev/null 1>&2 && pwd)"
HELPDOC=$(cat <<HEREDOC
   Usage: $SCRIPT_NAME <file.sqlite3> [--db <mysql_database_name> --table <table_name> --user <username> --pass <password> --dumpfile <sql_file> --execute --help]

   Dumps an SQLite table into a MySQL table. Requires an sqlite3 database file.
   MySQL table will be created if it doesn\'t already exist.

   The script will generate an *.sql file (specify name with --dumpfile option)
   that can be dumped into MySQL.

   Examples:
      $SCRIPT_NAME file.sqlite3
      $SCRIPT_NAME file.sqlite3 --db mydatabase --table customers --user username --pass password123 --dumpfile customers.sql
      $SCRIPT_NAME file.sqlite3 --db mydatabase --table customers --user username --pass password123 --dumpfile customers.sql --execute

   Options:
      --help       Show this help
      --db         Database name
      --table      Database table to dump data into (will be created if not exists)
      --user       Database username
      --pass       Database password
      --execute    Executes the generated SQL file at the end of the script
      --dumpfile   Specify the name of the SQL file to dump into
HEREDOC
)

# Unpack script arguments
argc="$#";: "$((i=0))"
while [ "$((i))" -lt "$argc" ]; do
  case "$1" in
    --help|-h) Help='true';;
    --execute) Execute='true';;
    --user) shift;: $((i=i+1)); User="$1";;
    --pass) shift;: $((i=i+1)); Pass="$1";;
    --db) shift;: $((i=i+1)); Db="$1";;
    --table) shift;: $((i=i+1)); TableName="$1";;
    --dump) shift;: $((i=i+1)); DumpFile="$1";;
    *) SQLiteFile="$1"
  esac
  shift;: "$((i=i+1))"
done

# If --help option specified or no arguments passed, print $HELPDOC and exit
if [ "$Help" ] || [ ! "$SQLiteFile" ] ; then
  echo "$HELPDOC"
  exit 0
fi

# Ensure sqlite3 is installed
if ! command -v sqlite3 >/dev/null 2>&1; then
  echo 'sqlite3 command not found, exiting'
  exit 1
fi

# Define array of SQL types
VARCHAR='VARCHAR(255)'
DATETIME='DATETIME'
INT='INT'
DECIMAL_10_2='DECIMAL(10,2)'
DECIMAL_27_18='DECIMAL(27,18)'
BOOLEAN='BOOLEAN'
MEDIUMTEXT='MEDIUMTEXT'
TEXT='TEXT'
CUSTOM='CUSTOM'
sqltypes=("$VARCHAR" "$DATETIME" "$INT" "$DECIMAL_10_2" "$DECIMAL_27_18" "$BOOLEAN" "$MEDIUMTEXT" "$TEXT" "$CUSTOM")

# Get all tables
IFS=' ' read -ra tables <<< "$(echo '.tables' | sqlite3 "$SQLiteFile")"
echo "There are ${#tables[@]} tables in $SQLiteFile"

# If --table was not provided or is invalid, prompt user for $table
if [ ! "$table" ] || ! echo "${tables[@]}" | grep "$table" >/dev/null 2>&1; then
  PS3="Please choose a table to dump: "
  select table in "${tables[@]}"; do
    ([ "$table" ] || echo "${tables[@]}" | grep "$REPLY" >/dev/null 2>&1) && break
    echo "Invalid option $REPLY"
  done
  [ ! "$table" ] && table="$REPLY"
fi
echo "You chose table $table"

# Prompt user for MySQL table name
read -rp "Enter a name for this table when dumping into MySQL (leave blank to use table name '$table'): "  TableName
[ ! "$TableName" ] && TableName="$table"
echo "Using table name '$TableName' when dumping into MySQL"

# Prompt user for MySQL dump file
read -rp "Enter an sql filename to dump into (leave blank to use filename '$TableName.sql'): "  DumpFile
[ ! "$DumpFile" ] && DumpFile="$TableName.sql"
echo "Using filename '$DumpFile' to dump SQL into"

# Dump table to dump file
dump_table="sqlite3 $SQLiteFile '.dump $table' > $DumpFile"
echo
echo "$dump_table"
echo
read -rp "Dumping table $table into $DumpFile, continue (y/n)? "
if [ "$REPLY" = 'y' ]; then
  if eval "$dump_table"; then
    linecount="$(wc -l "$DumpFile")"
    echo "Dump finished. Number of lines: $linecount"
  else
    echo "Something went wrong when running the command \`$dump_table\`"
    exit 1
  fi
fi

# If dump file does not exist, exit
if [ ! -f "$DumpFile" ]; then
  echo "$DumpFile does not exist, exiting"
  exit 1
fi

# Ask if user wishes to keep the current table schema
echo
grep -m 1 '^CREATE TABLE.*' "$DumpFile"
echo
echo "This is the current schema for table $TableName, modify?"
read -rp "Modify current schema (y/n): "
[ "$REPLY" = 'y' ] && ModifySchema='true'

# If user wants to modify schema, loop over each table column and prompt user for corresponding type
if [ "$ModifySchema" ]; then
  # Get all columns for selected table
  columns=()
  while IFS='' read -r line; do 
    columns+=("$line")
  done < <(echo "PRAGMA table_info($table)" | sqlite3 "$SQLiteFile" | awk -F'|' '{print $2}')
  # IFS=$'\n' read -ra columns <<< "$(echo "PRAGMA table_info($table)" | sqlite3 "$SQLiteFile" | awk -F'|' '{print $2}')"
  echo "${columns[@]}"
  i=$((0))
  for column in "${columns[@]}"; do
    # Print 10 values from column
    echo
    echo "SELECT $column from $table LIMIT 10;" | tee /dev/tty | sqlite3 "$SQLiteFile" | less -RiMSFX#4
    echo

    # Prompt user to select SQL type for column
    PS3="Select an SQL type for column '$column' in table '$table': "
    select sqltype in "${sqltypes[@]}"; do
      ([ "$sqltype" ] || echo "${sqltypes[@]}" | grep "$REPLY" >/dev/null 2>&1) && break
      echo "Invalid option $REPLY"
    done
    [ ! "$sqltype" ] && sqltype="$REPLY"

    # Add $sqltype to $coltypes array
    if [ "$sqltype" = "$CUSTOM" ]; then
      read -rp "Enter the custom type for column $column: "
      sqltype="$REPLY"
    fi
    echo "Setting column '$column' to type $sqltype"
    coltypes[$i]="$sqltype"
    ((i++))
  done

  # Assemble user-populated $coltypes array into a CREATE TABLE sql command
  create_table="CREATE TABLE IF NOT EXISTS $TableName (${columns[0]} ${coltypes[0]}"
  for ((j=1; j<${#columns[@]}; j++)); do
    create_table="$create_table, ${columns[$j]} ${coltypes[$j]}"
  done
  create_table="$create_table);"
  echo
  echo "$create_table"
  echo
fi

# Ask if user wants to load sql script after SQLite -> MySQL script conversion
if [ ! "$Execute" ]; then
  read -rp "Load $DumpFile into MySQL after preprocessing (y/n)? "
  [ "$REPLY" = 'y' ] && Execute='true'
fi

# Convert SQLite script to MySQL script
echo "Carrying out the following operations on $DumpFile:"
echo "   Deleting lines starting with 'PRAGMA'"
echo "   Replacing 'BEGIN TRANSACTION;' with 'BEGIN;'"
echo "   Replacing 'CREATE TABLE $table' with 'CREATE TABLE IF NOT EXISTS $TableName'"
echo "   Replacing 'INSERT INTO $table' with 'INSERT INTO $TableName'"
echo "   Replacing 'AUTOINCREMENT' with 'AUTO_INCREMENT'"
echo "   Deleting lines with 'CREATE INDEX'"
echo "   Deleting all backslashes"
# Weird emojis 🚡 and 🔠 used to represent \r and \n respectively, so that the
# rest of the backslashes can be then purged from the file. This is because
# many of the messages strings carry excessive bachslashes to decorate the text
# \\\\\\\ LIKE THIS \\\\\\\, which really screws with the MySQL interpreter
# when dumping the SQL queries into the database
sed -i.bak \
  -e '/^PRAGMA/d' \
  -e "s/BEGIN TRANSACTION\;/BEGIN\;/g" \
  -e "s/CREATE TABLE $table/CREATE TABLE IF NOT EXISTS $TableName/g" \
  -e "s/INSERT INTO $table/INSERT INTO $TableName/g" \
  -e "s/AUTOINCREMENT/AUTO_INCREMENT/g" \
  -e "/^CREATE INDEX/d" \
  -e 's/\\r/🚡/g' \
  -e 's/\\n/🔠/g' \
  -e 's/\\//g' \
  "$DumpFile"

# If user chose to modify the schema, insert the custom CREATE TABLE command created
if [ "$ModifySchema" ] && [ "$create_table" != '' ]; then
  echo "   Replacing \`$(grep -m 1 '^CREATE TABLE.*' "$DumpFile" | tr -d '\n')\` with \`$create_table\`"
  sed -i.bak "s/^CREATE TABLE.*/$create_table/g" "$DumpFile"
fi

# load sql file into MySQL
load_sql() {
  if [ ! "$Db" ]; then read -rp "Enter your MySQL database name: "; Db="$REPLY"; fi
  if [ ! "$User" ]; then read -rp "Enter your MySQL username: "; User="$REPLY"; fi
  if [ ! "$Pass" ]; then read -rp "Enter your MySQL password: "; Pass="$REPLY"; fi
  local load_sql_file="mysql -u$User -p$Pass -D $Db < $1"
  echo
  echo "$load_sql_file"
  echo
  eval "$load_sql_file"
  return
}

# Execute the load_sql function for filename $DumpFile
if [ "$Execute" ]; then
  load_sql "$DumpFile"
fi