use of org.h2.expression.Alias in project h2database by h2database.
the class TestPgServer method testCancelQuery.
private void testCancelQuery() throws Exception {
if (!getPgJdbcDriver()) {
return;
}
Server server = createPgServer("-pgPort", "5535", "-pgDaemon", "-key", "pgserver", "mem:pgserver");
ExecutorService executor = Executors.newSingleThreadExecutor();
try {
Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5535/pgserver", "sa", "sa");
final Statement stat = conn.createStatement();
stat.execute("create alias sleep for \"java.lang.Thread.sleep\"");
// create a table with 200 rows (cancel interval is 127)
stat.execute("create table test(id int)");
for (int i = 0; i < 200; i++) {
stat.execute("insert into test (id) values (rand())");
}
Future<Boolean> future = executor.submit(new Callable<Boolean>() {
@Override
public Boolean call() throws SQLException {
return stat.execute("select id, sleep(5) from test");
}
});
// give it a little time to start and then cancel it
Thread.sleep(100);
stat.cancel();
try {
future.get();
throw new IllegalStateException();
} catch (ExecutionException e) {
assertStartsWith(e.getCause().getMessage(), "ERROR: canceling statement due to user request");
} finally {
conn.close();
}
} finally {
server.stop();
executor.shutdown();
}
deleteDb("pgserver");
}
use of org.h2.expression.Alias in project h2database by h2database.
the class TableDefinition method delete.
void delete(Db db, Object obj) {
if (primaryKeyColumnNames == null || primaryKeyColumnNames.size() == 0) {
throw new IllegalStateException("No primary key columns defined " + "for table " + obj.getClass() + " - no update possible");
}
SQLStatement stat = new SQLStatement(db);
StatementBuilder buff = new StatementBuilder("DELETE FROM ");
buff.append(db.getDialect().getTableName(schemaName, tableName));
buff.resetCount();
Object alias = ClassUtils.newObject(obj.getClass());
Query<Object> query = Query.from(db, alias);
boolean firstCondition = true;
for (FieldDefinition field : fields) {
if (field.isPrimaryKey) {
Object aliasValue = field.getValue(alias);
Object value = field.getValue(obj);
if (!firstCondition) {
query.addConditionToken(ConditionAndOr.AND);
}
firstCondition = false;
query.addConditionToken(new Condition<>(aliasValue, value, CompareType.EQUAL));
}
}
stat.setSQL(buff.toString());
query.appendWhere(stat);
StatementLogger.delete(stat.getSQL());
stat.executeUpdate();
}
use of org.h2.expression.Alias in project h2database by h2database.
the class TableDefinition method update.
void update(Db db, Object obj) {
if (primaryKeyColumnNames == null || primaryKeyColumnNames.size() == 0) {
throw new IllegalStateException("No primary key columns defined " + "for table " + obj.getClass() + " - no update possible");
}
SQLStatement stat = new SQLStatement(db);
StatementBuilder buff = new StatementBuilder("UPDATE ");
buff.append(db.getDialect().getTableName(schemaName, tableName)).append(" SET ");
buff.resetCount();
for (FieldDefinition field : fields) {
if (!field.isPrimaryKey) {
buff.appendExceptFirst(", ");
buff.append(field.columnName);
buff.append(" = ?");
Object value = getValue(obj, field);
stat.addParameter(value);
}
}
Object alias = ClassUtils.newObject(obj.getClass());
Query<Object> query = Query.from(db, alias);
boolean firstCondition = true;
for (FieldDefinition field : fields) {
if (field.isPrimaryKey) {
Object aliasValue = field.getValue(alias);
Object value = field.getValue(obj);
if (!firstCondition) {
query.addConditionToken(ConditionAndOr.AND);
}
firstCondition = false;
query.addConditionToken(new Condition<>(aliasValue, value, CompareType.EQUAL));
}
}
stat.setSQL(buff.toString());
query.appendWhere(stat);
StatementLogger.update(stat.getSQL());
stat.executeUpdate();
}
use of org.h2.expression.Alias in project h2database by h2database.
the class TestMultiDimension method testPerformance2d.
private void testPerformance2d() throws SQLException {
deleteDb("multiDimension");
Connection conn;
conn = getConnection("multiDimension");
Statement stat = conn.createStatement();
stat.execute("CREATE ALIAS MAP FOR \"" + getClass().getName() + ".interleave\"");
stat.execute("CREATE TABLE TEST(X INT NOT NULL, Y INT NOT NULL, " + "XY BIGINT AS MAP(X, Y), DATA VARCHAR)");
stat.execute("CREATE INDEX IDX_X ON TEST(X, Y)");
stat.execute("CREATE INDEX IDX_XY ON TEST(XY)");
PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST(X, Y, DATA) VALUES(?, ?, ?)");
// the MultiDimension tool is faster for 4225 (65^2) points
// the more the bigger the difference
int max = getSize(30, 65);
long time = System.nanoTime();
for (int x = 0; x < max; x++) {
for (int y = 0; y < max; y++) {
long t2 = System.nanoTime();
if (t2 - time > TimeUnit.SECONDS.toNanos(1)) {
int percent = (int) (100.0 * ((double) x * max + y) / ((double) max * max));
trace(percent + "%");
time = t2;
}
prep.setInt(1, x);
prep.setInt(2, y);
prep.setString(3, "Test data");
prep.execute();
}
}
stat.execute("ANALYZE SAMPLE_SIZE 10000");
PreparedStatement prepRegular = conn.prepareStatement("SELECT * FROM TEST WHERE X BETWEEN ? AND ? " + "AND Y BETWEEN ? AND ? ORDER BY X, Y");
MultiDimension multi = MultiDimension.getInstance();
String sql = multi.generatePreparedQuery("TEST", "XY", new String[] { "X", "Y" });
sql += " ORDER BY X, Y";
PreparedStatement prepMulti = conn.prepareStatement(sql);
long timeMulti = 0, timeRegular = 0;
int timeMax = getSize(500, 2000);
Random rand = new Random(1);
while (timeMulti < timeMax) {
int size = rand.nextInt(max / 10);
int minX = rand.nextInt(max - size);
int minY = rand.nextInt(max - size);
int maxX = minX + size, maxY = minY + size;
time = System.nanoTime();
ResultSet rs1 = multi.getResult(prepMulti, new int[] { minX, minY }, new int[] { maxX, maxY });
timeMulti += System.nanoTime() - time;
time = System.nanoTime();
prepRegular.setInt(1, minX);
prepRegular.setInt(2, maxX);
prepRegular.setInt(3, minY);
prepRegular.setInt(4, maxY);
ResultSet rs2 = prepRegular.executeQuery();
timeRegular += System.nanoTime() - time;
while (rs1.next()) {
assertTrue(rs2.next());
assertEquals(rs1.getInt(1), rs2.getInt(1));
assertEquals(rs1.getInt(2), rs2.getInt(2));
}
assertFalse(rs2.next());
}
conn.close();
deleteDb("multiDimension");
trace("2d: regular: " + TimeUnit.NANOSECONDS.toMillis(timeRegular) + " MultiDimension: " + TimeUnit.NANOSECONDS.toMillis(timeMulti));
}
use of org.h2.expression.Alias in project h2database by h2database.
the class TestMultiDimension method testPerformance3d.
private void testPerformance3d() throws SQLException {
deleteDb("multiDimension");
Connection conn;
conn = getConnection("multiDimension");
Statement stat = conn.createStatement();
stat.execute("CREATE ALIAS MAP FOR \"" + getClass().getName() + ".interleave\"");
stat.execute("CREATE TABLE TEST(X INT NOT NULL, " + "Y INT NOT NULL, Z INT NOT NULL, " + "XYZ BIGINT AS MAP(X, Y, Z), DATA VARCHAR)");
stat.execute("CREATE INDEX IDX_X ON TEST(X, Y, Z)");
stat.execute("CREATE INDEX IDX_XYZ ON TEST(XYZ)");
PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST(X, Y, Z, DATA) VALUES(?, ?, ?, ?)");
// the MultiDimension tool is faster for 8000 (20^3) points
// the more the bigger the difference
int max = getSize(10, 20);
long time = System.nanoTime();
for (int x = 0; x < max; x++) {
for (int y = 0; y < max; y++) {
for (int z = 0; z < max; z++) {
long t2 = System.nanoTime();
if (t2 - time > TimeUnit.SECONDS.toNanos(1)) {
int percent = (int) (100.0 * ((double) x * max + y) / ((double) max * max));
trace(percent + "%");
time = t2;
}
prep.setInt(1, x);
prep.setInt(2, y);
prep.setInt(3, z);
prep.setString(4, "Test data");
prep.execute();
}
}
}
stat.execute("ANALYZE SAMPLE_SIZE 10000");
PreparedStatement prepRegular = conn.prepareStatement("SELECT * FROM TEST WHERE X BETWEEN ? AND ? " + "AND Y BETWEEN ? AND ? AND Z BETWEEN ? AND ? ORDER BY X, Y, Z");
MultiDimension multi = MultiDimension.getInstance();
String sql = multi.generatePreparedQuery("TEST", "XYZ", new String[] { "X", "Y", "Z" });
sql += " ORDER BY X, Y, Z";
PreparedStatement prepMulti = conn.prepareStatement(sql);
long timeMulti = 0, timeRegular = 0;
int timeMax = getSize(500, 2000);
Random rand = new Random(1);
while (timeMulti < timeMax) {
int size = rand.nextInt(max / 10);
int minX = rand.nextInt(max - size);
int minY = rand.nextInt(max - size);
int minZ = rand.nextInt(max - size);
int maxX = minX + size, maxY = minY + size, maxZ = minZ + size;
time = System.nanoTime();
ResultSet rs1 = multi.getResult(prepMulti, new int[] { minX, minY, minZ }, new int[] { maxX, maxY, maxZ });
timeMulti += System.nanoTime() - time;
time = System.nanoTime();
prepRegular.setInt(1, minX);
prepRegular.setInt(2, maxX);
prepRegular.setInt(3, minY);
prepRegular.setInt(4, maxY);
prepRegular.setInt(5, minZ);
prepRegular.setInt(6, maxZ);
ResultSet rs2 = prepRegular.executeQuery();
timeRegular += System.nanoTime() - time;
while (rs1.next()) {
assertTrue(rs2.next());
assertEquals(rs1.getInt(1), rs2.getInt(1));
assertEquals(rs1.getInt(2), rs2.getInt(2));
}
assertFalse(rs2.next());
}
conn.close();
deleteDb("multiDimension");
trace("3d: regular: " + TimeUnit.NANOSECONDS.toMillis(timeRegular) + " MultiDimension: " + TimeUnit.NANOSECONDS.toMillis(timeMulti));
}
Aggregations