Mercurial > paster

changeset 78:9bfe5ce3cc45

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
pasterd: rework themes
author David Demelier <markand@malikania.fr>
date Thu, 16 Mar 2023 20:45:59 +0100
parents fe78b16c694d
children 52029a52a385
files .hgignore GNUmakefile database.c database.h html/new.html html/paste.html json-util.c json-util.h page-download.c page-fork.c page-index.c page-index.h page-new.c page-new.h page-paste.c page-search.c paste.c paste.h pasterd.c sql/clear.sql sql/get.sql sql/init.sql sql/insert.sql sql/recents.sql sql/search.sql util.c util.h
diffstat 27 files changed, 581 insertions(+), 476 deletions(-) [+]
line wrap: on
line diff
--- a/.hgignore	Thu Mar 16 15:05:26 2023 +0100
+++ b/.hgignore	Thu Mar 16 20:45:59 2023 +0100
@@ -18,6 +18,7 @@
 ^config\.mk$
 ^extern/bcc/bcc$
 ^html/.*\.h$
+^sql/.*\.h$
 ^libpaster\.a$
 
 # Executables.
--- a/GNUmakefile	Thu Mar 16 15:05:26 2023 +0100
+++ b/GNUmakefile	Thu Mar 16 20:45:59 2023 +0100
@@ -58,7 +58,6 @@
 LIBPASTER_SRCS +=       page-search.c
 LIBPASTER_SRCS +=       page-static.c
 LIBPASTER_SRCS +=       page.c
-LIBPASTER_SRCS +=       paste.c
 LIBPASTER_SRCS +=       util.c
 LIBPASTER_OBJS :=       $(LIBPASTER_SRCS:.c=.o)
 LIBPASTER_DEPS :=       $(LIBPASTER_SRCS:.c=.d)
@@ -73,6 +72,14 @@
 LIBPASTER_HTML_SRCS +=  html/status.html
 LIBPASTER_HTML_OBJS :=  $(LIBPASTER_HTML_SRCS:.html=.h)
 
+LIBPASTER_SQL_SRCS :=   sql/clear.sql
+LIBPASTER_SQL_SRCS +=   sql/get.sql
+LIBPASTER_SQL_SRCS +=   sql/init.sql
+LIBPASTER_SQL_SRCS +=   sql/insert.sql
+LIBPASTER_SQL_SRCS +=   sql/recents.sql
+LIBPASTER_SQL_SRCS +=   sql/search.sql
+LIBPASTER_SQL_OBJS :=   $(LIBPASTER_SQL_SRCS:.sql=.h)
+
 TESTS_SRCS :=           tests/test-database.c
 TESTS_OBJS :=           $(TESTS_SRCS:.c=.o)
 TESTS :=                $(TESTS_SRCS:.c=)
@@ -103,6 +110,9 @@
 %.h: %.html
 	$(BCC) -cs0 $< html_${<F} > $@
 
+%.h: %.sql
+	$(BCC) -cs0 $< sql_${<F} > $@
+
 %: %.sh
 	$(SED) < $< > $@
 
@@ -110,7 +120,7 @@
 	$(AR) -rc $@ $^
 
 $(LIBPASTER_HTML_OBJS): extern/bcc/bcc
-$(LIBPASTER_SRCS): $(LIBPASTER_HTML_OBJS)
+$(LIBPASTER_SRCS): $(LIBPASTER_HTML_OBJS) $(LIBPASTER_SQL_OBJS)
 $(LIBPASTER): $(LIBPASTER_OBJS)
 
 pasterd: private LDLIBS += $(KCGI_LIBS) $(JANSSON_LIBS)
@@ -118,7 +128,7 @@
 
 clean:
 	rm -f extern/bcc/bcc extern/bcc/bcc.d
-	rm -f $(LIBPASTER) $(LIBPASTER_OBJS) $(LIBPASTER_DEPS) $(LIBPASTER_HTML_OBJS)
+	rm -f $(LIBPASTER) $(LIBPASTER_OBJS) $(LIBPASTER_DEPS) $(LIBPASTER_HTML_OBJS) $(LIBPASTER_SQL_OBJS)
 	rm -f paster pasterd pasterd.d
 	rm -f test.db $(TESTS_OBJS)
 
--- a/database.c	Thu Mar 16 15:05:26 2023 +0100
+++ b/database.c	Thu Mar 16 20:45:59 2023 +0100
@@ -24,118 +24,43 @@
 #include <sqlite3.h>
 
 #include "database.h"
+#include "json-util.h"
 #include "log.h"
-#include "paste.h"
 #include "util.h"
 
+#include "sql/clear.h"
+#include "sql/get.h"
+#include "sql/init.h"
+#include "sql/insert.h"
+#include "sql/recents.h"
+#include "sql/search.h"
+
+#define ID_MAX (12 + 1)
+
 static sqlite3 *db;
 
-static const char *sql_init =
-	"BEGIN EXCLUSIVE TRANSACTION;\n"
-	"\n"
-	"CREATE TABLE IF NOT EXISTS paste(\n"
-	"  uuid TEXT PRIMARY KEY,\n"
-	"  title TEXT,\n"
-	"  author TEXT,\n"
-	"  language TEXT,\n"
-	"  code TEXT,\n"
-	"  date INT DEFAULT CURRENT_TIMESTAMP,\n"
-	"  visible INTEGER DEFAULT 0,\n"
-	"  duration INT\n"
-	");\n"
-	"\n"
-	"END TRANSACTION";
-
-static const char *sql_get =
-	"SELECT uuid\n"
-	"     , title\n"
-	"     , author\n"
-	"     , language\n"
-	"     , code\n"
-	"     , strftime('%s', date)\n"
-	"     , visible\n"
-	"     , duration\n"
-	"  FROM paste\n"
-	" WHERE uuid = ?";
-
-static const char *sql_insert =
-	"INSERT INTO paste(\n"
-	"  uuid,\n"
-	"  title,\n"
-	"  author,\n"
-	"  language,\n"
-	"  code,\n"
-	"  visible,\n"
-	"  duration\n"
-	") VALUES (?, ?, ?, ?, ?, ?, ?)";
-
-static const char *sql_recents =
-	"SELECT uuid\n"
-	"     , title\n"
-	"     , author\n"
-	"     , language\n"
-	"     , code\n"
-	"     , strftime('%s', date) AS date\n"
-	"     , visible\n"
-	"     , duration\n"
-	"  FROM paste\n"
-	" WHERE visible = 1\n"
-	" ORDER BY date DESC\n"
-	" LIMIT ?\n";
-
-static const char *sql_clear =
-	"BEGIN EXCLUSIVE TRANSACTION;\n"
-	"\n"
-	"DELETE\n"
-	"  FROM paste\n"
-	" WHERE strftime('%s', 'now') - strftime('%s', date) >= duration;"
-	"\n"
-	"END TRANSACTION";
-
-static const char *sql_search =
-	"SELECT uuid\n"
-	"     , title\n"
-	"     , author\n"
-	"     , language\n"
-	"     , code\n"
-	"     , strftime('%s', date) AS date\n"
-	"     , visible\n"
-	"     , duration\n"
-	"  FROM paste\n"
-	" WHERE title like ?\n"
-	"   AND author like ?\n"
-	"   AND language like ?\n"
-	"   AND visible = 1\n"
-	" ORDER BY date DESC\n"
-	" LIMIT ?\n";
-
-/* sqlite3 use const unsigned char *. */
-static char *
-dup(const unsigned char *s)
+static inline json_t *
+convert(sqlite3_stmt *stmt)
 {
-	return estrdup(s ? (const char *)(s) : "");
+	return json_pack("{ss ss ss ss ss sI si si}",
+		"id",           sqlite3_column_text(stmt, 0),
+		"title",        sqlite3_column_text(stmt, 1),
+		"author",       sqlite3_column_text(stmt, 2),
+		"language",     sqlite3_column_text(stmt, 3),
+		"code",         sqlite3_column_text(stmt, 4),
+		"timestamp",    (json_int_t)sqlite3_column_int64(stmt, 5),
+		"visible",      sqlite3_column_int(stmt, 6),
+		"duration",     sqlite3_column_int(stmt, 7)
+	);
 }
 
