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: 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
themeMidnight
languagebash
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.

Code Block
themeMidnight
languagebash
pg_dump -U username -h hostname -a --inserts -t 'files' -t 'logs' 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

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

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

Code Block
themeMidnight
languagebash
asdfFIXME

 

 

MySQL to SQLite

Code Block
themeMidnight
languagebash
asdfasdFIXME