Examples with ISqlTemplate org.jumpmind.db.sql.ISqlTemplate used on opensource projects

Example 21 with ISqlTemplate

use of org.jumpmind.db.sql.ISqlTemplate in project symmetric-ds by JumpMind.

the class MsSqlSymmetricDialect method alterLockEscalation.

protected boolean alterLockEscalation() {
    ISqlTemplate sqlTemplate = platform.getSqlTemplate();
    String tablePrefix = getTablePrefix();
    try {
        String lockEscalationClause = "";
        if (parameterService.is(ParameterConstants.MSSQL_LOCK_ESCALATION_DISABLED, true)) {
            lockEscalationClause = "or t.lock_escalation != 1  or i.allow_page_locks = 'true' ";
        }
        if (parameterService.is(ParameterConstants.MSSQL_ROW_LEVEL_LOCKS_ONLY, true) && sqlTemplate.queryForInt("select count(*) from sys.indexes i inner join sys.tables t on t.object_id=i.object_id where t.name in ('" + tablePrefix.toLowerCase() + "_outgoing_batch','" + tablePrefix.toLowerCase() + "_data', '" + tablePrefix.toLowerCase() + "_data_event') and (i.allow_row_locks !='true' " + lockEscalationClause + ")") > 0) {
            log.info("Updating indexes to prevent lock escalation");
            String dataTable = platform.alterCaseToMatchDatabaseDefaultCase(tablePrefix + "_data");
            String dataEventTable = platform.alterCaseToMatchDatabaseDefaultCase(tablePrefix + "_data_event");
            String outgoingBatchTable = platform.alterCaseToMatchDatabaseDefaultCase(tablePrefix + "_outgoing_batch");
            sqlTemplate.update("ALTER INDEX ALL ON " + dataTable + " SET (ALLOW_ROW_LOCKS = ON)");
            sqlTemplate.update("ALTER INDEX ALL ON " + dataEventTable + " SET (ALLOW_ROW_LOCKS = ON)");
            sqlTemplate.update("ALTER INDEX ALL ON " + outgoingBatchTable + " SET (ALLOW_ROW_LOCKS = ON)");
            if (parameterService.is(ParameterConstants.MSSQL_LOCK_ESCALATION_DISABLED, true)) {
                sqlTemplate.update("ALTER INDEX ALL ON " + dataTable + " SET (ALLOW_PAGE_LOCKS = OFF)");
                sqlTemplate.update("ALTER INDEX ALL ON " + dataEventTable + " SET (ALLOW_PAGE_LOCKS = OFF)");
                sqlTemplate.update("ALTER INDEX ALL ON " + outgoingBatchTable + " SET (ALLOW_PAGE_LOCKS = OFF)");
                sqlTemplate.update("ALTER TABLE " + dataTable + " SET (LOCK_ESCALATION = DISABLE)");
                sqlTemplate.update("ALTER TABLE " + dataEventTable + " SET (LOCK_ESCALATION = DISABLE)");
                sqlTemplate.update("ALTER TABLE " + outgoingBatchTable + " SET (LOCK_ESCALATION = DISABLE)");
            }
            return true;
        } else {
            return false;
        }
    } catch (Exception e) {
        log.warn("Failed to disable lock escalation");
        log.debug("", e);
        return false;
    }
}

Example 22 with ISqlTemplate

use of org.jumpmind.db.sql.ISqlTemplate in project symmetric-ds by JumpMind.

the class Db2v9SymmetricDialect method createRequiredDatabaseObjects.

@Override
public void createRequiredDatabaseObjects() {
    ISqlTransaction transaction = null;
    try {
        transaction = platform.getSqlTemplate().startSqlTransaction();
        enableSyncTriggers(transaction);
        transaction.commit();
    } catch (Exception e) {
        try {
            log.info("Creating environment variables {} and {}", syncTriggersDisabledUserVariable, syncTriggersDisabledNodeVariable);
            ISqlTemplate template = getPlatform().getSqlTemplate();
            template.update("CREATE VARIABLE " + syncTriggersDisabledNodeVariable + " VARCHAR(50)");
            template.update("GRANT READ on VARIABLE " + syncTriggersDisabledNodeVariable + " TO PUBLIC");
            template.update("GRANT WRITE on VARIABLE " + syncTriggersDisabledNodeVariable + " TO PUBLIC");
            template.update("CREATE VARIABLE " + syncTriggersDisabledUserVariable + " INTEGER");
            template.update("GRANT READ on VARIABLE " + syncTriggersDisabledUserVariable + " TO PUBLIC");
            template.update("GRANT WRITE on VARIABLE " + syncTriggersDisabledUserVariable + " TO PUBLIC");
        } catch (Exception ex) {
            log.error("Error while initializing DB2 dialect", ex);
        }
    } finally {
        close(transaction);
    }
    super.createRequiredDatabaseObjects();
}

