Examples with CreateIndex org.h2.command.ddl.CreateIndex used on opensource projects

Example 6 with CreateIndex

use of org.h2.command.ddl.CreateIndex in project h2database by h2database.

the class FullText method init.

/**
 * 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
 *      &quot;org.h2.fulltext.FullText.init&quot;;
 * 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");
    while (rs.next()) {
        String word = rs.getString("NAME");
        int id = rs.getInt("ID");
        word = setting.convertWord(word);
        if (word != null) {
            setting.addWord(word, id);
        }
    }
    setting.setInitialized(true);
}

Example 7 with CreateIndex

use of org.h2.command.ddl.CreateIndex in project h2database by h2database.

the class ResultTempTable method find.

private Cursor find(Row row) {
    if (index == null) {
        // for the case "in(select ...)", the query might
        // use an optimization and not create the index
        // up front
        createIndex();
    }
    Cursor cursor = index.find(session, row, row);
    while (cursor.next()) {
        SearchRow found = cursor.getSearchRow();
        boolean ok = true;
        Database db = session.getDatabase();
        for (int i = 0; i < row.getColumnCount(); i++) {
            if (!db.areEqual(row.getValue(i), found.getValue(i))) {
                ok = false;
                break;
            }
        }
        if (ok) {
            return cursor;
        }
    }
    return null;
}

Example 8 with CreateIndex

use of org.h2.command.ddl.CreateIndex in project h2database by h2database.

the class ResultTempTable method createIndex.

private void createIndex() {
    IndexColumn[] indexCols = null;
    // sort columns. So we need to disregard the sort. Not ideal.
    if (sort != null && !distinct) {
        int[] colIndex = sort.getQueryColumnIndexes();
        indexCols = new IndexColumn[colIndex.length];
        for (int i = 0; i < colIndex.length; i++) {
            IndexColumn indexColumn = new IndexColumn();
            indexColumn.column = table.getColumn(colIndex[i]);
            indexColumn.sortType = sort.getSortTypes()[i];
            indexColumn.columnName = COLUMN_NAME + i;
            indexCols[i] = indexColumn;
        }
    } else {
        indexCols = new IndexColumn[columnCount];
        for (int i = 0; i < columnCount; i++) {
            IndexColumn indexColumn = new IndexColumn();
            indexColumn.column = table.getColumn(i);
            indexColumn.columnName = COLUMN_NAME + i;
            indexCols[i] = indexColumn;
        }
    }
    String indexName = table.getSchema().getUniqueIndexName(session, table, Constants.PREFIX_INDEX);
    int indexId = session.getDatabase().allocateObjectId();
    IndexType indexType = IndexType.createNonUnique(true);
    index = table.addIndex(session, indexName, indexId, indexCols, indexType, true, null);
}

Example 9 with CreateIndex

use of org.h2.command.ddl.CreateIndex in project siena by mandubian.

the class FullText method init.

/**
     * 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
     *      &quot;org.h2.fulltext.FullText.init&quot;;
     * 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 + ".FT_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 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 + ".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);
}

Example 10 with CreateIndex

use of org.h2.command.ddl.CreateIndex in project elastic-core-maven by OrdinaryDude.

the class FullTextTrigger method init.

/**
 * Initialize the fulltext support for a new database
 *
 * This method should be called from NxtDbVersion when performing the database version update
 * that enables NRS fulltext search support
 */
public static void init() {
    String ourClassName = FullTextTrigger.class.getName();
    try (Connection conn = Db.db.getConnection();
        Statement stmt = conn.createStatement();
        Statement qstmt = conn.createStatement()) {
        // 
        // Check if we have already been initialized.
        // 
        boolean alreadyInitialized = true;
        boolean triggersExist = false;
        try (ResultSet rs = qstmt.executeQuery("SELECT JAVA_CLASS FROM INFORMATION_SCHEMA.TRIGGERS " + "WHERE SUBSTRING(TRIGGER_NAME, 0, 4) = 'FTL_'")) {
            while (rs.next()) {
                triggersExist = true;
                if (!rs.getString(1).startsWith(ourClassName)) {
                    alreadyInitialized = false;
                }
            }
        }
        if (triggersExist && alreadyInitialized) {
            Logger.logInfoMessage("NRS fulltext support is already initialized");
            return;
        }
        // 
        // We need to delete an existing Lucene index since the V3 file format is not compatible with V5
        // 
        getIndexPath(conn);
        removeIndexFiles(conn);
        // 
        // Drop the H2 Lucene V3 function aliases
        // 
        stmt.execute("DROP ALIAS IF EXISTS FTL_INIT");
        stmt.execute("DROP ALIAS IF EXISTS FTL_CREATE_INDEX");
        stmt.execute("DROP ALIAS IF EXISTS FTL_DROP_INDEX");
        stmt.execute("DROP ALIAS IF EXISTS FTL_DROP_ALL");
        stmt.execute("DROP ALIAS IF EXISTS FTL_REINDEX");
        stmt.execute("DROP ALIAS IF EXISTS FTL_SEARCH");
        stmt.execute("DROP ALIAS IF EXISTS FTL_SEARCH_DATA");
        Logger.logInfoMessage("H2 fulltext function aliases dropped");
        // 
        // Create our schema and table
        // 
        stmt.execute("CREATE SCHEMA IF NOT EXISTS FTL");
        stmt.execute("CREATE TABLE IF NOT EXISTS FTL.INDEXES " + "(SCHEMA VARCHAR, TABLE VARCHAR, COLUMNS VARCHAR, PRIMARY KEY(SCHEMA, TABLE))");
        Logger.logInfoMessage("NRS fulltext schema created");
        // 
        try (ResultSet rs = qstmt.executeQuery("SELECT * FROM FTL.INDEXES")) {
            while (rs.next()) {
                String schema = rs.getString("SCHEMA");
                String table = rs.getString("TABLE");
                stmt.execute("DROP TRIGGER IF EXISTS FTL_" + table);
                stmt.execute(String.format("CREATE TRIGGER FTL_%s AFTER INSERT,UPDATE,DELETE ON %s.%s " + "FOR EACH ROW CALL \"%s\"", table, schema, table, ourClassName));
            }
        }
        // 
        // Rebuild the Lucene index since the Lucene V3 index is not compatible with Lucene V5
        // 
        reindex(conn);
        // 
        // Create our function aliases
        // 
        stmt.execute("CREATE ALIAS FTL_CREATE_INDEX FOR \"" + ourClassName + ".createIndex\"");
        stmt.execute("CREATE ALIAS FTL_DROP_INDEX FOR \"" + ourClassName + ".dropIndex\"");
        stmt.execute("CREATE ALIAS FTL_SEARCH NOBUFFER FOR \"" + ourClassName + ".search\"");
        Logger.logInfoMessage("NRS fulltext aliases created");
    } catch (SQLException exc) {
        Logger.logErrorMessage("Unable to initialize NRS fulltext search support", exc);
        throw new RuntimeException(exc.toString(), exc);
    }
}