def test_pageview_insert(self):
dmn = 'www.test.com'
turl = 'http://www.test.com'
PageView.create(
domain=dmn,
url=turl,
headers='',
params=''
)
self.assertTrue(PageView.select().count() > 0)
def test_pageview_select(self):
dmn = 'example.com'
turl = 'http://www.test.com'
PageView.create(
domain=dmn,
url=turl,
title='index',
ip='127.0.0.1',
headers='',
params=''
)
self.assertTrue(PageView.select(PageView.title).where(PageView.title=='index').count() > 0)
self.assertFalse(PageView.select(PageView.ip).where((PageView.ip=='0.0.0.9') & (PageView.title=='index')).count() > 0)
def get_paths(query, limit):
inner = (query.select(PageView.ip,
PageView.url).order_by(PageView.timestamp))
paths = (PageView.select(PageView.ip, fn.GROUP_CONCAT(PageView.url)).from_(
inner.alias('t1')).group_by(PageView.ip).order_by(
fn.COUNT(PageView.url).desc()).tuples().limit(limit))
return [(ip, urls.split(',')) for ip, urls in paths]
def get_query(start, end):
query = PageView.select()
if start and end:
query = query.where(PageView.timestamp.between(start, end))
elif start:
query = query.where(PageView.timestamp >= start)
elif end:
query = query.where(PageView.timestamp <= end)
return query
#!/usr/bin/env python
"""
This is just a demonstration script that performs a handful of queries on
the analytics database.
"""
from analytics import PageView, RefererHost
from analytics import url_index
# Print all URLs viewed in the ordered in which they were viewed.
print 'All URLs:'
for pageview in PageView.all():
print pageview['pageview']['url']
# Query for URLs that contained "sqlite" in the path and print the
# corresponding title.
print
print 'URLs containing "sqlite":'
for pageview in url_index.query('%sqlite%', 'LIKE'):
print pageview['pageview']['title']
# Query for the referer hostnames and list the URLs visited.
print
print 'Referer host and URL:'
for referer_host in RefererHost.all():
print referer_host['data']['referer_host'], referer_host['data']['url']
