From 660d6e73218229c6ac08ac2b7a19819169529fc8 Mon Sep 17 00:00:00 2001
From: Michael McVady <femtonaut@gmail.com>
Date: Fri, 12 Jan 2024 16:02:15 -0600
Subject: Start implementing search

---
 README.md     | 3 ++-
 db/db.sql     | 9 ++++++---
 src/clog.c    | 4 ++--
 src/queries.h | 4 ++--
 4 files changed, 12 insertions(+), 8 deletions(-)

diff --git a/README.md b/README.md
index 876e5ff..944b645 100644
--- a/README.md
+++ b/README.md
@@ -49,8 +49,9 @@ Use `psql` and load `db/db.sql` for models,
 9. ~Refactor URLs and HTTP calls in tests `tests.py` to reduce copy-pasta~
 10. Get rid of `requests` dependency in `tests.py`
 11. Fix `PATCH` redirects?
-12. Search
+12. [Full Text Search](https://dev.to/nightbird07/full-text-search-in-postgresql-a-comprehensive-guide-3kcn)
 13. Add preview functionality to edit form
+14. Add support for creating new entries
 
 ## But using C in 2024 is dangerous?
 
diff --git a/db/db.sql b/db/db.sql
index ab0e7fd..5866021 100644
--- a/db/db.sql
+++ b/db/db.sql
@@ -1,14 +1,17 @@
-CREATE DATABASE clog;
+CREATE DATABASE IF NOT EXISTS clog;
 
 \connect clog
 
 CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
 
-CREATE TABLE posts (
+CREATE TABLE IF NOT EXISTS entries (
 	id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
 	-- draft BOOLEAN NOT NULL DEFAULT TRUE,
 	title CHARACTER VARYING NOT NULL,
 	body CHARACTER VARYING NOT NULL,
 	created_at TIMESTAMP NOT NULL DEFAULT NOW(),
-	updated_at TIMESTAMP NOT NULL DEFAULT NOW()
+	updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
+	search_vector TSVECTOR DEFAULT NULL
 );
+
+CREATE INDEX entries_search_vector_idx ON entries USING gin(search_vector);
\ No newline at end of file
diff --git a/src/clog.c b/src/clog.c
index 5f41ce5..e27db22 100644
--- a/src/clog.c
+++ b/src/clog.c
@@ -490,8 +490,8 @@ int sql_update(const char *id, const char *title, const char *body) {
 	}
 
 	err = kore_pgsql_query_params(
-		&sql, q_update_entry, 0, 3, KORE_PGSQL_PARAM_TEXT(title), KORE_PGSQL_PARAM_TEXT(body),
-		KORE_PGSQL_PARAM_TEXT(id)
+		&sql, q_update_entry, 0, 4, KORE_PGSQL_PARAM_TEXT(title), KORE_PGSQL_PARAM_TEXT(body),
+		KORE_PGSQL_PARAM_TEXT(body), KORE_PGSQL_PARAM_TEXT(id)
 	);
 	if (err == KORE_RESULT_ERROR) {
 		kore_pgsql_logerror(&sql);
diff --git a/src/queries.h b/src/queries.h
index 1246091..f406434 100644
--- a/src/queries.h
+++ b/src/queries.h
@@ -24,8 +24,8 @@ const char *q_select_entries =
 
 const char *q_update_entry =
 "UPDATE entries "
-"SET title = $1, body = $2, updated_at = NOW() "
-"WHERE id = $3 "
+"SET title = $1, body = $2, updated_at = NOW(), search_vector = TO_TSVECTOR('english', $3) "
+"WHERE id = $4 "
 "RETURNING id;";
 
 // const char *q_delete_entry =
-- 
cgit v1.2.3