001package gudusoft.gsqlparser.stmt;
002
003import gudusoft.gsqlparser.*;
004import gudusoft.gsqlparser.nodes.*;
005import gudusoft.gsqlparser.nodes.EHiveInsertType;
006import gudusoft.gsqlparser.nodes.oracle.TErrorLoggingClause;
007import gudusoft.gsqlparser.stmt.mssql.TMssqlExecute;
008
009import java.util.ArrayList;
010
011/**
012 * SQL insert statement.
013 * <br>
014 * <br> {@link #getTargetTable} returns the table or view that receive the data.
015 * <br> {@link #getColumnList()} returns the list of columns in target table if specified.
016 * <br> Usually, the data comes from a value clause, you can get those values from {@link #getValues()}.
017 * <br> Always checking {@link #getInsertSource} before fetch the data source which may various from value clause
018 * to subquery and other forms. Below are some data source types:
019 * <ul>
020 *     <li>{@link EInsertSource#values} -&gt; {@link #getValues()}</li>
021 *     <li>{@link EInsertSource#subquery} -&gt; {@link #getSubQuery()}</li>
022 * </ul>
023 *
024 * <br>Examples:
025 * <pre>
026 * INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode,ModifiedDate)
027 * VALUES (N'Square Yards', N'Y2', GETDATE());
028 * </pre>
029 * Table name: Production.UnitMeasure. Fetched from {@link #getTargetTable} or the first element of {@link #tables}
030 * <br>column list: (Name, UnitMeasureCode,ModifiedDate). Fetch from {@link #getColumnList}
031 * <br>value list: (N'Square Yards', N'Y2', GETDATE()). Fetch from {@link #getValues}
032 * <br>
033 * <pre>
034 * INSERT INTO dbo.EmployeeSales
035 * SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD
036 * FROM Sales.SalesPerson AS sp
037 * INNER JOIN Person.Person AS c
038 * ON sp.BusinessEntityID = c.BusinessEntityID
039 * WHERE sp.BusinessEntityID LIKE '2%'
040 * ORDER BY sp.BusinessEntityID, c.LastName;
041 * </pre>
042 * select query in above insert statement can be fetched from {@link #getSubQuery}
043 *
044 * @see TCustomSqlStatement#cteList
045 * @see TCustomSqlStatement#targetTable
046 * @see TCustomSqlStatement#outputClause
047 * @see TCustomSqlStatement#returningClause
048 *
049 */
050public class TInsertSqlStatement extends TCustomSqlStatement {
051
052    private ArrayList<TInsertSqlStatement> multiInsertStatements;
053
054    /**
055     * Hive, from table insert..., insert ...
056     * @return
057     */
058    public ArrayList<TInsertSqlStatement> getMultiInsertStatements() {
059        if (this.multiInsertStatements == null){
060            this.multiInsertStatements = new ArrayList<>();
061        }
062        return multiInsertStatements;
063    }
064
065    private String fileFormat;
066    private String fileOptions = null;
067
068    public String getFileFormat() {
069        return fileFormat;
070    }
071
072    public String getFileOptions() {
073        return fileOptions;
074    }
075
076    private TObjectName sourceValueTable;
077
078    public TObjectName getSourceValueTable() {
079        return sourceValueTable;
080    }
081
082    private boolean insertAll = false;
083    private boolean insertFirst = false;
084
085    public void setInsertAll(boolean insertAll) {
086        this.insertAll = insertAll;
087    }
088
089    public void setInsertFirst(boolean insertFirst) {
090        this.insertFirst = insertFirst;
091    }
092
093    /**
094     * Oracle insert all
095     * @return Oracle insert all
096     */
097    public boolean isInsertAll() {
098
099        return insertAll;
100    }
101
102    /**
103     * Oracle insert first
104     * @return Oracle insert first
105     */
106    public boolean isInsertFirst() {
107        return insertFirst;
108    }
109
110    private TErrorLoggingClause errorLoggingClause;
111
112    /**
113     * Oracle error logging clause
114     * @return Oracle error logging clause
115     */
116    public TErrorLoggingClause getErrorLoggingClause() {
117        return errorLoggingClause;
118    }
119
120    /**
121     * Hive insert type
122     * @return Hive insert type
123     */
124    public EHiveInsertType getHiveInsertType() {
125        return hiveInsertType;
126    }
127
128    /**
129     * Hive directory name
130     * @return directory name
131     */
132    public TObjectName getDirectoryName() {
133        if( (directoryName == null)&&(fileOptions != null)){
134            String a[] = fileOptions.split("[,]");
135            for(String s:a){
136                if (s.trim().startsWith("\'path\'")){
137                    directoryName = TObjectName.createObjectName (this.dbvendor, EDbObjectType.directory_path, new TSourceToken(s.split("[ ]")[1]));
138                    break;
139                }
140            }
141        }
142        return directoryName;
143    }
144
145
146    private EHiveInsertType hiveInsertType = EHiveInsertType.intoUnknown;
147    private TObjectName directoryName = null;
148
149    private TPTNodeList<TInsertCondition>  insertConditions;
150    private TPTNodeList<TInsertIntoValue> insertIntoValues;
151
152    /**
153     * Oracle insert condition
154     * @return Oracle insert condition
155     */
156    public TPTNodeList<TInsertCondition> getInsertConditions() {
157        return insertConditions;
158    }
159
160    /**
161     * Oracle insert into values used after insert all/first clause
162     * @return Oracle insert into values used after insert all/first clause
163     */
164    public TPTNodeList<TInsertIntoValue> getInsertIntoValues() {
165        return insertIntoValues;
166    }
167
168    public void setInsertToken(TSourceToken insertToken) {
169        this.insertToken = insertToken;
170    }
171
172    /**
173     * INSERT keyword
174     * @return INSERT keyword
175     */
176    public TSourceToken getInsertToken() {
177
178        return insertToken;
179    }
180
181    private TSourceToken insertToken = null;
182    
183    /**
184     * value clause valid when {@link #getInsertSource} is {@link gudusoft.gsqlparser.EInsertSource#subquery}.
185     * @return {@link gudusoft.gsqlparser.stmt.TSelectSqlStatement sub-query}
186     */
187    public TSelectSqlStatement getSubQuery() {
188        return subQuery;
189    }
190
191    private TSelectSqlStatement subQuery = null;
192
193    private TFunctionCall functionCall = null;
194
195    private TMssqlExecute executeStmt = null;
196
197    /**
198     * value clause valid when {@link #getInsertSource} is {@link gudusoft.gsqlparser.EInsertSource#values_function}.
199     * @return row value was constructed by a function.
200     */
201    public TFunctionCall getFunctionCall() {
202        return functionCall;
203    }
204
205    /**
206     * Oracle PLSQL record name in values clause, {@link #getInsertSource} returns {@link  EInsertSource#values_oracle_record}
207     * @return record name in plsql.
208     */
209    public TObjectName getRecordName() {
210        return recordName;
211    }
212
213    private  TObjectName recordName = null;
214
215    /**
216     * Type of the source from where the data is coming for this insert statement.
217     * @return Type of the data source
218     */
219    public EInsertSource getInsertSource() {
220        return insertSource;
221    }
222
223    private EInsertSource insertSource = EInsertSource.values;
224
225    /**
226     * @deprecated As of v1.6.4.9, use {@link #getInsertSource} instead.
227     *
228     * @return how rows was insert into table. value can be one of
229     * <p>vt_values,  {@link #getValues}
230     * <p>vt_values_empty, syntax like: value ()
231     * <p>vt_query,   {@link #getSubQuery}
232     * <p>vt_default_values,
233     * <p>vt_execute,
234     * <p>vt_values_function, {@link #getFunctionCall} 
235     */
236    public int getValueType() {
237        return valueType;
238    }
239
240    /**
241     * value clause, valid when {@link #getInsertSource} is {@link EInsertSource#values}.
242     * represents in format like this: ((1,2,3),(4,5),(6,7,8)),
243     * if even value clause is (1,2,3), it will be saved in {@link TMultiTargetList} like ((1,2,3))
244     * <br>
245     * @return a single row value, or multi row values returned by query or value constructor.
246     */
247    public TMultiTargetList getValues() {
248        return values;
249    }
250
251    private TMultiTargetList values = null;
252
253    private int valueType = TBaseType.vt_values;
254
255    private TResultColumnList setColumnValues = null;
256
257    private TSourceToken ignore;
258    private TSourceToken priority_delayed;
259
260    private TResultColumnList onDuplicateKeyUpdate = null;
261
262    /**
263     * MySQL on duplicate key update column list.
264     * @return MySQL on duplicate key update column list
265     */
266    public TResultColumnList getOnDuplicateKeyUpdate() {
267        return onDuplicateKeyUpdate;
268    }
269
270    /**
271     * IGNORE keyword used in the insert statement.
272     * @return IGNORE keyword if used, otherwise, returns null.
273     */
274    public TSourceToken getIgnore() {
275
276        return ignore;
277    }
278
279    /**
280     * DELAY, LOW_PRIORITY, HIGH_PRIORITY keyword used in insert statement.
281     * @return null if none of those keywords is used: DELAY, LOW_PRIORITY, HIGH_PRIORITY
282     */
283    public TSourceToken getPriority_delayed() {
284        return priority_delayed;
285    }
286
287    /**
288     * set column value clauses in MySQL insert statement.
289     * @return MySQL specific column value list.
290     */
291    public TResultColumnList getSetColumnValues() {
292        return setColumnValues;
293    }
294
295    public TInsertSqlStatement(EDbVendor dbvendor) {
296        super(dbvendor);
297        sqlstatementtype = ESqlStatementType.sstinsert;
298    }
299
300    void buildsql() {
301    }
302
303    void clear() {
304    }
305
306    String getasprettytext() {
307        return "";
308    }
309
310    void iterate(TVisitorAbs pvisitor) {
311    }
312
313    /**
314     * columns of the target table.
315     * @return column name list in insert into clause.
316     */
317    public TObjectNameList getColumnList() {
318        return columnList;
319    }
320
321    private TObjectNameList columnList = null;
322
323    public void setValues(TMultiTargetList values) {
324        this.values = values;
325    }
326
327    public void setColumnList(TObjectNameList columnList) {
328        this.columnList = columnList;
329    }
330
331    public void setSubQuery(TSelectSqlStatement subQuery) {
332        insertSource = EInsertSource.subquery;
333        this.subQuery = subQuery;
334    }
335
336    /**
337     * SQL Server, execute statement used in the insert statement.
338     * {@link #getInsertSource()} returns {@link EInsertSource#execute}
339     *
340     * @return SQL Server, execute statement
341     */
342    public TMssqlExecute getExecuteStmt() {
343        return executeStmt;
344    }
345
346    private TPTNodeList<TInsertIntoValue> elseIntoValues;
347
348    /**
349     * Oracle,  values in else clause
350     * @return values in else clause
351     */
352    public TPTNodeList<TInsertIntoValue> getElseIntoValues() {
353        return elseIntoValues;
354    }
355
356    public int doParseStatement(TCustomSqlStatement psql) {
357        if (rootNode == null) return -1;
358        TInsertSqlNode insertNode = (TInsertSqlNode)rootNode;
359
360        if (this.sourcetokenlist.size() == 0){
361            // subquery nested in other statements.
362            this.setStartToken(insertNode.getStartToken());
363            this.setEndToken(insertNode.getEndToken());
364        }
365
366        super.doParseStatement(psql);
367
368        this.insertToken = insertNode.getInsertToken();
369        this.ignore = insertNode.getIgnore();
370        this.priority_delayed = insertNode.getPriority_delayed();
371        this.insertAll = insertNode.isInsertAll();
372        this.insertFirst = insertNode.isInsertFirst();
373        this.hiveInsertType = insertNode.getHiveInsertType();
374
375        this.valueType = insertNode.getValueType();
376
377        if((dbvendor == EDbVendor.dbvhive)||(dbvendor == EDbVendor.dbvsparksql)){
378
379            this.hiveInsertType = insertNode.getHiveInsertType();
380            this.directoryName = insertNode.getDirectoryName();
381            this.fileFormat = insertNode.getFileFormat();
382            this.fileOptions = insertNode.getFileOptions();
383            if (insertNode.getInsertSqlNodes() != null){
384                for(int i=1;i<insertNode.getInsertSqlNodes().size();i++){
385                    // i=1, start from the second insert statement, the first is this insert statement itself.
386                    TInsertSqlStatement newInsert = new TInsertSqlStatement(this.dbvendor);
387                    newInsert.rootNode = insertNode.getInsertSqlNodes().get(i);
388                    newInsert.setFrameStack(this.getFrameStack());
389                    newInsert.doParseStatement(psql);
390                    this.getMultiInsertStatements().add(newInsert);
391                }
392            }
393        }
394
395        if (insertNode.cteList != null){
396            this.setCteList( insertNode.cteList);
397            this.getCteList().doParse(this, ESqlClause.cte);
398        }
399
400
401        if (insertNode.getTopClause() != null){
402           insertNode.getTopClause().doParse(this,ESqlClause.top);
403           this.setTopClause(insertNode.getTopClause());
404        }
405
406        if ((this.valueType != TBaseType.vt_values_multi_table)
407                &&(insertNode.getTargetTable() != null)
408            )
409        {
410           // System.out.println(insertNode.getTargetTable().toString());
411           TTable lcTable = analyzeFromTable(insertNode.getTargetTable(),true);
412           lcTable.setEffectType(ETableEffectType.tetInsert);
413           setTargetTable(lcTable);
414           this.getRelations().add(lcTable);
415        }
416
417        if (insertNode.getColumnList() != null){
418            this.columnList = insertNode.getColumnList(); 
419            TObjectName crf ;
420            for (int i=0;i< insertNode.getColumnList().size();i++){
421                crf =insertNode.getColumnList().getObjectName(i);
422                // link this column to last 2 tables
423                crf.setLocation(ESqlClause.insertColumn);
424                getTargetTable().getObjectNameReferences().addObjectName(crf);
425                getTargetTable().getLinkedColumns().addObjectName(crf);
426                crf.setSourceTable(getTargetTable());
427            }
428
429            //insertNode.getColumnList().doParse(this,TBaseType.insertColumnClause);
430        }
431
432        if (insertNode.getOutputClause() != null){
433            insertNode.getOutputClause().doParse(this,ESqlClause.output);
434            this.setOutputClause(insertNode.getOutputClause());
435        }
436
437        switch(valueType){
438            case TBaseType.vt_values:
439                insertSource = EInsertSource.values;
440                insertNode.getValues().doParse(this,ESqlClause.insertValues);
441                this.values = insertNode.getValues();
442                // check is count of value list is the same as column list
443                if (columnList != null){
444                    TMultiTarget mt;
445                    TSourceToken st1;
446                    for(int k=0;k<values.size();k++){
447                        mt = values.getMultiTarget(k);
448                        if (mt.getColumnList() == null) continue;
449                        if (mt.getColumnList().size() == 0) continue;
450                        if (mt.getColumnList().getResultColumn(0).getExpr() == null) continue;
451                        if (mt.getColumnList().getResultColumn(0).getExpr().getExpressionType() == EExpressionType.subquery_t) continue;
452                        if (mt.getColumnList().size() != columnList.size()){
453                            st1 = mt.getColumnList().getStartToken();
454                            TSyntaxError err = new TSyntaxError(st1.toString()
455                                    ,st1.lineNo,st1.columnNo
456                                    ,String.format("value count(%d) is not the same as column list(%d)",mt.getColumnList().size(),columnList.size())
457                                    ,EErrorType.sperror ,TBaseType.MSG_ERROR_INSERT_VALUE_COLUMN_NUMBER_NOT_MATCH,this,st1.posinlist);
458                            this.parseerrormessagehandle( err);
459                        }
460                    }
461                }
462                break;
463            case TBaseType.vt_values_empty:
464                insertSource = EInsertSource.values_empty;
465                break;
466            case TBaseType.vt_values_multi_table:
467                insertSource = EInsertSource.values_multi_table;
468                if (subQuery == null){
469                    subQuery = new TSelectSqlStatement(this.dbvendor);
470                    subQuery.rootNode = insertNode.getSubQueryNode();
471                }
472                subQuery.doParseStatement(this);
473
474                break;
475            case TBaseType.vt_query:
476                insertSource = EInsertSource.subquery;
477                if (subQuery == null){
478                    subQuery = new TSelectSqlStatement(this.dbvendor);
479                    subQuery.rootNode = insertNode.getSubQueryNode();
480                }
481                subQuery.doParseStatement(this);
482                break;
483            case TBaseType.vt_hive_query:
484                insertSource = EInsertSource.hive_query;
485                if (subQuery == null){
486                    subQuery = new TSelectSqlStatement(this.dbvendor);
487                    subQuery.rootNode = insertNode.getSubQueryNode();
488                }
489                subQuery.doParseStatement(this);
490                break;
491            case TBaseType.vt_default_values:
492                insertSource = EInsertSource.default_values;
493                break;
494            case TBaseType.vt_execute:
495                insertSource = EInsertSource.execute;
496                executeStmt = new TMssqlExecute(EDbVendor.dbvmssql);
497                executeStmt.rootNode = insertNode.getExecuteSqlNode();
498                executeStmt.doParseStatement(this);
499                break;
500            case TBaseType.vt_values_function:
501                insertSource = EInsertSource.values_function;
502                insertNode.getFunctionCall().doParse(this,ESqlClause.insertValues);
503
504                this.functionCall = insertNode.getFunctionCall(); 
505                break;
506            case TBaseType.vt_values_oracle_record:
507                insertSource = EInsertSource.values_oracle_record;
508                insertNode.getRecordName().doParse(this,ESqlClause.insertValues);
509                this.recordName = insertNode.getRecordName();
510                break;
511            case TBaseType.vt_set_column_value:
512                insertSource = EInsertSource.set_column_value;
513                this.setColumnValues = insertNode.getSetColumnValues();
514                this.setColumnValues.doParse(this,ESqlClause.insertValues);
515                break;
516            case TBaseType.vt_table:
517                insertSource = EInsertSource.value_table;
518                this.sourceValueTable = insertNode.getSourceValueTable();
519                break;
520        }
521
522        if (insertNode.getInsertConditions() != null){
523            this.valueType = TBaseType.vt_values_multi_table;
524            this.insertConditions = insertNode.getInsertConditions();
525            this.insertConditions.doParse(this,ESqlClause.insertValues);
526            setTargetTable(insertConditions.getElement(0).getInsertIntoValues().getElement(0).getTable() );
527        }
528
529        if (insertNode.getInsertIntoValues() != null){
530            this.valueType = TBaseType.vt_values_multi_table;
531            this.insertIntoValues = insertNode.getInsertIntoValues();
532            this.insertIntoValues.doParse(this,ESqlClause.insertValues);
533            setTargetTable(insertIntoValues.getElement(0).getTable());
534        }
535
536        if (insertNode.getElseIntoValues() != null){
537            this.valueType = TBaseType.vt_values_multi_table;
538            this.elseIntoValues = insertNode.getElseIntoValues();
539            this.elseIntoValues.doParse(this,ESqlClause.insertValues);
540            setTargetTable(elseIntoValues.getElement(0).getTable());
541        }
542
543        if (insertNode.getReturningClause() != null){
544            insertNode.getReturningClause().doParse(this,ESqlClause.returning);
545            this.setReturningClause(insertNode.getReturningClause());
546        }
547
548        if (insertNode.getOnDuplicateKeyUpdate() != null){
549            this.onDuplicateKeyUpdate = insertNode.getOnDuplicateKeyUpdate();
550            onDuplicateKeyUpdate.doParse(this,ESqlClause.unknown);
551        }
552
553        errorLoggingClause = insertNode.getErrorLoggingClause();
554        return 0;
555    }
556
557    public void accept(TParseTreeVisitor v){
558        v.preVisit(this);
559
560        v.postVisit(this);
561    }
562
563    public void acceptChildren(TParseTreeVisitor v){
564        v.preVisit(this);
565
566        if (this.getCteList() != null){
567            this.getCteList().acceptChildren(v);
568        }
569
570        if (this.getTopClause() != null){
571            this.getTopClause().acceptChildren(v);
572        }
573
574        if (this.getTargetTable() != null){
575            this.getTargetTable().acceptChildren(v);
576        }
577
578        if (this.getColumnList() != null){
579            this.getColumnList().acceptChildren(v);
580        }
581
582        if (this.getOutputClause() != null){
583            this.getOutputClause().acceptChildren(v);
584        }
585
586        switch(this.getValueType()){
587            case TBaseType.vt_values:
588                this.getValues().acceptChildren(v);
589                break;
590            case TBaseType.vt_values_empty:
591                break;
592            case TBaseType.vt_query:
593                this.getSubQuery().acceptChildren(v);
594                break;
595            case TBaseType.vt_values_function:
596                this.getFunctionCall().acceptChildren(v);
597                break;
598            case TBaseType.vt_values_oracle_record:
599                this.getRecordName().acceptChildren(v);
600                break;
601            case TBaseType.vt_set_column_value:
602                this.getSetColumnValues().acceptChildren(v);
603                break;
604            default:
605                break;
606        }
607
608        if (this.getReturningClause() != null){
609            this.getReturningClause().acceptChildren(v);
610        }
611
612        v.postVisit(this);
613    }
614
615    public void setErrorLoggingClause(TErrorLoggingClause errorLoggingClause) {
616        this.errorLoggingClause = errorLoggingClause;
617    }
618
619    public void setHiveInsertType(EHiveInsertType hiveInsertType) {
620        this.hiveInsertType = hiveInsertType;
621    }
622
623    public void setDirectoryName(TObjectName directoryName) {
624        this.directoryName = directoryName;
625    }
626
627    public void setInsertConditions(TPTNodeList<TInsertCondition> insertConditions) {
628        this.insertConditions = insertConditions;
629    }
630
631    public void setInsertIntoValues(TPTNodeList<TInsertIntoValue> insertIntoValues) {
632        this.insertIntoValues = insertIntoValues;
633    }
634
635    public void setFunctionCall(TFunctionCall functionCall) {
636        this.functionCall = functionCall;
637    }
638
639    public void setExecuteStmt(TMssqlExecute executeStmt) {
640        this.executeStmt = executeStmt;
641    }
642
643    public void setRecordName(TObjectName recordName) {
644        this.recordName = recordName;
645    }
646
647    public void setInsertSource(EInsertSource insertSource) {
648        this.insertSource = insertSource;
649    }
650
651    public void setValueType(int valueType) {
652        this.valueType = valueType;
653    }
654
655    public void setSetColumnValues(TResultColumnList setColumnValues) {
656        this.setColumnValues = setColumnValues;
657    }
658
659    public void setOnDuplicateKeyUpdate(TResultColumnList onDuplicateKeyUpdate) {
660        this.onDuplicateKeyUpdate = onDuplicateKeyUpdate;
661    }
662}