Is it really necessary to create SQLite tables every time the application starts?

Question

I've noticed in more than one SQLite tutorial that the table is recreated in the onCreate() event of the class that extends SQLiteOpenHelper. I've already created my SQLite database and tables outside the Android environment (Eclipse IDE) with the help of a Firefox add-in. The database tables reside in the expected spot:

C:\aXX3&Space\Android\workspace\OnDemandAndAutomatic_Project\assets

It seems bizarre to me that I would have to recreate them programmatically each time (obviously, they continue to exist and retain data, or what would be the point)? However, I'm having a serious issue with this application right now which leads me to my question:

• Is it really necessary reminding Android every time about the database?

Much to my chagrin, I just realized that instead of creating my database tables in C:\aXX3&Space\Android\workspace\OnDemandAndAutomatic_Project\assets, I had put them in C:\aXX3&Space\Android\workspace\OnDemandAndAutomatic\assets, which was an earlier version of the application, yet copying the SQLite file to the proper location didn't change things a single bit. If this is what has been causing the problem, being that my application was looking for an AWOL database, is it not enough to simply copy and paste the file? Must I do something to officially introduce the database to the system/Android/Eclipse directory? Any suggestions?

Roybal

Answer 1

Actually SQLiteOpenHelper is responsible for your database creation, upgrade and many more. If you want to create tables programmatic then you have to write a create tables queries in the onCreate method of the SQLiteOpenHelper class.If you want to update your database after previously created database you can write the modified table's queries in the onUpgrade method only you will have to change the database version.

If you already created a database externally and if you want to use that database then you have to put that database in the assets folder and copy that file in the databases folder which is resides in the /data/data/packagename/databases folder.

here is the example for copy database from assets to databases folder

private static boolean copyDataBase(Context c) throws IOException {
    String DB_PATH = "/data/data/" + c.getPackageName() + "/databases/";
    AssetManager mg = c.getResources().getAssets();
    InputStream myInput = null;

    try {
        myInput = mg.open(DATABASE_NAME);

    } catch (IOException ex) {
        return false;
    }

    if (myInput != null) {
        String outFileName = DB_PATH + DATABASE_NAME;
        OutputStream myOutput = new FileOutputStream(outFileName);
        byte[] buffer = new byte[8000];
        int length;

        while ((length = myInput.read(buffer)) > 0) {
            myOutput.write(buffer, 0, length);
        }
        myOutput.flush();
        myOutput.close();
        myInput.close();

        Log.d(TAG, "Database is copied");
        return true;
    }
    return false;
}

Here is the method which check for the databases and its version for copy

public void copyDatabase() {

    try {
        SharedPreferences preferences = c.getSharedPreferences(c.getPackageName(), Context.MODE_PRIVATE);
        if (checkDataBase(c)) {
            if (preferences.getInt("dbversion", 0) != 0) {
                c.deleteDatabase(DatabaseHelper.DATABASE_NAME);
            }

        }
        getReadableDatabase();
        close();
        if (copyDataBase(c)) {
            Editor editor = preferences.edit();
            editor.putInt("dbversion", DatabaseHelper.DATABASE_VERSION);
            editor.commit();
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Here is the example which check whether the database is already exists or not?

public static boolean checkDataBase(Context c) {
    File f = new File("/data/data/" + c.getPackageName() + "/databases/"
            + DATABASE_NAME);
    if (!f.exists())
        return false;
    SQLiteDatabase checkDB = null;
    try {
        checkDB = SQLiteDatabase
                .openDatabase("/data/data/" + c.getPackageName()
                        + "/databases/" + DATABASE_NAME, null,
                        SQLiteDatabase.OPEN_READONLY);
        checkDB.close();
    } catch (SQLiteException e) {
        e.printStackTrace();
    }
    return checkDB != null ? true : false;
}

Tacky

Aha! That last might be my problem - I only have it in the assets folder. – Roybal

I searched my HD for "data\data\" (Windows 7) and found nothing. Is this a directory structure I need to create myself? If so, exactly where should it be in relationship to my assets folder, whose path is: C:\aXX3&Space\Android\workspace\OnDemandAndAutomatic_Project\assets ? – Roybal

If you have already database created then copy database to assets folder and when application starts copy database from assets folder to your databases folder which you will find from DDMS => File explorer.In file explorer there is a directory structure like data/data/{packagename}/databases. May be you will not find a databases folder because you have not created databases yet so first create a folder and then copy your database to the databases folder. – Tacky

"here is the example for copy database from assets to databases folder" - Is this necessary? Doesn't the Android [sub]system do this for you automatically when your app/.apk is installed on a device? – Roybal

Yes, I've created the DB and tables, using the Firefox plugin; it/they exist in the /assets folder. – Roybal

It will not automatically copy database. When application install first time you will have to copy the database from assets to the databases folder. – Tacky

Answer 2

I think you are misunderstanding here. When an SQLiteOpenHelper object is constructed, it will check if the SQLite database is exists or not, if not, it will call its onCreate( ) method (where normally developers implement the create database SQL)

Update:
@Clay Shannon: I will explain the mechanism that Eclipse and Android project working. Let's say you use Eclipse to programming Android project. The new project will create a project folder in workspace (in your case it is C:\aXX3&Space\Android\workspace and the project folder is OnDemandAndAutomatic_Project). This folder will contains several sub-folders such as: src, bin, assets, res,... Each folder has its own role, and you are interested in assets folder, right? Assets folder is used to contain reference files (which can't or you don't want to put in res folder) such as: html file, sound file, images file, text file... When Eclipse builds the apk from project, these files also included in the apk. When you install the apk on Android device, the apk is copied into Android system folder, and also a folder to contain app data is created, as Dharmendra mentioned: /data/data/{packagename}/ (packagename such as com.google.app, etc, and this path is for your Android OS of your device, not for Windows).

Your case here is you want to use your already-exists database, so you need to implement a function which check if your database exists or not, if not copy your db into the database path /data/data/{packagename}/databases/, and call that function when your app start. And how to do it is already answered here How to copy existing database from one app to another. Also here is reference to access asset files, in case you don't know http://www.wiseandroid.com/post/2010/06/14/Android-Beginners-Intro-to-Resources-and-Assets.aspx

Hope now you can solve your problem. Note: your already-exist db must be a SQLite database, or the app will not recognize it even if you copy to the right path.

Punt

Ah; it makes sense now. But intuitive? I think not. Who would imagine an OnCreate() event is conditional? – Roybal

Ok, I updated the answer. Hope you can solve your problem now – Punt

Okay, so I shouldn't have expected to find a /data/data/<packageName>/ folder on my dev machine (Windows); that explains it. Thanks. – Roybal

Answer 3

The SQLiteOpenHelper has its onCreate() called if the database does not exist. It has the onUpgrade() called if the database schema has changed and needs to be upgraded. So, the answer to your question is: no.

Scratchy