def reportEmptyPurgeCompleted(self, pid):
m_container = ManagementContainer.getInstance()
c = m_container.getDBConnection(ManagementContainer.AM_POOL_NAME)
s = None
needsRollback = True
try:
try:
s = c.createStatement()
# mark the number of messages dispatched
m_container.getActiveMailboxStoreManager().markDispatched(c, pid, 0)
# procedure expects partition ID, count of items purged, and duration
sql = "select msg_purge_mark_purged(%d,0,0)" % pid
s.execute(sql)
self.flushPartitionCache(pid)
c.commit()
needsRollback = False
except:
log("Exception caught trying to call msg_purge_mark_purged()")
finally:
if needsRollback:
c.rollback()
DbUtils.safeClose(s)
m_container.safeReturnDBConnection(c, ManagementContainer.AM_POOL_NAME)
def getNextPartition():
sql = 'select msg_purge_next_to_compute()'
mc = ManagementContainer.getInstance()
conn = mc.getDBConnection(ManagementContainer.AM_POOL_NAME)
s = None
rs = None
needsRollback = True
try:
s = conn.createStatement()
rs = s.executeQuery(sql)
if rs.next():
pid = rs.getInt(1)
conn.commit()
needsRollback = False
if pid == 0:
return None
else:
return pid
finally:
if (needsRollback):
conn.rollback()
DbUtils.safeClose(rs)
DbUtils.safeClose(s)
mc.safeReturnDBConnection(conn,ManagementContainer.AM_POOL_NAME)
return None
def getUserDBData(partId,msgId):
mc = ManagementContainer.getInstance()
conn = mc.getDBConnection(ManagementContainer.AM_POOL_NAME)
recipients = []
sender = 0
rs = None
s = None
try:
s = conn.createStatement()
sql = 'select user_id,is_sender from msg_users_%s where m1_message_id = %s' % (partId,msgId)
rs = s.executeQuery(sql)
while rs.next():
id = rs.getInt(1)
category = RecipientCategory.valueOf(rs.getInt(2))
if RecipientCategory.SENDER.equals(category):
sender = id
else:
recipients.append(MatchRecipient(id, category))
finally:
conn.rollback()
DbUtils.safeClose(rs)
DbUtils.safeClose(s)
mc.safeReturnDBConnection(conn, ManagementContainer.AM_POOL_NAME)
return (sender,recipients)
def getNextPartition(self):
sql = "select msg_purge_next_to_dispatch(true)"
# log('getNextPartition() sql ' + sql)
mc = ManagementContainer.getInstance()
pm = mc.getPartitionManager()
conn = mc.getDBConnection(ManagementContainer.AM_POOL_NAME)
s = None
rs = None
needsRollback = True
try:
s = conn.createStatement()
rs = s.executeQuery(sql)
if rs.next():
pid = rs.getInt(1)
# log('getNextPartition() pid = %d' % pid)
conn.commit()
needsRollback = False
if pid == 0:
return None
else:
return pm.getPartition(pid)
finally:
if needsRollback:
conn.rollback()
DbUtils.safeClose(rs)
DbUtils.safeClose(s)
mc.safeReturnDBConnection(conn, ManagementContainer.AM_POOL_NAME)
return None
def getRanges(self, partId):
rc = []
sql = "select * from generate_purge('msg_purge_todo_%d',%d)" % (partId,partId)
mc = ManagementContainer.getInstance()
conn = mc.getPDBConnection(partId)
s = None
rs = None
needsRollback = True
try:
s = conn.createStatement()
rs = s.executeQuery(sql)
while rs.next():
cmd = rs.getString(1)
parts = cmd.split(' ')
rangeStr = parts[-1]
range = rangeStr.split('-')
if len(range) != 2:
log('Invalid range specification found',rangeStr)
continue
rc.append((long(range[0]),long(range[1])))
conn.commit()
needsRollback = False
finally:
if (needsRollback):
conn.rollback()
DbUtils.safeClose(rs)
DbUtils.safeClose(s)
mc.returnDBConnection(conn)
return rc
def setUserPurgeDate(usersToPurge, markPurgeDaysBeforeCurrent):
mc = ManagementContainer.getInstance()
connection = mc.getDBConnection(ManagementContainer.POOL_NAME)
s = connection.createStatement()
s.executeUpdate("update dat_user_account set purge_date = current_timestamp - '"+str(markPurgeDaysBeforeCurrent)+" days'::interval where object_id in "+QueryUtils.literal(usersToPurge))
s.close()
connection.commit()
mc.safeReturnDBConnection(connection, ManagementContainer.POOL_NAME)
DbUtils.safeClose(s)
def updateContentId(oldMessageId, replacementId, partitionId):
mc = ManagementContainer.getInstance()
connection = mc.getDBConnection(ManagementContainer.AM_POOL_NAME)
s = connection.createStatement()
command = "update dat_repl_messages_" + str(partitionId) + " set content_replacement_id= " + str(replacementId) + " where m1_message_id= " + str(oldMessageId)
print "sql: " + command
s.executeUpdate(command)
connection.commit()
mc.safeReturnDBConnection(connection, ManagementContainer.AM_POOL_NAME)
DbUtils.safeClose(s)
def updatePassword(email, hashedPassword):
mc = ManagementContainer.getInstance()
connection = mc.getDBConnection(ManagementContainer.POOL_NAME)
s = connection.createStatement()
command = "update dat_user_account set hashed_password = \'" + str(hashedPassword) + "\' where primary_email = \'" + email + "\'"
print "EXEC: " + command
s.executeUpdate(command)
connection.commit()
mc.safeReturnDBConnection(connection, ManagementContainer.POOL_NAME)
DbUtils.safeClose(s)
def getPartitionId(custId,archiveId):
mc = ManagementContainer.getInstance()
connection = mc.getDBConnection(ManagementContainer.AM_POOL_NAME)
s = connection.createStatement()
command = "select partition_id from archive_chunk where customer_id=" + custId + " and archive_id=" + archiveId
print "EXEC: " + command
resultSet = s.executeQuery(command)
resultSet.next()
partition_id = resultSet.getInt(1)
mc.safeReturnDBConnection(connection, ManagementContainer.AM_POOL_NAME)
DbUtils.safeClose(s)
return partition_id
def getArchiveMessages(partitionId,archiveId):
mc = ManagementContainer.getInstance()
connection = mc.getDBConnection(ManagementContainer.AM_POOL_NAME)
s = connection.createStatement()
command = "select count(*) from archive_chunk_message_" + str(partitionId) + " where archive_id=" + archiveId
print "EXEC: " + command
resultSet = s.executeQuery(command)
resultSet.next()
count = resultSet.getInt(1)
mc.safeReturnDBConnection(connection, ManagementContainer.AM_POOL_NAME)
DbUtils.safeClose(s)
return count
def removePurgeHold(mc,custId):
conn = mc.getDBConnection(ManagementContainer.AM_POOL_NAME)
stmt = None
needsRollback = True
try:
stmt = conn.createStatement()
stmt.executeUpdate('delete from purge_holds where customer_id = ' + str(custId))
conn.commit()
needsRollback = False
finally:
if (needsRollback):
conn.rollback()
DbUtils.safeClose(stmt)
mc.safeReturnDBConnection(conn,ManagementContainer.AM_POOL_NAME)
def checkPassword(email, pword):
mc = ManagementContainer.getInstance()
connection = mc.getDBConnection(ManagementContainer.POOL_NAME)
s = connection.createStatement()
command = "select hashed_password from dat_user_account where primary_email = \'" + email + "\'"
print "EXEC: " + command
resultSet = s.executeQuery(command)
resultSet.next()
hashedPassword = resultSet.getString(1)
mc.safeReturnDBConnection(connection, ManagementContainer.POOL_NAME)
DbUtils.safeClose(s)
authMgr = mc.getAuthenticationManager()
if not authMgr.checkPassword(pword, hashedPassword):
print "Hashed passwords do not match"
sys.exit(1)
def getLocId(locName):
conn = ManagementContainer.getInstance().getDBConnection(ManagementContainer.AM_POOL_NAME)
s = None
rs = None
try:
s = conn.createStatement()
q = "select location_id from dat_cluster_locations where location = '%s'" % str(locName)
rs = s.executeQuery(q)
if rs.next():
return rs.getInt(1)
return None
finally:
conn.rollback()
DbUtils.safeClose(rs)
DbUtils.safeClose(s)
ManagementContainer.getInstance().safeReturnDBConnection(conn, ManagementContainer.AM_POOL_NAME)
def initPurge():
sql = "select msg_purge_initialize()"
log('initPurge:', sql)
mc = ManagementContainer.getInstance()
conn = mc.getDBConnection(ManagementContainer.AM_POOL_NAME)
s = None
needsRollback = True
try:
s = conn.createStatement()
s.executeQuery(sql)
conn.commit()
needsRollback = False
finally:
if (needsRollback):
conn.rollback()
DbUtils.safeClose(s)
mc.safeReturnDBConnection(conn,ManagementContainer.AM_POOL_NAME)
def createPurgeHold(custID, days):
sql = "insert into purge_holds values (" + str(custID) + ",current_timestamp + '" + str(days) + " days'::interval);"
stmt = None;
cont = ManagementContainer.getInstance()
c = cont.getDBConnection(ManagementContainer.AM_POOL_NAME)
rollback = True
try:
stmt = c.createStatement()
stmt.executeUpdate(sql);
print "Added purge hold for customer", custID, "for", days, "days."
c.commit();
rollback = False
finally:
DbUtils.safeClose(stmt)
if ( rollback ):
c.rollback()
cont.safeReturnDBConnection(c,ManagementContainer.AM_POOL_NAME)
def shouldChunk(self, partId, daysSinceLastPurge):
sql = "select reltuples from pg_class where relname = 'dat_repl_messages_%d'" % partId
mc = ManagementContainer.getInstance()
conn = mc.getPDBConnection(partId)
s = None
rs = None
tuples = 0
try:
s = conn.createStatement()
rs = s.executeQuery(sql)
if rs.next():
tuples = rs.getLong(1)
finally:
conn.rollback()
DbUtils.safeClose(rs)
DbUtils.safeClose(s)
mc.returnDBConnection(conn)
return (daysSinceLastPurge * 0.01 * tuples) >= self.maxTupleThreshold
def getDBData(partId,msgId):
mc = ManagementContainer.getInstance()
conn = mc.getDBConnection(ManagementContainer.AM_POOL_NAME)
s = None
rs = None
try:
s = conn.createStatement()
sql = 'select m1_message_id,customer_id,index_status,subject from dat_repl_messages_%s where m1_message_id = %s' % (partId,msgId)
rs = s.executeQuery(sql)
if rs.next():
return (rs.getLong(1),rs.getInt(2),rs.getInt(3),rs.getString(4))
finally:
conn.rollback()
DbUtils.safeClose(rs)
DbUtils.safeClose(s)
mc.safeReturnDBConnection(conn, ManagementContainer.AM_POOL_NAME)
#print time.asctime(),'Cannot find info for %s,%s' % (partId,msgId)
return (long(msgId),0,0,None)
def alterEmailFile(partitionId, messageId, extension, append=True):
mc = ManagementContainer.getInstance()
connection = mc.getDBConnection(ManagementContainer.AM_POOL_NAME)
s = connection.createStatement()
command = "select storage_location,customer_id from dat_repl_messages_" + str(partitionId) + " where m1_message_id=" + str(messageId)
print "sql: " + command
resultSet = s.executeQuery(command)
resultSet.next()
storageLocation = resultSet.getString(1)
customerId = resultSet.getString(2)
mc.safeReturnDBConnection(connection, ManagementContainer.AM_POOL_NAME)
DbUtils.safeClose(s)
storageLocation = "/ems/bigdisk/storagep" + partitionId + "/" + customerId + "/" + storageLocation
if append is True:
command = "mv " + storageLocation + " " + storageLocation + extension
print "EXEC: " + command
os.system(command)
else:
command = "mv " + storageLocation + extension + " " + storageLocation
print "EXEC: " + command
os.system(command)
def moveMessage(mc,clusLocId,partId,messageId,customerId):
conn = mc.getDBConnection(ManagementContainer.AM_POOL_NAME)
doRefeed = False
s = None
try:
s = conn.createStatement()
sql = 'update dat_repl_messages_%d set index_status = %d where m1_message_id = %d' % (int(partId),int(clusLocId),long(messageId))
try:
s.executeUpdate(sql)
conn.commit()
doRefeed = True
except:
conn.rollback()
print 'Exception (transaction aborted)', sql, sys.exc_info(), traceback.print_exc(file=sys.stderr)
finally:
DbUtils.safeClose(s)
mc.safeReturnDBConnection(conn, ManagementContainer.AM_POOL_NAME)
if doRefeed:
senderId,recipIds = getUserDBData(partId,messageId)
refeedMessage(mc,clusLocId,partId,messageId,customerId,senderId,recipIds)
def expireWinAuthPassword(email):
mc = ManagementContainer.getInstance()
connection = mc.getDBConnection(ManagementContainer.POOL_NAME)
s = connection.createStatement()
command = "update dat_user_account set password_expire_time=creation_date where primary_email = \'" + email + "\'"
print "EXEC: " + command
s.executeUpdate(command)
connection.commit()
command = "/opt/ems/bin/emscmd clean-expired-passwords"
os.system(command)
command = "select hashed_password,hashed_imap_password from dat_user_account where primary_email = \'" + email + "\'"
print "EXEC: " + command
resultSet = s.executeQuery(command)
resultSet.next()
hashedPassword = resultSet.getString(1)
hashedImapPassword = resultSet.getString(2)
mc.safeReturnDBConnection(connection, ManagementContainer.POOL_NAME)
DbUtils.safeClose(s)
if not hashedPassword is None or not hashedImapPassword is None:
print "Hashed password=\"", hashedPassword, "\" Hashed Imap Password=\"", hashedImapPassword, "\""
sys.exit(1)
def updateCustomerNames():
emails = ["*****@*****.**", "*****@*****.**", "*****@*****.**", "*****@*****.**", "*****@*****.**", "*****@*****.**"]
chinese = "中国的"
greek = "ελληνικά"
modi = "મોદી"
arabic = "العربية"
japanese = "日本人"
hebrew = "עברית"
names = [greek,hebrew,arabic,modi,chinese,japanese]
mc = ManagementContainer.getInstance()
connection = mc.getDBConnection(ManagementContainer.POOL_NAME)
s = connection.createStatement()
count = 0
for name in names:
command = "update dat_user_account set display_name = \'" + name + "\' where primary_email = \'" + emails[count] + "\'"
print "EXEC: " + command
command = String("update dat_user_account set display_name = \'" + name + "\' where primary_email = \'" + emails[count] + "\'", "utf-8")
s.executeUpdate(command)
count +=1
connection.commit()
mc.safeReturnDBConnection(connection, ManagementContainer.POOL_NAME)
DbUtils.safeClose(s)
def preCalcPartition(id):
log('preCalcPartition(%d)' % id)
mc = ManagementContainer.getInstance()
pm = mc.getPartitionManager()
sql = 'select msg_purge_compute(%d)' % id
log('preCalcPartition() invoking %s' % sql)
conn = mc.getPDBConnection(id)
s = None
needsRollback = True
try:
s = conn.createStatement()
s.executeQuery(sql)
conn.commit()
needsRollback = False
finally:
if (needsRollback):
conn.rollback()
DbUtils.safeClose(s)
mc.returnDBConnection(conn)
log('preCalcPartition(%d) done' % id)
def listPartitions(self):
sql = 'select id from dat_active_mailbox_partition order by purge_order'
mc = ManagementContainer.getInstance()
pm = mc.getPartitionManager()
conn = mc.getDBConnection(ManagementContainer.AM_POOL_NAME)
s = None
rs = None
rc = []
try:
s = conn.createStatement()
rs = s.executeQuery(sql)
while rs.next():
pid = rs.getInt(1)
rc.append(pm.getPartition(pid))
finally:
conn.rollback()
DbUtils.safeClose(rs)
DbUtils.safeClose(s)
mc.safeReturnDBConnection(conn,ManagementContainer.AM_POOL_NAME)
return rc
def markMessageMissing(messageId,pid,contentSubject):
mc = ManagementContainer.getInstance()
cr = mc.getContentReplacementDAO()
connection = mc.getDBConnection(mc.AM_POOL_NAME)
s = connection.createStatement()
#Make sure a content replacement for this content exists and if not create one
command = "/opt/ems/bin/emscmd add-content-replacement -c \"" + contentSubject + "\" -n \"" + "test Notes" + "\""
print "EXEC: " + command
process = os.popen(command)
output = process.read()
process.close()
print "OUT: " + output
contentId = re.search('m_id=(.+?),m_notes', output).group(1)
print "CONTENT ID " +str(contentId)
#Mark the message as missing in the db
updateRepl = "update dat_repl_messages_" + str(pid) + " set content_replacement_id = " + str(contentId) + " where m1_message_id = " + str(messageId)
updateKeyMap = "update dat_repl_message_key_map set requires_restore = true where m1_message_id = " + str(messageId)
#Grab the storage location and customer id of the message
results = getStorageLocation(pid,messageId,s)
s.executeUpdate(updateRepl)
s.executeUpdate(updateKeyMap)
connection.commit()
mc.safeReturnDBConnection(connection,mc.AM_POOL_NAME)
DbUtils.safeClose(s)
connection.close()
storageLocation = results[1]
customerId = results[0]
directory = "/ems/bigdisk/storagep"+str(pid)+"/"+str(customerId)+"/"
pathname = os.path.abspath(os.path.join(directory,storageLocation))
#add .bak to the message content file so the system thinks that it is missing
os.rename(pathname,pathname+".bak")
needsRollback = True
sql = 'select msg_purge_next_to_precompute(' + str(days) + ',' + str(id) + ')'
rs = s.executeQuery(sql)
if rs.next():
id = rs.getInt(1)
if id > 0:
partIdList.append(id)
conn.commit()
needsRollback = False
else:
done = True
finally:
if (needsRollback):
conn.rollback()
DbUtils.safeClose(rs)
DbUtils.safeClose(s)
mc.safeReturnDBConnection(conn,ManagementContainer.AM_POOL_NAME)
# build ranges by partition/shard
# todo - execute shard updates in parallel
for partId in partIdList:
conn = mc.getPDBConnection(partId)
s = None
needsRollback = True
try:
s = conn.createStatement()
sql = 'select msg_purge_partition(' + str(partId) + ', false,false)'
s.executeQuery(sql)
sql = "select ddl_table_create('msg_purge_todo_%d','create table msg_purge_todo_%d as select m1_message_id from msg_purge_%d where policy_delete_date < current_timestamp', true)"
sql = sql % (partId,partId,partId)
s = conn.createStatement()
done = False
id = -1
for msg_id in out:
sql = 'select partition_id from dat_repl_message_key_map where m1_message_id=' + msg_id
rs = s.executeQuery(sql)
if rs.next():
id = rs.getInt(1)
if id > 0:
print "Message id ", msg_id.strip(), "PartitionId = ", id
data.update({msg_id: id})
#partIdList.append(id)
conn.commit()
else:
done = True
finally:
conn.rollback()
DbUtils.safeClose(rs)
DbUtils.safeClose(s)
mc.safeReturnDBConnection(conn, ManagementContainer.AM_POOL_NAME)
for key in data:
print "executing command for msgId ", key.strip(), "partId ", data[key]
optCmd = "/opt/ems/bin/amp purge-v2 -P -i " + str(
data[key]) + " -w 'md.m1_message_id=" + key.strip() + "\' " + "2>/dev/null"
print optCmd
os.system(optCmd)
sys.exit(0)