DriftRemoteException

[AveLexinton]

import 'package:drift/drift.dart';
import 'package:drift_flutter/drift_flutter.dart';
import 'package:path_provider/path_provider.dart';

part 'database.g.dart';

class Accounts extends Table {
  late final account_id = integer()();
  late final account_name = text().withLength(min: 1, max: 128)();
  late final password = text().withLength(min: 1, max: 128)();
  late final session_id = text().withLength(min: 1, max: 1024).nullable()();
  late final session_expire = dateTime().nullable()();
  late final role = integer().clientDefault(() => 2)();
  late final phone = text().withLength(min: 1, max: 32)();
  late final nickname =
      text().withLength(min: 1, max: 128).clientDefault(() => 'Hello$phone')();

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

class TodoItems extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text().withLength(min: 6, max: 32)();
  TextColumn get content => text().named('body')();
  DateTimeColumn get createdAt => dateTime().nullable()();
}

@DriftDatabase(tables: [Accounts])
class AppDatabase extends _$AppDatabase {
  // After generating code, this class needs to define a `schemaVersion` getter
  // and a constructor telling drift where the database should be stored.
  // These are described in the getting started guide: https://drift.simonbinder.eu/setup/
  AppDatabase([QueryExecutor? executor]) : super(executor ?? _openConnection());

  @override
  int get schemaVersion => 1;

  @override
  MigrationStrategy get migration => MigrationStrategy(
    onUpgrade: (migrator, from, to) async {
      print('-----------from:$from, to:$to');
      if (from < 2) {
        await migrator.createTable(accounts);
      }
    },
  );

  static QueryExecutor _openConnection() {
    return driftDatabase(
      name: 'my_database',
      native: const DriftNativeOptions(
        // By default, `driftDatabase` from `package:drift_flutter` stores the
        // database files in `getApplicationDocumentsDirectory()`.
        databaseDirectory: getApplicationSupportDirectory,
      ),
      // If you need web support, see https://drift.simonbinder.eu/platforms/web/
    );
  }
}

void main1() async {
  // WidgetsFlutterBinding.ensureInitialized();
  print('hi');

  final database = AppDatabase();

  await database
      .into(database.accounts)
      .insert(
        AccountsCompanion.insert(
          account_id: Value(1),
          account_name: 'test',
          password: 'abc',
          phone: '12345',
        ),
      );
  List<Account> accounts = await database.select(database.accounts).get();
  print('items: $accounts');
}

After running the above code, the compiler reported errors:

Exception has occurred.
DriftRemoteException (SqliteException(1): while preparing statement, no such table: accounts, SQL logic error (code 1)
  Causing statement: INSERT INTO "accounts" ("account_id", "account_name", "password", "role", "phone", "nickname") VALUES (?, ?, ?, ?, ?, ?))

I printed the tables in the database, and the table 'accounts' was in it. Why were the errors present?

BTW, where is the sqlite3 .db file on a Android emulator, and how should I issue sql queries against it directly, any tools?

[simolus3]

You didn't mention that, but I assume the accounts table has been added later? The migration looks correct to me, but you also need to increase the schemaVersion getter so that the migration runs:

  int get schemaVersion => 2;

I printed the tables in the database, and the table 'accounts' was in it. Why were the errors present?

Which tool did you use for that, was that a separate database? You can use the devtools extension with drift to inspect the database, there's also a button there to validate that the schema is up-to-date.

[simolus3]

Ah, sorry about that. It's actually impossible to set a default value that depends on another value. Putting this into a generated as column as you've suggested would work, but then you can't override the value.

You could add an explicit custom_nickname column that's nullable and then add a generated nickname column that's generatedAs(coalesce([customNickname, const Constant('Hello_') + phone])) to use the custom value by default and then fall back to another one if necessary.

[AveLexinton]

Ok. Got it. Another question is how to constrain a column to have a value that is either Null or one of a predefined set of values. I had tried the following, but it was a required filed.

late final Column<String> info=
      text().check(
        info.isNull() |
            info.isIn([
              'stop',
              'length',
              'content',
              'log',
              'system',
            ]),
      )();

[dickermoshe]

All columns are required by default. Adding a check constraint doesn't override the NOT NULL constraint that is applied by default.

You need to add .nullable() like so:

late final Column<String> info=
      text().check(
        info.isNull() |
            info.isIn([
              'stop',
              'length',
              'content',
              'log',
              'system',
            ]),
      ).nullable()();

[AveLexinton]

I tried it. Although the build succeeded, it gave some warnings:

[WARNING] drift_dev on lib/database/database.dart: Could not analyze DriftElementId(package:aiassistant/database/database.dart, user_account) Null check operator used on a null value package:drift_dev/src/analysis/resolver/shared/dart_types.dart 378:47 readEnumConverter package:drift_dev/src/analysis/resolver/dart/column.dart 444:19 ColumnParser.parse package:drift_dev/src/analysis/resolver/dart/table.dart 361:18 DartTableResolver._parseColumns package:drift_dev/src/analysis/resolver/dart/table.dart 23:29 DartTableResolver.resolve package:drift_dev/src/analysis/resolver/resolver.dart 106:22 DriftResolver._resolveDiscovered package:drift_dev/src/analysis/resolver/resolver.dart 63:12 DriftResolver._restoreOrResolve package:drift_dev/src/analysis/resolver/resolver.dart 42:12 DriftResolver.resolveEntrypoint package:drift_dev/src/analysis/driver/driver.dart 270:16 DriftAnalysisDriver.resolveElement package:drift_dev/src/analysis/driver/driver.dart 255:7 DriftAnalysisDriver._analyzeLocalElements package:drift_dev/src/analysis/driver/driver.dart 319:5 DriftAnalysisDriver.resolveElementspackage:drift_dev/src/analysis/resolver/file_analysis.dart 26:7 FileAnalyzer.runAnalysisOn package:drift_dev/src/analysis/driver/driver.dart 332:22 DriftAnalysisDriver.fullyAnalyze package:drift_dev/src/backends/build/drift_builder.dart 196:20 _DriftBuildRun._analyze package:drift_dev/src/backends/build/drift_builder.dart 428:11 _DriftBuildRun._generateMonolithic package:drift_dev/src/backends/build/drift_builder.dart 184:7 _DriftBuildRun.run package:drift_dev/src/backends/build/drift_builder.dart 109:5 DriftBuilder.build

[AveLexinton]

Is there a cleaner way to do it without warnings?