X

Geertjan's Blog

  • November 6, 2010

How to Visually Diff Multiple Databases

Geertjan Wielenga
Product Manager
Today we'll combine the last two blog entries so that we can diff two databases and present the result visually to the user. The assumption is that you've followed everything described yesterday and that everything is up and running successfully. As a first next step, follow the blog entry from the day before that to set up your Maven-based NetBeans Platform application correctly for working with the Diff API. I.e., set the dependencies described in that blog entry, but don't create the TopComponent, since we'll handle that part differently.

At the end of this blog entry, you'll be able to diff two databases, as shown below, where you see the "Name" column of one database compared to the "Name" column of another database (where the databases have the same underlying structure):

The above could be pretty handy when (1) you have different versions of a database, e.g., version 1 and version 2, and you'd like to see the differences and/or (2) you have multiple different users making use of the same database structure (e.g., there's a customer application on top of a particular schema shared by multiple users and you'd like to see the difference between the database of User A and the database of User B). And a tool like the above is also a way to catch typos and other errors in databases.

As a further requirement, i.e., in addition to wanting to be able to diff databases, we also want to keep a history of diffs, which we'll then be able to reopen and compare with the current (or other diffs). To that end, we'll export the relevant parts of our databases to text files, compare those text files per project, and store the text files for later use (i.e., build up a history of diffs). Then, in the next blog entry, we'll learn how to display those text files in our application, enabling the user to choose, from the diff history, a particular diff for analysis.

So, let's get started!

Step 1: Create An Action To Handle Multiple Projects

With the above set up, i.e., you have the diff dependencies and you have done everything described yesterday, let's use the "New Action" wizard to create a conditionally enabled Action, with "User May Select Multiple Nodes" selected:

That will let the Action apply to the situation where TWO projects are selected, so that you can diff the underlying databases. In the "New Action" wizard, register the Action in the same place where you registered yesterday's Action, i.e., in the "CustomerSalesActions" category. We'll load the Action in the same way as the one from yesterday, i.e., into the context-sensitive list of Actions on the Node, rather than from a global menu item or global toolbar. Name the Action "DiffDatabasesAction", with "Diff Databases" as the display name.

When you complete the wizard, you'll have this Action, with new layer entries for the infrastructure to handle the underlying plumbing (make sure to make the Action asynchronous, via the layer attribute described yesterday). I.e., this is the Java class you now have, ready to be filled with the diff-related Java code:

import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;
import java.util.List;
import org.netbeans.api.project.Project;
public final class DiffDatabasesAction implements ActionListener {
private final List<Project> context;
public DiffDatabasesAction(List<Project> context) {
this.context = context;
}
public void actionPerformed(ActionEvent ev) {
for (Project project : context) {
// TODO use project
}
}
}

Note that we now have access to MULTIPLE projects, i.e., that's great, so now we have entry points for accessing the underlying databases (which are in fixed places within the projects, as explained yesterday), so that we can diff them.

Next, in the project's logical view, change the "getActions" to the following (or define it less constrictively, i.e., so that anything in our folder will be loaded):

@Override
public Action[] getActions(boolean arg0) {
Action[] nodeActions = new Action[7];
nodeActions[0] = Utilities.actionsForPath("Actions/CustomerSalesActions").get(0);
nodeActions[1] = Utilities.actionsForPath("Actions/CustomerSalesActions").get(1);
nodeActions[2] = CommonProjectActions.newFileAction();
nodeActions[3] = CommonProjectActions.copyProjectAction();
nodeActions[4] = CommonProjectActions.deleteProjectAction();
nodeActions[5] = CommonProjectActions.setAsMainProjectAction();
nodeActions[6] = CommonProjectActions.closeProjectAction();
return nodeActions;
}

OK. Now everything is ready, except for all the actual functionality that we need. :-)

Step 2: Create FreeMarker Template Files

