benchmark/lib/main.dart

import 'dart:io';

import 'package:dart_duckdb/dart_duckdb.dart' as duck;
import 'package:flutter/material.dart';
import 'package:flutter/services.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqlite3/sqlite3.dart' as sqlite;

void main() async {
  WidgetsFlutterBinding.ensureInitialized();
  await copyDB("poetry.db");
  await copyDB("poetry.duck.db");
  runApp(const MyApp());
}

Future<void> copyDB(String filename) async {
  ByteData data = await rootBundle.load('assets/db/$filename');
  List<int> bytes = data.buffer.asUint8List(
    data.offsetInBytes,
    data.lengthInBytes,
  );
  var base = (await getApplicationDocumentsDirectory()).path;
  await File("$base/$filename").writeAsBytes(bytes);
}

class MyApp extends StatelessWidget {
  const MyApp({super.key});

  @override
  Widget build(BuildContext context) {
    return MaterialApp(
        title: 'DB Benchmark',
        theme: ThemeData(colorScheme: ColorScheme.fromSeed(seedColor: Colors.deepPurple), useMaterial3: true),
        home: const MyHomePage(title: 'DB Benchmark'));
  }
}

class MyHomePage extends StatefulWidget {
  const MyHomePage({super.key, required this.title});

  final String title;

  @override
  State<MyHomePage> createState() => _MyHomePageState();
}

class _MyHomePageState extends State<MyHomePage> {
  sqlite.Database? db;
  duck.Database? duckdb;
  duck.Connection? connection;
  int _sqliteLikeTime = 0;
  int _sqliteLikeResult = 0;
  int _duckdbLikeTime = 0;
  int _duckdbLikeResult = 0;
  List<List<Object?>>? _sqliteResult;
  List<List<dynamic>>? _duckdbResult;

  void _like() {
    var sql = Stopwatch();
    sql.start();
    var sqlResultSet = db!.select('SELECT * FROM poetry WHERE author LIKE ? LIMIT 10 OFFSET 1000', ['王%']);
    sql.stop();
    var duck = Stopwatch();
    duck.start();
    var duckResultSet = connection!.query('SELECT * FROM poetry WHERE author LIKE \'王%\' LIMIT 10 OFFSET 1000').fetchAll();
    duck.stop();
    setState(() {
      _sqliteLikeResult = sqlResultSet.length;
      _sqliteLikeTime = sql.elapsedMilliseconds;
      _sqliteResult = sqlResultSet.rows;
      _duckdbLikeResult = duckResultSet.length;
      _duckdbLikeTime = duck.elapsedMilliseconds;
      _duckdbResult = duckResultSet;
    });
  }

  void _fts() {
    db!.execute("delete from poetry where id like 'mayan-test-%'");
    connection!.execute("delete from poetry where id like 'mayan-test-%'");
    var sql = Stopwatch();
    sql.start();
    final stmt = db!.prepare('INSERT INTO poetry (id,title,author,content) VALUES (?,?,?,?)');
    for (var i = 0; i < 1000; i++) {
      stmt.execute(['mayan-test-$i', 'title', 'author', 'content']);
    }
    stmt.dispose();
    sql.stop();
    var duck = Stopwatch();
    duck.start();
    final prepared = connection!.prepare("INSERT INTO poetry (id,title,author,content) VALUES (?,?,?,?)");
    for (var i = 0; i < 1000; i++) {
      prepared.bindParams(['mayan-test-$i', 'title', 'author', 'content']);
      prepared.execute();
    }
    duck.stop();
    setState(() {
      _sqliteLikeResult = 1000;
      _sqliteLikeTime = sql.elapsedMilliseconds;
      _duckdbLikeResult = 1000;
      _duckdbLikeTime = duck.elapsedMilliseconds;
    });
  }

  @override
  void initState() {
    getApplicationDocumentsDirectory().then((dir) {
      var base = dir.path;
      db ??= sqlite.sqlite3.open('$base/poetry.db');
      duckdb ??= duck.duckdb.open("$base/poetry.duck.db");
      connection ??= duck.duckdb.connect(duckdb!);
    });
    super.initState();
  }

  @override
  void dispose() {
    db!.dispose();
    connection!.dispose();
    duckdb!.dispose();
    super.dispose();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
        appBar: AppBar(backgroundColor: Theme.of(context).colorScheme.inversePrimary, title: Text(widget.title)),
        body: Column(children: [
          Row(mainAxisAlignment: MainAxisAlignment.spaceEvenly, children: [
            InkWell(onTap: _like, child: const Text("QUERY", style: TextStyle(fontSize: 50))),
            InkWell(onTap: _fts, child: const Text("WRITE", style: TextStyle(fontSize: 50)))
          ]),
          Container(height: 1, color: Colors.grey, margin: const EdgeInsets.symmetric(vertical: 5)),
          const Row(
              mainAxisAlignment: MainAxisAlignment.spaceEvenly,
              children: [Text("sqlite结果数"), Text("sqlite时长"), Text("duckdb结果数"), Text("duckdb时长")]),
          Row(mainAxisAlignment: MainAxisAlignment.spaceEvenly, children: [
            Text(_sqliteLikeResult.toString()),
            Text(_sqliteLikeTime.toString()),
            Text(_duckdbLikeResult.toString()),
            Text(_duckdbLikeTime.toString())
          ]),
          Row(children: [
            Expanded(child: Column(children: _sqliteResult?.map((r) => Text("${r[1]} - ${r[2]}")).toList()??[])),
            SizedBox(width: 60),
            Expanded(child: Column(children: _duckdbResult?.map((r) => Text("${r[1]} - ${r[2]}")).toList()??[]))
          ])
        ]));
  }
}