Multiple issues with Migration

[vaishnavmhetre]

// [STACKTRACE]

I/flutter (15575): init() -> tableName:accounts
I/flutter (15575): init() -> tableName:transactions
I/flutter (15575): init() -> tableName:transaction_metas
I/1.gpu   (15575): type=1400 audit(0.0:237124): avc: denied { ioctl } for path="/dev/kgsl-3d0" dev="tmpfs" ino=14787 ioctlcmd=945 scontext=u:r:untrusted_app:s0:c42,c257,c512,c768 tcontext=u:object_r:device:s0 tclass=chr_file permissive=1
I/flutter (15575): SQFENTITIY: alterTableQuery => [ALTER TABLE transactions ADD COLUMN SourceAccount integer, CREATE INDEX IF NOT EXISTS IDXAccountSourceAccount ON transactions (SourceAccount ASC), ALTER TABLE transactions ADD COLUMN EffectiveSourceAccount integer, CREATE INDEX IF NOT EXISTS IDXAccountEffectiveSourceAccount ON transactions (EffectiveSourceAccount ASC), ALTER TABLE transactions ADD COLUMN TransferDestinationAccount integer, CREATE INDEX IF NOT EXISTS IDXAccountTransferDestinationAccount ON transactions (TransferDestinationAccount ASC), ALTER TABLE transactions ADD COLUMN TransferEffectiveDestinationAccount integer, CREATE INDEX IF NOT EXISTS IDXAccountTransferEffectiveDestinationAccount ON transactions (TransferEffectiveDestinationAccount ASC), ALTER TABLE transactions ADD COLUMN type text]
I/flutter (15575): SQFENTITIY: alterTableQuery => [ALTER TABLE transactions ADD COLUMN SourceAccount integer, CREATE INDEX IF NOT EXISTS IDXAccountSourceAccount ON transactions (SourceAccount ASC), ALTER TABLE transactions ADD COLUMN EffectiveSourceAccount integer, CREATE INDEX IF NOT EXISTS IDXAccountEffectiveSourceAccount ON transactions (EffectiveSourceAccount ASC), ALTER TABLE transactions ADD COLUMN TransferDestinationAccount integer, CREATE INDEX IF NOT EXISTS IDXAccountTransferDestinationAccount ON transactions (TransferDestinationAccount ASC), ALTER TABLE transactions ADD COLUMN TransferEffectiveDestinationAccount integer, CREATE INDEX IF NOT EXISTS IDXAccountTransferEffectiveDestinationAccount ON transactions (TransferEffectiveDestinationAccount ASC), ALTER TABLE transactions ADD COLUMN type text]
I/flutter (15575): SQFENTITIY: Table named [transactions] was initialized successfully (Added new columns)
I/flutter (15575): SQFENTITIY: alterTableQuery => [ALTER TABLE transactions ADD COLUMN SourceAccount integer, CREATE INDEX IF NOT EXISTS IDXAccountSourceAccount ON transactions (SourceAccount ASC), ALTER TABLE transactions ADD COLUMN EffectiveSourceAccount integer, CREATE INDEX IF NOT EXISTS IDXAccountEffectiveSourceAccount ON transactions (EffectiveSourceAccount ASC), ALTER TABLE transactions ADD COLUMN TransferDestinationAccount integer, CREATE INDEX IF NOT EXISTS IDXAccountTransferDestinationAccount ON transactions (TransferDestinationAccount ASC), ALTER TABLE transactions ADD COLUMN TransferEffectiveDestinationAccount integer, CREATE INDEX IF NOT EXISTS IDXAccountTransferEffectiveDestinationAccount ON transactions (TransferEffectiveDestinationAccount ASC), ALTER TABLE transactions ADD COLUMN type text]
I/flutter (15575): SQFENTITIY: Table named [transactions] was initialized successfully (Added new columns)
E/SQLiteLog(15575): (1) duplicate column name: SourceAccount
I/flutter (15575): Warning database has been locked for 0:00:10.000000. Make sure you always use the transaction object for database operations during a transaction
I/flutter (15575): Warning database has been locked for 0:00:10.000000. Make sure you always use the transaction object for database operations during a transaction
I/flutter (15575): SQFENTITY ERROR while run execSQLList:
I/flutter (15575): DatabaseException(duplicate column name: SourceAccount (code 1 SQLITE_ERROR): , while compiling: ALTER TABLE transactions ADD COLUMN SourceAccount integer) sql 'ALTER TABLE transactions ADD COLUMN SourceAccount integer' args []}
E/flutter (15575): [ERROR:flutter/lib/ui/ui_dart_state.cc(157)] Unhandled Exception: Exception: SQFENTITIY: DATABASE INITIALIZE ERROR The primary key name '_id' for table named [accounts] must be in [id]
E/flutter (15575): #0      SqfEntityModelProvider.initializeDB (package:sqfentity/sqfentity.dart:585:13)
E/flutter (15575): <asynchronous suspension>
E/flutter (15575): #1      SqfEntityProvider.db (package:sqfentity/sqfentity.dart:63:22)
E/flutter (15575): <asynchronous suspension>
E/flutter (15575): #2      SqfEntityProvider.execDataTable (package:sqfentity/sqfentity.dart:212:36)
E/flutter (15575): #3      SqfEntityModelProvider.initializeDB (package:sqfentity/sqfentity.dart:573:14)
E/flutter (15575): #4      main (package:expense/main.dart:25:21)
E/flutter (15575): #5      _AsyncAwaitCompleter.start (dart:async-patch/async_patch.dart:45:6)
E/flutter (15575): #6      main (package:expense/main.dart:13:18)
E/flutter (15575): #7      _runMainZoned.<anonymous closure>.<anonymous closure> (dart:ui/hooks.dart:239:25)
E/flutter (15575): #8      _rootRun (dart:async/zone.dart:1126:13)
E/flutter (15575): #9      _CustomZone.run (dart:async/zone.dart:1023:19)
E/flutter (15575): #10     _runZoned (dart:async/zone.dart:1518:10)
E/flutter (15575): #11     runZoned (dart:async/zone.dart:1502:12)
E/flutter (15575): #12     _runMainZoned.<anonymous closure> (dart:ui/hooks.dart:231:5)
E/flutter (15575): #13     _startIsolate.<anonymous closure> (dart:isolate-patch/isolate_patch.dart:307:19)
E/flutter (15575): #14     _RawReceivePortImpl._handleMessage (dart:isolate-patch/isolate_patch.dart:174:12)

