OracleService.java

package com.cadrlife.devsearch.agent.service.oracle;

import java.io.File;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.cadrlife.devsearch.agent.service.RepoService;
import com.cadrlife.devsearch.domain.Project;
import com.google.common.base.Function;
import com.google.common.base.Joiner;
import com.google.common.collect.FluentIterable;

public class OracleService extends RepoService {
	private static final Logger LOG = LoggerFactory.getLogger(OracleService.class);
	private final QueryRunner queryRunner;


	public OracleService(DataSource ds) {
		queryRunner = new QueryRunner(ds);
	}


	static final String STANDARD_WHERE_STATEMENT = " owner not like '%SYS%' and owner not in ('DBSNMP', 'XDB','TRIPWIRE') and owner not like 'FLOWS_%' and owner not like 'APEX_%'";

	public Date getLastUpdateForProject(String owner, boolean quickCheck) throws IOException {
		Date sourceUpdate = queryForDate("select max(last_ddl_time) from dba_objects where owner = ?", owner);
		
		// If we have a mview / view, until we find a way to figure out when it was last compiled, we always need to refresh
		if (queryForInt("select count(distinct view_name) from dba_views where owner=?", owner) > 0) {
			sourceUpdate = new Date();
		} else if (queryForInt("select count(distinct mview_name) from dba_views where owner=?", owner) > 0) {
			sourceUpdate = new Date();
		}
		return sourceUpdate;
	}

	private Date queryForDate(String sql, Object...params) {
		ScalarHandler<Date> dateHandler = new ScalarHandler<>();
		try {
			return queryRunner.query(sql, dateHandler, params);
		}
		catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	private String queryForString(String sql, Object...params) {
		ScalarHandler<String> stringHandler = new ScalarHandler<>();
		try {
			return queryRunner.query(sql, stringHandler, params);
		}
		catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	private List <String> queryForStringList(String sql, Object...params) {
		try {
			ColumnListHandler <String> handler = new ColumnListHandler<String>(1);
			return queryRunner.query(sql, handler, params);
		}
		catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	private int queryForInt(String sql, Object...params) {
		ScalarHandler<Integer> dateHandler = new ScalarHandler<>();
		try {
			return queryRunner.query(sql, dateHandler, params);
		}
		catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
//	private <T> List <T> queryForBeanList(String sql, Class<T> klass, Object...params) {
//		BeanListHandler<T> listHandler = new BeanListHandler<T>(klass);
//		try {
//			return queryRunner.query(sql, listHandler, params);
//		}
//		catch (SQLException e) {
//			throw new RuntimeException(e);
//		}
//	}

	private List<String> listProjects() {
		return queryForStringList("select distinct owner from dba_source where " + STANDARD_WHERE_STATEMENT);
	}

	public boolean supportsQuickUpdate() {
		return false;
	}

	public Map<String, Date> quickScanForLastUpdatesForAllProjects() {
		return Collections.emptyMap();
	}

	@Override
	public List <Project> findAllProjects() {
		return FluentIterable.from(listProjects()).transform(new Function <String,Project>() {
			@Override
			public Project apply(String input) {
				return new Project().setName(input).setRepo(getRepoName());
			}
		}).toList();
	}

	@Override
	public void updateProject(final File file, Project project) {
		
		try {
			final String ownerSchema = project.getName();
			LOG.info("dumping schema {}", ownerSchema);
			ResultSetHandler <Void> sourceHandler = new ResultSetHandler <Void>() {

				@Override
				public Void handle(ResultSet rs) throws SQLException {
					while (rs.next()) {
						String sqlName = rs.getString(1);
						String sqlType = rs.getString(2);
						handleRow(sqlName, sqlType);
					}
					return null;
				}

				private void handleRow(String sqlName, String sqlType) {
					
					List <String> sourceLines = queryForStringList("select text from dba_source where owner = ? and name = ? and type = ? order by line", 
							ownerSchema,
							sqlName,
							sqlType);
					File localFile = file.toPath().resolve(sqlType).resolve(sqlName +".sql").toFile();
					try {
						saveStringToFile(Joiner.on("").join(sourceLines), localFile);
					}
					catch (IOException e) {
						String message = String.format("Couldn't write %s.%s source to %s", ownerSchema, sqlName, localFile.getAbsolutePath());
						throw new RuntimeException(message, e);
					}
				}

				
			};
			ResultSetHandler <Void> viewHandler = new ResultSetHandler <Void>() {

				@Override
				public Void handle(ResultSet rs) throws SQLException {
					while (rs.next()) {
						String sqlName = rs.getString(1);
						String sqlType = rs.getString(2);
						handleRow(sqlName, sqlType);
					}
					return null;
				}

				private void handleRow(String sqlName, String sqlType) {
					
					String source = queryForString(selectSourceLines(sqlType), 
							ownerSchema,
							sqlName);
					File localFile = file.toPath().resolve(sqlType).resolve(sqlName +".sql").toFile();
					try {
						saveStringToFile(source, localFile);
					}
					catch (IOException e) {
						String message = String.format("Couldn't write %s.%s source to %s", ownerSchema, sqlName, localFile.getAbsolutePath());
						throw new RuntimeException(message, e);
					}
				}

				private String selectSourceLines(String sqlType) {
					if ("MVIEW".equalsIgnoreCase(sqlType)) {
						return "select query from dba_mviews where owner = ? and mview_name = ?";
					}
					return "select text from dba_views where owner = ? and view_name = ?";
				}
			};
			queryRunner.query("select view_name, 'VIEW' as type from dba_views where owner = ?", viewHandler, ownerSchema);
			queryRunner.query("select mview_name, 'MVIEW' as type from dba_mviews where owner = ?", viewHandler, ownerSchema);
			queryRunner.query("select distinct name, type from dba_source where owner = ?", sourceHandler, ownerSchema);
		}
		catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	public String getSourceType() {
		return "oracle";
	}

}