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

Example 1 with JdbcSqlTemplate

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

the class AseDdlReader method getTriggers.

@Override
public List<Trigger> getTriggers(final String catalog, final String schema, final String tableName) throws SqlException {
    List<Trigger> triggers = new ArrayList<Trigger>();
    log.debug("Reading triggers for: " + tableName);
    JdbcSqlTemplate sqlTemplate = (JdbcSqlTemplate) platform.getSqlTemplate();
    String sql = "SELECT " + "trig.name AS trigger_name, " + "trig.id AS trigger_id, " + "tab.name AS table_name, " + "tab.id AS table_id, " + "db.name AS catalog, " + "trig.crdate AS created_on, " + "tab.deltrig AS table_delete_trigger_id, " + "tab.instrig AS table_insert_trigger_id, " + "tab.updtrig AS table_update_trigger_id " + "FROM sysobjects AS trig " + "INNER JOIN sysobjects AS tab " + "ON trig.id = tab.deltrig " + "OR trig.id = tab.instrig " + "OR trig.id = tab.updtrig " + "INNER JOIN master.dbo.sysdatabases AS db " + "ON db.dbid = db_id() " + "WHERE tab.name = ? AND db.name = ? ";
    triggers = sqlTemplate.query(sql, new ISqlRowMapper<Trigger>() {

        public Trigger mapRow(Row row) {
            Trigger trigger = new Trigger();
            trigger.setName(row.getString("trigger_name"));
            trigger.setTableName(row.getString("table_name"));
            trigger.setCatalogName(row.getString("catalog"));
            trigger.setEnabled(true);
            trigger.setSource("");
            if (row.getString("table_insert_trigger_id").equals(row.getString("trigger_id"))) {
                trigger.setTriggerType(TriggerType.INSERT);
                row.put("trigger_type", "insert");
            } else if (row.getString("table_delete_trigger_id").equals(row.getString("trigger_id"))) {
                trigger.setTriggerType(TriggerType.DELETE);
                row.put("trigger_type", "delete");
            } else if (row.getString("table_update_trigger_id").equals(row.getString("trigger_id"))) {
                trigger.setTriggerType(TriggerType.UPDATE);
                row.put("trigger_type", "update");
            }
            row.remove("table_insert_trigger_id");
            row.remove("table_delete_trigger_id");
            row.remove("table_update_trigger_id");
            trigger.setMetaData(row);
            return trigger;
        }
    }, tableName, catalog);
    for (final Trigger trigger : triggers) {
        int id = (Integer) trigger.getMetaData().get("trigger_id");
        String sourceSql = "SELECT text " + "FROM syscomments " + "WHERE id = ? " + "ORDER BY colid ";
        sqlTemplate.query(sourceSql, new ISqlRowMapper<Trigger>() {

            public Trigger mapRow(Row row) {
                trigger.setSource(trigger.getSource() + "\n" + row.getString("text"));
                return trigger;
            }
        }, id);
    }
    return triggers;
}

Example 2 with JdbcSqlTemplate

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

the class AbstractJdbcDdlReader method readTable.

public Table readTable(final String catalog, final String schema, final String table) {
    try {
        log.debug("reading table: " + table);
        JdbcSqlTemplate sqlTemplate = (JdbcSqlTemplate) platform.getSqlTemplate();
        return postprocessTableFromDatabase(sqlTemplate.execute(new IConnectionCallback<Table>() {

            public Table execute(Connection connection) throws SQLException {
                DatabaseMetaDataWrapper metaData = new DatabaseMetaDataWrapper();
                metaData.setMetaData(connection.getMetaData());
                metaData.setCatalog(catalog);
                metaData.setSchemaPattern(schema);
                metaData.setTableTypes(null);
                ResultSet tableData = null;
                try {
                    log.debug("getting table metadata for {}", table);
                    tableData = metaData.getTables(getTableNamePattern(table));
                    log.debug("done getting table metadata for {}", table);
                    if (tableData != null && tableData.next()) {
                        Map<String, Object> values = readMetaData(tableData, initColumnsForTable());
                        return readTable(connection, metaData, values);
                    } else {
                        return null;
                    }
                } finally {
                    close(tableData);
                }
            }
        }));
    } catch (SqlException e) {
        if (e.getMessage() != null && StringUtils.containsIgnoreCase(e.getMessage(), "does not exist")) {
            return null;
        } else {
            log.error("Failed to get metadata for {}", Table.getFullyQualifiedTableName(catalog, schema, table));
            throw e;
        }
    }
}