To diff the databases, we first need to export everything from them into files. Then we can diff the files. (Maybe this is not feasible for every imaginable scenario, so apologies if your trip down this road ends here.) We'll use FreeMarker as our templating engine (search this blog for other entries relating to this topic, since the NetBeans Platform has built in FreeMarker support).

To use the built-in FreeMarker support from the NetBeans Platform in our own application, we need to add the following dependency to the application module's POM:

<dependency>
<groupId>org.netbeans.modules</groupId>
<artifactId>org-netbeans-libs-freemarker</artifactId>
<version>RELEASE691</version>
<scope>runtime</scope>
</dependency>

Also, add this to the manifest of the functionality module:

OpenIDE-Module-Needs: javax.script.ScriptEngine.freemarker

OK, now FreeMarker is set up and ready for us to use it. In the folder where the "layer.xml" is found, create two text files, named "project0.txt" and "project1.txt". In the first file, write this content:

${content0}

In the second file, write this content:

${content1}

Later on, we will replace the variables above with strings that will contain the parts of the database that we'd like to diff.

Now go to the "layer.xml" file and register the above two templates as follows:

<folder name="CustomerSalesTemplates">
<file name="project0.txt" url="project0.txt">
<attr name="template" boolvalue="true"/>
<attr name="javax.script.ScriptEngine" stringvalue="freemarker"/>
</file>
<file name="project1.txt" url="project1.txt">
<attr name="template" boolvalue="true"/>
<attr name="javax.script.ScriptEngine" stringvalue="freemarker"/>
</file>
</folder>

From the above, you can see that we have a new folder in the filesystem, containing two text files, which we have registered as FreeMarker templates. That will enable the infrastructure to correctly convert the variables to the strings we will pass in later.

Step 3: Write Database Content To FreeMarker Template Files

Now fill out the "DiffDatabasesAction" class as follows, hopefully the comments inline will explain everything:

