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:
- pg_dump
- Quote field names with backticks
- Remote any SET statements
...
- Remove any
...
- SELECT pg_catalog.setvalstatements
- 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 | ||||
|---|---|---|---|---|
| ||||
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 | ||||
|---|---|---|---|---|
| ||||
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 | ||||
|---|---|---|---|---|
| ||||
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 | ||||
|---|---|---|---|---|
| ||||
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 | ||||
|---|---|---|---|---|
| ||||
asdfFIXME |
MySQL to SQLite
| Code Block | ||||
|---|---|---|---|---|
| ||||
asdfasdFIXME |