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

Example 1 with ISqlTemplate

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

the class RestService method queryNodeImpl.

private QueryResults queryNodeImpl(ISymmetricEngine engine, String sql, boolean isQuery) {
    QueryResults results = new QueryResults();
    org.jumpmind.symmetric.web.rest.model.Row xmlRow = null;
    org.jumpmind.symmetric.web.rest.model.Column xmlColumn = null;
    ISqlTemplate sqlTemplate = engine.getSqlTemplate();
    try {
        if (!isQuery) {
            int updates = sqlTemplate.update(sql);
            results.setNbrResults(updates);
            return results;
        }
        List<Row> rows = sqlTemplate.query(sql);
        int nbrRows = 0;
        for (Row row : rows) {
            xmlRow = new org.jumpmind.symmetric.web.rest.model.Row();
            Iterator<Map.Entry<String, Object>> itr = row.entrySet().iterator();
            int columnOrdinal = 0;
            while (itr.hasNext()) {
                xmlColumn = new org.jumpmind.symmetric.web.rest.model.Column();
                xmlColumn.setOrdinal(++columnOrdinal);
                Map.Entry<String, Object> pair = (Map.Entry<String, Object>) itr.next();
                xmlColumn.setName(pair.getKey());
                if (pair.getValue() != null) {
                    xmlColumn.setValue(pair.getValue().toString());
                }
                xmlRow.getColumnData().add(xmlColumn);
            }
            xmlRow.setRowNum(++nbrRows);
            results.getResults().add(xmlRow);
        }
        results.setNbrResults(nbrRows);
    } catch (Exception ex) {
        log.error("Exception while executing sql.", ex);
        throw new NotAllowedException("Error while executing sql %s.  Error is %s", sql, ex.getCause().getMessage());
    }
    return results;
}

Example 2 with ISqlTemplate

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

the class DataGapFastDetector method saveDataGaps.

protected long saveDataGaps(long ts, long printStats) {
    ISqlTemplate sqlTemplate = symmetricDialect.getPlatform().getSqlTemplate();
    int totalGapChanges = gapsDeleted.size() + gapsAdded.size();
    if (totalGapChanges > 0) {
        ISqlTransaction transaction = null;
        gaps = new ArrayList<DataGap>(gapsAll);
        Collections.sort(gaps);
        try {
            transaction = sqlTemplate.startSqlTransaction();
            int maxGapChanges = parameterService.getInt(ParameterConstants.ROUTING_MAX_GAP_CHANGES);
            if (!parameterService.is(ParameterConstants.CLUSTER_LOCKING_ENABLED) && (totalGapChanges > maxGapChanges || useInMemoryGaps)) {
                dataService.deleteAllDataGaps(transaction);
                if (useInMemoryGaps && totalGapChanges <= maxGapChanges) {
                    log.info("There are {} data gap changes, which is within the max of {}, so switching to database", totalGapChanges, maxGapChanges);
                    useInMemoryGaps = false;
                    printStats = insertDataGaps(transaction, ts, printStats);
                } else {
                    if (!useInMemoryGaps) {
                        log.info("There are {} data gap changes, which exceeds the max of {}, so switching to in-memory", totalGapChanges, maxGapChanges);
                        useInMemoryGaps = true;
                    }
                    DataGap newGap = new DataGap(gaps.get(0).getStartId(), gaps.get(gaps.size() - 1).getEndId());
                    dataService.insertDataGap(transaction, newGap);
                }
            } else {
                printStats = deleteDataGaps(transaction, ts, printStats);
                printStats = insertDataGaps(transaction, ts, printStats);
            }
            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();
            }
        }
    }
    return printStats;
}

Example 3 with ISqlTemplate

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

the class DataGapFastDetector method queryDataIdMap.

protected void queryDataIdMap() {
    String sql = routerService.getSql("selectDistinctDataIdFromDataEventUsingGapsSql");
    ISqlTemplate sqlTemplate = symmetricDialect.getPlatform().getSqlTemplate();
    for (DataGap dataGap : gaps) {
        long queryForIdsTs = System.currentTimeMillis();
        Object[] params = new Object[] { dataGap.getStartId(), dataGap.getEndId() };
        List<Long> ids = sqlTemplate.query(sql, this, params);
        dataIds.addAll(ids);
        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 });
        }
    }
}

Example 4 with ISqlTemplate

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

the class TransformationTest method testDeletesWithTransformedIdWork.

