video_progress_cache.dart
4.07 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
import 'package:sqflite/sqflite.dart';
import 'package:wow_english/common/core/user_util.dart';
class VideoProgressCacheEntry {
const VideoProgressCacheEntry({
required this.progressSeconds,
required this.durationSeconds,
});
final int progressSeconds;
final int durationSeconds;
}
/// 使用 SQLite 按用户保存视频播放进度。
class VideoProgressCache {
static const _databaseName = 'video_progress.db';
static const _databaseVersion = 1;
static const _tableName = 'video_progress';
static const _maxRecordCount = 100;
static Database? _database;
static Future<Database>? _openingDatabase;
static Future<void> _writeQueue = Future<void>.value();
static Future<VideoProgressCacheEntry?> get(int videoId) async {
final userId = _currentUserId;
if (userId == null) return null;
await _writeQueue;
final database = await _getDatabase();
final rows = await database.query(
_tableName,
columns: const ['progress_seconds', 'duration_seconds'],
where: 'user_id = ? AND video_id = ?',
whereArgs: [userId, videoId],
limit: 1,
);
if (rows.isEmpty) return null;
return VideoProgressCacheEntry(
progressSeconds: _toInt(rows.first['progress_seconds']),
durationSeconds: _toInt(rows.first['duration_seconds']),
);
}
static Future<void> save({
required int videoId,
required int progressSeconds,
required int durationSeconds,
}) async {
final userId = _currentUserId;
if (userId == null) return;
await _enqueueWrite(() async {
final database = await _getDatabase();
await database.transaction((transaction) async {
await transaction.insert(
_tableName,
{
'user_id': userId,
'video_id': videoId,
'progress_seconds': progressSeconds,
'duration_seconds': durationSeconds,
'updated_at': DateTime.now().millisecondsSinceEpoch,
},
conflictAlgorithm: ConflictAlgorithm.replace,
);
await transaction.rawDelete(
'''
DELETE FROM $_tableName
WHERE user_id = ? AND video_id NOT IN (
SELECT video_id FROM $_tableName
WHERE user_id = ?
ORDER BY updated_at DESC
LIMIT ?
)
''',
[userId, userId, _maxRecordCount],
);
});
});
}
static Future<void> remove(int videoId) async {
final userId = _currentUserId;
if (userId == null) return;
await _enqueueWrite(() async {
final database = await _getDatabase();
await database.delete(
_tableName,
where: 'user_id = ? AND video_id = ?',
whereArgs: [userId, videoId],
);
});
}
static int? get _currentUserId => UserUtil.getUser()?.id;
static Future<void> _enqueueWrite(Future<void> Function() operation) {
final future = _writeQueue.then((_) => operation());
_writeQueue = future.then<void>((_) {}, onError: (_, __) {});
return future;
}
static Future<Database> _getDatabase() async {
final database = _database;
if (database != null && database.isOpen) return database;
final openingDatabase = _openingDatabase;
if (openingDatabase != null) return openingDatabase;
final future = _openDatabase();
_openingDatabase = future;
try {
final result = await future;
_database = result;
return result;
} finally {
_openingDatabase = null;
}
}
static Future<Database> _openDatabase() async {
final databasesPath = await getDatabasesPath();
return openDatabase(
'$databasesPath/$_databaseName',
version: _databaseVersion,
onCreate: (database, _) => database.execute('''
CREATE TABLE $_tableName (
user_id INTEGER NOT NULL,
video_id INTEGER NOT NULL,
progress_seconds INTEGER NOT NULL,
duration_seconds INTEGER NOT NULL,
updated_at INTEGER NOT NULL,
PRIMARY KEY (user_id, video_id)
)
'''),
);
}
static int _toInt(Object? value) =>
value is num ? value.toInt() : int.tryParse('$value') ?? 0;
}