def get_where(cursor, class_type, orderby='id', fields="*", start=0, limit=0, test_mode=False, **wheres):
where = ""
if wheres != {}:
where = []
for k, v in wheres.items():
if v == None: continue
# Array handler
if type(v) in (list, tuple, set):
if len(v) == 0: continue
safe_vals = []
for i in v:
if type(i) == str:
safe_vals.append(database_f.escape(i))
else:
safe_vals.append(str(i))
where.append("%s IN (%s)" % (k, ",".join(safe_vals)))
# Non array
elif type(v) == str:
where.append("\"%s\" = '%s'" % (k, database_f.escape(v)))
elif type(v) in (int, float):
where.append("\"%s\" = '%s'" % (k, v))
else:
raise Exception("No handler for type %s" % type(v))
where = " AND ".join(where)
return _query(cursor, class_type, fields=fields, where=where, orderby=orderby, start=start, limit=limit, test_mode=test_mode)
def main(cursor):
table_name = common_f.get_val("table", "")
field_name = common_f.get_val("field", "")
new_value = common_f.get_val("value", "").strip()
where = common_f.get_val("where", "")
print_query = common_f.get_val("p", False)
silent = common_f.get_val("silent", False)
new_value_db = new_value
try:
if new_value_db != float(new_value_db) and new_value_db != int(new_value_db):
new_value_db = "'%s'" % database_f.escape(new_value_db)
except Exception as e:
new_value_db = "'%s'" % database_f.escape(new_value_db)
query = """UPDATE {table} SET {field} = {value} WHERE {where};""".format(
table=table_name, field=field_name, value=new_value_db, where=where
)
try:
cursor.execute(query)
except Exception as e:
raise Exception("Database error: %s\nQuery: %s" % (str(e.args[0]).replace("\n", ""), query))
if print_query:
return query
if not silent:
return new_value
else:
return ""
def get_user(cursor, username, password, from_cookie=False):
u = None
query = """SELECT * FROM users WHERE lower(username) = '{}'""".format(database_f.escape(username))
try: cursor.execute(query)
except Exception as e:
raise Exception("Database error: %s\nQuery: %s" % (str(e.args[0]).replace("\n",""), query))
for row in cursor:
u = User(**row)
if u == None:
return "Username not found"
# Do we need to hash the password?
h_password = password
if not from_cookie:
h_password = encode_password(password, u.salt, passes=1)
# Is the password correct?
if h_password != u.password:
return "Incorrect password"
# It's correct, return the user instance
common_f.cache['user'] = u
return u
def get_one(cursor, class_type, where="", test_mode=False, fields="*", orderby="", **wheres):
if where == "" and wheres != {}:
where = []
for k, v in wheres.items():
if type(v) == str:
where.append("%s = '%s'" % (k, database_f.escape(v)))
else:
where.append("%s = '%s'" % (k, v))
where = " AND ".join(where)
return _query(cursor, class_type, fields=fields, where=where, limit=1, orderby=orderby, test_mode=test_mode)
def test_escape(self):
self.test_targets.append(database_f.escape)
for str_in, expected in self.escape_vals:
self.assertEqual(database_f.escape(str_in), expected)
def main(cursor, query_filter=""):
query_filter = common_f.get_val("filter", query_filter)
if query_filter == "":
log_dict = common_q.get_all(cursor, user_log.UserLog, orderby="access_time DESC")
elif query_filter == "today":
today = datetime.date.today()
today = time.mktime(today.timetuple())
log_dict = common_q.get_all(
cursor, user_log.UserLog, where="access_time >= %d" % today, orderby="access_time DESC"
)
else:
log_dict = common_q.get_all(
cursor, user_log.UserLog, where="mode='%s'" % database_f.escape(query_filter), orderby="access_time DESC"
)
output = []
if len(log_dict) > 0:
user_dict = common_q.get_all(cursor, user.User)
user_dict[-1] = user.User(username="******")
output.append(
"""<table border="0" cellspacing="0" cellpadding="5" style="width:100%;">
<tr class="row2">
<th>Date</th>
<th>Mode</th>
<th>User</th>
<th>Load time</th>
<th colspan="2"> </th>
</tr>
"""
)
i = 1
for log_id, the_log in log_dict.items():
i += 1
the_date = the_log.access_time
output.append(
"""
<tr class="row{row}">
<td>{date}</td>
<td>{page}</td>
<td>{user}</td>
<td>{load_time}</td>
<td class="block_cell"><a href="web.py?mode=edit_log&log={log_id}">View</a></td>
<td class="block_cell"><a href="web.py?mode=edit_log&log={log_id}&sub_mode=delete">Delete</a></td>
</tr>""".format(
log_id=log_id,
row=i % 2,
load_time=round(the_log.load_time, 4),
date=common_f.display_date(the_date, "%d of %B at %H:%M"),
page="No mode specified" if the_log.page == "" else the_log.page,
user=user_dict[the_log.user_id].username,
)
)
output.append("</table>")
else:
output.append("<div style='padding:10px;'>No logs found</div>")
return "".join(output)
def _query(cursor, class_type, fields="*",
where = '',
orderby = '',
start=0, limit=0, test_mode=False):
if type(class_type) == dict:
table = class_type['Name']
else:
table = class_type.table_info['Name']
query = """SELECT {fields} FROM {table}""".format(
fields = ",".join(fields),
table = table,
)
# Where
if where != '': query += " WHERE {}".format(where)
# Order by
if orderby != '': query += " ORDER BY {}".format(database_f.escape(orderby))
# Limit stuff
if start > 0 and limit > 0: query += " LIMIT {}, {}".format(start, limit)
if start > 0 and limit < 1: query += " LIMIT 0, {}".format(limit)
if start < 1 and limit > 0: query += " LIMIT {}".format(limit)
# Test mode?
if test_mode:
return query
# Run query
try:
cursor.execute(query)
except Exception as e:
raise Exception("Database error: %s\nQuery: %s" % (str(e.args[0]).replace("\n",""), query))
# Get results, just 1 field? We want to return a list
if len(fields) == 1 and type(fields) in (list, tuple):
results = []
field_name = fields[0]
for row in cursor:
results.append(row[field_name])
else:
if "id" in fields:
results = collections.OrderedDict()
for row in cursor:
results[row['id']] = row
elif fields == "*":
results = collections.OrderedDict()
for row in cursor:
results[row['id']] = class_type(**row)
else:
raise Exception("")
results = []
for row in cursor:
results.append(row)
# No results?
if len(results) == 0:
if limit == 1:
return None
else:
return {}
# If limit is 1 we simple return the first item in our results
if limit == 1:
if len(fields) == 1 and type(fields) in (list, tuple):
return results[0]
else:
return results[list(results.keys())[0]]
return results
def main(cursor, query_filter=""):
query_filter = common_f.get_val("filter", query_filter)
if query_filter == "":
error_dict = common_q.get_all(cursor, error.Error, where="fixed = False", orderby="timestamp DESC")
elif query_filter == "today":
today = datetime.date.today()
today = time.mktime(today.timetuple())
error_dict = common_q.get_all(
cursor, error.Error, where="timestamp >= %d AND fixed = False" % today, orderby="timestamp DESC"
)
else:
error_dict = common_q.get_all(
cursor,
error.Error,
where="mode='%s' and fixed = False" % database_f.escape(query_filter),
orderby="timestamp DESC",
)
output = []
if len(error_dict) > 0:
user_dict = common_q.get_all(cursor, user.User)
user_dict[-1] = user.User(username="******")
output.append(
"""<table border="0" cellspacing="0" cellpadding="5" style="width:100%;">
<tr class="row2">
<th>Date</th>
<th>Mode</th>
<th>Func Call</th>
<th>Type</th>
<th>User</th>
<th colspan="2"> </th>
</tr>
"""
)
i = 1
for error_id, the_error in error_dict.items():
i += 1
the_date = the_error.timestamp
output.append(
"""
<tr class="row{row}" id="row{error_id}">
<td>{date}</td>
<td>{mode}</td>
<td>{function_call}</td>
<td>{etype}</td>
<td>{user}</td>
<td class="block_cell"><a href="web.py?mode=edit_error&error={error_id}">View</a></td>
<td class="block_cell"><a href="#" onclick="{onclick}">Fix</a></td>
</tr>""".format(
error_id=error_id,
row=i % 2,
etype=the_error.exception_type,
date=common_f.display_date(the_date, "%d of %B at %H:%M"),
mode="No mode specified" if the_error.mode == "" else the_error.mode,
function_call="" if the_error.function_call == "" else the_error.function_call,
user=user_dict[the_error.user_id].username,
onclick="""$('#ajax_target').load('web.py', {'mode':'edit_error', 'error':'%d', 'sub_mode':'fix'}); $('#row%d').hide(); return false;"""
% (error_id, error_id),
)
)
output.append("</table>")
else:
output.append("<div style='padding:10px;'>No errors found</div>")
modes = {}
# Select all the groups possible
query = """SELECT mode FROM errors GROUP BY mode"""
try:
cursor.execute(query)
except Exception as e:
raise Exception("Database error: %s\nQuery: %s" % (str(e.args[0]).replace("\n", ""), query))
for row in cursor:
modes[row["mode"]] = row["mode"]
page_data["Rows"] = len(error_dict)
page_data["Title"] = "Error list (%d)" % len(error_dict)
page_data[
"Filters"
] = """
<form action="web.py" method="get" accept-charset="utf-8" style="float: left;">
<a href="web.py?mode=list_errors">All errors</a>
<a href="web.py?mode=list_errors&filter=today">Todays errors</a>
<input type="hidden" name="mode" value="list_errors" />
{}
<input type="submit" value="Sort by mode" />
</form>
""".format(
html_f.option_box("filter", modes, selected=query_filter)
)
return "".join(output)