Which is better Ready XML database or convert it to SQLite? Can I put converted SQLite database on package(.apk)?

Question

I have large XML file(75MB). When I put it in res/xml folder, Eclipse freezes. Maybe it is related with my computer configurations, I have no idea(2.0GHz CPU, 2GB RAM). Now I am going to divide XML to 1-2MB parts and convert it to sqlite db. Does it worth to do it? Or are there some better ways to do this? After converting can I put sqlite databse on package(.apk)? I need your suggestions.

sqlite database is better since it would be faster than accessing and parsing xml... — Anirudha, Jul 21 '13 at 04:53
What's in that XML file? Maybe we could come up with a better solution with more context. — chandsie, Jul 21 '13 at 05:32
@Anirudh Can I use converted SQLite database in future. I am using mobile phone as an emulator. SQLite database is made inside it and could not get access to database. Question I am interested in can I do like that: I put sqlite database in apk, user download app from Play, install and will use app permanently without downloading database files. Please write your opinion about that. — Joe Rakhimov, Jul 21 '13 at 16:36
@chandsie it is dictionary XML database which include japanese characteristics. It will be English-Japanese dictionary — Joe Rakhimov, Jul 21 '13 at 16:40
@JoeRichard refer [this](http://stackoverflow.com/questions/513084/how-to-ship-an-android-application-with-a-database).. — Anirudha, Jul 21 '13 at 16:40

Peter Birdsall · Accepted Answer · 2013-08-04T00:19:46.583

Here's how you can put a sqlite database into your apk, or actually have it load for your app if it doesn't yet exits. You create or add the relevant parts to your DBHelper/DBContractor. You must put your sqlite database in /res/assets folder of your project. That's where it's going to read it from.

    public class DbHelper extends SQLiteOpenHelper {

public static String DATABASE_PATH;
public static final String DATABASE_NAME = "yourdatabasename.db";
private static final int DATABASE_VERSION = 1;
private Context context;
private SQLiteDatabase db;

public DbHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    this.context = context;
    String packageName = context.getPackageName();
    DATABASE_PATH = String.format("//data//data//%s//databases//",
            packageName);
    openDataBase();
}

@Override
public void onCreate(SQLiteDatabase db) {
    TDAdb.onCreate(db);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    TDAdb.onUpgrade(db, oldVersion, newVersion);
}

// Performing a database existence check
private boolean checkDataBase() {
    SQLiteDatabase checkDb = null;
    try {
        String path = DATABASE_PATH + DATABASE_NAME;
        checkDb = SQLiteDatabase.openDatabase(path, null,
                SQLiteDatabase.OPEN_READONLY);
    } catch (SQLException e) {
        Log.e(this.getClass().toString(), "Error while checking db");
    }

    if (checkDb != null) {
        checkDb.close();
    }
    return checkDb != null;
}

// Method for copying the database
private void copyDataBase() throws IOException {

    InputStream externalDbStream = context.getAssets().open(DATABASE_NAME);

    String outFileName = DATABASE_PATH + DATABASE_NAME;

    OutputStream localDbStream = new FileOutputStream(outFileName);

    byte[] buffer = new byte[1024];
    int bytesRead;
    while ((bytesRead = externalDbStream.read(buffer)) > 0) {
        localDbStream.write(buffer, 0, bytesRead);
    }

    localDbStream.close();
    externalDbStream.close();
}

public void createDataBase() {

    boolean dbExist = checkDataBase();
    if (!dbExist) {
        this.getReadableDatabase();
        try {
            copyDataBase();
        } catch (IOException e) {
            Log.e(this.getClass().toString(), "Copying error");
            throw new Error("Error copying database!");
        }
    } else {
        //Log.i(this.getClass().toString(), "Database already exists");
    }
}

public SQLiteDatabase openDataBase() throws SQLException {
    String path = DATABASE_PATH + DATABASE_NAME;

    if (db == null) {
        createDataBase();
        db = SQLiteDatabase.openDatabase(path, null,
                SQLiteDatabase.OPEN_READWRITE);
    }

    return db;
}

}

Hope this helps.

Here's the source for TDAdb, it is basically a java class that is used as a contract between my content provider and what's publicly available to other apps on the phone. It partly for security, consistency, like providing columns names, rawqueries, table names, etc. You can just specify column and table names as strings in your app if you want.

package com.birdsall.tda;

