Ok, I understand that Sqflite onUpgrade is only fire if there is older version. But what will happen if user newly installed and only call onCreate. Because all the db changes are in onUpgrade function.

For example, I have installed the app from app store. And then want to test the app. So that I run the app with debug mode and app installed from play store was uninstalled and reinstall new version because of incompatible.

When I open the app, only onCreate is called but does not trigger onUpgrade.

May I know how can I solve this issue?

import 'dart:async';
import 'dart:io';

import 'package:flutter/foundation.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';

const favouriteTable = 'Favourite';
const songTable = 'Song';
const ebookTable = 'Ebook';
const chantingCatalogTable = 'ChantingCatalog';
const chantingTable = 'Chanting';

class DatabaseProvider {
  static const _databaseName = "Thitsarparami.db";
  static const _databaseVersion = 3;
  static final DatabaseProvider dbProvider = DatabaseProvider();

  static Database? _database;

  Future<Database> get database async {
    return _database ??= await createDatabase();
  }

  Future<Database> createDatabase() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();

    //Thitsarparami is our database instance name
    String path = join(documentsDirectory.path, _databaseName);

    var database = await openDatabase(path,
        version: _databaseVersion, onCreate: initDB, onUpgrade: _onUpgrade);
    return database;
  }

  Future<void> _onUpgrade(
      Database database, int oldVersion, int newVersion) async {
    if (kDebugMode) {
      print("DBHelper: _onUpgrade called");
    }
    for (var version = oldVersion   1; version <= newVersion; version  ) {
      switch (version) {
        case 1:
          {
            break;
          }
        case 2:
          {
            initDB(database, _databaseVersion);
            break;
          }
        case 3:
          {
            await database.execute(
                "ALTER TABLE $chantingTable ADD COLUMN duration INTEGER");
            break;
          }
      }
    }
  }

  void initDB(Database database, int version) async {
    const idType = 'INTEGER PRIMARY KEY AUTOINCREMENT';
    const uuidType = 'TEXT PRIMARY KEY';
    //const textNotNullType = 'TEXT NOT NULL';
    const textType = 'TEXT';
    const integerType = 'INTEGER';

    String sql = "CREATE TABLE $chantingCatalogTable ("
        "id $uuidType, "
        "title $textType, "
        "sort_order $integerType, "
        "active $integerType, "
        "created_at $integerType, "
        "updated_at $integerType"
        ")";

    await database.execute("DROP TABLE IF EXISTS $chantingCatalogTable");
    await database.execute(sql);

    sql = "CREATE TABLE $chantingTable ("
        "id $uuidType, "
        "chanting_catalog_id $textType, "
        "title $textType, "
        "text $textType, "
        "file_name $textType, "
        "sort_order $integerType, "
        "active $integerType, "
        "created_at $integerType, "
        "updated_at $integerType, "
        "FOREIGN KEY (chanting_catalog_id) REFERENCES $chantingCatalogTable (id)"
        ")";

    await database.execute("DROP TABLE IF EXISTS $chantingTable");
    await database.execute(sql);

    sql = "CREATE TABLE $favouriteTable ("
        "id $idType, "
        "name $textType NOT NULL, "
        "is_default $integerType, "
        "UNIQUE(name)"
        ")";

    await database.execute("DROP TABLE IF EXISTS $favouriteTable");
    await database.execute(sql);

    sql = "CREATE TABLE $songTable ("
        "id $textType NOT NULL, "
        "favouriteId $integerType NOT NULL, "
        "task_id $textType, "
        "album $textType, "
        "title $textType, "
        "artist $textType, "
        "artUrl $textType, "
        "audioUrl $textType, "
        "duration $integerType, "
        "is_favourite $integerType, "
        "is_downloaded $integerType, "
        "sort_order $integerType, "
        "PRIMARY KEY(id, favouriteId)"
        ")";

    await database.execute("DROP TABLE IF EXISTS $songTable");
    await database.execute(sql);

    try {
      sql = "INSERT INTO $favouriteTable (name, is_default)"
          " VALUES (?,?)";

      await database.execute(sql, ["Downloaded", 1]);
    } catch (e) {
      if (kDebugMode) {
        print(e);
      }
    }

    sql = "CREATE TABLE $ebookTable ("
        "id $uuidType, "
        "taskId $integerType, "
        "title $textType, "
        "url $textType, "
        "thumbnail $textType, "
        "monk_name $textType, "
        "monk_image_url $textType"
        ")";

    await database.execute("DROP TABLE IF EXISTS $ebookTable");
    await database.execute(sql);
  }
}

CodePudding user response：

You are not supposed to fire onUpgrade when you install a brand new app.

onUpgrade handles if the user has an existing older version of the database.

onCreate handles if the user does not have any older version of the database.

Therefor you have to update your create statements too whenever you make some changes to your database:

sql = "CREATE TABLE $chantingTable ("
        "id $uuidType, "    
        ...
        "updated_at $integerType, "
        "duration $integerType" // new line
        "FOREIGN KEY (chanting_catalog_id) REFERENCES $chantingCatalogTable (id)"
        ")";

Also... Although your onUpgrade might work like that, might be prettier if you try to build the onUpgrade method something like this:

(from == oldVersion)

          if (from == 1) {
          }
          if (from <= 2) {
          }
          if (from <= 3) {
          }
          if (from <= 4) {
          }
          if (from <= 5) {
          }
          if (from <= 6) {
          }
          if (from <= 7) {
          }