Examples with TrimFunction org.hibernate.dialect.function.TrimFunction used on opensource projects

Example 1 with TrimFunction

use of org.hibernate.dialect.function.TrimFunction in project hibernate-orm by hibernate.

the class AnsiTrimEmulationFunctionTest method testBasicSybaseProcessing.

@Test
public void testBasicSybaseProcessing() {
    Dialect dialect = new SybaseDialect();
    TrimFunction function = new TrimFunction(dialect, new TypeConfiguration());
    performBasicSpaceTrimmingTests(dialect, function);
    final String expectedTrimPrep = "str_replace(str_replace(a.column,' ','#%#%'),'-',' ')";
    final String expectedPostTrimPrefix = "str_replace(str_replace(";
    final String expectedPostTrimSuffix = ",' ','-'),'#%#%',' ')";
    // -> trim(LEADING '-' FROM a.column)
    String rendered = render(dialect, function, TrimSpec.LEADING, '-', trimSource);
    String expected = expectedPostTrimPrefix + "ltrim(" + expectedTrimPrep + ")" + expectedPostTrimSuffix;
    assertEquals(expected, rendered);
    // -> trim(TRAILING '-' FROM a.column)
    rendered = render(dialect, function, TrimSpec.TRAILING, '-', trimSource);
    expected = expectedPostTrimPrefix + "rtrim(" + expectedTrimPrep + ")" + expectedPostTrimSuffix;
    assertEquals(expected, rendered);
    // -> trim(BOTH '-' FROM a.column)
    rendered = render(dialect, function, TrimSpec.BOTH, '-', trimSource);
    expected = expectedPostTrimPrefix + "ltrim(rtrim(" + expectedTrimPrep + "))" + expectedPostTrimSuffix;
    assertEquals(expected, rendered);
}

Example 2 with TrimFunction

use of org.hibernate.dialect.function.TrimFunction in project hibernate-orm by hibernate.

the class Dialect method initializeFunctionRegistry.

/**
 * Initialize the given registry with any dialect-specific functions.
 * <p>
 * Support for certain SQL functions is required, and if the database
 * does not support a required function, then the dialect must define
 * a way to emulate it.
 * <p>
 * These required functions include the functions defined by the JPA
 * query language specification:
 *
 * <ul>
 * <li> avg(arg)							- aggregate function
 * <li> count([distinct ]arg)				- aggregate function
 * <li> max(arg)							- aggregate function
 * <li> min(arg)							- aggregate function
 * <li> sum(arg)							- aggregate function
 * </ul>
 *
 * <ul>
 * <li> coalesce(arg0, arg1, ...)
 * <li> nullif(arg0, arg1)
 * </ul>
 *
 * <ul>
 * <li> lower(arg)
 * <li> upper(arg)
 * <li> length(arg)
 * <li> concat(arg0, arg1, ...)
 * <li> locate(pattern, string[, start])
 * <li> substring(string, start[, length])
 * <li> trim([[spec ][character ]from] string)
 * </ul>
 *
 * <ul>
 * <li> abs(arg)
 * <li> mod(arg0, arg1)
 * <li> sqrt(arg)
 * </ul>
 *
 * <ul>
 * <li> current date
 * <li> current time
 * <li> current timestamp
 * </ul>
 *
 * Along with an additional set of functions defined by ANSI SQL:
 *
 * <ul>
 * <li> any(arg)							- aggregate function
 * <li> every(arg)							- aggregate function
 * </ul>
 *
 * <ul>
 * <li> cast(arg as Type)
 * <li> extract(field from arg)
 * </ul>
 *
 * <ul>
 * <li> ln(arg)
 * <li> exp(arg)
 * <li> power(arg0, arg1)
 * <li> floor(arg)
 * <li> ceiling(arg)
 * </ul>
 *
 * <ul>
 * <li> position(pattern in string)
 * <li> substring(string from start[ for length])
 * <li> overlay(string placing replacement from start[ for length])
 * </ul>
 *
 * And the following functions for working with java.time types:
 *
 * <ul>
 * <li> local date
 * <li> local time
 * <li> local datetime
 * <li> offset datetime
 * <li> instant
 * </ul>
 *
 * And a number of additional "standard" functions:
 *
 * <ul>
 * <li> left(string, length)
 * <li> right(string, length)
 * <li> replace(string, pattern, replacement)
 * <li> pad(string with length spec[ character])
 * </ul>
 *
 * <ul>
 * <li> log10(arg)
 * <li> sign(arg)
 * <li> sin(arg)
 * <li> cos(arg)
 * <li> tan(arg)
 * <li> asin(arg)
 * <li> acos(arg)
 * <li> atan(arg)
 * <li> atan2(arg0, arg1)
 * <li> round(arg0, arg1)
 * <li> least(arg0, arg1, ...)
 * <li> greatest(arg0, arg1, ...)
 * </ul>
 *
 * <ul>
 * <li> format(datetime as pattern)
 * <li> collate(string as collation)
 * <li> str(arg)					- synonym of cast(a as String)
 * <li> ifnull(arg0, arg1)			- synonym of coalesce(a, b)
 * </ul>
 *
 * Finally, the following functions are defined as abbreviations
 * for extract(), and desugared by the parser:
 *
 * <ul>
 * <li> second(arg)					- synonym of extract(second from a)
 * <li> minute(arg)					- synonym of extract(minute from a)
 * <li> hour(arg)					- synonym of extract(hour from a)
 * <li> day(arg)					- synonym of extract(day from a)
 * <li> month(arg)					- synonym of extract(month from a)
 * <li> year(arg)					- synonym of extract(year from a)
 * </ul>
 *
 * Note that according to this definition, the second() function returns
 * a floating point value, contrary to the integer type returned by the
 * native function with this name on many databases. Thus, we don't just
 * naively map these HQL functions to the native SQL functions with the
 * same names.
 */
