Examples with Select org.h2.command.dml.Select used on opensource projects

Example 21 with Select

use of org.h2.command.dml.Select in project ignite by apache.

the class DmlAstUtils method selectForUpdate.

/**
 * Generate SQL SELECT based on UPDATE's WHERE, LIMIT, etc.
 *
 * @param update Update statement.
 * @param keysParamIdx Index of new param for the array of keys.
 * @return SELECT statement.
 */
public static GridSqlSelect selectForUpdate(GridSqlUpdate update, @Nullable Integer keysParamIdx) {
    GridSqlSelect mapQry = new GridSqlSelect();
    mapQry.from(update.target());
    Set<GridSqlTable> tbls = new HashSet<>();
    collectAllGridTablesInTarget(update.target(), tbls);
    assert tbls.size() == 1 : "Failed to determine target table for UPDATE";
    GridSqlTable tbl = tbls.iterator().next();
    GridH2Table gridTbl = tbl.dataTable();
    assert gridTbl != null : "Failed to determine target grid table for UPDATE";
    Column h2KeyCol = gridTbl.getColumn(GridH2KeyValueRowOnheap.KEY_COL);
    Column h2ValCol = gridTbl.getColumn(GridH2KeyValueRowOnheap.VAL_COL);
    GridSqlColumn keyCol = new GridSqlColumn(h2KeyCol, tbl, h2KeyCol.getName());
    keyCol.resultType(GridSqlType.fromColumn(h2KeyCol));
    GridSqlColumn valCol = new GridSqlColumn(h2ValCol, tbl, h2ValCol.getName());
    valCol.resultType(GridSqlType.fromColumn(h2ValCol));
    mapQry.addColumn(keyCol, true);
    mapQry.addColumn(valCol, true);
    for (GridSqlColumn c : update.cols()) {
        String newColName = Parser.quoteIdentifier("_upd_" + c.columnName());
        // We have to use aliases to cover cases when the user
        // wants to update _val field directly (if it's a literal)
        GridSqlAlias alias = new GridSqlAlias(newColName, elementOrDefault(update.set().get(c.columnName()), c), true);
        alias.resultType(c.resultType());
        mapQry.addColumn(alias, true);
    }
    GridSqlElement where = update.where();
    if (keysParamIdx != null)
        where = injectKeysFilterParam(where, keyCol, keysParamIdx);
    mapQry.where(where);
    mapQry.limit(update.limit());
    return mapQry;
}

Example 22 with Select

use of org.h2.command.dml.Select in project elastic-core-maven by OrdinaryDude.

the class FullTextTrigger method reindexTable.

/**
 * Reindex the table
 *
 * @param   conn                SQL connection
 * @throws  SQLException        Unable to reindex table
 */
private void reindexTable(Connection conn) throws SQLException {
    if (indexColumns.isEmpty()) {
        return;
    }
    // 
    // Build the SELECT statement for just the indexed columns
    // 
    StringBuilder sb = new StringBuilder();
    sb.append("SELECT DB_ID");
    for (int index : indexColumns) {
        sb.append(", ").append(columnNames.get(index));
    }
    sb.append(" FROM ").append(tableName);
    Object[] row = new Object[columnNames.size()];
    // 
    try (Statement qstmt = conn.createStatement();
        ResultSet rs = qstmt.executeQuery(sb.toString())) {
        while (rs.next()) {
            row[dbColumn] = rs.getObject(1);
            int i = 2;
            for (int index : indexColumns) {
                row[index] = rs.getObject(i++);
            }
            indexRow(row);
        }
    }
    // 
    // Commit the index updates
    // 
    commitIndex();
}

Example 23 with Select

use of org.h2.command.dml.Select 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);
    }
}

Example 24 with Select

use of org.h2.command.dml.Select in project elastic-core-maven by OrdinaryDude.

the class FullTextTrigger method init.

/**
 * Initialize the trigger (Trigger interface)
 *
 * @param   conn                Database connection
 * @param   schema              Database schema name
 * @param   trigger             Database trigger name
 * @param   table               Database table name
 * @param   before              TRUE if trigger is called before database operation
 * @param   type                Trigger type
 * @throws  SQLException        A SQL error occurred
 */
@Override
public void init(Connection conn, String schema, String trigger, String table, boolean before, int type) throws SQLException {
    // 
    if (!isActive || table.contains("_COPY_")) {
        return;
    }
    // 
    // Access the Lucene index
    // 
    // We need to get the access just once, either in a trigger or in a function alias
    // 
    getIndexAccess(conn);
    // 
    // Get table and index information
    // 
    tableName = schema + "." + table;
    try (Statement stmt = conn.createStatement()) {
        // 
        try (ResultSet rs = stmt.executeQuery("SHOW COLUMNS FROM " + table + " FROM " + schema)) {
            int index = 0;
            while (rs.next()) {
                String columnName = rs.getString("FIELD");
                String columnType = rs.getString("TYPE");
                columnType = columnType.substring(0, columnType.indexOf('('));
                columnNames.add(columnName);
                columnTypes.add(columnType);
                if (columnName.equals("DB_ID")) {
                    dbColumn = index;
                }
                index++;
            }
        }
        if (dbColumn < 0) {
            Logger.logErrorMessage("DB_ID column not found for table " + tableName);
            return;
        }
        // 
        try (ResultSet rs = stmt.executeQuery(String.format("SELECT COLUMNS FROM FTL.INDEXES WHERE SCHEMA = '%s' AND TABLE = '%s'", schema, table))) {
            if (rs.next()) {
                String[] columns = rs.getString(1).split(",");
                for (String column : columns) {
                    int pos = columnNames.indexOf(column);
                    if (pos >= 0) {
                        if (columnTypes.get(pos).equals("VARCHAR")) {
                            indexColumns.add(pos);
                        } else {
                            Logger.logErrorMessage("Indexed column " + column + " in table " + tableName + " is not a string");
                        }
                    } else {
                        Logger.logErrorMessage("Indexed column " + column + " not found in table " + tableName);
                    }
                }
            }
        }
        if (indexColumns.isEmpty()) {
            Logger.logErrorMessage("No indexed columns found for table " + tableName);
            return;
        }
        // 
        // Trigger is enabled
        // 
        isEnabled = true;
        indexTriggers.put(tableName, this);
    } catch (SQLException exc) {
        Logger.logErrorMessage("Unable to get table information", exc);
    }
}

Example 25 with Select

use of org.h2.command.dml.Select in project elastic-core-maven by OrdinaryDude.

the class FullTextTrigger method dropAll.

/**
 * Drop all fulltext indexes
 *
 * @param   conn                SQL connection
 * @throws  SQLException        Unable to drop fulltext indexes
 */
public static void dropAll(Connection conn) throws SQLException {
    // 
    try (Statement qstmt = conn.createStatement();
        Statement stmt = conn.createStatement();
        ResultSet rs = qstmt.executeQuery("SELECT TABLE FROM FTL.INDEXES")) {
        while (rs.next()) {
            String table = rs.getString(1);
            stmt.execute("DROP TRIGGER IF EXISTS FTL_" + table);
        }
        stmt.execute("TRUNCATE TABLE FTL.INDEXES");
        indexTriggers.clear();
    }
    // 
    // Delete the Lucene index
    // 
    removeIndexFiles(conn);
}