How to save any Comments for the Deparsers

[manticore-projects]

With my limited knowledge, I understand that the Parser silently throws away any comments and why these comments do not matter to the Parser. However, when eying on the Deparsers or Formatters, then such comments DO matter.

So what would be the most reliable and robust way to save the comments and to inject them into the Formatters again? I see two options:

a) amend the Model/Nodes in order to have a comment before or after each node (similar as yo have done with the Oracle Hints), although that would be a nasty task

b) look for any comments and its position relative to an ASCII non-whitespace character before the Parser engages and save these comments and its positions. Then, after parsing and deparsing, re-inject the commends at the same position relative to the ASCII non-whitespace characters. This works only when the deparser/formatter modifies the white-spaces but won't ever add/remove non-whitespace characters. Was that a reasonable assumption?

Do you have any better idea in mind? Which way would yo like to suggest please?

[manticore-projects]

I have prototyped the idea and it seems to work in general:

1. Raw input SQL

-- BOTH CLAUSES PRESENT
MERGE /*+parallel*/ INTO test1 /*the target table*/ a
USING all_objects /*the source table*/ 
ON ( /*joins in()!*/ a.object_id = b.object_id ) 
-- INSERT CLAUSE
WHEN NOT MATCHED THEN 
INSERT (object_id, status)
VALUES (b.object_id, b.status) 
/* UPDATE CLAUSE WITH A WHERE CONDITION */
WHEN MATCHED THEN 
UPDATE SET  a.status=b.status
WHERE   b.status != 'VALID'
;

2. JSQLParser (parsed, deparsed and formatted) would remove the comments

MERGE INTO test1 a 
    USING all_objects
        ON ( a.object_id = b.object_id ) 
WHEN NOT MATCHED THEN 
    INSERT ( object_id
                , status ) 
    VALUES ( b.object_id
                , b.status ) 
WHEN MATCHED THEN 
    UPDATE SET  a.status = b.status
    WHERE   b.status != 'VALID'
;

3. So we have to bring it back based on each comments relative Position

-- BOTH CLAUSES PRESENT
MERGE /*+parallel*/ INTO test1 /*the target table*/ a 
    USING all_objects /*the source table*/
        ON ( /*joins in()!*/ a.object_id = b.object_id )
-- INSERT CLAUSE 
WHEN NOT MATCHED THEN 
    INSERT ( object_id
                , status ) 
    VALUES ( b.object_id
                , b.status )
/* UPDATE CLAUSE WITH A WHERE CONDITION */ 
WHEN MATCHED THEN 
    UPDATE SET  a.status = b.status
    WHERE   b.status != 'VALID'
;

What do you think?

[manticore-projects]

Although we need to consider some nasty corner cases, e. g. -- COMMENTS at the end of the line:

-- BOTH CLAUSES PRESENT
MERGE /*+parallel*/ INTO test1 /*the target table*/ a
USING all_objects /*the source table*/ 
ON ( /*joins in()!*/ a.object_id = b.object_id ) 
-- INSERT CLAUSE
WHEN NOT MATCHED THEN 
INSERT (object_id -- ID Column
, status --Status Column
)
VALUES (b.object_id, b.status) 
/* UPDATE CLAUSE WITH A WHERE CONDITION */
WHEN MATCHED THEN 
UPDATE SET  a.status=b.status
WHERE   b.status != 'VALID'
;

Right now, I would transform that into /* INLINE */ comments:

-- BOTH CLAUSES PRESENT
MERGE /*+parallel*/ INTO test1 /*the target table*/ a 
    USING all_objects /*the source table*/
        ON ( /*joins in()!*/ a.object_id = b.object_id )
-- INSERT CLAUSE 
WHEN NOT MATCHED THEN 
    INSERT ( object_id /*ID Column*/
                , status /*Status Column*/ ) 
    VALUES ( b.object_id
                , b.status )
/* UPDATE CLAUSE WITH A WHERE CONDITION */ 
WHEN MATCHED THEN 
    UPDATE SET  a.status = b.status
    WHERE   b.status != 'VALID'
;

Any objections?
Which other unusual comments would exist in the wild? Could I get some mean test cases for stress testing please?

[manticore-projects]

SQL comment's fun makes me feel dirty:

-- BOTH CLAUSES PRESENT 'with a string' AND "a field"
MERGE /*+parallel*/ INTO test1 /*the target table*/ a 
    USING all_objects /*the source table*/
        ON ( /*joins in()!*/ a.object_id = b.object_id )
-- INSERT CLAUSE 
WHEN NOT MATCHED THEN 
    INSERT ( object_id /*ID Column*/
                , status /*Status Column*/ ) 
    VALUES ( b.object_id
                , b.status )
/* UPDATE CLAUSE
WITH A WHERE CONDITION */ 
WHEN MATCHED THEN 
    UPDATE SET  a.status = '/*this is no comment!*/ and -- this ain''t either'
    WHERE   b."--status" != 'VALID'
;

[wumpz]

Maybe this is a hint: #1113.

Some of JSqlParsers objects are connected to ASTNodes.

Table Table() #Table :
{
    //String serverName = null, databaseName = null, schemaName = null, tableName = null;
    List<String> data = new ArrayList<String>();
}
{
    data = RelObjectNameList()

    {
        Table table = new Table(data);
		linkAST(table,jjtThis);
        return table;
    }
}

Look at this linkAST method. It was not done for all productions, since this is performance and memory relevant. But from this connection you get the starting token and from this the preceeding special_token, which is the comment.

Look as well into SelectASTTest.java into this AST parsing stuff.

[manticore-projects]

No worries, I built an almost perfect solution (for JSQLFormatter), the examples above are "real life" outcomes of parsed statements with preserved comments. I went the route of binding the comments to its "relative" position in the SQL, assuming that deparsing will never change the content of the SQL.

Look at this, isn't it beautiful (attention to the aligned right side comments please):

-- UPDATE CALENDAR
UPDATE cfe.calendar
SET year_offset = ?            /* year offset */
    , settlement_shift = ?     /* settlement shift */
    , friday_is_holiday = ?    /* friday is a holiday */
    , saturday_is_holiday = ?  /* saturday is a holiday */
    , sunday_is_holiday = ?    /* sunday is a holiday */
WHERE id_calendar = ?
;

-- BOTH CLAUSES PRESENT 'with a string' AND "a field"
MERGE /*+ parallel */ INTO test1 /*the target table*/ a
    USING all_objects      /*the source table*/
        ON ( /*joins in()!*/ a.object_id = b.object_id )
-- INSERT CLAUSE 
WHEN /*comments between keywords!*/ NOT MATCHED THEN
    INSERT ( object_id     /*ID Column*/
                , status   /*Status Column*/ )
    VALUES ( b.object_id
                , b.status )
/* UPDATE CLAUSE
WITH A WHERE CONDITION */ 
WHEN MATCHED THEN          /* Lets rock */
    UPDATE SET  a.status = '/*this is no comment!*/ and -- this ain''t either'
    WHERE   b."--status" != 'VALID'
;