def putIndex(self, ch, zidx, timestamp, resolution, indexstr, update):
"""MySQL put index routine"""
# if in a TxN us the transaction cursor. Otherwise create one.
if self.txncursor is None:
cursor = self.conn.cursor()
else:
cursor = self.txncursor
if not update:
sql = "INSERT INTO {} ( annid, timestamp, cube) VALUES ( %s, %s, %s )".format(
ch.getIdxTable(resolution))
try:
cursor.execute(sql, (zidx, timestamp, indexstr))
except MySQLdb.Error, e:
logger.error("Error updating index {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
raise SpatialDBError(
"Error updating index {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
except BaseException, e:
logger.exception("Unknown error when updating index")
raise SpatialDBError("Unknown error when updating index")
def getCube(self,
ch,
timestamp,
zidx,
resolution,
update=False,
neariso=False,
direct=False):
"""Retrieve a cube from the database by token, resolution, and zidx"""
# if in a TxN us the transaction cursor. Otherwise create one.
if self.txncursor is None:
cursor = self.conn.cursor()
else:
cursor = self.txncursor
if not neariso:
sql = "SELECT cube FROM {} WHERE (zindex,timestamp) = ({},{})".format(
ch.getTable(resolution), zidx, timestamp)
else:
sql = "SELECT cube FROM {} WHERE (zindex,timestamp) = ({},{})".format(
ch.getNearIsoTable(resolution), zidx, timestamp)
if update:
sql += " FOR UPDATE"
try:
cursor.execute(sql)
row = cursor.fetchone()
except MySQLdb.Error, e:
logger.error("Failed to retrieve data cube: {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
raise SpatialDBError(
"Failed to retrieve data cube: {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
def getCubeIndex(self, ch, resolution, listofidxs, timestamp):
cursor = self.conn.cursor()
# if listoftimestamps:
# sql = "SELECT zindex, timestamp FROM {} WHERE zindex={} and timestamp in (%s)".format(self.getIndexStore(ch, resolution), listofidxs[0])
# else:
sql = "SELECT zindex FROM {} WHERE zindex in (%s) AND timestamp = {}".format(
self.getIndexStore(ch, resolution), timestamp)
# creats a %s for each list element
in_p = ', '.join(map(lambda x: '%s', listofidxs))
# replace the single %s with the in_p string
sql = sql % in_p
try:
rc = cursor.execute(sql, listofidxs)
ids_existing = cursor.fetchall()
if ids_existing:
ids_to_fetch = Set(listofidxs).difference(
Set(i[0] for i in ids_existing))
return list(ids_to_fetch)
else:
return listofidxs
except MySQLdb.Error, e:
logger.error("Error selecting zindex: {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
raise SpatialDBError(
"Error selecting zindex: {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
def putCubeIndex(self,
ch,
listoftimestamps,
listofidxs,
resolution,
neariso=False):
"""Add the listofidxs to the store"""
try:
cachedtime_list = [time.time()
] * len(listofidxs) * len(listoftimestamps)
index_list = list(
interleave([
cachedtime_list,
self.getIndexList(ch, listoftimestamps, listofidxs,
resolution, neariso)
]))
self.client.zadd(self.getIndexStore(), *index_list)
# if listoftimestamps:
# # TODO KL Test this
# cachedtime_list = [time.time()]*len(listofidxs)
# index_list = list(interleave([cachedtime_list, self.getIndexList(ch, resolution, listofidxs)]))
# self.client.zadd(self.getIndexStore(), *index_list)
# else:
# cachedtime_list = [time.time()]*len(listofidxs)
# index_list = list(interleave([cachedtime_list, self.getIndexList(ch, resolution, listofidxs)]))
# self.client.zadd(self.getIndexStore(), *index_list)
except Exception, e:
logger.error(
"Error inserting cube indexes into the database. {}".format(e))
raise SpatialDBError(
"Error inserting cube indexes into the database. {}".format(e))
def getIndex(self, ch, annid, timestamp, resolution, update):
"""MySQL fetch index routine"""
# if in a TxN us the transaction cursor. Otherwise create one.
if self.txncursor is None:
cursor = self.conn.cursor()
else:
cursor = self.txncursor
# get the block from the database
sql = "SELECT cube FROM {} WHERE annid = {} AND timestamp = {}".format(
ch.getIdxTable(resolution), annid, timestamp)
if update:
sql += " FOR UPDATE"
try:
cursor.execute(sql)
row = cursor.fetchone()
except MySQLdb.Error, e:
logger.error("Failed to retrieve cube {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
raise SpatialDBError(
"Failed to retrieve cube {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
def getCubes(self, ch, listofidxs, resolution, neariso=False):
# weird pythonism for tuples of length 1 they print as (1,) and don't parse
# just get the cube
if len(listofidxs) == 1:
data = self.getCube(ch, listofidxs[0], resolution, False)
if data is None:
return
else:
yield listofidxs[0], None
else:
try:
# Converting the listofidxs to INT. This is wrong and needs to be fixed.
listofidxs = [int(i) for i in listofidxs]
cql = "SELECT zidx, cuboid FROM {} WHERE resolution ={} AND zidx in {}".format(
ch.getTable(resolution), resolution, tuple(listofidxs))
rows = self.session.execute(cql)
for row in rows:
yield (row.zidx, row.cuboid.decode('hex'))
except Exception, e:
logger.error("Error retrieving cubes from the database")
raise SpatialDBError(
"Error retrieving cubes from the database")
def putExceptions(self,
ch,
zidx,
timestamp,
resolution,
annid,
excstr,
update=False):
"""Store a list of exceptions"""
"""This should be done in a transaction"""
# if in a TxN us the transaction cursor. Otherwise create one.
if self.txncursor is None:
cursor = self.conn.cursor()
else:
cursor = self.txncursor
if not update:
sql = "INSERT INTO {} (zindex, timestamp, id, exlist) VALUES (%s, %s, %s, %s)".format(
ch.getExceptionsTable(resolution))
try:
cursor.execute(sql, (zidx, timestamp, annid, excstr))
except MySQLdb.Error, e:
if self.txncursor is None:
cursor.close()
raise
logger.error(
"Error inserting exceptions {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
raise SpatialDBError(
"Error inserting exceptions {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
def putCacheCube(self, ch, timestamp, zidx, resolution, cube_str, update=False, neariso=False):
"""Store a single cube in the cache"""
# generating the key
key_list = self.generateKeys(ch, [timestamp], [zidx], resolution, neariso=neariso)
try:
self.client.mset( dict(zip(key_list, [cube_str])) )
except Exception, e:
logger.error("Error inserting cube into the database. {}".format(e))
raise SpatialDBError("Error inserting cube into the database. {}".format(e))
def putCube(self, ch, zidx, resolution, cubestr, update=False):
"""Store a cube into the database"""
try:
cql = "INSERT INTO {} ( resolution, zidx, cuboid ) VALUES ( {}, %s, %s )".format(
ch.getTable(resolution), resolution)
self.session.execute(cql, (zidx, cubestr.encode('hex')))
except Exception, e:
logger.error("Error inserting cube into the database")
raise SpatialDBError("Error inserting cube into the database")
def annotate(self, annid, timestamp, offset, locations, conflictopt):
"""Add annotation by a list of locations"""
try:
self.data[timestamp, :, :, :], exceptions = annotate_ctype(
self.data[timestamp, :, :, :], annid, offset,
np.array(locations, dtype=np.uint32), conflictopt)
return exceptions
except IndexError, e:
raise SpatialDBError("Voxel list includes out of bounds request.")
def getCubes(self,
ch,
listoftimestamps,
listofidxs,
resolution,
neariso=False,
direct=False):
# if in a TxN us the transaction cursor. Otherwise create one.
if self.txncursor is None:
cursor = self.conn.cursor()
else:
cursor = self.txncursor
# creating a list of tuples (zindex, timestamp) and unrolling them for sql execution as [zindex, timestamp, zindex, timestamp]
index_list = list(
itertools.chain.from_iterable(
itertools.product(listofidxs, listoftimestamps)))
if neariso:
print "Fetching data from isotropic tables"
sql = "SELECT zindex, timestamp, cube FROM {} WHERE (zindex, timestamp) in (%s)".format(
ch.getNearIsoTable(resolution))
else:
print "Fetching data from normal tables"
sql = "SELECT zindex, timestamp, cube FROM {} WHERE (zindex, timestamp) in (%s)".format(
ch.getTable(resolution))
# creats a %s for each list element
in_p = ', '.join(map(lambda x: '(%s,%s)', [1] * (len(index_list) / 2)))
# replace the single %s with the in_p string
sql = sql % in_p
try:
rc = cursor.execute(sql, index_list)
# Get the objects and add to the cube
while (True):
try:
retval = cursor.fetchone()
except:
break
if retval is not None:
yield (retval)
else:
return
except MySQLdb.Error, e:
logger.error(
"Failed to retrieve data cubes: {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
raise SpatialDBError(
"Failed to retrieve data cubes: {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
def __init__(self, db):
"""Connect to the Redis backend"""
self.db = db
try:
self.client = redis.StrictRedis(
connection_pool=RedisPool.blocking_pool)
self.pipe = self.client.pipeline(transaction=False)
except redis.ConnectionError as e:
logger.error("Could not connect to Redis server. {}".format(e))
raise SpatialDBError(
"Could not connect to Redis server. {}".format(e))
def putCacheCubes(self, ch, listoftimestamps, listofidxs, resolution, listofcubes, update=False, neariso=False):
"""Store multiple cubes in the cache"""
# import blosc
# print "inserting cube of shape: {}, res: {}".format(blosc.unpack_array(listofcubes[0]).shape, resolution)
# generating the list of keys
key_list = self.generateKeys(ch, listoftimestamps, listofidxs, resolution, neariso)
try:
self.client.mset( dict(zip(key_list, listofcubes)) )
except Exception, e:
logger.error("Error inserting cubes into the database. {}".format(e))
raise SpatialDBError("Error inserting cubes into the database. {}".format(e))
def __init__(self, db):
"""Connect to the database"""
self.db = db
try:
# connect to cassandra
self.cluster = Cluster([self.db.proj.getKVServer()])
self.session = self.cluster.connect(self.db.proj.getDBName())
self.session.default_timeout = 120
except Exception as e:
logger.warning(
"Could not connect to the Cassandra cluster. {}".format(e))
raise SpatialDBError(
"Could not connect to the Cassandra cluster. {}".format(e))
def putCube(self,
ch,
timestamp,
zidx,
resolution,
cubestr,
update=False,
neariso=False,
direct=False):
"""Store a cube from the annotation database"""
# if in a TxN us the transaction cursor. Otherwise create one.
if self.txncursor is None:
cursor = self.conn.cursor()
else:
cursor = self.txncursor
try:
# we created a cube from zeros
if not update:
if not neariso:
print "Inserting data from normal tables"
sql = "INSERT INTO {} (zindex, timestamp, cube) VALUES (%s, %s, %s)".format(
ch.getTable(resolution))
else:
print "Inserting data from isotropic tables"
sql = "INSERT INTO {} (zindex, timestamp, cube) VALUES (%s, %s, %s)".format(
ch.getNearIsoTable(resolution))
# this uses a cursor defined in the caller (locking context): not beautiful, but needed for locking
cursor.execute(sql, (zidx, timestamp, cubestr))
else:
if not neariso:
print "Updating data from normal tables"
sql = "UPDATE {} SET cube=(%s) WHERE (zindex,timestamp)=({},{})".format(
ch.getTable(resolution), zidx, timestamp)
else:
print "Updating data from isotropic tables"
sql = "UPDATE {} SET cube=(%s) WHERE (zindex,timestamp)=({},{})".format(
ch.getNearIsoTable(resolution), zidx, timestamp)
cursor.execute(sql, (cubestr, ))
except MySQLdb.Error, e:
logger.error(
"Error updating/inserting cube: {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
raise SpatialDBError(
"Error updating/inserting cube: {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
def getCube(self, ch, zidx, resolution, update=False):
"""Retrieve a cube from the database by token, resolution, and zidx"""
try:
cql = "SELECT cuboid FROM {} WHERE resolution = {} AND zidx = {}".format(
ch.getTable(resolution), resolution, zidx)
row = self.session.execute(cql)
if row:
return row[0].cuboid.decode('hex')
else:
return None
except Exception, e:
logger.error("Error retrieving cube from the database")
raise SpatialDBError("Error retrieving cube from the database")
def getCacheCube(self, ch, timestamp, zidx, resolution, update=False, neariso=False):
"""Retrieve a single cube from the cache"""
# list of id to fetch which do not exist in cache
ids_to_fetch = self.kvindex.getCubeIndex(ch, [timestamp], [zidx], resolution, neariso=neariso)
listofsuperidxs = self.generateSuperZindexes(ids_to_fetch, resolution)
# fetch the supercuboid from s3
super_cuboid = self.s3io.getCube(ch, timestamp, listofsuperidxs[0], resolution, update=update, neariso=neariso)
if super_cuboid:
for listofidxs, listoftimestamps, listofcubes in self.breakCubes(timestamp, zidx, resolution, super_cuboid):
self.putCacheCubes(ch, listoftimestamps, listofidxs, resolution, listofcubes, update=update, neariso=neariso)
try:
rows = self.client.mget( self.generateKeys(ch, [timestamp], [zidx], resolution, neariso) )
except Exception, e:
logger.error("Error retrieving cubes into the database. {}".format(e))
raise SpatialDBError("Error retrieving cubes into the database. {}".format(e))
def __init__(self, db):
"""Connect to the database"""
self.db = db
self.conn = None
# Connection info
try:
self.conn = MySQLdb.connect(host=self.db.proj.host,
user=self.db.proj.kvengine_user,
passwd=self.db.proj.kvengine_password,
db=self.db.proj.dbname)
except MySQLdb.Error, e:
self.conn = None
logger.error("Failed to connect to database: {}, {}".format(
self.db.proj.host, self.db.proj.dbname))
raise SpatialDBError(
"Failed to connect to database: {}, {}".format(
self.db.proj.host, self.db.proj.dbname))
def __init__(self, db):
"""Connect to the database"""
self.db = db
self.conn = None
# Connection info
try:
self.conn = MySQLdb.connect(host=self.db.proj.getDBHost(),
user=self.db.proj.getDBUser(),
passwd=self.db.proj.getDBPasswd(),
db=self.db.proj.getDBName())
except MySQLdb.Error, e:
self.conn = None
logger.error("Failed to connect to database: {}, {}".format(
self.db.proj.getDBHost(), self.db.proj.getDBName()))
raise SpatialDBError(
"Failed to connect to database: {}, {}".format(
self.db.proj.getDBHost(), self.db.proj.getDBName()))
def putCubeIndex(self, ch, resolution, listofidxs, timestamp):
cursor = self.conn.cursor()
# if listoftimestamps:
# sql = "REPLACE INTO {} (zindex, timestamp) VALUES (%s,%s)".format(self.getIndexStore(ch, resolution))
# else:
sql = "REPLACE INTO {} (zindex, timestamp VALUE (%s,{})".format(
self.getIndexStore(ch, resolution), timestamp)
try:
cursor.executemany(sql, listofidxs)
except MySQLdb.Error, e:
logger.error("Error inserting zindex: {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
raise SpatialDBError(
"Error inserting zindex: {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
def getCubeIndex(self,
ch,
listoftimestamps,
listofidxs,
resolution,
neariso=False):
"""Retrieve the indexes of inserted cubes"""
index_store = self.getIndexStore()
index_store_temp = index_store + '&temp'
index_list_size = len(listofidxs) * len(listoftimestamps)
try:
index_list = list(
interleave([[1] * index_list_size,
self.getIndexList(ch, listoftimestamps, listofidxs,
resolution, neariso)]))
self.client.zadd(index_store_temp, *index_list)
# if listoftimestamps:
# # TODO KL Test this
# index_list = list(interleave([[1]*len(listofidxs), self.getIndexList(ch, resolution, listofidxs)]))
# self.client.zadd(index_store_temp, *index_list)
# else:
# index_list = list(interleave([[1]*len(listofidxs), self.getIndexList(ch, resolution, listofidxs)]))
# self.client.zadd(index_store_temp, *index_list)
# self.client.zinterstore(index_store_temp, [index_store_temp, index_store] )
self.client.zunionstore(index_store_temp, {
index_store_temp: 1,
index_store: 0
}, 'MIN')
ids_to_fetch = self.client.zrevrangebyscore(
index_store_temp, '+inf', 1)
self.client.delete(index_store_temp)
except Exception, e:
logger.error(
"Error retrieving cube indexes into the database. {}".format(
e))
raise SpatialDBError(
"Error retrieving cube indexes into the database. {}".format(
e))
def deleteIndex(self, ch, annid, resolution):
"""MySQL update index routine"""
# if in a TxN us the transaction cursor. Otherwise create one.
if self.txncursor is None:
cursor = self.conn.cursor()
else:
cursor = self.txncursor
sql = "DELETE FROM {} WHERE annid={}".format(
ch.getIdxTable(resolution), annid)
try:
cursor.execute(sql)
except MySQLdb.Error, e:
logger.error("Error deleting the index {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
raise SpatialDBError(
"Error deleting the index {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
def getExceptions(self, ch, zidx, timestamp, resolution, annid):
"""Load a the list of excpetions for this cube."""
# if in a TxN us the transaction cursor. Otherwise create one.
if self.txncursor is None:
cursor = self.conn.cursor()
else:
cursor = self.txncursor
# get the block from the database
sql = "SELECT exlist FROM {} where zindex={} AND timestamp={} AND id={}".format(
ch.getExceptionsTable(resolution), zidx, timestamp, annid)
try:
cursor.execute(sql)
row = cursor.fetchone()
except MySQLdb.Error, e:
logger.error("Error reading exceptions {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
raise SpatialDBError(
"Error reading exceptions {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
def getCacheCubes(self, ch, listoftimestamps, listofidxs, resolution, neariso=False):
"""Retrieve multiple cubes from the cache"""
try:
ids_to_fetch = self.kvindex.getCubeIndex(ch, listoftimestamps, listofidxs, resolution, neariso=neariso)
super_listofidxs = self.generateSuperZindexes(ids_to_fetch, resolution)
if super_listofidxs:
logger.warn("Super indexes to fetch {}".format(super_listofidxs))
super_cuboids = self.s3io.getCubes(ch, listoftimestamps, super_listofidxs, resolution, neariso=neariso)
if super_cuboids:
for super_zidx, time_index, super_cuboid in super_cuboids:
superlistofidxs, superlistoftimestamps, superlistofcubes = self.breakCubes(time_index, super_zidx, resolution, super_cuboid)
# call putCubes and update index in the table before returning data
self.putCacheCubes(ch, superlistoftimestamps, superlistofidxs, resolution, superlistofcubes, update=True, neariso=neariso)
# fetch all cubes from redis
rows = self.client.mget( self.generateKeys(ch, listoftimestamps, listofidxs, resolution, neariso) )
for (timestamp, zidx), row in zip(itertools.product(listoftimestamps, listofidxs), rows):
yield(zidx, timestamp, row)
except Exception, e:
logger.error("Error retrieving cubes into the database. {}".format(e))
raise SpatialDBError("Error retrieving cubes into the database. {}".format(e))
def deleteExceptions(self, ch, zidx, timestamp, resolution, annid):
"""Delete a list of exceptions for this cuboid"""
# if in a TxN us the transaction cursor. Otherwise create one.
if self.txncursor is None:
cursor = self.conn.cursor()
else:
cursor = self.txncursor
sql = "DELETE FROM {} WHERE zindex ={} AND timestamp={} AND id ={}".format(
ch.getExceptionsTable(resolution), zidx, timestamp, annid)
try:
cursor.execute(sql)
except MySQLdb.Error, e:
if self.txncursor is None:
cursor.close()
logger.error("Error deleting exceptions {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
raise SpatialDBError(
"Error deleting exceptions {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
sql += " FOR UPDATE"
try:
cursor.execute(sql)
row = cursor.fetchone()
except MySQLdb.Error, e:
logger.error("Failed to retrieve cube {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
raise SpatialDBError(
"Failed to retrieve cube {}: {}. sql={}".format(
e.args[0], e.args[1], sql))
except BaseException, e:
logger.exception("Unknown exception")
raise SpatialDBError("Unknown exception")
finally:
# close the local cursor if not in a transaction
if self.txncursor is None:
cursor.close()
# If we can't find a index, they don't exist
if row is None:
return []
else:
return row[0]
def putIndex(self, ch, zidx, timestamp, resolution, indexstr, update):
"""MySQL put index routine"""