database_bookmarks.py 39.3 KB
Newer Older
Cédric Bellegarde's avatar
Cédric Bellegarde committed
1
# Copyright (c) 2017-2019 Cedric Bellegarde <cedric.bellegarde@adishatz.org>
2 3 4 5 6 7 8 9 10 11 12 13 14 15
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>.

from gi.repository import GLib, Gio

import sqlite3
16
import itertools
17
from urllib.parse import urlparse
Cédric Bellegarde's avatar
Cédric Bellegarde committed
18
from threading import Lock
Cédric Bellegarde's avatar
Cédric Bellegarde committed
19
from re import sub
20

21
from eolie.utils import noaccents, get_random_string
22
from eolie.define import EOLIE_DATA_PATH, Type
23
from eolie.localized import LocalizedCollation
24
from eolie.sqlcursor import SqlCursor
25
from eolie.logger import Logger
26
from eolie.database_upgrade import DatabaseUpgrade
27 28 29 30 31 32


class DatabaseBookmarks:
    """
        Eolie bookmarks db
    """
33

34
    DB_PATH = "%s/bookmarks.db" % EOLIE_DATA_PATH
35 36 37 38 39 40 41 42 43

    # SQLite documentation:
    # In SQLite, a column with type INTEGER PRIMARY KEY
    # is an alias for the ROWID.
    # Here, we define an id INT PRIMARY KEY but never feed it,
    # this make VACUUM not destroy rowids...
    __create_bookmarks = '''CREATE TABLE bookmarks (
                                               id INTEGER PRIMARY KEY,
                                               title TEXT NOT NULL,
44 45
                                               uri TEXT NOT NULL,
                                               popularity INT NOT NULL,
46
                                               atime REAL NOT NULL,
47 48
                                               guid TEXT NOT NULL,
                                               mtime REAL NOT NULL,
Cédric Bellegarde's avatar
Cédric Bellegarde committed
49
                                               position INT DEFAULT 0
50
                                               )'''
51 52 53 54 55 56
    __create_tags = '''CREATE TABLE tags (id INTEGER PRIMARY KEY,
                                          title TEXT NOT NULL)'''
    __create_bookmarks_tags = '''CREATE TABLE bookmarks_tags (
                                                    id INTEGER PRIMARY KEY,
                                                    bookmark_id INT NOT NULL,
                                                    tag_id INT NOT NULL)'''
57 58 59 60 61 62
    # Only useful for Firefox compatibility
    __create_parents = '''CREATE TABLE parents (
                                        id INTEGER PRIMARY KEY,
                                        bookmark_id INT NOT NULL,
                                        parent_guid TEXT NOT NULL,
                                        parent_name TEXT NOT NULL)'''
63 64 65 66 67

    def __init__(self):
        """
            Create database tables or manage update if needed
        """
68
        upgrade = DatabaseUpgrade(Type.BOOKMARK)
Cédric Bellegarde's avatar
Cédric Bellegarde committed
69
        self.thread_lock = Lock()
70
        if not GLib.file_test(self.DB_PATH, GLib.FileTest.IS_REGULAR):
71
            try:
72 73
                if not GLib.file_test(EOLIE_DATA_PATH, GLib.FileTest.IS_DIR):
                    GLib.mkdir_with_parents(EOLIE_DATA_PATH, 0o0750)
74 75 76
                # Create db schema
                with SqlCursor(self) as sql:
                    sql.execute(self.__create_bookmarks)
77 78
                    sql.execute(self.__create_tags)
                    sql.execute(self.__create_bookmarks_tags)
79
                    sql.execute(self.__create_parents)
80
                    sql.execute("PRAGMA user_version=%s" % upgrade.version)
81
            except Exception as e:
82
                Logger.error("DatabaseBookmarks::__init__(): %s", e)
83 84
        else:
            upgrade.upgrade(self)
85

86
    def add(self, title, uri, guid, tags, atime=0):
87 88 89 90
        """
            Add a new bookmark
            @param title as str
            @param uri as str
91
            @param guid as str
92
            @param tags as [str]
93
            @param parent_guid as str
94
            @param ctime as int
95
            @return bookmark id as int
96
        """
97 98 99 100 101 102
        # Find an uniq guid
        while guid is None:
            guid = get_random_string(12)
            if self.exists_guid(guid):
                guid = None

