How to modify type in a table of SQLite in Android, using onUpgrade method?

Question

Following this good example http://code.google.com/p/openintents/source/browse/trunk/notepad/NotePad/src/org/openintents/notepad/NotePadProvider.java?r=3878

I implement my sqlite database upgrade system. But in the link above the only example show how add new column in a table. The piece of code in the onUpgrade method is

 try {
      db.execSQL("ALTER TABLE " +   NOTES_TABLE_NAME + " ADD COLUMN "
                                                                    + Notes.TAGS + " TEXT;");
      db.execSQL("ALTER TABLE " + NOTES_TABLE_NAME + " ADD COLUMN "
                                                                    + Notes.ENCRYPTED + " INTEGER;");
      db.execSQL("ALTER TABLE " + NOTES_TABLE_NAME + " ADD COLUMN "
                                                                    + Notes.THEME + " TEXT;");
      } catch (SQLException e) {
      Log.e(TAG, "Error executing SQL: ", e);
      // If the error is "duplicate column name" then everything is fine,
      // as this happens after upgrading 2->3, then downgrading 3->2, 
      // and then upgrading again 2->3.
      }
      //  *** other upgrade from version x->y
      // ***

and in onCreate, something like this:

  public void onCreate(SQLiteDatabase db) {
      db.execSQL("CREATE TABLE " + NOTES_TABLE_NAME + " ("
           // Version 2:
           + Notes._ID + " INTEGER PRIMARY KEY,"
           + Notes.TITLE + " TEXT,"
           + Notes.NOTE + " TEXT,"
           + Notes.CREATED_DATE + " INTEGER,"
           + Notes.MODIFIED_DATE + " INTEGER,"
           // Version 3:
           + Notes.TAGS + " TEXT,"
           + Notes.ENCRYPTED + " INTEGER,"
           + Notes.THEME + " TEXT,"
           // Version 4:
           + Notes.SELECTION_START + " INTEGER,"
           + Notes.SELECTION_END + " INTEGER,"
           + Notes.SCROLL_POSITION + " REAL"
           + ");");
          }

Now I want a new database version where no column is added, but I want just change type of some field in a table. How can I do this?

Here http://sqlite.org/lang_altertable.html is said that is not possible to change a field type. How can I circumvent the problem?

I'm inserting data in database in this way:

   ContentValues v = new ContentValues();
   v.put("field1", myString); // fileld1 in db is defined as integer

   dbManager.updateTable("mytable", v, whereClause);

How can I make a CAST in this situation?

Answer 1

SQLite indeed does not allow changing existing table columns.

However, SQLite uses dynamic typing, so what column type you set in the table definition does not really matter (except for conversions due to type affinity).

In the general case, any changes to tables (other than new columns) require that you create a new table:

CREATE TABLE TempTable (...);
INSERT INTO TempTable SELECT col1, col2, ... FROM MyTable;
DROP TABLE MyTable;
ALTER TABLE TempTable RENAME TO MyTable;

If you want to change the type of values already stored in the table, you have to use CAST:

...
INSERT INTO TempTable SELECT CAST(col1 AS TEXT), ... From MyTable;
...

For new values, just put a value with the correct type into the ContentValues object.