Issue 1 - SourceAccount column for Transactions table alter request is called multiple times (2-3 (random but > 1) times as of logs and debug controls)

Issue 2 - DATABASE INITIALIZE ERROR The primary key name '_id' for table named [accounts] must be in [id]

Note - For issue 2, I wen't through #52. I investigated and completely uninstalled the app and re-ran the debug, still received this message.

/// Table schemas - 

// Account
const account = SqfEntityTable(
  tableName: 'accounts',
  modelName: 'Account',
  primaryKeyName: 'id',
  primaryKeyType: PrimaryKeyType.integer_auto_incremental,
  fields: [
    SqfEntityField("name", DbType.text),
    SqfEntityField("balance", DbType.real, defaultValue: 0.0),
    SqfEntityFieldRelationship(
      fieldName: 'ParentAccount',
      deleteRule: DeleteRule.NO_ACTION,
      relationType: RelationType.ONE_TO_ONE,
    )
  ],
);


// Transaction
const transaction = SqfEntityTable(
  tableName: 'transactions',
  modelName: 'Transaction',
  primaryKeyName: 'id',
  primaryKeyType: PrimaryKeyType.integer_auto_incremental,
  fields: [
    SqfEntityFieldRelationship(
      parentTable: account,
      fieldName: 'SourceAccount',
      relationType: RelationType.ONE_TO_MANY,
      deleteRule: DeleteRule.NO_ACTION,
    ),
    SqfEntityFieldRelationship(
      parentTable: account,
      fieldName: 'EffectiveSourceAccount',
      relationType: RelationType.ONE_TO_MANY,
      deleteRule: DeleteRule.NO_ACTION,
    ),
    SqfEntityFieldRelationship(
      parentTable: account,
      fieldName: 'TransferDestinationAccount',
      relationType: RelationType.ONE_TO_MANY,
      deleteRule: DeleteRule.NO_ACTION,
    ),
    SqfEntityFieldRelationship(
      parentTable: account,
      fieldName: 'TransferEffectiveDestinationAccount',
      relationType: RelationType.ONE_TO_MANY,
      deleteRule: DeleteRule.NO_ACTION,
    ),
    SqfEntityField('amount', DbType.real),
    SqfEntityField('balance', DbType.real),
    SqfEntityField('registeredAt', DbType.datetime),
    SqfEntityField('type', DbType.text),
  ],
);


