Db and Table always re-created when re-run the Apps

[rrifafauzikomara]

I have a case, when I first run the apps (before open the splash screen), the DB, table it's created. In the splash screen, I run a query to insert some data into the table that already created before. And then I download the DB and open it in DB Browser for SQLite and ya, the DB, table, and data are successfully created and inserted.

But.... after the apps go to home screen (from splash screen to home screen). I try to rerun the apps (it run again from splash screen). But I see in the log, that the DB and table are created again. Because of that, I get the error UNIQUE Constrait because the data is already inserted but the system created again and inserted again. Fyi in the splash screen, I have a logic to check if the data is already inserted it will save the log to DB. For example:

Log Id
1.0.0 --> insert data A
1.2.0 --> insert data B
1.3.0 --> insert data C
1.4.0 --> insert data D

I check on the table, the last log is 1.4.0 it means when I open the splash screen, it's not inserted query again. But when the API sent a new log 1.5.0, it will insert new data. The problem is because every re-run creates DB and table but actually the data already inserted. It's giving me an error UNIQUE Constraint. Why it's recreated again?

I try to generate .g.dart again and again, flutter clean and invalidate and cache. But still error.

database_module.dart

import 'package:core/db/table/appversion/lst_app_version_orion.dart';
import 'package:core/db/table/lead/table.dart';
import 'package:core/db/table/proposal/lst_bak_ulink.dart';
import 'package:core/db/table/proposal/lst_biaya_ulink.dart';
import 'package:core/db/table/proposal/lst_bisnis.dart';
import 'package:core/db/table/proposal/lst_bisnis_rider.dart';
import 'package:core/db/table/proposal/lst_det_bisnis.dart';
import 'package:core/db/table/proposal/lst_foundation_fee.dart';
import 'package:core/db/table/proposal/lst_group_bisnis.dart';
import 'package:core/db/table/proposal/lst_insurance_benefits.dart';
import 'package:core/db/table/proposal/lst_invest_description.dart';
import 'package:core/db/table/proposal/lst_jenis_invest.dart';
import 'package:core/db/table/proposal/lst_kurs.dart';
import 'package:core/db/table/proposal/lst_orion_field.dart';
import 'package:core/db/table/proposal/lst_orion_field_det.dart';
import 'package:core/db/table/proposal/lst_pay_mode.dart';
import 'package:core/db/table/proposal/lst_prod_group_orion.dart';
import 'package:core/db/table/proposal/lst_prod_group_orion_det.dart';
import 'package:core/db/table/proposal/lst_prodset.dart';
import 'package:core/db/table/proposal/lst_prodset_calc.dart';
import 'package:core/db/table/proposal/lst_prodset_form.dart';
import 'package:core/db/table/proposal/lst_prodset_fund.dart';
import 'package:core/db/table/proposal/lst_prodset_fund_rate.dart';
import 'package:core/db/table/proposal/lst_prodset_rider_valid.dart';
import 'package:core/db/table/proposal/lst_product_description.dart';
import 'package:core/db/table/proposal/lst_product_exclusions.dart';
import 'package:core/db/table/proposal/lst_product_exclusions_map.dart';
import 'package:core/db/table/proposal/lst_prop_notes.dart';
import 'package:core/db/table/proposal/lst_prop_notes_map.dart';
import 'package:core/db/table/proposal/lst_relation.dart';
import 'package:core/db/table/proposal/lst_rider.dart';
import 'package:core/db/table/proposal/lst_rider_benefits.dart';
import 'package:core/db/table/proposal/lst_rider_comb.dart';
import 'package:core/db/table/proposal/lst_rider_dependent.dart';
import 'package:core/db/table/proposal/lst_rider_flag_class.dart';
import 'package:core/db/table/proposal/lst_rider_flag_unit.dart';
import 'package:core/db/table/proposal/lst_rider_flag_up.dart';
import 'package:core/db/table/proposal/lst_rider_hardcode.dart';
import 'package:core/db/table/proposal/lst_rider_percentage_up.dart';
import 'package:core/db/table/proposal/lst_rider_plan_benefits.dart';
import 'package:core/db/table/proposal/lst_rider_plan_benefits_map.dart';
import 'package:core/db/table/proposal/lst_table.dart';
import 'package:core/db/table/proposal/lst_table_factor.dart';
import 'package:core/db/table/proposal/lst_table_fmax.dart';
import 'package:core/db/table/proposal/lst_table_new.dart';
import 'package:core/db/table/proposal/lst_tipe_medis.dart';
import 'package:core/db/table/proposal/lst_transaksi.dart';
import 'package:core/db/table/proposal/mst_medis_new.dart';
import 'package:core/db/table/proposaldraft/table.dart';
import 'package:core/db/table/spaj/table.dart';
import 'package:moor_flutter/moor_flutter.dart';
import 'package:proposal/data/datasources/local/dao/proposal_dao.dart';
import 'package:proposal/data/datasources/local/dao/proposal_draft_dao.dart';
import 'package:splash/data/datasources/local/dao/splash_dao.dart';
import 'package:spaj/data/datasources/local/dao/spaj_draft_dao.dart';
import 'package:spaj/data/datasources/local/dao/spaj_dao.dart';

