SRS - the final frontier

Ok. We're finally getting to the piece where we're going to add data to our schedule table programmatically - or update it if it's already there. The nice thing about the schedule is that it has a one-to-one correspondence to the question. Heck - I could have put them in the same table, come to thing of it. But, before getting all involved in that, let's see if we can make what we have work.

Ok, so, here's the current routine:

public static void updateSRSschedule(Question question, long secsRemaining, boolean correct, Context context) {

DataBaseHelper dbHelper = DataBaseHelper.createDB(context);

WordSchedule ws = dbHelper.selectScheduleById(question.id);

dbHelper.close();

Log.d(TAG, "========================== SRS ===========================");

Log.d(TAG, "Question was: " + question);
if (null == ws){
Log.d(TAG, "was null");
}
else {
Log.d(TAG, ws.toString());
}

// calculate the difference
if (correct){
if (secsRemaining > 5) {
// slow
}
else {
// fast
}
}
else {
// wrong
}

So, what I really need to do is figure out how to write and update data on the sqllite database. Let's google sqllite update android.

This one looks pretty good:

http://www.screaming-penguin.com/node/7742

I'll throw my comments into the code..



public class DataHelper {

private static final String DATABASE_NAME = "example.db";
private static final int DATABASE_VERSION = 1;
private static final String TABLE_NAME = "table1";

private Context context;
private SQLiteDatabase db;


// ok, so here's an SQLLite Statement

private SQLiteStatement insertStmt;


// and the actual, parameterized insert

private static final String INSERT = "insert into "
+ TABLE_NAME + "(name) values (?)";


public DataHelper(Context context) {
this.context = context;
OpenHelper openHelper = new OpenHelper(this.context);

// this is key
this.db = openHelper.getWritableDatabase();

// ok, and the insert statement is compiled.
// Hmm - I wonder if the query will work as an insert statement?
// And, should I convert my queries to compiled statements like this?

this.insertStmt = this.db.compileStatement(INSERT);
}

public long insert(String name) {

// this
this.insertStmt.bindString(1, name);
return this.insertStmt.executeInsert();
}

public void deleteAll() {
this.db.delete(TABLE_NAME, null, null);
}

public List selectAll() {
List list = new ArrayList();
Cursor cursor = this.db.query(TABLE_NAME, new String[] { "name" },
null, null, null, null, "name desc");
if (cursor.moveToFirst()) {
do {
list.add(cursor.getString(0));
} while (cursor.moveToNext());
}
if (cursor != null && !cursor.isClosed()) {
cursor.close();
}
return list;
}

private static class OpenHelper extends SQLiteOpenHelper {

OpenHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE " + TABLE_NAME + "
(id INTEGER PRIMARY KEY, name TEXT)");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w("Example", "Upgrading database, this will drop tables and recreate.");
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
onCreate(db);
}
}
}

Ok, it looks like something I can work with.

Let's look at the actual insert first:

insert into word_review_schedule (_id, last_review_date,next_review_date) values (1098, "2001-01-01", "2011-07-19");

Ok, here's our insert statement:

private static final String INSERT = "insert into " + SCHEDULE_TABLE_NAME
+ "(_id, last_review_date,next_review_date) values (?, ?, ?, ?)";



Next, are we opening the database as writable?

if (null == myDataBase) {

myDataBase = SQLiteDatabase.openDatabase(myPath, null,
SQLiteDatabase.OPEN_READWRITE);

}

I changed it to READWRITE.

What else?

Compile the insert statement:

this.insertStmt = this.db.compileStatement(INSERT)

We'll put that in the constructor, I guess.

Ok, and we als need to add this:

private SQLiteStatement insertStmt;

And the method:

public long insert(String name) {

this.insertStmt.bindString(1, name);

return this.insertStmt.executeInsert();

}


Here is some info on SQLLiteStatement:

void execute()
Execute this SQL statement, if it is not a SELECT / INSERT / DELETE / UPDATE, for example CREATE / DROP table, view, trigger, index etc.

long executeInsert()
Execute this SQL statement and return the ID of the row inserted due to this call.

// ok, this is how we'll do the update

int executeUpdateDelete()
Execute this SQL statement, if the the number of rows affected by execution of this SQL statement is of any importance to the caller - for example, UPDATE / DELETE SQL statements.


But - where's the bindString? I have an int I need to insert.

Ok, so, I think I'm going to have to cast it to a long. Nope, it seems to take an int no problem.


public long insert(int id, String lastReviewDate, String nextReviewDate) {

this.insertStmt.bindLong(1, id);
this.insertStmt.bindString(2, lastReviewDate);
this.insertStmt.bindString(3, nextReviewDate);

return this.insertStmt.executeInsert();

}