// Transaction Meta
const transactionMeta = SqfEntityTable(
  tableName: 'transaction_metas',
  modelName: 'TransactionMeta',
  primaryKeyName: 'id',
  primaryKeyType: PrimaryKeyType.integer_auto_incremental,
  fields: [
    SqfEntityField("description", DbType.text),
    SqfEntityField("happenedAt", DbType.datetime),
    SqfEntityFieldRelationship(
      parentTable: transaction,
      fieldName: "Transaction",
      relationType: RelationType.ONE_TO_ONE,
      deleteRule: DeleteRule.NO_ACTION,
    ),
  ],
);

[hhtokpinar]

For issue 2, I wen't through #52. I investigated and completely uninstalled the app and re-ran the debug, still received this message.

A created database won't be removed even if you uninstall the app. You can try to rename the databaseName to apply the changes. I'm testing your model to see the other issues

[hhtokpinar]

I tested your model. I found the cause of the issue that 2, it's because the column related itself by RelationType.ONE_TO_ONE.
I'll fix too in the next version

[hhtokpinar]

when it comes to the first issue, I didn't come across any issues like yours.
I removed SourceAccount and added it again to run alterTableQuery. Maybe the second issue causes it and disappeared after fixed.

The result like below:

flutter: SQFENTITIY: Table named [accounts] was initialized successfully (No added new columns)
flutter: SQFENTITIY: alterTableQuery => [ALTER TABLE transactions ADD COLUMN SourceAccount integer, CREATE INDEX IF NOT EXISTS IDXAccountSourceAccount ON transactions (SourceAccount ASC)]
flutter: SQFENTITIY: Table named [transactions] was initialized successfully (Added new columns)
flutter: SQFENTITIY: Table named [transaction_metas] was initialized successfully (No added new columns)
flutter: SQFENTITIY: The database is ready for use

now released v 1.3.5+1
could you try again?

[vaishnavmhetre]

Secondly the save method on TransactionMeta is hidden (_save). So how do I associate Transaction with TransactionMeta (create TransactionMeta object)?

[hhtokpinar]

Secondly the save method on TransactionMeta is hidden (_save). So how do I associate Transaction with TransactionMeta (create TransactionMeta object)?

TransactionMeta is a child table which is related one-to-one, so it has been saved automatically when you save the Transaction

I mean that transaction.save() saves all children sub items (only one-to-one)

[vaishnavmhetre]

I just wanted to suggest. The persistence flow needs more clear documentation, I mean what's automated. Same thing was seen by me in one of previous threads where someone mentioned about _save method.

What's automated and Actual examples with outputs will be really helpful as documentation to avoid such confusion and/or misunderstanding.

Although thanks for auto versioning the table updates. Ome of many innovative things only found here.

[hhtokpinar]

What's automated and Actual examples with outputs will be really helpful as documentation to avoid such confusion and/or misunderstanding.

You’re right! I’m developing it, fixing, and trying to prepare Wiki at the same time.
But I can say that there’re no automated functions except saving child objects and table updates when first initialized the app

[vaishnavmhetre]

Do we have subquery conditions?

Like for above models Transaction().select().transaction_meta.happened_at.equals(somedate).toList() returning Transactions list.

Secondly for some reason TransactionMeta has declarations for sourceAccount effective Account,etc (all account relations in Transaction table) as transactionsSource, etc.
Secondly there is no getter in transactionMeta generated for Transaction.

[hhtokpinar]

you should try to get Transactions which are contains their ID's by filtering TransactionMeta table in 2 steps instead subquery, like this:

// get Transaction id's first:
final transactionIds = await TransactionMeta().distinct(columnsToSelect: ['Transaction']).happenedAt.equals(DateTime.now()).toList();
 