public void initializeFunctionRegistry(QueryEngine queryEngine) {
    final TypeConfiguration typeConfiguration = queryEngine.getTypeConfiguration();
    final BasicTypeRegistry basicTypeRegistry = typeConfiguration.getBasicTypeRegistry();
    final BasicType<Date> timestampType = basicTypeRegistry.resolve(StandardBasicTypes.TIMESTAMP);
    final BasicType<Date> dateType = basicTypeRegistry.resolve(StandardBasicTypes.DATE);
    final BasicType<Date> timeType = basicTypeRegistry.resolve(StandardBasicTypes.TIME);
    final BasicType<Instant> instantType = basicTypeRegistry.resolve(StandardBasicTypes.INSTANT);
    final BasicType<OffsetDateTime> offsetDateTimeType = basicTypeRegistry.resolve(StandardBasicTypes.OFFSET_DATE_TIME);
    final BasicType<LocalDateTime> localDateTimeType = basicTypeRegistry.resolve(StandardBasicTypes.LOCAL_DATE_TIME);
    final BasicType<LocalTime> localTimeType = basicTypeRegistry.resolve(StandardBasicTypes.LOCAL_TIME);
    final BasicType<LocalDate> localDateType = basicTypeRegistry.resolve(StandardBasicTypes.LOCAL_DATE);
    CommonFunctionFactory functionFactory = new CommonFunctionFactory(queryEngine);
    // aggregate functions, supported on every database
    functionFactory.aggregates(this, SqlAstNodeRenderingMode.DEFAULT);
    // the ANSI SQL-defined aggregate functions any() and every() are only
    // supported on one database, but can be emulated using sum() and case,
    // though there is a more natural mapping on some databases
    functionFactory.everyAny_sumCase();
    // math functions supported on almost every database
    // Note that while certain mathematical functions return the same type
    // as their arguments, this is not the case in general - any function
    // involving exponentiation by a non-integer power, logarithms,
    // trigonometric functions, etc., should be considered to be of type
    // Double. In particular, there is no meaningful concept of an "exact
    // decimal" version of these functions, and if any database attempted
    // to implement such a silly thing, it would be dog slow.
    functionFactory.math();
    // trig functions supported on almost every database
    functionFactory.trigonometry();
    // coalesce() function, supported by most databases, must be emulated
    // in terms of nvl() for platforms which don't support it natively
    functionFactory.coalesce();
    // nullif() function, supported on almost every database
    functionFactory.nullif();
    // string functions, must be emulated where not supported
    functionFactory.leftRight();
    functionFactory.replace();
    functionFactory.concat();
    functionFactory.lowerUpper();
    // there are two forms of substring(), the JPA standard syntax, which
    // separates arguments using commas, and the ANSI SQL standard syntax
    // with named arguments (we support both)
    functionFactory.substring();
    // the JPA locate() function is especially tricky to emulate, calling
    // for lots of Dialect-specific customization
    functionFactory.locate();
    // JPA string length() function, a synonym for ANSI SQL character_length()
    functionFactory.length_characterLength();
    // only some databases support the ANSI SQL-style position() function, so
    // define it here as an alias for locate()
    queryEngine.getSqmFunctionRegistry().register("position", new LocatePositionEmulation(typeConfiguration));
    // very few databases support ANSI-style overlay() function, so emulate
    // it here in terms of either insert() or concat()/substring()
    queryEngine.getSqmFunctionRegistry().register("overlay", new InsertSubstringOverlayEmulation(typeConfiguration, false));
    // ANSI SQL trim() function is supported on almost all of the databases
    // we care about, but on some it must be emulated using ltrim(), rtrim(),
    // and replace()
    queryEngine.getSqmFunctionRegistry().register("trim", new TrimFunction(this, typeConfiguration));
    // ANSI SQL cast() function is supported on the databases we care most
    // about but in certain cases it doesn't allow some useful typecasts,
    // which must be emulated in a dialect-specific way
    // Note that two case are especially tricky to make portable:
    // - casts to and from Boolean, and
    // - casting Double or Float to String.
    queryEngine.getSqmFunctionRegistry().register("cast", new CastFunction(this, queryEngine.getPreferredSqlTypeCodeForBoolean()));
    // There is a 'collate' operator in a number of major databases
    functionFactory.collate();
    // ANSI SQL extract() function is supported on the databases we care most
    // about (though it is called datepart() in some of them) but HQL defines
    // additional non-standard temporal field types, which must be emulated in
    // a very dialect-specific way
    queryEngine.getSqmFunctionRegistry().register("extract", new ExtractFunction(this, typeConfiguration));
    // comparison functions supported on most databases, emulated on others
    // using a case expression
    functionFactory.leastGreatest();
    // two-argument synonym for coalesce() supported on most but not every
    // database, so define it here as an alias for coalesce(arg1,arg2)
    queryEngine.getSqmFunctionRegistry().register("ifnull", new CoalesceIfnullEmulation());
    // rpad() and pad() are supported on almost every database, and emulated
    // where not supported, but they're not considered "standard" ... instead
    // they're used to implement pad()
    functionFactory.pad();
    // pad() is a function we've designed to look like ANSI trim()
    queryEngine.getSqmFunctionRegistry().register("pad", new LpadRpadPadEmulation(typeConfiguration));
    // legacy Hibernate convenience function for casting to string, defined
    // here as an alias for cast(arg as String)
    queryEngine.getSqmFunctionRegistry().register("str", new CastStrEmulation(typeConfiguration));
    // format() function for datetimes, emulated on many databases using the
    // Oracle-style to_char() function, and on others using their native
    // formatting functions
    functionFactory.format_toChar();
    // timestampadd()/timestampdiff() delegated back to the Dialect itself
    // since there is a great variety of different ways to emulate them
    queryEngine.getSqmFunctionRegistry().register("timestampadd", new TimestampaddFunction(this, typeConfiguration));
    queryEngine.getSqmFunctionRegistry().register("timestampdiff", new TimestampdiffFunction(this, typeConfiguration));
    queryEngine.getSqmFunctionRegistry().registerAlternateKey("dateadd", "timestampadd");
    queryEngine.getSqmFunctionRegistry().registerAlternateKey("datediff", "timestampdiff");
    // ANSI SQL (and JPA) current date/time/timestamp functions, supported
    // natively on almost every database, delegated back to the Dialect
    queryEngine.getSqmFunctionRegistry().register("current_date", new CurrentFunction("current_date", currentDate(), dateType));
    queryEngine.getSqmFunctionRegistry().register("current_time", new CurrentFunction("current_time", currentTime(), timeType));
    queryEngine.getSqmFunctionRegistry().register("current_timestamp", new CurrentFunction("current_timestamp", currentTimestamp(), timestampType));
    queryEngine.getSqmFunctionRegistry().registerAlternateKey("current date", "current_date");
    queryEngine.getSqmFunctionRegistry().registerAlternateKey("current time", "current_time");
    queryEngine.getSqmFunctionRegistry().registerAlternateKey("current timestamp", "current_timestamp");
    // HQL current instant/date/time/datetime functions, delegated back to the Dialect
    queryEngine.getSqmFunctionRegistry().register("local_date", new CurrentFunction("local_date", currentDate(), localDateType));
    queryEngine.getSqmFunctionRegistry().register("local_time", new CurrentFunction("local_time", currentLocalTime(), localTimeType));
    queryEngine.getSqmFunctionRegistry().register("local_datetime", new CurrentFunction("local_datetime", currentLocalTimestamp(), localDateTimeType));
    queryEngine.getSqmFunctionRegistry().register("offset_datetime", new CurrentFunction("offset_datetime", currentTimestampWithTimeZone(), offsetDateTimeType));
    queryEngine.getSqmFunctionRegistry().registerAlternateKey("local date", "local_date");
    queryEngine.getSqmFunctionRegistry().registerAlternateKey("local time", "local_time");
    queryEngine.getSqmFunctionRegistry().registerAlternateKey("local datetime", "local_datetime");
    queryEngine.getSqmFunctionRegistry().registerAlternateKey("offset datetime", "offset_datetime");
    queryEngine.getSqmFunctionRegistry().register("instant", new CurrentFunction("instant", currentTimestamp(), instantType));
    // deprecated legacy!
    queryEngine.getSqmFunctionRegistry().registerAlternateKey("current_instant", "instant");
    queryEngine.getSqmFunctionRegistry().register("sql", new SqlFunction());
}

