def index(page, run_from, run_to):
start_time_stamp = int(time() * 1000)
preparation_sw = StopWatchTimer()
preparation_sw.start()
condition_types = g.tdb.get_condition_types()
query = g.tdb.session.query(Run)
# Run range is defined?
if run_from != -1 and run_to != -1:
# Check what is max/min run
run_min, run_max = run_from, run_to
if run_max < run_min:
run_min, run_max = run_max, run_min
# Filter query and count results
query = query.filter(Run.number >= run_min, Run.number <= run_max)
count = g.tdb.session.query(func.count(Run.number)).filter(Run.number >= run_min, Run.number <= run_max).scalar()
# we don't want pagination in this case, setting page size same/bigger than count
per_page = run_max - run_min
else:
count = g.tdb.session.query(func.count(Run.number)).scalar()
per_page = PER_PAGE
# Create pagination
pagination = Pagination(page, per_page, count)
preparation_sw.stop()
query_sw = StopWatchTimer()
query_sw.start()
# Get runs from query
runs = query.options(subqueryload(Run.conditions))\
.order_by(Run.number.desc())\
.slice(pagination.item_limit_from, pagination.item_limit_to)\
.all()
query_sw.stop()
performance = {
"preparation": preparation_sw.elapsed,
"query": query_sw.elapsed,
"selection": 0.0,
"start_time_stamp": start_time_stamp,
}
return render_template("runs/index.html", runs=runs,
DefaultConditions=DefaultConditions,
pagination=pagination,
condition_types=condition_types,
run_from=-1,
run_to=-1,
search_query="",
performance=performance)
if __name__ == "__main__":
print sys.argv
# Get connection string from arguments
parser = argparse.ArgumentParser(description="This example shows basics of how to select runs using search queries")
parser.add_argument("connection_string", help="RCDB connection string mysql://rcdb@localhost/rcdb")
parser.add_argument('target_cnd_names', nargs='*')
args = parser.parse_args()
print "Conditions:", args.target_cnd_names
# Open DB connection
db = RCDBProvider(args.connection_string)
sw = StopWatchTimer()
sw.start()
# Select production runs with event_count > 0.5M
result = db.select_runs("event_count > 100", 0, 20000)
#result.runs = db.get_runs(0, 20000)
sw.stop()
print "Selection took ", sw.elapsed
print("Selected {} runs".format(len(result.runs)))
print result.get_values(args.target_cnd_names, insert_run_number=True)
def get_values(self, condition_names, insert_run_number=False):
if self.db is None or not self.runs:
return [[]]
# Condition names... are just one name?
if isinstance(condition_names, basestring):
condition_names = [condition_names]
# No condition names?
if not condition_names:
if insert_run_number:
return [[run.number] for run in self.runs]
else:
# noinspection PyUnusedLocal
return [[] for run in self.runs]
target_cnd_names = condition_names
sw = StopWatchTimer()
sw.start()
all_cnt_types = self.db.get_condition_types()
all_cnd_types_by_name = {cnd.name: cnd for cnd in all_cnt_types}
# getting target conditions types and sorting them by id
target_cnd_types = [all_cnd_types_by_name[cnd_name] for cnd_name in target_cnd_names]
ct_id_to_user_defined_order = {}
for cnd_type in target_cnd_types:
for i in range(0, len(condition_names)):
if cnd_type.name == condition_names[i]:
ct_id_to_user_defined_order[cnd_type.id] = i
break
target_cnd_types = sorted(target_cnd_types, key=lambda x: x.id)
target_cnd_types_len = len(target_cnd_types)
ids = [ct.id for ct in target_cnd_types]
runs_asc = self.runs if not self.sort_desc else list(reversed(self.runs))
run_numbers = [r.number for r in runs_asc]
query = self.db.session.query(Condition) \
.filter(Condition.condition_type_id.in_(ids), Condition.run_number.in_(run_numbers)) \
.order_by(Condition.run_number, Condition.condition_type_id)
conditions = query.all()
# performance measurement
sw.stop()
self.performance["get_conditions"] = sw.elapsed
sw = StopWatchTimer()
sw.start()
# create empty rows
rows = []
def get_empty_row(run_number=0):
"""Creates empty rows properly
:param run_number: run number to add
"""
if insert_run_number:
# noinspection PyTypeChecker
return [run_number] + ([None] * target_cnd_types_len)
else:
return [None] * target_cnd_types_len
type_index = 0
prev_run = conditions[0].run_number
row = get_empty_row(runs_asc[0].number)
run_index = 0
while runs_asc[run_index].number != prev_run:
rows.append(row)
run_index += 1
row = get_empty_row(runs_asc[run_index].number)
for condition in conditions:
assert isinstance(condition, Condition)
type_id = condition.condition_type_id
if condition.run_number != prev_run:
prev_run = condition.run_number
while runs_asc[run_index].number != prev_run:
rows.append(row)
run_index += 1
row = get_empty_row(runs_asc[run_index].number)
type_index = 0
while type_index < target_cnd_types_len and type_id != target_cnd_types[type_index].id:
type_index += 1
if type_index == target_cnd_types_len:
type_index = 0
cell_index = ct_id_to_user_defined_order[target_cnd_types[type_index].id]
if insert_run_number:
row[cell_index + 1] = condition.value
else:
row[cell_index] = condition.value
# We have always have to
rows.append(row)
run_index += 1
# It may happen that we run out of selected conditions, because condition is not set for all runs after
# for example we have run=> condition 1=>x, 2=>y, 3=>None, 4=>None. DB will select only x and y conditions
# and 2 rows [[1],[2]], while it should [[1],[2],[None],[None]]. So we have to put missing rows in the end
while run_index < len(runs_asc):
rows.append(get_empty_row(runs_asc[run_index].number))
run_index += 1
# performance measure
sw.stop()
self.performance["tabling_values"] = sw.elapsed
if self.sort_desc:
rows.reverse()
return rows
def select_runs(self, search_str="", run_min=0, run_max=sys.maxsize, sort_desc=False):
""" Searches RCDB for runs with e
:param sort_desc: if True result runs will by sorted descendant by run_number, ascendant if False
:param run_min: minimum run to search
:param run_max: maximum run to search
:param search_str: Search pattern
:type search_str: str
:return: List of runs matching criteria
:rtype: list(Run)
"""
start_time_stamp = int(mktime(datetime.datetime.now().timetuple()) * 1000)
preparation_sw = StopWatchTimer()
preparation_sw.start()
if run_min > run_max:
run_min, run_max = run_max, run_min
# PHASE 0 - Maybe there is no query?!
if not search_str or not search_str.strip():
# If no query, just use get_runs function and return the result
preparation_sw.stop()
query_sw = StopWatchTimer()
query_sw.start()
sel_runs = self.get_runs(run_min, run_max, sort_desc)
query_sw.stop()
result = RunSelectionResult(sel_runs, self)
result.sort_desc = sort_desc
result.filter_condition_names = []
result.filter_condition_types = []
result.performance["preparation"] = preparation_sw.elapsed
result.performance["query"] = query_sw.elapsed
result.performance["selection"] = 0
result.performance["start_time_stamp"] = start_time_stamp
return result
# get all condition types
all_cnt_types = self.get_condition_types()
all_cnd_types_by_name = {cnd.name: cnd for cnd in all_cnt_types}
all_cnd_names = [str(key) for key in all_cnd_types_by_name.keys()]
# PHASE 1: getting what to search from search_str
search_str = str(search_str)
if '__' in search_str:
raise QueryFormatError("Query contains restricted symbol: '__'")
for alias in self.aliases:
al_name = "@" + alias.name
if al_name in search_str:
search_str = search_str.replace(al_name, '(' + alias.expression + ')')
search_str = search_str.replace('\n', ' ')
search_str = search_str.replace('\r', ' ')
tokens = [token for token in lexer.tokenize(search_str)]
target_cnd_types = []
names = []
aliased_cnd_types = []
aliased_cnd = []
names_count = 0
for token in tokens:
if token.type in lexer.rcdb_query_restricted:
raise QueryFormatError("Query contains restricted symbol: '{}'".format(token.value))
if token.type != "NAME":
continue
if token.value == 'math':
continue
if token.value == 'startswith':
continue
if token.value not in all_cnd_names:
message = "Name '{}' is not found in ConditionTypes".format(token.value)
raise QueryFormatError(message)
else:
cnd_name = token.value
cnd_type = all_cnd_types_by_name[token.value]
target_cnd_types.append(cnd_type)
token.value = "value[{}].value".format(names_count)
names_count += 1
names.append(cnd_name)
aliased_cnd.append(aliased(Condition))
aliased_cnd_types.append(aliased(ConditionType))
# PHASE 2: Database query
query = self.session.query()
if not names_count:
return None
search_eval = " ".join([token.value for token in tokens if isinstance(token, LexToken)])
for (i, alias_cnd) in enumerate(aliased_cnd):
query = query.add_entity(alias_cnd).filter(alias_cnd.condition_type_id == target_cnd_types[i].id)
if i != 0:
query = query.filter(alias_cnd.run_number == aliased_cnd[0].run_number)
query = query.filter(aliased_cnd[0].run_number >= run_min, aliased_cnd[0].run_number <= run_max) \
.join(aliased_cnd[0].run)
# apply sorting
if not sort_desc:
query = query.order_by(aliased_cnd[0].run_number)
else:
query = query.order_by(desc(aliased_cnd[0].run_number))
preparation_sw.stop()
query_sw = StopWatchTimer()
query_sw.start()
values = query.all()
query_sw.stop()
selection_sw = StopWatchTimer()
selection_sw.start()
# PHASE 3: Selecting runs
compiled_search_eval = compile(search_eval, '<string>', 'eval')
sel_runs = []
for value in values:
if isinstance(value, Condition):
value = (value,)
run = value[0].run
if eval(compiled_search_eval):
sel_runs.append(run)
selection_sw.stop()
result = RunSelectionResult(sel_runs, self)
result.filter_condition_names = names
result.filter_condition_types = target_cnd_types
result.sort_desc = sort_desc
result.performance["preparation"] = preparation_sw.elapsed
result.performance["query"] = query_sw.elapsed
result.performance["selection"] = selection_sw.elapsed
result.performance["start_time_stamp"] = start_time_stamp
return result
