Introduction

Last Monday, we began talking about persistence for both the iPhone and Android.
This allows us to store information that will last between invocations of our application.
In terms of the model-view-controller paradigm often used to create GUI applications, persistence techniques will correspond to models.
Model objects often provide methods for controllers to access persistence data and responsible for packing/unpacking this data.
We can often test a model independent of any controller by writing unit tests of its functionality.
Since we already know how to do logging we could use logging to provide the output of such tests.
Today, we aren't going to write any complete classes but we will use logging to test file I/O etc. i.e., we are writing tests for the built-in classes of these platforms.

More Intro

For both platforms, we said that there is a Unix-like filesystem we can access.
We saw we could view the contents of the iPhone Simulator filesystem by using Finder and going to:
```
~/Library/Application Support/iPhoneSimulator/User/
```

For Android, we can open in Eclipse

Window > Show View > Other... > Android > File Explorer.

We also showed in Android how SharedPreferences get stored in /data/data/PACKAGE_NAME/shared_prefs.

iPhone Filesystem Test

We could put the following code in some method that gets called during the start-up of our application:

NSArray *paths = NSSearchPathForDirectoriesInDomains(
    NSDocumentDirectory, NSUserDomainMask, YES); //look for Documents directories in user home folder 
                                                 //YES means expand tilde directories 

NSString *documentsDirectory = [paths objectAtIndex:0]; //Will only be one such dir so same

//For temporary dir could have done
//NSString *tempPath = NSTemporaryDirectory
	
NSString *filename = [documentsDirectory stringByAppendingPathComponent:@"theFile.txt"];
	
if([[NSFileManager defaultManager] fileExistsAtPath:filename])
{
   NSArray *readArray = [[NSArray alloc] initWithContentsOfFile:filename];
   NSLog(@"I read from the file: %@", [readArray objectAtIndex:0]);
   [readArray release];
}
NSMutableArray *storeArray = [[NSMutableArray alloc] init];
[storeArray addObject:@"Some interesting Data"];
[storeArray writeToFile:filename atomically:YES ];
  // writing such an array stores the data as XML much like a plist
NSLog(@"I wrote some data");
[storeArray release];

Android FileSystem Test (Read Part)

This could be put in your Activity's onCreate method:

File testFile = new File("data/data/org.pollett/files/filename.txt");
//another useful directory is /sdcard for the sd card in the device
//if it exists. res/raw is also a useful directory for resource files 

FileInputStream fis = null;
if(testFile.exists())
{
   try
   {
      fis = this.openFileInput("filename.txt");
      byte[] reader = new byte[fis.available()];
      while(fis.read(reader) != -1){}
      Log.i("AndroidPersistence", "I just read:\n" +
         new String(reader) );
   }
   catch (IOException ie)
   {
       Log.e("AndroidPersistence", "File Read Error");
   }
   finally
   {
       if(fis !=null) 
        {
           try {fis.close();} catch(Exception e) {}
         }
   }
}

Android FileSystem Test (Write Part)

FileOutputStream fos = null;
try
{
   fos = this.openFileOutput("filename.txt", Context.MODE_PRIVATE);
   fos.write("hi there\n".getBytes());
   Log.i("AndroidPersistence", "I just wrote stuff\n");
}
catch (IOException ie)
{
   Log.e("AndroidPersistence", "File Write Error");
}
finally
{
   if(fos !=null) 
   {
      try {fos.close();} catch(Exception e) {}
   }
}

SQLite

Both iPhone and Android have a very simple database system (SQLite) built-in.
To use SQLite on the iPhone you import the header "/usr/include/sqlite3.h". You also should add the libsqlite3.dylib framework to the project.
On Android, you import android.database.sqlite.*
You should think of a database as a collection of tables.
Each table consists of a set of rows, and each row is in turn split into columns.
In Android databases are stored in /data/data/[PACKAGE_NAME]/database/

Database Commands

Commands in SQLite are written in SQL, a language for manipulating databases.

Some common commands are:

CREATE -- create a table

CREATE TABLE IF NOT EXISTS 
  EMPLOYEE (ID INTEGER PRIMARY KEY, NAME TEXT, AGE INTEGER)

DROP -- get rid of a table
```
DROP TABLE IF EXISTS MY_TABLE
```

INSERT -- add rows to a table


