Deconstruct, Modify, Rebuild an SQL statement(Java version, part 2)

Deconstruct, Modify, Rebuild an SQL statement(Java version, part 2)
part 2, use low level APIs

General sql parser provides some low level APIs to allow user re-construct any elements of a parsed SQL statement. Here are 2 samples illustrate how to rebuild SQL statement in different cases:

Case 1:

We need to add a new reference column “CEL_NEWID” after “CEL_COD”
in this sql:

ALTER TABLE P_CAP 
ADD CONSTRAINT FK_P_CAP_R_PH_111_P_CEL 
FOREIGN KEY (CAP_CEL) REFERENCES P_CEL (CEL_COD);

which will turn sql into this one:

 
ALTER TABLE P_CAP 
ADD CONSTRAINT FK_P_CAP_R_PH_111_P_CEL 
FOREIGN KEY (CAP_CEL) REFERENCES P_CEL (CEL_COD,CEL_NEWID);

Steps how to achieve this:

1. Find the node or token that new sql element will be appended.

2. set the text of this node to include new value

Here is the Java code:

        TGSqlParser lcparser = new TGSqlParser(EDbVendor.dbvoracle);
        lcparser.sqltext = "ALTER TABLE P_CAP \n" +
                "ADD CONSTRAINT FK_P_CAP_R_PH_111_P_CEL \n" +
                "FOREIGN KEY (CAP_CEL) REFERENCES P_CEL (CEL_COD);";

        TAlterTableStatement at = (TAlterTableStatement) lcparser.sqlstatements.get(0);
        TAlterTableOption alterTableOption = at.getAlterTableOptionList().getAlterTableOption(0);
        TConstraint constraint =  alterTableOption.getConstraintList().getConstraint(0);
        assertTrue(constraint.getConstraint_type() == EConstraintType.foreign_key);
        TObjectName refColumn = constraint.getReferencedColumnList().getObjectName(0);
        refColumn.setString(refColumn.toString()+",CEL_NEWID");
        System.out.println(at.toString());

Case 2:

Add a new where clause in a select statement.

original SQL:

SELECT * FROM TABLE_X

New SQL:

SELECT * FROM TABLE_X where f > 0

Steps how to achieve this:

1. Find the node or token that new sql element will be appended.

2. create a new node and set text of this node.

3. append all tokens of this new created node after existing node.

4. adjust all end tokens of existing node if necessary.

Here is the Java code:

        TGSqlParser lcparser = new TGSqlParser(EDbVendor.dbvoracle);
        lcparser.sqltext = "SELECT * FROM TABLE_X";
        assertTrue(lcparser.parse() == 0);

        //step 1
        TSelectSqlStatement select = (TSelectSqlStatement)lcparser.sqlstatements.get(0);
        TSourceToken prevToken = select.joins.getEndToken();

        //step 2  
        TDummy dummy  = new TDummy();
        dummy.setGsqlparser(lcparser);
        dummy.setString(" where f > 0");
        
        //step 3 
dummy.addAllMyTokensToTokenList(prevToken.container,prevToken.posinlist + 1 );

        //step 4
        for(int i=0;i <prevToken.getNodesEndWithThisToken().size();i++){
            TParseTreeNode node = prevToken.getNodesEndWithThisToken().getElement(i);
            if (!((node instanceof TJoinList)
                    ||(node instanceof TJoin)
                    ||(node instanceof TJoinItemList)
                    ||(node instanceof TJoinItem)
                    ))
            {
                // change all end token of parse tree node except the table from join clause
                node.setEndToken(dummy.getEndToken());
            }
        }

        System.out.println(select.toString());

Deconstruct, Modify, Rebuild an SQL statement(Java version, part 1)