readr

db.c (6034B)

---

#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <sqlite3.h>

#include "db.h"
#include "utils.h"
#include "config.h"

static sqlite3* db = NULL;

void
db_create(void)
{
	const char* dbpath = expand_tilde(DB_PATH);

	const char* create_table = "CREATE TABLE IF NOT EXISTS posts ("
				   "  id INTEGER PRIMARY KEY AUTOINCREMENT,"
				   "  title TEXT NOT NULL,"
				   "  link TEXT NOT NULL,"
				   "  feed_url TEXT NOT NULL,"
				   "  comments TEXT NULL,"
				   "  pub_date TEXT,"
				   "  summary TEXT,"
				   "  seen INTEGER DEFAULT 0,"
				   "  UNIQUE(feed_url, link)"
				   ");";

	if (sqlite3_open(dbpath, &db) != SQLITE_OK) {
		fprintf(stderr, "could not open/create db file at path %s\n", dbpath);
		exit(1);
	}

	if (sqlite3_exec(db, create_table, NULL, NULL, NULL) != SQLITE_OK) {
		fprintf(stderr, "could not create table in db at path %s\n", DB_PATH);
		exit(1);
	}
}

void
db_insert_post(db_post_t dbp)
{
	if (db == NULL) {
		fprintf(stderr, "fetch_posts: bad args / missing db\n");
		exit(1);
	}
	if (dbp.link == NULL) {
		fprintf(stderr, "trying to insert a dbpost without a link!\n");
		exit(1);
	} else if (dbp.title == NULL) {
		fprintf(stderr, "trying to insert a dbpost without a title.\n");
		exit(1);
	} else if (dbp.feed_url == NULL) {
		fprintf(stderr, "trying to insert a dbpost without a feed.\n");
		exit(1);
	}

	sqlite3_stmt* ins = NULL;
	const char* ins_sql = "INSERT INTO posts(title, link, feed_url, comments, pub_date, summary, seen)"
			      " VALUES(?, ?, ?, ?, ?, ?, ?) ON CONFLICT(feed_url, link) DO NOTHING;";

	int rc = sqlite3_prepare_v2(db, ins_sql, -1, &ins, NULL);
	if (rc != SQLITE_OK) {
		fprintf(stderr, "prepare failed: %s\n", sqlite3_errmsg(db));
		exit(1);
	}

	sqlite3_reset(ins);

	sqlite3_clear_bindings(ins);

	if (sqlite3_bind_text(ins, 1, dbp.title, -1, SQLITE_TRANSIENT) != SQLITE_OK)
		perror("failed db insertion of: title");

	if (sqlite3_bind_text(ins, 2, dbp.link, -1, SQLITE_TRANSIENT) != SQLITE_OK)
		perror("failed db insertion of: link");

	if (sqlite3_bind_text(ins, 3, dbp.feed_url, -1, SQLITE_TRANSIENT) != SQLITE_OK)
		perror("failed db insertion of: feed_url");

	if (sqlite3_bind_text(ins, 4, dbp.comments ? dbp.comments : NULL, -1, SQLITE_TRANSIENT) != SQLITE_OK)
		perror("failed db insertion of: comments");

	if (sqlite3_bind_text(ins, 5, dbp.pub_date ? dbp.pub_date : NULL, -1, SQLITE_TRANSIENT) != SQLITE_OK)
		perror("failed db insertion of: pub_date");

	if (sqlite3_bind_text(ins, 6, dbp.summary ? dbp.summary : NULL, -1, SQLITE_TRANSIENT) != SQLITE_OK)
		perror("failed db insertion of: summary");

	if (sqlite3_bind_int(ins, 7, dbp.seen ? dbp.seen : 0) != SQLITE_OK)
		perror("failed db insertion of: seen");

	rc = sqlite3_step(ins);
	if (rc != SQLITE_DONE) {
		printf("%s\n", dbp.link);
		fprintf(stderr, "step failed: %s\n", sqlite3_errmsg(db));
	} else {
		int changes = sqlite3_changes(db);
		if (changes == 0) {
			// fprintf(stderr, "no change\n");
		} else {
			// sqlite3_int64 rowid = sqlite3_last_insert_rowid(db);
			// fprintf(stderr, "inserted rowid = %lld\n", (long long)rowid);
		}
	}

	sqlite3_finalize(ins);
}

