varias - Android/SQLite: Insertar-Actualizar columnas de la tabla para mantener el identificador

relacionar tablas en sqlite android

Question

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 en el extremo hace que las filas se eliminen cuando se realizan nuevas inserciones que vienen con las mismas coordenadas. La documentación de SQLite contiene más información sobre la cláusula de conflicto.

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

• Como una cláusula de conflicto en la CREATE TABLE .
• Como un activador INSERT .
• Como cláusula condicional en el método de ContentProvider#insert .
• ... mejor que puedas pensar

Creo que es más eficaz manejar estos conflictos dentro de la base de datos. Además, me resulta difícil volver a escribir el método de ContentProvider#insert para considerar el escenario de inserción / actualización . Aquí está el código del método de 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 del servidor, todo lo que hago es insertar los datos de la siguiente manera.

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

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

Editar:

@CommonsWare: traté de implementar su sugerencia de usar INSERT OR REPLACE . Se me ocurrió este código feo.

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 reviso la base de datos SQLite, sin embargo, todavía se eliminan las columnas iguales y se insertan con nuevos identificadores . No entiendo por qué sucede esto y pensé que la razón es mi cláusula de conflicto. Pero la documentation 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 ninguna parte, se usa el algoritmo ABORTO.

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

Answer 1

¿Puedo entender la noción percibida de que es mejor para el rendimiento hacer toda esta lógica en SQL, pero tal vez la solución más simple (menos código) sea la mejor en este caso? ¿Por qué no intentar primero la actualización y luego usar insertWithOnConflict() con CONFLICT_IGNORE para realizar la inserción (si es necesario) y obtener el ID de 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 verificar el valor de retorno de update() y hacer solo la inserción si el recuento afectado era cero, pero en un caso en el que no podría obtener el ID de la fila existente sin una selección adicional. Esta forma de inserción siempre le devolverá el ID correcto para devolverlo en Uri y no modificará la base de datos más de lo necesario.

Si desea hacer un gran número de estos a la vez, puede consultar el método bulkInsert() de su proveedor, donde puede ejecutar múltiples inserciones dentro de una sola 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 que las cosas sean 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() simplemente permite que se pasen múltiples ContentValues de ContentValues con una sola llamada al proveedor.

Answer 2

Una solución es crear una vista para la tabla de locations con un desencadenador INSTEAD OF en la vista, luego insertarla en la vista. Así es como se vería eso:

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 , inserte en la vista de locations_view vista. El activador se encargará de proporcionar el valor _id correcto mediante el uso de la selección secundaria. Si, por alguna razón, el inserto ya contiene un _id COALESCE lo mantendrá y anulará uno existente en la tabla.

Probablemente querrá comprobar cuánto afecta la selección secundaria al rendimiento y compararlo con otros posibles cambios que podría hacer, pero le permite mantener esta lógica fuera de su código.

Intenté algunas otras soluciones que involucran desencadenantes en la propia tabla basados ​​en INSERTAR O IGNORAR, pero parece que los desencadenadores ANTES y DESPUÉS solo se activan si realmente se insertarán en la tabla.

Puede que encuentre útil esta respuesta , que es la base del desencadenante.

Edición: debido a que los activadores ANTES y DESPUÉS no se activan cuando se ignora una inserción (que podría haberse actualizado en su lugar), debemos volver a escribir la inserción con un activador INSTEAD OF. Desafortunadamente, esos no funcionan con tablas, tenemos que crear una vista para usarla.

Answer 3

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

Lea más en los siguientes enlaces:

insertWithOnConflict()

Bandera CONFLICT_REPLACE

Answer 4

Utilice INSERTAR O SUSTITUIR .

Esta es la forma correcta de hacerlo.

Answer 5

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

Le recomendaría seguir con su solución actual, crear una tabla con la regla de ON CONFLICT , pero cada vez que inserte una fila y ocurra una violación de la restricción, su nueva fila tendrá un nuevo _id ya que la fila de origen se eliminará.

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

Si aún desea 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 la inserción falló (o viceversa). Para preservar la coherencia, debe 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();
}