The Mobile Experiment V 1/2 – SQLite

In my last post, we have a short introduction about the SQLite support on BB OS. This time, I’m gonna start implementing it for the TimeTracker application.

My first step was to create a new class called TimeTrackerDatabase, which is responsible for all database activities, i.e, database and table creation and data insertion. Remember that my application is very simple, so there is no need to edit and/or delete data.

The TimeTrackerDatabase constructor is responsible for verifying whether the SD card is present or not, as well as creating the database file in the proper place. Actually, the SD card test is done by another class private method called hasSD(), which was copied from one of the RIM’s sample code:

private boolean hasSD() {
   // Determine if the SDCard is present
   String root = null;
   Enumeration e = FileSystemRegistry.listRoots();
   while (e.hasMoreElements()) {
       root = (String)e.nextElement();
       if(root.equalsIgnoreCase("sdcard/")) {
           return true;
       }
   }
   return false;
}

The constructor itself will call the function above, and if the SD card is not present, it will show a message saying that the app requires a SD card to work. The way it is done below, the message is shown using the event thread. The invokeLater method will ensure that the dialog will be displayed in the next available oportunity by the main event thread. There is an excellent article about Blackberry UI Threading here: http://www.thinkingblackberry.com/archives/182.

Here is the complete constructor:

public TimeTrackerDatabase() throws Exception {
 if(!hasSD()) {
     UiApplication.getUiApplication().invokeLater(new Runnable()
     {
         public void run() {
           Dialog.alert("This application requires an SD card to be present. Exiting application...");
           System.exit(0);
         }
      });
 } else {
    dbLocation = "/SDCard/databases/TimeTracker/";
    // Create URI
    uriTracker = URI.create(dbLocation + DB_NAME);
    // Open or create a plain text database.  This will create the
    // directory and file defined by the URI (if they do not already exist).
    Database dbTracker = DatabaseFactory.openOrCreate(uriTracker);
    // Close the database in case it is blank and we need to write to the file
    dbTracker.close();
 }
}

If the application starts with no SD card installed, the following message will appear and the application will close.

The else portion of the code above is responsible for open or create the database file.

Since the database is created on the first run, we need to create the table as well. The nice about using SQLite is that you can use SQL syntax (what a discovery!), which I know and have used a little bit in the past. The following method creates the table schema, and it will be called only once during the application life-time. At first I thought it would be easier to install the database file with all tables, but at the end, it is really much simpler make the application deal with its own database. It is easy during the first installation, and easier during eventual updates.

 public boolean createDBSchema() throws Exception
 {
    openDatabase();
    Statement st = dbTracker.createStatement("CREATE TABLE IF NOT EXISTS " +
                                             "'tasks' ( " +
                                             " 'project' TEXT,  " +
                                             " 'notes' TEXT, " +
                                             " 'begin_date' INTEGER, " +
                                             " 'end_date' INTEGER " +
                                             " )" );

   st.prepare();
   st.execute();
   st.close();
   dbTracker.close();

   return true;
 }

The openDatabase method above just opens the database file using the dbTracker handler (see below). The Statement object is really simple to work with, and resembles me my old days using Visual Basic or Delphi.

public boolean openDatabase() throws Exception
{
   try {
      dbTracker = DatabaseFactory.open(uriTracker);
   }  catch ( Exception e ) {
      System.out.println( e.getMessage() );
      e.printStackTrace();
      return false;
   }
   return true;
}

Last but not least (I always wanted to use this cliché), I need a method that allows me to include data into the database. Everything is done using the INSERT SQL statement, like the following one:

INSERT INTO 'tasks' (project, notes, begin_date, end_date)
values ('Paint the house',  'Boring but necessary', 1295150379061,1295150407736)

The method to perform this task will basically take the parameters and put them together in a way to build a statement like the above:

public boolean saveData(TimeTrackerData data) throws Exception
{
	String stmt;

	openDatabase();
	stmt = "INSERT INTO 'tasks' (project, notes, begin_date, end_date VALUES (" +
				data.projectName +"'," +
				data.noteTask +"'," +
			    String.valueOf(data.beginDate) + "," +
			    String.valueOf(data.endDate) + ")";

	Statement st = dbTracker.createStatement(stmt);
	st.prepare();
	st.execute();
	st.close();
	dbTracker.close();

	return true;
}

As you can see, I decided to create another class, just to keep the values that will be saved into the database. I believe this is a nicer approach because we don’t have to keep changing the saveData method signature. The TimerTrackerData is a class but it doesn’t do anything than keeping the table fields as class proprieties.

public class TimeTrackerData {
	String projectName;
	String noteTask;
	long beginDate;
	long endDate;
}

Finally, back to the main class (our UI), we have to call the proper methods from the database class to perform as expected.

private void saveDatabaseData()
{
	// save data to the database
	TimeTrackerData trackerData = new TimeTrackerData();
	trackerData.projectName = projectListField.getEditField().toString();
	trackerData.noteTask = notesField.getText();
	trackerData.beginDate = dateField.getDate();
	trackerData.endDate = System.currentTimeMillis();
	try {
		ttDB.saveData(trackerData);
	} catch (Exception e) {
		System.out.println( e.getMessage() );
		e.printStackTrace();
	}

}

It seems this make this simple application complete. If you want the full source code, follow me on Twitter (twitter.com/mobilecreators) and ask for it! I’ll be glad to send it to you. Or leave a comment here, too!

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: