upsert method
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 {
final v = _serializeStringValue(row[k]);
valuesBuf.write(v);
}
//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;
}
});
}