103
        with SqlCursor(self) as sql:
104
            result = sql.execute("INSERT INTO bookmarks\
105 106
                                  (title, uri, popularity, guid, atime, mtime)\
                                  VALUES (?, ?, ?, ?, ?, ?)",
107
                                 (title, uri.rstrip('/'), 0, guid, atime, 0))
108 109 110 111 112 113
            bookmarks_id = result.lastrowid
            for tag in tags:
                if not tag:
                    continue
                tag_id = self.get_tag_id(tag)
                if tag_id is None:
114
                    tag_id = self.add_tag(tag)
115 116 117
                sql.execute("INSERT INTO bookmarks_tags\
                             (bookmark_id, tag_id) VALUES (?, ?)",
                            (bookmarks_id, tag_id))
118
            return bookmarks_id
119

120
    def remove(self, bookmark_id):
121 122 123
        """
            Remove bookmark from db
            @param bookmark id as int
124
            @param commit as bool
125 126 127 128 129 130
        """
        with SqlCursor(self) as sql:
            sql.execute("DELETE FROM bookmarks\
                         WHERE rowid=?", (bookmark_id,))
            sql.execute("DELETE FROM bookmarks_tags\
                         WHERE bookmark_id=?", (bookmark_id,))
131 132
            sql.execute("DELETE FROM parents\
                         WHERE bookmark_id=?", (bookmark_id,))
133

134
    def add_tag(self, tag):
135 136 137 138 139 140 141 142 143 144 145
        """
            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,))
            return result.lastrowid

146
    def del_tag(self, tag):
147 148 149 150 151 152 153 154 155 156 157 158 159
        """
            Add tag to db, return existing if exists
            @param tag as str
        """
        with SqlCursor(self) as sql:
            tag_id = self.get_tag_id(tag)
            if tag_id is None:
                return
            sql.execute("DELETE FROM tags\
                         WHERE rowid=?", (tag_id,))
            sql.execute("DELETE FROM bookmarks_tags\
                         WHERE tag_id=?", (tag_id,))

Cédric Bellegarde's avatar
Cédric Bellegarde committed
160 161 162 163 164 165 166 167 168
    def rename_tag(self, old, new):
        """
            Rename tag
            @param old as str
            @param new as str
        """
        with SqlCursor(self) as sql:
            sql.execute("UPDATE tags set title=? WHERE title=?", (new, old))

169 170 171 172 173 174 175 176 177 178 179 180 181 182 183
    def get_tags(self, bookmark_id):
        """
            Get tags for bookmark id
            @param bookmark id as int
            @return [str]
        """
        with SqlCursor(self) as sql:
            result = sql.execute("SELECT tags.title\
                                  FROM tags, bookmarks_tags\
                                  WHERE bookmarks_tags.bookmark_id=?\
                                  AND bookmarks_tags.tag_id=tags.rowid\
                                  ORDER BY title COLLATE LOCALIZED",
                                 (bookmark_id,))
            return list(itertools.chain(*result))

184 185 186 187 188 189 190 191 192 193
    def has_tag(self, bookmark_id, tag):
        """
            Return True if bookmark id as tag
            @param bookmark id as int
            @param tag as str
            @return bool
        """
        with SqlCursor(self) as sql:
            result = sql.execute("SELECT tags.rowid\
                                  FROM tags, bookmarks_tags\
194
                                  WHERE tags.title=? COLLATE NOCASE\
195 196 197 198 199 200 201 202
                                  AND bookmarks_tags.bookmark_id=?\
                                  AND bookmarks_tags.tag_id=tags.rowid",
                                 (tag, bookmark_id))
            v = result.fetchone()
            if v is not None:
                return True
            return False

203 204 205 206
    def get_id(self, uri):
        """
            Get id for uri
            @param uri as str
207
            @return id as int
208
        """
Cédric Bellegarde's avatar
Cédric Bellegarde committed
209 210
        if uri is None:
            return None
211 212 213
        with SqlCursor(self) as sql:
            result = sql.execute("SELECT rowid\
                                  FROM bookmarks\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
214
                                  WHERE uri=?", (uri.rstrip('/'),))
215 216 217 218 219
            v = result.fetchone()
            if v is not None:
                return v[0]
            return None

220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243
    def get_id_by_guid(self, guid):
        """
            Get id for guid
            @param guid as str
            @return id as int
        """
        with SqlCursor(self) as sql:
            result = sql.execute("SELECT rowid\
                                  FROM bookmarks\
                                  WHERE guid=?", (guid,))
            v = result.fetchone()
            if v is not None:
                return v[0]
            return None

    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 bookmarks\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
244
                                  WHERE mtime > ?\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
245
                                  AND uri != guid", (mtime,))
246 247
            return list(itertools.chain(*result))

248
    def get_parent_guid(self, bookmark_id):
249
        """
250
            Get parent for bookmark
251
            @param bookmark id as int
252
            @return guid as str
253 254
        """
        with SqlCursor(self) as sql:
255
            result = sql.execute("SELECT parent_guid\
256 257
                                  FROM parents\
                                  WHERE bookmark_id=?", (bookmark_id,))
258
            v = result.fetchone()
259
            if v is not None and v[0] is not None:
260
                return v[0]
261
            return "unfiled"
262 263 264 265 266 267 268 269 270 271 272 273

    def get_parent_name(self, bookmark_id):
        """
            Get parent for bookmark
            @param bookmark id as int
            @return name as str
        """
        with SqlCursor(self) as sql:
            result = sql.execute("SELECT parent_name\
                                  FROM parents\
                                  WHERE bookmark_id=?", (bookmark_id,))
            v = result.fetchone()
Cédric Bellegarde's avatar
Cédric Bellegarde committed
274
            if v is not None and v[0] is not None:
275
                return v[0]
Cédric Bellegarde's avatar
Cédric Bellegarde committed
276
            return ""
277

278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307
    def get_title(self, bookmark_id):
        """
            Get bookmark title
            @param bookmark id as int
            @return title as str
        """
        with SqlCursor(self) as sql:
            result = sql.execute("SELECT title\
                                  FROM bookmarks\
                                  WHERE rowid=?", (bookmark_id,))
            v = result.fetchone()
            if v is not None:
                return v[0]
            return ""

    def get_uri(self, bookmark_id):
        """
            Get bookmark uri
            @param bookmark id as int
            @return uri as str
        """
        with SqlCursor(self) as sql:
            result = sql.execute("SELECT uri\
                                  FROM bookmarks\
                                  WHERE rowid=?", (bookmark_id,))
            v = result.fetchone()
            if v is not None:
                return v[0]
            return ""

308 309 310 311 312 313 314 315 316 317 318 319 320
    def get_guid(self, bookmark_id):
        """
            Get bookmark guid
            @param bookmark id as int
            @return guid as str
        """
        with SqlCursor(self) as sql:
            result = sql.execute("SELECT guid\
                                  FROM bookmarks\
                                  WHERE rowid=?", (bookmark_id,))
            v = result.fetchone()
            if v is not None:
                return v[0]
321
            return None
322 323 324 325 326 327 328 329 330 331

    def get_guids(self):
        """
            Get all guids
            @return guids as [str]
        """
        with SqlCursor(self) as sql:
            result = sql.execute("SELECT guid FROM bookmarks")
            return list(itertools.chain(*result))

332 333 334
    def get_children(self, guid):
        """
            Get guid children
335
            @param guid as str
336 337 338 339 340 341 342 343 344 345
            @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))

346 347 348 349 350 351 352 353 354 355 356 357 358 359 360
    def get_mtime(self, bookmark_id):
        """
            Get bookmark mtime
            @param bookmark id as int
            @return mtime as int
        """
        with SqlCursor(self) as sql:
            result = sql.execute("SELECT mtime\
                                  FROM bookmarks\
                                  WHERE rowid=?", (bookmark_id,))
            v = result.fetchone()
            if v is not None:
                return v[0]
            return 0

361 362 363 364 365 366 367 368 369 370 371 372 373 374 375
    def get_position(self, bookmark_id):
        """
            Get bookmark position
            @param bookmark id as int
            @return position as int
        """
        with SqlCursor(self) as sql:
            result = sql.execute("SELECT position\
                                  FROM bookmarks\
                                  WHERE rowid=?", (bookmark_id,))
            v = result.fetchone()
            if v is not None:
                return v[0]
            return 0