INSERT OR REPLACE INTO EMPLOYEE (ID, NAME, AGE) VALUES (5, 'Bob', 30)

DELETE -- delete rows
```
DELETE FROM tablename
WHERE cond
```
An example condition might be age: > 25
SELECT -- returns rows and columns from a cartesian product of tables according to some criteria
```
SELECT col_name1, col_name2, ..., col_name_n /*to empahsize what table do: table_name.col_name */
FROM table_name_1, table_name_2, ..., table_name_m
WHERE cond
```
an example condition might be: EMPLOYEE.ID = PROJECT.directorID AND PROJECT.budget = 1000000

SQLite on the iPhone

To open an SQLite database on the iPhone you use the syntax:

sqlite3 *database;
int result = sqlite3_open("/path/to/database", &database);

If the database does not exist, this will create it.
result will equal SQLITE_OK if this command succeeds.
SQLite is written in C so its arguments are C-strings not NSStrings.
To convert to an NSString to a C string we can use:
```
char *cStringPath = [someStringObj UTF8String];
```
When we are done with the database, we should close the database:
```
sqlite3_close(database);
```

Executing Commands on an SQLite Database on the iPhone

The way to issue CREATE, DROP, INSERT, DELETE statements is generally similar:

char *errorMsg;
const char *createSQL ="CREATE TABLE IF NOT EXISTS PEOPLE (ID INTEGER PRIMARY KEY, HOBBY TEXT)";
int result = sqlite3_exec(database, createSQL, NULL, NULL, &errorMsg);

If an SQLite command returns data, like a SELECT you have to be a little more clever:

NSString *query = @"SELECT * FROM PEOPLE";
sqlite3_stmt *statement;
int result = sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, nil);
//could check if result is SQLITE_OK

while(sqlite3_step(statement) == SQLITE_ROW){
  // for the current row let's get out some columns
  int id = sqlite3_column_int(statement, 0); //0th column's data
  char *hobby = (char*)sqlite3_column_text(statement, 1);
  // do something with the data for this row
}
sqlite3_finalize(statement);

Android Database Persistence

To test out our database code we need to import the following packages:

import android.database.sqlite.*;
import android.database.*;
import android.content.*;
import android.util.*;

We will make use of two important classes:
- SQLiteOpenHelper - an abstract class whose onCreate and onUpgrade methods need to be implemented. Objects of this class are used to open/create the database
- SQLiteDatabase - objects of this class encapsulate a particular database and allow us to manipulate it.

On Testing Code

The following code just writes to the log file based on our SQL test.
We put it in the onCreate method of a dummy testing activity.

dbOpenHelper and db were private instance variables of our class.

dbOpenHelper = new DBOpenHelper(this, "My_Database", 3);
        
db = dbOpenHelper.getWritableDatabase();
        
//insert into database

db.execSQL("INSERT OR REPLACE INTO PEOPLE VALUES (5, 'test')");
db.close();
        
dbOpenHelper = new DBOpenHelper(this, "My_Database", 3);
        
db = dbOpenHelper.getReadableDatabase();
Cursor c = db.rawQuery("SELECT * FROM PEOPLE", null);

    	
for(int i=0; i< c.getCount(); i++)
{
   c.moveToNext();
   Log.i("SqliteAndroid", "Id="+c.getInt(0));
   Log.i("SqliteAndroid", "Hobby="+c.getString(1));        	
}
        
        
db.close();

Subclassing SQLiteOpenHelper

We subclass SQLiteOpenHelper as an inner class of our test activity

Here is the code:

    private static class DBOpenHelper extends SQLiteOpenHelper
    {
       public DBOpenHelper(Context context, String dbName, int version )
       {
    	  // third argument id to a cursor factory -- we don't care about
    	  super(context, dbName, null, version);
       }
       
       @Override
       public void onCreate(SQLiteDatabase db)
       {
    	   try
    	   {
    		  db.execSQL("CREATE TABLE IF NOT EXISTS PEOPLE (ID INTEGER PRIMARY KEY, HOBBY TEXT)"); 
    	   }
    	   catch(SQLException e)
    	   {
    		  Log.e("SqliteAndroid", "DBOpenHelper", e);
    	   }
       }
       
       @Override
       public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
       {
    	  db.execSQL("DROP TABLE IF EXISTS PEOPLE");
    	  this.onCreate(db);
       }
    }

Persistence, Sqlite

Outline