Search in sources :

Example 11 with Change

use of org.h2.mvstore.db.TransactionStore.Change in project h2database by h2database.

the class TestView method testViewAlterAndCommandCache.

/**
 * Make sure that when we change a view, that change in reflected in other
 * sessions command cache.
 */
private void testViewAlterAndCommandCache() throws SQLException {
    deleteDb("view");
    Connection conn = getConnection("view");
    Statement stat = conn.createStatement();
    stat.execute("create table t0(id int primary key)");
    stat.execute("create table t1(id int primary key)");
    stat.execute("insert into t0 values(0)");
    stat.execute("insert into t1 values(1)");
    stat.execute("create view v1 as select * from t0");
    ResultSet rs = stat.executeQuery("select * from v1");
    assertTrue(rs.next());
    assertEquals(0, rs.getInt(1));
    stat.execute("create or replace view v1 as select * from t1");
    rs = stat.executeQuery("select * from v1");
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    conn.close();
    deleteDb("view");
}
Also used : Statement(java.sql.Statement) PreparedStatement(java.sql.PreparedStatement) Connection(java.sql.Connection) JdbcConnection(org.h2.jdbc.JdbcConnection) ResultSet(java.sql.ResultSet)

Example 12 with Change

use of org.h2.mvstore.db.TransactionStore.Change in project narayana by jbosstm.

the class PerformanceTestCommitMarkableResource method testCommitMarkableResource.

