prepareInsertBatchedStatements method

List<Statement> prepareInsertBatchedStatements(String baseSql, List<String> columns, List<Map<String, Object?>> records, int maxParameters, [ String suffixSql = '', ])

Prepare multiple batched insert statements for an array of records.

Since SQLite only supports a limited amount of positional ? parameters, we generate multiple insert statements with each one being filled as much as possible from the given data. All statements are derived from same baseSql - the positional parameters will be appended to this string.

@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> prepareInsertBatchedStatements(
  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 insert 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 tempColJoinArray =
      List<String>.filled(columnCount, '', growable: false);

  int processed = 0;
  int prevInsertCount = -1;
  String insertPattern = '';

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

    // cache insert 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 (currentInsertCount != prevInsertCount) {
      insertPattern = List.generate(currentInsertCount, (recordIdx) {
        for (int i = 0; i < columnCount; i++) {
          tempColJoinArray[i] = makePositionalParam(
            recordIdx * columnCount + i + 1,
          );
        }
        return ' (${tempColJoinArray.join(', ')})';
      }).join(',');
    }

    String sql = baseSql + insertPattern;

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

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

    processed += currentInsertCount;
    prevInsertCount = currentInsertCount;
    stmts.add(Statement(sql, args));
  }
  return stmts;
}