Search in sources :

Example 61 with Database

use of org.h2.engine.Database in project h2database by h2database.

the class TableFunction method getTable.

private ValueResultSet getTable(Session session, Expression[] argList, boolean onlyColumnList, boolean distinctRows) {
    int len = columnList.length;
    Expression[] header = new Expression[len];
    Database db = session.getDatabase();
    for (int i = 0; i < len; i++) {
        Column c = columnList[i];
        ExpressionColumn col = new ExpressionColumn(db, c);
        header[i] = col;
    }
    LocalResult result = new LocalResult(session, header, len);
    if (distinctRows) {
        result.setDistinct();
    }
    if (!onlyColumnList) {
        Value[][] list = new Value[len][];
        int rows = 0;
        for (int i = 0; i < len; i++) {
            Value v = argList[i].getValue(session);
            if (v == ValueNull.INSTANCE) {
                list[i] = new Value[0];
            } else {
                ValueArray array = (ValueArray) v.convertTo(Value.ARRAY);
                Value[] l = array.getList();
                list[i] = l;
                rows = Math.max(rows, l.length);
            }
        }
        for (int row = 0; row < rows; row++) {
            Value[] r = new Value[len];
            for (int j = 0; j < len; j++) {
                Value[] l = list[j];
                Value v;
                if (l.length <= row) {
                    v = ValueNull.INSTANCE;
                } else {
                    Column c = columnList[j];
                    v = l[row];
                    v = c.convert(v);
                    v = v.convertPrecision(c.getPrecision(), false);
                    v = v.convertScale(true, c.getScale());
                }
                r[j] = v;
            }
            result.addRow(r);
        }
    }
    result.done();
    return ValueResultSet.get(getSimpleResultSet(result, Integer.MAX_VALUE));
}
Also used : LocalResult(org.h2.result.LocalResult) Column(org.h2.table.Column) Database(org.h2.engine.Database) Value(org.h2.value.Value) ValueArray(org.h2.value.ValueArray)

Example 62 with Database

use of org.h2.engine.Database 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);
}
Also used : PreparedStatement(java.sql.PreparedStatement) Statement(java.sql.Statement) SimpleResultSet(org.h2.tools.SimpleResultSet) ResultSet(java.sql.ResultSet)

Example 63 with Database

use of org.h2.engine.Database in project h2database by h2database.

the class FullText method parseKey.

/**
 * 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 = columns.toArray();
    Object[] dat = data.toArray();
    Object[][] columnData = { col, dat };
    return columnData;
}
Also used : ValueExpression(org.h2.expression.ValueExpression) Expression(org.h2.expression.Expression) JdbcConnection(org.h2.jdbc.JdbcConnection) Session(org.h2.engine.Session) Parser(org.h2.command.Parser)

Example 64 with Database

use of org.h2.engine.Database in project h2database by h2database.

the class FullText method search.

/**
 * 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);
    }
    Set<String> words = new HashSet<>();
    addWords(setting, words, text);
    Set<Integer> rIds = null, lastRowIds;
    PreparedStatement prepSelectMapByWordId = setting.prepare(conn, SELECT_MAP_BY_WORD_ID);
    for (String word : words) {
        lastRowIds = rIds;
        rIds = new HashSet<>();
        Integer wId = setting.getWordId(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.isEmpty()) {
        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;
}
Also used : SimpleResultSet(org.h2.tools.SimpleResultSet) PreparedStatement(java.sql.PreparedStatement) SimpleResultSet(org.h2.tools.SimpleResultSet) ResultSet(java.sql.ResultSet) HashSet(java.util.HashSet)

Example 65 with Database

use of org.h2.engine.Database in project h2database by h2database.

the class FullTextLucene method getIndexPath.

/**
 * Get the path of the Lucene index for this database.
 *
 * @param conn the database connection
 * @return the path
 */
protected static String getIndexPath(Connection conn) throws SQLException {
    Statement stat = conn.createStatement();
    ResultSet rs = stat.executeQuery("CALL DATABASE_PATH()");
    rs.next();
    String path = rs.getString(1);
    if (path == null) {
        return IN_MEMORY_PREFIX + conn.getCatalog();
    }
    int index = path.lastIndexOf(':');
    // position 1 means a windows drive letter is used, ignore that
    if (index > 1) {
        path = path.substring(index + 1);
    }
    rs.close();
    return path;
}
Also used : PreparedStatement(java.sql.PreparedStatement) Statement(java.sql.Statement) SimpleResultSet(org.h2.tools.SimpleResultSet) ResultSet(java.sql.ResultSet)

Aggregations

Database (org.h2.engine.Database)79 SQLException (java.sql.SQLException)45 PreparedStatement (java.sql.PreparedStatement)38 DbException (org.h2.message.DbException)37 ResultSet (java.sql.ResultSet)34 Statement (java.sql.Statement)32 SimpleResultSet (org.h2.tools.SimpleResultSet)32 Connection (java.sql.Connection)27 Table (org.h2.table.Table)25 Value (org.h2.value.Value)25 Column (org.h2.table.Column)22 IOException (java.io.IOException)19 Constraint (org.h2.constraint.Constraint)18 Expression (org.h2.expression.Expression)17 ExpressionColumn (org.h2.expression.ExpressionColumn)17 ValueString (org.h2.value.ValueString)15 ValueExpression (org.h2.expression.ValueExpression)14 Session (org.h2.engine.Session)13 JdbcConnection (org.h2.jdbc.JdbcConnection)13 Schema (org.h2.schema.Schema)13