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
195
196
197
198
199
|
import sqlite3
from common import gajim
import sys
import os
class FilesharingDatabase:
def __init__(self, plugin):
self.plugin = plugin
path_l = os.path.split(plugin.config.FILE_PATH)
path = os.path.join(path_l[0], 'shared_files.db')
db_exist = os.path.exists(path)
self.conn = sqlite3.connect(path)
# Enable foreign keys contraints
self.conn.cursor().execute("pragma foreign_keys = on")
if not db_exist:
self.create_database()
# NOTE: Make sure we are getting and setting the requester without its
# resource
def create_database(self):
c = self.conn.cursor()
# Create tables
c.execute("CREATE TABLE permissions" +
"(fid integer REFERENCES files(fid) ON DELETE CASCADE, " +
"account text, requester text)")
c.execute("CREATE TABLE files" +
"(fid INTEGER PRIMARY KEY AUTOINCREMENT," +
" file_path text, relative_path text, hash_sha1 text," +
"size numeric, description text, mod_date text, is_dir boolean)")
# Save (commit) the changes
self.conn.commit()
c.close()
def get_toplevel_files(self, account, requester):
c = self.conn.cursor()
data = (account, requester)
c.execute("SELECT relative_path, hash_sha1, size, description, " +
"mod_date, is_dir FROM (files JOIN permissions ON" +
" files.fid=permissions.fid) WHERE account=? AND requester=?" +
" AND relative_path NOT LIKE '%/%'", data)
result = c.fetchall()
c.close()
return result
def get_files_from_dir(self, account, requester, dir_):
c = self.conn.cursor()
data = (account, requester, dir_ + '/%')
c.execute("SELECT relative_path, hash_sha1, size, description, " +
"mod_date, is_dir FROM (files JOIN permissions ON" +
" files.fid=permissions.fid) WHERE account=? AND requester=?" +
" AND relative_path LIKE ?", data)
result = c.fetchall()
c.close()
fresult = []
for r in result:
name = r[0][len(dir_) + 1:]
if '/' not in name:
fresult.append(r)
return fresult
def get_files(self, account, requester):
"""
>>> file_ = ('file_path', 'relative_path', 'hash', 999, 'description', \
'date', False)
>>> foo = add_file('account@gajim', 'requester@jabber', file_)
>>> result = get_files('account@gajim', 'requester@jabber')
>>> len(result)
1
>>> _delete_file(1)
"""
c = self.conn.cursor()
data = (account, requester)
c.execute("SELECT relative_path, hash_sha1, size, description, " +
"mod_date, is_dir FROM (files JOIN permissions ON" +
" files.fid=permissions.fid) WHERE account=? AND requester=?", data)
result = c.fetchall()
c.close()
return result
def get_file(self, account, requester, hash_, name):
c = self.conn.cursor()
if hash_:
data = (account, requester, hash_)
sql = "SELECT relative_path, hash_sha1, size, description, " + \
"mod_date, file_path FROM (files JOIN permissions ON" + \
" files.fid=permissions.fid) WHERE account=? AND requester=?" +\
" AND hash_sha1=?"
else:
data = (account, requester, name)
sql = "SELECT relative_path, hash_sha1, size, description, " + \
"mod_date, file_path FROM (files JOIN permissions ON" + \
" files.fid=permissions.fid) WHERE account=? AND requester=?" +\
" AND relative_path=?"
c.execute(sql, data)
result = c.fetchall()
c.close()
if result == []:
return None
else:
return result[0]
def get_files_name(self, account, requester):
result = self.get_files(account, requester)
flist = []
for r in result:
flist.append(r[0])
return flist
def add_file(self, account, requester, file_):
"""
>>> file_ = ('file_path', 'relative_path', 'hash', 999, 'description', \
'date', False)
>>> add_file('account@gajim', 'requester@jabber', file_)
1
>>> _delete_file(1)
"""
self._check_duplicate(account, requester, file_)
requester = gajim.get_jid_without_resource(requester)
c = self.conn.cursor()
c.execute("INSERT INTO files (file_path, " +
"relative_path, hash_sha1, size, description, mod_date, " +
" is_dir) VALUES (?,?,?,?,?,?,?)", file_)
fid = c.lastrowid
permission_data = (fid, account, requester)
c.execute("INSERT INTO permissions VALUES (?,?,?)", permission_data)
self.conn.commit()
c.close()
return fid
def _check_duplicate(self, account, requester, file_):
c = self.conn.cursor()
data = (account, requester, file_[1])
c.execute("SELECT * FROM (files JOIN permissions ON" +
" files.fid=permissions.fid) WHERE account=? AND requester=?" +
" AND relative_path=? ", data)
result = c.fetchall()
if file_[2] != '':
data = (account, requester, file_[2])
c.execute("SELECT * FROM (files JOIN permissions ON" +
" files.fid=permissions.fid) WHERE account=? AND requester=?" +
" AND hash_sha1=?)", data)
result.extend(c.fetchall())
if len(result) > 0:
raise Exception('Duplicated entry')
c.close()
def _delete_file(self, fid):
c = self.conn.cursor()
data = (fid, )
c.execute("DELETE FROM files WHERE fid=?", data)
self.conn.commit()
c.close()
def _delete_dir(self, dir_, account, requester):
c = self.conn.cursor()
data = (account, requester, dir_, dir_ + '/%')
sql = "DELETE FROM files WHERE fid IN " + \
" (SELECT files.fid FROM files, permissions WHERE" + \
" files.fid=permissions.fid AND account=?"+ \
" AND requester=? AND (relative_path=? OR relative_path LIKE ?))"
c.execute(sql, data)
self.conn.commit()
c.close()
def delete(self, account, requester, relative_path):
c = self.conn.cursor()
data = (account, requester, relative_path)
c.execute("SELECT files.fid, is_dir FROM (files JOIN permissions ON" +
" files.fid=permissions.fid) WHERE account=? AND requester=? AND " +
"relative_path=? ", data)
result = c.fetchone()
c.close()
if result[1] == 0:
self._delete_file(result[0])
else:
self._delete_dir(relative_path, account, requester)
def delete_all(self, account, requester):
c = self.conn.cursor()
data = (account, requester)
sql = "DELETE FROM files WHERE fid IN (SELECT fid FROM permissions" + \
" WHERE account=? AND requester=?)"
c.execute(sql, data)
self.conn.commit()
c.close()
if __name__ == "__main__":
"""
DELETE DATABASE FILE BEFORE RUNNING TESTS
"""
import doctest
path = sys.path[0]
path = path + '/' + 'shared_files.db'
conn = sqlite3.connect(path)
# Enable foreign keys contraints
conn.cursor().execute("pragma foreign_keys = on")
FilesharingDatabase.create_database()
doctest.testmod()
|
