From b1494a5d7b2744d3a8273b929d92fe112f0ae129 Mon Sep 17 00:00:00 2001 From: Tom Smeding Date: Mon, 7 Sep 2020 21:51:15 +0200 Subject: server: Performance: Cache sqlite prepared statements --- db.c | 213 +++++++++++++++++++++++++++++++++++++------------------------------ 1 file 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; } -- cgit v1.2.3