upsert method

Future<void> upsert(
  1. {@required String table,
  2. @required Map<String, String> row,
  3. List<String> preserveColumns = const [],
  4. String indexColumn,
  5. bool verbose = false}
)

Insert a row if it does not exist or update it

It is highly recommended to use an unique index for the table to upsert into

Implementation

Future<void> upsert(
    {@required String table,
    @required Map<String, String> row,
    //@required List<String> columns,
    List<String> preserveColumns = const [],
    String indexColumn,
    bool verbose = false}) async {
  /// The [preserveColumns] is used to keep the current values
  /// for some columns. If this parameter is used an [indexColumn]
  /// must be provided to search for the value of the column to preserve
  await _mutex.synchronized(() async {
    if (!_isReady) {
      throw DatabaseNotReady();
    }
    if (preserveColumns.isNotEmpty) {
      if (indexColumn == null) {
        throw ArgumentError("Please provide a value for indexColumn "
            "if you use preserveColumns");
      }
    }
    try {
      final timer = Stopwatch()..start();
      var fields = "";
      var values = "";
      preserveColumns.forEach((c) {
        row[c] = "";
      });
      final n = row.length;
      var i = 1;
      final fieldsBuf = StringBuffer();
      final valuesBuf = StringBuffer();
      for (final k in row.keys) {
        fieldsBuf.write("$k");
        if (preserveColumns.contains(k)) {
          valuesBuf.write("(SELECT $k FROM $table WHERE "
              "$indexColumn='${row[indexColumn]}')");
        } else {
          valuesBuf.write("'${row[k]}'");
        }
        //pairs += "$k='${row[k]}'";
        if (i < n) {
          //fields += ",";
          fieldsBuf.write(",");
          valuesBuf.write(",");
          //pairs += ",";
        }
        i++;
      }
      fields = fieldsBuf.toString();
      values = valuesBuf.toString();
      // This only works for Sqlite > 3.24
      /*
      String q = "INSERT INTO $table ($fields) VALUES($values)";
      q += " ON CONFLICT($columns) DO UPDATE SET $pairs";*/
      final q = "INSERT OR REPLACE INTO $table ($fields) VALUES($values)";
      await _db.transaction((txn) async {
        await txn.execute(q);
      }).catchError((dynamic e) =>
          throw WriteQueryException("Can not upsert into table $table $e"));
      timer.stop();
      _changeFeedController.sink.add(DatabaseChangeEvent(
          type: DatabaseChange.upsert,
          value: i,
          query: q,
          table: table,
          data: row,
          executionTime: timer.elapsedMicroseconds));
      if (verbose) {
        print("$q $row in ${timer.elapsedMilliseconds} ms");
      }
    } catch (e) {
      rethrow;
    }
  });
}