How to implement insert, update, delete with a many-to-many relationship for tags?

Question

I am creating an application to manage some ebooks and I would like to have tagging functionality. The best db design I have found is to be the many-to-many relationship. I know how to set up my tables but since I am inexperienced, I can't exactly figure out how an insert, update, delete would look like.

Ebook Table:
- id
- name

Tags Table:
- id
- name

TagsToEbooks
- ebook_id
- tag_id

I am working with C++ and Sqlite. Let's say I have a C++ function that takes an array of tags and I want to associate them with a certain ebook. How would I implement or write it? How should I use placeholders? Do I iterate over the array and run a SQL statement for each single tag? Moreover, if I am deleting all tags associated with an ebook, they might remain in the tags table with no associations in the linking table(that has ebook_id and tag_id).

Please teach me how to cover these scenarios.

Some thoughts I had using pseudocode:

// When I want to insert a books tags
insert or select id if  it exists
then insert association


// When I want to update a books tags
delete associations of book
then do the insert function


// When I want to delete a books tags
DELETE FROM tasglink WHERE book_id in (SELECT id FROM ebooks WHERE name = :name)

//Create a trigger After delete on tagslink
DELETE FROM tags WHERE id NOT IN (SELECT id FROM tagslink WHERE GROUP BY id)

Answer 1

The implementation may be:

-- books table
CREATE TABLE Ebook (ebook_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name VARCHAR(255) UNIQUE);

-- tags table
CREATE TABLE Tags (tag_id INTEGER PRIMARY KEY AUTOINCREMENT,
                   name VARCHAR(255) UNIQUE);

-- adjacency table
PRAGMA foreign_keys = ON;
CREATE TABLE TagsToEbooks (ebook_id INT,
                           tag_id INT,
                           PRIMARY KEY (ebook_id, tag_id),
                           FOREIGN KEY (ebook_id) REFERENCES Ebook (ebook_id) 
                               ON DELETE CASCADE ON UPDATE CASCADE,
                           FOREIGN KEY (tag_id) REFERENCES Tags (tag_id)
                               ON DELETE CASCADE ON UPDATE CASCADE);

-- Trigger which will delete unused tags after book deletion
CREATE TRIGGER delete_unused_tags
AFTER DELETE ON Ebook
BEGIN
    DELETE 
    FROM Tags 
    WHERE NOT EXISTS ( SELECT NULL
                       FROM TagsToEbooks
                       WHERE Tags.tag_id = TagsToEbooks.tag_id);
END;

DEMO fiddle