-static void
-convert(sqlite3_stmt *stmt, struct paste *paste)
-{
-	paste->id = dup(sqlite3_column_text(stmt, 0));
-	paste->title = dup(sqlite3_column_text(stmt, 1));
-	paste->author = dup(sqlite3_column_text(stmt, 2));
-	paste->language = dup(sqlite3_column_text(stmt, 3));
-	paste->code = dup(sqlite3_column_text(stmt, 4));
-	paste->timestamp = sqlite3_column_int64(stmt, 5);
-	paste->visible = sqlite3_column_int(stmt, 6);
-	paste->duration = sqlite3_column_int64(stmt, 7);
-}
-
-static bool
+static int
 exists(const char *id)
 {
 	assert(id);
 
 	sqlite3_stmt *stmt = NULL;
-	bool ret = true;
+	int ret = 1;
 
 	if (sqlite3_prepare(db, sql_get, -1, &stmt, NULL) == SQLITE_OK) {
 		sqlite3_bind_text(stmt, 1, id, -1, NULL);
@@ -147,24 +72,19 @@
 }
 
 static const char *
-create_id(void)
+create_id(char *id)
 {
 	static const char table[] = "abcdefghijklmnopqrstuvwxyz1234567890";
-	static char id[12];
 
-	for (int i = 0; i < sizeof (id); ++i)
+	for (int i = 0; i < ID_MAX; ++i)
 		id[i] = table[rand() % (sizeof (table) - 1)];
 
-	return id;
+	id[ID_MAX - 1] = 0;
 }
 
-static bool
-set_id(struct paste *paste)
+static int
+set_id(json_t *paste)
 {
-	assert(paste);
-
-	paste->id = NULL;
-
 	/*
 	 * Avoid infinite loop, we only try to create a new id in 30 steps.
 	 *
@@ -172,16 +92,21 @@
 	 * not try to save with that id.
 	 */
 	int tries = 0;
+	char id[ID_MAX];
 
 	do {
-		free(paste->id);
-		paste->id = estrdup(create_id());
-	} while (++tries < 30 && exists(paste->id));
+		create_id(id);
+	} while (++tries < 30 && exists(id));
 
-	return tries < 30;
+	if (tries >= 30)
+		return -1;
+
+	json_object_set_new(paste, "id", json_string(id));
+
+	return 0;
 }
 
-bool
+int
 database_open(const char *path)
 {
 	assert(path);
@@ -190,7 +115,7 @@
 
 	if (sqlite3_open(path, &db) != SQLITE_OK) {
 		log_warn("database: unable to open %s: %s", path, sqlite3_errmsg(db));
-		return false;
+		return -1;
 	}
 
 	/* Wait for 30 seconds to lock the database. */
@@ -198,37 +123,34 @@
 
 	if (sqlite3_exec(db, sql_init, NULL, NULL, NULL) != SQLITE_OK) {
 		log_warn("database: unable to initialize %s: %s", path, sqlite3_errmsg(db));
-		return false;
+		return -1;
 	}
 
-	return true;
+	return 0;
 }
 
-bool
-database_recents(struct paste *pastes, size_t *max)
+json_t *
+database_recents(size_t limit)
 {
-	assert(pastes);
-	assert(max);
+	json_t *array = NULL;
+	sqlite3_stmt *stmt = NULL;
+	size_t i = 0;
 
-	sqlite3_stmt *stmt = NULL;
-
-	memset(pastes, 0, *max * sizeof (struct paste));
 	log_debug("database: accessing most recents");
 
 	if (sqlite3_prepare(db, sql_recents, -1, &stmt, NULL) != SQLITE_OK ||
-	    sqlite3_bind_int64(stmt, 1, *max) != SQLITE_OK)
+	    sqlite3_bind_int64(stmt, 1, limit) != SQLITE_OK)
 		goto sqlite_err;
 
-	size_t i = 0;
+	array = json_array();
 
-	for (; i < *max && sqlite3_step(stmt) == SQLITE_ROW; ++i)
-		convert(stmt, &pastes[i]);
+	for (; i < limit && sqlite3_step(stmt) == SQLITE_ROW; ++i)
+		json_array_append_new(array, convert(stmt));
 
 	log_debug("database: found %zu pastes", i);
 	sqlite3_finalize(stmt);
-	*max = i;
 
-	return true;
+	return array;
 
 sqlite_err:
 	log_warn("database: error (recents): %s\n", sqlite3_errmsg(db));
@@ -236,29 +158,26 @@
 	if (stmt)
 		sqlite3_finalize(stmt);
 
-	return (*max = 0);
+	return NULL;
 }
 
-bool
-database_get(struct paste *paste, const char *uuid)
+json_t *
+database_get(const char *id)
 {
-	assert(paste);
-	assert(uuid);
+	assert(id);
 
+	json_t *object = NULL;
 	sqlite3_stmt* stmt = NULL;
-	bool found = false;
 
-	memset(paste, 0, sizeof (struct paste));
-	log_debug("database: accessing paste with uuid: %s", uuid);
+	log_debug("database: accessing paste with uuid: %s", id);
 
 	if (sqlite3_prepare(db, sql_get, -1, &stmt, NULL) != SQLITE_OK ||
-	    sqlite3_bind_text(stmt, 1, uuid, -1, NULL) != SQLITE_OK)
+	    sqlite3_bind_text(stmt, 1, id, -1, NULL) != SQLITE_OK)
 		goto sqlite_err;
 
 	switch (sqlite3_step(stmt)) {
 	case SQLITE_ROW:
-		convert(stmt, paste);
-		found = true;
+		object = convert(stmt);
 		break;
 	case SQLITE_MISUSE:
 	case SQLITE_ERROR:
@@ -269,7 +188,7 @@
 
 	sqlite3_finalize(stmt);
 
-	return found;
+	return object;
 
 sqlite_err:
 	if (stmt)
@@ -277,11 +196,11 @@
 
 	log_warn("database: error (get): %s", sqlite3_errmsg(db));
 
-	return false;
+	return NULL;
 }
 
-bool
-database_insert(struct paste *paste)
+int
+database_insert(json_t *paste)
 {
 	assert(paste);
 
@@ -291,25 +210,25 @@
 
 	if (sqlite3_exec(db, "BEGIN EXCLUSIVE TRANSACTION", NULL, NULL, NULL) != SQLITE_OK) {
 		log_warn("database: could not lock database: %s", sqlite3_errmsg(db));
-		return false;
+		return -1;
 	}
 
-	if (!set_id(paste)) {
+	if (set_id(paste) < 0) {
 		log_warn("database: unable to randomize unique identifier");
 		sqlite3_exec(db, "END TRANSACTION", NULL, NULL, NULL);
-		return false;
+		return -1;
 	}
 
 	if (sqlite3_prepare(db, sql_insert, -1, &stmt, NULL) != SQLITE_OK)
 		goto sqlite_err;
 
-	sqlite3_bind_text(stmt, 1, paste->id, -1, SQLITE_STATIC);
-	sqlite3_bind_text(stmt, 2, paste->title, -1, SQLITE_STATIC);
-	sqlite3_bind_text(stmt, 3, paste->author, -1, SQLITE_STATIC);
-	sqlite3_bind_text(stmt, 4, paste->language, -1, SQLITE_STATIC);
-	sqlite3_bind_text(stmt, 5, paste->code, -1, SQLITE_STATIC);
-	sqlite3_bind_int(stmt, 6, paste->visible);
-	sqlite3_bind_int64(stmt, 7, paste->duration);
+	sqlite3_bind_text(stmt, 1, ju_get_string(paste, "id"), -1, SQLITE_STATIC);
+	sqlite3_bind_text(stmt, 2, ju_get_string(paste, "title"), -1, SQLITE_STATIC);
+	sqlite3_bind_text(stmt, 3, ju_get_string(paste, "author"), -1, SQLITE_STATIC);
+	sqlite3_bind_text(stmt, 4, ju_get_string(paste, "language"), -1, SQLITE_STATIC);
+	sqlite3_bind_text(stmt, 5, ju_get_string(paste, "code"), -1, SQLITE_STATIC);
+	sqlite3_bind_int(stmt, 6, ju_get_bool(paste, "visible"));
+	sqlite3_bind_int64(stmt, 7, ju_get_int(paste, "duration"));
 
 	if (sqlite3_step(stmt) != SQLITE_DONE)
 		goto sqlite_err;
@@ -318,9 +237,12 @@
 	sqlite3_finalize(stmt);
 
 	log_info("database: new paste (%s) from %s expires in one %lld seconds",
-	    paste->id, paste->author, paste->duration);
+	    ju_get_string(paste, "id"),
+	    ju_get_string(paste, "author"),
+	    ju_get_int(paste, "duration")
+	);
 
-	return true;
+	return 0;
 
 sqlite_err:
 	log_warn("database: error (insert): %s", sqlite3_errmsg(db));
@@ -329,31 +251,27 @@
 	if (stmt)
 		sqlite3_finalize(stmt);
 
-	free(paste->id);
-	paste->id = NULL;
+	/* Make sure it is not used anymore. */
+	json_object_del(paste, "id");
 
-	return false;
+	return 0;
 }
 
