cleandb.c (2112B)
1#include <sqlite3.h> 2#include <stdlib.h> 3#include <stdio.h> 4#include <string.h> 5#include <unistd.h> 6#include <signal.h> 7#include <time.h> 8 9#include "util.h" 10 11int 12main(int argc, const char **argv) 13{ 14 sqlite3 *db; 15 sqlite3_stmt *user_res, *del_res; 16 int status, uid, now, creat, verbose; 17 const char *dbpath; 18 19 dbpath = "db.sqlite3"; 20 if (argc > 1) dbpath = argv[1]; 21 22 status = sqlite3_open(dbpath, &db); 23 ASSERTV(status == SQLITE_OK, "Cannot access database: %s", 24 sqlite3_errmsg(db)); 25 26 status = sqlite3_busy_timeout(db, 10000); 27 ASSERTV(status == SQLITE_OK, "Failed to set busy timeout: %s", 28 sqlite3_errmsg(db)); 29 30 status = sqlite3_prepare_v2(db, "SELECT uid, name, creat FROM users", 31 -1, &user_res, NULL); 32 ASSERTV(status == SQLITE_OK, "Failed to fetch data from database: %s", 33 sqlite3_errmsg(db)); 34 35 now = time(NULL); 36 37 while (sqlite3_step(user_res) == SQLITE_ROW) { 38 uid = sqlite3_column_int(user_res, 0); 39 creat = sqlite3_column_int(user_res, 2); 40 41 if (now - creat < 60 * 12) 42 continue; 43 44 printf("Removing user '%s'\n", sqlite3_column_text(user_res, 1)); 45 46 /* delete posts */ 47 status = sqlite3_prepare_v2(db, "DELETE FROM posts WHERE uid = ?", 48 -1, &del_res, NULL); 49 ASSERTV(status == SQLITE_OK, "Failed to delete posts from db: %s", 50 sqlite3_errmsg(db)); 51 52 status = sqlite3_bind_int(del_res, 1, uid); 53 ASSERTV(status == SQLITE_OK, "Failed to bind param to sql query: %s", 54 sqlite3_errmsg(db)); 55 56 ASSERTV(sqlite3_step(del_res) == SQLITE_DONE, 57 "Failed to delete posts of user: %s", sqlite3_errmsg(db)); 58 59 sqlite3_finalize(del_res); 60 61 /* delete user */ 62 status = sqlite3_prepare_v2(db, "DELETE FROM users WHERE uid = ?", 63 -1, &del_res, NULL); 64 ASSERTV(status == SQLITE_OK, "Failed to delete users from db: %s", 65 sqlite3_errmsg(db)); 66 67 status = sqlite3_bind_int(del_res, 1, uid); 68 ASSERTV(status == SQLITE_OK, "Failed to bind param to sql query: %s", 69 sqlite3_errmsg(db)); 70 71 ASSERTV(sqlite3_step(del_res) == SQLITE_DONE, 72 "Failed to delete posts of user: %s", sqlite3_errmsg(db)); 73 74 sqlite3_finalize(del_res); 75 } 76 77 sqlite3_finalize(user_res); 78 sqlite3_close(db); 79} 80