private final QueryPartInternal delegate(Configuration configuration) { // These casts are safe for RowImpl RowN r = (RowN) row; RowN min = (RowN) minValue; RowN max = (RowN) maxValue; // These dialects don't support the SYMMETRIC keyword at all if (symmetric && asList(ASE, CUBRID, DB2, DERBY, FIREBIRD, H2, MYSQL, ORACLE, SQLITE, SQLSERVER, SYBASE) .contains(configuration.getDialect())) { if (not) { return (QueryPartInternal) r.notBetween(min, max).and(r.notBetween(max, min)); } else { return (QueryPartInternal) r.between(min, max).or(r.between(max, min)); } } // These dialects either don't support row value expressions, or they // Can't handle row value expressions with the BETWEEN predicate else if (row.size() > 1 && asList(CUBRID, DERBY, FIREBIRD, MYSQL, ORACLE, SQLITE, SQLSERVER, SYBASE) .contains(configuration.getDialect())) { Condition result = r.ge(min).and(r.le(max)); if (not) { result = result.not(); } return (QueryPartInternal) result; } else { return new Native(); } }
@Override final Field<BigDecimal> getFunction0(Configuration configuration) { switch (configuration.getDialect()) { case ASE: case CUBRID: case DB2: case DERBY: case FIREBIRD: case H2: case HSQLDB: case INGRES: case MYSQL: case ORACLE: case POSTGRES: case SQLSERVER: case SYBASE: return Factory.exp(one()); case SQLITE: return inline(Math.E, BigDecimal.class); // The Euler number doesn't seem to exist in any dialect... default: return function("e", getDataType()); } }
private final Table<Record> table(Configuration configuration) { switch (configuration.getDialect()) { case ORACLE: { if (array.getDataType().getType().isArray()) { return simulate().as(alias); } else { return new OracleArrayTable().as(alias); } } case H2: { return new H2ArrayTable().as(alias); } // [#756] These dialects need special care when aliasing unnested // arrays case HSQLDB: case POSTGRES: { return new PostgresHSQLDBTable().as(alias); } // Other dialects can simulate unnested arrays using UNION ALL default: { if (array.getDataType().getType().isArray() && array instanceof Param) { return simulate(); } else { throw new SQLDialectNotSupportedException( "ARRAY TABLE is not supported for " + configuration.getDialect()); } } } }
private final void init() { int columnCount = 0; try { columnCount = meta.getColumnCount(); } // This happens in Oracle for empty cursors returned from stored // procedures / functions catch (SQLException e) { log.warn("Cannot fetch column count for cursor : " + e.getMessage()); fields.add(field("dummy")); } try { for (int i = 1; i <= columnCount; i++) { String name = meta.getColumnLabel(i); int precision = meta.getPrecision(i); int scale = meta.getScale(i); DataType<?> dataType = SQLDataType.OTHER; String type = meta.getColumnTypeName(i); try { dataType = FieldTypeHelper.getDialectDataType( configuration.getDialect(), type, precision, scale); } // [#650, #667] TODO This should not happen. All types // should be known at this point catch (SQLDialectNotSupportedException ignore) { log.warn("Not supported by dialect", ignore.getMessage()); } fields.add(field(name, dataType)); } } catch (SQLException e) { throw Util.translate(null, e); } meta = null; }
@Override final Field<String> getFunction0(Configuration configuration) { Field<?>[] args = getArguments(); // [#861] Most dialects don't ship with a two-argument replace function: switch (configuration.getDialect()) { case ASE: { if (args.length == 2) { return function("str_replace", VARCHAR, args[0], args[1], val(null)); } else { return function("str_replace", VARCHAR, args); } } case DB2: case FIREBIRD: case HSQLDB: case INGRES: case MYSQL: case POSTGRES: case SQLITE: case SQLSERVER: case SYBASE: { if (args.length == 2) { return function("replace", VARCHAR, args[0], args[1], val("")); } else { return function("replace", VARCHAR, args); } } default: { return function("replace", VARCHAR, args); } } }
/** Return the expression to be rendered when the RHS is a number type */ private final Field<T> getNumberExpression(Configuration configuration) { switch (configuration.getDialect()) { case ASE: case FIREBIRD: case SQLSERVER: case SYBASE: { if (operator == ADD) { return field("{dateadd}(day, {0}, {1})", getDataType(), rhsAsNumber(), lhs); } else { return field("{dateadd}(day, {0}, {1})", getDataType(), rhsAsNumber().neg(), lhs); } } case DB2: case HSQLDB: { if (operator == ADD) { return lhs.add(field("{0} day", rhsAsNumber())); } else { return lhs.sub(field("{0} day", rhsAsNumber())); } } case DERBY: { if (operator == ADD) { return field( "{fn {timestampadd}({sql_tsi_day}, {0}, {1}) }", getDataType(), rhsAsNumber(), lhs); } else { return field( "{fn {timestampadd}({sql_tsi_day}, {0}, {1}) }", getDataType(), rhsAsNumber().neg(), lhs); } } case CUBRID: case MYSQL: { if (operator == ADD) { return field( "{date_add}({0}, {interval} {1} {day})", getDataType(), lhs, rhsAsNumber()); } else { return field( "{date_add}({0}, {interval} {1} {day})", getDataType(), lhs, rhsAsNumber().neg()); } } // Ingres is not working yet case INGRES: { if (operator == ADD) { return lhs.add(field("{date}({0} || ' days')", Object.class, rhsAsNumber())); } else { return lhs.sub(field("{date}({0} || ' days')", Object.class, rhsAsNumber())); } } case POSTGRES: { // This seems to be the most reliable way to avoid issues // with incompatible data types and timezones // ? + CAST (? || ' days' as interval) if (operator == ADD) { return lhs.add(rhsAsNumber().concat(" day").cast(DayToSecond.class)); } else { return lhs.sub(rhsAsNumber().concat(" day").cast(DayToSecond.class)); } } case SQLITE: if (operator == ADD) { return field("{datetime}({0}, '+" + rhsAsNumber() + " day')", getDataType(), lhs); } else { return field("{datetime}({0}, '-" + rhsAsNumber() + " day')", getDataType(), lhs); } // These dialects can add / subtract days using +/- operators case H2: case ORACLE: default: return new DefaultExpression(); } }
/** Return the expression to be rendered when the RHS is an interval type */ private final Field<T> getIntervalExpression(Configuration configuration) { SQLDialect dialect = configuration.getDialect(); int sign = (operator == ADD) ? 1 : -1; switch (dialect) { case ASE: case SYBASE: case SQLSERVER: { if (rhs.get(0).getType() == YearToMonth.class) { return field( "{dateadd}(mm, {0}, {1})", getDataType(), val(sign * rhsAsYTM().intValue()), lhs); } else { // SQL Server needs this cast. Field<Timestamp> lhsAsTS = lhs.cast(Timestamp.class); DayToSecond interval = rhsAsDTS(); // Be careful with 32-bit INT arithmetic. Sybase ASE // may fatally overflow when using micro-second precision if (interval.getNano() != 0) { return field( "{dateadd}(ss, {0}, {dateadd}(us, {1}, {2}))", getDataType(), val(sign * (long) interval.getTotalSeconds()), val(sign * interval.getMicro()), lhsAsTS); } else { return field( "{dateadd}(ss, {0}, {1})", getDataType(), val(sign * (long) interval.getTotalSeconds()), lhsAsTS); } } } case CUBRID: case MYSQL: { Interval interval = rhsAsInterval(); if (operator == SUBTRACT) { interval = interval.neg(); } if (rhs.get(0).getType() == YearToMonth.class) { return field( "{date_add}({0}, {interval} {1} {year_month})", getDataType(), lhs, val(interval, String.class)); } else { if (dialect == MYSQL) { return field( "{date_add}({0}, {interval} {1} {day_microsecond})", getDataType(), lhs, val(interval, String.class)); } else { return field( "{date_add}({0}, {interval} {1} {day_millisecond})", getDataType(), lhs, val(interval, String.class)); } } } case DB2: { if (rhs.get(0).getType() == YearToMonth.class) { if (operator == ADD) { return lhs.add(field("{0} month", val(rhsAsYTM().intValue()))); } else { return lhs.sub(field("{0} month", val(rhsAsYTM().intValue()))); } } else { // DB2 needs this cast if lhs is of type DATE. DataType<T> type = lhs.getDataType(); if (operator == ADD) { return lhs.cast(Timestamp.class) .add(field("{0} microseconds", val(rhsAsDTS().getTotalMicro()))) .cast(type); } else { return lhs.cast(Timestamp.class) .sub(field("{0} microseconds", val(rhsAsDTS().getTotalMicro()))) .cast(type); } } } case DERBY: case HSQLDB: { if (rhs.get(0).getType() == YearToMonth.class) { return field( "{fn {timestampadd}({sql_tsi_month}, {0}, {1}) }", getDataType(), val(sign * rhsAsYTM().intValue()), lhs); } else { return field( "{fn {timestampadd}({sql_tsi_second}, {0}, {1}) }", getDataType(), val(sign * (long) rhsAsDTS().getTotalSeconds()), lhs); } } case FIREBIRD: { if (rhs.get(0).getType() == YearToMonth.class) { return field( "{dateadd}({month}, {0}, {1})", getDataType(), val(sign * rhsAsYTM().intValue()), lhs); } else { return field( "{dateadd}({millisecond}, {0}, {1})", getDataType(), val(sign * (long) rhsAsDTS().getTotalMilli()), lhs); } } case H2: { if (rhs.get(0).getType() == YearToMonth.class) { return field( "{dateadd}('month', {0}, {1})", getDataType(), val(sign * rhsAsYTM().intValue()), lhs); } else { return field( "{dateadd}('ms', {0}, {1})", getDataType(), val(sign * (long) rhsAsDTS().getTotalMilli()), lhs); } } case INGRES: { throw new SQLDialectNotSupportedException( "Date time arithmetic not supported in Ingres. Contributions welcome!"); } case SQLITE: { String prefix = (sign > 0) ? "+" : "-"; if (rhs.get(0).getType() == YearToMonth.class) { return field( "{datetime}({0}, '" + prefix + rhsAsYTM().intValue() + " months')", getDataType(), lhs); } else { return field( "{datetime}({0}, '" + prefix + rhsAsDTS().getTotalSeconds() + " seconds')", getDataType(), lhs); } } case ORACLE: case POSTGRES: default: return new DefaultExpression(); } }
@SuppressWarnings("unchecked") @Override final Field<T> getFunction0(Configuration configuration) { SQLDialect dialect = configuration.getDialect(); // --------------------------------------------------------------------- // XXX: Bitwise operators // --------------------------------------------------------------------- // DB2, H2 and HSQLDB know functions, instead of operators if (BIT_AND == operator && asList(DB2, H2, HSQLDB, ORACLE).contains(dialect)) { return function("bitand", getDataType(), getArguments()); } else if (BIT_AND == operator && FIREBIRD == dialect) { return function("bin_and", getDataType(), getArguments()); } else if (BIT_XOR == operator && asList(DB2, H2, HSQLDB).contains(dialect)) { return function("bitxor", getDataType(), getArguments()); } else if (BIT_XOR == operator && FIREBIRD == dialect) { return function("bin_xor", getDataType(), getArguments()); } else if (BIT_OR == operator && asList(DB2, H2, HSQLDB).contains(dialect)) { return function("bitor", getDataType(), getArguments()); } else if (BIT_OR == operator && FIREBIRD == dialect) { return function("bin_or", getDataType(), getArguments()); } // Oracle has to simulate or/xor else if (BIT_OR == operator && ORACLE == dialect) { return lhs.sub(bitAnd(lhsAsNumber(), rhsAsNumber())).add(rhsAsNumber()); } // ~(a & b) & (a | b) else if (BIT_XOR == operator && asList(ORACLE, SQLITE).contains(dialect)) { return (Field<T>) bitAnd(bitNot(bitAnd(lhsAsNumber(), rhsAsNumber())), bitOr(lhsAsNumber(), rhsAsNumber())); } // Many dialects don't support shifts. Use multiplication/division instead else if (SHL == operator && asList(ASE, DB2, H2, HSQLDB, INGRES, ORACLE, SQLSERVER, SYBASE).contains(dialect)) { return lhs.mul(Factory.power(two(), rhsAsNumber())); } else if (SHR == operator && asList(ASE, DB2, H2, HSQLDB, INGRES, ORACLE, SQLSERVER, SYBASE).contains(dialect)) { return lhs.div(Factory.power(two(), rhsAsNumber())); } // Some dialects support shifts as functions else if (SHL == operator && FIREBIRD == dialect) { return function("bin_shl", getDataType(), getArguments()); } else if (SHR == operator && FIREBIRD == dialect) { return function("bin_shr", getDataType(), getArguments()); } // These operators are not supported in any dialect else if (BIT_NAND == operator) { return (Field<T>) bitNot(bitAnd(lhsAsNumber(), rhsAsNumber())); } else if (BIT_NOR == operator) { return (Field<T>) bitNot(bitOr(lhsAsNumber(), rhsAsNumber())); } else if (BIT_XNOR == operator) { return (Field<T>) bitNot(bitXor(lhsAsNumber(), rhsAsNumber())); } // --------------------------------------------------------------------- // XXX: Date time arithmetic operators // --------------------------------------------------------------------- // [#585] Date time arithmetic for numeric or interval RHS else if (asList(ADD, SUBTRACT).contains(operator) && lhs.getDataType().isDateTime() && (rhs.get(0).getDataType().isNumeric() || rhs.get(0).getDataType().isInterval())) { return new DateExpression(); } // --------------------------------------------------------------------- // XXX: Other operators // --------------------------------------------------------------------- // Use the default operator expression for all other cases else { return new DefaultExpression(); } }