SQLite table for tags

[sjehuda]

Feature requests

This is a proposal to add two tables to the database.

Table: Bookmarks
Columns: Bookmark_ID, Bookmark_Title, ...

Table: Tags
Columns: Tag_ID, Tag_Name

Table: Bookmarks_Tags
Columns: Bookmark_ID, Tag_ID

This should be storage wise and accelerate queries.

See my implementation at Slixfeed/sqlite.py

        tagged_feeds_table_sql = (
            """
            CREATE TABLE IF NOT EXISTS tagged_feeds (
                id INTEGER NOT NULL,
                feed_id INTEGER NOT NULL,
                tag_id INTEGER NOT NULL,
                FOREIGN KEY ("feed_id") REFERENCES "feeds" ("id")
                  ON UPDATE CASCADE
                  ON DELETE CASCADE,
                FOREIGN KEY ("tag_id") REFERENCES "tags" ("id")
                  ON UPDATE CASCADE
                  ON DELETE CASCADE,
                PRIMARY KEY ("id")
              );
            """
            )

[LeXofLeviafan]

                id INTEGER NOT NULL,
                feed_id INTEGER NOT NULL,
                tag_id INTEGER NOT NULL,
--              ...
                PRIMARY KEY ("id")

Wouldn't the primary key be normally feed_id, tag_id in such tables (without a need for a dedicated id column)?

[sjehuda]

Yes, and I think this is what Liferea does too.

I am not an expert, so please do not consider my advises fully.

---

If we do this, loading of tags will be faster, yes? See #559 #594

[LeXofLeviafan]

I've considered this for some time, and I think I will include a variant of this suggested change into the “expanded DB” update draft. (The old tags field will still be retained for backwards compatibility; but any code that modifies tags directly will need to support both versions of the DB 😅)

…For now though, into discussions this goes.