SQLite agregar columna, mantener datos

Tengo una base de datos con 4 columnas:

@Override public void onCreate(SQLiteDatabase database) { database.execSQL("CREATE TABLE " + DATABASENAME + " (name TEXT, latitude REAL, longitude REAL, country TEXT);"); } 

Ahora quiero agregar otra columna, pero mantener las filas que ya están en la base de datos. ¿Cómo debería hacer esto? No puedo encontrar referencias útiles a esto.

 @Override public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) { //What to do here? } 

EDITAR

He modificado mi onCreate para:

 @Override public void onCreate(SQLiteDatabase database) { database.execSQL("CREATE TABLE " + DATABASENAME + " (name TEXT, latitude REAL, longitude REAL, country TEXT, code TEXT);"); } 

Y mi onUpgrade a:

 @Override public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) { Logger.log("Updating userstations database from " + arg1 + " to " + arg2 + "."); if (arg1 == 2 && arg2 == 3) { db.execSQL("ALTER TABLE " + DATABASENAME + " ADD COLUMN code TEXT;"); } else if(arg1 == 1){ db.execSQL("DROP TABLE IF EXISTS " + DATABASENAME); } onCreate(db); } 

Sin embargo, ahora tengo este stacktrace:

 10-01 21:24:19.581: ERROR/Database(21434): Failure 1 (table userstations already exists) on 0x19c378 when preparing 'CREATE TABLE userstations (name TEXT, latitude REAL, longitude REAL, country TEXT, code TEXT);'. 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): Couldn't open userstations for writing (will try read-only): 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): android.database.sqlite.SQLiteException: table userstations already exists: CREATE TABLE userstations (name TEXT, latitude REAL, longitude REAL, country TEXT, code TEXT); 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.database.sqlite.SQLiteDatabase.native_execSQL(Native Method) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1763) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at com.myapp.myapp.databases.UserStationsOpenHelper.onCreate(UserStationsOpenHelper.java:25) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at com.myapp.myapp.databases.UserStationsOpenHelper.onUpgrade(UserStationsOpenHelper.java:36) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:132) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:187) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at com.myapp.myapp.databases.UserStationsOpenHelper.getStations(UserStationsOpenHelper.java:43) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at com.myapp.myapp.Data.readUserStations(Data.java:369) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at com.myapp.myapp.Data.getUserStations(Data.java:210) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at com.myapp.myapp.stationstab.StationsActivity.newStations(StationsActivity.java:190) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at com.myapp.myapp.stationstab.StationsActivity.refresh(StationsActivity.java:129) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at com.myapp.myapp.stationstab.StationsActivity.onCreate(StationsActivity.java:108) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1722) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.app.ActivityThread.startActivityNow(ActivityThread.java:1598) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.app.LocalActivityManager.moveToState(LocalActivityManager.java:127) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.app.LocalActivityManager.startActivity(LocalActivityManager.java:339) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at com.myapp.myapp.stationstab.StationsActivityGroup.onCreate(StationsActivityGroup.java:38) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1722) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.app.ActivityThread.startActivityNow(ActivityThread.java:1598) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.app.LocalActivityManager.moveToState(LocalActivityManager.java:127) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.app.LocalActivityManager.startActivity(LocalActivityManager.java:339) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.widget.TabHost$IntentContentStrategy.getContentView(TabHost.java:654) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.widget.TabHost.setCurrentTab(TabHost.java:326) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.widget.TabHost.addTab(TabHost.java:216) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at com.myapp.myapp.TreinVerkeer.setupTab(TreinVerkeer.java:131) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at com.myapp.myapp.TreinVerkeer.initTabs(TreinVerkeer.java:108) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at com.myapp.myapp.TreinVerkeer.onCreate(TreinVerkeer.java:62) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1722) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1784) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.app.ActivityThread.access$1500(ActivityThread.java:123) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.app.ActivityThread$H.handleMessage(ActivityThread.java:939) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.os.Handler.dispatchMessage(Handler.java:99) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.os.Looper.loop(Looper.java:123) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at android.app.ActivityThread.main(ActivityThread.java:3835) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at java.lang.reflect.Method.invokeNative(Native Method) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at java.lang.reflect.Method.invoke(Method.java:507) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:841) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:599) 10-01 21:24:19.611: ERROR/SQLiteOpenHelper(21434): at dalvik.system.NativeStart.main(Native Method) 10-01 21:24:19.621: DEBUG/AndroidRuntime(21434): Shutting down VM 10-01 21:24:19.621: WARN/dalvikvm(21434): threadid=1: thread exiting with uncaught exception (group=0x4018a560) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): FATAL EXCEPTION: main 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.myapp.myapp/com.myapp.myapp.TreinVerkeer}: java.lang.RuntimeException: Unable to start activity ComponentInfo{com.myapp.myapp/com.myapp.myapp.stationstab.StationsActivityGroup}: java.lang.RuntimeException: Unable to start activity ComponentInfo{com.myapp.myapp/com.myapp.myapp.stationstab.StationsActivity}: android.database.sqlite.SQLiteException: Can't upgrade read-only database from version 2 to 3: /data/data/com.myapp.myapp/databases/userstations 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1768) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1784) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.app.ActivityThread.access$1500(ActivityThread.java:123) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.app.ActivityThread$H.handleMessage(ActivityThread.java:939) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.os.Handler.dispatchMessage(Handler.java:99) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.os.Looper.loop(Looper.java:123) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.app.ActivityThread.main(ActivityThread.java:3835) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at java.lang.reflect.Method.invokeNative(Native Method) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at java.lang.reflect.Method.invoke(Method.java:507) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:841) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:599) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at dalvik.system.NativeStart.main(Native Method) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): Caused by: java.lang.RuntimeException: Unable to start activity ComponentInfo{com.myapp.myapp/com.myapp.myapp.stationstab.StationsActivityGroup}: java.lang.RuntimeException: Unable to start activity ComponentInfo{com.myapp.myapp/com.myapp.myapp.stationstab.StationsActivity}: android.database.sqlite.SQLiteException: Can't upgrade read-only database from version 2 to 3: /data/data/com.myapp.myapp/databases/userstations 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1768) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.app.ActivityThread.startActivityNow(ActivityThread.java:1598) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.app.LocalActivityManager.moveToState(LocalActivityManager.java:127) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.app.LocalActivityManager.startActivity(LocalActivityManager.java:339) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.widget.TabHost$IntentContentStrategy.getContentView(TabHost.java:654) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.widget.TabHost.setCurrentTab(TabHost.java:326) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.widget.TabHost.addTab(TabHost.java:216) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at com.myapp.myapp.TreinVerkeer.setupTab(TreinVerkeer.java:131) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at com.myapp.myapp.TreinVerkeer.initTabs(TreinVerkeer.java:108) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at com.myapp.myapp.TreinVerkeer.onCreate(TreinVerkeer.java:62) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1722) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): ... 11 more 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): Caused by: java.lang.RuntimeException: Unable to start activity ComponentInfo{com.myapp.myapp/com.myapp.myapp.stationstab.StationsActivity}: android.database.sqlite.SQLiteException: Can't upgrade read-only database from version 2 to 3: /data/data/com.myapp.myapp/databases/userstations 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1768) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.app.ActivityThread.startActivityNow(ActivityThread.java:1598) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.app.LocalActivityManager.moveToState(LocalActivityManager.java:127) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.app.LocalActivityManager.startActivity(LocalActivityManager.java:339) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at com.myapp.myapp.stationstab.StationsActivityGroup.onCreate(StationsActivityGroup.java:38) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1722) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): ... 22 more 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): Caused by: android.database.sqlite.SQLiteException: Can't upgrade read-only database from version 2 to 3: /data/data/com.myapp.myapp/databases/userstations 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:199) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at com.myapp.myapp.databases.UserStationsOpenHelper.getStations(UserStationsOpenHelper.java:43) 10-01 21:24:19.641: ERROR/AndroidRuntime(21434): at com.myapp.myapp 

