Wednesday, February 9, 2011

Reading FireFox bookmarks with JPA

One of the primary goals of DB Importer is simplicity. To make this concrete, the goal is to be able to start using an existing database with JPA in 5 minutes.

Now it is time to test this. I choose the FireFox SQLite bookmarks database as an example. Not because it is easy, but because it is hard ;-)

Preconditions: Eclipse and DB Importer are installed and a Java project is created.

1. Reverse Engineer Database
Right-click on a Java package and select DB Importer > Import from Database.
Select the org.sqlite.JDBC driver in the wizard:



2. Locate the Database
I need to replace <sqlite-file> in the URL with the actual database file. But where does FireFox store its bookmarks?

To find out this, I turn to my favourite programming tool: Google. Google "firefox bookmarks database" and click on the top link gives me this site, which explains that you should look for a file called "places.sqlite" under C:\Users\<user name>\AppData\Roaming\Mozilla\Firefox\Profiles\<some random characters>.default.

To find this file on my Mac, I open a terminal and enter:
ls $HOME/Library/Application\ Support/Firefox/Profiles/*/places.sqlite
This gives me the file, and i enter the full path in the wizard:



3. Generate Entity Classes
When I push Next, a new editor is opened:



This shows the tables and columns in the database and a preview of the Java code that will be generated.

Two of the tables are marked with errors. Clicking on them reveals that this is because they lack a primary key, so they cannot be used with JPA.

I Save the editor. This generates the Java files, but they have compilation errors because I have not yet added the JPA libraries.

4. Create a Test Application
To use JPA, I need to create a configuration file (persistence.xml) and add the JPA libraries and the JDBC driver to the project. This can be done with Maven, or you can use a wizard in DB Importer to quickly create a test application: Right-click on the Java package and select DB Importer > New Test Application. This opens another wizard:



I enter a class name and push Finish. This adds all the necessary JAR files to the project, creates SQLiteDialect.java, persistence.xml and PrintBookmarks.java and opens the two last files.

The generated PrintBookmarks class is just a template:
public class PrintBookmarks {
public static void main(String[] args) {
javax.persistence.EntityManagerFactory factory =
javax.persistence.Persistence.createEntityManagerFactory("unit1");
javax.persistence.EntityManager em = factory.createEntityManager();
javax.persistence.EntityTransaction tx = em.getTransaction();
tx.begin();
try {
// TODO Replace User with a real @Entity class.
java.util.List result = em.createQuery("from User").
getResultList();
System.out.println("Found " + result.size() + " objects:");
for(User obj : result) {
// TODO Change to some real method.
System.out.println(obj.getName());
}
tx.commit();
} catch(RuntimeException e) {
tx.rollback();
throw e;
} finally {
em.close();
factory.close();
}
}
}

But just by replacing "User" with "MozBookmarks" and "obj.getName()" with "obj.getTitle()" we have a working application.

Let's try: Run As > Java Application.

Success! All my bookmarks are printed. And it went really quickly, so I think I am satisfied with the simplicity.

Bonus: Modify the Generated Code
Simplicity is one thing, but how about flexibility? DB Importer gives you all the flexibility you need with a Groovy script that configures the generated code. Don't worry if you don't know Groovy, the script uses Java syntax.

(Note that configuring this script is only available in the commercial edition of DB Importer. You get simplicity for free but flexibility is commercial.)

First, I want to change the generated class names. For instance, I want the table MOZ_BOOKMARKS to be reverse engineered to a Java class Bookmark.

I select a table in the tree and double-click on the className in the table to the right. This opens an editor with ImportDB.groovy. This script allows you to modify anything you want in the generated Java code. I modify the className method and Save the editor. This changes the className in the preview from MozBookmarks to Bookmark.



I would also like to generate relations for foreign keys. The referenced table is missing in SQLite, so I need to calculate the referenced table based on the column name. I edit the getReferencedTable method as follows and Save the import script.



I can then modify the test app to use the Bookmarks - Place relation. I also modify it to print the bookmark hierarchy:
/**
* List all FireFox bookmarks.
* This depends on correct configuration in
* persistence.xml. Also, FireFox must not be running,
* since it locks the database.
*/
public class PrintBookmarks {
private EntityManagerFactory factory;
private EntityManager em;
private EntityTransaction tx;

public static void main(String[] args) {
new PrintBookmarks().run();
}

public void run() {
factory = Persistence.createEntityManagerFactory("unit1");
em = factory.createEntityManager();
tx = em.getTransaction();
tx.begin();
try {
printBookmarks(getTopBookmarks(), "");
tx.commit();
} catch (RuntimeException e) {
tx.rollback();
throw e;
} finally {
em.close();
factory.close();
}
}

private void printBookmarks(Collection list, String mrg) {
for (Bookmark bm : list) {
System.out.print(mrg + bm.getTitle());
Place place = bm.getFk();
if (place != null) {
System.out.print(" - " + place.getTitle() + ": "
+ place.getUrl());
}
System.out.println();
printBookmarks(getChildBookmarks(bm), mrg + " ");
}
}

private List getTopBookmarks() {
return getBookmarksByParent("0");
}

private List getChildBookmarks(Bookmark parent) {
return getBookmarksByParent(parent.getId());
}

@SuppressWarnings("unchecked")
private List getBookmarksByParent(String parent) {
Query query = em.createQuery(
"from Bookmark where parent=:parent");
query.setParameter("parent", parent);
return query.getResultList();
}
}
The complete source with the reverse engineered classes and this test app is available at bitbucket.

No comments: