Program freezes on 31k row inserts

[biblebreeze]

I am trying to insert a Bible per verse into a database. There are 31,102 rows and the program will not update. I use batch to build the table of contents and other areas in the following manner, and it works fine. It's the verses that the program freezes on. Any help is greatly appreciated: [Entries are going into the database, just one at a time and the program fully freezes during the inserts]

 //receives verses in json format and inserts it into db
  Future<Either<Failure, Success>> insertVersesFromJson(
      String verseTable, List<Map<String, dynamic>> list) async {
    try {
      list.map((Map<String, dynamic> json) async {
        final String verseId = json['verse_id'].toString();
        final String bookId = json['book_id'].toString();
        final String verseChapter = json['verse_chapter'].toString();
        final String verseNumber = json['verse_number'].toString();
        final String verseText = json['verse_text'].toString();
        final int isRed = json['is_red'] == 1 ? 1 : 0;
        final String bookName = json['book_name'].toString();

        final String query = 'INSERT INTO $verseTable VALUES( '
            '"$verseId","$bookId","$verseChapter","$verseNumber","$verseText", '
            '"$isRed","$bookName")';
        await customStatement(query);
        return <Map<String, dynamic>>[json];
      }).toList();
      return Right<Failure, Success>(Success());
    } catch (error) {
      errorLog(error.toString(), StackTrace.current);
      return Left<Failure, Success>(DatabaseFailure());
    }
  }

[simolus3]

Both here and in your SO question, you're not actually using a batch. Here, there aren't any batches at all. And in the snippet you've posted on SO, there's a batch for each element in the list, so there will be 31k batches instead of the single one you probably intended.
Also, note that list.map((...) async {}) will not await the asynchronous mapping at all, so the try/catch block will also not catch errors made by any query.
Finally, I recommend not using custom statements when the query can be written with type-safe drift APIs instead. If you want use custom statements, you should almost never use string interpolation in them because they make you vulnerable to something called SQL injection. For instance, if any of the texts included a ", your query would be invalid because the string literal would end too soon.

To use a batch here, start it in the try block and iterate over the list inside of the batch, like this:

 //receives verses in json format and inserts it into db
  Future<Either<Failure, Success>> insertVersesFromJson(
      String verseTable, List<Map<String, dynamic>> list) async {
    try {
      await batch((b) {
        for (Map<String, dynamic> json in list) {
          final String verseId = json['verse_id'].toString();
          // ...
          b.insert(verses, VerseCompanion.insert(verseId: verseId, ...));
        }
      });
      return Right<Failure, Success>(Success());
    } catch (error) {
      errorLog(error.toString(), StackTrace.current);
      return Left<Failure, Success>(DatabaseFailure());
    }
  }

If you need to use a custom statement, use b.customStatement('INSERT INTO $verseTable VALUES(?, ?, ?, ?, ?, ?, ?)', args: [verseId, bookId, verseChapter, verseNumber, verseText, isRed, bookName]);.

To address the freeze: Are you using NativeDatabase.createInBackground or a regular NativeDatabase constructor? The later will access the database in the main thread which can cause freezes. Replacing that with NativeDatabase.createInBackground can solve the freeze issue if you're not using that already.

[biblebreeze]

Thank you for taking the time to explain this in detail! Unfortunately, I have to deal with the injection potential because the Table is created right before the verses are added. If I try to access the table I get:

I will try the createInBackground. I have not touched much of the far backend since initially getting it going a few years ago.

import 'dart:ffi';
import 'dart:io';

import 'package:drift/drift.dart';
import 'package:drift/native.dart';
import 'package:flutter/services.dart';
import 'package:path/path.dart' as p;
import 'package:path_provider/path_provider.dart' as paths;
import 'package:sqlite3/open.dart';

import '../../d_infrastructure/6_data_sources/local_drift_source/drift_database.dart';
import '../utilities/io.dart';

///Database construction goes in here
/// Construction call for Moor Database - Injection point
MoorDatabase constructDb() {
  if (Platform.isWindows) {
    open.overrideFor(OperatingSystem.windows, _openOnWindows);
    final LazyDatabase executor = LazyDatabase(() async {
      final Directory dataDir = await paths.getApplicationDocumentsDirectory();

      final File dbFile = File(p.join(dataDir.path, 'biblebreeze.db'));
      final String path = p.join(dataDir.path, 'biblebreeze.db');
      //* C:\Users\username\AppData\Local\Temp\biblebreeze.db
      if (FileSystemEntity.typeSync(path) != FileSystemEntityType.notFound) {
        print(
            'GUMDUM00: TODO: Change back to == instead of != when done debugging database on windows: moving to $path');
        //* on windows, needs a forward slash / instead of p.join
        final ByteData data = await rootBundle.load('assets/biblebreeze.db');
        final List<int> bytes =
            data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);
        await File(path).writeAsBytes(bytes);
      }
      return NativeDatabase(dbFile);
    });
    return MoorDatabase(executor);
  }
  if (Platform.isIOS || Platform.isAndroid) {
    final LazyDatabase executor = LazyDatabase(() async {
      final Directory dataDir = await paths.getApplicationDocumentsDirectory();
      final File dbFile = File(p.join(dataDir.path, 'biblebreeze.db'));

      final String path = p.join(dataDir.path, 'biblebreeze.db');
      print('gumdum31: looking for $path');
//!gumdum: add in this ifCheck when deployed!!
//!      if (FileSystemEntity.typeSync(path) == FileSystemEntityType.notFound) {
      final ByteData data =
          await rootBundle.load(p.join('assets', 'biblebreeze.db'));

      final List<int> bytes =
          data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);
      await File(path).writeAsBytes(bytes);
//!      }

      return NativeDatabase(dbFile);
    });
    return MoorDatabase(executor);
  }

  if (Platform.isMacOS || Platform.isLinux) {
    final LazyDatabase executor = LazyDatabase(() async {
      final Directory dataDir = await getInstallationDirectory();
      final File dbFile = File(p.join(dataDir.path, 'biblebreeze.db'));
      final String path = p.join(dataDir.path, 'biblebreeze.db');

      print('gumdum30: looking for $path');
//!      if (FileSystemEntity.typeSync(path) == FileSystemEntityType.notFound) {
      print(
          'GUMDUM00: TODO: Change back to == instead of != when done debugging database on macos: moving to $path');
      final ByteData data =
          await rootBundle.load(p.join('assets', 'biblebreeze.db'));
      final List<int> bytes =
          data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);
      await File(path).writeAsBytes(bytes);
//!      }
      return NativeDatabase(dbFile);
    });
    return MoorDatabase(executor);
  }

  return MoorDatabase(NativeDatabase.memory());
}

DynamicLibrary _openOnWindows() {
  final Directory exeDir = File(Platform.resolvedExecutable).parent;
  final File libraryNextToExe = File(p.join(exeDir.path, 'libs', 'sqlite3.dll'));
  if (libraryNextToExe.existsSync()) {
    return DynamicLibrary.open(libraryNextToExe.path);
  }
  final Directory scriptDir = File(Platform.script.toFilePath()).parent;
  final File libraryNextToScript = File(p.join(scriptDir.path, 'sqlite3.dll'));
  return DynamicLibrary.open(libraryNextToScript.path);
}

final bool isDesktop =
    Platform.isWindows || Platform.isMacOS || Platform.isLinux;

[simolus3]

You pretty much just have to replace return NativeDatabase(dbFile); with return NativeDatabase.createInBackground(dbFile). Regarding the type error, perhaps the verses table and companion classes have slightly different names? They should be available as a getter in the generated database class or the part file.