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.
- Cómo realizar operaciones de base de datos mediante Async Task
- Android SQLite ORDER BY no funciona
- No se pudo abrir la base de datos SQLite
- ¿Cómo se obtiene el tipo de un cursor?
- ¿Es Parse.com un servicio adecuado para la copia de seguridad / sincronización con SQLite en Android?
@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
- Utilizar CursorLoader para consultar la base de datos SQLite y rellenar AutoCompleteTextView
- ¿Cómo popule ListView con elementos de la base de datos en Xamarin?
- ¿Qué hacer con Cursor después de una consulta SQLite?
- Android Studio vs idioma ruso
- SQLiteOpenHelper - ¿Cómo se crea la base de datos?
- SQLiteOpenHelper no puede llamar a onCreate?
- Abrir y editar archivos PDF en la aplicación de Android con API
- Aplicación de Android con base de datos constantemente "inicio / espera de un bloqueo gc alloc"
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
- API de huellas dactilares para el teléfono Android
- Cómo cambiar el nombre de los archivos en Android Studio