1 """This module has utility functions that work over the database
2 """
3
4 from parse import Parser
5 import sqlite3
6 from parse import dbfile
7 import datetime
8 import re
9
10
12 """Returns list of top *count* tuples in tuple(page,hits) format
13 @param count:size of list to be returned
14 @return: list
15 """
16 conn=sqlite3.connect(dbfile())
17 query="""select request_path,c from(select request_path,count(*) as c from serverlog group by request_path) order by c desc limit %s;"""%(count)
18 cur=conn.execute(query)
19 hitList= [t for t in cur]
20 conn.close()
21 return hitList
22
24 """Gives list of dead links at hostname in tuple(linked_by,linked_to) format
25 @param hostname:hostname of the machine which created the the log file in http://hostname/ format
26 @return:list
27 """
28 conn=sqlite3.connect(dbfile())
29 query="""select distinct referrer,request_path from serverlog where status_code='404' and referrer<>'-' and referrer like '%s%%';"""%hostname
30 cur=conn.execute(query)
31 deadlinks=cur.fetchall()
32 conn.close()
33 return deadlinks
34
36 """Get the date of most recent entry in the logfile
37 @return: string
38 """
39 conn=sqlite3.connect(dbfile())
40 query='select max(requestdate) from serverlog;'
41 cur=conn.execute(query)
42 lastdate=None
43 for t in cur:
44 lastdate=t
45 conn.close()
46 return lastdate[0]
47
48
49
51 """A funtion made for the internal use. It created the list of dates having diffrence of a week
52 @return: list
53 """
54 lastDate = lastRecordDate()
55 lastDate=datetime.datetime.strptime(lastDate,'%Y-%m-%d %H:%M:%S')
56 lastDate+=datetime.timedelta(days=1)
57 tdelta=datetime.timedelta(days=7)
58
59 l=[lastDate.strftime('%Y-%m-%d %H:%M:%S')]
60 for x in range(10):
61 lastDate-=tdelta
62 l.append(lastDate.strftime('%Y-%m-%d %H:%M:%S'))
63
64 return l
65
66
67
68
69 -def getHitsByPage(page):
70 """Gives the list of number of hits to the particular page in the log file in last ten weeks
71
72 @param page: absolute address of the required page
73 @return: list
74 """
75 weekenddate=__calcWeekDates__()[::-1]
76
77 weekIntervals= [ (weekenddate[x-1],weekenddate[x]) for x in range(1,len(weekenddate))]
78
79 query="""select count(*) from serverlog where request_path='%s' and requestdate between '%s' and '%s';"""%(page,'%s','%s')
80 conn=sqlite3.connect(dbfile())
81 hits=[]
82 for interval in weekIntervals:
83 cur=conn.execute(query%interval)
84 hits.append(cur.fetchone()[0])
85
86 conn.close()
87 return (hits,weekIntervals)
88
90 conn=sqlite3.connect(dbfile())
91 queryGglBing="""select referrer from serverlog where referrer<>'-'"""
92 cur=conn.execute(queryGglBing)
93 keywords=[]
94
95 reRef=re.compile(r'.*\.(google|bing)\..*/search\?(.*)')
96 for t in cur:
97 match=reRef.match(t[0])
98 if match:
99 lparam= match.group(2).split('&')
100 d=dict([l.split('=') for l in lparam])
101 kws=d['q'].replace('+',' ')
102
103 keywords.append(kws)
104
105
106
107
108 queryYahoo="""select referrer from serverlog where referrer<>'-'"""
109 cur=conn.execute(queryGglBing)
110 reRef=re.compile(r'.*\.yahoo\..*/search\?(.*)')
111
112 for t in cur:
113 match=reRef.match(t[0])
114 if match:
115 lparam= match.group(1).split('&')
116 d=dict([l.split('=') for l in lparam])
117 kws=d['p'].replace('+',' ')
118
119 keywords.append(kws)
120 return keywords
121