100%[====================================>] 41,227,814 86.84K/s ETA 00:00

09:53:18 (83.35 KB/s) - `/Users/gubatron/clicktrackr/clicktrackr_dump.sql.tar.gz' saved [41227814/41227814]

Uncompressing Dump...
Loading data in MySQL
/Users/gubatron/bin/clicktrackr_processing: line 12: clicktrackr_dump.sql: No such file or directory
Downloading latest tracker.log
--09:53:20-- http://www.wedoit4you.com/xxxxxxxxxxxxxx/tracker.log
=> `/Users/gubatron/clicktrackr/tracker.log'
Resolving www.wedoit4you.com... 208.113.146.143
Connecting to www.wedoit4you.com|208.113.146.143|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 3,220 [text/plain]

100%[====================================>] 3,220 --.--K/s

09:53:23 (33.72 KB/s) - `/Users/gubatron/clicktrackr/tracker.log' saved [3220/3220]

#!/home/wedoit4y/bin/python/bin/python2.5
# This is the script that processes the ClickTrackr Log
import os
import sys
import pickle
import time

#NAMES OF FILES WHERE WE'LL STORE THE DIFFERENT STAGES OF RETRIEVED
#AND PROCESSED DATA.

#File that holds a dictionary with URLs and HITs we got from the original log file
FILE_MAX_AGE=3600*1
FILE_TIMESTAMP="clicktrackr_last_timestamp.dat"
FILE_001="clicktrackr_001_url_hits.dat"
FILE_002="clicktrackr_002_blogs_lastposts.dat"
FILE_003="clicktrackr_003_posts_hits.dat"
FILE_004="clicktrackr_004_processed_hits.dat" #...and urls not found
FILE_SQL="clicktrackr_update_tables.sql"

try:
    import snowrss_config
    from snowrss_config import getDbCursor
    #from snowrss import *
except Exception,e:
    print "Could not import snowrss_config [%s]" % e
    sys.exit()

def dbExec(sql):
    """Give it some SQL and it will return the returning cursor"""
    try:
        cursor = getDbCursor()
        cursor.execute(sql)
        cursor.connection.close()
    except Exception, e:
        #MySQL has gone away
        print 'dbExec(%s): ' % unicode(sql)
        print e
        return None
    return cursor

def isFileFresh(fileName):
    """
    Returns True if the file is still good to be used.
    Othewise returns false
    """
    try:
        file_stat = os.stat(fileName)
        file_age = time.time() - file_stat.st_mtime
        if file_age > FILE_MAX_AGE:
            return False
        return True
    except:
        return False


def getData(line):
    """Returns a dict with, IP, Timestamp, URL and User Agent if found

    Parameters
        line - A Line with a ClickTracker log entry

    Output
        {'ip':...,'timestamp':...,'url':....,'ua':...}
        ip-> IP Addres
        time -> Time of the event
        url -> Referer Url
        ua -> User Agent of the rerferer user
    """
    l = line.split()
    result = {}
    result['ip']=l[0]
    result['time']=l[1]
    result['url']=l[2]

    result['ua']='N/A'
    if len(l)>3:
        rest = l[3:]
        ua_name = ''
        for b in rest:
            ua_name = ua_name + ' ' + b
        result['ua'] = ua_name

    return result

#Maximum time to count a click from the same IP on the same URL
TIME_BETWEEN_CLICKS = 12*3600

#On the last run (if finished, we write down the time of the last timestamp on file)
#If we did finish a run, we'll get this number from the timestamp file, and we'll ignore
#all previous log entries to that timestamp.
LAST_TIMESTAMP = None
POSSIBLE_LAST_TIMESTAMP = None

try:
    f = fopen(FILE_TIMESTAMP,"rb")
    LAST_TIMESTAMP = pickle.load(f)
    LAST_TIMESTAMP = long(LAST_TIMESTAMP)
    f.close()
except:
    print "No timestamp from last time found."

urls = {}
urls_not_found = {}
LOG_CLICK_TRACKER='tracker.log'

#check if there is a version of the log file backed that's still good enough to be used.
USABLE_LOG_FILE = LOG_CLICK_TRACKER

#use a copy of the log if we got some pickled data
if isFileFresh(LOG_CLICK_TRACKER + '.last') and isFileFresh(FILE_001):
    USABLE_LOG_FILE = LOG_CLICK_TRACKER + ".last"

IGNORED_ENTRIES = 0
if not isFileFresh(FILE_001):

    #open the tracker log (current or old)
    print "Loading data from ClickTrackr log..."
    f = open(USABLE_LOG_FILE,'r')

    f.seek(0,2)
    eof = f.tell()
    f.seek(0)

    while f.tell() < eof:
        entry = getData(f.readline())

        url = entry['url']
        ip = entry['ip']
        timestamp = entry['time']

        if LAST_TIMESTAMP is not None and long(timestamp) < LAST_TIMESTAMP:
            print "i",
            IGNORED_ENTRIES += 1
            continue

        POSSIBLE_LAST_TIMESTAMP = long(timestamp)

        if not url.startswith('http') or 
           url.startswith('http://babelfish.altavista.com') or 
           url.startswith('http://6'):
            #IGNORED_ENTRIES += 1
            continue

        #Ask if this URL is already there
        if urls.has_key(url):
            #Ask if this IP is already there
            if urls[url].has_key(ip):
                #Get the last time stamp inside this IP
                times = urls[url][ip]
                last_time = times[len(times)-1]
                delta_time = long(timestamp) - long(last_time)
                #If its been more than acceptable time
                if delta_time >= TIME_BETWEEN_CLICKS:
                    urls[url][ip].append(timestamp)

                    hits = 0
                    for ipbuffer in urls[url]:
                        if ipbuffer == 'hits': #just count the keys that are not 'hits'
                            continue
                        hits += len(urls[url][ipbuffer])

                    urls[url]['hits'] = hits
            else:
                urls[url][ip] = [timestamp]
                urls[url]['hits'] = 1
        else:
            urls[url]={}
            urls[url][ip] = [timestamp]
            urls[url]['hits']=1
    f.close()

    urls['POSSIBLE_LAST_TIMESTAMP'] = POSSIBLE_LAST_TIMESTAMP

    #we serialize this data for later
    if IGNORED_ENTRIES > 0:
        print "Ignored %d entries." % IGNORED_ENTRIES

    print "Saving ClickTrackr data to File..."
    f = file(FILE_001,"wb")
    pickle.dump(urls,f)
    f.close()
    print "Saving completed."

    #we make a backup of the current ClickTrackr log (.last), in case we need to run again
    #we can diff with this to know from where to relog in the future
    os.system("cp %s %s" % (LOG_CLICK_TRACKER,LOG_CLICK_TRACKER + ".last"))
else:
    #we unserialize the data
    print "Loading ClickTrackr data from existing file..."
    f = file(FILE_001,"rb")
    urls = pickle.load(f)
    f.close()
    POSSIBLE_LAST_TIMESTAMP = urls.pop('POSSIBLE_LAST_TIMESTAMP') #we popup so we have only urls and we dont modify further down
    print "Loading completed."

#LOAD ALL BLOG POST URLS, IDS AND CURRENT NUMBER OF HITS.
blog_urls = {} #blogs hashed by their urls, Buckets have {'post_id':,'post_link':}
blog_ids = {} #blogs hashed by their ids, Buckets have {'post_id':,'post_link':}
if not isFileFresh(FILE_002):
    print "Loading Blogs and Last Posts from DB"
    sql = "SELECT Blog_pk_id, Blog_url FROM BLOGS WHERE Blog_active=1;"
    cursor = dbExec(sql)
    results = cursor.fetchall()

    for r in results:
        #Get the ID of the last post on each blog"
        sql = u"SELECT BP_pk_id,BP_link FROM BLOG_POSTS WHERE BP_fk_blog_id = %d ORDER BY BP_pk_id DESC LIMIT 1" % (r['Blog_pk_id']);
        cursor = dbExec(sql)
        last_post = cursor.fetchone()

        if last_post:
            blog_urls[r['Blog_url']] = {'post_id':last_post['BP_pk_id'],'post_link':last_post['BP_link']}
            blog_ids[r['Blog_pk_id']] = {'post_id':last_post['BP_pk_id'],'post_link':last_post['BP_link']}

    #serialize blog_urls and blog_ids
    print "Saving Blogs to File..."
    f = file(FILE_002,"wb")
    pickle.dump(blog_urls,f)
    pickle.dump(blog_ids,f)
    f.close()
    print "Saving completed."
else:
    #load blog_urls from serialized data
    print "Loading Blogs and Last Posts from File..."
    f = file(FILE_002,"rb")
    blog_urls = pickle.load(f)
    blog_ids = pickle.load(f)
    f.close()
    print "Loading completed."

#LOAD ALL BLOG_POSTS URL AND ITS HITS
post_hits = {} #posts hashed by url, Buckets have (post_id, post_hits, blog_id)
if not isFileFresh(FILE_003):
    print "Loading Posts from DB..."
    sql = "SELECT SQL_CACHE BP_link, BP_pk_id, BP_fk_blog_id, PH_hits "
    sql += "FROM BLOG_POSTS LEFT JOIN POST_HITS ON BP_pk_id = PH_fk_post_id;"
    cursor = dbExec(sql)
    results = cursor.fetchall()

    for r in results:
        #hits might be null, if the post has never been reached on our page
        hit_count = int(r['PH_hits']) if r['PH_hits'] is not None else 0
        post_hits[r['BP_link']] = {'post_id':int(r['BP_pk_id']),
                                   'post_hits':hit_count,
                                   'blog_id':r['BP_fk_blog_id']}

    #now get the blog_posts
    print "Saving Posts from DB on file"
    f = file(FILE_003,"wb")
    pickle.dump(post_hits,f)
    f.close()
    print "Saving completed."
else:
    print "Loading Posts from File..."
    f = file(FILE_003,"rb")
    post_hits = pickle.load(f)
    f.close()
    print "Loading completed."


# The stars of the game are:
# - urls {:{'ip':,'hits':}} //The urls and how many hits we got from the click trackr log
# - blog_urls {:{'post_id':,'post_link':}} //urls of blogs, holding each a tuple with last post info
# - post_hits {:{'post_id':,'post_hits':,'blog_id':]} //urls and hit info of all posts
# - urls_not_found {'':}
if not isFileFresh(FILE_004):
    total_not_found = 0
    distinct_not_found = 0
    total_converted = 0

    converting_blog_url_to_post_url = False

    print "Crunching data..."
    for url in urls:
	#if the current url is the home of a blog
        #we try to see if the blog has any hits.
	if blog_urls.has_key(url):
            url = blog_urls[url]['post_link']
            converting_blog_url_to_post_url = True

        #if you find a direct match add the hits right away
        if post_hits.has_key(url):
	    new_hits = 0
	    if urls.has_key(url) and urls[url].has_key('hits'):
                new_hits = urls[url]['hits']
                if converting_blog_url_to_post_url:
                    total_converted+=1
                    print "!",
            
            old_hits = 0
            if post_hits[url].has_key('post_hits'):
                old_hits = post_hits[url]['post_hits']

            total_hits = new_hits + old_hits

            #finally update post_hits arrays.
            post_hits[url]['post_hits'] = total_hits
            str_a = "(%(post_id)d):%(post_hits)d:" % post_hits[url] 
            str_b = "%d+%d)" % (new_hits,old_hits)
            str_c = str_a + str_b
            print str_c,
        else:
            if urls_not_found.has_key(url):
                urls_not_found[url] += 1
            else:
                urls_not_found[url]=1
                distinct_not_found +=1
            total_not_found += 1
            print "-",

    print
    print "%d converted from blog to last post" % total_converted
    print "Didnt find total %d urls." % total_not_found
    print "Didn't find distinct %d urls." % distinct_not_found

    #serialize processed data in file 4
    print "Saving crunched data..."
    f = file(FILE_004,"wb")
    pickle.dump(post_hits,f)
    pickle.dump(urls_not_found,f)
    f.close()
    print "Data saved."
else:
    print "Loading Previously Crunched Data..."
    f = file(FILE_004,"rb")
    post_hits = pickle.load(f)
    urls_not_found = pickle.load(f)
    f.close
    print "Loading completed."


    #If we can't find it on the blog posts, we could try to
    #slim the URL of this url too http://servername.com/folder
    #and look up on the blog url

    #if nothing, we slim down to http://servername.com

    #if in any of these 2 cases we find a match then
    #we add a hit on the last post of this blog

    #the output of this file should be for now a file with SQL insert statements
print len(post_hits)

#Generate SQL output from post_hits array
# - post_hits {:{'post_id':,'post_hits':,'blog_id':]}
print "Writing SQL..."
f = file(FILE_SQL,"wb")
for url in post_hits:
    post_id = post_hits[url]['post_id']
    hits = post_hits[url]['post_hits']
    f.writelines("UPDATE POST_HITS SET PH_hits = %d WHERE PH_fk_post_id = %d;n" % (hits,post_id))
f.close()
print "Finished Writing SQL"

#If we make it all the way till here, we write down the new LAST_TIMESTAMP
if POSSIBLE_LAST_TIMESTAMP is not None:
    f = file(FILE_TIMESTAMP,"wb")
    pickle.dump(POSSIBLE_LAST_TIMESTAMP,f)
    f.close()
    print "Wrote last timestamp."
else:
    print "Did not write LAST timestamp."

for u in urls_not_found:
    print u