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.StringTokenizer;
013:
014: import org.h2.test.TestBase;
015:
016: /**
017: * Fulltext search tests.
018: */
019: public class TestFullText extends TestBase {
020:
021: public void test() throws Exception {
022: if (config.memory) {
023: return;
024: }
025: test(false);
026: testPerformance(false);
027: String luceneFullTextClassName = "org.h2.fulltext.FullTextLucene";
028: try {
029: Class.forName(luceneFullTextClassName);
030: test(true);
031: testPerformance(true);
032: } catch (ClassNotFoundException e) {
033: println("Class not found, not tested: "
034: + luceneFullTextClassName);
035: // ok
036: } catch (NoClassDefFoundError e) {
037: println("Class not found, not tested: "
038: + luceneFullTextClassName);
039: // ok
040: }
041:
042: }
043:
044: private void testPerformance(boolean lucene) throws Exception {
045: deleteDb("fullText");
046: Connection conn = getConnection("fullText");
047: String prefix = lucene ? "FTL" : "FT";
048: Statement stat = conn.createStatement();
049: String className = lucene ? "FullTextLucene" : "FullText";
050: stat.execute("CREATE ALIAS IF NOT EXISTS " + prefix
051: + "_INIT FOR \"org.h2.fulltext." + className
052: + ".init\"");
053: stat.execute("CALL " + prefix + "_INIT()");
054: stat.execute("DROP TABLE IF EXISTS TEST");
055: stat
056: .execute("CREATE TABLE TEST AS SELECT * FROM INFORMATION_SCHEMA.HELP");
057: stat.execute("ALTER TABLE TEST ALTER COLUMN ID INT NOT NULL");
058: stat.execute("CREATE PRIMARY KEY ON TEST(ID)");
059: long time = System.currentTimeMillis();
060: stat.execute("CALL " + prefix
061: + "_CREATE_INDEX('PUBLIC', 'TEST', NULL)");
062: println("create " + prefix + ": "
063: + (System.currentTimeMillis() - time));
064: PreparedStatement prep = conn.prepareStatement("SELECT * FROM "
065: + prefix + "_SEARCH(?, 0, 0)");
066: time = System.currentTimeMillis();
067: ResultSet rs = stat.executeQuery("SELECT TEXT FROM TEST");
068: int count = 0;
069: while (rs.next()) {
070: String text = rs.getString(1);
071: StringTokenizer tokenizer = new StringTokenizer(text,
072: " ()[].,;:-+*/!?=<>{}#@'\"~$_%&|");
073: while (tokenizer.hasMoreTokens()) {
074: String word = tokenizer.nextToken();
075: if (word.length() < 10) {
076: continue;
077: }
078: prep.setString(1, word);
079: ResultSet rs2 = prep.executeQuery();
080: while (rs2.next()) {
081: rs2.getString(1);
082: count++;
083: }
084: }
085: }
086: println("search " + prefix + ": "
087: + (System.currentTimeMillis() - time) + " count: "
088: + count);
089: stat.execute("CALL " + prefix + "_DROP_ALL()");
090: conn.close();
091: }
092:
093: private void test(boolean lucene) throws Exception {
094: deleteDb("fullText");
095: Connection conn = getConnection("fullText");
096: String prefix = lucene ? "FTL_" : "FT_";
097: Statement stat = conn.createStatement();
098: String className = lucene ? "FullTextLucene" : "FullText";
099: stat
100: .execute("CREATE ALIAS IF NOT EXISTS " + prefix
101: + "INIT FOR \"org.h2.fulltext." + className
102: + ".init\"");
103: stat.execute("CALL " + prefix + "INIT()");
104: stat.execute("DROP TABLE IF EXISTS TEST");
105: stat
106: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
107: stat.execute("INSERT INTO TEST VALUES(1, 'Hello World')");
108: stat.execute("CALL " + prefix
109: + "CREATE_INDEX('PUBLIC', 'TEST', NULL)");
110: ResultSet rs;
111: rs = stat.executeQuery("SELECT * FROM " + prefix
112: + "SEARCH('Hello', 0, 0)");
113: rs.next();
114: check(rs.getString(1), "\"PUBLIC\".\"TEST\" WHERE \"ID\"=1");
115: checkFalse(rs.next());
116: rs = stat.executeQuery("SELECT * FROM " + prefix
117: + "SEARCH('Hallo', 0, 0)");
118: checkFalse(rs.next());
119: stat.execute("INSERT INTO TEST VALUES(2, 'Hallo Welt')");
120: rs = stat.executeQuery("SELECT * FROM " + prefix
121: + "SEARCH('Hello', 0, 0)");
122: rs.next();
123: check(rs.getString(1), "\"PUBLIC\".\"TEST\" WHERE \"ID\"=1");
124: checkFalse(rs.next());
125: rs = stat.executeQuery("SELECT * FROM " + prefix
126: + "SEARCH('Hallo', 0, 0)");
127: rs.next();
128: check(rs.getString(1), "\"PUBLIC\".\"TEST\" WHERE \"ID\"=2");
129: checkFalse(rs.next());
130:
131: stat.execute("CALL " + prefix + "REINDEX()");
132: rs = stat.executeQuery("SELECT * FROM " + prefix
133: + "SEARCH('Hello', 0, 0)");
134: rs.next();
135: check(rs.getString(1), "\"PUBLIC\".\"TEST\" WHERE \"ID\"=1");
136: checkFalse(rs.next());
137: rs = stat.executeQuery("SELECT * FROM " + prefix
138: + "SEARCH('Hallo', 0, 0)");
139: rs.next();
140: check(rs.getString(1), "\"PUBLIC\".\"TEST\" WHERE \"ID\"=2");
141: checkFalse(rs.next());
142:
143: stat.execute("INSERT INTO TEST VALUES(3, 'Hello World')");
144: stat.execute("INSERT INTO TEST VALUES(4, 'Hello World')");
145: stat.execute("INSERT INTO TEST VALUES(5, 'Hello World')");
146:
147: rs = stat.executeQuery("SELECT * FROM " + prefix
148: + "SEARCH('World', 0, 0) ORDER BY QUERY");
149: rs.next();
150: check(rs.getString(1), "\"PUBLIC\".\"TEST\" WHERE \"ID\"=1");
151: rs.next();
152: check(rs.getString(1), "\"PUBLIC\".\"TEST\" WHERE \"ID\"=3");
153: rs.next();
154: check(rs.getString(1), "\"PUBLIC\".\"TEST\" WHERE \"ID\"=4");
155: rs.next();
156: check(rs.getString(1), "\"PUBLIC\".\"TEST\" WHERE \"ID\"=5");
157: checkFalse(rs.next());
158:
159: rs = stat.executeQuery("SELECT * FROM " + prefix
160: + "SEARCH('World', 1, 0)");
161: rs.next();
162: check(rs.getString(1).startsWith(
163: "\"PUBLIC\".\"TEST\" WHERE \"ID\"="));
164: checkFalse(rs.next());
165:
166: rs = stat.executeQuery("SELECT * FROM " + prefix
167: + "SEARCH('World', 0, 2) ORDER BY QUERY");
168: rs.next();
169: check(rs.getString(1).startsWith(
170: "\"PUBLIC\".\"TEST\" WHERE \"ID\"="));
171: rs.next();
172: check(rs.getString(1).startsWith(
173: "\"PUBLIC\".\"TEST\" WHERE \"ID\"="));
174: checkFalse(rs.next());
175:
176: rs = stat.executeQuery("SELECT * FROM " + prefix
177: + "SEARCH('World', 2, 1) ORDER BY QUERY");
178: rs.next();
179: check(rs.getString(1).startsWith(
180: "\"PUBLIC\".\"TEST\" WHERE \"ID\"="));
181: rs.next();
182: check(rs.getString(1).startsWith(
183: "\"PUBLIC\".\"TEST\" WHERE \"ID\"="));
184: checkFalse(rs.next());
185:
186: rs = stat.executeQuery("SELECT * FROM " + prefix
187: + "SEARCH('1', 0, 0)");
188: rs.next();
189: check(rs.getString(1), "\"PUBLIC\".\"TEST\" WHERE \"ID\"=1");
190: checkFalse(rs.next());
191: conn.close();
192:
193: conn = getConnection("fullText");
194: stat = conn.createStatement();
195: rs = stat.executeQuery("SELECT * FROM " + prefix
196: + "SEARCH('World', 0, 0)");
197:
198: stat.execute("CALL " + prefix + "DROP_ALL()");
199: rs = stat.executeQuery("SELECT * FROM " + prefix
200: + "SEARCH('World', 2, 1)");
201: stat.execute("CALL " + prefix + "DROP_ALL()");
202:
203: conn.close();
204:
205: }
206: }
|