Probleme with 'places.sqlite' -> 'moz_places' Can't retrieve the "real" $LAST_VISITED_URL ?
the last url in moz_places in places.sqlite are not the same as in my history tab I posted here
Here is my `last-url.sh`
#!/bin/bash rm -rf /tmp/db.sqlite cp ~/.mozilla/firefox/*.default-esr/places.sqlite /tmp/db.sqlite ; LAST_URL=`sqlite3 /tmp/db.sqlite "select url from moz_places order by last_visit_date desc limit 1;"`;
echo $LAST_URL
My script keeps giving me wrong answer. For exemple, a stackoverflow.com url instead of the youtube.com one even if i reload the page.
I have only one `xxxxxx.default-esr` profile.
Screenshot examples: the "google test" is a brand new visited url, and the others were refreshed pages
Modified by dehya
All Replies (7)
I tested through the site https://inloop.github.io/sqlite-viewer/ with places.sqlite and this query:
SELECT url, title, last_visit_date, datetime(last_visit_date/1000000, 'unixepoch') AS LatestVisit FROM 'moz_places' ORDER BY last_visit_date DESC
The result seemed correct when I had just started up Firefox.
One thing I noticed is if I visit a site, then load the database into the query tool and run the query again, it isn't there. Same after following a link in that page. Same after following a link in that page. It's not until I follow a third link that all four of the pages appeared in moz_places. This appears to be some kind of optimization (SQLite uses journaling files) that causes weird results during your session. But when you exit out of Firefox, presumably it cleans up and does all the updates before quitting, so the results should be accurate by then.
Closing firefox don't make the change, I'am really lost on this one. Thanks for help. Now i ask myself how firefox create the history tab ? as it reflect the real history of browsing, including refreshes.
Using what file and what sql request on which file (is it even use places.sqlite? Now i doubt)
PS: I liked that one : datetime(last_visit_date/1000000, 'unixepoch')
Modified by dehya
Some history may be cached in memory, but the Library window shows a lot of entries, so presumably it is going to disk for that information. The SQLite engine is constructing that from the places.sqlite file and from its journaling files, so it has a more complete view than you do simply operating on the one file.
Ok thanks a lot. There is a places.wal file Write-Ahead Logging: this is what you talk about when saying journaling files ? So can a tool like wal2json help me to achieve my goal.
Modified by dehya
Thanks. When you say journaling file, you talk about places.sqlite.wal ? I saw a tool wal2json on github (here). Not trivial to install so should i give it more try ?
I did a mistake previously. Closing firefox populate places.sqlite properly. I don't know what i did before. sorry Anyway closing firefox to get that data is not an option, so i will keep searching.
Maybe there is a way to tell firefox to populate the db And i saw a wal2json on github . Maybe all the info i need are on this places.sqlite.wal
Modified by dehya
Sorry , closing firefox make the request coherent ( I made a mistake). So it populate the db at that time. When you say journaling file, you talk about places.sqlite.wal ? Can a tool like wal2json give me my real time info? Maybe you see another way to programmatically get the $LAST_URL Thanks for the help.
Modified by dehya
Firefox writes -wal and -shm files.
I don't know about programmatic access to SQLite databases, but I'm puzzled why sqlite3 doesn't handle it automatically.
I kind of glazed over and didn't finish https://www.sqlite.org/wal.html