-bool
-database_search(struct paste *pastes,
-                size_t *max,
+json_t *
+database_search(size_t limit,
                 const char *title,
                 const char *author,
                 const char *language)
 {
-	assert(pastes);
-	assert(max);
-
+	json_t *array = NULL;
 	sqlite3_stmt *stmt = NULL;
+	size_t i = 0;
 
 	log_debug("database: searching title=%s, author=%s, language=%s",
 	    title    ? title    : "",
 	    author   ? author   : "",
 	    language ? language : "");
 
-	memset(pastes, 0, *max * sizeof (struct paste));
-
 	/* Select everything if not specified. */
 	title    = title    ? title    : "%";
 	author   = author   ? author   : "%";
@@ -367,19 +285,18 @@
 		goto sqlite_err;
 	if (sqlite3_bind_text(stmt, 3, language, -1, NULL) != SQLITE_OK)
 		goto sqlite_err;
-	if (sqlite3_bind_int64(stmt, 4, *max) != SQLITE_OK)
+	if (sqlite3_bind_int64(stmt, 4, limit) != SQLITE_OK)
 		goto sqlite_err;
 
-	size_t i = 0;
+	array = json_array();
 
-	for (; i < *max && sqlite3_step(stmt) == SQLITE_ROW; ++i)
-		convert(stmt, &pastes[i]);
+	for (; i < limit && sqlite3_step(stmt) == SQLITE_ROW; ++i)
+		json_array_append_new(array, convert(stmt));
 
 	log_debug("database: found %zu pastes", i);
 	sqlite3_finalize(stmt);
-	*max = i;
 
-	return true;
+	return array;
 
 sqlite_err:
 	log_warn("database: error (search): %s\n", sqlite3_errmsg(db));
@@ -387,7 +304,7 @@
 	if (stmt)
 		sqlite3_finalize(stmt);
 
-	return (*max = 0);
+	return NULL;
 }
 
 void
--- a/database.h	Thu Mar 16 15:05:26 2023 +0100
+++ b/database.h	Thu Mar 16 20:45:59 2023 +0100
@@ -19,33 +19,79 @@
 #ifndef PASTER_DATABASE_H
 #define PASTER_DATABASE_H
 
-#include <stdbool.h>
 #include <stddef.h>
 
-struct paste;
+#include <jansson.h>
 
-bool
-database_open(const char *);
+/**
+ * Open the database specified by path.
+ *
+ * \pre path != NULL
+ * \param path path to the SQLite file
+ * \return 0 on success or -1 on error
+ */
+int
+database_open(const char *path);
 
-bool
-database_recents(struct paste *, size_t *);
+/**
+ * Obtain a list of recent pastes.
+ *
+ * \param limit max number of items to fetch
+ * \return a JSON array of paste objects or NULL on failure
+ */
+json_t *
+database_recents(size_t limit);
 
-bool
-database_get(struct paste *, const char *);
+/**
+ * Obtain a specific paste by id.
+ *
+ * \pre id != NULL
+ * \param id the paste identifier
+ * \return NULL on failure or if not found
+ */
+json_t *
+database_get(const char *id);
 
-bool
-database_insert(struct paste *);
+/**
+ * Insert a new paste into the database.
+ *
+ * On insertion, the paste gets a new string "id" property generated from the
+ * database.
+ *
+ * \pre paste != NULL
+ * \param paste the paste object
+ * \return 0 on success or -1 on failure
+ */
+int
+database_insert(json_t *paste);
 
-bool
-database_search(struct paste *,
-                size_t *,
-                const char *,
-                const char *,
-                const char *);
+/**
+ * Search for pastes based on criterias.
+ *
+ * If any of the criterias is NULL it is considered as ignored (and then match a
+ * database item).
+ *
+ * \param limit max number of items to fetch
+ * \param title paste title (or NULL to match any)
+ * \param author paste author (or NULL to match any)
+ * \param language paste language (or NULL to match any)
+ * \return a JSON array of objects or NULL on failure
+ */
+json_t *
+database_search(size_t limit,
+                const char *title,
+                const char *author,
+                const char *language);
 
+/**
+ * Cleanup expired pastes.
+ */
 void
 database_clear(void);
 
+/**
+ * Close the database handle
+ */
 void
 database_finish(void);
 
--- a/html/new.html	Thu Mar 16 15:05:26 2023 +0100
+++ b/html/new.html	Thu Mar 16 20:45:59 2023 +0100
@@ -9,7 +9,7 @@
 
 					<tr>
 						<td class="label">Author</td>
-						<td><input name="author" type="text" placeholder="Anonymous" /></td>
+						<td><input name="author" type="text" placeholder="Anonymous" value="{{author}}" /></td>
 					</tr>
 
 					<tr>
@@ -35,8 +35,8 @@
 					</tr>
 
 					<tr>
-						<td class="label">Private</td>
-						<td><input type="checkbox" name="private" checked></input></td>
+						<td class="label">Public</td>
+						<td><input type="checkbox" name="visible"></input></td>
 					</tr>
 				</table>
 
--- a/html/paste.html	Thu Mar 16 15:05:26 2023 +0100
+++ b/html/paste.html	Thu Mar 16 20:45:59 2023 +0100
@@ -33,7 +33,7 @@
 			</tr>
 			<tr>
 				<td class="label">Expires in</td>
-				<td>{{expiration}}</td>
+				<td>{{expires}}</td>
 			</tr>
 		</tbody>
 	</table>
--- a/json-util.c	Thu Mar 16 15:05:26 2023 +0100
+++ b/json-util.c	Thu Mar 16 20:45:59 2023 +0100
@@ -21,7 +21,6 @@
 
 #include "json-util.h"
 #include "util.h"
-#include "paste.h"
 
 json_t *
 ju_languages(const char *selected)
@@ -59,17 +58,84 @@
 }
 
 json_t *
-ju_date(const struct paste *paste)
+ju_date(time_t timestamp)
 {
-	assert(paste);
-
-	return json_string(bstrftime("%c", localtime(&paste->timestamp)));
+	return json_string(bstrftime("%c", localtime(&timestamp)));
 }
 
 json_t *
-ju_expiration(const struct paste *paste)
+ju_expires(time_t timestamp, int duration)
+{
+	return json_string(ttl(timestamp, duration));
+}
+
+const char *
+ju_get_string(const json_t *doc, const char *key)
+{
+	const json_t *val;
+
+	if (!doc || !(val = json_object_get(doc, key)) || !json_is_string(val))
+		return NULL;
+
+	return json_string_value(val);
+}
+
+intmax_t
+ju_get_int(const json_t *doc, const char *key)
+{
+	const json_t *val;
+
+	if (!doc || !(val = json_object_get(doc, key)) || !json_is_integer(val))
+		return 0;
+
+	return json_integer_value(val);
+}
+
+int
+ju_get_bool(const json_t *doc, const char *key)
 {
-	assert(paste);
+	const json_t *val;
+
+	if (!doc || !(val = json_object_get(doc, key)) || !json_is_boolean(val))
+		return 0;
+
+	return json_boolean_value(val);
+}
+
+json_t *
+ju_paste_new(void)
+{
+	return json_pack("{ss ss ss ss si si}",
+		"title",        "Untitled",
+		"author",       "Anonymous",
+		"language",     "nohighlight",
+		"code",         "The best code is no code",
+		"visible",      0,
+		"duration",     PASTE_DURATION_HOUR
+	);
+}
 
-	return json_string(ttl(paste->timestamp, paste->duration));
+json_t *
+ju_extend(json_t *doc, const char *fmt, ...)
+{
+	assert(fmt);
+
+	json_t *ret, *val;
+	json_error_t err;
+	va_list ap;
+	const char *key;
+
+	va_start(ap, fmt);
+	ret = json_vpack_ex(&err, 0, fmt, ap);
+	va_end(ap);
+
+	/* Now steal every nodes from doc and put them in ret. */
+	if (doc) {
+		json_object_foreach(doc, key, val)
+			json_object_set(ret, key, val);
+
+		json_decref(doc);
+	}
+
+	return ret;
 }
--- a/json-util.h	Thu Mar 16 15:05:26 2023 +0100
+++ b/json-util.h	Thu Mar 16 20:45:59 2023 +0100
@@ -19,9 +19,10 @@
 #ifndef PASTER_PASTER_JSON_UTIL_H
 #define PASTER_PASTER_JSON_UTIL_H
 
-#include <jansson.h>
+#include <stdint.h>
+#include <time.h>
 
-struct paste;
+#include <jansson.h>
 
 /**
  * Create an array of all possible languages supported by the application. If
@@ -73,26 +74,40 @@
 /**
  * Create a convenient ISO date string containing the paste creation date.
  *
- * \pre paste != NULL
- * \param paste this paste
+ * \param timestamp the timestamp
  * \return a string with an ISO date
  */
 json_t *
-ju_date(const struct paste *paste);
+ju_date(time_t timestamp);
 
 /**
- * Create a convenient remaining time for the given paste.
+ * Create a convenient remaining time for the given timestamp/duration.
  *
  * Returns strings in the form:
  *
  * - `2 day(s)`
  * - `3 hours(s)`
  *
- * \pre paste != NULL
- * \param paste this paste
+ * \param timestamp the timestamp
+ * \param duration the duration in seconds (e.g. 3600)
  * \return a string containing the expiration time
  */
 json_t *
