summaryrefslogtreecommitdiff
path: root/raphodo/rpdsql.py
diff options
context:
space:
mode:
authorJörg Frings-Fürst <debian@jff-webhosting.net>2017-07-06 22:55:08 +0200
committerJörg Frings-Fürst <debian@jff-webhosting.net>2017-07-06 22:55:08 +0200
commit083849161f075878e4175cd03cb7afa83d64e7f5 (patch)
tree101feb02f6306f8f8b335faa39d74f1eaafc8d54 /raphodo/rpdsql.py
parentb5287ed17bda10877d84ba86fcf148ee74b93b9b (diff)
New upstream version 0.9.0upstream/0.9.0
Diffstat (limited to 'raphodo/rpdsql.py')
-rwxr-xr-xraphodo/rpdsql.py957
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']))
+