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

[Raziyah00]

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)

[Oba22]

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;

https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=3240437678fe642477e75f07a30affb6