def query_handler(environ, start_response):
setupStderr(environ["wsgi.errors"])
params = dict(parse_qsl(environ.get("QUERY_STRING", "")))
try:
db_connection = db.connect()
try:
results = db.query(db_connection, *query(**params), dict_result=True)
total = db.query(db_connection, "SELECT FOUND_ROWS()")[0][0]
finally:
db_connection.close()
except MySQLdb.Error:
traceback.print_exc()
return common.show_error("Failed to query database!", start_response,
"500 Database error")
try:
echo = int(params["echo"])
except (ValueError, KeyError):
echo = 0
response_headers = [("Content-type", "application/json; charset=utf-8")]
start_response("200 OK", response_headers)
return [json.dumps({"results": results, "echo": echo,
"total": total, "count": len(results)},
ensure_ascii=False).encode("utf-8")]
def test_submit(self):
self.assertEqual(len(db.query(self.db, "SELECT * FROM frequencies")), 0)
self.assertEqual(len(db.query(self.db, "SELECT * FROM filters")), 0)
# Ensure missing or invalid JSON results in an error
self.assertResponse(submit_handler, "400 Processing Error",
REQUEST_METHOD="POST", body="")
self.assertResponse(submit_handler, "400 Processing Error",
REQUEST_METHOD="POST", body="Oops...")
self.assertResponse(submit_handler, "400 Processing Error",
REQUEST_METHOD="POST", body="{123:]")
self.assertResponse(submit_handler, "400 Processing Error",
REQUEST_METHOD="POST", body="1")
self.assertEqual(len(db.query(self.db, "SELECT * FROM frequencies")), 0)
self.assertEqual(len(db.query(self.db, "SELECT * FROM filters")), 0)
# Ensure even an empty object, or one with the wrong fields returns successfully
self.assertResponse(submit_handler, "204 No Content",
REQUEST_METHOD="POST", body="{}")
self.assertResponse(submit_handler, "204 No Content",
REQUEST_METHOD="POST", body="{\"hello\": \"world\"}")
self.assertEqual(len(db.query(self.db, "SELECT * FROM frequencies")), 0)
self.assertEqual(len(db.query(self.db, "SELECT * FROM filters")), 0)
# Now some actually valid data
self.assertResponse(submit_handler, "204 No Content",
REQUEST_METHOD="POST", body=valid_data)
self.assertEqual(len(db.query(self.db, "SELECT * FROM frequencies")), 2)
self.assertEqual(len(db.query(self.db, "SELECT * FROM filters")), 1)
# Now make sure apparently valid data with timestamps that cause geometrical
# mean calculations to fail with MySQL errors return OK but don't change DB
invalid_data = json.loads(valid_data)
invalid_data["filters"]["||example.com^"]["firstParty"]["example.com"]["latest"] = 3
invalid_data = json.dumps(invalid_data)
self.assertResponse(submit_handler, "204 No Content",
REQUEST_METHOD="POST", body=invalid_data)
self.assertEqual(len(db.query(self.db, "SELECT * FROM frequencies")), 2)
self.assertEqual(len(db.query(self.db, "SELECT * FROM filters")), 1)
def query_handler(environ, start_response):
setupStderr(environ["wsgi.errors"])
params = dict(parse_qsl(environ.get("QUERY_STRING", "")))
try:
db_connection = db.connect()
try:
results = db.query(db_connection, *query(**params), dict_result=True)
total = db.query(db_connection, "SELECT FOUND_ROWS()")[0][0]
finally:
db_connection.close()
except MySQLdb.Error:
traceback.print_exc()
return common.show_error("Failed to query database!", start_response,
"500 Database error")
try:
echo = int(params["echo"])
except (ValueError, KeyError):
echo = 0
response_headers = [("Content-type", "application/json; charset=utf-8")]
start_response("200 OK", response_headers)
return [json.dumps({"results": results, "echo": echo,
"total": total, "count": len(results)},
ensure_ascii=False).encode("utf-8")]
def test_submit(self):
self.assertEqual(len(db.query(self.db, "SELECT * FROM frequencies")),
0)
self.assertEqual(len(db.query(self.db, "SELECT * FROM filters")), 0)
# Ensure missing or invalid JSON results in an error
self.assertResponse(submit_handler,
"400 Processing Error",
REQUEST_METHOD="POST",
body="")
self.assertResponse(submit_handler,
"400 Processing Error",
REQUEST_METHOD="POST",
body="Oops...")
self.assertResponse(submit_handler,
"400 Processing Error",
REQUEST_METHOD="POST",
body="{123:]")
self.assertResponse(submit_handler,
"400 Processing Error",
REQUEST_METHOD="POST",
body="1")
self.assertEqual(len(db.query(self.db, "SELECT * FROM frequencies")),
0)
self.assertEqual(len(db.query(self.db, "SELECT * FROM filters")), 0)
# Ensure even an empty object, or one with the wrong fields returns successfully
self.assertResponse(submit_handler,
"204 No Content",
REQUEST_METHOD="POST",
body="{}")
self.assertResponse(submit_handler,
"204 No Content",
REQUEST_METHOD="POST",
body="{\"hello\": \"world\"}")
self.assertEqual(len(db.query(self.db, "SELECT * FROM frequencies")),
0)
self.assertEqual(len(db.query(self.db, "SELECT * FROM filters")), 0)
# Now some actually valid data
self.assertResponse(submit_handler,
"204 No Content",
REQUEST_METHOD="POST",
body=valid_data)
self.assertEqual(len(db.query(self.db, "SELECT * FROM frequencies")),
2)
self.assertEqual(len(db.query(self.db, "SELECT * FROM filters")), 1)
# Now make sure apparently valid data with timestamps that cause geometrical
# mean calculations to fail with MySQL errors return OK but don't change DB
invalid_data = json.loads(valid_data)
invalid_data["filters"]["||example.com^"]["firstParty"]["example.com"][
"latest"] = 3
invalid_data = json.dumps(invalid_data)
self.assertResponse(submit_handler,
"204 No Content",
REQUEST_METHOD="POST",
body=invalid_data)
self.assertEqual(len(db.query(self.db, "SELECT * FROM frequencies")),
2)
self.assertEqual(len(db.query(self.db, "SELECT * FROM filters")), 1)
def test_query_and_write(self):
insert_sql = """INSERT INTO `filters` (filter, sha1)
VALUES (%s, UNHEX(SHA1(filter)))"""
select_sql = "SELECT filter FROM filters ORDER BY filter ASC"
# Table should be empty to start with
self.assertEqual(db.query(self.db, select_sql), ())
# Write some data and query it back
db.write(self.db, ((insert_sql, "something"),))
self.assertEqual(db.query(self.db, select_sql), ((u"something",),))
# Write an array of SQL strings
db.write(self.db, ((insert_sql, "a"), (insert_sql, "b"), (insert_sql, "c")))
self.assertEqual(db.query(self.db, select_sql), ((u"a",), (u"b",), (u"c",), (u"something",)))
# Write a sequence of SQL but roll back when a problem arrises
with self.assertRaises(MySQLdb.ProgrammingError):
db.write(self.db, ((insert_sql, "f"), (insert_sql, "g"), (insert_sql, "h"),
("GFDGks",)))
self.assertEqual(db.query(self.db, select_sql), ((u"a",), (u"b",), (u"c",), (u"something",)))
def test_calculations(self):
interval = 86400
# Tables should be empty to start with
self.assertEqual(db.query(self.db, "SELECT * FROM filters"), ())
self.assertEqual(db.query(self.db, "SELECT * FROM frequencies"), ())
# First batch
db.write(self.db, geometrical_mean.update(interval, test_data[0]))
self.assertEqual(db.query(self.db, "SELECT * FROM filters"),
(("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"),
u"##.top-box-right-ad"),))
self.assertEqual(
db.query(self.db, "SELECT * FROM frequencies"),
(("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"acxiom-online.com",
6L, datetime.utcfromtimestamp(1414817340948 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"demdex.net",
36L, datetime.utcfromtimestamp(1414838712373 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"doubleclick.net",
26L, datetime.utcfromtimestamp(1414823430333 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"google.com",
50L, datetime.utcfromtimestamp(1414849084678 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"yahoo.com",
14L, datetime.utcfromtimestamp(1414859271125 / 1000))))
# Second batch
db.write(self.db, geometrical_mean.update(interval, test_data[1]))
self.assertEqual(db.query(self.db, "SELECT * FROM filters"),
(("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"),
u"##.top-box-right-ad"),))
self.assertEqual(
db.query(self.db, "SELECT * FROM frequencies"),
(("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"acxiom-online.com",
6L, datetime.utcfromtimestamp(1414817340948 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"adsymptotic.com",
49L, datetime.utcfromtimestamp(1414953943015 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"amazon.com",
2L, datetime.utcfromtimestamp(1414913563746 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"demdex.net",
36L, datetime.utcfromtimestamp(1414838712373 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"doubleclick.net",
26L, datetime.utcfromtimestamp(1414823430333 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"google.com",
self.geometrical(interval, 21, 1414953920364, 50, 1414849084678),
datetime.utcfromtimestamp(1414953920364 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"live.com",
34L, datetime.utcfromtimestamp(1414916268769 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"yahoo.com",
self.geometrical(interval, 27, 1414917270343, 14, 1414859271125),
datetime.utcfromtimestamp(1414917270343 / 1000))))
# Third batch
db.write(self.db, geometrical_mean.update(interval, test_data[2]))
self.assertEqual(db.query(self.db, "SELECT * FROM filters"),
(("22de8d2ba8429eb170a0ece6ea7a426f7b22e574".decode("hex"),
u"stevedeace.com##.topAddHolder"),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"),
u"##.top-box-right-ad")))
self.assertEqual(
db.query(self.db, "SELECT * FROM frequencies"),
(("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"acxiom-online.com",
6L, datetime.utcfromtimestamp(1414817340948 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"adsymptotic.com",
self.geometrical(interval, 15, 1414994112862, 49, 1414953943015),
datetime.utcfromtimestamp(1414994112862 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"amazon.com",
2L, datetime.utcfromtimestamp(1414913563746 / 1000)),
("22de8d2ba8429eb170a0ece6ea7a426f7b22e574".decode("hex"), u"amazonaws.com",
18L, datetime.utcfromtimestamp(1414977342966 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"demdex.net",
36L, datetime.utcfromtimestamp(1414838712373 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"doubleclick.net",
26L, datetime.utcfromtimestamp(1414823430333 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"google.com",
self.geometrical(interval, 14, 1415008533089,
self.geometrical(interval, 21, 1414953920364,
50, 1414849084678),
1414953920364),
datetime.utcfromtimestamp(1415008533089 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"live.com",
34L, datetime.utcfromtimestamp(1414916268769 / 1000)),
("22de8d2ba8429eb170a0ece6ea7a426f7b22e574".decode("hex"), u"mathtag.com",
14L, datetime.utcfromtimestamp(1415032601175 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"yahoo.com",
self.geometrical(interval, 43, 1415045194098,
self.geometrical(interval, 27, 1414917270343,
14, 1414859271125),
1414917270343),
datetime.utcfromtimestamp(1415045194098 / 1000))))
def test_calculations(self):
interval = 86400
# Tables should be empty to start with
self.assertEqual(db.query(self.db, "SELECT * FROM filters"), ())
self.assertEqual(db.query(self.db, "SELECT * FROM frequencies"), ())
# First batch
db.write(self.db, geometrical_mean.update(interval, test_data[0]))
self.assertEqual(db.query(self.db, "SELECT * FROM filters"),
(("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"),
u"##.top-box-right-ad"),))
self.assertEqual(
db.query(self.db, "SELECT * FROM frequencies"),
(("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"acxiom-online.com",
6L, datetime.utcfromtimestamp(1414817340948 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"demdex.net",
36L, datetime.utcfromtimestamp(1414838712373 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"doubleclick.net",
26L, datetime.utcfromtimestamp(1414823430333 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"google.com",
50L, datetime.utcfromtimestamp(1414849084678 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"yahoo.com",
14L, datetime.utcfromtimestamp(1414859271125 / 1000))))
# Second batch
db.write(self.db, geometrical_mean.update(interval, test_data[1]))
self.assertEqual(db.query(self.db, "SELECT * FROM filters"),
(("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"),
u"##.top-box-right-ad"),))
self.assertEqual(
db.query(self.db, "SELECT * FROM frequencies"),
(("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"acxiom-online.com",
6L, datetime.utcfromtimestamp(1414817340948 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"adsymptotic.com",
49L, datetime.utcfromtimestamp(1414953943015 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"amazon.com",
2L, datetime.utcfromtimestamp(1414913563746 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"demdex.net",
36L, datetime.utcfromtimestamp(1414838712373 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"doubleclick.net",
26L, datetime.utcfromtimestamp(1414823430333 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"google.com",
self.geometrical(interval, 21, 1414953920364, 50, 1414849084678),
datetime.utcfromtimestamp(1414953920364 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"live.com",
34L, datetime.utcfromtimestamp(1414916268769 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"yahoo.com",
self.geometrical(interval, 27, 1414917270343, 14, 1414859271125),
datetime.utcfromtimestamp(1414917270343 / 1000))))
# Third batch
db.write(self.db, geometrical_mean.update(interval, test_data[2]))
self.assertEqual(db.query(self.db, "SELECT * FROM filters"),
(("22de8d2ba8429eb170a0ece6ea7a426f7b22e574".decode("hex"),
u"stevedeace.com##.topAddHolder"),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"),
u"##.top-box-right-ad")))
self.assertEqual(
db.query(self.db, "SELECT * FROM frequencies"),
(("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"acxiom-online.com",
6L, datetime.utcfromtimestamp(1414817340948 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"adsymptotic.com",
self.geometrical(interval, 15, 1414994112862, 49, 1414953943015),
datetime.utcfromtimestamp(1414994112862 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"amazon.com",
2L, datetime.utcfromtimestamp(1414913563746 / 1000)),
("22de8d2ba8429eb170a0ece6ea7a426f7b22e574".decode("hex"), u"amazonaws.com",
18L, datetime.utcfromtimestamp(1414977342966 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"demdex.net",
36L, datetime.utcfromtimestamp(1414838712373 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"doubleclick.net",
26L, datetime.utcfromtimestamp(1414823430333 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"google.com",
self.geometrical(interval, 14, 1415008533089,
self.geometrical(interval, 21, 1414953920364,
50, 1414849084678),
1414953920364),
datetime.utcfromtimestamp(1415008533089 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"live.com",
34L, datetime.utcfromtimestamp(1414916268769 / 1000)),
("22de8d2ba8429eb170a0ece6ea7a426f7b22e574".decode("hex"), u"mathtag.com",
14L, datetime.utcfromtimestamp(1415032601175 / 1000)),
("8c5ea548436c61f05536e205a29ada6204f603b0".decode("hex"), u"yahoo.com",
self.geometrical(interval, 43, 1415045194098,
self.geometrical(interval, 27, 1414917270343,
14, 1414859271125),
1414917270343),
datetime.utcfromtimestamp(1415045194098 / 1000))))
