Like many applications Google Chrome uses a SQLite database (or rather a number of SQLite databases) to store information relating to pages visited. One of these databases is the history DB which uses a set of normalised tables which, amongst other things, holds a table showing the date and time of every page visited, where appropriate the previous page (the page on which the user clicked a link/referring page) and an internal ID number of an entry in the URL table that contains the text of the page itself.
The purpose of this article is to show how we can generate a list of the pages that a user has followed while browsing the internet, or more correctly showing the chain of webpages that led to a specific page. To achieve this we will use a feature of SQL known as Recursive Common Table Expressions (RCTE), don’t worry it sounds worse than it is!
But first let’s look at a simple RCTE (from the SQLite web site) and see how it all works:
WITH RECURSIVE cte(x) AS (
SELECT cte.x + 1
So what does the above query do? Very simply it creates a simple table with one column (x) containing the value 1 and writes out the value, then adds 1 to this column and then writes out the value until it has written all the results from 1 to 10. i.e.:
The essential feature of any RCTE is that there is a UNION between two tables, the first part