def add_exception(self, uri):
"""
Add an exception
@param uri as str
"""
try:
with SqlCursor(self) as sql:
sql.execute("INSERT INTO exceptions (uri) VALUES (?)", (uri,))
sql.commit()
except:
pass
def remove_exception(self, uri):
"""
Remove an exception
@param uri as str
"""
try:
with SqlCursor(self) as sql:
sql.execute("DELETE FROM exceptions WHERE uri=?", (uri,))
sql.commit()
except:
pass
def __upgrade_1(self):
"""
Add a sorted field to artists
"""
with SqlCursor(El().history) as sql:
try:
sql.execute("ALTER TABLE history ADD"
" opened INT NOT NULL DEFAULT 0")
sql.commit()
except:
pass
def import_html(self, path):
"""
Import html bookmarks
@param path as str
"""
try:
self.thread_lock.acquire()
from bs4 import BeautifulSoup
SqlCursor.add(self)
f = Gio.File.new_for_path(path)
if not f.query_exists():
return
(status, content, tag) = f.load_contents(None)
if status:
data = content.decode("utf-8")
soup = BeautifulSoup(data, "html.parser")
parent_name = ""
position = 0
for dt in soup.findAll("dt"):
h3 = dt.find("h3")
if h3 is not None:
parent_name = h3.contents[0]
continue
else:
a = dt.find("a")
uri = a.get("href")
if a.get("tags") is None:
tags = [parent_name]
else:
tags = [a.get("tags")]
title = a.contents[0]
if uri is None:
parent_name = title
continue
elif not uri.startswith('http') or not title:
continue
uri = uri.rstrip('/')
rowid = self.get_id(uri)
if rowid is None:
if not tags:
tags = [parent_name]
# Add bookmark
bookmark_id = self.add(title, uri, None,
tags, 0, False)
# Set position
self.set_position(bookmark_id, position, False)
position += 1
with SqlCursor(self) as sql:
sql.commit()
SqlCursor.remove(self)
except Exception as e:
print("DatabaseBookmarks::import_html:", e)
finally:
self.thread_lock.release()
def exists_guid(self, guid):
"""
Check if guid exists in db
@return bool
"""
with SqlCursor(self) as sql:
result = sql.execute(
"SELECT guid FROM history\
WHERE guid=?", (guid, ))
v = result.fetchone()
return v is not None
def get_empties(self):
"""
Get empties history entries (without atime)
@return history ids as [int]
"""
with SqlCursor(self) as sql:
result = sql.execute("SELECT history.rowid FROM history\
WHERE NOT EXISTS (\
SELECT rowid FROM history_atime AS ha\
WHERE ha.history_id=history.rowid)")
return list(itertools.chain(*result))
def __update(self):
"""
Update database
"""
self.__cancellable.reset()
try:
SqlCursor.add(self)
session = Soup.Session.new()
request = session.request(self.__URI)
stream = request.send(self.__cancellable)
bytes = bytearray(0)
buf = stream.read_bytes(1024, self.__cancellable).get_data()
while buf:
bytes += buf
buf = stream.read_bytes(1024, self.__cancellable).get_data()
stream.close()
data = bytes.decode('utf-8')
j = json.loads(data)
with SqlCursor(self) as sql:
count = 0
for item in j:
if self.__cancellable.is_cancelled():
raise IOError("Cancelled")
sql.execute(
"INSERT INTO phishing\
(uri, mtime) VALUES (?, ?)",
(item["url"].rstrip("/"), self.__mtime))
count += 1
if count == 1000:
sql.commit()
count = 0
sql.commit()
# Delete removed entries
with SqlCursor(self) as sql:
sql.execute(
"DELETE FROM phishing\
WHERE mtime!=?", (self.__mtime, ))
sql.commit()
SqlCursor.remove(self)
except Exception as e:
print("DatabasePhishing::__update()", e)
def remove(self, history_id):
"""
Remove item from history
@param history id as int
"""
with SqlCursor(self) as sql:
sql.execute(
"DELETE from history\
WHERE rowid=?", (history_id, ))
sql.execute(
"DELETE from history_atime\
WHERE history_id=?", (history_id, ))
def get_default_css_rules(self):
"""
Return default css rules
"""
rules = ""
with SqlCursor(self) as sql:
request = "SELECT rule FROM adblock_css WHERE\
blacklist='' AND whitelist=''"
result = sql.execute(request)
for rule in list(itertools.chain(*result)):
rules += "%s,\n" % rule
return rules[:-2] + "{display: none !important;}"
def del_tag_from(self, tag_id, bookmark_id):
"""
Remove tag from bookmark
@param tag id as int
@param bookmark id as int
@param commit as bool
"""
with SqlCursor(self) as sql:
sql.execute(
"DELETE from bookmarks_tags\
WHERE bookmark_id=? and tag_id=?",
(bookmark_id, tag_id))
def get_ids_for_mtime(self, mtime):
"""
Get ids that need to be synced related to mtime
@param mtime as int
@return [int]
"""
with SqlCursor(self) as sql:
result = sql.execute(
"SELECT rowid\
FROM history\
WHERE mtime > ?", (mtime, ))
return list(itertools.chain(*result))
def set_position(self, bookmark_id, position):
"""
Set bookmark position
@param bookmark id as int
@param mtime as int
@param commit as bool
"""
with SqlCursor(self) as sql:
sql.execute(
"UPDATE bookmarks\
SET position=? where rowid=?",
(position, bookmark_id))
def set_mtime(self, history_id, mtime, commit=True):
"""
Set history mtime
@param history_id as int
@param mtime as int
@param commit as bool
"""
with SqlCursor(self) as sql:
sql.execute("UPDATE history\
SET mtime=? where rowid=?", (mtime, history_id))
if commit:
sql.commit()
def get_atimes(self, history_id):
"""
Get history access times
@param history_id as int
@return [int]
"""
with SqlCursor(self) as sql:
result = sql.execute(
"SELECT atime\
FROM history_atime\
WHERE history_id=?", (history_id, ))
return list(itertools.chain(*result))
def set_mtime(self, bookmark_id, mtime, commit=True):
"""
Set bookmark sync time
@param bookmark id as int
@param mtime as int
@param commit as bool
"""
with SqlCursor(self) as sql:
sql.execute("UPDATE bookmarks\
SET mtime=? where rowid=?", (mtime, bookmark_id))
if commit:
sql.commit()
def set_tag_title(self, tag_id, title):
"""
Set tag id title
@param tag id as int
@parma title as str
"""
with SqlCursor(self) as sql:
sql.execute("UPDATE tags SET title=? WHERE id=?", (
title,
tag_id,
))
sql.commit()
def add_tag_to(self, tag_id, bookmark_id):
"""
Add tag to bookmark
@param tag id as int
@param bookmark id as int
@param commit as bool
"""
with SqlCursor(self) as sql:
sql.execute(
"INSERT INTO bookmarks_tags\
(bookmark_id, tag_id) VALUES (?, ?)",
(bookmark_id, tag_id))
def is_an_exception(self, uri):
"""
True if uri not in exceptions
@param uri as str
@return bool
"""
with SqlCursor(self.__exceptions) as sql:
result = sql.execute(
"SELECT rowid FROM exceptions\
WHERE uri=?", (uri, ))
v = result.fetchone()
return v is not None
def add_tag(self, tag, commit=False):
"""
Add tag to db, return existing if exists
@param tag as str
@return tag id as int
"""
with SqlCursor(self) as sql:
result = sql.execute("INSERT INTO tags\
(title) VALUES (?)",
(tag,))
if commit:
sql.commit()
return result.lastrowid
def get_higher_popularity(self):
"""
Get higher available popularity
@return int
"""
with SqlCursor(self) as sql:
result = sql.execute("SELECT popularity\
FROM bookmarks\
ORDER BY POPULARITY DESC LIMIT 1")
v = result.fetchone()
if v is not None:
return v[0]
return 0
def get_recents(self):
"""
Get recents bookmarks
@return [(id, title, uri)]
"""
with SqlCursor(self) as sql:
result = sql.execute("SELECT bookmarks.rowid,\
bookmarks.uri,\
bookmarks.title\
FROM bookmarks\
WHERE bookmarks.guid != bookmarks.uri\
ORDER BY bookmarks.mtime DESC")
return list(result)
def clean_tags(self):
"""
Remove orphan tags
"""
with SqlCursor(self) as sql:
sql.execute("DELETE from tags\
WHERE NOT EXISTS (\
SELECT bookmarks_tags.rowid\
FROM bookmarks, bookmarks_tags\
WHERE tags.rowid = bookmarks_tags.tag_id\
AND bookmarks.rowid = bookmarks_tags.bookmark_id\
AND bookmarks.del!=1)")
sql.commit()
def set_uri(self, bookmark_id, uri, commit=True):
"""
Set bookmark uri
@param bookmark id as int
@param uri as str
@param commit as bool
"""
with SqlCursor(self) as sql:
sql.execute("UPDATE bookmarks\
SET uri=?\
WHERE rowid=?", (uri.rstrip('/'), bookmark_id,))
if commit:
sql.commit()
def set_title(self, bookmark_id, title, commit=True):
"""
Set bookmark title
@param bookmark id as int
@param title as str
@param commit as bool
"""
with SqlCursor(self) as sql:
sql.execute("UPDATE bookmarks\
SET title=?\
WHERE rowid=?", (title, bookmark_id,))
if commit:
sql.commit()
def delete(self, bookmark_id, delete=True, commit=True):
"""
Mark bookmark as deleted
@param bookmark id as int
@param delete as bool
@param commit as bool
"""
with SqlCursor(self) as sql:
sql.execute("UPDATE bookmarks\
SET del=?\
WHERE rowid=?", (delete, bookmark_id))
if commit:
sql.commit()
def find(self, value, domain=""):
"""
True if value is an exception
@param value as str
@param domain as str
@return bool
"""
with SqlCursor(self) as sql:
result = sql.execute(
"SELECT rowid FROM exceptions\
WHERE value=? AND domain=?", (value, domain))
v = result.fetchone()
return v is not None
def set_title(self, history_id, title, commit=True):
"""
Set history title
@param history_id as int
@param title as str
@param commit as bool
"""
with SqlCursor(self) as sql:
sql.execute("UPDATE history\
SET title=?\
WHERE rowid=?", (title, history_id,))
if commit:
sql.commit()
def __save_css_default_rule(self, line):
"""
Save default (without blacklist, whitelist) rule to db
@param line as str
"""
name = line[2:]
# Update entry if exists, create else
with SqlCursor(self) as sql:
debug("Add filter: %s" % name)
sql.execute(
"INSERT INTO adblock_css\
(name, mtime) VALUES (?, ?)",
(name, self.__adblock_mtime))
def get_children(self, guid):
"""
Get guid children
@param guid as str
@return [str]
"""
with SqlCursor(self) as sql:
result = sql.execute("SELECT bookmarks.guid\
FROM bookmarks, parents\
WHERE parents.parent_guid=?\
AND parents.bookmark_id=bookmarks.rowid\
ORDER BY position ASC", (guid,))
return list(itertools.chain(*result))
def get_from_atime(self, atime):
"""
Get history ids from atime
@param atime as int
@return modified history ids as [int]
"""
with SqlCursor(self) as sql:
result = sql.execute(
"SELECT DISTINCT history.rowid\
FROM history, history_atime\
WHERE history_atime.history_id=history.rowid\
AND atime >= ?", (atime, ))
return list(itertools.chain(*result))
