Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 5.3

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

Currently there are 3 back-ends that are known to work: Postgres 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

Basically Use a shell script one liner:that does:

  1. pg_dump
  2. Quote field names with backticks
  3. Remote any SET statements
  4. Remove any SELECT pg_catalog.setval statements
  5. Feed INSERT statements into MySQL

 

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

code
Code Block
themeMidnight
languagebash
pg_dump -U username -h hostname -a --inserts filesender |
sed -r 's/^(INSERT\ INTO\ )(files|logs)(\ VALUES\ )/\1`\2`\3/g' |
grepsed --color=never "^INSERT INTO"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.

Code Block
themeMidnight
languagebash
pg_dump -U username -h hostname -a --inserts filesender |
sed -r '/SET\ ([a-z_]*)\ =\ (.*);/d' |
sed -r '/SELECT\ pg_catalog.setval(.*);/d' |
sqlite3 filesender.sqlite

 

 

SQLite to PostgreSQL

Code Block
themeMidnight
languagebash
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

Code Block
themeMidnight
languagebash
FIXME 



MySQL to PostgreSQL

Code Block
themeMidnight
languagebash
FIXME

 

 

MySQL to SQLite

Code Block
themeMidnight
languagebash
FIXME