| 1 |
4426
|
aaronmk
|
#!/bin/sh
|
| 2 |
|
|
# Translates a MySQL DB export to PostgreSQL
|
| 3 |
|
|
# The export must be created with:
|
| 4 |
|
|
# `--compatible=postgresql --add-locks=false --set-charset`
|
| 5 |
|
|
# Usage: self {input...|<input} >translated
|
| 6 |
|
|
|
| 7 |
7542
|
aaronmk
|
selfDir="$(dirname -- "$0")"
|
| 8 |
|
|
|
| 9 |
4426
|
aaronmk
|
sedEreFlag="$(test "$(uname)" = Darwin && echo E || echo r)"
|
| 10 |
|
|
|
| 11 |
|
|
sed () { "$(which sed)" -"$sedEreFlag" "$@";}
|
| 12 |
|
|
|
| 13 |
4463
|
aaronmk
|
echo 'SET standard_conforming_strings = off;'
|
| 14 |
5887
|
aaronmk
|
echo 'SET escape_string_warning = off;'
|
| 15 |
4426
|
aaronmk
|
sed \
|
| 16 |
7541
|
aaronmk
|
-e 's/ UNIQUE KEY ("[^"]*")/ \/*CONSTRAINT \1 *\/UNIQUE/g' \
|
| 17 |
7540
|
aaronmk
|
-e 's/ UNIQUE KEY/ UNIQUE/g' \
|
| 18 |
|
|
-e 's/^ ((CONSTRAINT "[^"]*" )?((FULLTEXT|FOREIGN) )?KEY .*[^,])(,?)$/ \/*\1*\/CHECK (true)\5/' \
|
| 19 |
4426
|
aaronmk
|
-e "s/\/\*.* (SET NAMES )([0-9A-Za-z_]+) \*\/;/\1'\2';/" \
|
| 20 |
7534
|
aaronmk
|
-e 's/ (CHARACTER SET|COLLATE) [0-9A-Za-z_]+//g' \
|
| 21 |
6070
|
aaronmk
|
-e "s/ COMMENT '[^']*'//g" \
|
| 22 |
7693
|
aaronmk
|
-e 's/ (binary|blob)/ bytea/g' \
|
| 23 |
4426
|
aaronmk
|
-e 's/ longtext/ text/g' \
|
| 24 |
6165
|
aaronmk
|
-e 's/ (int|mediumint|smallint|tinyint)([ (])/ integer\2/g' \
|
| 25 |
4426
|
aaronmk
|
-e 's/ double/ double precision/g' \
|
| 26 |
7694
|
aaronmk
|
-e 's/ (integer|bigint|bytea|double precision|float)(\([^)]*\))?( unsigned)?/ \1/g' \
|
| 27 |
7695
|
aaronmk
|
-e 's/ (char)\(0\)/ \1/g' \
|
| 28 |
7535
|
aaronmk
|
-e 's/ datetime/ timestamp/g' \
|
| 29 |
7542
|
aaronmk
|
"$@"\
|
| 30 |
|
|
|"$selfDir/my2pg.data"
|
