From 6722589b515ccee267212cf783fc0ebbaa20cd90 Mon Sep 17 00:00:00 2001 From: Martin Kagamino Lehoux Date: Mon, 27 Jul 2026 17:18:40 +0200 Subject: feat: catalog section + deduplicate items by name - Show all items split into List (unchecked) and Catalog (checked) sections - Tapping a catalog item unchecks it (returns to list) - Add action now upserts: unchecks existing item instead of creating duplicate - UNIQUE constraint on groceries.name as defense-in-depth - Tests for dedup behavior (insert, re-add from catalog, no-op on list) --- drizzle/0001_dear_bastion.sql | 1 + drizzle/meta/0001_snapshot.json | 63 ++++++++++++++++++++++++++++++++ drizzle/meta/_journal.json | 7 ++++ src/lib/server/db/groceries.test.ts | 73 +++++++++++++++++++++++++++++++++++++ src/lib/server/db/schema.ts | 2 +- src/routes/+page.server.ts | 22 ++++++++--- src/routes/+page.svelte | 35 +++++++++++++++++- 7 files changed, 196 insertions(+), 7 deletions(-) create mode 100644 drizzle/0001_dear_bastion.sql create mode 100644 drizzle/meta/0001_snapshot.json create mode 100644 src/lib/server/db/groceries.test.ts diff --git a/drizzle/0001_dear_bastion.sql b/drizzle/0001_dear_bastion.sql new file mode 100644 index 0000000..ecc896d --- /dev/null +++ b/drizzle/0001_dear_bastion.sql @@ -0,0 +1 @@ +CREATE UNIQUE INDEX `groceries_name_unique` ON `groceries` (`name`); \ No newline at end of file diff --git a/drizzle/meta/0001_snapshot.json b/drizzle/meta/0001_snapshot.json new file mode 100644 index 0000000..92fe548 --- /dev/null +++ b/drizzle/meta/0001_snapshot.json @@ -0,0 +1,63 @@ +{ + "version": "6", + "dialect": "sqlite", + "id": "b20afe62-9a50-42b6-bb34-1be35863e817", + "prevId": "265bc06f-5de9-43a2-9427-39ce061f134c", + "tables": { + "groceries": { + "name": "groceries", + "columns": { + "id": { + "name": "id", + "type": "integer", + "primaryKey": true, + "notNull": true, + "autoincrement": true + }, + "name": { + "name": "name", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "checked": { + "name": "checked", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": false + }, + "created_at": { + "name": "created_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + } + }, + "indexes": { + "groceries_name_unique": { + "name": "groceries_name_unique", + "columns": ["name"], + "isUnique": true + } + }, + "foreignKeys": {}, + "compositePrimaryKeys": {}, + "uniqueConstraints": {}, + "checkConstraints": {} + } + }, + "views": {}, + "enums": {}, + "_meta": { + "schemas": {}, + "tables": {}, + "columns": {} + }, + "internal": { + "indexes": {} + } +} diff --git a/drizzle/meta/_journal.json b/drizzle/meta/_journal.json index 77a9b2e..7e6fb0d 100644 --- a/drizzle/meta/_journal.json +++ b/drizzle/meta/_journal.json @@ -8,6 +8,13 @@ "when": 1785163537728, "tag": "0000_known_scarecrow", "breakpoints": true + }, + { + "idx": 1, + "version": "6", + "when": 1785165400399, + "tag": "0001_dear_bastion", + "breakpoints": true } ] } diff --git a/src/lib/server/db/groceries.test.ts b/src/lib/server/db/groceries.test.ts new file mode 100644 index 0000000..b4f5050 --- /dev/null +++ b/src/lib/server/db/groceries.test.ts @@ -0,0 +1,73 @@ +import { describe, it, expect, beforeEach } from 'vitest'; +import { drizzle } from 'drizzle-orm/libsql'; +import { createClient } from '@libsql/client'; +import { groceries } from '$lib/server/db/schema'; +import { eq } from 'drizzle-orm'; + +describe('groceries add', () => { + const client = createClient({ url: ':memory:' }); + const db = drizzle(client, { schema: { groceries } }); + + beforeEach(async () => { + await db.run(`DROP TABLE IF EXISTS groceries`); + await db.run(`CREATE TABLE groceries ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + name TEXT NOT NULL, + checked INTEGER NOT NULL DEFAULT 0, + created_at TEXT NOT NULL + )`); + }); + + async function addItem(name: string) { + const existing = await db + .select() + .from(groceries) + .where(eq(groceries.name, name)) + .limit(1); + + if (existing.length > 0) { + await db + .update(groceries) + .set({ checked: false }) + .where(eq(groceries.id, existing[0].id)); + } else { + await db.insert(groceries).values({ + name, + createdAt: new Date().toISOString(), + }); + } + } + + it('creates a new item when name is new', async () => { + await addItem('milk'); + + const items = await db.select().from(groceries); + expect(items).toHaveLength(1); + expect(items[0].name).toBe('milk'); + expect(items[0].checked).toBe(false); + }); + + it('unchecks an existing checked item instead of creating duplicate', async () => { + const now = new Date().toISOString(); + await db + .insert(groceries) + .values({ name: 'milk', checked: true, createdAt: now }); + await addItem('milk'); + + const items = await db.select().from(groceries); + expect(items).toHaveLength(1); + expect(items[0].checked).toBe(false); + }); + + it('keeps an unchecked item unchanged (already on list)', async () => { + const now = new Date().toISOString(); + await db + .insert(groceries) + .values({ name: 'milk', checked: false, createdAt: now }); + await addItem('milk'); + + const items = await db.select().from(groceries); + expect(items).toHaveLength(1); + expect(items[0].checked).toBe(false); + }); +}); diff --git a/src/lib/server/db/schema.ts b/src/lib/server/db/schema.ts index a8527fd..403ef51 100644 --- a/src/lib/server/db/schema.ts +++ b/src/lib/server/db/schema.ts @@ -2,7 +2,7 @@ import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core'; export const groceries = sqliteTable('groceries', { id: integer('id').primaryKey({ autoIncrement: true }), - name: text('name').notNull(), + name: text('name').notNull().unique(), checked: integer('checked', { mode: 'boolean' }).notNull().default(false), createdAt: text('created_at').notNull(), }); diff --git a/src/routes/+page.server.ts b/src/routes/+page.server.ts index e6f3b0f..947cc3b 100644 --- a/src/routes/+page.server.ts +++ b/src/routes/+page.server.ts @@ -8,7 +8,6 @@ export const load: PageServerLoad = async () => { const items = await db .select() .from(groceries) - .where(eq(groceries.checked, false)) .orderBy(desc(groceries.createdAt)); return { items }; @@ -23,10 +22,23 @@ export const actions: Actions = { return fail(400, { error: 'Name is required' }); } - await db.insert(groceries).values({ - name, - createdAt: new Date().toISOString(), - }); + const [existing] = await db + .select() + .from(groceries) + .where(eq(groceries.name, name)) + .limit(1); + + if (existing) { + await db + .update(groceries) + .set({ checked: false }) + .where(eq(groceries.id, existing.id)); + } else { + await db.insert(groceries).values({ + name, + createdAt: new Date().toISOString(), + }); + } return { success: true }; }, diff --git a/src/routes/+page.svelte b/src/routes/+page.svelte index 0e9e1ac..325d15a 100644 --- a/src/routes/+page.svelte +++ b/src/routes/+page.svelte @@ -4,6 +4,9 @@ let { data, form } = $props(); let name = $state(''); + + let listItems = $derived(data?.items?.filter((i) => !i.checked) ?? []); + let catalogItems = $derived(data?.items?.filter((i) => i.checked) ?? []);

Groceries

@@ -23,11 +26,31 @@

{form.error}

{/if} +

List

+ +
+ +
+ +{#if listItems.length === 0} +

Nothing to buy

+{/if} +
+

Catalog

+
+{#if catalogItems.length === 0} +

No items yet

+{/if} + -- cgit v1.3