[SA Pro 11.1.107] - BUG: Stored procedure code formatting

[SA Pro 11.1.107] - BUG: Stored procedure code formatting

I've got the following stored procedure code:

	Code:
	DROP PROCEDURE IF EXISTS `usp_get_group_of_sales_packages` ; DELIMITER $$$ CREATE PROCEDURE `usp_get_group_of_sales_packages` ( a_media_type_name varchar(100) -- 'PAPER_TICKET', 'SMART_CARD' ,a_type_of_travel_document_private_code varchar(255) -- ttdpm_.. ,a_g_tid int ) f:BEGIN SELECT 1 AS a; END ;$$$ DELIMITER ;

and the formatting rule for CREATE PROCEDURE:

	Code:
	CREATE ... PROCEDURE ... ( ..., ... ) ... BEGIN <stmtList> END;

This rule ruins the resulting formatted code as follows:

	Code:
	DROP PROCEDURE IF EXISTS `usp_get_group_of_sales_packages`; DELIMITER $$$ CREATE PROCEDURE `usp_get_group_of_sales_packages` ( a_media_type_name VARCHAR(100) -- 'PAPER_TICKET', 'SMART_CARD', a_type_of_travel_document_private_code VARCHAR(255) -- ttdpm_.., a_g_tid INT ) f: BEGIN SELECT 1 AS a; END;$$$ DELIMITER ;

That the f: label was moved one line up is a minor inconvenience that can be fixed by adding it to the formatting rule. The problem is that the arguments got commented out.

Thank you. I quickly tested with 11.1.110 and there results are better, but it looks like there is still some issue

Formatting code template

	Code:
	CREATE PROCEDURE ... ( ..., ... ) BEGIN <stmtList> END

and the result after formatting

	Code:
	DROP PROCEDURE IF EXISTS `usp_get_group_of_sales_packages`; DELIMITER $$$ CREATE PROCEDURE `usp_get_group_of_sales_packages` ( a_media_type_name VARCHAR(100) -- 'PAPER_TICKET', 'SMART_CARD' , a_type_of_travel_document_private_code VARCHAR(255) -- ttdpm_.. , a_g_tid INT )f: BEGIN SELECT 1 AS a; END;$$$ DELIMITER;

The comma shifting between parameters is caused by trailing comments. This is actually a very tricky case, although it may appear simple at first. The formatter cannot add comma at the end of the line, and the pattern rules aren't flexible enough to tell it where to insert commands in a middle of the text line. The pattern would need to reference comments as some special elements.

Yes, but your rule wants to put arguments on separate lines, mine wants to move them on the same line. The formatter should not add the comma to the end of the line, it should add it to the end of the non-commented part of the line (before --). Also, having that lone comma in a separate line (or in front of the argument) is merely ugly, but moving subsequent arguments behind a comment mark of the previous argument is very bad.

No other rules can restore the original version, they would not undo this by moving argument out from behind comments (which would be equally wrong anyway). If we hadn't had those few hundred stored procedures sitting safely in a repository before batch formatting them with that rule, this could result in a nasty data loss.

Are there any ways to handle/reference comments as special elements?

	Quote:
	should add it to the end of the non-commented part of the line (before --)

You're totally correct. The reason why I said this is more like and edge case for the formatter, because the formatting is based on code patterns, it doesn't understand comments as a syntax elements in the pattern, there is no such element supported by the formatting engine. After seeing the unexpected results with commas moved to the next like I assumed there is a hack somewhere outside of the pattern based formatting that makes it check for presence of single line comments, and kind of post factum (after formatting) move commas to the next line in order to avoid breaking the code entirely if commas get overshadowed by comments.

Do I understand correctly? You're saying that if the formatting engine would recognize the comment as a syntax element, it would work as I described?

That's correct. The issue is that a line comment isn't something it knows or understands well. Currently the formatting patterns are based on keyword based anchors and "any other text" represented by ... placeholders between anchors. Commas and white spaces are recognized too, and used in the formatting. In that sense, comments are treated like "any other text."

Thanks for clarifying it. I guess I'll just undo the changes made to the rule and make it put the procedure arguments on separate lines again.