|
1
|
|
|
""" |
|
2
|
|
|
SQLite plugin object. |
|
3
|
|
|
This plugin stores metadata about all media in an sqlite database. |
|
4
|
|
|
|
|
5
|
|
|
You'll need to include [PluginSQLite] in your config file. |
|
6
|
|
|
By default, the sqlite database will be created in your application |
|
7
|
|
|
directory. If you want to specify a different path then |
|
8
|
|
|
specify a fully qualified `database_file` path. |
|
9
|
|
|
|
|
10
|
|
|
``` |
|
11
|
|
|
[PluginSQLite] |
|
12
|
|
|
database_file=/path/to/database.db |
|
13
|
|
|
|
|
14
|
|
|
.. moduleauthor:: Jaisen Mathai <[email protected]> |
|
15
|
|
|
""" |
|
16
|
|
|
from __future__ import print_function |
|
17
|
|
|
|
|
18
|
|
|
import json |
|
19
|
|
|
import os |
|
20
|
|
|
import sqlite3 |
|
21
|
|
|
import time |
|
22
|
|
|
#import json |
|
23
|
|
|
|
|
24
|
|
|
#from google_auth_oauthlib.flow import InstalledAppFlow |
|
25
|
|
|
#from google.auth.transport.requests import AuthorizedSession |
|
26
|
|
|
#from google.oauth2.credentials import Credentials |
|
27
|
|
|
|
|
28
|
|
|
from elodie.geolocation import place_name |
|
29
|
|
|
from elodie.localstorage import Db |
|
30
|
|
|
from elodie.media.base import Base, get_all_subclasses |
|
31
|
|
|
from elodie.plugins.plugins import PluginBase |
|
32
|
|
|
|
|
33
|
|
|
class SQLite(PluginBase): |
|
34
|
|
|
"""A class to execute plugin actions. |
|
35
|
|
|
|
|
36
|
|
|
Requires a config file with the following configurations set. |
|
37
|
|
|
database_file: |
|
38
|
|
|
The full path to the SQLite database (.db). |
|
39
|
|
|
|
|
40
|
|
|
""" |
|
41
|
|
|
|
|
42
|
|
|
__name__ = 'SQLite' |
|
43
|
|
|
|
|
44
|
|
|
def __init__(self): |
|
45
|
|
|
super(SQLite, self).__init__() |
|
46
|
|
|
|
|
47
|
|
|
# Default the database file to be in the application plugin directory. |
|
48
|
|
|
# Override with value from config file. |
|
49
|
|
|
self.database_file = '{}/plugins/{}/elodie.db'.format( |
|
50
|
|
|
self.application_directory, |
|
51
|
|
|
__name__.lower() |
|
52
|
|
|
) |
|
53
|
|
|
if('database_file' in self.config_for_plugin): |
|
54
|
|
|
self.database_file = self.config_for_plugin['database_file'] |
|
55
|
|
|
|
|
56
|
|
|
self.con = sqlite3.connect(self.database_file) |
|
57
|
|
|
self.con.row_factory = sqlite3.Row |
|
58
|
|
|
self.cursor = self.con.cursor() |
|
59
|
|
|
if(not self._validate_schema()): |
|
60
|
|
|
self._create_schema() |
|
61
|
|
|
|
|
62
|
|
|
def after(self, file_path, destination_folder, final_file_path, metadata): |
|
63
|
|
|
self._upsert(file_path, destination_folder, final_file_path, metadata) |
|
64
|
|
|
|
|
65
|
|
|
def batch(self): |
|
66
|
|
|
pass |
|
67
|
|
|
|
|
68
|
|
|
def before(self, file_path, destination_folder): |
|
69
|
|
|
pass |
|
70
|
|
|
|
|
71
|
|
|
def generate_db(self): |
|
72
|
|
|
db = Db() |
|
73
|
|
|
for checksum, file_path in db.all(): |
|
74
|
|
|
subclasses = get_all_subclasses() |
|
75
|
|
|
media = Base.get_class_by_file(file_path, get_all_subclasses()) |
|
76
|
|
|
media.set_checksum( |
|
77
|
|
|
db.checksum(file_path) |
|
78
|
|
|
) |
|
79
|
|
|
metadata = media.get_metadata() |
|
80
|
|
|
destination_folder = os.path.dirname(file_path) |
|
81
|
|
|
final_file_path = '{}{}'.format(os.path.sep, os.path.basename(file_path)) |
|
82
|
|
|
self._upsert(file_path, destination_folder, final_file_path, metadata) |
|
83
|
|
|
|
|
84
|
|
|
def _create_schema(self): |
|
85
|
|
|
self.database_schema = '{}{}{}'.format( |
|
86
|
|
|
os.path.dirname(os.path.realpath(__file__)), |
|
87
|
|
|
os.sep, |
|
88
|
|
|
'schema.sql' |
|
89
|
|
|
) |
|
90
|
|
|
|
|
91
|
|
|
with open(self.database_schema, 'r') as fp_schema: |
|
92
|
|
|
sql_statement = fp_schema.read() |
|
93
|
|
|
self.cursor.executescript(sql_statement) |
|
94
|
|
|
|
|
95
|
|
|
def _insert_row_sql(self, final_path, metadata): |
|
96
|
|
|
path = '{}/{}.{}'.format( |
|
97
|
|
|
metadata['directory_path'], |
|
98
|
|
|
metadata['base_name'], |
|
99
|
|
|
metadata['extension'] |
|
100
|
|
|
) |
|
101
|
|
|
return ( |
|
102
|
|
|
"""INSERT INTO `metadata` ( |
|
103
|
|
|
`hash`, `path`, `album`, `camera_make`, `camera_model`, |
|
104
|
|
|
`date_taken`, `latitude`, `location_name`, `longitude`, |
|
105
|
|
|
`original_name`, `title`, `_modified`) |
|
106
|
|
|
VALUES ( |
|
107
|
|
|
:hash, :path, :album, :camera_make, :camera_model, |
|
108
|
|
|
:date_taken, :latitude, :location_name, :longitude, |
|
109
|
|
|
:original_name, :title, datetime('now'))""", |
|
110
|
|
|
self._sql_values(final_path, metadata) |
|
111
|
|
|
) |
|
112
|
|
|
|
|
113
|
|
|
def _run_query(self, sql, values): |
|
114
|
|
|
self.cursor.execute(sql, values) |
|
115
|
|
|
return self.cursor.fetchall() |
|
116
|
|
|
|
|
117
|
|
|
def _sql_values(self, final_path, metadata, current_path=None): |
|
118
|
|
|
timestamp = int(time.time()) |
|
119
|
|
|
return { |
|
120
|
|
|
'hash': metadata['checksum'], |
|
121
|
|
|
'path': final_path, |
|
122
|
|
|
'album': metadata['album'], |
|
123
|
|
|
'camera_make': metadata['camera_make'], |
|
124
|
|
|
'camera_model': metadata['camera_model'], |
|
125
|
|
|
'date_taken': time.strftime('%Y-%m-%d %H:%M:%S', metadata['date_taken']), |
|
126
|
|
|
'latitude': metadata['latitude'], |
|
127
|
|
|
'location_name': place_name(metadata['latitude'], metadata['longitude'])['default'], |
|
128
|
|
|
'longitude': metadata['longitude'], |
|
129
|
|
|
'original_name': metadata['original_name'], |
|
130
|
|
|
'title': metadata['title'], |
|
131
|
|
|
'current_path': current_path |
|
132
|
|
|
} |
|
133
|
|
|
|
|
134
|
|
|
def _update_row_sql(self, current_path, final_path, metadata): |
|
135
|
|
|
timestamp = int(time.time()) |
|
136
|
|
|
return ( |
|
137
|
|
|
"""UPDATE `metadata` SET `hash`=:hash, `path`=:path, `album`=:album, `camera_make`=:camera_make, |
|
138
|
|
|
`camera_model`=:camera_model, `date_taken`=:date_taken, `latitude`=:latitude, |
|
139
|
|
|
`longitude`=:longitude, `original_name`=:original_name, `title`=:title, `_modified`=datetime('now') |
|
140
|
|
|
WHERE `path`=:current_path""", |
|
141
|
|
|
self._sql_values(final_path, metadata, current_path) |
|
142
|
|
|
) |
|
143
|
|
|
|
|
144
|
|
|
def _upsert(self, file_path, destination_folder, final_file_path, metadata): |
|
145
|
|
|
# We check if the source path exists in the database already. |
|
146
|
|
|
# If it does then we assume that this is an update operation. |
|
147
|
|
|
full_destination_path = '{}{}'.format(destination_folder, final_file_path) |
|
148
|
|
|
self.cursor.execute("SELECT `path` FROM `metadata` WHERE `path`=:path", {'path': file_path}) |
|
149
|
|
|
|
|
150
|
|
|
if(self.cursor.fetchone() is None): |
|
151
|
|
|
self.log(u'SQLite plugin inserting {}'.format(file_path)) |
|
152
|
|
|
sql_statement, sql_values = self._insert_row_sql(full_destination_path, metadata) |
|
153
|
|
|
else: |
|
154
|
|
|
self.log(u'SQLite plugin updating {}'.format(file_path)) |
|
155
|
|
|
sql_statement, sql_values = self._update_row_sql(file_path, full_destination_path, metadata) |
|
156
|
|
|
|
|
157
|
|
|
self.cursor.execute(sql_statement, sql_values) |
|
158
|
|
|
|
|
159
|
|
|
def _validate_schema(self): |
|
160
|
|
|
try: |
|
161
|
|
|
self.cursor.execute('SELECT * FROM `metadata` LIMIT 1'); |
|
162
|
|
|
return True |
|
163
|
|
|
except sqlite3.OperationalError: |
|
164
|
|
|
return False |
|
165
|
|
|
|
jmathai /
elodie