part 'database_module.g.dart';

@UseMoor(tables: [
  LstAppVersionOrion,

  /// table from proposal
  LstBakUlink,
  LstBisnis,
  LstDetBisnis,
  LstJenisInvest,
  LstOrionField,
  LstOrionFieldDet,
  LstProdset,
  LstProdsetCalc,
  LstProdsetForm,
  LstProdsetFund,
  LstProdsetFundRate,
  LstProdsetRiderValid,
  LstRider,
  LstRiderComb,
  LstRiderDependent,
  LstKurs,
  LstProdGroupOrionDet,
  LstProdGroupOrion,
  LstPayMode,
  LstBisnisRider,
  LstTableFmax,
  LstRelation,
  LstRiderFlagClass,
  LstRiderFlagUnit,
  LstRiderFlagUp,
  LstRiderHardcode,
  LstRiderPercentageUp,
  LstInvestDescription,
  MstMedisNew,
  LstTipeMedis,
  LstInsuranceBenefits,
  LstPropNotesMap,
  LstTable,
  LstPropNotes,
  LstGroupBisnis,
  LstProductDescription,
  LstProductExclusions,
  LstProductExclusionsMap,
  LstRiderBenefits,
  LstTableFactor,
  LstTransaksi,
  LstBiayaUlink,
  LstRiderPlanBenefits,
  LstFoundationFee,
  LstFoundationFee,
  LstTableNew,
  LstRiderPlanBenefitsMap,

  /// table from proposal draft
  LstDraftPropProduct,
  LstDraftPropDataPersonal,
  LstDraftPropFund,
  LstDraftPropParticipant,
  LstDraftPropTopup,
  LstDraftPropRider,
  LstDraftPropParticipantMap,

  /// table from spaj
  LstAgama,
  LstBank,
  LstBankPusat,
  LstHighRiskActivities,
  LstJnUsaha,
  LstKlasifikasiNew,
  LstMasterProvinsi,
  LstNegara,
  LstPekerjaan,
  LstStatus,

  /// table from lead
  LstLeadActivityType,
  LstLeadSegment,
  LstLeadsSegmentBank,
  LstLeadsSource,
  MstLeads,
  MstLeadsActivity
], daos: [
  SplashDao,
  ProposalDao,
  ProposalDraftDao,
  SpajDao,
  SpajDraftDao,
])
class AppDatabase extends _$AppDatabase {
  AppDatabase()
      : super(FlutterQueryExecutor.inDatabaseFolder(
            path: 'db_orion', logStatements: true));

  @override
  int get schemaVersion => 1;
}

Flutter Doctor -V

[✓] Flutter (Channel stable, 1.22.5, on macOS 11.2.3 20D91 darwin-x64, locale
    en-US)
    • Flutter version 1.22.5 at /Users/rrifafauzikomara/Documents/flutter
    • Framework revision 7891006299 (4 months ago), 2020-12-10 11:54:40 -0800
    • Engine revision ae90085a84
    • Dart version 2.10.4

 
