001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (license2)
004: * Initial Developer: H2 Group
005: */
006: package org.h2.test.synth;
007:
008: import java.sql.Connection;
009: import java.sql.ResultSet;
010: import java.sql.SQLException;
011: import java.sql.Statement;
012:
013: import org.h2.test.TestBase;
014: import org.h2.test.synth.sql.RandomGen;
015:
016: /**
017: * A test that runs random operations against a table to test the various index
018: * implementations.
019: */
020: public class TestSimpleIndex extends TestBase {
021:
022: Connection conn;
023: Statement stat;
024: RandomGen random;
025:
026: public void test() throws Exception {
027: deleteDb("simpleIndex");
028: conn = getConnection("simpleIndex");
029: random = new RandomGen(null);
030: stat = conn.createStatement();
031: for (int i = 0; i < 10000; i++) {
032: testIndex(i);
033: }
034: }
035:
036: private void testIndex(int seed) throws Exception {
037: random.setSeed(seed);
038: String unique = random.nextBoolean() ? "UNIQUE " : "";
039: int len = random.getInt(2) + 1;
040: StringBuffer buff = new StringBuffer();
041: for (int i = 0; i < len; i++) {
042: if (i > 0) {
043: buff.append(", ");
044: }
045: buff.append((char) ('A' + random.getInt(3)));
046: }
047: String cols = buff.toString();
048: execute("CREATE MEMORY TABLE TEST_M(A INT, B INT, C INT, DATA VARCHAR(255))");
049: execute("CREATE CACHED TABLE TEST_D(A INT, B INT, C INT, DATA VARCHAR(255))");
050: execute("CREATE MEMORY TABLE TEST_MI(A INT, B INT, C INT, DATA VARCHAR(255))");
051: execute("CREATE CACHED TABLE TEST_DI(A INT, B INT, C INT, DATA VARCHAR(255))");
052: execute("CREATE " + unique + "INDEX M ON TEST_MI(" + cols + ")");
053: execute("CREATE " + unique + "INDEX D ON TEST_DI(" + cols + ")");
054: for (int i = 0; i < 100; i++) {
055: println("i=" + i);
056: testRows(i);
057: }
058: execute("DROP INDEX M");
059: execute("DROP INDEX D");
060: execute("DROP TABLE TEST_M");
061: execute("DROP TABLE TEST_D");
062: execute("DROP TABLE TEST_MI");
063: execute("DROP TABLE TEST_DI");
064: }
065:
066: private void testRows(int id) throws Exception {
067: String a = randomValue(), b = randomValue(), c = randomValue();
068: String data = a + "/" + b + "/" + c;
069: String sql = "VALUES(" + a + ", " + b + ", " + c + ", '" + data
070: + "')";
071: boolean em, ed;
072: // if(id==73) {
073: // print("halt");
074: // }
075: try {
076: execute("INSERT INTO TEST_MI " + sql);
077: em = false;
078: } catch (SQLException e) {
079: em = true;
080: }
081: try {
082: execute("INSERT INTO TEST_DI " + sql);
083: ed = false;
084: } catch (SQLException e) {
085: ed = true;
086: }
087: if (em != ed) {
088: error("different result: ");
089: }
090: if (!em) {
091: execute("INSERT INTO TEST_M " + sql);
092: execute("INSERT INTO TEST_D " + sql);
093: }
094: StringBuffer buff = new StringBuffer("WHERE 1=1");
095: int len = random.getLog(10);
096: for (int i = 0; i < len; i++) {
097: buff.append(" AND ");
098: buff.append('A' + random.getInt(3));
099: switch (random.getInt(10)) {
100: case 0:
101: buff.append("<");
102: buff.append((random.getInt(100) - 50));
103: break;
104: case 1:
105: buff.append("<=");
106: buff.append((random.getInt(100) - 50));
107: break;
108: case 2:
109: buff.append(">");
110: buff.append((random.getInt(100) - 50));
111: break;
112: case 3:
113: buff.append(">=");
114: buff.append((random.getInt(100) - 50));
115: break;
116: case 4:
117: buff.append("<>");
118: buff.append((random.getInt(100) - 50));
119: break;
120: case 5:
121: buff.append(" IS NULL");
122: break;
123: case 6:
124: buff.append(" IS NOT NULL");
125: break;
126: default:
127: buff.append("=");
128: buff.append((random.getInt(100) - 50));
129: }
130: }
131: String where = buff.toString();
132: String r1 = getResult("SELECT DATA FROM TEST_M " + where
133: + " ORDER BY DATA");
134: String r2 = getResult("SELECT DATA FROM TEST_D " + where
135: + " ORDER BY DATA");
136: String r3 = getResult("SELECT DATA FROM TEST_MI " + where
137: + " ORDER BY DATA");
138: String r4 = getResult("SELECT DATA FROM TEST_DI " + where
139: + " ORDER BY DATA");
140: check(r1, r2);
141: check(r1, r3);
142: check(r1, r4);
143: }
144:
145: private String getResult(String sql) throws Exception {
146: ResultSet rs = stat.executeQuery(sql);
147: StringBuffer buff = new StringBuffer();
148: while (rs.next()) {
149: buff.append(rs.getString(1));
150: buff.append("; ");
151: }
152: rs.close();
153: return buff.toString();
154: }
155:
156: private String randomValue() {
157: return random.getInt(10) == 0 ? "NULL" : ""
158: + (random.getInt(100) - 50);
159: }
160:
161: private void execute(String sql) throws Exception {
162: try {
163: println(sql + ";");
164: stat.execute(sql);
165: println("> update count: 1");
166: } catch (SQLException e) {
167: println("> exception");
168: throw e;
169: }
170: }
171:
172: }
|