Flutter Big SQLite && Big ListView with low memory consumption

events.dart

import 'package:sqflite_common_ffi/sqflite_ffi.dart';

import '../../map/utils/geojson.dart';
import 'objs.dart';

class Event extends DbObject {
  String? asset;
  int? surface;
  int? regna;
  int? event;

  DbObject? parent;

  Event({point, this.asset}) : super() {
    if (point != null) {
      location = GeoJson(GeoJsonType.Point)..coordinates.add(point);
    }
  }

  static Event fromValues(Map<String, dynamic> values) {
    Event event = Event();
    if (values['geo'] != null) {
      event.location = GeoJson.parse(values['geo']);
    }
    event.id = values['id'];
    event.title = values['title'];
    event.regna = values['regna'];
    event.surface = values['surface'];
    event.event = values['event'];
    event.asset = values['asset'];
    event.date = DateTime.parse(values['date']);
    event.update = DateTime.parse(values['update']);
    event.agroneo = values['agroneo'];

    return event;
  }

  Map<String, dynamic> toMap() {
    Map<String, dynamic> data = {
      'title': title,
      'asset': asset,
      'date': date.toIso8601String(),
      'update': update.toIso8601String(),
    };
    if (id != null) {
      data['id'] = id;
    }
    if (surface != null) {
      data['surface'] = surface;
    }
    if (regna != null) {
      data['regna'] = regna;
    }
    if (event != null) {
      data['event'] = event;
    }
    if (location != null && location!.type != GeoJsonType.Empty && bounds != null) {
      data['geo'] = location.toString();
      data.addAll({'nwLat': bounds!.northWest.latitude, 'nwLon': bounds!.northWest.longitude, 'seLat': bounds!.southEast.latitude, 'seLon': bounds!.southEast.longitude});
    }
    return data;
  }
}

abstract mixin class EventDb {
  Future<Database> getDatabase();

  Future<Event> saveEvent(final Event event) async {
    Map<String, dynamic> data = event.toMap();

    event.id = await (await getDatabase()).insert("events", data, conflictAlgorithm: ConflictAlgorithm.replace);
    return event;
  }

  Future<void> saveEvents(final List<Event> events) async {
    Database database = await getDatabase();
    Batch batch = database.batch();
    for (Event event in events) {
      batch.insert("events", event.toMap(), conflictAlgorithm: ConflictAlgorithm.replace);
    }
    await batch.commit(noResult: true);
  }

  Future<List<Event>> getEvents({int? event, int? regna, int? surface, Event? afterEvent, Event? beforeEvent, int? limit, int? offset}) async {
    Database database = await getDatabase();
    List<String> filters = [];

    if (surface != null) {
      filters.add("surface = $surface");
    } else if (regna != null) {
      filters.add("regna = $regna");
    } else if (event != null) {
      filters.add("event = $event");
    } else {
      filters.add("event IS NULL");
    }
    String orderBy = 'date DESC, id DESC';
    if (afterEvent != null) {
      filters.add("(date < '${afterEvent.date.toIso8601String()}' OR (date = '${afterEvent.date.toIso8601String()}' AND id < ${afterEvent.id}))");
    } else if (beforeEvent != null) {
      filters.add("(date > '${beforeEvent.date.toIso8601String()}' OR (date = '${beforeEvent.date.toIso8601String()}' AND id > ${beforeEvent.id}))");
      orderBy = 'date ASC, id ASC';
    }
    String whereParent = filters.isNotEmpty ? "${filters.join(" AND ")} AND " : "";
    return [for (Map<String, dynamic> event in await database.query('events', where: "$whereParent archive IS NULL", orderBy: orderBy, limit: limit, offset: offset)) Event.fromValues(event)];
  }
}

list.dart

import 'package:agroneo/events/view.dart';
import 'package:agroneo/ux/loading.dart';
import 'package:flutter/material.dart';
import 'package:intl/intl.dart';

import '../data/sql/events.dart';
import '../utils/language.dart';
import '../ux/lists.dart';
import '../ux/platform/icons.dart';
import '../ux/utils/text.dart';

class EventsList extends InfiniteListView<Event> {
  const EventsList(super.dbConnector, {super.key, super.onReload, super.header, super.footer});

  static EventsList get({int? event, int? surface, int? regna, void Function()? onReload, Widget? header, Widget? footer}) {
    return EventsList(DataEventsList(event: event, surface: surface, regna: regna), onReload: onReload, header: header, footer: footer);
  }
}

class DataEventsList extends DataInfiniteList<Event> {
  final int? event;
  final int? surface;
  final int? regna;
  final double iconSize = 50;

  DataEventsList({this.event, this.surface, this.regna});

  @override
  Widget loading(BuildContext context, index) {
    return Container(
        padding: const EdgeInsets.symmetric(vertical: 15, horizontal: 10),
        decoration: getOddEvenBoxDecoration(context, 0),
        child: const SizedBox(
          height: 60,
          child: Loading(delay: 500),
        ));
  }

  @override
  Widget getView(BuildContext context, Event item, int index) {
    AppLocalizations local = Language.of(context)!;
    DateFormat dateFormat = DateFormat(null, local.localeName);
    return Container(
      padding: const EdgeInsets.symmetric(vertical: 15, horizontal: 10),
      decoration: getOddEvenBoxDecoration(context, index),
      child: InkWell(
        borderRadius: BorderRadius.circular(5),
        onTap: () {
          EventView.view(context, event: item).then((value) {});
        },
        child: Padding(
            padding: const EdgeInsets.symmetric(vertical: 5, horizontal: 10),
            child: Row(
              mainAxisSize: MainAxisSize.max,
              mainAxisAlignment: MainAxisAlignment.start,
              children: [
                if (item.asset != null) Image.network('${item.asset!}@${iconSize.toInt()}x${iconSize.toInt()}', height: iconSize),
                if (item.asset == null) Icon(BaseIcons.event, size: iconSize),
                const SizedBox(width: 8),
                Expanded(
                  child: Column(
                    crossAxisAlignment: CrossAxisAlignment.start,
                    mainAxisAlignment: MainAxisAlignment.start,
                    children: [
                      H3(item.title != null ? item.title! : local.events(1)),
                      H5(index.toString()),
                      Text("update: ${dateFormat.format(item.update)}"),
                      if (item.parent != null) Text(item.parent!.title!)
                    ],
                  ),
                ),
              ],
            )),
      ),
    );
  }

  @override
  Future<List<Event>> getItemsAfter(Event? after, {int? limit}) {
    if (after != null) {
      return db.getEvents(event: event, surface: surface, regna: regna, afterEvent: after, limit: limit);
    }
    return db.getEvents(event: event, surface: surface, regna: regna, limit: limit != null ? limit * 2 : null);
  }

  @override
  Future<List<Event>> getItemsBefore(Event before, {int? limit}) {
    return db.getEvents(event: event, surface: surface, regna: regna, beforeEvent: before, limit: limit);
  }

}

lists.dart

import 'dart:async';
import 'dart:math';

import 'package:agroneo/ux/platform/load.dart';
import 'package:agroneo/ux/ux.dart';

import '../data/sqlite.dart';

abstract class BaseListView extends StatelessWidget {
  final void Function()? onReload;

  static const BorderSide border = BorderSide(color: Colors.black12);

  const BaseListView({super.key, this.onReload});

  Color getOddEvenColor(BuildContext context, int index) {
    return index.isEven ? Colors.black.withOpacity(0.015) : Colors.white.withOpacity(0.015);
  }

  BoxDecoration getOddEvenBoxDecoration(BuildContext context, int index) {
    return BoxDecoration(color: getOddEvenColor(context, index), border: index == 0 ? const Border(top: border, bottom: border) : const Border(bottom: border));
  }
}

abstract class InfiniteListView<T> extends StatelessWidget {
  final Widget? header;
  final Widget? footer;

  final DataInfiniteList dbConnector;

  final void Function()? onReload;

  const InfiniteListView(this.dbConnector, {super.key, this.onReload, this.header, this.footer});

  @override
  Widget build(BuildContext context) {
    ListView list = ListView.builder(
      itemBuilder: (context, index) {
        if (header != null && index == 0) {
          return Padding(padding: const EdgeInsets.symmetric(vertical: 15, horizontal: 10), child: header);
        }

        return dbConnector.build(context, index - (header != null ? 1 : 0));
      },
    );

    if (footer == null) {
      return list;
    }
    return Column(
      children: [Expanded(child: list), if (footer != null) footer!],
    );
  }
}

abstract class DataInfiniteList<T> {
  final int limit = 10;
  final int purge = 20;

  final Db db = Db();

  int maxIndex = double.maxFinite.toInt();

  int lastIndex = -1;
  int loadingIndex = -1;

  final Map<int, FutureOr<T?>> items = {};

  Widget? build(BuildContext context, int index) {
    AxisDirection axis = index < lastIndex ? AxisDirection.up : AxisDirection.down;
    lastIndex = index;
    if (index > maxIndex || (loadingIndex >= 0 && index > loadingIndex)) {
      return null;
    }

    if ((index == 0 && items[index + limit] == null) ||
        (axis == AxisDirection.down && items[index + limit] == null) ||
        (axis == AxisDirection.up && items[index - limit] == null)) {
      _getItems(axis, index: index - (axis == AxisDirection.up ? 1 : 0));
    }
    FutureOr<T?> item = items[index];
    item = item ?? _getItems(axis, index: index - (axis == AxisDirection.up ? 1 : 0));
    if (item is Future<T?>) {
      return FutureBuilder(
          future: item,
          builder: (context, snapshot) {
            if (snapshot.connectionState != ConnectionState.done) {
              loadingIndex = index;
              return loading(context, index);
            }
            loadingIndex = -1;
            if (snapshot.connectionState == ConnectionState.done && snapshot.data == null) {
              return const SizedBox.shrink();
            }
            if (snapshot.data != null) {
              return getView(context, snapshot.data as T, index);
            }
            maxIndex = index - 1;
            return const SizedBox.shrink();
          });
    }

    return getView(context, item as T, index);
  }

  Widget loading(BuildContext context, int index) {
    return Ux.loading(context, size: 30, delay: 100);
  }

  Widget getView(BuildContext context, T item, int index);

  Future<List<T>> dbStamp = Future(() => []);

  Future<List<T>> getItemsAfter(T? after, {int? limit});

  Future<List<T>> _getItemsAfter(T? after, {int? limit}) async {
    await dbStamp;
    dbStamp = getItemsAfter(after, limit: limit);
    return await dbStamp;
  }

  Future<List<T>> getItemsBefore(T before, {int? limit});

  Future<List<T>> _getItemsBefore(T before, {int? limit}) async {
    await dbStamp;
    dbStamp = getItemsBefore(before, limit: limit);
    return await dbStamp;
  }

  FutureOr<T?> _getItems(AxisDirection axis, {int index = 0}) async {
    List<int> keys = items.keys.toList()..sort();

    int indexAfter = keys.isEmpty ? 0 : keys.last;
    int limitAfter = axis == AxisDirection.down ? limit * 2 : limit;
    FutureOr<T?>? last = keys.isEmpty ? null : items[indexAfter];

    int indexBefore = keys.isEmpty ? 0 : keys.first;
    int limitBefore = axis == AxisDirection.up ? limit * 2 : limit;
    FutureOr<T?>? first = keys.isEmpty ? null : items[indexBefore];

    Future<List<T>> afterItems = Future<List<T>>(() async {
      return _getItemsAfter(await last, limit: limitAfter);
    });
    if (last != null) {
      indexAfter++;
    }
    for (int i = 0; i < limitAfter; i++) {
      if (items[indexAfter + i] == null) {
        items[indexAfter + i] = Future<T?>(() async {
          List<T> items = (await afterItems);
          return i >= items.length ? null : items[i];
        });
      }
    }

    afterItems.then((afterItems) {
      for (T item in afterItems) {
        items[indexAfter++] = item;
      }
      if (afterItems.length < limit) {
        maxIndex = maxIndex = min(indexAfter + afterItems.length, maxIndex);
      }
    });

    if (indexBefore > 0 && first != null) {
      Future<List<T>> beforeItems = Future<List<T>>(() async {
        return _getItemsBefore((await first) as T, limit: limitBefore);
      });

      for (int i = 0; i < limitBefore; i++) {
        if (indexBefore - i >= 0 && items[indexBefore - i] == null) {
          items[indexBefore - i] = Future<T?>(() async {
            List<T> items = (await beforeItems);
            return i <= 0 || i - 1 >= items.length ? null : items[i - 1];
          });
        }
      }

      beforeItems.then((afterItems) {
        for (T item in afterItems) {
          items[--indexBefore] = item;
        }
      });
    }
    items.removeWhere((key, value) => key > index + purge || key < index - purge);

    return items[index];
  }

  static const BorderSide border = BorderSide(color: Colors.black12);

  Color getOddEvenColor(BuildContext context, int index) {
    return index.isEven ? Colors.black.withOpacity(0.015) : Colors.white.withOpacity(0.015);
  }

  BoxDecoration getOddEvenBoxDecoration(BuildContext context, int index) {
    return BoxDecoration(color: getOddEvenColor(context, index), border: index == 0 ? const Border(top: border, bottom: border) : const Border(bottom: border));
  }
}

de la balle !