[✓] Android toolchain - develop for Android devices (Android SDK version 30.0.2)
    • Android SDK at /Users/rrifafauzikomara/Library/Android/sdk
    • Platform android-30, build-tools 30.0.2
    • Java binary at: /Applications/Android
      Studio.app/Contents/jre/jdk/Contents/Home/bin/java
    • Java version OpenJDK Runtime Environment (build
      1.8.0_242-release-1644-b3-6915495)
    • All Android licenses accepted.

[✓] Xcode - develop for iOS and macOS (Xcode 12.4)
    • Xcode at /Applications/Xcode.app/Contents/Developer
    • Xcode 12.4, Build version 12D4e
    • CocoaPods version 1.10.1

[✓] Android Studio (version 4.1)
    • Android Studio at /Applications/Android Studio.app/Contents
    • Flutter plugin installed
    • Dart plugin version 201.9317
    • Java version OpenJDK Runtime Environment (build
      1.8.0_242-release-1644-b3-6915495)

[✓] Connected device (1 available)
    • iPhone R Rifa Fauzi (mobile) • 00008030-001825A82282402E • ios • iOS
      14.0.1

Libraries

moor_flutter: ^3.1.0
build_runner:
moor_generator: ^3.3.1

Query

flutter pub run build_runner build --delete-conflicting-outputs

UPDATE

When I delete the tables (from coding) LstLeadActivityType, LstLeadSegment, LstLeadsSegmentBank, LstLeadsSource, MstLeads, MstLeadsActivity it's working. It's table in moor have a limit? or maybe my code is wrong? Below is that table before I deleted.

LstLeadActivityType

import 'package:moor/moor.dart';

class LstLeadActivityType extends Table {
  @override
  String get tableName => 'LST_LEADS_ACTIVITY_TYPE';

  IntColumn get lsatId => integer().named('LSAT_ID')();

  IntColumn get lsatSubId => integer().named('LSAT_SUB_ID')();

  TextColumn get lsatActivityType =>
      text().named('LSAT_ACTIVITY_TYPE').nullable()();

  TextColumn get lsatSubActivity =>
      text().named('LSAT_SUB_ACTIVITY').nullable()();

  IntColumn get lsatActive => integer().named('LSAT_ACTIVE').nullable()();

  @override
  Set<Column> get primaryKey => {lsatId, lsatSubId};
}

LstLeadSegment

import 'package:moor/moor.dart';

class LstLeadSegment extends Table {
  @override
  String get tableName => 'LST_LEADS_SEGMENT';

  IntColumn get lsldSegId => integer().named('LSLD_SEG_ID')();

  TextColumn get lsldSegment => text().named('LSLD_SEGMENT').nullable()();

  TextColumn get lsldSubSegment =>
      text().named('LSLD_SUB_SEGMENT').nullable()();

  IntColumn get lsldActive => integer().named('LSLD_ACTIVE').nullable()();

  @override
  Set<Column> get primaryKey => {lsldSegId};
}

LstLeadsSegmentBank

import 'package:moor/moor.dart';

class LstLeadsSegmentBank extends Table {
  @override
  String get tableName => 'LST_LEADS_SEGMENT_BANK';

  IntColumn get lsldSegBankId => integer().named('LSLD_SEG_BANK_ID')();

  IntColumn get lsldSegId => integer().named('LSLD_SEG_ID')();

  IntColumn get jnBank => integer().named('JN_BANK')();

  IntColumn get lslbSegBankACtive =>
      integer().named('LSLD_SEG_BANK_ACTIVE').nullable()();

  @override
  Set<Column> get primaryKey => {lsldSegBankId};
}

LstLeadsSource

import 'package:moor/moor.dart';

class LstLeadsSource extends Table {
  @override
  String get tableName => 'LST_LEADS_SOURCE';

  IntColumn get lsldSrcId => integer().named('LSLD_SRC_ID')();

  TextColumn get lsldSource => text().named('LSLD_SOURCE').nullable()();

  TextColumn get lsldSubSource => text().named('LSLD_SUB_SOURCE').nullable()();