static char*
dup_col(sqlite3_stmt* st, int i)
{
	const unsigned char* t = sqlite3_column_text(st, i);
	if (!t) return NULL;
	size_t n = strlen((const char*)t);
	char* s = (char*)ecalloc(n + 1, sizeof(char));
	if (!s) return NULL;
	memcpy(s, t, n + 1);
	return s;
}

db_fetch_post_t
db_fetch_posts(const char* feed_url)
{
	if (!db || !feed_url) {
		fprintf(stderr, "fetch_posts: bad args / missing db\n");
		return (db_fetch_post_t) { .success = 0 };
	}

	const char* sql = "SELECT id, title, link, feed_url, comments, pub_date, summary, seen "
			  "FROM posts WHERE feed_url = ? "
			  "ORDER BY id DESC LIMIT ?;";

	sqlite3_stmt* st = NULL;
	if (sqlite3_prepare_v2(db, sql, -1, &st, NULL) != SQLITE_OK) {
		fprintf(stderr, "prepare failed: %s\n", sqlite3_errmsg(db));
		return (db_fetch_post_t) { .success = 0 };
	}
	sqlite3_bind_text(st, 1, feed_url, -1, SQLITE_TRANSIENT);
	sqlite3_bind_int(st, 2, MAX_POST_PER_FEED);

	int len = 0, cap = 0;
	db_post_t** rows = NULL;

	while (sqlite3_step(st) == SQLITE_ROW) {
		db_post_t* p = ecalloc(1, sizeof(db_post_t));
		p->id = sqlite3_column_int(st, 0);
		p->title = dup_col(st, 1);
		p->link = dup_col(st, 2);
		p->feed_url = dup_col(st, 3);
		p->comments = dup_col(st, 4);
		p->pub_date = dup_col(st, 5);
		p->summary = dup_col(st, 6);
		p->seen = sqlite3_column_int(st, 7);
		if (len == cap) {
			cap = cap ? cap * 2 : MAX_POST_PER_FEED;
			rows = (db_post_t**)realloc(rows, sizeof(db_post_t*) * (size_t)cap);
			if (rows == NULL) {
				fprintf(stderr, "could not reallocate for list of posts fetch from db\n");
				break;
			}
		}
		rows[len++] = p;
	}

	int rc = sqlite3_finalize(st);

	if (rc != SQLITE_OK) {
		fprintf(stderr, "finalize: %s\n", sqlite3_errmsg(db));
		for (int i = 0; i < len; i++) {
			free(rows[i]->title);
			free(rows[i]->link);
			free(rows[i]->feed_url);
			free(rows[i]->comments);
			free(rows[i]->pub_date);
			free(rows[i]->summary);
		}
		free(rows);
		return (db_fetch_post_t) { .success = 0 };
	}

	return (db_fetch_post_t) {
		.posts = rows,
		.count = len,
		.success = 1,
	};
}

int
db_mark_as_seen(int id)
{
	if (!db) {
		fprintf(stderr, "db_mark_as_seen: missing db\n");
		return 1;
	}

	const char* sql = "UPDATE posts SET seen=1 WHERE id=?;";
	sqlite3_stmt* st = NULL;

	if (sqlite3_prepare_v2(db, sql, -1, &st, NULL) != SQLITE_OK) {
		fprintf(stderr, "db_mark_as_seen: prepare: %s\n", sqlite3_errmsg(db));
		return 1;
	}
	if (sqlite3_bind_int(st, 1, id) != SQLITE_OK) {
		fprintf(stderr, "db_mark_as_seen: bind id: %s\n", sqlite3_errmsg(db));
		sqlite3_finalize(st);
		return 1;
	}

	int rc = sqlite3_step(st);
	if (rc != SQLITE_DONE) {
		fprintf(stderr, "db_mark_as_seen: step: %s\n", sqlite3_errmsg(db));
		sqlite3_finalize(st);
		return 1;
	}

	sqlite3_finalize(st);
	return 0;
}

void
db_close(void)
{
	if (sqlite3_close(db) != SQLITE_OK) {
		fprintf(stderr, "prepare insert failed\n");
		exit(1);
	}
}