4.3.1r21
[enigma2.git] / usr / lib / enigma2 / python / Plugins / Extensions / Browser / BrowserDB.py
1 from Tools.Directories import SCOPE_CONFIG, resolveFilename
2 import sqlite3
3
4 import os.path as os_path
5 from time import time
6 from datetime import datetime, timedelta
7 from email.Utils import mktime_tz, parsedate_tz
8
9 from Cookie import SmartCookie, CookieError
10
11 from OpenSSL.crypto import load_certificate, FILETYPE_PEM
12
13 class Bookmark:
14         def __init__(self, id = -1, name = "", url = "", group = 0):
15                 self.id = id
16                 self.name = name
17                 self.url = url
18                 self.group = group
19
20         def __str__(self):
21                 return "Bookmark::%s|%s|%s" %(self.id, self.name, self.url)
22
23 class Group:
24         def __init__(self, id = -1, name = "", parent = 0):
25                 self.id = id
26                 self.name = name
27                 self.parent = parent
28
29 class HistoryItem:
30         def __init__(self, id = -1, timestamp = None, title = None, url = None):
31                 self.id = id
32                 if timestamp == None:
33                         self.timestamp = time()
34                 else:
35                         self.timestamp = float(timestamp)
36                 self.title = title
37                 self.url = url
38
39         def __str__(self):
40                 return "HistoryItem::%s|%s|%s|%s" %(self.id, self.timestamp, self.title, self.url)
41
42 class Certificate:
43         def __init__(self, id = -1, host = None, pem = None):
44                 self.id = id
45                 self.host = host
46                 self.pem = pem
47                 self.cert = load_certificate(FILETYPE_PEM, self.pem)
48
49         def notBefore(self):
50                 return self.crtParseASN1Time(self.cert.get_notBefore())
51
52         def notAfter(self):
53                 return self.crtParseASN1Time(self.cert.get_notAfter())
54
55         def crtParseASN1Time(self, date):
56                 dateTime = date[:14]
57                 additional = date[14:]
58                 res = datetime.strptime(dateTime, "%Y%m%d%H%M%S")
59                 if additional != "Z":
60                         if len(additional) == 5:
61                                 h = int(additional[1:3])
62                                 m = int(additional[3:5])
63                                 delta = timedelta(hours = h, minutes = m)
64                                 if additional.startswith("+"):
65                                         res = res + delta
66                                 elif additional.startswith("-"):
67                                         res = res - delta
68                 return res
69
70         def __str__(self):
71                 return "Certificate::id=%s|host=%s|serial=%s|SHA1digest=%s issued by %s, not valid before %s or after %s" %(
72                                 self.id, self.host, self.cert.get_serial_number(), self.cert.digest("sha1"), self.cert.get_issuer().commonName, self.notBefore(), self.notAfter()
73                         )
74
75 class Cookie:
76         def __init__(self, key, domain, path, raw, expires, convertDate = False):
77                 self.key = key
78                 self.domain = domain
79                 self.path = path
80
81                 self.expires = self.cookieDate2Ts(expires) if convertDate else expires
82                 self.raw = raw
83
84         @staticmethod
85         def fromRawString(raw):
86                 try:
87                         sc = SmartCookie(raw)
88                         for key, cookie in sc.items():
89                                 try:
90                                         return Cookie(key, cookie['domain'], cookie['path'], raw, cookie['expires'], True)
91                                 except:
92                                         return None
93                 except CookieError as e:
94                         print e
95                         return None
96
97         def cookieDate2Ts(self, cd):
98                 #Sat, 02-Nov-2013 11:33:51 GMT
99                 if cd.strip() != "" and cd != None:
100                         return mktime_tz(parsedate_tz(cd.strip()))
101                 return long(0)
102
103         def __str__(self):
104                 return "Cookie::key=%s|domain=%s|path=%s|expires=%s" %(self.key, self.domain, self.path, self.expires)
105
106 class IterCookies:
107         def __init__(self, cookies):
108                 self.list = cookies
109
110         def __iter__(self):
111                 return self
112
113         def next(self):
114                 if len(self.list) == 0:
115                         raise StopIteration
116                 else:
117                         cookie = self.list.pop(0)
118                         return (cookie.key, cookie.domain, cookie.path, cookie.raw, cookie.expires)
119
120 class BrowserDB:
121         TABLE_VERSION = "__version"
122         TABLE_BM = "bookmarks"
123         TABLE_BM_GROUPS = "bookmark_groups"
124         TABLE_HISTORY = "history"
125         TABLE_CERTS = "certificates"
126         TABLE_COOKIES = "cookies"
127
128         DB_VERSION = 1
129
130         FIELD_ID = "id"
131         FIELD_VERSION_V = "version"
132         FIELD_BM_GROUP_NAME = "name"
133         FIELD_BM_GROUP_PARENT = "parent"
134         FIELD_BM_NAME = "name"
135         FIELD_BM_URL = "url"
136         FIELD_BM_GROUPID = "groupid"
137         FIELD_HIS_TS = "timestamp"
138         FIELD_HIS_TITLE = "title"
139         FIELD_HIS_URL = "url"
140         FIELD_CERT_HOST = "host"
141         FIELD_CERT_PEM = "pem"
142         FIELD_COOKIE_KEY = "key"
143         FIELD_COOKIE_DOMAIN = "domain"
144         FIELD_COOKIE_PATH = "path"
145         FIELD_COOKIE_EXPIRES = "expires"
146         FIELD_COOKIE_RAW = "raw"
147
148         SQL_CREATE_VERSION_T = "CREATE TABLE %s (%s INTEGER)" %(TABLE_VERSION, FIELD_VERSION_V)
149         SQL_CREATE_BM_T = "CREATE TABLE %s (%s INTEGER PRIMARY KEY, %s TEXT, %s TEXT, %s INTEGER DEFAULT -1 NOT NULL)" %(TABLE_BM, FIELD_ID, FIELD_BM_NAME, FIELD_BM_URL, FIELD_BM_GROUPID)
150         SQL_CREATE_BM_GROUPS_T = "CREATE TABLE %s (%s INTEGER PRIMARY KEY, %s INTEGER DEFAULT 0 NOT NULL, %s TEXT)" %(TABLE_BM_GROUPS, FIELD_ID, FIELD_BM_GROUP_PARENT, FIELD_BM_GROUP_NAME)
151         SQL_CREATE_HISTORY_T = "CREATE TABLE %s (%s INTEGER PRIMARY KEY, %s REAL, %s TEXT, %s TEXT)" %(TABLE_HISTORY, FIELD_ID, FIELD_HIS_TS, FIELD_HIS_TITLE, FIELD_HIS_URL)
152         SQL_CREATE_CERT_T = "CREATE TABLE %s (%s INTEGER PRIMARY KEY, %s TEXT, %s TEXT)" %(TABLE_CERTS, FIELD_ID, FIELD_CERT_HOST, FIELD_CERT_PEM)
153         SQL_CREATE_COOKIE_T = "CREATE TABLE %(table)s( %(key)s TEXT, %(domain)s TEXT, %(path)s TEXT, %(raw)s TEXT, %(expires)s REAL, PRIMARY KEY(%(key)s, %(domain)s, %(path)s) )" %{
154                         'table' : TABLE_COOKIES, 'key' : FIELD_COOKIE_KEY, 'domain' : FIELD_COOKIE_DOMAIN, 'path': FIELD_COOKIE_PATH, 'raw' : FIELD_COOKIE_RAW, 'expires' : FIELD_COOKIE_EXPIRES }
155         SQL_SET_VERSION = "INSERT INTO " + TABLE_VERSION + " (" + FIELD_VERSION_V + ") values (?)"
156
157         CERT_OK = 0
158         CERT_UNKOWN = 1
159         CERT_CHANGED = 2
160
161         __instance = None
162
163         @staticmethod
164         def getInstance():
165                 instance = None
166                 try:
167                         instance = BrowserDB()
168                 except BrowserDB, d:
169                         instance = d
170
171                 return instance
172
173         def __init__(self):
174                 if BrowserDB.__instance != None:
175                         raise BrowserDB.__instance
176                 BrowserDB.__instance = self
177
178                 self.__dbfile = "%swebbrowser.db" %( resolveFilename(SCOPE_CONFIG) )
179                 self.__conn = None
180                 self.__connect()
181
182         def __connect(self):
183                 if not os_path.exists(self.__dbfile):
184                         self.__conn = sqlite3.connect(self.__dbfile)
185                         self.__createDB()
186                 else:
187                         self.__conn = sqlite3.connect(self.__dbfile)
188                         self.__checkDbVersion()
189                 self.__conn.text_factory = str
190
191         def __createDB(self):
192                 c = self.__conn.cursor()
193                 print "[BrowserDB].__initDB :: Creating Database Tables"
194                 c.execute(BrowserDB.SQL_CREATE_VERSION_T)
195                 c.execute(BrowserDB.SQL_CREATE_BM_T)
196                 c.execute(BrowserDB.SQL_CREATE_BM_GROUPS_T)
197                 c.execute(BrowserDB.SQL_CREATE_HISTORY_T)
198                 c.execute(BrowserDB.SQL_CREATE_CERT_T)
199                 c.execute(BrowserDB.SQL_CREATE_COOKIE_T)
200
201                 print BrowserDB.SQL_SET_VERSION
202                 c.execute(BrowserDB.SQL_SET_VERSION, (str(BrowserDB.DB_VERSION)))
203
204                 print "[BrowserDB].__initDB :: Adding default bookmarks"
205                 list = [ Bookmark(-1, "Dream Multimedia TV", "http://www.dream-multimedia-tv.de/"),
206                                 Bookmark(-1, "Dream Multimedia TV - Forum", "http://www.dream-multimedia-tv.de/board/"),
207                                 Bookmark(-1, "Twitter", "http://www.twitter.com"),
208                                 Bookmark(-1, "Google", "http://www.google.com"),
209                                 Bookmark(-1, "Facebook", "http://www.facebook.com"),
210                 ]
211                 self.setBookmarks(list)
212
213                 self.__conn.commit()
214                 c.close()
215                 print "[BrowserDB].__initDB :: Database Tables Initialized"
216
217         def __checkDbVersion(self):
218                 pass #TODO implement version Check
219
220         def __upgradeDB(self, fv):
221                 pass #TODO implement Updates
222
223         def getBookmarks(self, needle = None):
224                 c = self.__conn.cursor()
225                 bookmarks = []
226                 if needle is None or needle == "":
227                         bookmarks = self.__getBookmarks(c)
228                 else:
229                         bookmarks = self.__filterBookmarks(c, needle)
230                 c.close()
231                 return bookmarks
232
233         def setBookmarks(self, bookmarks):
234                 c = self.__conn.cursor()
235                 for bm in bookmarks:
236                         self.setBookmark(bm, c)
237                 c.close()
238
239         def setBookmark(self, bm, cursor = None):
240                 c = None
241                 if cursor is None:
242                         c = self.__conn.cursor()
243                 else:
244                         c = cursor
245
246                 if bm.id == -1:
247                         self.__addBookmark(bm, c)
248                 else:
249                         self.__updateBookmark(bm, c)
250
251                 if cursor is None:
252                         c.close()
253
254         def deleteBookmark(self, bm):
255                 c = self.__conn.cursor()
256                 self.__deleteBookmark(bm, c)
257                 c.close()
258
259         def __getBookmarks(self, cursor):
260                 sql = "SELECT * FROM %s ORDER BY %s ASC" %(BrowserDB.TABLE_BM, BrowserDB.FIELD_BM_NAME)
261                 cursor.execute(sql)
262                 return self.__assignBookmarks(cursor)
263
264         def __filterBookmarks(self, cursor, needle):
265                 needle = "%" + needle + "%"
266                 sql = "SELECT * FROM %(table)s WHERE %(name)s LIKE ? ORDER BY %(name)s ASC" %{"table" : BrowserDB.TABLE_BM, "name" : BrowserDB.FIELD_BM_NAME}
267                 cursor.execute(sql, [needle])
268                 return self.__assignBookmarks(cursor)
269
270         def __assignBookmarks(self, cursor):
271                 bookmarks = []
272                 for item in cursor:
273                         bookmarks.append( Bookmark(int(item[0]), str(item[1]), str(item[2]), str(item[3])) )
274                 return bookmarks
275
276         def __addBookmark(self, bm, cursor):
277                 sql = "INSERT INTO %s (%s, %s, %s, %s) values (NULL, ?, ?, ?)" %(
278                         BrowserDB.TABLE_BM,
279                         BrowserDB.FIELD_ID,
280                         BrowserDB.FIELD_BM_NAME,
281                         BrowserDB.FIELD_BM_URL,
282                         BrowserDB.FIELD_BM_GROUPID
283                 )
284                 print sql
285                 cursor.execute(sql, (bm.name, bm.url, bm.group))
286                 self.__conn.commit()
287
288         def __updateBookmark(self, bm, cursor):
289                 sql = "UPDATE %s SET %s=?, %s=?, %s=? WHERE %s=?" %(
290                         BrowserDB.TABLE_BM,
291                         BrowserDB.FIELD_BM_NAME,
292                         BrowserDB.FIELD_BM_URL,
293                         BrowserDB.FIELD_BM_GROUPID,
294                         BrowserDB.FIELD_ID
295                 )
296                 print sql
297                 cursor.execute(sql, (bm.name, bm.url, bm.group, str(bm.id)) )
298                 self.__conn.commit()
299
300         def __deleteBookmark(self, bm, cursor):
301                 sql = "DELETE FROM %s WHERE %s=?" %(BrowserDB.TABLE_BM, BrowserDB.FIELD_ID)
302                 print sql
303                 cursor.execute(sql, (str(bm.id)) )
304                 self.__conn.commit()
305
306         def getHistory(self, needle = None):
307                 items = []
308                 c = self.__conn.cursor()
309                 if needle is None or needle == "":
310                         items = self.__getHistory(c)
311                 else:
312                         items = self.__filterHistory(c, needle)
313                 c.close()
314
315                 return items
316
317         def __getHistory(self, cursor):
318                 sql = "SELECT * FROM %s ORDER BY %s DESC" %(BrowserDB.TABLE_HISTORY, BrowserDB.FIELD_HIS_TS)
319                 cursor.execute(sql)
320                 return self.__assignHistory(cursor)
321
322         def __filterHistory(self, cursor, needle):
323                 needle = "%" + needle + "%"
324                 sql = "SELECT * FROM %(table)s WHERE %(title)s LIKE ? ORDER BY %(ts)s DESC" %{"table" : BrowserDB.TABLE_HISTORY, "title" : BrowserDB.FIELD_HIS_TITLE, "ts" : BrowserDB.FIELD_HIS_TS}
325                 cursor.execute(sql, [needle])
326                 return self.__assignHistory(cursor)
327
328         def __assignHistory(self, cursor):
329                 items = []
330                 for item in cursor:
331                         items.append( HistoryItem(int(item[0]), str(item[1]), str(item[2]), str(item[3])) )
332                 return items
333
334         def addToHistory(self, hi, cursor = None):
335                 c = None
336                 if cursor is None:
337                         c = self.__conn.cursor()
338                 else:
339                         c = cursor
340                 self.__addHistoryItem(hi, c)
341
342                 if cursor is None:
343                         c.close()
344
345         def clearHistory(self):
346                 c = self.__conn.cursor()
347                 self.__clearHistory(c)
348                 c.close()
349
350         def __clearHistory(self, cursor):
351                 sql = "DELETE FROM %s" %(BrowserDB.TABLE_HISTORY)
352                 cursor.execute(sql)
353                 self.__conn.commit()
354
355         def __addHistoryItem(self, hi, cursor):
356                 sql = "INSERT INTO %s (%s, %s, %s, %s) values (NULL, ?, ?, ?)" %(
357                         BrowserDB.TABLE_HISTORY,
358                         BrowserDB.FIELD_ID,
359                         BrowserDB.FIELD_HIS_TS,
360                         BrowserDB.FIELD_HIS_TITLE,
361                         BrowserDB.FIELD_HIS_URL,
362                 )
363                 print sql
364                 cursor.execute(sql, (str(hi.timestamp), hi.title, hi.url))
365                 self.__conn.commit()
366
367         def suggsetUrls(self, needle):
368                 c = self.__conn.cursor()
369                 return self.__suggestUrls(c, needle)
370                 c.close()
371
372         def __suggestUrls(self, cursor, needle):
373                 needle = "%" + needle + "%"
374                 sql = "SELECT DISTINCT %(url)s FROM %(table)s WHERE %(url)s LIKE ? ORDER BY %(url)s ASC"
375
376                 urls = []
377                 sqlBM = sql %{"url" : BrowserDB.FIELD_BM_URL, "table" : BrowserDB.TABLE_BM}
378                 print sqlBM
379                 urls = self.__execUrlSearch(cursor, sqlBM, needle, urls)
380
381                 sqlHis = sql %{"url" : BrowserDB.FIELD_HIS_URL, "table" : BrowserDB.TABLE_HISTORY}
382                 print sqlHis
383                 urls = self.__execUrlSearch(cursor, sqlHis, needle, urls)
384
385                 self.__conn.commit()
386                 #remove duplicates
387                 d = {}
388                 for x in urls:
389                         d[x] = 1
390                 urls = list(d.keys())
391                 return urls
392
393         def __execUrlSearch(self, cursor, sql, needle, list):
394                 print "needle=%s" %needle
395                 cursor.execute(sql, [needle])
396                 for item in cursor:
397                         list.append(str(item[0]))
398                 return list
399
400         def addCert(self, cert):
401                 c = self.__conn.cursor()
402                 sql = "INSERT INTO %s (%s, %s, %s) values (NULL, ?, ?)" %(
403                         BrowserDB.TABLE_CERTS,
404                         BrowserDB.FIELD_ID,
405                         BrowserDB.FIELD_CERT_HOST,
406                         BrowserDB.FIELD_CERT_PEM
407                 )
408                 print sql
409                 c.execute(sql, (cert.host, cert.pem))
410                 self.__conn.commit()
411                 c.close()
412
413         def checkCert(self, cert):
414                 c = self.__conn.cursor()
415                 #First check if the given host + cert are in the exception list
416                 sql = "SELECT %s FROM %s WHERE %s LIKE ? AND %s LIKE ? LIMIT 1" %(
417                         BrowserDB.FIELD_ID,
418                         BrowserDB.TABLE_CERTS,
419                         BrowserDB.FIELD_CERT_HOST,
420                         BrowserDB.FIELD_CERT_PEM
421                 )
422
423                 c.execute(sql, (cert.host, cert.pem))
424                 for item in c:
425                         c.close()
426                         return BrowserDB.CERT_OK
427
428                 #Check if there is any other known "exceptional" cert for the given host, if so - BE CAREFUL!
429                 sql = "SELECT %s FROM %s WHERE %s LIKE ? AND %s NOT LIKE ? LIMIT 1" %(
430                         BrowserDB.FIELD_ID,
431                         BrowserDB.TABLE_CERTS,
432                         BrowserDB.FIELD_CERT_HOST,
433                         BrowserDB.FIELD_CERT_PEM
434                 )
435                 c.execute(sql, (cert.host, cert.pem))
436                 for item in c:
437                         c.close()
438                         return BrowserDB.CERT_CHANGED
439
440                 c.close()
441                 return self.CERT_UNKOWN
442
443         def getCerts(self):
444                 items = []
445                 c = self.__conn.cursor()
446                 items = self.__getCerts(c)
447                 c.close()
448                 return items
449
450         def __getCerts(self, cursor):
451                 sql = "SELECT * FROM %s ORDER BY %s DESC" %(BrowserDB.TABLE_CERTS, BrowserDB.FIELD_CERT_HOST)
452                 cursor.execute(sql)
453                 return self.__assignCerts(cursor)
454
455         def __assignCerts(self, cursor):
456                 certs = []
457                 for item in cursor:
458                         certs.append( Certificate(item[0], item[1], item[2]) )
459                 return certs
460
461         def deleteCert(self, cert):
462                 c = self.__conn.cursor()
463                 self.__deleteCert(cert, c)
464                 c.close()
465
466         def __deleteCert(self, cert, cursor):
467                 sql = "DELETE FROM %s WHERE %s=?" %(BrowserDB.TABLE_CERTS, BrowserDB.FIELD_ID)
468                 print sql
469                 cursor.execute(sql, (str(cert.id)) )
470                 self.__conn.commit()
471
472         def getCookies(self):
473                 cookies = []
474                 c = self.__conn.cursor()
475                 cookies = self.__getCookies(c)
476                 c.close()
477                 return cookies
478
479         def __getCookies(self, cursor):
480                 sql = "SELECT * FROM %s ORDER BY %s ASC" %(BrowserDB.TABLE_COOKIES, BrowserDB.FIELD_COOKIE_DOMAIN)
481                 cursor.execute(sql)
482                 return self.__assignCookies(cursor)
483
484         def __assignCookies(self, cursor):
485                 cookies = []
486                 for c in cursor:
487                         cookies.append(Cookie(c[0], c[1], c[2], c[3], c[4]))
488                 return cookies
489
490         def persistCookies(self, cookies):
491                 c = self.__conn.cursor()
492                 self.__persistCookies(cookies, c)
493                 c.close()
494
495         def __persistCookies(self, cookies, cursor = None):
496                 sql = "REPLACE INTO %s (%s, %s, %s, %s, %s) values ( ?, ?, ?, ?, ? )" %(
497                         BrowserDB.TABLE_COOKIES,
498                         BrowserDB.FIELD_COOKIE_KEY,
499                         BrowserDB.FIELD_COOKIE_DOMAIN,
500                         BrowserDB.FIELD_COOKIE_PATH,
501                         BrowserDB.FIELD_COOKIE_RAW,
502                         BrowserDB.FIELD_COOKIE_EXPIRES)
503                 print sql
504                 sqlDel = "DELETE FROM %(table)s WHERE %(expires)s > 0 and %(expires)s < ?" %{ 'table' : BrowserDB.TABLE_COOKIES, 'expires' : BrowserDB.FIELD_COOKIE_EXPIRES }
505                 print sqlDel
506                 c = None
507                 if cursor is None:
508                         c = self.__conn.cursor()
509                 else:
510                         c = cursor
511
512                 c.executemany(sql, IterCookies(cookies))
513                 self.__conn.commit()
514
515                 c.execute(sqlDel, [str(time())])
516                 self.__conn.commit()
517
518                 if cursor is None:
519                         c.close()
520
521         def deleteAllCookies(self):
522                 c = self.__conn.cursor()
523                 self.__deleteAllCookies(c)
524                 c.close()
525
526         def __deleteAllCookies(self, cursor):
527                 sql = "DELETE FROM %s" %(BrowserDB.TABLE_COOKIES)
528                 cursor = self.__conn.cursor()
529                 cursor.execute(sql)
530                 self.__conn.commit()
531                 cursor.close()
532
533         def deleteCookie(self, cookie):
534                 c = self.__conn.cursor()
535                 self.__deleteCookie(cookie, c)
536                 c.close()
537
538         def __deleteCookie(self, cookie, cursor):
539                 sql = "DELETE FROM %s WHERE %s like ? AND %s like ? AND %s like ?" %(BrowserDB.TABLE_COOKIES, BrowserDB.FIELD_COOKIE_KEY, BrowserDB.FIELD_COOKIE_DOMAIN, BrowserDB.FIELD_COOKIE_PATH)
540                 cursor = self.__conn.cursor()
541                 cursor.execute(sql, (cookie.key, cookie.domain, cookie.path))
542                 self.__conn.commit()
543                 cursor.close()