Ok. so, how about a test, with just phony data for now?


if (null == ws) {

Log.d(TAG, "was null");

dbHelper = DataBaseHelper.createDB(context);
dbHelper.insert(question.id, "2011-07-18", "2011-07-23");
dbHelper.close();

}

I really should think about just moving this into question - there would be no need to create a row, no heavy-duty join statements.

How am I going to look at it? Now that I have root, I should be able to run SQLLite from the command line.

http://developer.android.com/guide/developing/tools/adb.html

Examining sqlite3 Databases from a Remote Shell

From an adb remote shell, you can use the sqlite3 command-line program to manage SQLite databases created by Android applications. The sqlite3 tool includes many useful commands, such as .dump to print out the contents of a table and .schema to print the SQL CREATE statement for an existing table. The tool also gives you the ability to execute SQLite commands on the fly.

To use sqlite3, enter a remote shell on the emulator instance, as described above, then invoke the tool using the sqlite3 command. Optionally, when invoking sqlite3 you can specify the full path to the database you want to explore. Emulator/device instances store SQLite3 databases in the folder /data/data//databases/.

Here's an example:

$ adb -s emulator-5554 shell
# sqlite3 /data/data/com.example.google.rss.rssexample/databases/rssitems.db
SQLite version 3.3.12
Enter ".help" for instructions
.... enter commands, then quit...
sqlite> .exit

Or, better yet, copy the sqllite data to my mackbook and read it with razorsql.

Btw, this is what I will soon be doing. I didn't realize Exerciser Monkey was so easy.


UI/Application Exerciser Monkey

The Monkey is a program that runs on your emulator or device and generates pseudo-random streams of user events such as clicks, touches, or gestures, as well as a number of system-level events. You can use the Monkey to stress-test applications that you are developing, in a random yet repeatable manner.

The simplest way to use the monkey is with the following command, which will launch your application and send 500 pseudo-random events to it.

$ adb shell monkey -v -p your.package.name 500




Ok, let's run our test.

Ok, it's working. I pulled in in with adb pull an saw another record there, very nice. But, there is a noticeable delay after selecting the answer. I have an index set up on date. I dunno. Well, let's code the update statement.


This works:

update word_review_schedule set last_review_date = "2011-12-12", next_review_date = "2012-12-12" where _id = 1268

private static final String UPDATE = "update " + WORD_REVIEW_SCHEDULE
+ "set last_review_date = ?, next_review_date = ?) here _id = ?";

Put the compile in, add the updateStmt:

private SQLiteStatement updateStmt;

Oops. Theres a bit of problem. The updateDelete statement isn't available until api 11. What's that? Honeycomb or something?


http://code.google.com/p/android/issues/detail?id=15921

Yeah, the AVD manager says 11 is Android 3.0. I don't want to go there - I'm on Nexus and I still only have 2.3.4, I believe. So, can you update or delete something in android? Or is is that you have to use a non-compiled statement?

Ok, this url looks helpful:

http://www.higherpass.com/Android/Tutorials/Accessing-Data-With-Android-Cursors/


ContentValues updateCountry = new ContentValues();
updateCountry.put("country_name", "United States");
db.update("tbl_countries", updateCountry, "id=?", new String[] {Long.toString(countryId)});


Ok, let's pull the honeycomb stuff. This is just 3 lines of code.

public void update(int id, String lastReviewDate, String nextReviewDate) {

// private static final String UPDATE = "update " + WORD_REVIEW_SCHEDULE
// + "set last_review_date = ?, next_review_date = ?) where _id = ?";

ContentValues contentValues = new ContentValues();
contentValues.put("last_review_date", lastReviewDate);
contentValues.put("next_review_date", nextReviewDate);
this.myDataBase.update(DataBaseHelper.WORD_REVIEW_SCHEDULE,
contentValues, "_id=?", new String[] { Integer.toString(id) });

}


Ok, this looks like a good try.



According to the docs, there is also this:

execSQL(String sql)
Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data.

And this for the insert:

insert(String table, String nullColumnHack, ContentValues values)
Convenience method for inserting a row into the database.

Ok, this is the one:

update(String table, ContentValues values, String whereClause, String[] whereArgs)
Convenience method for updating rows in the database.


Ok. Well, let's give it a go...

We'll call it like this:


dbHelper = DataBaseHelper.createDB(context);

dbHelper.update(question.id, "2011-07-18", "2011-12-25");

dbHelper.close();

I almost called insert - checking pays of again!

Well, it didn't crash. Let's see if there's a record out there with 12-25.

Ok. Seems to be working! I think I'll check the fine-tuning later.

And that's a wrap.