Example 23 with ISqlTemplate

use of org.jumpmind.db.sql.ISqlTemplate in project symmetric-ds by JumpMind.

the class DataGapFastDetector method fixOverlappingGaps.

protected void fixOverlappingGaps(List<DataGap> gaps, ProcessInfo processInfo) {
    try {
        ISqlTransaction transaction = null;
        log.debug("Looking for overlapping gaps");
        try {
            ISqlTemplate sqlTemplate = symmetricDialect.getPlatform().getSqlTemplate();
            transaction = sqlTemplate.startSqlTransaction();
            DataGap prevGap = null, lastGap = null;
            for (int i = 0; i < gaps.size(); i++) {
                DataGap curGap = gaps.get(i);
                if (lastGap != null) {
                    log.warn("Removing gap found after last gap: " + curGap);
                    dataService.deleteDataGap(transaction, curGap);
                    gaps.remove(i--);
                } else {
                    if (lastGap == null && curGap.gapSize() >= maxDataToSelect - 1) {
                        lastGap = curGap;
                    }
                    if (prevGap != null) {
                        if (prevGap.overlaps(curGap)) {
                            log.warn("Removing overlapping gaps: " + prevGap + ", " + curGap);
                            dataService.deleteDataGap(transaction, prevGap);
                            dataService.deleteDataGap(transaction, curGap);
                            DataGap newGap = null;
                            if (curGap.equals(lastGap)) {
                                newGap = new DataGap(prevGap.getStartId(), prevGap.getStartId() + maxDataToSelect - 1);
                            } else {
                                newGap = new DataGap(prevGap.getStartId(), prevGap.getEndId() > curGap.getEndId() ? prevGap.getEndId() : curGap.getEndId());
                            }
                            log.warn("Inserting new gap to fix overlap: " + newGap);
                            dataService.insertDataGap(transaction, newGap);
                            gaps.remove(i--);
                            gaps.set(i, newGap);
                            curGap = newGap;
                        }
                    }
                }
                prevGap = curGap;
            }
            transaction.commit();
        } catch (Error ex) {
            if (transaction != null) {
                transaction.rollback();
            }
            throw ex;
        } catch (RuntimeException ex) {
            if (transaction != null) {
                transaction.rollback();
            }
            throw ex;
        } finally {
            if (transaction != null) {
                transaction.close();
            }
        }
    } catch (RuntimeException ex) {
        processInfo.setStatus(Status.ERROR);
        throw ex;
    }
}

Example 24 with ISqlTemplate

use of org.jumpmind.db.sql.ISqlTemplate in project symmetric-ds by JumpMind.

the class DataGapRouteReader method prepareCursor.

protected ISqlReadCursor<Data> prepareCursor() {
    IParameterService parameterService = engine.getParameterService();
    int numberOfGapsToQualify = parameterService.getInt(ParameterConstants.ROUTING_MAX_GAPS_TO_QUALIFY_IN_SQL, 100);
    int maxGapsBeforeGreaterThanQuery = parameterService.getInt(ParameterConstants.ROUTING_DATA_READER_THRESHOLD_GAPS_TO_USE_GREATER_QUERY, 100);
    boolean useGreaterThanDataId = false;
    if (maxGapsBeforeGreaterThanQuery > 0 && this.dataGaps.size() > maxGapsBeforeGreaterThanQuery) {
        useGreaterThanDataId = true;
    }
    String channelId = context.getChannel().getChannelId();
    String sql = null;
    Boolean lastSelectUsedGreaterThanQuery = lastSelectUsedGreaterThanQueryByEngineName.get(parameterService.getEngineName());
    if (lastSelectUsedGreaterThanQuery == null) {
        lastSelectUsedGreaterThanQuery = Boolean.FALSE;
    }
    if (useGreaterThanDataId) {
        sql = getSql("selectDataUsingStartDataId", context.getChannel().getChannel());
        if (!lastSelectUsedGreaterThanQuery) {
            log.info("Switching to select from the data table where data_id >= start gap because there were {} gaps found " + "which was more than the configured threshold of {}", dataGaps.size(), maxGapsBeforeGreaterThanQuery);
            lastSelectUsedGreaterThanQueryByEngineName.put(parameterService.getEngineName(), Boolean.TRUE);
        }
    } else {
        sql = qualifyUsingDataGaps(dataGaps, numberOfGapsToQualify, getSql("selectDataUsingGapsSql", context.getChannel().getChannel()));
        if (lastSelectUsedGreaterThanQuery) {
            log.info("Switching to select from the data table where data_id between gaps");
            lastSelectUsedGreaterThanQueryByEngineName.put(parameterService.getEngineName(), Boolean.FALSE);
        }
    }
    if (parameterService.is(ParameterConstants.ROUTING_DATA_READER_ORDER_BY_DATA_ID_ENABLED, true)) {
        sql = String.format("%s %s", sql, engine.getRouterService().getSql("orderByDataId"));
    }
    ISqlTemplate sqlTemplate = engine.getSymmetricDialect().getPlatform().getSqlTemplate();
    Object[] args = null;
    int[] types = null;
    int dataIdSqlType = engine.getSymmetricDialect().getSqlTypeForIds();
    if (useGreaterThanDataId) {
        args = new Object[] { channelId, dataGaps.get(0).getStartId() };
        types = new int[] { Types.VARCHAR, dataIdSqlType };
    } else {
        int numberOfArgs = 1 + 2 * (numberOfGapsToQualify < dataGaps.size() ? numberOfGapsToQualify : dataGaps.size());
        args = new Object[numberOfArgs];
        types = new int[numberOfArgs];
        args[0] = channelId;
        types[0] = Types.VARCHAR;
        for (int i = 0; i < numberOfGapsToQualify && i < dataGaps.size(); i++) {
            DataGap gap = dataGaps.get(i);
            args[i * 2 + 1] = gap.getStartId();
            types[i * 2 + 1] = dataIdSqlType;
            if ((i + 1) == numberOfGapsToQualify && (i + 1) < dataGaps.size()) {
                /*
                     * there were more gaps than we are going to use in the SQL.
                     * use the last gap as the end data id for the last range
                     */
                args[i * 2 + 2] = dataGaps.get(dataGaps.size() - 1).getEndId();
            } else {
                args[i * 2 + 2] = gap.getEndId();
            }
            types[i * 2 + 2] = dataIdSqlType;
        }
    }
    this.currentGap = dataGaps.remove(0);
    return sqlTemplate.queryForCursor(sql, new ISqlRowMapper<Data>() {

        public Data mapRow(Row row) {
            return engine.getDataService().mapData(row);
        }
    }, args, types);
}

