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.db;
007:
008: import java.sql.Connection;
009: import java.sql.PreparedStatement;
010: import java.sql.ResultSet;
011: import java.sql.Statement;
012: import java.util.Random;
013:
014: import org.h2.result.SortOrder;
015: import org.h2.test.TestBase;
016:
017: /**
018: * Index tests.
019: */
020: public class TestIndex extends TestBase {
021:
022: Connection conn;
023: Statement stat;
024: Random random = new Random();
025:
026: private void reconnect() throws Exception {
027: if (conn != null) {
028: conn.close();
029: conn = null;
030: }
031: conn = getConnection("index");
032: stat = conn.createStatement();
033: }
034:
035: public void test() throws Exception {
036: testDescIndex();
037:
038: if (config.networked && config.big) {
039: return;
040: }
041:
042: random.setSeed(100);
043:
044: deleteDb("index");
045: testWideIndex(147);
046: testWideIndex(313);
047: testWideIndex(979);
048: testWideIndex(1200);
049: testWideIndex(2400);
050: if (config.big && config.logMode == 2) {
051: for (int i = 0; i < 2000; i++) {
052: if ((i % 100) == 0) {
053: System.out.println("width: " + i);
054: }
055: testWideIndex(i);
056: }
057: }
058:
059: testLike();
060: reconnect();
061: testConstraint();
062: testLargeIndex();
063: testMultiColumnIndex();
064: // long time;
065: // time = System.currentTimeMillis();
066: testHashIndex(true, false);
067:
068: testHashIndex(false, false);
069: // System.out.println("btree="+(System.currentTimeMillis()-time));
070: // time = System.currentTimeMillis();
071: testHashIndex(true, true);
072: testHashIndex(false, true);
073: // System.out.println("hash="+(System.currentTimeMillis()-time));
074:
075: testMultiColumnHashIndex();
076:
077: conn.close();
078: }
079:
080: void testDescIndex() throws Exception {
081: if (config.memory) {
082: return;
083: }
084: ResultSet rs;
085: reconnect();
086: stat.execute("CREATE TABLE TEST(ID INT)");
087: stat.execute("CREATE INDEX IDX_ND ON TEST(ID DESC)");
088: rs = conn.getMetaData().getIndexInfo(null, null, "TEST", false,
089: false);
090: rs.next();
091: check(rs.getString("ASC_OR_DESC"), "D");
092: check(rs.getInt("SORT_TYPE"), SortOrder.DESCENDING);
093: stat
094: .execute("INSERT INTO TEST SELECT X FROM SYSTEM_RANGE(1, 30)");
095: rs = stat
096: .executeQuery("SELECT COUNT(*) FROM TEST WHERE ID BETWEEN 10 AND 20");
097: rs.next();
098: check(rs.getInt(1), 11);
099: reconnect();
100: rs = conn.getMetaData().getIndexInfo(null, null, "TEST", false,
101: false);
102: rs.next();
103: check(rs.getString("ASC_OR_DESC"), "D");
104: check(rs.getInt("SORT_TYPE"), SortOrder.DESCENDING);
105: rs = stat
106: .executeQuery("SELECT COUNT(*) FROM TEST WHERE ID BETWEEN 10 AND 20");
107: rs.next();
108: check(rs.getInt(1), 11);
109: stat.execute("DROP TABLE TEST");
110: conn.close();
111: }
112:
113: String getRandomString(int len) {
114: StringBuffer buff = new StringBuffer();
115: for (int i = 0; i < len; i++) {
116: buff.append((char) ('a' + random.nextInt(26)));
117: }
118: return buff.toString();
119: }
120:
121: void testWideIndex(int length) throws Exception {
122: reconnect();
123: stat.execute("CREATE TABLE TEST(ID INT, NAME VARCHAR)");
124: stat.execute("CREATE INDEX IDXNAME ON TEST(NAME)");
125: for (int i = 0; i < 100; i++) {
126: stat.execute("INSERT INTO TEST VALUES(" + i + ", SPACE("
127: + length + ") || " + i + " )");
128: }
129: ResultSet rs = stat
130: .executeQuery("SELECT * FROM TEST ORDER BY NAME");
131: while (rs.next()) {
132: int id = rs.getInt("ID");
133: String name = rs.getString("NAME");
134: check("" + id, name.trim());
135: }
136: if (!config.memory) {
137: reconnect();
138: rs = stat.executeQuery("SELECT * FROM TEST ORDER BY NAME");
139: while (rs.next()) {
140: int id = rs.getInt("ID");
141: String name = rs.getString("NAME");
142: check("" + id, name.trim());
143: }
144: }
145: stat.execute("DROP TABLE TEST");
146: }
147:
148: void testLike() throws Exception {
149: reconnect();
150: stat.execute("CREATE TABLE ABC(ID INT, NAME VARCHAR)");
151: stat.execute("INSERT INTO ABC VALUES(1, 'Hello')");
152: PreparedStatement prep = conn
153: .prepareStatement("SELECT * FROM ABC WHERE NAME LIKE CAST(? AS VARCHAR)");
154: prep.setString(1, "Hi%");
155: prep.execute();
156: stat.execute("DROP TABLE ABC");
157: }
158:
159: void testConstraint() throws Exception {
160: if (config.memory) {
161: return;
162: }
163: stat.execute("CREATE TABLE PARENT(ID INT PRIMARY KEY)");
164: stat
165: .execute("CREATE TABLE CHILD(ID INT PRIMARY KEY, PID INT, FOREIGN KEY(PID) REFERENCES PARENT(ID))");
166: reconnect();
167: stat.execute("DROP TABLE PARENT");
168: stat.execute("DROP TABLE CHILD");
169: }
170:
171: void testLargeIndex() throws Exception {
172: random.setSeed(10);
173: for (int i = 1; i < 100; i += getSize(1000, 3)) {
174: stat.execute("DROP TABLE IF EXISTS TEST");
175: stat.execute("CREATE TABLE TEST(NAME VARCHAR(" + i + "))");
176: stat.execute("CREATE INDEX IDXNAME ON TEST(NAME)");
177: PreparedStatement prep = conn
178: .prepareStatement("INSERT INTO TEST VALUES(?)");
179: for (int j = 0; j < getSize(2, 5); j++) {
180: prep.setString(1, getRandomString(i));
181: prep.execute();
182: }
183: if (!config.memory) {
184: conn.close();
185: conn = getConnection("index");
186: stat = conn.createStatement();
187: }
188: ResultSet rs = stat
189: .executeQuery("SELECT COUNT(*) FROM TEST WHERE NAME > 'mdd'");
190: rs.next();
191: int count = rs.getInt(1);
192: trace(i + " count=" + count);
193: }
194:
195: stat.execute("DROP TABLE IF EXISTS TEST");
196: }
197:
198: void testHashIndex(boolean primaryKey, boolean hash)
199: throws Exception {
200: if (config.memory) {
201: return;
202: }
203:
204: reconnect();
205:
206: stat.execute("DROP TABLE IF EXISTS TEST");
207: if (primaryKey) {
208: stat.execute("CREATE TABLE TEST(A INT PRIMARY KEY "
209: + (hash ? "HASH" : "") + ", B INT)");
210: } else {
211: stat.execute("CREATE TABLE TEST(A INT, B INT)");
212: stat.execute("CREATE UNIQUE " + (hash ? "HASH" : "")
213: + " INDEX ON TEST(A)");
214: }
215: PreparedStatement prep;
216: prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?)");
217: int len = getSize(5, 1000);
218: for (int a = 0; a < len; a++) {
219: prep.setInt(1, a);
220: prep.setInt(2, a);
221: prep.execute();
222: check(1, getValue(stat,
223: "SELECT COUNT(*) FROM TEST WHERE A=" + a));
224: check(0, getValue(stat,
225: "SELECT COUNT(*) FROM TEST WHERE A=-1-" + a));
226: }
227:
228: reconnect();
229:
230: prep = conn.prepareStatement("DELETE FROM TEST WHERE A=?");
231: for (int a = 0; a < len; a++) {
232: if (getValue(stat, "SELECT COUNT(*) FROM TEST WHERE A=" + a) != 1) {
233: check(1, getValue(stat,
234: "SELECT COUNT(*) FROM TEST WHERE A=" + a));
235: }
236: prep.setInt(1, a);
237: check(1, prep.executeUpdate());
238: }
239: check(0, getValue(stat, "SELECT COUNT(*) FROM TEST"));
240: }
241:
242: void testMultiColumnIndex() throws Exception {
243: stat.execute("DROP TABLE IF EXISTS TEST");
244: stat.execute("CREATE TABLE TEST(A INT, B INT)");
245: PreparedStatement prep;
246: prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?)");
247: int len = getSize(3, 260);
248: for (int a = 0; a < len; a++) {
249: prep.setInt(1, a);
250: prep.setInt(2, a);
251: prep.execute();
252: }
253: stat.execute("INSERT INTO TEST SELECT A, B FROM TEST");
254: stat.execute("CREATE INDEX ON TEST(A, B)");
255: prep = conn.prepareStatement("DELETE FROM TEST WHERE A=?");
256: for (int a = 0; a < len; a++) {
257: log(stat, "SELECT * FROM TEST");
258: check(2, getValue(stat,
259: "SELECT COUNT(*) FROM TEST WHERE A="
260: + (len - a - 1)));
261: check((len - a) * 2, getValue(stat,
262: "SELECT COUNT(*) FROM TEST"));
263: prep.setInt(1, (len - a - 1));
264: prep.execute();
265: }
266: check(0, getValue(stat, "SELECT COUNT(*) FROM TEST"));
267: }
268:
269: void testMultiColumnHashIndex() throws Exception {
270: if (config.memory) {
271: return;
272: }
273:
274: stat.execute("DROP TABLE IF EXISTS TEST");
275: stat
276: .execute("CREATE TABLE TEST(A INT, B INT, DATA VARCHAR(255))");
277: stat.execute("CREATE UNIQUE HASH INDEX IDX_AB ON TEST(A, B)");
278: PreparedStatement prep;
279: prep = conn
280: .prepareStatement("INSERT INTO TEST VALUES(?, ?, ?)");
281: // speed is quadratic (len*len)
282: int len = getSize(2, 14);
283: for (int a = 0; a < len; a++) {
284: for (int b = 0; b < len; b += 2) {
285: prep.setInt(1, a);
286: prep.setInt(2, b);
287: prep.setString(3, "i(" + a + "," + b + ")");
288: prep.execute();
289: }
290: }
291:
292: reconnect();
293:
294: prep = conn
295: .prepareStatement("UPDATE TEST SET DATA=DATA||? WHERE A=? AND B=?");
296: for (int a = 0; a < len; a++) {
297: for (int b = 0; b < len; b += 2) {
298: prep.setString(1, "u(" + a + "," + b + ")");
299: prep.setInt(2, a);
300: prep.setInt(3, b);
301: prep.execute();
302: }
303: }
304:
305: reconnect();
306:
307: ResultSet rs = stat
308: .executeQuery("SELECT * FROM TEST WHERE DATA <> 'i('||a||','||b||')u('||a||','||b||')'");
309: checkFalse(rs.next());
310: check(len * (len / 2), getValue(stat,
311: "SELECT COUNT(*) FROM TEST"));
312: stat.execute("DROP TABLE TEST");
313: }
314:
315: int getValue(Statement stat, String sql) throws Exception {
316: ResultSet rs = stat.executeQuery(sql);
317: rs.next();
318: return rs.getInt(1);
319: }
320:
321: void log(Statement stat, String sql) throws Exception {
322: trace(sql);
323: ResultSet rs = stat.executeQuery(sql);
324: int cols = rs.getMetaData().getColumnCount();
325: while (rs.next()) {
326: StringBuffer buff = new StringBuffer();
327: for (int i = 0; i < cols; i++) {
328: if (i > 0) {
329: buff.append(", ");
330: }
331: buff.append("[" + i + "]=" + rs.getString(i + 1));
332: }
333: trace(buff.toString());
334: }
335: trace("---done---");
336: }
337:
338: }
|