63 lines
2.0 KiB
Bash
63 lines
2.0 KiB
Bash
#!/bin/bash
|
|
|
|
## Description and Usage
|
|
# This file will take a sqlite3 dump sql file as input and convert it into Mysql dump file
|
|
# sqlite3 latestCache.db .dump > latestCache_dump.sql will create latestCache_dump.sql from latestCache.db sqlite3 file
|
|
# chmod +x sqlite_to_mysql.sh is needed on bash shell first to make this script run
|
|
# ./sqlite_to_mysql.sh latestCache_dump.sql will produce latestCache_dump_mysql.sql which can be imported in MySQL
|
|
|
|
# Check if input file is provided
|
|
if [ "$#" -ne 1 ]; then
|
|
echo "Usage: $0 <sqlite_sql_file>"
|
|
exit 1
|
|
fi
|
|
|
|
# Input file
|
|
input_file=$1
|
|
|
|
# Output file
|
|
output_file="${input_file%.sql}_mysql.sql"
|
|
|
|
# Create a new output file
|
|
> "$output_file"
|
|
|
|
# Function to modify the SQL dump for MySQL compatibility
|
|
convert_sqlite_to_mysql() {
|
|
awk '
|
|
# Remove SQLite-specific PRAGMA and transaction statements
|
|
/PRAGMA.*;/ { next }
|
|
/BEGIN TRANSACTION;/ { next }
|
|
/COMMIT;/ { next }
|
|
|
|
# Replace AUTOINCREMENT with AUTO_INCREMENT
|
|
{ gsub(/AUTOINCREMENT/, "AUTO_INCREMENT") }
|
|
|
|
# Adjust integer types to auto increment if they are primary keys
|
|
{ gsub(/INTEGER NOT NULL/, "INT NOT NULL AUTO_INCREMENT") }
|
|
|
|
# Convert VARCHAR to TEXT
|
|
{ gsub(/VARCHAR/, "TEXT") }
|
|
|
|
# Convert BOOLEAN to TINYINT for MySQL compatibility
|
|
{ gsub(/\bBOOLEAN\b/, "TINYINT(1)") }
|
|
|
|
# Handle CREATE TABLE blocks to remove quotes around column names
|
|
/CREATE TABLE/ { in_create_table=1 }
|
|
in_create_table && /;/ { in_create_table=0 }
|
|
in_create_table { gsub(/"([a-zA-Z0-9_]+)"/, "&"); gsub(/"/, "") }
|
|
|
|
# Print the modified line
|
|
{ print }
|
|
' "$1" |
|
|
# Replace '' with \' according to the specified conditions
|
|
sed "/^INSERT INTO/ { s/\([^,(]\)''/\1\\\\'/g; s/''\([^,)]\)/\\\\'\1/g }" > "$output_file"
|
|
|
|
|
|
}
|
|
|
|
# Call the conversion function
|
|
convert_sqlite_to_mysql "$input_file"
|
|
|
|
# Print success message
|
|
echo "Conversion complete. MySQL-compatible file created: $output_file"
|