Source code

001package gudusoft.gsqlparser.stmt;
002
003import gudusoft.gsqlparser.*;
004import gudusoft.gsqlparser.nodes.*;
005import gudusoft.gsqlparser.nodes.couchbase.TUseKeyIndex;
006import gudusoft.gsqlparser.nodes.mssql.TOptionClause;
007import gudusoft.gsqlparser.nodes.oracle.TErrorLoggingClause;
008import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
009import gudusoft.gsqlparser.util.SQLUtil;
010
011import java.util.ArrayList;
012
013/**
014 * SQL update statement.
015 * <br>
016 * <br> {@link #getTargetTable} returns the target table, this table can also be fetched from the first element of  {@link #tables}.
017 * <br> If there is a from clause in update statement, {@link #joins} returns all the tables in the from clause.
018 * <br> {@link #getResultColumnList()} returns assignment in set clause.
019 *
020 *<pre>
021 * UPDATE dbo.Table2
022 * SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
023 * FROM dbo.Table2
024 * INNER JOIN dbo.Table1
025 * ON (dbo.Table2.ColA = dbo.Table1.ColA);
026 *</pre>
027 * Table: dbo.Table2 can be fetched from {@link #getTargetTable} or {@link #tables}
028 * <br>set clause: dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB, {@link #getResultColumnList}
029 * <br>from clause: dbo.Table2 inner join , {@link #joins}
030 *
031 *
032 * @see TCustomSqlStatement#cteList
033 * @see TCustomSqlStatement#topClause
034 * @see TCustomSqlStatement#targetTable
035 * @see TCustomSqlStatement#outputClause
036 * @see TCustomSqlStatement#joins
037 * @see TCustomSqlStatement#resultColumnList 
038 * @see TCustomSqlStatement#whereClause
039 * @see TCustomSqlStatement#returningClause
040 */
041
042public class TUpdateSqlStatement extends TCustomSqlStatement {
043
044
045    private TInsertSqlStatement insertSqlStatement = null;
046
047    /**
048     * Teradata, insert statement used after ELSE keyword
049     * @return insert statement used after ELSE keyword
050     */
051    public TInsertSqlStatement getInsertSqlStatement() {
052        return insertSqlStatement;
053    }
054
055    private TErrorLoggingClause errorLoggingClause;
056
057    /**
058     * Oracle, error logging clause
059     * @return Oracle, error logging clause
060     */
061    public TErrorLoggingClause getErrorLoggingClause() {
062        return errorLoggingClause;
063    }
064
065    private TOptionClause optionClause;
066
067    /**
068     * sql server option clause
069     * @return option clause
070     *
071     * @see gudusoft.gsqlparser.nodes.mssql.TOptionClause
072     */
073    public TOptionClause getOptionClause() {
074        return optionClause;
075    }
076
077    private TSourceToken updateToken = null;
078
079    public void setUpdateToken(TSourceToken updateToken) {
080        this.updateToken = updateToken;
081    }
082
083    /**
084     *  UPDATE keyword
085     *
086     * @return UPDATE keyword in update statement.
087     */
088    public TSourceToken getUpdateToken() {
089
090        return updateToken;
091    }
092
093    private TJoinList referenceJoins = null;
094
095    /**
096     * @deprecated As of v1.9.7.2, use {@link #joins} instead.
097     * <br>
098     * <p>  getReferenceJoins() represents: table_references
099     * @return table references in from clause
100     */
101    public TJoinList getReferenceJoins() {
102        if (this.referenceJoins == null){
103            this.referenceJoins = new TJoinList();
104        }
105        return referenceJoins;
106    }
107
108    private TOrderBy orderByClause = null;
109
110    /**
111     * Couchbase, MySQL limit clause.
112     * @return Couchbase, MySQL limit clause.
113     */
114    public TLimitClause getLimitClause() {
115        return limitClause;
116    }
117
118    /**
119     * Order by clause is not used.
120     * @return Order by clause
121     */
122    public TOrderBy getOrderByClause() {
123        return orderByClause;
124    }
125
126    private TLimitClause limitClause = null;
127
128    public TUpdateSqlStatement(EDbVendor dbvendor) {
129        super(dbvendor);
130        sqlstatementtype = ESqlStatementType.sstupdate;
131    }
132
133    void buildsql() {
134    }
135
136    void clear() {
137    }
138
139    String getasprettytext() {
140        return "";
141    }
142
143    void iterate(TVisitorAbs pvisitor) {
144    }
145
146    public int doParseStatement(TCustomSqlStatement psql) {
147        if (rootNode == null) return -1;
148        TUpdateSqlNode updateNode = (TUpdateSqlNode)rootNode;
149
150        if (this.sourcetokenlist.size() == 0){
151            // subquery nested in other statements.
152            this.setStartToken(updateNode.getStartToken());
153            this.setEndToken(updateNode.getEndToken());
154        }
155
156        super.doParseStatement(psql);
157        this.updateToken = updateNode.getUpdateToken();
158        
159        if (updateNode.cteList != null){
160            this.setCteList(updateNode.cteList);
161            this.getCteList().doParse(this, ESqlClause.cte);
162        }
163
164        if (updateNode.getTopClause() != null){
165           updateNode.getTopClause().doParse(this,ESqlClause.top);
166           this.setTopClause(updateNode.getTopClause());
167        }
168
169        if (updateNode.getSourceTableList() != null){ // update ... from source table list
170            TFromTable lcFromTable = null;
171            TJoin lcJoin = null;
172
173            for(int i=0; i<updateNode.getSourceTableList().size();i++){
174                lcFromTable = updateNode.getSourceTableList().getFromTable(i);
175                if (lcFromTable.getFromtableType() != ETableSource.join){
176                    lcJoin = new TJoin();
177                    TTable lcTable =  analyzeFromTable(lcFromTable,true);
178                    lcTable.setEffectType(ETableEffectType.tetSelect);
179                    lcJoin.setTable(lcTable);
180                    this.getRelations().add(lcTable);
181                }else{
182                    this.fromSourceJoin = lcFromTable.getJoinExpr();
183
184                    this.fromSourceTable = new TTable();
185                    this.fromSourceTable.setTableType(ETableSource.join);
186                    this.fromSourceTable.setAliasClause(lcFromTable.getJoinExpr().getAliasClause());
187                    this.fromSourceTable.setStartToken(lcFromTable.getStartToken());
188                    this.fromSourceTable.setEndToken(lcFromTable.getEndToken());
189                    this.fromSourceTable.setGsqlparser(lcFromTable.getGsqlparser());
190                    this.fromSourceTable.setJoinExpr(this.fromSourceJoin);
191                    this.getRelations().add(this.fromSourceTable);
192
193                    lcJoin = analyzeJoin(lcFromTable.getJoinExpr(),null,true);
194                    lcJoin.doParse(this, ESqlClause.join);
195                }
196                joins.addJoin(lcJoin);
197            }
198        }
199
200        if (updateNode.getReferenceTableList() != null){
201            // Postgresql syntax:
202            // update table_name1 set f=1
203            // from table_references
204            // Processed before the target table so that the alias search below can
205            // link a target that is the alias of a table in the FROM clause
206            // (e.g. Snowflake/migration-style: UPDATE a SET ... FROM real_table a JOIN ...)
207            TFromTable lcFromTable = null;
208            TJoin lcJoin = null;
209
210            for(int i=0; i<updateNode.getReferenceTableList().size();i++){
211                lcFromTable = updateNode.getReferenceTableList().getFromTable(i);
212                if (lcFromTable.getFromtableType() != ETableSource.join){
213                    lcJoin = new TJoin();
214                    TTable lcTable = analyzeFromTable(lcFromTable,true);
215                    lcTable.setEffectType(ETableEffectType.tetSelect);
216                    lcJoin.setTable( lcTable);
217                    this.getRelations().add(lcTable);
218                }else{
219                    this.fromSourceJoin = lcFromTable.getJoinExpr();
220
221                    this.fromSourceTable = new TTable();
222                    this.fromSourceTable.setTableType(ETableSource.join);
223                    this.fromSourceTable.setAliasClause(lcFromTable.getJoinExpr().getAliasClause());
224                    this.fromSourceTable.setStartToken(lcFromTable.getJoinExpr().getStartToken());
225                    this.fromSourceTable.setEndToken(lcFromTable.getJoinExpr().getEndToken());
226                    this.fromSourceTable.setGsqlparser(lcFromTable.getJoinExpr().getGsqlparser());
227                    this.fromSourceTable.setJoinExpr(this.fromSourceJoin);
228                    this.getRelations().add(this.fromSourceTable);
229
230                    lcJoin = analyzeJoin(lcFromTable.getJoinExpr(),null,true);
231                    lcJoin.doParse(this, ESqlClause.join);
232                }
233              //  this.getReferenceJoins().addJoin(lcJoin);
234                joins.addJoin(lcJoin);
235            }
236
237        }
238
239        if (updateNode.getTargetTable().getFromtableType() != ETableSource.join ){
240            // search in from clause to see if this is the table alias of table in from clause
241            boolean isTableAlias = false;
242            TTable lcLinkTable = null;
243            String targetName = updateNode.getTargetTable().toString();
244            if (getJoins().size() > 0){
245                for(int j=0;j<getJoins().size() && !isTableAlias;j++){
246                    TJoin lcJoinForAlias = getJoins().getJoin(j);
247                    TTable lcCandidate = lcJoinForAlias.getTable();
248                    if (lcCandidate != null && lcCandidate.getAliasClause() != null
249                            && SQLUtil.sameName(this.dbvendor, ESQLDataObjectType.dotTable, targetName, lcCandidate.getAliasName())){
250                        lcLinkTable = lcCandidate;
251                        isTableAlias = true;
252                        break;
253                    }
254                    // Also walk join items - the target alias may be on a joined table
255                    // (e.g. UPDATE S ... FROM A JOIN B S ON ...)
256                    if (lcJoinForAlias.getJoinItems() != null){
257                        for(int k=0;k<lcJoinForAlias.getJoinItems().size();k++){
258                            TTable lcItemTable = lcJoinForAlias.getJoinItems().getJoinItem(k).getTable();
259                            if (lcItemTable != null && lcItemTable.getAliasClause() != null
260                                    && SQLUtil.sameName(this.dbvendor, ESQLDataObjectType.dotTable, targetName, lcItemTable.getAliasName())){
261                                lcLinkTable = lcItemTable;
262                                isTableAlias = true;
263                                break;
264                            }
265                        }
266                    }
267                }
268            }
269            TTable lcTable = analyzeFromTable(updateNode.getTargetTable(),false);
270            lcTable.setEffectType(ETableEffectType.tetUpdate);
271            unbindCteTarget(lcTable);
272            if (isTableAlias){
273                lcTable.setLinkTable(lcLinkTable);
274                // the linked FROM table is the real update target, so it must
275                // report the update effect, same as a directly named target
276                lcLinkTable.setEffectType(ETableEffectType.tetUpdate);
277                setTargetTable(lcLinkTable);
278            }else{
279                tables.insertElementAt(lcTable,0);
280                setTargetTable(lcTable);
281                this.getRelations().add(0,lcTable); // update table 需要放在 from table 前
282            }
283            //setTargetTable(lcTable);
284        }else{
285            this.fromSourceJoin = updateNode.getTargetTable().getJoinExpr();
286
287            this.fromSourceTable = new TTable();
288            this.fromSourceTable.setTableType(ETableSource.join);
289            this.fromSourceTable.setAliasClause(updateNode.getTargetTable().getJoinExpr().getAliasClause());
290            this.fromSourceTable.setStartToken(updateNode.getTargetTable().getJoinExpr().getStartToken());
291            this.fromSourceTable.setEndToken(updateNode.getTargetTable().getJoinExpr().getEndToken());
292            this.fromSourceTable.setGsqlparser(updateNode.getTargetTable().getJoinExpr().getGsqlparser());
293            this.fromSourceTable.setJoinExpr(this.fromSourceJoin);
294            this.getRelations().add(0,this.fromSourceTable); // update table 需要放在 from table 前
295
296            TJoin  lcJoin1 = analyzeJoin(updateNode.getTargetTable().getJoinExpr(),null,true);
297            lcJoin1.doParse(this, ESqlClause.join);
298            setTargetTable(lcJoin1.getTable());
299            joins.addJoin(lcJoin1);
300        }
301
302        if (updateNode.getUseKeyIndex() != null){
303            setUseKeyIndex(updateNode.getUseKeyIndex());
304            getUseKeyIndex().doParse(this,ESqlClause.unknown);
305        }
306
307        if (updateNode.getOutputClause() != null){
308            updateNode.getOutputClause().doParse(this,ESqlClause.output);
309            this.setOutputClause(updateNode.getOutputClause());
310        }
311
312        setResultColumnList(updateNode.getResultColumnList());
313
314        if (updateNode.getUnSetTerms() != null){
315            setUnSetTerms(updateNode.getUnSetTerms());
316            getUnSetTerms().doParse(this,ESqlClause.unSet);
317        }
318
319
320        if (getResultColumnList() != null){
321            getResultColumnList().doParse(this,ESqlClause.set);
322        }
323
324        if (updateNode.getOrderByClause() != null){
325            setOrderByClause(updateNode.getOrderByClause());
326            getOrderByClause().doParse(this,ESqlClause.orderby);
327        }
328
329        if (updateNode.getLimitClause() != null){
330            setLimitClause(updateNode.getLimitClause());
331            getLimitClause().doParse(this,ESqlClause.limit);
332        }
333
334        if (updateNode.getWhereCondition() != null){
335            updateNode.getWhereCondition().doParse(this,ESqlClause.where);
336            this.setWhereClause(updateNode.getWhereCondition());
337        }
338
339        if (updateNode.getReturningClause() != null){
340            updateNode.getReturningClause().doParse(this,ESqlClause.returning);
341            this.setReturningClause(updateNode.getReturningClause());
342        }
343
344        this.optionClause = updateNode.getOptionClause();
345
346        errorLoggingClause = updateNode.getErrorLoggingClause();
347
348        if (updateNode.getInsertSqlNode() != null){
349            insertSqlStatement = new TInsertSqlStatement(this.dbvendor);
350            insertSqlStatement.rootNode = updateNode.getInsertSqlNode();
351            insertSqlStatement.doParseStatement(this);
352
353        }
354
355        // remove table alias A from tables like this SQL:
356        // delete  A
357        // from  myTable A
358        // join otherTable B on A.Id=B.Id
359        int deletedTables[] = new int[this.tables.size()];
360        TTable lcTable = null,lcTable2 = null;
361        for(int i=0;i<this.tables.size();i++){
362            lcTable = this.tables.getTable(i);
363            if ((lcTable.getAliasClause() == null)&&(lcTable.isBaseTable())){
364                for(int j=0;j<this.tables.size();j++){
365                    if (i == j) {continue;}
366                    lcTable2 = this.tables.getTable(j);
367                    if (lcTable2.getAliasClause() != null){
368                        if (lcTable2.getAliasClause().toString().compareToIgnoreCase(lcTable.toString()) == 0){
369                            deletedTables[i] = 1;
370                            //lcTable.setLinkTable(true);
371                            lcTable.setLinkTable(lcTable2);
372//                            for(int k=0;k<lcTable.getObjectNameReferences().size();k++){
373//                                lcTable2.getObjectNameReferences().addObjectName(lcTable.getObjectNameReferences().getObjectName(k));
374//                                //lcTable2.getLinkedColumns().addObjectName(lcTable.getObjectNameReferences().getObjectName(k));
375//                                //lcTable.getObjectNameReferences().getObjectName(k).setSourceTable(lcTable2);
376//                               // System.out.println(lcTable.getObjectNameReferences().getAliasName(k).toString());
377//                            }
378                        }
379                    }
380
381                }
382            }
383        }
384//        for(int i = this.tables.size()-1;i>=0;i--){
385//            if (deletedTables[i] == 1){
386//                //this.tables.removeElementAt(i);
387//            }
388//        }
389        
390        return 0;
391    }
392
393    public void accept(TParseTreeVisitor v){
394        v.preVisit(this);
395        v.postVisit(this);
396    }
397
398    public void acceptChildren(TParseTreeVisitor v){
399        v.preVisit(this);
400
401        if (this.getCteList() != null){
402            this.getCteList().acceptChildren(v);
403        }
404
405
406
407        if (TBaseType.USE_JOINEXPR_INSTEAD_OF_JOIN){
408
409            for(TTable table:getRelations()){
410                table.acceptChildren(v);
411            }
412
413        }else{
414            if (this.getTargetTable() != null){
415                this.getTargetTable().acceptChildren(v);
416            }
417            if (this.joins.size() > 0){
418                this.joins.acceptChildren(v);
419            }
420        }
421
422        if (this.getTopClause() != null){
423            this.getTopClause().acceptChildren(v);
424        }
425
426        if (this.getOutputClause() != null){
427            this.getOutputClause().acceptChildren(v);
428        }
429
430        if (this.getResultColumnList() != null){
431            this.getResultColumnList().acceptChildren(v);
432        }else{
433            TBaseType.log("set clause in update stmt is null",TLog.WARNING);
434        }
435
436
437        if (this.getWhereClause() != null){
438            this.getWhereClause().acceptChildren(v);
439        }
440
441        if (this.getOrderByClause() != null){
442            this.getOrderByClause().acceptChildren(v);
443        }
444
445        if (this.getLimitClause() != null){
446            this.getLimitClause().acceptChildren(v);
447        }
448
449        if (this.getReturningClause() != null){
450            this.getReturningClause().acceptChildren(v);
451        }
452
453        v.postVisit(this);
454    }
455
456    public void setErrorLoggingClause(TErrorLoggingClause errorLoggingClause) {
457        this.errorLoggingClause = errorLoggingClause;
458    }
459
460    public void setOptionClause(TOptionClause optionClause) {
461        this.optionClause = optionClause;
462    }
463
464    public void setReferenceJoins(TJoinList referenceJoins) {
465        this.referenceJoins = referenceJoins;
466    }
467
468    public void setOrderByClause(TOrderBy orderByClause) {
469        this.orderByClause = orderByClause;
470    }
471
472    public void setLimitClause(TLimitClause limitClause) {
473        this.limitClause = limitClause;
474    }
475
476    private TUseKeyIndex useKeyIndex;//couchbase
477
478    public void setUseKeyIndex(TUseKeyIndex useKeyIndex) {
479        this.useKeyIndex = useKeyIndex;
480    }
481
482    public TUseKeyIndex getUseKeyIndex() {
483
484        return useKeyIndex;
485    }
486
487    private TResultColumnList unSetTerms; //couchbase
488
489    public void setUnSetTerms(TResultColumnList unSetTerms) {
490        this.unSetTerms = unSetTerms;
491    }
492
493    /**
494     * Couchbase unset clause
495     * @return Couchbase unset clause
496     */
497    public TResultColumnList getUnSetTerms() {
498
499        return unSetTerms;
500    }
501
502}