You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
82 lines
4.7 KiB
82 lines
4.7 KiB
#! /usr/bin/env bash
|
|
|
|
# TODO
|
|
# support with recall feeds
|
|
# #MemorizationDay from memorizationfeed.xml
|
|
# #BehaviorRecallDay from recallfeed.xml
|
|
# #PreparationDay from preparationfeed.xml
|
|
# e.g. 'SELECT url,title,visit_count FROM moz_places WHERE url LIKE "%#%Day%"'
|
|
# make the requests actually work
|
|
# simplify the syntax
|
|
# handle parameters instead of the implicit --all
|
|
# handle "brain" keyword requests
|
|
# http://www.google.com/search?num=100&hl=en&q=sqlite+site:fabien.benetou.fr
|
|
# PmWiki output format
|
|
# %s/"\(.*\)","\(.*\)","\(.*\)"/* \3 [[\1|\2]]/
|
|
# consider bc for the math
|
|
# browse the web for more cool queries
|
|
# http://www.thesimplelogic.com/2010/04/17/wandering-wikipedia-datamining-my-firefox-history/
|
|
# see
|
|
# http://fabien.benetou.fr/MemoryRecalls/ImprovingPIM#FirefoxSQLiteLogs
|
|
# http://self/wiki/PBES/PBES#FirefoxSQLiteLogs
|
|
# http://fabien.benetou.fr/Tools/Tools#SQLite
|
|
#
|
|
# Note that the name might be misleading since currently only Firefox SQLite format is supported
|
|
|
|
ANHOURAGO=`date +%s --date "now 1 hour ago"`
|
|
ADAYAGO=`date +%s --date "now 1 day ago"`
|
|
AWEEKAGO=`date +%s --date "now 1 week ago"`
|
|
DB=~/.mozilla/firefox/*.default/places.sqlite
|
|
|
|
echo "top 10 visited URL this week"
|
|
|
|
echo -e "\ntop 10 visited URL in general"
|
|
#sqlite3 -list -line $DB 'SELECT url,title,visit_count FROM moz_places ORDER BY visit_count DESC LIMIT 10'
|
|
echo "this week"
|
|
#sqlite3 -list -line $DB "SELECT url,title,visit_date as moment FROM moz_historyvisits, moz_places WHERE moz_historyvisits.place_id = moz_places.id and visit_date>($AWEEKAGO*1000000)"
|
|
echo "this hour"
|
|
#sqlite3 -list -line $DB "SELECT url,title,visit_date as moment FROM moz_historyvisits, moz_places WHERE moz_historyvisits.place_id = moz_places.id and visit_date>($ANHOURAGO*1000000)"
|
|
#sqlite3 -list -line $DB "SELECT url,title,visit_date as moment FROM moz_historyvisits, moz_places WHERE moz_historyvisits.place_id = moz_places.id and visit_date>($ADAYAGO*1000000)"
|
|
|
|
URL=$1
|
|
echo -e "\nhow many time URL X has been visited this week"
|
|
echo -e "\nhow many time URL X has been visited in general"
|
|
# e.g. repository, memory recall, ...
|
|
|
|
echo -e "\nwhen was the URL X been visited for the first time?"
|
|
# practical to check against deja-vu
|
|
|
|
startingURL=$1
|
|
endingURL=$2
|
|
echo -e "\nURLs visited between starting URL and ending URL"
|
|
# as first done with http://fabien.benetou.fr/Events/DrumbeatParis#VisitedLinks
|
|
|
|
|
|
URLSLISTFILE=~/.urlstocheck
|
|
#SCRIPTURL=`grep '^\$ScriptUrl' ~/www/mirrors/fabien/local/config.php | grep -v 127 | sed -e "s/.*'\(.*\)';/\1/"`
|
|
#echo "" > $URLSLISTFILE
|
|
#ls ~/www/mirrors/fabien/wiki.d/ | grep -v "del-" | tr "." "/" | sed -e "s,^,$SCRIPTURL\/," >> $URLSLISTFILE
|
|
#while read line; do
|
|
# sqlite3 -column $DB "SELECT count(*),url FROM moz_historyvisits, moz_places WHERE moz_historyvisits.place_id = moz_places.id and url LIKE '$line'" | grep "0" && echo -e "\t$line"
|
|
#done < $URLSLISTFILE
|
|
echo -e "\nwhich URLs from this list have never been visited this week"
|
|
echo -e "\nwhich URLs from this list have never been visited in general"
|
|
# e.g. all wiki pages, all page from a wiki group (e.g. memory recall)
|
|
|
|
#types with for each a list of URLs
|
|
echo -e "\nwhat is the repartition of visits per type of activity this week"
|
|
echo -e "\nwhat is the repartition of visits per type of activity in general"
|
|
# total
|
|
# SELECT url,title,datetime(visit_date/1000000, 'unixepoch','localtime') as moment FROM moz_historyvisits, moz_places WHERE moz_historyvisits.place_id = moz_places.id
|
|
|
|
# entertainment
|
|
# SELECT url,title,datetime(visit_date/1000000, 'unixepoch','localtime') as moment FROM moz_historyvisits, moz_places WHERE moz_historyvisits.place_id = moz_places.id AND (url like "%casttv%" OR url like "%ninja%" OR url like "%otaku%" OR url like "%youtube%")
|
|
# work
|
|
# SELECT url,title,datetime(visit_date/1000000, 'unixepoch','localtime') as moment FROM moz_historyvisits, moz_places WHERE moz_historyvisits.place_id = moz_places.id AND (url like "%fabien.benetou.fr%" OR url like "%127.0.0.1%" OR url like "%seedea.org%" OR url like "%agi-wiki.org%" OR url like "%ourp.im%")
|
|
# social
|
|
# SELECT url,title,datetime(visit_date/1000000, 'unixepoch','localtime') as moment FROM moz_historyvisits, moz_places WHERE moz_historyvisits.place_id = moz_places.id AND (url like "%facebook%" OR url like "%twitter%" OR url like "%mail.google%")
|
|
|
|
echo -e "\nwhat are the sources of your Wikipedia explorations?"
|
|
# from http://www.thesimplelogic.com/2010/04/17/wandering-wikipedia-datamining-my-firefox-history/
|
|
WP="SELECT curr.id, curr.url, curr.title, prev.id, prev.url, prev.title, t.visit_date FROM moz_places curr, moz_places prev, moz_historyvisits frm, moz_historyvisits t WHERE t.place_id = curr.id AND frm.place_id = prev.id AND frm.id = t.from_visit AND curr.url LIKE 'http://en.wikipedia.org/%' AND prev.url NOT LIKE 'http://en.wikipedia.org/%'"
|
|
sqlite3 -list -line $DB "$WP"
|
|
|