Tuesday, February 25, 2014

When to start fresh?

   I've been working on an app that tracks multiple vehicle fuel usage. I had been storing data as a text file because it was easier to implement but I realized that a SQLite database would be a better choice. The problem was I knew nothing about SQLite and only a rudimentary idea of databases in general. So to learn more I made a database test app ( see http://to-learn-android.blogspot.com/2014/02/sqlite-database.html ). At first I thought I would be able to later cut and paste the code in to the original vehicle app thereby replacing the text file storage code. The problem I later realized was the text storage code was so interwoven in to the rest of the code that it would be easier to start fresh. Two main things I learned from this exercise. First is to write code more compartmentalized, in other words make it more object oriented. Storage code should have been a separate class to begin with. Second I learned about SQLite databases, though I am far from an expert I have a better understanding than I began with.

   One other thing I need to learn is how to format the data for display so the data aligns with Vehicle Mileage Gallons and Price.      

Wednesday, February 5, 2014

SQLite database

   Success in finally creating a SQLite database, adding data and retrieving data. 

First I had to create a database. It was a little bit of a problem because you have to get everything just right including spacing between the words.

public void onCreate(SQLiteDatabase db) {
String CREATE_VEHICLE_TABLE = "create table " + TABLE_VEHICLES + "("
+ KEY_ID + " integer primary key autoincrement, "
+ KEY_VEHICLE_NAME + " text not null, " + KEY_VEHICLE_MILES
+ " text not null, " + KEY_GAL + " text not null, " + KEY_PRICE
+ " text not null )";
// for test

try {
} catch (SQLException e) {

Next was adding data, which actually was not that hard.

public void saveToDb(String name, String miles, String gal, String price) {

SQLiteDatabase databaseWrite = this.getWritableDatabase();
ContentValues values = new ContentValues();

values.put(KEY_VEHICLE_NAME, name);
values.put(KEY_VEHICLE_MILES, miles);
values.put(KEY_GAL, gal);
values.put(KEY_PRICE, price);
databaseWrite.insert(TABLE_VEHICLES, null, values);


//  for testing
Log.i("MyTag", name + " " + miles + " " + gal + " " + price);

The hardest part turned out to be retrieving the correct data. I wanted it to get the last data saved. All the examples I could find online used  moveToFirst() which is the bottom of the stack but I kept thinking it was the top of the stack. Finally after a lot of frustration I figured out to use moveToLast(), which looking back on makes sense.

public String[] getFromDb(String vehicle_name) {
SQLiteDatabase databaseRead = this.getReadableDatabase();

Cursor dbc = databaseRead.query(TABLE_VEHICLES, new String[] {
KEY_VEHICLE_MILES, KEY_GAL, KEY_PRICE }, null, null, null,
null, null);
dbc.moveToLast();// move Cursor to last row entered

// get the values in the row
String miles = dbc.getString(dbc.getColumnIndex(KEY_VEHICLE_MILES));// index = 0
String gal = dbc.getString(dbc.getColumnIndex(KEY_GAL));// index = 1
String price = dbc.getString(dbc.getColumnIndex(KEY_PRICE));// index = 2

// add values to a string array to be able to return as a small array
String[] returnData = new String[3];
returnData[0] = miles;
returnData[1] = gal;
returnData[2] = price;

// close Cursor and database to save memory
return returnData;// returns string array


10,000 hour to Mastery.
10,000 - 554.75 = 9445.25 hours left