def query_execute(userquery,options):
global saved_qpresults
query = "select * from earthquake"
if userquery != "":
query = userquery
print >> sys.stderr, "user query: ",query
sdbioptions = {'afl':False}
print >> sys.stderr, "saved_qpresults",saved_qpresults
if saved_qpresults == 0:
saved_qpresults = sdbi.verifyQuery(query,sdbioptions)
#only do this check for new queries
if options['reduce_res_check'] and (saved_qpresults['size'] > sdbi.D3_DATA_THRESHOLD):
return {'reduce_res':True}
if 'reduce_type' in options: # reduction requested
sdbioptions['reduce_res'] = True
srtoptions = {'afl':False}
if 'predicate' in options:
srtoptions['predicate'] = options['predicate']
sdbioptions['reduce_options'] = setup_reduce_type(options['reduce_type'],srtoptions)
else: #return original query
sdbioptions = {'afl':False,'reduce_res':False}
queryresultobj = sdbi.executeQuery(query,sdbioptions)
print >> sys.stderr, "retrieved data from db.\nparsing results..."
sdbioptions={'dimnames':saved_qpresults['dims']}
queryresultarr = sdbi.getAllAttrArrFromQueryForJSON(queryresultobj[0],sdbioptions)
if queryresultobj[1] != 0:
saved_qpresults = queryresultobj[1]
# get the new dim info
queryresultarr['dimnames'] = saved_qpresults['dims'];
queryresultarr['dimbases'] = saved_qpresults['dimbases'];
queryresultarr['dimwidths'] = saved_qpresults['dimwidths'];
print >> sys.stderr, "setting saved_qpresults to 0"
saved_qpresults = 0 # reset so we can use later
return queryresultarr # note that I don't set reduce_res false. JS code will still handle it properly
def fetch_first_tile2(userquery,options):
db = sdbi.scidbOpenConn()
global experts
query = userquery
sdbioptions = {'afl':False,'db':db}
saved_qpresults = sdbi.verifyQuery(query,sdbioptions)
sdbi.scidbCloseConn(db)
print "saved qp results, looking for error"
if 'error' in saved_qpresults:
print "error found, returning error:",saved_qpresults
return saved_qpresults
else:
print "saved_qpresults:",saved_qpresults
user_id = options['user_id']
# setup metadata
print "setting up metadata"
with sbdata.metadata_lock:
sbdata.backend_metadata[user_id] = {}
sbdata.backend_metadata[user_id]['orig_query'] = query
sbdata.backend_metadata[user_id]['saved_qpresults'] = saved_qpresults
if 'data_threshold' in options:
sbdata.backend_metadata[user_id]['data_threshold'] = options['data_threshold']
else: # default is whatever is prescribed in scidb server interface code
sbdata.backend_metadata[user_id]['data_threshold'] = sdbi.D3_DATA_THRESHOLD
sbdata.backend_metadata[user_id]['levels'] = 0 #sbdata.default_levels #leave at default levels for now
#TODO: let # levels vary
#get tile
base_id = [0] * saved_qpresults['numdims']
print "base id:",base_id
if 'usenumpy' in options:
usenumpy = options['usenumpy']
else:
usenumpy = False
tile = ti.getTileByIDN(base_id,0,user_id,usenumpy)
if 'error' in tile:
return tile
#save tile info
tile_key = str(base_id)
with sbdata.user_history_lock: # add tile to history
currtime = datetime.now()
sbdata.user_history[user_id] = [{'tile_id':base_id,'level':0,'timestamp':currtime}]
try:
if not os.path.isdir(history_dir):
os.makedirs(history_dir)
with open(history_dir +"/"+str(user_id)+".txt", "a") as myfile:
myfile.write(str(base_id)+'\t0\t'+str(currtime)+"\n")
except Exception as e:
print e # don't barf if this doesn't work
with sbdata.user_tiles_lock:# save tile
sbdata.user_tiles[user_id] = {0:{tile_key:tile}}
print "added tile to sbdata.user_tiles:"#,sbdata.user_tiles
#start prefetching
#print "setting up prefetching experts"
#experts = range(1)
#expert_threads = range(1)
#experts[0] = dumb_expert.BasicExpert()
#expert_threads[0] = threading.Thread(target=experts[0].prefetch,args=(sbdata.max_prefetched,user_id,))
#expert_threads[0].start()
#sdbi.scidbCloseConn(db)
return tile
def query_execute(userquery,options):
user_id = options['user_id']
resolution = sdbi.D3_DATA_THRESHOLD
if 'resolution' in options and options['resolution'] > 0:
print "got resolution"
resolution = options['resolution']
db = sdbi.scidbOpenConn()
query = userquery
#print "user query: ",query
sdbioptions = {'afl':False,'db':db,'resolution':resolution}
saved_qpresults = None
if 'saved_qpresults' in options:
saved_qpresults = options['saved_qpresults']
#dumb_expert.prefetch(3,options['user_id'])
#tile = ti.getTileByID(2,sbdata.backend_metadata[options['user_id']]['levels'],options['user_id'])
#print "tile: ",tile
#sdbioptions={'dimnames':saved_qpresults['dims']}
#print sdbi.getAllAttrArrFromQueryForJSON(tile[0],sdbioptions)
#fetch_tile(0,1,{'user_id':user_id})
if saved_qpresults is None: # first time
#fetch_first_tile(query,{'user_id':user_id})
saved_qpresults = sdbi.verifyQuery(query,sdbioptions)
#with sbdata.metadata_lock:
# sbdata.backend_metadata[user_id] = {}
# sbdata.backend_metadata[user_id]['orig_query'] = query
# sbdata.backend_metadata[user_id]['saved_qpresults'] = saved_qpresults
# if 'data_threshold' in options:
# sbdata.backend_metadata[user_id]['data_threshold'] = options['data_threshold']
# else: # default
# sbdata.backend_metadata[user_id]['data_threshold'] = sdbi.D3_DATA_THRESHOLD
# sbdata.backend_metadata[user_id]['levels'] = sbdata.default_levels #leave at default levels for now
# #TODO: let # levels vary
#only do this check for new queries
if options['reduce_res_check'] and (saved_qpresults['size'] > resolution):#sdbi.D3_DATA_THRESHOLD):
return {'reduce_res':True,'saved_qpresults':saved_qpresults}
if 'reduce_type' in options: # reduction requested
sdbioptions['reduce_res'] = True
srtoptions = {'afl':False,'saved_qpresults':saved_qpresults}
if 'predicate' in options:
srtoptions['predicate'] = options['predicate']
sdbioptions['reduce_options'] = setup_reduce_type(options['reduce_type'],srtoptions)
else: #return original query
sdbioptions = {'afl':False,'reduce_res':False,'db':db}
queryresultobj = sdbi.executeQuery(query,sdbioptions)
print "queryresultobj:",queryresultobj
print "retrieved data from db.\nparsing results..."
sdbioptions={'dimnames':saved_qpresults['dims']}
queryresultarr = sdbi.getAllAttrArrFromQueryForJSON(queryresultobj[0],sdbioptions)
if queryresultobj[1] != 0:
saved_qpresults = queryresultobj[1]
# get the new dim info
queryresultarr['dimnames'] = saved_qpresults['dims']
queryresultarr['dimbases'] = saved_qpresults['dimbases']
queryresultarr['dimwidths'] = saved_qpresults['dimwidths']
queryresultarr['saved_qpresults'] = saved_qpresults
sdbi.scidbCloseConn(db)
return queryresultarr # note that I don't set reduce_res false. JS code will still handle it properly
def verify_query(query):
saved_qpresults = None
try:
db = sdbi.scidbOpenConn()
sdbioptions = {'afl':False,'db':db}
saved_qpresults = sdbi.verifyQuery(query,sdbioptions)
sdbi.scidbCloseConn(db)
except Exception as e:
if DEBUG: print "error occured:",e
pass
return saved_qpresults
def fetch_first_tile(userquery,options):
db = sdbi.scidbOpenConn()
global experts
query = userquery
sdbioptions = {'afl':False,'db':db}
saved_qpresults = sdbi.verifyQuery(query,sdbioptions)
user_id = options['user_id']
# setup metadata
print "setting up metadata"
with sbdata.metadata_lock:
sbdata.backend_metadata[user_id] = {}
sbdata.backend_metadata[user_id]['orig_query'] = query
sbdata.backend_metadata[user_id]['saved_qpresults'] = saved_qpresults
if 'data_threshold' in options:
sbdata.backend_metadata[user_id]['data_threshold'] = options['data_threshold']
else: # default is whatever is prescribed in scidb server interface code
sbdata.backend_metadata[user_id]['data_threshold'] = sdbi.D3_DATA_THRESHOLD
sbdata.backend_metadata[user_id]['levels'] = 0 #sbdata.default_levels #leave at default levels for now
#TODO: let # levels vary
#get tile
tile = ti.getTileByIDXY(0,0,0,user_id)
#save tile info
tile_key = "0,0"
with sbdata.user_history_lock: # add tile to history
sbdata.user_history[user_id] = [{'tile_xid':0,'tile_yid':0,'level':0,'timestamp':datetime.now()}]
with sbdata.user_tiles_lock:# save tile
sbdata.user_tiles[user_id] = {0:{tile_key:tile}}
print "added tile to sbdata.user_tiles:"#,sbdata.user_tiles
#start prefetching
#print "setting up prefetching experts"
#experts = range(1)
#expert_threads = range(1)
#experts[0] = dumb_expert.BasicExpert()
#expert_threads[0] = threading.Thread(target=experts[0].prefetch,args=(sbdata.max_prefetched,user_id,))
#expert_threads[0].start()
#sdbi.scidbCloseConn(db)
return tile
def compare_tiles(query,threshold,zoom_diff,x_label,y_label):
db = sdbi.scidbOpenConn()
sdbioptions = {'afl':False,'db':db}
saved_qpresults = sdbi.verifyQuery(query,sdbioptions)
sdbi.scidbCloseConn(db)
if 'error' in saved_qpresults:
print "error found, returning error:",saved_qpresults
return saved_qpresults
else:
if DEBUG_PRINT: print "saved_qpresults:",saved_qpresults
if x_label not in saved_qpresults['dims']:
print "x_label",x_label,"not found"
return
if y_label not in saved_qpresults['dims']:
print "y_label",y_label,"not found"
return
n = saved_qpresults['numdims']
root_k = math.ceil(math.pow(threshold,1.0/n))
threshold = root_k ** 2 ## adjust to make it a nice power
tsize = saved_qpresults['size'] # get the size of the result
levels = 1
if tsize > threshold: # if k happens to be larger than the total results
levels = math.ceil(math.log(1.0*tsize/threshold)/(n*math.log(zoom_diff)))+1 # need to account for zoom diff
if DEBUG_PRINT: print "levels:",levels
tile_metadata = sdbi.get_complete_tile_metadata(root_k,zoom_diff,saved_qpresults)
if DEBUG_PRINT: print "tile metadata:",tile_metadata
#_id = [0,0]
#tile = ti.getTileNoUser(_id,query,saved_qpresults,levels,threshold)
#if 'error' in tile:
# print 'error found, reeturning error:',tile
# return tile
for l in range(1,int(levels)):
total_tiles_root = int(math.pow(zoom_diff,l))
for xid in range(0,total_tiles_root):
for yid in range(0,total_tiles_root):
#get tile
tile_info = {'type':'xy','tile_xid':xid,'tile_yid':yid,'x_label':x_label,'y_label':y_label}
tile = ti.getTileNoUser(tile_info,query,saved_qpresults,l,levels,threshold,USE_NUMPY)
if 'error' in tile:
print 'error found, reeturning error:',tile
return tile
#stats = tile['stats']
#if DEBUG_PRINT: print stats
if yid+1 < total_tiles_root:
for yid2 in range(yid+1,total_tiles_root):
#if DEBUG_PRINT: print "comparing tile (lvl:",l,", x:",xid,", y:",yid,") and tile (lvl:",l,", x:",xid,", y:",yid2,")"
#check rest of col: xid,yid2
tile_info['tile_xid']=xid
tile_info['tile_yid']=yid2
#get other tile
newtile = ti.getTileNoUser(tile_info,query,saved_qpresults,l,levels,threshold,USE_NUMPY)
if 'error' in newtile:
print 'error found, reeturning error:',newtile
return newtile
#compare tiles
if xid+1 < total_tiles_root:
for xid2 in range(xid+1,total_tiles_root):
for yid2 in range(0,total_tiles_root):
#if DEBUG_PRINT: print "comparing tile (lvl:",l,", x:",xid,", y:",yid,") and tile (lvl:",l,", x:",xid2,", y:",yid2,")"
#check all tiles w/ > xid: xid2,yid2
tile_info['tile_xid']=xid2
tile_info['tile_yid']=yid2
#get other tile
newtile = ti.getTileNoUser(tile_info,query,saved_qpresults,l,levels,threshold,USE_NUMPY)
if 'error' in newtile:
print 'error found, reeturning error:',newtile
return newtile
def queryplan_execute(userquery,options):
sdbioptions = {'afl':False}
return sdbi.verifyQuery(userquery,sdbioptions)
