How to import pre-made db to sqlite managed by orm

Question

I have a .db file and I want to setup it at first run of my android application. I use OrmLite to manage my database.

In that .db file a have about 7000 records and when I want to import it by common methods (use foreach - create command ) it takes many time (about 2-3 mins) to import.

How can i solve this problem?

thanks

Answer 1

Let say you have database named "prepared.db" and your package name is "com.example.android". This is what I do.

1. make sure you prepared database is under assets folder.
2. In the constructor of class that extends OrmLiteSqliteOpenHelper, check whether or not database exist.
3. If database DID NOT exist, copy db from assets folder

(Both Step 2 and 3 happen inside constructor)

This is code sample and It work for me.

public class DatabaseHelper extends OrmLiteSqliteOpenHelper {

    private static final String DATABASE_NAME = "prepared.db";
    private static final String DATABASE_PATH = "/data/data/com.example.android/databases/";


    public DatabaseHelper(Context context) {
        super(context, DATABASE_PATH+DATABASE_NAME, null, DATABASE_VERSION);

        boolean dbexist = checkdatabase();
        if (!dbexist) {

            // If database did not exist, try copying existing database from assets folder.
            try {
                File dir = new File(DATABASE_PATH);
                dir.mkdirs();
                InputStream myinput = mContext.getAssets().open(DATABASE_NAME);
                String outfilename = DATABASE_PATH + DATABASE_NAME;
                Log.i(DatabaseHelper.class.getName(), "DB Path : " + outfilename);
                OutputStream myoutput = new FileOutputStream(outfilename);
                byte[] buffer = new byte[1024];
                int length;
                while ((length = myinput.read(buffer)) > 0) {
                    myoutput.write(buffer, 0, length);
                }
                myoutput.flush();
                myoutput.close();
                myinput.close();            
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /*
    * Check whether or not database exist
    */
    private boolean checkdatabase() {
        boolean checkdb = false;

        String myPath = DATABASE_PATH + DATABASE_NAME;
        File dbfile = new File(myPath);
        checkdb = dbfile.exists();

        Log.i(DatabaseHelper.class.getName(), "DB Exist : " + checkdb);

        return checkdb;
    }
}

P.S : If the database file size is more than 1mb, error will occur. You can split database to solve such issue.

Answer 2

If you want to provide the data as an external db, you can dump on or more tables (definition and data) using

sqlite3 prepared.db ".dump mytable" > dump.sql

and import them using

sqlite3 main.db < dump.sql

If you want to setup things programmatically in Java, make sure that you do everything in one single transaction using TransactionManager.callInTransaction, since multiple commits (autocommits) are quite expensive in SQLite.