Read safari history using ASObj-C

foundation
asobjc

#1

I lost a URL in the safari history and I just couldn’t find it so I wrote a script to dump out all the info in the safari history plist file. I put comments in the script that explains how it works and what it does.

It does show how to find all the information in the Safari history items without using any kind of GUI scripting. I did use AppleScript to format the information to be more readable and that made the run time for from under a minute to about 22 minutes. So this script can be pretty fast if all the AppleScript formatting isn’t use.

I thought some people might be interested in this so I posting it.

Here’s the script:

use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
use framework "Foundation"

-- The script was used to get an actual Safari history.  It got all 22,273 history items without an error.  The Mac OSX was Sierra.  It did take 22 minutws to process it.
-- It get all 7 pieces of information stored in the history list although some web pages do not generate all 7 items of informtion.
-- I used copy and paste to get the information from ScriptDebugger to Apple Numbers.
-- This example does not show any decent kind of information output such as writing the information to disk and then opeing the file in Apple numbers.
-- I wrote this to find a missing URL in the history list and I was about to find it using the script output so I did not finish it.
-- But this does show how to process the history list and different ways to process the list can be implemented from this example.

-- This example reads the browser history straight from Safari's history file at /Users/bill/Library/Safari/History.plist.  All values from the history are eventually turned into strings
-- and each new value for the current history line has a tab placed in between all the items in the current InforString line.  At the end of the script InforString (which contains all
-- the comipled info) is returned from script debugger.

-- The overall idea behind how this works is:
-- It reads the entire "History.plist" into an NSDictionary.  The valueForKeyPath gets the property for the specified relationship for every every line in the history file.
-- This is why there is a repeat with a "from and to loop" in the script.  I have to specify which line and which property to get the info for particular line in the history items.
-- I don't need to store the value of TheIndex for each line but I thought it might be helpful.  But in the end I didn't need it.
-- To get the time for the last visit I created an NSDate using initWithTimeIntervalSinceReferenceDate and put the value in the history info for "lastVisitedDate" for
-- the input to the NSDate method just mentioned.  This works because the history info stores the number of seconds from January 1, 2001 00:00:00 UTC.

property OriginalTextItemDelimiters : {""}

set OriginalTextItemDelimiters to AppleScript's text item delimiters

set SafariHistoryPath to ((POSIX path of (path to library folder from user domain)) & "Safari/History.plist") as text
set TheDictionary to current application's NSDictionary's dictionaryWithContentsOfFile:SafariHistoryPath
set TheArray to TheDictionary's valueForKeyPath:"WebHistoryDates"
set NumberOfArrayItems to TheArray's |count|()

