files_db.dart 33 KB

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