protected void testDeletesWithTransformedIdWork(ISymmetricEngine rootServer, ISymmetricEngine clientServer) throws Exception {
    String rootTableName = rootServer.getDatabasePlatform().getTableFromCache("TRANSFORM_TABLE_A_SRC", false).getName();
    String clientTableName = clientServer.getDatabasePlatform().getTableFromCache("TRANSFORM_TABLE_A_TGT", false).getName();
    ISqlTemplate rootTemplate = rootServer.getDatabasePlatform().getSqlTemplate();
    ISqlTemplate clientTemplate = clientServer.getDatabasePlatform().getSqlTemplate();
    rootTemplate.update(String.format("insert into %s values(?,?)", rootTableName), "1", 1);
    assertEquals(0, clientTemplate.queryForInt(String.format("select count(*) from %s", clientTableName)));
    pull("client");
    assertEquals(1, clientTemplate.queryForInt(String.format("select count(*) from %s", clientTableName)));
    rootTemplate.update(String.format("delete from %s", rootTableName));
    assertEquals(1, clientTemplate.queryForInt(String.format("select count(*) from %s", clientTableName)));
    pull("client");
    assertEquals(0, clientTemplate.queryForInt(String.format("select count(*) from %s", clientTableName)));
}

Example 5 with ISqlTemplate

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

the class GroupletService method getGrouplets.

public List<Grouplet> getGrouplets(boolean refreshCache) {
    long maxCacheTime = parameterService.getLong(ParameterConstants.CACHE_TIMEOUT_GROUPLETS_IN_MS);
    List<Grouplet> all = cache;
    if (all == null || System.currentTimeMillis() - lastCacheTime >= maxCacheTime || lastCacheTime == 0 || refreshCache) {
        ISqlTemplate sqlTemplate = platform.getSqlTemplate();
        final Map<String, Grouplet> groupletMap = new HashMap<String, Grouplet>();
        all = sqlTemplate.query(getSql("selectGroupletSql"), new ISqlRowMapper<Grouplet>() {

            public Grouplet mapRow(Row rs) {
                Grouplet grouplet = new Grouplet();
                grouplet.setGroupletId(rs.getString("grouplet_id"));
                grouplet.setDescription(rs.getString("description"));
                grouplet.setGroupletLinkPolicy(GroupletLinkPolicy.valueOf(rs.getString("grouplet_link_policy")));
                grouplet.setCreateTime(rs.getDateTime("create_time"));
                grouplet.setLastUpdateBy(rs.getString("last_update_by"));
                grouplet.setLastUpdateTime(rs.getDateTime("last_update_time"));
                groupletMap.put(grouplet.getGroupletId(), grouplet);
                return grouplet;
            }
        });
        sqlTemplate.query(getSql("selectGroupletLinkSql"), new ISqlRowMapper<GroupletLink>() {

            public GroupletLink mapRow(Row rs) {
                GroupletLink groupletLink = new GroupletLink();
                String groupletId = rs.getString("grouplet_id");
                Grouplet grouplet = groupletMap.get(groupletId);
                groupletLink.setExternalId(rs.getString("external_id"));
                groupletLink.setCreateTime(rs.getDateTime("create_time"));
                groupletLink.setLastUpdateBy(rs.getString("last_update_by"));
                groupletLink.setLastUpdateTime(rs.getDateTime("last_update_time"));
                if (grouplet != null) {
                    grouplet.getGroupletLinks().add(groupletLink);
                }
                return groupletLink;
            }
        });
        sqlTemplate.query(getSql("selectTriggerRouterGroupletSql"), new ISqlRowMapper<TriggerRouterGrouplet>() {

            public TriggerRouterGrouplet mapRow(Row rs) {
                TriggerRouterGrouplet trGrouplet = new TriggerRouterGrouplet();
                String groupletId = rs.getString("grouplet_id");
                Grouplet grouplet = groupletMap.get(groupletId);
                trGrouplet.setAppliesWhen(AppliesWhen.valueOf(rs.getString("applies_when")));
                trGrouplet.setRouterId(rs.getString("router_id"));
                trGrouplet.setTriggerId(rs.getString("trigger_id"));
                trGrouplet.setCreateTime(rs.getDateTime("create_time"));
                trGrouplet.setLastUpdateBy(rs.getString("last_update_by"));
                trGrouplet.setLastUpdateTime(rs.getDateTime("last_update_time"));
                if (grouplet != null) {
                    grouplet.getTriggerRouterGrouplets().add(trGrouplet);
                }
                return trGrouplet;
            }
        });
        cache = all;
        lastCacheTime = System.currentTimeMillis();
    }
    return all;
}