001/**
002 * Copyright 2005-2015 The Kuali Foundation
003 *
004 * Licensed under the Educational Community License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 * http://www.opensource.org/licenses/ecl2.php
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016package org.kuali.rice.krad.devtools.pdle;
017
018import java.sql.Connection;
019import java.sql.ResultSet;
020import java.sql.SQLException;
021import java.sql.Statement;
022import java.util.HashMap;
023import java.util.List;
024import java.util.Map;
025
026import org.apache.commons.lang.StringUtils;
027import org.apache.log4j.Logger;
028import org.kuali.rice.core.framework.persistence.jdbc.dao.PlatformAwareDaoBaseJdbc;
029import org.kuali.rice.krad.devtools.pdle.PostDataLoadEncryptionDao;
030import org.springframework.dao.DataAccessException;
031import org.springframework.jdbc.core.ConnectionCallback;
032import org.springframework.jdbc.core.RowMapper;
033
034public class PostDataLoadEncryptionDaoJdbc extends PlatformAwareDaoBaseJdbc implements PostDataLoadEncryptionDao {
035    private static final Logger LOG = Logger.getLogger(PostDataLoadEncryptionDaoJdbc.class);
036
037    protected static final String AND_SEPARATOR = " AND ";
038    protected static final String COMMA_SEPARATOR = ", ";
039    protected static final String BACKUP_TABLE_EXTENSION = "_bak";
040    protected static final String BACKUP_TABLE_ENCRYPT_IND = "ENCRYPT_IND";
041    
042    void executeSql(String sql) {
043        LOG.info("Executing sql: " + sql);
044        getJdbcTemplate().execute(sql);
045    }
046
047    public void createBackupTable(String tableName) {
048        executeSql(getDbPlatform().getCreateTableFromTableSql(tableName + "_bak", tableName));
049    }
050
051    public void truncateTable(String tableName) {
052        executeSql(getDbPlatform().getTruncateTableSql(tableName));
053    }
054
055    public void restoreTableFromBackup(String tableName) {
056        truncateTable(tableName);
057        executeSql(getDbPlatform().getInsertDataFromTableSql(tableName, tableName + "_bak"));
058    }
059
060    public void dropBackupTable(String tableName) {
061        executeSql(getDbPlatform().getDropTableSql(tableName + "_bak"));
062    }
063    
064    public boolean doesBackupTableExist(String tableName){
065        try{
066            Object tableNameObj = getJdbcTemplate().queryForObject(
067                "SELECT count(*) FROM " + tableName + BACKUP_TABLE_EXTENSION + " WHERE 0=1", Integer.class);
068            return true;
069        } catch(Exception ex){
070            return false;
071        }
072    }
073
074    public void addEncryptionIndicatorToBackupTable(String tableName){
075        executeSql(new StringBuffer().append("ALTER TABLE ")
076                .append(tableName + BACKUP_TABLE_EXTENSION)
077                .append(" ADD ")
078                .append(BACKUP_TABLE_ENCRYPT_IND)
079                .append(" VARCHAR2(1)").toString());
080    }
081
082    public void dropEncryptionIndicatorFromBackupTable(String tableName){
083        executeSql(new StringBuffer().append("ALTER TABLE ")
084                .append(tableName + BACKUP_TABLE_EXTENSION)
085                .append(" DROP COLUMN ")
086                .append(BACKUP_TABLE_ENCRYPT_IND).toString());
087    }
088    
089    public List<Map<String, String>> retrieveUnencryptedColumnValuesFromBackupTable(String tableName, final List<String> columnNames, int numberOfRowsToCommitAfter) {
090        return getJdbcTemplate().query(
091                getSelectBackupTableColumnsSql(tableName, columnNames, numberOfRowsToCommitAfter),
092                new RowMapper() {
093                    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
094                        Map<String, String> columnValuesMap = new HashMap<String, String>();
095                        for(String columnName: columnNames){
096                            columnValuesMap.put(columnName, rs.getString(columnName));
097                        }
098                        return columnValuesMap;
099                    }
100                });
101    }
102    
103    public void updateColumnValuesInBackupTable(
104            String tableName, Map<String, List<String>> columnNameOldNewValuesMap) {
105        if(columnNameOldNewValuesMap==null || columnNameOldNewValuesMap.size()<1)
106            return;
107        executeSql(getUpdateBackupTableColumnsSql(tableName, columnNameOldNewValuesMap));
108    }
109
110    public String getUpdateBackupTableColumnsSql(
111            String tableName, Map<String, List<String>> columnNameOldNewValuesMap){
112        tableName = tableName + BACKUP_TABLE_EXTENSION;
113        StringBuffer columnsNamesNewValuesBuf = new StringBuffer();
114        List<String> columnOldNewValueList;
115        String columnNewValue;
116        for(String columnName: columnNameOldNewValuesMap.keySet()){
117            columnOldNewValueList = columnNameOldNewValuesMap.get(columnName);
118            columnNewValue = (columnOldNewValueList!=null&&columnOldNewValueList.size()>1)?columnOldNewValueList.get(ENCRYPTED_VALUE_INDEX):"";
119            columnsNamesNewValuesBuf.append(columnName)
120                                .append("='")
121                                .append(columnNewValue)
122                                .append("'")
123                                .append(COMMA_SEPARATOR);
124        }
125        columnsNamesNewValuesBuf.append(BACKUP_TABLE_ENCRYPT_IND)
126                            .append("='Y'");
127
128        StringBuffer columnsNamesOldValuesBuf = new StringBuffer();
129        String columnOldValue; 
130        for(String columnName: columnNameOldNewValuesMap.keySet()){
131            columnOldNewValueList = columnNameOldNewValuesMap.get(columnName);
132            columnOldValue = (columnOldNewValueList!=null&&columnOldNewValueList.size()>0)?columnOldNewValueList.get(UNENCRYPTED_VALUE_INDEX):"";
133            if(StringUtils.isEmpty(columnOldValue) || columnOldValue.equalsIgnoreCase("null")){
134                columnsNamesOldValuesBuf.append(columnName)
135                .append(" IS NULL ")
136                .append(AND_SEPARATOR);
137            } else{
138                columnsNamesOldValuesBuf.append(columnName)
139                                    .append("='")
140                                    .append(columnOldValue)
141                                    .append("'")
142                                    .append(AND_SEPARATOR);
143            }
144        }
145        columnsNamesOldValuesBuf.append(BACKUP_TABLE_ENCRYPT_IND)
146                            .append(" IS NULL ");
147        
148        return new StringBuffer("UPDATE ")
149                                .append(tableName)
150                                .append(" SET ")
151                                .append(columnsNamesNewValuesBuf)
152                                .append(" WHERE ")
153                                .append(columnsNamesOldValuesBuf)
154                                .toString();
155    }
156
157    protected String getSelectBackupTableColumnsSql(String tableName, List<String> columnNames, int numberOfRowsToCommitAfter){
158        tableName = tableName + BACKUP_TABLE_EXTENSION;
159        StringBuffer columnsNamesBuf = new StringBuffer();
160        for(String columnName: columnNames){
161            columnsNamesBuf.append(columnName).append(COMMA_SEPARATOR);
162        }
163        String selectColumns = StringUtils.stripEnd(columnsNamesBuf.toString(), COMMA_SEPARATOR); 
164        return new StringBuffer("SELECT ")
165                    .append(selectColumns)
166                    .append(" FROM ")
167                    .append(tableName)
168                    .append(" WHERE ")
169                    .append(BACKUP_TABLE_ENCRYPT_IND)
170                    .append(" IS NULL AND ROWNUM<=")
171                    .append(numberOfRowsToCommitAfter).toString();
172    }
173    
174    public boolean performEncryption(
175            final String tableName, 
176            final List<Map<String, List<String>>> rowsToEncryptColumnNameOldNewValuesMap) throws Exception {
177
178        Boolean success = (Boolean) getJdbcTemplate().execute(new ConnectionCallback() {
179                public Object doInConnection(Connection conn) throws SQLException, DataAccessException {
180                    try{
181                        conn.setAutoCommit(false);
182                        Statement statement = conn.createStatement();
183                        int counter = 0;
184                        for (Map<String, List<String>> columnNameOldNewValuesMap: rowsToEncryptColumnNameOldNewValuesMap) {
185                            statement.addBatch(getUpdateBackupTableColumnsSql(tableName, columnNameOldNewValuesMap));
186                            counter++;
187                        }
188                        statement.executeBatch();
189                        conn.commit();
190                        LOG.info(new StringBuffer("Encrypted ").append(" attributes of table ").append(tableName));
191                    }
192                    catch (Exception e) {
193                        LOG.error(new StringBuffer("Caught exception, while encrypting ").append(" attributes of table ").append(tableName), e);
194                        conn.rollback();
195                        return false;
196                    }
197                    return true;
198                }
199            });
200        return success;
201    }   
202}