Migrationen von Daten zwischen zwei Datenbanken sind in der Regel eine komplexe Operation. Mit SQL kann man dabei sehr viele Probleme lösen, so dass meistens gar keine Programmierung in irgendeiner anderen Sprache notwendig sein müsste.
Die im folgenden beschriebenen Operationen sind aufwändig und nicht für Anwendungsfälle mit Nutzerinterkation geeignet. Sie eignen sich aber hervorragend für einmalige Migrationen oder Reparaturen an Datenbanken.
Die folgenden Beispiele beziehen sich auf MySQL, sind aber in vielen Fällen auch auf jede andere Form von SQL-Datenbank anwendbar.
SQL-Skripte
Als Programmierer denkt man bei SQL immer nur an einzelne Kommandos, die man aus der sicheren Umgebung seiner Programmiersprache (wie z.B. PHP) absetzt. Tatsächlich kann man aber auch halbwegs komplexe Skripte in SQL schreiben.
Diese Skripte speichert man einfach als .sql-Datei, und kann danach diese Datei komplett an die Datenbank zur Ausführung schicken. MySQL bzw. Bash bietet darüber hinaus die Option, die Rückgabe des Befehls in einer andere Datei zu schreiben, die interessanterweise dann eine TSV-Datei ist:
$ mysql -h ${HOSTNAME} -u ${USERNAME} -p${PASSWORD} ${DATABASE} < MYSCRIPT.sql
$ mysql -h ${HOSTNAME} -u ${USERNAME} -p${PASSWORD} ${DATABASE} < MYSCRIPT.sql > output.txt # Pipe output into TXT file in TSV format
Alternativ kann man das Skript über ein GUI-Tool wie z.B. PhpMyAdmin oder HeidiSQL ausführen.
Gerade bei komplexen Skripten sollte man die Möglichkeit nutzen, in SQL-Skripten Kommentare zu hinterlassen. Kommentare werden einfach mit einem # eingeleitet.
Übrigens lohnt sich vor komplexen Operationen, ein Backup der Datenbank zu erzeugen, bzw. erstmal an einer Kopie der Datenbank zu üben. Ein Backup erzeugt man mit einer einfachen Kommandozeile:
$ mysqldump -h ${HOSTNAME} -u ${USERNAME} -p${PASSWORD} --skip-comments --add-drop-table --single-transaction --quick ${DATABASE} > dbdump-$(date "+%Y-%m-%d_%H-%M").sql
Bei Backups werden auch gerne die Daten einzelner Tabellen ignoriert. Dafür muss man den Dump in zwei Schritten erzeugen:
$ mysqldump -h ${HOSTNAME} -u ${USERNAME} -p${PASSWORD} --skip-comments --add-drop-table --single-transaction --quick --ignore-table=${DATABASE}.${TABLE1} --ignore-table=${DATABASE}.${TABLE2} ${DATABASE} > dbdump.sql # Dump tables with data, ignore TABLE1 and TABLE2
$ mysqldump -h ${HOSTNAME} -u ${USERNAME} -p${PASSWORD} --skip-comments --add-drop-table --single-transaction --quick --no-data ${DATABASE} ${TABLE1} ${TABLE2} >> dbdump.sql # Dump structure of TABLE1 and TABLE2, append it to dbdump.sql
Sub-SELECT
Einer meiner größten Freunde für komplexe Fragen ist das Sub-SELECT. Damit kann eine WHERE-Bedingung die Werte eines anderen SELECT erhalten, und so komplexe Fragen lösen.
# Find categories without existing parents
SELECT * FROM categories WHERE categories.parent NOT IN (
SELECT id FROM categories
);
Eine kleine Einschränkung bei schreibenden Operationen (INSERT, REPLACE, UPDATE, DELETE) ist, dass das Sub-SELECT sich nicht auf die selbe Tabelle beziehen darf, in der man gerade schreiben möchte. Es gibt aber tatsächlich einen kleinen Trick zum Schreiben mit Sub-SELECTs, den man dafür verwenden kann:
# Delete categories without existing parents
DELETE FROM categories WHERE categories.parent NOT IN (
SELECT id FROM (
SELECT id FROM categories
) AS tmp
);
Bedingte Ausführung
Bei der Änderung von Daten sollte man die Möglichkeiten von SQL nutzen, einige Kommandos fehlertoleranter zu machen. So kann ein INSERT fehlschlagen, wenn ein bereits vergebener UNIQUE-Key verwendet wird – ein INSERT IGNORE würde in diesem Fall davon ausgehen, dass die Zeile einfach nicht ausgetauscht werden muss, und macht mit der nächsten Zeile weiter.
Den Zusatz IGNORE gibt es auch für UPDATE und DELETE.
Bei der Erzeugung von neuen Tabellen hilft CREATE TABLE IF NOT EXISTS, bereits existierende Tabellen zu ignorieren.
SQL erzeugen… mit SQL
Gerade bei Migrationen kann es notwendig sein, Teile einer Datenbank in eine andere Form zu bringen. Wie bei jeder anderen Programmiersprache auch kann man mit SQL auch neues SQL erzeugen. Das Hilfsmittel dafür ist die SQL-Funktion CONCAT:
# Generate query for updating prices
SELECT
CONCAT(
"UPDATE articles_prices SET price = ",
products.products_price,
" WHERE articles_prices.ean = ",
QUOTE(products.products_model),
";"
) AS '# Update prices'
FROM personal_offers
JOIN products ON products.id = personal_offers.products_id
WHERE personal_offers.type = 1
;
Das Ergebnis dieser Query lässt man in eine separate Datei exportieren, was folgende Ausgabe erzeugt:
# Update prices
UPDATE articles_prices SET price = 12.40 WHERE articles_prices.ean = 'ABC 1';
UPDATE articles_prices SET price = 19.49 WHERE articles_prices.ean = 'ABC 2';
UPDATE articles_prices SET price = 14.99 WHERE articles_prices.ean = 'ABC 3';
Mit weiteren SQL-String-Funktionen wie z.B. IF oder REPLACE kann man auch komplexere Ausgaben erzeugen:
# Generate query for updating prices
SELECT
CONCAT(
"UPDATE articles_prices SET price = ",
IF(personal_offers.personal_offer = 0, products.products_price, personal_offers.personal_offer),
" WHERE articles_prices.ean = ",
QUOTE(REPLACE(products.products_model,' ','-')),
";"
) AS '# Update prices'
FROM personal_offers
JOIN products ON products.id = personal_offers.products_id
WHERE personal_offers.type = 1
;
Die obigen Beispiele sind natürlich sehr trivial. Bei komplexen Migrationen können so aber relativ komplexe Operationen einfach gehandhabt werden, gerne auch zwischen infrastrukturell getrennten Datenbanken.
Oder ganz andere Dateien mit SQL erzeugen
Mit SQL kann man natürlich auch andere Dateien erzeugen als nur SQL-Skripte. Ein gutes Beispiel ist das Erzeugen einer statischen Redirect-Tabelle für den Apache:
SELECT
CONCAT(
'RewriteRule\t"^/',
tmp.url,
'$"\t"https://%{HTTP_HOST}/',
urls.path
'"\t[R=301,L]'
) AS '# Apache redirects'
FROM tmp
JOIN urls ON tmp.article_id = urls.article_id
WHERE 1
;
Das Ergebnis dieser Query generiert eine TSV-Datei, die man entweder in einer Tabellenkalkulation nachbearbeiten lassen kann – oder die passenden Texte, die man direkt in die Apache-Konfiguration übertragen lassen kann.
# Apache redirects
RewriteRule "^/url_1/$" "https://%{HTTP_HOST}/url_a/" [R=301,L]
RewriteRule "^/url_2/$" "https://%{HTTP_HOST}/url_b/" [R=301,L]
Fazit
Getreu nach dem Motto „Wenn man einen Hammer hat, sieht jedes Problem aus wie ein Nagel“ greifen wir als Programmierer bei der Lösung von SQL-Problemen meistens nicht zu SQL, sondern zu unserer Lieblings-Programmiersprache. Dabei nehmen wir zum Teil erhebliche Mehraufwände in Kauf (Aufbau der DB-Verbindung, Quoting, Transformationen).
Mit ein bisschen Kenntnis von SQL kann man diese Aufwände vermeiden, und gleichzeitig die komplette Kraft von SQL nutzen.