server: Ensure db queries are all indexed

2 files changed, 4 insertions, 3 deletions

diff --git a/db.c b/db.c
index 413953b..0dd16e8 100644
--- a/db.c
+++ b/db.c
@@ -249,7 +249,7 @@ 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);
+	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;
@@ -262,7 +262,7 @@ bool db_set_pass(i64 userid, const char *pass) {
 	if (passhash.length == -1) return false;
 
 	sqlite3_stmt *stmt;
-	SQLITE(prepare_v2, database, "update USERS set passhash = ? where id = ?", -1, &stmt, NULL);
+	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;
diff --git a/schema.sql b/schema.sql
index 06baafd..15d274c 100644
--- a/schema.sql
+++ b/schema.sql
@@ -17,6 +17,7 @@ create table Members (
 	foreign key(room) references Rooms(id) on delete cascade,
 	foreign key(user) references Users(id) on delete cascade
 );
+create index members_user_index on Members(user);
 
 create table Users (
 	id integer primary key not null,
@@ -34,7 +35,7 @@ create table Messages (
 	foreign key(room) references Rooms(id) on delete cascade,
 	foreign key(user) references Users(id) on delete set null
 );
-create index messages_time_index on Messages(time);
+create index messages_time_index on Messages(room, time desc);
 
 create table Firebase (
 	user integer not null,