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

Example 51 with Delete

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

Example 52 with Delete

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

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

the class UpdatePlanBuilder method planForUpdate.

/**
     * Prepare update plan for UPDATE or DELETE.
     *
     * @param stmt UPDATE or DELETE statement.
     * @param errKeysPos index to inject param for re-run keys at. Null if it's not a re-run plan.
     * @return Update plan.
     * @throws IgniteCheckedException if failed.
     */
private static UpdatePlan planForUpdate(GridSqlStatement stmt, @Nullable Integer errKeysPos) throws IgniteCheckedException {
    GridSqlElement target;
    FastUpdateArguments fastUpdate;
    UpdateMode mode;
    if (stmt instanceof GridSqlUpdate) {
        // Let's verify that user is not trying to mess with key's columns directly
        verifyUpdateColumns(stmt);
        GridSqlUpdate update = (GridSqlUpdate) stmt;
        target = update.target();
        fastUpdate = DmlAstUtils.getFastUpdateArgs(update);
        mode = UpdateMode.UPDATE;
    } else if (stmt instanceof GridSqlDelete) {
        GridSqlDelete del = (GridSqlDelete) stmt;
        target = del.from();
        fastUpdate = DmlAstUtils.getFastDeleteArgs(del);
        mode = UpdateMode.DELETE;
    } else
        throw new IgniteSQLException("Unexpected DML operation [cls=" + stmt.getClass().getName() + ']', IgniteQueryErrorCode.UNEXPECTED_OPERATION);
    GridSqlTable tbl = gridTableForElement(target);
    GridH2Table gridTbl = tbl.dataTable();
    GridH2RowDescriptor desc = gridTbl.rowDescriptor();
    if (desc == null)
        throw new IgniteSQLException("Row descriptor undefined for table '" + gridTbl.getName() + "'", IgniteQueryErrorCode.NULL_TABLE_DESCRIPTOR);
    if (fastUpdate != null)
        return UpdatePlan.forFastUpdate(mode, gridTbl, fastUpdate);
    else {
        GridSqlSelect sel;
        if (stmt instanceof GridSqlUpdate) {
            List<GridSqlColumn> updatedCols = ((GridSqlUpdate) stmt).cols();
            int valColIdx = -1;
            String[] colNames = new String[updatedCols.size()];
            int[] colTypes = new int[updatedCols.size()];
            for (int i = 0; i < updatedCols.size(); i++) {
                colNames[i] = updatedCols.get(i).columnName();
                colTypes[i] = updatedCols.get(i).resultType().type();
                Column column = updatedCols.get(i).column();
                if (desc.isValueColumn(column.getColumnId()))
                    valColIdx = i;
            }
            boolean hasNewVal = (valColIdx != -1);
            // Statement updates distinct properties if it does not have _val in updated columns list
            // or if its list of updated columns includes only _val, i.e. is single element.
            boolean hasProps = !hasNewVal || updatedCols.size() > 1;
            // Index of new _val in results of SELECT
            if (hasNewVal)
                valColIdx += 2;
            int newValColIdx = (hasNewVal ? valColIdx : 1);
            KeyValueSupplier newValSupplier = createSupplier(desc.context(), desc.type(), newValColIdx, hasProps, false, true);
            sel = DmlAstUtils.selectForUpdate((GridSqlUpdate) stmt, errKeysPos);
            return UpdatePlan.forUpdate(gridTbl, colNames, colTypes, newValSupplier, valColIdx, sel.getSQL());
        } else {
            sel = DmlAstUtils.selectForDelete((GridSqlDelete) stmt, errKeysPos);
            return UpdatePlan.forDelete(gridTbl, sel.getSQL());
        }
    }
}

Example 54 with Delete

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

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