Example 3 with TrimFunction

use of org.hibernate.dialect.function.TrimFunction in project hibernate-orm by hibernate.

the class AnsiTrimEmulationFunctionTest method testBasicSqlServerProcessing.

@Test
public void testBasicSqlServerProcessing() {
    Dialect dialect = new SQLServerDialect();
    TrimFunction function = new TrimFunction(dialect, new TypeConfiguration());
    performBasicSpaceTrimmingTests(dialect, function);
    final String expectedTrimPrep = "replace(replace(a.column,' ','#%#%'),'-',' ')";
    final String expectedPostTrimPrefix = "replace(replace(";
    final String expectedPostTrimSuffix = ",' ','-'),'#%#%',' ')";
    // -> trim(LEADING '-' FROM a.column)
    String rendered = render(dialect, function, TrimSpec.LEADING, '-', trimSource);
    String expected = expectedPostTrimPrefix + "ltrim(" + expectedTrimPrep + ")" + expectedPostTrimSuffix;
    assertEquals(expected, rendered);
    // -> trim(TRAILING '-' FROM a.column)
    rendered = render(dialect, function, TrimSpec.TRAILING, '-', trimSource);
    expected = expectedPostTrimPrefix + "rtrim(" + expectedTrimPrep + ")" + expectedPostTrimSuffix;
    assertEquals(expected, rendered);
    // -> trim(BOTH '-' FROM a.column)
    rendered = render(dialect, function, TrimSpec.BOTH, '-', trimSource);
    expected = expectedPostTrimPrefix + "ltrim(rtrim(" + expectedTrimPrep + "))" + expectedPostTrimSuffix;
    assertEquals(expected, rendered);
}