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
No comments:
Post a Comment