1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
|
import os, json, platform, socket, sqlite3
from datetime import datetime
from pathlib import Path
class Metadata:
def __init__(self, paths):
self.paths=paths
self.today=datetime.today()
## Create folders
self.paths["metadata"]=os.path.join(self.paths["root"], "metadata.db")
## Init database
self.con=sqlite3.connect(self.paths["metadata"])
cur=self.con.cursor()
tables=cur.execute("""SELECT name FROM sqlite_master WHERE type='table' AND name='files'; """).fetchall()
if len(tables) == 0:
cur.execute("CREATE TABLE files(id INTEGER PRIMARY KEY AUTOINCREMENT, subpath TEXT UNIQUE, created REAL, added REAL, hostname TEXT, platform TEXT);")
self.con.commit()
cur.execute("CREATE TABLE tags(id INTEGER, name TEXT, FOREIGN KEY(id) REFERENCES files(id));")
self.con.commit()
cur.execute("CREATE TABLE cache(name TEXT PRIMARY KEY, value TEXT);")
self.con.commit()
def create(self, subpath, created):
"""
Create a new note file entry.
"""
cur=self.con.cursor()
cur.execute("""INSERT INTO files(subpath,created,added,hostname,platform) values('{}','{}','{}','{}','{}')""".format(
subpath,
created.timestamp(),
datetime.today().timestamp(),
socket.gethostname(),
platform.platform()
))
self.con.commit()
def getfileinfo(self, subpath, name):
"""
Get associated info (name argument e.g hostname) with a subpath.
"""
subpath_id=self.subpathid(subpath, True)
cur=self.con.cursor()
cur.execute('SELECT {} FROM files WHERE id="{}"'.format(name,subpath_id))
return list(cur.fetchone())[0]
def setcache(self, name, value):
"""
Set the value of a cache entry.
"""
cur=self.con.cursor()
cur.execute('SELECT value FROM cache WHERE name="{}"'.format(name))
if cur.fetchone() is None:
cur.execute('INSERT INTO cache values("{}","{}")'.format(name,value))
else:
cur.execute('UPDATE cache SET value="{}" WHERE name="{}"'.format(value,name))
self.con.commit()
def getcache(self,name):
"""
Get the value of a cache entry.
"""
cur=self.con.cursor()
cur.execute('SELECT value FROM cache WHERE name="{}"'.format(name))
result=cur.fetchone()
if result is None:
return None
return result[0]
def subpathid(self, subpath, required=False):
"""
Get the id (sqlite id) of a subpath. If required=True then abort if subpath not found.
"""
cur=self.con.cursor()
cur.execute('SELECT id FROM files WHERE subpath="{}"'.format(subpath))
result=cur.fetchone()
if result is not None:
return list(result)[0]
if required:
print("Subpath not found: "+subpath)
exit(1)
return None
def delete(self,subpath, ignore_error=False):
"""
Delete subpath and its associated tags from the metadata.
"""
cur=self.con.cursor()
subpath_id=None
if ignore_error:
subpath_id=self.subpathid(subpath, False)
else:
subpath_id=self.subpathid(subpath, True)
if subpath_id is None:
return
cur.execute('DELETE FROM tags WHERE id={}'.format(subpath_id))
cur.execute('DELETE FROM files WHERE id={}'.format(subpath_id))
self.con.commit()
def addtag(self, subpath, tag):
"""
Attach a tag to a specific subpath.
"""
taglist=self.listtags(subpath)
if tag not in taglist:
cur=self.con.cursor()
subpath_id=self.subpathid(subpath, True)
cur.execute('INSERT INTO tags(id, name) VALUES({},"{}")'.format(subpath_id,tag))
self.con.commit()
else:
print("{} as already be tagged with {}".format(subpath,tag))
def deletetag(self, subpath, tag):
"""
Delete a tag attached to a specific subpath.
"""
cur=self.con.cursor()
subpath_id=self.subpathid(subpath, True)
cur.execute('DELETE FROM tags WHERE id={} AND name="{}"'.format(subpath_id,tag))
self.con.commit()
def obliteratetag(self, tag):
"""
Remove all occurences of a tag from the database.
"""
cur=self.con.cursor()
cur.execute('DELETE FROM tags WHERE name="{}"'.format(tag))
self.con.commit()
def searchtag(self,tag):
"""
Get all subpaths associated with a specific tag.
"""
cur=self.con.cursor()
ids=[i[0] for i in cur.execute('SELECT id FROM tags WHERE name="{}"'.format(tag)) ]
subpaths=[cur.execute('SELECT subpath FROM files WHERE id={}'.format(i)).fetchone()[0] for i in ids]
return subpaths
def listtags(self, subpath=None):
"""
List either all tags (subpath is None), or the ones associated with a subpath.
"""
cur=self.con.cursor()
if subpath is not None:
subpath_id=self.subpathid(subpath, True)
tags=[i[0] for i in cur.execute('SELECT DISTINCT name FROM tags WHERE id={}'.format(subpath_id)) ]
else:
tags=[i[0] for i in cur.execute('SELECT DISTINCT name FROM tags') ]
return tags
def fix_deleted(self, dry=True):
"""
Search for files deleted by the user and update database accordingly.
"""
cur=self.con.cursor()
for result in cur.execute("SELECT subpath FROM files"):
subpath=result[0]
path=os.path.join(self.paths["files"], subpath)
if not os.path.exists(path):
if dry:
print("Deletion detected => " + subpath)
else:
print("Fixing file deletion => " + subpath)
self.delete(subpath)
def fix_new(self, layout, dry=True):
"""
Search for new files added by the user and update the database accordingly.
"""
cur=self.con.cursor()
for subpath in layout.flatten():
result=cur.execute('SELECT * from files where subpath="{}"'.format(subpath))
if len(result.fetchall()) <= 0 :
if dry:
print("New file detected => "+str(subpath))
else:
print("Fixing new file => "+str(subpath))
self.create(subpath,layout.gettoday())
def flatten_ordered(self, desc=False, ordby="created"):
"""
List all subpaths present in the database. Results are sorted (DESC and ASC) by creation date.
"""
cur=self.con.cursor()
if desc:
result=cur.execute("SELECT subpath FROM files ORDER BY {} DESC".format(ordby))
else:
result=cur.execute("SELECT subpath FROM files ORDER BY {} ASC".format(ordby))
result=[subpath[0] for subpath in result.fetchall()]
return result
|
