1#!/usr/bin/env python3 2 3# Copyright (C) 2015-2019 Damon Lynch <damonlynch@gmail.com> 4 5# This file is part of Rapid Photo Downloader. 6# 7# Rapid Photo Downloader is free software: you can redistribute it and/or 8# modify it under the terms of the GNU General Public License as published by 9# the Free Software Foundation, either version 3 of the License, or 10# (at your option) any later version. 11# 12# Rapid Photo Downloader is distributed in the hope that it will be useful, 13# but WITHOUT ANY WARRANTY; without even the implied warranty of 14# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 15# GNU General Public License for more details. 16# 17# You should have received a copy of the GNU General Public License 18# along with Rapid Photo Downloader. If not, 19# see <http://www.gnu.org/licenses/>. 20 21__author__ = 'Damon Lynch' 22__copyright__ = "Copyright 2015-2019, Damon Lynch" 23 24import sqlite3 25import os 26import datetime 27from collections import namedtuple 28from typing import Optional, List, Tuple, Any, Sequence 29import logging 30 31from PyQt5.QtCore import Qt 32from tenacity import retry, stop_after_attempt 33 34from raphodo.storage import get_program_data_directory, get_program_cache_directory 35from raphodo.utilities import divide_list_on_length 36from raphodo.photoattributes import PhotoAttributes 37from raphodo.constants import FileType, Sort, Show 38from raphodo.utilities import runs 39 40FileDownloaded = namedtuple('FileDownloaded', 'download_name, download_datetime') 41 42InCache = namedtuple('InCache', 'md5_name, mdatatime, orientation_unknown, failure') 43 44ThumbnailRow = namedtuple( 45 'ThumbnailRow', 46 'uid, scan_id, mtime, marked, file_name, extension, file_type, downloaded, ' 47 'previously_downloaded, job_code, proximity_col1, proximity_col2' 48) 49 50sqlite3.register_adapter(bool, int) 51sqlite3.register_converter("BOOLEAN", lambda v: bool(int(v))) 52sqlite3.register_adapter(FileType, int) 53sqlite3.register_converter("FILETYPE", lambda v: FileType(int(v))) 54 55# The timeout default is five seconds. 56sqlite3_timeout = 10.0 57sqlite3_retry_attempts = 5 58 59class ThumbnailRowsSQL: 60 """ 61 In memory database of thumbnail rows displayed in main window. 62 """ 63 64 def __init__(self) -> None: 65 """ 66 67 """ 68 69 self.db = ':memory:' 70 71 self.sort_order_map = {Qt.AscendingOrder: 'ASC', Qt.DescendingOrder: 'DESC'} 72 self.sort_map = { 73 Sort.checked_state: 'marked', Sort.filename: 'file_name', 74 Sort.extension: 'extension', Sort.file_type: 'file_type', 75 Sort.device: 'device_name' 76 } 77 78 self.conn = sqlite3.connect(self.db, detect_types=sqlite3.PARSE_DECLTYPES) 79 80 self.conn.execute( 81 """CREATE TABLE devices (scan_id INTEGER NOT NULL, device_name TEXT NOT NULL, 82 PRIMARY KEY (scan_id) )""" 83 ) 84 85 self.conn.execute( 86 """CREATE TABLE files ( 87 uid BLOB PRIMARY KEY, 88 scan_id INTEGER NOT NULL, 89 mtime REAL NOT NULL, 90 marked BOOLEAN NOT NULL, 91 file_name TEXT NOT NULL, 92 extension TEXT NOT NULL, 93 file_type FILETYPE NOT NULL, 94 downloaded BOOLEAN NOT NULL, 95 previously_downloaded BOOLEAN NOT NULL, 96 job_code BOOLEAN NOT NULL, 97 proximity_col1 INTEGER NOT NULL, 98 proximity_col2 INTEGER NOT NULL, 99 FOREIGN KEY (scan_id) REFERENCES devices (scan_id) 100 )""" 101 ) 102 103 self.conn.execute('CREATE INDEX IF NOT EXISTS scand_id_idx ON devices (scan_id)') 104 105 self.conn.execute('CREATE INDEX IF NOT EXISTS marked_idx ON files (marked)') 106 107 self.conn.execute('CREATE INDEX IF NOT EXISTS file_type_idx ON files (file_type)') 108 109 self.conn.execute('CREATE INDEX IF NOT EXISTS downloaded_idx ON files (downloaded)') 110 111 self.conn.execute( 112 """CREATE INDEX IF NOT EXISTS previously_downloaded_idx ON files 113 (previously_downloaded)""" 114 ) 115 116 self.conn.execute( 117 """CREATE INDEX IF NOT EXISTS job_code_idx ON files 118 (job_code)""" 119 ) 120 121 self.conn.execute( 122 """CREATE INDEX IF NOT EXISTS proximity_col1_idx ON files 123 (proximity_col1)""" 124 ) 125 126 self.conn.execute( 127 """CREATE INDEX IF NOT EXISTS proximity_col2_idx ON files 128 (proximity_col2)""" 129 ) 130 131 self.conn.commit() 132 133 def add_or_update_device(self, scan_id: int, device_name: str) -> None: 134 query = 'INSERT OR REPLACE INTO devices (scan_id, device_name) VALUES (?,?)' 135 logging.debug('%s (%s, %s)', query, scan_id, device_name) 136 self.conn.execute(query, (scan_id, device_name)) 137 138 self.conn.commit() 139 140 def get_all_devices(self) -> List[int]: 141 query = 'SELECT scan_id FROM devices' 142 rows = self.conn.execute(query).fetchall() 143 return [row[0] for row in rows] 144 145 def add_thumbnail_rows(self, thumbnail_rows: Sequence[ThumbnailRow]) -> None: 146 """ 147 Add a list of rows to database of thumbnail rows 148 """ 149 150 logging.debug("Adding %s rows to db", len(thumbnail_rows)) 151 self.conn.executemany( 152 r"""INSERT INTO files (uid, scan_id, mtime, marked, file_name, 153 extension, file_type, downloaded, previously_downloaded, job_code, proximity_col1, 154 proximity_col2) 155 VALUES (?,?,?,?,?,?,?,?,?,?,?,?)""", thumbnail_rows 156 ) 157 158 self.conn.commit() 159 160 def _build_where(self, scan_id: Optional[int]=None, 161 show: Optional[Show]=None, 162 previously_downloaded: Optional[bool]=None, 163 downloaded: Optional[bool]=None, 164 job_code: Optional[bool]=None, 165 file_type: Optional[FileType]=None, 166 marked: Optional[bool]=None, 167 extensions: Optional[List[str]]=None, 168 proximity_col1: Optional[List[int]]=None, 169 proximity_col2: Optional[List[int]]=None, 170 exclude_scan_ids: Optional[List[int]]=None, 171 uids: Optional[List[bytes]]=None,) -> Tuple[str, List[Any]]: 172 173 where_clauses = [] 174 where_values = [] 175 176 if scan_id is not None: 177 where_clauses.append('scan_id=?') 178 where_values.append(scan_id) 179 180 if marked is not None: 181 where_clauses.append('marked=?') 182 where_values.append(marked) 183 184 if file_type is not None: 185 where_clauses.append('file_type=?') 186 where_values.append(file_type) 187 188 if show == Show.new_only: 189 where_clauses.append('previously_downloaded=0') 190 elif previously_downloaded is not None: 191 where_clauses.append('previously_downloaded=?') 192 where_values.append(previously_downloaded) 193 194 if downloaded is not None: 195 where_clauses.append('downloaded=?') 196 where_values.append(downloaded) 197 198 if job_code is not None: 199 where_clauses.append('job_code=?') 200 where_values.append(job_code) 201 202 if extensions is not None: 203 if len(extensions) == 1: 204 where_clauses.append('extension=?') 205 where_values.append(extensions[0]) 206 else: 207 where_clauses.append('extension IN ({})'.format(','.join('?' * len(extensions)))) 208 where_values.extend(extensions) 209 210 if uids is not None: 211 if len(uids) == 1: 212 where_clauses.append('uid=?') 213 where_values.append(uids[0]) 214 else: 215 # assume max host parameters in a single SQL statement is 999 216 if len(uids) > 900: 217 uids = uids[:900] 218 where_clauses.append('uid IN ({})'.format(','.join('?' * len(uids)))) 219 where_values.extend(uids) 220 221 if exclude_scan_ids is not None: 222 if len(exclude_scan_ids) == 1: 223 where_clauses.append(('scan_id!=?')) 224 where_values.append(exclude_scan_ids[0]) 225 else: 226 where_clauses.append('scan_id NOT IN ({})'.format(','.join('?' * len( 227 exclude_scan_ids)))) 228 where_values.extend(exclude_scan_ids) 229 230 for p, col_name in ((proximity_col1, 'proximity_col1'), (proximity_col2, 'proximity_col2')): 231 if not p: 232 continue 233 if len(p) == 1: 234 where_clauses.append('{}=?'.format(col_name)) 235 where_values.append(p[0]) 236 else: 237 p.sort() 238 or_clauses = [] 239 for first, last in runs(p): 240 if first == last: 241 or_clauses.append('{}=?'.format(col_name)) 242 where_values.append(first) 243 else: 244 or_clauses.append( 245 '({} BETWEEN ? AND ?)'.format(col_name, first, last) 246 ) 247 where_values.extend((first, last)) 248 where_clauses.append('({})'.format(' OR '.join(or_clauses))) 249 250 where = ' AND '.join(where_clauses) 251 return where, where_values 252 253 def _build_sort(self, sort_by: Sort, sort_order: Qt.SortOrder) -> str: 254 if sort_by == Sort.modification_time: 255 sort = 'ORDER BY mtime {}'.format(self.sort_order_map[sort_order]) 256 else: 257 sort = 'ORDER BY {0} {1}, mtime {1}'.format( 258 self.sort_map[sort_by], self.sort_order_map[sort_order] 259 ) 260 return sort 261 262 def get_view(self, sort_by: Sort, 263 sort_order: Qt.SortOrder, 264 show: Show, 265 proximity_col1: Optional[List[int]] = None, 266 proximity_col2: Optional[List[int]] = None) -> List[Tuple[bytes, bool]]: 267 268 where, where_values = self._build_where( 269 show=show, proximity_col1=proximity_col1, proximity_col2=proximity_col2 270 ) 271 272 sort = self._build_sort(sort_by, sort_order) 273 274 query = 'SELECT uid, marked FROM files' 275 276 if sort_by == Sort.device: 277 query = '{} NATURAL JOIN devices'.format(query) 278 279 if where: 280 query = '{} WHERE {}'.format(query, where) 281 282 query = '{} {}'.format(query, sort) 283 284 if where: 285 logging.debug('%s %s', query, where_values) 286 return self.conn.execute(query, tuple(where_values)).fetchall() 287 else: 288 logging.debug('%s', query) 289 return self.conn.execute(query).fetchall() 290 291 def get_first_uid_from_uid_list(self, sort_by: Sort, 292 sort_order: Qt.SortOrder, 293 show: Show, 294 uids: List[bytes], 295 proximity_col1: Optional[List[int]] = None, 296 proximity_col2: Optional[List[int]] = None) -> Optional[bytes]: 297 """ 298 Given a list of uids, and sort and filtering criteria, return the first 299 uid that the user will have displayed -- if any are displayed. 300 """ 301 302 where, where_values = self._build_where( 303 show=show, proximity_col1=proximity_col1, proximity_col2=proximity_col2, uids=uids 304 ) 305 306 sort = self._build_sort(sort_by, sort_order) 307 308 query = 'SELECT uid FROM files' 309 310 if sort_by == Sort.device: 311 query = '{} NATURAL JOIN devices'.format(query) 312 313 query = '{} WHERE {}'.format(query, where) 314 315 query = '{} {}'.format(query, sort) 316 317 logging.debug('%s (using %s where values)', query, len(where_values)) 318 row = self.conn.execute(query, tuple(where_values)).fetchone() 319 if row: 320 return row[0] 321 return None 322 323 def get_uids(self, scan_id: Optional[int]=None, 324 show: Optional[Show]=None, 325 previously_downloaded: Optional[bool]=None, 326 downloaded: Optional[bool]=None, 327 job_code: Optional[bool]=None, 328 file_type: Optional[FileType]=None, 329 marked: Optional[bool]=None, 330 proximity_col1: Optional[List[int]]=None, 331 proximity_col2: Optional[List[int]]=None, 332 exclude_scan_ids: Optional[List[int]]=None, 333 return_file_name=False) -> List[bytes]: 334 335 where, where_values = self._build_where( 336 scan_id=scan_id, show=show, 337 previously_downloaded=previously_downloaded, 338 downloaded=downloaded, file_type=file_type, 339 job_code=job_code, 340 marked=marked, proximity_col1=proximity_col1, 341 proximity_col2=proximity_col2, 342 exclude_scan_ids=exclude_scan_ids 343 ) 344 345 if return_file_name: 346 query = 'SELECT file_name FROM files' 347 else: 348 query = 'SELECT uid FROM files' 349 350 if where: 351 query = '{} WHERE {}'.format(query, where) 352 353 if where_values: 354 logging.debug('%s %s', query, where_values) 355 rows = self.conn.execute(query, tuple(where_values)).fetchall() 356 else: 357 logging.debug('%s', query) 358 rows = self.conn.execute(query).fetchall() 359 return [row[0] for row in rows] 360 361 def get_count(self, scan_id: Optional[int]=None, 362 show: Optional[Show]=None, 363 previously_downloaded: Optional[bool]=None, 364 downloaded: Optional[bool]=None, 365 job_code: Optional[bool]=None, 366 file_type: Optional[FileType]=None, 367 marked: Optional[bool] = None, 368 proximity_col1: Optional[List[int]]=None, 369 proximity_col2: Optional[List[int]]=None) -> int: 370 371 where, where_values = self._build_where( 372 scan_id=scan_id, show=show, 373 previously_downloaded=previously_downloaded, 374 downloaded=downloaded, job_code=job_code, 375 file_type=file_type, 376 marked=marked, proximity_col1=proximity_col1, 377 proximity_col2=proximity_col2 378 ) 379 380 query = 'SELECT COUNT(*) FROM files' 381 382 if where: 383 query = '{} WHERE {}'.format(query, where) 384 385 if where_values: 386 # logging.debug('%s %s', query, where_values) 387 rows = self.conn.execute(query, tuple(where_values)).fetchone() 388 else: 389 # logging.debug('%s', query) 390 rows = self.conn.execute(query).fetchone() 391 return rows[0] 392 393 def validate_uid(self, uid: bytes) -> None: 394 rows = self.conn.execute('SELECT uid FROM files WHERE uid=?', (uid, )).fetchall() 395 if not rows: 396 raise KeyError('UID does not exist in database') 397 398 def set_marked(self, uid: bytes, marked: bool) -> None: 399 query = 'UPDATE files SET marked=? WHERE uid=?' 400 logging.debug('%s (%s, %s)', query, marked, uid) 401 self.conn.execute(query, (marked, uid)) 402 self.conn.commit() 403 404 def set_all_marked_as_unmarked(self, scan_id: int=None) -> None: 405 if scan_id is None: 406 query = 'UPDATE files SET marked=0 WHERE marked=1' 407 logging.debug(query) 408 self.conn.execute(query) 409 else: 410 query = 'UPDATE files SET marked=0 WHERE marked=1 AND scan_id=?' 411 logging.debug('%s (%s)', query, scan_id) 412 self.conn.execute(query, (scan_id, )) 413 self.conn.commit() 414 415 def _update_marked(self, uids: List[bytes], marked: bool) -> None: 416 query = 'UPDATE files SET marked=? WHERE uid IN ({})' 417 logging.debug('%s (%s on %s uids)', query, marked, len(uids)) 418 self.conn.execute(query.format(','.join('?' * len(uids))), [marked] + uids) 419 420 def _update_previously_downloaded(self, uids: List[bytes], previously_downloaded: bool) -> None: 421 query = 'UPDATE files SET previously_downloaded=? WHERE uid IN ({})' 422 logging.debug('%s (%s on %s uids)', query, previously_downloaded, len(uids)) 423 self.conn.execute(query.format(','.join('?' * len(uids))), [previously_downloaded] + uids) 424 425 def _set_list_values(self, uids: List[bytes], update_value, value) -> None: 426 if len(uids) == 0: 427 return 428 429 # Limit to number of parameters: 900 430 # See https://www.sqlite.org/limits.html 431 if len(uids) > 900: 432 uid_chunks = divide_list_on_length(uids, 900) 433 for chunk in uid_chunks: 434 update_value(chunk, value) 435 else: 436 update_value(uids, value) 437 self.conn.commit() 438 439 def set_list_marked(self, uids: List[bytes], marked: bool) -> None: 440 self._set_list_values(uids=uids, update_value=self._update_marked, value=marked) 441 442 def set_list_previously_downloaded(self, uids: List[bytes], 443 previously_downloaded: bool) -> None: 444 self._set_list_values( 445 uids=uids, update_value=self._update_previously_downloaded, value=previously_downloaded 446 ) 447 448 def set_downloaded(self, uid: bytes, downloaded: bool) -> None: 449 query = 'UPDATE files SET downloaded=? WHERE uid=?' 450 logging.debug('%s (%s, <uid>)', query, downloaded) 451 self.conn.execute(query, (downloaded, uid)) 452 self.conn.commit() 453 454 def set_job_code_assigned(self, uids: List[bytes], job_code: bool) -> None: 455 if len(uids) == 1: 456 query = 'UPDATE files SET job_code=? WHERE uid=?' 457 # logging.debug('%s (%s, <uid>)', query, job_code) 458 self.conn.execute(query, (job_code, uids[0])) 459 else: 460 # Limit to number of parameters: 900 461 # See https://www.sqlite.org/limits.html 462 if len(uids) > 900: 463 name_chunks = divide_list_on_length(uids, 900) 464 for chunk in name_chunks: 465 self._mass_set_job_code_assigned(chunk, job_code) 466 else: 467 self._mass_set_job_code_assigned(uids, job_code) 468 self.conn.commit() 469 470 def _mass_set_job_code_assigned(self, uids: List[bytes], job_code: bool) -> None: 471 query = 'UPDATE files SET job_code=? WHERE uid IN ({})' 472 logging.debug('%s (%s files)', query, len(uids)) 473 self.conn.execute(query.format( 474 ','.join('?' * len(uids))), [job_code] + uids) 475 476 def assign_proximity_groups(self, groups: Sequence[Tuple[int, int, bytes]]) -> None: 477 query = 'UPDATE files SET proximity_col1=?, proximity_col2=? WHERE uid=?' 478 logging.debug('%s (%s operations)', query, len(groups)) 479 self.conn.executemany(query, groups) 480 self.conn.commit() 481 482 def get_uids_for_device(self, scan_id: int) -> List[int]: 483 query = 'SELECT uid FROM files WHERE scan_id=?' 484 logging.debug('%s (%s, )', query, scan_id) 485 rows = self.conn.execute(query, (scan_id, )).fetchall() 486 return [row[0] for row in rows] 487 488 def any_files_marked(self, scan_id: Optional[int]=None) -> bool: 489 if scan_id is None: 490 row = self.conn.execute('SELECT uid FROM files WHERE marked=1 LIMIT 1').fetchone() 491 else: 492 row = self.conn.execute( 493 'SELECT uid FROM files WHERE marked=1 AND scan_id=? LIMIT 1', (scan_id, ) 494 ).fetchone() 495 return row is not None 496 497 def any_files_to_download(self, scan_id: Optional[int]=None) -> bool: 498 if scan_id is not None: 499 row = self.conn.execute( 500 'SELECT uid FROM files WHERE downloaded=0 AND scan_id=? LIMIT 1', (scan_id,) 501 ).fetchone() 502 else: 503 row = self.conn.execute('SELECT uid FROM files WHERE downloaded=0 LIMIT 1').fetchone() 504 return row is not None 505 506 def any_files_download_completed(self) -> bool: 507 row = self.conn.execute('SELECT uid FROM files WHERE downloaded=1 LIMIT 1').fetchone() 508 return row is not None 509 510 def any_files(self, scan_id: Optional[int]=None) -> bool: 511 """ 512 Determine if there are any files associated with this scan_id, of if no scan_id 513 is specified, any file at all 514 515 :param scan_id: optional device to check 516 :return: True if found, else False 517 """ 518 519 if scan_id is not None: 520 row = self.conn.execute( 521 'SELECT uid FROM files WHERE scan_id=? LIMIT 1', (scan_id,) 522 ).fetchone() 523 else: 524 row = self.conn.execute('SELECT uid FROM files LIMIT 1').fetchone() 525 return row is not None 526 527 def any_files_with_extensions(self, scan_id: int, extensions: List[str]) -> bool: 528 where, where_values = self._build_where(scan_id=scan_id, extensions=extensions) 529 query = 'SELECT uid FROM files' 530 531 if where: 532 query = '{} WHERE {}'.format(query, where) 533 534 if where_values: 535 logging.debug('%s %s', query, where_values) 536 row = self.conn.execute(query, tuple(where_values)).fetchone() 537 else: 538 logging.debug('%s', query) 539 row = self.conn.execute(query).fetchone() 540 return row is not None 541 542 def any_files_of_type(self, scan_id: int, file_type: FileType) -> bool: 543 where, where_values = self._build_where(scan_id=scan_id, file_type=file_type) 544 query = 'SELECT uid FROM files' 545 if where: 546 query = '{} WHERE {}'.format(query, where) 547 548 if where_values: 549 logging.debug('%s %s', query, where_values) 550 row = self.conn.execute(query, tuple(where_values)).fetchone() 551 else: 552 logging.debug('%s', query) 553 row = self.conn.execute(query).fetchone() 554 return row is not None 555 556 def get_single_file_of_type(self, file_type: FileType, 557 downloaded: Optional[bool] = None, 558 scan_id: Optional[int]=None, 559 exclude_scan_ids: Optional[List[int]] = None) -> Optional[bytes]: 560 where, where_values = self._build_where( 561 scan_id=scan_id, downloaded=downloaded, file_type=file_type, 562 exclude_scan_ids=exclude_scan_ids 563 ) 564 query = 'SELECT uid FROM files' 565 566 if where: 567 query = '{} WHERE {}'.format(query, where) 568 569 if where_values: 570 logging.debug('%s %s', query, where_values) 571 row = self.conn.execute(query, tuple(where_values)).fetchone() 572 else: 573 logging.debug('%s', query) 574 row = self.conn.execute(query).fetchone() 575 576 if row is None: 577 return None 578 return row[0] 579 580 def any_marked_file_no_job_code(self) -> bool: 581 row = self.conn.execute( 582 'SELECT uid FROM files WHERE marked=1 AND job_code=0 LIMIT 1' 583 ).fetchone() 584 return row is not None 585 586 def _any_not_previously_downloaded(self, uids: List[bytes]) -> bool: 587 query = 'SELECT uid FROM files WHERE uid IN ({}) AND previously_downloaded=0 LIMIT 1' 588 logging.debug('%s (%s files)', query, len(uids)) 589 row = self.conn.execute(query.format(','.join('?' * len(uids))), uids).fetchone() 590 return row is not None 591 592 def any_not_previously_downloaded(self, uids: List[bytes]) -> bool: 593 """ 594 595 :param uids: list of UIDs to check 596 :return: True if any of the files associated with the UIDs have not been 597 previously downloaded 598 """ 599 if len(uids) > 900: 600 uid_chunks = divide_list_on_length(uids, 900) 601 for chunk in uid_chunks: 602 if self._any_not_previously_downloaded(uids=uid_chunks): 603 return True 604 return False 605 else: 606 return self._any_not_previously_downloaded(uids=uids) 607 608 def _delete_uids(self, uids: List[bytes]) -> None: 609 query = 'DELETE FROM files WHERE uid IN ({})' 610 logging.debug('%s (%s files)', query, len(uids)) 611 self.conn.execute(query.format(','.join('?' * len(uids))), uids) 612 613 def delete_uids(self, uids: List[bytes]) -> None: 614 """ 615 Deletes thumbnails from SQL cache 616 :param uids: list of uids to delete 617 """ 618 619 if len(uids) == 0: 620 return 621 622 # Limit to number of parameters: 900 623 # See https://www.sqlite.org/limits.html 624 if len(uids) > 900: 625 name_chunks = divide_list_on_length(uids, 900) 626 for chunk in name_chunks: 627 self._delete_uids(chunk) 628 else: 629 self._delete_uids(uids) 630 self.conn.commit() 631 632 def delete_files_by_scan_id(self, scan_id: int, downloaded: Optional[bool]=None) -> None: 633 query = 'DELETE FROM files' 634 where, where_values = self._build_where(scan_id=scan_id, downloaded=downloaded) 635 query = '{} WHERE {}'.format(query, where) 636 logging.debug('%s (%s)', query, where_values) 637 self.conn.execute(query, where_values) 638 self.conn.commit() 639 640 def delete_device(self, scan_id: int) -> None: 641 query = 'DELETE FROM devices WHERE scan_id=?' 642 logging.debug('%s (%s, )', query, scan_id) 643 self.conn.execute(query, (scan_id, )) 644 self.conn.commit() 645 646 647class DownloadedSQL: 648 """ 649 Previous file download detection. 650 651 Used to detect if a file has been downloaded before. A file is the 652 same if the file name (excluding path), size and modification time 653 are the same. For performance reasons, Exif information is never 654 checked. 655 """ 656 657 def __init__(self, data_dir: str = None) -> None: 658 """ 659 :param data_dir: where the database is saved. If None, use 660 default 661 """ 662 if data_dir is None: 663 data_dir = get_program_data_directory(create_if_not_exist=True) 664 665 self.db = os.path.join(data_dir, 'downloaded_files.sqlite') 666 self.table_name = 'downloaded' 667 self.update_table() 668 669 670 def update_table(self, reset: bool = False) -> None: 671 """ 672 Create or update the database table 673 :param reset: if True, delete the contents of the table and 674 build it 675 """ 676 677 conn = sqlite3.connect(self.db, detect_types=sqlite3.PARSE_DECLTYPES) 678 679 if reset: 680 conn.execute(r"""DROP TABLE IF EXISTS {tn}""".format( 681 tn=self.table_name) 682 ) 683 conn.execute("VACUUM") 684 685 conn.execute( 686 """CREATE TABLE IF NOT EXISTS {tn} ( 687 file_name TEXT NOT NULL, 688 mtime REAL NOT NULL, 689 size INTEGER NOT NULL, 690 download_name TEXT NOT NULL, 691 download_datetime timestamp, 692 PRIMARY KEY (file_name, mtime, size) 693 )""".format(tn=self.table_name) 694 ) 695 696 # Use the character . to for download_name and path to indicate the user manually marked a 697 # file as previously downloaded 698 699 conn.execute( 700 """CREATE INDEX IF NOT EXISTS download_datetime_idx ON 701 {tn} (download_name)""".format(tn=self.table_name) 702 ) 703 704 conn.commit() 705 conn.close() 706 707 @retry(stop=stop_after_attempt(sqlite3_retry_attempts)) 708 def add_downloaded_file(self, name: str, size: int, 709 modification_time: float, download_full_file_name: str) -> None: 710 """ 711 Add file to database of downloaded files 712 :param name: original filename of photo / video, without path 713 :param size: file size 714 :param modification_time: file modification time 715 :param download_full_file_name: renamed file including path, 716 or the character . that the user manually marked the file 717 as previously downloaded 718 """ 719 conn = sqlite3.connect(self.db, timeout=sqlite3_timeout) 720 721 logging.debug('Adding %s to downloaded files', name) 722 723 try: 724 conn.execute( 725 r"""INSERT OR REPLACE INTO {tn} (file_name, size, mtime, 726 download_name, download_datetime) VALUES (?,?,?,?,?)""".format(tn=self.table_name), 727 (name, size, modification_time, download_full_file_name, datetime.datetime.now()) 728 ) 729 except sqlite3.OperationalError as e: 730 logging.warning( 731 "Database error adding download file %s: %s. May retry.", 732 download_full_file_name, e 733 ) 734 conn.close() 735 raise sqlite3.OperationalError from e 736 else: 737 conn.commit() 738 conn.close() 739 740 def file_downloaded(self, name: str, 741 size: int, modification_time: float) -> Optional[FileDownloaded]: 742 """ 743 Returns download path and filename if a file with matching 744 name, modification time and size has previously been downloaded 745 :param name: file name, not including path 746 :param size: file size in bytes 747 :param modification_time: file modification time 748 :return: download name (including path) and when it was 749 downloaded, else None if never downloaded 750 """ 751 conn = sqlite3.connect(self.db, detect_types=sqlite3.PARSE_DECLTYPES) 752 c = conn.cursor() 753 c.execute( 754 """SELECT download_name, download_datetime as [timestamp] FROM {tn} WHERE 755 file_name=? AND size=? AND mtime=?""".format(tn=self.table_name), 756 (name, size, modification_time) 757 ) 758 row = c.fetchone() 759 if row is not None: 760 return FileDownloaded._make(row) 761 else: 762 return None 763 764 765class CacheSQL: 766 def __init__(self, location: str=None, create_table_if_not_exists: bool=True) -> None: 767 """ 768 769 :param location: path on the file system where the Table exists 770 :param create_table_if_not_exists: 771 """ 772 if location is None: 773 location = get_program_cache_directory(create_if_not_exist=True) 774 self.db = os.path.join(location, self.db_fs_name()) 775 self.table_name = 'cache' 776 if create_table_if_not_exists: 777 self.update_table() 778 779 def db_fs_name(self) -> str: 780 return 'thumbnail_cache.sqlite' 781 782 def cache_exists(self) -> bool: 783 conn = sqlite3.connect(self.db) 784 row = conn.execute( 785 """SELECT name FROM sqlite_master WHERE type='table' AND name='{}'""".format( 786 self.table_name 787 ) 788 ).fetchone() 789 conn.close() 790 return row is not None 791 792 793 def update_table(self, reset: bool=False) -> None: 794 """ 795 Create or update the database table 796 :param reset: if True, delete the contents of the table and 797 build it 798 """ 799 conn = sqlite3.connect(self.db, detect_types=sqlite3.PARSE_DECLTYPES) 800 801 if reset: 802 conn.execute(r"""DROP TABLE IF EXISTS {tn}""".format(tn=self.table_name)) 803 conn.execute("VACUUM") 804 805 conn.execute( 806 """CREATE TABLE IF NOT EXISTS {tn} ( 807 uri TEXT NOT NULL, 808 mtime REAL NOT NULL, 809 mdatatime REAL, 810 size INTEGER NOT NULL, 811 md5_name TEXT NOT NULL, 812 orientation_unknown BOOLEAN NOT NULL, 813 failure BOOLEAN NOT NULL, 814 PRIMARY KEY (uri, mtime, size) 815 )""".format(tn=self.table_name) 816 ) 817 818 conn.execute("""CREATE INDEX IF NOT EXISTS md5_name_idx ON 819 {tn} (md5_name)""".format(tn=self.table_name)) 820 821 conn.commit() 822 conn.close() 823 824 @retry(stop=stop_after_attempt(sqlite3_retry_attempts)) 825 def add_thumbnail(self, uri: str, 826 size: int, 827 mtime: float, 828 mdatatime: float, 829 md5_name: str, 830 orientation_unknown: bool, 831 failure: bool) -> None: 832 """ 833 Add file to database of downloaded files 834 :param uri: original filename of photo / video with path 835 :param size: file size 836 :param mtime: file modification time 837 :param mdatatime: file time recorded in metadata 838 :param md5_name: full file name converted to md5 839 :param orientation_unknown: if True, the orientation of the 840 file could not be determined, else False 841 :param failure: if True, indicates the thumbnail could not be 842 generated, otherwise False 843 """ 844 845 conn = sqlite3.connect(self.db, timeout=sqlite3_timeout) 846 847 try: 848 conn.execute( 849 r"""INSERT OR REPLACE INTO {tn} (uri, size, mtime, mdatatime, 850 md5_name, orientation_unknown, failure) VALUES (?,?,?,?,?,?,?)""".format( 851 tn=self.table_name 852 ), (uri, size, mtime, mdatatime, md5_name, orientation_unknown, failure) 853 ) 854 except sqlite3.OperationalError as e: 855 logging.warning("Database error adding thumbnail for %s: %s. May retry.", uri, e) 856 conn.close() 857 raise sqlite3.OperationalError from e 858 else: 859 conn.commit() 860 conn.close() 861 862 @retry(stop=stop_after_attempt(sqlite3_retry_attempts)) 863 def have_thumbnail(self, uri: str, size: int, mtime: float) -> Optional[InCache]: 864 """ 865 Returns download path and filename if a file with matching 866 name, modification time and size has previously been downloaded 867 :param uri: file name, including path 868 :param size: file size in bytes 869 :param mtime: file modification time 870 :return: md5 name (excluding path) and if the value indicates a 871 thumbnail generation failure, else None if thumbnail not 872 present 873 """ 874 875 conn = sqlite3.connect(self.db, timeout=sqlite3_timeout) 876 877 try: 878 c = conn.cursor() 879 c.execute( 880 """SELECT md5_name, mdatatime, orientation_unknown, failure FROM {tn} WHERE 881 uri=? AND size=? AND mtime=?""".format(tn=self.table_name), (uri, size, mtime) 882 ) 883 row = c.fetchone() 884 except sqlite3.OperationalError as e: 885 logging.warning("Database error reading thumbnail for %s: %s. May retry.", uri, e) 886 conn.close() 887 raise sqlite3.OperationalError from e 888 889 if row is not None: 890 return InCache._make(row) 891 else: 892 return None 893 894 @retry(stop=stop_after_attempt(sqlite3_retry_attempts)) 895 def _delete(self, names: List[str], conn): 896 conn.execute("""DELETE FROM {tn} WHERE md5_name IN ({values})""".format( 897 tn=self.table_name, values=','.join('?' * len(names))), names) 898 899 def delete_thumbnails(self, md5_names: List[str]) -> None: 900 """ 901 Deletes thumbnails from SQL cache 902 :param md5_names: list of names, without path 903 """ 904 905 if len(md5_names) == 0: 906 return 907 908 conn = sqlite3.connect(self.db) 909 # Limit to number of parameters: 900 910 # See https://www.sqlite.org/limits.html 911 try: 912 if len(md5_names) > 900: 913 name_chunks = divide_list_on_length(md5_names, 900) 914 for chunk in name_chunks: 915 self._delete(chunk, conn) 916 else: 917 self._delete(md5_names, conn) 918 except sqlite3.OperationalError as e: 919 logging.error("Database error while deleting %s thumbnails: %s", len(md5_names), e) 920 else: 921 conn.commit() 922 conn.close() 923 924 925 def no_thumbnails(self) -> int: 926 """ 927 :return: how many thumbnails are in the db 928 """ 929 930 conn = sqlite3.connect(self.db) 931 c = conn.cursor() 932 c.execute('SELECT COUNT(*) FROM {tn}'.format(tn=self.table_name)) 933 count = c.fetchall() 934 return count[0][0] 935 936 def md5_names(self) -> List[Tuple[str]]: 937 conn = sqlite3.connect(self.db) 938 c = conn.cursor() 939 c.execute('SELECT md5_name FROM {tn}'.format(tn=self.table_name)) 940 rows = c.fetchall() 941 return rows 942 943 def vacuum(self) -> None: 944 conn = sqlite3.connect(self.db) 945 conn.execute("VACUUM") 946 conn.close() 947 948class FileFormatSQL: 949 def __init__(self, data_dir: str=None) -> None: 950 """ 951 :param data_dir: where the database is saved. If None, use 952 default 953 """ 954 if data_dir is None: 955 data_dir = get_program_data_directory(create_if_not_exist=True) 956 957 self.db = os.path.join(data_dir, 'file_formats.sqlite') 958 self.table_name = 'formats' 959 self.update_table() 960 961 def update_table(self, reset: bool=False) -> None: 962 """ 963 Create or update the database table 964 :param reset: if True, delete the contents of the table and 965 build it 966 """ 967 968 conn = sqlite3.connect(self.db, detect_types=sqlite3.PARSE_DECLTYPES) 969 970 if reset: 971 conn.execute(r"""DROP TABLE IF EXISTS {tn}""".format( 972 tn=self.table_name)) 973 conn.execute("VACUUM") 974 975 conn.execute( 976 """CREATE TABLE IF NOT EXISTS {tn} ( 977 id INTEGER PRIMARY KEY, 978 extension TEXT NOT NULL, 979 camera TEXT NOT NULL, 980 size INTEGER NOT NULL, 981 orientation_offset INTEGER, 982 datetime_offset INTEGER, 983 cache INTEGER NOT NULL, 984 app0 INTEGER, 985 orientation TEXT, 986 exif_thumbnail TEXT, 987 thumbnail_preview_same INTEGER, 988 preview_source TEXT, 989 previews TEXT 990 )""".format(tn=self.table_name) 991 ) 992 993 conn.execute("""CREATE INDEX IF NOT EXISTS extension_idx ON 994 {tn} (extension)""".format(tn=self.table_name)) 995 conn.execute("""CREATE INDEX IF NOT EXISTS camera_idx ON 996 {tn} (camera)""".format(tn=self.table_name)) 997 998 conn.commit() 999 conn.close() 1000 1001 def add_format(self, pa: PhotoAttributes) -> None: 1002 conn = sqlite3.connect(self.db) 1003 c = conn.cursor() 1004 c.execute( 1005 """INSERT OR IGNORE INTO {tn} (extension, camera, size, orientation_offset, 1006 datetime_offset, cache, app0, orientation, exif_thumbnail, thumbnail_preview_same, 1007 preview_source, previews) 1008 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""".format(tn=self.table_name), 1009 ( 1010 pa.ext, 1011 pa.model, 1012 pa.total, 1013 pa.minimum_exif_read_size_in_bytes_orientation, 1014 pa.minimum_exif_read_size_in_bytes_datetime, 1015 pa.bytes_cached_post_thumb, 1016 pa.has_app0, 1017 pa.orientation, 1018 pa.exif_thumbnail_details, 1019 pa.exif_thumbnail_and_preview_identical, 1020 pa.preview_source, 1021 pa.preview_size_and_types 1022 ) 1023 ) 1024 1025 conn.commit() 1026 conn.close() 1027 1028 def get_orientation_bytes(self, extension: str) -> Optional[int]: 1029 conn = sqlite3.connect(self.db) 1030 c = conn.cursor() 1031 c.execute( 1032 """SELECT max(orientation_offset) FROM {tn} WHERE extension=(?)""".format( 1033 tn=self.table_name 1034 ), (extension,) 1035 ) 1036 row = c.fetchone() 1037 if row is not None: 1038 return row[0] 1039 return None 1040 1041 def get_datetime_bytes(self, extension: str) -> Optional[int]: 1042 conn = sqlite3.connect(self.db) 1043 c = conn.cursor() 1044 c.execute( 1045 """SELECT max(datetime_offset) FROM {tn} WHERE extension=(?)""".format( 1046 tn=self.table_name 1047 ), (extension,) 1048 ) 1049 row = c.fetchone() 1050 if row is not None: 1051 return row[0] 1052 return None 1053 1054 1055if __name__ == '__main__': 1056 import uuid 1057 d = ThumbnailRowsSQL() 1058 uid = uuid.uuid4().bytes 1059 scan_id = 0 1060 device_name = '1D X' 1061 mtime = datetime.datetime.now().timestamp() 1062 marked = True 1063 file_name = 'image.cr2' 1064 extension= 'cr2' 1065 file_type = FileType.photo 1066 downloaded = False 1067 previously_downloaded = True 1068 proximity_col1 = -1 1069 proximity_col2 = -1 1070 1071 d.add_or_update_device(scan_id=scan_id, device_name=device_name) 1072 1073 tr = ThumbnailRow( 1074 uid=uid, scan_id=scan_id, marked=marked, mtime=mtime, file_name=file_name, 1075 file_type=file_type, extension=extension, downloaded=downloaded, 1076 previously_downloaded=previously_downloaded, job_code=False, 1077 proximity_col1=proximity_col1, proximity_col2=proximity_col2 1078 ) 1079 1080 uid = uuid.uuid4().bytes 1081 scan_id = 1 1082 device_name = 'NEXUS 5X' 1083 mtime = datetime.datetime.now().timestamp() 1084 marked = True 1085 file_name = 'image.dng' 1086 extension= 'dng' 1087 file_type = FileType.photo 1088 downloaded = False 1089 previously_downloaded = False 1090 1091 d.add_or_update_device(scan_id=scan_id, device_name=device_name) 1092 1093 tr2 = ThumbnailRow( 1094 uid=uid, scan_id=scan_id, marked=marked, mtime=mtime, file_name=file_name, 1095 file_type=file_type, extension=extension, downloaded=downloaded, 1096 previously_downloaded=previously_downloaded, job_code=False, 1097 proximity_col1=proximity_col1, proximity_col2=proximity_col2 1098 ) 1099 1100 1101 uid = uuid.uuid4().bytes 1102 mtime = datetime.datetime.now().timestamp() 1103 marked = False 1104 file_name = 'image.mp4' 1105 extension= 'mp4' 1106 file_type = FileType.video 1107 downloaded = False 1108 previously_downloaded = True 1109 1110 tr3 = ThumbnailRow( 1111 uid=uid, scan_id=scan_id, marked=marked, mtime=mtime, file_name=file_name, 1112 file_type=file_type, extension=extension, downloaded=downloaded, 1113 previously_downloaded=previously_downloaded, job_code=False, 1114 proximity_col1=proximity_col1, proximity_col2=proximity_col2 1115 ) 1116 1117 d.add_thumbnail_rows([tr, tr2, tr3]) 1118 1119 cursor = d.conn.cursor() 1120 cursor.execute('SELECT * FROM files') 1121 for row in map(ThumbnailRow._make, cursor.fetchall()): 1122 print(row) 1123 1124 d.set_marked(uid, False) 1125 d.set_downloaded(uid, True) 1126 1127 print(d.get_view(sort_by=Sort.device, sort_order=Qt.DescendingOrder, show=Show.all)) 1128 1129 print(d.get_uids_for_device(0)) 1130 print(d.get_uids_for_device(1)) 1131 print(d.any_files_marked()) 1132 1133 print(d.get_uids(marked=True, return_file_name=True)) 1134 print(d.get_uids(marked=False, return_file_name=True)) 1135 print(d.get_uids(downloaded=False, return_file_name=True)) 1136 print(d.get_uids(downloaded=True, return_file_name=True)) 1137 print(d.get_uids(file_type=FileType.video, return_file_name=True)) 1138 print("next two lines should be identical") 1139 print(d.get_uids(scan_id=0, file_type=FileType.photo, return_file_name=True)) 1140 print(d.get_uids(exclude_scan_ids=[1,], file_type=FileType.photo, return_file_name=True)) 1141 print(d.get_uids(previously_downloaded=False, return_file_name=True)) 1142 print(d.get_count(scan_id=0)) 1143 print(d.get_count(previously_downloaded=True)) 1144 print(d.get_count(show=Show.new_only)) 1145 print(d.get_count(marked=True)) 1146 uids = d.get_uids(downloaded=False) 1147 print("UIDs", len(uids), "; available to download?", d.any_files_to_download()) 1148 d.set_list_marked(uids, marked=False) 1149 print(d.get_count(marked=True)) 1150 d.set_list_marked(uids, marked=True) 1151 print(d.get_count(marked=True)) 1152 print(d.any_files_with_extensions(scan_id=0, extensions=['cr2', 'dng'])) 1153 print(d.any_files_with_extensions(scan_id=0, extensions=['nef', 'dng'])) 1154 print(d.any_files_with_extensions(scan_id=0, extensions=['nef'])) 1155 print(d.any_files_with_extensions(scan_id=0, extensions=['cr2'])) 1156 1157