path: root/db.c

#define _GNU_SOURCE
#include <stdlib.h>
#include <string.h>
#include <assert.h>
#include <sqlite3.h>
#include <sodium.h>
#include "db.h"
#include "schema.sql.h"


#define SQLITE(func,...) do{if(sqlite3_##func(__VA_ARGS__)!=SQLITE_OK){die_sqlite("sqlite3_" #func);}}while(0)

#define DATABASE_VERSION 3

#define PASSHASH_OPSLIMIT 3
#define PASSHASH_MEMLIMIT crypto_pwhash_MEMLIMIT_INTERACTIVE

#define MAX_DATABASE_SIZE_BYTES ((i64)2 * 1024 * 1024 * 1024)


sqlite3 *database=NULL;

__attribute__((noreturn))
static void die_sqlite(const char *func){
	die("%s: %s",func,sqlite3_errmsg(database));
}

struct hashed_password {
	char str[crypto_pwhash_STRBYTES + 1];
	int length;  // -1 if hashing failed
};

static struct hashed_password hash_password(const char *pass) {
	struct hashed_password result;
	int ret = crypto_pwhash_str(
			result.str, pass, strlen(pass),
			PASSHASH_OPSLIMIT, PASSHASH_MEMLIMIT);
	if (ret != 0) {
		result.length = -1;
	} else {
		result.length = crypto_pwhash_STRBYTES;
	}
	return result;
}

// Returns whether successful
bool select_simple_int(const char *query, i64 *out) {
	sqlite3_stmt *stmt;
	int res = sqlite3_prepare_v2(database, query, -1, &stmt, NULL);
	bool retval = false;
	if (res == SQLITE_OK) {
		if (sqlite3_step(stmt) == SQLITE_ROW) {
			*out = sqlite3_column_int64(stmt, 0);
			retval = true;
		}
		SQLITE(finalize, stmt);
	}
	return retval;
}


