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

Example 11 with Select

use of org.h2.command.dml.Select 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 12 with Select

use of org.h2.command.dml.Select in project siena by mandubian.

the class FullText method indexExistingRows.

/**
     * 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);
    }
}

Example 13 with Select

use of org.h2.command.dml.Select in project siena by mandubian.

the class FullText method dropIndex.

/**
     * 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 + ".FT_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 + ".FT_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;
        }
    }
}

Example 14 with Select

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

the class GridSqlQueryParser method collectOptimizedTableFiltersOrder.

/**
     * @param qry Query.
     */
private void collectOptimizedTableFiltersOrder(Query qry) {
    if (qry instanceof SelectUnion) {
        collectOptimizedTableFiltersOrder(((SelectUnion) qry).getLeft());
        collectOptimizedTableFiltersOrder(((SelectUnion) qry).getRight());
    } else {
        Select select = (Select) qry;
        TableFilter filter = select.getTopTableFilter();
        int i = 0;
        do {
            assert0(filter != null, select);
            assert0(filter.getNestedJoin() == null, select);
            // Here all the table filters must have generated unique aliases,
            // thus we can store them in the same map for all the subqueries.
            optimizedTableFilterOrder.put(filter.getTableAlias(), i++);
            Table tbl = filter.getTable();
            // Go down and collect inside of optimized subqueries.
            if (tbl instanceof TableView) {
                ViewIndex viewIdx = (ViewIndex) filter.getIndex();
                collectOptimizedTableFiltersOrder(viewIdx.getQuery());
            }
            filter = filter.getJoin();
        } while (filter != null);
    }
}

Example 15 with Select

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

the class GridSqlQueryParser method parseSelect.

/**
     * @param select Select.
     */
private GridSqlSelect parseSelect(Select select) {
    GridSqlSelect res = (GridSqlSelect) h2ObjToGridObj.get(select);
    if (res != null)
        return res;
    res = new GridSqlSelect();
    h2ObjToGridObj.put(select, res);
    res.distinct(select.isDistinct());
    Expression where = CONDITION.get(select);
    res.where(parseExpression(where, true));
    ArrayList<TableFilter> tableFilters = new ArrayList<>();
    TableFilter filter = select.getTopTableFilter();
    do {
        assert0(filter != null, select);
        assert0(filter.getNestedJoin() == null, select);
        // Can use optimized join order only if we are not inside of an expression.
        if (parsingSubQryExpression == 0 && optimizedTableFilterOrder != null) {
            String tblAlias = filter.getTableAlias();
            int idx = optimizedTableFilterOrder.get(tblAlias);
            setElementAt(tableFilters, idx, filter);
        } else
            tableFilters.add(filter);
        filter = filter.getJoin();
    } while (filter != null);
    // Build FROM clause from correctly ordered table filters.
    GridSqlElement from = null;
    for (int i = 0; i < tableFilters.size(); i++) {
        TableFilter f = tableFilters.get(i);
        GridSqlElement gridFilter = parseTableFilter(f);
        from = from == null ? gridFilter : new GridSqlJoin(from, gridFilter, f.isJoinOuter(), parseExpression(f.getJoinCondition(), true));
    }
    res.from(from);
    ArrayList<Expression> expressions = select.getExpressions();
    for (int i = 0; i < expressions.size(); i++) res.addColumn(parseExpression(expressions.get(i), true), i < select.getColumnCount());
    int[] grpIdx = GROUP_INDEXES.get(select);
    if (grpIdx != null)
        res.groupColumns(grpIdx);
    int havingIdx = HAVING_INDEX.get(select);
    if (havingIdx >= 0)
        res.havingColumn(havingIdx);
    processSortOrder(select.getSortOrder(), res);
    res.limit(parseExpression(select.getLimit(), false));
    res.offset(parseExpression(select.getOffset(), false));
    return res;
}