files_db.dart 29 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916
  1. import 'dart:io';
  2. import 'package:logging/logging.dart';
  3. import 'package:path/path.dart';
  4. import 'package:path_provider/path_provider.dart';
  5. import 'package:photos/models/backup_status.dart';
  6. import 'package:photos/models/file.dart';
  7. import 'package:photos/models/file_load_result.dart';
  8. import 'package:photos/models/file_type.dart';
  9. import 'package:photos/models/location.dart';
  10. import 'package:sqflite/sqflite.dart';
  11. import 'package:sqflite_migration/sqflite_migration.dart';
  12. class FilesDB {
  13. /*
  14. Note: columnUploadedFileID and columnCollectionID have to be compared against
  15. both NULL and -1 because older clients might have entries where the DEFAULT
  16. was unset, and a migration script to set the DEFAULT would break in case of
  17. duplicate entries for un-uploaded files that were created due to a collision
  18. in background and foreground syncs.
  19. */
  20. static final _databaseName = "ente.files.db";
  21. static final Logger _logger = Logger("FilesDB");
  22. static final table = 'files';
  23. static final tempTable = 'temp_files';
  24. static final columnGeneratedID = '_id';
  25. static final columnUploadedFileID = 'uploaded_file_id';
  26. static final columnOwnerID = 'owner_id';
  27. static final columnCollectionID = 'collection_id';
  28. static final columnLocalID = 'local_id';
  29. static final columnTitle = 'title';
  30. static final columnDeviceFolder = 'device_folder';
  31. static final columnLatitude = 'latitude';
  32. static final columnLongitude = 'longitude';
  33. static final columnFileType = 'file_type';
  34. static final columnIsDeleted = 'is_deleted';
  35. static final columnCreationTime = 'creation_time';
  36. static final columnModificationTime = 'modification_time';
  37. static final columnUpdationTime = 'updation_time';
  38. static final columnEncryptedKey = 'encrypted_key';
  39. static final columnKeyDecryptionNonce = 'key_decryption_nonce';
  40. static final columnFileDecryptionHeader = 'file_decryption_header';
  41. static final columnThumbnailDecryptionHeader = 'thumbnail_decryption_header';
  42. static final columnMetadataDecryptionHeader = 'metadata_decryption_header';
  43. static final initializationScript = [...createTable(table)];
  44. static final migrationScripts = [
  45. ...alterDeviceFolderToAllowNULL(),
  46. ...alterTimestampColumnTypes(),
  47. ...addIndices(),
  48. ];
  49. final dbConfig = MigrationConfig(
  50. initializationScript: initializationScript,
  51. migrationScripts: migrationScripts);
  52. // make this a singleton class
  53. FilesDB._privateConstructor();
  54. static final FilesDB instance = FilesDB._privateConstructor();
  55. // only have a single app-wide reference to the database
  56. static Future<Database> _dbFuture;
  57. Future<Database> get database async {
  58. // lazily instantiate the db the first time it is accessed
  59. _dbFuture ??= _initDatabase();
  60. return _dbFuture;
  61. }
  62. // this opens the database (and creates it if it doesn't exist)
  63. Future<Database> _initDatabase() async {
  64. Directory documentsDirectory = await getApplicationDocumentsDirectory();
  65. String path = join(documentsDirectory.path, _databaseName);
  66. _logger.info("DB path " + path);
  67. return await openDatabaseWithMigration(path, dbConfig);
  68. }
  69. // SQL code to create the database table
  70. static List<String> createTable(String tableName) {
  71. return [
  72. '''
  73. CREATE TABLE $tableName (
  74. $columnGeneratedID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  75. $columnLocalID TEXT,
  76. $columnUploadedFileID INTEGER DEFAULT -1,
  77. $columnOwnerID INTEGER,
  78. $columnCollectionID INTEGER DEFAULT -1,
  79. $columnTitle TEXT NOT NULL,
  80. $columnDeviceFolder TEXT,
  81. $columnLatitude REAL,
  82. $columnLongitude REAL,
  83. $columnFileType INTEGER,
  84. $columnModificationTime TEXT NOT NULL,
  85. $columnEncryptedKey TEXT,
  86. $columnKeyDecryptionNonce TEXT,
  87. $columnFileDecryptionHeader TEXT,
  88. $columnThumbnailDecryptionHeader TEXT,
  89. $columnMetadataDecryptionHeader TEXT,
  90. $columnIsDeleted INTEGER DEFAULT 0,
  91. $columnCreationTime TEXT NOT NULL,
  92. $columnUpdationTime TEXT,
  93. UNIQUE($columnLocalID, $columnUploadedFileID, $columnCollectionID)
  94. );
  95. ''',
  96. ];
  97. }
  98. static List<String> addIndices() {
  99. return [
  100. '''
  101. CREATE INDEX IF NOT EXISTS collection_id_index ON $table($columnCollectionID);
  102. ''',
  103. '''
  104. CREATE INDEX IF NOT EXISTS device_folder_index ON $table($columnDeviceFolder);
  105. ''',
  106. '''
  107. CREATE INDEX IF NOT EXISTS creation_time_index ON $table($columnCreationTime);
  108. ''',
  109. '''
  110. CREATE INDEX IF NOT EXISTS updation_time_index ON $table($columnUpdationTime);
  111. '''
  112. ];
  113. }
  114. static List<String> alterDeviceFolderToAllowNULL() {
  115. return [
  116. ...createTable(tempTable),
  117. '''
  118. INSERT INTO $tempTable
  119. SELECT *
  120. FROM $table;
  121. DROP TABLE $table;
  122. ALTER TABLE $tempTable
  123. RENAME TO $table;
  124. '''
  125. ];
  126. }
  127. static List<String> alterTimestampColumnTypes() {
  128. return [
  129. '''
  130. DROP TABLE IF EXISTS $tempTable;
  131. ''',
  132. '''
  133. CREATE TABLE $tempTable (
  134. $columnGeneratedID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  135. $columnLocalID TEXT,
  136. $columnUploadedFileID INTEGER DEFAULT -1,
  137. $columnOwnerID INTEGER,
  138. $columnCollectionID INTEGER DEFAULT -1,
  139. $columnTitle TEXT NOT NULL,
  140. $columnDeviceFolder TEXT,
  141. $columnLatitude REAL,
  142. $columnLongitude REAL,
  143. $columnFileType INTEGER,
  144. $columnModificationTime INTEGER NOT NULL,
  145. $columnEncryptedKey TEXT,
  146. $columnKeyDecryptionNonce TEXT,
  147. $columnFileDecryptionHeader TEXT,
  148. $columnThumbnailDecryptionHeader TEXT,
  149. $columnMetadataDecryptionHeader TEXT,
  150. $columnCreationTime INTEGER NOT NULL,
  151. $columnUpdationTime INTEGER,
  152. UNIQUE($columnLocalID, $columnUploadedFileID, $columnCollectionID)
  153. );
  154. ''',
  155. '''
  156. INSERT INTO $tempTable
  157. SELECT
  158. $columnGeneratedID,
  159. $columnLocalID,
  160. $columnUploadedFileID,
  161. $columnOwnerID,
  162. $columnCollectionID,
  163. $columnTitle,
  164. $columnDeviceFolder,
  165. $columnLatitude,
  166. $columnLongitude,
  167. $columnFileType,
  168. CAST($columnModificationTime AS INTEGER),
  169. $columnEncryptedKey,
  170. $columnKeyDecryptionNonce,
  171. $columnFileDecryptionHeader,
  172. $columnThumbnailDecryptionHeader,
  173. $columnMetadataDecryptionHeader,
  174. CAST($columnCreationTime AS INTEGER),
  175. CAST($columnUpdationTime AS INTEGER)
  176. FROM $table;
  177. ''',
  178. '''
  179. DROP TABLE $table;
  180. ''',
  181. '''
  182. ALTER TABLE $tempTable
  183. RENAME TO $table;
  184. ''',
  185. ];
  186. }
  187. Future<void> clearTable() async {
  188. final db = await instance.database;
  189. await db.delete(table);
  190. }
  191. Future<void> insertMultiple(List<File> files) async {
  192. final startTime = DateTime.now();
  193. final db = await instance.database;
  194. var batch = db.batch();
  195. int batchCounter = 0;
  196. for (File file in files) {
  197. if (batchCounter == 400) {
  198. await batch.commit(noResult: true);
  199. batch = db.batch();
  200. batchCounter = 0;
  201. }
  202. batch.insert(
  203. table,
  204. _getRowForFile(file),
  205. conflictAlgorithm: ConflictAlgorithm.replace,
  206. );
  207. batchCounter++;
  208. }
  209. await batch.commit(noResult: true);
  210. final endTime = DateTime.now();
  211. final duration = Duration(
  212. microseconds:
  213. endTime.microsecondsSinceEpoch - startTime.microsecondsSinceEpoch);
  214. _logger.info("Batch insert of " +
  215. files.length.toString() +
  216. " took " +
  217. duration.inMilliseconds.toString() +
  218. "ms.");
  219. }
  220. Future<int> insert(File file) async {
  221. final db = await instance.database;
  222. return db.insert(
  223. table,
  224. _getRowForFile(file),
  225. conflictAlgorithm: ConflictAlgorithm.replace,
  226. );
  227. }
  228. Future<File> getFile(int generatedID) async {
  229. final db = await instance.database;
  230. final results = await db.query(table,
  231. where: '$columnGeneratedID = ?', whereArgs: [generatedID]);
  232. if (results.isEmpty) {
  233. return null;
  234. }
  235. return _convertToFiles(results)[0];
  236. }
  237. Future<File> getUploadedFile(int uploadedID, int collectionID) async {
  238. final db = await instance.database;
  239. final results = await db.query(
  240. table,
  241. where: '$columnUploadedFileID = ? AND $columnCollectionID = ?',
  242. whereArgs: [
  243. uploadedID,
  244. collectionID,
  245. ],
  246. );
  247. if (results.isEmpty) {
  248. return null;
  249. }
  250. return _convertToFiles(results)[0];
  251. }
  252. Future<Set<int>> getUploadedFileIDs(int collectionID) async {
  253. final db = await instance.database;
  254. final results = await db.query(
  255. table,
  256. columns: [columnUploadedFileID],
  257. where: '$columnCollectionID = ?',
  258. whereArgs: [
  259. collectionID,
  260. ],
  261. );
  262. final ids = <int>{};
  263. for (final result in results) {
  264. ids.add(result[columnUploadedFileID]);
  265. }
  266. return ids;
  267. }
  268. Future<BackedUpFileIDs> getBackedUpIDs() async {
  269. final db = await instance.database;
  270. final results = await db.query(
  271. table,
  272. columns: [columnLocalID, columnUploadedFileID],
  273. where:
  274. '$columnLocalID IS NOT NULL AND ($columnUploadedFileID IS NOT NULL AND $columnUploadedFileID IS NOT -1)',
  275. );
  276. final localIDs = <String>{};
  277. final uploadedIDs = <int>{};
  278. for (final result in results) {
  279. localIDs.add(result[columnLocalID]);
  280. uploadedIDs.add(result[columnUploadedFileID]);
  281. }
  282. return BackedUpFileIDs(localIDs.toList(), uploadedIDs.toList());
  283. }
  284. Future<FileLoadResult> getAllUploadedFiles(int startTime, int endTime,
  285. {int limit, bool asc}) async {
  286. final db = await instance.database;
  287. final order = (asc ?? false ? 'ASC' : 'DESC');
  288. final results = await db.query(
  289. table,
  290. where:
  291. '$columnCreationTime >= ? AND $columnCreationTime <= ? AND ($columnCollectionID IS NOT NULL AND $columnCollectionID IS NOT -1)',
  292. whereArgs: [startTime, endTime],
  293. orderBy:
  294. '$columnCreationTime ' + order + ', $columnModificationTime ' + order,
  295. limit: limit,
  296. );
  297. final files = _convertToFiles(results);
  298. return FileLoadResult(files, files.length == limit);
  299. }
  300. Future<FileLoadResult> getAllLocalAndUploadedFiles(int startTime, int endTime,
  301. {int limit, bool asc}) async {
  302. final db = await instance.database;
  303. final order = (asc ?? false ? 'ASC' : 'DESC');
  304. final results = await db.query(
  305. table,
  306. where:
  307. '$columnCreationTime >= ? AND $columnCreationTime <= ? AND ($columnLocalID IS NOT NULL OR ($columnCollectionID IS NOT NULL AND $columnCollectionID IS NOT -1))',
  308. whereArgs: [startTime, endTime],
  309. orderBy:
  310. '$columnCreationTime ' + order + ', $columnModificationTime ' + order,
  311. limit: limit,
  312. );
  313. final files = _convertToFiles(results);
  314. return FileLoadResult(files, files.length == limit);
  315. }
  316. Future<FileLoadResult> getImportantFiles(
  317. int startTime, int endTime, List<String> paths,
  318. {int limit, bool asc}) async {
  319. final db = await instance.database;
  320. String inParam = "";
  321. for (final path in paths) {
  322. inParam += "'" + path.replaceAll("'", "''") + "',";
  323. }
  324. inParam = inParam.substring(0, inParam.length - 1);
  325. final order = (asc ?? false ? 'ASC' : 'DESC');
  326. final results = await db.query(
  327. table,
  328. where:
  329. '$columnCreationTime >= ? AND $columnCreationTime <= ? AND (($columnLocalID IS NOT NULL AND $columnDeviceFolder IN ($inParam)) OR ($columnCollectionID IS NOT NULL AND $columnCollectionID IS NOT -1))',
  330. whereArgs: [startTime, endTime],
  331. orderBy:
  332. '$columnCreationTime ' + order + ', $columnModificationTime ' + order,
  333. limit: limit,
  334. );
  335. final uploadedFileIDs = <int>{};
  336. final files = _convertToFiles(results);
  337. final List<File> deduplicatedFiles = [];
  338. for (final file in files) {
  339. final id = file.uploadedFileID;
  340. if (id != null && id != -1 && uploadedFileIDs.contains(id)) {
  341. continue;
  342. }
  343. uploadedFileIDs.add(id);
  344. deduplicatedFiles.add(file);
  345. }
  346. return FileLoadResult(deduplicatedFiles, files.length == limit);
  347. }
  348. Future<FileLoadResult> getFilesInCollection(
  349. int collectionID, int startTime, int endTime,
  350. {int limit, bool asc}) async {
  351. final db = await instance.database;
  352. final order = (asc ?? false ? 'ASC' : 'DESC');
  353. final results = await db.query(
  354. table,
  355. where:
  356. '$columnCollectionID = ? AND $columnCreationTime >= ? AND $columnCreationTime <= ?',
  357. whereArgs: [collectionID, startTime, endTime],
  358. orderBy:
  359. '$columnCreationTime ' + order + ', $columnModificationTime ' + order,
  360. limit: limit,
  361. );
  362. final files = _convertToFiles(results);
  363. _logger.info("Fetched " + files.length.toString() + " files");
  364. return FileLoadResult(files, files.length == limit);
  365. }
  366. Future<FileLoadResult> getFilesInPath(String path, int startTime, int endTime,
  367. {int limit, bool asc}) async {
  368. final db = await instance.database;
  369. final order = (asc ?? false ? 'ASC' : 'DESC');
  370. final results = await db.query(
  371. table,
  372. where:
  373. '$columnDeviceFolder = ? AND $columnCreationTime >= ? AND $columnCreationTime <= ? AND $columnLocalID IS NOT NULL',
  374. whereArgs: [path, startTime, endTime],
  375. orderBy:
  376. '$columnCreationTime ' + order + ', $columnModificationTime ' + order,
  377. groupBy: columnLocalID,
  378. limit: limit,
  379. );
  380. final files = _convertToFiles(results);
  381. return FileLoadResult(files, files.length == limit);
  382. }
  383. Future<List<File>> getAllVideos() async {
  384. final db = await instance.database;
  385. final results = await db.query(
  386. table,
  387. where: '$columnLocalID IS NOT NULL AND $columnFileType = 1',
  388. orderBy: '$columnCreationTime DESC',
  389. );
  390. return _convertToFiles(results);
  391. }
  392. Future<List<File>> getAllInPath(String path) async {
  393. final db = await instance.database;
  394. final results = await db.query(
  395. table,
  396. where: '$columnLocalID IS NOT NULL AND $columnDeviceFolder = ?',
  397. whereArgs: [path],
  398. orderBy: '$columnCreationTime DESC',
  399. groupBy: columnLocalID,
  400. );
  401. return _convertToFiles(results);
  402. }
  403. Future<List<File>> getFilesCreatedWithinDurations(
  404. List<List<int>> durations) async {
  405. final db = await instance.database;
  406. String whereClause = "";
  407. for (int index = 0; index < durations.length; index++) {
  408. whereClause += "($columnCreationTime > " +
  409. durations[index][0].toString() +
  410. " AND $columnCreationTime < " +
  411. durations[index][1].toString() +
  412. ")";
  413. if (index != durations.length - 1) {
  414. whereClause += " OR ";
  415. }
  416. }
  417. final results = await db.query(
  418. table,
  419. where: whereClause,
  420. orderBy: '$columnCreationTime ASC',
  421. );
  422. return _convertToFiles(results);
  423. }
  424. Future<List<File>> getFilesToBeUploadedWithinFolders(
  425. Set<String> folders) async {
  426. if (folders.isEmpty) {
  427. return [];
  428. }
  429. final db = await instance.database;
  430. String inParam = "";
  431. for (final folder in folders) {
  432. inParam += "'" + folder.replaceAll("'", "''") + "',";
  433. }
  434. inParam = inParam.substring(0, inParam.length - 1);
  435. final results = await db.query(
  436. table,
  437. where:
  438. '($columnUploadedFileID IS NULL OR $columnUploadedFileID IS -1) AND $columnDeviceFolder IN ($inParam)',
  439. orderBy: '$columnCreationTime DESC',
  440. groupBy: columnLocalID,
  441. );
  442. return _convertToFiles(results);
  443. }
  444. Future<List<File>> getAllLocalFiles() async {
  445. final db = await instance.database;
  446. final results = await db.query(
  447. table,
  448. where:
  449. '($columnUploadedFileID IS NULL OR $columnUploadedFileID IS -1) AND $columnLocalID IS NOT NULL',
  450. orderBy: '$columnCreationTime DESC',
  451. groupBy: columnLocalID,
  452. );
  453. return _convertToFiles(results);
  454. }
  455. Future<List<File>> getEditedRemoteFiles() async {
  456. final db = await instance.database;
  457. final results = await db.query(
  458. table,
  459. where:
  460. '($columnCollectionID IS NOT NULL AND $columnCollectionID IS NOT -1) AND ($columnUploadedFileID IS NULL OR $columnUploadedFileID IS -1)',
  461. orderBy: '$columnCreationTime DESC',
  462. groupBy: columnLocalID,
  463. );
  464. return _convertToFiles(results);
  465. }
  466. Future<List<int>> getUploadedFileIDsToBeUpdated() async {
  467. final db = await instance.database;
  468. final rows = await db.query(
  469. table,
  470. columns: [columnUploadedFileID],
  471. where:
  472. '($columnLocalID IS NOT NULL AND ($columnUploadedFileID IS NOT NULL AND $columnUploadedFileID IS NOT -1) AND $columnUpdationTime IS NULL)',
  473. orderBy: '$columnCreationTime DESC',
  474. distinct: true,
  475. );
  476. final uploadedFileIDs = <int>[];
  477. for (final row in rows) {
  478. uploadedFileIDs.add(row[columnUploadedFileID]);
  479. }
  480. return uploadedFileIDs;
  481. }
  482. Future<File> getUploadedFileInAnyCollection(int uploadedFileID) async {
  483. final db = await instance.database;
  484. final results = await db.query(
  485. table,
  486. where: '$columnUploadedFileID = ?',
  487. whereArgs: [
  488. uploadedFileID,
  489. ],
  490. limit: 1,
  491. );
  492. if (results.isEmpty) {
  493. return null;
  494. }
  495. return _convertToFiles(results)[0];
  496. }
  497. Future<Set<String>> getExistingLocalFileIDs() async {
  498. final db = await instance.database;
  499. final rows = await db.query(
  500. table,
  501. columns: [columnLocalID],
  502. distinct: true,
  503. where: '$columnLocalID IS NOT NULL',
  504. );
  505. final result = <String>{};
  506. for (final row in rows) {
  507. result.add(row[columnLocalID]);
  508. }
  509. return result;
  510. }
  511. Future<int> getNumberOfUploadedFiles() async {
  512. final db = await instance.database;
  513. final rows = await db.query(
  514. table,
  515. columns: [columnUploadedFileID],
  516. where:
  517. '($columnLocalID IS NOT NULL AND ($columnUploadedFileID IS NOT NULL AND $columnUploadedFileID IS NOT -1) AND $columnUpdationTime IS NOT NULL)',
  518. distinct: true,
  519. );
  520. return rows.length;
  521. }
  522. Future<int> updateUploadedFile(
  523. String localID,
  524. String title,
  525. Location location,
  526. int creationTime,
  527. int modificationTime,
  528. int updationTime,
  529. ) async {
  530. final db = await instance.database;
  531. return await db.update(
  532. table,
  533. {
  534. columnTitle: title,
  535. columnLatitude: location.latitude,
  536. columnLongitude: location.longitude,
  537. columnCreationTime: creationTime,
  538. columnModificationTime: modificationTime,
  539. columnUpdationTime: updationTime,
  540. },
  541. where: '$columnLocalID = ?',
  542. whereArgs: [localID],
  543. );
  544. }
  545. Future<List<File>> getMatchingFiles(
  546. String title,
  547. String deviceFolder,
  548. ) async {
  549. final db = await instance.database;
  550. final rows = await db.query(
  551. table,
  552. where: '''$columnTitle=? AND $columnDeviceFolder=?''',
  553. whereArgs: [
  554. title,
  555. deviceFolder,
  556. ],
  557. );
  558. if (rows.isNotEmpty) {
  559. return _convertToFiles(rows);
  560. } else {
  561. return null;
  562. }
  563. }
  564. Future<int> update(File file) async {
  565. final db = await instance.database;
  566. return await db.update(
  567. table,
  568. _getRowForFile(file),
  569. where: '$columnGeneratedID = ?',
  570. whereArgs: [file.generatedID],
  571. );
  572. }
  573. Future<int> updateUploadedFileAcrossCollections(File file) async {
  574. final db = await instance.database;
  575. return await db.update(
  576. table,
  577. _getRowForFileWithoutCollection(file),
  578. where: '$columnUploadedFileID = ?',
  579. whereArgs: [file.uploadedFileID],
  580. );
  581. }
  582. Future<int> delete(int uploadedFileID) async {
  583. final db = await instance.database;
  584. return db.delete(
  585. table,
  586. where: '$columnUploadedFileID =?',
  587. whereArgs: [uploadedFileID],
  588. );
  589. }
  590. Future<int> deleteMultipleUploadedFiles(List<int> uploadedFileIDs) async {
  591. final db = await instance.database;
  592. return await db.delete(
  593. table,
  594. where: '$columnUploadedFileID IN (${uploadedFileIDs.join(', ')})',
  595. );
  596. }
  597. Future<int> deleteLocalFile(File file) async {
  598. final db = await instance.database;
  599. if (file.localID != null) {
  600. // delete all files with same local ID
  601. return db.delete(
  602. table,
  603. where: '$columnLocalID =?',
  604. whereArgs: [file.localID],
  605. );
  606. } else {
  607. return db.delete(
  608. table,
  609. where: '$columnGeneratedID =?',
  610. whereArgs: [file.generatedID],
  611. );
  612. }
  613. }
  614. Future<void> deleteLocalFiles(List<String> localIDs) async {
  615. String inParam = "";
  616. for (final localID in localIDs) {
  617. inParam += "'" + localID + "',";
  618. }
  619. inParam = inParam.substring(0, inParam.length - 1);
  620. final db = await instance.database;
  621. await db.rawQuery('''
  622. UPDATE $table
  623. SET $columnLocalID = NULL
  624. WHERE $columnLocalID IN ($inParam);
  625. ''');
  626. }
  627. Future<List<File>> getLocalFiles(List<String> localIDs) async {
  628. String inParam = "";
  629. for (final localID in localIDs) {
  630. inParam += "'" + localID + "',";
  631. }
  632. inParam = inParam.substring(0, inParam.length - 1);
  633. final db = await instance.database;
  634. final results = await db.query(
  635. table,
  636. where: '$columnLocalID IN ($inParam)',
  637. );
  638. return _convertToFiles(results);
  639. }
  640. Future<int> deleteFromCollection(int uploadedFileID, int collectionID) async {
  641. final db = await instance.database;
  642. return db.delete(
  643. table,
  644. where: '$columnUploadedFileID = ? AND $columnCollectionID = ?',
  645. whereArgs: [uploadedFileID, collectionID],
  646. );
  647. }
  648. Future<int> deleteCollection(int collectionID) async {
  649. final db = await instance.database;
  650. return db.delete(
  651. table,
  652. where: '$columnCollectionID = ?',
  653. whereArgs: [collectionID],
  654. );
  655. }
  656. Future<int> removeFromCollection(int collectionID, List<int> fileIDs) async {
  657. final db = await instance.database;
  658. return db.delete(
  659. table,
  660. where:
  661. '$columnCollectionID =? AND $columnUploadedFileID IN (${fileIDs.join(', ')})',
  662. whereArgs: [collectionID],
  663. );
  664. }
  665. Future<List<File>> getLatestLocalFiles() async {
  666. final db = await instance.database;
  667. final rows = await db.rawQuery('''
  668. SELECT $table.*
  669. FROM $table
  670. INNER JOIN
  671. (
  672. SELECT $columnDeviceFolder, MAX($columnCreationTime) AS max_creation_time
  673. FROM $table
  674. WHERE $table.$columnLocalID IS NOT NULL
  675. GROUP BY $columnDeviceFolder
  676. ) latest_files
  677. ON $table.$columnDeviceFolder = latest_files.$columnDeviceFolder
  678. AND $table.$columnCreationTime = latest_files.max_creation_time;
  679. ''');
  680. final files = _convertToFiles(rows);
  681. // TODO: Do this de-duplication within the SQL Query
  682. final folderMap = <String, File>{};
  683. for (final file in files) {
  684. if (folderMap.containsKey(file.deviceFolder)) {
  685. if (folderMap[file.deviceFolder].updationTime < file.updationTime) {
  686. continue;
  687. }
  688. }
  689. folderMap[file.deviceFolder] = file;
  690. }
  691. return folderMap.values.toList();
  692. }
  693. Future<List<File>> getLatestCollectionFiles() async {
  694. final db = await instance.database;
  695. final rows = await db.rawQuery('''
  696. SELECT $table.*
  697. FROM $table
  698. INNER JOIN
  699. (
  700. SELECT $columnCollectionID, MAX($columnCreationTime) AS max_creation_time
  701. FROM $table
  702. WHERE ($columnCollectionID IS NOT NULL AND $columnCollectionID IS NOT -1)
  703. GROUP BY $columnCollectionID
  704. ) latest_files
  705. ON $table.$columnCollectionID = latest_files.$columnCollectionID
  706. AND $table.$columnCreationTime = latest_files.max_creation_time;
  707. ''');
  708. final files = _convertToFiles(rows);
  709. // TODO: Do this de-duplication within the SQL Query
  710. final collectionMap = <int, File>{};
  711. for (final file in files) {
  712. if (collectionMap.containsKey(file.collectionID)) {
  713. if (collectionMap[file.collectionID].updationTime < file.updationTime) {
  714. continue;
  715. }
  716. }
  717. collectionMap[file.collectionID] = file;
  718. }
  719. return collectionMap.values.toList();
  720. }
  721. Future<File> getLastModifiedFileInCollection(int collectionID) async {
  722. final db = await instance.database;
  723. final rows = await db.query(
  724. table,
  725. where: '$columnCollectionID = ?',
  726. whereArgs: [collectionID],
  727. orderBy: '$columnUpdationTime DESC',
  728. limit: 1,
  729. );
  730. if (rows.isNotEmpty) {
  731. return _getFileFromRow(rows[0]);
  732. } else {
  733. return null;
  734. }
  735. }
  736. Future<Map<String, int>> getFileCountInDeviceFolders() async {
  737. final db = await instance.database;
  738. final rows = await db.rawQuery('''
  739. SELECT COUNT($columnGeneratedID) as count, $columnDeviceFolder
  740. FROM $table
  741. WHERE $columnLocalID IS NOT NULL
  742. GROUP BY $columnDeviceFolder
  743. ''');
  744. final result = <String, int>{};
  745. for (final row in rows) {
  746. result[row[columnDeviceFolder]] = row["count"];
  747. }
  748. return result;
  749. }
  750. Future<bool> doesFileExistInCollection(
  751. int uploadedFileID, int collectionID) async {
  752. final db = await instance.database;
  753. final rows = await db.query(
  754. table,
  755. where: '$columnUploadedFileID = ? AND $columnCollectionID = ?',
  756. whereArgs: [uploadedFileID, collectionID],
  757. limit: 1,
  758. );
  759. return rows.isNotEmpty;
  760. }
  761. List<File> _convertToFiles(List<Map<String, dynamic>> results) {
  762. final List<File> files = [];
  763. for (final result in results) {
  764. files.add(_getFileFromRow(result));
  765. }
  766. return files;
  767. }
  768. Map<String, dynamic> _getRowForFile(File file) {
  769. final row = <String, dynamic>{};
  770. if (file.generatedID != null) {
  771. row[columnGeneratedID] = file.generatedID;
  772. }
  773. row[columnLocalID] = file.localID;
  774. row[columnUploadedFileID] = file.uploadedFileID ?? -1;
  775. row[columnOwnerID] = file.ownerID;
  776. row[columnCollectionID] = file.collectionID ?? -1;
  777. row[columnTitle] = file.title;
  778. row[columnDeviceFolder] = file.deviceFolder;
  779. if (file.location != null) {
  780. row[columnLatitude] = file.location.latitude;
  781. row[columnLongitude] = file.location.longitude;
  782. }
  783. switch (file.fileType) {
  784. case FileType.image:
  785. row[columnFileType] = 0;
  786. break;
  787. case FileType.video:
  788. row[columnFileType] = 1;
  789. break;
  790. default:
  791. row[columnFileType] = -1;
  792. }
  793. row[columnCreationTime] = file.creationTime;
  794. row[columnModificationTime] = file.modificationTime;
  795. row[columnUpdationTime] = file.updationTime;
  796. row[columnEncryptedKey] = file.encryptedKey;
  797. row[columnKeyDecryptionNonce] = file.keyDecryptionNonce;
  798. row[columnFileDecryptionHeader] = file.fileDecryptionHeader;
  799. row[columnThumbnailDecryptionHeader] = file.thumbnailDecryptionHeader;
  800. row[columnMetadataDecryptionHeader] = file.metadataDecryptionHeader;
  801. return row;
  802. }
  803. Map<String, dynamic> _getRowForFileWithoutCollection(File file) {
  804. final row = <String, dynamic>{};
  805. row[columnLocalID] = file.localID;
  806. row[columnUploadedFileID] = file.uploadedFileID ?? -1;
  807. row[columnOwnerID] = file.ownerID;
  808. row[columnTitle] = file.title;
  809. row[columnDeviceFolder] = file.deviceFolder;
  810. if (file.location != null) {
  811. row[columnLatitude] = file.location.latitude;
  812. row[columnLongitude] = file.location.longitude;
  813. }
  814. switch (file.fileType) {
  815. case FileType.image:
  816. row[columnFileType] = 0;
  817. break;
  818. case FileType.video:
  819. row[columnFileType] = 1;
  820. break;
  821. default:
  822. row[columnFileType] = -1;
  823. }
  824. row[columnCreationTime] = file.creationTime;
  825. row[columnModificationTime] = file.modificationTime;
  826. row[columnUpdationTime] = file.updationTime;
  827. row[columnFileDecryptionHeader] = file.fileDecryptionHeader;
  828. row[columnThumbnailDecryptionHeader] = file.thumbnailDecryptionHeader;
  829. row[columnMetadataDecryptionHeader] = file.metadataDecryptionHeader;
  830. return row;
  831. }
  832. File _getFileFromRow(Map<String, dynamic> row) {
  833. final file = File();
  834. file.generatedID = row[columnGeneratedID];
  835. file.localID = row[columnLocalID];
  836. file.uploadedFileID =
  837. row[columnUploadedFileID] == -1 ? null : row[columnUploadedFileID];
  838. file.ownerID = row[columnOwnerID];
  839. file.collectionID =
  840. row[columnCollectionID] == -1 ? null : row[columnCollectionID];
  841. file.title = row[columnTitle];
  842. file.deviceFolder = row[columnDeviceFolder];
  843. if (row[columnLatitude] != null && row[columnLongitude] != null) {
  844. file.location = Location(row[columnLatitude], row[columnLongitude]);
  845. }
  846. file.fileType = getFileType(row[columnFileType]);
  847. file.creationTime = row[columnCreationTime];
  848. file.modificationTime = row[columnModificationTime];
  849. file.updationTime = row[columnUpdationTime] ?? -1;
  850. file.encryptedKey = row[columnEncryptedKey];
  851. file.keyDecryptionNonce = row[columnKeyDecryptionNonce];
  852. file.fileDecryptionHeader = row[columnFileDecryptionHeader];
  853. file.thumbnailDecryptionHeader = row[columnThumbnailDecryptionHeader];
  854. file.metadataDecryptionHeader = row[columnMetadataDecryptionHeader];
  855. return file;
  856. }
  857. }