Understanding Android.com's "Saving Data in SQL Databases" Tutorial

Question

I was reading this tutorial: http://developer.android.com/training/basics/data-storage/databases.html and it raised a lot of questions.

This is my code I made while following the tutorial:

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;

public final class SongDbHelper extends SQLiteOpenHelper {
    // If you change the database schema, you must increment the database version.
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "Song.db";
    private static final String TEXT_TYPE = " TEXT";
    private static final String PRIMARY_KEY_TYPE = " INTEGER PRIMARY KEY";
    private static final String COMMA_SEP = ",";
    private static final String SQL_CREATE_ENTRIES =
            "CREATE TABLE " + SongEntry.TABLE_NAME + " (" +
                    SongEntry._ID + PRIMARY_KEY_TYPE + COMMA_SEP +
                    SongEntry.COLUMN_NAME_SONG_TITLE + TEXT_TYPE + COMMA_SEP +
                    SongEntry.COLUMN_NAME_ENGLISH_LYRICS + TEXT_TYPE + COMMA_SEP +
                    SongEntry.COLUMN_NAME_ROMAJI_LYRICS + TEXT_TYPE + COMMA_SEP +
                    SongEntry.COLUMN_NAME_KANJI_LYRICS + TEXT_TYPE + COMMA_SEP +
                    SongEntry.COLUMN_NAME_INFO + TEXT_TYPE +
            " )";
    private static final String SQL_DELETE_ENTRIES =
            "DROP TABLE IF EXISTS " + SongEntry.TABLE_NAME;

    public SongDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQL_CREATE_ENTRIES);
    }
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // This database is only a cache for online data, so its upgrade policy is
        // to simply to discard the data and start over
        db.execSQL(SQL_DELETE_ENTRIES);
        onCreate(db);
    }
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onUpgrade(db, oldVersion, newVersion);
    }


    public static abstract class SongEntry implements BaseColumns {
        public static final String TABLE_NAME = "Song";
        public static final String COLUMN_NAME_SONG_TITLE = "song_title";
        public static final String COLUMN_NAME_ENGLISH_LYRICS = "english_lyrics";
        public static final String COLUMN_NAME_ROMAJI_LYRICS = "romaji_lyrics";
        public static final String COLUMN_NAME_KANJI_LYRICS = "kanji_lyrics";
        public static final String COLUMN_NAME_INFO = "info";

    }
}

1. Are we supposed to call SongDbHelper.onCreate() every time the app opens up? (I'm imagining putting it in my main activity's onCreate() method.

2. If you call SongDbHelper.onCreate() more than once, will it recreate the table, or will SongDbHelper.onCreate() just fail silently and gracefully?

3. Why does the app need to call SongDbHelper.onCreate() anyways? If I put my app on the app store, won't I just let people download the database that I already filled with data?

4. When/where in my app do I call SongDbHelper.onUpgrade()? I assume this happens when the user notices that "There is an update available for this app. Click here to download it" or something like that. I'm just confused, because I'd imagine the user simply downloads a .db file with the database intact; and therefore won't need to call SongDbHelper.onCreate().

5. If the app has to call SongDbHelper.onCreate(), how does it fill the database with values? I think it would defeat the purpose of a database if I also have code that fills in the values, because then the values would be duplicated (in the code and the database).

6. What is the best way to fill a database with values? With the terminal, or programmatically in a separate program from the app, or only in the Android app?

Answer 1

Your SongDbHelper class should be implemented as Singleton instance.

It could be a good practice instantiate it in your Application class and exposing the SongDbHelper object for all your App

public class YourApp extends Application {
   public SongDbHelper songDBHelper;

   @Override
   public void onCreate() {
     super.onCreate();
     songDBHelper = new SongDbHelper(.....);
   }
   public SongDBHelper getSongDB() {
     return songDBHelper;
   }
}

Answer 2

Firstly, I would recommend you to read the tutorial again to understand it. Now moving on to your questions,

1) You are not going to call onCreate() ever. As you can see, it comes from the super class SQLiteOpenHelper and it is called by the system and not the you when the app installs for the first time.

2) So from the previous question you can understand that there is not question of calling the onCreate() more than once. It will be called by the system only once to create the database when the user installs the app for the first time.

3) It doesn't work in this way as you are saying. When the app installs the onCreate() will be called to create the database. After that you can use your logic to fill data into it. No pre-defined data will be downloaded from anywhere as you said.

4) No, its also not like that "whenever the users get an update". onUpgrade() is only called when the database version changes. Like if you want to change the schema of the database on an app already existing in the Play Store, you can change the version number and perform the upgrade appropriately in the onUpgrade().

5) Your question is not clear.

I hope your conception will be changed after reading this answer. Do read the tutorials again to get a proper concept or post a new question if you have further questions.

UPDATE

5) You are thinking to create a database with song lyrics from before and put it inside your app. This is why you are having a misconception with onCreate().

See, when the user installs the app for the first time, the onCreate() will be called and a database with the given schema will be created. There would be no content inside it.

To put something inside it, you need to insert rows into that table programmatically. Hope it doesn't defeats the purpose now.

6) Now as for your use-case.

According to me, the best solution would be to put all your song lyrics in your web server and fetch those lyrics inside your app and put it in the database.

When the user install the app and runs for the first time, you should query the web server to check for the available lyrics and then fetch them one by one and put them inside your database using insert() method.

NOTE - the onCreate() just creates a blank table and doesn't put any values into it. But you are programmatically putting content inside your database, like the song lyrics using the insert() method.

Now when ever some new songs come up, just put the lyrics in your server and the app will automatically query the server to check if any new song lyrics is available and download it and insert in into the database accordingly.

Hope it is now clear to you.

Answer 3

Answer for :

Point 1,2,3 : As simply mentioned in tutorial you are following at this link, we are not supposed to call SongDbHelper.onCreate(). Instead of it, when we want to have reference of database from Helper class, we use the CONSTUCTOR like :

SongDbHelper mDbHelper = new SongDbHelper(getContext());
// this will call super method internally and
// this will create table in database

Point 4 : onUpgrade() is also not responsibility of ours to call explicitely. When we change database schema, we update DATABASE_VERSION and android framework will internally call onUpgrade() for us

Point 5 : You can store song lyrics one by one from your xml to database. This is right way as far as i know

Update :

The best way would be to store your database on a web server which is made prior, download it from the web server and have the app then read/write into database. This will not defeat purpose of database, in addition it will not duplicate database entries and code entries (xml). In addition to that, your app size will also be smaller because application will download database in runtime, rather than storing in device memory initially