Sqliteopenhelper Problem with Fully Qualified Db Path Name

SQLiteOpenHelper problem with fully qualified DB path name

Historically, you have not been able to use paths with SQLiteOpenHelper. It only worked on simple filenames. I had not realized that they relaxed that restriction in Android 2.2.

If you wish to use databases on the SD card, and you wish to support Android 2.1 and earlier, you cannot use SQLiteOpenHelper.

Sorry!

SQLiteOpenHelper - creating database on SD card

First you have to specify the path of the sdcard. You can do that by creating a string like this:

public static final String  DATABASE_FILE_PATH = "/sdcard";

But for you should call

Environment.getExternalStorageDirectory()   

to get the root path to the SD card and use that to create the database. After that you create the database as you want. Here is an example

public class DatabaseHelper
{ 
   private static final String TAG                  = "DatabaseHelper";

  public static final String  DATABASE_FILE_PATH      = Environment.getExternalStorageDirectory();
  public static final String  DATABASE_NAME      = "mydb"; 
  public static final String  TRACKS_TABLE        = "tracks";
  public static final String  TRACK_INFO_TABLE        = "track_info";

  private static final String TRACKS_TABLE_CREATE     = "create table "
           + TRACKS_TABLE
           + " (_id integer primary key autoincrement, title text not null, description text null, created_at date not null);";

  private static final String TRACK_INFO_TABLE_CREATE = "create table " 
           + TRACK_INFO_TABLE 
           + " (_id integer primary key autoincrement, track_id integer not null, latitude real not null, longitude real not null, altitude real not null, created_at date not null);";

private SQLiteDatabase      database;

public DatabaseHelper() 
{  
    try
    {
        database = SQLiteDatabase.openDatabase(DATABASE_FILE_PATH
            + File.separator + DATABASE_NAME, null,SQLiteDatabase.OPEN_READWRITE);
    }
    catch (SQLiteException ex)
    {
        Log.e(TAG, "error -- " + ex.getMessage(), ex);
        // error means tables does not exits
        createTables();
    }
    finally
    {
        DBUtil.safeCloseDataBase(database);
    }
}

private void createTables()
{
    database.execSQL(TRACKS_TABLE_CREATE);
    database.execSQL(TRACK_INFO_TABLE_CREATE);
}

public void close()
{
    DBUtil.safeCloseDataBase(database);
}

public SQLiteDatabase getReadableDatabase()
{
    database = SQLiteDatabase.openDatabase(DATABASE_FILE_PATH
            + File.separator + DATABASE_NAME, null,
            SQLiteDatabase.OPEN_READONLY);
    return database;
}

public SQLiteDatabase getWritableDatabase()
{
    database = SQLiteDatabase.openDatabase(DATABASE_FILE_PATH
            + File.separator + DATABASE_NAME, null,
            SQLiteDatabase.OPEN_READWRITE);
    return database;
}

And in the end you have to set permission in manifest like this: android.permission.WRITE_EXTERNAL_STORAGE

Good luck :)
Arkde

Android SQLite Read Database from External Storage Folder

Taken from @k3b answers:

You can use the SQLiteOpenHelper with a custom path if you provide a custom ContextClass and if you have write access to the target directory.

public class DatabaseHelper extends SQLiteOpenHelper {
  private static final int DATABASE_VERSION = 3;
    .....

  DatabaseHelper(final Context context, String databaseName) {
    super(new DatabaseContext(context), databaseName, null, DATABASE_VERSION);
  }
}

And here is the custom DatabaseContext class that does all the magic:

class DatabaseContext extends ContextWrapper {

  private static final String DEBUG_CONTEXT = "DatabaseContext";

  public DatabaseContext(Context base) {
    super(base);
  }

  @Override
  public File getDatabasePath(String name)  {
    File sdcard = Environment.getExternalStorageDirectory();    
    String dbfile = sdcard.getAbsolutePath() + File.separator+ "databases" + File.separator + name;
    if (!dbfile.endsWith(".db")) {
      dbfile += ".db" ;
    }

    File result = new File(dbfile);

    if (!result.getParentFile().exists()) {
      result.getParentFile().mkdirs();
    }

    if (Log.isLoggable(DEBUG_CONTEXT, Log.WARN)) {
      Log.w(DEBUG_CONTEXT, "getDatabasePath(" + name + ") = " + result.getAbsolutePath());
    }

    return result;
  }

  /* this version is called for android devices >= api-11. thank to @damccull for fixing this. */
  @Override
  public SQLiteDatabase openOrCreateDatabase(String name, int mode, SQLiteDatabase.CursorFactory factory, DatabaseErrorHandler errorHandler) {
    return openOrCreateDatabase(name,mode, factory);
  }

  /* this version is called for android devices < api-11 */
  @Override
  public SQLiteDatabase openOrCreateDatabase(String name, int mode, SQLiteDatabase.CursorFactory factory) {
    SQLiteDatabase result = SQLiteDatabase.openOrCreateDatabase(getDatabasePath(name), null);
    // SQLiteDatabase result = super.openOrCreateDatabase(name, mode, factory);
    if (Log.isLoggable(DEBUG_CONTEXT, Log.WARN)) {
      Log.w(DEBUG_CONTEXT, "openOrCreateDatabase(" + name + ",,) = " + result.getPath());
    }
    return result;
  }
}

How does this work:

Normal android apps have their local database files relative to the app folder. By using a customer context with overwritten getDatabasePath() the database is now relative to a different directory on the sd card.

How to use custom database path in ICS and still be backwards compatible?

AFAIK, as of Android 2.2, SQLiteOpenHelper supports databases on external storage, by supplying a full path to the database file.

That being said, bear in mind that external storage and internal storage sorta merged with Android 3.0. In Android 1.x and 2.x, internal and external storage were separate partitions with separate space. In Android 3.0+, they share a partition, with external storage simply being a designated directory in the partition used by internal storage.

Hence, if the reason you are using external storage is due to size considerations, that will no longer be needed starting with API Level 11. If you are using external storage specifically because the user has access to it, that would still be relevant, though I've always been nervous about that, and am more nervous now because Android 3.0 now allows simultaneous access to external storage by apps and the user.

Changing sqlite3 database data on upgrade

This would work in the exact same way as upgrading schema. You are only running SQL statements. Simply use Update statements instead of Alter Table etc

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  if (oldVersion<2) {
    // do upgrade from 1 to 2
  }

  if (oldVersion<3) {
    // do upgrade from 2 to 3, which will also cover 1->3,
    // since you just upgraded 1->2
  }

  // and so on
}

You can use Switch or If statements to check the old version. I personally preferIf as I think it is more readable and the fall through is much more obvious.

Reference:
Confusion: How does SQLiteOpenHelper onUpgrade() behave? And together with import of an old database backup?

---

Related Topics