import Utils, My_Logger, logging, datetime, csv, time
from Spot import Spot
from pysqlite2 import dbapi2 as sqlite
class SpotHandler():
def __init__(self, properties_file):
self.utils = Utils.Utils(properties_file)
## Install logger
LOG_FILENAME = self.utils.install_dir+'/'+self.utils.logs_folder_name+'/Coordinator.log'
self.my_logger = logging.getLogger('SpotHandler')
self.my_logger.setLevel(self.utils.logging_level)
handler = logging.handlers.RotatingFileHandler(
LOG_FILENAME, maxBytes=2*1024*1024, backupCount=5)
formatter = logging.Formatter("%(asctime)s - %(levelname)s - %(name)s - %(message)s")
handler.setFormatter(formatter)
self.my_logger.addHandler(handler)
con = sqlite.connect(self.utils.db_file)
cur = con.cursor()
try:
cur.executescript('DROP TABLE IF EXISTS spotIntances;')
con.commit()
cur.execute('create table IF NOT EXISTS spotIntances(id INTEGER PRIMARY KEY AUTOINCREMENT, instanceType text, chargingDate datetime, bidPrice real, paidSoFar real, instanceTableID int, tiramola_instanceType text)')
con.commit()
except sqlite.DatabaseError, e:
print e.message
print "ERROR in create"
self.startDate = datetime.datetime.strptime(self.utils.startDate,'%Y-%m-%dT%H:%M:%SZ')
def convertSimtimeToDatetime(self, simTime):
return self.startDate + datetime.timedelta(0,simTime*30)
def getMarketPrice(self, simTime, instanceType):
con = sqlite.connect(self.utils.marketPriceDB)
cur = con.cursor()
try:
market_price = None
if(simTime > 0):
#self.my_logger.debug("Market Price for: " + str(self.convertSimtimeToDatetime(simTime).strftime('%Y-%m-%dT%H:%M:%SZ')) + " for vm type: " + str(self.utils.spotInstanceType) + " sim time: " + str(simTime))
market_price = cur.execute("""select max(price) from market_prices where instance_type = ? and log_date <= ? and log_date > ?""",(str(instanceType),str(self.convertSimtimeToDatetime(simTime).strftime('%Y-%m-%dT%H:%M:%SZ')),str(self.convertSimtimeToDatetime(simTime-1).strftime('%Y-%m-%dT%H:%M:%SZ')),)).fetchall()
#self.my_logger.debug("Maximum Market Price for the past 30secs: " + str(market_price))
if(market_price[0][0] == None):
market_price = cur.execute("""select price from market_prices where instance_type = ? and log_date <= ? order by log_date desc limit 1""",(str(instanceType),str(self.convertSimtimeToDatetime(simTime).strftime('%Y-%m-%dT%H:%M:%SZ')),)).fetchall()
#self.my_logger.debug("Latest Market Price: " + str(market_price))
else:
market_price = cur.execute("""select price from market_prices where instance_type = ? and log_date <= ? order by log_date desc limit 1""",(str(instanceType),str(self.convertSimtimeToDatetime(simTime).strftime('%Y-%m-%dT%H:%M:%SZ')),)).fetchall()
return market_price[0][0]
except sqlite.DatabaseError, e:
self.my_logger.debug("Error Market Price for: " + str(self.convertSimtimeToDatetime(simTime).strftime('%Y-%m-%dT%H:%M:%SZ')) + " for vm type: " + str(self.utils.spotInstanceType) + " sim time: " + str(simTime))
self.my_logger.debug(str(e.message))
print "ERROR in select market price"
con.rollback()
def getOnDemandPrice(self, instanceType):
return self.utils.onDemandPrices[instanceType]
def provisionSpot(self, instanceType, count, simTime, instanceTableID, tiramola_instanceType):
bidPrice = self.getBidPrice(tiramola_instanceType, instanceType)
self.storeSpot(instanceType,count,bidPrice,self.convertSimtimeToDatetime(simTime),instanceTableID,tiramola_instanceType)
def checkSpots(self, simTime):
totalCost = 0
removeIdList = []
spotsList = self.fetchSpots()
# self.my_logger.debug("Fetched Spots: " + str(len(spotsList)))
for spot in spotsList:
# self.my_logger.debug(str(spot))
currentMarketPrice = self.getMarketPrice(simTime, spot.instanceType)
if(currentMarketPrice > spot.bidPrice):
totalCost += self.stopSpot(simTime, spot,force=True)
removeIdList.append(spot.instanceTableID)
else:
self.calculateSpotCost(simTime, spot)
return (totalCost, removeIdList)
def checkSpotsAhead(self, simTime):
removeIdList = []
spotsList = self.fetchSpots()
for spot in spotsList:
currentMarketPrice = self.getMarketPrice(simTime, spot.instanceType)
if(currentMarketPrice > spot.bidPrice):
removeIdList.append(spot.instanceTableID)
return (0, removeIdList)
def stopSpot(self, simTime, spot, force=False):
cost = self.calculateSpotCost(simTime, spot,force,True)
self.removeSpotFromDB(spot)
return cost
def stopSpotBasedOnInstanceTableId(self, simTime, instanceTableId, force=False):
spot = self.fetchSpotsBasedOnInstanceTableId(instanceTableId)
cost = self.calculateSpotCost(simTime, spot,force,True)
self.removeSpotFromDB(spot)
return cost
def removeSpotFromDB(self, spot):
con = sqlite.connect(self.utils.db_file)
cur = con.cursor()
try:
cur.execute("""DELETE FROM spotIntances where id=?""", (str(spot.id),))
con.commit()
except sqlite.DatabaseError, e:
print e.message
print "ERROR in delete"
def calculateSpotCost(self, simTime, spot, force=False, stop=False):
marketPrice = self.getMarketPrice(simTime, spot.instanceType)
minutes = self.calculateRunningTimeMinutes(simTime,spot.chargingDate)
if(stop and not force):
if(minutes%60 > 0):
minutes += (60-(minutes%60.0))
elif(stop and force):
minutes = int(minutes/60)*60
con = sqlite.connect(self.utils.db_file)
cur = con.cursor()
try:
if((int(minutes/60)*marketPrice > 0)):
cur.execute("""update spotIntances set paidSoFar = paidSoFar + ?, chargingDate = ? where id=?""", (int(minutes/60)*marketPrice, self.convertSimtimeToDatetime(simTime)-datetime.timedelta(0,(minutes%60)*60),str(spot.id),))
con.commit()
if(stop):
paidSoFar = cur.execute("""select paidSoFar from spotIntances where id = ?""", (str(spot.id),)).fetchall()
return paidSoFar[0][0]
else:
return 0
except sqlite.DatabaseError, e:
print e.message
print "ERROR in sql"
def calculateRunningTimeMinutes(self,simTime, chargingDate):
experimentTime = self.convertSimtimeToDatetime(simTime)
chargingDateTime = datetime.datetime.strptime(chargingDate,"%Y-%m-%d %H:%M:%S")
var1 = time.mktime(experimentTime.timetuple())
var2 = time.mktime(chargingDateTime.timetuple())
return (var1 - var2)/60.0
def getBidPrice(self, tiramola_instanceType, instanceType):
if(self.utils.bidPolicy == 'max'):
onDemandPrice = self.getOnDemandPrice(instanceType);
if('_14' in tiramola_instanceType):
return onDemandPrice*0.25
elif('_12' in tiramola_instanceType):
return onDemandPrice*0.5
else:
return onDemandPrice
else:
return self.cumulusBiDPolicy(instanceType)
def cumulusBiDPolicy(cumulusBiDPolicy):
return getOnDemandPrice(instanceType)
def fetchSpots(self):
spotsList = []
con = sqlite.connect(self.utils.db_file)
cur = con.cursor()
try:
spotsTuples = cur.execute('select * from spotIntances')
con.commit()
for spotTuple in spotsTuples:
spotsList.append(Spot(spotTuple[0],spotTuple[1],spotTuple[2],spotTuple[3],spotTuple[4],spotTuple[5],spotTuple[6]))
return spotsList
except sqlite.DatabaseError, e:
print e.message
print "ERROR in fetch"
def fetchSpotsBasedOnInstanceTableId(self, instanceTableId):
con = sqlite.connect(self.utils.db_file)
cur = con.cursor()
try:
spotsTuples = cur.execute("""select * from spotIntances where InstanceTableId = ?""",(str(instanceTableId),))
con.commit()
for spotTuple in spotsTuples:
return Spot(spotTuple[0],spotTuple[1],spotTuple[2],spotTuple[3],spotTuple[4],spotTuple[5],spotTuple[6])
except sqlite.DatabaseError, e:
print e.message
print "ERROR in fetch based on instance table id"
def storeSpot(self, instanceType,count,bidPrice,chargingDate,instanceTableID,tiramola_instanceType):
con = sqlite.connect(self.utils.db_file)
cur = con.cursor()
try:
for i in range(0, int(count)):
cur.execute(""" insert into spotIntances(instanceType, chargingDate, bidPrice, paidSoFar, instanceTableID, tiramola_instanceType) values (?,?,?,?,?,?)""",(instanceType,chargingDate,bidPrice,0,instanceTableID,tiramola_instanceType,))
con.commit()
except sqlite.DatabaseError, e:
print e.message
print "ERROR in insert"
#insert to db spot with paidSoFar=0