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

Example 6 with JdbcSqlTemplate

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

the class FirebirdDdlReader 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.RDB$TRIGGER_NAME as TRIGGER_NAME, " + "TRIG.RDB$RELATION_NAME as TABLE_NAME, " + "TYPES1.RDB$TYPE_NAME as TRIGGER_TYPE, " + "TRIG.RDB$TRIGGER_SEQUENCE as TRIGGER_SEQUENCE, " + "TRIG.RDB$TRIGGER_BLR as TRIGGER_BLR, " + "TRIG.RDB$DESCRIPTION as DESCRIPTION, " + "TRIG.RDB$TRIGGER_INACTIVE as TRIGGER_INACTIVE, " + "TYPES2.RDB$TYPE_NAME as SYSTEM_FLAG, " + "TRIG.RDB$FLAGS as FLAGS, " + "TRIG.RDB$VALID_BLR as VALID_BLR, " + "TRIG.RDB$DEBUG_INFO as DEBUG_INFO," + "TRIG.RDB$TRIGGER_SOURCE as TRIGGER_SOURCE " + "from RDB$TRIGGERS as TRIG " + "inner join RDB$TYPES as TYPES1 " + "on TYPES1.RDB$FIELD_NAME = 'RDB$TRIGGER_TYPE' " + "and TYPES1.RDB$TYPE = TRIG.RDB$TRIGGER_TYPE " + "inner join RDB$TYPES as TYPES2 " + "on TYPES2.RDB$FIELD_NAME = 'RDB$SYSTEM_FLAG' " + "and TYPES2.RDB$TYPE = TRIG.RDB$SYSTEM_FLAG " + "where RDB$RELATION_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.setEnabled(true);
            trigger.setSource(row.getString("TRIGGER_SOURCE"));
            row.remove("TRIGGER_SOURCE");
            String triggerType = row.getString("TRIGGER_TYPE");
            if (triggerType.contains("STORE"))
                trigger.setTriggerType(TriggerType.INSERT);
            else if (triggerType.contains("ERASE"))
                trigger.setTriggerType(TriggerType.DELETE);
            else if (triggerType.contains("MODIFY"))
                trigger.setTriggerType(TriggerType.UPDATE);
            trigger.setMetaData(row);
            return trigger;
        }
    }, tableName);
    return triggers;
}

Example 7 with JdbcSqlTemplate

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

the class DerbyDdlReader 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.TRIGGERNAME as TRIGGER_NAME, " + "TAB.TABLENAME as TABLE_NAME, " + "SC.SCHEMANAME as SCHEMA_NAME, " + "TRIG.CREATIONTIMESTAMP, " + "TRIG.EVENT as TRIGGER_TYPE, " + "TRIG.STATE, " + "TRIG.FIRINGTIME as TRIGGER_TIME, " + "TRIG.WHENSTMTID, " + "TRIG.ACTIONSTMTID, " + "TRIG.REFERENCEDCOLUMNS, " + "TRIG.TRIGGERDEFINITION as source, " + "TRIG.REFERENCINGOLD, " + "TRIG.REFERENCINGNEW, " + "TRIG.OLDREFERENCINGNAME, " + "TRIG.NEWREFERENCINGNAME, " + "TRIG.TYPE, " + "TRIG.TRIGGERID, " + "TRIG.TABLEID, " + "TRIG.SCHEMAID " + "FROM SYS.SYSTRIGGERS AS TRIG " + "INNER JOIN SYS.SYSTABLES AS TAB " + "ON TAB.TABLEID = TRIG.TABLEID " + "INNER JOIN SYS.SYSSCHEMAS AS SC " + "ON SC.SCHEMAID = TRIG.SCHEMAID " + "WHERE TABLENAME=? and SC.SCHEMANAME=? ";
    triggers = sqlTemplate.query(sql, new ISqlRowMapper<Trigger>() {

        public Trigger mapRow(Row row) {
            Trigger trigger = new Trigger();
            trigger.setName(row.getString("TRIGGER_NAME"));
            trigger.setSchemaName(row.getString("SCHEMA_NAME"));
            trigger.setTableName(row.getString("TABLE_NAME"));
            if (row.getString("STATE").equals("E")) {
                row.put("STATE", "ENABLED");
                trigger.setEnabled(true);
            } else if (row.getString("STATE").equals("D")) {
                row.put("STATE", "DISABLED");
                trigger.setEnabled(false);
            }
            String event = row.getString("TRIGGER_TYPE");
            switch(event.charAt(0)) {
                case ('I'):
                    event = "INSERT";
                    break;
                case ('D'):
                    event = "DELETE";
                    break;
                case ('U'):
                    event = "UPDATE";
            }
            row.put("TRIGGER_TYPE", event);
            trigger.setTriggerType(TriggerType.valueOf(event));
            if (row.getString("TRIGGER_TIME").equals("A"))
                row.put("TRIGGER_TIME", "AFTER");
            else if (row.getString("TRIGGER_TIME").equals("B"))
                row.put("TRIGGER_TIME", "BEFORE");
            trigger.setMetaData(row);
            trigger.setSource(row.getString("source"));
            row.remove("source");
            return trigger;
        }
    }, tableName, schema);
    return triggers;
}

