Source code

001
002package gudusoft.gsqlparser.util;
003
004import gudusoft.gsqlparser.EDbVendor;
005import gudusoft.gsqlparser.EExpressionType;
006import gudusoft.gsqlparser.EJoinType;
007import gudusoft.gsqlparser.ETableSource;
008import gudusoft.gsqlparser.TCustomSqlStatement;
009import gudusoft.gsqlparser.TGSqlParser;
010import gudusoft.gsqlparser.TSourceToken;
011import gudusoft.gsqlparser.TSourceTokenList;
012import gudusoft.gsqlparser.nodes.IExpressionVisitor;
013import gudusoft.gsqlparser.nodes.TCTE;
014import gudusoft.gsqlparser.nodes.TCaseExpression;
015import gudusoft.gsqlparser.nodes.TExpression;
016import gudusoft.gsqlparser.nodes.TExpressionList;
017import gudusoft.gsqlparser.nodes.TFunctionCall;
018import gudusoft.gsqlparser.nodes.TJoin;
019import gudusoft.gsqlparser.nodes.TJoinItem;
020import gudusoft.gsqlparser.nodes.TOrderByItem;
021import gudusoft.gsqlparser.nodes.TParseTreeNode;
022import gudusoft.gsqlparser.nodes.TParseTreeNodeList;
023import gudusoft.gsqlparser.nodes.TResultColumn;
024import gudusoft.gsqlparser.nodes.TTableList;
025import gudusoft.gsqlparser.nodes.TWhenClauseItem;
026import gudusoft.gsqlparser.nodes.TWhenClauseItemList;
027import gudusoft.gsqlparser.stmt.TSelectSqlStatement;
028import gudusoft.gsqlparser.stmt.mssql.TMssqlBlock;
029import gudusoft.gsqlparser.stmt.mssql.TMssqlCreateProcedure;
030import gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateProcedure;
031
032import java.io.File;
033import java.io.FileOutputStream;
034import java.io.IOException;
035import java.io.PrintStream;
036import java.util.ArrayList;
037import java.util.Arrays;
038import java.util.HashMap;
039import java.util.HashSet;
040import java.util.List;
041
042enum ClauseType {
043        where, connectby, startwith, orderby, casewhen, casethen
044}
045
046class JoinCondition
047{
048
049        public String lefttable, righttable, leftcolumn, rightcolumn,
050                        leftTableLocation, rightTableLocation, leftColumnLocation,
051                        rightColumnLocation, joinType, operator;
052        public List<TCustomSqlStatement> sql = new ArrayList<TCustomSqlStatement>( );
053
054        public int hashCode( )
055        {
056                int hashCode = 0;
057                if ( lefttable != null )
058                        hashCode += lefttable.hashCode( );
059                if ( righttable != null )
060                        hashCode += righttable.hashCode( );
061                if ( leftcolumn != null )
062                        hashCode += leftcolumn.hashCode( );
063                if ( rightcolumn != null )
064                        hashCode += rightcolumn.hashCode( );
065
066                for ( TCustomSqlStatement stmt : sql )
067                {
068                        hashCode += stmt.hashCode( );
069                }
070
071                return hashCode;
072        }
073
074        public boolean equals( Object obj )
075        {
076                if ( this == obj )
077                        return true;
078                if ( !( obj instanceof JoinCondition ) )
079                        return false;
080
081                JoinCondition join = (JoinCondition) obj;
082
083                if ( this.leftcolumn != null
084                                && !this.leftcolumn.equals( join.leftcolumn ) )
085                        return false;
086                if ( this.rightcolumn != null
087                                && !this.rightcolumn.equals( join.rightcolumn ) )
088                        return false;
089                if ( this.lefttable != null && !this.lefttable.equals( join.lefttable ) )
090                        return false;
091                if ( this.righttable != null
092                                && !this.righttable.equals( join.righttable ) )
093                        return false;
094
095                if ( join.righttable != null
096                                && !join.righttable.equals( this.righttable ) )
097                        return false;
098                if ( join.lefttable != null && !join.lefttable.equals( this.lefttable ) )
099                        return false;
100                if ( join.rightcolumn != null
101                                && !join.rightcolumn.equals( this.rightcolumn ) )
102                        return false;
103                if ( join.leftcolumn != null
104                                && !join.leftcolumn.equals( this.leftcolumn ) )
105                        return false;
106
107                if ( join.sql.size( ) != this.sql.size( ) )
108                        return false;
109                for ( int i = 0; i < join.sql.size( ); i++ )
110                {
111                        if ( !join.sql.get( i ).equals( this.sql.get( i ) ) )
112                                return false;
113                }
114
115                return true;
116        }
117}
118
119class TColumn
120{
121
122        public List<String> tableNames = new ArrayList<String>( );
123        public String columnName;
124        public String columnPrex;
125        public String columnAlias;
126        public String columnLocation;
127        public String tableLocation;
128
129        public String getFullName( String tableName )
130        {
131                if ( tableName != null )
132                {
133                        return tableName + "." + columnName;
134                }
135                else
136                {
137                        return columnName;
138                }
139        }
140
141        public String getOrigName( )
142        {
143                if ( columnPrex != null )
144                {
145                        return columnPrex + "." + columnName;
146                }
147                else
148                {
149                        return columnName;
150                }
151        }
152
153}
154
155class TTable
156{
157
158        public String tableName;
159        public String prefixName;
160        public String tableAlias;
161        public String tableLocation;
162}
163
164class joinConditonsInExpr implements IExpressionVisitor
165{
166
167        private TExpression expr;
168        private joinRelationAnalyze analysis;
169        private TCustomSqlStatement stmt;
170        private String joinType;
171
172        public joinConditonsInExpr( joinRelationAnalyze analysis, TExpression expr,
173                        TCustomSqlStatement stmt, String joinType )
174        {
175                this.stmt = stmt;
176                this.analysis = analysis;
177                this.expr = expr;
178                this.joinType = joinType;
179        }
180
181        boolean is_compare_condition( EExpressionType t )
182        {
183                return ( ( t == EExpressionType.simple_comparison_t )
184                                || ( t == EExpressionType.group_comparison_t ) || ( t == EExpressionType.in_t ) );
185        }
186
187        private String getExpressionTable( TExpression expr )
188        {
189                if ( expr.getObjectOperand( ) != null )
190                        return expr.getObjectOperand( ).getObjectString( );
191                else if ( expr.getLeftOperand( ) != null
192                                && expr.getLeftOperand( ).getObjectOperand( ) != null )
193                        return expr.getLeftOperand( ).getObjectOperand( ).getObjectString( );
194                else if ( expr.getRightOperand( ) != null
195                                && expr.getRightOperand( ).getObjectOperand( ) != null )
196                        return expr.getRightOperand( )
197                                        .getObjectOperand( )
198                                        .getObjectString( );
199                else
200                        return null;
201        }
202
203        private String getExpressionTableLocation( TExpression expr )
204        {
205                TSourceToken tableToken = null;
206                if ( expr.getObjectOperand( ) != null )
207                {
208                        tableToken = expr.getObjectOperand( ).getTableToken( );
209                }
210                else if ( expr.getLeftOperand( ) != null
211                                && expr.getLeftOperand( ).getObjectOperand( ) != null )
212                {
213                        tableToken = expr.getLeftOperand( )
214                                        .getObjectOperand( )
215                                        .getTableToken( );
216                }
217                else if ( expr.getRightOperand( ) != null
218                                && expr.getRightOperand( ).getObjectOperand( ) != null )
219                {
220                        tableToken = expr.getRightOperand( )
221                                        .getObjectOperand( )
222                                        .getTableToken( );
223                }
224                if ( tableToken != null )
225                {
226                        return "(" + tableToken.lineNo + "," + tableToken.columnNo + ")";
227                }
228                return null;
229        }
230
231        private void analyzeMssqlJoinCondition( TExpression expr )
232        {
233                String joinType = null;
234                if ( expr.getExpressionType( ) == EExpressionType.left_join_t )
235                {
236                        joinType = EJoinType.left.name( );
237                }
238                if ( expr.getExpressionType( ) == EExpressionType.right_join_t )
239                {
240                        joinType = EJoinType.right.name( );
241                }
242
243                dealCompareCondition( expr.getLeftOperand( ),
244                                expr.getRightOperand( ),
245                                expr.getOperatorToken( ).toString( ),
246                                joinType );
247
248        }
249
250        public boolean exprVisit( TParseTreeNode pnode, boolean flag )
251        {
252                TExpression lcexpr = (TExpression) pnode;
253
254                TExpression slexpr, srexpr, lc_expr = lcexpr;
255
256                if ( lc_expr.getGsqlparser( ).getDbVendor( ) == EDbVendor.dbvmssql )
257                {
258                        if ( lc_expr.getExpressionType( ) == EExpressionType.left_join_t
259                                        || lc_expr.getExpressionType( ) == EExpressionType.right_join_t )
260                        {
261                                analyzeMssqlJoinCondition( lc_expr );
262                        }
263                }
264
265                if ( is_compare_condition( lc_expr.getExpressionType( ) ) )
266                {
267                        slexpr = lc_expr.getLeftOperand( );
268                        srexpr = lc_expr.getRightOperand( );
269
270                        if ( srexpr.getFunctionCall( ) != null
271                                        && srexpr.getFunctionCall( )
272                                                        .getFunctionName( )
273                                                        .toString( )
274                                                        .equalsIgnoreCase( "ISNULL" ) )
275                        {
276                                TExpressionList list = srexpr.getFunctionCall( ).getArgs( );
277                                for ( int i = 0; i < list.size( ); i++ )
278                                {
279                                        dealCompareCondition( slexpr,
280                                                        list.getExpression( i ),
281                                                        lc_expr.getOperatorToken( ).toString( ),
282                                                        null );
283                                }
284                        }
285                        else
286                                dealCompareCondition( slexpr,
287                                                srexpr,
288                                                lc_expr.getOperatorToken( ).toString( ),
289                                                null );
290                }
291
292                if ( lcexpr.getExpressionType( ) == EExpressionType.function_t )
293                {
294                        TFunctionCall func = (TFunctionCall) lcexpr.getFunctionCall( );
295                        if ( func.getArgs( ) != null )
296                        {
297                                for ( int k = 0; k < func.getArgs( ).size( ); k++ )
298                                {
299                                        TExpression expr = func.getArgs( ).getExpression( k );
300                                        expr.inOrderTraverse( this );
301                                }
302                        }
303                        if ( func.getAnalyticFunction( ) != null )
304                        {
305                                TParseTreeNodeList list = func.getAnalyticFunction( )
306                                                .getPartitionBy_ExprList( );
307                                searchJoinInList( list, stmt );
308
309                                if ( func.getAnalyticFunction( ).getOrderBy( ) != null )
310                                {
311                                        list = func.getAnalyticFunction( ).getOrderBy( ).getItems( );
312                                        searchJoinInList( list, stmt );
313                                }
314                        }
315
316                }
317                else if ( lcexpr.getExpressionType( ) == EExpressionType.subquery_t )
318                {
319                        if ( lcexpr.getSubQuery( ) instanceof TSelectSqlStatement )
320                        {
321                                TSelectSqlStatement query = lcexpr.getSubQuery( );
322                                analysis.searchSubQuery( query );
323                        }
324                }
325                else if ( lcexpr.getExpressionType( ) == EExpressionType.case_t )
326                {
327                        TCaseExpression expr = lcexpr.getCaseExpression( );
328                        TExpression conditionExpr = expr.getInput_expr( );
329                        if ( conditionExpr != null )
330                        {
331                                conditionExpr.inOrderTraverse( this );
332                        }
333                        TExpression defaultExpr = expr.getElse_expr( );
334                        if ( defaultExpr != null )
335                        {
336                                defaultExpr.inOrderTraverse( this );
337                        }
338                        TWhenClauseItemList list = expr.getWhenClauseItemList( );
339                        searchJoinInList( list, stmt );
340                }
341                else if ( lcexpr.getExpressionType( ) == EExpressionType.exists_t )
342                {
343                        if ( lcexpr.getRightOperand( ) != null
344                                        && lcexpr.getRightOperand( ).getSubQuery( ) != null )
345                        {
346                                TSelectSqlStatement query = lcexpr.getRightOperand( )
347                                                .getSubQuery( );
348                                analysis.searchSubQuery( query );
349                        }
350                }
351                return true;
352        }
353
354        private void dealCompareCondition( TExpression slexpr, TExpression srexpr,
355                        String operator, String type )
356        {
357                if ( ( ( slexpr.getExpressionType( ) == EExpressionType.simple_object_name_t )
358                                || ( slexpr.isOracleOuterJoin( ) ) || ( srexpr.isOracleOuterJoin( ) && slexpr.getExpressionType( ) == EExpressionType.simple_constant_t ) )
359                                && ( ( srexpr.getExpressionType( ) == EExpressionType.simple_object_name_t )
360                                                || ( srexpr.isOracleOuterJoin( ) )
361                                                || ( slexpr.isOracleOuterJoin( ) && srexpr.getExpressionType( ) == EExpressionType.simple_constant_t ) || ( slexpr.isOracleOuterJoin( ) && srexpr.getExpressionType( ) == EExpressionType.case_t ) )
362                                || ( slexpr.getExpressionType( ) == EExpressionType.simple_object_name_t && srexpr.getExpressionType( ) == EExpressionType.subquery_t )
363                                || ( slexpr.getExpressionType( ) == EExpressionType.subquery_t && srexpr.getExpressionType( ) == EExpressionType.simple_object_name_t ) )
364                {
365                        TExpression lattr = null, rattr = null;
366                        JoinCondition jr = new JoinCondition( );
367                        jr.sql.add( stmt );
368                        jr.operator = operator;
369
370                        if ( type != null )
371                        {
372                                jr.joinType = type;
373                        }
374                        else if ( joinType != null )
375                        {
376                                jr.joinType = joinType;
377                        }
378
379                        if ( slexpr.isOracleOuterJoin( ) )
380                        {
381                                lattr = slexpr;
382                                jr.lefttable = lattr != null ? getExpressionTable( lattr )
383                                                : null;
384                                jr.leftTableLocation = lattr != null ? getExpressionTableLocation( lattr )
385                                                : null;
386
387                                TSourceToken columnToken = getBeforeToken( lattr.getEndToken( ) );
388                                jr.leftcolumn = lattr != null ? columnToken.toString( ) : null;
389                                if ( columnToken != null )
390                                {
391                                        jr.leftColumnLocation = "("
392                                                        + columnToken.lineNo
393                                                        + ","
394                                                        + columnToken.columnNo
395                                                        + ")";
396                                }
397                                jr.joinType = EJoinType.leftouter.name( );
398                        }
399                        else if ( slexpr.getExpressionType( ) == EExpressionType.simple_object_name_t )
400                        {
401                                lattr = slexpr;
402                                jr.lefttable = lattr != null ? getExpressionTable( lattr )
403                                                : null;
404                                jr.leftTableLocation = lattr != null ? getExpressionTableLocation( lattr )
405                                                : null;
406
407                                jr.leftcolumn = lattr != null ? lattr.getEndToken( ).toString( )
408                                                : null;
409
410                                TSourceToken columnToken = lattr.getEndToken( );
411                                if ( columnToken != null )
412                                {
413                                        jr.leftColumnLocation = "("
414                                                        + columnToken.lineNo
415                                                        + ","
416                                                        + columnToken.columnNo
417                                                        + ")";
418                                }
419                        }
420
421                        if ( srexpr.isOracleOuterJoin( ) )
422                        {
423                                rattr = srexpr;
424                                jr.righttable = rattr != null ? getExpressionTable( rattr )
425                                                : null;
426                                jr.rightTableLocation = rattr != null ? getExpressionTableLocation( rattr )
427                                                : null;
428
429                                TSourceToken columnToken = getBeforeToken( rattr.getEndToken( ) );
430                                jr.rightcolumn = rattr != null ? columnToken.toString( ) : null;
431                                if ( columnToken != null )
432                                {
433                                        jr.rightColumnLocation = "("
434                                                        + columnToken.lineNo
435                                                        + ","
436                                                        + columnToken.columnNo
437                                                        + ")";
438                                }
439
440                                if ( slexpr.getExpressionType( ) != EExpressionType.subquery_t )
441                                {
442                                        analysis.joinRelationSet.add( jr );
443                                }
444                                jr.joinType = EJoinType.rightouter.name( );
445                        }
446                        else if ( srexpr.getExpressionType( ) == EExpressionType.simple_object_name_t )
447                        {
448                                rattr = srexpr;
449                                jr.righttable = rattr != null ? getExpressionTable( rattr )
450                                                : null;
451                                jr.rightTableLocation = rattr != null ? getExpressionTableLocation( rattr )
452                                                : null;
453                                jr.rightcolumn = rattr != null ? rattr.getEndToken( )
454                                                .toString( ) : null;
455
456                                TSourceToken columnToken = rattr.getEndToken( );
457                                if ( columnToken != null )
458                                {
459                                        jr.rightColumnLocation = "("
460                                                        + columnToken.lineNo
461                                                        + ","
462                                                        + columnToken.columnNo
463                                                        + ")";
464                                }
465
466                                if ( slexpr.getExpressionType( ) != EExpressionType.subquery_t )
467                                {
468                                        analysis.joinRelationSet.add( jr );
469                                }
470                        }
471                        else if ( srexpr.getExpressionType( ) == EExpressionType.case_t )
472                        {
473                                TCaseExpression expr = srexpr.getCaseExpression( );
474
475                                TWhenClauseItemList list = expr.getWhenClauseItemList( );
476                                for ( int i = 0; i < list.size( ); i++ )
477                                {
478                                        TExpression thenexpr = ( (TWhenClauseItem) list.getWhenClauseItem( i ) ).getReturn_expr( );
479                                        if ( thenexpr.getExpressionType( ) == EExpressionType.simple_object_name_t )
480                                        {
481                                                rattr = thenexpr;
482                                        }
483                                        JoinCondition condtion = new JoinCondition( );
484                                        condtion.leftcolumn = jr.leftcolumn;
485                                        condtion.lefttable = jr.lefttable;
486                                        condtion.leftTableLocation = jr.leftTableLocation;
487                                        condtion.leftColumnLocation = jr.leftColumnLocation;
488                                        condtion.sql = jr.sql;
489                                        condtion.righttable = rattr != null ? getExpressionTable( rattr )
490                                                        : null;
491                                        condtion.rightTableLocation = rattr != null ? getExpressionTableLocation( rattr )
492                                                        : null;
493
494                                        if ( rattr != null )
495                                        {
496                                                if ( rattr.isOracleOuterJoin( ) )
497                                                {
498                                                        condtion.joinType = EJoinType.rightouter.name( );
499                                                        TSourceToken columnToken = getBeforeToken( rattr.getEndToken( ) );
500                                                        condtion.rightcolumn = columnToken.toString( );
501                                                        if ( columnToken != null )
502                                                        {
503                                                                jr.rightColumnLocation = "("
504                                                                                + columnToken.lineNo
505                                                                                + ","
506                                                                                + columnToken.columnNo
507                                                                                + ")";
508                                                        }
509                                                }
510                                                else
511                                                {
512                                                        condtion.rightcolumn = rattr.getEndToken( )
513                                                                        .toString( );
514                                                        TSourceToken columnToken = rattr.getEndToken( );
515                                                        if ( columnToken != null )
516                                                        {
517                                                                jr.rightColumnLocation = "("
518                                                                                + columnToken.lineNo
519                                                                                + ","
520                                                                                + columnToken.columnNo
521                                                                                + ")";
522                                                        }
523                                                }
524                                        }
525                                        else
526                                                condtion.rightcolumn = null;
527
528                                        analysis.joinRelationSet.add( condtion );
529                                }
530                                if ( expr.getElse_expr( ) != null )
531                                {
532                                        TExpression elseexpr = expr.getElse_expr( );
533                                        if ( elseexpr.getExpressionType( ) == EExpressionType.simple_object_name_t )
534                                        {
535                                                rattr = elseexpr;
536                                        }
537
538                                        JoinCondition condtion = new JoinCondition( );
539                                        condtion.leftcolumn = jr.leftcolumn;
540                                        condtion.lefttable = jr.lefttable;
541                                        condtion.leftColumnLocation = jr.leftColumnLocation;
542                                        condtion.leftTableLocation = jr.leftTableLocation;
543                                        condtion.sql = jr.sql;
544                                        condtion.righttable = rattr != null ? getExpressionTable( rattr )
545                                                        : null;
546                                        condtion.rightTableLocation = rattr != null ? getExpressionTableLocation( rattr )
547                                                        : null;
548                                        if ( rattr != null )
549                                        {
550                                                if ( rattr.isOracleOuterJoin( ) )
551                                                {
552                                                        TSourceToken columnToken = getBeforeToken( rattr.getEndToken( ) );
553                                                        condtion.rightcolumn = columnToken.toString( );
554                                                        if ( columnToken != null )
555                                                        {
556                                                                jr.rightColumnLocation = "("
557                                                                                + columnToken.lineNo
558                                                                                + ","
559                                                                                + columnToken.columnNo
560                                                                                + ")";
561                                                        }
562                                                }
563                                                else
564                                                {
565                                                        condtion.rightcolumn = rattr.getEndToken( )
566                                                                        .toString( );
567                                                        TSourceToken columnToken = rattr.getEndToken( );
568                                                        if ( columnToken != null )
569                                                        {
570                                                                jr.rightColumnLocation = "("
571                                                                                + columnToken.lineNo
572                                                                                + ","
573                                                                                + columnToken.columnNo
574                                                                                + ")";
575                                                        }
576                                                }
577                                        }
578                                        else
579                                                condtion.rightcolumn = null;
580                                        analysis.joinRelationSet.add( condtion );
581                                }
582                        }
583
584                        if ( srexpr.getExpressionType( ) == EExpressionType.subquery_t )
585                        {
586                                TSelectSqlStatement subquery = (TSelectSqlStatement) srexpr.getSubQuery( );
587                                addSubqueryJoin( jr, subquery, false );
588                        }
589
590                        if ( slexpr.getExpressionType( ) == EExpressionType.subquery_t )
591                        {
592                                TSelectSqlStatement subquery = (TSelectSqlStatement) slexpr.getSubQuery( );
593                                addSubqueryJoin( jr, subquery, true );
594                        }
595                }
596        }
597
598        private TSourceToken getBeforeToken( TSourceToken token )
599        {
600                TSourceTokenList tokens = token.container;
601                int index = token.posinlist;
602
603                for ( int i = index - 1; i >= 0; i-- )
604                {
605                        TSourceToken currentToken = tokens.get( i );
606                        if ( currentToken.toString( ).trim( ).length( ) == 0 )
607                        {
608                                continue;
609                        }
610                        else
611                        {
612                                return currentToken;
613                        }
614                }
615                return token;
616        }
617
618        private void addSubqueryJoin( JoinCondition jr,
619                        TSelectSqlStatement subquery, Boolean isLeft )
620        {
621                if ( subquery.isCombinedQuery( ) )
622                {
623                        addSubqueryJoin( jr, subquery.getLeftStmt( ), isLeft );
624                        addSubqueryJoin( jr, subquery.getRightStmt( ), isLeft );
625                }
626                else
627                {
628                        for ( int i = 0; i < subquery.getResultColumnList( ).size( ); i++ )
629                        {
630                                TResultColumn field = subquery.getResultColumnList( )
631                                                .getResultColumn( i );
632                                TColumn column = analysis.attrToColumn( field, subquery );
633                                for ( String tableName : column.tableNames )
634                                {
635                                        JoinCondition condtion = new JoinCondition( );
636                                        if ( isLeft )
637                                        {
638                                                condtion.rightcolumn = jr.rightcolumn;
639                                                condtion.righttable = jr.righttable;
640                                                condtion.rightColumnLocation = jr.rightColumnLocation;
641                                                condtion.rightTableLocation = jr.rightTableLocation;
642                                                condtion.sql.add( stmt );
643                                                condtion.sql.add( subquery );
644                                                condtion.lefttable = tableName;
645                                                condtion.leftcolumn = column.columnName;
646                                                condtion.leftColumnLocation = column.columnLocation;
647                                                condtion.leftTableLocation = column.tableLocation;
648                                        }
649                                        else
650                                        {
651                                                condtion.leftcolumn = jr.leftcolumn;
652                                                condtion.leftColumnLocation = jr.leftColumnLocation;
653                                                condtion.lefttable = jr.lefttable;
654                                                condtion.leftTableLocation = jr.leftTableLocation;
655                                                condtion.sql.add( stmt );
656                                                condtion.sql.add( subquery );
657                                                condtion.righttable = tableName;
658                                                condtion.rightcolumn = column.columnName;
659                                                condtion.rightColumnLocation = column.columnLocation;
660                                                condtion.rightTableLocation = column.tableLocation;
661                                        }
662                                        analysis.joinRelationSet.add( condtion );
663                                }
664                        }
665                }
666        }
667
668        private void searchJoinInList( TParseTreeNodeList list,
669                        TCustomSqlStatement stmt )
670        {
671                if ( list != null )
672                {
673                        for ( int i = 0; i < list.size( ); i++ )
674                        {
675                                List<TExpression> exprList = new ArrayList<TExpression>( );
676
677                                if ( list.getElement( i ) instanceof TOrderByItem )
678                                {
679                                        exprList.add( (TExpression) ( (TOrderByItem) list.getElement( i ) ).getSortKey( ) );
680                                }
681                                else if ( list.getElement( i ) instanceof TExpression )
682                                {
683                                        exprList.add( (TExpression) list.getElement( i ) );
684                                }
685                                else if ( list.getElement( i ) instanceof TWhenClauseItem )
686                                {
687                                        exprList.add( ( (TWhenClauseItem) list.getElement( i ) ).getComparison_expr( ) );
688                                        exprList.add( ( (TWhenClauseItem) list.getElement( i ) ).getReturn_expr( ) );
689                                }
690
691                                for ( TExpression lcexpr : exprList )
692                                {
693                                        lcexpr.inOrderTraverse( this );
694                                }
695                        }
696                }
697        }
698
699        public void searchExpression( )
700        {
701                this.expr.inOrderTraverse( this );
702        }
703}
704
705public class joinRelationAnalyze
706{
707
708        private static boolean isOutputFile;
709        private StringBuilder buffer = new StringBuilder( );
710        private HashMap cteMap = new HashMap( );
711        private HashMap tableAliasMap = new HashMap( );
712        private List<TCustomSqlStatement> searchInSubQuerys = new ArrayList<TCustomSqlStatement>( );
713        private List<TCustomSqlStatement> searchInTables = new ArrayList<TCustomSqlStatement>( );
714        private List<TCustomSqlStatement> searchInClauses = new ArrayList<TCustomSqlStatement>( );
715        public HashMap queryAliasMap = new HashMap( );
716        public HashSet<JoinCondition> joinRelationSet = new HashSet<JoinCondition>( );
717        private List<JoinCondition> conditions = new ArrayList<JoinCondition>( );
718
719        public String getAnalysisResult( )
720        {
721                return buffer.toString( );
722        }
723
724        public List<JoinCondition> getJoinConditions( )
725        {
726                return conditions;
727        }
728
729        public static void main( String[] args )
730        {
731                if ( args.length == 0 )
732                {
733                        System.out.println( "Usage: joinRelationAnalyze <sql script file path> <output file path> [/t <database type>]" );
734                        System.out.println( "sql script file path: The sql file will be analyzed." );
735                        System.out.println( "output file path: Option, write the analysis result to the specified file." );
736                        System.out.println( "/t: Option, set the database type. Support oracle, mysql, mssql and db2, the default type is oracle" );
737                        // Console.Read();
738                        return;
739                }
740
741                String outputFile = null;
742                FileOutputStream writer = null;
743                if ( args.length > 1 && !args[1].equalsIgnoreCase( "/t" ) )
744                {
745                        outputFile = args[1];
746                        isOutputFile = true;
747                }
748                try
749                {
750                        if ( outputFile != null )
751                        {
752
753                                writer = new FileOutputStream( outputFile );
754                                System.setOut( new PrintStream( writer ) );
755
756                        }
757
758                        EDbVendor vendor = EDbVendor.dbvoracle;
759
760                        List<String> argList = Arrays.asList( args );
761
762                        int index = argList.indexOf( "/t" );
763
764                        if ( index != -1 && args.length > index + 1 )
765                        {
766                                vendor = TGSqlParser.getDBVendorByName(args[index + 1]);
767                        }
768
769                        joinRelationAnalyze analysis = new joinRelationAnalyze( new File( args[0] ),
770                                        vendor );
771                        System.out.print( analysis.getAnalysisResult( ) );
772                        // if (args.length <= 1)
773                        // {
774                        // Console.Read();
775                        // }
776                        // else
777                        {
778                                if ( writer != null )
779                                {
780                                        writer.close( );
781                                }
782                        }
783                }
784                catch ( IOException e )
785                {
786                        e.printStackTrace( );
787                }
788
789        } // main
790
791        public joinRelationAnalyze( String sql, EDbVendor dbVendor )
792        {
793                TGSqlParser sqlparser = new TGSqlParser( dbVendor );
794                sqlparser.sqltext = sql;
795                analyzeSQL( sqlparser, false );
796        }
797
798        public joinRelationAnalyze( File file, EDbVendor dbVendor )
799        {
800                TGSqlParser sqlparser = new TGSqlParser( dbVendor );
801                sqlparser.sqlfilename = file.getAbsolutePath( );
802                analyzeSQL( sqlparser, false );
803        }
804
805        public joinRelationAnalyze( TGSqlParser sqlparser, boolean showLocation )
806        {
807                analyzeSQL( sqlparser, showLocation );
808        }
809
810        private void analyzeSQL( TGSqlParser sqlparser, boolean showLocation )
811        {
812                int ret = sqlparser.parse( );
813
814                if ( ret != 0 )
815                {
816                        buffer.append( sqlparser.getErrormessage( ) );
817                        return;
818                }
819                else
820                {
821                        for ( int j = 0; j < sqlparser.getSqlstatements( ).size( ); j++ )
822                        {
823                                TCustomSqlStatement select = (TCustomSqlStatement) sqlparser.sqlstatements.get( j );
824                                analyzeStmt( select );
825                        }
826                }
827
828                buffer.append( "JoinTable1\tJoinColumn1\tJoinTable2\tJoinColumn2\tJoinType\tJoinOperator\r\n" );
829
830                conditions.clear( );
831
832                for ( JoinCondition join : joinRelationSet )
833                {
834                        String lefttable = join.lefttable;
835                        String righttable = join.righttable;
836                        String leftcolumn = join.leftcolumn;
837                        String rightcolumn = join.rightcolumn;
838                        String leftColumnLocation = join.leftColumnLocation;
839                        String rightColumnLocation = join.rightColumnLocation;
840                        String leftTableLocation = join.leftTableLocation;
841                        String rightTableLocation = join.rightTableLocation;
842                        String joinType = join.joinType;
843                        String operator = join.operator;
844
845                        if ( ( lefttable == null || lefttable.length( ) == 0 )
846                                        && ( righttable == null || righttable.length( ) == 0 ) )
847                                continue;
848
849                        List<String[]> leftJoinNameList = getRealName( lefttable,
850                                        leftcolumn,
851                                        join.sql );
852                        List<String[]> rightJoinNameList = getRealName( righttable,
853                                        rightcolumn,
854                                        join.sql );
855
856                        for ( String[] leftJoinNames : leftJoinNameList )
857                        {
858                                for ( String[] rightJoinNames : rightJoinNameList )
859                                {
860                                        if ( leftJoinNames[0] != null
861                                                        && rightJoinNames[0] != null
862                                                        && leftJoinNames[1] != null
863                                                        && rightJoinNames[1] != null )
864                                        {
865                                                JoinCondition condition = new JoinCondition( );
866                                                condition.lefttable = leftJoinNames[0];
867                                                condition.righttable = rightJoinNames[0];
868                                                condition.leftcolumn = leftJoinNames[1];
869                                                condition.rightcolumn = rightJoinNames[1];
870                                                condition.leftColumnLocation = leftColumnLocation;
871                                                condition.rightColumnLocation = rightColumnLocation;
872                                                condition.leftTableLocation = leftTableLocation;
873                                                condition.rightTableLocation = rightTableLocation;
874                                                condition.joinType = joinType;
875                                                condition.operator = operator;
876
877                                                if ( !conditions.contains( condition ) )
878                                                {
879                                                        conditions.add( condition );
880                                                        if ( showLocation )
881                                                        {
882                                                                buffer.append( fillString( condition.lefttable
883                                                                                + condition.leftTableLocation )
884                                                                                + "\t"
885                                                                                + fillString( condition.leftcolumn
886                                                                                                + condition.leftColumnLocation )
887                                                                                + "\t"
888                                                                                + fillString( condition.righttable
889                                                                                                + condition.rightTableLocation )
890                                                                                + "\t"
891                                                                                + fillString( condition.rightcolumn
892                                                                                                + condition.rightColumnLocation )
893                                                                                + "\t"
894                                                                                + fillString( condition.joinType )
895                                                                                + "\t"
896                                                                                + fillString( condition.operator )
897                                                                                + "\r\n" );
898                                                        }
899                                                        else
900                                                        {
901                                                                buffer.append( fillString( condition.lefttable )
902                                                                                + "\t"
903                                                                                + fillString( condition.leftcolumn )
904                                                                                + "\t"
905                                                                                + fillString( condition.righttable )
906                                                                                + "\t"
907                                                                                + fillString( condition.rightcolumn )
908                                                                                + "\t"
909                                                                                + fillString( condition.joinType )
910                                                                                + "\t"
911                                                                                + fillString( condition.operator )
912                                                                                + "\r\n" );
913                                                        }
914                                                }
915                                        }
916                                }
917                        }
918                }
919        }
920
921        private void analyzeStmt( TCustomSqlStatement select )
922        {
923                if ( select.getCteList( ) != null && select.getCteList( ).size( ) > 0 )
924                {
925                        for ( int i = 0; i < select.getCteList( ).size( ); i++ )
926                        {
927                                TCTE expression = (TCTE) select.getCteList( ).getCTE( i );
928                                cteMap.put( expression.getTableName( ),
929                                                expression.getSubquery( ) );
930                        }
931                }
932
933                analyzeStatement( select );
934        }
935
936        private void analyzeStatement( TCustomSqlStatement select )
937        {
938                if ( select instanceof TSelectSqlStatement )
939                {
940                        TSelectSqlStatement stmt = (TSelectSqlStatement) select;
941
942                        searchJoinFromStatement( stmt );
943
944                        if ( stmt.isCombinedQuery( ) )
945                        {
946                                analyzeStatement( stmt.getLeftStmt( ) );
947                                analyzeStatement( stmt.getRightStmt( ) );
948                        }
949                        else
950                        {
951                                for ( int i = 0; i < select.getResultColumnList( ).size( ); i++ )
952                                {
953                                        TResultColumn field = select.getResultColumnList( )
954                                                        .getResultColumn( i );
955                                        searchFields( field, select );
956                                }
957                        }
958                }
959                else if ( select instanceof TPlsqlCreateProcedure )
960                {
961                        TPlsqlCreateProcedure createProcedure = (TPlsqlCreateProcedure) select;
962                        if ( createProcedure.getBodyStatements( ) != null )
963                        {
964                                for ( int i = 0; i < createProcedure.getBodyStatements( )
965                                                .size( ); i++ )
966                                {
967                                        analyzeStmt( createProcedure.getBodyStatements( ).get( i ) );
968                                }
969                        }
970                }
971                else if ( select instanceof TMssqlCreateProcedure )
972                {
973                        TMssqlCreateProcedure createProcedure = (TMssqlCreateProcedure) select;
974                        if ( createProcedure.getBodyStatements( ) != null )
975                        {
976                                for ( int i = 0; i < createProcedure.getBodyStatements( )
977                                                .size( ); i++ )
978                                {
979                                        analyzeStmt( createProcedure.getBodyStatements( ).get( i ) );
980                                }
981                        }
982                }
983                else if ( select instanceof TMssqlBlock )
984                {
985                        TMssqlBlock block = (TMssqlBlock) select;
986                        if ( block.getBodyStatements( ) != null )
987                        {
988                                for ( int i = 0; i < block.getBodyStatements( ).size( ); i++ )
989                                {
990                                        analyzeStmt( block.getBodyStatements( ).get( i ) );
991                                }
992                        }
993                }
994                else if ( select.getResultColumnList( ) != null )
995                {
996                        for ( int i = 0; i < select.getResultColumnList( ).size( ); i++ )
997                        {
998                                TResultColumn field = select.getResultColumnList( )
999                                                .getResultColumn( i );
1000                                searchFields( field, select );
1001                        }
1002                }
1003        }
1004
1005        private void searchJoinFromStatement( TSelectSqlStatement stmt )
1006        {
1007                if ( stmt.joins != null )
1008                {
1009                        for ( int i = 0; i < stmt.joins.size( ); i++ )
1010                        {
1011                                TJoin join = stmt.joins.getJoin( i );
1012                                handleJoin( stmt, join );
1013                        }
1014                }
1015        }
1016
1017        private void handleJoin( TSelectSqlStatement stmt, TJoin join )
1018        {
1019                if ( join.getJoin( ) != null )
1020                {
1021                        handleJoin( stmt, join.getJoin( ) );
1022                }
1023                if ( join.getJoinItems( ) != null )
1024                {
1025                        for ( int j = 0; j < join.getJoinItems( ).size( ); j++ )
1026                        {
1027                                TJoinItem joinItem = join.getJoinItems( ).getJoinItem( j );
1028                                TExpression expr = joinItem.getOnCondition( );
1029                                if ( expr != null )
1030                                {
1031                                        searchExpression( expr, stmt, joinItem.getJoinType( )
1032                                                        .name( ) );
1033                                }
1034                        }
1035                }
1036        }
1037
1038        private List<String[]> getRealName( String tableAlias, String columnAlias,
1039                        List<TCustomSqlStatement> stmtList )
1040        {
1041                List<String[]> nameList = new ArrayList<String[]>( );
1042                for ( TCustomSqlStatement stmt : stmtList )
1043                {
1044
1045                        gudusoft.gsqlparser.nodes.TTable table = null;
1046                        String columnName = columnAlias;
1047                        if ( ( tableAlias == null || tableAlias.length( ) == 0 )
1048                                        && stmt instanceof TSelectSqlStatement
1049                                        && ( (TSelectSqlStatement) stmt ).tables.size( ) == 1
1050                                        && ( (TSelectSqlStatement) stmt ).tables.getTable( 0 )
1051                                                        .getAliasClause( ) == null )
1052                        {
1053                                table = ( (TSelectSqlStatement) stmt ).tables.getTable( 0 );
1054                                getTableNames( nameList, table, columnName );
1055                                continue;
1056                        }
1057                        else if ( tableAlias == null || tableAlias.length( ) == 0 )
1058                        {
1059                                nameList.add( new String[]{
1060                                                null, columnName
1061                                } );
1062                                continue;
1063                        }
1064
1065                        if ( tableAliasMap.containsKey( tableAlias.toLowerCase( )
1066                                        + ":"
1067                                        + stmt.toString( ) ) )
1068                        {
1069                                table = (gudusoft.gsqlparser.nodes.TTable) tableAliasMap.get( tableAlias.toLowerCase( )
1070                                                + ":"
1071                                                + stmt.toString( ) );
1072                                getTableNames( nameList, table, columnName );
1073                                continue;
1074                        }
1075                        else if ( tableAliasMap.containsKey( tableAlias.toLowerCase( ) )
1076                                        && !containsKey( tableAliasMap, tableAlias.toLowerCase( )
1077                                                        + ":" ) )
1078                        {
1079                                table = (gudusoft.gsqlparser.nodes.TTable) tableAliasMap.get( tableAlias.toLowerCase( ) );
1080                                getTableNames( nameList, table, columnName );
1081                                continue;
1082                        }
1083                        else
1084                        {
1085                                if ( queryAliasMap.containsKey( tableAlias.toLowerCase( ) ) )
1086                                {
1087                                        Object value = queryAliasMap.get( tableAlias.toLowerCase( ) );
1088                                        if ( value instanceof TSelectSqlStatement )
1089                                        {
1090                                                TSelectSqlStatement sql = (TSelectSqlStatement) value;
1091                                                getRealNameFromSql( nameList, columnAlias, stmt, sql );
1092                                        }
1093                                        continue;
1094                                }
1095                                else if ( stmt instanceof TSelectSqlStatement )
1096                                {
1097                                        findTableByAlias( nameList,
1098                                                        (TSelectSqlStatement) stmt,
1099                                                        tableAlias,
1100                                                        columnAlias,
1101                                                        new ArrayList<TSelectSqlStatement>( ) );
1102                                        continue;
1103                                }
1104                                continue;
1105                        }
1106                }
1107                return nameList;
1108        }
1109
1110        private void getTableNames( List<String[]> nameList,
1111                        gudusoft.gsqlparser.nodes.TTable table, String columnName )
1112        {
1113                if ( !( table.getSubquery( ) instanceof TSelectSqlStatement ) )
1114                {
1115                        nameList.add( new String[]{
1116                                        table.getFullName( ), columnName
1117                        } );
1118                }
1119                else
1120                {
1121                        TSelectSqlStatement stmt = (TSelectSqlStatement) table.getSubquery( );
1122                        getRealNameFromSql( nameList, columnName, null, stmt );
1123                }
1124        }
1125
1126        private void getRealNameFromSql( List<String[]> nameList,
1127                        String columnAlias, TCustomSqlStatement stmt,
1128                        TSelectSqlStatement sql )
1129        {
1130                gudusoft.gsqlparser.nodes.TTable table = null;
1131                String columnName = null;
1132
1133                if ( sql.isCombinedQuery( ) )
1134                {
1135                        getRealNameFromSql( nameList, columnAlias, stmt, sql.getLeftStmt( ) );
1136                        getRealNameFromSql( nameList, columnAlias, stmt, sql.getRightStmt( ) );
1137                }
1138                else
1139                {
1140                        for ( int i = 0; i < sql.getResultColumnList( ).size( ); i++ )
1141                        {
1142                                TResultColumn field = sql.getResultColumnList( )
1143                                                .getResultColumn( i );
1144                                switch ( field.getExpr( ).getExpressionType( ) )
1145                                {
1146                                        case simple_object_name_t :
1147                                                TColumn column = attrToColumn( field, sql );
1148                                                if ( ( ( column.columnAlias == null || column.columnAlias.length( ) == 0 ) && columnAlias.trim( )
1149                                                                .equalsIgnoreCase( column.columnName.trim( ) ) )
1150                                                                || ( ( column.columnAlias != null && column.columnAlias.length( ) > 0 ) && columnAlias.trim( )
1151                                                                                .equals( column.columnAlias.trim( ) ) )
1152                                                                || column.columnName.equals( "*" ) )
1153                                                {
1154                                                        if ( column.columnPrex != null )
1155                                                        {
1156                                                                if ( stmt != null
1157                                                                                && tableAliasMap.containsKey( column.columnPrex.toLowerCase( )
1158                                                                                                + ":"
1159                                                                                                + stmt.toString( ) ) )
1160                                                                {
1161                                                                        table = (gudusoft.gsqlparser.nodes.TTable) tableAliasMap.get( column.columnPrex.toLowerCase( )
1162                                                                                        + ":"
1163                                                                                        + stmt.toString( ) );
1164                                                                }
1165                                                                else if ( tableAliasMap.containsKey( column.columnPrex.toLowerCase( ) ) )
1166                                                                {
1167                                                                        table = (gudusoft.gsqlparser.nodes.TTable) tableAliasMap.get( column.columnPrex.toLowerCase( ) );
1168                                                                }
1169                                                        }
1170                                                        else
1171                                                        {
1172                                                                table = sql.tables.getTable( 0 );
1173                                                        }
1174
1175                                                        if ( column.columnName.equals( "*" ) )
1176                                                        {
1177                                                                columnName = columnAlias;
1178                                                        }
1179                                                        else
1180                                                        {
1181                                                                columnName = column.columnName;
1182                                                        }
1183                                                }
1184                                                break;
1185                                }
1186                        }
1187                        if ( table != null )
1188                        {
1189                                nameList.add( new String[]{
1190                                                getTableName( table ), columnName
1191                                } );
1192                        }
1193                }
1194        }
1195
1196        private String getTableName( gudusoft.gsqlparser.nodes.TTable table )
1197        {
1198                if ( table.getSubquery( ) != null
1199                                && table.getSubquery( ).tables != null
1200                                && table.getSubquery( ).tables.size( ) > 0 )
1201                {
1202                        return getTableName( table.getSubquery( ).tables.getTable( 0 ) );
1203                }
1204                return table.getFullName( );
1205        }
1206
1207        private void findTableByAlias( List<String[]> nameList,
1208                        TSelectSqlStatement stmt, String tableAlias, String columnAlias,
1209                        List<TSelectSqlStatement> stats )
1210        {
1211                if ( stats.contains( stmt ) )
1212                        return;
1213                else
1214                        stats.add( stmt );
1215
1216                if ( stmt.isCombinedQuery( ) )
1217                {
1218                        findTableByAlias( nameList,
1219                                        stmt.getLeftStmt( ),
1220                                        tableAlias,
1221                                        columnAlias,
1222                                        stats );
1223                        findTableByAlias( nameList,
1224                                        stmt.getRightStmt( ),
1225                                        tableAlias,
1226                                        columnAlias,
1227                                        stats );
1228                }
1229                else
1230                {
1231                        for ( int i = 0; i < stmt.tables.size( ); i++ )
1232                        {
1233                                gudusoft.gsqlparser.nodes.TTable table = stmt.tables.getTable( i );
1234                                if ( table.getAliasClause( ) != null
1235                                                && table.getAliasClause( ).toString( ).length( ) > 0 )
1236                                {
1237                                        if ( table.getAliasClause( )
1238                                                        .toString( )
1239                                                        .equalsIgnoreCase( tableAlias ) )
1240                                        {
1241                                                nameList.add( new String[]{
1242                                                                table.getTableName( ).toString( ), columnAlias
1243                                                } );
1244                                                return;
1245                                        }
1246                                }
1247                                else if ( table.getTableName( ) != null )
1248                                {
1249                                        if ( table.getTableName( )
1250                                                        .toString( )
1251                                                        .equalsIgnoreCase( tableAlias ) )
1252                                        {
1253                                                nameList.add( new String[]{
1254                                                                table.getTableName( ).toString( ), columnAlias
1255                                                } );
1256                                                return;
1257                                        }
1258                                }
1259                        }
1260                }
1261                if ( nameList.size( ) == 0
1262                                && stmt.getParentStmt( ) instanceof TSelectSqlStatement )
1263                {
1264                        findTableByAlias( nameList,
1265                                        (TSelectSqlStatement) stmt.getParentStmt( ),
1266                                        tableAlias,
1267                                        columnAlias,
1268                                        stats );
1269                }
1270
1271        }
1272
1273        private boolean containsKey( HashMap tableAliasMap, String key )
1274        {
1275                String[] collection = (String[]) tableAliasMap.keySet( )
1276                                .toArray( new String[0] );
1277                for ( String str : collection )
1278                {
1279                        if ( str.toLowerCase( ).startsWith( key.toLowerCase( ) ) )
1280                                return true;
1281                }
1282                return false;
1283        }
1284
1285        private String fillString( String text )
1286        {
1287                if ( text == null )
1288                {
1289                        return "";
1290                }
1291
1292                int tablength = 8;
1293                if ( isOutputFile )
1294                {
1295                        tablength = 9;
1296                }
1297
1298                if ( text.length( ) < tablength )
1299                        text += "\t";
1300                return text;
1301        }
1302
1303        public void searchFields( TResultColumn field, TCustomSqlStatement select )
1304        {
1305                switch ( field.getExpr( ).getExpressionType( ) )
1306                {
1307                        case simple_object_name_t :
1308                                searchTables( select );
1309                                searchClauses( select );
1310                                break;
1311                        case simple_constant_t :
1312                                searchExpression( field.getExpr( ), select, null );
1313                                searchTables( select );
1314                                searchClauses( select );
1315                                break;
1316                        case case_t :
1317                                searchExpression( field.getExpr( ), select, null );
1318                                searchTables( select );
1319                                searchClauses( select );
1320                                break;
1321                        case function_t :
1322                                searchExpression( field.getExpr( ), select, null );
1323                                searchTables( select );
1324                                searchClauses( select );
1325
1326                                TFunctionCall func = field.getExpr( ).getFunctionCall( );
1327                                // buffer.AppendLine("function name {0}",
1328                                // func.funcname.AsText);
1329
1330                                // check column : function arguments
1331                                if ( func.getArgs( ) != null )
1332                                {
1333                                        for ( int k = 0; k < func.getArgs( ).size( ); k++ )
1334                                        {
1335                                                TExpression expr = (TExpression) func.getArgs( )
1336                                                                .getExpression( k );
1337                                                searchExpression( expr, select, null );
1338                                        }
1339                                }
1340                                else
1341                                {
1342                                        if ( select.tables.getTable( 0 ).getAliasClause( ) != null )
1343                                        {
1344                                                String alias = select.tables.getTable( 0 )
1345                                                                .getAliasClause( )
1346                                                                .toString( );
1347                                                if ( !tableAliasMap.containsKey( alias.toLowerCase( )
1348                                                                .trim( ) + ":" + select.toString( ) ) )
1349                                                {
1350                                                        tableAliasMap.put( alias.toLowerCase( ).trim( )
1351                                                                        + ":"
1352                                                                        + select.toString( ),
1353                                                                        select.tables.getTable( 0 ) );
1354                                                }
1355                                                if ( !tableAliasMap.containsKey( alias.toLowerCase( )
1356                                                                .trim( ) ) )
1357                                                {
1358                                                        tableAliasMap.put( alias.toLowerCase( ).trim( ),
1359                                                                        select.tables.getTable( 0 ) );
1360                                                }
1361                                        }
1362                                }
1363
1364                                if ( func.getAnalyticFunction( ) != null )
1365                                {
1366                                        TParseTreeNodeList list = func.getAnalyticFunction( )
1367                                                        .getPartitionBy_ExprList( );
1368
1369                                        searchExpressionList( select, list );
1370
1371                                        if ( func.getAnalyticFunction( ).getOrderBy( ) != null )
1372                                        {
1373                                                list = func.getAnalyticFunction( )
1374                                                                .getOrderBy( )
1375                                                                .getItems( );
1376                                                searchExpressionList( select, list );
1377                                        }
1378                                }
1379
1380                                // check order by clause
1381                                // if (select instanceof TSelectSqlStatement &&
1382                                // ((TSelectSqlStatement)select).GroupbyClause != null)
1383                                // {
1384                                // for (int j = 0; j <
1385                                // ((TSelectSqlStatement)select).GroupbyClause.GroupItems.Count();
1386                                // j++)
1387                                // {
1388                                // TLzGroupByItem i =
1389                                // (TLzGroupByItem)((TSelectSqlStatement)select).GroupbyClause.GroupItems[j];
1390                                // searchExpression((TExpression)i._ndExpr, select);
1391                                // searchTables(select);
1392                                // }
1393
1394                                // }
1395
1396                                break;
1397                        case subquery_t :
1398                                if ( field.getExpr( ).getSubQuery( ) instanceof TSelectSqlStatement )
1399                                {
1400                                        searchSubQuery( field.getExpr( ).getSubQuery( ) );
1401                                }
1402                                break;
1403                        default :
1404                                buffer.append( "searchFields of type: "
1405                                                + field.getExpr( ).getExpressionType( )
1406                                                + " not implemented yet\r\n" );
1407                                break;
1408                }
1409        }
1410
1411        private void searchExpressionList( TCustomSqlStatement select,
1412                        TParseTreeNodeList list )
1413        {
1414                if ( list == null )
1415                        return;
1416
1417                for ( int i = 0; i < list.size( ); i++ )
1418                {
1419                        TExpression lcexpr = null;
1420                        if ( list.getElement( i ) instanceof TOrderByItem )
1421                        {
1422                                lcexpr = (TExpression) ( (TOrderByItem) list.getElement( i ) ).getSortKey( );
1423                        }
1424                        else if ( list.getElement( i ) instanceof TExpression )
1425                        {
1426                                lcexpr = (TExpression) list.getElement( i );
1427                        }
1428
1429                        if ( lcexpr != null )
1430                        {
1431                                searchExpression( lcexpr, select, null );
1432                        }
1433                }
1434        }
1435
1436        private void searchClauses( TCustomSqlStatement select )
1437        {
1438                if ( !searchInClauses.contains( select ) )
1439                {
1440                        searchInClauses.add( select );
1441                }
1442                else
1443                {
1444                        return;
1445                }
1446                if ( select instanceof TSelectSqlStatement )
1447                {
1448
1449                        TSelectSqlStatement statement = (TSelectSqlStatement) select;
1450                        HashMap clauseTable = new HashMap( );
1451
1452                        // if (statement.SortClause != null)
1453                        // {
1454                        // TLzOrderByList sortList = (TLzOrderByList)statement.SortClause;
1455                        // for (int i = 0; i < sortList.Count(); i++)
1456                        // {
1457                        // TLzOrderBy orderBy = sortList[i];
1458                        // TExpression expr = orderBy.SortExpr;
1459                        // clauseTable.add(expr, ClauseType.orderby);
1460                        // }
1461                        // }
1462
1463                        if ( statement.getWhereClause( ) != null )
1464                        {
1465                                clauseTable.put( ( statement.getWhereClause( ).getCondition( ) ),
1466                                                ClauseType.where );
1467                        }
1468                        // if (statement.ConnectByClause != null)
1469                        // {
1470                        // clauseTable.add((TExpression)statement.ConnectByClause,
1471                        // ClauseType.connectby);
1472                        // }
1473                        // if (statement.StartwithClause != null)
1474                        // {
1475                        // clauseTable.add((TExpression)statement.StartwithClause,
1476                        // ClauseType.startwith);
1477                        // }
1478                        for ( TExpression expr : (TExpression[]) clauseTable.keySet( )
1479                                        .toArray( new TExpression[0] ) )
1480                        {
1481                                ClauseType type = (ClauseType) clauseTable.get( expr );
1482                                searchExpression( expr,
1483                                                select,
1484                                                type == null ? null : type.name( ) );
1485                                searchTables( select );
1486
1487                        }
1488                }
1489        }
1490
1491        void searchTables( TCustomSqlStatement select )
1492        {
1493                if ( !searchInTables.contains( select ) )
1494                {
1495                        searchInTables.add( select );
1496                }
1497                else
1498                {
1499                        return;
1500                }
1501
1502                gudusoft.gsqlparser.nodes.TTableList tables = select.tables;
1503
1504                if ( tables.size( ) == 1 )
1505                {
1506                        gudusoft.gsqlparser.nodes.TTable lzTable = tables.getTable( 0 );
1507                        if ( ( lzTable.getTableType( ) == ETableSource.objectname )
1508                                        && ( lzTable.getAliasClause( ) == null || lzTable.getAliasClause( )
1509                                                        .toString( )
1510                                                        .trim( )
1511                                                        .length( ) == 0 ) )
1512                        {
1513                                if ( cteMap.containsKey( lzTable.getTableName( ).toString( ) ) )
1514                                {
1515                                        searchSubQuery( (TSelectSqlStatement) cteMap.get( lzTable.getTableName( )
1516                                                        .toString( ) ) );
1517                                }
1518                                else
1519                                {
1520                                        if ( lzTable.getAliasClause( ) != null )
1521                                        {
1522                                                String alias = lzTable.getAliasClause( ).toString( );
1523                                                if ( !tableAliasMap.containsKey( alias.toLowerCase( )
1524                                                                .trim( ) + ":" + select.toString( ) ) )
1525                                                {
1526                                                        tableAliasMap.put( alias.toLowerCase( ).trim( )
1527                                                                        + ":"
1528                                                                        + select.toString( ), lzTable );
1529                                                }
1530                                                if ( !tableAliasMap.containsKey( alias.toLowerCase( )
1531                                                                .trim( ) ) )
1532                                                {
1533                                                        tableAliasMap.put( alias.toLowerCase( ).trim( ),
1534                                                                        lzTable );
1535                                                }
1536                                        }
1537                                }
1538                        }
1539                }
1540
1541                for ( int i = 0; i < tables.size( ); i++ )
1542                {
1543                        gudusoft.gsqlparser.nodes.TTable lztable = tables.getTable( i );
1544                        switch ( lztable.getTableType( ) )
1545                        {
1546                                case objectname :
1547                                        TTable table = TLzTaleToTable( lztable );
1548                                        String alias = table.tableAlias;
1549                                        if ( alias != null )
1550                                                alias = alias.trim( );
1551                                        else if ( table.tableName != null )
1552                                                alias = table.tableName.trim( );
1553
1554                                        if ( cteMap.containsKey( lztable.getTableName( ).toString( ) ) )
1555                                        {
1556                                                searchSubQuery( (TSelectSqlStatement) cteMap.get( lztable.getTableName( )
1557                                                                .toString( ) ) );
1558                                        }
1559                                        else
1560                                        {
1561                                                if ( alias != null )
1562                                                {
1563                                                        if ( !tableAliasMap.containsKey( alias.toLowerCase( )
1564                                                                        .trim( )
1565                                                                        + ":"
1566                                                                        + select.toString( ) ) )
1567                                                        {
1568                                                                tableAliasMap.put( alias.toLowerCase( ).trim( )
1569                                                                                + ":"
1570                                                                                + select.toString( ), lztable );
1571                                                        }
1572                                                        if ( !tableAliasMap.containsKey( alias.toLowerCase( )
1573                                                                        .trim( ) ) )
1574                                                        {
1575                                                                tableAliasMap.put( alias.toLowerCase( ).trim( ),
1576                                                                                lztable );
1577                                                        }
1578                                                }
1579                                        }
1580                                        break;
1581                                case subquery :
1582                                        if ( lztable.getAliasClause( ) != null )
1583                                        {
1584                                                String tableAlias = lztable.getAliasClause( )
1585                                                                .toString( )
1586                                                                .trim( );
1587                                                if ( !queryAliasMap.containsKey( tableAlias.toLowerCase( ) ) )
1588                                                {
1589                                                        queryAliasMap.put( tableAlias.toLowerCase( ),
1590                                                                        (TSelectSqlStatement) lztable.getSubquery( ) );
1591                                                }
1592                                        }
1593                                        searchSubQuery( (TSelectSqlStatement) lztable.getSubquery( ) );
1594                                        break;
1595                                default :
1596                                        break;
1597                        }
1598                }
1599        }
1600
1601        public void searchSubQuery( TSelectSqlStatement select )
1602        {
1603                if ( !searchInSubQuerys.contains( select ) )
1604                {
1605                        searchInSubQuerys.add( select );
1606                }
1607                else
1608                {
1609                        return;
1610                }
1611
1612                searchJoinFromStatement( select );
1613
1614                if ( select.isCombinedQuery( ) )
1615                {
1616                        searchSubQuery( select.getLeftStmt( ) );
1617                        searchSubQuery( select.getRightStmt( ) );
1618                }
1619                else
1620                {
1621                        for ( int i = 0; i < select.getResultColumnList( ).size( ); i++ )
1622                        {
1623                                TResultColumn field = select.getResultColumnList( )
1624                                                .getResultColumn( i );
1625                                searchFields( field, select );
1626                        }
1627                }
1628        }
1629
1630        public TColumn attrToColumn( TResultColumn field, TCustomSqlStatement stmt )
1631        {
1632                TColumn column = new TColumn( );
1633
1634                TExpression attr = field.getExpr( );
1635
1636                column.columnAlias = field.getAliasClause( ) == null ? null
1637                                : field.getAliasClause( ).toString( );
1638                TSourceToken columnToken = attr.getEndToken( );
1639                column.columnName = columnToken.toString( );
1640                if ( columnToken != null )
1641                {
1642                        column.columnLocation = "("
1643                                        + columnToken.lineNo
1644                                        + ","
1645                                        + columnToken.columnNo
1646                                        + ")";
1647                }
1648
1649                if ( attr.toString( ).indexOf( "." ) > 0 )
1650                {
1651                        column.columnPrex = attr.toString( ).substring( 0,
1652                                        attr.toString( ).lastIndexOf( "." ) );
1653
1654                        String tableName = column.columnPrex;
1655                        if ( tableName.indexOf( "." ) > 0 )
1656                        {
1657                                tableName = tableName.substring( tableName.lastIndexOf( "." ) + 1 );
1658                        }
1659                        if ( !column.tableNames.contains( tableName ) )
1660                        {
1661                                column.tableNames.add( tableName );
1662
1663                                if ( attr.getObjectOperand( ) != null
1664                                                && attr.getObjectOperand( ).getTableToken( ) != null )
1665                                {
1666                                        TSourceToken tableToken = attr.getObjectOperand( )
1667                                                        .getTableToken( );
1668                                        if ( tableToken != null )
1669                                        {
1670                                                column.tableLocation = "("
1671                                                                + tableToken.lineNo
1672                                                                + ","
1673                                                                + tableToken.columnNo
1674                                                                + ")";
1675                                        }
1676                                }
1677                        }
1678                }
1679                else
1680                {
1681                        TTableList tables = stmt.tables;
1682                        for ( int i = 0; i < tables.size( ); i++ )
1683                        {
1684                                gudusoft.gsqlparser.nodes.TTable lztable = tables.getTable( i );
1685                                TTable table = TLzTaleToTable( lztable );
1686                                if ( !column.tableNames.contains( table.tableName ) )
1687                                {
1688                                        column.tableNames.add( table.tableName );
1689                                        column.tableLocation = table.tableLocation;
1690                                }
1691                        }
1692                }
1693
1694                return column;
1695        }
1696
1697        TTable TLzTaleToTable( gudusoft.gsqlparser.nodes.TTable lztable )
1698        {
1699                TTable table = new TTable( );
1700                if ( lztable.getSubquery( ) == null && lztable.getTableName( ) != null )
1701                {
1702                        table.tableName = lztable.getName( );
1703                        if ( lztable.getTableName( ).toString( ).indexOf( "." ) > 0 )
1704                        {
1705                                table.prefixName = lztable.getTableName( )
1706                                                .toString( )
1707                                                .substring( 0, lztable.getFullName( ).indexOf( '.' ) );
1708                        }
1709                        table.tableLocation = lztable.getTableName( ).coordinate( );
1710                }
1711
1712                if ( lztable.getAliasClause( ) != null )
1713                {
1714                        table.tableAlias = lztable.getAliasClause( ).toString( );
1715                        if ( table.tableLocation == null )
1716                        {
1717                                table.tableLocation = lztable.getAliasClause( )
1718                                                .getAliasName( )
1719                                                .coordinate( );
1720                        }
1721                }
1722                return table;
1723        }
1724
1725        void searchExpression( TExpression expr, TCustomSqlStatement stmt,
1726                        String joinType )
1727        {
1728                joinConditonsInExpr c = new joinConditonsInExpr( this,
1729                                expr,
1730                                stmt,
1731                                joinType );
1732                c.searchExpression( );
1733        }
1734
1735}