/** Return the user's secure_hash_key. If one wasn't found create one on the fly. */
private SecureHashKeyResult getSecureHashKey(UserAccount user, Connection c) throws SQLException {
PreparedStatement select = null;
// Create lazily.
PreparedStatement update = null;
int userId = user.getUserID();
boolean justCreated = false;
byte[] key = null;
try {
// TODO: consider having UserManager returning secure_hash_key.
// TODO: We have similar logic in several places for creating secure_hash_key just-in-time.
// D.R.Y. this out. Sorry I couldn't resist using this cliche :)
select = c.prepareStatement("SELECT secure_hash_key FROM dat_user_account WHERE object_id=?");
select.setInt(1, userId);
ResultSet rs = select.executeQuery();
if (!rs.next()) {
LogMessageGen lmg = new LogMessageGen();
lmg.setSubject("dat_user_account row not found");
lmg.param(LoggingConsts.USER_ID, userId);
// possible that the user simply disappeared by the time we got here.
m_logCategory.warn(lmg.toString());
} else {
key = rs.getBytes(1);
if (key == null || key.length == 0) {
// hash key not found; create one on the fly.
update =
c.prepareStatement("UPDATE dat_user_account SET secure_hash_key=? WHERE object_id=?");
key = createNewRandomKey();
update.setBytes(1, key);
update.setInt(2, userId);
int ct = update.executeUpdate();
if (ct != 1) {
LogMessageGen lmg = new LogMessageGen();
lmg.setSubject("Unable to update dat_user_account.secure_hash_key");
lmg.param(LoggingConsts.USER_ID, userId);
m_logCategory.error(lmg.toString());
} else {
justCreated = true;
}
} // needed to set key.
} // user found
} finally {
DbUtils.safeClose(select);
DbUtils.safeClose(update);
}
return new SecureHashKeyResult(key, justCreated);
}
private static Map saveSites(ExchSite3[] sites, int siteType, int sourceID, Connection c)
throws SQLException {
PreparedStatement insert = null;
PreparedStatement delete = null;
try {
insert =
c.prepareStatement(
"insert into dat_customer_sites (site_id, site_type, source_id, internal_name, display_name) "
+ "values (?, ?, ?, ?, ?)");
delete = c.prepareStatement("delete from dat_customer_sites where site_id = ?");
Map siteIDs = new HashMap(sites.length * 2 + 1);
for (int i = 0; i < sites.length; i++) {
ExchSite3 site = sites[i];
int siteID = queryLookupSiteId(sourceID, site.getInternalName(), siteType, c);
if (siteID == 0) {
// if we couldn't find an existing siteID, grab the next one from the sequence
siteID = getNextFromSequence("seq_site_id", c);
} else {
// if there is an existing siteID, delete it so we can insert the changes
delete.setInt(1, siteID);
int deleted = delete.executeUpdate();
if (deleted != 1) {
throw new SQLException("Delete for siteID " + siteID + " returned " + deleted);
}
}
siteIDs.put(site.getInternalName(), siteID);
insert.setInt(1, siteID);
insert.setInt(2, siteType);
insert.setInt(3, sourceID);
insert.setString(
4, DirectoryUtils.truncateString(site.getInternalName(), DB_SITE_INTERNALNAME_LENGTH));
insert.setString(
5, DirectoryUtils.truncateString(site.getDisplayName(), DB_SITE_DISPLAYNAME_LENGTH));
insert.executeUpdate();
}
return siteIDs;
} finally {
if (delete != null) {
delete.close();
}
if (insert != null) {
insert.close();
}
}
}
/** Returns event_id of newly created logging entry. */
private Integer logEvent(IClient client, String eventText, InputStream logFileSteam, Connection c)
throws SQLException {
PreparedStatement stmt = null;
int id = 0;
try {
long longId = getSequenceId("seq_client_log_event_ids", c);
if (longId > Integer.MAX_VALUE) {
// dat_client_log_events.event_id is a 32 bit integer numeric type.
// this is a bad problem; ensure it's logged; don't depend on whoever's catching
// the exception.
m_logger.log("seq_client_log_event_ids overflowed").error();
throw new IllegalStateException("seq_client_log_event_ids overflowed.");
}
id = (int) longId;
stmt =
c.prepareStatement(
"insert into dat_client_log_events "
+ "(event_id, customer_id, user_id, event_time, description, has_log_file) "
+ "values "
+ "(?, ?, ?, current_timestamp, ?, ?)");
stmt.setInt(1, id);
stmt.setInt(2, client.getCustomerId());
stmt.setInt(3, client.getUserId());
stmt.setString(4, eventText);
stmt.setInt(5, logFileSteam == null ? 0 : 1);
if (stmt.executeUpdate() != 1) {
throw new SQLException("Can't insert client event for " + client);
}
} finally {
DbUtils.safeClose(stmt);
}
return new Integer(id);
}
/**
* Returns the siteId for the site with the given source, name, and type. If no such site is
* found, this method returns 0;
*/
private static int queryLookupSiteId(
int sourceID, String siteInternalName, int siteType, Connection c) throws SQLException {
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps =
c.prepareStatement(
"select site_id from dat_customer_sites "
+ "where source_id = ? and internal_name = ? and site_type = ?");
int siteID = 0;
ps.setInt(1, sourceID);
ps.setString(2, siteInternalName);
ps.setInt(3, siteType);
rs = ps.executeQuery();
if (rs.next()) {
siteID = rs.getInt(1);
}
return siteID;
} finally {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
}
}
private static int queryLookupServerId(int sourceID, String serverInternalName, Connection c)
throws SQLException {
PreparedStatement idSelect = null;
ResultSet rs = null;
try {
int serverId = 0;
idSelect =
c.prepareStatement(
"select server_id from dat_customer_servers "
+ "where source_id = ? and internal_name = ?");
idSelect.setInt(1, sourceID);
idSelect.setString(2, serverInternalName);
rs = idSelect.executeQuery();
if (rs.next()) {
serverId = rs.getInt(1);
}
return serverId;
} finally {
if (rs != null) {
rs.close();
}
if (idSelect != null) {
idSelect.close();
}
}
}
private static int getSourceID(Customer cust, String sourceName, Connection c)
throws SQLException {
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps =
c.prepareStatement(
"select source_id from dat_dirsync_sources where customer_id = ? and source = ?");
ps.setInt(1, cust.getCustID());
ps.setString(2, sourceName);
rs = ps.executeQuery();
if (rs.next()) {
return rs.getInt(1);
}
rs.close();
rs = null;
ps.close();
ps = null;
// doesn't already exist, so create it with nulls for scheduling stuff
ps =
c.prepareStatement(
"insert into dat_dirsync_sources (source_id, customer_id, source) values (nextval('seq_source_id'), ?, ?)");
ps.setInt(1, cust.getCustID());
ps.setString(2, sourceName);
ps.executeUpdate();
ps.close();
ps = null;
// and grab the sourceID
ps = c.prepareStatement("select currval('seq_source_id')");
rs = ps.executeQuery();
rs.next();
return rs.getInt(1);
} finally {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
}
}
public void purgeLogEvents(final List<SearchConstraint> constraints, final int daysToKeep) {
blockHereIfBadNfs();
StringBuffer sql =
new StringBuffer(
"select u.mail_directory || '/logs/' || e.event_id || '.log' "
+ "from dat_client_log_events e, dat_user_account u "
+ "where event_time < current_timestamp - (? || ' days')::interval "
+ "and e.has_log_file != 0 "
+ "and e.user_id = u.object_id");
appendWhereConstraints(sql, constraints, s_propToColumnMap);
m_logCategory.info("Purge event logs query is\n" + sql);
StringBuffer sql2 =
new StringBuffer(
"delete from dat_client_log_events "
+ "where event_time < current_timestamp - (? || ' days')::interval");
if (!constraints.isEmpty()) {
sql2.append(" and event_id in (select e.event_id from dat_client_log_events e ");
appendWhere(sql2, constraints, s_propToColumnMap);
sql2.append(")");
}
m_logCategory.info("Purge event logs query is\n" + sql2);
try {
Connection c = m_txManager.getConnection();
PreparedStatement stmt = null;
ResultSet rs = null;
boolean needsRollback = true;
try {
stmt = c.prepareStatement(sql.toString());
stmt.setInt(1, daysToKeep);
rs = stmt.executeQuery();
while (rs.next()) {
File logFile = new File(getNfsRoot(), rs.getString(1));
// nfs usage, but DB transaction takes no locks.
if (logFile.exists()) {
boolean ok = logFile.delete();
if (!ok) {
m_logger
.log("Unable to delete")
.param(LoggingConsts.FILENAME, logFile.getAbsolutePath())
.warn();
}
} // file exists.
} // each row
// Below, no nfs usage occurs. We can use the same connection.
// the 'sql delete' may use DB locks.
stmt = c.prepareStatement(sql2.toString());
stmt.setInt(1, daysToKeep);
stmt.executeUpdate();
c.commit();
needsRollback = false;
} finally {
DbUtils.safeClose(rs);
DbUtils.safeClose(stmt);
if (needsRollback) {
c.rollback();
}
m_txManager.returnConnection(c);
}
} catch (Exception ex) {
m_logger.log("Error in purgeLogEvents").warn(ex);
}
}
public void setKeyCreatingIfNeeded(
List<? extends IClientInfo> clients,
List<IClientInfo> clientsNeedingSignalUpdate,
Connection c)
throws SQLException {
PreparedStatement select = null;
// Create lazily.
PreparedStatement update = null;
try {
select = c.prepareStatement("SELECT secure_hash_key FROM dat_user_account WHERE object_id=?");
for (IClientInfo client : clients) {
int userId = client.getUserId();
// ensure dat_user_account.secure_hash_key is filled.
select.setInt(1, userId);
ResultSet rs = select.executeQuery();
if (!rs.next()) {
LogMessageGen lmg = new LogMessageGen();
lmg.setSubject("dat_user_account row not found");
lmg.param(LoggingConsts.USER_ID, userId);
// possible that the user simply disappeared by the time we got here.
m_logCategory.warn(lmg.toString());
continue;
}
boolean firstTimeCreate = false;
byte[] key = rs.getBytes(1);
if (key == null || key.length == 0) {
if (update == null) {
update =
c.prepareStatement(
"UPDATE dat_user_account SET secure_hash_key=? WHERE object_id=?");
}
key = createNewRandomKey();
update.setBytes(1, key);
update.setInt(2, userId);
int ct = update.executeUpdate();
if (ct != 1) {
LogMessageGen lmg = new LogMessageGen();
lmg.setSubject("Unable to update dat_user_account.secure_hash_key");
lmg.param(LoggingConsts.USER_ID, userId);
m_logCategory.error(lmg.toString());
continue;
} else {
firstTimeCreate = true;
}
} // no existing key.
client.getHashSupport().setHashKey(key);
if (firstTimeCreate) {
if (clientsNeedingSignalUpdate != null) {
// EMSDEV-7854. Don't actually do
// updateSignalFiles(client) right here; we want to avoid nfs usage in the middle of a
// db
// transaction.
clientsNeedingSignalUpdate.add(client);
}
}
} // each client.
} finally {
DbUtils.safeClose(select);
DbUtils.safeClose(update);
}
}
/** Writes user state information to the shared filesystem */
private void updateStateCache(
Customer customer, int transitionId, CustomerState newState, Connection c)
throws SQLException {
PreparedStatement pst = null;
ResultSet rs = null;
try {
pst =
c.prepareStatement(
"SELECT u.secure_hash_key, u.object_id, u.user_state, u.is_deleted, u.last_activation_id "
+ "FROM dat_user_account u, dat_transition_users tr "
+ "WHERE u.object_id = tr.user_id AND u.customer_id=? "
+ "AND tr.transition_id=?;");
// The state files are used by Outlook, BB, and maybe future clients.
pst.setInt(1, customer.getCustID());
pst.setInt(2, transitionId);
rs = pst.executeQuery();
while (rs.next()) {
int i = 0;
byte[] key = rs.getBytes(++i);
int userId = rs.getInt(++i);
CustomerState state = CustomerState.fromInt(rs.getInt(++i));
int isDeletedInt = rs.getInt(++i);
int lastActivationId = rs.getInt(++i);
// If the user is marked deleted but has a key, we'll try cleaning
// up state files.
boolean isDeleted = isDeletedInt != IUserManager.USER_NOT_DELETED;
if (key == null || key.length == 0) {
LogMessageGen lmg = new LogMessageGen();
lmg.setSubject("dat_user_account.secure_hash_key not set");
lmg.param(LoggingConsts.USER_ID, userId);
m_logCategory.info(lmg.toString());
// Without a key, we can't determine the signal filenames
// so no cleanup is possible.
continue;
}
ClientHashSupport hash = null;
hash = new ClientHashSupport();
hash.setCustomerId(customer.getCustID());
hash.setUserId(userId);
hash.setHashKey(key);
hash.setLastActivationId(lastActivationId);
if (m_logCategory.isInfoEnabled()) {
LogMessageGen lmg = new LogMessageGen();
lmg.setSubject("Updating signal files");
lmg.param(LoggingConsts.USER_ID, userId);
m_logCategory.info(lmg.toString());
}
// wrt EMSDEV-7854 nfs calls and database transactions.
// Above is only doing a select; no locks are taken and this
// method is private.
updateSignalFiles(hash, state, isDeleted);
} // each row.
} finally {
DbUtils.safeClose(rs);
DbUtils.safeClose(pst);
}
}
private static Map saveServers(
TopologyData3 td, int sourceID, Map adminGroupIDs, Map routingGroupIDs, Connection c)
throws SQLException {
PreparedStatement insert = null;
PreparedStatement update = null;
try {
insert =
c.prepareStatement(
"insert into dat_customer_servers (server_id, source_id, admin_group_id, routing_group_id, internal_name, display_name, sink_capable, sink_enabled, server_role, server_version, cloud_service_id) "
+ "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
update =
c.prepareStatement(
"update dat_customer_servers "
+ "set source_id = ?, admin_group_id = ?, routing_group_id = ?, internal_name = ?, "
+ " display_name = ?, sink_capable = ?, server_role = ?, server_version = ?, is_deleted = false, purge_time = NULL, cloud_service_id = ? "
+ "where server_id = ?");
Map serverIDs = new HashMap(adminGroupIDs.size() * 8 + 1);
ExchSite3[] adminGroups = td.getAdminGroups();
ExchSite3[] routingGroups = td.getRoutingGroups();
for (int i = 0; i < adminGroups.length; i++) {
ExchSite3 adminGroup = adminGroups[i];
int adminGroupID = ((Number) adminGroupIDs.get(adminGroup.getInternalName())).intValue();
ExchServer3[] servers = adminGroup.getServers();
for (int j = 0; j < servers.length; j++) {
ExchServer3 server = servers[j];
int serverID = queryLookupServerId(sourceID, server.getInternalName(), c);
int routingGroupID =
findRoutingGroupID(server.getInternalName(), routingGroups, routingGroupIDs);
// for non-sinkable servers, attempt to parse cloud service from version field
CloudService cloudService = CloudService.NONE;
if (!server.isSinkCapable()) {
Matcher matcher = SERVER_VERSION_PATTERN.matcher(server.getServerVersion());
if (matcher.matches()) {
cloudService = CloudService.valueOfCaseInsensitive(matcher.group(1), cloudService);
}
}
if (serverID == 0) {
// if we couldn't find an existing serverID, grab the next one from the sequence
serverID = getNextFromSequence("seq_server_id", c);
serverIDs.put(server.getInternalName(), serverID);
insert.setInt(1, serverID);
insert.setInt(2, sourceID);
insert.setInt(3, adminGroupID);
insert.setInt(4, routingGroupID);
insert.setString(
5,
DirectoryUtils.truncateString(
server.getInternalName(), DB_SERVER_INTERNALNAME_LENGTH));
insert.setString(
6,
DirectoryUtils.truncateString(
server.getDisplayName(), DB_SERVER_DISPLAYNAME_LENGTH));
insert.setInt(7, server.isSinkCapable() ? 1 : 0);
insert.setInt(
8,
cloudService.getId() <= CloudService.ONPREMISES.getId()
? 1
: 0); // by default, all non-cloud servers are sink-enabled (so if they become
// e2k servers, they're enabled)
insert.setInt(9, server.getServerRole());
insert.setString(
10,
DirectoryUtils.truncateString(server.getServerVersion(), DB_SERVER_VERSION_LENGTH));
insert.setInt(11, cloudService.getId());
insert.executeUpdate();
} else {
// if there is an existing serverID, update it (preserve value of sink_enabled)
serverIDs.put(server.getInternalName(), serverID);
update.setInt(1, sourceID);
update.setInt(2, adminGroupID);
update.setInt(3, routingGroupID);
update.setString(
4,
DirectoryUtils.truncateString(
server.getInternalName(), DB_SERVER_INTERNALNAME_LENGTH));
update.setString(
5,
DirectoryUtils.truncateString(
server.getDisplayName(), DB_SERVER_DISPLAYNAME_LENGTH));
update.setInt(6, server.isSinkCapable() ? 1 : 0);
update.setInt(7, server.getServerRole());
update.setString(
8,
DirectoryUtils.truncateString(server.getServerVersion(), DB_SERVER_VERSION_LENGTH));
update.setInt(9, cloudService.getId());
update.setInt(10, serverID);
update.executeUpdate();
}
}
}
return serverIDs;
} finally {
if (update != null) {
update.close();
}
if (insert != null) {
insert.close();
}
}
}
private static Map saveStores(
TopologyData3 td, int sourceID, ExchSite3[] sites, Map serverIDs, Connection c)
throws SQLException {
PreparedStatement insert = null;
PreparedStatement delete = null;
try {
insert =
c.prepareStatement(
"insert into dat_customer_stores (store_id, source_id, server_id, internal_name, display_name) "
+ "values (?, ?, ?, ?, ?)");
delete = c.prepareStatement("delete from dat_customer_stores where store_id = ?");
Map storeIDs = new HashMap(101);
for (int i = 0; i < sites.length; i++) {
ExchSite3 site = sites[i];
ExchServer3[] servers = site.getServers();
for (int j = 0; j < servers.length; j++) {
ExchServer3 server = servers[j];
int serverID = ((Number) serverIDs.get(server.getInternalName())).intValue();
ExchStore2[] stores = server.getStores();
for (int k = 0; k < stores.length; k++) {
ExchStore2 store = stores[k];
int storeId = queryLookupStoreId(sourceID, store.getInternalName(), c);
if (storeId == 0) {
// if we couldn't find an existing storeID, grab the next one from the sequence
storeId = getNextFromSequence("seq_store_id", c);
} else {
// if there is an existing storeID, delete it so we can insert the changes
delete.setInt(1, storeId);
int deleted = delete.executeUpdate();
if (deleted != 1) {
throw new SQLException("Delete for store " + storeId + " returned " + deleted);
}
}
storeIDs.put(store.getInternalName(), storeId);
insert.setInt(1, storeId);
insert.setInt(2, sourceID);
insert.setInt(3, serverID);
insert.setString(
4,
DirectoryUtils.truncateString(
store.getInternalName(), DB_STORE_INTERNALNAME_LENGTH));
insert.setString(
5,
DirectoryUtils.truncateString(store.getDisplayName(), DB_STORE_DISPLAYNAME_LENGTH));
insert.executeUpdate();
}
}
}
return storeIDs;
} finally {
if (delete != null) {
delete.close();
}
if (insert != null) {
insert.close();
}
}
}
private static void deleteTopologyData(
Connection c, int sourceID, Collection groupIDs, Collection serverIDs, Collection storeIDs)
throws SQLException {
PreparedStatement ps = null;
int purgeInterval =
ManagementContainer.getInstance()
.getConfiguration()
.getIntProperty(PURGE_DAY_INTERVAL_PROP, DEFAULT_PURGE_DAY_INTERVAL);
List<Integer> deletedStores = null;
List<Integer> deletedServers = null;
List<Integer> deletedSites = null;
try {
// First determine what stores to delete
String strQuery =
"select store_id from dat_customer_stores where exists ( "
+ " select * from dat_customer_servers svr "
+ " where svr.server_id = dat_customer_stores.server_id and "
+ " exists ( "
+ " select * from dat_customer_sites s "
+ " where s.site_id = svr.admin_group_id and source_id = ? "
+ " ) "
+ " ) and "
+ " store_id not in "
+ QueryUtils.literal(storeIDs)
+ " and is_deleted = false";
ps = c.prepareStatement(strQuery);
ps.setInt(1, sourceID);
ResultSet rs = ps.executeQuery();
// Convert the result set to a list of store id's to be deleted
while (rs.next()) {
if (deletedStores == null) deletedStores = new ArrayList<Integer>();
deletedStores.add(rs.getInt(1));
}
ps.close();
if (deletedStores != null) // Check to see if we have anything to delete
{
strQuery =
"update dat_customer_stores set is_deleted = true, purge_time = current_timestamp + '"
+ purgeInterval
+ " days'::interval"
+ " where store_id in "
+ QueryUtils.literal(deletedStores);
ps = c.prepareStatement(strQuery);
ps.executeUpdate();
ps.close();
// Log what we marked for deletion
logPoliciesUsingRemovedTopologyObjs(
"Marking as deleted", "store", deletedStores, IUserManager.PROP_STORE_ID, c);
}
ps = null;
// delete the servers
// First determine what servers to delete
strQuery =
"select server_id from dat_customer_servers "
+ "where "
+ " exists ( "
+ " select * from dat_customer_sites s "
+ " where s.site_id = dat_customer_servers.admin_group_id and source_id = ? "
+ " ) and "
+ " server_id not in "
+ QueryUtils.literal(serverIDs)
+ " and is_deleted = false";
ps = c.prepareStatement(strQuery);
ps.setInt(1, sourceID);
rs = ps.executeQuery();
// Convert the result set to a list of server id's to be deleted
while (rs.next()) {
if (deletedServers == null) deletedServers = new ArrayList<Integer>();
deletedServers.add(rs.getInt(1));
}
ps.close();
if (deletedServers != null) // Check to see if we have anything to delete
{
strQuery =
"update dat_customer_servers set is_deleted = true, purge_time = current_timestamp + '"
+ purgeInterval
+ " days'::interval"
+ " where server_id in "
+ QueryUtils.literal(deletedServers);
ps = c.prepareStatement(strQuery);
ps.executeUpdate();
ps.close();
// Log what we marked for deletion
logPoliciesUsingRemovedTopologyObjs(
"Marking as deleted", "server", deletedServers, IUserManager.PROP_SERVER_ID, c);
}
ps = null;
// delete the sites
// First determine what sites to delete
strQuery =
"select site_id from dat_customer_sites "
+ "where "
+ " source_id = ? and is_deleted = false and "
+ " site_id not in "
+ QueryUtils.literal(groupIDs);
ps = c.prepareStatement(strQuery);
ps.setInt(1, sourceID);
rs = ps.executeQuery();
// Convert the result set to a list of site id's to be deleted
while (rs.next()) {
if (deletedSites == null) deletedSites = new ArrayList<Integer>();
deletedSites.add(rs.getInt(1));
}
ps.close();
if (deletedSites != null) // Check to see if we have anything to delete
{
strQuery =
"update dat_customer_sites set is_deleted = true, purge_time = current_timestamp + '"
+ purgeInterval
+ " days'::interval"
+ " where site_id in "
+ QueryUtils.literal(deletedSites);
ps = c.prepareStatement(strQuery);
ps.executeUpdate();
ps.close();
// Log what we marked for deletion
logPoliciesUsingRemovedTopologyObjs(
"Marking as deleted", "site", deletedSites, IUserManager.PROP_ROUTING_GROUP_ID, c);
}
ps = null;
} finally {
if (ps != null) {
ps.close();
}
}
}
