How does an Android app automatically fill its database with values after the user installs it?

Question

This is a follow-up question to this question.

I was reading this tutorial from Android.com, and I made this code 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";

    }
}

When I publish my app to the Google Play store, and a user installs it, SongDbHelper.onCreate() will automatically be called, creating a database with my Song table. How does this database become filled with values? I am guessing that I need to write code in the SongDbHelper.onCreate() method that fills it with data. That means I need to store the data in the source code somewhere.

I thought of storing my song lyrics in my strings.xml file, but it seems redundant. Then, the lyrics will be in the strings.xml file and the database. I almost don't need a database in that case, unless I need to call SELECT on the data to filter it. Is this really the way people fill the database?

Ideally, I would like to fill the database on my own, and then have users download the database from the app store. People have said that we can't do that. Is that really true? It seems like a waste to have the data in 2 places (the database and strings.xml). I might have a lot of data!

Do most people have their app download the database from an internet server? I would like to not have to have an internet server for all my published apps, and just host the database on Google Play.

You have run Insert query on splashscreen first time. Or you should have used 'default' to add default data in Table. — AndroidLad, Jun 29 '15 at 12:28

score 1 · Answer 1 · answered Jun 29 '15 at 12:33

1

Yes, if you want to have preloaded data, you need to include them in you app as an asset or resource. It will indeed take the double of the space.

But that is generally used to get data for the first launch even without internet connection, and later add new and updated data via internet.

If you don't want to pay for a server : for free, you can get a website at Gogle App Engine. It's fairly easy if you just want to store xml files or images.

answered Jun 29 '15 at 12:33

Stephane Mathis

6,542
6
43
69

So, if I have an extremely large database, and that is the entire purpose of my app (to provide this information), then I should really not double the space by storing everything in xml, but require the user to have an internet connection on the first launch to download it? (Or I can let them choose an artist whose lyrics they want to download?) That would be the best quality, because the user can choose how much space the app takes up? Thanks! – Rock Lee Jun 29 '15 at 12:38
1

If you ship a prepared SQLite database rather than XML, there's not really any duplicated use of space once the application is installed, and if it's less than 50MB (lyrics for at least 20,000 songs, I'd guess) then there's nothing but the APK to download. – nkorth Jun 29 '15 at 12:43
1

Correction: since APK is a compressed format, you could likely store lyrics for upwards of 100,000 songs. You might not have to worry so much about the size of the data! – nkorth Jun 29 '15 at 12:47
1

What is the size of you data ? Because it can be faster to download one 40 MB file than twenty 2 MB file. But yes, you could have an xml (or json, it's smaller !) file with all artists (or gender or any category) and a link to download the associated data. OR, just have all artists and song names in one file and download the lyrics when it is needed. – Stephane Mathis Jun 29 '15 at 12:50
About 700 (and counting, as more are released from same producer) thumbnails for each CD cover, and roughly between 5,000-10,000 actual tracks. But then multiply that by 3 (for the kanji, romaji, and english versions) so it's about 15,000-30,000 actual song-sized lyrics. Then for each song it needs to store some additional information about the song, approximately the size of one lyric. I am serving a niche community that likes music from one main producer, so the songs will grow at a steady pace. – Rock Lee Jun 29 '15 at 13:17
1

700MB ? For the lyrics only or with the CD cover ? – Stephane Mathis Jun 29 '15 at 13:49
@StephaneMathis 700 CD cover thumbnails (.jpg's). I don't know how many MB. – Rock Lee Jun 29 '15 at 21:00

score 1 · Accepted Answer · edited May 23 '17 at 12:30

It is possible to include a filled database, and this might help: Ship an application with a database You don't have to run your own server or downloads or anything, because arbitrary files placed in the assets folder will be included in your APK.

(However, if it would make your APK larger than 50MB, you will need to deal with some sort of download. For this, Google provides the expansion files system, so you still don't need your own web server.)

And in simpler cases, I believe it is common practice to just run some insert queries in onCreate, like in the tutorial you read.

How does an Android app automatically fill its database with values after the user installs it?

2 Answers2