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.
- Android MediaStore obtiene distintas carpetas de archivos de música
- Android classificar los resultados de la consulta sqlite ignorando el caso
- Android: Excepción al actualizar la tabla en la base de datos
- Acceso a los datos principales de iOS desde Android
- AsyncTask de Android y instancia de SQLite DB
@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
- ¿Rellenar la base de datos de Android desde el archivo CSV?
- SQLite Transformar columnas en cadena delimitada por comas
- Eliminación automática de objetos anidados en ORMLite
- declaración no válida en fillwindow () en Android
- ¿Cómo funciona un cursor SQLite trabajar internamente?
- RawQuery: enlace o índice de columna fuera de rango
- Android SqliteAssetHelper - fusionar las tablas de la base de datos del activo con el existente
- Android.database.sqlite.SQLITeException: no hay tal tabla: admin mientras compila: INSERT INTO ... etc
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