/* * DBeaver - Universal Database Manager * Copyright (C) 2010-2017 Serge Rider (serge@jkiss.org) * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package org.jkiss.dbeaver.model.sql; import net.sf.jsqlparser.parser.CCJSqlParserUtil; import net.sf.jsqlparser.schema.Database; import net.sf.jsqlparser.schema.Table; import net.sf.jsqlparser.statement.Statement; import net.sf.jsqlparser.statement.alter.Alter; import net.sf.jsqlparser.statement.create.index.CreateIndex; import net.sf.jsqlparser.statement.create.table.CreateTable; import net.sf.jsqlparser.statement.create.view.CreateView; import net.sf.jsqlparser.statement.delete.Delete; import net.sf.jsqlparser.statement.drop.Drop; import net.sf.jsqlparser.statement.insert.Insert; import net.sf.jsqlparser.statement.select.PlainSelect; import net.sf.jsqlparser.statement.select.Select; import net.sf.jsqlparser.statement.select.SelectBody; import net.sf.jsqlparser.statement.select.SelectItem; import net.sf.jsqlparser.statement.update.Update; import org.jkiss.code.NotNull; import org.jkiss.code.Nullable; import org.jkiss.dbeaver.model.DBPDataSource; import org.jkiss.dbeaver.model.DBUtils; import org.jkiss.dbeaver.model.exec.DBCAttributeMetaData; import org.jkiss.dbeaver.model.exec.DBCEntityMetaData; import org.jkiss.utils.CommonUtils; import java.util.*; import java.util.regex.Matcher; import java.util.regex.Pattern; /** * SQLQuery */ public class SQLQuery implements SQLScriptElement { private static final Pattern QUERY_TITLE_PATTERN = Pattern.compile("(?:--|/\\*)\\s*(?:NAME|TITLE)\\s*:\\s*(.+)\\s*", Pattern.CASE_INSENSITIVE); @Nullable private final DBPDataSource dataSource; @NotNull private String originalText; @NotNull private String text; private int offset; private int length; private Object data; @NotNull private SQLQueryType type; @Nullable private Statement statement; private List<SQLQueryParameter> parameters; private SingleTableMeta singleTableMeta; private List<SQLSelectItem> selectItems; private String queryTitle; public SQLQuery(@Nullable DBPDataSource dataSource, @NotNull String text) { this(dataSource, text, 0, text.length()); } /** * Copy constructor. * Copies query state but sets new query string. */ public SQLQuery(@Nullable DBPDataSource dataSource, @NotNull String text, @NotNull SQLQuery sourceQuery) { this(dataSource, text, sourceQuery, true); } public SQLQuery(@Nullable DBPDataSource dataSource, @NotNull String text, @NotNull SQLQuery sourceQuery, boolean preserveOriginal) { this(dataSource, text, sourceQuery.offset, sourceQuery.length); if (preserveOriginal) { this.originalText = sourceQuery.originalText; } this.parameters = sourceQuery.parameters; this.data = sourceQuery.data; } public SQLQuery(@Nullable DBPDataSource dataSource, @NotNull String text, int offset, int length) { this.dataSource = dataSource; this.originalText = this.text = text; this.offset = offset; this.length = length; try { statement = CCJSqlParserUtil.parse(text); if (statement instanceof Select) { type = SQLQueryType.SELECT; // Detect single source table SelectBody selectBody = ((Select) statement).getSelectBody(); if (selectBody instanceof PlainSelect) { PlainSelect plainSelect = (PlainSelect) selectBody; if (plainSelect.getFromItem() instanceof Table && CommonUtils.isEmpty(plainSelect.getJoins()) && CommonUtils.isEmpty(plainSelect.getGroupByColumnReferences()) && CommonUtils.isEmpty(plainSelect.getIntoTables())) { Table fromItem = (Table) plainSelect.getFromItem(); Database database = fromItem.getDatabase(); String catalogName = database == null ? null : database.getDatabaseName(); String schemaName = fromItem.getSchemaName(); String tableName = fromItem.getName(); singleTableMeta = new SingleTableMeta( unquoteIdentifier(catalogName), unquoteIdentifier(schemaName), unquoteIdentifier(tableName)); } // Extract select items info final List<SelectItem> items = plainSelect.getSelectItems(); if (items != null && !items.isEmpty()) { selectItems = new ArrayList<>(); for (SelectItem item : items) { selectItems.add(new SQLSelectItem(item)); } } } } else if (statement instanceof Insert) { type = SQLQueryType.INSERT; } else if (statement instanceof Update) { type = SQLQueryType.UPDATE; } else if (statement instanceof Delete) { type = SQLQueryType.DELETE; } else if (statement instanceof Alter || statement instanceof CreateTable || statement instanceof CreateView || statement instanceof Drop || statement instanceof CreateIndex) { type = SQLQueryType.DDL; } else { type = SQLQueryType.UNKNOWN; } } catch (Throwable e) { this.type = SQLQueryType.UNKNOWN; //log.debug("Error parsing SQL query [" + query + "]:" + CommonUtils.getRootCause(e).getMessage()); } // Extract query title queryTitle = null; final Matcher matcher = QUERY_TITLE_PATTERN.matcher(text); if (matcher.find()) { queryTitle = matcher.group(1); } } private String unquoteIdentifier(String name) { if (name == null) { return null; } return DBUtils.getUnQuotedIdentifier(dataSource, name); } /** * Plain select is a SELECT statement without INTO clause, without LIMIT or TOP modifiers * @return true is this query is a plain select */ public boolean isPlainSelect() { if (statement instanceof Select && ((Select) statement).getSelectBody() instanceof PlainSelect) { PlainSelect selectBody = (PlainSelect) ((Select) statement).getSelectBody(); return selectBody.getFromItem() != null && CommonUtils.isEmpty(selectBody.getIntoTables()) && selectBody.getLimit() == null && selectBody.getTop() == null && !selectBody.isForUpdate(); } return false; } public SQLSelectItem getSelectItem(String name) { if (selectItems == null) { return null; } for (SQLSelectItem item : selectItems) { if (item.getName().equals(name)) { return item; } } return null; } public SQLSelectItem getSelectItem(int index) { return selectItems == null || selectItems.size() <= index ? null : selectItems.get(index); } @NotNull public String getOriginalText() { return originalText; } @NotNull public String getText() { return text; } public void setText(@NotNull String text) { this.text = text; } public String getQueryTitle() { return queryTitle; } @Nullable public Statement getStatement() { return statement; } public List<SQLQueryParameter> getParameters() { return parameters; } public int getOffset() { return offset; } public int getLength() { return length; } /** * User defined data object. May be used to identify statements. * @return data or null */ public Object getData() { return data; } public void setData(Object data) { this.data = data; } @NotNull public SQLQueryType getType() { return type; } public DBCEntityMetaData getSingleSource() { return singleTableMeta; } public void setParameters(List<SQLQueryParameter> parameters) { this.parameters = parameters; } public void reset() { this.text = this.originalText; if (this.parameters != null) { setParameters(this.parameters); } } @Override public String toString() { return text; } private static class SingleTableMeta implements DBCEntityMetaData { private final String catalogName; private final String schemaName; private final String tableName; private SingleTableMeta(String catalogName, String schemaName, @NotNull String tableName) { this.catalogName = catalogName; this.schemaName = schemaName; this.tableName = tableName; } @Nullable @Override public String getCatalogName() { return catalogName; } @Nullable @Override public String getSchemaName() { return schemaName; } @NotNull @Override public String getEntityName() { return tableName; } @NotNull @Override public List<? extends DBCAttributeMetaData> getAttributes() { return Collections.emptyList(); } @Override public String toString() { return DBUtils.getSimpleQualifiedName(catalogName, schemaName, tableName); } @Override public int hashCode() { return (catalogName == null ? 1 : catalogName.hashCode()) * (schemaName == null ? 2 : schemaName.hashCode()) * tableName.hashCode(); } @Override public boolean equals(Object obj) { if (!(obj instanceof SingleTableMeta)) { return false; } SingleTableMeta md2 = (SingleTableMeta) obj; return CommonUtils.equalObjects(catalogName, md2.catalogName) && CommonUtils.equalObjects(schemaName, md2.schemaName) && CommonUtils.equalObjects(tableName, md2.tableName); } } @Override public boolean equals(Object obj) { return obj instanceof SQLQuery && text.equals(((SQLQuery) obj).text); } }