376 377 378 379
    def get_tag_id(self, title):
        """
            Get tag id
            @param title as str
380
            @return tag id as int
381 382 383 384
        """
        with SqlCursor(self) as sql:
            result = sql.execute("SELECT rowid\
                                  FROM tags\
385
                                  WHERE title=? COLLATE NOCASE", (title,))
386 387 388 389 390
            v = result.fetchone()
            if v is not None:
                return v[0]
            return None

391 392 393 394 395 396 397 398 399 400 401 402 403 404 405
    def get_tag_title(self, tag_id):
        """
            Get tag id title
            @param tag id as int
            @return title as str
        """
        with SqlCursor(self) as sql:
            result = sql.execute("SELECT tags.title\
                                  FROM tags\
                                  WHERE id=?", (tag_id,))
            v = result.fetchone()
            if v is not None:
                return v[0]
            return None

406
    def get_all_tags(self):
407 408 409 410 411 412 413 414 415 416
        """
            Get all tags
            @return [rowid, str]
        """
        with SqlCursor(self) as sql:
            result = sql.execute("SELECT rowid, title\
                                  FROM tags\
                                  ORDER BY title COLLATE LOCALIZED")
            return list(result)

417
    def get_bookmarks(self, tag_id=None):
418 419
        """
            Get all bookmarks
420 421
            @param tag id as int
            @return [(id, title, uri)]
422 423
        """
        with SqlCursor(self) as sql:
424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441
            if tag_id is None:
                result = sql.execute("\
                                SELECT bookmarks.rowid,\
                                       bookmarks.uri,\
                                       bookmarks.title\
                                FROM bookmarks\
                                ORDER BY bookmarks.popularity DESC")
            else:
                result = sql.execute("\
                                SELECT bookmarks.rowid,\
                                       bookmarks.uri,\
                                       bookmarks.title\
                                FROM bookmarks, bookmarks_tags\
                                WHERE bookmarks.rowid=\
                                      bookmarks_tags.bookmark_id\
                                      AND bookmarks_tags.tag_id=?\
                                      AND bookmarks.guid != bookmarks.uri\
                                ORDER BY bookmarks.popularity DESC", (tag_id,))
442 443
            return list(result)

444
    def get_populars(self, limit):
445 446
        """
            Get popular bookmarks
447
            @param limit as bool
Cédric Bellegarde's avatar
Cédric Bellegarde committed
448
            @return [(id, title, uri)]
449 450 451 452
        """
        with SqlCursor(self) as sql:
            result = sql.execute("\
                            SELECT bookmarks.rowid,\
453 454
                                   bookmarks.uri,\
                                   bookmarks.title\
455
                            FROM bookmarks\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
456
                            WHERE popularity!=0\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
457
                            AND bookmarks.guid != bookmarks.uri\
458 459
                            ORDER BY bookmarks.popularity DESC,\
                            bookmarks.atime DESC\
460
                            LIMIT ?", (limit,))
461 462
            return list(result)

Cédric Bellegarde's avatar
Cédric Bellegarde committed
463 464 465 466 467 468 469 470
    def get_unclassified(self):
        """
            Get bookmarks without tag
            @return [(id, title, uri)]
        """
        with SqlCursor(self) as sql:
            result = sql.execute("\
                            SELECT bookmarks.rowid,\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
471 472
                                   bookmarks.uri,\
                                   bookmarks.title\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
473
                            FROM bookmarks\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
474
                            WHERE NOT EXISTS (\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
475 476
                                SELECT bookmark_id FROM bookmarks_tags\
                                WHERE bookmark_id=bookmarks.rowid)\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
477
                            AND bookmarks.guid != bookmarks.uri\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
478 479 480
                            ORDER BY bookmarks.popularity DESC")
            return list(result)

481 482 483
    def get_recents(self):
        """
            Get recents bookmarks
Cédric Bellegarde's avatar
Cédric Bellegarde committed
484
            @return [(id, title, uri)]
485 486
        """
        with SqlCursor(self) as sql:
487
            result = sql.execute("SELECT bookmarks.rowid,\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
488 489
                                  bookmarks.uri,\
                                  bookmarks.title\
490
                                  FROM bookmarks\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
491
                                  WHERE bookmarks.guid != bookmarks.uri\
492
                                  ORDER BY bookmarks.mtime DESC")
493 494
            return list(result)

495 496 497 498 499 500 501 502 503 504 505 506 507 508 509
    def get_popularity(self, bookmark_id):
        """
            Get popularity for bookmark id
            @param bookmark_id as int
            @return popularity as int
        """
        with SqlCursor(self) as sql:
            result = sql.execute("SELECT popularity\
                                  FROM bookmarks\
                                  WHERE rowid=?", (bookmark_id,))
            v = result.fetchone()
            if v is not None:
                return v[0]
            return 0

Cédric Bellegarde's avatar
Cédric Bellegarde committed
510
    def get_higher_popularity(self):
511
        """
Cédric Bellegarde's avatar
Cédric Bellegarde committed
512
            Get higher available popularity
513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538
            @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_avg_popularity(self):
        """
            Return avarage popularity
            @return avarage popularity as int
        """
        with SqlCursor(self) as sql:
            result = sql.execute("SELECT AVG(popularity)\
                                  FROM (SELECT popularity\
                                        FROM bookmarks\
                                        ORDER BY POPULARITY DESC LIMIT 100)")
            v = result.fetchone()
            if v and v[0] > 5:
                return v[0]
            return 5

539 540 541 542 543 544 545 546 547 548 549
    def set_guid(self, bookmark_id, guid):
        """
            Set bookmark guid
            @param bookmark_id as int
            @param guid as str
        """
        with SqlCursor(self) as sql:
            sql.execute("UPDATE bookmarks\
                         SET guid=?\
                         WHERE rowid=?", (bookmark_id, guid))

550
    def set_title(self, bookmark_id, title):
551 552 553 554 555 556 557 558 559 560
        """
            Set bookmark title
            @param bookmark id as int
            @param title as str
        """
        with SqlCursor(self) as sql:
            sql.execute("UPDATE bookmarks\
                         SET title=?\
                         WHERE rowid=?", (title, bookmark_id,))

561
    def set_uri(self, bookmark_id, uri):
562 563 564 565 566 567 568 569
        """
            Set bookmark uri
            @param bookmark id as int
            @param uri as str
        """
        with SqlCursor(self) as sql:
            sql.execute("UPDATE bookmarks\
                         SET uri=?\
570
                         WHERE rowid=?", (uri.rstrip('/'), bookmark_id,))
571

572
    def set_popularity(self, bookmark_id, popularity):
573 574 575 576 577 578 579 580 581 582
        """
            Set bookmark popularity
            @param bookmark id as int
            @param popularity as int
            @param commit as bool
        """
        with SqlCursor(self) as sql:
            sql.execute("UPDATE bookmarks\
                         SET popularity=?\
                         WHERE rowid=?", (popularity, bookmark_id,))
583

584
    def set_parent(self, bookmark_id, parent_guid, parent_name):
585 586
        """
            Set parent id for bookmark
587 588 589
            @param bookmark_id as int
            @param parent_guid as str
            @param parent_name as str
590 591
        """
        with SqlCursor(self) as sql:
592 593 594 595 596
            result = sql.execute("SELECT parent_guid\
                                  FROM parents\
                                  WHERE bookmark_id=?", (bookmark_id,))
            v = result.fetchone()
            if v is None or v[0] is None:
597 598 599 600 601 602 603 604 605
                sql.execute("INSERT INTO parents\
                             (bookmark_id, parent_guid, parent_name)\
                             VALUES (?, ?, ?)",
                            (bookmark_id, parent_guid, parent_name))
            else:
                sql.execute("UPDATE parents\
                             SET parent_guid=?, parent_name=?\
                             WHERE bookmark_id=?",
                            (parent_guid, parent_name, bookmark_id))
606

607 608 609 610 611 612 613 614
    def set_access_time(self, uri, atime):
        """
            Set bookmark access time
            @param uri as str
            @param atime as int
        """
        with SqlCursor(self) as sql:
            sql.execute("UPDATE bookmarks\
615
                         SET atime=? where uri=?", (atime, uri.rstrip('/')))
616

617
    def set_mtime(self, bookmark_id, mtime):
618 619 620 621 622 623 624 625 626 627
        """
            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))

628
    def set_position(self, bookmark_id, position):
629 630 631 632 633 634 635 636 637 638 639
        """
            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))

640 641 642 643 644 645 646 647 648
    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,))

649 650 651 652 653 654
    def set_more_popular(self, uri):
        """
            Increment bookmark popularity
            @param uri as str
        """
        with SqlCursor(self) as sql:
655
            uri = uri.rstrip('/')
656 657 658 659 660
            result = sql.execute("SELECT popularity FROM bookmarks\
                                  WHERE uri=?", (uri,))
            v = result.fetchone()
            if v is not None:
                sql.execute("UPDATE bookmarks set popularity=?\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
661
                             WHERE uri=?", (v[0] + 1, uri))
662

663
    def add_tag_to(self, tag_id, bookmark_id):
664 665 666 667 668 669 670 671 672 673 674
        """
            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))