// select transactions by filtered Id
final transactions = await Transaction().select().id.inValues(transactionIds).toList();

did you mean that?

[vaishnavmhetre]

Yeah somewhat just like this but this creates 2 calls to DB. Can we use joins or subqueries to reduce calls to db? I have seen this functionality in Laravel Framework (PHP based) just to let know if helps.

It might be a good inspiration to add up awesome things as ORM for sqflite.

Actually many languages use Reflection API to avoid Code generation which isn't a stable thing as is in dart but following package reflectable might be somewhat useful to avoid any rewrites (code generation) and align with dynamic calls like in several ORMs (Laravel Eloquent) as I tried the same in Java Project link and worked like charm. Its not a complete thing, as I was passing over time getting trained in Java with this, but might provide a push somewhat.

[vaishnavmhetre]

you should try to get Transactions which are contains their ID's by filtering TransactionMeta table in 2 steps instead subquery, like this:

// get Transaction id's first:
final transactionIds = await TransactionMeta().distinct(columnsToSelect: ['Transaction']).happenedAt.equals(DateTime.now()).toList();
 
// select transactions by filtered Id
final transactions = await Transaction().select().id.inValues(transactionIds).toList();

did you mean that?

Tried this using between(startDate, endDate) instead of equals, which returns [TransactionMeta] instance and not transactionIds!

[hhtokpinar]

Yeah somewhat just like this but this creates 2 calls to DB.

it no longer 2 calls to DB as shown below.
following first line just build pure SQL and its parameters, so the second line uses it...
like this:

// get Transaction id's first:
final  transactionIds = TransactionMeta().select().happenedAt.equals(DateTime.now()).toListPrimaryKeySQL();
// 
// select transactions by filtered Id
final transactions = await Transaction().select().id.inValues(transactionIds).toList();

just upgrade to this version:

sqfentity: 1.4.0-dev.1.3

it's worth a try :)

[vaishnavmhetre]

Yeah somewhat just like this but this creates 2 calls to DB.

it no longer 2 calls to DB as shown below.
following first line just build pure SQL and its parameters, so the second line uses it...
like this:

// get Transaction id's first:
final  transactionIds = TransactionMeta().select().happenedAt.equals(DateTime.now()).toListPrimaryKeySQL();
// 
// select transactions by filtered Id
final transactions = await Transaction().select().id.inValues(transactionIds).toList();

just upgrade to this version:

sqfentity: 1.4.0-dev.1.3

it's worth a try :)

Well if I can't get Transactions with list of Primary Keys of TransactionMeta right, I will need to check TransactionMeta column to find values in?

[hhtokpinar]

Well if I can't get Transactions with list of Primary Keys of TransactionMeta right, I will need to check TransactionMeta column to find values in?

What do you need to check for? I’m not sure I get

[vaishnavmhetre]

I want to get Transactions where their TransactionMeta.happenedAt is in given month and Year only.

Considering that Transaction and TransactionMeta have One to one relation.

[hhtokpinar]

got it... How about creating a view model like tables?

[vaishnavmhetre]

I have declared custom models and I've set up converters to convert data from Database types to those models for easy access. Coding those converters wasn't an issue but getting data and passing it through generically is becoming a tedious task.
So having models making select, update, where queries would be a great help, just by declaring functions returning generic relations objects with column specific data.

I do know reflection features are not provided fully by Dart (yet), although I suggest you to refer Laravel Framework's Eloquent models in case you need some help with easy features.

[tomerpeled]

For issue 2, I wen't through #52. I investigated and completely uninstalled the app and re-ran the debug, still received this message.

A created database won't be removed even if you uninstall the app. You can try to rename the databaseName to apply the changes. I'm testing your model to see the other issues

Is there a way, that we can remove the existing db?

[hhtokpinar]

It’s off-sqfentity, you can look at here: https://stackoverflow.com/questions/59124856/how-to-check-and-delete-all-existing-db-of-sqflite-in-flutter

For issue 2, I wen't through #52. I investigated and completely uninstalled the app and re-ran the debug, still received this message.

A created database won't be removed even if you uninstall the app. You can try to rename the databaseName to apply the changes. I'm testing your model to see the other issues

Is there a way, that we can remove the existing db?

[tomerpeled]

Thanks