import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class TDAdb {

public static final String LOG_TAG = "TDAdb";

// tables
public static final String CHAPTER_TABLE = "chapters";
public static final String RULE_TABLE = "rules";
public static final String INDEXHEADER_TABLE = "indexheader";
public static final String KEYDESCRIPTORS_TABLE = "keydescriptors";
public static final String RULEREFERENCES_TABLE = "rulereferences";
public static final String SEARCHREQUEST_TABLE = "searchrequest";
public static String SQLITE_TABLE = "chapters";

// Chapter Table Columns
public static final String KEY_ROWID = "_id";
public static final String COL_CHAPTER = "chapter";
public static final String COL_CHAPTERTITLE = "chaptertitle";

// Searchrequest Columns
public static final String COL_ID = "id";

// Rule Table Columns
public static final String COL_RULE = "rule";
// public static final String COL_CHAPTER = "chapter";
public static final String COL_KEYDESCRIPTOR = "keydescriptor";
public static final String COL_RULETITLE = "ruletitle";
public static final String COL_DESCR = "descr";
public static final String COL_DESCRIPTION = "description"; // LongText
public static final String COL_LABEL = "label";
public static final String COL_USERLABEL = "userlabel";
public static final String COL_LABELID = "labelid"; // Integer
public static final String COL_USERLABELID = "userlabelid"; // Integer
public static final String COL_TDTIP = "tdtip";
public static final String COL_DEFUNCT = "defunct";
public static final String COL_USCFREVISION = "uscfrevision";
public static final String COL_HIGHLIGHT = "highlight"; // LongText
public static final String COL_PAGENO = "pageno";
public static final String COL_CHANGEDATE = "changedate"; // Integer
public static final String COL_REFINC = "refinc";
public static final String COL_RULEINC = "ruleinc";

// keydescriptors table columns
public static final String COL_KEYDESC = "keydesc";
public static final String COL_IMAGEREFERENCE = "imagereference";

// indexheader table columns
public static final String COL_INDEX_HEADER = "index_header";
public static final String COL_INDEXID = "indexid";
public static final String COL_INDEXLEXICON = "indexlexicon";

// rulereferences table columns
public static final String COL_RULEREFERENCE = "rulereference";
public static final String COL_REF_ORDER = "ref_order";

// columns defined in RAW Queries via AS
public static final String COLRAW_XREFRULE = "xrefrule";
public static final String COL_PROJ_THECOUNT = "count(*) as thecount";
public static final String COLTHECOUNT = "thecount";

public static final String[] PROJECTION_CHAPTER = { KEY_ROWID, COL_CHAPTER,
        COL_CHAPTERTITLE, };

public static final String[] PROJECTION_RULES = { KEY_ROWID, COL_CHAPTER,
        COL_RULE, COL_RULETITLE, COL_KEYDESCRIPTOR, };

public static final String[] PROJECTION_RULES_RULE = { COL_RULE, COL_RULETITLE, COL_KEYDESCRIPTOR, };

public static final String[] PROJECTION_RULEREFERENCE = { KEY_ROWID,
        COL_RULEREFERENCE, COL_REF_ORDER, };

public static final String[] PROJECTION_SEARCHREQUEST = { KEY_ROWID,
        COL_ID, };

/* ************** MAKE SURE THERE'S and _id *********************** */
public static final String RAWQUERY_RULESDESCRIBED = "select rules._id, rule, chapter, ruletitle, rules.keydescriptor, keydesc FROM rules LEFT OUTER JOIN keydescriptors ON rules.keydescriptor = keydescriptors.keydescriptor WHERE chapter = ?  and rules.keydescriptor = '2' group by 1, 2, 3, 4, 5 order by 1, 4";
public static final String RAWQUERY_RULESCROSSREF = "SELECT rules._id, rules.rule, rules.ruletitle,  rulereferences.rule AS xrefrule, rulereferences.ref_order FROM rulereferences INNER JOIN rules ON rulereferences.rulereference = rules.rule WHERE (((rules.keydescriptor)='2') AND ((rulereferences.rule)= ? )) ORDER BY rulereferences.ref_order, rules.rule";
public static final String RAWQUERY_RULESCROSSREFDETAIL = "SELECT rules._id, rules.rule, rules.ruletitle, rules.keydescriptor, keydescriptors.keydesc FROM rules INNER JOIN keydescriptors ON rules.keydescriptor = keydescriptors.keydescriptor WHERE (((rules.rule)= ? )) GROUP BY rules.rule, rules.ruletitle, rules.keydescriptor, keydescriptors.keydesc ORDER BY rules.rule, Min(rules.keydescriptor)";
public static final String RAWQUERY_INDEX = "SELECT DISTINCT _id, indexlexicon FROM indexheader GROUP BY 2 ORDER BY 2";
public static final String RAWQUERY_INDEXCHILD = "SELECT indexheader._id, indexheader.index_header, indexer.rule, indexheader.indexid, rules.pageno, rules.uscfrevision, rules.defunct FROM (indexheader LEFT JOIN indexer ON indexheader.indexid = indexer.indexid) LEFT JOIN rules ON indexer.rule = rules.rule WHERE (((indexheader.indexlexicon)= ? )) GROUP BY indexheader._id, indexheader.index_header, indexer.rule, indexheader.indexid, rules.pageno, rules.uscfrevision, rules.defunct HAVING (((rules.pageno) Is Not Null)) ORDER BY indexheader.index_header, indexheader.indexid";
public static final String RAWQUERY_RULEDETAIL = "SELECT rules._id, rules.rule, rules.ruletitle, rules.descr, rules.description, rules.keydescriptor, rules.tdtip, rules.uscfrevision, rules.defunct, rules.highlight, rules.label, rules.userlabel, rules.changedate,  rules.chapter, rules.labelid, rules.userlabelid, rules.tdtip, rules.pageno, rules.refinc, rules.ruleinc, keydesc FROM rules LEFT OUTER JOIN keydescriptors ON rules.keydescriptor = keydescriptors.keydescriptor GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20 HAVING (((rules.rule)= ? )) ORDER BY 1, 5";
public static final String RAWQUERY_CLUBDETAIL = "SELECT rules._id, rules.rule, rules.ruletitle, rules.descr, rules.description, rules.keydescriptor, rules.tdtip, rules.uscfrevision, rules.defunct, rules.highlight, rules.label, rules.userlabel, rules.changedate,  rules.chapter, rules.labelid, rules.userlabelid, rules.tdtip, rules.pageno, rules.refinc, rules.ruleinc FROM rules  WHERE rules.keydescriptor = '1' AND (((rules.rule)= ? )) ORDER BY 1, 5";
public static final String RAWQUERY_RULEDETAIL_KEY = "SELECT rules._id, rules.rule, rules.ruletitle, rules.descr, rules.description, rules.keydescriptor, rules.tdtip, rules.uscfrevision, rules.defunct, rules.highlight, rules.label, rules.userlabel, rules.changedate,  rules.chapter, rules.labelid, rules.userlabelid, rules.tdtip, rules.pageno, rules.refinc, rules.ruleinc, keydesc FROM rules LEFT OUTER JOIN keydescriptors ON rules.keydescriptor = keydescriptors.keydescriptor WHERE rules._id = ? GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20  ORDER BY 1, 5";
public static final String RAWQUERY_LABEL = "select distinct _id, label, count(*) as thecount from rules group by 2 order by label";
public static final String RAWQUERY_LABELDETAIL = "SELECT rules._id, rules.rule, rules.ruletitle, keydescriptors.keydesc FROM rules INNER JOIN keydescriptors ON rules.keydescriptor = keydescriptors.keydescriptor WHERE (((rules.label)= ? )) GROUP BY rules.rule, rules.ruletitle, keydescriptors.keydesc ORDER BY rules.rule, Min(rules.keydescriptor)";
public static final String RAWQUERY_USERLABEL = "select distinct _id, userlabel, count(*) as thecount from rules group by 2 order by userlabel";
public static final String RAWQUERY_USERLABELDETAIL = "SELECT rules._id, rules.rule, rules.ruletitle, keydescriptors.keydesc FROM rules INNER JOIN keydescriptors ON rules.keydescriptor = keydescriptors.keydescriptor WHERE (((rules.userlabel)= ? )) GROUP BY rules.rule, rules.ruletitle, keydescriptors.keydesc ORDER BY rules.rule, Min(rules.keydescriptor)";
public static final String RAWQUERY_ALLTDTIPS = "select distinct _id, rule, ruletitle from rules where keydescriptor = '3' order by rule";
public static final String RAWQUERY_ALLCLUB = "select distinct _id, rule, ruletitle from rules where keydescriptor = '1' order by rule";
public static final String RAWQUERY_RULECLASS = "select distinct _id, rule, ruletitle from rules where keydescriptor = ? order by rule";
public static final String RAWQUERY_RULECLASS_USERLABEL = "select distinct _id, rule, ruletitle from rules where userlabel > ' ' order by rule";
public static final String RAWQUERY_RULECLASS_HIGHLIGHT = "select distinct _id, rule, ruletitle from rules where highlight > ' ' order by rule";

public static void onCreate(SQLiteDatabase db) {

    // Log.i(LOG_TAG, "onCreate");
}

public static void onUpgrade(SQLiteDatabase db, int oldVersion,
        int newVersion) {
    Log.w(LOG_TAG, "Upgrading database from version " + oldVersion + " to "
            + newVersion + ", which will destroy all old data");

    onCreate(db);
}
}

Thx Peter, it was helpful for me and I appreciate your help :) — Joe Rakhimov, Jul 31 '13 at 08:48
@JoeRichard I posted the TDBdb source and a brief explanation. Hope it helps and answers your question. Have a good day. — Peter Birdsall, Aug 04 '13 at 00:20

Which is better Ready XML database or convert it to SQLite? Can I put converted SQLite database on package(.apk)?

1 Answers1