manage.py custom sql fails with triggers and sqlite

[hambaloney@…]

Ran into an issue where using the manage.py interface to bring in custom SQL containing triggers for sqlite causes it to fail with the error:

The full error: near ";": syntax error

Dug around to find in django/core/management.py -> get_custom_sql_for_model, the sql commands are parsed by semicolons.

sqlite triggers follow the format:

CREATE TRIGGER trigger-name [ BEFORE | AFTER ]
database-event ON [database-name .] table-name
BEGIN
trigger step;
trigger step;
END;

manage.py attempts to parse and run each line ending in a semicolon separately causing the error.

As a temporary fix I've modified management.py as follows, please don't hurt me, I know its ugly. Note, I'm using sqlite3.

[anonymous]

This is a dupe of problems mentioned in #3214, amongst other places. We don't support multi-line or complex SQL data in initial SQL files and it's not clear we ever will (because we would essentially have to write a generic SQL parser that understood the SQL for every single backend we support).

If somebody every comes up with a neat, truly generic solution, we'll lok at it, but micro-fixes like this unfortunately cause more trouble than they're worth, because every change impacts some other use-case and we end up chasing our tail forever.

[Malcolm Tredinnick]

Last comment was by me. Not sure why Trac decided I was anonymous (since I was logged in at the time).