wake-up-neo.com

Android/SQLite: Tabellenspalten einfügen, um den Bezeichner beizubehalten

Derzeit verwende ich die folgende Anweisung, um eine Tabelle in einer SQLite-Datenbank auf einem Android-Gerät zu erstellen.

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

Die Konfliktklausel am Ende bewirkt, dass Zeilen drop fallen, wenn neue Einfügungen mit denselben Koordinaten vorgenommen werden. Die SQLite-Dokumentation enthält weitere Informationen zur Konfliktklausel.).

Stattdessen möchte ichdie vorherigen Zeilen beibehalten und update ihre Spalten. Was ist der effizienteste Weg, dies in einer Android/SQLite-Umgebung zu tun?

  • Als Konfliktklausel in der CREATE TABLE-Anweisung.
  • Als INSERT Auslöser.
  • Als Bedingungsklausel in der ContentProvider#insert-Methode.
  • ... besser können Sie sich ausdenken

Ich denke, es ist performanter, solche Konflikte innerhalb der Datenbank zu behandeln. Außerdem fällt es mir schwer, die ContentProvider#insert-Methode umzuschreiben, um das insert-update-Szenario zu berücksichtigen. Hier ist der Code der insert Methode:

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);
}

Wenn Daten vom Backend ankommen, füge ich die Daten wie folgt ein.

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

Ich habe Probleme damit, herauszufinden, wie ich hier einen alternativen Aufruf von ContentProvider#update beantragen kann. Außerdem ist dies sowieso nicht meine bevorzugte Lösung.


Bearbeiten:

@CommonsWare: Ich habe versucht, Ihren Vorschlag zur Verwendung von INSERT OR REPLACE zu implementieren. Ich habe mir dieses hässliche Stück Code ausgedacht.

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;
}

Wenn ich die SQLite-Datenbank überprüfe, werden jedoch gleich viele Spalten entfernt und mit neuen IDs eingefügt. Ich verstehe nicht, warum dies passiert, und dachte, der Grund ist meine Konfliktklausel. Aber das Dokumentation sagt das Gegenteil aus.

Der in der Klausel OR eines INSERT oder UPDATE überschriebene Algorithmus überschreibt jeden in einer CREATE TABLE angegebenen Algorithmus. Wenn an keiner Stelle ein Algorithmus Angegeben ist, wird der ABORT-Algorithmus verwendet.

Ein weiterer Nachteil dieses Versuchs besteht darin, dass Sie den Wert der ID verlieren, die von einer Einfügeanweisung zurückgegeben wird. Um dies zu kompensieren, fand ich endlich eine Option, um nach dem last_insert_rowid zu fragen. Es ist wie erklärt in den Posts von dtmilano und swiz . Ich bin mir jedoch nicht sicher, ob dies sicher ist, da ein weiterer Einsatz dazwischen passieren kann.

25
JJD

Ich kann die wahrgenommene Vorstellung verstehen, dass es für die Leistung am besten ist, all diese Logik in SQL auszuführen, aber vielleicht ist die einfachste (am wenigsten Code) Lösung in diesem Fall die beste? Warum versuchen Sie es nicht zuerst mit dem Update, und verwenden Sie dann insertWithOnConflict() mit CONFLICT_IGNORE, um die Einfügung (falls erforderlich) vorzunehmen und die erforderliche Zeilen-ID zu erhalten:

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);
}

Eine einfachere Lösung wäre, den Rückgabewert von update() zu überprüfen und die Einfügung nur durchzuführen, wenn die betroffene Anzahl Null ist. In diesem Fall könnten Sie jedoch die ID der vorhandenen Zeile nicht ohne ein zusätzliches select erhalten. Diese Form der Einfügung wird Ihnen immer die korrekte ID zurückgeben, die in Uri zurückgegeben wird, und ändert die Datenbank nicht mehr als nötig.

Wenn Sie eine große Anzahl davon gleichzeitig ausführen möchten, sehen Sie sich die bulkInsert()-Methode Ihres Providers an, in der Sie mehrere Einfügungen in einer einzigen Transaktion ausführen können. In diesem Fall sollte die "einfachere" Lösung problemlos funktionieren, da Sie nicht die id des aktualisierten Datensatzes zurückgeben müssen.

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;
}

