use of org.h2.jaqu.Db in project h2database by h2database.
the class TestFuzzOptimizations method testInSelect.
private void testInSelect() {
Db db = new Db(conn);
db.execute("CREATE TABLE TEST(A INT, B INT)");
db.execute("CREATE INDEX IDX ON TEST(A)");
db.execute("INSERT INTO TEST SELECT X/4, MOD(X, 4) " + "FROM SYSTEM_RANGE(1, 16)");
db.execute("UPDATE TEST SET A = NULL WHERE A = 0");
db.execute("UPDATE TEST SET B = NULL WHERE B = 0");
Random random = new Random();
long seed = random.nextLong();
println("seed: " + seed);
for (int i = 0; i < 100; i++) {
String column = random.nextBoolean() ? "A" : "B";
String value = new String[] { "NULL", "0", "A", "B" }[random.nextInt(4)];
String compare = random.nextBoolean() ? "A" : "B";
int x = random.nextInt(3);
String sql1 = "SELECT * FROM TEST T WHERE " + column + "+0 " + "IN(SELECT " + value + " FROM TEST I WHERE I." + compare + "=?) ORDER BY 1, 2";
String sql2 = "SELECT * FROM TEST T WHERE " + column + " " + "IN(SELECT " + value + " FROM TEST I WHERE I." + compare + "=?) ORDER BY 1, 2";
List<Map<String, Object>> a = db.prepare(sql1).set(x).query();
List<Map<String, Object>> b = db.prepare(sql2).set(x).query();
assertTrue("seed: " + seed + " sql: " + sql1 + " a: " + a + " b: " + b, a.equals(b));
}
db.execute("DROP TABLE TEST");
}
use of org.h2.jaqu.Db in project h2database by h2database.
the class TestFuzzOptimizations method testGroupSorted.
private void testGroupSorted() {
Db db = new Db(conn);
db.execute("CREATE TABLE TEST(A INT, B INT, C INT)");
Random random = new Random();
long seed = random.nextLong();
println("seed: " + seed);
for (int i = 0; i < 100; i++) {
Prepared p = db.prepare("INSERT INTO TEST VALUES(?, ?, ?)");
p.set(new String[] { null, "0", "1", "2" }[random.nextInt(4)]);
p.set(new String[] { null, "0", "1", "2" }[random.nextInt(4)]);
p.set(new String[] { null, "0", "1", "2" }[random.nextInt(4)]);
p.execute();
}
int len = getSize(1000, 3000);
for (int i = 0; i < len / 10; i++) {
db.execute("CREATE TABLE TEST_INDEXED AS SELECT * FROM TEST");
int jLen = 1 + random.nextInt(2);
for (int j = 0; j < jLen; j++) {
String x = "CREATE INDEX IDX" + j + " ON TEST_INDEXED(";
int kLen = 1 + random.nextInt(2);
for (int k = 0; k < kLen; k++) {
if (k > 0) {
x += ",";
}
x += new String[] { "A", "B", "C" }[random.nextInt(3)];
}
db.execute(x + ")");
}
for (int j = 0; j < 10; j++) {
String x = "SELECT ";
for (int k = 0; k < 3; k++) {
if (k > 0) {
x += ",";
}
x += new String[] { "SUM(A)", "MAX(B)", "AVG(C)", "COUNT(B)" }[random.nextInt(4)];
x += " S" + k;
}
x += " FROM ";
String group = " GROUP BY ";
int kLen = 1 + random.nextInt(2);
for (int k = 0; k < kLen; k++) {
if (k > 0) {
group += ",";
}
group += new String[] { "A", "B", "C" }[random.nextInt(3)];
}
group += " ORDER BY 1, 2, 3";
List<Map<String, Object>> a = db.query(x + "TEST" + group);
List<Map<String, Object>> b = db.query(x + "TEST_INDEXED" + group);
assertEquals(a.toString(), b.toString());
assertTrue(a.equals(b));
}
db.execute("DROP TABLE TEST_INDEXED");
}
db.execute("DROP TABLE TEST");
}
use of org.h2.jaqu.Db in project h2database by h2database.
the class TestDiskSpaceLeak method main.
/**
* Run just this test.
*
* @param args ignored
*/
public static void main(String... args) throws Exception {
DeleteDbFiles.execute("data", null, true);
Class.forName("org.h2.Driver");
Connection conn;
long before = 0;
for (int i = 0; i < 10; i++) {
conn = DriverManager.getConnection("jdbc:h2:data/test");
ResultSet rs;
rs = conn.createStatement().executeQuery("select count(*) from information_schema.lobs");
rs.next();
System.out.println("lobs: " + rs.getInt(1));
rs = conn.createStatement().executeQuery("select count(*) from information_schema.lob_map");
rs.next();
System.out.println("lob_map: " + rs.getInt(1));
rs = conn.createStatement().executeQuery("select count(*) from information_schema.lob_data");
rs.next();
System.out.println("lob_data: " + rs.getInt(1));
conn.close();
Recover.execute("data", "test");
new File("data/test.h2.sql").renameTo(new File("data/test." + i + ".sql"));
conn = DriverManager.getConnection("jdbc:h2:data/test");
// ((JdbcConnection) conn).setPowerOffCount(i);
((JdbcConnection) conn).setPowerOffCount(28);
String last = "connect";
try {
conn.createStatement().execute("drop table test if exists");
last = "drop";
conn.createStatement().execute("create table test(id identity, b blob)");
last = "create";
conn.createStatement().execute("insert into test values(1, space(10000))");
last = "insert";
conn.createStatement().execute("delete from test");
last = "delete";
conn.createStatement().execute("insert into test values(1, space(10000))");
last = "insert2";
conn.createStatement().execute("delete from test");
last = "delete2";
} catch (SQLException e) {
// ignore
} finally {
JdbcUtils.closeSilently(conn);
}
long now = new File("data/test.h2.db").length();
long diff = now - before;
before = now;
System.out.println(now + " " + diff + " " + i + " " + last);
}
}
use of org.h2.jaqu.Db in project h2database by h2database.
the class TableView method createTableViewMaybeRecursive.
/**
* Create a view.
*
* @param schema the schema
* @param id the view id
* @param name the view name
* @param querySQL the query
* @param parameters the parameters
* @param columnTemplates the columns
* @param session the session
* @param literalsChecked whether literals in the query are checked
* @param isTableExpression if this is a table expression
* @param isPersistent whether the view is persisted
* @param db the database
* @return the view
*/
public static TableView createTableViewMaybeRecursive(Schema schema, int id, String name, String querySQL, ArrayList<Parameter> parameters, Column[] columnTemplates, Session session, boolean literalsChecked, boolean isTableExpression, boolean isPersistent, Database db) {
Table recursiveTable = TableView.createShadowTableForRecursiveTableExpression(isPersistent, session, name, schema, Arrays.asList(columnTemplates), db);
List<Column> columnTemplateList;
String[] querySQLOutput = { null };
ArrayList<String> columnNames = new ArrayList<>();
for (Column columnTemplate : columnTemplates) {
columnNames.add(columnTemplate.getName());
}
try {
Prepared withQuery = session.prepare(querySQL, false, false);
if (isPersistent) {
withQuery.setSession(session);
}
columnTemplateList = TableView.createQueryColumnTemplateList(columnNames.toArray(new String[1]), (Query) withQuery, querySQLOutput);
} finally {
TableView.destroyShadowTableForRecursiveExpression(isPersistent, session, recursiveTable);
}
// build with recursion turned on
TableView view = new TableView(schema, id, name, querySQL, parameters, columnTemplateList.toArray(columnTemplates), session, true, /* try recursive */
literalsChecked, isTableExpression, isPersistent);
// and no recursive index
if (!view.isRecursiveQueryDetected()) {
if (isPersistent) {
db.addSchemaObject(session, view);
view.lock(session, true, true);
session.getDatabase().removeSchemaObject(session, view);
// during database startup - this method does not normally get called - and it
// needs to be to correctly un-register the table which the table expression
// uses...
view.removeChildrenAndResources(session);
} else {
session.removeLocalTempTable(view);
}
view = new TableView(schema, id, name, querySQL, parameters, columnTemplates, session, false, /* detected not recursive */
literalsChecked, isTableExpression, isPersistent);
}
return view;
}
use of org.h2.jaqu.Db in project h2database by h2database.
the class Db method upgradeDb.
Db upgradeDb() {
if (!upgradeChecked.contains(dbUpgrader.getClass())) {
// flag as checked immediately because calls are nested.
upgradeChecked.add(dbUpgrader.getClass());
JQDatabase model = dbUpgrader.getClass().getAnnotation(JQDatabase.class);
if (model.version() > 0) {
DbVersion v = new DbVersion();
DbVersion dbVersion = // (SCHEMA="" && TABLE="") == DATABASE
from(v).where(v.schema).is("").and(v.table).is("").selectFirst();
if (dbVersion == null) {
// database has no version registration, but model specifies
// version: insert DbVersion entry and return.
DbVersion newDb = new DbVersion(model.version());
insert(newDb);
} else {
// check to see if upgrade is required.
if ((model.version() > dbVersion.version) && (dbUpgrader != null)) {
// database is an older version than the model
boolean success = dbUpgrader.upgradeDatabase(this, dbVersion.version, model.version());
if (success) {
dbVersion.version = model.version();
update(dbVersion);
}
}
}
}
}
return this;
}
Aggregations