675
    def del_tag_from(self, tag_id, bookmark_id):
676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693
        """
            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 clean_tags(self):
        """
            Remove orphan tags
        """
        with SqlCursor(self) as sql:
            sql.execute("DELETE from tags\
                         WHERE NOT EXISTS (\
694 695 696
                            SELECT bookmarks_tags.rowid\
                            FROM bookmarks, bookmarks_tags\
                            WHERE tags.rowid = bookmarks_tags.tag_id\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
697
                            AND bookmarks.rowid = bookmarks_tags.bookmark_id)")
698 699 700 701 702 703 704 705 706

    def reset_popularity(self, uri):
        """
            Reset popularity for uri
            @param uri as str
        """
        with SqlCursor(self) as sql:
            sql.execute("UPDATE bookmarks SET popularity=0 WHERE uri=?",
                        (uri,))
707

708 709 710 711 712 713 714 715 716 717 718 719
    def is_empty(self):
        """
            True if db is empty
            @return bool
        """
        with SqlCursor(self) as sql:
            result = sql.execute("SELECT rowid FROM bookmarks LIMIT 1")
            v = result.fetchone()
            if v is not None:
                return True
            return False

720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761
    def import_html(self, path):
        """
            Import html bookmarks
            @param path as str
        """
        try:
            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,
762
                                                   tags, 0)
763
                            # Set position
764
                            self.set_position(bookmark_id, position)
765 766 767
                            position += 1
            SqlCursor.remove(self)
        except Exception as e:
768
            Logger.error("DatabaseBookmarks::import_html(): %s", e)
769

770 771 772 773 774 775 776 777 778 779
    def import_chromium(self, chrome):
        """
            Chromium/Chrome importer
            As Eolie doesn't sync with Chromium, we do not handle parent
            guid and just import parents as tags
            @param chrome as bool
        """
        try:
            SqlCursor.add(self)
            import json
780
            homedir = GLib.get_home_dir()
781
            if chrome:
782
                path = homedir + "/.config/chrome/Default/Bookmarks"
783
            else:
784
                path = homedir + "/.config/chromium/Default/Bookmarks"
785
            status = False
786
            f = Gio.File.new_for_path(path)
787 788
            if f.query_exists():
                (status, content, tag) = f.load_contents(None)
789 790 791 792 793 794 795 796 797 798 799 800 801 802
            if status:
                data = content.decode("utf-8")
                j = json.loads(data)
                parents = []
                # Setup initial parents
                for root in j["roots"]:
                    parents.append(("", j["roots"][root]["children"]))
                # Walk parents and children
                while parents:
                    (parent_name, children) = parents.pop(0)
                    bookmarks = []
                    for child in children:
                        if child["type"] == "folder":
                            parents.append((child["name"], child["children"]))
803 804
                        elif child["type"] == "url":
                            bookmarks.append((child["name"],
Cédric Bellegarde's avatar
Cédric Bellegarde committed
805
                                              child["url"]))
806 807 808 809 810 811
                    position = 0
                    for bookmark in bookmarks:
                        tags = [parent_name]
                        title = bookmark[0]
                        uri = bookmark[1]
                        if not uri.startswith('http') or not title:
812
                            continue
813 814 815 816 817
                        uri = uri.rstrip('/')
                        rowid = self.get_id(uri)
                        if rowid is None:
                            # Add bookmark
                            bookmark_id = self.add(title, uri, None,
818
                                                   tags, 0)
819
                            # Set position
820
                            self.set_position(bookmark_id, position)
821
                            position += 1
822
            SqlCursor.remove(self)
823
        except Exception as e:
824
            Logger.error("DatabaseBookmarks::import_chromium(): %s", e)
825

826
    def import_firefox(self, profile):
827 828
        """
            Mozilla Firefox importer
