如何使用onUpgrade方法修改Android中SQLite表中的type ?

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

遵循这个很好的例子http://code.google.com/p/openintents/source/browse/trunk/trunk/notepad/notepad/src/org/openints/notepad/notepad/notepad/notepad/notepadproprovider.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

我实现了我的sqlite数据库升级系统。但是在上面的链接中，唯一的示例显示了如何在表中添加新列。onUpgrade方法中的代码段是

 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:

在onCreate，类似这样的东西:

  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?

在这里，http://sqlite.org/lang_altertable.html表示不可能更改字段类型。我该如何规避这个问题呢?

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?

在这种情况下我该如何投石膏?

1 个解决方案

#1

SQLite indeed does not allow changing existing table columns.

SQLite确实不允许更改现有的表列。

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).

但是，SQLite使用动态类型，所以在表定义中设置什么列类型并不重要(由于类型关联而导致的转换除外)。

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:

如果要更改表中已存储的值的类型，必须使用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.

对于新值，只需将正确类型的值放入ContentValues对象中。

#1