summaryrefslogtreecommitdiff
path: root/src/core/storage_sql.c
diff options
context:
space:
mode:
Diffstat (limited to 'src/core/storage_sql.c')
-rw-r--r--src/core/storage_sql.c1209
1 files changed, 1209 insertions, 0 deletions
diff --git a/src/core/storage_sql.c b/src/core/storage_sql.c
new file mode 100644
index 0000000..4a5dc13
--- /dev/null
+++ b/src/core/storage_sql.c
@@ -0,0 +1,1209 @@
+#include "hybbx/storage.h"
+#include "hybbx/auth.h"
+#include "hybbx/config.h"
+#include "hybbx/password.h"
+#include "hybbx/security.h"
+#include "hybbx/util.h"
+#include "hybbx/log.h"
+#include "storage_private.h"
+
+#include <errno.h>
+#include <stdio.h>
+#include <stdlib.h>
+#include <string.h>
+#include <sys/stat.h>
+#include <time.h>
+#include <unistd.h>
+
+#ifdef HYBBX_HAVE_SQLITE
+#include <sqlite3.h>
+#endif
+
+#define HYBBX_SQL_DEFAULT_USER_DB "users.db"
+#define HYBBX_SQL_DEFAULT_MAIL_DB "mail.db"
+
+#ifndef HYBBX_HAVE_SQLITE
+
+hybbx_result_t hybbx_storage_sql_open(hybbx_storage_t *storage)
+{
+ (void)storage;
+ hybbx_log_warn("[storage] SQLite backend requested but hybbx was built without "
+ "libsqlite3 — use backend=flatfile or rebuild with sqlite3");
+ return HYBBX_ERR_UNSUPPORTED;
+}
+
+void hybbx_storage_sql_close(hybbx_storage_t *storage)
+{
+ (void)storage;
+}
+
+hybbx_result_t hybbx_storage_sql_register_user(hybbx_storage_t *storage,
+ const hybbx_user_registration_t *reg,
+ hybbx_user_record_t *out)
+{
+ (void)storage;
+ (void)reg;
+ (void)out;
+ return HYBBX_ERR_UNSUPPORTED;
+}
+
+hybbx_result_t hybbx_storage_sql_find_user(hybbx_storage_t *storage,
+ const char *username,
+ hybbx_user_record_t *out)
+{
+ (void)storage;
+ (void)username;
+ (void)out;
+ return HYBBX_ERR_UNSUPPORTED;
+}
+
+hybbx_result_t hybbx_storage_sql_resolve_user(hybbx_storage_t *storage,
+ const char *name,
+ hybbx_user_record_t *out)
+{
+ (void)storage;
+ (void)name;
+ (void)out;
+ return HYBBX_ERR_UNSUPPORTED;
+}
+
+hybbx_result_t hybbx_storage_sql_count_level(hybbx_storage_t *storage,
+ hybbx_user_level_t level,
+ size_t *count)
+{
+ (void)storage;
+ (void)level;
+ (void)count;
+ return HYBBX_ERR_UNSUPPORTED;
+}
+
+hybbx_result_t hybbx_storage_sql_foreach_user(hybbx_storage_t *storage,
+ hybbx_storage_user_fn fn,
+ void *ctx)
+{
+ (void)storage;
+ (void)fn;
+ (void)ctx;
+ return HYBBX_ERR_UNSUPPORTED;
+}
+
+hybbx_result_t hybbx_storage_sql_update_user(hybbx_storage_t *storage,
+ const hybbx_user_record_t *user)
+{
+ (void)storage;
+ (void)user;
+ return HYBBX_ERR_UNSUPPORTED;
+}
+
+hybbx_result_t hybbx_storage_sql_delete_user(hybbx_storage_t *storage,
+ uint64_t user_id)
+{
+ (void)storage;
+ (void)user_id;
+ return HYBBX_ERR_UNSUPPORTED;
+}
+
+hybbx_result_t hybbx_storage_sql_session_begin(hybbx_storage_t *storage,
+ const hybbx_user_record_t *user,
+ const char *transport,
+ hybbx_session_record_t *out)
+{
+ (void)storage;
+ (void)user;
+ (void)transport;
+ (void)out;
+ return HYBBX_ERR_UNSUPPORTED;
+}
+
+hybbx_result_t hybbx_storage_sql_session_end(hybbx_storage_t *storage,
+ uint64_t session_id)
+{
+ (void)storage;
+ (void)session_id;
+ return HYBBX_ERR_UNSUPPORTED;
+}
+
+void hybbx_storage_sql_backup_files(const hybbx_storage_t *storage)
+{
+ (void)storage;
+}
+
+void hybbx_storage_sql_backup_tick(hybbx_storage_t *storage)
+{
+ (void)storage;
+}
+
+#else /* HYBBX_HAVE_SQLITE */
+
+struct sql_state {
+ sqlite3 *users_db;
+ sqlite3 *mail_db;
+ unsigned backup_tick;
+};
+
+static int sql_str_ieq(const char *a, const char *b)
+{
+ if (a == NULL || b == NULL) {
+ return 0;
+ }
+
+ while (*a != '\0' && *b != '\0') {
+ char ca = (char)(*a >= 'A' && *a <= 'Z' ? *a + 32 : *a);
+ char cb = (char)(*b >= 'A' && *b <= 'Z' ? *b + 32 : *b);
+
+ if (ca != cb) {
+ return 0;
+ }
+ a++;
+ b++;
+ }
+
+ return *a == '\0' && *b == '\0';
+}
+
+static int mkdir_p(const char *path)
+{
+ char buf[HYBBX_PATH_MAX];
+ size_t len;
+ size_t i;
+
+ if (path == NULL || path[0] == '\0') {
+ return -1;
+ }
+
+ len = strlen(path);
+ if (len >= sizeof(buf)) {
+ return -1;
+ }
+
+ memcpy(buf, path, len + 1);
+
+ for (i = 1; i < len; i++) {
+ if (buf[i] == '/') {
+ buf[i] = '\0';
+ if (mkdir(buf, 0755) != 0 && errno != EEXIST) {
+ return -1;
+ }
+ buf[i] = '/';
+ }
+ }
+
+ if (mkdir(buf, 0755) != 0 && errno != EEXIST) {
+ return -1;
+ }
+
+ return 0;
+}
+
+static hybbx_result_t sql_exec(sqlite3 *db, const char *sql)
+{
+ char *err = NULL;
+ int rc;
+
+ if (db == NULL || sql == NULL) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ rc = sqlite3_exec(db, sql, NULL, NULL, &err);
+ if (rc != SQLITE_OK) {
+ hybbx_log_warn("[storage] sqlite: %s",
+ err != NULL ? err : sqlite3_errmsg(db));
+ sqlite3_free(err);
+ return HYBBX_ERR_IO;
+ }
+
+ return HYBBX_OK;
+}
+
+static hybbx_result_t sql_meta_get(sqlite3 *db, const char *key, uint64_t *value)
+{
+ sqlite3_stmt *stmt;
+ int rc;
+
+ *value = 0;
+
+ rc = sqlite3_prepare_v2(db,
+ "SELECT value FROM meta WHERE key = ?1;",
+ -1, &stmt, NULL);
+ if (rc != SQLITE_OK) {
+ return HYBBX_ERR_IO;
+ }
+
+ sqlite3_bind_text(stmt, 1, key, -1, SQLITE_STATIC);
+ rc = sqlite3_step(stmt);
+ if (rc == SQLITE_ROW) {
+ *value = (uint64_t)sqlite3_column_int64(stmt, 0);
+ }
+ sqlite3_finalize(stmt);
+ return HYBBX_OK;
+}
+
+static hybbx_result_t sql_meta_set(sqlite3 *db, const char *key, uint64_t value)
+{
+ sqlite3_stmt *stmt;
+ int rc;
+
+ rc = sqlite3_prepare_v2(db,
+ "INSERT INTO meta(key,value) VALUES(?1,?2) "
+ "ON CONFLICT(key) DO UPDATE SET value=?2;",
+ -1, &stmt, NULL);
+ if (rc != SQLITE_OK) {
+ return HYBBX_ERR_IO;
+ }
+
+ sqlite3_bind_text(stmt, 1, key, -1, SQLITE_STATIC);
+ sqlite3_bind_int64(stmt, 2, (sqlite3_int64)value);
+ rc = sqlite3_step(stmt);
+ sqlite3_finalize(stmt);
+
+ return (rc == SQLITE_DONE) ? HYBBX_OK : HYBBX_ERR_IO;
+}
+
+static hybbx_result_t sql_meta_bump(sqlite3 *db, const char *key, uint64_t *value)
+{
+ hybbx_result_t rc;
+
+ rc = sql_meta_get(db, key, value);
+ if (rc != HYBBX_OK) {
+ return rc;
+ }
+
+ (*value)++;
+ return sql_meta_set(db, key, *value);
+}
+
+static hybbx_result_t sql_open_db(const char *path, sqlite3 **out_db)
+{
+ int rc;
+ int existed = (access(path, F_OK) == 0);
+
+ if (out_db == NULL) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ *out_db = NULL;
+ rc = sqlite3_open(path, out_db);
+ if (rc != SQLITE_OK) {
+ hybbx_log_warn("[storage] cannot open '%s': %s",
+ path, sqlite3_errmsg(*out_db));
+ if (*out_db != NULL) {
+ sqlite3_close(*out_db);
+ *out_db = NULL;
+ }
+ return HYBBX_ERR_IO;
+ }
+
+ sqlite3_busy_timeout(*out_db, 5000);
+ if (!existed) {
+ hybbx_log_info("[storage] created new database %s", path);
+ } else {
+ hybbx_log_info("[storage] opened existing database %s", path);
+ }
+
+ return HYBBX_OK;
+}
+
+static hybbx_result_t sql_init_users_schema(sqlite3 *db)
+{
+ static const char *schema =
+ "CREATE TABLE IF NOT EXISTS users ("
+ " id INTEGER PRIMARY KEY,"
+ " username TEXT NOT NULL UNIQUE,"
+ " nickname TEXT,"
+ " level INTEGER NOT NULL,"
+ " active INTEGER NOT NULL,"
+ " created_at INTEGER NOT NULL,"
+ " full_name TEXT,"
+ " country TEXT,"
+ " location TEXT,"
+ " email TEXT,"
+ " password TEXT NOT NULL,"
+ " last_login_at INTEGER"
+ ");"
+ "CREATE INDEX IF NOT EXISTS idx_users_nickname ON users(nickname);"
+ "CREATE TABLE IF NOT EXISTS sessions ("
+ " session_id INTEGER PRIMARY KEY,"
+ " user_id INTEGER NOT NULL,"
+ " username TEXT,"
+ " transport TEXT,"
+ " remote TEXT,"
+ " connected_at INTEGER,"
+ " disconnected_at INTEGER,"
+ " active INTEGER"
+ ");"
+ "CREATE TABLE IF NOT EXISTS meta ("
+ " key TEXT PRIMARY KEY,"
+ " value INTEGER NOT NULL"
+ ");";
+
+ return sql_exec(db, schema);
+}
+
+static hybbx_result_t sql_init_mail_schema(sqlite3 *db)
+{
+ static const char *schema =
+ "CREATE TABLE IF NOT EXISTS messages ("
+ " id INTEGER PRIMARY KEY,"
+ " owner TEXT NOT NULL,"
+ " from_user TEXT NOT NULL,"
+ " subject TEXT,"
+ " body TEXT,"
+ " received_at INTEGER NOT NULL,"
+ " read_flag INTEGER NOT NULL DEFAULT 0,"
+ " deleted_at INTEGER,"
+ " folder INTEGER NOT NULL DEFAULT 0"
+ ");"
+ "CREATE INDEX IF NOT EXISTS idx_mail_owner ON messages(owner, folder, received_at DESC);"
+ "CREATE TABLE IF NOT EXISTS meta ("
+ " key TEXT PRIMARY KEY,"
+ " value INTEGER NOT NULL"
+ ");";
+
+ return sql_exec(db, schema);
+}
+
+static void sql_row_to_user(sqlite3_stmt *stmt, hybbx_user_record_t *user)
+{
+ memset(user, 0, sizeof(*user));
+ user->id = (uint64_t)sqlite3_column_int64(stmt, 0);
+ hybbx_strlcpy(user->username, (const char *)sqlite3_column_text(stmt, 1),
+ sizeof(user->username));
+ if (sqlite3_column_text(stmt, 2) != NULL) {
+ hybbx_strlcpy(user->nickname, (const char *)sqlite3_column_text(stmt, 2),
+ sizeof(user->nickname));
+ }
+ user->level = (hybbx_user_level_t)sqlite3_column_int(stmt, 3);
+ user->active = sqlite3_column_int(stmt, 4);
+ user->created_at = (time_t)sqlite3_column_int64(stmt, 5);
+ if (sqlite3_column_text(stmt, 6) != NULL) {
+ hybbx_strlcpy(user->full_name, (const char *)sqlite3_column_text(stmt, 6),
+ sizeof(user->full_name));
+ }
+ if (sqlite3_column_text(stmt, 7) != NULL) {
+ hybbx_strlcpy(user->country, (const char *)sqlite3_column_text(stmt, 7),
+ sizeof(user->country));
+ }
+ if (sqlite3_column_text(stmt, 8) != NULL) {
+ hybbx_strlcpy(user->location, (const char *)sqlite3_column_text(stmt, 8),
+ sizeof(user->location));
+ }
+ if (sqlite3_column_text(stmt, 9) != NULL) {
+ hybbx_strlcpy(user->email, (const char *)sqlite3_column_text(stmt, 9),
+ sizeof(user->email));
+ }
+ if (sqlite3_column_text(stmt, 10) != NULL) {
+ hybbx_strlcpy(user->password, (const char *)sqlite3_column_text(stmt, 10),
+ sizeof(user->password));
+ }
+ user->last_login_at = (time_t)sqlite3_column_int64(stmt, 11);
+}
+
+static hybbx_result_t sql_ensure_default_sysop(hybbx_storage_t *storage)
+{
+ struct sql_state *state = storage->backend_data;
+ size_t sysop_count = 0;
+ hybbx_user_record_t sysop;
+ char plain_password[HYBBX_PASSWORD_MAX_LEN + 1];
+ uint64_t user_id;
+ sqlite3_stmt *stmt;
+ int rc;
+ hybbx_result_t hres;
+
+ hres = hybbx_storage_sql_count_level(storage, HYBBX_LEVEL_SYSOP, &sysop_count);
+ if (hres != HYBBX_OK) {
+ return hres;
+ }
+ if (sysop_count > 0) {
+ return HYBBX_OK;
+ }
+
+ hres = sql_meta_bump(state->users_db, "user_next", &user_id);
+ if (hres != HYBBX_OK) {
+ return hres;
+ }
+
+ memset(&sysop, 0, sizeof(sysop));
+ sysop.id = user_id;
+ hybbx_strlcpy(sysop.username, "sysop", sizeof(sysop.username));
+ hybbx_strlcpy(sysop.nickname, HYBBX_DEFAULT_SYSOP_USERNAME,
+ sizeof(sysop.nickname));
+ sysop.level = HYBBX_LEVEL_SYSOP;
+ sysop.active = 1;
+ sysop.created_at = time(NULL);
+ hybbx_strlcpy(sysop.full_name, "System Operator", sizeof(sysop.full_name));
+
+ if (hybbx_password_generate_alnum(plain_password, sizeof(plain_password),
+ HYBBX_SYSOP_INIT_PASSWORD_MIN,
+ HYBBX_SYSOP_INIT_PASSWORD_MAX) != HYBBX_OK) {
+ return HYBBX_ERR_IO;
+ }
+ if (hybbx_password_hash(plain_password, sysop.password,
+ sizeof(sysop.password)) != HYBBX_OK) {
+ return HYBBX_ERR_IO;
+ }
+
+ rc = sqlite3_prepare_v2(state->users_db,
+ "INSERT INTO users(id,username,nickname,level,active,"
+ "created_at,full_name,country,location,email,password,"
+ "last_login_at) VALUES(?1,?2,?3,?4,?5,?6,?7,'','','',?8,0);",
+ -1, &stmt, NULL);
+ if (rc != SQLITE_OK) {
+ return HYBBX_ERR_IO;
+ }
+
+ sqlite3_bind_int64(stmt, 1, (sqlite3_int64)sysop.id);
+ sqlite3_bind_text(stmt, 2, sysop.username, -1, SQLITE_STATIC);
+ sqlite3_bind_text(stmt, 3, sysop.nickname, -1, SQLITE_STATIC);
+ sqlite3_bind_int(stmt, 4, (int)sysop.level);
+ sqlite3_bind_int(stmt, 5, sysop.active);
+ sqlite3_bind_int64(stmt, 6, (sqlite3_int64)sysop.created_at);
+ sqlite3_bind_text(stmt, 7, sysop.full_name, -1, SQLITE_STATIC);
+ sqlite3_bind_text(stmt, 8, sysop.password, -1, SQLITE_STATIC);
+ rc = sqlite3_step(stmt);
+ sqlite3_finalize(stmt);
+ if (rc != SQLITE_DONE) {
+ return HYBBX_ERR_IO;
+ }
+
+ hybbx_log_info("[storage] created default Sysop in %s (login: %s / %s)",
+ storage->sql_cfg.user_db, sysop.nickname, plain_password);
+ hybbx_security_log_write("sysop_created user=%s", sysop.nickname);
+ return HYBBX_OK;
+}
+
+static hybbx_result_t sql_copy_file(const char *src, const char *dst)
+{
+ FILE *in_fp;
+ FILE *out_fp;
+ unsigned char buf[4096];
+ size_t n;
+
+ in_fp = fopen(src, "rb");
+ if (in_fp == NULL) {
+ return HYBBX_ERR_IO;
+ }
+
+ out_fp = fopen(dst, "wb");
+ if (out_fp == NULL) {
+ fclose(in_fp);
+ return HYBBX_ERR_IO;
+ }
+
+ while ((n = fread(buf, 1, sizeof(buf), in_fp)) > 0) {
+ if (fwrite(buf, 1, n, out_fp) != n) {
+ fclose(in_fp);
+ fclose(out_fp);
+ return HYBBX_ERR_IO;
+ }
+ }
+
+ fclose(in_fp);
+ if (fclose(out_fp) != 0) {
+ return HYBBX_ERR_IO;
+ }
+
+ return HYBBX_OK;
+}
+
+static void sql_backup_one(sqlite3 *db, const char *src_path,
+ const char *backup_dir)
+{
+ char dst[HYBBX_PATH_MAX];
+ const char *base;
+ hybbx_result_t rc;
+
+ if (db == NULL || src_path == NULL || src_path[0] == '\0') {
+ return;
+ }
+
+ (void)sqlite3_wal_checkpoint_v2(db, NULL, SQLITE_CHECKPOINT_TRUNCATE, NULL, NULL);
+
+ base = strrchr(src_path, '/');
+ base = (base != NULL) ? base + 1 : src_path;
+
+ if (backup_dir != NULL && backup_dir[0] != '\0') {
+ char name[HYBBX_PATH_MAX];
+
+ if (mkdir_p(backup_dir) != 0) {
+ return;
+ }
+ if (strlen(base) + strlen(HYBBX_STORAGE_BACKUP_SUFFIX) + 1 >=
+ sizeof(name)) {
+ return;
+ }
+ snprintf(name, sizeof(name), "%s%s", base, HYBBX_STORAGE_BACKUP_SUFFIX);
+ if (hybbx_path_join(dst, sizeof(dst), backup_dir, name) != HYBBX_OK) {
+ return;
+ }
+ } else {
+ if (strlen(src_path) + strlen(HYBBX_STORAGE_BACKUP_SUFFIX) + 1 >=
+ sizeof(dst)) {
+ return;
+ }
+ snprintf(dst, sizeof(dst), "%s%s", src_path, HYBBX_STORAGE_BACKUP_SUFFIX);
+ }
+
+ rc = sql_copy_file(src_path, dst);
+ if (rc != HYBBX_OK) {
+ hybbx_log_warn("[storage] backup failed %s -> %s", src_path, dst);
+ }
+}
+
+hybbx_result_t hybbx_storage_sql_open(hybbx_storage_t *storage)
+{
+ struct sql_state *state;
+ hybbx_result_t rc;
+
+ if (storage == NULL || storage->path == NULL) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ if (storage->backend == HYBBX_STORAGE_MYSQL ||
+ storage->backend == HYBBX_STORAGE_MARIADB) {
+ hybbx_log_warn("[storage] MySQL/MariaDB backends are not supported — "
+ "use flatfile or sqlite");
+ return HYBBX_ERR_UNSUPPORTED;
+ }
+
+ if (mkdir_p(storage->path) != 0) {
+ hybbx_log_warn("[storage] cannot create data path '%s'",
+ storage->path);
+ return HYBBX_ERR_IO;
+ }
+
+ state = calloc(1, sizeof(*state));
+ if (state == NULL) {
+ return HYBBX_ERR_NOMEM;
+ }
+
+ rc = sql_open_db(storage->sql_cfg.user_db, &state->users_db);
+ if (rc != HYBBX_OK) {
+ free(state);
+ return rc;
+ }
+
+ rc = sql_init_users_schema(state->users_db);
+ if (rc != HYBBX_OK) {
+ hybbx_storage_sql_close(storage);
+ free(state);
+ return rc;
+ }
+
+ rc = sql_open_db(storage->sql_cfg.mail_db, &state->mail_db);
+ if (rc != HYBBX_OK) {
+ sqlite3_close(state->users_db);
+ free(state);
+ return rc;
+ }
+
+ rc = sql_init_mail_schema(state->mail_db);
+ if (rc != HYBBX_OK) {
+ sqlite3_close(state->users_db);
+ sqlite3_close(state->mail_db);
+ free(state);
+ return rc;
+ }
+
+ storage->backend_data = state;
+
+ rc = sql_ensure_default_sysop(storage);
+ if (rc != HYBBX_OK) {
+ hybbx_storage_sql_close(storage);
+ return rc;
+ }
+
+ hybbx_log_info("[storage] sqlite user_db=%s mail_db=%s backup_interval=%us",
+ storage->sql_cfg.user_db, storage->sql_cfg.mail_db,
+ storage->sql_cfg.backup_interval_sec);
+
+ return HYBBX_OK;
+}
+
+void hybbx_storage_sql_close(hybbx_storage_t *storage)
+{
+ struct sql_state *state;
+
+ if (storage == NULL) {
+ return;
+ }
+
+ state = storage->backend_data;
+ if (state == NULL) {
+ return;
+ }
+
+ if (state->users_db != NULL) {
+ sqlite3_close(state->users_db);
+ }
+ if (state->mail_db != NULL) {
+ sqlite3_close(state->mail_db);
+ }
+
+ free(state);
+ storage->backend_data = NULL;
+}
+
+sqlite3 *hybbx_storage_sql_mail_db(hybbx_storage_t *storage)
+{
+ struct sql_state *state;
+
+ if (storage == NULL) {
+ return NULL;
+ }
+
+ state = storage->backend_data;
+ if (state == NULL) {
+ return NULL;
+ }
+
+ return state->mail_db;
+}
+
+static hybbx_result_t sql_find_user_by_column(sqlite3 *db, const char *column,
+ const char *name,
+ hybbx_user_record_t *out)
+{
+ sqlite3_stmt *stmt;
+ int rc;
+ const char *sql_username =
+ "SELECT id,username,nickname,level,active,created_at,full_name,"
+ "country,location,email,password,last_login_at "
+ "FROM users WHERE lower(username)=lower(?1) LIMIT 1;";
+ const char *sql_nickname =
+ "SELECT id,username,nickname,level,active,created_at,full_name,"
+ "country,location,email,password,last_login_at "
+ "FROM users WHERE lower(nickname)=lower(?1) LIMIT 1;";
+ const char *sql = sql_str_ieq(column, "nickname") ? sql_nickname :
+ sql_username;
+
+ rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
+ if (rc != SQLITE_OK) {
+ return HYBBX_ERR_IO;
+ }
+
+ sqlite3_bind_text(stmt, 1, name, -1, SQLITE_STATIC);
+ rc = sqlite3_step(stmt);
+ if (rc == SQLITE_ROW) {
+ sql_row_to_user(stmt, out);
+ sqlite3_finalize(stmt);
+ return HYBBX_OK;
+ }
+
+ sqlite3_finalize(stmt);
+ return HYBBX_ERR_NOT_FOUND;
+}
+
+hybbx_result_t hybbx_storage_sql_find_user(hybbx_storage_t *storage,
+ const char *username,
+ hybbx_user_record_t *out)
+{
+ struct sql_state *state;
+ char normalized[HYBBX_USER_NAME_MAX];
+
+ if (storage == NULL || username == NULL || out == NULL) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ state = storage->backend_data;
+ if (state == NULL || state->users_db == NULL) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ hybbx_strlcpy(normalized, username, sizeof(normalized));
+ hybbx_username_normalize(normalized);
+ return sql_find_user_by_column(state->users_db, "username", normalized, out);
+}
+
+hybbx_result_t hybbx_storage_sql_resolve_user(hybbx_storage_t *storage,
+ const char *name,
+ hybbx_user_record_t *out)
+{
+ hybbx_result_t rc;
+
+ if (storage == NULL || name == NULL || out == NULL || name[0] == '\0') {
+ return HYBBX_ERR_INVALID;
+ }
+
+ rc = hybbx_storage_sql_find_user(storage, name, out);
+ if (rc == HYBBX_OK) {
+ return HYBBX_OK;
+ }
+ if (rc != HYBBX_ERR_NOT_FOUND) {
+ return rc;
+ }
+
+ return sql_find_user_by_column(
+ ((struct sql_state *)storage->backend_data)->users_db,
+ "nickname", name, out);
+}
+
+typedef struct foreach_ctx {
+ hybbx_storage_user_fn fn;
+ void *user_ctx;
+} foreach_ctx_t;
+
+static int sql_foreach_cb(void *ctx, int n_cols, char **values, char **cols)
+{
+ hybbx_user_record_t user;
+ foreach_ctx_t *fctx = (foreach_ctx_t *)ctx;
+ hybbx_result_t rc;
+ int i;
+
+ (void)n_cols;
+ (void)cols;
+ memset(&user, 0, sizeof(user));
+
+ for (i = 0; values[i] != NULL; i++) {
+ switch (i) {
+ case 0: user.id = (uint64_t)strtoull(values[i], NULL, 10); break;
+ case 1: hybbx_strlcpy(user.username, values[i], sizeof(user.username)); break;
+ case 2: hybbx_strlcpy(user.nickname, values[i], sizeof(user.nickname)); break;
+ case 3: user.level = (hybbx_user_level_t)atoi(values[i]); break;
+ case 4: user.active = atoi(values[i]); break;
+ case 5: user.created_at = (time_t)strtoll(values[i], NULL, 10); break;
+ case 6: hybbx_strlcpy(user.full_name, values[i], sizeof(user.full_name)); break;
+ case 7: hybbx_strlcpy(user.country, values[i], sizeof(user.country)); break;
+ case 8: hybbx_strlcpy(user.location, values[i], sizeof(user.location)); break;
+ case 9: hybbx_strlcpy(user.email, values[i], sizeof(user.email)); break;
+ case 10: hybbx_strlcpy(user.password, values[i], sizeof(user.password)); break;
+ case 11: user.last_login_at = (time_t)strtoll(values[i], NULL, 10); break;
+ default: break;
+ }
+ }
+
+ rc = fctx->fn(&user, fctx->user_ctx);
+ return (rc == HYBBX_OK) ? 0 : 1;
+}
+
+hybbx_result_t hybbx_storage_sql_foreach_user(hybbx_storage_t *storage,
+ hybbx_storage_user_fn fn,
+ void *ctx)
+{
+ struct sql_state *state;
+ foreach_ctx_t fctx;
+ char *err = NULL;
+ int rc;
+
+ if (storage == NULL || fn == NULL) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ state = storage->backend_data;
+ if (state == NULL || state->users_db == NULL) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ fctx.fn = fn;
+ fctx.user_ctx = ctx;
+
+ rc = sqlite3_exec(state->users_db,
+ "SELECT id,username,nickname,level,active,created_at,"
+ "full_name,country,location,email,password,last_login_at "
+ "FROM users ORDER BY id;",
+ sql_foreach_cb, &fctx, &err);
+ if (rc == SQLITE_ABORT) {
+ sqlite3_free(err);
+ return HYBBX_ERR_BUSY;
+ }
+ if (rc != SQLITE_OK) {
+ hybbx_log_warn("[storage] foreach: %s", err != NULL ? err : "error");
+ sqlite3_free(err);
+ return HYBBX_ERR_IO;
+ }
+
+ return HYBBX_OK;
+}
+
+hybbx_result_t hybbx_storage_sql_count_level(hybbx_storage_t *storage,
+ hybbx_user_level_t level,
+ size_t *count)
+{
+ struct sql_state *state;
+ sqlite3_stmt *stmt;
+ int rc;
+
+ if (storage == NULL || count == NULL) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ *count = 0;
+ state = storage->backend_data;
+ if (state == NULL || state->users_db == NULL) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ rc = sqlite3_prepare_v2(state->users_db,
+ "SELECT COUNT(*) FROM users WHERE level=?1;",
+ -1, &stmt, NULL);
+ if (rc != SQLITE_OK) {
+ return HYBBX_ERR_IO;
+ }
+
+ sqlite3_bind_int(stmt, 1, (int)level);
+ rc = sqlite3_step(stmt);
+ if (rc == SQLITE_ROW) {
+ *count = (size_t)sqlite3_column_int64(stmt, 0);
+ }
+ sqlite3_finalize(stmt);
+ return HYBBX_OK;
+}
+
+typedef struct identity_taken_ctx {
+ const char *username;
+ const char *nickname;
+ int taken;
+} identity_taken_ctx_t;
+
+static hybbx_result_t identity_taken_cb(const hybbx_user_record_t *user, void *ctx)
+{
+ identity_taken_ctx_t *ictx = (identity_taken_ctx_t *)ctx;
+
+ if (sql_str_ieq(user->username, ictx->username) ||
+ sql_str_ieq(user->username, ictx->nickname) ||
+ (user->nickname[0] != '\0' &&
+ (sql_str_ieq(user->nickname, ictx->nickname) ||
+ sql_str_ieq(user->nickname, ictx->username)))) {
+ ictx->taken = 1;
+ return HYBBX_ERR_BUSY;
+ }
+
+ return HYBBX_OK;
+}
+
+hybbx_result_t hybbx_storage_sql_register_user(hybbx_storage_t *storage,
+ const hybbx_user_registration_t *reg,
+ hybbx_user_record_t *out)
+{
+ struct sql_state *state;
+ hybbx_user_record_t existing;
+ identity_taken_ctx_t taken;
+ uint64_t user_id;
+ sqlite3_stmt *stmt;
+ int rc;
+ hybbx_result_t hres;
+
+ if (storage == NULL || reg == NULL || out == NULL) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ if (!hybbx_registration_valid(reg, storage->guest_prefix)) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ state = storage->backend_data;
+ if (state == NULL || state->users_db == NULL) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ hres = hybbx_storage_sql_find_user(storage, reg->username, &existing);
+ if (hres == HYBBX_OK) {
+ return HYBBX_ERR_BUSY;
+ }
+ if (hres != HYBBX_ERR_NOT_FOUND) {
+ return hres;
+ }
+
+ memset(&taken, 0, sizeof(taken));
+ taken.username = reg->username;
+ taken.nickname = reg->nickname;
+ hres = hybbx_storage_sql_foreach_user(storage, identity_taken_cb, &taken);
+ if (hres == HYBBX_ERR_BUSY || taken.taken) {
+ return HYBBX_ERR_BUSY;
+ }
+ if (hres != HYBBX_OK) {
+ return hres;
+ }
+
+ hres = sql_meta_bump(state->users_db, "user_next", &user_id);
+ if (hres != HYBBX_OK) {
+ return hres;
+ }
+
+ memset(out, 0, sizeof(*out));
+ out->id = user_id;
+ hybbx_strlcpy(out->username, reg->username, sizeof(out->username));
+ hybbx_username_normalize(out->username);
+ hybbx_strlcpy(out->nickname, reg->nickname, sizeof(out->nickname));
+ out->level = HYBBX_LEVEL_USER;
+ out->active = 0;
+ out->created_at = time(NULL);
+ hybbx_strlcpy(out->full_name, reg->full_name, sizeof(out->full_name));
+ hybbx_strlcpy(out->country, reg->country, sizeof(out->country));
+ hybbx_strlcpy(out->location, reg->location, sizeof(out->location));
+ hybbx_strlcpy(out->email, reg->email, sizeof(out->email));
+
+ if (reg->password[0] != '\0') {
+ if (!hybbx_password_plain_valid(reg->password)) {
+ return HYBBX_ERR_INVALID;
+ }
+ hres = hybbx_password_hash(reg->password, out->password,
+ sizeof(out->password));
+ if (hres != HYBBX_OK) {
+ return hres;
+ }
+ }
+
+ rc = sqlite3_prepare_v2(state->users_db,
+ "INSERT INTO users(id,username,nickname,level,active,"
+ "created_at,full_name,country,location,email,password,"
+ "last_login_at) VALUES(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,0);",
+ -1, &stmt, NULL);
+ if (rc != SQLITE_OK) {
+ return HYBBX_ERR_IO;
+ }
+
+ sqlite3_bind_int64(stmt, 1, (sqlite3_int64)out->id);
+ sqlite3_bind_text(stmt, 2, out->username, -1, SQLITE_STATIC);
+ sqlite3_bind_text(stmt, 3, out->nickname, -1, SQLITE_STATIC);
+ sqlite3_bind_int(stmt, 4, (int)out->level);
+ sqlite3_bind_int(stmt, 5, out->active);
+ sqlite3_bind_int64(stmt, 6, (sqlite3_int64)out->created_at);
+ sqlite3_bind_text(stmt, 7, out->full_name, -1, SQLITE_STATIC);
+ sqlite3_bind_text(stmt, 8, out->country, -1, SQLITE_STATIC);
+ sqlite3_bind_text(stmt, 9, out->location, -1, SQLITE_STATIC);
+ sqlite3_bind_text(stmt, 10, out->email, -1, SQLITE_STATIC);
+ sqlite3_bind_text(stmt, 11, out->password, -1, SQLITE_STATIC);
+ rc = sqlite3_step(stmt);
+ sqlite3_finalize(stmt);
+
+ return (rc == SQLITE_DONE) ? HYBBX_OK : HYBBX_ERR_IO;
+}
+
+hybbx_result_t hybbx_storage_sql_update_user(hybbx_storage_t *storage,
+ const hybbx_user_record_t *user)
+{
+ struct sql_state *state;
+ sqlite3_stmt *stmt;
+ int rc;
+
+ if (storage == NULL || user == NULL || user->id == 0) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ state = storage->backend_data;
+ if (state == NULL || state->users_db == NULL) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ rc = sqlite3_prepare_v2(state->users_db,
+ "UPDATE users SET username=?2,nickname=?3,level=?4,"
+ "active=?5,created_at=?6,full_name=?7,country=?8,"
+ "location=?9,email=?10,password=?11,last_login_at=?12 "
+ "WHERE id=?1;",
+ -1, &stmt, NULL);
+ if (rc != SQLITE_OK) {
+ return HYBBX_ERR_IO;
+ }
+
+ sqlite3_bind_int64(stmt, 1, (sqlite3_int64)user->id);
+ sqlite3_bind_text(stmt, 2, user->username, -1, SQLITE_STATIC);
+ sqlite3_bind_text(stmt, 3, user->nickname, -1, SQLITE_STATIC);
+ sqlite3_bind_int(stmt, 4, (int)user->level);
+ sqlite3_bind_int(stmt, 5, user->active);
+ sqlite3_bind_int64(stmt, 6, (sqlite3_int64)user->created_at);
+ sqlite3_bind_text(stmt, 7, user->full_name, -1, SQLITE_STATIC);
+ sqlite3_bind_text(stmt, 8, user->country, -1, SQLITE_STATIC);
+ sqlite3_bind_text(stmt, 9, user->location, -1, SQLITE_STATIC);
+ sqlite3_bind_text(stmt, 10, user->email, -1, SQLITE_STATIC);
+ sqlite3_bind_text(stmt, 11, user->password, -1, SQLITE_STATIC);
+ sqlite3_bind_int64(stmt, 12, (sqlite3_int64)user->last_login_at);
+ rc = sqlite3_step(stmt);
+ sqlite3_finalize(stmt);
+
+ if (rc != SQLITE_DONE) {
+ return HYBBX_ERR_IO;
+ }
+ if (sqlite3_changes(state->users_db) == 0) {
+ return HYBBX_ERR_NOT_FOUND;
+ }
+
+ return HYBBX_OK;
+}
+
+hybbx_result_t hybbx_storage_sql_delete_user(hybbx_storage_t *storage,
+ uint64_t user_id)
+{
+ struct sql_state *state;
+ sqlite3_stmt *stmt;
+ int rc;
+
+ if (storage == NULL || user_id == 0) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ state = storage->backend_data;
+ if (state == NULL || state->users_db == NULL) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ rc = sqlite3_prepare_v2(state->users_db,
+ "DELETE FROM users WHERE id=?1;",
+ -1, &stmt, NULL);
+ if (rc != SQLITE_OK) {
+ return HYBBX_ERR_IO;
+ }
+
+ sqlite3_bind_int64(stmt, 1, (sqlite3_int64)user_id);
+ rc = sqlite3_step(stmt);
+ sqlite3_finalize(stmt);
+
+ if (rc != SQLITE_DONE) {
+ return HYBBX_ERR_IO;
+ }
+ if (sqlite3_changes(state->users_db) == 0) {
+ return HYBBX_ERR_NOT_FOUND;
+ }
+
+ return HYBBX_OK;
+}
+
+hybbx_result_t hybbx_storage_sql_session_begin(hybbx_storage_t *storage,
+ const hybbx_user_record_t *user,
+ const char *transport,
+ hybbx_session_record_t *out)
+{
+ struct sql_state *state;
+ sqlite3_stmt *stmt;
+ int rc;
+ hybbx_result_t hres;
+
+ if (storage == NULL || user == NULL || transport == NULL || out == NULL) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ state = storage->backend_data;
+ if (state == NULL || state->users_db == NULL) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ memset(out, 0, sizeof(*out));
+ hres = sql_meta_bump(state->users_db, "session_next", &out->session_id);
+ if (hres != HYBBX_OK) {
+ return hres;
+ }
+
+ out->user_id = user->id;
+ hybbx_strlcpy(out->username, user->username, sizeof(out->username));
+ hybbx_strlcpy(out->transport, transport, sizeof(out->transport));
+ out->connected_at = time(NULL);
+ out->active = 1;
+
+ rc = sqlite3_prepare_v2(state->users_db,
+ "INSERT INTO sessions(session_id,user_id,username,"
+ "transport,remote,connected_at,disconnected_at,active) "
+ "VALUES(?1,?2,?3,?4,'',?5,0,1);",
+ -1, &stmt, NULL);
+ if (rc != SQLITE_OK) {
+ return HYBBX_ERR_IO;
+ }
+
+ sqlite3_bind_int64(stmt, 1, (sqlite3_int64)out->session_id);
+ sqlite3_bind_int64(stmt, 2, (sqlite3_int64)out->user_id);
+ sqlite3_bind_text(stmt, 3, out->username, -1, SQLITE_STATIC);
+ sqlite3_bind_text(stmt, 4, out->transport, -1, SQLITE_STATIC);
+ sqlite3_bind_int64(stmt, 5, (sqlite3_int64)out->connected_at);
+ rc = sqlite3_step(stmt);
+ sqlite3_finalize(stmt);
+
+ return (rc == SQLITE_DONE) ? HYBBX_OK : HYBBX_ERR_IO;
+}
+
+hybbx_result_t hybbx_storage_sql_session_end(hybbx_storage_t *storage,
+ uint64_t session_id)
+{
+ struct sql_state *state;
+ sqlite3_stmt *stmt;
+ int rc;
+ time_t now = time(NULL);
+
+ if (storage == NULL || session_id == 0) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ state = storage->backend_data;
+ if (state == NULL || state->users_db == NULL) {
+ return HYBBX_ERR_INVALID;
+ }
+
+ rc = sqlite3_prepare_v2(state->users_db,
+ "UPDATE sessions SET disconnected_at=?2, active=0 "
+ "WHERE session_id=?1;",
+ -1, &stmt, NULL);
+ if (rc != SQLITE_OK) {
+ return HYBBX_ERR_IO;
+ }
+
+ sqlite3_bind_int64(stmt, 1, (sqlite3_int64)session_id);
+ sqlite3_bind_int64(stmt, 2, (sqlite3_int64)now);
+ rc = sqlite3_step(stmt);
+ sqlite3_finalize(stmt);
+
+ if (rc != SQLITE_DONE) {
+ return HYBBX_ERR_IO;
+ }
+
+ if (sqlite3_changes(state->users_db) == 0) {
+ rc = sqlite3_prepare_v2(state->users_db,
+ "INSERT INTO sessions(session_id,user_id,username,"
+ "transport,remote,connected_at,disconnected_at,"
+ "active) VALUES(?1,0,'','','',0,?2,0);",
+ -1, &stmt, NULL);
+ if (rc != SQLITE_OK) {
+ return HYBBX_ERR_IO;
+ }
+ sqlite3_bind_int64(stmt, 1, (sqlite3_int64)session_id);
+ sqlite3_bind_int64(stmt, 2, (sqlite3_int64)now);
+ rc = sqlite3_step(stmt);
+ sqlite3_finalize(stmt);
+ if (rc != SQLITE_DONE) {
+ return HYBBX_ERR_IO;
+ }
+ }
+
+ return HYBBX_OK;
+}
+
+void hybbx_storage_sql_backup_files(const hybbx_storage_t *storage)
+{
+ struct sql_state *state;
+ const char *backup_dir;
+
+ if (storage == NULL || storage->backend != HYBBX_STORAGE_SQLITE) {
+ return;
+ }
+
+ state = storage->backend_data;
+ if (state == NULL) {
+ return;
+ }
+
+ backup_dir = storage->sql_cfg.backup_path;
+ if (backup_dir != NULL && backup_dir[0] == '\0') {
+ backup_dir = NULL;
+ }
+
+ sql_backup_one(state->users_db, storage->sql_cfg.user_db, backup_dir);
+ sql_backup_one(state->mail_db, storage->sql_cfg.mail_db, backup_dir);
+}
+
+void hybbx_storage_sql_backup_tick(hybbx_storage_t *storage)
+{
+ struct sql_state *state;
+
+ if (storage == NULL || storage->backend != HYBBX_STORAGE_SQLITE) {
+ return;
+ }
+
+ state = storage->backend_data;
+ if (state == NULL || storage->sql_cfg.backup_interval_sec == 0) {
+ return;
+ }
+
+ state->backup_tick++;
+ if (state->backup_tick >= storage->sql_cfg.backup_interval_sec) {
+ state->backup_tick = 0;
+ hybbx_storage_sql_backup_files(storage);
+ }
+}
+
+#endif /* HYBBX_HAVE_SQLITE */
git clone -b <branch> https://cgit.mode42.com/<repo>.git
git clone -b <branch> git://cgit.mode42.com/<repo>.git


info@mode42.com