File D:\maven-qcr-plugin-testsunitaires\qcr4java\h2-1.3.170-sources\target\site\qc\lecode\fulltext\FullText.java.html

1 /*
2 * Copyright 2004-2011 H2 Group. Multiple-Licensed under the H2 License,
3 * Version 1.0, and under the Eclipse Public License, Version 1.0
4 * (http://h2database.com/html/license.html).
5 * Initial Developer: H2 Group
6 */
7 package org.h2.fulltext;
8
9 import java.io.IOException;
10 import java.io.Reader;
11 import java.io.StreamTokenizer;
12 import java.sql.Clob;
13 import java.sql.Connection;
14 import java.sql.DatabaseMetaData;
15 import java.sql.PreparedStatement;
16 import java.sql.ResultSet;
17 import java.sql.SQLException;
18 import java.sql.Statement;
19 import java.sql.Types;
20 import java.util.ArrayList;
21 import java.util.Arrays;
22 import java.util.HashMap;
23 import java.util.HashSet;
24 import java.util.Iterator;
25 import java.util.StringTokenizer;
26 import java.util.UUID;
27 import org.h2.api.Trigger;
28 import org.h2.command.Parser;
29 import org.h2.engine.Session;
30 import org.h2.expression.Comparison;
31 import org.h2.expression.ConditionAndOr;
32 import org.h2.expression.Expression;
33 import org.h2.expression.ExpressionColumn;
34 import org.h2.expression.ValueExpression;
35 import org.h2.jdbc.JdbcConnection;
36 import org.h2.message.DbException;
37 import org.h2.tools.SimpleResultSet;
38 import org.h2.util.IOUtils;
39 import org.h2.util.JdbcUtils;
40 import org.h2.util.New;
41 import org.h2.util.StatementBuilder;
42 import org.h2.util.StringUtils;
43
44 /**
45 * This class implements the native full text search.
46 * Most methods can be called using SQL statements as well.
47 */
48 public class FullText {
49
50 /**
51 * A column name of the result set returned by the searchData method.
52 */
53 private static final String FIELD_SCHEMA = "SCHEMA";
54
55 /**
56 * A column name of the result set returned by the searchData method.
57 */
58 private static final String FIELD_TABLE = "TABLE";
59
60 /**
61 * A column name of the result set returned by the searchData method.
62 */
63 private static final String FIELD_COLUMNS = "COLUMNS";
64
65 /**
66 * A column name of the result set returned by the searchData method.
67 */
68 private static final String FIELD_KEYS = "KEYS";
69
70 /**
71 * The hit score.
72 */
73 private static final String FIELD_SCORE = "SCORE";
74
75 private static final String TRIGGER_PREFIX = "FT_";
76 private static final String SCHEMA = "FT";
77 private static final String SELECT_MAP_BY_WORD_ID = "SELECT ROWID FROM " + SCHEMA + ".MAP WHERE WORDID=?";
78 private static final String SELECT_ROW_BY_ID = "SELECT KEY, INDEXID FROM " + SCHEMA + ".ROWS WHERE ID=?";
79
80 /**
81 * The column name of the result set returned by the search method.
82 */
83 private static final String FIELD_QUERY = "QUERY";
84
85 /**
86 * Initializes full text search functionality for this database. This adds
87 * the following Java functions to the database:
88 * <ul>
89 * <li>FT_CREATE_INDEX(schemaNameString, tableNameString,
90 * columnListString)</li>
91 * <li>FT_SEARCH(queryString, limitInt, offsetInt): result set</li>
92 * <li>FT_REINDEX()</li>
93 * <li>FT_DROP_ALL()</li>
94 * </ul>
95 * It also adds a schema FT to the database where bookkeeping information
96 * is stored. This function may be called from a Java application, or by
97 * using the SQL statements:
98 *
99 * <pre>
100 * CREATE ALIAS IF NOT EXISTS FT_INIT FOR
101 * &quot;org.h2.fulltext.FullText.init&quot;;
102 * CALL FT_INIT();
103 * </pre>
104 *
105 * @param conn the connection
106 */
107 public static void init(Connection conn) throws SQLException {
108 Statement stat = conn.createStatement();
109 stat.execute("CREATE SCHEMA IF NOT EXISTS " + SCHEMA);
110 stat.execute("CREATE TABLE IF NOT EXISTS " + SCHEMA
111 + ".INDEXES(ID INT AUTO_INCREMENT PRIMARY KEY, SCHEMA VARCHAR, TABLE VARCHAR, COLUMNS VARCHAR, UNIQUE(SCHEMA, TABLE))");
112 stat.execute("CREATE TABLE IF NOT EXISTS " + SCHEMA
113 + ".WORDS(ID INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR, UNIQUE(NAME))");
114 stat.execute("CREATE TABLE IF NOT EXISTS " + SCHEMA
115 + ".ROWS(ID IDENTITY, HASH INT, INDEXID INT, KEY VARCHAR, UNIQUE(HASH, INDEXID, KEY))");
116 stat.execute("CREATE TABLE IF NOT EXISTS " + SCHEMA
117 + ".MAP(ROWID INT, WORDID INT, PRIMARY KEY(WORDID, ROWID))");
118 stat.execute("CREATE TABLE IF NOT EXISTS " + SCHEMA + ".IGNORELIST(LIST VARCHAR)");
119 stat.execute("CREATE TABLE IF NOT EXISTS " + SCHEMA + ".SETTINGS(KEY VARCHAR PRIMARY KEY, VALUE VARCHAR)");
120 stat.execute("CREATE ALIAS IF NOT EXISTS FT_CREATE_INDEX FOR \"" + FullText.class.getName() + ".createIndex\"");
121 stat.execute("CREATE ALIAS IF NOT EXISTS FT_DROP_INDEX FOR \"" + FullText.class.getName() + ".dropIndex\"");
122 stat.execute("CREATE ALIAS IF NOT EXISTS FT_SEARCH FOR \"" + FullText.class.getName() + ".search\"");
123 stat.execute("CREATE ALIAS IF NOT EXISTS FT_SEARCH_DATA FOR \"" + FullText.class.getName() + ".searchData\"");
124 stat.execute("CREATE ALIAS IF NOT EXISTS FT_REINDEX FOR \"" + FullText.class.getName() + ".reindex\"");
125 stat.execute("CREATE ALIAS IF NOT EXISTS FT_DROP_ALL FOR \"" + FullText.class.getName() + ".dropAll\"");
126 FullTextSettings setting = FullTextSettings.getInstance(conn);
127 ResultSet rs = stat.executeQuery("SELECT * FROM " + SCHEMA + ".IGNORELIST");
128 while (rs.next()) {
129 String commaSeparatedList = rs.getString(1);
130 setIgnoreList(setting, commaSeparatedList);
131 }
132 rs = stat.executeQuery("SELECT * FROM " + SCHEMA + ".SETTINGS");
133 while (rs.next()) {
134 String key = rs.getString(1);
135 if ("whitespaceChars".equals(key)) {
136 String value = rs.getString(2);
137 setting.setWhitespaceChars(value);
138 }
139 }
140 rs = stat.executeQuery("SELECT * FROM " + SCHEMA + ".WORDS");
141 HashMap<String, Integer> map = setting.getWordList();
142 while (rs.next()) {
143 String word = rs.getString("NAME");
144 int id = rs.getInt("ID");
145 word = setting.convertWord(word);
146 if (word != null) {
147 map.put(word, id);
148 }
149 }
150 setting.setInitialized(true);
151 }
152
153 /**
154 * Create a new full text index for a table and column list. Each table may
155 * only have one index at any time.
156 *
157 * @param conn the connection
158 * @param schema the schema name of the table (case sensitive)
159 * @param table the table name (case sensitive)
160 * @param columnList the column list (null for all columns)
161 */
162 public static void createIndex(Connection conn, String schema, String table, String columnList) throws SQLException {
163 init(conn);
164 PreparedStatement prep = conn.prepareStatement("INSERT INTO " + SCHEMA
165 + ".INDEXES(SCHEMA, TABLE, COLUMNS) VALUES(?, ?, ?)");
166 prep.setString(1, schema);
167 prep.setString(2, table);
168 prep.setString(3, columnList);
169 prep.execute();
170 createTrigger(conn, schema, table);
171 indexExistingRows(conn, schema, table);
172 }
173
174 /**
175 * Re-creates the full text index for this database. Calling this method is
176 * usually not needed, as the index is kept up-to-date automatically.
177 *
178 * @param conn the connection
179 */
180 public static void reindex(Connection conn) throws SQLException {
181 init(conn);
182 removeAllTriggers(conn, TRIGGER_PREFIX);
183 FullTextSettings setting = FullTextSettings.getInstance(conn);
184 setting.getWordList().clear();
185 Statement stat = conn.createStatement();
186 stat.execute("TRUNCATE TABLE " + SCHEMA + ".WORDS");
187 stat.execute("TRUNCATE TABLE " + SCHEMA + ".ROWS");
188 stat.execute("TRUNCATE TABLE " + SCHEMA + ".MAP");
189 ResultSet rs = stat.executeQuery("SELECT * FROM " + SCHEMA + ".INDEXES");
190 while (rs.next()) {
191 String schema = rs.getString("SCHEMA");
192 String table = rs.getString("TABLE");
193 createTrigger(conn, schema, table);
194 indexExistingRows(conn, schema, table);
195 }
196 }
197
198 /**
199 * Drop an existing full text index for a table. This method returns
200 * silently if no index for this table exists.
201 *
202 * @param conn the connection
203 * @param schema the schema name of the table (case sensitive)
204 * @param table the table name (case sensitive)
205 */
206 public static void dropIndex(Connection conn, String schema, String table) throws SQLException {
207 init(conn);
208 PreparedStatement prep = conn.prepareStatement("SELECT ID FROM " + SCHEMA
209 + ".INDEXES WHERE SCHEMA=? AND TABLE=?");
210 prep.setString(1, schema);
211 prep.setString(2, table);
212 ResultSet rs = prep.executeQuery();
213 if (!rs.next()) {
214 return;
215 }
216 int indexId = rs.getInt(1);
217 prep = conn.prepareStatement("DELETE FROM " + SCHEMA
218 + ".INDEXES WHERE ID=?");
219 prep.setInt(1, indexId);
220 prep.execute();
221 createOrDropTrigger(conn, schema, table, false);
222 prep = conn.prepareStatement("DELETE FROM " + SCHEMA +
223 ".ROWS WHERE INDEXID=? AND ROWNUM<10000");
224 while (true) {
225 prep.setInt(1, indexId);
226 int deleted = prep.executeUpdate();
227 if (deleted == 0) {
228 break;
229 }
230 }
231 prep = conn.prepareStatement("DELETE FROM " + SCHEMA + ".MAP M " +
232 "WHERE NOT EXISTS (SELECT * FROM " + SCHEMA + ".ROWS R WHERE R.ID=M.ROWID) AND ROWID<10000");
233 while (true) {
234 int deleted = prep.executeUpdate();
235 if (deleted == 0) {
236 break;
237 }
238 }
239 }
240
241 /**
242 * Drops all full text indexes from the database.
243 *
244 * @param conn the connection
245 */
246 public static void dropAll(Connection conn) throws SQLException {
247 init(conn);
248 Statement stat = conn.createStatement();
249 stat.execute("DROP SCHEMA IF EXISTS " + SCHEMA);
250 removeAllTriggers(conn, TRIGGER_PREFIX);
251 FullTextSettings setting = FullTextSettings.getInstance(conn);
252 setting.removeAllIndexes();
253 setting.getIgnoreList().clear();
254 setting.getWordList().clear();
255 }
256
257 /**
258 * Searches from the full text index for this database.
259 * The returned result set has the following column:
260 * <ul><li>QUERY (varchar): the query to use to get the data.
261 * The query does not include 'SELECT * FROM '. Example:
262 * PUBLIC.TEST WHERE ID = 1
263 * </li><li>SCORE (float) the relevance score. This value is always 1.0
264 * for the native fulltext search.
265 * </li></ul>
266 *
267 * @param conn the connection
268 * @param text the search query
269 * @param limit the maximum number of rows or 0 for no limit
270 * @param offset the offset or 0 for no offset
271 * @return the result set
272 */
273 public static ResultSet search(Connection conn, String text, int limit, int offset) throws SQLException {
274 try {
275 return search(conn, text, limit, offset, false);
276 } catch (DbException e) {
277 throw DbException.toSQLException(e);
278 }
279 }
280
281 /**
282 * Searches from the full text index for this database. The result contains
283 * the primary key data as an array. The returned result set has the
284 * following columns:
285 * <ul>
286 * <li>SCHEMA (varchar): the schema name. Example: PUBLIC </li>
287 * <li>TABLE (varchar): the table name. Example: TEST </li>
288 * <li>COLUMNS (array of varchar): comma separated list of quoted column
289 * names. The column names are quoted if necessary. Example: (ID) </li>
290 * <li>KEYS (array of values): comma separated list of values. Example: (1)
291 * </li>
292 * <li>SCORE (float) the relevance score. This value is always 1.0
293 * for the native fulltext search.
294 * </li>
295 * </ul>
296 *
297 * @param conn the connection
298 * @param text the search query
299 * @param limit the maximum number of rows or 0 for no limit
300 * @param offset the offset or 0 for no offset
301 * @return the result set
302 */
303 public static ResultSet searchData(Connection conn, String text, int limit, int offset) throws SQLException {
304 try {
305 return search(conn, text, limit, offset, true);
306 } catch (DbException e) {
307 throw DbException.toSQLException(e);
308 }
309 }
310
311 /**
312 * Change the ignore list. The ignore list is a comma separated list of
313 * common words that must not be indexed. The default ignore list is empty.
314 * If indexes already exist at the time this list is changed, reindex must
315 * be called.
316 *
317 * @param conn the connection
318 * @param commaSeparatedList the list
319 */
320 public static void setIgnoreList(Connection conn, String commaSeparatedList) throws SQLException {
321 try {
322 init(conn);
323 FullTextSettings setting = FullTextSettings.getInstance(conn);
324 setIgnoreList(setting, commaSeparatedList);
325 Statement stat = conn.createStatement();
326 stat.execute("TRUNCATE TABLE " + SCHEMA + ".IGNORELIST");
327 PreparedStatement prep = conn.prepareStatement("INSERT INTO " + SCHEMA + ".IGNORELIST VALUES(?)");
328 prep.setString(1, commaSeparatedList);
329 prep.execute();
330 } catch (DbException e) {
331 throw DbException.toSQLException(e);
332 }
333 }
334
335 /**
336 * Change the whitespace characters. The whitespace characters are used to
337 * separate words. If indexes already exist at the time this list is
338 * changed, reindex must be called.
339 *
340 * @param conn the connection
341 * @param whitespaceChars the list of characters
342 */
343 public static void setWhitespaceChars(Connection conn, String whitespaceChars) throws SQLException {
344 try {
345 init(conn);
346 FullTextSettings setting = FullTextSettings.getInstance(conn);
347 setting.setWhitespaceChars(whitespaceChars);
348 PreparedStatement prep = conn.prepareStatement("MERGE INTO " + SCHEMA + ".SETTINGS VALUES(?, ?)");
349 prep.setString(1, "whitespaceChars");
350 prep.setString(2, whitespaceChars);
351 prep.execute();
352 } catch (DbException e) {
353 throw DbException.toSQLException(e);
354 }
355 }
356
357 /**
358 * INTERNAL.
359 * Convert the object to a string.
360 *
361 * @param data the object
362 * @param type the SQL type
363 * @return the string
364 */
365 protected static String asString(Object data, int type) throws SQLException {
366 if (data == null) {
367 return "NULL";
368 }
369 switch (type) {
370 case Types.BIT:
371 case Types.BOOLEAN:
372 case Types.INTEGER:
373 case Types.BIGINT:
374 case Types.DECIMAL:
375 case Types.DOUBLE:
376 case Types.FLOAT:
377 case Types.NUMERIC:
378 case Types.REAL:
379 case Types.SMALLINT:
380 case Types.TINYINT:
381 case Types.DATE:
382 case Types.TIME:
383 case Types.TIMESTAMP:
384 case Types.LONGVARCHAR:
385 case Types.CHAR:
386 case Types.VARCHAR:
387 return data.toString();
388 case Types.CLOB:
389 try {
390 if (data instanceof Clob) {
391 data = ((Clob) data).getCharacterStream();
392 }
393 return IOUtils.readStringAndClose((Reader) data, -1);
394 } catch (IOException e) {
395 throw DbException.toSQLException(e);
396 }
397 case Types.VARBINARY:
398 case Types.LONGVARBINARY:
399 case Types.BINARY:
400 case Types.JAVA_OBJECT:
401 case Types.OTHER:
402 case Types.BLOB:
403 case Types.STRUCT:
404 case Types.REF:
405 case Types.NULL:
406 case Types.ARRAY:
407 case Types.DATALINK:
408 case Types.DISTINCT:
409 throw throwException("Unsupported column data type: " + type);
410 default:
411 return "";
412 }
413 }
414
415 /**
416 * Create an empty search result and initialize the columns.
417 *
418 * @param data true if the result set should contain the primary key data as
419 * an array.
420 * @return the empty result set
421 */
422 protected static SimpleResultSet createResultSet(boolean data) {
423 SimpleResultSet result = new SimpleResultSet();
424 if (data) {
425 result.addColumn(FullText.FIELD_SCHEMA, Types.VARCHAR, 0, 0);
426 result.addColumn(FullText.FIELD_TABLE, Types.VARCHAR, 0, 0);
427 result.addColumn(FullText.FIELD_COLUMNS, Types.ARRAY, 0, 0);
428 result.addColumn(FullText.FIELD_KEYS, Types.ARRAY, 0, 0);
429 } else {
430 result.addColumn(FullText.FIELD_QUERY, Types.VARCHAR, 0, 0);
431 }
432 result.addColumn(FullText.FIELD_SCORE, Types.FLOAT, 0, 0);
433 return result;
434 }
435
436 /**
437 * Parse a primary key condition into the primary key columns.
438 *
439 * @param conn the database connection
440 * @param key the primary key condition as a string
441 * @return an array containing the column name list and the data list
442 */
443 protected static Object[][] parseKey(Connection conn, String key) {
444 ArrayList<String> columns = New.arrayList();
445 ArrayList<String> data = New.arrayList();
446 JdbcConnection c = (JdbcConnection) conn;
447 Session session = (Session) c.getSession();
448 Parser p = new Parser(session);
449 Expression expr = p.parseExpression(key);
450 addColumnData(columns, data, expr);
451 Object[] col = new Object[columns.size()];
452 columns.toArray(col);
453 Object[] dat = new Object[columns.size()];
454 data.toArray(dat);
455 Object[][] columnData = { col, dat };
456 return columnData;
457 }
458
459 /**
460 * INTERNAL.
461 * Convert an object to a String as used in a SQL statement.
462 *
463 * @param data the object
464 * @param type the SQL type
465 * @return the SQL String
466 */
467 protected static String quoteSQL(Object data, int type) throws SQLException {
468 if (data == null) {
469 return "NULL";
470 }
471 switch (type) {
472 case Types.BIT:
473 case Types.BOOLEAN:
474 case Types.INTEGER:
475 case Types.BIGINT:
476 case Types.DECIMAL:
477 case Types.DOUBLE:
478 case Types.FLOAT:
479 case Types.NUMERIC:
480 case Types.REAL:
481 case Types.SMALLINT:
482 case Types.TINYINT:
483 return data.toString();
484 case Types.DATE:
485 case Types.TIME:
486 case Types.TIMESTAMP:
487 case Types.LONGVARCHAR:
488 case Types.CHAR:
489 case Types.VARCHAR:
490 return quoteString(data.toString());
491 case Types.VARBINARY:
492 case Types.LONGVARBINARY:
493 case Types.BINARY:
494 if (data instanceof UUID) {
495 return "'" + data.toString() + "'";
496 }
497 return "'" + StringUtils.convertBytesToHex((byte[]) data) + "'";
498 case Types.CLOB:
499 case Types.JAVA_OBJECT:
500 case Types.OTHER:
501 case Types.BLOB:
502 case Types.STRUCT:
503 case Types.REF:
504 case Types.NULL:
505 case Types.ARRAY:
506 case Types.DATALINK:
507 case Types.DISTINCT:
508 throw throwException("Unsupported key data type: " + type);
509 default:
510 return "";
511 }
512 }
513
514 /**
515 * Remove all triggers that start with the given prefix.
516 *
517 * @param conn the database connection
518 * @param prefix the prefix
519 */
520 protected static void removeAllTriggers(Connection conn, String prefix) throws SQLException {
521 Statement stat = conn.createStatement();
522 ResultSet rs = stat.executeQuery("SELECT * FROM INFORMATION_SCHEMA.TRIGGERS");
523 Statement stat2 = conn.createStatement();
524 while (rs.next()) {
525 String schema = rs.getString("TRIGGER_SCHEMA");
526 String name = rs.getString("TRIGGER_NAME");
527 if (name.startsWith(prefix)) {
528 name = StringUtils.quoteIdentifier(schema) + "." + StringUtils.quoteIdentifier(name);
529 stat2.execute("DROP TRIGGER " + name);
530 }
531 }
532 }
533
534 /**
535 * Set the column indices of a set of keys.
536 *
537 * @param index the column indices (will be modified)
538 * @param keys the key list
539 * @param columns the column list
540 */
541 protected static void setColumns(int[] index, ArrayList<String> keys, ArrayList<String> columns) throws SQLException {
542 for (int i = 0, keySize = keys.size(); i < keySize; i++) {
543 String key = keys.get(i);
544 int found = -1;
545 int columnsSize = columns.size();
546 for (int j = 0; found == -1 && j < columnsSize; j++) {
547 String column = columns.get(j);
548 if (column.equals(key)) {
549 found = j;
550 }
551 }
552 if (found < 0) {
553 throw throwException("Column not found: " + key);
554 }
555 index[i] = found;
556 }
557 }
558
559 /**
560 * Do the search.
561 *
562 * @param conn the database connection
563 * @param text the query
564 * @param limit the limit
565 * @param offset the offset
566 * @param data whether the raw data should be returned
567 * @return the result set
568 */
569 protected static ResultSet search(Connection conn, String text, int limit,
570 int offset, boolean data) throws SQLException {
571 SimpleResultSet result = createResultSet(data);
572 if (conn.getMetaData().getURL().startsWith("jdbc:columnlist:")) {
573 // this is just to query the result set columns
574 return result;
575 }
576 if (text == null || text.trim().length() == 0) {
577 return result;
578 }
579 FullTextSettings setting = FullTextSettings.getInstance(conn);
580 if (!setting.isInitialized()) {
581 init(conn);
582 }
583 HashSet<String> words = New.hashSet();
584 addWords(setting, words, text);
585 HashSet<Integer> rIds = null, lastRowIds = null;
586 HashMap<String, Integer> allWords = setting.getWordList();
587
588 PreparedStatement prepSelectMapByWordId = setting.prepare(conn, SELECT_MAP_BY_WORD_ID);
589 for (String word : words) {
590 lastRowIds = rIds;
591 rIds = New.hashSet();
592 Integer wId = allWords.get(word);
593 if (wId == null) {
594 continue;
595 }
596 prepSelectMapByWordId.setInt(1, wId.intValue());
597 ResultSet rs = prepSelectMapByWordId.executeQuery();
598 while (rs.next()) {
599 Integer rId = rs.getInt(1);
600 if (lastRowIds == null || lastRowIds.contains(rId)) {
601 rIds.add(rId);
602 }
603 }
604 }
605 if (rIds == null || rIds.size() == 0) {
606 return result;
607 }
608 PreparedStatement prepSelectRowById = setting.prepare(conn, SELECT_ROW_BY_ID);
609 int rowCount = 0;
610 for (int rowId : rIds) {
611 prepSelectRowById.setInt(1, rowId);
612 ResultSet rs = prepSelectRowById.executeQuery();
613 if (!rs.next()) {
614 continue;
615 }
616 if (offset > 0) {
617 offset--;
618 } else {
619 String key = rs.getString(1);
620 int indexId = rs.getInt(2);
621 IndexInfo index = setting.getIndexInfo(indexId);
622 if (data) {
623 Object[][] columnData = parseKey(conn, key);
624 result.addRow(
625 index.schema,
626 index.table,
627 columnData[0],
628 columnData[1],
629 1.0);
630 } else {
631 String query = StringUtils.quoteIdentifier(index.schema) +
632 "." + StringUtils.quoteIdentifier(index.table) +
633 " WHERE " + key;
634 result.addRow(query, 1.0);
635 }
636 rowCount++;
637 if (limit > 0 && rowCount >= limit) {
638 break;
639 }
640 }
641 }
642 return result;
643 }
644
645 private static void addColumnData(ArrayList<String> columns, ArrayList<String> data, Expression expr) {
646 if (expr instanceof ConditionAndOr) {
647 ConditionAndOr and = (ConditionAndOr) expr;
648 Expression left = and.getExpression(true);
649 Expression right = and.getExpression(false);
650 addColumnData(columns, data, left);
651 addColumnData(columns, data, right);
652 } else {
653 Comparison comp = (Comparison) expr;
654 ExpressionColumn ec = (ExpressionColumn) comp.getExpression(true);
655 ValueExpression ev = (ValueExpression) comp.getExpression(false);
656 String columnName = ec.getColumnName();
657 columns.add(columnName);
658 if (ev == null) {
659 data.add(null);
660 } else {
661 data.add(ev.getValue(null).getString());
662 }
663 }
664 }
665
666 /**
667 * Add all words in the given text to the hash set.
668 *
669 * @param setting the fulltext settings
670 * @param set the hash set
671 * @param reader the reader
672 */
673 protected static void addWords(FullTextSettings setting, HashSet<String> set, Reader reader) {
674 StreamTokenizer tokenizer = new StreamTokenizer(reader);
675 tokenizer.resetSyntax();
676 tokenizer.wordChars(' ' + 1, 255);
677 char[] whitespaceChars = setting.getWhitespaceChars().toCharArray();
678 for (char ch : whitespaceChars) {
679 tokenizer.whitespaceChars(ch, ch);
680 }
681 try {
682 while (true) {
683 int token = tokenizer.nextToken();
684 if (token == StreamTokenizer.TT_EOF) {
685 break;
686 } else if (token == StreamTokenizer.TT_WORD) {
687 String word = tokenizer.sval;
688 word = setting.convertWord(word);
689 if (word != null) {
690 set.add(word);
691 }
692 }
693 }
694 } catch (IOException e) {
695 throw DbException.convertIOException(e, "Tokenizer error");
696 }
697 }
698
699 /**
700 * Add all words in the given text to the hash set.
701 *
702 * @param setting the fulltext settings
703 * @param set the hash set
704 * @param text the text
705 */
706 protected static void addWords(FullTextSettings setting, HashSet<String> set, String text) {
707 String whitespaceChars = setting.getWhitespaceChars();
708 StringTokenizer tokenizer = new StringTokenizer(text, whitespaceChars);
709 while (tokenizer.hasMoreTokens()) {
710 String word = tokenizer.nextToken();
711 word = setting.convertWord(word);
712 if (word != null) {
713 set.add(word);
714 }
715 }
716 }
717
718 /**
719 * Create the trigger.
720 *
721 * @param conn the database connection
722 * @param schema the schema name
723 * @param table the table name
724 */
725 protected static void createTrigger(Connection conn, String schema, String table) throws SQLException {
726 createOrDropTrigger(conn, schema, table, true);
727 }
728
729 private static void createOrDropTrigger(Connection conn,
730 String schema, String table, boolean create) throws SQLException {
731 Statement stat = conn.createStatement();
732 String trigger = StringUtils.quoteIdentifier(schema) + "."
733 + StringUtils.quoteIdentifier(TRIGGER_PREFIX + table);
734 stat.execute("DROP TRIGGER IF EXISTS " + trigger);
735 if (create) {
736 StringBuilder buff = new StringBuilder("CREATE TRIGGER IF NOT EXISTS ");
737 // needs to be called on rollback as well, because we use the init connection
738 // do to changes in the index (not the user connection)
739 buff.append(trigger).
740 append(" AFTER INSERT, UPDATE, DELETE, ROLLBACK ON ").
741 append(StringUtils.quoteIdentifier(schema)).
742 append('.').
743 append(StringUtils.quoteIdentifier(table)).
744 append(" FOR EACH ROW CALL \"").
745 append(FullText.FullTextTrigger.class.getName()).
746 append('\"');
747 stat.execute(buff.toString());
748 }
749 }
750
751 /**
752 * Add the existing data to the index.
753 *
754 * @param conn the database connection
755 * @param schema the schema name
756 * @param table the table name
757 */
758 protected static void indexExistingRows(Connection conn, String schema, String table) throws SQLException {
759 FullText.FullTextTrigger existing = new FullText.FullTextTrigger();
760 existing.init(conn, schema, null, table, false, Trigger.INSERT);
761 String sql = "SELECT * FROM " + StringUtils.quoteIdentifier(schema) + "." + StringUtils.quoteIdentifier(table);
762 ResultSet rs = conn.createStatement().executeQuery(sql);
763 int columnCount = rs.getMetaData().getColumnCount();
764 while (rs.next()) {
765 Object[] row = new Object[columnCount];
766 for (int i = 0; i < columnCount; i++) {
767 row[i] = rs.getObject(i + 1);
768 }
769 existing.fire(conn, null, row);
770 }
771 }
772
773 private static String quoteString(String data) {
774 if (data.indexOf('\'') < 0) {
775 return "'" + data + "'";
776 }
777 int len = data.length();
778 StringBuilder buff = new StringBuilder(len + 2);
779 buff.append('\'');
780 for (int i = 0; i < len; i++) {
781 char ch = data.charAt(i);
782 if (ch == '\'') {
783 buff.append(ch);
784 }
785 buff.append(ch);
786 }
787 buff.append('\'');
788 return buff.toString();
789 }
790
791 private static void setIgnoreList(FullTextSettings setting, String commaSeparatedList) {
792 String[] list = StringUtils.arraySplit(commaSeparatedList, ',', true);
793 HashSet<String> set = setting.getIgnoreList();
794 for (String word : list) {
795 String converted = setting.convertWord(word);
796 if (converted != null) {
797 set.add(converted);
798 }
799 }
800 }
801
802 /**
803 * Check if a the indexed columns of a row probably have changed. It may
804 * return true even if the change was minimal (for example from 0.0 to
805 * 0.00).
806 *
807 * @param oldRow the old row
808 * @param newRow the new row
809 * @param indexColumns the indexed columns
810 * @return true if the indexed columns don't match
811 */
812 protected static boolean hasChanged(Object[] oldRow, Object[] newRow, int[] indexColumns) {
813 for (int c : indexColumns) {
814 Object o = oldRow[c], n = newRow[c];
815 if (o == null) {
816 if (n != null) {
817 return true;
818 }
819 } else if (!o.equals(n)) {
820 return true;
821 }
822 }
823 return false;
824 }
825
826 /**
827 * Trigger updates the index when a inserting, updating, or deleting a row.
828 */
829 public static class FullTextTrigger implements Trigger {
830
831 protected FullTextSettings setting;
832 protected IndexInfo index;
833 protected int[] columnTypes;
834 protected PreparedStatement prepInsertWord, prepInsertRow, prepInsertMap;
835 protected PreparedStatement prepDeleteRow, prepDeleteMap;
836 protected PreparedStatement prepSelectRow;
837
838 /**
839 * INTERNAL
840 */
841 public void init(Connection conn, String schemaName, String triggerName,
842 String tableName, boolean before, int type) throws SQLException {
843 setting = FullTextSettings.getInstance(conn);
844 if (!setting.isInitialized()) {
845 FullText.init(conn);
846 }
847 ArrayList<String> keyList = New.arrayList();
848 DatabaseMetaData meta = conn.getMetaData();
849 ResultSet rs = meta.getColumns(null,
850 JdbcUtils.escapeMetaDataPattern(schemaName),
851 JdbcUtils.escapeMetaDataPattern(tableName),
852 null);
853 ArrayList<String> columnList = New.arrayList();
854 while (rs.next()) {
855 columnList.add(rs.getString("COLUMN_NAME"));
856 }
857 columnTypes = new int[columnList.size()];
858 index = new IndexInfo();
859 index.schema = schemaName;
860 index.table = tableName;
861 index.columns = new String[columnList.size()];
862 columnList.toArray(index.columns);
863 rs = meta.getColumns(null,
864 JdbcUtils.escapeMetaDataPattern(schemaName),
865 JdbcUtils.escapeMetaDataPattern(tableName),
866 null);
867 for (int i = 0; rs.next(); i++) {
868 columnTypes[i] = rs.getInt("DATA_TYPE");
869 }
870 if (keyList.size() == 0) {
871 rs = meta.getPrimaryKeys(null,
872 JdbcUtils.escapeMetaDataPattern(schemaName),
873 tableName);
874 while (rs.next()) {
875 keyList.add(rs.getString("COLUMN_NAME"));
876 }
877 }
878 if (keyList.size() == 0) {
879 throw throwException("No primary key for table " + tableName);
880 }
881 ArrayList<String> indexList = New.arrayList();
882 PreparedStatement prep = conn.prepareStatement(
883 "SELECT ID, COLUMNS FROM " + SCHEMA + ".INDEXES WHERE SCHEMA=? AND TABLE=?");
884 prep.setString(1, schemaName);
885 prep.setString(2, tableName);
886 rs = prep.executeQuery();
887 if (rs.next()) {
888 index.id = rs.getInt(1);
889 String columns = rs.getString(2);
890 if (columns != null) {
891 for (String s : StringUtils.arraySplit(columns, ',', true)) {
892 indexList.add(s);
893 }
894 }
895 }
896 if (indexList.size() == 0) {
897 indexList.addAll(columnList);
898 }
899 index.keys = new int[keyList.size()];
900 setColumns(index.keys, keyList, columnList);
901 index.indexColumns = new int[indexList.size()];
902 setColumns(index.indexColumns, indexList, columnList);
903 setting.addIndexInfo(index);
904 prepInsertWord = conn.prepareStatement(
905 "INSERT INTO " + SCHEMA + ".WORDS(NAME) VALUES(?)");
906 prepInsertRow = conn.prepareStatement(
907 "INSERT INTO " + SCHEMA + ".ROWS(HASH, INDEXID, KEY) VALUES(?, ?, ?)");
908 prepInsertMap = conn.prepareStatement(
909 "INSERT INTO " + SCHEMA + ".MAP(ROWID, WORDID) VALUES(?, ?)");
910 prepDeleteRow = conn.prepareStatement(
911 "DELETE FROM " + SCHEMA + ".ROWS WHERE HASH=? AND INDEXID=? AND KEY=?");
912 prepDeleteMap = conn.prepareStatement(
913 "DELETE FROM " + SCHEMA + ".MAP WHERE ROWID=? AND WORDID=?");
914 prepSelectRow = conn.prepareStatement(
915 "SELECT ID FROM " + SCHEMA + ".ROWS WHERE HASH=? AND INDEXID=? AND KEY=?");
916 }
917
918 /**
919 * INTERNAL
920 */
921 public void fire(Connection conn, Object[] oldRow, Object[] newRow)
922 throws SQLException {
923 if (oldRow != null) {
924 if (newRow != null) {
925 // update
926 if (hasChanged(oldRow, newRow, index.indexColumns)) {
927 delete(oldRow);
928 insert(newRow);
929 }
930 } else {
931 // delete
932 delete(oldRow);
933 }
934 } else if (newRow != null) {
935 // insert
936 insert(newRow);
937 }
938 }
939
940 /**
941 * INTERNAL
942 */
943 public void close() {
944 setting.removeIndexInfo(index);
945 }
946
947 /**
948 * INTERNAL
949 */
950 public void remove() {
951 setting.removeIndexInfo(index);
952 }
953
954 /**
955 * Add a row to the index.
956 *
957 * @param row the row
958 */
959 protected void insert(Object[] row) throws SQLException {
960 String key = getKey(row);
961 int hash = key.hashCode();
962 prepInsertRow.setInt(1, hash);
963 prepInsertRow.setInt(2, index.id);
964 prepInsertRow.setString(3, key);
965 prepInsertRow.execute();
966 ResultSet rs = prepInsertRow.getGeneratedKeys();
967 rs.next();
968 int rowId = rs.getInt(1);
969 prepInsertMap.setInt(1, rowId);
970 int[] wordIds = getWordIds(row);
971 for (int id : wordIds) {
972 prepInsertMap.setInt(2, id);
973 prepInsertMap.execute();
974 }
975 }
976
977 /**
978 * Delete a row from the index.
979 *
980 * @param row the row
981 */
982 protected void delete(Object[] row) throws SQLException {
983 String key = getKey(row);
984 int hash = key.hashCode();
985 prepSelectRow.setInt(1, hash);
986 prepSelectRow.setInt(2, index.id);
987 prepSelectRow.setString(3, key);
988 ResultSet rs = prepSelectRow.executeQuery();
989 if (rs.next()) {
990 int rowId = rs.getInt(1);
991 prepDeleteMap.setInt(1, rowId);
992 int[] wordIds = getWordIds(row);
993 for (int id : wordIds) {
994 prepDeleteMap.setInt(2, id);
995 prepDeleteMap.executeUpdate();
996 }
997 prepDeleteRow.setInt(1, hash);
998 prepDeleteRow.setInt(2, index.id);
999 prepDeleteRow.setString(3, key);
1000 prepDeleteRow.executeUpdate();
1001 }
1002 }
1003
1004 private int[] getWordIds(Object[] row) throws SQLException {
1005 HashSet<String> words = New.hashSet();
1006 for (int idx : index.indexColumns) {
1007 int type = columnTypes[idx];
1008 Object data = row[idx];
1009 if (type == Types.CLOB && data != null) {
1010 Reader reader;
1011 if (data instanceof Reader) {
1012 reader = (Reader) data;
1013 } else {
1014 reader = ((Clob) data).getCharacterStream();
1015 }
1016 addWords(setting, words, reader);
1017 } else {
1018 String string = asString(data, type);
1019 addWords(setting, words, string);
1020 }
1021 }
1022 HashMap<String, Integer> allWords = setting.getWordList();
1023 int[] wordIds = new int[words.size()];
1024 Iterator<String> it = words.iterator();
1025 for (int i = 0; it.hasNext(); i++) {
1026 String word = it.next();
1027 Integer wId = allWords.get(word);
1028 int wordId;
1029 if (wId == null) {
1030 prepInsertWord.setString(1, word);
1031 prepInsertWord.execute();
1032 ResultSet rs = prepInsertWord.getGeneratedKeys();
1033 rs.next();
1034 wordId = rs.getInt(1);
1035 allWords.put(word, wordId);
1036 } else {
1037 wordId = wId.intValue();
1038 }
1039 wordIds[i] = wordId;
1040 }
1041 Arrays.sort(wordIds);
1042 return wordIds;
1043 }
1044
1045 private String getKey(Object[] row) throws SQLException {
1046 StatementBuilder buff = new StatementBuilder();
1047 for (int columnIndex : index.keys) {
1048 buff.appendExceptFirst(" AND ");
1049 buff.append(StringUtils.quoteIdentifier(index.columns[columnIndex]));
1050 Object o = row[columnIndex];
1051 if (o == null) {
1052 buff.append(" IS NULL");
1053 } else {
1054 buff.append('=').append(quoteSQL(o, columnTypes[columnIndex]));
1055 }
1056 }
1057 return buff.toString();
1058 }
1059
1060 }
1061
1062 /**
1063 * INTERNAL
1064 * Close all fulltext settings, freeing up memory.
1065 */
1066 public static void closeAll() {
1067 FullTextSettings.closeAll();
1068 }
1069
1070 /**
1071 * Throw a SQLException with the given message.
1072 *
1073 * @param message the message
1074 * @return never returns normally
1075 * @throws SQLException the exception
1076 */
1077 protected static SQLException throwException(String message) throws SQLException {
1078 throw new SQLException(message, "FULLTEXT");
1079 }
1080
1081 }
1082