Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#7856 closed (wontfix)

custom_sql_for_model incorrectly parses the sql file for advanced sql statements

Reported by: thebitguru Owned by: nobody
Component: Core (Other) Version: master
Severity: Keywords:
Cc: farhan@… Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description 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 = NEW.image_id;
        UPDATE core_image SET number_linked = number_linked - 1 WHERE = OLD.image_id

Then when I do 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 = NEW.image_id;',

Next SQL statement
==> u'\tUPDATE core_image SET number_linked = number_linked - 1 WHERE = 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 = NEW.image_id;

Change History (2)

comment:1 Changed 7 years ago by mtredinnick

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to wontfix
  • Status changed from new to closed

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.

comment:2 Changed 7 years ago by thebitguru

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.

Note: See TracTickets for help on using tickets.
Back to Top