Poor SQL formatting

[decadence]

Hello. Firstly thanks for the great free application. It's very fast and handy.
But there is one problem I faced from time to time when using HeidiSQL.

Steps to reproduce this issue

1. Paste large SQL to Query tab
2. Edit - Reformat SQL

Let's pretend we have following unformatted SQL query.

SELECT el.ID, el.NAME , el.IBLOCK_SECTION_ID, price.PRICE FROM b_iblock_element el JOIN b_iblock_element_property SHOW_IN_CAT ON ( SHOW_IN_CAT.IBLOCK_ELEMENT_ID = el.ID AND SHOW_IN_CAT.IBLOCK_PROPERTY_ID = 716 AND SHOW_IN_CAT.VALUE = "329" ) JOIN (SELECT prop.IBLOCK_ELEMENT_ID FROM b_iblock_element_property prop WHERE prop.IBLOCK_PROPERTY_ID IN (676, 678, 674, 664, 665, 675,456) AND prop.VALUE = "экокожа") AS prop ON prop.IBLOCK_ELEMENT_ID = el.ID JOIN b_iblock_element_property DESTINATION ON ( DESTINATION.IBLOCK_PROPERTY_ID = 1315 AND DESTINATION.VALUE = "для молодежной" AND DESTINATION.IBLOCK_ELEMENT_ID = el.ID ) JOIN b_catalog_price price ON (el.ID = price.PRODUCT_ID) WHERE el.IBLOCK_ID = 38 AND el.IBLOCK_SECTION_ID IN (18449, 18466)

Current behavior

HeidiSQL 10.2.0 Reformat SQL feature produces next SQL:

SELECT el.ID, el.NAME, el.IBLOCK_SECTION_ID, price.PRICE
FROM b_iblock_element el
JOIN b_iblock_element_property SHOW_IN_CAT ON (SHOW_IN_CAT.IBLOCK_ELEMENT_ID = el.ID AND SHOW_IN_CAT.IBLOCK_PROPERTY_ID = 716 AND SHOW_IN_CAT.VALUE = "329")
JOIN (
SELECT prop.IBLOCK_ELEMENT_ID
FROM b_iblock_element_property prop
WHERE prop.IBLOCK_PROPERTY_ID IN (676, 678, 674, 664, 665, 675,456) AND prop.VALUE = "экокожа") AS prop ON prop.IBLOCK_ELEMENT_ID = el.ID
JOIN b_iblock_element_property DESTINATION ON (DESTINATION.IBLOCK_PROPERTY_ID = 1315 AND DESTINATION.VALUE = "для молодежной" AND DESTINATION.IBLOCK_ELEMENT_ID = el.ID)
JOIN b_catalog_price price ON (el.ID = price.PRODUCT_ID)
WHERE el.IBLOCK_ID = 38 AND el.IBLOCK_SECTION_ID IN (18449, 18466)

Better but still not very readable. It just adds new lines but it's hard to analyze this query.

Expected behavior

For example SQLyog 12.2.6 produces this SQL:

SELECT
  el.ID,
  el.NAME,
  el.IBLOCK_SECTION_ID,
  price.PRICE
FROM
  b_iblock_element el
  JOIN b_iblock_element_property SHOW_IN_CAT
    ON (
      SHOW_IN_CAT.IBLOCK_ELEMENT_ID = el.ID
      AND SHOW_IN_CAT.IBLOCK_PROPERTY_ID = 716
      AND SHOW_IN_CAT.VALUE = "329"
    )
  JOIN
    (SELECT
      prop.IBLOCK_ELEMENT_ID
    FROM
      b_iblock_element_property prop
    WHERE prop.IBLOCK_PROPERTY_ID IN (676, 678, 674, 664, 665, 675, 456)
      AND prop.VALUE = "экокожа") AS prop
    ON prop.IBLOCK_ELEMENT_ID = el.ID
  JOIN b_iblock_element_property DESTINATION
    ON (
      DESTINATION.IBLOCK_PROPERTY_ID = 1315
      AND DESTINATION.VALUE = "для молодежной"
      AND DESTINATION.IBLOCK_ELEMENT_ID = el.ID
    )
  JOIN b_catalog_price price
    ON (el.ID = price.PRODUCT_ID)
WHERE el.IBLOCK_ID = 38
  AND el.IBLOCK_SECTION_ID IN (18449, 18466)

It formats JOINs and moves every condition on new line plus adds tabs.

I found some old threads on your website forum (like here) but seems nothing has changed from that time.

Is there any chance to enhance this feature?
Thanks.

[ansgarbecker]

The current reformat logic was a quick shot, some years ago, but at least gave me line breaks where I wanted them. This, and also the fact that it's my own brewn code, are the advantages here. But I'm aware this provides quite poor results. So, what ideas do we have?

• write our own code reformatter: Problem: much effort (I guess).
• use some online formatter. There seem to be several very good ones, e.g. the one from jdorn . Problem: sending user's SQL code to external services is highly critical.
• use a ready and free Delphi library. Problem: I can't find any, at least not a free one with an OpenSource license.

[decadence]

@ansgarbecker thanks for your attention.
I looked jdorn/sql-formatter and this can be used as simple PHP script on the user's side without any web server (but PHP is required).

For example you can add option which holds path to command for reformatting and pass query to it.
php sql-formatter.php "SELECT * from users".

sql-formatter.php then would be like:

require_once('SqlFormatter.php');
echo SqlFormatter::format($argv[1]);