  IntColumn get lsldActive => integer().named('LSLD_ACTIVE').nullable()();

  @override
  Set<Column> get primaryKey => {lsldSrcId};
}

MstLeads

import 'package:moor/moor.dart';

class MstLeads extends Table {
  @override
  String get tableName => 'MST_LEADS';

  IntColumn get msldId => integer().named('MSLD_ID')();

  IntColumn get lsldSrcId => integer().named('LSLD_SRC_ID').nullable()();

  TextColumn get msldName => text().named('MSLD_NAME').nullable()();

  TextColumn get msldDob => text().named('MSLD_DOB').nullable()();

  IntColumn get msldAge => integer().named('MSLD_AGE').nullable()();

  IntColumn get msldGender => integer().named('MSLD_GENDER').nullable()();

  TextColumn get msldEmail => text().named('MSLD_EMAIL').nullable()();

  TextColumn get msldNoHp => text().named('MSLD_NO_HP').nullable()();

  TextColumn get msagId => text().named('MSAG_ID').nullable()();

  IntColumn get jnBank => integer().named('JN_BANK').nullable()();

  TextColumn get lcbNoFirst => text().named('LCB_NO_FIRST').nullable()();

  TextColumn get lcbNo => text().named('LCB_NO').nullable()();

  IntColumn get lsldSegBankId =>
      integer().named('LSLD_SEG_BANK_ID').nullable()();

  TextColumn get msldReferralCode =>
      text().named('MSLD_REFERRAL_CODE').nullable()();

  TextColumn get msldReferralOther =>
      text().named('MSLD_REFERRAL_OTHER').nullable()();

  IntColumn get lsbsId => integer().named('LSBS_ID').nullable()();

  IntColumn get lsdbsNumber => integer().named('LSDBS_NUMBER').nullable()();

  TextColumn get lkuId => text().named('LKU_ID').nullable()();

  IntColumn get msldApe => integer().named('MSLD_APE').nullable()();

  IntColumn get msldSync => integer().named('MSLD_SYNC').nullable()();

  IntColumn get msldActive => integer().named('MSLD_ACTIVE').nullable()();

  TextColumn get msldCreatedOn => text().named('MSLD_CREATED_ON').nullable()();

  TextColumn get msldTempId => text().named('msld_temp_id').nullable()();

  TextColumn get msldModifiedOn =>
      text().named('MSLD_MODIFIED_ON').nullable()();

  @override
  Set<Column> get primaryKey => {msldId};
}

MstLeadsActivity

import 'package:moor/moor.dart';

class MstLeadsActivity extends Table {
  @override
  String get tableName => 'MST_LEADS_ACTIVITY';

  IntColumn get msactId => integer().named('MSACT_ID')();

  IntColumn get msldId => integer().named('MSLD_ID').nullable()();

  IntColumn get lsatId => integer().named('LSAT_ID').nullable()();

  IntColumn get lsatSubId => integer().named('LSAT_ID').nullable()();

  TextColumn get msagId => text().named('MSAG_ID').nullable()();

  TextColumn get msactDate => text().named('MSACT_DATE').nullable()();

  TextColumn get msactRemarks => text().named('MSACT_REMARKS').nullable()();

  IntColumn get msactSync => integer().named('MSACT_SYNC').nullable()();

  IntColumn get msactActive => integer().named('MSACT_ACTIVE').nullable()();

  TextColumn get msldTempId => text().named('msld_temp_id')();

  TextColumn get msactCreatedOn =>
      text().named('MSACT_CREATED_ON').nullable()();

  TextColumn get msactModifiedOn =>
      text().named('MSACT_MODIFIED_ON').nullable()();

  @override
  Set<Column> get primaryKey => {msactId};
}

[simolus3]

Moor shouldn't re-create the database if it already exists. Can you verify that the database has the correct user version set? You can run pragma user_version on the database to check, it should be 1.

[rrifafauzikomara]

Sorry, after I re-coding to create a table for leads it's work. I think it's because of a typo.

[rrifafauzikomara]

It's fixed after I re-coding to create a table for leads. I think it's because of a typo.