files_db.dart 24 KB

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