In der Tat macht der Transaktionscode die Sache schneller, indem er die Anzahl der Schreibvorgänge in der Datei auf die Datei minimiert. bulkInsert() erlaubt nur die Weitergabe mehrerer ContentValues mit einem einzigen Aufruf an den Provider.

19
Devunwired

Eine Lösung besteht darin, eine Ansicht für die Tabelle locations mit einem INSTEAD OF-Trigger in der Ansicht zu erstellen und dann in die Ansicht einzufügen. So würde das aussehen:

Aussicht:

CREATE VIEW locations_view AS SELECT * FROM locations;

Auslösen:

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;

Anstatt in die Tabelle locations einzufügen, fügen Sie sie in die Ansicht locations_view ein. Der Auslöser sorgt dafür, dass der korrekte _id-Wert mithilfe der Unterauswahl bereitgestellt wird. Wenn aus irgendeinem Grund die Einfügung bereits einen _id enthält, behält die COALESCE sie und überschreibt eine vorhandene in der Tabelle.

Sie werden wahrscheinlich prüfen wollen, inwieweit sich die Auswahl durch die Unterauswahl auswirkt, und vergleichen Sie diese mit anderen möglichen Änderungen, die Sie vornehmen könnten, aber Sie können diese Logik aus Ihrem Code heraushalten.

Ich habe ein paar andere Lösungen ausprobiert, bei denen Trigger für die Tabelle selbst verwendet wurden, die auf INSERT OR IGNORE basiert. Es scheint jedoch, dass BEVOR und NACHD nur getriggert werden, wenn sie tatsächlich in die Tabelle eingefügt werden.

Möglicherweise finden Sie diese Antwort hilfreich, was die Basis für den Auslöser ist.

Edit: Da die BEFORE und AFTER-Trigger nicht ausgelöst werden, wenn ein Insert ignoriert wird (was stattdessen aktualisiert worden sein könnte), müssen wir den Insert mit einem INSTEAD OF-Trigger neu schreiben. Leider funktionieren diese nicht mit Tabellen - wir müssen eine Ansicht erstellen, um sie verwenden zu können.

5
blazeroni

INSERT OR REPLACE funktioniert genauso wie ON CONFLICT REPLACE. Die Zeile wird gelöscht, wenn die Zeile mit der eindeutigen Spalte bereits vorhanden ist. Anschließend wird eine Zeile eingefügt. Es wird nie aktualisiert.

Ich würde empfehlen, dass Sie bei Ihrer aktuellen Lösung bleiben, Sie erstellen eine Tabelle mit ON CONFLICT clausule, aber jedes Mal, wenn Sie eine Zeile einfügen und die Einschränkungsverletzung auftritt, wird Ihre neue Zeile mit dem neuen _id als Origin-Zeile gelöscht.

Oder Sie können eine Tabelle ohne ON CONFLICT clausule erstellen und INSERT OR REPLACE verwenden. Dazu können Sie die Methode insertWithOnConflict () verwenden. Diese Methode ist jedoch verfügbar, da API-Ebene 8 mehr Codierung erfordert und zu derselben Lösung führt wie die Tabelle mit ON CONFLICT clausule.

Wenn Sie Ihre Origin-Zeile dennoch beibehalten möchten, bedeutet dies, dass Sie den gleichen _id beibehalten möchten. Sie müssen zunächst zwei Abfragen durchführen, um eine Zeile einzufügen, und zweitens, um eine Zeile zu aktualisieren, falls das Einfügen fehlgeschlagen ist (oder umgekehrt). Um die Konsistenz zu gewährleisten, müssen Sie Abfragen in einer Transaktion ausführen.

    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();
    }
3
biegleux

Verwenden Sie insertWithOnConflict und setzen Sie den letzten Parameter (conflictAlgorithm) auf CONFLICT_REPLACE.

Lesen Sie mehr unter den folgenden Links:

insertWithOnConflict-Dokumentation

CONFLICT_REPLACE-Flag

0
Muzikant