Example 25 with ISqlTemplate

use of org.jumpmind.db.sql.ISqlTemplate in project symmetric-ds by JumpMind.

the class DataGapDetector method beforeRouting.

/**
     * Always make sure sym_data_gap is up to date to make sure that we don't
     * dual route data.
     */
public void beforeRouting() {
    long printStats = System.currentTimeMillis();
    ProcessInfo processInfo = this.statisticManager.newProcessInfo(new ProcessInfoKey(nodeService.findIdentityNodeId(), null, ProcessType.GAP_DETECT));
    try {
        long ts = System.currentTimeMillis();
        processInfo.setStatus(Status.QUERYING);
        final List<DataGap> gaps = dataService.findDataGaps();
        long lastDataId = -1;
        final int dataIdIncrementBy = parameterService.getInt(ParameterConstants.DATA_ID_INCREMENT_BY);
        final long maxDataToSelect = parameterService.getLong(ParameterConstants.ROUTING_LARGEST_GAP_SIZE);
        final long gapTimoutInMs = parameterService.getLong(ParameterConstants.ROUTING_STALE_DATA_ID_GAP_TIME);
        long databaseTime = symmetricDialect.getDatabaseTime();
        int idsFilled = 0;
        int newGapsInserted = 0;
        int rangeChecked = 0;
        int gapsDeleted = 0;
        Set<DataGap> gapCheck = new HashSet<DataGap>(gaps);
        boolean supportsTransactionViews = symmetricDialect.supportsTransactionViews();
        long earliestTransactionTime = 0;
        if (supportsTransactionViews) {
            Date date = symmetricDialect.getEarliestTransactionStartTime();
            if (date != null) {
                earliestTransactionTime = date.getTime() - parameterService.getLong(ParameterConstants.DBDIALECT_ORACLE_TRANSACTION_VIEW_CLOCK_SYNC_THRESHOLD_MS, 60000);
            }
        }
        for (final DataGap dataGap : gaps) {
            final boolean lastGap = dataGap.equals(gaps.get(gaps.size() - 1));
            String sql = routerService.getSql("selectDistinctDataIdFromDataEventUsingGapsSql");
            ISqlTemplate sqlTemplate = symmetricDialect.getPlatform().getSqlTemplate();
            Object[] params = new Object[] { dataGap.getStartId(), dataGap.getEndId() };
            lastDataId = -1;
            processInfo.setStatus(Status.QUERYING);
            long queryForIdsTs = System.currentTimeMillis();
            List<Number> ids = sqlTemplate.query(sql, new NumberMapper(), params);
            if (System.currentTimeMillis() - queryForIdsTs > Constants.LONG_OPERATION_THRESHOLD) {
                log.info("It took longer than {}ms to run the following sql for gap from {} to {}.  {}", new Object[] { Constants.LONG_OPERATION_THRESHOLD, dataGap.getStartId(), dataGap.getEndId(), sql });
            }
            processInfo.setStatus(Status.PROCESSING);
            idsFilled += ids.size();
            rangeChecked += dataGap.getEndId() - dataGap.getStartId();
            ISqlTransaction transaction = null;
            try {
                transaction = sqlTemplate.startSqlTransaction();
                for (Number number : ids) {
                    long dataId = number.longValue();
                    processInfo.incrementCurrentDataCount();
                    if (lastDataId == -1 && dataGap.getStartId() + dataIdIncrementBy <= dataId) {
                        // there was a new gap at the start
                        DataGap newGap = new DataGap(dataGap.getStartId(), dataId - 1);
                        if (!gapCheck.contains(newGap)) {
                            dataService.insertDataGap(transaction, newGap);
                            gapCheck.add(newGap);
                        }
                        newGapsInserted++;
                    } else if (lastDataId != -1 && lastDataId + dataIdIncrementBy != dataId && lastDataId != dataId) {
                        // found a gap somewhere in the existing gap
                        DataGap newGap = new DataGap(lastDataId + 1, dataId - 1);
                        if (!gapCheck.contains(newGap)) {
                            dataService.insertDataGap(transaction, newGap);
                            gapCheck.add(newGap);
                        }
                        newGapsInserted++;
                    }
                    lastDataId = dataId;
                }
                // if we found data in the gap
                if (lastDataId != -1) {
                    if (!lastGap && lastDataId + dataIdIncrementBy <= dataGap.getEndId()) {
                        DataGap newGap = new DataGap(lastDataId + dataIdIncrementBy, dataGap.getEndId());
                        if (!gapCheck.contains(newGap)) {
                            dataService.insertDataGap(transaction, newGap);
                            gapCheck.add(newGap);
                        }
                        newGapsInserted++;
                    }
                    dataService.deleteDataGap(transaction, dataGap);
                    gapsDeleted++;
                // if we did not find data in the gap and it was not the
                // last gap
                } else if (!lastGap) {
                    Date createTime = dataGap.getCreateTime();
                    if (supportsTransactionViews) {
                        if (createTime != null && (createTime.getTime() < earliestTransactionTime || earliestTransactionTime == 0)) {
                            if (dataService.countDataInRange(dataGap.getStartId() - 1, dataGap.getEndId() + 1) == 0) {
                                if (dataGap.getStartId() == dataGap.getEndId()) {
                                    log.info("Found a gap in data_id at {}.  Skipping it because there are no pending transactions in the database", dataGap.getStartId());
                                } else {
                                    log.info("Found a gap in data_id from {} to {}.  Skipping it because there are no pending transactions in the database", dataGap.getStartId(), dataGap.getEndId());
                                }
                                dataService.deleteDataGap(transaction, dataGap);
                                gapsDeleted++;
                            }
                        }
                    } else if (createTime != null && databaseTime - createTime.getTime() > gapTimoutInMs) {
                        if (dataService.countDataInRange(dataGap.getStartId() - 1, dataGap.getEndId() + 1) == 0) {
                            if (dataGap.getStartId() == dataGap.getEndId()) {
                                log.info("Found a gap in data_id at {}.  Skipping it because the gap expired", dataGap.getStartId());
                            } else {
                                log.info("Found a gap in data_id from {} to {}.  Skipping it because the gap expired", dataGap.getStartId(), dataGap.getEndId());
                            }
                            dataService.deleteDataGap(transaction, dataGap);
                            gapsDeleted++;
                        }
                    }
                }
                if (System.currentTimeMillis() - printStats > 30000) {
                    log.info("The data gap detection process has been running for {}ms, detected {} rows that have been previously routed over a total gap range of {}, " + "inserted {} new gaps, and deleted {} gaps", new Object[] { System.currentTimeMillis() - ts, idsFilled, rangeChecked, newGapsInserted, gapsDeleted });
                    printStats = System.currentTimeMillis();
                }
                transaction.commit();
            } catch (Error ex) {
                if (transaction != null) {
                    transaction.rollback();
                }
                throw ex;
            } catch (RuntimeException ex) {
                if (transaction != null) {
                    transaction.rollback();
                }
                throw ex;
            } finally {
                if (transaction != null) {
                    transaction.close();
                }
            }
        }
        if (lastDataId != -1) {
            DataGap newGap = new DataGap(lastDataId + 1, lastDataId + maxDataToSelect);
            if (!gapCheck.contains(newGap)) {
                dataService.insertDataGap(newGap);
                gapCheck.add(newGap);
            }
        }
        long updateTimeInMs = System.currentTimeMillis() - ts;
        if (updateTimeInMs > 10000) {
            log.info("Detecting gaps took {} ms", updateTimeInMs);
        }
        processInfo.setStatus(Status.OK);
    } catch (RuntimeException ex) {
        processInfo.setStatus(Status.ERROR);
        throw ex;
    }
}