void db_init(void){
	SQLITE(config, SQLITE_CONFIG_SERIALIZED);
	SQLITE(initialize);

	SQLITE(open_v2, "db.db", &database, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
	SQLITE(busy_timeout, database, 500);

	SQLITE(exec, database, "pragma foreign_keys = 1", NULL, NULL, NULL);

	i64 page_size, page_count;
	if (select_simple_int("pragma page_size", &page_size) &&
			select_simple_int("pragma page_count", &page_count)) {
		i64 max_num_pages = MAX_DATABASE_SIZE_BYTES / page_size;
		if (page_count >= max_num_pages - 1) {
			fprintf(stderr, "WARNING: Database already at maximum size!\n");
			max_num_pages = page_count + 2;
		}

		// SQLite doesn't allow preparing a pragma, so let's splice the integer in directly
		char *str = NULL;
		asprintf(&str, "pragma max_page_count = %" PRIi64, max_num_pages);
		SQLITE(exec, database, str, NULL, NULL, NULL);
		free(str);
	} else {
		die("Failed to query database page statistics: %s", sqlite3_errmsg(database));
	}

	i64 version;
	if (select_simple_int("select version from Meta", &version)) {
		if (version != DATABASE_VERSION) {
			die("Database version incompatible: database %" PRIi64 ", application %d",
				version, DATABASE_VERSION);
		}
	} else {
		char *str = malloc(schema_sql_len+1, char);
		memcpy(str, schema_sql, schema_sql_len);
		str[schema_sql_len] = '\0';
		SQLITE(exec, database, str, NULL, NULL, NULL);
		free(str);

		sqlite3_stmt *stmt;
		SQLITE(prepare_v2, database, "insert into Meta (version) values (?)", -1, &stmt, NULL);
		SQLITE(bind_int64, stmt, 1, DATABASE_VERSION);
		if (sqlite3_step(stmt) != SQLITE_DONE) {
			die("Could not set database version: %s", sqlite3_errmsg(database));
		}
		SQLITE(finalize, stmt);
	}
}

void db_reinit(void){
	SQLITE(open_v2,"db.db",&database,SQLITE_OPEN_READWRITE,NULL);
	SQLITE(busy_timeout,database,500);
}

void db_close(void){
	sqlite3_close(database);
	SQLITE(shutdown);
	database=NULL;
}


static char* gen_room_name(void){
	const int name_len=8;
	const char *alphabet="abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
	const int alpha_len=strlen(alphabet);

	char *name=malloc(name_len+1,char);
	for(int i=0;i<name_len;i++){
		name[i]=alphabet[random()%alpha_len];
	}
	name[name_len]='\0';
	debug("Generated name: %s",name);
	return name;
}

struct db_name_id db_create_room(void){
	char *name=gen_room_name();

	sqlite3_stmt *stmt;
	SQLITE(prepare_v2,database,"insert into Rooms (name) values (?)",-1,&stmt,NULL);
	SQLITE(bind_text,stmt,1,name,-1,SQLITE_STATIC);
	bool success=sqlite3_step(stmt)==SQLITE_DONE;
	SQLITE(finalize,stmt);

	i64 userid=sqlite3_last_insert_rowid(database);

	if(!success){
		free(name);
		return (struct db_name_id){NULL,-1};
	}

	return (struct db_name_id){name,userid};
}

bool db_add_member(i64 roomid,i64 userid){
	assert(roomid!=-1&&userid!=-1);
	sqlite3_stmt *stmt;
	SQLITE(prepare_v2,database,"insert into Members (room, user) values (?, ?)",-1,&stmt,NULL);
	SQLITE(bind_int64,stmt,1,roomid);
	SQLITE(bind_int64,stmt,2,userid);
	bool success=sqlite3_step(stmt)==SQLITE_DONE;
	SQLITE(finalize,stmt);
	return success;
}

bool db_is_member(i64 roomid,i64 userid){
	sqlite3_stmt *stmt;
	SQLITE(prepare_v2,database,"select 1 from Members where room = ? and user = ?",-1,&stmt,NULL);
	SQLITE(bind_int64,stmt,1,roomid);
	SQLITE(bind_int64,stmt,2,userid);
	bool success=sqlite3_step(stmt)==SQLITE_ROW;
	SQLITE(finalize,stmt);
	return success;
}

struct db_user_list db_list_members(i64 roomid){
	sqlite3_stmt *stmt;
	SQLITE(prepare_v2,database,
			"select U.id, U.name "
			"from Users as U, Members as M "
			"where M.room = ? and M.user = U.id"
		,-1,&stmt,NULL);
	SQLITE(bind_int64,stmt,1,roomid);

	struct db_user_list ul;
	i64 cap=4;
	ul.count=0;
	ul.list=malloc(cap,struct db_name_id);

	int ret;
	while((ret=sqlite3_step(stmt))==SQLITE_ROW){
		if(ul.count==cap){
			cap*=2;
			ul.list=realloc(ul.list,cap,struct db_name_id);
		}
		ul.list[ul.count].id=sqlite3_column_int64(stmt,0);
		ul.list[ul.count].name=strdup((const char*)sqlite3_column_text(stmt,1));
		ul.count++;
	}

	if(ret!=SQLITE_DONE)die_sqlite("sqlite3_step");
	SQLITE(finalize,stmt);

	return ul;
}

i64 db_find_room(const char *name){
	sqlite3_stmt *stmt;
	SQLITE(prepare_v2,database,"select id from Rooms where name = ?",-1,&stmt,NULL);
	SQLITE(bind_text,stmt,1,name,-1,SQLITE_STATIC);
	i64 roomid=-1;
	if(sqlite3_step(stmt)==SQLITE_ROW){
		roomid=sqlite3_column_int64(stmt,0);
	}
	SQLITE(finalize,stmt);
	return roomid;
}

char* db_get_roomname(i64 roomid){
	sqlite3_stmt *stmt;
	SQLITE(prepare_v2,database,"select name from Rooms where id = ?",-1,&stmt,NULL);
	SQLITE(bind_int64,stmt,1,roomid);
	const unsigned char *name_sq=NULL;
	if(sqlite3_step(stmt)==SQLITE_ROW){
		name_sq=sqlite3_column_text(stmt,0);
	}
	char *name=NULL;
	if(name_sq)name=strdup((const char*)name_sq);
	SQLITE(finalize,stmt);
	return name;
}

struct db_room_list db_list_rooms(i64 userid){
	sqlite3_stmt *stmt;
	SQLITE(prepare_v2,database,
			"select M.room, R.name "
			"from Members as M, Rooms as R "
			"where M.user = ? and M.room = R.id"
		,-1,&stmt,NULL);
	SQLITE(bind_int64,stmt,1,userid);

	struct db_room_list rl;
	i64 cap=4;
	rl.count=0;
	rl.list=malloc(cap,struct db_name_id);

	int ret;
	while((ret=sqlite3_step(stmt))==SQLITE_ROW){
		if(rl.count==cap){
			cap*=2;
			rl.list=realloc(rl.list,cap,struct db_name_id);
		}
		rl.list[rl.count].id=sqlite3_column_int64(stmt,0);
		rl.list[rl.count].name=strdup((const char*)sqlite3_column_text(stmt,1));
		rl.count++;
	}

	if(ret!=SQLITE_DONE)die_sqlite("sqlite3_step");
	SQLITE(finalize,stmt);

	return rl;
}


i64 db_create_user(const char *name,const char *pass){
	struct hashed_password passhash = hash_password(pass);
	if (passhash.length == -1) return -1;

	sqlite3_stmt *stmt;
	SQLITE(prepare_v2,database,"insert into Users (name, passhash) values (?, ?)",-1,&stmt,NULL);
	SQLITE(bind_text,stmt,1,name,-1,SQLITE_STATIC);
	SQLITE(bind_text,stmt,2,passhash.str,passhash.length,SQLITE_STATIC);
	bool success=sqlite3_step(stmt)==SQLITE_DONE;
	SQLITE(finalize,stmt);
	if(success){
		return sqlite3_last_insert_rowid(database);
	} else {
		return -1;
	}
}

bool db_set_username(i64 userid, const char *name) {
	sqlite3_stmt *stmt;
	SQLITE(prepare_v2, database, "update Users set name = ? where id = ?", -1, &stmt, NULL);
	SQLITE(bind_text, stmt, 1, name, -1, SQLITE_STATIC);
	SQLITE(bind_int64, stmt, 2, userid);
	bool success = sqlite3_step(stmt) == SQLITE_DONE;
	SQLITE(finalize, stmt);
	return success;
}

bool db_set_pass(i64 userid, const char *pass) {
	struct hashed_password passhash = hash_password(pass);
	if (passhash.length == -1) return false;

	sqlite3_stmt *stmt;
	SQLITE(prepare_v2, database, "update Users set passhash = ? where id = ?", -1, &stmt, NULL);
	SQLITE(bind_text, stmt, 1, passhash.str, passhash.length, SQLITE_STATIC);
	SQLITE(bind_int64, stmt, 2, userid);
	bool success = sqlite3_step(stmt) == SQLITE_DONE;
	SQLITE(finalize, stmt);
	return success;
}

char* db_get_username(i64 userid){
	sqlite3_stmt *stmt;
	SQLITE(prepare_v2,database,"select name from Users where id = ?",-1,&stmt,NULL);
	SQLITE(bind_int64,stmt,1,userid);
	const unsigned char *name_sq=NULL;
	if(sqlite3_step(stmt)==SQLITE_ROW){
		name_sq=sqlite3_column_text(stmt,0);
	}
	char *name=NULL;
	if(name_sq)name=strdup((const char*)name_sq);
	SQLITE(finalize,stmt);
	return name;
}

bool db_check_pass(i64 userid, const char *pass) {
	sqlite3_stmt *stmt;
	SQLITE(prepare_v2, database, "select passhash from Users where id = ?", -1, &stmt, NULL);
	SQLITE(bind_int64, stmt, 1, userid);

	const unsigned char *passhash_sq = NULL;
	if (sqlite3_step(stmt) == SQLITE_ROW) {
		passhash_sq = sqlite3_column_text(stmt, 0);
	}

	int ret = crypto_pwhash_str_verify((const char*)passhash_sq, pass, strlen(pass));
	bool success = ret == 0;
	bool rehash = success &&
					crypto_pwhash_str_needs_rehash(
							(const char*)passhash_sq, PASSHASH_OPSLIMIT, PASSHASH_MEMLIMIT);

	SQLITE(finalize, stmt);

	if (rehash) {
		fprintf(stderr, "Rehashing password for userid=%" PRIi64 "\n", userid);
		// Ignore whether this succeeds; if it doesn't, we continue using the old hash
		db_set_pass(userid, pass);
	}

	return success;
}

i64 db_find_user(const char *name){
	sqlite3_stmt *stmt;
	SQLITE(prepare_v2,database,"select id from Users where name = ?",-1,&stmt,NULL);
	SQLITE(bind_text,stmt,1,name,-1,SQLITE_STATIC);
	i64 userid=-1;
	if(sqlite3_step(stmt)==SQLITE_ROW){
		userid=sqlite3_column_int64(stmt,0);
	}
	SQLITE(finalize,stmt);
	return userid;
}

struct db_strings_list db_user_tokens(i64 userid){
	sqlite3_stmt *stmt;
	SQLITE(prepare_v2,database,"select token from Firebase where user = ?",-1,&stmt,NULL);
	SQLITE(bind_int64,stmt,1,userid);

	struct db_strings_list sl;
	i64 cap=4;
	sl.count=0;
	sl.list=malloc(cap,char*);

	int ret;
	while((ret=sqlite3_step(stmt))==SQLITE_ROW){
		if(sl.count==cap){
			cap*=2;
			sl.list=realloc(sl.list,cap,char*);
		}
		sl.list[sl.count]=strdup((const char*)sqlite3_column_text(stmt,0));
		sl.count++;
	}

	if(ret!=SQLITE_DONE)die_sqlite("sqlite3_step");
	SQLITE(finalize,stmt);

	return sl;
}

bool db_add_token(i64 userid,const char *token){
	assert(userid!=-1);

	sqlite3_stmt *stmt;
	SQLITE(prepare_v2,database,"delete from Firebase where token = ?",-1,&stmt,NULL);
	SQLITE(bind_text,stmt,1,token,-1,SQLITE_STATIC);
	bool success=sqlite3_step(stmt)==SQLITE_DONE;
	SQLITE(finalize,stmt);
	if(!success)return false;

	SQLITE(prepare_v2,database,"insert into Firebase (user, token) values (?, ?)",-1,&stmt,NULL);
	SQLITE(bind_int64,stmt,1,userid);
	SQLITE(bind_text,stmt,2,token,-1,SQLITE_STATIC);
	success=sqlite3_step(stmt)==SQLITE_DONE;
	SQLITE(finalize,stmt);
	return success;
}

bool db_delete_token(i64 userid,const char *token){
	assert(userid!=-1);
	sqlite3_stmt *stmt;
	SQLITE(prepare_v2,database,"delete from Firebase where user = ? and token = ?",-1,&stmt,NULL);
	SQLITE(bind_int64,stmt,1,userid);
	SQLITE(bind_text,stmt,2,token,-1,SQLITE_STATIC);
	bool success=sqlite3_step(stmt)==SQLITE_DONE;
	SQLITE(finalize,stmt);
	return success;
}


i64 db_create_message(i64 roomid,i64 userid,i64 timestamp,i64 replyid,const char *message){
	sqlite3_stmt *stmt;
	SQLITE(prepare_v2,database,
			"insert into Messages (room, user, time, reply, message) "
			"values (?, ?, ?, ?, ?)"
		,-1,&stmt,NULL);
	SQLITE(bind_int64,stmt,1,roomid);
	SQLITE(bind_int64,stmt,2,userid);
	SQLITE(bind_int64,stmt,3,timestamp);
	if(replyid>=0)SQLITE(bind_int64,stmt,4,replyid);
	else SQLITE(bind_null,stmt,4);
	SQLITE(bind_blob,stmt,5,message,strlen(message),SQLITE_STATIC);
	if(sqlite3_step(stmt)!=SQLITE_DONE)die_sqlite("sqlite3_step");
	SQLITE(finalize,stmt);

	return sqlite3_last_insert_rowid(database);
}

struct db_message_list db_get_messages(i64 roomid,i64 count){
	return db_get_messages_before(roomid,count,-1);
}

struct db_message_list db_get_messages_before(i64 roomid,i64 count,i64 beforeid){
	assert(count>=0);

	sqlite3_stmt *stmt;
	if(beforeid<0){
		SQLITE(prepare_v2,database,
				"select id, user, time, reply, message "
				"from Messages "
				"where room = ? "
				"order by time desc "
				"limit ?"
			,-1,&stmt,NULL);
		SQLITE(bind_int64,stmt,1,roomid);
		SQLITE(bind_int64,stmt,2,count);
	} else {
		SQLITE(prepare_v2,database,
				"select id, user, time, reply, message "
				"from Messages "
				"where room = ? and time < (select time from Messages where id = ?) "
				"order by time desc "
				"limit ?"
			,-1,&stmt,NULL);
		SQLITE(bind_int64,stmt,1,roomid);
		SQLITE(bind_int64,stmt,2,beforeid);
		SQLITE(bind_int64,stmt,3,count);
	}

	struct db_message_list ml;
	i64 cap=count;
	ml.count=0;
	ml.list=malloc(cap,struct db_message);

	int ret;
	while((ret=sqlite3_step(stmt))==SQLITE_ROW){
		if(ml.count==cap){
			die("sqlite gave too many rows while 'limit %" PRIi64 "' was present",count);
		}
		ml.list[ml.count].msgid=sqlite3_column_int64(stmt,0);
		ml.list[ml.count].roomid=roomid;
		ml.list[ml.count].userid=sqlite3_column_int64(stmt,1);
		ml.list[ml.count].timestamp=sqlite3_column_int64(stmt,2);
		if(sqlite3_column_type(stmt,3)==SQLITE_INTEGER){
			ml.list[ml.count].replyid=sqlite3_column_int64(stmt,3);
		} else {
			ml.list[ml.count].replyid=-1;  // NULL, not a reply
		}
		ml.list[ml.count].message=strdup((const char*)sqlite3_column_text(stmt,4));
		ml.count++;
	}

	if(ret!=SQLITE_DONE)die_sqlite("sqlite3_step");
	SQLITE(finalize,stmt);

	return ml;
}

struct db_message db_get_message(i64 msgid) {
	sqlite3_stmt *stmt;
	SQLITE(prepare_v2, database,
			"select room, user, time, reply, message "
			"from Messages where id = ?",
		-1, &stmt, NULL);
	SQLITE(bind_int64, stmt, 1, msgid);

	struct db_message msg;
	if (sqlite3_step(stmt) == SQLITE_ROW) {
		msg.msgid = msgid;
		msg.roomid = sqlite3_column_int64(stmt,0);
		msg.userid = sqlite3_column_int64(stmt,1);
		msg.timestamp = sqlite3_column_int64(stmt,2);
		if (sqlite3_column_type(stmt, 3) == SQLITE_INTEGER) {
			msg.replyid = sqlite3_column_int64(stmt, 3);
		} else {
			msg.replyid = -1;  // NULL, not a reply
		}
		msg.message = strdup((const char*)sqlite3_column_text(stmt, 4));
	} else {
		msg.msgid = -1;
		msg.roomid = -1;
		msg.userid = -1;
		msg.timestamp = -1;
		msg.replyid = -1;
		msg.message = NULL;
	}

	SQLITE(finalize, stmt);
	return msg;
}


void db_nullify_name_id(struct db_name_id ni){
	if(ni.name)free(ni.name);
}

void db_nullify_room_list(struct db_room_list rl){
	for(i64 i=0;i<rl.count;i++){
		free(rl.list[i].name);
	}
	if(rl.list)free(rl.list);
	rl.list=NULL;
}

void db_nullify_user_list(struct db_user_list ul){
	for(i64 i=0;i<ul.count;i++){
		free(ul.list[i].name);
	}
	if(ul.list)free(ul.list);
	ul.list=NULL;
}

void db_nullify_message(struct db_message msg){
	if(msg.message)free(msg.message);
}

void db_nullify_message_list(struct db_message_list ml){
	for(i64 i=0;i<ml.count;i++){
		db_nullify_message(ml.list[i]);
	}
	if(ml.list)free(ml.list);
	ml.list=NULL;
}

void db_nullify_strings_list(struct db_strings_list sl){
	for(i64 i=0;i<sl.count;i++){
		free(sl.list[i]);
	}
	if(sl.list)free(sl.list);
	sl.list=NULL;
}