}
public void testSQLFunctions() throws Exception {
Session s = openSession();
Transaction t = s.beginTransaction();
Simple simple = new Simple();
simple.setName("Simple 1");
s.save(simple, new Long(10) );
if ( getDialect() instanceof Cache71Dialect) {
s.find("from Simple s where repeat('foo', 3) = 'foofoofoo'");
s.find("from Simple s where repeat(s.name, 3) = 'foofoofoo'");
s.find("from Simple s where repeat( lower(s.name), (3 + (1-1)) / 2) = 'foofoofoo'");
}
assertTrue(
s.find("from Simple s where upper( s.name ) ='SIMPLE 1'").size()==1
);
if ( !(getDialect() instanceof HSQLDialect) ) {
assertTrue(
s.find("from Simple s where not( upper( s.name ) ='yada' or 1=2 or 'foo'='bar' or not('foo'='foo') or 'foo' like 'bar' )").size()==1
);
}
if ( !(getDialect() instanceof MySQLDialect) && !(getDialect() instanceof SybaseDialect) && !(getDialect() instanceof MckoiDialect) && !(getDialect() instanceof InterbaseDialect) && !(getDialect() instanceof TimesTenDialect) ) { //My SQL has a funny concatenation operator
assertTrue(
s.find("from Simple s where lower( s.name || ' foo' ) ='simple 1 foo'").size()==1
);
}
/* + is not concat in Cache
if ( (getDialect() instanceof Cache71Dialect) ) {
assertTrue(
s.find("from Simple s where lower( cons.name ' foo' ) ='simple 1 foo'").size()==1
);
}
*/
if ( (getDialect() instanceof Cache71Dialect) ) {
assertTrue(
s.find("from Simple s where lower( concat(s.name, ' foo') ) ='simple 1 foo'").size()==1
);
}
Simple other = new Simple();
other.setName("Simple 2");
other.setCount(12);
simple.setOther(other);
s.save( other, new Long(20) );
//s.find("from Simple s where s.name ## 'cat|rat|bag'");
assertTrue(
s.find("from Simple s where upper( s.other.name ) ='SIMPLE 2'").size()==1
);
assertTrue(
s.find("from Simple s where not ( upper( s.other.name ) ='SIMPLE 2' )").size()==0
);
assertTrue(
s.find("select distinct s from Simple s where ( ( s.other.count + 3 ) = (15*2)/2 and s.count = 69) or ( ( s.other.count + 2 ) / 7 ) = 2").size()==1
);
assertTrue(
s.find("select s from Simple s where ( ( s.other.count + 3 ) = (15*2)/2 and s.count = 69) or ( ( s.other.count + 2 ) / 7 ) = 2 order by s.other.count").size()==1
);
Simple min = new Simple();
min.setCount(-1);
s.save(min, new Long(30) );
if ( ! (getDialect() instanceof MySQLDialect) && ! (getDialect() instanceof HSQLDialect) ) { //My SQL has no subqueries
assertTrue(
s.find("from Simple s where s.count > ( select min(sim.count) from Simple sim )").size()==2
);