Convert Chrome sqlite file containing browsing history to a line-delimited text file for use in OpenWPM

convertHistoryToTxt

// Stratosphere of Surveillance
// http://stratosphere.undisclosed.cc
// ITP Fall 2015
//
// Read Chrome "History" sqlite database (rename to "History.sqlite")
// Export history of URLs to line-delimited text file
// Note: Firefox conversion does not yet work
//
// 1: copy "History" sqlite file into your data directory
// 2: run script to export urls to txt file
// 3. use the text file as the input for the OpenWPM python script

import de.bezier.data.sql.*;

SQLite db;

void setup()
{
  size( 100, 100 );

  exportChromeHistory("History.sqlite", "chrome-urls.txt"); // export url history from Chrome

  // firefox .sqlite does not yet work
  //exportFFHistory("places.sql", "firefox-urls.txt"); // export url history from Firefox (not working yet)

  exit();
}

void draw() {
}

void exportChromeHistory(String sqlFile, String outFile) {
  PrintWriter pw = createWriter(outFile);
  db = new SQLite( this, "data/" + sqlFile );  // open database file
  if ( db.connect() ) {
    db.query("SELECT url FROM %s", "urls");
    while (db.next ()) {
      String url = db.getString("url");
      if ( url.startsWith("http") ) {
        pw.println(url);
      }
    }
  } else {
    println("error: could not connect to db");
  }
  pw.flush();
  pw.close();
}


void exportFFHistory(String sqlFile, String outFile) {
  PrintWriter pw = createWriter(outFile);
  db = new SQLite( this, "data/" + sqlFile );  // open database file
  if ( db.connect() ) {
    db.query("SELECT moz_places FROM %s", "moz_places");
    while (db.next ()) {
      pw.println(db.getString("url"));
    }
  } else {
    println("error: could not connect to db");
  }
  pw.flush();
  pw.close();
}