Monday, 25 April 2016

Query your firefox browsing history with sqlite

Background

I was search for a number that I had rang from an advertisement I seen on the internet. A few months later I needed that number. I went to the url and found the ad had expired. There was no number available. I have an itemized phone bill but the number could be any where with in the last 6 months. I needed to narrow down the search.

Information that I knew:
  • The url of the advertisement
  • I rang the number around lunchtime.

Firefox history

I checked my firefox browser history. I thought that if i knew the date I visited the advertisement it would reduce the number of telephone numbers I had to choose from. The browser history page was not detailed enough for that.

2 issues with the firefox history browser
  • It organizes the website you visited into what month you visited it if it older than a month. There is no column for exact date or time of the visit.
  • When using the searchbox the url would appear in the results but it would not tell you what month,date or time you visited the url.


Firefox history database

In your home directory firefox keeps all the history in a sqlite file.
~/.mozilla/firefox/<random directory>/places.sqlite
To find your database run this command
find ~/.mozilla/firefox/ -name places.sqlite
In the moz_places table it has an epoch in microseconds of the exact time you last visited the website. Unfortunately if you visited the site more recently you only have that date. Firefox does not keep a record of every time you visited a url. By chance I visited this url on 2 different computers so one computer still had my first visit time while the other had the time that I visited the expired advertisement.

I was then able to get the exact url and query the table. I converted from microseconds to a date in one query.
select datetime((select last_visit_date from moz_places where url='<EXACT URL>')/1000000,'unixepoch', 'localtime');

I found the date I visited the url. I was able to see on my phone bill the next day that I rang a number I did not know. I rang the number and it turned out to be the person I was looking for. The query helped me narrow down my search.

Thursday, 14 April 2016

Java nio move under the hood

Moving files with java.nio it will figure out if you are moving a file on the same partition or to a different partition.
  • If you are moving on the same file system then it will do a move which is faster than a copy.
  • If you moving to another filesystem then it will do a copy.
Do not always assume move is fast in java nio. If there is issue with the move it will can mask the issue and do a copy of the file instead.
By looking through the code below you can see.

http://hg.openjdk.java.net/jdk8/jdk8/jdk/file/687fd7c7986d/src/share/classes/java/nio/file/Files.java

This is the move method in the Files class. It decides if should do a move or
copy based on if the file is moving with in the same file system or to another  one.
  public static Path move(Path source, Path target, CopyOption... options)  
     throws IOException  
   {  
     FileSystemProvider provider = provider(source);  
     if (provider(target) == provider) {  
       // same provider  
       provider.move(source, target, options);  
     } else {  
       // different providers  
       CopyMoveHelper.moveToForeignTarget(source, target, options);  
     }  
     return target;  
   }  

FileSystemProvider is an abstract class that provides methods for concrete classes to provide a move method that is associated with the file system. 
You will have a different move implementations for every file system.

For linux filesystems this class provides the move method that just calls UnixCopyFile.move()
In this class it does a rename on the files from source to target. If it can not complete that job then it will just do a copy which will transfer the bytes. Look at the code below. The exception can be ignored.
If the rename exception is not bad enough it will ignore and continue with a copy.
 // first try rename  
     try {  
       rename(source, target);  ######  
       return;  
     } catch (UnixException x) {  
       if (x.errno() != EXDEV && x.errno() != EISDIR) {  
         x.rethrowAsIOException(source, target);  
       }  
     }  
     // copy source to target  
     if (sourceAttrs.isDirectory()) {  
       copyDirectory(source, sourceAttrs, target, flags);  
     } else {  
       if (sourceAttrs.isSymbolicLink()) {  
         copyLink(source, sourceAttrs, target, flags);  
       } else {  
         if (sourceAttrs.isDevice()) {  
           copySpecial(source, sourceAttrs, target, flags);  
         } else {  
           copyFile(source, sourceAttrs, target, flags, 0L);  ######  
         }  
       }  
     }   
This C file has the native JNI rename method for linux

In the end you could be faster to do a rename instead of a move if you know your application is working on the same file system. Less decisions will be made by java for you. 

Sunday, 10 April 2016

Sqlite 2 databases can not be correctly read by sqlite3

If you try to open a sqlite file that is created with sqlite 2 with sqlite3 command then you can get an error like this when running commands on that database

Error: file is encrypted or is not a database

The database file must be created and read by the same version of sqlite. Sqlite3 will not be able to see the tables created in the sqlite2 database.
This can also cause a problem if you are using the sqlite 3 jdbc connector with a sqlite database file that was created with sqlite2. Queries on this file will say that the table you are trying to access does not exist.