FileSender supports several different database back-ends, by virtue of PHP's PDO.

Currently there are 3 back-ends that are known to work: PostgreSQL, MySQL, and SQLite.

Assumptions:

• The target database has the right schema, tables, etc, and is empty. See the create scripts in the scripts/ directory.
• Both the source and destination database are called filesender
• SQLite databases are called filesender.sqlite

Postgres to MySQL

...

pgdump

add backticks to field values

...

Use a shell script that does:

1. pg_dump
2. Quote field names with backticks
3. Remote any SET statements

...

1. Remove any

...

1. SELECT pg_catalog.setvalstatements
2. Feed INSERT statements into MySQL

 

You can't simply filter all lines that start with INSERT INTO because the data contains newlines itself.

Code Block
theme Midnight language bash
pg_dump -U username -h hostname -a --inserts -t 'files' -t 'logs' filesender | sed -r 's/^(INSERT\ INTO\ )(files|logs)(\ VALUES\ )/\1`\2`\3/g' | sed -r '/SET\ ([a-z_]*)\ =\ (.*);/d' | sed -r '/SELECT\ pg_catalog.setval(.*);/d' | mysql -u username -h hostname -p filesender

 

PostgreSQL to SQLite

Largely the same shell script, but no backticks are needed here.

This takes a while depending on the amount of records.

pg_dump -U username -h hostname -a --inserts filesender |
grep --color=never "^INSERT INTO" |
sed -r '/SET\ ([a-z_]*)\ =\ (.*);/d' |
sed -r '/SELECT\ pg_catalog.setval(.*);/d' |
sqlite3 filesender.sqlite

SQLite to PostgreSQL

fixme

sqlite3 filesender.sqlite |
grep -E --color=never "^INSERT\ INTO\ \"(logs|files)\" VALUES"echo -e ".mode insert files\nSELECT * FROM files;\n.mode insert logs\nSELECT * FROM logs;" |
sqlite filesender.sqlite |
psql -U username -h hostname filesender

SQLite to MySQL

fixme

sqlite3 filesender.sqlite .dump |
sed -r 's/^(INSERT\ INTO\ )"(logs|files)"(\ VALUES)/\1`\2`\3/g' |
grep --color=never "^INSERT INTO \`" |
mysql -u username -h hostname -p filesender

FIXME 

MySQL to PostgreSQL

asdfFIXME

MySQL to SQLite

asdfasdFIXME