SQL Lite - swapping out the XML

Ok, now that we've got a read loop going on, our next and hopefully final step will be to substitute the current XML read for the SQL database read. We've got a lot of the same logic in the program. Actually, there is a hitch. The new database doesn't have the answers set up, unlike the XML file. So, for now what we'll do is just substitute in some dummy answers. But the big step after that will be to pull random answers from the database. We've already done that with the android app, so it should lessent the work. But for now, let's hurry up and get the questions loaded. We'll just pull logic from the XML routine.

This is the current parsing code, invoked when a "Question" tag is encountered in the data stream:

- (void)parser:(NSXMLParser *)parser
didStartElement:(NSString *)elementName
namespaceURI:(NSString *)namespaceURI
qualifiedName:(NSString *)qName
attributes:(NSDictionary *)attributeDict
{

if ([elementName isEqualToString:XML_TAG_QUESTION]){

// answer counter - initialize for new question
cntr = 1;

// allocate the question
question = [[Question alloc] init];

// get the question text
question.questionTxt = [attributeDict valueForKey:XML_TAG_QUESTION_ATTRIBUTE_TEXT]; // XML_TAG_QUESTION_ATTRIBUTE_TEXT

// get get the question number
NSString *text = [attributeDict valueForKey:XML_TAG_QUESTION_ATTRIBUTE_NUMBER];

// convert from the text value
question.number = [text intValue];

// Now get the question answer
text = [attributeDict valueForKey:XML_TAG_QUESTION_ATTRIBUTE_CORRECT_ANSWER];

// add the correct answer after converting it to an int
question.correctAnswer = [text intValue];



// add it to the question
[appState addQuestion: question];

[question release];

}

Ok, this is a little bit different than it will need to be. It's set up with the assumption that the answer part of the data structure exists. Also, the correct answer is specified as a number in the question logic, whereas the approach we will be using when we're choosing possible answers at random will be simple to compare the string of the selected answer with the hiragana.

So, what's the best way to get this moving? Maybe for now we can just set a dummy number, like 1. We're just trying to advance a little bit here. And the dummy answers will always be the same anyway, until we implement the population of the answers.

So, we have our dummy answers in the XML file - they look like this:

<answers>
<answer text="おおきい" />
<answer text="おおきな" />
<answer text="ちかてつ" />
<answer text="ちゃわん" />
</answers>

So, for now we'll need to just hardcode them.

Here's the updated code:

-(void) selectAll
{
const char *dbpath = [databasePath UTF8String];
sqlite3_stmt *statement;

if (sqlite3_open(dbpath, &jlptDB) == SQLITE_OK)
{

int cntr_question = 0;

NSLog(@"select all, open ok");

//NSString *querySQL = [NSString stringWithFormat: @"SELECT address, phone FROM contacts WHERE name=\"%@\"", name.text];


//NSString *querySQL = [NSString stringWithFormat: @"SELECT kanji, hiragana, english FROM all_words where _id = 1;"];
NSString *querySQL = [NSString stringWithFormat: @"SELECT _id, kanji, hiragana, english FROM all_words;"];

const char *query_stmt = [querySQL UTF8String];

if (sqlite3_prepare_v2(jlptDB, query_stmt, -1, &statement, NULL) == SQLITE_OK)
{
NSLog(@"select all, prepare ok");

while (sqlite3_step(statement) == SQLITE_ROW)
{
cntr_question++;

NSLog(@"select all, row found");

NSString *kanji = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 0)];

NSString *hiragana = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)];

NSString *english = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 2)];

NSLog(@"kanji: %@, hiragana: %@, english: %@", kanji, hiragana, english);

// allocate the question
question = [[Question alloc] init];

question.number = cntr_question;
question.questionTxt = kanji;

// add the correct answer after converting it to an int
question.correctAnswer = 1;

[kanji release];
[hiragana release];
[english release];

// add it to the app state
[appState addQuestion: question];



int cntr =1;

// Get get the answer from the question
NSString *answer = @"おおきい";


// convert counter to an object for storage in dictionary
NSNumber *counter = [NSNumber numberWithInt:cntr];

[question.answerHash setObject: answer
forKey: counter ];

// increment counter
cntr++;


// Get get the answer from the question
answer = @"おおきな";

// convert counter to an object for storage in dictionary
counter = [NSNumber numberWithInt:cntr];

[question.answerHash setObject: answer
forKey: counter ];

// increment counter
cntr++;

// Get get the answer from the question
answer = @"ちかてつ";

// convert counter to an object for storage in dictionary
counter = [NSNumber numberWithInt:cntr];

[question.answerHash setObject: answer
forKey: counter ];

// increment counter
cntr++;

// Get get the answer from the question
answer = @"ちゃわん";

// convert counter to an object for storage in dictionary
counter = [NSNumber numberWithInt:cntr];

[question.answerHash setObject: answer
forKey: counter ];

// increment counter
cntr++;


[question release];
}
sqlite3_finalize(statement);
}
else {
NSLog(@"select all, prepare unssuccessful");
}
sqlite3_close(jlptDB);
}
else {
NSLog(@"select all, open failed");

}
}

Ok, and now let's update the app delegate for the new reader:

Not quite - I'm getting a "2" in the question field.

Oh, right, I change the select to add the _id column, then change my mind.

Here's the updated select:

NSString *querySQL = [NSString stringWithFormat: @"SELECT kanji, hiragana, english FROM all_words;"];


And we're good. Here's the screenshot: