flutter-expense-tracker/lib/db.dart

// https://docs.flutter.dev/cookbook/persistence/sqlite

// SQLite
import 'dart:io';
import 'dart:async';
import 'package:flutter/material.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';

// Local
import "/globals.dart";
import '/models/expense.dart';
import '/models/income.dart';
import '/models/asset.dart';
import '/models/tracked_item.dart';

// Leaned on this example:
//   https://learnflutterwithme.com/sqlite
class DatabaseHelper {
  DatabaseHelper._privateConstructor();
  static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

  static Database? _db;
  Future<Database> get db async => _db ??= await _initDatabase();

  Future<Database> _initDatabase() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, "ExpenseTracker.sqlite.db");
    return await openDatabase(
      path,
      version: 2,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
    );
  }

  Future _onCreate(Database db, int version) async {
    debugPrint(
      "_onCreate(): version=$version",
    );
    _createExpense(db);
    _createIncome(db);
    _createAsset(db);
  }

  Future _onUpgrade(Database db, int previousVersion, int newVersion) async {
    debugPrint(
      "_onUpgrade(): previousVersion=$previousVersion, newVersion=$newVersion",
    );
    // Added in DB version 2.
    if (previousVersion < 2 && newVersion >= 2) {
      _createIncome(db);
      _createAsset(db);
    }
  }

  void _createExpense(Database db) async {
    await db.execute("""
      CREATE TABLE expense
        ( id INTEGER PRIMARY KEY
        , name TEXT NOT NULL UNIQUE
        , cost DOUBLE NOT NULL
        , frequency TEXT NOT NULL
        , description TEXT
      )""");
  }

  void _createIncome(Database db) async {
    await db.execute("""
      CREATE TABLE income
        ( id INTEGER PRIMARY KEY
        , name TEXT NOT NULL UNIQUE
        , revenue DOUBLE NOT NULL
        , frequency TEXT NOT NULL
        , description TEXT
      )""");
  }

  void _createAsset(Database db) async {
    await db.execute("""
      CREATE TABLE asset
        ( id INTEGER PRIMARY KEY
        , name TEXT NOT NULL UNIQUE
        , amount DOUBLE NOT NULL
        , description TEXT
      )""");
  }

  /// Expense Section
  ///
  Future<List<Expense>> getExpenses() async {
    if (testing) debugPrint("getExpenses(): Accessing db.");
    Database db = await instance.db;

    if (testing) debugPrint("getExpenses(): Querying table.");
    var expenses = await db.query("expense", orderBy: "name");

    if (testing) debugPrint("getExpenses(): Putting into object list.");
    List<Expense> expenseList = expenses.isNotEmpty
        ? expenses.map((c) => Expense.fromMap(c)).toList()
        : [];

    if (testing) debugPrint("getExpenses(): Returning!");
    return expenseList;
  }

  Future<int> addExpense(TrackedItem expense) async {
    Database db = await instance.db;
    return await db.insert(
      "expense",
      expense.toMap(),
    );
  }

  Future<int> removeExpense(int id) async {
    Database db = await instance.db;
    return await db.delete(
      "expense",
      where: "id = ?",
      whereArgs: [id],
    );
  }

  Future<int> updateExpense(TrackedItem expense) async {
    Database db = await instance.db;
    return await db.update(
      "expense",
      expense.toMap(),
      where: "id = ?",
      whereArgs: [expense.id],
    );
  }

  Future<bool> checkExpenseNameExists(String name) async {
    Database db = await instance.db;
    var expenses = await db.query(
      "expense",
      where: "name = ?",
      whereArgs: [name],
    );
    return expenses.isNotEmpty;
  }

  ///

  /// Income Section
  ///
  Future<List<Income>> getIncomes() async {
    if (testing) debugPrint("getIncomes(): Accessing db.");
    Database db = await instance.db;

    if (testing) debugPrint("getIncomes(): Querying table.");
    var incomes = await db.query("income", orderBy: "name");

    if (testing) debugPrint("getIncomes(): Putting into object list.");
    List<Income> incomeList = incomes.isNotEmpty
        ? incomes.map((c) => Income.fromMap(c)).toList()
        : [];

    if (testing) debugPrint("getIncomes(): Returning!");
    return incomeList;
  }

  Future<int> addIncome(TrackedItem income) async {
    Database db = await instance.db;
    return await db.insert(
      "income",
      income.toMap(),
    );
  }

  Future<int> removeIncome(int id) async {
    Database db = await instance.db;
    return await db.delete(
      "income",
      where: "id = ?",
      whereArgs: [id],
    );
  }

  Future<int> updateIncome(TrackedItem income) async {
    Database db = await instance.db;
    return await db.update(
      "income",
      income.toMap(),
      where: "id = ?",
      whereArgs: [income.id],
    );
  }

  Future<bool> checkIncomeNameExists(String name) async {
    Database db = await instance.db;
    var incomes = await db.query(
      "income",
      where: "name = ?",
      whereArgs: [name],
    );
    return incomes.isNotEmpty;
  }

  ///
  /// Liquid Asset Section
  Future<List<Asset>> getAssets() async {
    if (testing) debugPrint("getAssets(): Accessing db.");
    Database db = await instance.db;

    if (testing) debugPrint("getAssets(): Querying table.");
    var assets = await db.query("asset", orderBy: "name");

    if (testing) debugPrint("getAssets(): Putting into object list.");
    List<Asset> assetList =
        assets.isNotEmpty ? assets.map((c) => Asset.fromMap(c)).toList() : [];

    if (testing) debugPrint("getAssets(): Returning!");
    return assetList;
  }

  Future<int> addAsset(TrackedItem asset) async {
    Database db = await instance.db;
    return await db.insert(
      "asset",
      asset.toMap(),
    );
  }

  Future<int> removeAsset(int id) async {
    Database db = await instance.db;
    return await db.delete(
      "asset",
      where: "id = ?",
      whereArgs: [id],
    );
  }

  Future<int> updateAsset(TrackedItem asset) async {
    Database db = await instance.db;
    return await db.update(
      "asset",
      asset.toMap(),
      where: "id = ?",
      whereArgs: [asset.id],
    );
  }

  Future<bool> checkAssetNameExists(String name) async {
    Database db = await instance.db;
    var assets = await db.query(
      "asset",
      where: "name = ?",
      whereArgs: [name],
    );
    return assets.isNotEmpty;
  }

  ///
}