Aves_mio0.88/lib/model/db/db_sqflite_schema.dart

// lib/model/db/db_sqflite_schema.dart
import 'package:sqflite/sqflite.dart';

class SqfliteLocalMediaDbSchema {
  static const entryTable = 'entry';
  static const dateTakenTable = 'dateTaken';
  static const metadataTable = 'metadata';
  static const addressTable = 'address';
  static const favouriteTable = 'favourites';
  static const coverTable = 'covers';
  static const dynamicAlbumTable = 'dynamicAlbums';
  static const vaultTable = 'vaults';
  static const trashTable = 'trash';
  static const videoPlaybackTable = 'videoPlayback';

  static const foldersTable = 'folders';

  static const allTables = [
    entryTable,
    dateTakenTable,
    metadataTable,
    addressTable,
    favouriteTable,
    coverTable,
    dynamicAlbumTable,
    vaultTable,
    trashTable,
    videoPlaybackTable,
    foldersTable,
  ];

  static Future<void> createLatestVersion(Database db) async {
    await Future.forEach(allTables, (table) => createTable(db, table));
  }

  static Future<void> createTable(Database db, String table) async {
    switch (table) {
      case entryTable:
        await db.execute(
          'CREATE TABLE $entryTable('
          'id INTEGER PRIMARY KEY'
          ', contentId INTEGER'
          ', uri TEXT'
          ', path TEXT'
          ', sourceMimeType TEXT'
          ', width INTEGER'
          ', height INTEGER'
          ', sourceRotationDegrees INTEGER'
          ', sizeBytes INTEGER'
          ', title TEXT'
          ', dateAddedSecs INTEGER DEFAULT (strftime(\'%s\',\'now\'))'
          ', dateModifiedMillis INTEGER'
          ', sourceDateTakenMillis INTEGER'
          ', durationMillis INTEGER'
          ', trashed INTEGER DEFAULT 0'
          ', origin INTEGER DEFAULT 0'
          ', provider TEXT'
          ', remoteId TEXT'
          ', remotePath TEXT'
          ', remoteThumb1 TEXT'
          ', remoteThumb2 TEXT'
          ')',
        );

        await db.execute(
          'CREATE INDEX IF NOT EXISTS idx_entry_origin '
          'ON $entryTable(origin)',
        );

        await db.execute(
          'CREATE INDEX IF NOT EXISTS idx_entry_remoteId '
          'ON $entryTable(remoteId)',
        );

        return;

      case dateTakenTable:
        await db.execute(
          'CREATE TABLE $dateTakenTable('
          'id INTEGER PRIMARY KEY'
          ', dateMillis INTEGER'
          ')',
        );
        return;

      case metadataTable:
        await db.execute(
          'CREATE TABLE $metadataTable('
          'id INTEGER PRIMARY KEY'
          ', mimeType TEXT'
          ', dateMillis INTEGER'
          ', flags INTEGER'
          ', rotationDegrees INTEGER'
          ', xmpSubjects TEXT'
          ', xmpTitle TEXT'
          ', latitude REAL'
          ', longitude REAL'
          ', rating INTEGER'
          ')',
        );
        return;

      case addressTable:
        await db.execute(
          'CREATE TABLE $addressTable('
          'id INTEGER PRIMARY KEY'
          ', addressLine TEXT'
          ', countryCode TEXT'
          ', countryName TEXT'
          ', adminArea TEXT'
          ', locality TEXT'
          ')',
        );
        return;

      case favouriteTable:
        await db.execute(
          'CREATE TABLE $favouriteTable('
          'id INTEGER PRIMARY KEY'
          ')',
        );
        return;

      case coverTable:
        await db.execute(
          'CREATE TABLE $coverTable('
          'filter TEXT PRIMARY KEY'
          ', entryId INTEGER'
          ', packageName TEXT'
          ', color TEXT'
          ')',
        );
        return;

      case dynamicAlbumTable:
        await db.execute(
          'CREATE TABLE $dynamicAlbumTable('
          'name TEXT PRIMARY KEY'
          ', filter TEXT'
          ')',
        );
        return;

      case vaultTable:
        await db.execute(
          'CREATE TABLE $vaultTable('
          'name TEXT PRIMARY KEY'
          ', autoLock INTEGER'
          ', useBin INTEGER'
          ', lockType TEXT'
          ')',
        );
        return;

      case trashTable:
        await db.execute(
          'CREATE TABLE $trashTable('
          'id INTEGER PRIMARY KEY'
          ', path TEXT'
          ', dateMillis INTEGER'
          ')',
        );
        return;

      case videoPlaybackTable:
        await db.execute(
          'CREATE TABLE $videoPlaybackTable('
          'id INTEGER PRIMARY KEY'
          ', resumeTimeMillis INTEGER'
          ')',
        );
        return;

      case foldersTable:
        await db.execute(
          'CREATE TABLE $foldersTable('
          'path TEXT PRIMARY KEY,'
          'display_name TEXT,'
          'content_type INTEGER NOT NULL DEFAULT 0,'
          'file_count INTEGER NOT NULL DEFAULT 0,'
          'total_size INTEGER NOT NULL DEFAULT 0,'
          'thumb_entry_id INTEGER,'
          'thumb_date INTEGER,'
          'album_type INTEGER NOT NULL DEFAULT 0,'
          'priority INTEGER NOT NULL DEFAULT 0,'
          'is_remote INTEGER NOT NULL DEFAULT 0,'
          'user TEXT,'
          'device_id TEXT,'
          'hidden INTEGER NOT NULL DEFAULT 0,'
          'hybrid_folder INTEGER DEFAULT 0'
          ')',
        );

        await db.execute(
          'CREATE INDEX IF NOT EXISTS idx_folders_path '
          'ON $foldersTable(path)',
        );

        await db.execute(
          'CREATE INDEX IF NOT EXISTS idx_folders_is_remote '
          'ON $foldersTable(is_remote)',
        );

        await db.execute(
          'CREATE INDEX IF NOT EXISTS idx_folders_priority '
          'ON $foldersTable(priority)',
        );

        await db.execute(
          'CREATE INDEX IF NOT EXISTS idx_folders_hybrid '
          'ON $foldersTable(hybrid_folder)',
        );

        await db.execute(
          'CREATE INDEX IF NOT EXISTS idx_folders_content_type_path '
          'ON $foldersTable(content_type, path)',
        );

        await db.execute(
          'CREATE INDEX IF NOT EXISTS idx_folders_content_type_remote '
          'ON $foldersTable(content_type, is_remote)',
        );

        return;

      default:
        throw Exception('unknown table=$table');
    }
  }
}