public final class DiffDatabasesAction implements ActionListener {
private final List<Project> context;
public DiffDatabasesAction(List<Project> context) {
this.context = context;
}
@Override
public void actionPerformed(ActionEvent ev) {
try {//For the first project,
//create a StringBuilder that will contain database content,
//a HashMap to pass in to the FreeMarker template,
//a FileObject for the registered template,
//and a DataObject from the FileObject:

StringBuilder sb0 = new StringBuilder();
HashMap hashMap0 = new HashMap();
FileObject template0 = FileUtil.getConfigFile("CustomerSalesTemplates/project0.txt");
DataObject dTemplate0 = DataObject.find(template0);//For the second project,
//do the same as the above:

StringBuilder sb1 = new StringBuilder();
HashMap hashMap1 = new HashMap();
FileObject template1 = FileUtil.getConfigFile("CustomerSalesTemplates/project1.txt");
DataObject dTemplate1 = DataObject.find(template1);//Start the progress bar:
ProgressHandle handle = ProgressHandleFactory.createHandle("Comparing projects...");
handle.start();
//Assume we only want to compare two projects:
if (context.size() == 2) {
for (int i = 0; i < context.size(); i++) {
//Get the current project:
Project project = context.get(i);
//Create a db connection per project,
//using the utility class described yesterday:

DBProps config = new DBProps();
FileObject projDir = project.getProjectDirectory();
config.setDbHost(projDir.getPath() + "/.derby");
config.setDatabase("sample");
config.setUsername("app");
config.setPassword("app");
//Create a new entity manager for the db:
EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("com.customer_CustomerSalesDomain_nbm_1.0-SNAPSHOTPU", config.buildPersistenceProperties());
EntityManager entityManager = entityManagerFactory.createEntityManager();
//Get all the Customer content from the current db,
//and write all the names into the StringBuilders:

Query query = entityManager.createQuery("Select c From Customer c");
List<Customer> resultList = query.getResultList();
for (Customer customer : resultList) {
if (i == 0) {
sb0.append(customer.getName()).append("\\n");
} else if (i == 1) {
sb1.append(customer.getName()).append("\\n");
}
}
}
//Put the StringBuilders into the HashMap,
//which will be passed to the template creator:

hashMap0.put("content0", sb0.toString());
hashMap1.put("content1", sb1.toString());
//Get a time stamp, for the name of the folder
//that will contain the two text files:

String stamp = now().toString();
//Create a "Diffs" folder, if it doesn't exist:
FileObject diffs = FileUtil.getConfigFile("Diffs");
if (diffs == null) {
FileUtil.getConfigRoot().createFolder("Diffs");
}
//Create a subfolder named after the timestamp,
//and put the two files there:

FileObject checkedDiffs = FileUtil.getConfigFile("Diffs").createFolder(stamp);
DataFolder df = DataFolder.findFolder(checkedDiffs);
DataObject dobj0 = dTemplate0.createFromTemplate(df, "project0", hashMap0);
DataObject dobj1 = dTemplate1.createFromTemplate(df, "project1", hashMap1);
FileObject createdFile0 = dobj0.getPrimaryFile();
FileObject createdFile1 = dobj1.getPrimaryFile();
//Prepare to pass the text files to the Diff Viewer:
File textFile0 = FileUtil.toFile(createdFile0);
File textFile1 = FileUtil.toFile(createdFile1);
StreamSource project0 = StreamSource.createSource("name1",
"Project 1", "text/plain",
textFile0);
StreamSource project1 = StreamSource.createSource("name2",
"Project 2", "text/plain",
textFile1);
//Create the Diff Viewer:
diff(project0, project1, stamp);
//Stop the progress bar:
handle.finish();
}
} catch (IOException ex) {
Exceptions.printStackTrace(ex);
}
}//Create the Diff Viewer on the fly,
//making sure that it won't be opened when the app restarts:

public void diff(final StreamSource projectA, final StreamSource projectB, final String stamp) {
SwingUtilities.invokeLater(new Runnable() {
@Override
public void run() {
try {
DiffView view = Diff.getDefault().createDiff(projectA, projectB);
TopComponent tc = new TopComponent() {
@Override
public int getPersistenceType() {
return PERSISTENCE_NEVER;
}
};
tc.setDisplayName("Diff Viewer" + " -- " + stamp);
tc.setLayout(new BorderLayout());
tc.add(view.getComponent(), BorderLayout.CENTER);
tc.open();
tc.requestActive();
} catch (IOException ex) {
}
}
});
}//Utility method for creating the timestamp:
public static final String DATE_FORMAT_NOW = "yyyy-MM-dd HH:mm:ss";
public String now() {
Calendar cal = Calendar.getInstance();
SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_NOW);
return sdf.format(cal.getTime());
}
}

That's it, you're done. (Yes, lots of conversions between different types of objects, but unavoidable since different methods have different requirements and different functionality comes from different subsystems of the NetBeans Platform.) Run the application, open the projects, right-click on two of them, choose "Diff Databases", and you'll see something like this:

Next time, we'll read our "Diffs" folder, created in the user directory via the code above, and display the diff history so that the user can do interesting things with it (i.e., open a previous diff in the Diff Viewer and then compare it to a later diff).

Join the discussion

Comments ( 1 )
  • Dmitry Thursday, March 24, 2011

    Hi, excellent article, thanks,

    I have mavenized netbeans platform based application (release6.9.1) and wanted to add template handling in my module M1. For that I added OpenIDE-Module-Needs: .... string to manifest of M1. For providing freemarker I added also dependency on org-netbeans-libs-freemarker to my _application_ module as described in this article (It also required me to add dependency on org-netbeans-modules-queries because it isn't compiled without that).

    Everything compiled Ok but when I tried to start application I got message "....M1 - No module providing the capability javax.script.ScriptEngine.freemarker could be found."

    Any ideas what is wrong in my case ?

    Thanks for your attention


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.