Fix: Make tags unique (empties product tags)

2 files changed, 21 insertions, 1 deletions

diff --git a/migrations/20241124111952_uniquetags.sql b/migrations/20241124111952_uniquetags.sql
new file mode 100644
index 0000000..ff8b6ef
--- /dev/null
+++ b/migrations/20241124111952_uniquetags.sql
@@ -0,0 +1,19 @@
+DELETE FROM producttag;
+CREATE TABLE IF NOT EXISTS newtag
+(
+    id   INTEGER PRIMARY KEY NOT NULL,
+    name TEXT                NOT NULL UNIQUE
+);
+
+WITH uniquetags AS (
+    SELECT id, name, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS row_num
+    FROM tag
+)
+
+INSERT INTO newtag (id, name)
+SELECT id, name
+FROM uniquetags
+WHERE row_num = 1;
+
+DROP TABLE tag;
+ALTER TABLE newtag RENAME TO tag;
diff --git a/src/db/product.rs b/src/db/product.rs
index 1c8be1f..2fd2f54 100644
--- a/src/db/product.rs
+++ b/src/db/product.rs
@@ -31,13 +31,14 @@ impl Product {
             .await?;
 
         if !tags.is_empty() {
-            let mut tags_query_builder: QueryBuilder<Sqlite> = QueryBuilder::new("INSERT INTO tag (name) ");
+            let mut tags_query_builder: QueryBuilder<Sqlite> = QueryBuilder::new("INSERT OR REPLACE INTO tag (name) ");
             tags_query_builder.push_values(tags.iter(), | mut b, tag | { b.push_bind(tag); });
             tags_query_builder.push(" RETURNING id, name;");
             let added_tags: Vec<Tag> = tags_query_builder
                 .build_query_as::<Tag>()
                 .fetch_all(&mut **tx)
                 .await?;
+            println!("{:?}", added_tags);
 
             let mut producttags_query_builder: QueryBuilder<Sqlite> = QueryBuilder::new("INSERT INTO producttag (pid, tid) ");
             producttags_query_builder.push_values(added_tags.iter(), | mut b, added_tag | {