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);
 	}
 }