Example 8 with JdbcSqlTemplate

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

the class InformixDdlReader method getTriggers.

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 " + "trigname AS trigger_name, " + "owner AS schema_name, " + "tab.tabname AS table_name, " + "event AS trigger_type" + "trigid, " + "tabid, " + "old, " + "new, " + "collation " + "FROM systriggers AS trig " + "INNER JOIN systables AS tab " + "ON tab.tabid = trig.tabid " + "WHERE tab.tabname=? AND tab.owner=? ;";
    triggers = sqlTemplate.query(sql, new ISqlRowMapper<Trigger>() {

        public Trigger mapRow(Row row) {
            Trigger trigger = new Trigger();
            trigger.setName(row.getString("trigger_name"));
            trigger.setSchemaName(row.getString("schema_name"));
            trigger.setTableName(row.getString("table_name"));
            trigger.setEnabled(true);
            trigger.setSource("");
            String trigEvent = row.getString("trigger_type");
            switch(trigEvent.toUpperCase().charAt(0)) {
                case ('I'):
                    trigEvent = "INSERT";
                    break;
                case ('U'):
                    trigEvent = "UPDATE";
                    break;
                case ('D'):
                    trigEvent = "DELETE";
            }
            trigger.setTriggerType(TriggerType.valueOf(trigEvent));
            row.put("trigger_type", trigEvent);
            trigger.setMetaData(row);
            return trigger;
        }
    }, tableName, schema);
    for (final Trigger trigger : triggers) {
        String id = trigger.getMetaData().get("trigid").toString();
        String sourceSql = "SELECT " + "data " + "FROM systrigbody " + "WHERE trigid=? AND (datakey='A' OR datakey='D') " + "ORDER BY datakey DESC, seqno ASC ;";
        sqlTemplate.query(sourceSql, new ISqlRowMapper<Trigger>() {

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

Example 9 with JdbcSqlTemplate

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

the class HsqlDb2DdlReader 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 10 with JdbcSqlTemplate

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

the class OracleDdlReader method getTableNames.

public List<String> getTableNames(final String catalog, final String schema, final String[] tableTypes) {
    List<String> tableNames = new ArrayList<String>();
    JdbcSqlTemplate sqlTemplate = (JdbcSqlTemplate) platform.getSqlTemplate();
    StringBuilder sql = new StringBuilder("select TABLE_NAME from ALL_TABLES");
    Object[] params = null;
    if (isNotBlank(schema)) {
        sql.append(" where OWNER=?");
        params = new Object[] { schema };
    }
    tableNames = sqlTemplate.query(sql.toString(), new ISqlRowMapper<String>() {

        public String mapRow(Row row) {
            return row.getString("TABLE_NAME");
        }
    }, params);
    return tableNames;
}