Example 3 with JdbcSqlTemplate

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

the class AbstractJdbcDdlReader method getTableNames.

public List<String> getTableNames(final String catalog, final String schema, final String[] tableTypes) {
    JdbcSqlTemplate sqlTemplate = (JdbcSqlTemplate) platform.getSqlTemplate();
    List<String> list = sqlTemplate.execute(new IConnectionCallback<List<String>>() {

        public List<String> execute(Connection connection) throws SQLException {
            ArrayList<String> list = new ArrayList<String>();
            DatabaseMetaData meta = connection.getMetaData();
            ResultSet rs = null;
            try {
                rs = meta.getTables(catalog, schema, null, tableTypes);
                while (rs.next()) {
                    String tableName = rs.getString("TABLE_NAME");
                    if (tableName == null) {
                        tableName = rs.getString("NAME");
                    }
                    list.add(tableName);
                }
                return list;
            } finally {
                close(rs);
            }
        }
    });
    return list;
}

Example 4 with JdbcSqlTemplate

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

the class H2DdlReader method getTriggers.

@Override
public List<Trigger> getTriggers(final String catalog, final String schema, final String tableName) throws SqlException {
    List<Trigger> triggers = new ArrayList<Trigger>();
    log.debug("Reading triggers for: " + tableName);
    JdbcSqlTemplate sqlTemplate = (JdbcSqlTemplate) platform.getSqlTemplate();
    String sql = "SELECT * FROM INFORMATION_SCHEMA.TRIGGERS " + "WHERE TABLE_NAME=? and TRIGGER_SCHEMA=? and TRIGGER_CATALOG=? ;";
    triggers = sqlTemplate.query(sql, new ISqlRowMapper<Trigger>() {

        public Trigger mapRow(Row row) {
            Trigger trigger = new Trigger();
            trigger.setName(row.getString("TRIGGER_NAME"));
            trigger.setCatalogName(row.getString("TRIGGER_CATALOG"));
            trigger.setSchemaName(row.getString("TRIGGER_SCHEMA"));
            trigger.setTableName(row.getString("TABLE_NAME"));
            trigger.setEnabled(true);
            trigger.setSource(row.getString("SQL"));
            row.remove("SQL");
            String triggerType = row.getString("TRIGGER_TYPE");
            if (triggerType.equals("DELETE") || triggerType.equals("INSERT") || triggerType.equals("UPDATE")) {
                trigger.setTriggerType(TriggerType.valueOf(triggerType));
            }
            trigger.setMetaData(row);
            return trigger;
        }
    }, tableName, schema, catalog);
    return triggers;
}

Example 5 with JdbcSqlTemplate

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

the class GreenplumDdlReader method getTriggers.

public List<Trigger> getTriggers(final String catalog, final String schema, final String tableName) {
    List<Trigger> triggers = new ArrayList<Trigger>();
    log.debug("Reading triggers for: " + tableName);
    JdbcSqlTemplate sqlTemplate = (JdbcSqlTemplate) platform.getSqlTemplate();
    String sql = "SELECT " + "trigger_name, " + "trigger_schema, " + "trigger_catalog, " + "event_manipulation AS trigger_type, " + "event_object_table AS table_name," + "trig.*, " + "pgproc.prosrc " + "FROM INFORMATION_SCHEMA.TRIGGERS AS trig " + "INNER JOIN pg_catalog.pg_trigger AS pgtrig " + "ON pgtrig.tgname=trig.trigger_name " + "INNER JOIN pg_catalog.pg_proc AS pgproc " + "ON pgproc.oid=pgtrig.tgfoid " + "WHERE event_object_table=? AND event_object_schema=?;";
    triggers = sqlTemplate.query(sql, new ISqlRowMapper<Trigger>() {

        public Trigger mapRow(Row row) {
            Trigger trigger = new Trigger();
            trigger.setName(row.getString("trigger_name"));
            trigger.setCatalogName(row.getString("trigger_catalog"));
            trigger.setSchemaName(row.getString("trigger_schema"));
            trigger.setTableName(row.getString("table_name"));
            trigger.setEnabled(true);
            trigger.setSource(row.getString("prosrc"));
            row.remove("prosrc");
            String triggerType = row.getString("trigger_type");
            if (triggerType.equals("DELETE") || triggerType.equals("INSERT") || triggerType.equals("UPDATE")) {
                trigger.setTriggerType(TriggerType.valueOf(triggerType));
            }
            trigger.setMetaData(row);
            return trigger;
        }
    }, tableName, schema);
    return triggers;
}