And get its output to HeidiSQL.
This is not very handy to users to set up but let them not sending sensitive data to other services.

[ansgarbecker]

Yea, interesting idea.

I just found a free SQL formatter written in Delphi: https://github.com/SqlToys/SqlFormatter
I could contact the author if he's permitting HeidiSQL to use that. And probably also ask him how to compile, as the master branch shows some unavailable components in my Delphi.

[ansgarbecker]

Or I'll give it a try and enhance my existing formatter first. The other options have their own disadvantages. I should at least check how much effort it will be to implement a similar formatting as your example code from SQLyog.

[decadence]

Thanks! Will wait for possible solutions.

[ansgarbecker]

jdorn's parser creates following output, quite impressive:

SELECT 
  el.ID, 
  el.NAME, 
  el.IBLOCK_SECTION_ID, 
  price.PRICE 
FROM 
  b_iblock_element el 
  JOIN b_iblock_element_property SHOW_IN_CAT ON (
    SHOW_IN_CAT.IBLOCK_ELEMENT_ID = el.ID 
    AND SHOW_IN_CAT.IBLOCK_PROPERTY_ID = 716 
    AND SHOW_IN_CAT.VALUE = "329"
  ) 
  JOIN (
    SELECT 
      prop.IBLOCK_ELEMENT_ID 
    FROM 
      b_iblock_element_property prop 
    WHERE 
      prop.IBLOCK_PROPERTY_ID IN (676, 678, 674, 664, 665, 675, 456) 
      AND prop.VALUE = "экокожа"
  ) AS prop ON prop.IBLOCK_ELEMENT_ID = el.ID 
  JOIN b_iblock_element_property DESTINATION ON (
    DESTINATION.IBLOCK_PROPERTY_ID = 1315 
    AND DESTINATION.VALUE = "для молодежной" 
    AND DESTINATION.IBLOCK_ELEMENT_ID = el.ID
  ) 
  JOIN b_catalog_price price ON (el.ID = price.PRODUCT_ID) 
WHERE 
  el.IBLOCK_ID = 38 
  AND el.IBLOCK_SECTION_ID IN (18449, 18466)

Comparing it to your SQLyog example, differences are minor (left: jdorn, right: SQLyog):

[decadence]

Yes, pretty similar. As I see that PHP library has not any syntax settings so this output is only one way to go. But that would be enough.

[ansgarbecker]

Yes, and that library seems to be unmaintained since 2015. Not the best option probably.

[leeoniya]

porting https://github.com/jdorn/sql-formatter/blob/master/lib/SqlFormatter.php to delphi is probably not a huge task, though.

i've "contributed" to that lib in my past php life.

jdorn/sql-formatter#34
jdorn/sql-formatter#32
jdorn/sql-formatter#30

[decadence]

Of course the best way is if you can write your own formatter and not to rely on third party libraries (especially written on other languages and with lack of support).

But we all understand you provide HeidiSQL for free and it may require much efforts so maybe option with call external command is applicable for the first time.

[evs-xsarus]

Might javascript be easier, you can use https://github.com/kufii/sql-formatter-plus . Just found this one for Visual Studio Code and indents nicely. Turns out to be a new implementation of jdorn's version.

[maxiride]

I initially came here to report on the subject but I now see that the issue has been added to the 11.1 milestone so I'll just leave my comment here as a reference.

I noticed two things:

• IF statements aren't recognized: word doesn't get coloured nor uppercased
• FOREIGN KEYs constrains aren't placed o new lines.

IF example

FOREIGN KEY example

Well after uploading the GIFs I noticed the typo in the CREATE statement 😆, nonetheless also with the correction the behaviour remains.

[ansgarbecker]

Next HeidiSQL build now finally has an additional online formatter, using doctrine/sql-formatter :

This is how the example in the very first post here gets reformatted:

SELECT
   el.ID,
   el.NAME,
   el.IBLOCK_SECTION_ID,
   price.PRICE
FROM
   b_iblock_element el
   JOIN b_iblock_element_property SHOW_IN_CAT ON (
      SHOW_IN_CAT.IBLOCK_ELEMENT_ID = el.ID
      AND SHOW_IN_CAT.IBLOCK_PROPERTY_ID = 716
      AND SHOW_IN_CAT.VALUE = "329"
   )
   JOIN (
      SELECT
         prop.IBLOCK_ELEMENT_ID
      FROM
         b_iblock_element_property prop
      WHERE
         prop.IBLOCK_PROPERTY_ID IN (676, 678, 674, 664, 665, 675, 456)
         AND prop.VALUE = "экокожа"
   ) AS prop ON prop.IBLOCK_ELEMENT_ID = el.ID
   JOIN b_iblock_element_property DESTINATION ON (
      DESTINATION.IBLOCK_PROPERTY_ID = 1315
      AND DESTINATION.VALUE = "для молодежной"
      AND DESTINATION.IBLOCK_ELEMENT_ID = el.ID
   )
   JOIN b_catalog_price price ON (el.ID = price.PRODUCT_ID)
WHERE
   el.IBLOCK_ID = 38
   AND el.IBLOCK_SECTION_ID IN (18449, 18466)

Note that it adds indentation like in your preferences > SQL > Tab width / Tabs to spaces.

I'm marking this as completed for now, as there is nothing I can configure in the formatter, but I suppose it's much better than the old internal one from myself.

[decadence]

@ansgarbecker Thanks!

[ansgarbecker]

Thanks to Andi, we have a third formatter option for www.sqlformat.org now: