search

oniony / TMSU

TMSU lets you tags your files and then access them through a nifty virtual filesystem from any other application.
Other
2.03k stars 118 forks source link

Use unique indexes and upserts? #272

Open KAction opened 1 year ago

KAction commented 1 year ago

Hello.

Other day I was working on some script that would add/remove tags automatically based on complicated, non-general-utility logic, and I figured that it will be easier to work with database directly.

I really appreciate the choice of sqlite3 as data storage instead of some language-specific binary format. One .schema command, and I knew exactly what I need to do.

One thing that surprised me is that tmsu database has no unique indexes, so if I want know id of the tag, creating it if necessary, I have to do it using multiple queries:

SELECT id FROM tag WHERE name = ?
-- process in code 
INSERT INTO tag(name) VALUES (?) RETURNING id

Should table had unique index, the same could have been done atomically and much easier:

INSERT INTO tag(name) VALUES (?) ON CONFLICT (name) DO UPDATE SET name = name RETURNING id

Additionally, unique index would be extra protection against logic errors that may introduce duplicate tags/value/files. Would you be interested if I make pull request that creates unique indexes, like following:

drop index tag_name_idx on tag;
create unique index tag_name_idx on tag (name);

drop index value_name idx on value;
create unique index value_name_idx on value (name);
...
hydrargyrum commented 6 months ago

If I'm not mistaken, it seems there's already a unique index on value(name): https://github.com/oniony/TMSU/blob/0bf4b8031cbeffc0347007d85647062953e90571/storage/database/schema.go#L186 However, a unique index on tag(name) would be very useful IMHO too.