829
            @param profile as str
830
        """
831 832
        try:
            SqlCursor.add(self)
833 834 835 836 837 838
            path = "%s/.mozilla/firefox/%s/places.sqlite" % \
                (GLib.get_home_dir(),
                 profile)
            f = Gio.File.new_for_path(path)
            if f.query_exists():
                c = sqlite3.connect(path, 600.0)
839 840
                # Add bookmarks
                bookmarks = self.__get_firefox_bookmarks(c)
Cédric Bellegarde's avatar
Cédric Bellegarde committed
841
                for (title, uri, parent_name, bookmark_guid,
842 843 844 845 846 847 848 849 850 851 852 853 854 855 856
                     parent_guid, position) in bookmarks:
                    tags = self.__get_tags_for_firefox_bookmark(c,
                                                                bookmark_guid)
                    bookmark_guid = self.__clean_guid(bookmark_guid)
                    parent_guid = self.__clean_guid(parent_guid)
                    if not uri.startswith('http') or not title:
                        continue
                    uri = uri.rstrip('/')
                    rowid = self.get_id(uri)
                    if rowid is None:
                        # If bookmark is not tagged, we use parent name
                        if not tags:
                            tags = [parent_name]
                        # Bookmarks and folder
                        bookmark_id = self.add(title, uri, bookmark_guid,
857
                                               tags, 0)
858
                        self.set_parent(bookmark_id, parent_guid,
859 860
                                        parent_name)
                        self.set_position(bookmark_id, position)
861
                # Add folders, we need to get them
862
                # as Firefox needs children order
863 864 865 866 867 868 869 870 871 872 873 874
                parents = self.__get_firefox_parents(c)
                for (title, parent_name, bookmark_guid,
                     parent_guid, position) in parents:
                    bookmark_guid = self.__clean_guid(bookmark_guid)
                    parent_guid = self.__clean_guid(parent_guid)
                    if not title or bookmark_guid == "root":
                        continue
                    uri = bookmark_guid
                    rowid = self.get_id(uri)
                    if rowid is None:
                        # Bookmarks and folder
                        bookmark_id = self.add(title, uri, bookmark_guid,
875
                                               [], 0)
876
                        self.set_parent(bookmark_id, parent_guid,
877 878
                                        parent_name)
                        self.set_position(bookmark_id, position)
879 880
            SqlCursor.remove(self)
        except Exception as e:
881
            Logger.error("DatabaseBookmarks::import_firefox(): %s", e)
882 883 884 885 886 887 888 889 890 891 892

    def exists_guid(self, guid):
        """
            Check if guid exists in db
            @return bool
        """
        with SqlCursor(self) as sql:
            result = sql.execute("SELECT guid FROM bookmarks\
                                  WHERE guid=?", (guid,))
            v = result.fetchone()
            return v is not None
893

Cédric Bellegarde's avatar
Cédric Bellegarde committed
894
    def search(self, search, limit):
895 896 897
        """
            Search string in db (uri and title)
            @param search as str
