Spaced repetition systems (SRS) - revisited

I had previously explored implementing a space repetition system, and even set up the database to handle it. At the time, I wasn't sure I wanted to implement it though, and decided to leave the decision until later. Well, it's later, and I'm going to give it try. I have some concerns about it, for example support, database updates, etc. But it seems like a really effective way to hit bring the benefits of long term memory and also focus on the most problematical questions.

Here's the first blog I made about it, which is kind of an overview:

http://gettingintomobile.blogspot.com/2011/06/spaced-repetition-learning-systems-srs.html

The basic idea is I'm going to bring up a list of questions for each level. It will include any questions that fit into the current range of numbers and are *not* scheduled already, as well as any questions that have been scheduled for that date. So, ultimately, the database will have a row for each word, and it's scheduled date.

I also experimented with some select statements. Are they in the current db? Let me check RazorSQL.

No, the views aren't there, but the schedule table is.

Ok, we'll get back to that. Let's check out the next post...

That really just talks about Enum, which is why it's a string in the database.

That was kind of a hassle, I remember. How is it stored specifically in the database?

At worst, it can be an if statement. It avoids having to re-sequence numbers.

Ok, well. Theres nothing left to do but do it.

Ok, here's the join:

Select v_all_words_by_level_freq._id, v_all_words_by_level_freq.level, v_all_words_by_level_freq.number, kanji, hiragana, english, freq, rating, last_review_date,
next_review_date
from v_all_words_by_level_freq
left join word_review_schedule
on v_all_words_by_level_freq._id = word_review_schedule._id

So, this is selecting the whole table. What we will need is a where clause, which would be something like

where word_review_schedule. next_review_date is null

So the whole thing is:

Select v_all_words_by_level_freq._id, v_all_words_by_level_freq.level, v_all_words_by_level_freq.number, kanji, hiragana, english, freq, rating, last_review_date,
next_review_date
from v_all_words_by_level_freq
left join word_review_schedule
on v_all_words_by_level_freq._id = word_review_schedule._id
where word_review_schedule. next_review_date is null

ok, that works. Next we need where date is less than or equal today's date on the schedule table.

First, lets insert a row into that table to play with.

insert into word_review_schedule (_id, rating, last_review_date,
next_review_date) values (1, ", "", "")

Ok, but let's put in real dates.

Also, I don't think we need the rating. We just need the schedule date. It could be useful for diagnostic purposes.

insert into word_review_schedule (_id, rating, last_review_date,
next_review_date) values (1, "RATING", date() , date() )

That works. Ok, let's see if we can add a value to the second date.

Ah, here we go:

http://www.sqlite.org/lang_datefunc.html


The time string can be followed by zero or more modifiers that alter date and/or time. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right; order is important. The available modifiers are as follows.

NNN days
etc.

All I need is the days.

insert into word_review_schedule (_id, rating, last_review_date,
next_review_date) values (2, "RATING", date() , date(), '+1 day' )

Oh, it looks like that only applies to select statement. Maybe there's a way I could subselect it? Probably I will just have to do it programatically.

insert into word_review_schedule (_id, rating, last_review_date,
next_review_date) values (1, "RATING", date() , "2011-07-18" )

Ok, that worked. Now let's see if we can do a select.



Select v_all_words_by_level_freq._id, v_all_words_by_level_freq.level, v_all_words_by_level_freq.number, kanji, hiragana, english, freq, rating, last_review_date,
next_review_date
from v_all_words_by_level_freq
left join word_review_schedule
on v_all_words_by_level_freq._id = word_review_schedule._id
where word_review_schedule.next_review_date is null or word_review_schedule. next_review_date <= date()


That worked...let's make sure it's just selecting the one record.

Select v_all_words_by_level_freq._id, v_all_words_by_level_freq.level, v_all_words_by_level_freq.number, kanji, hiragana, english, freq, rating, last_review_date,
next_review_date
from v_all_words_by_level_freq
left join word_review_schedule
on v_all_words_by_level_freq._id = word_review_schedule._id
where word_review_schedule. next_review_date <= date()

Yes. Excellent. That's a big deal.

Ok, the last svn version before this is the comment "password protect resource"

Alright, I've located where in the code I'm pulling the data currently:

Select _id, level, number, kanji, hiragana, english, freq, rating, last_review_date,
next_review_date
from v_words_schedule_join
where level = 5 and (next_review_date <= "2011-07-18" or next_review_date is null)


So, this looks pretty good. We're going to have to get rid of the break, because the input isn't just limited to the start end/number. We will have to add an else statement. It might be just easier to use the same select clause - just the level - and check all the dates? It would be a little bit more efficient to use a where clause that selects on date. I guess we should do that.

Ok, how about we add a routine to the DataBaseHelper? We'll add a date parameter. That should help with testing - we won't have to constantly change system date.


Ok, so, first I guess we need to add this view:


Select v_all_words_by_level_freq._id, v_all_words_by_level_freq.level, v_all_words_by_level_freq.number, kanji, hiragana, english, freq, rating, last_review_date,
next_review_date
from v_all_words_by_level_freq
left join word_review_schedule
on v_all_words_by_level_freq._id = word_review_schedule._id
order by next_review_date

We'll call it v_words_schedule_join.

Ok, that's done.

Now, how to modify the database routine.

Ok, what how exactly does the query work again?

Let's check:

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

Ok there's 3 queries:

Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)
Query the given table, returning a Cursor over the result set.

