server: Performance: Cache sqlite prepared statements

1 files changed, 117 insertions, 96 deletions

diff --git a/db.c b/db.c
index f4c28f3..e58b284 100644
--- a/db.c
+++ b/db.c
@@ -25,6 +25,11 @@ static void die_sqlite(const char *func){
 	die("%s: %s",func,sqlite3_errmsg(database));
 }
 
+static void reset_stmt(sqlite3_stmt *stmt) {
+	SQLITE(reset, stmt);
+	SQLITE(clear_bindings, stmt);
+}
+
 struct hashed_password {
 	char str[crypto_pwhash_STRBYTES + 1];
 	int length;  // -1 if hashing failed
@@ -138,11 +143,11 @@ static char* gen_room_name(void){
 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);
+	static sqlite3_stmt *stmt = NULL;
+	if (!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);
+	reset_stmt(stmt);
 
 	i64 userid=sqlite3_last_insert_rowid(database);
 
@@ -156,32 +161,34 @@ struct db_name_id db_create_room(void){
 
 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);
+	static sqlite3_stmt *stmt = NULL;
+	if (!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);
+	reset_stmt(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);
+	static sqlite3_stmt *stmt = NULL;
+	if (!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);
+	reset_stmt(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);
+	static sqlite3_stmt *stmt = NULL;
+	if (!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;
@@ -201,26 +208,26 @@ struct db_user_list db_list_members(i64 roomid){
 	}
 
 	if(ret!=SQLITE_DONE)die_sqlite("sqlite3_step");
-	SQLITE(finalize,stmt);
+	reset_stmt(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);
+	static sqlite3_stmt *stmt = NULL;
+	if (!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);
+	reset_stmt(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);
+	static sqlite3_stmt *stmt = NULL;
+	if (!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){
@@ -228,17 +235,19 @@ char* db_get_roomname(i64 roomid){
 	}
 	char *name=NULL;
 	if(name_sq)name=strdup((const char*)name_sq);
-	SQLITE(finalize,stmt);
+	reset_stmt(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);
+	static sqlite3_stmt *stmt = NULL;
+	if (!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;
@@ -258,7 +267,7 @@ struct db_room_list db_list_rooms(i64 userid){
 	}
 
 	if(ret!=SQLITE_DONE)die_sqlite("sqlite3_step");
-	SQLITE(finalize,stmt);
+	reset_stmt(stmt);
 
 	return rl;
 }
@@ -268,12 +277,12 @@ 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);
+	static sqlite3_stmt *stmt = NULL;
+	if (!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);
+	reset_stmt(stmt);
 	if(success){
 		return sqlite3_last_insert_rowid(database);
 	} else {
@@ -282,12 +291,12 @@ i64 db_create_user(const char *name,const char *pass){
 }
 
 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);
+	static sqlite3_stmt *stmt = NULL;
+	if (!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);
+	reset_stmt(stmt);
 	return success;
 }
 
@@ -295,18 +304,18 @@ 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);
+	static sqlite3_stmt *stmt = NULL;
+	if (!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);
+	reset_stmt(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);
+	static sqlite3_stmt *stmt = NULL;
+	if (!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){
@@ -314,13 +323,13 @@ char* db_get_username(i64 userid){
 	}
 	char *name=NULL;
 	if(name_sq)name=strdup((const char*)name_sq);
-	SQLITE(finalize,stmt);
+	reset_stmt(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);
+	static sqlite3_stmt *stmt = NULL;
+	if (!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;
@@ -334,7 +343,7 @@ bool db_check_pass(i64 userid, const char *pass) {
 					crypto_pwhash_str_needs_rehash(
 							(const char*)passhash_sq, PASSHASH_OPSLIMIT, PASSHASH_MEMLIMIT);
 
-	SQLITE(finalize, stmt);
+	reset_stmt(stmt);
 
 	if (rehash) {
 		fprintf(stderr, "Rehashing password for userid=%" PRIi64 "\n", userid);
@@ -346,20 +355,20 @@ bool db_check_pass(i64 userid, const char *pass) {
 }
 
 i64 db_find_user(const char *name){
-	sqlite3_stmt *stmt;
-	SQLITE(prepare_v2,database,"select id from Users where name = ?",-1,&stmt,NULL);
+	static sqlite3_stmt *stmt = NULL;
+	if (!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);
+	reset_stmt(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);
+	static sqlite3_stmt *stmt = NULL;
+	if (!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;
@@ -378,7 +387,7 @@ struct db_strings_list db_user_tokens(i64 userid){
 	}
 
 	if(ret!=SQLITE_DONE)die_sqlite("sqlite3_step");
-	SQLITE(finalize,stmt);
+	reset_stmt(stmt);
 
 	return sl;
 }
@@ -386,39 +395,42 @@ struct db_strings_list db_user_tokens(i64 userid){
 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);
+	static sqlite3_stmt *stmt1 = NULL;
+	if (!stmt1) SQLITE(prepare_v2,database,"delete from Firebase where token = ?",-1,&stmt1,NULL);
+	SQLITE(bind_text,stmt1,1,token,-1,SQLITE_STATIC);
+	bool success=sqlite3_step(stmt1)==SQLITE_DONE;
+	reset_stmt(stmt1);
 	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);
+	static sqlite3_stmt *stmt2 = NULL;
+	if (!stmt2) SQLITE(prepare_v2,database,"insert into Firebase (user, token) values (?, ?)",-1,&stmt2,NULL);
+	SQLITE(bind_int64,stmt2,1,userid);
+	SQLITE(bind_text,stmt2,2,token,-1,SQLITE_STATIC);
+	success=sqlite3_step(stmt2)==SQLITE_DONE;
+	reset_stmt(stmt2);
 	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);
+	static sqlite3_stmt *stmt = NULL;
+	if (!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);
+	reset_stmt(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);
+	static sqlite3_stmt *stmt = NULL;
+	if (!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);
@@ -426,7 +438,7 @@ i64 db_create_message(i64 roomid,i64 userid,i64 timestamp,i64 replyid,const char
 	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);
+	reset_stmt(stmt);
 
 	return sqlite3_last_insert_rowid(database);
 }
@@ -438,28 +450,35 @@ struct db_message_list db_get_messages(i64 roomid,i64 count){
 struct db_message_list db_get_messages_before(i64 roomid,i64 count,i64 beforeid){
 	assert(count>=0);
 
-	sqlite3_stmt *stmt;
+	static sqlite3_stmt *stmt1 = NULL, *stmt2 = NULL;
+	sqlite3_stmt *stmt = NULL;  // the one used for this invocation
 	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);
+		if (!stmt1) {
+			SQLITE(prepare_v2,database,
+					"select id, user, time, reply, message "
+					"from Messages "
+					"where room = ? "
+					"order by time desc "
+					"limit ?"
+				,-1,&stmt1,NULL);
+		}
+		SQLITE(bind_int64,stmt1,1,roomid);
+		SQLITE(bind_int64,stmt1,2,count);
+		stmt = stmt1;
 	} 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);
+		if (!stmt2) {
+			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,&stmt2,NULL);
+		}
+		SQLITE(bind_int64,stmt2,1,roomid);
+		SQLITE(bind_int64,stmt2,2,beforeid);
+		SQLITE(bind_int64,stmt2,3,count);
+		stmt = stmt2;
 	}
 
 	struct db_message_list ml;
@@ -486,17 +505,19 @@ struct db_message_list db_get_messages_before(i64 roomid,i64 count,i64 beforeid)
 	}
 
 	if(ret!=SQLITE_DONE)die_sqlite("sqlite3_step");
-	SQLITE(finalize,stmt);
+	reset_stmt(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);
+	static sqlite3_stmt *stmt = NULL;
+	if (!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;
@@ -520,7 +541,7 @@ struct db_message db_get_message(i64 msgid) {
 		msg.message = NULL;
 	}
 
-	SQLITE(finalize, stmt);
+	reset_stmt(stmt);
 	return msg;
 }