/*
* Copyright 2004-2011 H2 Group. Multiple-Licensed under the H2 License,
* Version 1.0, and under the Eclipse Public License, Version 1.0
* (http://h2database.com/html/license.html).
* Initial Developer: H2 Group
*/
package org.h2.fulltext;
import java.io.IOException;
import java.io.Reader;
import java.io.StreamTokenizer;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.StringTokenizer;
import java.util.UUID;
import org.h2.api.Trigger;
import org.h2.command.Parser;
import org.h2.engine.Session;
import org.h2.expression.Comparison;
import org.h2.expression.ConditionAndOr;
import org.h2.expression.Expression;
import org.h2.expression.ExpressionColumn;
import org.h2.expression.ValueExpression;
import org.h2.jdbc.JdbcConnection;
import org.h2.message.DbException;
import org.h2.tools.SimpleResultSet;
import org.h2.util.IOUtils;
import org.h2.util.JdbcUtils;
import org.h2.util.New;
import org.h2.util.StatementBuilder;
import org.h2.util.StringUtils;
/**
* This class implements the native full text search.
* Most methods can be called using SQL statements as well.
*/
public class FullText {
/**
* A column name of the result set returned by the searchData method.
*/
private static final String FIELD_SCHEMA = "SCHEMA";
/**
* A column name of the result set returned by the searchData method.
*/
private static final String FIELD_TABLE = "TABLE";
/**
* A column name of the result set returned by the searchData method.
*/
private static final String FIELD_COLUMNS = "COLUMNS";
/**
* A column name of the result set returned by the searchData method.
*/
private static final String FIELD_KEYS = "KEYS";
/**
* The hit score.
*/
private static final String FIELD_SCORE = "SCORE";
private static final String TRIGGER_PREFIX = "FT_";
private static final String SCHEMA = "FT";
private static final String SELECT_MAP_BY_WORD_ID = "SELECT ROWID FROM " + SCHEMA + ".MAP WHERE WORDID=?";
private static final String SELECT_ROW_BY_ID = "SELECT KEY, INDEXID FROM " + SCHEMA + ".ROWS WHERE ID=?";
/**
* The column name of the result set returned by the search method.
*/
private static final String FIELD_QUERY = "QUERY";
/**
* Initializes full text search functionality for this database. This adds
* the following Java functions to the database:
* <ul>
* <li>FT_CREATE_INDEX(schemaNameString, tableNameString,
* columnListString)</li>
* <li>FT_SEARCH(queryString, limitInt, offsetInt): result set</li>
* <li>FT_REINDEX()</li>
* <li>FT_DROP_ALL()</li>
* </ul>
* It also adds a schema FT to the database where bookkeeping information
* is stored. This function may be called from a Java application, or by
* using the SQL statements:
*
* <pre>
* CREATE ALIAS IF NOT EXISTS FT_INIT FOR
* "org.h2.fulltext.FullText.init";
* CALL FT_INIT();
* </pre>
*
* @param conn the connection
*/
public static void init(Connection conn) throws SQLException {
Statement stat = conn.createStatement();
stat.execute("CREATE SCHEMA IF NOT EXISTS " + SCHEMA);
stat.execute("CREATE TABLE IF NOT EXISTS " + SCHEMA
+ ".INDEXES(ID INT AUTO_INCREMENT PRIMARY KEY, SCHEMA VARCHAR, TABLE VARCHAR, COLUMNS VARCHAR, UNIQUE(SCHEMA, TABLE))");
stat.execute("CREATE TABLE IF NOT EXISTS " + SCHEMA
+ ".WORDS(ID INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR, UNIQUE(NAME))");
stat.execute("CREATE TABLE IF NOT EXISTS " + SCHEMA
+ ".ROWS(ID IDENTITY, HASH INT, INDEXID INT, KEY VARCHAR, UNIQUE(HASH, INDEXID, KEY))");
stat.execute("CREATE TABLE IF NOT EXISTS " + SCHEMA
+ ".MAP(ROWID INT, WORDID INT, PRIMARY KEY(WORDID, ROWID))");
stat.execute("CREATE TABLE IF NOT EXISTS " + SCHEMA + ".IGNORELIST(LIST VARCHAR)");
stat.execute("CREATE TABLE IF NOT EXISTS " + SCHEMA + ".SETTINGS(KEY VARCHAR PRIMARY KEY, VALUE VARCHAR)");
stat.execute("CREATE ALIAS IF NOT EXISTS FT_CREATE_INDEX FOR \"" + FullText.class.getName() + ".createIndex\"");
stat.execute("CREATE ALIAS IF NOT EXISTS FT_DROP_INDEX FOR \"" + FullText.class.getName() + ".dropIndex\"");
stat.execute("CREATE ALIAS IF NOT EXISTS FT_SEARCH FOR \"" + FullText.class.getName() + ".search\"");
stat.execute("CREATE ALIAS IF NOT EXISTS FT_SEARCH_DATA FOR \"" + FullText.class.getName() + ".searchData\"");
stat.execute("CREATE ALIAS IF NOT EXISTS FT_REINDEX FOR \"" + FullText.class.getName() + ".reindex\"");
stat.execute("CREATE ALIAS IF NOT EXISTS FT_DROP_ALL FOR \"" + FullText.class.getName() + ".dropAll\"");
FullTextSettings setting = FullTextSettings.getInstance(conn);
ResultSet rs = stat.executeQuery("SELECT * FROM " + SCHEMA + ".IGNORELIST");
while (rs.next()) {
String commaSeparatedList = rs.getString(1);
setIgnoreList(setting, commaSeparatedList);
}
rs = stat.executeQuery("SELECT * FROM " + SCHEMA + ".SETTINGS");
while (rs.next()) {
String key = rs.getString(1);
if ("whitespaceChars".equals(key)) {
String value = rs.getString(2);
setting.setWhitespaceChars(value);
}
}
rs = stat.executeQuery("SELECT * FROM " + SCHEMA + ".WORDS");
HashMap<String, Integer> map = setting.getWordList();
while (rs.next()) {
String word = rs.getString("NAME");
int id = rs.getInt("ID");
word = setting.convertWord(word);
if (word != null) {
map.put(word, id);
}
}
setting.setInitialized(true);
}
/**
* Create a new full text index for a table and column list. Each table may
* only have one index at any time.
*
* @param conn the connection
* @param schema the schema name of the table (case sensitive)
* @param table the table name (case sensitive)
* @param columnList the column list (null for all columns)
*/
public static void createIndex(Connection conn, String schema, String table, String columnList) throws SQLException {
init(conn);
PreparedStatement prep = conn.prepareStatement("INSERT INTO " + SCHEMA
+ ".INDEXES(SCHEMA, TABLE, COLUMNS) VALUES(?, ?, ?)");
prep.setString(1, schema);
prep.setString(2, table);
prep.setString(3, columnList);
prep.execute();
createTrigger(conn, schema, table);
indexExistingRows(conn, schema, table);
}
/**
* Re-creates the full text index for this database. Calling this method is
* usually not needed, as the index is kept up-to-date automatically.
*
* @param conn the connection
*/
public static void reindex(Connection conn) throws SQLException {
init(conn);
removeAllTriggers(conn, TRIGGER_PREFIX);
FullTextSettings setting = FullTextSettings.getInstance(conn);
setting.getWordList().clear();
Statement stat = conn.createStatement();
stat.execute("TRUNCATE TABLE " + SCHEMA + ".WORDS");
stat.execute("TRUNCATE TABLE " + SCHEMA + ".ROWS");
stat.execute("TRUNCATE TABLE " + SCHEMA + ".MAP");
ResultSet rs = stat.executeQuery("SELECT * FROM " + SCHEMA + ".INDEXES");
while (rs.next()) {
String schema = rs.getString("SCHEMA");
String table = rs.getString("TABLE");
createTrigger(conn, schema, table);
indexExistingRows(conn, schema, table);
}
}
/**
* Drop an existing full text index for a table. This method returns
* silently if no index for this table exists.
*
* @param conn the connection
* @param schema the schema name of the table (case sensitive)
* @param table the table name (case sensitive)
*/
public static void dropIndex(Connection conn, String schema, String table) throws SQLException {
init(conn);
PreparedStatement prep = conn.prepareStatement("SELECT ID FROM " + SCHEMA
+ ".INDEXES WHERE SCHEMA=? AND TABLE=?");
prep.setString(1, schema);
prep.setString(2, table);
ResultSet rs = prep.executeQuery();
if (!rs.next()) {
return;
}
int indexId = rs.getInt(1);
prep = conn.prepareStatement("DELETE FROM " + SCHEMA
+ ".INDEXES WHERE ID=?");
prep.setInt(1, indexId);
prep.execute();
createOrDropTrigger(conn, schema, table, false);
prep = conn.prepareStatement("DELETE FROM " + SCHEMA +
".ROWS WHERE INDEXID=? AND ROWNUM<10000");
while (true) {
prep.setInt(1, indexId);
int deleted = prep.executeUpdate();
if (deleted == 0) {
break;
}
}
prep = conn.prepareStatement("DELETE FROM " + SCHEMA + ".MAP M " +
"WHERE NOT EXISTS (SELECT * FROM " + SCHEMA + ".ROWS R WHERE R.ID=M.ROWID) AND ROWID<10000");
while (true) {
int deleted = prep.executeUpdate();
if (deleted == 0) {
break;
}
}
}
/**
* Drops all full text indexes from the database.
*
* @param conn the connection
*/
public static void dropAll(Connection conn) throws SQLException {
init(conn);
Statement stat = conn.createStatement();
stat.execute("DROP SCHEMA IF EXISTS " + SCHEMA);
removeAllTriggers(conn, TRIGGER_PREFIX);
FullTextSettings setting = FullTextSettings.getInstance(conn);
setting.removeAllIndexes();
setting.getIgnoreList().clear();
setting.getWordList().clear();
}
/**
* Searches from the full text index for this database.
* The returned result set has the following column:
* <ul><li>QUERY (varchar): the query to use to get the data.
* The query does not include 'SELECT * FROM '. Example:
* PUBLIC.TEST WHERE ID = 1
* </li><li>SCORE (float) the relevance score. This value is always 1.0
* for the native fulltext search.
* </li></ul>
*
* @param conn the connection
* @param text the search query
* @param limit the maximum number of rows or 0 for no limit
* @param offset the offset or 0 for no offset
* @return the result set
*/
public static ResultSet search(Connection conn, String text, int limit, int offset) throws SQLException {
try {
return search(conn, text, limit, offset, false);
} catch (DbException e) {
throw DbException.toSQLException(e);
}
}
/**
* Searches from the full text index for this database. The result contains
* the primary key data as an array. The returned result set has the
* following columns:
* <ul>
* <li>SCHEMA (varchar): the schema name. Example: PUBLIC </li>
* <li>TABLE (varchar): the table name. Example: TEST </li>
* <li>COLUMNS (array of varchar): comma separated list of quoted column
* names. The column names are quoted if necessary. Example: (ID) </li>
* <li>KEYS (array of values): comma separated list of values. Example: (1)
* </li>
* <li>SCORE (float) the relevance score. This value is always 1.0
* for the native fulltext search.
* </li>
* </ul>
*
* @param conn the connection
* @param text the search query
* @param limit the maximum number of rows or 0 for no limit
* @param offset the offset or 0 for no offset
* @return the result set
*/
public static ResultSet searchData(Connection conn, String text, int limit, int offset) throws SQLException {
try {
return search(conn, text, limit, offset, true);
} catch (DbException e) {
throw DbException.toSQLException(e);
}
}
/**
* Change the ignore list. The ignore list is a comma separated list of
* common words that must not be indexed. The default ignore list is empty.
* If indexes already exist at the time this list is changed, reindex must
* be called.
*
* @param conn the connection
* @param commaSeparatedList the list
*/
public static void setIgnoreList(Connection conn, String commaSeparatedList) throws SQLException {
try {
init(conn);
FullTextSettings setting = FullTextSettings.getInstance(conn);
setIgnoreList(setting, commaSeparatedList);
Statement stat = conn.createStatement();
stat.execute("TRUNCATE TABLE " + SCHEMA + ".IGNORELIST");
PreparedStatement prep = conn.prepareStatement("INSERT INTO " + SCHEMA + ".IGNORELIST VALUES(?)");
prep.setString(1, commaSeparatedList);
prep.execute();
} catch (DbException e) {
throw DbException.toSQLException(e);
}
}
/**
* Change the whitespace characters. The whitespace characters are used to
* separate words. If indexes already exist at the time this list is
* changed, reindex must be called.
*
* @param conn the connection
* @param whitespaceChars the list of characters
*/
public static void setWhitespaceChars(Connection conn, String whitespaceChars) throws SQLException {
try {
init(conn);
FullTextSettings setting = FullTextSettings.getInstance(conn);
setting.setWhitespaceChars(whitespaceChars);
PreparedStatement prep = conn.prepareStatement("MERGE INTO " + SCHEMA + ".SETTINGS VALUES(?, ?)");
prep.setString(1, "whitespaceChars");
prep.setString(2, whitespaceChars);
prep.execute();
} catch (DbException e) {
throw DbException.toSQLException(e);
}
}
/**
* INTERNAL.
* Convert the object to a string.
*
* @param data the object
* @param type the SQL type
* @return the string
*/
protected static String asString(Object data, int type) throws SQLException {
if (data == null) {
return "NULL";
}
switch (type) {
case Types.BIT:
case Types.BOOLEAN:
case Types.INTEGER:
case Types.BIGINT:
case Types.DECIMAL:
case Types.DOUBLE:
case Types.FLOAT:
case Types.NUMERIC:
case Types.REAL:
case Types.SMALLINT:
case Types.TINYINT:
case Types.DATE:
case Types.TIME:
case Types.TIMESTAMP:
case Types.LONGVARCHAR:
case Types.CHAR:
case Types.VARCHAR:
return data.toString();
case Types.CLOB:
try {
if (data instanceof Clob) {
data = ((Clob) data).getCharacterStream();
}
return IOUtils.readStringAndClose((Reader) data, -1);
} catch (IOException e) {
throw DbException.toSQLException(e);
}
case Types.VARBINARY:
case Types.LONGVARBINARY:
case Types.BINARY:
case Types.JAVA_OBJECT:
case Types.OTHER:
case Types.BLOB:
case Types.STRUCT:
case Types.REF:
case Types.NULL:
case Types.ARRAY:
case Types.DATALINK:
case Types.DISTINCT:
throw throwException("Unsupported column data type: " + type);
default:
return "";
}
}
/**
* Create an empty search result and initialize the columns.
*
* @param data true if the result set should contain the primary key data as
* an array.
* @return the empty result set
*/
protected static SimpleResultSet createResultSet(boolean data) {
SimpleResultSet result = new SimpleResultSet();
if (data) {
result.addColumn(FullText.FIELD_SCHEMA, Types.VARCHAR, 0, 0);
result.addColumn(FullText.FIELD_TABLE, Types.VARCHAR, 0, 0);
result.addColumn(FullText.FIELD_COLUMNS, Types.ARRAY, 0, 0);
result.addColumn(FullText.FIELD_KEYS, Types.ARRAY, 0, 0);
} else {
result.addColumn(FullText.FIELD_QUERY, Types.VARCHAR, 0, 0);
}
result.addColumn(FullText.FIELD_SCORE, Types.FLOAT, 0, 0);
return result;
}
/**
* Parse a primary key condition into the primary key columns.
*
* @param conn the database connection
* @param key the primary key condition as a string
* @return an array containing the column name list and the data list
*/
protected static Object[][] parseKey(Connection conn, String key) {
ArrayList<String> columns = New.arrayList();
ArrayList<String> data = New.arrayList();
JdbcConnection c = (JdbcConnection) conn;
Session session = (Session) c.getSession();
Parser p = new Parser(session);
Expression expr = p.parseExpression(key);
addColumnData(columns, data, expr);
Object[] col = new Object[columns.size()];
columns.toArray(col);
Object[] dat = new Object[columns.size()];
data.toArray(dat);
Object[][] columnData = { col, dat };
return columnData;
}
/**
* INTERNAL.
* Convert an object to a String as used in a SQL statement.
*
* @param data the object
* @param type the SQL type
* @return the SQL String
*/
protected static String quoteSQL(Object data, int type) throws SQLException {
if (data == null) {
return "NULL";
}
switch (type) {
case Types.BIT:
case Types.BOOLEAN:
case Types.INTEGER:
case Types.BIGINT:
case Types.DECIMAL:
case Types.DOUBLE:
case Types.FLOAT:
case Types.NUMERIC:
case Types.REAL:
case Types.SMALLINT:
case Types.TINYINT:
return data.toString();
case Types.DATE:
case Types.TIME:
case Types.TIMESTAMP:
case Types.LONGVARCHAR:
case Types.CHAR:
case Types.VARCHAR:
return quoteString(data.toString());
case Types.VARBINARY:
case Types.LONGVARBINARY:
case Types.BINARY:
if (data instanceof UUID) {
return "'" + data.toString() + "'";
}
return "'" + StringUtils.convertBytesToHex((byte[]) data) + "'";
case Types.CLOB:
case Types.JAVA_OBJECT:
case Types.OTHER:
case Types.BLOB:
case Types.STRUCT:
case Types.REF:
case Types.NULL:
case Types.ARRAY:
case Types.DATALINK:
case Types.DISTINCT:
throw throwException("Unsupported key data type: " + type);
default:
return "";
}
}
/**
* Remove all triggers that start with the given prefix.
*
* @param conn the database connection
* @param prefix the prefix
*/
protected static void removeAllTriggers(Connection conn, String prefix) throws SQLException {
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery("SELECT * FROM INFORMATION_SCHEMA.TRIGGERS");
Statement stat2 = conn.createStatement();
while (rs.next()) {
String schema = rs.getString("TRIGGER_SCHEMA");
String name = rs.getString("TRIGGER_NAME");
if (name.startsWith(prefix)) {
name = StringUtils.quoteIdentifier(schema) + "." + StringUtils.quoteIdentifier(name);
stat2.execute("DROP TRIGGER " + name);
}
}
}
/**
* Set the column indices of a set of keys.
*
* @param index the column indices (will be modified)
* @param keys the key list
* @param columns the column list
*/
protected static void setColumns(int[] index, ArrayList<String> keys, ArrayList<String> columns) throws SQLException {
for (int i = 0, keySize = keys.size(); i < keySize; i++) {
String key = keys.get(i);
int found = -1;
int columnsSize = columns.size();
for (int j = 0; found == -1 && j < columnsSize; j++) {
String column = columns.get(j);
if (column.equals(key)) {
found = j;
}
}
if (found < 0) {
throw throwException("Column not found: " + key);
}
index[i] = found;
}
}
/**
* Do the search.
*
* @param conn the database connection
* @param text the query
* @param limit the limit
* @param offset the offset
* @param data whether the raw data should be returned
* @return the result set
*/
protected static ResultSet search(Connection conn, String text, int limit,
int offset, boolean data) throws SQLException {
SimpleResultSet result = createResultSet(data);
if (conn.getMetaData().getURL().startsWith("jdbc:columnlist:")) {
// this is just to query the result set columns
return result;
}
if (text == null || text.trim().length() == 0) {
return result;
}
FullTextSettings setting = FullTextSettings.getInstance(conn);
if (!setting.isInitialized()) {
init(conn);
}
HashSet<String> words = New.hashSet();
addWords(setting, words, text);
HashSet<Integer> rIds = null, lastRowIds = null;
HashMap<String, Integer> allWords = setting.getWordList();
PreparedStatement prepSelectMapByWordId = setting.prepare(conn, SELECT_MAP_BY_WORD_ID);
for (String word : words) {
lastRowIds = rIds;
rIds = New.hashSet();
Integer wId = allWords.get(word);
if (wId == null) {
continue;
}
prepSelectMapByWordId.setInt(1, wId.intValue());
ResultSet rs = prepSelectMapByWordId.executeQuery();
while (rs.next()) {
Integer rId = rs.getInt(1);
if (lastRowIds == null || lastRowIds.contains(rId)) {
rIds.add(rId);
}
}
}
if (rIds == null || rIds.size() == 0) {
return result;
}
PreparedStatement prepSelectRowById = setting.prepare(conn, SELECT_ROW_BY_ID);
int rowCount = 0;
for (int rowId : rIds) {
prepSelectRowById.setInt(1, rowId);
ResultSet rs = prepSelectRowById.executeQuery();
if (!rs.next()) {
continue;
}
if (offset > 0) {
offset--;
} else {
String key = rs.getString(1);
int indexId = rs.getInt(2);
IndexInfo index = setting.getIndexInfo(indexId);
if (data) {
Object[][] columnData = parseKey(conn, key);
result.addRow(
index.schema,
index.table,
columnData[0],
columnData[1],
1.0);
} else {
String query = StringUtils.quoteIdentifier(index.schema) +
"." + StringUtils.quoteIdentifier(index.table) +
" WHERE " + key;
result.addRow(query, 1.0);
}
rowCount++;
if (limit > 0 && rowCount >= limit) {
break;
}
}
}
return result;
}
private static void addColumnData(ArrayList<String> columns, ArrayList<String> data, Expression expr) {
if (expr instanceof ConditionAndOr) {
ConditionAndOr and = (ConditionAndOr) expr;
Expression left = and.getExpression(true);
Expression right = and.getExpression(false);
addColumnData(columns, data, left);
addColumnData(columns, data, right);
} else {
Comparison comp = (Comparison) expr;
ExpressionColumn ec = (ExpressionColumn) comp.getExpression(true);
ValueExpression ev = (ValueExpression) comp.getExpression(false);
String columnName = ec.getColumnName();
columns.add(columnName);
if (ev == null) {
data.add(null);
} else {
data.add(ev.getValue(null).getString());
}
}
}
/**
* Add all words in the given text to the hash set.
*
* @param setting the fulltext settings
* @param set the hash set
* @param reader the reader
*/
protected static void addWords(FullTextSettings setting, HashSet<String> set, Reader reader) {
StreamTokenizer tokenizer = new StreamTokenizer(reader);
tokenizer.resetSyntax();
tokenizer.wordChars(' ' + 1, 255);
char[] whitespaceChars = setting.getWhitespaceChars().toCharArray();
for (char ch : whitespaceChars) {
tokenizer.whitespaceChars(ch, ch);
}
try {
while (true) {
int token = tokenizer.nextToken();
if (token == StreamTokenizer.TT_EOF) {
break;
} else if (token == StreamTokenizer.TT_WORD) {
String word = tokenizer.sval;
word = setting.convertWord(word);
if (word != null) {
set.add(word);
}
}
}
} catch (IOException e) {
throw DbException.convertIOException(e, "Tokenizer error");
}
}
/**
* Add all words in the given text to the hash set.
*
* @param setting the fulltext settings
* @param set the hash set
* @param text the text
*/
protected static void addWords(FullTextSettings setting, HashSet<String> set, String text) {
String whitespaceChars = setting.getWhitespaceChars();
StringTokenizer tokenizer = new StringTokenizer(text, whitespaceChars);
while (tokenizer.hasMoreTokens()) {
String word = tokenizer.nextToken();
word = setting.convertWord(word);
if (word != null) {
set.add(word);
}
}
}
/**
* Create the trigger.
*
* @param conn the database connection
* @param schema the schema name
* @param table the table name
*/
protected static void createTrigger(Connection conn, String schema, String table) throws SQLException {
createOrDropTrigger(conn, schema, table, true);
}
private static void createOrDropTrigger(Connection conn,
String schema, String table, boolean create) throws SQLException {
Statement stat = conn.createStatement();
String trigger = StringUtils.quoteIdentifier(schema) + "."
+ StringUtils.quoteIdentifier(TRIGGER_PREFIX + table);
stat.execute("DROP TRIGGER IF EXISTS " + trigger);
if (create) {
StringBuilder buff = new StringBuilder("CREATE TRIGGER IF NOT EXISTS ");
// needs to be called on rollback as well, because we use the init connection
// do to changes in the index (not the user connection)
buff.append(trigger).
append(" AFTER INSERT, UPDATE, DELETE, ROLLBACK ON ").
append(StringUtils.quoteIdentifier(schema)).
append('.').
append(StringUtils.quoteIdentifier(table)).
append(" FOR EACH ROW CALL \"").
append(FullText.FullTextTrigger.class.getName()).
append('\"');
stat.execute(buff.toString());
}
}
/**
* Add the existing data to the index.
*
* @param conn the database connection
* @param schema the schema name
* @param table the table name
*/
protected static void indexExistingRows(Connection conn, String schema, String table) throws SQLException {
FullText.FullTextTrigger existing = new FullText.FullTextTrigger();
existing.init(conn, schema, null, table, false, Trigger.INSERT);
String sql = "SELECT * FROM " + StringUtils.quoteIdentifier(schema) + "." + StringUtils.quoteIdentifier(table);
ResultSet rs = conn.createStatement().executeQuery(sql);
int columnCount = rs.getMetaData().getColumnCount();
while (rs.next()) {
Object[] row = new Object[columnCount];
for (int i = 0; i < columnCount; i++) {
row[i] = rs.getObject(i + 1);
}
existing.fire(conn, null, row);
}
}
private static String quoteString(String data) {
if (data.indexOf('\'') < 0) {
return "'" + data + "'";
}
int len = data.length();
StringBuilder buff = new StringBuilder(len + 2);
buff.append('\'');
for (int i = 0; i < len; i++) {
char ch = data.charAt(i);
if (ch == '\'') {
buff.append(ch);
}
buff.append(ch);
}
buff.append('\'');
return buff.toString();
}
private static void setIgnoreList(FullTextSettings setting, String commaSeparatedList) {
String[] list = StringUtils.arraySplit(commaSeparatedList, ',', true);
HashSet<String> set = setting.getIgnoreList();
for (String word : list) {
String converted = setting.convertWord(word);
if (converted != null) {
set.add(converted);
}
}
}
/**
* Check if a the indexed columns of a row probably have changed. It may
* return true even if the change was minimal (for example from 0.0 to
* 0.00).
*
* @param oldRow the old row
* @param newRow the new row
* @param indexColumns the indexed columns
* @return true if the indexed columns don't match
*/
protected static boolean hasChanged(Object[] oldRow, Object[] newRow, int[] indexColumns) {
for (int c : indexColumns) {
Object o = oldRow[c], n = newRow[c];
if (o == null) {
if (n != null) {
return true;
}
} else if (!o.equals(n)) {
return true;
}
}
return false;
}
/**
* Trigger updates the index when a inserting, updating, or deleting a row.
*/
public static class FullTextTrigger implements Trigger {
protected FullTextSettings setting;
protected IndexInfo index;
protected int[] columnTypes;
protected PreparedStatement prepInsertWord, prepInsertRow, prepInsertMap;
protected PreparedStatement prepDeleteRow, prepDeleteMap;
protected PreparedStatement prepSelectRow;
/**
* INTERNAL
*/
public void init(Connection conn, String schemaName, String triggerName,
String tableName, boolean before, int type) throws SQLException {
setting = FullTextSettings.getInstance(conn);
if (!setting.isInitialized()) {
FullText.init(conn);
}
ArrayList<String> keyList = New.arrayList();
DatabaseMetaData meta = conn.getMetaData();
ResultSet rs = meta.getColumns(null,
JdbcUtils.escapeMetaDataPattern(schemaName),
JdbcUtils.escapeMetaDataPattern(tableName),
null);
ArrayList<String> columnList = New.arrayList();
while (rs.next()) {
columnList.add(rs.getString("COLUMN_NAME"));
}
columnTypes = new int[columnList.size()];
index = new IndexInfo();
index.schema = schemaName;
index.table = tableName;
index.columns = new String[columnList.size()];
columnList.toArray(index.columns);
rs = meta.getColumns(null,
JdbcUtils.escapeMetaDataPattern(schemaName),
JdbcUtils.escapeMetaDataPattern(tableName),
null);
for (int i = 0; rs.next(); i++) {
columnTypes[i] = rs.getInt("DATA_TYPE");
}
if (keyList.size() == 0) {
rs = meta.getPrimaryKeys(null,
JdbcUtils.escapeMetaDataPattern(schemaName),
tableName);
while (rs.next()) {
keyList.add(rs.getString("COLUMN_NAME"));
}
}
if (keyList.size() == 0) {
throw throwException("No primary key for table " + tableName);
}
ArrayList<String> indexList = New.arrayList();
PreparedStatement prep = conn.prepareStatement(
"SELECT ID, COLUMNS FROM " + SCHEMA + ".INDEXES WHERE SCHEMA=? AND TABLE=?");
prep.setString(1, schemaName);
prep.setString(2, tableName);
rs = prep.executeQuery();
if (rs.next()) {
index.id = rs.getInt(1);
String columns = rs.getString(2);
if (columns != null) {
for (String s : StringUtils.arraySplit(columns, ',', true)) {
indexList.add(s);
}
}
}
if (indexList.size() == 0) {
indexList.addAll(columnList);
}
index.keys = new int[keyList.size()];
setColumns(index.keys, keyList, columnList);
index.indexColumns = new int[indexList.size()];
setColumns(index.indexColumns, indexList, columnList);
setting.addIndexInfo(index);
prepInsertWord = conn.prepareStatement(
"INSERT INTO " + SCHEMA + ".WORDS(NAME) VALUES(?)");
prepInsertRow = conn.prepareStatement(
"INSERT INTO " + SCHEMA + ".ROWS(HASH, INDEXID, KEY) VALUES(?, ?, ?)");
prepInsertMap = conn.prepareStatement(
"INSERT INTO " + SCHEMA + ".MAP(ROWID, WORDID) VALUES(?, ?)");
prepDeleteRow = conn.prepareStatement(
"DELETE FROM " + SCHEMA + ".ROWS WHERE HASH=? AND INDEXID=? AND KEY=?");
prepDeleteMap = conn.prepareStatement(
"DELETE FROM " + SCHEMA + ".MAP WHERE ROWID=? AND WORDID=?");
prepSelectRow = conn.prepareStatement(
"SELECT ID FROM " + SCHEMA + ".ROWS WHERE HASH=? AND INDEXID=? AND KEY=?");
}
/**
* INTERNAL
*/
public void fire(Connection conn, Object[] oldRow, Object[] newRow)
throws SQLException {
if (oldRow != null) {
if (newRow != null) {
// update
if (hasChanged(oldRow, newRow, index.indexColumns)) {
delete(oldRow);
insert(newRow);
}
} else {
// delete
delete(oldRow);
}
} else if (newRow != null) {
// insert
insert(newRow);
}
}
/**
* INTERNAL
*/
public void close() {
setting.removeIndexInfo(index);
}
/**
* INTERNAL
*/
public void remove() {
setting.removeIndexInfo(index);
}
/**
* Add a row to the index.
*
* @param row the row
*/
protected void insert(Object[] row) throws SQLException {
String key = getKey(row);
int hash = key.hashCode();
prepInsertRow.setInt(1, hash);
prepInsertRow.setInt(2, index.id);
prepInsertRow.setString(3, key);
prepInsertRow.execute();
ResultSet rs = prepInsertRow.getGeneratedKeys();
rs.next();
int rowId = rs.getInt(1);
prepInsertMap.setInt(1, rowId);
int[] wordIds = getWordIds(row);
for (int id : wordIds) {
prepInsertMap.setInt(2, id);
prepInsertMap.execute();
}
}
/**
* Delete a row from the index.
*
* @param row the row
*/
protected void delete(Object[] row) throws SQLException {
String key = getKey(row);
int hash = key.hashCode();
prepSelectRow.setInt(1, hash);
prepSelectRow.setInt(2, index.id);
prepSelectRow.setString(3, key);
ResultSet rs = prepSelectRow.executeQuery();
if (rs.next()) {
int rowId = rs.getInt(1);
prepDeleteMap.setInt(1, rowId);
int[] wordIds = getWordIds(row);
for (int id : wordIds) {
prepDeleteMap.setInt(2, id);
prepDeleteMap.executeUpdate();
}
prepDeleteRow.setInt(1, hash);
prepDeleteRow.setInt(2, index.id);
prepDeleteRow.setString(3, key);
prepDeleteRow.executeUpdate();
}
}
private int[] getWordIds(Object[] row) throws SQLException {
HashSet<String> words = New.hashSet();
for (int idx : index.indexColumns) {
int type = columnTypes[idx];
Object data = row[idx];
if (type == Types.CLOB && data != null) {
Reader reader;
if (data instanceof Reader) {
reader = (Reader) data;
} else {
reader = ((Clob) data).getCharacterStream();
}
addWords(setting, words, reader);
} else {
String string = asString(data, type);
addWords(setting, words, string);
}
}
HashMap<String, Integer> allWords = setting.getWordList();
int[] wordIds = new int[words.size()];
Iterator<String> it = words.iterator();
for (int i = 0; it.hasNext(); i++) {
String word = it.next();
Integer wId = allWords.get(word);
int wordId;
if (wId == null) {
prepInsertWord.setString(1, word);
prepInsertWord.execute();
ResultSet rs = prepInsertWord.getGeneratedKeys();
rs.next();
wordId = rs.getInt(1);
allWords.put(word, wordId);
} else {
wordId = wId.intValue();
}
wordIds[i] = wordId;
}
Arrays.sort(wordIds);
return wordIds;
}
private String getKey(Object[] row) throws SQLException {
StatementBuilder buff = new StatementBuilder();
for (int columnIndex : index.keys) {
buff.appendExceptFirst(" AND ");
buff.append(StringUtils.quoteIdentifier(index.columns[columnIndex]));
Object o = row[columnIndex];
if (o == null) {
buff.append(" IS NULL");
} else {
buff.append('=').append(quoteSQL(o, columnTypes[columnIndex]));
}
}
return buff.toString();
}
}
/**
* INTERNAL
* Close all fulltext settings, freeing up memory.
*/
public static void closeAll() {
FullTextSettings.closeAll();
}
/**
* Throw a SQLException with the given message.
*
* @param message the message
* @return never returns normally
* @throws SQLException the exception
*/
protected static SQLException throwException(String message) throws SQLException {
throw new SQLException(message, "FULLTEXT");
}
}