this one (above) is close enough to what we've been doing.

Ok, how to I do multiple parameters?

Ah, here we go:

selection A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.

This is the select, actually:

Select _id, level, number, kanji, hiragana, english, freq, rating, last_review_date,
next_review_date
from v_words_schedule_join
where level = 5 and (next_review_date <= "2011-07-18" or next_review_date is null)

So, the routine should look like this:

Cursor cursor = this.myDataBase.query(VIEW_BY_LEVEL_FREQ, new String[] {
""level", "number", "kanji", "hiragana", "english", "freq", "next_review_date" },
"level = ? (next_review_date is null or next_review_date <=?)", new String[] { strLevel, strDate }, null, null, null);

Added the date parameter. Modified Question to have a string attribute for next date.

Ok. Here's what we have so far:


public ArrayList selectByLevelAndNextReviewDate(int levelIn, String date_YYYY_MM_DD) {

String strLevel = Integer.toString(levelIn);

Cursor cursor = this.myDataBase
.query(VIEW_WORD_SCHEDULE_JOIN,
new String[] { "level", "number", "kanji", "hiragana",
"english", "freq", "next_review_date" },
"level = ? and (next_review_date is null or next_review_date <=?)",
new String[] { strLevel, date_YYYY_MM_DD }, null, null,
null);

ArrayList rows = new ArrayList();
if (cursor.moveToFirst()) {
do {
int level = cursor.getInt(0);
int number = cursor.getInt(1);
String kanji = cursor.getString(2);
String hiragana = cursor.getString(3);
String english = cursor.getString(4);
int freq = cursor.getInt(5);
String nextReviewDate_YYYY_MM_DD = cursor.getString(6);

if (kanji.length() == 0) {
kanji = hiragana;
}

Question row = new Question(level, number, kanji, hiragana,
english, freq, nextReviewDate_YYYY_MM_DD);
rows.add(row);

} while (cursor.moveToNext());
}

if (cursor != null && !cursor.isClosed()) {
cursor.close();
}

return rows;
}



Ok. Let's call this routine and see what happens. First, clear the schedule. Ok, after changing a couple of typos and making sure the to copy in the database, it seems to be working fine, as usual. So, it's able to access the database.

Ok, this post is long enough. I'm going to start testing this a little bit on on the next blog post

Cleaning up loose ends

In the last posting, we explored whether it was worth it to chase down about 250 or so words without a frequency assigned to them, and decided, nope. Instead, we punted and assigned a unique negative number to each unmatched word. This is sufficient to be able to treat the problem without messy if statements - except perhaps a non-display of the frequency number in such a case.

This post, then, is simply about cleaning up our current sqlite databse, which is currently strewn with views and tables spawned in the nonetheless successful effort to assign a frequency of word use number to the japanese words in the database. The first order of the day, then, is to backup up the database. Although this can be done with a file copy, we'll use our trial version of RazerSQL to do it from a menu.

That competed, we want to move the data from all_words_temp1 back into all_words.

First, clear out the table:

delete from all_words

Now copy into it:

INSERT INTO all_words SELECT * FROM all_words_temp1

It looks like we have an extra column somewhere. Ok, all_words doesn't have freq. Let's drop the table and recreate it.

Dropped, now create:

CREATE TABLE all_words
(
_id INTEGER,
level INTEGER,
number INTEGER,
kanji TEXT(25),
hiragana TEXT(25),
english TEXT(50),
freq INTEGER
)


And try again:

INSERT INTO all_words_bak

SELECT
_id, level, number, kanji, hiragana, english, freq
FROM
all_words

Check the count:

SELECT
count(*)
FROM
all_words

8449.

That sounds right

Ok, it's time to start deleting - everything except all_words, and that android_metadata table.

Hmm...what about indexes, and views that will be needed on the android? I can do it iteratively, but I think I need to put one on freq, at least.

CREATE INDEX level_index
ON all_words (level)

CREATE INDEX number_index
ON all_words (number)

CREATE INDEX freq_index
ON all_words (freq)

And create a view that sorts on level and freq


CREATE VIEW v_all_words_by_level_freq_desc AS SELECT _id, level, number, kanji, hiragana, english, freq
FROM all_words
ORDER BY level, freq DESC

Whoah - I just realized that I have been assuming the frequency was the actual frequency - the most highly used words having the highest number - but actually it's an assigned rank. So, the most frequent number is the lowest, not the highest.

This poses a bit of a problem in terms of assignment of the null frequencies - they have to be higher than any frequency at their levels. What I could do, though, is figure out what the highest number is, and then, add the _id to that constant.

Let's backup the table...

CREATE TABLE all_words_bak
(
_id INTEGER,
level INTEGER,
number INTEGER,
kanji TEXT(25),
hiragana TEXT(25),
english TEXT(50),
freq INTEGER
)



INSERT INTO all_words_bak

SELECT
_id, level, number, kanji, hiragana, english, freq
FROM
all_words



Now:


What's the highest frequency in the table?

SELECT _id, level, number, kanji, hiragana, english, freq
FROM all_words
ORDER BY freq DESC

(I'm sure there's a better way to do the above, like a high function or something, but I don't care. )

89385. So, as long as the frequency assigned is greater than 89385 and unique, we'll get a consistent order.

Let's update using this:

UPDATE all_words SET freq = (_id + 100000) where freq < 0

Let's check it out:

AS SELECT _id, level, number, kanji, hiragana, english, freq
FROM all_words
ORDER BY level, freq


CREATE VIEW v_all_words_by_level_freq AS SELECT _id, level, number, kanji, hiragana, english, freq
FROM all_words
ORDER BY level, freq

Looks good. Ok. That wraps up this session. Next is working through how to update the database to a new version on the android.

Cleaning up the frequency....

In the last post, I managed to get ten more word frequencies assigned by jumping through some SQL hoops. In this post, we'll examine what other words remain with unassigned frequencies, and devise a strategy for handling them - or, perhaps, figuring out how best to punt.

First, how many remain?

select * from all_words_temp1 where freq is null

235 remaining.


The missing frequencies fall into several groups:

1. Double-words separated by a "/" such as "いい/よい" .

2. Words that are really two words, such as "すぐに".

3. Number words with counters like "ようか".

4. Idiomatic conjugations such as "ください"

5. Double words separated by a "," such as "より、ほう"

6. Double words separated by a raised point such as "あいさつ・する".

7. Words ending in "さん" such as "おくさん",

8. Words starting with the honorable "ご" such as "ごくろうさま".

9. Words that just don't match, although you would think they would.

Level 5 has 29 nulls out of 669, 4.3%.
Level 4 has 30 nulls out of 634, 4.73
Level 3 has 17 nulls out of 1835 .92%
Level 2 has 85 nulls out of 1835, 4.6%
Level 1 has 74 nulls out of 3476, 2.1%

Altogether, there are 235 nulls out of 8214 rows, so we have overall coverage of 97%. I'm going to punt and say that's adequate. The customer will still be learning those words - just at the highest game level for that jlpt test level. If a level has 10 words, that's a maximum of 9 out of 183 levels for level 3.

The question is, how to deal with that 3% programmatically? I think the simplest thing is just assign them negative numbers. That way, they'll appear last. What I can do hopefully is just use a negative of the id number and assign that to frequency. So, something like

UPDATE all_words_temp1 SET freq = (_id * -1) where freq is null

should do the trick. Let's backup all words first.


CREATE TABLE all_words_bak2
(level INTEGER,
freq INTEGER,
number INTEGER,
kanji TEXT(25),
hiragana TEXT(25),
english TEXT(50),
_id INTEGER)


Then backup:
INSERT INTO all_words_bak2 SELECT * FROM all_words_temp1

And run the update:
UPDATE all_words_temp1 SET freq = (_id * -1) where freq is null

Perfect. We're almost ready to move on to updating the database on the Android - after backing up and cleaning up.

SQLLite and Android - how to get things moving

In this post, we'll explore how to take an existing sql table (currently in mysql) and get it into sqllite. I have a lot of questions - how to code it, how to load the data, should I start out on the device, the emulator, the Mac? Adopting a "logical" test-first approach, I'm going to reduce this to the simplest step I can, which is transferring the data from mySql (for which the table was created in the previous post) to sqlLite. That needs to pass the logical test of having the data in sqllite.

In fact, that will require me to set up an sql lite database on the mac. I know there's already one running on the android phone, and on the emulator as well. Since it's always better to test on a real device, I'll go with the device. But the question is - how to get the data into the sql lite database? Actually, I ran across a post on how to pre-package your data into an sqllite database for android - let's check it out. It's at http://tinyurl.com/mvtwqm.

Essentially, the code checks for an existing db, and if not, it copies the data from your res folder into your sql lite database folder, where it can then be accessed by your program. From all the comments, it looks as if this is a pretty standard requirement - and that a lot of people had issues with getting the code to run. But, there is also a mention of an SQLite Database Browser. Let's have a look at that, first, at http://sourceforge.net/projects/sqlitebrowser/

The description states "SQLite Database browser is a light GUI editor for SQLite databases, built on top of Qt. The main goal of the project is to allow non-technical users to create, modify and edit SQLite databases using a set of wizards and a spreadsheet-like interface."

That sounds good - does it set up the sql lite database itself? Let's download it and give it a try.

Ok, it looks pretty simple. Open files - presumably for sql files? You can run sql...but how do you identify the database it's talking to? Or is it wrapping it's own version? Let's try a create table. Oops - a crash. But I don't see how to connect to a running database. A google search shows there are actually a variety of database front ends for sqllite, both cross-platform and mac-specific. I'm going to just google SqlLite Mac. Right, sqllite comes with mac, is located in /usr/bin directory and called sqlite3, on a site that recommends razorsql. Shall I download that? I hate to learn tools I'm unlikely to purchase. Ah, ok, on the free database browser, the "new" button pop up bubble actually says "create a new database file". So let's create one in desktop/databasetest called "jlpt". I'm not going to worry about getting it onto the device yet.

Ok, I created the database. But I don't see any way to run an sql script. Well, I could copy and paste an sql file into the execute sql window. Well, ok, let's give it a quick try. First, let's export the database from phpAdmin and see if I can cut and paste it. Done. Ok, it seems to be running -and running - and running. Somehow, I don't think it's working.

Let's give the command line a try. sqllite3. It was /usr/bin/sqllite3. Yup, and there's the command from .help - .read FILENAME Execute SQL in FILENAME. Let's move the file to ~/test.sql. It can't read it. Ok, how about /Users/myname/test.sql. Wait, I think I need to open the database first - jlpt. Arggh. This is a hassle Ok, I give up. Let's download RazorSQL for the 30 day evaluation.

Ohhh, that's *much* better. I've already created a database after spending an hour spinning my wheels on the other options. So easy. The connect dialogue lets me specify a profile name, the database tool (slqlite3) and whether or not to create the database. Perfect. Ok, now, that I've created the database, what's next? I need to import the data. Uh-oh. I need to specify the separator and the file encoding. And the separator doesn't even give a default. I don't see one, so I'll say line break and pray. Ok, it doesn't like the "set" statement. Hmm...I wonder if I can just import the csv file instead? Well, that creates the table, but then says "no such column - level" when there plainly is one. What now? Well, I unchecked the "halt on error", and it's running, very slowly, through all 8k+ words. But - what will be there when it's done - if anything? I'm in it up to my knees, I tell you!

18% - it's going to be a while. Ah, I see - maybe the problem was the first insert had the column headings. Good this might actually work. 46%. Slooow. I might as well come back later and check. Anyway, it's time to watch a little bit of Madmen - season two finally is out on DVD!

Ok. I'm back from Madmen. It was another good one - that's an addictive show. Anyway - it looks like the insert worked. 8k+ word were inserted. One possible hiccup is if it stored the characters in the proper format - a select all of the records shows junk in the japanese character columns. But that might just be the display. I took the default on the storage format. We'll find out soon enough if it should've been something different. So, for now, this logical test of getting from mySql to sqlLite has succeeded. In the next step, we'll start looking at actually having the data set up on the Android.