files_db.dart 39 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236
  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:photos/models/magic_metadata.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. static final columnPubMMdEncodedJson = 'pub_mmd_encoded_json';
  53. static final columnPubMMdVersion = 'pub_mmd_ver';
  54. // part of magic metadata
  55. // Only parse & store selected fields from JSON in separate columns if
  56. // we need to write query based on that field
  57. static final columnMMdVisibility = 'mmd_visibility';
  58. static final initializationScript = [...createTable(table)];
  59. static final migrationScripts = [
  60. ...alterDeviceFolderToAllowNULL(),
  61. ...alterTimestampColumnTypes(),
  62. ...addIndices(),
  63. ...addMetadataColumns(),
  64. ...addMagicMetadataColumns(),
  65. ...addUniqueConstraintOnCollectionFiles(),
  66. ...addPubMagicMetadataColumns()
  67. ];
  68. final dbConfig = MigrationConfig(
  69. initializationScript: initializationScript,
  70. migrationScripts: migrationScripts,
  71. );
  72. // make this a singleton class
  73. FilesDB._privateConstructor();
  74. static final FilesDB instance = FilesDB._privateConstructor();
  75. // only have a single app-wide reference to the database
  76. static Future<Database> _dbFuture;
  77. Future<Database> get database async {
  78. // lazily instantiate the db the first time it is accessed
  79. _dbFuture ??= _initDatabase();
  80. return _dbFuture;
  81. }
  82. // this opens the database (and creates it if it doesn't exist)
  83. Future<Database> _initDatabase() async {
  84. Directory documentsDirectory = await getApplicationDocumentsDirectory();
  85. String path = join(documentsDirectory.path, _databaseName);
  86. _logger.info("DB path " + path);
  87. return await openDatabaseWithMigration(path, dbConfig);
  88. }
  89. // SQL code to create the database table
  90. static List<String> createTable(String tableName) {
  91. return [
  92. '''
  93. CREATE TABLE $tableName (
  94. $columnGeneratedID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  95. $columnLocalID TEXT,
  96. $columnUploadedFileID INTEGER DEFAULT -1,
  97. $columnOwnerID INTEGER,
  98. $columnCollectionID INTEGER DEFAULT -1,
  99. $columnTitle TEXT NOT NULL,
  100. $columnDeviceFolder TEXT,
  101. $columnLatitude REAL,
  102. $columnLongitude REAL,
  103. $columnFileType INTEGER,
  104. $columnModificationTime TEXT NOT NULL,
  105. $columnEncryptedKey TEXT,
  106. $columnKeyDecryptionNonce TEXT,
  107. $columnFileDecryptionHeader TEXT,
  108. $columnThumbnailDecryptionHeader TEXT,
  109. $columnMetadataDecryptionHeader TEXT,
  110. $columnIsDeleted INTEGER DEFAULT 0,
  111. $columnCreationTime TEXT NOT NULL,
  112. $columnUpdationTime TEXT,
  113. UNIQUE($columnLocalID, $columnUploadedFileID, $columnCollectionID)
  114. );
  115. ''',
  116. ];
  117. }
  118. static List<String> addIndices() {
  119. return [
  120. '''
  121. CREATE INDEX IF NOT EXISTS collection_id_index ON $table($columnCollectionID);
  122. ''',
  123. '''
  124. CREATE INDEX IF NOT EXISTS device_folder_index ON $table($columnDeviceFolder);
  125. ''',
  126. '''
  127. CREATE INDEX IF NOT EXISTS creation_time_index ON $table($columnCreationTime);
  128. ''',
  129. '''
  130. CREATE INDEX IF NOT EXISTS updation_time_index ON $table($columnUpdationTime);
  131. '''
  132. ];
  133. }
  134. static List<String> alterDeviceFolderToAllowNULL() {
  135. return [
  136. ...createTable(tempTable),
  137. '''
  138. INSERT INTO $tempTable
  139. SELECT *
  140. FROM $table;
  141. DROP TABLE $table;
  142. ALTER TABLE $tempTable
  143. RENAME TO $table;
  144. '''
  145. ];
  146. }
  147. static List<String> alterTimestampColumnTypes() {
  148. return [
  149. '''
  150. DROP TABLE IF EXISTS $tempTable;
  151. ''',
  152. '''
  153. CREATE TABLE $tempTable (
  154. $columnGeneratedID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  155. $columnLocalID TEXT,
  156. $columnUploadedFileID INTEGER DEFAULT -1,
  157. $columnOwnerID INTEGER,
  158. $columnCollectionID INTEGER DEFAULT -1,
  159. $columnTitle TEXT NOT NULL,
  160. $columnDeviceFolder TEXT,
  161. $columnLatitude REAL,
  162. $columnLongitude REAL,
  163. $columnFileType INTEGER,
  164. $columnModificationTime INTEGER NOT NULL,
  165. $columnEncryptedKey TEXT,
  166. $columnKeyDecryptionNonce TEXT,
  167. $columnFileDecryptionHeader TEXT,
  168. $columnThumbnailDecryptionHeader TEXT,
  169. $columnMetadataDecryptionHeader TEXT,
  170. $columnCreationTime INTEGER NOT NULL,
  171. $columnUpdationTime INTEGER,
  172. UNIQUE($columnLocalID, $columnUploadedFileID, $columnCollectionID)
  173. );
  174. ''',
  175. '''
  176. INSERT INTO $tempTable
  177. SELECT
  178. $columnGeneratedID,
  179. $columnLocalID,
  180. $columnUploadedFileID,
  181. $columnOwnerID,
  182. $columnCollectionID,
  183. $columnTitle,
  184. $columnDeviceFolder,
  185. $columnLatitude,
  186. $columnLongitude,
  187. $columnFileType,
  188. CAST($columnModificationTime AS INTEGER),
  189. $columnEncryptedKey,
  190. $columnKeyDecryptionNonce,
  191. $columnFileDecryptionHeader,
  192. $columnThumbnailDecryptionHeader,
  193. $columnMetadataDecryptionHeader,
  194. CAST($columnCreationTime AS INTEGER),
  195. CAST($columnUpdationTime AS INTEGER)
  196. FROM $table;
  197. ''',
  198. '''
  199. DROP TABLE $table;
  200. ''',
  201. '''
  202. ALTER TABLE $tempTable
  203. RENAME TO $table;
  204. ''',
  205. ];
  206. }
  207. static List<String> addMetadataColumns() {
  208. return [
  209. '''
  210. ALTER TABLE $table ADD COLUMN $columnFileSubType INTEGER;
  211. ''',
  212. '''
  213. ALTER TABLE $table ADD COLUMN $columnDuration INTEGER;
  214. ''',
  215. '''
  216. ALTER TABLE $table ADD COLUMN $columnExif TEXT;
  217. ''',
  218. '''
  219. ALTER TABLE $table ADD COLUMN $columnHash TEXT;
  220. ''',
  221. '''
  222. ALTER TABLE $table ADD COLUMN $columnMetadataVersion INTEGER;
  223. ''',
  224. ];
  225. }
  226. static List<String> addMagicMetadataColumns() {
  227. return [
  228. '''
  229. ALTER TABLE $table ADD COLUMN $columnMMdEncodedJson TEXT DEFAULT '{}';
  230. ''',
  231. '''
  232. ALTER TABLE $table ADD COLUMN $columnMMdVersion INTEGER DEFAULT 0;
  233. ''',
  234. '''
  235. ALTER TABLE $table ADD COLUMN $columnMMdVisibility INTEGER DEFAULT $kVisibilityVisible;
  236. '''
  237. ];
  238. }
  239. static List<String> addUniqueConstraintOnCollectionFiles() {
  240. return [
  241. '''
  242. DELETE from $table where $columnCollectionID || '-' || $columnUploadedFileID IN
  243. (SELECT $columnCollectionID || '-' || $columnUploadedFileID from $table WHERE
  244. $columnCollectionID is not NULL AND $columnUploadedFileID is NOT NULL
  245. AND $columnCollectionID != -1 AND $columnUploadedFileID != -1
  246. GROUP BY ($columnCollectionID || '-' || $columnUploadedFileID) HAVING count(*) > 1)
  247. AND ($columnCollectionID || '-' || $columnUploadedFileID || '-' || $columnGeneratedID) NOT IN
  248. (SELECT $columnCollectionID || '-' || $columnUploadedFileID || '-' || max($columnGeneratedID)
  249. from $table WHERE
  250. $columnCollectionID is not NULL AND $columnUploadedFileID is NOT NULL
  251. AND $columnCollectionID != -1 AND $columnUploadedFileID != -1 GROUP BY
  252. ($columnCollectionID || '-' || $columnUploadedFileID) HAVING count(*) > 1);
  253. ''',
  254. '''
  255. CREATE UNIQUE INDEX IF NOT EXISTS cid_uid ON $table ($columnCollectionID, $columnUploadedFileID)
  256. WHERE $columnCollectionID is not NULL AND $columnUploadedFileID is not NULL
  257. AND $columnCollectionID != -1 AND $columnUploadedFileID != -1;
  258. '''
  259. ];
  260. }
  261. static List<String> addPubMagicMetadataColumns() {
  262. return [
  263. '''
  264. ALTER TABLE $table ADD COLUMN $columnPubMMdEncodedJson TEXT DEFAULT '{}';
  265. ''',
  266. '''
  267. ALTER TABLE $table ADD COLUMN $columnPubMMdVersion INTEGER DEFAULT 0;
  268. '''
  269. ];
  270. }
  271. Future<void> clearTable() async {
  272. final db = await instance.database;
  273. await db.delete(table);
  274. }
  275. Future<void> insertMultiple(List<File> files) async {
  276. final startTime = DateTime.now();
  277. final db = await instance.database;
  278. var batch = db.batch();
  279. int batchCounter = 0;
  280. for (File file in files) {
  281. if (batchCounter == 400) {
  282. await batch.commit(noResult: true);
  283. batch = db.batch();
  284. batchCounter = 0;
  285. }
  286. batch.insert(
  287. table,
  288. _getRowForFile(file),
  289. conflictAlgorithm: ConflictAlgorithm.replace,
  290. );
  291. batchCounter++;
  292. }
  293. await batch.commit(noResult: true);
  294. final endTime = DateTime.now();
  295. final duration = Duration(
  296. microseconds:
  297. endTime.microsecondsSinceEpoch - startTime.microsecondsSinceEpoch,
  298. );
  299. _logger.info(
  300. "Batch insert of " +
  301. files.length.toString() +
  302. " took " +
  303. duration.inMilliseconds.toString() +
  304. "ms.",
  305. );
  306. }
  307. Future<int> insert(File file) async {
  308. final db = await instance.database;
  309. return db.insert(
  310. table,
  311. _getRowForFile(file),
  312. conflictAlgorithm: ConflictAlgorithm.replace,
  313. );
  314. }
  315. Future<File> getFile(int generatedID) async {
  316. final db = await instance.database;
  317. final results = await db.query(
  318. table,
  319. where: '$columnGeneratedID = ?',
  320. whereArgs: [generatedID],
  321. );
  322. if (results.isEmpty) {
  323. return null;
  324. }
  325. return _convertToFiles(results)[0];
  326. }
  327. Future<File> getUploadedFile(int uploadedID, int collectionID) async {
  328. final db = await instance.database;
  329. final results = await db.query(
  330. table,
  331. where: '$columnUploadedFileID = ? AND $columnCollectionID = ?',
  332. whereArgs: [
  333. uploadedID,
  334. collectionID,
  335. ],
  336. );
  337. if (results.isEmpty) {
  338. return null;
  339. }
  340. return _convertToFiles(results)[0];
  341. }
  342. Future<Set<int>> getUploadedFileIDs(int collectionID) async {
  343. final db = await instance.database;
  344. final results = await db.query(
  345. table,
  346. columns: [columnUploadedFileID],
  347. where: '$columnCollectionID = ?',
  348. whereArgs: [
  349. collectionID,
  350. ],
  351. );
  352. final ids = <int>{};
  353. for (final result in results) {
  354. ids.add(result[columnUploadedFileID]);
  355. }
  356. return ids;
  357. }
  358. Future<BackedUpFileIDs> getBackedUpIDs() async {
  359. final db = await instance.database;
  360. final results = await db.query(
  361. table,
  362. columns: [columnLocalID, columnUploadedFileID],
  363. where:
  364. '$columnLocalID IS NOT NULL AND ($columnUploadedFileID IS NOT NULL AND $columnUploadedFileID IS NOT -1)',
  365. );
  366. final localIDs = <String>{};
  367. final uploadedIDs = <int>{};
  368. for (final result in results) {
  369. localIDs.add(result[columnLocalID]);
  370. uploadedIDs.add(result[columnUploadedFileID]);
  371. }
  372. return BackedUpFileIDs(localIDs.toList(), uploadedIDs.toList());
  373. }
  374. Future<FileLoadResult> getAllUploadedFiles(
  375. int startTime,
  376. int endTime,
  377. int ownerID, {
  378. int limit,
  379. bool asc,
  380. int visibility = kVisibilityVisible,
  381. Set<int> ignoredCollectionIDs,
  382. }) async {
  383. final db = await instance.database;
  384. final order = (asc ?? false ? 'ASC' : 'DESC');
  385. final results = await db.query(
  386. table,
  387. where:
  388. '$columnCreationTime >= ? AND $columnCreationTime <= ? AND $columnOwnerID = ? AND ($columnCollectionID IS NOT NULL AND $columnCollectionID IS NOT -1)'
  389. ' AND $columnMMdVisibility = ?',
  390. whereArgs: [startTime, endTime, ownerID, visibility],
  391. orderBy:
  392. '$columnCreationTime ' + order + ', $columnModificationTime ' + order,
  393. limit: limit,
  394. );
  395. final files = _convertToFiles(results);
  396. List<File> deduplicatedFiles =
  397. _deduplicatedAndFilterIgnoredFiles(files, ignoredCollectionIDs);
  398. return FileLoadResult(deduplicatedFiles, files.length == limit);
  399. }
  400. Future<FileLoadResult> getAllLocalAndUploadedFiles(
  401. int startTime,
  402. int endTime,
  403. int ownerID, {
  404. int limit,
  405. bool asc,
  406. Set<int> ignoredCollectionIDs,
  407. }) async {
  408. final db = await instance.database;
  409. final order = (asc ?? false ? 'ASC' : 'DESC');
  410. final results = await db.query(
  411. table,
  412. where:
  413. '$columnCreationTime >= ? AND $columnCreationTime <= ? AND ($columnOwnerID IS NULL OR $columnOwnerID = ?) AND ($columnMMdVisibility IS NULL OR $columnMMdVisibility = ?)'
  414. ' AND ($columnLocalID IS NOT NULL OR ($columnCollectionID IS NOT NULL AND $columnCollectionID IS NOT -1))',
  415. whereArgs: [startTime, endTime, ownerID, kVisibilityVisible],
  416. orderBy:
  417. '$columnCreationTime ' + order + ', $columnModificationTime ' + order,
  418. limit: limit,
  419. );
  420. final files = _convertToFiles(results);
  421. List<File> deduplicatedFiles =
  422. _deduplicatedAndFilterIgnoredFiles(files, ignoredCollectionIDs);
  423. return FileLoadResult(deduplicatedFiles, files.length == limit);
  424. }
  425. Future<FileLoadResult> getImportantFiles(
  426. int startTime,
  427. int endTime,
  428. int ownerID,
  429. List<String> paths, {
  430. int limit,
  431. bool asc,
  432. Set<int> ignoredCollectionIDs,
  433. }) async {
  434. final db = await instance.database;
  435. String inParam = "";
  436. for (final path in paths) {
  437. inParam += "'" + path.replaceAll("'", "''") + "',";
  438. }
  439. inParam = inParam.substring(0, inParam.length - 1);
  440. final order = (asc ?? false ? 'ASC' : 'DESC');
  441. final results = await db.query(
  442. table,
  443. where:
  444. '$columnCreationTime >= ? AND $columnCreationTime <= ? AND ($columnOwnerID IS NULL OR $columnOwnerID = ?) AND ($columnMMdVisibility IS NULL OR $columnMMdVisibility = ?)'
  445. 'AND (($columnLocalID IS NOT NULL AND $columnDeviceFolder IN ($inParam)) OR ($columnCollectionID IS NOT NULL AND $columnCollectionID IS NOT -1))',
  446. whereArgs: [startTime, endTime, ownerID, kVisibilityVisible],
  447. orderBy:
  448. '$columnCreationTime ' + order + ', $columnModificationTime ' + order,
  449. limit: limit,
  450. );
  451. final files = _convertToFiles(results);
  452. List<File> deduplicatedFiles =
  453. _deduplicatedAndFilterIgnoredFiles(files, ignoredCollectionIDs);
  454. return FileLoadResult(deduplicatedFiles, files.length == limit);
  455. }
  456. List<File> _deduplicatedAndFilterIgnoredFiles(
  457. List<File> files,
  458. Set<int> ignoredCollectionIDs,
  459. ) {
  460. final uploadedFileIDs = <int>{};
  461. final List<File> deduplicatedFiles = [];
  462. for (final file in files) {
  463. final id = file.uploadedFileID;
  464. if (ignoredCollectionIDs != null &&
  465. ignoredCollectionIDs.contains(file.collectionID)) {
  466. continue;
  467. }
  468. if (id != null && id != -1 && uploadedFileIDs.contains(id)) {
  469. continue;
  470. }
  471. uploadedFileIDs.add(id);
  472. deduplicatedFiles.add(file);
  473. }
  474. return deduplicatedFiles;
  475. }
  476. Future<FileLoadResult> getFilesInCollection(
  477. int collectionID,
  478. int startTime,
  479. int endTime, {
  480. int limit,
  481. bool asc,
  482. }) async {
  483. final db = await instance.database;
  484. final order = (asc ?? false ? 'ASC' : 'DESC');
  485. final results = await db.query(
  486. table,
  487. where:
  488. '$columnCollectionID = ? AND $columnCreationTime >= ? AND $columnCreationTime <= ?',
  489. whereArgs: [collectionID, startTime, endTime],
  490. orderBy:
  491. '$columnCreationTime ' + order + ', $columnModificationTime ' + order,
  492. limit: limit,
  493. );
  494. final files = _convertToFiles(results);
  495. _logger.info("Fetched " + files.length.toString() + " files");
  496. return FileLoadResult(files, files.length == limit);
  497. }
  498. Future<FileLoadResult> getFilesInPath(
  499. String path,
  500. int startTime,
  501. int endTime, {
  502. int limit,
  503. bool asc,
  504. }) async {
  505. final db = await instance.database;
  506. final order = (asc ?? false ? 'ASC' : 'DESC');
  507. final results = await db.query(
  508. table,
  509. where:
  510. '$columnDeviceFolder = ? AND $columnCreationTime >= ? AND $columnCreationTime <= ? AND $columnLocalID IS NOT NULL',
  511. whereArgs: [path, startTime, endTime],
  512. orderBy:
  513. '$columnCreationTime ' + order + ', $columnModificationTime ' + order,
  514. groupBy: columnLocalID,
  515. limit: limit,
  516. );
  517. final files = _convertToFiles(results);
  518. return FileLoadResult(files, files.length == limit);
  519. }
  520. Future<List<File>> getAllVideos() async {
  521. final db = await instance.database;
  522. final results = await db.query(
  523. table,
  524. where: '$columnLocalID IS NOT NULL AND $columnFileType = 1',
  525. orderBy: '$columnCreationTime DESC',
  526. );
  527. return _convertToFiles(results);
  528. }
  529. Future<List<File>> getAllInPath(String path) async {
  530. final db = await instance.database;
  531. final results = await db.query(
  532. table,
  533. where: '$columnLocalID IS NOT NULL AND $columnDeviceFolder = ?',
  534. whereArgs: [path],
  535. orderBy: '$columnCreationTime DESC',
  536. groupBy: columnLocalID,
  537. );
  538. return _convertToFiles(results);
  539. }
  540. Future<List<File>> getFilesCreatedWithinDurations(
  541. List<List<int>> durations,
  542. Set<int> ignoredCollectionIDs,
  543. ) async {
  544. final db = await instance.database;
  545. String whereClause = "";
  546. for (int index = 0; index < durations.length; index++) {
  547. whereClause += "($columnCreationTime > " +
  548. durations[index][0].toString() +
  549. " AND $columnCreationTime < " +
  550. durations[index][1].toString() +
  551. ")";
  552. if (index != durations.length - 1) {
  553. whereClause += " OR ";
  554. }
  555. }
  556. final results = await db.query(
  557. table,
  558. where: whereClause,
  559. orderBy: '$columnCreationTime ASC',
  560. );
  561. final files = _convertToFiles(results);
  562. return _deduplicatedAndFilterIgnoredFiles(files, ignoredCollectionIDs)
  563. }
  564. Future<List<File>> getFilesToBeUploadedWithinFolders(
  565. Set<String> folders,
  566. ) async {
  567. if (folders.isEmpty) {
  568. return [];
  569. }
  570. final db = await instance.database;
  571. String inParam = "";
  572. for (final folder in folders) {
  573. inParam += "'" + folder.replaceAll("'", "''") + "',";
  574. }
  575. inParam = inParam.substring(0, inParam.length - 1);
  576. final results = await db.query(
  577. table,
  578. where:
  579. '($columnUploadedFileID IS NULL OR $columnUploadedFileID IS -1) AND $columnDeviceFolder IN ($inParam)',
  580. orderBy: '$columnCreationTime DESC',
  581. groupBy: columnLocalID,
  582. );
  583. return _convertToFiles(results);
  584. }
  585. // Files which user added to a collection manually but they are not uploaded yet.
  586. Future<List<File>> getPendingManualUploads() async {
  587. final db = await instance.database;
  588. final results = await db.query(
  589. table,
  590. where:
  591. '($columnUploadedFileID IS NULL OR $columnUploadedFileID IS -1) AND '
  592. '$columnCollectionID IS NOT NULL AND $columnCollectionID IS NOT -1 AND '
  593. '$columnLocalID IS NOT NULL AND $columnLocalID IS NOT -1',
  594. orderBy: '$columnCreationTime DESC',
  595. groupBy: columnLocalID,
  596. );
  597. var files = _convertToFiles(results);
  598. // future-safe filter just to ensure that the query doesn't end up returning files
  599. // which should not be backed up
  600. files.removeWhere(
  601. (e) =>
  602. e.collectionID == null ||
  603. e.localID == null ||
  604. e.uploadedFileID != null,
  605. );
  606. return files;
  607. }
  608. Future<List<File>> getAllLocalFiles() async {
  609. final db = await instance.database;
  610. final results = await db.query(
  611. table,
  612. where:
  613. '($columnUploadedFileID IS NULL OR $columnUploadedFileID IS -1) AND $columnLocalID IS NOT NULL',
  614. orderBy: '$columnCreationTime DESC',
  615. groupBy: columnLocalID,
  616. );
  617. return _convertToFiles(results);
  618. }
  619. Future<List<File>> getEditedRemoteFiles() async {
  620. final db = await instance.database;
  621. final results = await db.query(
  622. table,
  623. where:
  624. '($columnCollectionID IS NOT NULL AND $columnCollectionID IS NOT -1) AND ($columnUploadedFileID IS NULL OR $columnUploadedFileID IS -1)',
  625. orderBy: '$columnCreationTime DESC',
  626. groupBy: columnLocalID,
  627. );
  628. return _convertToFiles(results);
  629. }
  630. Future<List<int>> getUploadedFileIDsToBeUpdated() async {
  631. final db = await instance.database;
  632. final rows = await db.query(
  633. table,
  634. columns: [columnUploadedFileID],
  635. where:
  636. '($columnLocalID IS NOT NULL AND ($columnUploadedFileID IS NOT NULL AND $columnUploadedFileID IS NOT -1) AND $columnUpdationTime IS NULL)',
  637. orderBy: '$columnCreationTime DESC',
  638. distinct: true,
  639. );
  640. final uploadedFileIDs = <int>[];
  641. for (final row in rows) {
  642. uploadedFileIDs.add(row[columnUploadedFileID]);
  643. }
  644. return uploadedFileIDs;
  645. }
  646. Future<File> getUploadedFileInAnyCollection(int uploadedFileID) async {
  647. final db = await instance.database;
  648. final results = await db.query(
  649. table,
  650. where: '$columnUploadedFileID = ?',
  651. whereArgs: [
  652. uploadedFileID,
  653. ],
  654. limit: 1,
  655. );
  656. if (results.isEmpty) {
  657. return null;
  658. }
  659. return _convertToFiles(results)[0];
  660. }
  661. Future<Set<String>> getExistingLocalFileIDs() async {
  662. final db = await instance.database;
  663. final rows = await db.query(
  664. table,
  665. columns: [columnLocalID],
  666. distinct: true,
  667. where: '$columnLocalID IS NOT NULL',
  668. );
  669. final result = <String>{};
  670. for (final row in rows) {
  671. result.add(row[columnLocalID]);
  672. }
  673. return result;
  674. }
  675. Future<int> getNumberOfUploadedFiles() async {
  676. final db = await instance.database;
  677. final rows = await db.query(
  678. table,
  679. columns: [columnUploadedFileID],
  680. where:
  681. '($columnLocalID IS NOT NULL AND ($columnUploadedFileID IS NOT NULL AND $columnUploadedFileID IS NOT -1) AND $columnUpdationTime IS NOT NULL)',
  682. distinct: true,
  683. );
  684. return rows.length;
  685. }
  686. Future<int> updateUploadedFile(
  687. String localID,
  688. String title,
  689. Location location,
  690. int creationTime,
  691. int modificationTime,
  692. int updationTime,
  693. ) async {
  694. final db = await instance.database;
  695. return await db.update(
  696. table,
  697. {
  698. columnTitle: title,
  699. columnLatitude: location.latitude,
  700. columnLongitude: location.longitude,
  701. columnCreationTime: creationTime,
  702. columnModificationTime: modificationTime,
  703. columnUpdationTime: updationTime,
  704. },
  705. where: '$columnLocalID = ?',
  706. whereArgs: [localID],
  707. );
  708. }
  709. Future<List<File>> getMatchingFiles(
  710. String title,
  711. String deviceFolder,
  712. ) async {
  713. final db = await instance.database;
  714. final rows = await db.query(
  715. table,
  716. where: '''$columnTitle=? AND $columnDeviceFolder=?''',
  717. whereArgs: [
  718. title,
  719. deviceFolder,
  720. ],
  721. );
  722. if (rows.isNotEmpty) {
  723. return _convertToFiles(rows);
  724. } else {
  725. return null;
  726. }
  727. }
  728. Future<int> update(File file) async {
  729. final db = await instance.database;
  730. return await db.update(
  731. table,
  732. _getRowForFile(file),
  733. where: '$columnGeneratedID = ?',
  734. whereArgs: [file.generatedID],
  735. );
  736. }
  737. Future<int> updateUploadedFileAcrossCollections(File file) async {
  738. final db = await instance.database;
  739. return await db.update(
  740. table,
  741. _getRowForFileWithoutCollection(file),
  742. where: '$columnUploadedFileID = ?',
  743. whereArgs: [file.uploadedFileID],
  744. );
  745. }
  746. Future<int> delete(int uploadedFileID) async {
  747. final db = await instance.database;
  748. return db.delete(
  749. table,
  750. where: '$columnUploadedFileID =?',
  751. whereArgs: [uploadedFileID],
  752. );
  753. }
  754. Future<int> deleteMultipleUploadedFiles(List<int> uploadedFileIDs) async {
  755. final db = await instance.database;
  756. return await db.delete(
  757. table,
  758. where: '$columnUploadedFileID IN (${uploadedFileIDs.join(', ')})',
  759. );
  760. }
  761. Future<int> deleteLocalFile(File file) async {
  762. final db = await instance.database;
  763. if (file.localID != null) {
  764. // delete all files with same local ID
  765. return db.delete(
  766. table,
  767. where: '$columnLocalID =?',
  768. whereArgs: [file.localID],
  769. );
  770. } else {
  771. return db.delete(
  772. table,
  773. where: '$columnGeneratedID =?',
  774. whereArgs: [file.generatedID],
  775. );
  776. }
  777. }
  778. Future<void> deleteLocalFiles(List<String> localIDs) async {
  779. String inParam = "";
  780. for (final localID in localIDs) {
  781. inParam += "'" + localID + "',";
  782. }
  783. inParam = inParam.substring(0, inParam.length - 1);
  784. final db = await instance.database;
  785. await db.rawQuery(
  786. '''
  787. UPDATE $table
  788. SET $columnLocalID = NULL
  789. WHERE $columnLocalID IN ($inParam);
  790. ''',
  791. );
  792. }
  793. Future<List<File>> getLocalFiles(List<String> localIDs) async {
  794. String inParam = "";
  795. for (final localID in localIDs) {
  796. inParam += "'" + localID + "',";
  797. }
  798. inParam = inParam.substring(0, inParam.length - 1);
  799. final db = await instance.database;
  800. final results = await db.query(
  801. table,
  802. where: '$columnLocalID IN ($inParam)',
  803. );
  804. return _convertToFiles(results);
  805. }
  806. Future<int> deleteUnSyncedLocalFiles(List<String> localIDs) async {
  807. String inParam = "";
  808. for (final localID in localIDs) {
  809. inParam += "'" + localID + "',";
  810. }
  811. inParam = inParam.substring(0, inParam.length - 1);
  812. final db = await instance.database;
  813. return db.delete(
  814. table,
  815. where:
  816. '($columnUploadedFileID is NULL OR $columnUploadedFileID = -1 ) AND $columnLocalID IN ($inParam)',
  817. );
  818. }
  819. Future<int> deleteFromCollection(int uploadedFileID, int collectionID) async {
  820. final db = await instance.database;
  821. return db.delete(
  822. table,
  823. where: '$columnUploadedFileID = ? AND $columnCollectionID = ?',
  824. whereArgs: [uploadedFileID, collectionID],
  825. );
  826. }
  827. Future<int> deleteFilesFromCollection(
  828. int collectionID,
  829. List<int> uploadedFileIDs,
  830. ) async {
  831. final db = await instance.database;
  832. return db.delete(
  833. table,
  834. where:
  835. '$columnCollectionID = ? AND $columnUploadedFileID IN (${uploadedFileIDs.join(', ')})',
  836. whereArgs: [collectionID],
  837. );
  838. }
  839. Future<int> collectionFileCount(int collectionID) async {
  840. final db = await instance.database;
  841. var count = Sqflite.firstIntValue(
  842. await db.rawQuery(
  843. 'SELECT COUNT(*) FROM $table where $columnCollectionID = $collectionID',
  844. ),
  845. );
  846. return count;
  847. }
  848. Future<int> fileCountWithVisibility(int visibility, int ownerID) async {
  849. final db = await instance.database;
  850. var count = Sqflite.firstIntValue(
  851. await db.rawQuery(
  852. 'SELECT COUNT(*) FROM $table where $columnMMdVisibility = $visibility AND $columnOwnerID = $ownerID',
  853. ),
  854. );
  855. return count;
  856. }
  857. Future<int> deleteCollection(int collectionID) async {
  858. final db = await instance.database;
  859. return db.delete(
  860. table,
  861. where: '$columnCollectionID = ?',
  862. whereArgs: [collectionID],
  863. );
  864. }
  865. Future<int> removeFromCollection(int collectionID, List<int> fileIDs) async {
  866. final db = await instance.database;
  867. return db.delete(
  868. table,
  869. where:
  870. '$columnCollectionID =? AND $columnUploadedFileID IN (${fileIDs.join(', ')})',
  871. whereArgs: [collectionID],
  872. );
  873. }
  874. Future<List<File>> getLatestLocalFiles() async {
  875. final db = await instance.database;
  876. final rows = await db.rawQuery(
  877. '''
  878. SELECT $table.*
  879. FROM $table
  880. INNER JOIN
  881. (
  882. SELECT $columnDeviceFolder, MAX($columnCreationTime) AS max_creation_time
  883. FROM $table
  884. WHERE $table.$columnLocalID IS NOT NULL
  885. GROUP BY $columnDeviceFolder
  886. ) latest_files
  887. ON $table.$columnDeviceFolder = latest_files.$columnDeviceFolder
  888. AND $table.$columnCreationTime = latest_files.max_creation_time;
  889. ''',
  890. );
  891. final files = _convertToFiles(rows);
  892. // TODO: Do this de-duplication within the SQL Query
  893. final folderMap = <String, File>{};
  894. for (final file in files) {
  895. if (folderMap.containsKey(file.deviceFolder)) {
  896. if (folderMap[file.deviceFolder].updationTime < file.updationTime) {
  897. continue;
  898. }
  899. }
  900. folderMap[file.deviceFolder] = file;
  901. }
  902. return folderMap.values.toList();
  903. }
  904. Future<List<File>> getLatestCollectionFiles() async {
  905. final db = await instance.database;
  906. final rows = await db.rawQuery(
  907. '''
  908. SELECT $table.*
  909. FROM $table
  910. INNER JOIN
  911. (
  912. SELECT $columnCollectionID, MAX($columnCreationTime) AS max_creation_time
  913. FROM $table
  914. WHERE ($columnCollectionID IS NOT NULL AND $columnCollectionID IS NOT -1)
  915. GROUP BY $columnCollectionID
  916. ) latest_files
  917. ON $table.$columnCollectionID = latest_files.$columnCollectionID
  918. AND $table.$columnCreationTime = latest_files.max_creation_time;
  919. ''',
  920. );
  921. final files = _convertToFiles(rows);
  922. // TODO: Do this de-duplication within the SQL Query
  923. final collectionMap = <int, File>{};
  924. for (final file in files) {
  925. if (collectionMap.containsKey(file.collectionID)) {
  926. if (collectionMap[file.collectionID].updationTime < file.updationTime) {
  927. continue;
  928. }
  929. }
  930. collectionMap[file.collectionID] = file;
  931. }
  932. return collectionMap.values.toList();
  933. }
  934. Future<File> getLastModifiedFileInCollection(int collectionID) async {
  935. final db = await instance.database;
  936. final rows = await db.query(
  937. table,
  938. where: '$columnCollectionID = ?',
  939. whereArgs: [collectionID],
  940. orderBy: '$columnUpdationTime DESC',
  941. limit: 1,
  942. );
  943. if (rows.isNotEmpty) {
  944. return _getFileFromRow(rows[0]);
  945. } else {
  946. return null;
  947. }
  948. }
  949. Future<Map<String, int>> getFileCountInDeviceFolders() async {
  950. final db = await instance.database;
  951. final rows = await db.rawQuery(
  952. '''
  953. SELECT COUNT($columnGeneratedID) as count, $columnDeviceFolder
  954. FROM $table
  955. WHERE $columnLocalID IS NOT NULL
  956. GROUP BY $columnDeviceFolder
  957. ''',
  958. );
  959. final result = <String, int>{};
  960. for (final row in rows) {
  961. result[row[columnDeviceFolder]] = row["count"];
  962. }
  963. return result;
  964. }
  965. Future<List<String>> getLocalFilesBackedUpWithoutLocation() async {
  966. final db = await instance.database;
  967. final rows = await db.query(
  968. table,
  969. columns: [columnLocalID],
  970. distinct: true,
  971. where:
  972. '$columnLocalID IS NOT NULL AND ($columnUploadedFileID IS NOT NULL AND $columnUploadedFileID IS NOT -1) '
  973. 'AND ($columnLatitude IS NULL OR $columnLongitude IS NULL OR $columnLongitude = 0.0 or $columnLongitude = 0.0)',
  974. );
  975. final result = <String>[];
  976. for (final row in rows) {
  977. result.add(row[columnLocalID]);
  978. }
  979. return result;
  980. }
  981. Future<void> markForReUploadIfLocationMissing(List<String> localIDs) async {
  982. if (localIDs.isEmpty) {
  983. return;
  984. }
  985. String inParam = "";
  986. for (final localID in localIDs) {
  987. inParam += "'" + localID + "',";
  988. }
  989. inParam = inParam.substring(0, inParam.length - 1);
  990. final db = await instance.database;
  991. await db.rawUpdate(
  992. '''
  993. UPDATE $table
  994. SET $columnUpdationTime = NULL
  995. WHERE $columnLocalID IN ($inParam)
  996. AND ($columnLatitude IS NULL OR $columnLongitude IS NULL OR $columnLongitude = 0.0 or $columnLongitude = 0.0);
  997. ''',
  998. );
  999. }
  1000. Future<bool> doesFileExistInCollection(
  1001. int uploadedFileID,
  1002. int collectionID,
  1003. ) async {
  1004. final db = await instance.database;
  1005. final rows = await db.query(
  1006. table,
  1007. where: '$columnUploadedFileID = ? AND $columnCollectionID = ?',
  1008. whereArgs: [uploadedFileID, collectionID],
  1009. limit: 1,
  1010. );
  1011. return rows.isNotEmpty;
  1012. }
  1013. Future<Map<int, File>> getFilesFromIDs(List<int> ids) async {
  1014. final result = <int, File>{};
  1015. if (ids.isEmpty) {
  1016. return result;
  1017. }
  1018. String inParam = "";
  1019. for (final id in ids) {
  1020. inParam += "'" + id.toString() + "',";
  1021. }
  1022. inParam = inParam.substring(0, inParam.length - 1);
  1023. final db = await instance.database;
  1024. final results = await db.query(
  1025. table,
  1026. where: '$columnUploadedFileID IN ($inParam)',
  1027. );
  1028. final files = _convertToFiles(results);
  1029. for (final file in files) {
  1030. result[file.uploadedFileID] = file;
  1031. }
  1032. return result;
  1033. }
  1034. List<File> _convertToFiles(List<Map<String, dynamic>> results) {
  1035. final List<File> files = [];
  1036. for (final result in results) {
  1037. files.add(_getFileFromRow(result));
  1038. }
  1039. return files;
  1040. }
  1041. Map<String, dynamic> _getRowForFile(File file) {
  1042. final row = <String, dynamic>{};
  1043. if (file.generatedID != null) {
  1044. row[columnGeneratedID] = file.generatedID;
  1045. }
  1046. row[columnLocalID] = file.localID;
  1047. row[columnUploadedFileID] = file.uploadedFileID ?? -1;
  1048. row[columnOwnerID] = file.ownerID;
  1049. row[columnCollectionID] = file.collectionID ?? -1;
  1050. row[columnTitle] = file.title;
  1051. row[columnDeviceFolder] = file.deviceFolder;
  1052. if (file.location != null) {
  1053. row[columnLatitude] = file.location.latitude;
  1054. row[columnLongitude] = file.location.longitude;
  1055. }
  1056. row[columnFileType] = getInt(file.fileType);
  1057. row[columnCreationTime] = file.creationTime;
  1058. row[columnModificationTime] = file.modificationTime;
  1059. row[columnUpdationTime] = file.updationTime;
  1060. row[columnEncryptedKey] = file.encryptedKey;
  1061. row[columnKeyDecryptionNonce] = file.keyDecryptionNonce;
  1062. row[columnFileDecryptionHeader] = file.fileDecryptionHeader;
  1063. row[columnThumbnailDecryptionHeader] = file.thumbnailDecryptionHeader;
  1064. row[columnMetadataDecryptionHeader] = file.metadataDecryptionHeader;
  1065. row[columnFileSubType] = file.fileSubType ?? -1;
  1066. row[columnDuration] = file.duration ?? 0;
  1067. row[columnExif] = file.exif;
  1068. row[columnHash] = file.hash;
  1069. row[columnMetadataVersion] = file.metadataVersion;
  1070. row[columnMMdVersion] = file.mMdVersion ?? 0;
  1071. row[columnMMdEncodedJson] = file.mMdEncodedJson ?? '{}';
  1072. row[columnMMdVisibility] =
  1073. file.magicMetadata?.visibility ?? kVisibilityVisible;
  1074. row[columnPubMMdVersion] = file.pubMmdVersion ?? 0;
  1075. row[columnPubMMdEncodedJson] = file.pubMmdEncodedJson ?? '{}';
  1076. if (file.pubMagicMetadata != null &&
  1077. file.pubMagicMetadata.editedTime != null) {
  1078. // override existing creationTime to avoid re-writing all queries related
  1079. // to loading the gallery
  1080. row[columnCreationTime] = file.pubMagicMetadata.editedTime;
  1081. }
  1082. return row;
  1083. }
  1084. Map<String, dynamic> _getRowForFileWithoutCollection(File file) {
  1085. final row = <String, dynamic>{};
  1086. row[columnLocalID] = file.localID;
  1087. row[columnUploadedFileID] = file.uploadedFileID ?? -1;
  1088. row[columnOwnerID] = file.ownerID;
  1089. row[columnTitle] = file.title;
  1090. row[columnDeviceFolder] = file.deviceFolder;
  1091. if (file.location != null) {
  1092. row[columnLatitude] = file.location.latitude;
  1093. row[columnLongitude] = file.location.longitude;
  1094. }
  1095. row[columnFileType] = getInt(file.fileType);
  1096. row[columnCreationTime] = file.creationTime;
  1097. row[columnModificationTime] = file.modificationTime;
  1098. row[columnUpdationTime] = file.updationTime;
  1099. row[columnFileDecryptionHeader] = file.fileDecryptionHeader;
  1100. row[columnThumbnailDecryptionHeader] = file.thumbnailDecryptionHeader;
  1101. row[columnMetadataDecryptionHeader] = file.metadataDecryptionHeader;
  1102. row[columnFileSubType] = file.fileSubType ?? -1;
  1103. row[columnDuration] = file.duration ?? 0;
  1104. row[columnExif] = file.exif;
  1105. row[columnHash] = file.hash;
  1106. row[columnMetadataVersion] = file.metadataVersion;
  1107. row[columnMMdVersion] = file.mMdVersion ?? 0;
  1108. row[columnMMdEncodedJson] = file.mMdEncodedJson ?? '{}';
  1109. row[columnMMdVisibility] =
  1110. file.magicMetadata?.visibility ?? kVisibilityVisible;
  1111. row[columnPubMMdVersion] = file.pubMmdVersion ?? 0;
  1112. row[columnPubMMdEncodedJson] = file.pubMmdEncodedJson ?? '{}';
  1113. if (file.pubMagicMetadata != null &&
  1114. file.pubMagicMetadata.editedTime != null) {
  1115. // override existing creationTime to avoid re-writing all queries related
  1116. // to loading the gallery
  1117. row[columnCreationTime] = file.pubMagicMetadata.editedTime;
  1118. }
  1119. return row;
  1120. }
  1121. File _getFileFromRow(Map<String, dynamic> row) {
  1122. final file = File();
  1123. file.generatedID = row[columnGeneratedID];
  1124. file.localID = row[columnLocalID];
  1125. file.uploadedFileID =
  1126. row[columnUploadedFileID] == -1 ? null : row[columnUploadedFileID];
  1127. file.ownerID = row[columnOwnerID];
  1128. file.collectionID =
  1129. row[columnCollectionID] == -1 ? null : row[columnCollectionID];
  1130. file.title = row[columnTitle];
  1131. file.deviceFolder = row[columnDeviceFolder];
  1132. if (row[columnLatitude] != null && row[columnLongitude] != null) {
  1133. file.location = Location(row[columnLatitude], row[columnLongitude]);
  1134. }
  1135. file.fileType = getFileType(row[columnFileType]);
  1136. file.creationTime = row[columnCreationTime];
  1137. file.modificationTime = row[columnModificationTime];
  1138. file.updationTime = row[columnUpdationTime] ?? -1;
  1139. file.encryptedKey = row[columnEncryptedKey];
  1140. file.keyDecryptionNonce = row[columnKeyDecryptionNonce];
  1141. file.fileDecryptionHeader = row[columnFileDecryptionHeader];
  1142. file.thumbnailDecryptionHeader = row[columnThumbnailDecryptionHeader];
  1143. file.metadataDecryptionHeader = row[columnMetadataDecryptionHeader];
  1144. file.fileSubType = row[columnFileSubType] ?? -1;
  1145. file.duration = row[columnDuration] ?? 0;
  1146. file.exif = row[columnExif];
  1147. file.hash = row[columnHash];
  1148. file.metadataVersion = row[columnMetadataVersion] ?? 0;
  1149. file.mMdVersion = row[columnMMdVersion] ?? 0;
  1150. file.mMdEncodedJson = row[columnMMdEncodedJson] ?? '{}';
  1151. file.pubMmdVersion = row[columnPubMMdVersion] ?? 0;
  1152. file.pubMmdEncodedJson = row[columnPubMMdEncodedJson] ?? '{}';
  1153. return file;
  1154. }
  1155. }