Use unique indexes and upserts?

[KAction]

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]

If I'm not mistaken, it seems there's already a unique index on value(name):

TMSU/storage/database/schema.go

Line 186 in 0bf4b80

CONSTRAINT con_value_name UNIQUE (name)

However, a unique index on tag(name) would be very useful IMHO too.