-ju_expiration(const struct paste *paste);
+ju_expires(time_t timestamp, int duration);
+
+const char *
+ju_get_string(const json_t *, const char *);
+
+intmax_t
+ju_get_int(const json_t *, const char *);
+
+int
+ju_get_bool(const json_t *, const char *);
+
+json_t *
+ju_paste_new(void);
+
+json_t *
+ju_extend(json_t *doc, const char *fmt, ...);
 
 #endif /* !PASTER_PASTER_JSON_UTIL_H */
--- a/page-download.c	Thu Mar 16 15:05:26 2023 +0100
+++ b/page-download.c	Thu Mar 16 20:45:59 2023 +0100
@@ -2,11 +2,11 @@
  * page-download.c -- page /download/<id>
  *
  * Copyright (c) 2020-2023 David Demelier <markand@malikania.fr>
- * 
+ *
  * Permission to use, copy, modify, and/or distribute this software for any
  * purpose with or without fee is hereby granted, provided that the above
  * copyright notice and this permission notice appear in all copies.
- * 
+ *
  * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
  * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
  * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
@@ -16,49 +16,48 @@
  * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
  */
 
-#include <sys/types.h>
 #include <assert.h>
-#include <stdarg.h>
-#include <stdint.h>
-
-#include <kcgi.h>
 
 #include "database.h"
 #include "page.h"
-#include "paste.h"
+#include "json-util.h"
 
 static void
-get(struct kreq *r)
+get(struct kreq *req)
 {
-	struct paste paste = {0};
+	json_t *paste;
 
-	if (!database_get(&paste, r->path))
-		page_status(r, KHTTP_404);
+	if (!(paste = database_get(req->path)))
+		page_status(req, KHTTP_404);
 	else {
-		khttp_head(r, kresps[KRESP_CONTENT_TYPE], "%s", kmimetypes[KMIME_APP_OCTET_STREAM]);
+		khttp_head(req, kresps[KRESP_CONTENT_TYPE], "%s", kmimetypes[KMIME_APP_OCTET_STREAM]);
 #if 0
 		/* TODO: this seems to generated truncated files. */
-		khttp_head(r, kresps[KRESP_CONTENT_LENGTH], "%zu", strlen(paste.code));
+		khttp_head(req, kresps[KRESP_CONTENT_LENGTH], "%zu", strlen(paste.code));
 #endif
-		khttp_head(r, kresps[KRESP_CONNECTION], "keep-alive");
-		khttp_head(r, kresps[KRESP_CONTENT_DISPOSITION],
-		    "attachment; filename=\"%s.%s\"", paste.id, paste.language);
-		khttp_body(r);
-		khttp_puts(r, paste.code);
-		khttp_free(r);
-		paste_finish(&paste);
+		khttp_head(req, kresps[KRESP_CONNECTION], "keep-alive");
+		khttp_head(req, kresps[KRESP_CONTENT_DISPOSITION], "attachment; filename=\"%s.%s\"",
+		    ju_get_string(paste, "id"),
+		    ju_get_string(paste, "language")
+		);
+		khttp_body(req);
+		khttp_puts(req, ju_get_string(paste, "code"));
+		khttp_free(req);
+		json_decref(paste);
 	}
 }
 
 void
-page_download(struct kreq *r)
+page_download(struct kreq *req)
 {
-	switch (r->method) {
+	assert(req);
+
+	switch (req->method) {
 	case KMETHOD_GET:
-		get(r);
+		get(req);
 		break;
 	default:
-		page_status(r, KHTTP_400);
+		page_status(req, KHTTP_400);
 		break;
 	}
 }
--- a/page-fork.c	Thu Mar 16 15:05:26 2023 +0100
+++ b/page-fork.c	Thu Mar 16 20:45:59 2023 +0100
@@ -19,21 +19,19 @@
 #include <assert.h>
 
 #include "database.h"
+#include "json-util.h"
 #include "page-new.h"
 #include "page.h"
-#include "paste.h"
 
 static void
 get(struct kreq *req)
 {
-	struct paste paste = {0};
+	json_t *paste;
 
-	if (!database_get(&paste, req->path))
+	if (!(paste = database_get(req->path)))
 		page_status(req, KHTTP_404);
-	else {
-		page_new_render(req, &paste);
-		paste_finish(&paste);
-	}
+	else
+		page_new_render(req, paste);
 }
 
 void
--- a/page-index.c	Thu Mar 16 15:05:26 2023 +0100
+++ b/page-index.c	Thu Mar 16 20:45:59 2023 +0100
@@ -22,64 +22,34 @@
 #include "json-util.h"
 #include "page-index.h"
 #include "page.h"
-#include "paste.h"
 #include "util.h"
 
 #include "html/index.h"
 
-static void
-get(struct kreq *r)
-{
-	struct paste pastes[10] = {0};
-	size_t pastesz = NELEM(pastes);
-
-	if (!database_recents(pastes, &pastesz))
-		page_status(r, KHTTP_500);
-	else {
-		page_index_render(r, pastes, pastesz);
-
-		for (size_t i = 0; i < pastesz; ++i)
-			paste_finish(&pastes[i]);
-	}
-}
-
-static inline json_t *
-create_pastes(const struct paste *pastes, size_t pastesz)
-{
-	json_t *array = json_array();
-	const struct paste *paste;
+#define LIMIT 16
+#define TITLE "paster -- recent pastes"
 
-	for (size_t i = 0; i < pastesz; ++i) {
-		paste = &pastes[i];
+static void
+get(struct kreq *req)
+{
+	json_t *pastes;
 
-		json_array_append_new(array, json_pack("{ss ss ss ss so so}",
-			"id",           paste->id,
-			"author",       paste->author,
-			"title",        paste->title,
-			"date",         ju_date(paste),
-			"expiration",   ju_expiration(paste)
-		));
-	}
-
-	return array;
-}
-
-static inline json_t *
-create_doc(const struct paste *pastes, size_t pastesz)
-{
-	return json_pack("{ss so}",
-		"pagetitle",    "paster -- recent pastes",
-		"pastes",       create_pastes(pastes, pastesz)
-	);
+	if (!(pastes = database_recents(LIMIT)))
+		page_status(req, KHTTP_500);
+	else
+		page_index_render(req, pastes);
 }
 
 void
-page_index_render(struct kreq *req, const struct paste *pastes, size_t pastesz)
+page_index_render(struct kreq *req, json_t *pastes)
 {
 	assert(req);
 	assert(pastes);
 
-	page(req, KHTTP_200, html_index, create_doc(pastes, pastesz));
+	page(req, KHTTP_200, html_index, json_pack("{ss so}",
+		"title",        TITLE,
+		"pastes",       pastes
+	));
 }
 
 void
--- a/page-index.h	Thu Mar 16 15:05:26 2023 +0100
+++ b/page-index.h	Thu Mar 16 20:45:59 2023 +0100
@@ -2,11 +2,11 @@
  * page-index.h -- page /
  *
  * Copyright (c) 2020-2023 David Demelier <markand@malikania.fr>
- * 
+ *
  * Permission to use, copy, modify, and/or distribute this software for any
  * purpose with or without fee is hereby granted, provided that the above
  * copyright notice and this permission notice appear in all copies.
- * 
+ *
  * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
  * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
  * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
@@ -19,13 +19,12 @@
 #ifndef PASTER_PAGE_INDEX_H
 #define PASTER_PAGE_INDEX_H
 
-#include <stddef.h>
+#include <jansson.h>
 
 struct kreq;
-struct paste;
 
 void
-page_index_render(struct kreq *, const struct paste *, size_t);
+page_index_render(struct kreq *, json_t *pastes);
 
 void
 page_index(struct kreq *);
--- a/page-new.c	Thu Mar 16 15:05:26 2023 +0100
+++ b/page-new.c	Thu Mar 16 20:45:59 2023 +0100
@@ -23,18 +23,11 @@
 #include "json-util.h"
 #include "page-new.h"
 #include "page.h"
-#include "paste.h"
 #include "util.h"
 
 #include "html/new.h"
 
-static const struct paste paste_default = {
-	.id = "",
-	.title = "unknown",
-	.author = "anonymous",
-	.language = "nohighlight",
-	.code = ""
-};
+#define TITLE "paster -- create a new paste"
 
 static long long int
 duration(const char *val)
@@ -43,8 +36,8 @@
 		if (strcmp(val, durations[i].title) == 0)
 			return durations[i].secs;
 
-	/* Default to month. */
-	return PASTE_DURATION_MONTH;
+	/* Default to day. */
+	return 60 * 60 * 24;
 }
 
 static void
@@ -54,76 +47,69 @@
 }
 
 static void
