custom_sql_for_model incorrectly parses the sql file for advanced sql statements

[Farhan Ahmad]

django.core.management.custom_sql_for_model incorrectly parses advanced SQL statements in model sql files. In my specific case I am adding a ​Postgresql RULE that has sub-statements in it. In this case the custom sql function breaks up the one rule statement into multiple ones.

I have the following SQL in app/sql/model.sql (replace app and model appropriately).

CREATE RULE productlinkimage_onupdate_set_linked AS ON UPDATE TO core_productlinkimage
WHERE NEW.image_id <> OLD.image_id AND NEW.is_deleted = OLD.is_deleted
DO (
        UPDATE core_image SET number_linked = number_linked + 1 WHERE core_image.id = NEW.image_id;
        UPDATE core_image SET number_linked = number_linked - 1 WHERE core_image.id = OLD.image_id
);

Then when I do django-admin.py syncdb it creates the following set of commands to execute separately, and, obviously, fails.

Starting SQL statement (I have added the line breaks for for better wrapping)
==> u'\nCREATE RULE productlinkimage_onupdate_set_linked AS ON UPDATE TO core_productlinkimage
\nWHERE NEW.image_id <> OLD.image_id AND NEW.is_deleted = OLD.is_deleted\nDO (
\n\tUPDATE core_image SET number_linked = number_linked + 1 WHERE core_image.id = NEW.image_id;',

Next SQL statement
==> u'\tUPDATE core_image SET number_linked = number_linked - 1 WHERE core_image.id = OLD.image_id\n);',

The exact output is...

Installing custom SQL for core.Product model
Failed to install custom SQL for core.Product model: syntax error at end of input
LINE 6: ...nked = number_linked + 1 WHERE core_image.id = NEW.image_id;

[Malcolm Tredinnick]

We don't intend to put a full SQL parser into Django, which is what would be required to handle all of these cases smoothly, So this is a case of "don't do that".

If you really need to insert such complex SQL, then register a signal handler for syncdb and write Python code to do the insert yourself. Django supports simple SQL statements (think "no embedded semicolon" as one guideline, but there are other edge-cases as well) and multiple such statements in a file (so we have to split them up, since not all DB backends can handle multiple statements in one call). Since our goal is to be an 85% solution covering all the common cases and making other cases possible (through the syncdb signal), the fact that we can't parse the above is unfortunate, but not tragic.

[Farhan Ahmad]

Thanks for quickly reviewing this, Malcolm. I agree with your decision, and in my case I have moved this rule to a separate file that I will manually load using psql. I looked into the signals route, but I am going to skip that for now because the alternative is quick and easy, and my application is very specific, i.e. not looking at distributing so it shouldn't be a problem. Thanks again.