def pull_download_history(history_db, user, profile, downloads_output,
downloads_headers):
desired_columns = ['url', 'content', 'dateAdded']
available_columns = get_column_headers(
history_db, 'moz_annos') + get_column_headers(history_db, 'moz_places')
query_columns_list = [i for i in desired_columns if i in available_columns]
query_columns = ', '.join(
[i for i in desired_columns if i in available_columns])
unavailable = ','.join(
list(set(desired_columns) - set(query_columns_list)))
if len(unavailable) > 0:
log.debug(
'The following desired columns are not available in the database: {0}'
.format(unavailable))
log.debug("Executing sqlite query for download history...")
try:
downloads_data = sqlite3.connect(history_db).cursor().execute(
'SELECT url,group_concat(content),dateAdded,lastModified FROM moz_annos \
LEFT JOIN moz_places ON moz_places.id = moz_annos.place_id \
GROUP BY place_id').fetchall()
log.debug("Success. Found {0} lines of data.".format(
len(downloads_data)))
except sqlite3.OperationalError:
error = [
x for x in traceback.format_exc().split('\n')
if x.startswith("OperationalError")
]
log.error('Failed to run query. [{0}]'.format(error[0]))
return
log.debug("Parsing and writing downloads data...")
for item in downloads_data:
record = OrderedDict((h, '') for h in downloads_headers)
record['user'] = user
record['profile'] = profile
record['download_url'] = item[0]
record['download_path'] = item[1].split(',')[0]
record['download_started'] = firefox_time(item[2])
record['download_finished'] = firefox_time(item[3])
if len(item[1].split(',')) >= 4:
record['download_totalbytes'] = item[1].split(',')[3].split(
':')[1].replace('}', '')
else:
record['download_totalbytes'] = 'N/A'
downloads_output.write_entry(record.values())
log.debug("Done.")
def pull_visit_history(history_db, user, profile, urls_output, urls_headers):
desired_columns = ['visit_date','title','url','visit_count','typed','last_visit_date','description']
available_columns = get_column_headers(history_db,'moz_places') + get_column_headers(history_db,'moz_historyvisits')
query_columns_list = [i for i in desired_columns if i in available_columns]
query_columns = ', '.join([i for i in desired_columns if i in available_columns])
unavailable = ','.join(list(set(desired_columns) - set(query_columns_list)))
if len(unavailable) > 0:
log.debug('The following desired columns are not available in the database: {0}'.format(unavailable))
log.debug("Executing sqlite query for visit history...")
try:
urls_data = sqlite3.connect(history_db).cursor().execute(
'SELECT {0} FROM moz_historyvisits left join moz_places \
on moz_places.id = moz_historyvisits.place_id'.format(query_columns)
).fetchall()
log.debug("Success. Found {0} lines of data.".format(len(urls_data)))
except sqlite3.OperationalError:
error = [x for x in traceback.format_exc().split('\n') if x.startswith("OperationalError")]
log.error('Failed to run query. [{0}]'.format(error[0]))
return
log.debug("Parsing and writing visits data...")
nondict = dict.fromkeys(desired_columns)
for item in urls_data:
record = OrderedDict((h, '') for h in urls_headers)
item_dict = dict(zip(query_columns_list,item))
nondict.update(item_dict)
record['user'] = user
record['profile'] = profile
record['visit_time'] = firefox_time(nondict['visit_date']).split('.')[0]+'Z'
if nondict['title']:
record['title'] = nondict['title'].encode('utf-8')
record['url'] = nondict['url']
record['visit_count'] = nondict['visit_count']
record['typed'] = nondict['typed']
record['last_visit_time'] = firefox_time(nondict['last_visit_date']).split('.')[0]+'Z'
if nondict['description']:
record['description'] = nondict['description'].encode('utf-8')
urls_output.write_entry(record.values())
log.debug("Done.")
def parse_profiles(profile_data, user, profile_output, profile_headers):
log.debug("Success. Found metadata for {0} profiles.".format(len(profile_data.items())))
for k,v in profile_data.items():
record = OrderedDict((h, '') for h in profile_headers)
record['user'] = user
record['profile'] = k
for key, val in v.items():
if key in profile_headers:
record[key] = val
record['active_time'] = firefox_time(record['active_time']*1000000)
profile_output.write_entry(record.values())