Cédric Bellegarde's avatar
Cédric Bellegarde committed
898
            @param limit as int
899
            @return [(id, title, uri, score)] as [(int, str, str, int)]
900
        """
Cédric Bellegarde's avatar
Cédric Bellegarde committed
901
        words = sub("[^\w]", " ", search.lower()).split()
902
        items = []
903
        with SqlCursor(self) as sql:
904 905 906 907
            filters = ()
            for word in words:
                filters += ("%" + word + "%", "%" + word + "%")
            filters += (limit,)
908 909

            # Search items matching all words
910
            request = "SELECT rowid, title, uri\
911
                       FROM bookmarks WHERE "
912 913 914 915
            words_copy = list(words)
            while words_copy:
                word = words_copy.pop(0)
                if word:
916 917 918
                    request += " (title LIKE ? OR uri LIKE ?) AND"
            request += " guid != uri ORDER BY mtime DESC,\
                        popularity DESC LIMIT ?"
919 920 921 922

            result = sql.execute(request, filters)
            items += list(result)

923
            # Search items matching any word
924
            request = "SELECT rowid, title, uri\
925
                       FROM bookmarks WHERE\
926
                       guid != uri"
927
            words_copy = list(words)
928 929
            if words:
                request += " AND ("
930 931 932
            while words_copy:
                word = words_copy.pop(0)
                if word:
933
                    request += "title LIKE ? OR uri LIKE ?"
934 935
                    if words_copy:
                        request += " OR "
936 937 938
            if words:
                request += ")"
            request += " ORDER BY mtime DESC, popularity DESC LIMIT ?"
939 940
            result = sql.execute(request, filters)
            items += list(result)
941

942 943 944 945
        # Do some scoring calculation on items
        scored_items = []
        uris = []
        for item in items:
946 947 948
            uri = item[2]
            if uri in uris:
                continue
949
            score = 0
950 951 952 953 954 955
            title = item[1].lower()
            parsed = urlparse(uri)
            if not parsed.path:
                score += 2
            elif not parsed.query:
                score += 2
956
            for word in words:
957
                lower_word = word.lower()
958 959
                # If netloc match word, +1
                if parsed.netloc.find(lower_word) != -1:
960
                    score += len(lower_word) * 2
961
                # URI match
962 963 964 965 966 967 968
                elif uri.find(lower_word) != -1:
                    score += len(lower_word)
                # Title match
                elif title.find(lower_word) != -1:
                    score += len(lower_word) * 2
            scored_items.append((item[0], item[1], item[2], score))
            uris.append(uri)
969
        return scored_items
970 971 972 973 974 975 976

    def get_cursor(self):
        """
            Return a new sqlite cursor
        """
        try:
            c = sqlite3.connect(self.DB_PATH, 600.0)
977
            c.create_collation('LOCALIZED', LocalizedCollation())
978 979
            c.create_function("noaccents", 1, noaccents)
            return c
980 981
        except Exception as e:
            Logger.error("DatabaseBookmarks::get_cursor(): %s", e)
982 983 984 985 986
            exit(-1)

#######################
# PRIVATE             #
#######################
987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044
    def __get_firefox_bookmarks(self, c):
        """
            Return firefox bookmarks
            @param c as Sqlite cursor
            @return (title, url, parent title, guid, parent guid, position)
             as (str, str, str, str, str, int)
        """
        result = c.execute("SELECT bookmarks.title,\
                                   moz_places.url,\
                                   parent.title,\
                                   bookmarks.guid,\
                                   parent.guid,\
                                   bookmarks.position\
                            FROM moz_bookmarks AS bookmarks,\
                                 moz_bookmarks AS parent,\
                                 moz_places\
                            WHERE bookmarks.fk=moz_places.id\
                            AND parent.id=bookmarks.parent\
                            AND bookmarks.type=1")
        return list(result)

    def __get_tags_for_firefox_bookmark(self, c, guid):
        """
            Return firefox bookmarks
            @param c as Sqlite cursor
            @param guid as str
            @return (title, url, parent title, guid, parent guid, position)
             as (str, str, str, str, str, int)
        """
        result = c.execute("SELECT parent.title\
                            FROM moz_bookmarks AS bookmarks,\
                                 moz_bookmarks AS tag,\
                                 moz_bookmarks AS parent\
                            WHERE bookmarks.fk=tag.fk\
                            AND tag.fk=bookmarks.fk\
                            AND tag.title is null\
                            AND parent.id=tag.parent\
                            AND bookmarks.guid=?", (guid,))
        return list(itertools.chain(*result))

    def __get_firefox_parents(self, c):
        """
            Return firefox parents
            @param c as Sqlite cursor
            @return (title, parent title, guid, parent guid, position)
             as (str, str, str, str, str, int)
        """
        result = c.execute("SELECT bookmarks.title,\
                                   parent.title,\
                                   bookmarks.guid,\
                                   parent.guid,\
                                   bookmarks.position\
                            FROM moz_bookmarks AS bookmarks,\
                                 moz_bookmarks AS parent\
                            WHERE parent.id=bookmarks.parent\
                            AND bookmarks.type=2")
        return list(result)

1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062
    def __clean_guid(self, guid):
        """
            Clean guid to match sync API
            @param guid as str
            @return str
        """
        if guid == "root________":
            return "places"
        elif guid == "menu________":
            return "menu"
        elif guid == "toolbar_____":
            return "toolbar"
        elif guid == "unfiled_____":
            return "unfiled"
        elif guid == "mobile______":
            return "mobile"
        else:
            return guid