// @org.junit.Ignore
@Test
public void testCommitMarkableResource() throws Exception {
    System.out.println("testCommitMarkableResource: " + new Date());
    ConnectionPoolDataSource dataSource = null;
    DataSource recoveryDataSource = null;
    if (dbType.equals("oracle")) {
        // ORA-01795: maximum number of expressions in a list is 1000
        BeanPopulator.getDefaultInstance(JTAEnvironmentBean.class).setCommitMarkableResourceRecordDeleteBatchSize(1000);
        Class clazz = Class.forName("oracle.jdbc.pool.OracleConnectionPoolDataSource");
        dataSource = (ConnectionPoolDataSource) clazz.newInstance();
        clazz.getMethod("setDriverType", new Class[] { String.class }).invoke(dataSource, new Object[] { "thin" });
        clazz.getMethod("setServerName", new Class[] { String.class }).invoke(dataSource, new Object[] { "tywin.eng.hst.ams2.redhat.com" });
        clazz.getMethod("setNetworkProtocol", new Class[] { String.class }).invoke(dataSource, new Object[] { "tcp" });
        clazz.getMethod("setDatabaseName", new Class[] { String.class }).invoke(dataSource, new Object[] { "orcl" });
        clazz.getMethod("setUser", new Class[] { String.class }).invoke(dataSource, new Object[] { "dtf11" });
        clazz.getMethod("setPassword", new Class[] { String.class }).invoke(dataSource, new Object[] { "dtf11" });
        clazz.getMethod("setPortNumber", new Class[] { int.class }).invoke(dataSource, new Object[] { 1521 });
        recoveryDataSource = (DataSource) dataSource;
    } else if (dbType.equals("sybase")) {
        // wide table support?
        BeanPopulator.getDefaultInstance(JTAEnvironmentBean.class).setCommitMarkableResourceRecordDeleteBatchSize(2000);
        Class clazz = Class.forName("com.sybase.jdbc3.jdbc.SybConnectionPoolDataSource");
        dataSource = (ConnectionPoolDataSource) clazz.newInstance();
        clazz.getMethod("setServerName", new Class[] { String.class }).invoke(dataSource, new Object[] { "192.168.1.5" });
        clazz.getMethod("setDatabaseName", new Class[] { String.class }).invoke(dataSource, new Object[] { "LOCALHOST" });
        clazz.getMethod("setUser", new Class[] { String.class }).invoke(dataSource, new Object[] { "sa" });
        clazz.getMethod("setPassword", new Class[] { String.class }).invoke(dataSource, new Object[] { "sybase" });
        clazz.getMethod("setPortNumber", new Class[] { int.class }).invoke(dataSource, new Object[] { 5000 });
        Class clazz2 = Class.forName("com.sybase.jdbc3.jdbc.SybDataSource");
        recoveryDataSource = (DataSource) clazz2.newInstance();
        clazz2.getMethod("setServerName", new Class[] { String.class }).invoke(recoveryDataSource, new Object[] { "192.168.1.5" });
        clazz2.getMethod("setDatabaseName", new Class[] { String.class }).invoke(recoveryDataSource, new Object[] { "LOCALHOST" });
        clazz2.getMethod("setUser", new Class[] { String.class }).invoke(recoveryDataSource, new Object[] { "sa" });
        clazz2.getMethod("setPassword", new Class[] { String.class }).invoke(recoveryDataSource, new Object[] { "sybase" });
        clazz2.getMethod("setPortNumber", new Class[] { int.class }).invoke(recoveryDataSource, new Object[] { 5000 });
    } else if (dbType.equals("h2")) {
        // Smaller batch size as H2 uses a hashtable in the delete which is
        // inefficent for bytearray clause
        BeanPopulator.getDefaultInstance(JTAEnvironmentBean.class).setCommitMarkableResourceRecordDeleteBatchSize(100);
        dataSource = new JdbcDataSource();
        ((JdbcDataSource) dataSource).setURL("jdbc:h2:mem:JBTMDB;MVCC=TRUE;DB_CLOSE_DELAY=-1");
        recoveryDataSource = ((JdbcDataSource) dataSource);
    } else if (dbType.equals("postgres")) {
        dataSource = new PGConnectionPoolDataSource();
        ((PGConnectionPoolDataSource) dataSource).setPortNumber(5432);
        ((PGConnectionPoolDataSource) dataSource).setUser("dtf11");
        ((PGConnectionPoolDataSource) dataSource).setPassword("dtf11");
        ((PGConnectionPoolDataSource) dataSource).setServerName("tywin.eng.hst.ams2.redhat.com");
        ((PGConnectionPoolDataSource) dataSource).setDatabaseName("jbossts");
        recoveryDataSource = new PGSimpleDataSource();
        ((PGSimpleDataSource) recoveryDataSource).setPortNumber(5432);
        ((PGSimpleDataSource) recoveryDataSource).setUser("dtf11");
        ((PGSimpleDataSource) recoveryDataSource).setPassword("dtf11");
        ((PGSimpleDataSource) recoveryDataSource).setServerName("tywin.eng.hst.ams2.redhat.com");
        ((PGSimpleDataSource) recoveryDataSource).setDatabaseName("jbossts");
    } else if (dbType.equals("mysql")) {
        // com.mysql.jdbc.PacketTooBigException: Packet for query is too
        // large (1318148 > 1048576). You can change this value on the
        // server by setting the max_allowed_packet' variable
        BeanPopulator.getDefaultInstance(JTAEnvironmentBean.class).setCommitMarkableResourceRecordDeleteBatchSize(3500);
        dataSource = new MysqlConnectionPoolDataSource();
        // need paranoid as otherwise it sends a connection change user
        ((MysqlConnectionPoolDataSource) dataSource).setUrl("jdbc:mysql://tywin.eng.hst.ams2.redhat.com:3306/jbossts?user=dtf11&password=dtf11&paranoid=true");
        recoveryDataSource = (DataSource) dataSource;
    } else if (dbType.equals("db2")) {
        Class clazz = Class.forName("com.ibm.db2.jcc.DB2ConnectionPoolDataSource");
        dataSource = (ConnectionPoolDataSource) clazz.newInstance();
        clazz.getMethod("setServerName", new Class[] { String.class }).invoke(dataSource, new Object[] { "tywin.eng.hst.ams2.redhat.com" });
        clazz.getMethod("setDatabaseName", new Class[] { String.class }).invoke(dataSource, new Object[] { "BTDB1" });
        clazz.getMethod("setUser", new Class[] { String.class }).invoke(dataSource, new Object[] { "db2" });
        clazz.getMethod("setPassword", new Class[] { String.class }).invoke(dataSource, new Object[] { "db2" });
        clazz.getMethod("setDriverType", new Class[] { int.class }).invoke(dataSource, new Object[] { 4 });
        clazz.getMethod("setPortNumber", new Class[] { int.class }).invoke(dataSource, new Object[] { 50001 });
        Class clazz2 = Class.forName("com.ibm.db2.jcc.DB2DataSource");
        recoveryDataSource = (DataSource) clazz2.newInstance();
        clazz2.getMethod("setServerName", new Class[] { String.class }).invoke(recoveryDataSource, new Object[] { "tywin.eng.hst.ams2.redhat.com" });
        clazz2.getMethod("setDatabaseName", new Class[] { String.class }).invoke(recoveryDataSource, new Object[] { "BTDB1" });
        clazz2.getMethod("setUser", new Class[] { String.class }).invoke(recoveryDataSource, new Object[] { "db2" });
        clazz2.getMethod("setPassword", new Class[] { String.class }).invoke(recoveryDataSource, new Object[] { "db2" });
        clazz2.getMethod("setDriverType", new Class[] { int.class }).invoke(recoveryDataSource, new Object[] { 4 });
        clazz2.getMethod("setPortNumber", new Class[] { int.class }).invoke(recoveryDataSource, new Object[] { 50001 });
    } else if (dbType.equals("sqlserver")) {
        Class clazz = Class.forName("com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource");
        dataSource = (ConnectionPoolDataSource) clazz.newInstance();
        clazz.getMethod("setServerName", new Class[] { String.class }).invoke(dataSource, new Object[] { "dev30.mw.lab.eng.bos.redhat.com" });
        clazz.getMethod("setDatabaseName", new Class[] { String.class }).invoke(dataSource, new Object[] { "dballo01" });
        clazz.getMethod("setUser", new Class[] { String.class }).invoke(dataSource, new Object[] { "dballo01" });
        clazz.getMethod("setPassword", new Class[] { String.class }).invoke(dataSource, new Object[] { "dballo01" });
        clazz.getMethod("setSendStringParametersAsUnicode", new Class[] { Boolean.class }).invoke(dataSource, new Object[] { false });
        clazz.getMethod("setPortNumber", new Class[] { int.class }).invoke(dataSource, new Object[] { 3918 });
        recoveryDataSource = (DataSource) dataSource;
    }
    PooledConnection pooledConnection = dataSource.getPooledConnection();
    Utils.createTables(pooledConnection.getConnection());
    doTest(new Handler(dataSource, recoveryDataSource), "_testCommitMarkableResource_" + dbType);
}
Also used : JdbcDataSource(org.h2.jdbcx.JdbcDataSource) PGConnectionPoolDataSource(org.postgresql.ds.PGConnectionPoolDataSource) JTAEnvironmentBean(com.arjuna.ats.jta.common.JTAEnvironmentBean) Date(java.util.Date) PGSimpleDataSource(org.postgresql.ds.PGSimpleDataSource) XADataSource(javax.sql.XADataSource) PGConnectionPoolDataSource(org.postgresql.ds.PGConnectionPoolDataSource) MysqlXADataSource(com.mysql.jdbc.jdbc2.optional.MysqlXADataSource) DataSource(javax.sql.DataSource) JdbcDataSource(org.h2.jdbcx.JdbcDataSource) MysqlConnectionPoolDataSource(com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource) ConnectionPoolDataSource(javax.sql.ConnectionPoolDataSource) PGXADataSource(org.postgresql.xa.PGXADataSource) PGConnectionPoolDataSource(org.postgresql.ds.PGConnectionPoolDataSource) MysqlConnectionPoolDataSource(com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource) ConnectionPoolDataSource(javax.sql.ConnectionPoolDataSource) PooledConnection(javax.sql.PooledConnection) PGSimpleDataSource(org.postgresql.ds.PGSimpleDataSource) MysqlConnectionPoolDataSource(com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource) Test(org.junit.Test)

