Tue 9 Mar 2010
Today I wanted to see all the URLs I had visited in Firefox. I tell it keep my history for a very long time. I grow tired of bookmarking and managing those bookmars. I want to be able to do something like say: “What was that site I visited a few days ago that sold Widget X”.
Firefox3 stores its history in a file called places.sqlite which is in sqlite3 format. So with sqlite3 installed this is the command I used to export my history to a text file.
echo "SELECT datetime(moz_historyvisits.visit_date/1000000,'unixepoch'), moz_places.url,moz_places.title,moz_places.visit_count FROM moz_places, moz_historyvisits WHERE moz_places.id = moz_historyvisits.place_id ORDER by moz_historyvisits.visit_date; " | sqlite3 ./places.sqlite
Now I can add a grep to the end of that and find what I was looking for.
Why not just use the built in search for finding stuff? Well the simple reason is that I can add more complex searches easily using regex.
Plus I wanted to be able to export and save my history to a file where I can search it later. Without having to install some firefox extension.
If you wanted to get really adventurous you could do some php like this:
<?
$todo=”SELECT datetime(moz_historyvisits.visit_date/1000000,’unixepoch’), moz_places.url,moz_places.title,moz_places.visit_count FROM moz_places, moz_historyvisits WHERE moz_places.id = moz_historyvisits.place_id ORDER by moz_historyvisits.visit_date;”;
$dbh = new PDO(’sqlite:places.sqlite’);
foreach ($dbh->query($todo) as $row)
{
//print_r($row);
echo “<tr>\n”;
echo “<td>”.$row[0].”</td>\n”;
echo “<td><a href=\”".$row['url'].”\”>”.$row['title'].”</a></td>\n”;
echo “</tr>\n”;
}
?>
</table>
