prepareDeleteBatchedStatements method

List<Statement> prepareDeleteBatchedStatements(String baseSql, List<String> columns, List<Map<String, Object?>> records, int maxParameters, { String suffixSql = '', })

Prepare multiple batched DELETE statements for an array of records.

Since SQLite only supports a limited amount of positional ? parameters, we generate multiple delete statements with each one being filled as much as possible from the given data. This function only supports column equality checks

@param baseSql base SQL string to which inserts should be appended @param columns columns that describe records @param records records to be inserted @param maxParameters max parameters this SQLite can accept - determines batching factor @param suffixSql optional SQL string to append to each insert statement @returns array of statements ready to be executed by the adapter

Implementation

List<Statement> prepareDeleteBatchedStatements(
  String baseSql,
  List<String> columns,
  List<Map<String, Object?>> records,
  int maxParameters, {
  String suffixSql = '',
}) {
  final stmts = <Statement>[];
  final columnCount = columns.length;
  final recordCount = records.length;

  // Amount of rows we can delete at once
  final batchMaxSize = (maxParameters / columnCount).floor();

  // keep a temporary join array for joining strings, to avoid
  // the overhead of generating a new array every time
  final tempColumnComparisonJoinArr =
      List<String>.filled(columnCount, '', growable: false);

  int processed = 0;
  int prevDeleteCount = -1;
  String deletePattern = '';

  while (processed < recordCount) {
    final currentDeleteCount = min(recordCount - processed, batchMaxSize);

    // cache delete pattern as it is going to be the same for every batch
    // of `batchMaxSize` - ideally we can externalize this cache since for a
    // given adapter this is _always_ going to be the same
    if (currentDeleteCount != prevDeleteCount) {
      deletePattern = List.generate(currentDeleteCount, (recordIdx) {
        for (int i = 0; i < columnCount; i++) {
          tempColumnComparisonJoinArr[i] =
              '"${columns[i]}" = ${makePositionalParam(recordIdx * columnCount + i + 1)}';
        }
        return ' (${tempColumnComparisonJoinArr.join(' AND ')})';
      }).join(' OR');
    }
    String sql = baseSql + deletePattern;

    if (suffixSql != '') {
      sql += ' $suffixSql';
    }

    final List<Object?> args = [];
    for (int i = 0; i < currentDeleteCount; i++) {
      for (int j = 0; j < columnCount; j++) {
        args.add(records[processed + i][columns[j]]);
      }
    }

    processed += currentDeleteCount;
    prevDeleteCount = currentDeleteCount;
    stmts.add(Statement(sql, args));
  }
  return stmts;
}