Join FlipAndroid.COM Telegram Group: https://t.me/joinchat/F_aqThGkhwcLzmI49vKAiw


Android / SQLite: columnas de la tabla Insert-Update para mantener el identificador

Actualmente, estoy usando la siguiente declaración para crear una tabla en una base de datos SQLite en un dispositivo Android.

CREATE TABLE IF NOT EXISTS 'locations' ( '_id' INTEGER PRIMARY KEY AUTOINCREMENT, 'name' TEXT, 'latitude' REAL, 'longitude' REAL, UNIQUE ( 'latitude', 'longitude' ) ON CONFLICT REPLACE ); 

La cláusula de conflicto al final hace que las filas se eliminen cuando se hacen nuevos insertos que vienen con las mismas coordenadas. La documentación de SQLite contiene información adicional sobre la cláusula de conflicto.

En su lugar, me gustaría mantener las filas anteriores y acaba de actualizar sus columnas. ¿Cuál es la forma más eficiente de hacerlo en un entorno Android / SQLite?

  • Como una cláusula de conflicto en la instrucción CREATE TABLE .
  • Como un disparador INSERT .
  • Como cláusula condicional en el método de ContentProvider#insert .
  • Mejor que puedas pensar

Yo creo que es más eficiente para manejar estos conflictos dentro de la base de datos. Además, me resulta difícil reescribir el método de ContentProvider#insert para considerar el escenario de inserción-actualización . Aquí está el código del método insert :

 public Uri insert(Uri uri, ContentValues values) { final SQLiteDatabase db = mOpenHelper.getWritableDatabase(); long id = db.insert(DatabaseProperties.TABLE_NAME, null, values); return ContentUris.withAppendedId(uri, id); } 

Cuando los datos llegan desde el backend todo lo que hago es insertar los datos de la siguiente manera.

 getContentResolver.insert(CustomContract.Locations.CONTENT_URI, contentValues); 

Tengo problemas para averiguar cómo aplicar una llamada alternativa a ContentProvider#update aquí. Además, esta no es mi solución favorecida de todos modos.


Editar:

