They say you ask the right question and you get the right answer and this particular saying is in my opinion very relevant to the world of development. I say this because I have just spent the last fourty minutes or so asking the wrong question and persevering with a certain method of thought which has had me banging my head against a brick wall. The brick wall of course has stood strong laughing in my face. I know something can be learned from this and I wanted to share my thoughts but warn you this is going to get a little geeky.
So you may or may not know I am slowly trying to learn developing an Android application. Like every type of development books can only bring you so far and there is nothing quite like trying to apply what knowledge you have (or don’t) into a situation that is going to push your limits and you may also benefit from. I will be (at some point) publishing something about how I have been building this thing but for now let’s address the problem of asking the right question. For those of you who have no interest in geek stuff feel free to skip to the end.
My application uses sqlite which is built into Android as a database to store information on the system. I have not used sqlite that much but it’s not that different from what I normally use and so can easily transfer my knowledge. This is where it is going to get geeky. I have two tables that reference each other, my app stores packs of cards, the cards have information on them and each card can only belong in one pack. So I have a table to store information on the pack and a table to store the cards. Logic depicts that if I chose to throw away a whole deck then all the cards in it would also be be thrown away with it. In the world of development this is not always automated for you. What you have to do is tell the database to do this specifically by using a reference and then it should “just work” (if you made the reference right) in the world of the geek this is called a “Foreign key constraint”. However… it’s not so easy using sqlite which has this feature turned off by default (I found this out by google and some experimentation). Foreign key constraints have to be switched on every time you connect to the database which is done by very simply issuing this command to the DB.
Now usually to define what is in a database in sqlite I have been accessing it and running the “.dump” command which gives me the commands the program would use to re-create the entire database and all of it’s data. This is where I was asking the wrong question.
sqlite> PRAGMA foreign_keys=ON;
CREATE TABLE decks(_id integer primary key autoincrement, name text not null);
CREATE TABLE cards(_id integer primary key autoincrement, deck_id integer not null REFERENCES decks(_id) ON DELETE CASCADE, question text not null, answer text null);
Look explicitly at the first line of the output from the dump where it states “PRAGMA foreign_keys=OFF;” I looked at this result and thought.. “What the hell, I just switched that on, something must be wrong.” It was here I went totally and utterly down the wrong path and started to begin to ask the wrong questions. I googled ways to set this feature to be on and came across many pointing to the same information “Just type PRAGMA foreign_keys=ON”. I tried again, multiple times in fact and always got the same result from the output. I then ran a search to see if this was a feature for a particular version of the software and found that it was introduced in 3.6.19. Okay what version do I have? 3.7.13 okay it’s not that then (my number is higher and therefore has the feature implemented). Maybe it’s a problem with the Mac version I thought and started to search for people having problems with the Mac version of the code. In general.. no, I got nothing to tell me there was a problem. I was frustrated by this point which does nothing to help my brain as I have pointed out. I stepped away from the computer for a moment just to make myself some tea and a though occurred to me. The way I was asking the system to confirm it had changed the setting I was wrong, after a little more research I found the answer to my question.
There is of course a reason why sqlite was giving me the “OFF” setting and I’m not going to go into that. For the geeks out there I hope this helps you at some point, for geeks and non-geeks alike it’s more important to realise two things from this situation. Try stepping away from your problem for a moment and doing something different. Remember if you ask the right question and you get the right answer as I have said before. If you ever get stuck in a place in your mind where you seem to ask the same question time and time again and always come back with a similar answer try thinking differently about your problem or try asking a different question or the same question worded differently. Your answer may indeed be right in front of your eyes.