/**
* Copyright 2014 Yahoo! Inc. Licensed under the Apache License, Version 2.0
* (the "License"); you may not use this file except in compliance with the
* License. You may obtain a copy of the License at
* http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law
* or agreed to in writing, software distributed under the License is
* distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the License for the specific language
* governing permissions and limitations under the License. See accompanying
* LICENSE file.
*/
package com.yahoo.sql4d.sql4ddriver.sql;
import com.google.common.collect.ImmutableMap;
import com.yahoo.sql4d.query.nodes.Interval;
import java.sql.Connection;
import org.apache.commons.pool.BasePoolableObjectFactory;
import static java.lang.String.*;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.DataSource;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.apache.commons.pool.impl.GenericObjectPoolFactory;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import scala.Tuple2;
/**
* Mysql Actions. Right now handle druid specific queries, split this into generic
* mysql accessor and druid specific changes when this grows in size.
*
* @author srikalyan
*/
public class MysqlAccessor extends BasePoolableObjectFactory<Tuple2<DataSource, Connection>> {
private final String connectorUrl = "jdbc:mysql://%s:%d/%s?autoReconnectForPools=true";
private String host = "localhost";
private int port = 3306;
private String id = "druid";//druid by default.
private String password = "diurd";//diurd by default.
private String db = "druid";//druid by default
private ObjectPool<Tuple2<DataSource, Connection>> pool = null ;
static {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (ClassNotFoundException | InstantiationException | IllegalAccessException ex) {
Logger.getLogger(MysqlAccessor.class.getName()).log(Level.SEVERE, null, ex);
}
}
public MysqlAccessor() {
this("localhost", 3306, "druid", "diurd", "druid");
}
public MysqlAccessor(String host, int port) {
this(host, port, "druid", "diurd", "druid");
}
public MysqlAccessor(String host, int port, String id, String passwd) {
this(host, port, id, passwd, "druid");
}
public MysqlAccessor(String host, int port, String id, String passwd, String db) {
this.host = host;
this.port = port;
this.id = id;
this.password = passwd;
this.db = db;
init();
}
private void init() {
GenericObjectPool.Config config = new GenericObjectPool.Config();
config.maxActive = 2;//TODO: Make this configurable.
config.testOnBorrow = true;
config.testWhileIdle = true;
config.timeBetweenEvictionRunsMillis = 10000;
config.minEvictableIdleTimeMillis = 60000;
GenericObjectPoolFactory genericObjectPoolFactory = new GenericObjectPoolFactory(this, config);
pool = genericObjectPoolFactory.createPool();
}
@Override
public Tuple2<DataSource, Connection> makeObject() throws Exception {
DataSource ds = new DriverManagerDataSource(format(connectorUrl, host, port, db), id, password);
return new Tuple2<>(ds, ds.getConnection());
}
@Override
public void destroyObject(Tuple2<DataSource, Connection> connTuple) throws Exception {
connTuple._2().close();
}
@Override
public boolean validateObject(Tuple2<DataSource, Connection> conn) {
try {
return conn._2().isValid(0);
} catch (SQLException ex) {
Logger.getLogger(MysqlAccessor.class.getName()).log(Level.SEVERE, null, ex);
try {//Could be due to stale connection. Invalidate the object.
pool.invalidateObject(conn);
} catch (Exception ex1) {//TODO: Something serious with DB.
Logger.getLogger(MysqlAccessor.class.getName()).log(Level.SEVERE, null, ex1);
}
}
return false;
}
public Tuple2<DataSource, Connection> getConnection() {
try {
return pool.borrowObject();
} catch (Exception ex) {
Logger.getLogger(MysqlAccessor.class.getName()).log(Level.SEVERE, null, ex);
}
return null;
}
public void returnConnection(Tuple2<DataSource, Connection> con) {
try {
if (con != null) {
pool.returnObject(con);
}
} catch (Exception ex) {
Logger.getLogger(MysqlAccessor.class.getName()).log(Level.SEVERE, null, ex);
}
}
public void shutdown() {
try {
pool.clear();
pool.close();
} catch (Exception ex) {
Logger.getLogger(MysqlAccessor.class.getName()).log(Level.SEVERE, null, ex);
}
}
public List<Map<String, Object>> query(Map<String, String> params, String query) {
List<Map<String, Object>> result = null;
Tuple2<DataSource, Connection> conn = null;
try {
conn = getConnection();
NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(conn._1());
result = jdbcTemplate.queryForList(query, params);
} catch (Exception ex) {
Logger.getLogger(MysqlAccessor.class.getName()).log(Level.SEVERE, null, ex);
} finally {
returnConnection(conn);
}
return result;
}
/**
* Suitable for CRUD operations where no result set is expected.
* @param params
* @param query
* @return
*/
public boolean execute(Map<String, String> params, String query) {
final AtomicBoolean result = new AtomicBoolean(false);
Tuple2<DataSource, Connection> conn = null;
try {
conn = getConnection();
NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(conn._1());
jdbcTemplate.execute(query, params, new PreparedStatementCallback<Void>() {
@Override
public Void doInPreparedStatement(PreparedStatement ps) {
try {
result.set(ps.execute());
} catch(SQLException e) {
result.set(false);
}
return null;
}
});
} catch (Exception ex) {
Logger.getLogger(MysqlAccessor.class.getName()).log(Level.SEVERE, null, ex);
result.set(false);
} finally {
returnConnection(conn);
}
return result.get();
}
public List<Map<String, Object>> segmentsInRange(String table, Interval interval) {
return query(ImmutableMap.of("dataSource", table,
"iStart", interval.startTime,
"iEnd", interval.endTime), DAODictionary.SEGMENTS_IN_RANGE);
}
public List<Map<String, Object>> usedSegmentsInRange(String table, Interval interval) {
return query(ImmutableMap.of("dataSource", table,
"iStart", interval.startTime,
"iEnd", interval.endTime,
"used", "1"), DAODictionary.USED_SEGMENTS_IN_RANGE);
}
public boolean disableSegmentsInRange(String table, Interval interval) {
return execute(ImmutableMap.of("dataSource", table,
"iStart", interval.startTime,
"iEnd", interval.endTime), DAODictionary.DISABLE_SEGMENTS_IN_RANGE);
}
public boolean disableAllSegments(String table) {
return execute(ImmutableMap.of("dataSource", table), DAODictionary.DISABLE_ALL_SEGMENTS);
}
public static void main(String[] args) {
MysqlAccessor accessor = new MysqlAccessor();
System.out.println(accessor.usedSegmentsInRange("abf2", new Interval("2014-10-31/2014-11-01")));
accessor.shutdown();
}
}