SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[11.2.246 Pro] - Q: Formatting algorithm

Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[11.2.246 Pro] - Q: Formatting algorithm
Author Message

Joined: 11 Mar 2010
Posts: 1741

Post [11.2.246 Pro] - Q: Formatting algorithm Reply with quote
Could you elaborate a bit on the algorithm SA uses to format code, please? For example, in what order are the rules processed? Is the order even relevant? How is it decided if a rule is skipped? Does it even skip rules?

Page 99, Overview of Chapter 5, Code Formatter and Beautifier states that having two rules, one for IF containing BEGIN and another one just for BEGIN will use the rule for BEGIN if there's no IF in the code but will use the IF rule for formatting the BEGIN...END part of that IF statement even if there's a separate rule for BEGIN parts. How will it decide which rule is the BEGIN...END part going to be formatted by? Will it apply each rule to the complete code mindlessly and in the end, the last one applied will win and give the code its final format?

I recall having a discussion about this (perhaps years ago?) but cannot find it. I remember something about matches found on the first (couple of?) pattern keyword(s) will decide the application of the rule in question, thus the matching the first (few?) pattern keyword(s) is mandatory with all the other pattern keywords being optional, but that brings up the question of having multiple rules starting with the same pattern keyword (eg. CREATE PROCEDURE/VIEW/TABLE/etc.). Also, my latest experiments show it does not work this way, so my memory is most likely failing me on this one.

For example, I have the following code:

CREATE TABLE `ut_schema_history`(`installed_rank` INT(11) NOT NULL ,`version` VARCHAR(50) default NULL ,`description` VARCHAR(200) NOT NULL ,`type` VARCHAR(20) NOT NULL ,`script` VARCHAR(1000) NOT NULL ,`checksum` INT(11) default NULL ,`installed_by` VARCHAR(100) NOT NULL ,`installed_on` timestamp NOT NULL default CURRENT_TIMESTAMP ,`execution_time` INT(11) NOT NULL ,`success` TINYINT(1) NOT NULL ,PRIMARY KEY(`installed_rank`) ,KEY `ut_schema_history_s_idx` (`success`) ) ENGINE = INNODB default charset = utf8

and the following rule:


Now that rule formats the above code to

CREATE TABLE `ut_schema_history`
    `installed_rank`      INT(11) NOT NULL
    ,`version`            VARCHAR(50) DEFAULT NULL
    ,`description`        VARCHAR(200) NOT NULL
    ,`type`               VARCHAR(20) NOT NULL
    ,`script`             VARCHAR(1000) NOT NULL
    ,`checksum`           INT(11) DEFAULT NULL
    ,`installed_by`       VARCHAR(100) NOT NULL
    ,`execution_time`     INT(11) NOT NULL
    ,`success`            TINYINT(1) NOT NULL
    ,PRIMARY KEY(`installed_rank`)
    ,KEY                  `ut_schema_history_s_idx` (`success`)

and that's sort of the expected result.

But add the part IF NOT EXISTS to the CREATE TABLE statement and that neat and tidy result goes out of the window. The code is now formatted to this:

CREATE TABLE IF  NOT EXISTS `ut_schema_history`
                    `installed_rank` INT(11) NOT NULL,
                    `version` VARCHAR(50) DEFAULT NULL,
                    `description` VARCHAR(200) NOT NULL,
                    `type` VARCHAR(20) NOT NULL,
                    `script` VARCHAR(1000) NOT NULL,
                    `checksum` INT(11) DEFAULT NULL,
                    `installed_by` VARCHAR(100) NOT NULL,
                    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    `execution_time` INT(11) NOT NULL,
                    `success` TINYINT(1) NOT NULL,
                    PRIMARY KEY(`installed_rank`),
                    KEY `ut_schema_history_s_idx` (`success`)
                 )ENGINE = INNODB DEFAULT CHARSET = utf8;

Considering that I have Code Formatting / Commas set to Stacked (trailing) and only the CREATE TABLE is modified to use leading commas (to be able to detect these kind of anomalies) it is clear that the CREATE TABLE rule is no longer applied to this code. At least not to the entirety of it, because if I break CREATE and TABLE into separate lines, they will back on the same line after formatting. There's also a surplus space between the IF and NOT keywords. As for the code part between parentheses, I've got no idea which rule is used there, but it's definitely not the one for (...) as that would have it formatted to a single line with my current settings. Its indenting is also a bit strange. I know, I could create a separate rule for CREATE TABLE IF NOT EXISTS statements as well, and so far my experiments prove the wouldn't even interfere with each other, but then TEMPORARY gets introduced, meaning that I'd have to create quite a few separate rules taking into account any combinations of the presence (or lack of presence) of OR REPLACE, TEMPORARY, IF NOT EXISTS. And that's only for the simple CREATE TABLE type statements, I haven't even checked CREATE for PROCEDURE, VIEW, FUNCTION, TRIGGER, and so on.

The user guide goes into detail what each setting will do but it says almost nothing about how it will be done. I've got formats defined for a couple of patterns but I simply cannot achieve my goals. I guess it either does not work as I think it should or it actually does not work as it should. I might have conflicting rules as well, so knowing how it works could help resolve those conflicts (provided conflicts can even happen, of course).
Thu Jul 23, 2020 5:10 am View user's profile Send private message
Site Admin

Joined: 26 Nov 2006
Posts: 7140

Post Reply with quote
Hi. I'm trying to get a meaningful explanation for how this stuff is working internally, which I can share with you. There seem to be a couple of gray areas. The developer who has worked on that feature is currently on vacation, returning back in a few days. I'm waiting for the input.
Tue Jul 28, 2020 10:54 am View user's profile Send private message

Joined: 11 Mar 2010
Posts: 1741

Post Reply with quote
That would be great, thanks a lot!
Sat Aug 01, 2020 8:28 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.