diff options
Diffstat (limited to 'raphodo/rpdsql.py')
| -rwxr-xr-x | raphodo/rpdsql.py | 957 |
1 files changed, 957 insertions, 0 deletions
diff --git a/raphodo/rpdsql.py b/raphodo/rpdsql.py new file mode 100755 index 0000000..7224cc2 --- /dev/null +++ b/raphodo/rpdsql.py @@ -0,0 +1,957 @@ +#!/usr/bin/env python3 + +# Copyright (C) 2015-2017 Damon Lynch <damonlynch@gmail.com> + +# This file is part of Rapid Photo Downloader. +# +# Rapid Photo Downloader is free software: you can redistribute it and/or +# modify it under the terms of the GNU General Public License as published by +# the Free Software Foundation, either version 3 of the License, or +# (at your option) any later version. +# +# Rapid Photo Downloader is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Rapid Photo Downloader. If not, +# see <http://www.gnu.org/licenses/>. + +__author__ = 'Damon Lynch' +__copyright__ = "Copyright 2015-2017, Damon Lynch" + +import sqlite3 +import os +import datetime +from collections import namedtuple +from typing import Optional, List, Tuple, Any, Sequence +import logging + +from PyQt5.QtCore import Qt + +from raphodo.storage import (get_program_data_directory, get_program_cache_directory) +from raphodo.utilities import divide_list_on_length +from raphodo.photoattributes import PhotoAttributes +from raphodo.constants import FileType, Sort, Show +from raphodo.utilities import runs + +FileDownloaded = namedtuple('FileDownloaded', 'download_name, download_datetime') + +InCache = namedtuple('InCache', 'md5_name, mdatatime, orientation_unknown, failure') + +ThumbnailRow = namedtuple('ThumbnailRow', 'uid, scan_id, mtime, marked, file_name, extension, ' + 'file_type, downloaded, previously_downloaded, ' + 'job_code, proximity_col1, proximity_col2') + +sqlite3.register_adapter(bool, int) +sqlite3.register_converter("BOOLEAN", lambda v: bool(int(v))) +sqlite3.register_adapter(FileType, int) +sqlite3.register_converter("FILETYPE", lambda v: FileType(int(v))) + + +class ThumbnailRowsSQL: + """ + In memory database of thumbnail rows displayed in main window. + """ + + def __init__(self) -> None: + """ + + """ + + self.db = ':memory:' + + self.sort_order_map = {Qt.AscendingOrder: 'ASC', Qt.DescendingOrder: 'DESC'} + self.sort_map = {Sort.checked_state: 'marked', Sort.filename: 'file_name', + Sort.extension: 'extension', Sort.file_type: 'file_type', + Sort.device: 'device_name'} + + self.conn = sqlite3.connect(self.db, detect_types=sqlite3.PARSE_DECLTYPES) + + self.conn.execute("""CREATE TABLE devices ( + scan_id INTEGER NOT NULL, + device_name TEXT NOT NULL, + PRIMARY KEY (scan_id) + )""") + + self.conn.execute("""CREATE TABLE files ( + uid BLOB PRIMARY KEY, + scan_id INTEGER NOT NULL, + mtime REAL NOT NULL, + marked BOOLEAN NOT NULL, + file_name TEXT NOT NULL, + extension TEXT NOT NULL, + file_type FILETYPE NOT NULL, + downloaded BOOLEAN NOT NULL, + previously_downloaded BOOLEAN NOT NULL, + job_code BOOLEAN NOT NULL, + proximity_col1 INTEGER NOT NULL, + proximity_col2 INTEGER NOT NULL, + FOREIGN KEY (scan_id) REFERENCES devices (scan_id) + )""") + + self.conn.execute('CREATE INDEX IF NOT EXISTS scand_id_idx ON devices (scan_id)') + + self.conn.execute('CREATE INDEX IF NOT EXISTS marked_idx ON files (marked)') + + self.conn.execute('CREATE INDEX IF NOT EXISTS file_type_idx ON files (file_type)') + + self.conn.execute('CREATE INDEX IF NOT EXISTS downloaded_idx ON files (downloaded)') + + self.conn.execute("""CREATE INDEX IF NOT EXISTS previously_downloaded_idx ON files + (previously_downloaded)""") + + self.conn.execute("""CREATE INDEX IF NOT EXISTS job_code_idx ON files + (job_code)""") + + self.conn.execute("""CREATE INDEX IF NOT EXISTS proximity_col1_idx ON files + (proximity_col1)""") + + self.conn.execute("""CREATE INDEX IF NOT EXISTS proximity_col2_idx ON files + (proximity_col2)""") + + self.conn.commit() + + def add_or_update_device(self, scan_id: int, device_name: str) -> None: + query = 'INSERT OR REPLACE INTO devices (scan_id, device_name) VALUES (?,?)' + logging.debug('%s (%s, %s)', query, scan_id, device_name) + self.conn.execute(query, (scan_id, device_name)) + + self.conn.commit() + + def get_all_devices(self) -> List[int]: + query = 'SELECT scan_id FROM devices' + rows = self.conn.execute(query).fetchall() + return [row[0] for row in rows] + + def add_thumbnail_rows(self, thumbnail_rows: Sequence[ThumbnailRow]) -> None: + """ + Add a list of rows to database of thumbnail rows + """ + + logging.debug("Adding %s rows to db", len(thumbnail_rows)) + self.conn.executemany(r"""INSERT INTO files (uid, scan_id, mtime, marked, file_name, + extension, file_type, downloaded, previously_downloaded, job_code, proximity_col1, + proximity_col2) + VALUES (?,?,?,?,?,?,?,?,?,?,?,?)""", thumbnail_rows) + + self.conn.commit() + + def _build_where(self, scan_id: Optional[int]=None, + show: Optional[Show]=None, + previously_downloaded: Optional[bool]=None, + downloaded: Optional[bool]=None, + job_code: Optional[bool]=None, + file_type: Optional[FileType]=None, + marked: Optional[bool]=None, + extensions: Optional[List[str]]=None, + proximity_col1: Optional[List[int]]=None, + proximity_col2: Optional[List[int]]=None, + exclude_scan_ids: Optional[List[int]]=None) -> Tuple[str, Tuple[Any]]: + + where_clauses = [] + where_values = [] + + if scan_id is not None: + where_clauses.append('scan_id=?') + where_values.append(scan_id) + + if marked is not None: + where_clauses.append('marked=?') + where_values.append(marked) + + if file_type is not None: + where_clauses.append('file_type=?') + where_values.append(file_type) + + if show == Show.new_only: + where_clauses.append('previously_downloaded=0') + elif previously_downloaded is not None: + where_clauses.append('previously_downloaded=?') + where_values.append(previously_downloaded) + + if downloaded is not None: + where_clauses.append('downloaded=?') + where_values.append(downloaded) + + if job_code is not None: + where_clauses.append('job_code=?') + where_values.append(job_code) + + if extensions is not None: + if len(extensions) == 1: + where_clauses.append('extension=?') + where_values.append(extensions[0]) + else: + where_clauses.append('extension IN ({})'.format(','.join('?' * len(extensions)))) + where_values.extend(extensions) + + if exclude_scan_ids is not None: + if len(exclude_scan_ids) == 1: + where_clauses.append(('scan_id!=?')) + where_values.append(exclude_scan_ids[0]) + else: + where_clauses.append('scan_id NOT IN ({})'.format(','.join('?' * len( + exclude_scan_ids)))) + where_values.extend(exclude_scan_ids) + + for p, col_name in ((proximity_col1, 'proximity_col1'), (proximity_col2, 'proximity_col2')): + if not p: + continue + if len(p) == 1: + where_clauses.append('{}=?'.format(col_name)) + where_values.append(p[0]) + else: + p.sort() + or_clauses = [] + for first, last in runs(p): + if first == last: + or_clauses.append('{}=?'.format(col_name)) + where_values.append(first) + else: + or_clauses.append('({} BETWEEN ? AND ?)'.format(col_name, first, + last)) + where_values.extend((first, last)) + where_clauses.append('({})'.format(' OR '.join(or_clauses))) + + where = ' AND '.join(where_clauses) + return (where, where_values) + + def get_view(self, sort_by: Sort, + sort_order: Qt.SortOrder, + show: Show, + proximity_col1: Optional[List[int]] = None, + proximity_col2: Optional[List[int]] = None) -> List[Tuple[bytes, bool]]: + + where, where_values = self._build_where(show=show, + proximity_col1=proximity_col1, + proximity_col2=proximity_col2) + + if sort_by == Sort.modification_time: + sort = 'ORDER BY mtime {}'.format(self.sort_order_map[sort_order]) + else: + sort = 'ORDER BY {0} {1}, mtime {1}'.format(self.sort_map[sort_by], + self.sort_order_map[sort_order]) + + query = 'SELECT uid, marked FROM files' + + if sort_by == Sort.device: + query = '{} NATURAL JOIN devices'.format(query) + + if where: + query = '{} WHERE {}'.format(query, where) + + query = '{} {}'.format(query, sort) + + if where: + logging.debug('%s %s', query, where_values) + return self.conn.execute(query, tuple(where_values)).fetchall() + else: + logging.debug('%s', query) + return self.conn.execute(query).fetchall() + + def get_uids(self, scan_id: Optional[int]=None, + show: Optional[Show]=None, + previously_downloaded: Optional[bool]=None, + downloaded: Optional[bool]=None, + job_code: Optional[bool]=None, + file_type: Optional[FileType]=None, + marked: Optional[bool]=None, + proximity_col1: Optional[List[int]]=None, + proximity_col2: Optional[List[int]]=None, + exclude_scan_ids: Optional[List[int]]=None, + return_file_name=False) -> List[bytes]: + + where, where_values = self._build_where(scan_id=scan_id, show=show, + previously_downloaded=previously_downloaded, + downloaded=downloaded, file_type=file_type, + job_code=job_code, + marked=marked, proximity_col1=proximity_col1, + proximity_col2=proximity_col2, + exclude_scan_ids=exclude_scan_ids) + + if return_file_name: + query = 'SELECT file_name FROM files' + else: + query = 'SELECT uid FROM files' + + if where: + query = '{} WHERE {}'.format(query, where) + + if where_values: + logging.debug('%s %s', query, where_values) + rows = self.conn.execute(query, tuple(where_values)).fetchall() + else: + logging.debug('%s', query) + rows = self.conn.execute(query).fetchall() + return [row[0] for row in rows] + + def get_count(self, scan_id: Optional[int]=None, + show: Optional[Show]=None, + previously_downloaded: Optional[bool]=None, + downloaded: Optional[bool]=None, + job_code: Optional[bool]=None, + file_type: Optional[FileType]=None, + marked: Optional[bool] = None, + proximity_col1: Optional[List[int]]=None, + proximity_col2: Optional[List[int]]=None) -> int: + + where, where_values = self._build_where(scan_id=scan_id, show=show, + previously_downloaded=previously_downloaded, + downloaded=downloaded, job_code=job_code, + file_type=file_type, + marked=marked, proximity_col1=proximity_col1, + proximity_col2=proximity_col2) + + query = 'SELECT COUNT(*) FROM files' + + if where: + query = '{} WHERE {}'.format(query, where) + + if where_values: + # logging.debug('%s %s', query, where_values) + rows = self.conn.execute(query, tuple(where_values)).fetchone() + else: + # logging.debug('%s', query) + rows = self.conn.execute(query).fetchone() + return rows[0] + + def validate_uid(self, uid: bytes) -> None: + rows = self.conn.execute('SELECT uid FROM files WHERE uid=?', (uid, )).fetchall() + if not rows: + raise KeyError('UID does not exist in database') + + def set_marked(self, uid: bytes, marked: bool) -> None: + query = 'UPDATE files SET marked=? WHERE uid=?' + logging.debug('%s (%s, %s)', query, marked, uid) + self.conn.execute(query, (marked, uid)) + self.conn.commit() + + def set_all_marked_as_unmarked(self, scan_id: int=None) -> None: + if scan_id is None: + query = 'UPDATE files SET marked=0 WHERE marked=1' + logging.debug(query) + self.conn.execute(query) + else: + query = 'UPDATE files SET marked=0 WHERE marked=1 AND scan_id=?' + logging.debug('%s (%s)', query, scan_id) + self.conn.execute(query, (scan_id, )) + self.conn.commit() + + def _update_marked(self, uids: List[bytes], marked: bool) -> None: + query = 'UPDATE files SET marked=? WHERE uid IN ({})' + logging.debug('%s (%s on %s uids)', query, marked, len(uids)) + self.conn.execute(query.format(','.join('?' * len(uids))), [marked] + uids) + + def set_list_marked(self, uids: List[bytes], marked: bool) -> None: + if len(uids) == 0: + return + + # Limit to number of parameters: 999 + # See https://www.sqlite.org/limits.html + if len(uids) > 999: + uid_chunks = divide_list_on_length(uids, 999) + for chunk in uid_chunks: + self._update_marked(chunk, marked) + else: + self._update_marked(uids, marked) + self.conn.commit() + + def set_downloaded(self, uid: bytes, downloaded: bool) -> None: + query = 'UPDATE files SET downloaded=? WHERE uid=?' + logging.debug('%s (%s, <uid>)', query, downloaded) + self.conn.execute(query, (downloaded, uid)) + self.conn.commit() + + def set_job_code_assigned(self, uids: List[bytes], job_code: bool) -> None: + if len(uids) == 1: + query = 'UPDATE files SET job_code=? WHERE uid=?' + # logging.debug('%s (%s, <uid>)', query, job_code) + self.conn.execute(query, (job_code, uids[0])) + else: + # Limit to number of parameters: 999 + # See https://www.sqlite.org/limits.html + if len(uids) > 999: + name_chunks = divide_list_on_length(uids, 999) + for chunk in name_chunks: + self._mass_set_job_code_assigned(chunk, job_code) + else: + self._mass_set_job_code_assigned(uids, job_code) + self.conn.commit() + + def _mass_set_job_code_assigned(self, uids: List[bytes], job_code: bool) -> None: + query = 'UPDATE files SET job_code=? WHERE uid IN ({})' + logging.debug('%s (%s files)', query, len(uids)) + self.conn.execute(query.format( + ','.join('?' * len(uids))), [job_code] + uids) + + def assign_proximity_groups(self, groups: Sequence[Tuple[int, int, bytes]]) -> None: + query = 'UPDATE files SET proximity_col1=?, proximity_col2=? WHERE uid=?' + logging.debug('%s (%s operations)', query, len(groups)) + self.conn.executemany(query, groups) + self.conn.commit() + + def get_uids_for_device(self, scan_id: int) -> List[int]: + query = 'SELECT uid FROM files WHERE scan_id=?' + logging.debug('%s (%s, )', query, scan_id) + rows = self.conn.execute(query, (scan_id, )).fetchall() + return [row[0] for row in rows] + + def any_files_marked(self, scan_id: Optional[int]=None) -> bool: + if scan_id is None: + row = self.conn.execute('SELECT uid FROM files WHERE marked=1 LIMIT 1').fetchone() + else: + row = self.conn.execute('SELECT uid FROM files WHERE marked=1 AND scan_id=? LIMIT 1', + (scan_id, )).fetchone() + return row is not None + + def any_files_to_download(self, scan_id: Optional[int]=None) -> bool: + if scan_id is not None: + row = self.conn.execute('SELECT uid FROM files WHERE downloaded=0 AND scan_id=? ' + 'LIMIT 1', (scan_id,)).fetchone() + else: + row = self.conn.execute('SELECT uid FROM files WHERE downloaded=0 LIMIT 1').fetchone() + return row is not None + + def any_files(self, scan_id: Optional[int]=None) -> bool: + """ + Determine if there are any files associated with this scan_id, of if no scan_id + is specified, any file at all + + :param scan_id: optional device to check + :return: True if found, else False + """ + + if scan_id is not None: + row = self.conn.execute('SELECT uid FROM files WHERE scan_id=? LIMIT 1', + (scan_id,)).fetchone() + else: + row = self.conn.execute('SELECT uid FROM files LIMIT 1').fetchone() + return row is not None + + def any_files_with_extensions(self, scan_id: int, extensions: List[str]) -> bool: + where, where_values = self._build_where(scan_id=scan_id, extensions=extensions) + query = 'SELECT uid FROM files' + + if where: + query = '{} WHERE {}'.format(query, where) + + if where_values: + logging.debug('%s %s', query, where_values) + row = self.conn.execute(query, tuple(where_values)).fetchone() + else: + logging.debug('%s', query) + row = self.conn.execute(query).fetchone() + return row is not None + + def any_files_of_type(self, scan_id: int, file_type: FileType) -> bool: + where, where_values = self._build_where(scan_id=scan_id, file_type=file_type) + query = 'SELECT uid FROM files' + if where: + query = '{} WHERE {}'.format(query, where) + + if where_values: + logging.debug('%s %s', query, where_values) + row = self.conn.execute(query, tuple(where_values)).fetchone() + else: + logging.debug('%s', query) + row = self.conn.execute(query).fetchone() + return row is not None + + def get_single_file_of_type(self, file_type: FileType, + downloaded: Optional[bool] = None, + scan_id: Optional[int]=None, + exclude_scan_ids: Optional[List[int]] = None) -> Optional[bytes]: + where, where_values = self._build_where(scan_id=scan_id, downloaded=downloaded, + file_type=file_type, exclude_scan_ids=exclude_scan_ids) + query = 'SELECT uid FROM files' + + if where: + query = '{} WHERE {}'.format(query, where) + + if where_values: + logging.debug('%s %s', query, where_values) + row = self.conn.execute(query, tuple(where_values)).fetchone() + else: + logging.debug('%s', query) + row = self.conn.execute(query).fetchone() + + if row is None: + return None + return row[0] + + def any_marked_file_no_job_code(self) -> bool: + row = self.conn.execute('SELECT uid FROM files WHERE marked=1 AND job_code=0 ' + 'LIMIT 1').fetchone() + return row is not None + + def _delete_uids(self, uids: List[bytes]) -> None: + query = 'DELETE FROM files WHERE uid IN ({})' + logging.debug('%s (%s files)', query, len(uids)) + self.conn.execute(query.format( + ','.join('?' * len(uids))), uids) + + def delete_uids(self, uids: List[bytes]) -> None: + """ + Deletes thumbnails from SQL cache + :param uids: list of uids to delete + """ + + if len(uids) == 0: + return + + # Limit to number of parameters: 999 + # See https://www.sqlite.org/limits.html + if len(uids) > 999: + name_chunks = divide_list_on_length(uids, 999) + for chunk in name_chunks: + self._delete_uids(chunk) + else: + self._delete_uids(uids) + self.conn.commit() + + def delete_files_by_scan_id(self, scan_id: int, downloaded: Optional[bool]=None) -> None: + query = 'DELETE FROM files' + where, where_values = self._build_where(scan_id=scan_id, downloaded=downloaded) + query = '{} WHERE {}'.format(query, where) + logging.debug('%s (%s)', query, where_values) + self.conn.execute(query, where_values) + self.conn.commit() + + def delete_device(self, scan_id: int) -> None: + query = 'DELETE FROM devices WHERE scan_id=?' + logging.debug('%s (%s, )', query, scan_id) + self.conn.execute(query, (scan_id, )) + self.conn.commit() + + +class DownloadedSQL: + """ + Previous file download detection. + + Used to detect if a file has been downloaded before. A file is the + same if the file name (excluding path), size and modification time + are the same. For performance reasons, Exif information is never + checked. + """ + + def __init__(self, data_dir: str = None) -> None: + """ + :param data_dir: where the database is saved. If None, use + default + """ + if data_dir is None: + data_dir = get_program_data_directory(create_if_not_exist=True) + + self.db = os.path.join(data_dir, 'downloaded_files.sqlite') + self.table_name = 'downloaded' + self.update_table() + + + def update_table(self, reset: bool = False) -> None: + """ + Create or update the database table + :param reset: if True, delete the contents of the table and + build it + """ + + conn = sqlite3.connect(self.db, detect_types=sqlite3.PARSE_DECLTYPES) + + if reset: + conn.execute(r"""DROP TABLE IF EXISTS {tn}""".format( + tn=self.table_name)) + conn.execute("VACUUM") + + conn.execute("""CREATE TABLE IF NOT EXISTS {tn} ( + file_name TEXT NOT NULL, + mtime REAL NOT NULL, + size INTEGER NOT NULL, + download_name TEXT NOT NULL, + download_datetime timestamp, + PRIMARY KEY (file_name, mtime, size) + )""".format(tn=self.table_name)) + + conn.execute("""CREATE INDEX IF NOT EXISTS download_datetime_idx ON + {tn} (download_name)""".format(tn=self.table_name)) + + conn.commit() + conn.close() + + def add_downloaded_file(self, name: str, size: int, + modification_time: float, download_full_file_name: str) -> None: + """ + Add file to database of downloaded files + :param name: original filename of photo / video, without path + :param size: file size + :param modification_time: file modification time + :param download_full_file_name: renamed file including path + """ + conn = sqlite3.connect(self.db) + + logging.debug('Adding %s to downloaded files', name) + + conn.execute(r"""INSERT OR REPLACE INTO {tn} (file_name, size, mtime, + download_name, download_datetime) VALUES (?,?,?,?,?)""".format( + tn=self.table_name), (name, size, modification_time, + download_full_file_name, datetime.datetime.now())) + + conn.commit() + conn.close() + + def file_downloaded(self, name: str, size: int, modification_time: float) -> FileDownloaded: + """ + Returns download path and filename if a file with matching + name, modification time and size has previously been downloaded + :param name: file name, not including path + :param size: file size in bytes + :param modification_time: file modification time + :return: download name (including path) and when it was + downloaded, else None if never downloaded + """ + conn = sqlite3.connect(self.db, detect_types=sqlite3.PARSE_DECLTYPES) + c = conn.cursor() + c.execute("""SELECT download_name, download_datetime as [timestamp] FROM {tn} WHERE + file_name=? AND size=? AND mtime=?""".format( + tn=self.table_name), (name, size, modification_time)) + row = c.fetchone() + if row is not None: + return FileDownloaded._make(row) + else: + return None + +class CacheSQL: + def __init__(self, location: str=None) -> None: + if location is None: + location = get_program_cache_directory(create_if_not_exist=True) + self.db = os.path.join(location, self.db_fs_name()) + self.table_name = 'cache' + self.update_table() + + def db_fs_name(self) -> str: + return 'thumbnail_cache.sqlite' + + def update_table(self, reset: bool=False) -> None: + """ + Create or update the database table + :param reset: if True, delete the contents of the table and + build it + """ + conn = sqlite3.connect(self.db, detect_types=sqlite3.PARSE_DECLTYPES) + + if reset: + conn.execute(r"""DROP TABLE IF EXISTS {tn}""".format( + tn=self.table_name)) + conn.execute("VACUUM") + + conn.execute("""CREATE TABLE IF NOT EXISTS {tn} ( + uri TEXT NOT NULL, + mtime REAL NOT NULL, + mdatatime REAL, + size INTEGER NOT NULL, + md5_name TEXT NOT NULL, + orientation_unknown BOOLEAN NOT NULL, + failure BOOLEAN NOT NULL, + PRIMARY KEY (uri, mtime, size) + )""".format(tn=self.table_name)) + + conn.execute("""CREATE INDEX IF NOT EXISTS md5_name_idx ON + {tn} (md5_name)""".format(tn=self.table_name)) + + conn.commit() + conn.close() + + def add_thumbnail(self, uri: str, size: int, + mtime: float, + mdatatime: float, + md5_name: str, + orientation_unknown: bool, + failure: bool) -> None: + """ + Add file to database of downloaded files + :param uri: original filename of photo / video with path + :param size: file size + :param mtime: file modification time + :param mdatatime: file time recorded in metadata + :param md5_name: full file name converted to md5 + :param orientation_unknown: if True, the orientation of the + file could not be determined, else False + :param failure: if True, indicates the thumbnail could not be + generated, otherwise False + """ + + conn = sqlite3.connect(self.db) + + conn.execute(r"""INSERT OR REPLACE INTO {tn} (uri, size, mtime, mdatatime, + md5_name, orientation_unknown, failure) VALUES (?,?,?,?,?,?,?)""".format( + tn=self.table_name), (uri, size, mtime, mdatatime, + md5_name, orientation_unknown, failure)) + + conn.commit() + conn.close() + + def have_thumbnail(self, uri: str, size: int, mtime: float) -> Optional[InCache]: + """ + Returns download path and filename if a file with matching + name, modification time and size has previously been downloaded + :param uri: file name, including path + :param size: file size in bytes + :param mtime: file modification time + :return: md5 name (excluding path) and if the value indicates a + thumbnail generation failure, else None if thumbnail not + present + """ + + conn = sqlite3.connect(self.db) + c = conn.cursor() + c.execute("""SELECT md5_name, mdatatime, orientation_unknown, failure FROM {tn} WHERE + uri=? AND size=? AND mtime=?""".format(tn=self.table_name), (uri, size, mtime)) + row = c.fetchone() + if row is not None: + return InCache._make(row) + else: + return None + + def _delete(self, names: List[str], conn): + conn.execute("""DELETE FROM {tn} WHERE md5_name IN ({values})""".format( + tn=self.table_name, values=','.join('?' * len(names))), names) + + def delete_thumbnails(self, md5_names: List[str]) -> None: + """ + Deletes thumbnails from SQL cache + :param md5_names: list of names, without path + """ + + if len(md5_names) == 0: + return + + conn = sqlite3.connect(self.db) + # Limit to number of parameters: 999 + # See https://www.sqlite.org/limits.html + if len(md5_names) > 999: + name_chunks = divide_list_on_length(md5_names, 999) + for chunk in name_chunks: + self._delete(chunk, conn) + else: + self._delete(md5_names, conn) + conn.commit() + conn.close() + + + def no_thumbnails(self) -> int: + """ + :return: how many thumbnails are in the db + """ + + conn = sqlite3.connect(self.db) + c = conn.cursor() + c.execute('SELECT COUNT(*) FROM {tn}'.format(tn=self.table_name)) + count = c.fetchall() + return count[0][0] + + def md5_names(self) -> List[Tuple[str]]: + conn = sqlite3.connect(self.db) + c = conn.cursor() + c.execute('SELECT md5_name FROM {tn}'.format(tn=self.table_name)) + rows = c.fetchall() + return rows + + def vacuum(self) -> None: + conn = sqlite3.connect(self.db) + conn.execute("VACUUM") + conn.close() + +class FileFormatSQL: + def __init__(self, data_dir: str=None) -> None: + """ + :param data_dir: where the database is saved. If None, use + default + """ + if data_dir is None: + data_dir = get_program_data_directory(create_if_not_exist=True) + + self.db = os.path.join(data_dir, 'file_formats.sqlite') + self.table_name = 'formats' + self.update_table() + + def update_table(self, reset: bool=False) -> None: + """ + Create or update the database table + :param reset: if True, delete the contents of the table and + build it + """ + + conn = sqlite3.connect(self.db, detect_types=sqlite3.PARSE_DECLTYPES) + + if reset: + conn.execute(r"""DROP TABLE IF EXISTS {tn}""".format( + tn=self.table_name)) + conn.execute("VACUUM") + + conn.execute("""CREATE TABLE IF NOT EXISTS {tn} ( + id INTEGER PRIMARY KEY, + extension TEXT NOT NULL, + camera TEXT NOT NULL, + size INTEGER NOT NULL, + orientation_offset INTEGER, + datetime_offset INTEGER, + cache INTEGER NOT NULL, + app0 INTEGER, + orientation TEXT, + exif_thumbnail TEXT, + thumbnail_preview_same INTEGER, + preview_source TEXT, + previews TEXT + )""".format(tn=self.table_name)) + + conn.execute("""CREATE INDEX IF NOT EXISTS extension_idx ON + {tn} (extension)""".format(tn=self.table_name)) + conn.execute("""CREATE INDEX IF NOT EXISTS camera_idx ON + {tn} (camera)""".format(tn=self.table_name)) + + conn.commit() + conn.close() + + def add_format(self, pa: PhotoAttributes) -> None: + conn = sqlite3.connect(self.db) + c = conn.cursor() + c.execute("""INSERT OR IGNORE INTO {tn} (extension, camera, size, orientation_offset, + datetime_offset, cache, app0, orientation, exif_thumbnail, thumbnail_preview_same, + preview_source, previews) + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""".format(tn=self.table_name), + (pa.ext, + pa.model, + pa.total, + pa.minimum_exif_read_size_in_bytes_orientation, + pa.minimum_exif_read_size_in_bytes_datetime, + pa.bytes_cached_post_thumb, + pa.has_app0, + pa.orientation, + pa.exif_thumbnail_details, + pa.exif_thumbnail_and_preview_identical, + pa.preview_source, + pa.preview_size_and_types)) + + conn.commit() + conn.close() + + def get_orientation_bytes(self, extension: str) -> Optional[int]: + conn = sqlite3.connect(self.db) + c = conn.cursor() + c.execute("""SELECT max(orientation_offset) FROM {tn} WHERE extension=(?)""".format( + tn=self.table_name), (extension,)) + row = c.fetchone() + if row is not None: + return row[0] + return None + + def get_datetime_bytes(self, extension: str) -> Optional[int]: + conn = sqlite3.connect(self.db) + c = conn.cursor() + c.execute("""SELECT max(datetime_offset) FROM {tn} WHERE extension=(?)""".format( + tn=self.table_name), (extension,)) + row = c.fetchone() + if row is not None: + return row[0] + return None + + +if __name__ == '__main__': + import uuid + d = ThumbnailRowsSQL() + uid = uuid.uuid4().bytes + scan_id = 0 + device_name = '1D X' + mtime = datetime.datetime.now().timestamp() + marked = True + file_name = 'image.cr2' + extension= 'cr2' + file_type = FileType.photo + downloaded = False + previously_downloaded = True + proximity_col1 = -1 + proximity_col2 = -1 + + d.add_or_update_device(scan_id=scan_id, device_name=device_name) + + tr = ThumbnailRow(uid=uid, scan_id=scan_id, marked=marked, mtime=mtime, file_name=file_name, + file_type=file_type, extension=extension, downloaded=downloaded, + previously_downloaded=previously_downloaded, job_code=False, + proximity_col1=proximity_col1, proximity_col2=proximity_col2 + ) + + uid = uuid.uuid4().bytes + scan_id = 1 + device_name = 'NEXUS 5X' + mtime = datetime.datetime.now().timestamp() + marked = True + file_name = 'image.dng' + extension= 'dng' + file_type = FileType.photo + downloaded = False + previously_downloaded = False + + d.add_or_update_device(scan_id=scan_id, device_name=device_name) + + tr2 = ThumbnailRow(uid=uid, scan_id=scan_id, marked=marked, mtime=mtime, file_name=file_name, + file_type=file_type, extension=extension, downloaded=downloaded, + previously_downloaded=previously_downloaded, job_code=False, + proximity_col1=proximity_col1, proximity_col2=proximity_col2 + ) + + + uid = uuid.uuid4().bytes + mtime = datetime.datetime.now().timestamp() + marked = False + file_name = 'image.mp4' + extension= 'mp4' + file_type = FileType.video + downloaded = False + previously_downloaded = True + + tr3 = ThumbnailRow(uid=uid, scan_id=scan_id, marked=marked, mtime=mtime, file_name=file_name, + file_type=file_type, extension=extension, downloaded=downloaded, + previously_downloaded=previously_downloaded, job_code=False, + proximity_col1=proximity_col1, proximity_col2=proximity_col2 + ) + + d.add_thumbnail_rows([tr, tr2, tr3]) + + cursor = d.conn.cursor() + cursor.execute('SELECT * FROM files') + for row in map(ThumbnailRow._make, cursor.fetchall()): + print(row) + + d.set_marked(uid, False) + d.set_downloaded(uid, True) + + print(d.get_view(sort_by=Sort.device, sort_order=Qt.DescendingOrder, show=Show.all)) + + print(d.get_uids_for_device(0)) + print(d.get_uids_for_device(1)) + print(d.any_files_marked()) + + print(d.get_uids(marked=True, return_file_name=True)) + print(d.get_uids(marked=False, return_file_name=True)) + print(d.get_uids(downloaded=False, return_file_name=True)) + print(d.get_uids(downloaded=True, return_file_name=True)) + print(d.get_uids(file_type=FileType.video, return_file_name=True)) + print("next two lines should be identical") + print(d.get_uids(scan_id=0, file_type=FileType.photo, return_file_name=True)) + print(d.get_uids(exclude_scan_ids=[1,], file_type=FileType.photo, return_file_name=True)) + print(d.get_uids(previously_downloaded=False, return_file_name=True)) + print(d.get_count(scan_id=0)) + print(d.get_count(previously_downloaded=True)) + print(d.get_count(show=Show.new_only)) + print(d.get_count(marked=True)) + uids = d.get_uids(downloaded=False) + print("UIDs", len(uids), "; available to download?", d.any_files_to_download()) + d.set_list_marked(uids, marked=False) + print(d.get_count(marked=True)) + d.set_list_marked(uids, marked=True) + print(d.get_count(marked=True)) + print(d.any_files_with_extensions(scan_id=0, extensions=['cr2', 'dng'])) + print(d.any_files_with_extensions(scan_id=0, extensions=['nef', 'dng'])) + print(d.any_files_with_extensions(scan_id=0, extensions=['nef'])) + print(d.any_files_with_extensions(scan_id=0, extensions=['cr2'])) + |