Example 13 with Change

use of org.h2.mvstore.db.TransactionStore.Change in project siena by mandubian.

the class FullText method setIgnoreList.

/**
     * 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);
    }
}
Also used : PreparedStatement(java.sql.PreparedStatement) Statement(java.sql.Statement) PreparedStatement(java.sql.PreparedStatement) DbException(org.h2.message.DbException)

Example 14 with Change

use of org.h2.mvstore.db.TransactionStore.Change in project frostwire by frostwire.

the class FullTextLucene2 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;
    }
    try {
        IndexAccess access = getIndexAccess(conn);
        /*## LUCENE2 ##
            access.modifier.flush();
            String path = getIndexPath(conn);
            IndexReader reader = IndexReader.open(path);
            Analyzer analyzer = new StandardAnalyzer();
            Searcher searcher = new IndexSearcher(reader);
            QueryParser parser = new QueryParser(LUCENE_FIELD_DATA, analyzer);
            Query query = parser.parse(text);
            Hits hits = searcher.search(query);
            int max = hits.length();
            if (limit == 0) {
                limit = max;
            }
            for (int i = 0; i < limit && i + offset < max; i++) {
                Document doc = hits.doc(i + offset);
                float score = hits.score(i + offset);
            //*/
        // ## LUCENE3 ##
        // take a reference as the searcher may change
        Searcher searcher = access.searcher;
        // reuse the same analyzer; it's thread-safe;
        // also allows subclasses to control the analyzer used.
        Analyzer analyzer = access.writer.getAnalyzer();
        QueryParser parser = new QueryParser(Version.LUCENE_30, LUCENE_FIELD_DATA, analyzer);
        Query query = parser.parse(text);
        // Lucene 3 insists on a hard limit and will not provide
        // a total hits value. Take at least 100 which is
        // an optimal limit for Lucene as any more
        // will trigger writing results to disk.
        int maxResults = (limit == 0 ? 100 : limit) + offset;
        TopDocs docs = searcher.search(query, maxResults);
        if (limit == 0) {
            limit = docs.totalHits;
        }
        for (int i = 0, len = docs.scoreDocs.length; i < limit && i + offset < docs.totalHits && i + offset < len; i++) {
            ScoreDoc sd = docs.scoreDocs[i + offset];
            Document doc = searcher.doc(sd.doc);
            float score = sd.score;
            // */
            String q = doc.get(LUCENE_FIELD_QUERY);
            if (data) {
                int idx = q.indexOf(" WHERE ");
                JdbcConnection c = (JdbcConnection) conn;
                Session session = (Session) c.getSession();
                Parser p = new Parser(session);
                String tab = q.substring(0, idx);
                ExpressionColumn expr = (ExpressionColumn) p.parseExpression(tab);
                String schemaName = expr.getOriginalTableAliasName();
                String tableName = expr.getColumnName();
                q = q.substring(idx + " WHERE ".length());
                Object[][] columnData = parseKey(conn, q);
                result.addRow(schemaName, tableName, columnData[0], columnData[1], score);
            } else {
                result.addRow(q, score);
            }
        }
    /*## LUCENE2 ##
            // TODO keep it open if possible
            reader.close();
            //*/
    } catch (Exception e) {
        throw convertException(e);
    }
    return result;
}
Also used : SimpleResultSet(org.h2.tools.SimpleResultSet) JdbcConnection(org.h2.jdbc.JdbcConnection) Analyzer(org.apache.lucene.analysis.Analyzer) StandardAnalyzer(org.apache.lucene.analysis.standard.StandardAnalyzer) Document(org.apache.lucene.document.Document) IOException(java.io.IOException) Parser(org.h2.command.Parser) QueryParser(org.apache.lucene.queryParser.QueryParser) ExpressionColumn(org.h2.expression.ExpressionColumn) QueryParser(org.apache.lucene.queryParser.QueryParser) Session(org.h2.engine.Session)

