mirror of
https://github.com/zarzet/SpotiFLAC-Mobile.git
synced 2026-11-21 20:00:49 +01:00
378 lines
13 KiB
Dart
378 lines
13 KiB
Dart
import 'package:flutter_test/flutter_test.dart';
|
|
import 'package:sqflite/sqflite.dart';
|
|
import 'package:mornye/models/library_database_models.dart';
|
|
import 'package:mornye/services/library_row_mapper.dart';
|
|
import 'package:mornye/services/library_schema.dart';
|
|
|
|
import 'support/sqlite_process_database.dart';
|
|
|
|
Map<String, dynamic> _track(String id, {String isrc = 'same'}) =>
|
|
LocalLibraryItem(
|
|
id: id,
|
|
trackName: ' Song ',
|
|
artistName: 'ARTIST',
|
|
albumName: 'Album',
|
|
filePath: '/music/$id.flac',
|
|
scannedAt: DateTime.utc(2026),
|
|
isrc: isrc,
|
|
trackNumber: 1,
|
|
totalTracks: 2,
|
|
hasLyrics: true,
|
|
hasReplayGain: true,
|
|
).toJson();
|
|
|
|
void main() {
|
|
TestWidgetsFlutterBinding.ensureInitialized();
|
|
late SqliteProcessDatabase db;
|
|
setUp(() async {
|
|
db = await SqliteProcessDatabase.open();
|
|
await db.execute('PRAGMA recursive_triggers = ON');
|
|
});
|
|
tearDown(() => db.close());
|
|
|
|
test(
|
|
'Windows alias migration hides duplicates without deleting records',
|
|
() async {
|
|
await LibrarySchema.create(db, LibrarySchema.version);
|
|
for (final entry in {
|
|
'a': r'\\?\C:\Music\song.flac',
|
|
'b': r'C:\Music\song.flac',
|
|
'c': r'C:\Music\song.opus',
|
|
'd': r'\\?\UNC\server\share\song.flac',
|
|
'e': r'\\server\share\song.flac',
|
|
}.entries) {
|
|
await db.insert(
|
|
'library',
|
|
LibraryRowMapper.encode({
|
|
..._track(entry.key),
|
|
'filePath': entry.value,
|
|
}),
|
|
);
|
|
}
|
|
final original = await db.query('library');
|
|
for (var attempt = 0; attempt < 2; attempt++) {
|
|
await LibrarySchema.upgrade(db, 21, LibrarySchema.version);
|
|
expect(await db.query('library'), original);
|
|
final visible = await db.query(
|
|
LibrarySchema.visibleView,
|
|
orderBy: 'id',
|
|
);
|
|
expect(visible.map((row) => row['id']), ['a', 'c', 'd']);
|
|
}
|
|
// An unavailable winning source must not hide an available alias.
|
|
await db.insert('library_sources', {
|
|
'id': 'offline',
|
|
'path': r'C:\Music',
|
|
'display_name': 'Offline',
|
|
'enabled': 1,
|
|
'available': 0,
|
|
});
|
|
await db.update(
|
|
'library',
|
|
{'source_id': 'offline'},
|
|
where: 'id = ?',
|
|
whereArgs: ['a'],
|
|
);
|
|
final visible = await db.query(LibrarySchema.visibleView, orderBy: 'id');
|
|
expect(visible.map((row) => row['id']), ['b', 'c', 'd']);
|
|
},
|
|
);
|
|
|
|
test(
|
|
'v20 index migration preserves rows and can run again after a downgrade',
|
|
() async {
|
|
await LibrarySchema.create(db, LibrarySchema.version);
|
|
const removed = {
|
|
'idx_library_track_artist': 'track_name, artist_name',
|
|
'idx_library_album': 'album_name, album_artist',
|
|
'idx_library_track_norm': 'track_name_norm',
|
|
'idx_library_artist_norm': 'artist_name_norm',
|
|
'idx_library_album_norm': 'album_name_norm',
|
|
'idx_library_scanned_at': 'scanned_at',
|
|
};
|
|
await db.execute('DROP INDEX idx_library_album_artist_norm');
|
|
await db.execute('DROP TABLE ${LibrarySchema.stateTable}');
|
|
for (final MapEntry(:key, :value) in removed.entries) {
|
|
await db.execute('CREATE INDEX $key ON library($value)');
|
|
}
|
|
await db.insert('library', LibraryRowMapper.encode(_track('persisted')));
|
|
final rows = await db.query('library');
|
|
final summary = await db.query(LibrarySchema.lookupSummaryTable);
|
|
|
|
for (var attempt = 0; attempt < 2; attempt++) {
|
|
await LibrarySchema.upgrade(db, 20, LibrarySchema.version);
|
|
expect(await db.query('library'), rows);
|
|
expect(await db.query(LibrarySchema.lookupSummaryTable), summary);
|
|
expect(
|
|
(await db.rawQuery(
|
|
"SELECT name FROM sqlite_master WHERE type = 'index' AND tbl_name = 'library'",
|
|
)).map((row) => row['name']),
|
|
isNot(anyElement(isIn(removed.keys))),
|
|
);
|
|
final plan = await db.rawQuery(
|
|
'EXPLAIN QUERY PLAN SELECT id FROM library WHERE album_artist_norm = ?',
|
|
['artist'],
|
|
);
|
|
expect(
|
|
plan.single['detail'],
|
|
contains('idx_library_album_artist_norm'),
|
|
);
|
|
// Released network ingestion names this index explicitly.
|
|
expect(
|
|
await db.rawQuery(
|
|
'SELECT id FROM library INDEXED BY idx_library_file_path WHERE file_path = ?',
|
|
['/music/persisted.flac'],
|
|
),
|
|
[
|
|
{'id': 'persisted'},
|
|
],
|
|
);
|
|
expect(await db.query(LibrarySchema.stateTable), isEmpty);
|
|
for (final MapEntry(:key, :value) in removed.entries) {
|
|
await db.execute('CREATE INDEX $key ON library($value)');
|
|
}
|
|
}
|
|
},
|
|
);
|
|
|
|
test(
|
|
'v19 upgrade rescans local Ogg gain tags while preserving indexed fields',
|
|
() async {
|
|
await LibrarySchema.create(db, 19);
|
|
final songs = [
|
|
('opus', 'content://media/audio/1', 'opus', 4, 3),
|
|
('vorbis', '/music/song.ogg', 'vorbis', 4, 3),
|
|
('suffix', '/music/song.OPUS', null, 4, 3),
|
|
('remote', 'network://peer/song.opus', 'opus', 4, 4),
|
|
('flac', '/music/song.flac', 'flac', 4, 4),
|
|
('legacy', '/music/legacy.opus', 'opus', 0, 0),
|
|
];
|
|
for (final (id, path, format, version, _) in songs) {
|
|
await db.insert('library', {
|
|
...LibraryRowMapper.encode(_track(id)),
|
|
'file_path': path,
|
|
'format': format,
|
|
'file_mod_time': 1234,
|
|
'audio_metadata_scan_version': version,
|
|
});
|
|
}
|
|
await LibrarySchema.upgrade(db, 19, LibrarySchema.version);
|
|
for (final (id, path, _, _, version) in songs) {
|
|
final row = (await db.query(
|
|
'library',
|
|
where: 'id = ?',
|
|
whereArgs: [id],
|
|
)).single;
|
|
expect(row['audio_metadata_scan_version'], version, reason: id);
|
|
expect(row['file_path'], path, reason: id);
|
|
expect(row['file_mod_time'], 1234, reason: id);
|
|
expect(row['has_lyrics'], 1, reason: id);
|
|
expect(row['has_replaygain'], 1, reason: id);
|
|
expect(
|
|
libraryIncrementalSnapshotModTime(
|
|
storedModTime: 1234,
|
|
storedScanVersion: version,
|
|
),
|
|
version < libraryAudioMetadataScanVersion ? -1 : 1234,
|
|
reason: id,
|
|
);
|
|
}
|
|
// A completed scan writes the current version, so later upgrades do not
|
|
// repeatedly invalidate these songs.
|
|
await db.update('library', {'audio_metadata_scan_version': 4});
|
|
await LibrarySchema.upgrade(
|
|
db,
|
|
LibrarySchema.version,
|
|
LibrarySchema.version,
|
|
);
|
|
expect(
|
|
(await db.query(
|
|
'library',
|
|
)).every((row) => row['audio_metadata_scan_version'] == 4),
|
|
isTrue,
|
|
);
|
|
},
|
|
);
|
|
|
|
test(
|
|
'fresh schema maintains lookup counts across replace, source move and deletion',
|
|
() async {
|
|
await LibrarySchema.create(db, LibrarySchema.version);
|
|
for (final id in ['a', 'b']) {
|
|
await db.insert(
|
|
'library',
|
|
LibraryRowMapper.encode(_track(id)),
|
|
conflictAlgorithm: ConflictAlgorithm.replace,
|
|
);
|
|
}
|
|
Future<List<Map<String, Object?>>> counts() => db.query(
|
|
LibrarySchema.lookupSummaryTable,
|
|
orderBy: 'source_id, kind, value',
|
|
);
|
|
expect((await counts()).map((row) => row['ref_count']), [2, 2]);
|
|
await db.insert(
|
|
'library',
|
|
LibraryRowMapper.encode(_track('a', isrc: 'new')),
|
|
conflictAlgorithm: ConflictAlgorithm.replace,
|
|
);
|
|
expect((await counts()).map((row) => row['ref_count']), [1, 1, 2]);
|
|
await db.insert('library_sources', {
|
|
'id': 'nas',
|
|
'path': 'network://nas/music',
|
|
'display_name': 'NAS',
|
|
});
|
|
await db.update(
|
|
'library',
|
|
{'source_id': 'nas'},
|
|
where: 'id = ?',
|
|
whereArgs: ['a'],
|
|
);
|
|
expect((await db.query(LibrarySchema.visibleView)).length, 2);
|
|
await db.update(
|
|
'library_sources',
|
|
{'available': 0},
|
|
where: 'id = ?',
|
|
whereArgs: ['nas'],
|
|
);
|
|
expect((await db.query(LibrarySchema.visibleView)).single['id'], 'b');
|
|
await db.delete('library', where: 'id = ?', whereArgs: ['a']);
|
|
expect((await counts()).map((row) => row['ref_count']), [1, 1]);
|
|
await db.delete('library');
|
|
expect(await counts(), isEmpty);
|
|
},
|
|
);
|
|
|
|
test(
|
|
'v1 upgrade preserves tracks and backfills current search and path identities',
|
|
() async {
|
|
await db.execute('''CREATE TABLE library (
|
|
id TEXT PRIMARY KEY, track_name TEXT NOT NULL, artist_name TEXT NOT NULL,
|
|
album_name TEXT NOT NULL, album_artist TEXT, file_path TEXT NOT NULL UNIQUE,
|
|
scanned_at TEXT NOT NULL, isrc TEXT, track_number INTEGER, disc_number INTEGER,
|
|
duration INTEGER, release_date TEXT, bit_depth INTEGER, sample_rate INTEGER,
|
|
genre TEXT, format TEXT
|
|
)''');
|
|
await db.insert('library', {
|
|
'id': 'old',
|
|
'track_name': ' Song ',
|
|
'artist_name': 'ARTIST',
|
|
'album_name': 'Album',
|
|
'file_path':
|
|
'content://example.documents/document/nas%3AMusic%2FSong.flac',
|
|
'scanned_at': '2026-01-01T00:00:00.000Z',
|
|
'isrc': 'legacy-isrc',
|
|
'genre': ' Pop ',
|
|
});
|
|
await LibrarySchema.upgrade(db, 1, LibrarySchema.version);
|
|
final row = (await db.query('library')).single;
|
|
expect(row['source_id'], LocalLibraryItem.legacySourceId);
|
|
expect(row['search_text'], 'song artist album artist');
|
|
expect(row['match_key'], 'song|artist');
|
|
expect(row['sort_genre'], 'pop');
|
|
expect(row['audio_metadata_scan_version'], 0);
|
|
expect(row['has_lyrics'], 0);
|
|
expect(row['has_replaygain'], 0);
|
|
expect(
|
|
(await db.query(
|
|
'library_path_keys',
|
|
)).every((key) => key['item_id'] == 'old'),
|
|
isTrue,
|
|
);
|
|
expect(await db.query('library_path_keys'), isNotEmpty);
|
|
expect((await db.query(LibrarySchema.visibleView)).single['id'], 'old');
|
|
expect(
|
|
(await db.query(
|
|
LibrarySchema.lookupSummaryTable,
|
|
)).map((row) => row['ref_count']),
|
|
[1, 1],
|
|
);
|
|
},
|
|
);
|
|
|
|
test(
|
|
'v16 upgrade repairs existing lookup counts and replacement triggers',
|
|
() async {
|
|
await LibrarySchema.create(db, 16);
|
|
await db.execute('DROP TRIGGER library_lookup_insert_isrc');
|
|
await db.execute('''
|
|
CREATE TRIGGER library_lookup_insert_isrc AFTER INSERT ON library
|
|
WHEN NEW.isrc IS NOT NULL AND NEW.isrc != ''
|
|
BEGIN
|
|
INSERT OR IGNORE INTO library_lookup_summary(source_id, kind, value, ref_count)
|
|
VALUES (NEW.source_id, 'isrc', NEW.isrc, 0);
|
|
UPDATE library_lookup_summary SET ref_count = ref_count + 1
|
|
WHERE source_id = NEW.source_id AND kind = 'isrc' AND value = NEW.isrc;
|
|
END
|
|
''');
|
|
for (final id in ['a', 'b']) {
|
|
await db.insert(
|
|
'library',
|
|
LibraryRowMapper.encode(_track(id)),
|
|
conflictAlgorithm: ConflictAlgorithm.replace,
|
|
);
|
|
}
|
|
Future<List<Map<String, Object?>>> counts() =>
|
|
db.query(LibrarySchema.lookupSummaryTable, orderBy: 'kind');
|
|
expect((await counts()).map((row) => row['ref_count']), [1, 2]);
|
|
await LibrarySchema.upgrade(db, 16, LibrarySchema.version);
|
|
expect((await counts()).map((row) => row['ref_count']), [2, 2]);
|
|
await db.insert(
|
|
'library',
|
|
LibraryRowMapper.encode(_track('a')),
|
|
conflictAlgorithm: ConflictAlgorithm.replace,
|
|
);
|
|
expect((await counts()).map((row) => row['ref_count']), [2, 2]);
|
|
await db.delete('library', where: 'id = ?', whereArgs: ['a']);
|
|
expect((await counts()).map((row) => row['ref_count']), [1, 1]);
|
|
expect((await db.query('library')).single['id'], 'b');
|
|
},
|
|
);
|
|
|
|
test(
|
|
'v17 upgrade retains existing rows and adds network file versions',
|
|
() async {
|
|
await LibrarySchema.create(db, 17);
|
|
await db.execute('ALTER TABLE library DROP COLUMN network_file_version');
|
|
await db.insert('library', {
|
|
'id': 'old',
|
|
'track_name': 'Song',
|
|
'artist_name': 'Artist',
|
|
'album_name': 'Album',
|
|
'file_path': 'network://nas/song.flac',
|
|
'scanned_at': '2026-10-19',
|
|
});
|
|
await LibrarySchema.upgrade(db, 17, LibrarySchema.version);
|
|
expect(
|
|
(await db.query('library')).single['network_file_version'],
|
|
isNull,
|
|
);
|
|
expect((await db.query('library')).single['id'], 'old');
|
|
},
|
|
);
|
|
|
|
test('row mapping round-trips metadata and preserves unknown scan flags', () {
|
|
final track = _track('full');
|
|
track['networkFileVersion'] = 'network-fingerprint';
|
|
expect(LibraryRowMapper.decode(LibraryRowMapper.encode(track)), track);
|
|
final filename = LibraryRowMapper.encode({
|
|
...track,
|
|
'sourceId': 'nas',
|
|
'metadataFromFilename': true,
|
|
'hasReplayGain': false,
|
|
});
|
|
expect(filename['source_id'], 'nas');
|
|
expect(filename['audio_metadata_scan_version'], 0);
|
|
expect(filename['has_replaygain'], 0);
|
|
final tagged = LibraryRowMapper.encode({
|
|
...track,
|
|
'hasReplayGain': false,
|
|
'replaygain_track_gain': '-4.5 dB',
|
|
'explicit': 1,
|
|
'audioMetadataScanVersion': 3,
|
|
});
|
|
expect(tagged['has_replaygain'], 1);
|
|
expect(tagged['audio_metadata_scan_version'], 3);
|
|
expect(LibraryRowMapper.decode(tagged)['explicit'], true);
|
|
});
|
|
}
|