001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (http://h2database.com/html/license.html).
004: * Initial Developer: H2 Group
005: */
006: package org.h2.fulltext;
007:
008: import java.sql.Connection;
009: import java.sql.DatabaseMetaData;
010: import java.sql.PreparedStatement;
011: import java.sql.ResultSet;
012: import java.sql.SQLException;
013: import java.sql.Statement;
014: import java.sql.Types;
015: import java.util.ArrayList;
016: import java.util.Arrays;
017: import java.util.HashMap;
018: import java.util.HashSet;
019: import java.util.Iterator;
020: import java.util.StringTokenizer;
021:
022: import org.h2.api.Trigger;
023: import org.h2.command.Parser;
024: import org.h2.engine.Session;
025: import org.h2.expression.Comparison;
026: import org.h2.expression.ConditionAndOr;
027: import org.h2.expression.Expression;
028: import org.h2.expression.ExpressionColumn;
029: import org.h2.expression.ValueExpression;
030: import org.h2.jdbc.JdbcConnection;
031: import org.h2.tools.SimpleResultSet;
032: import org.h2.util.ByteUtils;
033: import org.h2.util.ObjectUtils;
034: import org.h2.util.JdbcUtils;
035: import org.h2.util.StringUtils;
036: import org.h2.value.DataType;
037:
038: /**
039: * This class implements the native full text search.
040: */
041: public class FullText implements Trigger {
042:
043: private static final String TRIGGER_PREFIX = "FT_";
044: private static final String SCHEMA = "FT";
045:
046: /**
047: * The column name of the result set returned by the search method.
048: */
049: private static final String FIELD_QUERY = "QUERY";
050:
051: /**
052: * A column name of the result set returned by the searchData method.
053: */
054: private static final String FIELD_SCHEMA = "SCHEMA";
055:
056: /**
057: * A column name of the result set returned by the searchData method.
058: */
059: private static final String FIELD_TABLE = "TABLE";
060:
061: /**
062: * A column name of the result set returned by the searchData method.
063: */
064: private static final String FIELD_COLUMNS = "COLUMNS";
065:
066: /**
067: * A column name of the result set returned by the searchData method.
068: */
069: private static final String FIELD_KEYS = "KEYS";
070:
071: private IndexInfo index;
072: private int[] dataTypes;
073: private PreparedStatement prepInsertWord, prepInsertRow,
074: prepInsertMap;
075: private PreparedStatement prepDeleteRow, prepDeleteMap;
076: private PreparedStatement prepSelectRow;
077:
078: /**
079: * Create a new full text index for a table and column list. Each table may
080: * only have one index at any time.
081: *
082: * @param conn the connection
083: * @param schema the schema name of the table
084: * @param table the table name
085: * @param columnList the column list (null for all columns)
086: */
087: public static void createIndex(Connection conn, String schema,
088: String table, String columnList) throws SQLException {
089: init(conn);
090: PreparedStatement prep = conn.prepareStatement("INSERT INTO "
091: + SCHEMA
092: + ".INDEXES(SCHEMA, TABLE, COLUMNS) VALUES(?, ?, ?)");
093: prep.setString(1, schema);
094: prep.setString(2, table);
095: prep.setString(3, columnList);
096: prep.execute();
097: createTrigger(conn, schema, table);
098: indexExistingRows(conn, schema, table);
099: }
100:
101: private static void createTrigger(Connection conn, String schema,
102: String table) throws SQLException {
103: Statement stat = conn.createStatement();
104: String trigger = StringUtils.quoteIdentifier(schema) + "."
105: + StringUtils.quoteIdentifier(TRIGGER_PREFIX + table);
106: stat.execute("DROP TRIGGER IF EXISTS " + trigger);
107: StringBuffer buff = new StringBuffer(
108: "CREATE TRIGGER IF NOT EXISTS ");
109: buff.append(trigger);
110: buff.append(" AFTER INSERT, UPDATE, DELETE ON ");
111: buff.append(StringUtils.quoteIdentifier(schema) + "."
112: + StringUtils.quoteIdentifier(table));
113: buff.append(" FOR EACH ROW CALL \"");
114: buff.append(FullText.class.getName());
115: buff.append("\"");
116: stat.execute(buff.toString());
117: }
118:
119: private static void indexExistingRows(Connection conn,
120: String schema, String table) throws SQLException {
121: FullText existing = new FullText();
122: existing.init(conn, schema, null, table, false, INSERT);
123: StringBuffer buff = new StringBuffer("SELECT * FROM ");
124: buff.append(StringUtils.quoteIdentifier(schema) + "."
125: + StringUtils.quoteIdentifier(table));
126: ResultSet rs = conn.createStatement().executeQuery(
127: buff.toString());
128: int columnCount = rs.getMetaData().getColumnCount();
129: while (rs.next()) {
130: Object[] row = new Object[columnCount];
131: for (int i = 0; i < columnCount; i++) {
132: row[i] = rs.getObject(i + 1);
133: }
134: existing.fire(conn, null, row);
135: }
136: }
137:
138: /**
139: * Re-creates the full text index for this database
140: *
141: * @param conn the connection
142: */
143: public static void reindex(Connection conn) throws SQLException {
144: init(conn);
145: removeAllTriggers(conn);
146: FullTextSettings setting = FullTextSettings.getInstance(conn);
147: setting.getWordList().clear();
148: Statement stat = conn.createStatement();
149: stat.execute("TRUNCATE TABLE " + SCHEMA + ".WORDS");
150: stat.execute("TRUNCATE TABLE " + SCHEMA + ".ROWS");
151: stat.execute("TRUNCATE TABLE " + SCHEMA + ".MAP");
152: ResultSet rs = stat.executeQuery("SELECT * FROM " + SCHEMA
153: + ".INDEXES");
154: while (rs.next()) {
155: String schema = rs.getString("SCHEMA");
156: String table = rs.getString("TABLE");
157: createTrigger(conn, schema, table);
158: indexExistingRows(conn, schema, table);
159: }
160: }
161:
162: /**
163: * Change the ignore list. The ignore list is a comma separated list of
164: * common words that must not be indexed. The default ignore list is empty.
165: * If indexes already exist at the time this list is changed, reindex must
166: * be called.
167: *
168: * @param conn the connection
169: * @param commaSeparatedList the list
170: */
171: public static void setIgnoreList(Connection conn,
172: String commaSeparatedList) throws SQLException {
173: init(conn);
174: FullTextSettings setting = FullTextSettings.getInstance(conn);
175: setIgnoreList(setting, commaSeparatedList);
176: Statement stat = conn.createStatement();
177: stat.execute("TRUNCATE TABLE " + SCHEMA + ".IGNORELIST");
178: PreparedStatement prep = conn.prepareStatement("INSERT INTO "
179: + SCHEMA + ".IGNORELIST VALUES(?)");
180: prep.setString(1, commaSeparatedList);
181: prep.execute();
182: }
183:
184: private static void setIgnoreList(FullTextSettings setting,
185: String commaSeparatedList) {
186: String[] list = StringUtils.arraySplit(commaSeparatedList, ',',
187: true);
188: HashSet set = setting.getIgnoreList();
189: for (int i = 0; i < list.length; i++) {
190: String word = list[i];
191: word = setting.convertWord(word);
192: if (word != null) {
193: set.add(list[i]);
194: }
195: }
196: }
197:
198: private static void removeAllTriggers(Connection conn)
199: throws SQLException {
200: Statement stat = conn.createStatement();
201: ResultSet rs = stat
202: .executeQuery("SELECT * FROM INFORMATION_SCHEMA.TRIGGERS");
203: Statement stat2 = conn.createStatement();
204: while (rs.next()) {
205: String schema = rs.getString("TRIGGER_SCHEMA");
206: String name = rs.getString("TRIGGER_NAME");
207: if (name.startsWith(TRIGGER_PREFIX)) {
208: name = StringUtils.quoteIdentifier(schema) + "."
209: + StringUtils.quoteIdentifier(name);
210: stat2.execute("DROP TRIGGER " + name);
211: }
212: }
213: }
214:
215: /**
216: * Drops all full text indexes from the database.
217: *
218: * @param conn the connection
219: */
220: public static void dropAll(Connection conn) throws SQLException {
221: init(conn);
222: Statement stat = conn.createStatement();
223: stat.execute("DROP SCHEMA IF EXISTS " + SCHEMA);
224: removeAllTriggers(conn);
225: FullTextSettings setting = FullTextSettings.getInstance(conn);
226: setting.getIgnoreList().clear();
227: setting.getWordList().clear();
228: }
229:
230: /**
231: * Initializes full text search functionality for this database. This adds
232: * the following Java functions to the database:
233: * <ul>
234: * <li>FT_CREATE_INDEX(schemaNameString, tableNameString, columnListString)
235: * </li><li>FT_SEARCH(queryString, limitInt, offsetInt): result set
236: * </li><li>FT_REINDEX()
237: * </li><li>FT_DROP_ALL()
238: * </li></ul>
239: * It also adds a schema FULLTEXT to the database where bookkeeping
240: * information is stored. This function may be called from a Java
241: * application, or by using the SQL statements:
242: * <pre>
243: * CREATE ALIAS IF NOT EXISTS FULLTEXT_INIT FOR
244: * "org.h2.fulltext.FullText.init";
245: * CALL FULLTEXT_INIT();
246: * </pre>
247: *
248: * @param conn the connection
249: */
250: public static void init(Connection conn) throws SQLException {
251: Statement stat = conn.createStatement();
252: stat.execute("CREATE SCHEMA IF NOT EXISTS " + SCHEMA);
253: stat
254: .execute("CREATE TABLE IF NOT EXISTS "
255: + SCHEMA
256: + ".INDEXES(ID INT AUTO_INCREMENT PRIMARY KEY, SCHEMA VARCHAR, TABLE VARCHAR, COLUMNS VARCHAR, UNIQUE(SCHEMA, TABLE))");
257: stat
258: .execute("CREATE MEMORY TABLE IF NOT EXISTS "
259: + SCHEMA
260: + ".WORDS(ID INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR, UNIQUE(NAME))");
261: stat
262: .execute("CREATE TABLE IF NOT EXISTS "
263: + SCHEMA
264: + ".ROWS(ID IDENTITY, HASH INT, INDEXID INT, KEY VARCHAR, UNIQUE(HASH, INDEXID, KEY))");
265:
266: stat
267: .execute("CREATE TABLE IF NOT EXISTS "
268: + SCHEMA
269: + ".MAP(ROWID INT, WORDID INT, PRIMARY KEY(WORDID, ROWID))");
270:
271: stat.execute("CREATE TABLE IF NOT EXISTS " + SCHEMA
272: + ".IGNORELIST(LIST VARCHAR)");
273: stat
274: .execute("CREATE ALIAS IF NOT EXISTS FT_CREATE_INDEX FOR \""
275: + FullText.class.getName() + ".createIndex\"");
276: stat.execute("CREATE ALIAS IF NOT EXISTS FT_SEARCH FOR \""
277: + FullText.class.getName() + ".search\"");
278: stat.execute("CREATE ALIAS IF NOT EXISTS FT_SEARCH_DATA FOR \""
279: + FullText.class.getName() + ".searchData\"");
280: stat.execute("CREATE ALIAS IF NOT EXISTS FT_REINDEX FOR \""
281: + FullText.class.getName() + ".reindex\"");
282: stat.execute("CREATE ALIAS IF NOT EXISTS FT_DROP_ALL FOR \""
283: + FullText.class.getName() + ".dropAll\"");
284: FullTextSettings setting = FullTextSettings.getInstance(conn);
285: ResultSet rs = stat.executeQuery("SELECT * FROM " + SCHEMA
286: + ".IGNORELIST");
287: while (rs.next()) {
288: String commaSeparatedList = rs.getString(1);
289: setIgnoreList(setting, commaSeparatedList);
290: }
291: rs = stat.executeQuery("SELECT * FROM " + SCHEMA + ".WORDS");
292: HashMap map = setting.getWordList();
293: while (rs.next()) {
294: String word = rs.getString("NAME");
295: int id = rs.getInt("ID");
296: word = setting.convertWord(word);
297: if (word != null) {
298: map.put(word, ObjectUtils.getInteger(id));
299: }
300: }
301: }
302:
303: /**
304: * INTERNAL
305: */
306: public void init(Connection conn, String schemaName,
307: String triggerName, String tableName, boolean before,
308: int type) throws SQLException {
309: init(conn);
310: FullTextSettings setting = FullTextSettings.getInstance(conn);
311: ArrayList keyList = new ArrayList();
312: DatabaseMetaData meta = conn.getMetaData();
313: ResultSet rs = meta.getColumns(null, schemaName, tableName,
314: null);
315: ArrayList columnList = new ArrayList();
316: while (rs.next()) {
317: columnList.add(rs.getString("COLUMN_NAME"));
318: }
319: dataTypes = new int[columnList.size()];
320: index = new IndexInfo();
321: index.schemaName = schemaName;
322: index.tableName = tableName;
323: index.columnNames = new String[columnList.size()];
324: columnList.toArray(index.columnNames);
325: rs = meta.getColumns(null, schemaName, tableName, null);
326: for (int i = 0; rs.next(); i++) {
327: dataTypes[i] = rs.getInt("DATA_TYPE");
328: }
329: if (keyList.size() == 0) {
330: rs = meta.getPrimaryKeys(null, schemaName, tableName);
331: while (rs.next()) {
332: keyList.add(rs.getString("COLUMN_NAME"));
333: }
334: }
335: if (keyList.size() == 0) {
336: throw new SQLException("No primary key for table "
337: + tableName);
338: }
339: ArrayList indexList = new ArrayList();
340: PreparedStatement prep = conn
341: .prepareStatement("SELECT ID, COLUMNS FROM " + SCHEMA
342: + ".INDEXES WHERE SCHEMA=? AND TABLE=?");
343: prep.setString(1, schemaName);
344: prep.setString(2, tableName);
345: rs = prep.executeQuery();
346: if (rs.next()) {
347: index.id = rs.getInt(1);
348: String columns = rs.getString(2);
349: if (columns != null) {
350: String[] list = StringUtils.arraySplit(columns, ',',
351: true);
352: for (int i = 0; i < list.length; i++) {
353: indexList.add(list[i]);
354: }
355: }
356: }
357: if (indexList.size() == 0) {
358: indexList.addAll(columnList);
359: }
360: index.keys = new int[keyList.size()];
361: setColumns(index.keys, keyList, columnList);
362: index.indexColumns = new int[indexList.size()];
363: setColumns(index.indexColumns, indexList, columnList);
364: setting.addIndexInfo(index);
365: prepInsertWord = conn.prepareStatement("INSERT INTO " + SCHEMA
366: + ".WORDS(NAME) VALUES(?)");
367: prepInsertRow = conn.prepareStatement("INSERT INTO " + SCHEMA
368: + ".ROWS(HASH, INDEXID, KEY) VALUES(?, ?, ?)");
369: prepInsertMap = conn.prepareStatement("INSERT INTO " + SCHEMA
370: + ".MAP(ROWID, WORDID) VALUES(?, ?)");
371: prepDeleteRow = conn.prepareStatement("DELETE FROM " + SCHEMA
372: + ".ROWS WHERE HASH=? AND INDEXID=? AND KEY=?");
373: prepDeleteMap = conn.prepareStatement("DELETE FROM " + SCHEMA
374: + ".MAP WHERE ROWID=? AND WORDID=?");
375: prepSelectRow = conn
376: .prepareStatement("SELECT ID FROM " + SCHEMA
377: + ".ROWS WHERE HASH=? AND INDEXID=? AND KEY=?");
378:
379: PreparedStatement prepSelectMapByWordId = conn
380: .prepareStatement("SELECT ROWID FROM " + SCHEMA
381: + ".MAP WHERE WORDID=?");
382: PreparedStatement prepSelectRowById = conn
383: .prepareStatement("SELECT KEY, INDEXID FROM " + SCHEMA
384: + ".ROWS WHERE ID=?");
385: setting.setPrepSelectMapByWordId(prepSelectMapByWordId);
386: setting.setPrepSelectRowById(prepSelectRowById);
387: }
388:
389: private void setColumns(int[] index, ArrayList keys,
390: ArrayList columns) throws SQLException {
391: for (int i = 0; i < keys.size(); i++) {
392: String key = (String) keys.get(i);
393: int found = -1;
394: for (int j = 0; found == -1 && j < columns.size(); j++) {
395: String column = (String) columns.get(j);
396: if (column.equals(key)) {
397: found = j;
398: }
399: }
400: if (found < 0) {
401: throw new SQLException("FULLTEXT", "Column not found: "
402: + key);
403: }
404: index[i] = found;
405: }
406: }
407:
408: /**
409: * INTERNAL
410: */
411: public void fire(Connection conn, Object[] oldRow, Object[] newRow)
412: throws SQLException {
413: FullTextSettings setting = FullTextSettings.getInstance(conn);
414: if (oldRow != null) {
415: delete(setting, oldRow);
416: }
417: if (newRow != null) {
418: insert(setting, newRow);
419: }
420: }
421:
422: private String getKey(Object[] row) throws SQLException {
423: StringBuffer buff = new StringBuffer();
424: for (int i = 0; i < index.keys.length; i++) {
425: if (i > 0) {
426: buff.append(" AND ");
427: }
428: int columnIndex = index.keys[i];
429: buff.append(StringUtils
430: .quoteIdentifier(index.columnNames[columnIndex]));
431: Object o = row[columnIndex];
432: if (o == null) {
433: buff.append(" IS NULL");
434: } else {
435: buff.append("=");
436: buff.append(quoteSQL(o, dataTypes[columnIndex]));
437: }
438: }
439: String key = buff.toString();
440: return key;
441: }
442:
443: private String quoteString(String data) {
444: if (data.indexOf('\'') < 0) {
445: return "'" + data + "'";
446: }
447: StringBuffer buff = new StringBuffer(data.length() + 2);
448: buff.append('\'');
449: for (int i = 0; i < data.length(); i++) {
450: char ch = data.charAt(i);
451: if (ch == '\'') {
452: buff.append(ch);
453: }
454: buff.append(ch);
455: }
456: buff.append('\'');
457: return buff.toString();
458: }
459:
460: private String quoteBinary(byte[] data) {
461: return "'" + ByteUtils.convertBytesToString(data) + "'";
462: }
463:
464: private String asString(Object data, int type) throws SQLException {
465: if (data == null) {
466: return "NULL";
467: }
468: switch (type) {
469: case Types.BIT:
470: case DataType.TYPE_BOOLEAN:
471: case Types.INTEGER:
472: case Types.BIGINT:
473: case Types.DECIMAL:
474: case Types.DOUBLE:
475: case Types.FLOAT:
476: case Types.NUMERIC:
477: case Types.REAL:
478: case Types.SMALLINT:
479: case Types.TINYINT:
480: case Types.DATE:
481: case Types.TIME:
482: case Types.TIMESTAMP:
483: case Types.LONGVARCHAR:
484: case Types.CHAR:
485: case Types.VARCHAR:
486: return data.toString();
487: case Types.VARBINARY:
488: case Types.LONGVARBINARY:
489: case Types.BINARY:
490: case Types.JAVA_OBJECT:
491: case Types.CLOB:
492: case Types.OTHER:
493: case Types.BLOB:
494: case Types.STRUCT:
495: case Types.REF:
496: case Types.NULL:
497: case Types.ARRAY:
498: case DataType.TYPE_DATALINK:
499: case Types.DISTINCT:
500: throw new SQLException("FULLTEXT",
501: "Unsupported column data type: " + type);
502: default:
503: return "";
504: }
505: }
506:
507: private String quoteSQL(Object data, int type) throws SQLException {
508: if (data == null) {
509: return "NULL";
510: }
511: switch (type) {
512: case Types.BIT:
513: case DataType.TYPE_BOOLEAN:
514: case Types.INTEGER:
515: case Types.BIGINT:
516: case Types.DECIMAL:
517: case Types.DOUBLE:
518: case Types.FLOAT:
519: case Types.NUMERIC:
520: case Types.REAL:
521: case Types.SMALLINT:
522: case Types.TINYINT:
523: return data.toString();
524: case Types.DATE:
525: case Types.TIME:
526: case Types.TIMESTAMP:
527: case Types.LONGVARCHAR:
528: case Types.CHAR:
529: case Types.VARCHAR:
530: return quoteString(data.toString());
531: case Types.VARBINARY:
532: case Types.LONGVARBINARY:
533: case Types.BINARY:
534: return quoteBinary((byte[]) data);
535: case Types.JAVA_OBJECT:
536: case Types.CLOB:
537: case Types.OTHER:
538: case Types.BLOB:
539: case Types.STRUCT:
540: case Types.REF:
541: case Types.NULL:
542: case Types.ARRAY:
543: case DataType.TYPE_DATALINK:
544: case Types.DISTINCT:
545: throw new SQLException("FULLTEXT",
546: "Unsupported key data type: " + type);
547: default:
548: return "";
549: }
550: }
551:
552: private static void addWords(FullTextSettings setting, HashSet set,
553: String text) {
554: StringTokenizer tokenizer = new StringTokenizer(text,
555: " \t\n\r\f+\"*%&/()=?'!,.;:-_#@|^~`{}[]");
556: while (tokenizer.hasMoreTokens()) {
557: String word = tokenizer.nextToken();
558: word = setting.convertWord(word);
559: if (word != null) {
560: set.add(word);
561: }
562: }
563: }
564:
565: private int[] getWordIds(FullTextSettings setting, Object[] row)
566: throws SQLException {
567: HashSet words = new HashSet();
568: for (int i = 0; i < index.indexColumns.length; i++) {
569: int idx = index.indexColumns[i];
570: String data = asString(row[idx], dataTypes[idx]);
571: addWords(setting, words, data);
572: }
573: HashMap allWords = setting.getWordList();
574: int[] wordIds = new int[words.size()];
575: Iterator it = words.iterator();
576: for (int i = 0; it.hasNext(); i++) {
577: String word = (String) it.next();
578: Integer wId = (Integer) allWords.get(word);
579: int wordId;
580: if (wId == null) {
581: prepInsertWord.setString(1, word);
582: prepInsertWord.execute();
583: ResultSet rs = JdbcUtils
584: .getGeneratedKeys(prepInsertWord);
585: rs.next();
586: wordId = rs.getInt(1);
587: allWords.put(word, ObjectUtils.getInteger(wordId));
588: } else {
589: wordId = wId.intValue();
590: }
591: wordIds[i] = wordId;
592: }
593: Arrays.sort(wordIds);
594: return wordIds;
595: }
596:
597: private void insert(FullTextSettings setting, Object[] row)
598: throws SQLException {
599: String key = getKey(row);
600: int hash = key.hashCode();
601: prepInsertRow.setInt(1, hash);
602: prepInsertRow.setInt(2, index.id);
603: prepInsertRow.setString(3, key);
604: prepInsertRow.execute();
605: ResultSet rs = JdbcUtils.getGeneratedKeys(prepInsertRow);
606: rs.next();
607: int rowId = rs.getInt(1);
608: prepInsertMap.setInt(1, rowId);
609: int[] wordIds = getWordIds(setting, row);
610: for (int i = 0; i < wordIds.length; i++) {
611: prepInsertMap.setInt(2, wordIds[i]);
612: prepInsertMap.execute();
613: }
614: }
615:
616: private void delete(FullTextSettings setting, Object[] row)
617: throws SQLException {
618: String key = getKey(row);
619: int hash = key.hashCode();
620: prepSelectRow.setInt(1, hash);
621: prepSelectRow.setInt(2, index.id);
622: prepSelectRow.setString(3, key);
623: ResultSet rs = prepSelectRow.executeQuery();
624: if (rs.next()) {
625: int rowId = rs.getInt(1);
626: prepDeleteMap.setInt(1, rowId);
627: int[] wordIds = getWordIds(setting, row);
628: for (int i = 0; i < wordIds.length; i++) {
629: prepDeleteMap.setInt(2, wordIds[i]);
630: prepDeleteMap.executeUpdate();
631: }
632: prepDeleteRow.setInt(1, hash);
633: prepDeleteRow.setInt(2, index.id);
634: prepDeleteRow.setString(3, key);
635: prepDeleteRow.executeUpdate();
636: }
637: }
638:
639: /**
640: * Searches from the full text index for this database. The result contains
641: * the primary key data as an array. The returned result set has the
642: * following columns:
643: * <ul>
644: * <li>SCHEMA (varchar): The schema name. Example: PUBLIC </li>
645: * <li>TABLE (varchar): The table name. Example: TEST </li>
646: * <li>COLUMNS (array of varchar): Comma separated list of quoted column
647: * names. The column names are quoted if necessary. Example: (ID) </li>
648: * <li>KEYS (array of values): Comma separated list of values. Example: (1)
649: * </li>
650: * </ul>
651: *
652: * @param conn the connection
653: * @param text the search query
654: * @param limit the maximum number of rows or 0 for no limit
655: * @param offset the offset or 0 for no offset
656: * @return the result set
657: */
658: public static ResultSet searchData(Connection conn, String text,
659: int limit, int offset) throws SQLException {
660: return search(conn, text, limit, offset, true);
661: }
662:
663: /**
664: * Searches from the full text index for this database.
665: * The returned result set has the following column:
666: * <ul><li>QUERY (varchar): The query to use to get the data.
667: * The query does not include 'SELECT * FROM '. Example:
668: * PUBLIC.TEST WHERE ID = 1
669: * </li></ul>
670: *
671: * @param conn the connection
672: * @param text the search query
673: * @param limit the maximum number of rows or 0 for no limit
674: * @param offset the offset or 0 for no offset
675: * @return the result set
676: */
677: public static ResultSet search(Connection conn, String text,
678: int limit, int offset) throws SQLException {
679: return search(conn, text, limit, offset, false);
680: }
681:
682: protected static SimpleResultSet createResultSet(boolean data)
683: throws SQLException {
684: SimpleResultSet result = new SimpleResultSet();
685: if (data) {
686: result
687: .addColumn(FullText.FIELD_SCHEMA, Types.VARCHAR, 0,
688: 0);
689: result.addColumn(FullText.FIELD_TABLE, Types.VARCHAR, 0, 0);
690: result.addColumn(FullText.FIELD_COLUMNS, Types.ARRAY, 0, 0);
691: result.addColumn(FullText.FIELD_KEYS, Types.ARRAY, 0, 0);
692: } else {
693: result.addColumn(FullText.FIELD_QUERY, Types.VARCHAR, 0, 0);
694: }
695: return result;
696: }
697:
698: private static ResultSet search(Connection conn, String text,
699: int limit, int offset, boolean data) throws SQLException {
700: SimpleResultSet result = createResultSet(data);
701: if (conn.getMetaData().getURL().startsWith("jdbc:columnlist:")) {
702: // this is just to query the result set columns
703: return result;
704: }
705: FullTextSettings setting = FullTextSettings.getInstance(conn);
706: HashSet words = new HashSet();
707: addWords(setting, words, text);
708: HashSet rIds = null, lastRowIds = null;
709: HashMap allWords = setting.getWordList();
710:
711: PreparedStatement prepSelectMapByWordId = setting
712: .getPrepSelectMapByWordId();
713: for (Iterator it = words.iterator(); it.hasNext();) {
714: lastRowIds = rIds;
715: rIds = new HashSet();
716: String word = (String) it.next();
717: Integer wId = (Integer) allWords.get(word);
718: if (wId == null) {
719: continue;
720: }
721: prepSelectMapByWordId.setInt(1, wId.intValue());
722: ResultSet rs = prepSelectMapByWordId.executeQuery();
723: while (rs.next()) {
724: Integer rId = ObjectUtils.getInteger(rs.getInt(1));
725: if (lastRowIds == null || lastRowIds.contains(rId)) {
726: rIds.add(rId);
727: }
728: }
729: }
730: if (rIds == null || rIds.size() == 0) {
731: return result;
732: }
733: PreparedStatement prepSelectRowById = setting
734: .getPrepSelectRowById();
735: int rowCount = 0;
736: for (Iterator it = rIds.iterator(); it.hasNext();) {
737: int rowId = ((Integer) it.next()).intValue();
738: prepSelectRowById.setInt(1, rowId);
739: ResultSet rs = prepSelectRowById.executeQuery();
740: if (!rs.next()) {
741: continue;
742: }
743: if (offset > 0) {
744: offset--;
745: } else {
746: String key = rs.getString(1);
747: int indexId = rs.getInt(2);
748: IndexInfo index = setting.getIndexInfo(indexId);
749: if (data) {
750: Object[][] columnData = parseKey(conn, key);
751: Object[] row = new Object[] { index.schemaName,
752: index.tableName, columnData[0],
753: columnData[1] };
754: result.addRow(row);
755: } else {
756: StringBuffer buff = new StringBuffer();
757: buff.append(StringUtils
758: .quoteIdentifier(index.schemaName));
759: buff.append('.');
760: buff.append(StringUtils
761: .quoteIdentifier(index.tableName));
762: buff.append(" WHERE ");
763: buff.append(key);
764: String query = buff.toString();
765: result.addRow(new String[] { query });
766: }
767: rowCount++;
768: if (limit > 0 && rowCount >= limit) {
769: break;
770: }
771: }
772: }
773: return result;
774: }
775:
776: protected static Object[][] parseKey(Connection conn, String key)
777: throws SQLException {
778: ArrayList columns = new ArrayList();
779: ArrayList data = new ArrayList();
780: JdbcConnection c = (JdbcConnection) conn;
781: Session session = (Session) c.getSession();
782: Parser p = new Parser(session);
783: Expression expr = p.parseExpression(key);
784: addColumnData(columns, data, expr);
785: Object[] col = new Object[columns.size()];
786: columns.toArray(col);
787: Object[] dat = new Object[columns.size()];
788: data.toArray(dat);
789: Object[][] columnData = new Object[][] { col, dat };
790: return columnData;
791: }
792:
793: private static void addColumnData(ArrayList columns,
794: ArrayList data, Expression expr) {
795: if (expr instanceof ConditionAndOr) {
796: ConditionAndOr and = (ConditionAndOr) expr;
797: Expression left = and.getExpression(true);
798: Expression right = and.getExpression(false);
799: addColumnData(columns, data, left);
800: addColumnData(columns, data, right);
801: } else {
802: Comparison comp = (Comparison) expr;
803: ExpressionColumn ec = (ExpressionColumn) comp
804: .getExpression(true);
805: ValueExpression ev = (ValueExpression) comp
806: .getExpression(false);
807: String columnName = ec.getColumnName();
808: columns.add(columnName);
809: if (ev == null) {
810: data.add(null);
811: } else {
812: data.add(ev.getValue(null).getString());
813: }
814: }
815: }
816:
817: }
|