NAME

dmarc_sqlite_to_mysql - Export SQLite database to MySQL SQL format

SYNOPSIS

dmarc_sqlite_to_mysql [options] <sqlite_db_path> [output_file]

OPTIONS

-h, --help

Print help message and exit.

-v, --verbose

Print status messages to STDERR during export.

ARGUMENTS

sqlite_db_path

Path to the SQLite database file to export.

output_file (optional)

Output file for the SQL statements. If omitted, writes to STDOUT.

DESCRIPTION

This script exports data from a SQLite database into MySQL-compatible SQL INSERT statements. It respects foreign key dependencies by exporting tables in the correct order.

Important: The script automatically normalizes domain names to lowercase and handles case-insensitive collisions. Since MySQL's default collation is case-insensitive, domains like "Example.com" and "example.com" would cause UNIQUE constraint violations. The script deduplicates these by: - Normalizing all domains to lowercase - Tracking id mappings for case-insensitive duplicates - Automatically remapping all foreign key references

Binary IP address data (source_ip varbinary field) are converted to hexadecimal format for safe import into MySQL.

Note: Foreign key lookup tables (fk_disposition, fk_disposition_reason, fk_dkim_result, fk_spf_result, fk_spf_scope) are NOT exported as they are pre-populated by the MySQL schema file. You must create the MySQL database schema FIRST using mail_dmarc_schema.mysql before importing the data.

The output is suitable for importing directly into MySQL using:

mysql -u user -p database < share/mail_dmarc_schema.mysql
mysql -u user -p database < export.sql

EXAMPLE

Export to file: dmarc_sqlite_to_mysql mail_dmarc.db mail_dmarc_export.sql

Complete migration workflow: # 1. Create the MySQL database and schema first mysql -u root -p -e "CREATE DATABASE mail_dmarc" mysql -u root -p mail_dmarc < share/mail_dmarc_schema.mysql

# 2. Export SQLite data
dmarc_sqlite_to_mysql mail_dmarc.db mail_dmarc_export.sql

# 3. Import the data
mysql -u root -p mail_dmarc < mail_dmarc_export.sql

Export directly to MySQL: dmarc_sqlite_to_mysql mail_dmarc.db | mysql -u user -p database