set InforString to ""
repeat with TheIndex from 0 to NumberOfArrayItems - 1
	set CurrentItem to (TheArray's objectAtIndex:TheIndex)
	
	set TheURL to (CurrentItem's valueForKeyPath:"") as string
	set TheTitle to (CurrentItem's valueForKeyPath:"title") as string
	set TheDisplayTitle to (CurrentItem's valueForKeyPath:"displayTitle") as string
	set TheVisitCount to (CurrentItem's valueForKeyPath:"visitCount") as integer as string
	set TheSeconds to (CurrentItem's valueForKeyPath:"lastVisitedDate")
	set TheLastVisitedDate to (current application's NSDate's alloc()'s initWithTimeIntervalSinceReferenceDate:TheSeconds) as date as string
	set IndexStr to "Item number: " & (TheIndex as string)
	
	set VisitCountList to (CurrentItem's valueForKeyPath:"D") as list
	if (VisitCountList = {}) then
		set URLRedirectsString to "Empty list"
	else
		set AppleScript's text item delimiters to {", "}
		set VisitCountListListStr to ("{" & text items of VisitCountList as string) & "}"
		set AppleScript's text item delimiters to OriginalTextItemDelimiters
	end if
	
	set URLRedirects to (CurrentItem's valueForKeyPath:"redirectURLs") as list
	if (URLRedirects = missing value) then
		set URLRedirectsString to "Empty list"
	else
		set AppleScript's text item delimiters to {", "}
		set RedirectListStr to ("{" & text items of URLRedirects as string) & "}"
		set AppleScript's text item delimiters to OriginalTextItemDelimiters
	end if
	
	set AppleScript's text item delimiters to OriginalTextItemDelimiters
	
	if (TheDisplayTitle ≠ missing value) then
		set InforString to InforString & ((TheIndex + 1) as string) & tab & TheDisplayTitle & tab & TheURL & tab & TheLastVisitedDate & tab & TheVisitCount & tab & VisitCountListListStr & tab & RedirectListStr & return
	else
		set InforString to InforString & ((TheIndex + 1) as string) & tab & TheTitle & tab & TheURL & tab & TheLastVisitedDate & tab & TheVisitCount & tab & VisitCountListListStr & tab & RedirectListStr & return
		
	end if
end repeat

InforString

Bill


(Phil Stokes) #2

History.plist is a relic of an older version of Safari. Nowadays, history is stored in (among other places) History.db.

I don’t have an AS-ObjC version, but this handler will dump your history out to a Desktop file called ‘BrowserHistory.txt’.

It shouldn’t take more than about 1 or 2 seconds. Call it with

set homePath to (POSIX path of (path to home folder))

try
	its gatheredBrowserDataForUser(homePath)
end try

on gatheredBrowserDataForUser(usr)
	
	set browserPath to usr & "Desktop/BrowserHistory.txt"
	
	try
		do shell script "echo 'Safari:
	' >> " & browserPath
	end try
	
	try
		(do shell script "plutil -p ~/Library/Safari/Downloads.plist | grep -vi bookmark >>" & browserPath)
	end try
	
	try
		(do shell script "sqlite3 ~/Library/Safari/History.db \"SELECT h.visit_time, i.url FROM history_visits h INNER JOIN history_items i ON h.history_item = i.id\" >> " & browserPath)
	end try
	
	
	try
		(do shell script "plutil -p ~/Library/Safari/UserNotificationPermissions.plist | grep -a3 '\\\"Permission\\\" => 1' >>" & browserPath)
	end try
	
	try
		(do shell script "plutil -p ~/Library/Safari/LastSession.plist | grep -iv taburl >>" & browserPath)
	end try
	
	
end gatheredBrowserDataForUser

#3

Phill,

Your solution uses shell scripts with “plutil” an a single called to “SQLite” (for other people reading “plutil” is “property list utility”). I don’t use shell scripts any more and I have never used SQLite on the Mac or anywhere else. I know it is included in Mac OSX but I have never checked into it. As I understand it’s a file based database and that is all I know about it. Working from the command line in Terminal sounds like that might be hard to work with but as I said I’ve never used it. But the syntax h.visit_time, i.url, INNER JOIN, ON, … sound like its got it own specific language so it is another thing to learn.

plutil -p according to Apple’s own documentation says “The output format is not stable and not designed for machine parsing. The purpose of this command is to be able to easily read the contents of a plist file, no matter what format it is in.” So this does not seem safe to use. I got my property names from the current implementation of Safari that it uses for the history list and it is true if the properties change it breaks the script. In that case I would have to rediscover the new properties (which isn’t read hard in ScriptDebugger). So in that way both of our implementations are not perfect.

Using grep to process he data is more low level then I would want to get into. The smallest change in the output could easily break that.

Overall I do not see how the way I used is old school. I used ASObj-C to get the needed info and you used SQLite and processed the output. Can you explain how this is old school stuff.

Bill


(Jim Underwood) #4

Bill, SQL is a near universal language (with some local variants) used by most modern relational databases. It is very powerful. The great thing is once you know SQL, you can use it with virtually any SQL database, like SQLite and even FileMakerPro.

Since SQLite works the same (or very similar) across many platforms (and is free), it is often used for Mac apps, like Evernote.

While there is a SQL dictionary for AppleScript, I have found that using a shell script where I can easily use native SQL commands to be the best approach, at least for me (I’m an old SQL database-app designer/programmer).

I am very much a shell script novice, but I use them whenever it is the best tool to use for my use case.

I often see AppleScript power users like @ccstone using a shell script in their AppleScripts for this very reason.

grep (Regular Expressions) is an extremely powerful tool that usually has a very compact syntax. Plain AppleScript does not support regular expressions, so if you need/want to use RegEx, then you will need to turn to:

  • grep
  • ASObjC
  • Scripting Additions like Satimage.osax (very powerful)

I personally find Satimage.osax RegEx to be the easiest to understand and use, and it is very fast. But if I had grown up in the Unix world, I’d probably saying that about grep.

There are many, many cool solutions for getting Mac system info using shell scripts that combine the appropriate command with grep to extract the desired info.

I try to use the best tool for the job (and that includes my ability to use the tool), regardless of whether it is in favor or not, old school or not, or really anything else.

Mostly I just want to get the job done soon with reasonable performance. I have learned that sometimes programmer’s time is more valuable than execution time. :wink:


(Phil Stokes) #5

That’s not what I said. I said History.plist is a relic of an older version of Safari. The only reason you have that file on your mac in Sierra is because you’ve upgraded from an older version of macOS where the History.plist file was still used by Safari.

A clean install of macOS would not have that file. Thus, your script might work for you on your machine now, but it wouldn’t work on my machine or indeed any machine that shipped with (I think) 10.10* or later because there’s no History.plist file to target.

ps. Don’t test this unless you have a backup, but I think if you do clean all history in Safari, you’ll find that you don’t have a History.plist file anymore, too.

*pps. It looks like History.plist was last used in 10.9 and 10.10 saw the change to History.db.


#6

Phil,

Ok, so it’s me that is out of date, not the ASObj-C. That’s ok then :slight_smile:

Bill


#7

I do seem to surprise you often with all the things I don’t do. What do you expect from a guy who didn’t figure out ScriptDebugger was a single word until after I had been using it for over 20 years :slight_smile:

I don’t do grep, I don’t do SQLite and I have sworn off C and all it variants for good. I know so many useless things from custom things I’ve done but will never do again it is sometime hard me to believe it and I was there when it happened. Of course I did swear off ASObj-C years back and that didn’t seem to do me much good so who knows maybe I will learn SQL some day. But I’ve never been big on business applications for databases.

Of course being disabled for 6 years has taken me out of scripting and filmmaker pro stuff on any professional level. I pretty much just do the writing any more. I’ve been doing some ghost writing for a Japanese chef and there is no end to the interesting things that gets me into. I’d just get bored with SQL.

Bill


(Ed Stockly) #8

On this line:

set TheArray to TheDictionary's valueForKeyPath:"WebHistoryDates"

I get this error message.

missing value doesn’t understand the “valueForKeyPath_” message.

So that file must not be there.


#9

Ed,

I tried removing the “History.plist” from the “Safari” folder in the user library and I got the same error. It worked fine with the file in the folder. The script I wrote was not a great one and didn’t do any kind of error checking, including checking if the file was there. I wanted to find something lost in the history and I wanted to practice ASObj-C so that was my main focus. The script was an example of ASObj-C more than how to read Safari’s history.

Assuming Safari is still working for you and the history still works that would tend to confirm Phil’s about the file itself not being all that important. The item I wanted from the history was from a long time ago. This got me thinking. So I checked the modification date on the “History.db” and it is yesterday. The modify date on “History.plist” was from a while back. So I was just lucky I found the URL. Safari is no longer using the the “History.plist.” That definitely confirms what Phil was saying. Had I tried looking for a recent URL it wouldn’t have worked.

Bill


(Shane Stanley) #10

I think you flatter Database Events more than somewhat. It’s built on SQLite3, but I think it’s stretching things to call it a dictionary for SQL.

I know very little SQL, but I wonder if there might be a better approach than using do shell script.

Take @sphil’s example above. This bit:

		(do shell script "sqlite3 ~/Library/Safari/History.db \"SELECT h.visit_time, i.url FROM history_visits h INNER JOIN history_items i ON h.history_item = i.id\" >> " & browserPath)

Takes much less than a second here, with 14,000+ entries. But suppose I want to do something with those results in AppleScript. I might try something like this, using the same query:

set theList to paragraphs of (do shell script "sqlite3 ~/Library/Safari/History.db \"SELECT h.visit_time, i.url FROM history_visits h INNER JOIN history_items i ON h.history_item = i.id\"")
return count of theList

Now we’re out more than 9 seconds, because of the time taken to assemble the list. Even this:

set theList to (do shell script "sqlite3 ~/Library/Safari/History.db \"SELECT h.visit_time, i.url FROM history_visits h INNER JOIN history_items i ON h.history_item = i.id\"")
return count of theList

takes a full 2 seconds, because of the need to get the result back to AppleScript.

Now if we could do something similar via ASObjC, we might be able to get back a pointer, and convert the bits of the result as we need, or otherwise manipulate them as Cocoa objects. Cocoa arrays are much more efficient than lists when the number of items is large, like here.

Here’s an example using the FMDB framework, which is just an Objective-C wrapper around SQLite3:

use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use framework "FMDB"
use scripting additions

set thePath to current application's NSString's stringWithString:"~/Library/Safari/History.db"
set theDb to current application's FMDatabase's databaseWithPath:(thePath's stringByExpandingTildeInPath())
theDb's |open|()
set x to theDb's executeQuery:"SELECT h.visit_time, i.url FROM history_visits h INNER JOIN history_items i ON h.history_item = i.id"
set theList to current application's NSMutableArray's array()
repeat while x's next() = 1
	theList's addObject:(x's resultDictionary())
end repeat
theDb's |close|()

In this case theList is an array of dictionaries like this: {url:"http://news.bbc.co.uk/", visit_time:5.2074825439578e8}. So you can filter it, sort it, extract by key, and so on, and it supports fields of differing types (string/date/number/etc).

The catch is that it’s slower – about 15 seconds on the same sample. But the while loop could be pushed into Objective-C code in the framework, and it should be about as fast as the original do shell script method.

Which is a long way around asking if there’s any interest or demand for an ASObjC alternative to do shell script for executing SQLite queries/updates/statements.


(Phil Stokes) #11

You’re such a ‘do shell script’ hater, Shane!

I don’t have problem with the timing of this script. It outputs the text file in less than 2 seconds on my machine, and anything else I’d want to do with it after that I’d probably do via BBEdit, or Python or some other tool entirely more suitable than AppleScript (one such thing might be converting those epoch time intervals to dates: they’re counting from 00:00:00 Jan 1, 2001 UTC, I believe, not 1970).

That said, just to play along, is it possible to pipe things through NSTask from ASObjC? That should be pretty fast if so.


(Shane Stanley) #12

I’m not really – I use it quite a lot. But I’ve had the issue of SQLite in the back of my mind, though, watching people using do shell script, and then having to jump through hoops to make the results usable. It seems to me that if there were something just as fast (or faster), but which produced an array of records rather than a single string to parse, it might be a useful thing.

[quote=“sphil, post:11, topic:654”] that I’d probably do via BBEdit, or Python or some other tool entirely more suitable than AppleScript
[/quote]

In this case that’s certainly true. I’m thinking of other cases, though, where the data is needed in AppleScript – where it’s to be put into InDesign documents, for example, or in a couple of cases Illustrator and Photoshop pages, or the values used in calculations.

I actually think they’re using the Unix epoch here – or I haven’t browsed for 30 years :slight_smile: But if they were from the Cocoa reference point of 2000, it would be exactly the sort of thing the approach I’m talking about would lend itself to. The user could call dateForColumn: and get back a date, rather than getting a string containing a number in scientific format, and having to do the conversion themselves.

It is, but (a) it’s not really faster, and (b) it can’t cope with very large results (well, I can’t make it cope with them).


(Phil Stokes) #13

You mean 1970? Nah, I’m 99% certain that’s not so.

Take any one of those numbers before the decimal point and divide it by /60/60/24/365 and you get 16 years+; i.e., 2001.

I just haven’t actually figured out how to translate them properly. I can’t find a ‘dateForColumn’ method in Dash.


(Shane Stanley) #14

You’re right – it’s using the Cocoa reference date.

That’s part of FMDB framework, and it is assuming the Unix epoch.

use framework "Foundation"
use scripting additions

set aString to current application's NSString's stringWithString:"4.96049969674676e8"
current application's NSDate's dateWithTimeIntervalSinceReferenceDate:(aString's doubleValue())
--> (NSDate) "2016-09-20 07:39:29 +0000"

#15

Shane,

I go a free browser called “DB Browser for SQLite” from http://sqlitebrowser.org. I was able to easily load the “History.db” file into this new browser. I just loaded the entire “History.db” file. After that the data can easily be seen inside by the program.

Then I exported the data from that application as tab separated values and imported those records into FileMaker. Then I processed the data in all kinds of ways with no problem. As I got more and more used to the structure of the data it got easier and easier to work with the data.

The one weird things is there is no actual tab separated export from “DB Browser for SQLite” but it does have something called comma sperated export which allows a person substitute a tab for the comma and then it becomes a tab operated export. I could never get it to work with comma separated values but it works great with tab separated values.

Since “DB Browser for SQLite” is an SQL browser and it was able to process the data into something that can easily be worked with, and I did not need anything else to reformat the data to make it usable, I strongly suspect there is some way all the processing could be done in SQL. Then all this other kind of processing done earlier today would not be needed. It is a definite weak link in the process to reformat something Apple tells you will not always be the same. It seems pretty likely that only good way to do this is to preprocess the data with SQL and then do some light processing later to get whatever information is needed from the data.

Unfortunately I’m not all that inclined to sit down and figure out SQL just to do this one thing. Although I was not going to anything further with this after last night but I did do the stuff I just listed above. I’m trying to put all my spare time into getting the new version of the ASObj-C database released since it finally has a full interface now, and can do a lot more things.

P.S. Hope you had a great vacation :slight_smile:

Bill


(Jim Underwood) #16

Shane, yes, I’m interested, provided that:

  1. It is at least as fast as shell script + convert text results to list
  2. Returns/uses a std AppleScript List or record (user specified)
  3. Would either return a list, or use a list to update the DB.

What are you thinking about providing, a simple handler, or something more complicated?

Thanks for asking.


(Shane Stanley) #17

Excellent, thanks – although it already seems a long time ago…


(Shane Stanley) #18

At the very least. For large results like the earlier example, it should be significantly faster.

It returns an array or dictionary, which you can coerce as list or as record. As an array, you can also do things like sort and filter very easily.

If I understand you correctly, yes. You have the option of providing a query string plus a list of items to bind to the ? placeholders.

Something more complicated. The FMDB framework already provides most of what’s needed. But because it’s designed for Objective-C or Swift, it returns results one row at a time, and for big results that’s slow from ASObjC (see my code above – that’s working code). So I’m just toying with adding alternative methods to return results for all rows in one go, as an array of arrays or dictionaries. And similarly adding methods for writing multiple rows in a single call. Other than that, it obviously needs some documentation.

But the guts of it’s already there (and used in a lot of macOS and iOS apps).


#19

Can I help with, or do, the documentation for the project? I know you’re always pretty busy.

Shane, I am assuming you are talking about using the FMDB v2.7 at “https://github.com/ccgus/fmdb” which is based on “http://sqlite.org/

Also for others reading this
The “Class Reference” and “Category References” for FMDB can be found at:
http://ccgus.github.io/fmdb/html/index.html

The documentation for sqlite can be found at http://www.sqlite.org/docs.html
http://www.sqlite.org/faq.html

Bill


(Jim Underwood) #20

Shane, this sounds very good.

May I suggest that you move all of the posts about this to a new forum topic, so it is easier for others to find, and for us to find later.