-post(struct kreq *r)
+post(struct kreq *req)
 {
-	struct paste paste = {
-		.author         = estrdup("Anonymous"),
-		.title          = estrdup("Untitled"),
-		.language       = estrdup("nohighlight"),
-		.code           = estrdup(""),
-		.visible        = true,
-		.duration       = PASTE_DURATION_DAY
-	};
+	const char *key, *val, *id, *scheme;
+	json_t *paste;
 	int raw = 0;
 
-	for (size_t i = 0; i < r->fieldsz; ++i) {
-		const char *key = r->fields[i].key;
-		const char *val = r->fields[i].val;
+	paste = ju_paste_new();
 
-		if (strcmp(key, "title") == 0)
-			replace(&paste.title, val);
-		else if (strcmp(key, "author") == 0)
-			replace(&paste.author, val);
+	for (size_t i = 0; i < req->fieldsz; ++i) {
+		key = req->fields[i].key;
+		val = req->fields[i].val;
+
+		if (strcmp(key, "title") == 0 && strlen(val))
+			json_object_set_new(paste, "title", json_string(val));
+		else if (strcmp(key, "author") == 0 && strlen(val))
+			json_object_set_new(paste, "author", json_string(val));
 		else if (strcmp(key, "language") == 0)
-			replace(&paste.language, val);
+			json_object_set_new(paste, "language", json_string(val));
 		else if (strcmp(key, "duration") == 0)
-			paste.duration = duration(val);
+			json_object_set_new(paste, "duration", json_integer(duration(val)));
 		else if (strcmp(key, "code") == 0)
-			paste.code = estrdup(val);
-		else if (strcmp(key, "private") == 0)
-			paste.visible = strcmp(val, "on") != 0;
-		else if (strcmp(key, "raw") == 0) {
+			json_object_set_new(paste, "code", json_string(val));
+		else if (strcmp(key, "visible") == 0)
+			json_object_set_new(paste, "visible", json_boolean(strcmp(val, "on") == 0));
+		else if (strcmp(key, "raw") == 0)
 			raw = strcmp(val, "on") == 0;
-		}
 	}
 
-	if (!database_insert(&paste))
-		page_status(r,KHTTP_500);
+	if (database_insert(paste) < 0)
+		page_status(req, KHTTP_500);
 	else {
+		id = ju_get_string(paste, "id");
+		scheme = req->scheme == KSCHEME_HTTP ? "http" : "https";
+
 		if (raw) {
 			/* For CLI users (e.g. paster) just print the location. */
-			khttp_head(r, kresps[KRESP_STATUS], "%s", khttps[KHTTP_201]);
-			khttp_body(r);
-			khttp_printf(r, "%s://%s/paste/%s\n",
-			    r->scheme == KSCHEME_HTTP ? "http" : "https",
-			    r->host, paste.id);
-			khttp_free(r);
+			khttp_head(req, kresps[KRESP_STATUS], "%s", khttps[KHTTP_201]);
+			khttp_body(req);
+			khttp_printf(req, "%s://%s/paste/%s\n", scheme, req->host, id);
 		} else {
 			/* Otherwise, redirect to paste details. */
-			khttp_head(r, kresps[KRESP_STATUS], "%s", khttps[KHTTP_302]);
-			khttp_head(r, kresps[KRESP_LOCATION], "/paste/%s", paste.id);
-			khttp_body(r);
-			khttp_free(r);
+			khttp_head(req, kresps[KRESP_STATUS], "%s", khttps[KHTTP_302]);
+			khttp_head(req, kresps[KRESP_LOCATION], "/paste/%s", id);
+			khttp_body(req);
 		}
+
+		khttp_free(req);
 	}
 
-	paste_finish(&paste);
+	json_decref(paste);
 }
 
+#include "log.h"
+
 void
-page_new_render(struct kreq *req, const struct paste *paste)
+page_new_render(struct kreq *req, json_t *paste)
 {
 	assert(req);
 
-	if (!paste)
-		paste = &paste_default;
-
-	page(req, KHTTP_200, html_new, json_pack("{ss ss so so ss}",
-		"pagetitle",    "paster -- create new paste",
-		"title",        paste->title,
-		"languages",    ju_languages(paste->language),
-		"durations",    ju_durations(),
-		"code",         paste->code
+	page(req, KHTTP_200, html_new, ju_extend(paste, "{ss so so}",
+		"pagetitle", TITLE,
+		"durations", ju_durations(),
+		"languages", ju_languages(ju_get_string(paste, "language"))
 	));
 }
 
--- a/page-new.h	Thu Mar 16 15:05:26 2023 +0100
+++ b/page-new.h	Thu Mar 16 20:45:59 2023 +0100
@@ -2,11 +2,11 @@
  * page-new.h -- page /new
  *
  * Copyright (c) 2020-2023 David Demelier <markand@malikania.fr>
- * 
+ *
  * Permission to use, copy, modify, and/or distribute this software for any
  * purpose with or without fee is hereby granted, provided that the above
  * copyright notice and this permission notice appear in all copies.
- * 
+ *
  * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
  * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
  * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
@@ -19,11 +19,12 @@
 #ifndef PASTER_PAGE_NEW_H
 #define PASTER_PAGE_NEW_H
 
+#include <jansson.h>
+
 struct kreq;
-struct paste;
 
 void
-page_new_render(struct kreq *, const struct paste *);
+page_new_render(struct kreq *, json_t *);
 
 void
 page_new(struct kreq *);
--- a/page-paste.c	Thu Mar 16 15:05:26 2023 +0100
+++ b/page-paste.c	Thu Mar 16 20:45:59 2023 +0100
@@ -22,43 +22,53 @@
 #include "json-util.h"
 #include "page-paste.h"
 #include "page.h"
-#include "paste.h"
 #include "util.h"
 
 #include "html/paste.h"
 
 static inline json_t *
-create_pagetitle(const struct paste *paste)
+mk_pagetitle(const json_t *paste)
 {
-	return json_sprintf("paster -- %s", paste->title);
+	return json_sprintf("paster -- %s", ju_get_string(paste, "title"));
+}
+
+static inline json_t *
+mk_date(const json_t *paste)
+{
+	return ju_date(ju_get_int(paste, "timestamp"));
 }
 
 static inline json_t *
-create_paste(const struct paste *paste)
+mk_public(const json_t *paste)
 {
-	return json_pack("{so ss ss ss ss ss ss so so}",
-		"pagetitle",    create_pagetitle(paste),
-		"id",           paste->id,
-		"title",        paste->title,
-		"author",       paste->author,
-		"language",     paste->language,
-		"code",         paste->code,
-		"public",       paste->visible ? "Yes" : "No",
-		"date",         ju_date(paste),
-		"expiration",   ju_expiration(paste)
+	const intmax_t visible = ju_get_int(paste, "visible");
+
+	return json_string(visible ? "Yes" : "No");
+}
+
+static inline json_t *
+mk_expires(const json_t *paste)
+{
+	return ju_expires(
+	    ju_get_int(paste, "timestamp"),
+	    ju_get_int(paste, "duration")
 	);
 }
 
 static void
-get(struct kreq *r)
+get(struct kreq *req)
 {
-	struct paste paste = {0};
+	json_t *paste;
 
-	if (!database_get(&paste, r->path))
-		page_status(r, KHTTP_404);
+	if (!(paste = database_get(req->path)))
+		page_status(req, KHTTP_404);
 	else {
-		page(r, KHTTP_200, html_paste, create_paste(&paste));
-		paste_finish(&paste);
+		page(req, KHTTP_200, html_paste, ju_extend(paste, "{so so so so}",
+			"pagetitle",    mk_pagetitle(paste),
+			"date",         mk_date(paste),
+			"public",       mk_public(paste),
+			"expires",      mk_expires(paste)
+		));
 	}
 }
 
--- a/page-search.c	Thu Mar 16 15:05:26 2023 +0100
+++ b/page-search.c	Thu Mar 16 20:45:59 2023 +0100
@@ -24,31 +24,26 @@
 #include "page-index.h"
 #include "page-search.h"
 #include "page.h"
-#include "paste.h"
 #include "util.h"
 
 #include "html/search.h"
 
-static inline json_t *
-create_root(void)
-{
-	return json_pack("{ss so}",
-		"pagetitle",    "paster -- search",
-		"languages",    ju_languages(NULL)
-	);
-}
+#define TITLE    "paster -- search"
+#define LIMIT    16
 
 static void
 get(struct kreq *req)
 {
-	page(req, KHTTP_200, html_search, create_root());
+	page(req, KHTTP_200, html_search, json_pack("{ss so}",
+		"pagetitle",    "paster -- search",
+		"languages",    ju_languages(NULL)
+	));
 }
 
 static void
 post(struct kreq *req)
 {
-	struct paste pastes[10] = {0};
-	size_t pastesz = NELEM(pastes);
+	json_t *pastes;
 	const char *key, *val, *title = NULL, *author = NULL, *language = NULL;
 
 	for (size_t i = 0; i < req->fieldsz; ++i) {
@@ -69,13 +64,10 @@
 	if (author && strlen(author) == 0)
 		author = NULL;
 
-	if (!database_search(pastes, &pastesz, title, author, language))
+	if (!(pastes = database_search(16, title, author, language)))
 		page_status(req, KHTTP_500);
 	else
-		page_index_render(req, pastes, pastesz);
-
-	for (size_t i = 0; i < pastesz; ++i)
-		paste_finish(&pastes[i]);
+		page_index_render(req, pastes);
 }
 
 void
--- a/paste.c	Thu Mar 16 15:05:26 2023 +0100
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,36 +0,0 @@
-/*
- * paste.c -- paste definition
- *
- * Copyright (c) 2020-2023 David Demelier <markand@malikania.fr>
- * 
- * Permission to use, copy, modify, and/or distribute this software for any
- * purpose with or without fee is hereby granted, provided that the above
- * copyright notice and this permission notice appear in all copies.
- * 
- * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
- * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
- * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
- * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
- * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
- * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
- * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
- */
-
-#include <assert.h>
-#include <stdlib.h>
-#include <string.h>
-
-#include "paste.h"
-
-void
-paste_finish(struct paste *paste)
-{
-	assert(paste);
-
-	free(paste->id);
-	free(paste->title);
-	free(paste->author);
-	free(paste->language);
-	free(paste->code);
-	memset(paste, 0, sizeof (struct paste));
-}
--- a/paste.h	Thu Mar 16 15:05:26 2023 +0100
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,44 +0,0 @@
-/*
- * paste.h -- paste definition
- *
- * Copyright (c) 2020-2023 David Demelier <markand@malikania.fr>
- * 
- * Permission to use, copy, modify, and/or distribute this software for any
- * purpose with or without fee is hereby granted, provided that the above
- * copyright notice and this permission notice appear in all copies.
- * 
- * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
- * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
- * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
- * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
- * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
- * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
- * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
- */
-
-#ifndef PASTER_PASTE_H
-#define PASTER_PASTE_H
-
-#include <stdbool.h>
-#include <time.h>
-
-#define PASTE_DURATION_HOUR      3600           /* Seconds in one hour. */
-#define PASTE_DURATION_DAY       86400          /* Seconds in one day. */
-#define PASTE_DURATION_WEEK      604800         /* Seconds in one week. */
-#define PASTE_DURATION_MONTH     2592000        /* Rounded to 30 days. */
-
-struct paste {
-	char *id;
-	char *title;
-	char *author;
-	char *language;
-	char *code;
-	time_t timestamp;
-	bool visible;
-	long long int duration;
-};
-
-void
-paste_finish(struct paste *);
-
-#endif /* !PASTER_PASTE_H */
--- a/pasterd.c	Thu Mar 16 15:05:26 2023 +0100
+++ b/pasterd.c	Thu Mar 16 20:45:59 2023 +0100
@@ -42,7 +42,7 @@
 
 	if (!config.databasepath[0])
 		die("abort: no database specified\n");
-	if (!database_open(config.databasepath))
+	if (database_open(config.databasepath) < 0)
 		die("abort: could not open database\n");
 }
 
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/clear.sql	Thu Mar 16 20:45:59 2023 +0100
@@ -0,0 +1,21 @@
+--
+-- clear.sql -- cleanup expirated pastes
+--
+-- Copyright (c) 2020-2023 David Demelier <markand@malikania.fr>
+--
+-- Permission to use, copy, modify, and/or distribute this software for any
+-- purpose with or without fee is hereby granted, provided that the above
+-- copyright notice and this permission notice appear in all copies.
+--
+-- THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
+-- WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
+-- MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
+-- ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
+-- WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
+-- ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
+-- OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
+--
+
+DELETE
+  FROM paste
+ WHERE strftime('%s', 'now') - strftime('%s', date) >= `duration`
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/get.sql	Thu Mar 16 20:45:59 2023 +0100
@@ -0,0 +1,28 @@
+--
+-- get.sql -- get a paste by id
+--
+-- Copyright (c) 2020-2023 David Demelier <markand@malikania.fr>
+--
+-- Permission to use, copy, modify, and/or distribute this software for any
+-- purpose with or without fee is hereby granted, provided that the above
+-- copyright notice and this permission notice appear in all copies.
+--
+-- THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
+-- WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
+-- MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
+-- ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
+-- WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
+-- ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
+-- OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
+--
+
+SELECT `id`
+     , `title`
+     , `author`
+     , `language`
+     , `code`
+     , strftime('%s', `date`)
+     , `visible`
+     , `duration`
+  FROM `paste`
+ WHERE `id` = ?
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/init.sql	Thu Mar 16 20:45:59 2023 +0100
@@ -0,0 +1,32 @@
+--
+-- init.sql -- database initialization
+--
+-- Copyright (c) 2020-2023 David Demelier <markand@malikania.fr>
+--
+-- Permission to use, copy, modify, and/or distribute this software for any
+-- purpose with or without fee is hereby granted, provided that the above
+-- copyright notice and this permission notice appear in all copies.
+--
+-- THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
+-- WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
+-- MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
+-- ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
+-- WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
+-- ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
+-- OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
+--
+
+BEGIN EXCLUSIVE TRANSACTION;
+
+CREATE TABLE IF NOT EXISTS paste(
+	`id`            TEXT primary key,
+	`title`         TEXT not null,
+	`author`        TEXT not null,
+	`language`      TEXT not null,
+	`code`          TEXT not null,
+	`date`          INT default CURRENT_TIMESTAMP,
+	`visible`       INT default 0,
+	`duration`      INT
+);
+
+END TRANSACTION;
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/insert.sql	Thu Mar 16 20:45:59 2023 +0100
@@ -0,0 +1,27 @@
+--
+-- insert.sql -- create a new paste
+--
+-- Copyright (c) 2020-2023 David Demelier <markand@malikania.fr>
+--
+-- Permission to use, copy, modify, and/or distribute this software for any
+-- purpose with or without fee is hereby granted, provided that the above
+-- copyright notice and this permission notice appear in all copies.
+--
+-- THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
+-- WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
+-- MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
+-- ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
+-- WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
+-- ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
+-- OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
+--
+
+INSERT INTO paste(
+  `id`,
+  `title`,
+  `author`,
+  `language`,
+  `code`,
+  `visible`,
+  `duration`
+) VALUES (?, ?, ?, ?, ?, ?, ?)
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/recents.sql	Thu Mar 16 20:45:59 2023 +0100
@@ -0,0 +1,30 @@
+--
+-- recents.sql -- get a list of recent pastes
+--
+-- Copyright (c) 2020-2023 David Demelier <markand@malikania.fr>
+--
+-- Permission to use, copy, modify, and/or distribute this software for any
+-- purpose with or without fee is hereby granted, provided that the above
+-- copyright notice and this permission notice appear in all copies.
+--
+-- THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
+-- WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
+-- MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
+-- ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
+-- WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
+-- ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
+-- OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
+--
+
+SELECT `id`
+     , `title`
+     , `author`
+     , `language`
+     , `code`
+     , strftime('%s', date)
+     , `visible`
+     , `duration`
+  FROM paste
+ WHERE `visible` = 1
+ ORDER BY date DESC
+ LIMIT ?
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/search.sql	Thu Mar 16 20:45:59 2023 +0100
@@ -0,0 +1,33 @@
+--
+-- search.sql -- search existing public pastes
+--
+-- Copyright (c) 2020-2023 David Demelier <markand@malikania.fr>
+--
+-- Permission to use, copy, modify, and/or distribute this software for any
+-- purpose with or without fee is hereby granted, provided that the above
+-- copyright notice and this permission notice appear in all copies.
+--
+-- THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
+-- WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
+-- MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
+-- ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
+-- WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
+-- ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
+-- OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
+--
+
+SELECT `id`
+     , `title`
+     , `author`
+     , `language`
+     , `code`
+     , strftime('%s', date)
+     , `visible`
+     , `duration`
+  FROM paste
+ WHERE `title` like ?
+   AND `author` like ?
+   AND `language` like ?
+   AND `visible` = 1
+ ORDER BY date DESC
+ LIMIT ?
--- a/util.c	Thu Mar 16 15:05:26 2023 +0100
+++ b/util.c	Thu Mar 16 20:45:59 2023 +0100
@@ -28,7 +28,6 @@
 
 #include "config.h"
 #include "util.h"
-#include "paste.h"
 
 const char * const languages[] = {
 	"nohighlight",
--- a/util.h	Thu Mar 16 15:05:26 2023 +0100
+++ b/util.h	Thu Mar 16 20:45:59 2023 +0100
@@ -24,6 +24,11 @@
 
 #define NELEM(x) (sizeof (x) / sizeof (x)[0])
 
+#define PASTE_DURATION_HOUR     (3600)
+#define PASTE_DURATION_DAY      (PASTE_DURATION_HOUR * 24)
+#define PASTE_DURATION_WEEK     (PASTE_DURATION_DAY * 7)
+#define PASTE_DURATION_MONTH    (PASTE_DURATION_DAY * 30)
+
 struct tm;
 struct kreq;