def _make_sql(self, query, ordering=True):
""" Calculates the SQL for the table widget based on the query """
## Calculate the SQL
query_str = "select "
try:
self.order = int(query.get('order',self.order))
except: self.order=0
try:
self.direction = int(query.get('direction',self.direction))
except: self.direction = 0
total_elements = self.elements + self.filter_elements
## Fixup the elements - if no table specified use the global
## table - this is just a shortcut which allows us to be lazy:
for e in total_elements:
if not e.table: e.table = self.table
if not e.case: e.case = self.case
## The columns and their aliases:
query_str += ",".join([ e.select() + " as `" + e.name + "`" for e in self.elements ])
query_str += _make_join_clause(total_elements)
if self.where:
w = ["(%s)" % self.where,]
else:
w = []
for e in total_elements:
tmp = e.where()
if tmp: w.append(tmp)
## Is there a filter condition?
if self.filter_str:
filter_str = self.filter_str.replace('\r\n', ' ').replace('\n', ' ')
filter_str = parser.parse_to_sql(filter_str, total_elements, ui=None)
if not filter_str: filter_str=1
else: filter_str = 1
query_str += "where (%s and (%s)) " % (" and ".join(w), filter_str)
if self.groupby:
query_str += "group by %s " % DB.escape_column_name(self.groupby)
elif self._groupby:
query_str += "group by %s " % self.groupby
## Now calculate the order by:
if ordering:
try:
query_str += "order by %s " % self.elements[self.order].order_by()
if self.direction == 1:
query_str += "asc"
else: query_str += "desc"
except IndexError:
pass
return query_str
def test03FilteredLoad(self):
""" Test that filter expressions work when loading """
filter = ' "rhost" maxmind_country AUS and "status" = 404 '
## See if we can load the preset again:
log = LogFile.load_preset(self.test_case, self.log_preset, [self.datafile])
t = time.time()
table_name = self.test_table + "filtered"
## Load the data:
for a in log.load(table_name, filter=filter):
print a
print "Took %s seconds to load log" % (time.time()-t)
## How many rows were inserted
dbh = DB.DBO(self.test_case)
dbh.execute("select count(*) as c from `%s_log`", table_name)
row = dbh.fetch()
## Now try to enforce the same filter on the original table
## (which has all the rows in it):
for f in log.fields: f.table = self.test_table+"_log"
filter_sql = parser.parse_to_sql(filter, log.fields, None)
dbh.execute("select count(*) as c from `%s_log` where %s", (self.test_table, filter_sql))
row2 = dbh.fetch()
self.assertEqual(row['c'], row2['c'])
def filter_gui(query, result):
result.heading("Filter Table")
try:
filter_str = self.filter_string(query['filter'])
result.para(filter_str)
## Check the current filter string for errors by attempting to parse it:
try:
sql = parser.parse_to_sql(filter_str, elements)
## This is good if we get here - lets refresh to it now:
if query.has_key('__submit__'):
result.refresh(0, query, pane='parent')
return
except Exception, e:
result.text('Error parsing expression: %s' % e,
color='red')
result.text('\n', color='black')
except KeyError:
pass
result.start_form(query, pane="self")
result.textarea("Search Query", 'filter')
result.result += """<tr></tr>
<tr><td colspan=2 align=center>The following can be used to insert text rapidly into the search string</td></tr>
<tr><td>Column</td><td>
<select name=filter_column>
%s
</select> <a href=# onclick='var t=dojo.widget.manager.getWidgetById("filter"); t.execCommand("inserthtml", document.getElementsByName("filter_column")[0].value);'>Insert </a></td></tr>
""" % "\n".join([
"<option value=' \"%s\" '>%s</option>" % (e.name, e.name)
for e in elements
])
## Round up all the possible methods from all colmn types:
operators = {}
for e in elements:
for method in e.operators():
operators[method] = 1
methods = operators.keys()
methods.sort()
result.result += """<tr><td>Operators</td><td>
<select name=filter_operators>
%s
</select><a href=# onclick='var t=dojo.widget.manager.getWidgetById("filter"); t.execCommand("inserthtml", document.getElementsByName("filter_operators")[0].value);'>Insert </a></td></tr>
""" % "\n".join(
["<option value=' %s '>%s</option>" % (m, m) for m in methods])
result.end_form()
def filter_gui(query, result):
result.heading("Filter Table")
try:
filter_str = self.filter_string(query['filter'])
result.para(filter_str)
## Check the current filter string for errors by attempting to parse it:
try:
sql = parser.parse_to_sql(filter_str,types)
## This is good - lets refresh to it now:
if query.has_key('__submit__'):
result.refresh(0,query,pane='parent')
return
except Exception,e:
result.text('Error parsing expression: %s' % e, color='red')
result.text('\n',color='black')
except KeyError:
pass
result.start_form(query, pane="self")
result.textarea("Search Query", 'filter', cols=60, rows=5)
result.result += """<tr></tr>
<tr><td colspan=2 align=center>The following can be used to insert text rapidly into the search string</td></tr>
<tr><td>Column</td><td>
<select name=filter_column>
%s
</select> <a href=# onclick='var t=dojo.widget.manager.getWidgetById("filter"); t.execCommand("inserthtml", document.getElementsByName("filter_column")[0].value);'>Insert </a></td></tr>
""" % "\n".join(["<option value=' \"%s\" '>%s</option>" % (n,n) for n in names])
## Round up all the possible methods from all colmn types:
operators = {}
for t in types.values():
for method in t.operators():
operators[method]=1
methods = operators.keys()
methods.sort()
result.result+="""<tr><td>Operators</td><td>
<select name=filter_operators>
%s
</select><a href=# onclick='var t=dojo.widget.manager.getWidgetById("filter"); t.execCommand("inserthtml", document.getElementsByName("filter_operators")[0].value);'>Insert </a></td></tr>
""" % "\n".join(["<option value=' %s '>%s</option>" % (m,m) for m in methods])
result.end_form()
def outstanding_inodes(self, word_id=None):
## Remove any references to the LogicalIndexOffsets table
## - this will allow us to count how many inodes are
## involved in the re-indexing without making references
## to this column type.
elements = [ e for e in self.elements if e.table != "LogicalIndexOffsets" ]
## Calculate the new filter string
try:
new_filter_string = self.ui.defaults[self.filter]
sql = parser.parse_to_sql(new_filter_string,
elements, None)
except KeyError:
sql = '1'
tables = UI._make_join_clause(elements)
final_sql = DB.expand("%s where (%s) and (%s)",
(tables, sql,
self.table_where_clause))
count, total = Indexing.count_outdated_inodes(self.case, final_sql, word_id=word_id)
return count, total, tables, sql