SQLLite on the pc/mac

After I tested the app a bit last night, I was puzzled to see that newer vocabulary kept coming up, despite the fact that I had done more than the amount required to pass the level. It didn't take me long to figure out what the problem was, though. I have a piece of data in the question table called "freq" for frequency of usages. These aren't rankings, but rather absolute numbers taken from samples of online Japanese textbooks. So, I have't gotten around to officially assigning a frequency sequence or ranking yet, because up until now, just reading the rows in order of frequency has been adequate. But with the SRS algorithm, which is based on scheduled date, the words which would typically fall within the range of the specified frequency sequence or ranking are no longer there - they've been reschedule for a future date. As a result, the sequentially assigned sequence number can now apply to a shifting set of words.

Anyway, the obvious solution to this problem is to create the frequency number as a new field in the question table. This will be a one-off exercise, using the existing code which reads in the data, and the update logic which we implemented yesterday.

So - let's take a look at the code:



for (Question question : rows) {

// The counter is used as the sequentially assigned frequency number.
cntr++;

// null questions mean they haven't been seen yet

if (null == question.nextReviewDate_YYYY_MM_DD) {

// this will need to change to checking question.freq_sequence number

if ((cntr >= startNum) && (cntr <= endNum)) {

// question.number is like an id
quizSequence.add(new Integer(question.number));
}
} else {

// it automatically adds the row if it's not null
// because a scheduled date exists for it and
// that date is .le. the parameter date (should be
// today's date unless testing)

quizSequence.add(new Integer(question.number));

}

}

Ok, first, lets bring up RazorSQL and add the field. Ok, done. I love wizards.



Ok, now, lets modify this code to update the question with a freq_seq:

public void updateFreqSequence(int id, int freqSequence) {

// private static final String UPDATE = "update " + WORD_REVIEW_SCHEDULE
// + "set freq_sequence = ?) 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) });

}


How does this look:

public void updateFreqSequence(int id, int freqSequence) {

ContentValues contentValues = new ContentValues();
contentValues.put("freq_sequence", freqSequence);
this.myDataBase.update(DataBaseHelper.ALL_WORDS_TABLE,
contentValues, "_id=?", new String[] { Integer.toString(id) });

}

We'll have to clear the schedule table to make sure all the questions make it through the quiz. That could be an argument for keeping it as a separate table. The strongest argument against is the time it takes to insert vs. the time it takes to update. And update must be faster.

Actually, we can just comment out all the filters for now.

Let's create a select on everything:

public ArrayList selectAllByFreq() {

// Cursor cursor = this.myDataBase.query("all_words",
// new String[] { "english" }, null, null, null, null, null);


// todo change to join view to pick up next - or not
Cursor cursor = this.myDataBase.query(VIEW_BY_FREQ, new String[] {
"_id", "level", "number", "kanji", "hiragana", "english",
"freq" }, null, null, null, null,
null);

ArrayList rows = new ArrayList();


I added a view to the DB to just select on frequency, without the level.

Ok, how about this for the code:


public void initQuizSequence(Context context, int startNum, int endNum) {

Log.d(TAG, "initQuizSequence");

quizSequence.clear();

DataBaseHelper dbHelper = DataBaseHelper.createDB(context);

//ArrayList rows = dbHelper.selectByLevelAndNextReviewDate(
// jlptLevel, "2011-11-17");


ArrayList rows = dbHelper.selectAllByFreq();


dbHelper.close();

int cntr = 0;


for (Question question : rows) {


cntr++;

dbHelper = DataBaseHelper.createDB(context);


dbHelper.updateFreqSequence(question.id, cntr);

dbHelper.close();

}


Ok, it's going to be a while. For some reason, if I don't run the dbHelper open/close after each update, I get some kind of corrupt image malfromed exception. But in the meantime it's taking about 1 second per update. So, at 5 or 6 thousand words, 6000/60 = 600/6 = 100 minutest - an hour and 1/2.

Well while that's running, let's see how long it takes to run that same routine on the mac.

Well, it's not so simple. A lot of the code is Android specific...I need a driver, probably. I just really dislike it when things take so long. I wonder if there's a way to do it using, like an _id number on the view or something?

Here's a post highly recommended on SO:

http://stackoverflow.com/questions/41233/java-and-sqlite

Ok, pretty good. It created a table called test.db in the root.

Let's see if I can get it to list the table by freq:

Let's try this:

ResultSet rs = stat.executeQuery("select * from v_all_words_by_freq;");
while (rs.next()) {
System.out.println("english = " + rs.getString("english"));
System.out.println("freq = " + rs.getString("freq"));
}


Wow. Now that was fast!

Ok, how about we try out the update statement?

How does this look?

PreparedStatement prep = conn.prepareStatement(
"update all_words set freq_sequence = ? where _id = ?;");

int cntr = 1;

ResultSet rs = stat.executeQuery("select * from v_all_words_by_freq;");
while (rs.next()) {
System.out.println("english = " + rs.getString("english"));
System.out.println("freq = " + rs.getString("freq"));

int _id = rs.getInt("_id");

prep.setInt(1, cntr);
prep.setInt(2, _id);

prep.addBatch();

cntr++;
//prep.setString(2, "politics");

}
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);

And we are done!

The update on the phone is still crawling around at 2500.

Ok, let's kill that and move the updated database into the project. That was great. What a dramatic exhibition of the difference in processing power between a phone and a laptop/macbook.

Ok, that's a wrap for now. I'll pick this up in the next post.

For prosperity, here is the body of the SO post:

http://stackoverflow.com/questions/41233/java-and-sqlite

Hey I found your questions while search for information with SQLite and Java. Just thought I add my answer which I also posted on my blog.

I have been coding in Java for a while know. I have also known about SQLite but never used it… Well I have have used it through other applications but never in an app that I coded. So I needed it for a project this week and its so simple use!

First I found the website of David Crawshaw who has a Java JDBC driver for SQLite. Just add his JAR file to your classpath and import java.sql.*

His test app will create a database file, send some SQL commands to create a table, store some data in the table, and read it back and display on console. It will create the test.db file in the root directory of the project.


public class Test {
public static void main(String[] args) throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists people;");
stat.executeUpdate("create table people (name, occupation);");
PreparedStatement prep = conn.prepareStatement(
"insert into people values (?, ?);");

prep.setString(1, "Gandhi");
prep.setString(2, "politics");
prep.addBatch();
prep.setString(1, "Turing");
prep.setString(2, "computers");
prep.addBatch();
prep.setString(1, "Wittgenstein");
prep.setString(2, "smartypants");
prep.addBatch();

conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);

ResultSet rs = stat.executeQuery("select * from people;");
while (rs.next()) {
System.out.println("name = " + rs.getString("name"));
System.out.println("job = " + rs.getString("occupation"));
}
rs.close();
conn.close();
}
}