1697 lines
51 KiB
Dart
1697 lines
51 KiB
Dart
// lib/model/db/db_sqflite.dart
|
||
import 'dart:io';
|
||
import 'package:aves/model/activity/activity_entry.dart';
|
||
import 'package:aves/model/activity/activity_preview_point.dart';
|
||
import 'package:aves/model/covers.dart';
|
||
import 'package:aves/model/db/db.dart';
|
||
import 'package:aves/model/db/db_sqflite_schema.dart';
|
||
import 'package:aves/model/db/db_sqflite_upgrade.dart';
|
||
import 'package:aves/model/db/folder_content_type.dart';
|
||
import 'package:aves/model/entry/entry.dart';
|
||
import 'package:aves/model/favourites.dart';
|
||
import 'package:aves/model/filters/filters.dart';
|
||
import 'package:aves/model/metadata/address.dart';
|
||
import 'package:aves/model/metadata/catalog.dart';
|
||
import 'package:aves/model/metadata/trash.dart';
|
||
import 'package:aves/model/vaults/details.dart';
|
||
import 'package:aves/model/viewer/video_playback.dart';
|
||
import 'package:aves/services/common/services.dart';
|
||
import 'package:collection/collection.dart';
|
||
import 'package:flutter/foundation.dart';
|
||
import 'package:sqflite/sqflite.dart';
|
||
|
||
// ✅ NEW: Remote origin costante
|
||
import 'package:aves/remote/remote_origin.dart';
|
||
import 'package:path/path.dart' as p;
|
||
import 'package:aves/model/source/collection_source.dart';
|
||
import 'package:aves/utils/android_file_utils.dart';
|
||
|
||
import 'folder_stats.dart';
|
||
|
||
class SqfliteLocalMediaDb implements LocalMediaDb {
|
||
late Database _db;
|
||
|
||
// 🔴 4A: espone la connessione nativa aperta dal loader
|
||
@override
|
||
Database get rawDb => _db;
|
||
|
||
@override
|
||
//Future<String> get path async => pContext.join(await getDatabasesPath(), 'metadata.db');
|
||
Future<String> get path async => pContext.join(await getDatabasesPath(), 'aves.db');
|
||
static const entryTable = SqfliteLocalMediaDbSchema.entryTable;
|
||
static const dateTakenTable = SqfliteLocalMediaDbSchema.dateTakenTable;
|
||
static const metadataTable = SqfliteLocalMediaDbSchema.metadataTable;
|
||
static const addressTable = SqfliteLocalMediaDbSchema.addressTable;
|
||
static const favouriteTable = SqfliteLocalMediaDbSchema.favouriteTable;
|
||
static const coverTable = SqfliteLocalMediaDbSchema.coverTable;
|
||
//static const dynamicAlbumTable = SqfliteLocalMediaDbSchema.dynamicAlbumTable;
|
||
static const vaultTable = SqfliteLocalMediaDbSchema.vaultTable;
|
||
static const trashTable = SqfliteLocalMediaDbSchema.trashTable;
|
||
static const videoPlaybackTable = SqfliteLocalMediaDbSchema.videoPlaybackTable;
|
||
static const activityTable = 'activity';
|
||
static const activityPreviewPointTable = 'activity_preview_point';
|
||
|
||
static const _entryInsertSliceMaxCount = 10000; // number of entries
|
||
static const _queryCursorBufferSize = 1000; // number of rows
|
||
static int _lastId = 0;
|
||
|
||
@override
|
||
int get nextId => ++_lastId;
|
||
|
||
@override
|
||
Future<Set<String>> getLocalFolderPaths() async {
|
||
final rows = await _db.query(
|
||
'folders',
|
||
columns: ['path'],
|
||
where: 'is_remote = ? AND content_type = ?',
|
||
whereArgs: [
|
||
0,
|
||
FolderContentType.media,
|
||
],
|
||
);
|
||
|
||
return rows.map((r) => r['path'] as String).toSet();
|
||
}
|
||
|
||
// ============================================================
|
||
// FOLDERS API (LOCAL)
|
||
// ============================================================
|
||
|
||
@override
|
||
Future<Map<String, FolderStats>> buildFoldersInMemory(CollectionSource source) async {
|
||
final Map<String, FolderStats> folders = {};
|
||
|
||
for (final e in source.allEntries) {
|
||
final path = e.path;
|
||
if (path == null || path.isEmpty) continue;
|
||
|
||
final dir = p.dirname(path);
|
||
final stats = folders.putIfAbsent(dir, () {
|
||
return FolderStats(
|
||
path: dir,
|
||
displayName: dir.split('/').last,
|
||
contentType: FolderContentType.media,
|
||
fileCount: 0,
|
||
totalSize: 0,
|
||
thumbEntryId: null,
|
||
thumbDate: null,
|
||
albumType: 0,
|
||
priority: 0,
|
||
isRemote: 0,
|
||
user: null,
|
||
deviceId: null,
|
||
hidden: 0,
|
||
hybridFolder: null,
|
||
);
|
||
});
|
||
|
||
stats.fileCount++;
|
||
stats.totalSize += e.sizeBytes ?? 0;
|
||
|
||
final date = e.dateModifiedMillis ?? 0;
|
||
if (stats.thumbDate == null || date > stats.thumbDate!) {
|
||
stats.thumbDate = date;
|
||
stats.thumbEntryId = e.id;
|
||
}
|
||
|
||
stats.albumType = androidFileUtils.getAlbumType(dir).index;
|
||
stats.priority = stats.albumType;
|
||
stats.isRemote = e.origin;
|
||
stats.user = null;
|
||
stats.deviceId = null;
|
||
}
|
||
|
||
return folders;
|
||
}
|
||
|
||
@override
|
||
Future<void> saveFoldersBatch(List<FolderStats> folders) async {
|
||
await _db.transaction((txn) async {
|
||
final batch = txn.batch();
|
||
|
||
for (final stats in folders) {
|
||
batch.insert(
|
||
'folders',
|
||
{
|
||
'path': stats.path,
|
||
'display_name': stats.displayName,
|
||
'content_type': stats.contentType,
|
||
'album_type': stats.albumType,
|
||
'priority': stats.priority,
|
||
'is_remote': stats.isRemote,
|
||
'hybrid_folder': stats.hybridFolder,
|
||
'user': stats.user,
|
||
'device_id': stats.deviceId,
|
||
'file_count': stats.fileCount,
|
||
'total_size': stats.totalSize,
|
||
'thumb_entry_id': stats.thumbEntryId,
|
||
'thumb_date': stats.thumbDate,
|
||
'hidden': stats.hidden,
|
||
},
|
||
conflictAlgorithm: ConflictAlgorithm.replace,
|
||
);
|
||
}
|
||
|
||
await batch.commit(noResult: true);
|
||
});
|
||
}
|
||
|
||
@override
|
||
Future<List<FolderStats>> loadFolders({
|
||
int? contentType,
|
||
int? isRemote,
|
||
}) async {
|
||
final whereParts = <String>[];
|
||
final whereArgs = <Object?>[];
|
||
|
||
if (contentType != null) {
|
||
whereParts.add('content_type = ?');
|
||
whereArgs.add(contentType);
|
||
}
|
||
|
||
if (isRemote != null) {
|
||
whereParts.add('is_remote = ?');
|
||
whereArgs.add(isRemote);
|
||
}
|
||
|
||
final rows = await _db.query(
|
||
'folders',
|
||
where: whereParts.isEmpty ? null : whereParts.join(' AND '),
|
||
whereArgs: whereArgs.isEmpty ? null : whereArgs,
|
||
);
|
||
|
||
return rows.map((row) {
|
||
final path = row['path'] as String;
|
||
final displayName = row['display_name'] as String? ?? path;
|
||
|
||
final rawHybridFolder = row['hybrid_folder'];
|
||
final hybridFolder = rawHybridFolder is num ? rawHybridFolder.toInt() : 0;
|
||
|
||
return FolderStats(
|
||
path: path,
|
||
displayName: displayName,
|
||
contentType: row['content_type'] as int? ?? FolderContentType.media,
|
||
fileCount: row['file_count'] as int? ?? 0,
|
||
totalSize: row['total_size'] as int? ?? 0,
|
||
thumbEntryId: row['thumb_entry_id'] as int?,
|
||
thumbDate: row['thumb_date'] as int?,
|
||
albumType: row['album_type'] as int? ?? 0,
|
||
priority: row['priority'] as int? ?? 0,
|
||
isRemote: row['is_remote'] as int? ?? 0,
|
||
user: row['user'] as String?,
|
||
deviceId: row['device_id'] as String?,
|
||
hidden: row['hidden'] as int? ?? 0,
|
||
hybridFolder: hybridFolder,
|
||
);
|
||
}).toList();
|
||
}
|
||
|
||
Future<void> updateFolderPriority(String path, int priority) async {
|
||
await _db.update(
|
||
'folders',
|
||
{'priority': priority},
|
||
where: 'path = ?',
|
||
whereArgs: [path],
|
||
);
|
||
}
|
||
|
||
Future<void> updateFolderHidden(String path, int hidden) async {
|
||
await _db.update(
|
||
'folders',
|
||
{'hidden': hidden},
|
||
where: 'path = ?',
|
||
whereArgs: [path],
|
||
);
|
||
}
|
||
|
||
Future<void> updateFolderDisplayName(String path, String name) async {
|
||
await _db.update(
|
||
'folders',
|
||
{'display_name': name},
|
||
where: 'path = ?',
|
||
whereArgs: [path],
|
||
);
|
||
}
|
||
|
||
Future<void> updateFolderCover(String path, int entryId) async {
|
||
await _db.update(
|
||
'folders',
|
||
{
|
||
'thumb_entry_id': entryId,
|
||
'thumb_date': DateTime.now().millisecondsSinceEpoch,
|
||
},
|
||
where: 'path = ?',
|
||
whereArgs: [path],
|
||
);
|
||
}
|
||
|
||
Future<void> deleteFolder(String path) async {
|
||
await _db.delete(
|
||
'folders',
|
||
where: 'path = ?',
|
||
whereArgs: [path],
|
||
);
|
||
}
|
||
|
||
@override
|
||
Future<void> init() async {
|
||
debugPrint('[localMediaDb][init] START');
|
||
|
||
final dbPath = await path;
|
||
debugPrint('[localMediaDb][init] DB PATH = $dbPath');
|
||
|
||
try {
|
||
debugPrint('[localMediaDb][init] calling openDatabase...');
|
||
_db = await openDatabase(
|
||
dbPath,
|
||
onConfigure: (db) async {
|
||
debugPrint('[localMediaDb][init] onConfigure');
|
||
try {
|
||
await db.execute('PRAGMA journal_mode=WAL');
|
||
} catch (e) {
|
||
debugPrint('[localMediaDb][init] WAL failed: $e');
|
||
}
|
||
try {
|
||
await db.execute('PRAGMA foreign_keys=ON');
|
||
} catch (e) {
|
||
debugPrint('[localMediaDb][init] FK failed: $e');
|
||
}
|
||
},
|
||
onCreate: (db, version) async {
|
||
debugPrint('[localMediaDb][init] onCreate version=$version');
|
||
SqfliteLocalMediaDbSchema.createLatestVersion(db);
|
||
await db.execute('''
|
||
CREATE TABLE IF NOT EXISTS folders (
|
||
path TEXT PRIMARY KEY,
|
||
display_name TEXT,
|
||
content_type INTEGER NOT NULL DEFAULT 0,
|
||
file_count INTEGER,
|
||
total_size INTEGER,
|
||
thumb_entry_id INTEGER,
|
||
thumb_date INTEGER,
|
||
album_type INTEGER,
|
||
priority INTEGER,
|
||
is_remote INTEGER,
|
||
user TEXT,
|
||
device_id TEXT,
|
||
hidden INTEGER,
|
||
hybrid_folder INTEGER DEFAULT 0
|
||
)
|
||
''');
|
||
},
|
||
/*
|
||
onUpgrade: (db, oldVersion, newVersion) async {
|
||
debugPrint('[localMediaDb][init] onUpgrade $oldVersion -> $newVersion');
|
||
await LocalMediaDbUpgrader.upgradeDb(db, oldVersion, newVersion);
|
||
if (oldVersion < 17) {
|
||
await db.execute('''
|
||
CREATE TABLE IF NOT EXISTS folders (
|
||
path TEXT PRIMARY KEY,
|
||
display_name TEXT,
|
||
content_type INTEGER NOT NULL DEFAULT 0,
|
||
file_count INTEGER,
|
||
total_size INTEGER,
|
||
thumb_entry_id INTEGER,
|
||
thumb_date INTEGER,
|
||
album_type INTEGER,
|
||
priority INTEGER,
|
||
is_remote INTEGER,
|
||
user TEXT,
|
||
device_id TEXT,
|
||
hidden INTEGER,
|
||
hybrid_folder INTEGER DEFAULT 0
|
||
)
|
||
''');
|
||
}
|
||
|
||
},*/
|
||
onUpgrade: (db, oldVersion, newVersion) async {
|
||
debugPrint('[localMediaDb][init] onUpgrade $oldVersion -> $newVersion');
|
||
await LocalMediaDbUpgrader.upgradeDb(db, oldVersion, newVersion);
|
||
|
||
if (oldVersion < 17) {
|
||
await db.execute('''
|
||
CREATE TABLE IF NOT EXISTS folders (
|
||
path TEXT PRIMARY KEY,
|
||
display_name TEXT,
|
||
content_type INTEGER NOT NULL DEFAULT 0,
|
||
file_count INTEGER,
|
||
total_size INTEGER,
|
||
thumb_entry_id INTEGER,
|
||
thumb_date INTEGER,
|
||
album_type INTEGER,
|
||
priority INTEGER,
|
||
is_remote INTEGER,
|
||
user TEXT,
|
||
device_id TEXT,
|
||
hidden INTEGER,
|
||
hybrid_folder INTEGER DEFAULT 0
|
||
)
|
||
''');
|
||
}
|
||
},
|
||
version: 17,
|
||
);
|
||
debugPrint('[localMediaDb][init] openDatabase SUCCESS');
|
||
debugPrint('[localMediaDb][init] _db is null? ${_db == null}');
|
||
debugPrint('[localMediaDb][init] _db runtimeType = ${_db.runtimeType}');
|
||
} catch (e, st) {
|
||
debugPrint('[localMediaDb][init] openDatabase FAILED: $e');
|
||
debugPrint('[localMediaDb][init] STACK: $st');
|
||
debugPrint('[localMediaDb][init] deleting DB and retrying');
|
||
|
||
await deleteDatabase(dbPath);
|
||
|
||
/* _db = await openDatabase(
|
||
dbPath,
|
||
version: 17,
|
||
onCreate: (db, version) {
|
||
debugPrint('[localMediaDb][init] onCreate (retry) version=$version');
|
||
SqfliteLocalMediaDbSchema.createLatestVersion(db);
|
||
},
|
||
onUpgrade: LocalMediaDbUpgrader.upgradeDb,
|
||
);
|
||
*/
|
||
_db = await openDatabase(
|
||
dbPath,
|
||
version: 17,
|
||
onCreate: (db, version) async {
|
||
debugPrint('[localMediaDb][init] onCreate (retry) version=$version');
|
||
SqfliteLocalMediaDbSchema.createLatestVersion(db);
|
||
|
||
await db.execute('''
|
||
CREATE TABLE IF NOT EXISTS folders (
|
||
path TEXT PRIMARY KEY,
|
||
display_name TEXT,
|
||
content_type INTEGER NOT NULL DEFAULT 0,
|
||
file_count INTEGER,
|
||
total_size INTEGER,
|
||
thumb_entry_id INTEGER,
|
||
thumb_date INTEGER,
|
||
album_type INTEGER,
|
||
priority INTEGER,
|
||
is_remote INTEGER,
|
||
user TEXT,
|
||
device_id TEXT,
|
||
hidden INTEGER,
|
||
hybrid_folder INTEGER DEFAULT 0
|
||
)
|
||
''');
|
||
},
|
||
onUpgrade: (db, oldVersion, newVersion) async {
|
||
await LocalMediaDbUpgrader.upgradeDb(db, oldVersion, newVersion);
|
||
|
||
if (oldVersion < 17) {
|
||
await db.execute('''
|
||
CREATE TABLE IF NOT EXISTS folders (
|
||
path TEXT PRIMARY KEY,
|
||
display_name TEXT,
|
||
content_type INTEGER NOT NULL DEFAULT 0,
|
||
file_count INTEGER,
|
||
total_size INTEGER,
|
||
thumb_entry_id INTEGER,
|
||
thumb_date INTEGER,
|
||
album_type INTEGER,
|
||
priority INTEGER,
|
||
is_remote INTEGER,
|
||
user TEXT,
|
||
device_id TEXT,
|
||
hidden INTEGER,
|
||
hybrid_folder INTEGER DEFAULT 0
|
||
)
|
||
''');
|
||
}
|
||
},
|
||
);
|
||
|
||
debugPrint('[localMediaDb][init] openDatabase RETRY SUCCESS');
|
||
}
|
||
|
||
debugPrint('[localMediaDb][init] running PRAGMA user_version...');
|
||
try {
|
||
await _db.rawQuery('PRAGMA user_version');
|
||
debugPrint('[localMediaDb][init] PRAGMA OK');
|
||
} catch (e) {
|
||
debugPrint('[localMediaDb][init] PRAGMA FAILED: $e');
|
||
}
|
||
|
||
debugPrint('[localMediaDb][init] ensureRemoteColumns...');
|
||
try {
|
||
await _ensureRemoteColumns(_db);
|
||
debugPrint('[localMediaDb][init] ensureRemoteColumns OK');
|
||
} catch (e) {
|
||
debugPrint('[localMediaDb][init] ensureRemoteColumns FAILED: $e');
|
||
}
|
||
|
||
debugPrint('[localMediaDb][init] ensureFolderColumns...');
|
||
try {
|
||
await _ensureFolderColumns(_db);
|
||
debugPrint('[localMediaDb][init] ensureFolderColumns OK');
|
||
} catch (e, st) {
|
||
debugPrint('[localMediaDb][init] ensureFolderColumns FAILED: $e');
|
||
debugPrint('[localMediaDb][init] ensureFolderColumns STACK: $st');
|
||
}
|
||
|
||
debugPrint('[localMediaDb][init] ensureActivityTables...');
|
||
try {
|
||
await _ensureActivityTables(_db);
|
||
debugPrint('[localMediaDb][init] ensureActivityTables OK');
|
||
} catch (e, st) {
|
||
debugPrint('[localMediaDb][init] ensureActivityTables FAILED: $e');
|
||
debugPrint('[localMediaDb][init] ensureActivityTables STACK: $st');
|
||
}
|
||
|
||
debugPrint('[localMediaDb][init] computing lastId...');
|
||
try {
|
||
final maxIdRows = await _db.rawQuery('SELECT MAX(id) AS maxId FROM $entryTable');
|
||
_lastId = (maxIdRows.firstOrNull?['maxId'] as int?) ?? 0;
|
||
debugPrint('[localMediaDb][init] lastId=$_lastId');
|
||
} catch (e) {
|
||
debugPrint('[localMediaDb][init] lastId FAILED: $e');
|
||
}
|
||
|
||
debugPrint('[localMediaDb][init] END');
|
||
}
|
||
|
||
@override
|
||
Future<int> dbFileSize() async {
|
||
final file = File(await path);
|
||
return await file.exists() ? await file.length() : 0;
|
||
}
|
||
|
||
@override
|
||
Future<void> reset() async {
|
||
debugPrint('$runtimeType reset');
|
||
await _db.close();
|
||
await deleteDatabase(await path);
|
||
await init();
|
||
}
|
||
|
||
@override
|
||
Future<void> removeIds(Set<int> ids, {Set<EntryDataType>? dataTypes}) async {
|
||
if (ids.isEmpty) return;
|
||
|
||
final _dataTypes = dataTypes ?? EntryDataType.values.toSet();
|
||
|
||
final batch = _db.batch();
|
||
const where = 'id = ?';
|
||
const coverWhere = 'entryId = ?';
|
||
ids.forEach((id) {
|
||
final whereArgs = [id];
|
||
if (_dataTypes.contains(EntryDataType.basic)) {
|
||
batch.delete(entryTable, where: where, whereArgs: whereArgs);
|
||
}
|
||
if (_dataTypes.contains(EntryDataType.catalog)) {
|
||
batch.delete(dateTakenTable, where: where, whereArgs: whereArgs);
|
||
batch.delete(metadataTable, where: where, whereArgs: whereArgs);
|
||
}
|
||
if (_dataTypes.contains(EntryDataType.address)) {
|
||
batch.delete(addressTable, where: where, whereArgs: whereArgs);
|
||
}
|
||
if (_dataTypes.contains(EntryDataType.references)) {
|
||
batch.delete(favouriteTable, where: where, whereArgs: whereArgs);
|
||
batch.delete(coverTable, where: coverWhere, whereArgs: whereArgs);
|
||
batch.delete(trashTable, where: where, whereArgs: whereArgs);
|
||
batch.delete(videoPlaybackTable, where: where, whereArgs: whereArgs);
|
||
}
|
||
});
|
||
await batch.commit(noResult: true);
|
||
}
|
||
|
||
// entries
|
||
|
||
@override
|
||
Future<void> clearEntries() async {
|
||
final count = await _db.delete(entryTable, where: '1');
|
||
debugPrint('$runtimeType clearEntries deleted $count rows');
|
||
}
|
||
|
||
@override
|
||
Future<Set<AvesEntry>> loadEntries({int? origin, String? directory}) async {
|
||
// Costruzione dinamica della WHERE
|
||
String? where;
|
||
final whereArgs = <Object?>[];
|
||
|
||
if (origin != null) {
|
||
where = 'origin = ?';
|
||
whereArgs.add(origin);
|
||
}
|
||
|
||
// 📌 Diagnostica: segna i parametri logici d’ingresso
|
||
debugPrint('[localMediaDb] loadEntries(origin=$origin, directory=$directory)');
|
||
|
||
final entries = <AvesEntry>{};
|
||
|
||
if (directory != null) {
|
||
final separator = pContext.separator;
|
||
if (!directory.endsWith(separator)) {
|
||
directory = '$directory$separator';
|
||
}
|
||
|
||
where = '${where != null ? '$where AND ' : ''}path LIKE ?';
|
||
whereArgs.add('$directory%');
|
||
|
||
debugPrint('[localMediaDb] SQL(queryCursor, by directory) WHERE="$where" args=$whereArgs');
|
||
|
||
final cursor = await _db.queryCursor(entryTable, where: where, whereArgs: whereArgs, bufferSize: _queryCursorBufferSize);
|
||
final dirLength = directory.length;
|
||
var readRows = 0;
|
||
while (await cursor.moveNext()) {
|
||
final row = cursor.current;
|
||
readRows++;
|
||
// skip entries in subfolders
|
||
final path = row['path'] as String?;
|
||
if (path != null && !path.substring(dirLength).contains(separator)) {
|
||
entries.add(AvesEntry.fromMap(row));
|
||
}
|
||
}
|
||
debugPrint('[localMediaDb] rows read=$readRows kept=${entries.length} (directory filter)');
|
||
} else {
|
||
debugPrint('[localMediaDb] SQL(queryCursor) WHERE="${where ?? '—'}" args=$whereArgs');
|
||
|
||
final cursor = await _db.queryCursor(entryTable, where: where, whereArgs: whereArgs, bufferSize: _queryCursorBufferSize);
|
||
var readRows = 0;
|
||
while (await cursor.moveNext()) {
|
||
readRows++;
|
||
entries.add(AvesEntry.fromMap(cursor.current));
|
||
}
|
||
debugPrint('[localMediaDb] rows read=$readRows kept=${entries.length}');
|
||
|
||
// 🔎 Fallback di sola diagnostica: se stiamo cercando remoti (origin==RemoteOrigin.value)
|
||
// ma non troviamo nulla, verifichiamo se in tabella ci sono righe origin=RemoteOrigin.value
|
||
if ((origin == RemoteOrigin.value) && entries.isEmpty) {
|
||
try {
|
||
final countRows = await _db.rawQuery(
|
||
'SELECT COUNT(*) AS c FROM $entryTable WHERE origin=?',
|
||
[RemoteOrigin.value],
|
||
);
|
||
final c = (countRows.firstOrNull?['c'] as int?) ?? 0;
|
||
debugPrint('[localMediaDb][diag] check COUNT(origin=${RemoteOrigin.value})=$c');
|
||
if (c > 0) {
|
||
final sample = await _db.rawQuery(
|
||
'SELECT id, remoteId, provider, uri, trashed, dateModifiedMillis '
|
||
'FROM $entryTable WHERE origin=? ORDER BY id DESC LIMIT 5',
|
||
[RemoteOrigin.value],
|
||
);
|
||
debugPrint('[localMediaDb][diag] sample origin=${RemoteOrigin.value} = $sample');
|
||
}
|
||
} catch (e, st) {
|
||
debugPrint('[localMediaDb][diag] fallback check failed: $e\n$st');
|
||
}
|
||
}
|
||
}
|
||
|
||
return entries;
|
||
}
|
||
|
||
@override
|
||
Future<Set<AvesEntry>> loadEntriesById(Set<int> ids) => _getByIds(ids, entryTable, AvesEntry.fromMap);
|
||
|
||
@override
|
||
Future<void> insertEntries(Set<AvesEntry> entries) async {
|
||
if (entries.isEmpty) return;
|
||
final stopwatch = Stopwatch()..start();
|
||
int inserted = 0;
|
||
await Future.forEach(entries.slices(_entryInsertSliceMaxCount), (slice) async {
|
||
debugPrint('$runtimeType saveEntries inserting slice of [${inserted + 1}, ${inserted + slice.length}] entries');
|
||
final batch = _db.batch();
|
||
slice.forEach((entry) => _batchInsertEntry(batch, entry));
|
||
await batch.commit(noResult: true);
|
||
inserted += slice.length;
|
||
});
|
||
debugPrint('$runtimeType saveEntries complete in ${stopwatch.elapsed.inMilliseconds}ms for ${entries.length} entries');
|
||
}
|
||
|
||
@override
|
||
Future<void> updateEntry(int id, AvesEntry entry) async {
|
||
final batch = _db.batch();
|
||
batch.delete(entryTable, where: 'id = ?', whereArgs: [id]);
|
||
_batchInsertEntry(batch, entry);
|
||
await batch.commit(noResult: true);
|
||
}
|
||
|
||
void _batchInsertEntry(Batch batch, AvesEntry entry) {
|
||
batch.insert(
|
||
entryTable,
|
||
entry.toDatabaseMap(),
|
||
conflictAlgorithm: ConflictAlgorithm.replace,
|
||
);
|
||
}
|
||
|
||
@override
|
||
Future<Set<AvesEntry>> searchLiveEntries(String query, {int? limit}) async {
|
||
final rows = await _db.query(
|
||
entryTable,
|
||
where: '(title LIKE ? OR path LIKE ?) AND trashed = ?',
|
||
whereArgs: ['%$query%', '%$query%', 0],
|
||
orderBy: 'sourceDateTakenMillis DESC',
|
||
limit: limit,
|
||
);
|
||
return rows.map(AvesEntry.fromMap).toSet();
|
||
}
|
||
|
||
@override
|
||
Future<Set<AvesEntry>> searchLiveDuplicates(int origin, Set<AvesEntry>? entries) async {
|
||
String where = 'origin = ? AND trashed = ?';
|
||
if (entries != null) {
|
||
where += ' AND contentId IN (${entries.map((v) => v.contentId).join(',')})';
|
||
}
|
||
final rows = await _db.rawQuery(
|
||
'SELECT *, MAX(id) AS id'
|
||
' FROM $entryTable'
|
||
' WHERE $where'
|
||
' GROUP BY contentId'
|
||
' HAVING COUNT(id) > 1',
|
||
[origin, 0],
|
||
);
|
||
final duplicates = rows.map(AvesEntry.fromMap).toSet();
|
||
if (duplicates.isNotEmpty) {
|
||
debugPrint('$runtimeType found duplicates=$duplicates');
|
||
}
|
||
return duplicates;
|
||
}
|
||
|
||
// date taken
|
||
|
||
@override
|
||
Future<void> clearDates() async {
|
||
final count = await _db.delete(dateTakenTable, where: '1');
|
||
debugPrint('$runtimeType clearDates deleted $count rows');
|
||
}
|
||
|
||
@override
|
||
Future<Map<int?, int?>> loadDates() async {
|
||
debugPrint('[localMediaDb][loadDates] _db is null? ${_db == null}');
|
||
debugPrint('[localMediaDb][loadDates] instance hashCode = ${this.hashCode}');
|
||
final result = <int?, int?>{};
|
||
final cursor = await _db.queryCursor(dateTakenTable, bufferSize: _queryCursorBufferSize);
|
||
while (await cursor.moveNext()) {
|
||
final row = cursor.current;
|
||
result[row['id'] as int] = row['dateMillis'] as int? ?? 0;
|
||
}
|
||
return result;
|
||
}
|
||
|
||
// catalog metadata
|
||
|
||
@override
|
||
Future<void> clearCatalogMetadata() async {
|
||
final count = await _db.delete(metadataTable, where: '1');
|
||
debugPrint('$runtimeType clearMetadataEntries deleted $count rows');
|
||
}
|
||
|
||
@override
|
||
Future<Set<CatalogMetadata>> loadCatalogMetadata() async {
|
||
final result = <CatalogMetadata>{};
|
||
final cursor = await _db.queryCursor(metadataTable, bufferSize: _queryCursorBufferSize);
|
||
while (await cursor.moveNext()) {
|
||
result.add(CatalogMetadata.fromMap(cursor.current));
|
||
}
|
||
return result;
|
||
}
|
||
|
||
@override
|
||
Future<Set<CatalogMetadata>> loadCatalogMetadataById(Set<int> ids) => _getByIds(ids, metadataTable, CatalogMetadata.fromMap);
|
||
|
||
@override
|
||
Future<void> saveCatalogMetadata(Set<CatalogMetadata> metadataEntries) async {
|
||
if (metadataEntries.isEmpty) return;
|
||
final stopwatch = Stopwatch()..start();
|
||
try {
|
||
final batch = _db.batch();
|
||
metadataEntries.forEach((metadata) => _batchInsertMetadata(batch, metadata));
|
||
await batch.commit(noResult: true);
|
||
debugPrint('$runtimeType saveMetadata complete in ${stopwatch.elapsed.inMilliseconds}ms for ${metadataEntries.length} entries');
|
||
} catch (error, stack) {
|
||
debugPrint('$runtimeType failed to save metadata with error=$error\n$stack');
|
||
}
|
||
}
|
||
|
||
@override
|
||
Future<void> updateCatalogMetadata(int id, CatalogMetadata? metadata) async {
|
||
final batch = _db.batch();
|
||
batch.delete(dateTakenTable, where: 'id = ?', whereArgs: [id]);
|
||
batch.delete(metadataTable, where: 'id = ?', whereArgs: [id]);
|
||
_batchInsertMetadata(batch, metadata);
|
||
await batch.commit(noResult: true);
|
||
}
|
||
|
||
void _batchInsertMetadata(Batch batch, CatalogMetadata? metadata) {
|
||
if (metadata == null) return;
|
||
if (metadata.dateMillis != 0) {
|
||
batch.insert(
|
||
dateTakenTable,
|
||
{
|
||
'id': metadata.id,
|
||
'dateMillis': metadata.dateMillis,
|
||
},
|
||
conflictAlgorithm: ConflictAlgorithm.replace,
|
||
);
|
||
}
|
||
batch.insert(
|
||
metadataTable,
|
||
metadata.toMap(),
|
||
conflictAlgorithm: ConflictAlgorithm.replace,
|
||
);
|
||
}
|
||
|
||
// address
|
||
|
||
@override
|
||
Future<void> clearAddresses() async {
|
||
final count = await _db.delete(addressTable, where: '1');
|
||
debugPrint('$runtimeType clearAddresses deleted $count rows');
|
||
}
|
||
|
||
@override
|
||
Future<Set<AddressDetails>> loadAddresses() async {
|
||
final result = <AddressDetails>{};
|
||
final cursor = await _db.queryCursor(addressTable, bufferSize: _queryCursorBufferSize);
|
||
while (await cursor.moveNext()) {
|
||
result.add(AddressDetails.fromMap(cursor.current));
|
||
}
|
||
return result;
|
||
}
|
||
|
||
@override
|
||
Future<Set<AddressDetails>> loadAddressesById(Set<int> ids) => _getByIds(ids, addressTable, AddressDetails.fromMap);
|
||
|
||
@override
|
||
Future<void> saveAddresses(Set<AddressDetails> addresses) async {
|
||
if (addresses.isEmpty) return;
|
||
final stopwatch = Stopwatch()..start();
|
||
final batch = _db.batch();
|
||
addresses.forEach((address) => _batchInsertAddress(batch, address));
|
||
await batch.commit(noResult: true);
|
||
debugPrint('$runtimeType saveAddresses complete in ${stopwatch.elapsed.inMilliseconds}ms for ${addresses.length} entries');
|
||
}
|
||
|
||
@override
|
||
Future<void> updateAddress(int id, AddressDetails? address) async {
|
||
final batch = _db.batch();
|
||
batch.delete(addressTable, where: 'id = ?', whereArgs: [id]);
|
||
_batchInsertAddress(batch, address);
|
||
await batch.commit(noResult: true);
|
||
}
|
||
|
||
void _batchInsertAddress(Batch batch, AddressDetails? address) {
|
||
if (address == null) return;
|
||
batch.insert(
|
||
addressTable,
|
||
address.toMap(),
|
||
conflictAlgorithm: ConflictAlgorithm.replace,
|
||
);
|
||
}
|
||
|
||
// vaults
|
||
|
||
@override
|
||
Future<void> clearVaults() async {
|
||
final count = await _db.delete(vaultTable, where: '1');
|
||
debugPrint('$runtimeType clearVaults deleted $count rows');
|
||
}
|
||
|
||
@override
|
||
Future<Set<VaultDetails>> loadAllVaults() async {
|
||
final result = <VaultDetails>{};
|
||
final cursor = await _db.queryCursor(vaultTable, bufferSize: _queryCursorBufferSize);
|
||
while (await cursor.moveNext()) {
|
||
result.add(VaultDetails.fromMap(cursor.current));
|
||
}
|
||
return result;
|
||
}
|
||
|
||
@override
|
||
Future<void> addVaults(Set<VaultDetails> rows) async {
|
||
if (rows.isEmpty) return;
|
||
final batch = _db.batch();
|
||
rows.forEach((row) => _batchInsertVault(batch, row));
|
||
await batch.commit(noResult: true);
|
||
}
|
||
|
||
@override
|
||
Future<void> updateVault(String oldName, VaultDetails row) async {
|
||
final batch = _db.batch();
|
||
batch.delete(vaultTable, where: 'name = ?', whereArgs: [oldName]);
|
||
_batchInsertVault(batch, row);
|
||
await batch.commit(noResult: true);
|
||
}
|
||
|
||
void _batchInsertVault(Batch batch, VaultDetails row) {
|
||
batch.insert(
|
||
vaultTable,
|
||
row.toMap(),
|
||
conflictAlgorithm: ConflictAlgorithm.replace,
|
||
);
|
||
}
|
||
|
||
@override
|
||
Future<void> removeVaults(Set<VaultDetails> rows) async {
|
||
if (rows.isEmpty) return;
|
||
|
||
final batch = _db.batch();
|
||
rows.map((v) => v.name).forEach((name) => batch.delete(vaultTable, where: 'name = ?', whereArgs: [name]));
|
||
await batch.commit(noResult: true);
|
||
}
|
||
|
||
// trash
|
||
|
||
@override
|
||
Future<void> clearTrashDetails() async {
|
||
final count = await _db.delete(trashTable, where: '1');
|
||
debugPrint('$runtimeType clearTrashDetails deleted $count rows');
|
||
}
|
||
|
||
@override
|
||
Future<Set<TrashDetails>> loadAllTrashDetails() async {
|
||
final result = <TrashDetails>{};
|
||
final cursor = await _db.queryCursor(trashTable, bufferSize: _queryCursorBufferSize);
|
||
while (await cursor.moveNext()) {
|
||
result.add(TrashDetails.fromMap(cursor.current));
|
||
}
|
||
return result;
|
||
}
|
||
|
||
@override
|
||
Future<void> updateTrash(int id, TrashDetails? details) async {
|
||
final batch = _db.batch();
|
||
batch.delete(trashTable, where: 'id = ?', whereArgs: [id]);
|
||
_batchInsertTrashDetails(batch, details);
|
||
await batch.commit(noResult: true);
|
||
}
|
||
|
||
void _batchInsertTrashDetails(Batch batch, TrashDetails? details) {
|
||
if (details == null) return;
|
||
batch.insert(
|
||
trashTable,
|
||
details.toMap(),
|
||
conflictAlgorithm: ConflictAlgorithm.replace,
|
||
);
|
||
}
|
||
|
||
// favourites
|
||
|
||
@override
|
||
Future<void> clearFavourites() async {
|
||
final count = await _db.delete(favouriteTable, where: '1');
|
||
debugPrint('$runtimeType clearFavourites deleted $count rows');
|
||
}
|
||
|
||
@override
|
||
Future<Set<FavouriteRow>> loadAllFavourites() async {
|
||
final result = <FavouriteRow>{};
|
||
final cursor = await _db.queryCursor(favouriteTable, bufferSize: _queryCursorBufferSize);
|
||
while (await cursor.moveNext()) {
|
||
result.add(FavouriteRow.fromMap(cursor.current));
|
||
}
|
||
return result;
|
||
}
|
||
|
||
@override
|
||
Future<void> addFavourites(Set<FavouriteRow> rows) async {
|
||
if (rows.isEmpty) return;
|
||
final batch = _db.batch();
|
||
rows.forEach((row) => _batchInsertFavourite(batch, row));
|
||
await batch.commit(noResult: true);
|
||
}
|
||
|
||
@override
|
||
Future<void> updateFavouriteId(int id, FavouriteRow row) async {
|
||
final batch = _db.batch();
|
||
batch.delete(favouriteTable, where: 'id = ?', whereArgs: [id]);
|
||
_batchInsertFavourite(batch, row);
|
||
await batch.commit(noResult: true);
|
||
}
|
||
|
||
void _batchInsertFavourite(Batch batch, FavouriteRow row) {
|
||
batch.insert(
|
||
favouriteTable,
|
||
row.toMap(),
|
||
conflictAlgorithm: ConflictAlgorithm.replace,
|
||
);
|
||
}
|
||
|
||
@override
|
||
Future<void> removeFavourites(Set<FavouriteRow> rows) async {
|
||
if (rows.isEmpty) return;
|
||
|
||
final ids = rows.map((row) => row.entryId).whereType<int>().toSet();
|
||
if (ids.isEmpty) return;
|
||
|
||
final batch = _db.batch();
|
||
for (final id in ids) {
|
||
batch.delete(favouriteTable, where: 'id = ?', whereArgs: [id]);
|
||
}
|
||
await batch.commit(noResult: true);
|
||
}
|
||
|
||
// covers
|
||
|
||
@override
|
||
Future<void> clearCovers() async {
|
||
final count = await _db.delete(coverTable, where: '1');
|
||
debugPrint('$runtimeType clearCovers deleted $count rows');
|
||
}
|
||
|
||
@override
|
||
Future<Set<CoverRow>> loadAllCovers() async {
|
||
final result = <CoverRow>{};
|
||
final cursor = await _db.queryCursor(coverTable, bufferSize: _queryCursorBufferSize);
|
||
while (await cursor.moveNext()) {
|
||
final rowMap = cursor.current;
|
||
final row = CoverRow.fromMap(rowMap);
|
||
if (row != null) {
|
||
result.add(row);
|
||
} else {
|
||
debugPrint('$runtimeType failed to deserialize cover from row=$rowMap');
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
|
||
@override
|
||
Future<void> addCovers(Set<CoverRow> rows) async {
|
||
if (rows.isEmpty) return;
|
||
|
||
final batch = _db.batch();
|
||
rows.forEach((row) => _batchInsertCover(batch, row));
|
||
await batch.commit(noResult: true);
|
||
}
|
||
|
||
@override
|
||
Future<void> updateCoverEntryId(int id, CoverRow row) async {
|
||
final batch = _db.batch();
|
||
batch.delete(coverTable, where: 'entryId = ?', whereArgs: [id]);
|
||
_batchInsertCover(batch, row);
|
||
await batch.commit(noResult: true);
|
||
}
|
||
|
||
void _batchInsertCover(Batch batch, CoverRow row) {
|
||
batch.insert(
|
||
coverTable,
|
||
row.toMap(),
|
||
conflictAlgorithm: ConflictAlgorithm.replace,
|
||
);
|
||
}
|
||
|
||
@override
|
||
Future<void> removeCovers(Set<CollectionFilter> filters) async {
|
||
if (filters.isEmpty) return;
|
||
|
||
// for backward compatibility, remove stored JSON instead of removing de/reserialized filters
|
||
final obsoleteFilterJson = <String>{};
|
||
|
||
final rows = await _db.query(coverTable);
|
||
rows.forEach((row) {
|
||
final filterJson = row['filter'] as String?;
|
||
if (filterJson != null) {
|
||
final filter = CollectionFilter.fromJson(filterJson);
|
||
if (filters.any((v) => filter == v)) {
|
||
obsoleteFilterJson.add(filterJson);
|
||
}
|
||
}
|
||
});
|
||
|
||
final batch = _db.batch();
|
||
obsoleteFilterJson.forEach((filterJson) => batch.delete(coverTable, where: 'filter = ?', whereArgs: [filterJson]));
|
||
await batch.commit(noResult: true);
|
||
}
|
||
|
||
// dynamic albums
|
||
/*
|
||
@override
|
||
Future<int> clearDynamicAlbums() async {
|
||
final count = await _db.delete(dynamicAlbumTable, where: '1');
|
||
debugPrint('$runtimeType clearDynamicAlbums deleted $count rows');
|
||
return count;
|
||
}
|
||
|
||
@override
|
||
Future<Set<DynamicAlbumRow>> loadAllDynamicAlbums({int bufferSize = _queryCursorBufferSize}) async {
|
||
final result = <DynamicAlbumRow>{};
|
||
try {
|
||
final cursor = await _db.queryCursor(dynamicAlbumTable, bufferSize: bufferSize);
|
||
while (await cursor.moveNext()) {
|
||
final rowMap = cursor.current;
|
||
final row = DynamicAlbumRow.fromMap(rowMap);
|
||
if (row != null) {
|
||
result.add(row);
|
||
} else {
|
||
debugPrint('$runtimeType failed to deserialize dynamic album from row=$rowMap');
|
||
}
|
||
}
|
||
} catch (error, stack) {
|
||
debugPrint('$runtimeType failed to query table=$dynamicAlbumTable error=$error\n$stack');
|
||
if (bufferSize > 1) {
|
||
debugPrint('$runtimeType retry to query table=$dynamicAlbumTable with no cursor buffer');
|
||
final safeRows = await loadAllDynamicAlbums(bufferSize: 1);
|
||
final clearedCount = await clearDynamicAlbums();
|
||
await addDynamicAlbums(safeRows);
|
||
final addedCount = safeRows.length;
|
||
final lostCount = clearedCount - addedCount;
|
||
debugPrint('$runtimeType kept $addedCount rows, lost $lostCount rows from table=$dynamicAlbumTable');
|
||
return safeRows;
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
|
||
@override
|
||
Future<void> addDynamicAlbums(Set<DynamicAlbumRow> rows) async {
|
||
if (rows.isEmpty) return;
|
||
|
||
final batch = _db.batch();
|
||
rows.forEach((row) => _batchInsertDynamicAlbum(batch, row));
|
||
await batch.commit(noResult: true);
|
||
}
|
||
|
||
void _batchInsertDynamicAlbum(Batch batch, DynamicAlbumRow row) {
|
||
batch.insert(
|
||
dynamicAlbumTable,
|
||
row.toMap(),
|
||
conflictAlgorithm: ConflictAlgorithm.replace,
|
||
);
|
||
}
|
||
|
||
@override
|
||
Future<void> removeDynamicAlbums(Set<String> names) async {
|
||
if (names.isEmpty) return;
|
||
|
||
final batch = _db.batch();
|
||
names.forEach((name) => batch.delete(dynamicAlbumTable, where: 'name = ?', whereArgs: [name]));
|
||
await batch.commit(noResult: true);
|
||
}
|
||
*/
|
||
// video playback
|
||
|
||
@override
|
||
Future<void> clearVideoPlayback() async {
|
||
final count = await _db.delete(videoPlaybackTable, where: '1');
|
||
debugPrint('$runtimeType clearVideoPlayback deleted $count rows');
|
||
}
|
||
|
||
@override
|
||
Future<Set<VideoPlaybackRow>> loadAllVideoPlayback() async {
|
||
final result = <VideoPlaybackRow>{};
|
||
final cursor = await _db.queryCursor(videoPlaybackTable, bufferSize: _queryCursorBufferSize);
|
||
while (await cursor.moveNext()) {
|
||
final rowMap = cursor.current;
|
||
final row = VideoPlaybackRow.fromMap(rowMap);
|
||
if (row != null) {
|
||
result.add(row);
|
||
} else {
|
||
debugPrint('$runtimeType failed to deserialize video playback from row=$rowMap');
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
|
||
@override
|
||
Future<VideoPlaybackRow?> loadVideoPlayback(int id) async {
|
||
final rows = await _db.query(videoPlaybackTable, where: 'id = ?', whereArgs: [id]);
|
||
if (rows.isEmpty) return null;
|
||
|
||
return VideoPlaybackRow.fromMap(rows.first);
|
||
}
|
||
|
||
@override
|
||
Future<void> addVideoPlayback(Set<VideoPlaybackRow> rows) async {
|
||
if (rows.isEmpty) return;
|
||
|
||
final batch = _db.batch();
|
||
rows.forEach((row) => _batchInsertVideoPlayback(batch, row));
|
||
await batch.commit(noResult: true);
|
||
}
|
||
|
||
void _batchInsertVideoPlayback(Batch batch, VideoPlaybackRow row) {
|
||
batch.insert(
|
||
videoPlaybackTable,
|
||
row.toMap(),
|
||
conflictAlgorithm: ConflictAlgorithm.replace,
|
||
);
|
||
}
|
||
|
||
@override
|
||
Future<void> removeVideoPlayback(Set<int> ids) async {
|
||
if (ids.isEmpty) return;
|
||
|
||
final batch = _db.batch();
|
||
ids.forEach((id) => batch.delete(videoPlaybackTable, where: 'id = ?', whereArgs: [id]));
|
||
await batch.commit(noResult: true);
|
||
}
|
||
// ============================================================
|
||
// ACTIVITIES
|
||
// ============================================================
|
||
|
||
@override
|
||
Future<void> clearActivities() async {
|
||
final batch = _db.batch();
|
||
batch.delete(activityPreviewPointTable, where: '1');
|
||
batch.delete(activityTable, where: '1');
|
||
await batch.commit(noResult: true);
|
||
debugPrint('$runtimeType clearActivities complete');
|
||
}
|
||
|
||
@override
|
||
Future<Set<ActivityEntry>> loadActivities({
|
||
int? origin,
|
||
String? directory,
|
||
String? format,
|
||
String? sport,
|
||
int? startAfterMillis,
|
||
int? startBeforeMillis,
|
||
bool? favourite,
|
||
String? query,
|
||
int? limit,
|
||
}) async {
|
||
final whereParts = <String>[];
|
||
final whereArgs = <Object?>[];
|
||
if (origin != null) {
|
||
whereParts.add('origin = ?');
|
||
whereArgs.add(origin);
|
||
}
|
||
|
||
if (directory != null && directory.trim().isNotEmpty) {
|
||
whereParts.add('relative_path LIKE ?');
|
||
whereArgs.add('${directory.trim()}%');
|
||
}
|
||
|
||
if (format != null && format.trim().isNotEmpty) {
|
||
whereParts.add('format = ?');
|
||
whereArgs.add(format.trim());
|
||
}
|
||
|
||
if (sport != null && sport.trim().isNotEmpty) {
|
||
whereParts.add('sport = ?');
|
||
whereArgs.add(sport.trim());
|
||
}
|
||
|
||
if (startAfterMillis != null) {
|
||
whereParts.add('start_time_millis >= ?');
|
||
whereArgs.add(startAfterMillis);
|
||
}
|
||
|
||
if (startBeforeMillis != null) {
|
||
whereParts.add('start_time_millis <= ?');
|
||
whereArgs.add(startBeforeMillis);
|
||
}
|
||
|
||
if (favourite != null) {
|
||
whereParts.add('is_favorite = ?');
|
||
whereArgs.add(favourite ? 1 : 0);
|
||
}
|
||
|
||
if (query != null && query.trim().isNotEmpty) {
|
||
final q = '%${query.trim()}%';
|
||
whereParts.add('''
|
||
(
|
||
file_name LIKE ?
|
||
OR display_name LIKE ?
|
||
OR route_name LIKE ?
|
||
OR title LIKE ?
|
||
OR description LIKE ?
|
||
OR notes LIKE ?
|
||
OR sport LIKE ?
|
||
OR device_manufacturer LIKE ?
|
||
OR device_model LIKE ?
|
||
OR tags LIKE ?
|
||
)
|
||
''');
|
||
whereArgs.addAll([q, q, q, q, q, q, q, q, q, q]);
|
||
}
|
||
|
||
// Per default non mostro attività nascoste.
|
||
whereParts.add('is_hidden = ?');
|
||
whereArgs.add(0);
|
||
|
||
whereParts.add('scan_status != ?');
|
||
whereArgs.add('missing');
|
||
|
||
final rows = await _db.query(
|
||
activityTable,
|
||
where: whereParts.isEmpty ? null : whereParts.join(' AND '),
|
||
whereArgs: whereArgs,
|
||
orderBy: 'start_time_millis DESC, date_modified_millis DESC',
|
||
limit: limit,
|
||
);
|
||
|
||
return rows.map(ActivityEntry.fromMap).toSet();
|
||
}
|
||
|
||
@override
|
||
Future<ActivityEntry?> loadActivity(int id) async {
|
||
final rows = await _db.query(
|
||
activityTable,
|
||
where: 'id = ?',
|
||
whereArgs: [id],
|
||
limit: 1,
|
||
);
|
||
|
||
if (rows.isEmpty) return null;
|
||
return ActivityEntry.fromMap(rows.first);
|
||
}
|
||
|
||
@override
|
||
Future<ActivityEntry?> loadActivityByUri(String uri) async {
|
||
final rows = await _db.query(
|
||
activityTable,
|
||
where: 'uri = ?',
|
||
whereArgs: [uri],
|
||
limit: 1,
|
||
);
|
||
|
||
if (rows.isEmpty) return null;
|
||
return ActivityEntry.fromMap(rows.first);
|
||
}
|
||
|
||
@override
|
||
Future<int> insertActivity(ActivityEntry entry) async {
|
||
final map = Map<String, Object?>.from(entry.toMap());
|
||
|
||
if (map['id'] == null) {
|
||
map.remove('id');
|
||
}
|
||
|
||
return _db.insert(
|
||
activityTable,
|
||
map,
|
||
conflictAlgorithm: ConflictAlgorithm.replace,
|
||
);
|
||
}
|
||
|
||
@override
|
||
Future<void> updateActivity(int id, ActivityEntry entry) async {
|
||
final map = Map<String, Object?>.from(entry.toMap());
|
||
map['id'] = id;
|
||
|
||
await _db.update(
|
||
activityTable,
|
||
map,
|
||
where: 'id = ?',
|
||
whereArgs: [id],
|
||
conflictAlgorithm: ConflictAlgorithm.replace,
|
||
);
|
||
}
|
||
|
||
@override
|
||
Future<int> upsertActivity(ActivityEntry entry) async {
|
||
final existing = await loadActivityByUri(entry.uri);
|
||
|
||
if (existing?.id != null) {
|
||
await updateActivity(existing!.id!, entry);
|
||
return existing.id!;
|
||
}
|
||
|
||
return insertActivity(entry);
|
||
}
|
||
|
||
@override
|
||
Future<void> removeActivities(Set<int> ids) async {
|
||
if (ids.isEmpty) return;
|
||
|
||
final batch = _db.batch();
|
||
|
||
for (final id in ids) {
|
||
batch.delete(
|
||
activityPreviewPointTable,
|
||
where: 'activity_id = ?',
|
||
whereArgs: [id],
|
||
);
|
||
batch.delete(
|
||
activityTable,
|
||
where: 'id = ?',
|
||
whereArgs: [id],
|
||
);
|
||
}
|
||
|
||
await batch.commit(noResult: true);
|
||
}
|
||
|
||
@override
|
||
Future<void> markActivitiesMissingExcept(Set<String> uris) async {
|
||
final now = DateTime.now().millisecondsSinceEpoch;
|
||
|
||
if (uris.isEmpty) {
|
||
await _db.update(
|
||
activityTable,
|
||
{
|
||
'scan_status': 'missing',
|
||
'last_scan_millis': now,
|
||
'date_modified_millis': now,
|
||
},
|
||
);
|
||
return;
|
||
}
|
||
|
||
final placeholders = List.filled(uris.length, '?').join(',');
|
||
|
||
await _db.rawUpdate(
|
||
'''
|
||
UPDATE $activityTable
|
||
SET scan_status = ?, last_scan_millis = ?, date_modified_millis = ?
|
||
WHERE uri NOT IN ($placeholders)
|
||
''',
|
||
['missing', now, now, ...uris],
|
||
);
|
||
}
|
||
|
||
@override
|
||
Future<Set<ActivityEntry>> searchActivities(String query, {int? limit}) {
|
||
return loadActivities(query: query, limit: limit);
|
||
}
|
||
|
||
@override
|
||
Future<void> clearActivityPreviewPoints(int activityId) async {
|
||
await _db.delete(
|
||
activityPreviewPointTable,
|
||
where: 'activity_id = ?',
|
||
whereArgs: [activityId],
|
||
);
|
||
}
|
||
|
||
@override
|
||
Future<void> saveActivityPreviewPoints(
|
||
int activityId,
|
||
List<ActivityPreviewPoint> points,
|
||
) async {
|
||
await _db.transaction((txn) async {
|
||
await txn.delete(
|
||
activityPreviewPointTable,
|
||
where: 'activity_id = ?',
|
||
whereArgs: [activityId],
|
||
);
|
||
|
||
if (points.isEmpty) return;
|
||
|
||
final batch = txn.batch();
|
||
|
||
for (final point in points) {
|
||
final map = Map<String, Object?>.from(point.toMap());
|
||
map['activity_id'] = activityId;
|
||
|
||
batch.insert(
|
||
activityPreviewPointTable,
|
||
map,
|
||
conflictAlgorithm: ConflictAlgorithm.replace,
|
||
);
|
||
}
|
||
|
||
await batch.commit(noResult: true);
|
||
});
|
||
}
|
||
|
||
@override
|
||
Future<List<ActivityPreviewPoint>> loadActivityPreviewPoints(int activityId) async {
|
||
final rows = await _db.query(
|
||
activityPreviewPointTable,
|
||
where: 'activity_id = ?',
|
||
whereArgs: [activityId],
|
||
orderBy: 'segment_index ASC, point_index ASC',
|
||
);
|
||
|
||
return rows.map(ActivityPreviewPoint.fromMap).toList();
|
||
}
|
||
|
||
@override
|
||
Future<Map<int, List<ActivityPreviewPoint>>> loadActivityPreviewPointsByActivityIds(
|
||
Set<int> activityIds,
|
||
) async {
|
||
final result = <int, List<ActivityPreviewPoint>>{};
|
||
|
||
if (activityIds.isEmpty) return result;
|
||
|
||
final placeholders = List.filled(activityIds.length, '?').join(',');
|
||
|
||
final rows = await _db.rawQuery(
|
||
'''
|
||
SELECT *
|
||
FROM $activityPreviewPointTable
|
||
WHERE activity_id IN ($placeholders)
|
||
ORDER BY activity_id ASC, segment_index ASC, point_index ASC
|
||
''',
|
||
activityIds.toList(),
|
||
);
|
||
|
||
for (final row in rows) {
|
||
final point = ActivityPreviewPoint.fromMap(row);
|
||
result.putIfAbsent(point.activityId, () => <ActivityPreviewPoint>[]).add(point);
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
// convenience methods
|
||
|
||
Future<Set<T>> _getByIds<T>(Set<int> ids, String table, T Function(Map<String, Object?> row) mapRow) async {
|
||
final result = <T>{};
|
||
if (ids.isNotEmpty) {
|
||
final cursor = await _db.queryCursor(table, where: 'id IN (${ids.join(',')})', bufferSize: _queryCursorBufferSize);
|
||
while (await cursor.moveNext()) {
|
||
result.add(mapRow(cursor.current));
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
|
||
Future<void> _ensureFolderColumns(Database db) async {
|
||
Future<Set<String>> columnsOf(String table) async {
|
||
final rows = await db.rawQuery('PRAGMA table_info($table)');
|
||
return rows.map((r) => (r['name'] as String).toLowerCase()).toSet();
|
||
}
|
||
|
||
try {
|
||
await db.execute('''
|
||
CREATE TABLE IF NOT EXISTS folders (
|
||
path TEXT PRIMARY KEY,
|
||
display_name TEXT,
|
||
content_type INTEGER NOT NULL DEFAULT 0,
|
||
file_count INTEGER,
|
||
total_size INTEGER,
|
||
thumb_entry_id INTEGER,
|
||
thumb_date INTEGER,
|
||
album_type INTEGER,
|
||
priority INTEGER,
|
||
is_remote INTEGER,
|
||
user TEXT,
|
||
device_id TEXT,
|
||
hidden INTEGER,
|
||
hybrid_folder INTEGER DEFAULT 0
|
||
)
|
||
''');
|
||
} catch (_) {}
|
||
|
||
final cols = await columnsOf('folders');
|
||
|
||
if (!cols.contains('content_type')) {
|
||
try {
|
||
await db.execute(
|
||
'ALTER TABLE folders ADD COLUMN content_type INTEGER NOT NULL DEFAULT 0',
|
||
);
|
||
} catch (_) {}
|
||
}
|
||
|
||
try {
|
||
await db.execute(
|
||
'CREATE INDEX IF NOT EXISTS idx_folders_content_type_path '
|
||
'ON folders(content_type, path)',
|
||
);
|
||
} catch (_) {}
|
||
|
||
try {
|
||
await db.execute(
|
||
'CREATE INDEX IF NOT EXISTS idx_folders_content_type_remote '
|
||
'ON folders(content_type, is_remote)',
|
||
);
|
||
} catch (_) {}
|
||
}
|
||
|
||
// --------------------------------------------------------
|
||
// MIGRAZIONE "GUARDIA" PER COLONNE REMOTE
|
||
// --------------------------------------------------------
|
||
Future<void> _ensureRemoteColumns(Database db) async {
|
||
Future<Set<String>> _columnsOf(String table) async {
|
||
final rows = await db.rawQuery('PRAGMA table_info($table)');
|
||
return rows.map((r) => (r['name'] as String).toLowerCase()).toSet();
|
||
}
|
||
|
||
final cols = await _columnsOf(entryTable);
|
||
|
||
final alters = <String>[
|
||
if (!cols.contains('origin')) "ALTER TABLE $entryTable ADD COLUMN origin INTEGER",
|
||
if (!cols.contains('trashed')) "ALTER TABLE $entryTable ADD COLUMN trashed INTEGER",
|
||
if (!cols.contains('provider')) "ALTER TABLE $entryTable ADD COLUMN provider TEXT",
|
||
if (!cols.contains('remoteid')) "ALTER TABLE $entryTable ADD COLUMN remoteId TEXT",
|
||
if (!cols.contains('remotepath')) "ALTER TABLE $entryTable ADD COLUMN remotePath TEXT",
|
||
if (!cols.contains('remotethumb1')) "ALTER TABLE $entryTable ADD COLUMN remoteThumb1 TEXT",
|
||
if (!cols.contains('remotethumb2')) "ALTER TABLE $entryTable ADD COLUMN remoteThumb2 TEXT",
|
||
if (!cols.contains('hybrid_file')) "ALTER TABLE $entryTable ADD COLUMN hybrid_file INTEGER DEFAULT 0",
|
||
if (!cols.contains('latitude')) "ALTER TABLE $entryTable ADD COLUMN latitude REAL",
|
||
if (!cols.contains('longitude')) "ALTER TABLE $entryTable ADD COLUMN longitude REAL",
|
||
if (!cols.contains('altitude')) "ALTER TABLE $entryTable ADD COLUMN altitude REAL",
|
||
];
|
||
|
||
for (final sql in alters) {
|
||
try {
|
||
await db.execute(sql);
|
||
} catch (_) {
|
||
// idempotenza: ignora duplicate column name ecc.
|
||
}
|
||
}
|
||
|
||
// Indici utili ai remoti
|
||
try {
|
||
await db.execute("CREATE INDEX IF NOT EXISTS entry_remote_idx ON $entryTable(origin, remoteId)");
|
||
} catch (_) {}
|
||
try {
|
||
await db.execute("CREATE INDEX IF NOT EXISTS entry_origin_trashed_idx ON $entryTable(origin, trashed)");
|
||
} catch (_) {}
|
||
}
|
||
|
||
Future<void> _ensureActivityTables(Database db) async {
|
||
await db.execute('''
|
||
CREATE TABLE IF NOT EXISTS $activityTable (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
||
origin INTEGER NOT NULL DEFAULT 0,
|
||
|
||
uri TEXT NOT NULL UNIQUE,
|
||
relative_path TEXT,
|
||
directory_name TEXT,
|
||
file_name TEXT NOT NULL,
|
||
file_extension TEXT NOT NULL,
|
||
format TEXT NOT NULL,
|
||
|
||
display_name TEXT,
|
||
route_name TEXT,
|
||
title TEXT,
|
||
description TEXT,
|
||
notes TEXT,
|
||
|
||
sport TEXT,
|
||
activity_type TEXT,
|
||
device_manufacturer TEXT,
|
||
device_model TEXT,
|
||
source_app TEXT,
|
||
|
||
start_time_millis INTEGER,
|
||
end_time_millis INTEGER,
|
||
duration_millis INTEGER,
|
||
|
||
distance_meters REAL,
|
||
elevation_gain_meters REAL,
|
||
elevation_loss_meters REAL,
|
||
min_elevation_meters REAL,
|
||
max_elevation_meters REAL,
|
||
|
||
file_distance_meters REAL,
|
||
file_elevation_gain_meters REAL,
|
||
computed_distance_meters REAL,
|
||
computed_elevation_gain_meters REAL,
|
||
|
||
average_speed_mps REAL,
|
||
max_speed_mps REAL,
|
||
average_pace_sec_per_km REAL,
|
||
|
||
average_heart_rate INTEGER,
|
||
max_heart_rate INTEGER,
|
||
average_cadence REAL,
|
||
max_cadence REAL,
|
||
calories REAL,
|
||
power_avg REAL,
|
||
power_max REAL,
|
||
|
||
point_count INTEGER,
|
||
segment_count INTEGER,
|
||
lap_count INTEGER,
|
||
|
||
min_latitude REAL,
|
||
max_latitude REAL,
|
||
min_longitude REAL,
|
||
max_longitude REAL,
|
||
center_latitude REAL,
|
||
center_longitude REAL,
|
||
|
||
size_bytes INTEGER,
|
||
last_modified_millis INTEGER,
|
||
date_added_millis INTEGER NOT NULL,
|
||
date_modified_millis INTEGER NOT NULL,
|
||
last_scan_millis INTEGER,
|
||
|
||
is_favorite INTEGER NOT NULL DEFAULT 0,
|
||
is_hidden INTEGER NOT NULL DEFAULT 0,
|
||
rating INTEGER,
|
||
tags TEXT,
|
||
|
||
scan_status TEXT NOT NULL DEFAULT 'ok',
|
||
scan_error TEXT,
|
||
diagnostics TEXT
|
||
)
|
||
''');
|
||
|
||
Future<Set<String>> activityColumns() async {
|
||
final rows = await db.rawQuery('PRAGMA table_info($activityTable)');
|
||
return rows.map((row) => (row['name'] as String).toLowerCase()).toSet();
|
||
}
|
||
|
||
final activityCols = await activityColumns();
|
||
|
||
if (!activityCols.contains('origin')) {
|
||
try {
|
||
await db.execute(
|
||
'ALTER TABLE $activityTable ADD COLUMN origin INTEGER NOT NULL DEFAULT 0',
|
||
);
|
||
} catch (_) {}
|
||
}
|
||
|
||
await db.execute('''
|
||
CREATE TABLE IF NOT EXISTS $activityPreviewPointTable (
|
||
activity_id INTEGER NOT NULL,
|
||
segment_index INTEGER NOT NULL,
|
||
point_index INTEGER NOT NULL,
|
||
latitude REAL NOT NULL,
|
||
longitude REAL NOT NULL,
|
||
|
||
PRIMARY KEY (activity_id, segment_index, point_index),
|
||
FOREIGN KEY (activity_id) REFERENCES $activityTable(id) ON DELETE CASCADE
|
||
)
|
||
''');
|
||
|
||
await db.execute(
|
||
'CREATE INDEX IF NOT EXISTS idx_activity_origin ON $activityTable(origin)',
|
||
);
|
||
|
||
await db.execute(
|
||
'CREATE INDEX IF NOT EXISTS idx_activity_start_time ON $activityTable(start_time_millis)',
|
||
);
|
||
await db.execute(
|
||
'CREATE INDEX IF NOT EXISTS idx_activity_sport ON $activityTable(sport)',
|
||
);
|
||
await db.execute(
|
||
'CREATE INDEX IF NOT EXISTS idx_activity_format ON $activityTable(format)',
|
||
);
|
||
await db.execute(
|
||
'CREATE INDEX IF NOT EXISTS idx_activity_distance ON $activityTable(distance_meters)',
|
||
);
|
||
await db.execute(
|
||
'CREATE INDEX IF NOT EXISTS idx_activity_elevation_gain ON $activityTable(elevation_gain_meters)',
|
||
);
|
||
await db.execute(
|
||
'CREATE INDEX IF NOT EXISTS idx_activity_duration ON $activityTable(duration_millis)',
|
||
);
|
||
await db.execute(
|
||
'CREATE INDEX IF NOT EXISTS idx_activity_directory ON $activityTable(relative_path)',
|
||
);
|
||
await db.execute(
|
||
'CREATE INDEX IF NOT EXISTS idx_activity_favorite ON $activityTable(is_favorite)',
|
||
);
|
||
await db.execute(
|
||
'CREATE INDEX IF NOT EXISTS idx_activity_device ON $activityTable(device_manufacturer, device_model)',
|
||
);
|
||
await db.execute(
|
||
'CREATE INDEX IF NOT EXISTS idx_activity_scan_status ON $activityTable(scan_status)',
|
||
);
|
||
await db.execute(
|
||
'CREATE INDEX IF NOT EXISTS idx_activity_preview_activity ON $activityPreviewPointTable(activity_id)',
|
||
);
|
||
}
|
||
}
|