package org.jivesoftware.openfire.plugin.rules;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.concurrent.CopyOnWriteArrayList;
import org.jivesoftware.database.DbConnectionManager;
import org.jivesoftware.openfire.cluster.ClusterManager;
import org.jivesoftware.openfire.plugin.cluster.RulesUpdatedEvent;
import org.jivesoftware.util.cache.CacheFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class DbRuleManager {
private static final Logger Log = LoggerFactory.getLogger(DbRuleManager.class);
//SQL Statements
private static final String UPDATE_RULE =
"UPDATE ofPfRules" +
" SET type=?,tojid=?,fromjid=?,rulef=?,disabled=?,log=?,description=?,ruleorder=?,sourcetype=?,desttype=? WHERE id=?";
private static final String DELETE_RULE =
"DELETE FROM ofPfRules WHERE id=?";
private static final String INSERT_RULE =
"INSERT INTO ofPfRules(ruleorder,type,tojid,fromjid,rulef,disabled,log,description,sourcetype,desttype) VALUES(?,?,?,?,?,?,?,?,?,?)";
private static final String COUNT =
"select count(*) from ofPfRules";
private static final String GET_RULES =
"SELECT rulef,id,type,tojid,fromjid,disabled,log,description,ruleorder,sourcetype,desttype from ofPfRules order by ruleorder";
/*private static final String RULE_OPTIONS =
"SELECT optionKey, optionValue, optionRequired, classType from ofPfRulesOptions where ruleId = ?";*/
/*private static final String GET_RULE_BY_ID =
"SELECT rule,id,type,tojid,fromjid,disabled,log,description,ruleorder from ofPfRules where id=?";*/
private static final String GET_RULE_BY_ORDER_ID =
"SELECT ruleorder,rulef,id,type,tojid,fromjid,disabled,log,description,sourcetype,desttype from ofPfRules where ruleorder=? order by ruleorder DESC";
private static final String GET_LAST_ORDERID =
"SELECT ruleorder from ofPfRules order by ruleorder DESC";
private static final DbRuleManager DB_RULE_MANAGER = new DbRuleManager();
private List<Rule> rules = new CopyOnWriteArrayList<Rule>();
private DbRuleManager() {
rules = getRules();
}
public static DbRuleManager getInstance() {
return DB_RULE_MANAGER;
}
public List<Rule> getRules() {
if (rules.isEmpty()) {
synchronized (rules) {
if (rules.isEmpty()) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(GET_RULES);
rs = pstmt.executeQuery();
while (rs.next()) {
Rule rule = null;
String ruleType = rs.getString(1);
if (ruleType.equals(Reject.class.getName()))
rule = new Reject();
else if (ruleType.equals(Pass.class.getName()))
rule = new Pass();
else if (ruleType.equals(Drop.class.getName()))
rule = new Drop();
rule.setRuleId(rs.getString(2));
rule.setPacketType(Rule.PacketType.valueOf(rs.getString(3)));
rule.setDestination(rs.getString(4));
rule.setSource(rs.getString(5));
rule.isDisabled(rs.getBoolean(6));
rule.doLog(rs.getBoolean(7));
rule.setDescription(rs.getString(8));
rule.setOrder(rs.getInt(9));
rule.setSourceType(Rule.SourceDestType.valueOf(rs.getString(10)));
rule.setDestType(Rule.SourceDestType.valueOf(rs.getString(11)));
rules.add(rule);
}
} catch (SQLException sqle) {
Log.error(sqle.getMessage(), sqle);
}
finally {
DbConnectionManager.closeConnection(pstmt, con);
}
}
}
}
return rules;
}
// private void getSavedOptions() {
// if (rules != null) {
// Connection con = null;
// PreparedStatement pstmt = null;
// ResultSet rs = null;
// try {
// for (Rule rule : rules) {
// Log.info("getting options for rule " + rule.getRuleId());
// con = DbConnectionManager.getConnection();
// pstmt = con.prepareStatement(RULE_OPTIONS);
// pstmt.setInt(1, Integer.parseInt(rule.getRuleId()));
//
// rs = pstmt.executeQuery();
// List<RuleOption> savedOptions = new ArrayList<RuleOption>();
// while (rs.next()) {
// RuleOption option = new RuleOption();
// option.setName(rs.getString(1));
// Log.info("Name " + option.getName());
// option.setValue(rs.getString(2));
// option.setRequired(rs.getBoolean(3));
// option.setType(rs.getString(4));
// savedOptions.add(option);
// }
// //rule.setSavedOptions(savedOptions);
// pstmt.close();
// rs.close();
//
//
// }
//
//
// } catch (SQLException sqle) {
// Log.error(sqle.getMessage(), sqle);
// }
// finally {
// DbConnectionManager.closeConnection(pstmt, con);
// }
//
// }
// }
public int getLastOrderId() {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
int count = -1;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(GET_LAST_ORDERID);
rs = pstmt.executeQuery();
if (rs.next()) {
count = rs.getInt(1);
}
else {
count = 0;
}
} catch (SQLException sqle) {
Log.error(sqle.getMessage(), sqle);
// Result set probably empty
return 0;
}
finally {
DbConnectionManager.closeConnection(pstmt, con);
}
return count;
}
public int getCount() {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
int count = -1;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(COUNT);
rs = pstmt.executeQuery();
rs.next();
count = rs.getInt(1);
} catch (SQLException sqle) {
Log.error(sqle.getMessage(), sqle);
}
finally {
DbConnectionManager.closeConnection(pstmt, con);
}
return count;
}
public boolean addRule(Rule rule) {
return addRule(rule, null);
}
public boolean addRule(Rule rule, Integer order) {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(INSERT_RULE);
if (order == null) {
order = getLastOrderId() + 1;
pstmt.setInt(1, order);
} else {
pstmt.setInt(1, order);
}
rule.setOrder(order);
pstmt.setString(2, rule.getPackeType().toString());
pstmt.setString(3, rule.getDestination());
pstmt.setString(4, rule.getSource());
pstmt.setString(5, rule.getClass().getName());
if (rule.isDisabled()) {
pstmt.setBoolean(6, Boolean.TRUE);
}
else {
pstmt.setBoolean(6, Boolean.FALSE);
}
if (rule.doLog()) {
pstmt.setBoolean(7, Boolean.TRUE);
}
else {
pstmt.setBoolean(7, Boolean.FALSE);
}
pstmt.setString(8, rule.getDescription());
pstmt.setString(9, rule.getSourceType().toString());
pstmt.setString(10, rule.getDestType().toString());
pstmt.execute();
rules.clear();
} catch (SQLException sqle) {
Log.error(sqle.getMessage(), sqle);
return false;
}
finally {
DbConnectionManager.closeConnection(pstmt, con);
updateCluster();
}
return true;
}
public boolean deleteRule(int ruleId) {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(DELETE_RULE);
pstmt.setInt(1,new Integer(ruleId));
pstmt.execute();
rules.remove(getRuleById(ruleId));
} catch (SQLException sqle) {
Log.error(sqle.getMessage(), sqle);
return false;
}
finally {
DbConnectionManager.closeConnection(pstmt, con);
updateCluster();
}
return true;
}
public void moveRuleOrder(int ruleId, int order) {
Rule rule = getRuleById(ruleId);
//See if there is a gap that we can just update the rule with
Rule orderIWant = getRuleByOrderId(order);
if (orderIWant == null) {
updateRule(rule, order);
}
//No gap. Move all rules >= to the order.
else {
List<Rule> rules = getRules();
for (int i = rules.size(); i > 0; i--) {
Rule moveRule = rules.get(i);
if (new Integer(moveRule.getOrder()) >= order) {
updateRule(moveRule, order + 1);
} else break;
}
updateRule(rule, order);
}
}
public Rule getRuleByOrderId(int order) {
Rule rule = null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(GET_RULE_BY_ORDER_ID);
pstmt.setInt(1, order);
rs = pstmt.executeQuery();
while (rs.next()) {
rule.setOrder(rs.getInt(1));
String ruleType = rs.getString(2);
if (ruleType.equals(Reject.class.getName())) {
rule = new Reject();
}
else if (ruleType.equals(Pass.class.getName())) {
rule = new Pass();
}
else if (ruleType.equals(Drop.class.getName())) {
rule = new Drop();
}
rule.setRuleId(rs.getString(3));
rule.setPacketType(Rule.PacketType.valueOf(rs.getString(4)));
rule.setDestination(rs.getString(5));
rule.setSource(rs.getString(6));
rule.isDisabled(rs.getBoolean(7));
rule.doLog(rs.getBoolean(8));
rule.setDescription(rs.getString(9));
rule.setSourceType(Rule.SourceDestType.valueOf(rs.getString(10)));
rule.setDestType(Rule.SourceDestType.valueOf(rs.getString(11)));
}
} catch (SQLException sqle) {
Log.error(sqle.getMessage(), sqle);
}
finally {
DbConnectionManager.closeConnection(pstmt, con);
}
return rule;
}
public Rule getRuleById(int id) {
Rule rule = null;
String ruleId = Integer.toString(id);
for (Rule cRule : rules) {
if (cRule.getRuleId().equals(ruleId)) {
rule = cRule;
break;
}
}
return rule;
}
public boolean updateRule(Rule rule) {
return updateRule(rule, rule.getOrder());
}
public boolean updateRule(Rule rule, Integer order) {
//SET type=?,tojid=?,fromjid=?,rulef=?,disabled=?,log=?,description=?,ruleorder=?,sourcetype=?,desttype=? WHERE id=?";
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(UPDATE_RULE);
pstmt.setString(1, rule.getPackeType().toString());
pstmt.setString(2, rule.getDestination());
pstmt.setString(3, rule.getSource());
pstmt.setString(4, rule.getClass().getName());
if (rule.isDisabled())
pstmt.setBoolean(5, Boolean.TRUE);
else
pstmt.setBoolean(5, Boolean.FALSE);
if (rule.doLog())
pstmt.setBoolean(6, Boolean.TRUE);
else
pstmt.setBoolean(6, Boolean.FALSE);
pstmt.setString(7, rule.getDescription());
pstmt.setInt(8, order);
pstmt.setString(9, rule.getSourceType().toString());
pstmt.setString(10, rule.getDestType().toString());
pstmt.setInt(11, new Integer(rule.getRuleId()));
pstmt.executeUpdate();
rules.clear();
} catch (SQLException sqle) {
Log.error(sqle.getMessage(), sqle);
return false;
}
finally {
DbConnectionManager.closeConnection(pstmt, con);
updateCluster();
}
return true;
}
/*
Moving a rule one up or down so we can just swap
*/
public boolean moveOne(Rule src, Rule dest) {
int srcOrder = src.getOrder();
int destOrder = dest.getOrder();
dest.setOrder(srcOrder);
src.setOrder(destOrder);
updateRule(src);
updateRule(dest);
return true;
}
private void updateCluster() {
boolean isClustered = ClusterManager.isClusteringEnabled();
if (isClustered) {
RulesUpdatedEvent request = new RulesUpdatedEvent();
CacheFactory.doClusterTask(request);
}
}
public void clear() {
if (!rules.isEmpty()) {
rules.clear();
}
}
}