@CommonsWare: He intentado implementar tu sugerencia para usar INSERT OR REPLACE . Me vino con este pedazo feo de código.

 private static long insertOrReplace(SQLiteDatabase db, ContentValues values, String tableName) { final String COMMA_SPACE = ", "; StringBuilder columnsBuilder = new StringBuilder(); StringBuilder placeholdersBuilder = new StringBuilder(); List<Object> pureValues = new ArrayList<Object>(values.size()); Iterator<Entry<String, Object>> iterator = values.valueSet().iterator(); while (iterator.hasNext()) { Entry<String, Object> pair = iterator.next(); String column = pair.getKey(); columnsBuilder.append(column).append(COMMA_SPACE); placeholdersBuilder.append("?").append(COMMA_SPACE); Object value = pair.getValue(); pureValues.add(value); } final String columns = columnsBuilder.substring(0, columnsBuilder.length() - COMMA_SPACE.length()); final String placeholders = placeholderBuilder.substring(0, placeholdersBuilder.length() - COMMA_SPACE.length()); db.execSQL("INSERT OR REPLACE INTO " + tableName + "(" + columns + ") VALUES (" + placeholders + ")", pureValues.toArray()); // The last insert id retrieved here is not safe. Some other inserts can happen inbetween. Cursor cursor = db.rawQuery("SELECT * from SQLITE_SEQUENCE;", null); long lastId = INVALID_LAST_ID; if (cursor != null && cursor.getCount() > 0 && cursor.moveToFirst()) { lastId = cursor.getLong(cursor.getColumnIndex("seq")); } cursor.close(); return lastId; } 

Cuando compruebo la base de datos SQLite, sin embargo, las columnas iguales todavía se quitan e insertan con nuevos ids . No entiendo por qué sucede esto y pensé que la razón es mi cláusula de conflicto. Pero la documentación dice lo contrario.

El algoritmo especificado en la cláusula OR de un INSERT o UPDATE anula cualquier algoritmo especificado en un CREATE TABLE. Si no se especifica ningún algoritmo en ningún lugar, se utiliza el algoritmo ABORT.

Otra desventaja de este intento es que pierdes el valor del id que es devuelto por una instrucción insert. Para compensar esto, finalmente encontré una opción para pedir el last_insert_rowid . Es como se explica en los postes de dtmilano y swiz . Sin embargo, no estoy seguro si esto es seguro ya que otro inserto puede ocurrir entre ellos.

5 Solutions collect form web for “Android / SQLite: columnas de la tabla Insert-Update para mantener el identificador”

Puedo entender la noción percibida de que es mejor para el rendimiento hacer toda esta lógica en SQL, pero quizás la solución más simple (menos de código) es la mejor en este caso? ¿Por qué no intentar la actualización primero, y luego usar insertWithOnConflict() con CONFLICT_IGNORE para hacer el inserto (si es necesario) y obtener la id de la fila que necesita:

 public Uri insert(Uri uri, ContentValues values) { final SQLiteDatabase db = mOpenHelper.getWritableDatabase(); String selection = "latitude=? AND longitude=?"; String[] selectionArgs = new String[] {values.getAsString("latitude"), values.getAsString("longitude")}; //Do an update if the constraints match db.update(DatabaseProperties.TABLE_NAME, values, selection, null); //This will return the id of the newly inserted row if no conflict //It will also return the offending row without modifying it if in conflict long id = db.insertWithOnConflict(DatabaseProperties.TABLE_NAME, null, values, CONFLICT_IGNORE); return ContentUris.withAppendedId(uri, id); } 

Una solución más simple sería comprobar el valor devuelto de update() y sólo hacer la inserción si el recuento afectado era cero, pero entonces habría un caso en el que no podría obtener el id de la fila existente sin una selección adicional. Esta forma de inserto siempre le devolverá el ID correcto para pasar de nuevo en el Uri , y no modificará la base de datos más de lo necesario.

Si desea realizar un gran número de estos a la vez, puede consultar el método bulkInsert() en su proveedor, donde puede ejecutar varias inserciones dentro de una única transacción. En este caso, ya que no es necesario devolver el id del registro actualizado, la solución "más simple" debería funcionar bien:

 public int bulkInsert(Uri uri, ContentValues[] values) { final SQLiteDatabase db = mOpenHelper.getWritableDatabase(); String selection = "latitude=? AND longitude=?"; String[] selectionArgs = null; int rowsAdded = 0; long rowId; db.beginTransaction(); try { for (ContentValues cv : values) { selectionArgs = new String[] {cv.getAsString("latitude"), cv.getAsString("longitude")}; int affected = db.update(DatabaseProperties.TABLE_NAME, cv, selection, selectionArgs); if (affected == 0) { rowId = db.insert(DatabaseProperties.TABLE_NAME, null, cv); if (rowId > 0) rowsAdded++; } } db.setTransactionSuccessful(); } catch (SQLException ex) { Log.w(TAG, ex); } finally { db.endTransaction(); } return rowsAdded; } 

En realidad, el código de transacción es lo que hace las cosas más rápidas al minimizar el número de veces que la memoria de la base de datos se escribe en el archivo, bulkInsert() sólo permite que varios ContentValues se transmitan con una sola llamada al proveedor.

Una solución es crear una vista para la tabla de locations con un disparador INSTEAD OF en la vista e insertarla en la vista. Esto es lo que se vería así:

Ver:

 CREATE VIEW locations_view AS SELECT * FROM locations; 

Desencadenar:

 CREATE TRIGGER update_location INSTEAD OF INSERT ON locations_view FOR EACH ROW BEGIN INSERT OR REPLACE INTO locations (_id, name, latitude, longitude) VALUES ( COALESCE(NEW._id, (SELECT _id FROM locations WHERE latitude = NEW.latitude AND longitude = NEW.longitude)), NEW.name, NEW.latitude, NEW.longitude ); END; 

En lugar de insertar en la tabla de locations , se inserta en la vista locations_view . El disparador se encargará de proporcionar el valor _id correcto utilizando la sub-selección. Si, por alguna razón, la inserción ya contiene un _id el COALESCE lo mantendrá y anulará uno existente en la tabla.

Es probable que desee comprobar cuánto afecta la sub-selección al rendimiento y compararlo con otros posibles cambios que podría realizar, pero sí le permite mantener esta lógica fuera de su código.

He intentado algunas otras soluciones que implican los disparadores en la tabla sí mismo basada en INSERT O IGNORE, pero parece que los disparadores de BEFORE y de AFTER solamente activan si insertan realmente en la tabla.

Es posible que encuentre esta respuesta útil, que es la base del disparador.

Editar: Debido a que los disparadores de BEFORE y AFTER no disparen cuando se ignora un inserto (que podría haberse actualizado en su lugar), necesitamos volver a escribir el inserto con un disparador INSTEAD OF. Desafortunadamente, los que no funcionan con tablas – tenemos que crear una vista para usarlo.

INSERT OR REPLACE funciona igual que ON CONFLICT REPLACE . Se eliminará la fila si la fila con la columna única ya existe y que hace una inserción. Nunca se actualiza.

Te recomiendo que te quedes con tu solución actual, creas una tabla con clausule ON CONFLICT , pero cada vez que insertes una fila y ocurre la violación de restricción, tu nueva fila tendrá nuevo _id ya que la fila de origen será borrada.

O puede crear tabla sin clausule ON CONFLICT y usar INSERT OR REPLACE , puede usar el método insertWithOnConflict () para eso, pero está disponible desde el nivel 8 de la API, requiere más codificación y conduce a la misma solución que la tabla con clausule ON CONFLICT .

Si todavía quiere mantener su fila de origen, significa que desea mantener el mismo _id tendrá que hacer dos consultas, la primera para insertar una fila, la segunda para actualizar una fila si falla la inserción (o viceversa). Para preservar la coherencia, tiene que ejecutar consultas en una transacción.

  db.beginTransaction(); try { long rowId = db.insert(table, null, values); if (rowId == -1) { // insertion failed String whereClause = "latitude=? AND longitude=?"; String[] whereArgs = new String[] {values.getAsString("latitude"), values.getAsString("longitude")}; db.update(table, values, whereClause, whereArgs); // now you have to get rowId so you can return correct Uri from insert() // method of your content provider, so another db.query() is required } db.setTransactionSuccessful(); } finally { db.endTransaction(); } 

Utilice insertWithOnConflict y establezca el último parámetro ( conflictAlgorithm ) en CONFLICT_REPLACE .

Lea más en los siguientes enlaces:

Documentación de InsertWithOnConflict

CONFLICT_REPLACE flag

Utilice INSERTAR O REEMPLAZAR .

Esta es la manera correcta de hacerlo.

FlipAndroid es un fan de Google para Android, Todo sobre Android Phones, Android Wear, Android Dev y Aplicaciones para Android Aplicaciones.