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            if (isTableAlias){
272                lcTable.setLinkTable(lcLinkTable);
273                // the linked FROM table is the real update target, so it must
274                // report the update effect, same as a directly named target
275                lcLinkTable.setEffectType(ETableEffectType.tetUpdate);
276                setTargetTable(lcLinkTable);
277            }else{
278                tables.insertElementAt(lcTable,0);
279                setTargetTable(lcTable);
280                this.getRelations().add(0,lcTable); // update table 需要放在 from table 前
281            }
282            //setTargetTable(lcTable);
283        }else{
284            this.fromSourceJoin = updateNode.getTargetTable().getJoinExpr();
285
286            this.fromSourceTable = new TTable();
287            this.fromSourceTable.setTableType(ETableSource.join);
288            this.fromSourceTable.setAliasClause(updateNode.getTargetTable().getJoinExpr().getAliasClause());
289            this.fromSourceTable.setStartToken(updateNode.getTargetTable().getJoinExpr().getStartToken());
290            this.fromSourceTable.setEndToken(updateNode.getTargetTable().getJoinExpr().getEndToken());
291            this.fromSourceTable.setGsqlparser(updateNode.getTargetTable().getJoinExpr().getGsqlparser());
292            this.fromSourceTable.setJoinExpr(this.fromSourceJoin);
293            this.getRelations().add(0,this.fromSourceTable); // update table 需要放在 from table 前
294
295            TJoin  lcJoin1 = analyzeJoin(updateNode.getTargetTable().getJoinExpr(),null,true);
296            lcJoin1.doParse(this, ESqlClause.join);
297            setTargetTable(lcJoin1.getTable());
298            joins.addJoin(lcJoin1);
299        }
300
301        if (updateNode.getUseKeyIndex() != null){
302            setUseKeyIndex(updateNode.getUseKeyIndex());
303            getUseKeyIndex().doParse(this,ESqlClause.unknown);
304        }
305
306        if (updateNode.getOutputClause() != null){
307            updateNode.getOutputClause().doParse(this,ESqlClause.output);
308            this.setOutputClause(updateNode.getOutputClause());
309        }
310
311        setResultColumnList(updateNode.getResultColumnList());
312
313        if (updateNode.getUnSetTerms() != null){
314            setUnSetTerms(updateNode.getUnSetTerms());
315            getUnSetTerms().doParse(this,ESqlClause.unSet);
316        }
317
318
319        if (getResultColumnList() != null){
320            getResultColumnList().doParse(this,ESqlClause.set);
321        }
322
323        if (updateNode.getOrderByClause() != null){
324            setOrderByClause(updateNode.getOrderByClause());
325            getOrderByClause().doParse(this,ESqlClause.orderby);
326        }
327
328        if (updateNode.getLimitClause() != null){
329            setLimitClause(updateNode.getLimitClause());
330            getLimitClause().doParse(this,ESqlClause.limit);
331        }
332
333        if (updateNode.getWhereCondition() != null){
334            updateNode.getWhereCondition().doParse(this,ESqlClause.where);
335            this.setWhereClause(updateNode.getWhereCondition());
336        }
337
338        if (updateNode.getReturningClause() != null){
339            updateNode.getReturningClause().doParse(this,ESqlClause.returning);
340            this.setReturningClause(updateNode.getReturningClause());
341        }
342
343        this.optionClause = updateNode.getOptionClause();
344
345        errorLoggingClause = updateNode.getErrorLoggingClause();
346
347        if (updateNode.getInsertSqlNode() != null){
348            insertSqlStatement = new TInsertSqlStatement(this.dbvendor);
349            insertSqlStatement.rootNode = updateNode.getInsertSqlNode();
350            insertSqlStatement.doParseStatement(this);
351
352        }
353
354        // remove table alias A from tables like this SQL:
355        // delete  A
356        // from  myTable A
357        // join otherTable B on A.Id=B.Id
358        int deletedTables[] = new int[this.tables.size()];
359        TTable lcTable = null,lcTable2 = null;
360        for(int i=0;i<this.tables.size();i++){
361            lcTable = this.tables.getTable(i);
362            if ((lcTable.getAliasClause() == null)&&(lcTable.isBaseTable())){
363                for(int j=0;j<this.tables.size();j++){
364                    if (i == j) {continue;}
365                    lcTable2 = this.tables.getTable(j);
366                    if (lcTable2.getAliasClause() != null){
367                        if (lcTable2.getAliasClause().toString().compareToIgnoreCase(lcTable.toString()) == 0){
368                            deletedTables[i] = 1;
369                            //lcTable.setLinkTable(true);
370                            lcTable.setLinkTable(lcTable2);
371//                            for(int k=0;k<lcTable.getObjectNameReferences().size();k++){
372//                                lcTable2.getObjectNameReferences().addObjectName(lcTable.getObjectNameReferences().getObjectName(k));
373//                                //lcTable2.getLinkedColumns().addObjectName(lcTable.getObjectNameReferences().getObjectName(k));
374//                                //lcTable.getObjectNameReferences().getObjectName(k).setSourceTable(lcTable2);
375//                               // System.out.println(lcTable.getObjectNameReferences().getAliasName(k).toString());
376//                            }
377                        }
378                    }
379
380                }
381            }
382        }
383//        for(int i = this.tables.size()-1;i>=0;i--){
384//            if (deletedTables[i] == 1){
385//                //this.tables.removeElementAt(i);
386//            }
387//        }
388        
389        return 0;
390    }
391
392    public void accept(TParseTreeVisitor v){
393        v.preVisit(this);
394        v.postVisit(this);
395    }
396
397    public void acceptChildren(TParseTreeVisitor v){
398        v.preVisit(this);
399
400        if (this.getCteList() != null){
401            this.getCteList().acceptChildren(v);
402        }
403
404
405
406        if (TBaseType.USE_JOINEXPR_INSTEAD_OF_JOIN){
407
408            for(TTable table:getRelations()){
409                table.acceptChildren(v);
410            }
411
412        }else{
413            if (this.getTargetTable() != null){
414                this.getTargetTable().acceptChildren(v);
415            }
416            if (this.joins.size() > 0){
417                this.joins.acceptChildren(v);
418            }
419        }
420
421        if (this.getTopClause() != null){
422            this.getTopClause().acceptChildren(v);
423        }
424
425        if (this.getOutputClause() != null){
426            this.getOutputClause().acceptChildren(v);
427        }
428
429        if (this.getResultColumnList() != null){
430            this.getResultColumnList().acceptChildren(v);
431        }else{
432            TBaseType.log("set clause in update stmt is null",TLog.WARNING);
433        }
434
435
436        if (this.getWhereClause() != null){
437            this.getWhereClause().acceptChildren(v);
438        }
439
440        if (this.getOrderByClause() != null){
441            this.getOrderByClause().acceptChildren(v);
442        }
443
444        if (this.getLimitClause() != null){
445            this.getLimitClause().acceptChildren(v);
446        }
447
448        if (this.getReturningClause() != null){
449            this.getReturningClause().acceptChildren(v);
450        }
451
452        v.postVisit(this);
453    }
454
455    public void setErrorLoggingClause(TErrorLoggingClause errorLoggingClause) {
456        this.errorLoggingClause = errorLoggingClause;
457    }
458
459    public void setOptionClause(TOptionClause optionClause) {
460        this.optionClause = optionClause;
461    }
462
463    public void setReferenceJoins(TJoinList referenceJoins) {
464        this.referenceJoins = referenceJoins;
465    }
466
467    public void setOrderByClause(TOrderBy orderByClause) {
468        this.orderByClause = orderByClause;
469    }
470
471    public void setLimitClause(TLimitClause limitClause) {
472        this.limitClause = limitClause;
473    }
474
475    private TUseKeyIndex useKeyIndex;//couchbase
476
477    public void setUseKeyIndex(TUseKeyIndex useKeyIndex) {
478        this.useKeyIndex = useKeyIndex;
479    }
480
481    public TUseKeyIndex getUseKeyIndex() {
482
483        return useKeyIndex;
484    }
485
486    private TResultColumnList unSetTerms; //couchbase
487
488    public void setUnSetTerms(TResultColumnList unSetTerms) {
489        this.unSetTerms = unSetTerms;
490    }
491
492    /**
493     * Couchbase unset clause
494     * @return Couchbase unset clause
495     */
496    public TResultColumnList getUnSetTerms() {
497
498        return unSetTerms;
499    }
500
501}