/*
* JBoss, Home of Professional Open Source.
* See the COPYRIGHT.txt file distributed with this work for information
* regarding copyright ownership. Some portions may be licensed
* to Red Hat, Inc. under one or more contributor license agreements.
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
* 02110-1301 USA.
*/
/*
*/
package org.teiid.translator.jdbc.sqlserver;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import org.teiid.core.util.StringUtil;
import org.teiid.language.*;
import org.teiid.metadata.Column;
import org.teiid.metadata.MetadataFactory;
import org.teiid.metadata.Table;
import org.teiid.translator.ExecutionContext;
import org.teiid.translator.MetadataProcessor;
import org.teiid.translator.SourceSystemFunctions;
import org.teiid.translator.Translator;
import org.teiid.translator.TranslatorException;
import org.teiid.translator.TypeFacility;
import org.teiid.translator.jdbc.AliasModifier;
import org.teiid.translator.jdbc.ConvertModifier;
import org.teiid.translator.jdbc.FunctionModifier;
import org.teiid.translator.jdbc.JDBCExecutionFactory;
import org.teiid.translator.jdbc.JDBCMetdataProcessor;
import org.teiid.translator.jdbc.TemplateFunctionModifier;
import org.teiid.translator.jdbc.sybase.SybaseExecutionFactory;
import org.teiid.util.Version;
/**
* Updated to assume the use of the DataDirect, 2005 driver, or later.
*/
@Translator(name="sqlserver", description="A translator for Microsoft SQL Server Database")
public class SQLServerExecutionFactory extends SybaseExecutionFactory {
public static final String V_2000 = "2000"; //$NON-NLS-1$
public static final String V_2005 = "2005"; //$NON-NLS-1$
public static final String V_2008 = "2008"; //$NON-NLS-1$
public static final String V_2012 = "2012"; //$NON-NLS-1$
public static final Version SEVEN_0 = Version.getVersion("7.0"); //$NON-NLS-1$
public static final Version NINE_0 = Version.getVersion("9.0"); //$NON-NLS-1$
public static final Version TEN_0 = Version.getVersion("10.0"); //$NON-NLS-1$
public static final Version ELEVEN_0 = Version.getVersion("11.0"); //$NON-NLS-1$
//TEIID-31 remove mod modifier for SQL Server 2008
public SQLServerExecutionFactory() {
setSupportsFullOuterJoins(true);
setMaxInCriteriaSize(JDBCExecutionFactory.DEFAULT_MAX_IN_CRITERIA);
setMaxDependentInPredicates(2);
}
@Override
public void start() throws TranslatorException {
super.start();
registerFunctionModifier(SourceSystemFunctions.WEEK, new FunctionModifier() {
@Override
public List<?> translate(Function function) {
return Arrays.asList("DATEPART(ISO_WEEK, ", function.getParameters().get(0), ")"); //$NON-NLS-1$ //$NON-NLS-2$
}
});
registerFunctionModifier(SourceSystemFunctions.LOCATE, new AliasModifier("CHARINDEX")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.MD5, new TemplateFunctionModifier("HASHBYTES('MD5', ", 0, ")")); //$NON-NLS-1$ //$NON-NLS-2$
registerFunctionModifier(SourceSystemFunctions.SHA1, new TemplateFunctionModifier("HASHBYTES('SHA1', ", 0, ")")); //$NON-NLS-1$ //$NON-NLS-2$
registerFunctionModifier(SourceSystemFunctions.SHA2_256, new TemplateFunctionModifier("HASHBYTES('SHA2_256', ", 0, ")")); //$NON-NLS-1$ //$NON-NLS-2$
registerFunctionModifier(SourceSystemFunctions.SHA2_512, new TemplateFunctionModifier("HASHBYTES('SHA2_512', ", 0, ")")); //$NON-NLS-1$ //$NON-NLS-2$
}
@Override
public void initCapabilities(Connection connection)
throws TranslatorException {
super.initCapabilities(connection);
if (getVersion().compareTo(TEN_0) >= 0) {
//date support
convertModifier.addTypeMapping("date", FunctionModifier.DATE); //$NON-NLS-1$
formatMap.put("yyyy-MM-dd", "DATE"); //$NON-NLS-1$ //$NON-NLS-2$
convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.DATE, new FunctionModifier() {
@Override
public List<?> translate(Function function) {
List<Object> result = new ArrayList<Object>();
result.add("cast("); //$NON-NLS-1$
result.add(function.getParameters().get(0));
result.add(" AS DATE)"); //$NON-NLS-1$
return result;
}
});
//timestamp/datetime2
convertModifier.addTypeMapping("datetime2", FunctionModifier.TIMESTAMP); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.PARSETIMESTAMP, new SybaseFormatFunctionModifier("CONVERT(DATETIME2, ", formatMap)); //$NON-NLS-1$
}
}
@Override
protected void populateDateFormats() {
formatMap.put("MM/dd/yy", 1); //$NON-NLS-1$
formatMap.put("yy.MM.dd", 2); //$NON-NLS-1$
formatMap.put("dd/MM/yy", 3); //$NON-NLS-1$
formatMap.put("dd.MM.yy", 4); //$NON-NLS-1$
formatMap.put("dd-MM-yy", 5); //$NON-NLS-1$
formatMap.put("dd MMM yy", 6); //$NON-NLS-1$
formatMap.put("MMM dd, yy", 7); //$NON-NLS-1$
formatMap.put("MM-dd-yy", 10); //$NON-NLS-1$
formatMap.put("yy/MM/dd", 11); //$NON-NLS-1$
formatMap.put("yyMMdd", 12); //$NON-NLS-1$
for (Map.Entry<String, Object> entry : new HashSet<Map.Entry<String, Object>>(formatMap.entrySet())) {
formatMap.put(entry.getKey().replace("yy", "yyyy"), (Integer)entry.getValue() + 100); //$NON-NLS-1$ //$NON-NLS-2$
}
formatMap.put("MMM d yyyy hh:mma", 100); //$NON-NLS-1$
formatMap.put("HH:mm:ss", 8); //$NON-NLS-1$
formatMap.put("MMM d yyyy hh:mm:ss:SSSa", 109); //$NON-NLS-1$
formatMap.put("dd MMM yyyy HH:mm:ss:SSS", 113); //$NON-NLS-1$
formatMap.put("kk:MM:ss:SSS", 14); //$NON-NLS-1$
formatMap.put("yyyy-MM-dd HH:mm:ss", 120); //$NON-NLS-1$
formatMap.put("yyyy-MM-dd HH:mm:ss.SSS", 121); //$NON-NLS-1$
formatMap.put("yyyy-MM-dd'T'HH:mm:ss.SSS", 126); //$NON-NLS-1$
//formatMap.put("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'", 127); //$NON-NLS-1$
}
@Override
protected List<Object> convertDateToString(Function function) {
if (getVersion().compareTo(TEN_0) >= 0) {
return Arrays.asList("convert(varchar, ", function.getParameters().get(0), ")"); //$NON-NLS-1$ //$NON-NLS-2$
}
return Arrays.asList("replace(convert(varchar, ", function.getParameters().get(0), ", 102), '.', '-')"); //$NON-NLS-1$ //$NON-NLS-2$
}
@Override
protected List<?> convertTimestampToString(Function function) {
return Arrays.asList("convert(varchar, ", function.getParameters().get(0), ", 21)"); //$NON-NLS-1$ //$NON-NLS-2$
}
@Override
public List<?> translate(LanguageObject obj, ExecutionContext context) {
if (obj instanceof ColumnReference) {
ColumnReference elem = (ColumnReference)obj;
if (getVersion().compareTo(SEVEN_0) <= 0
&& TypeFacility.RUNTIME_TYPES.STRING.equals(elem.getType()) && elem.getMetadataObject() != null && "uniqueidentifier".equalsIgnoreCase(elem.getMetadataObject().getNativeType())) { //$NON-NLS-1$
return Arrays.asList("cast(", elem, " as char(36))"); //$NON-NLS-1$ //$NON-NLS-2$
}
} else if (obj instanceof AggregateFunction) {
AggregateFunction af = (AggregateFunction)obj;
if (af.getName().equals(AggregateFunction.STDDEV_POP)) {
af.setName("STDDEVP"); //$NON-NLS-1$
} else if (af.getName().equals(AggregateFunction.STDDEV_SAMP)) {
af.setName("STDDEV"); //$NON-NLS-1$
} else if (af.getName().equals(AggregateFunction.VAR_POP)) {
af.setName("VARP"); //$NON-NLS-1$
} else if (af.getName().equals(AggregateFunction.VAR_SAMP)) {
af.setName("VAR"); //$NON-NLS-1$
}
} else if (obj instanceof WithItem) {
WithItem withItem = (WithItem)obj;
//unlike normal unions, recursive cte require additional type handling
if (withItem.isRecusive()) {
List<DerivedColumn> derivedColumns = withItem.getSubquery().getProjectedQuery().getDerivedColumns();
List<DerivedColumn> derivedColumnsRecurse = ((SetQuery)withItem.getSubquery()).getRightQuery().getProjectedQuery().getDerivedColumns();
for (int i = 0; i < derivedColumns.size(); i++) {
String nativeType = null;
boolean castLeft = true;
boolean castRight = true;
DerivedColumn dc = derivedColumns.get(i);
if (dc.getExpression() instanceof ColumnReference) {
Column c = ((ColumnReference)dc.getExpression()).getMetadataObject();
if (c != null && c.getNativeType() != null) {
nativeType = c.getNativeType();
castLeft = false;
}
}
DerivedColumn dcR = derivedColumnsRecurse.get(i);
if (dcR.getExpression() instanceof ColumnReference) {
Column c = ((ColumnReference)dcR.getExpression()).getMetadataObject();
if (c != null) {
if (nativeType == null) {
if (c.getNativeType() != null) {
nativeType = c.getNativeType();
castRight = false;
}
} else {
if (nativeType.equals(c.getNativeType())) {
continue; //it matches
}
//we won't gracefully handle this case, we'll just assume the first type
}
}
}
if (castLeft) {
addCast(nativeType, dc);
}
if (castRight) {
addCast(nativeType, dcR);
}
}
}
}
return super.translate(obj, context);
}
private void addCast(String nativeType, DerivedColumn dc) {
if (nativeType != null) {
Function cast = ConvertModifier.createConvertFunction(getLanguageFactory(), dc.getExpression(), nativeType);
cast.setName("cast"); //$NON-NLS-1$
dc.setExpression(cast);
} else {
dc.setExpression(ConvertModifier.createConvertFunction(getLanguageFactory(), dc.getExpression(), TypeFacility.getDataTypeName(dc.getExpression().getType())));
}
}
@Override
public List<String> getSupportedFunctions() {
List<String> supportedFunctions = new ArrayList<String>();
supportedFunctions.addAll(super.getDefaultSupportedFunctions());
supportedFunctions.add("ABS"); //$NON-NLS-1$
supportedFunctions.add("ACOS"); //$NON-NLS-1$
supportedFunctions.add("ASIN"); //$NON-NLS-1$
supportedFunctions.add("ATAN"); //$NON-NLS-1$
supportedFunctions.add("ATAN2"); //$NON-NLS-1$
supportedFunctions.add("COS"); //$NON-NLS-1$
supportedFunctions.add("COT"); //$NON-NLS-1$
supportedFunctions.add("DEGREES"); //$NON-NLS-1$
supportedFunctions.add("EXP"); //$NON-NLS-1$
supportedFunctions.add("FLOOR"); //$NON-NLS-1$
supportedFunctions.add("LOG"); //$NON-NLS-1$
supportedFunctions.add("LOG10"); //$NON-NLS-1$
supportedFunctions.add("MOD"); //$NON-NLS-1$
supportedFunctions.add("PI"); //$NON-NLS-1$
supportedFunctions.add("POWER"); //$NON-NLS-1$
supportedFunctions.add("RADIANS"); //$NON-NLS-1$
supportedFunctions.add("SIGN"); //$NON-NLS-1$
supportedFunctions.add("SIN"); //$NON-NLS-1$
supportedFunctions.add("SQRT"); //$NON-NLS-1$
supportedFunctions.add("TAN"); //$NON-NLS-1$
supportedFunctions.add("ASCII"); //$NON-NLS-1$
supportedFunctions.add("CHAR"); //$NON-NLS-1$
supportedFunctions.add("CHR"); //$NON-NLS-1$
supportedFunctions.add("CONCAT"); //$NON-NLS-1$
supportedFunctions.add("||"); //$NON-NLS-1$
//supportedFunctons.add("INITCAP"); //$NON-NLS-1$
supportedFunctions.add("LCASE"); //$NON-NLS-1$
supportedFunctions.add("LEFT"); //$NON-NLS-1$
supportedFunctions.add("LENGTH"); //$NON-NLS-1$
supportedFunctions.add(SourceSystemFunctions.LOCATE);
supportedFunctions.add("LOWER"); //$NON-NLS-1$
supportedFunctions.add("LPAD"); //$NON-NLS-1$
supportedFunctions.add("LTRIM"); //$NON-NLS-1$
supportedFunctions.add("REPEAT"); //$NON-NLS-1$
//supportedFunctions.add("RAND"); //$NON-NLS-1$
supportedFunctions.add("REPLACE"); //$NON-NLS-1$
supportedFunctions.add("RIGHT"); //$NON-NLS-1$
supportedFunctions.add("RPAD"); //$NON-NLS-1$
supportedFunctions.add("RTRIM"); //$NON-NLS-1$
supportedFunctions.add("SPACE"); //$NON-NLS-1$
supportedFunctions.add("SUBSTRING"); //$NON-NLS-1$
//supportedFunctons.add("TRANSLATE"); //$NON-NLS-1$
supportedFunctions.add("UCASE"); //$NON-NLS-1$
supportedFunctions.add("UPPER"); //$NON-NLS-1$
//supportedFunctons.add("CURDATE"); //$NON-NLS-1$
//supportedFunctons.add("CURTIME"); //$NON-NLS-1$
supportedFunctions.add("DAYNAME"); //$NON-NLS-1$
supportedFunctions.add("DAYOFMONTH"); //$NON-NLS-1$
supportedFunctions.add("DAYOFWEEK"); //$NON-NLS-1$
supportedFunctions.add("DAYOFYEAR"); //$NON-NLS-1$
supportedFunctions.add("HOUR"); //$NON-NLS-1$
supportedFunctions.add("MINUTE"); //$NON-NLS-1$
supportedFunctions.add("MONTH"); //$NON-NLS-1$
supportedFunctions.add("MONTHNAME"); //$NON-NLS-1$
//supportedFunctions.add("NOW"); //$NON-NLS-1$
supportedFunctions.add("QUARTER"); //$NON-NLS-1$
supportedFunctions.add("SECOND"); //$NON-NLS-1$
supportedFunctions.add("TIMESTAMPADD"); //$NON-NLS-1$
supportedFunctions.add("TIMESTAMPDIFF"); //$NON-NLS-1$
supportedFunctions.add("WEEK"); //$NON-NLS-1$
supportedFunctions.add("YEAR"); //$NON-NLS-1$
supportedFunctions.add("CAST"); //$NON-NLS-1$
supportedFunctions.add("CONVERT"); //$NON-NLS-1$
supportedFunctions.add("IFNULL"); //$NON-NLS-1$
supportedFunctions.add("NVL"); //$NON-NLS-1$
supportedFunctions.add(SourceSystemFunctions.FORMATTIMESTAMP);
supportedFunctions.add(SourceSystemFunctions.PARSETIMESTAMP);
if (getVersion().compareTo(TEN_0) >= 0) {
supportedFunctions.add(SourceSystemFunctions.SHA2_256);
supportedFunctions.add(SourceSystemFunctions.SHA2_512);
}
if (getVersion().compareTo(NINE_0) >= 0) {
supportedFunctions.add(SourceSystemFunctions.MD5);
supportedFunctions.add(SourceSystemFunctions.SHA1);
}
return supportedFunctions;
}
@Override
public boolean supportsInlineViews() {
return true;
}
@Override
public boolean supportsFunctionsInGroupBy() {
return true;
}
@Override
public boolean supportsRowLimit() {
return true;
}
@Override
public boolean supportsRowOffset() {
return getVersion().compareTo(TEN_0) >= 0;
}
@Override
public boolean supportsIntersect() {
return true;
}
@Override
public boolean supportsExcept() {
return true;
};
@Override
public int getMaxFromGroups() {
return DEFAULT_MAX_FROM_GROUPS;
}
@Override
public boolean supportsAggregatesEnhancedNumeric() {
return true;
}
@Override
public boolean nullPlusNonNullIsNull() {
return true;
}
@Override
public boolean booleanNullable() {
return true;
}
/**
* Overridden to allow for year based versions
*/
@Override
public void setDatabaseVersion(String version) {
if (version != null) {
if (version.equals(V_2000)) {
setDatabaseVersion(SEVEN_0);
return;
} else if (version.equals(V_2005)) {
setDatabaseVersion(NINE_0);
return;
} else if (version.equals(V_2008)) {
setDatabaseVersion(TEN_0);
return;
} else if (version.equals(V_2012)) {
setDatabaseVersion(ELEVEN_0);
return;
}
}
super.setDatabaseVersion(version);
}
@Override
public String translateLiteralDate(Date dateValue) {
if (getVersion().compareTo(TEN_0) >= 0) {
return super.translateLiteralDate(dateValue);
}
return super.translateLiteralTimestamp(new Timestamp(dateValue.getTime()));
}
@Override
public boolean hasTimeType() {
return getVersion().compareTo(TEN_0) >= 0;
}
/**
* The SQL Server driver maps the time escape to a timestamp/datetime, so
* use a cast of the string literal instead.
*/
@Override
public String translateLiteralTime(Time timeValue) {
return "cast('" + formatDateValue(timeValue) + "' as time)"; //$NON-NLS-1$ //$NON-NLS-2$
}
@Override
public boolean supportsCommonTableExpressions() {
return true;
}
@Override
public boolean supportsSubqueryCommonTableExpressions() {
return false;
}
@Override
public boolean supportsRecursiveCommonTableExpressions() {
return getVersion().compareTo(TEN_0) >= 0;
}
@Override
protected boolean supportsCrossJoin() {
return true;
}
@Override
public boolean supportsElementaryOlapOperations() {
return true;
}
@Override
public boolean supportsWindowDistinctAggregates() {
return false;
}
@Override
public boolean supportsWindowOrderByWithAggregates() {
return false;
}
@Override
public boolean supportsFormatLiteral(String literal,
org.teiid.translator.ExecutionFactory.Format format) {
if (format == Format.NUMBER) {
return false; //TODO: add support
}
return formatMap.containsKey(literal);
}
@Override
public boolean supportsOnlyFormatLiterals() {
return true;
}
@Override
protected boolean setFetchSize() {
return true;
}
@Override
@Deprecated
protected JDBCMetdataProcessor createMetadataProcessor() {
return (JDBCMetdataProcessor)getMetadataProcessor();
}
@Override
public MetadataProcessor<Connection> getMetadataProcessor() {
return new JDBCMetdataProcessor() {
@Override
protected Column addColumn(ResultSet columns, Table table,
MetadataFactory metadataFactory, int rsColumns)
throws SQLException {
Column c = super.addColumn(columns, table, metadataFactory, rsColumns);
//The ms jdbc driver does not correctly report the auto incremented column
if (!c.isAutoIncremented() && c.getNativeType() != null && StringUtil.endsWithIgnoreCase(c.getNativeType(), " identity")) { //$NON-NLS-1$
c.setAutoIncremented(true);
}
return c;
}
};
}
@Override
protected boolean usesDatabaseVersion() {
return true;
}
@Override
public boolean useStreamsForLobs() {
return true;
}
@Override
public boolean supportsSelectWithoutFrom() {
return true;
}
@Override
public String getHibernateDialectClassName() {
if (getVersion().compareTo(NINE_0) >= 0) {
if (getVersion().compareTo(TEN_0) >= 0) {
return "org.hibernate.dialect.SQLServer2008Dialect"; //$NON-NLS-1$
}
return "org.hibernate.dialect.SQLServer2005Dialect"; //$NON-NLS-1$
}
return "org.hibernate.dialect.SQLServerDialect"; //$NON-NLS-1$
}
@Override
public boolean supportsGroupByRollup() {
return getVersion().compareTo(NINE_0) >= 0;
}
@Override
public boolean useWithRollup() {
return getVersion().compareTo(TEN_0) < 0;
}
@Override
public boolean supportsConvert(int fromType, int toType) {
if (fromType == TypeFacility.RUNTIME_CODES.OBJECT && this.convertModifier.hasTypeMapping(toType)) {
return true;
}
return super.supportsConvert(fromType, toType);
}
@Override
public boolean supportsLiteralOnlyWithGrouping() {
return true;
}
@Override
public List<?> translateCommand(Command command, ExecutionContext context) {
if (command instanceof Insert) {
Insert insert = (Insert)command;
if (insert.getValueSource() instanceof QueryExpression) {
QueryExpression qe = (QueryExpression)insert.getValueSource();
if (qe.getWith() != null) {
With with = qe.getWith();
qe.setWith(null);
return Arrays.asList(with, insert);
}
}
}
//handle offset support
boolean useRowNumber = false;
if (getVersion().compareTo(ELEVEN_0) >= 0 || !(command instanceof QueryExpression)) {
if (getVersion().compareTo(ELEVEN_0) >= 0 && command instanceof QueryExpression) {
QueryExpression queryCommand = (QueryExpression)command;
if (queryCommand.getLimit() != null) {
if (queryCommand.getOrderBy() == null) {
//an order by is required
//we could use top if offset is 0, but that would require contextual knowledge in useSelectLimit
if (((queryCommand instanceof Select && ((Select)queryCommand).isDistinct())
|| (queryCommand instanceof SetQuery && !((SetQuery)queryCommand).isAll()))) {
//can't use the @@version with distinct
useRowNumber = true;
} else {
List<Object> parts = new ArrayList<Object>();
Limit limit = queryCommand.getLimit();
queryCommand.setLimit(null);
parts.add(queryCommand);
parts.add(" ORDER BY @@version "); //$NON-NLS-1$
parts.add(limit);
return parts;
}
} else {
List<Object> parts = new ArrayList<Object>();
Limit limit = queryCommand.getLimit();
queryCommand.setLimit(null);
parts.add(queryCommand);
parts.add(" "); //$NON-NLS-1$
parts.add(limit);
return parts;
}
}
}
if (!useRowNumber) {
return super.translateCommand(command, context);
}
}
QueryExpression queryCommand = (QueryExpression)command;
if (!useRowNumber && (queryCommand.getLimit() == null || queryCommand.getLimit().getRowOffset() == 0)) {
return super.translateCommand(command, context);
}
Limit limit = queryCommand.getLimit();
queryCommand.setLimit(null);
List<Object> parts = new ArrayList<Object>();
if (queryCommand.getWith() != null) {
With with = queryCommand.getWith();
queryCommand.setWith(null);
parts.add(with);
}
OrderBy orderBy = queryCommand.getOrderBy();
queryCommand.setOrderBy(null);
parts.add("SELECT "); //$NON-NLS-1$
/*
* if all of the columns are aliased, assume that names matter - it actually only seems to matter for
* the first query of a set op when there is a order by. Rather than adding logic to traverse up,
* we just use the projected names
*/
boolean allAliased = true;
for (DerivedColumn selectSymbol : queryCommand.getProjectedQuery().getDerivedColumns()) {
if (selectSymbol.getAlias() == null) {
allAliased = false;
break;
}
}
if (allAliased) {
String[] columnNames = queryCommand.getColumnNames();
for (int i = 0; i < columnNames.length; i++) {
if (i > 0) {
parts.add(", "); //$NON-NLS-1$
}
parts.add(columnNames[i]);
}
} else {
parts.add("*"); //$NON-NLS-1$
}
boolean addedToSelect = false;
if (orderBy != null && queryCommand instanceof Select) {
Select select = (Select)queryCommand;
if (!select.isDistinct() && select.getGroupBy() == null) {
//the order by may be unrelated, so it needs to be with the select
WindowFunction expression = new WindowFunction();
expression.setFunction(new AggregateFunction("ROW_NUMBER", //$NON-NLS-1$
false, Collections.EMPTY_LIST, TypeFacility.RUNTIME_TYPES.INTEGER));
WindowSpecification windowSpecification = new WindowSpecification();
windowSpecification.setOrderBy(orderBy);
expression.setWindowSpecification(windowSpecification);
select.getDerivedColumns().add(new DerivedColumn("ROWNUM_", expression)); //$NON-NLS-1$
parts.add(" FROM ("); //$NON-NLS-1$
parts.add(select);
addedToSelect = true;
}
}
if (!addedToSelect) {
//the order by can be done above the view
parts.add(" FROM (SELECT v.*, ROW_NUMBER() OVER ("); //$NON-NLS-1$
if (orderBy != null) {
parts.add(orderBy);
} else {
//use an order by a "constant"
parts.add("ORDER BY @@version"); //$NON-NLS-1$
}
parts.add(") ROWNUM_ FROM ("); //$NON-NLS-1$
parts.add(queryCommand);
parts.add(") v"); //$NON-NLS-1$
}
parts.add(") v WHERE ROWNUM_ "); //$NON-NLS-1$
if (limit.getRowLimit() != Integer.MAX_VALUE) {
parts.add("<= "); //$NON-NLS-1$
parts.add((long)limit.getRowLimit() + limit.getRowOffset());
parts.add(" AND ROWNUM_ "); //$NON-NLS-1$
}
parts.add("> "); //$NON-NLS-1$
parts.add(limit.getRowOffset());
if (orderBy != null) {
parts.add(" ORDER BY ROWNUM_"); //$NON-NLS-1$
}
return parts;
}
@Override
public List<?> translateLimit(Limit limit, ExecutionContext context) {
if (getVersion().compareTo(ELEVEN_0) >= 0) {
return Arrays.asList("OFFSET ", limit.getRowOffset(), " ROWS FETCH NEXT ", limit.getRowLimit(), " ROWS ONLY"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
return super.translateLimit(limit, context);
}
@Override
public boolean useSelectLimit() {
return getVersion().compareTo(ELEVEN_0) < 0;
}
@Override
public String translateLiteralTimestamp(Timestamp timestampValue) {
if (getVersion().compareTo(TEN_0) < 0) {
return super.translateLiteralTimestamp(timestampValue);
}
return "{ts '" + formatDateValue(timestampValue) + "'}"; //$NON-NLS-1$ //$NON-NLS-2$
}
}