Example 15 with Change

use of org.h2.mvstore.db.TransactionStore.Change in project h2database by h2database.

the class Insert method insertRows.

private int insertRows() {
    session.getUser().checkRight(table, Right.INSERT);
    setCurrentRowNumber(0);
    table.fire(session, Trigger.INSERT, true);
    rowNumber = 0;
    GeneratedKeys generatedKeys = session.getGeneratedKeys();
    generatedKeys.initialize(table);
    int listSize = list.size();
    if (listSize > 0) {
        int columnLen = columns.length;
        for (int x = 0; x < listSize; x++) {
            session.startStatementWithinTransaction();
            generatedKeys.nextRow();
            Row newRow = table.getTemplateRow();
            Expression[] expr = list.get(x);
            setCurrentRowNumber(x + 1);
            for (int i = 0; i < columnLen; i++) {
                Column c = columns[i];
                int index = c.getColumnId();
                Expression e = expr[i];
                if (e != null) {
                    // e can be null (DEFAULT)
                    e = e.optimize(session);
                    try {
                        Value v = c.convert(e.getValue(session), session.getDatabase().getMode());
                        newRow.setValue(index, v);
                        if (e instanceof SequenceValue) {
                            generatedKeys.add(c);
                        }
                    } catch (DbException ex) {
                        throw setRow(ex, x, getSQL(expr));
                    }
                }
            }
            rowNumber++;
            table.validateConvertUpdateSequence(session, newRow);
            boolean done = table.fireBeforeRow(session, null, newRow);
            if (!done) {
                table.lock(session, true, false);
                try {
                    table.addRow(session, newRow);
                } catch (DbException de) {
                    if (handleOnDuplicate(de)) {
                        // MySQL returns 2 for updated row
                        // TODO: detect no-op change
                        rowNumber++;
                    } else {
                        // INSERT IGNORE case
                        rowNumber--;
                    }
                    continue;
                }
                generatedKeys.confirmRow(newRow);
                session.log(table, UndoLogRecord.INSERT, newRow);
                table.fireAfterRow(session, null, newRow, false);
            }
        }
    } else {
        table.lock(session, true, false);
        if (insertFromSelect) {
            query.query(0, this);
        } else {
            ResultInterface rows = query.query(0);
            while (rows.next()) {
                generatedKeys.nextRow();
                Value[] r = rows.currentRow();
                Row newRow = addRowImpl(r);
                if (newRow != null) {
                    generatedKeys.confirmRow(newRow);
                }
            }
            rows.close();
        }
    }
    table.fire(session, Trigger.INSERT, false);
    return rowNumber;
}
Also used : SequenceValue(org.h2.expression.SequenceValue) ResultInterface(org.h2.result.ResultInterface) Expression(org.h2.expression.Expression) Column(org.h2.table.Column) ExpressionColumn(org.h2.expression.ExpressionColumn) Value(org.h2.value.Value) SequenceValue(org.h2.expression.SequenceValue) GeneratedKeys(org.h2.engine.GeneratedKeys) Row(org.h2.result.Row) DbException(org.h2.message.DbException)

Aggregations

DbException (org.h2.message.DbException)7 IOException (java.io.IOException)5 PreparedStatement (java.sql.PreparedStatement)5 Statement (java.sql.Statement)4 ArrayList (java.util.ArrayList)4 ExpressionColumn (org.h2.expression.ExpressionColumn)4 Row (org.h2.result.Row)4 SearchRow (org.h2.result.SearchRow)4 Column (org.h2.table.Column)4 SQLException (java.sql.SQLException)3 Properties (java.util.Properties)3 Index (org.h2.index.Index)3 MVStore (org.h2.mvstore.MVStore)3 Connection (java.sql.Connection)2 ResultSet (java.sql.ResultSet)2 Analyzer (org.apache.lucene.analysis.Analyzer)2 StandardAnalyzer (org.apache.lucene.analysis.standard.StandardAnalyzer)2 Document (org.apache.lucene.document.Document)2 QueryParser (org.apache.lucene.queryParser.QueryParser)2 Constraint (org.h2.constraint.Constraint)2