Por favor vea esta página para la sintaxis para crear una nueva columna en una tabla. Básicamente es:

 ALTER TABLE mytable ADD COLUMN mycolumn TEXT 

En su método onUpgrade, se vería algo como esto:

 @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { String upgradeQuery = "ALTER TABLE mytable ADD COLUMN mycolumn TEXT"; if (oldVersion == 1 && newVersion == 2) db.execSQL(upgradeQuery); } 

Un mejor enfoque

 @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { switch (oldVersion) { case 1: db.execSQL(SQL_MY_TABLE); case 2: db.execSQL("ALTER TABLE myTable ADD COLUMN myNewColumn TEXT"); } } 

Vamos a decir en el caso 1, que actualizó a la versión de db 2. Usted creó una nueva tabla, pero se olvidó de la myNewColumn se verá en el caso 2. Lo que esto hará es que si cambia la versión db a 3, el caso 2 se ejecutará si su Pasando de 2 a 3.

No he trabajado con android, pero sqlite proporciona 'alter table' como la mayoría de las implementaciones de SQL hace:

SQLite alter table

  • Usar swagger con una base de datos de android
  • GreenDao consultas profundas con relaciones n: m
  • Android - Base de datos SQLite en tarjeta SD
  • ¿Debo usar un proveedor de contenido?
  • Android: transacciones SQLite al usar ContentResolver
  • Android - Pulling SQlite base de datos dispositivo Android
  • Datos de exportación de Android a csv
  • No es posible obtener datos de la base de datos Sqlite
  • Recuperar un nombre de usuario y una contraseña en SQLITE DB por EMAIL_ADDRESS en ANDROID
  • SQLite - insertar una cadena con nuevas líneas en la base de datos desde el archivo csv
  • ¿Cuántas filas puedo insertar en una tabla en SQLite Android?
  • FlipAndroid es un fan de Google para Android, Todo sobre Android Phones, Android Wear, Android Dev y Aplicaciones para Android Aplicaciones.