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

Example 11 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 12 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 13 with ISqlTemplate

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

the class MySqlDdlReader method readColumn.

@Override
protected Column readColumn(DatabaseMetaDataWrapper metaData, Map<String, Object> values) throws SQLException {
    Column column = super.readColumn(metaData, values);
    // is an illegal ISO value, so we replace it with NULL
    if ((column.getMappedTypeCode() == Types.TIMESTAMP) && "0000-00-00 00:00:00".equals(column.getDefaultValue())) {
        column.setDefaultValue(null);
    }
    // make sure the defaultvalue is null when an empty is returned.
    if ("".equals(column.getDefaultValue())) {
        column.setDefaultValue(null);
    }
    if (column.getJdbcTypeName().equalsIgnoreCase(TypeMap.POINT) || column.getJdbcTypeName().equalsIgnoreCase(TypeMap.LINESTRING) || column.getJdbcTypeName().equalsIgnoreCase(TypeMap.POLYGON)) {
        column.setJdbcTypeName(TypeMap.GEOMETRY);
    }
    if (column.getJdbcTypeName().equalsIgnoreCase("enum")) {
        ISqlTemplate template = platform.getSqlTemplate();
        String unParsedEnums = template.queryForString("SELECT SUBSTRING(COLUMN_TYPE,5) FROM information_schema.COLUMNS" + " WHERE TABLE_SCHEMA=? AND TABLE_NAME=? AND COLUMN_NAME=?", metaData.getCatalog(), (String) values.get("TABLE_NAME"), column.getName());
        if (unParsedEnums != null) {
            unParsedEnums = unParsedEnums.trim();
            if (unParsedEnums.startsWith("(")) {
                unParsedEnums = unParsedEnums.substring(1);
                if (unParsedEnums.endsWith(")")) {
                    unParsedEnums = unParsedEnums.substring(0, unParsedEnums.length() - 1);
                }
            }
            String[] parsedEnums = unParsedEnums.split(",");
            for (int i = 0; i < parsedEnums.length; i++) {
                String parsedEnum = parsedEnums[i];
                if (parsedEnum.startsWith("'")) {
                    parsedEnum = parsedEnum.substring(1);
                    if (parsedEnum.endsWith("'")) {
                        parsedEnum = parsedEnum.substring(0, parsedEnum.length() - 1);
                    }
                }
                parsedEnums[i] = parsedEnum;
            }
            column.setEnumValues(parsedEnums);
        }
    }
    return column;
}

Example 14 with ISqlTemplate

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

the class DbExportImportTest method exportThenImportCsv.

@Test
public void exportThenImportCsv() throws Exception {
    ISymmetricEngine engine = getSymmetricEngine();
    IDatabasePlatform platform = engine.getSymmetricDialect().getPlatform();
    Database testTables = platform.readDatabaseFromXml("/test-dbimport.xml", true);
    Table table = testTables.findTable("test_db_import_1", false);
    recreateImportTable();
    final int RECORD_COUNT = 100;
    DbFill fill = new DbFill(platform);
    fill.setRecordCount(RECORD_COUNT);
    fill.fillTables(table.getName());
    DbExport export = new DbExport(platform);
    export.setFormat(Format.CSV);
    export.setNoCreateInfo(true);
    export.setNoData(false);
    String csvOutput = export.exportTables(new String[] { table.getName() });
    logger.info(csvOutput);
    ISqlTemplate sqlTemplate = platform.getSqlTemplate();
    List<Row> rowsBeforeImport = sqlTemplate.query(SELECT_FROM_TEST_DB_IMPORT_1_ORDER_BY_ID);
    recreateImportTable();
    DbImport importCsv = new DbImport(platform);
    importCsv.setFormat(DbImport.Format.CSV);
    importCsv.importTables(csvOutput, table.getName());
    Assert.assertEquals(RECORD_COUNT, sqlTemplate.queryForInt("select count(*) from " + table.getName()));
    compareRows(table, rowsBeforeImport, sqlTemplate.query(SELECT_FROM_TEST_DB_IMPORT_1_ORDER_BY_ID));
// TODO test error
// TODO test replace
// TODO test ignore
// TODO test force
}

Example 15 with ISqlTemplate

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

the class DbExportImportTest method exportTableInAnotherSchemaOnH2.

@Test
public void exportTableInAnotherSchemaOnH2() throws Exception {
    if (getPlatform().getName().equals(DatabaseNamesConstants.H2)) {
        ISymmetricEngine engine = getSymmetricEngine();
        ISqlTemplate template = getPlatform().getSqlTemplate();
        template.update("CREATE SCHEMA IF NOT EXISTS A");
        template.update("CREATE TABLE IF NOT EXISTS A.TEST (ID INT, NOTES VARCHAR(100), PRIMARY KEY (ID))");
        template.update("DELETE FROM A.TEST");
        template.update("INSERT INTO A.TEST VALUES(1,'test')");
        DbExport export = new DbExport(engine.getDatabasePlatform());
        export.setSchema("A");
        export.setFormat(Format.SQL);
        export.setNoCreateInfo(false);
        export.setNoData(false);
        export.exportTables(new String[] { "TEST" }).toLowerCase();
    // TODO validate
    }
}