jeka
/
utun2
1
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
821 Commits
15 Branches
2 Tags
169 MiB
 
 
 
 
 
 
Branch: feature/x25519-migration
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'feature/x25519-migration'
${ noResults }
utun2/tools/chatgui/db/db_manager.cpp

645 lines
28 KiB
Raw Permalink Blame History

#include "db_manager.h"
#include "../src/debug_ui.h"
#include <openssl/sha.h>
#include <openssl/evp.h>
#include <cstring>
DbManager::DbManager(const QString& path, QObject* parent)
: QObject(parent)
{
int rc = sqlite3_open(path.toUtf8().constData(), &m_db);
if (rc != SQLITE_OK) {
GUI_ERROR("Cannot open database: %s", sqlite3_errmsg(m_db));
sqlite3_close(m_db);
m_db = nullptr;
return;
}
sqlite3_exec(m_db, "PRAGMA journal_mode=WAL", nullptr, nullptr, nullptr);
sqlite3_exec(m_db, "PRAGMA foreign_keys=ON", nullptr, nullptr, nullptr);
m_myNodeId = 100;
initTables();
if (getChannels().isEmpty()) seedStubData();
GUI_INFO("Database opened: %s (my_node_id=%llu)", path.toUtf8().constData(),
static_cast<unsigned long long>(m_myNodeId));
}
DbManager::~DbManager() {
if (m_db) { sqlite3_close(m_db); m_db = nullptr; }
}
/* ======================================================================== */
sqlite3_stmt* DbManager::prepareOrNull(const char* sql) const {
sqlite3_stmt* stmt = nullptr;
if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) != SQLITE_OK) {
GUI_ERROR("prepare failed: %s — %s", sql, sqlite3_errmsg(m_db));
return nullptr;
}
return stmt;
}
QString DbManager::colText(sqlite3_stmt* stmt, int col) {
return QString::fromUtf8(
reinterpret_cast<const char*>(sqlite3_column_text(stmt, col)));
}
QByteArray DbManager::colBlob(sqlite3_stmt* stmt, int col) {
int len = sqlite3_column_bytes(stmt, col);
if (len <= 0) return {};
return QByteArray(static_cast<const char*>(sqlite3_column_blob(stmt, col)), len);
}
/* ======================================================================== */
void DbManager::initTables() {
const char* sql =
"CREATE TABLE IF NOT EXISTS local_identity ("
" id INTEGER PRIMARY KEY CHECK (id = 1),"
" node_id INTEGER NOT NULL UNIQUE,"
" name TEXT NOT NULL,"
" x25519_pubkey BLOB NOT NULL,"
" x25519_privkey BLOB,"
" ed25519_pubkey BLOB,"
" created_at INTEGER DEFAULT (unixepoch()),"
" updated_at INTEGER DEFAULT (unixepoch())"
");"
"CREATE TABLE IF NOT EXISTS my_nodes ("
" node_id INTEGER PRIMARY KEY,"
" name TEXT NOT NULL,"
" x25519_pubkey BLOB NOT NULL,"
" ed25519_pubkey BLOB,"
" created_at INTEGER DEFAULT (unixepoch()),"
" updated_at INTEGER DEFAULT (unixepoch())"
");"
"CREATE TABLE IF NOT EXISTS nodes ("
" node_id INTEGER PRIMARY KEY,"
" name TEXT,"
" x25519_pubkey BLOB NOT NULL,"
" ed25519_pubkey BLOB,"
" is_online INTEGER DEFAULT 0,"
" last_seen_at INTEGER DEFAULT (unixepoch()),"
" created_at INTEGER DEFAULT (unixepoch()),"
" updated_at INTEGER DEFAULT (unixepoch())"
");"
"CREATE TABLE IF NOT EXISTS accounts ("
" node_id INTEGER PRIMARY KEY REFERENCES nodes(node_id),"
" display_name TEXT NOT NULL,"
" avatar_color TEXT DEFAULT '#4A90E2',"
" avatar_letter TEXT NOT NULL,"
" is_contact INTEGER DEFAULT 1,"
" created_at INTEGER DEFAULT (unixepoch())"
");"
"CREATE TABLE IF NOT EXISTS channels ("
" id INTEGER PRIMARY KEY AUTOINCREMENT,"
" channel_id TEXT NOT NULL UNIQUE,"
" name TEXT NOT NULL,"
" owner_node_id INTEGER,"
" is_dm INTEGER DEFAULT 0,"
" last_message TEXT,"
" last_msg_at INTEGER,"
" created_at INTEGER DEFAULT (unixepoch())"
");"
"CREATE UNIQUE INDEX IF NOT EXISTS idx_channel_cid ON channels(channel_id);"
"CREATE TABLE IF NOT EXISTS channel_members ("
" channel_id TEXT NOT NULL REFERENCES channels(channel_id) ON DELETE CASCADE,"
" node_id INTEGER NOT NULL,"
" joined_at INTEGER DEFAULT (unixepoch()),"
" PRIMARY KEY (channel_id, node_id)"
");"
"CREATE INDEX IF NOT EXISTS idx_cm_node ON channel_members(node_id);"
"CREATE TABLE IF NOT EXISTS messages ("
" id INTEGER PRIMARY KEY AUTOINCREMENT,"
" channel_id TEXT NOT NULL REFERENCES channels(channel_id) ON DELETE CASCADE,"
" author_node_id INTEGER NOT NULL,"
" content TEXT NOT NULL CHECK(length(content) <= 4096),"
" content_type TEXT DEFAULT 'text/plain',"
" timestamp INTEGER NOT NULL,"
" signature BLOB NOT NULL CHECK(length(signature) == 64),"
" is_outgoing INTEGER DEFAULT 0,"
" is_read INTEGER DEFAULT 0,"
" created_at INTEGER DEFAULT (unixepoch())"
");"
"CREATE INDEX IF NOT EXISTS idx_msg_channel_time ON messages(channel_id, timestamp);"
"CREATE INDEX IF NOT EXISTS idx_msg_author ON messages(author_node_id);"
"CREATE UNIQUE INDEX IF NOT EXISTS idx_msg_dedup "
" ON messages(channel_id, author_node_id, timestamp);"
"CREATE TABLE IF NOT EXISTS reactions ("
" message_id INTEGER NOT NULL REFERENCES messages(id) ON DELETE CASCADE,"
" node_id INTEGER NOT NULL,"
" emoji TEXT NOT NULL,"
" signature BLOB NOT NULL CHECK(length(signature) == 64),"
" created_at INTEGER DEFAULT (unixepoch()),"
" PRIMARY KEY (message_id, node_id, emoji)"
");"
"CREATE TABLE IF NOT EXISTS attachments ("
" id INTEGER PRIMARY KEY AUTOINCREMENT,"
" message_id INTEGER NOT NULL REFERENCES messages(id) ON DELETE CASCADE,"
" filename TEXT NOT NULL,"
" content_type TEXT NOT NULL,"
" size INTEGER NOT NULL,"
" sha256 BLOB NOT NULL,"
" file_path TEXT,"
" signature BLOB NOT NULL CHECK(length(signature) == 64),"
" is_upload INTEGER DEFAULT 0,"
" created_at INTEGER DEFAULT (unixepoch())"
");"
"CREATE INDEX IF NOT EXISTS idx_att_msg ON attachments(message_id);"
"CREATE TABLE IF NOT EXISTS ui_state ("
" key TEXT PRIMARY KEY,"
" value TEXT"
");"
"CREATE TABLE IF NOT EXISTS bucket_meta ("
" channel_id TEXT NOT NULL,"
" day_unix INTEGER NOT NULL,"
" hour INTEGER NOT NULL,"
" msg_count INTEGER DEFAULT 0,"
" sig_hash BLOB DEFAULT '',"
" PRIMARY KEY (channel_id, day_unix, hour)"
");"
"CREATE TABLE IF NOT EXISTS node_addresses ("
" id INTEGER PRIMARY KEY AUTOINCREMENT,"
" node_id INTEGER NOT NULL REFERENCES nodes(node_id) ON DELETE CASCADE,"
" family INTEGER NOT NULL CHECK(family IN (4, 6)),"
" address BLOB NOT NULL,"
" port INTEGER NOT NULL CHECK(port > 0 AND port <= 65535),"
" is_nat INTEGER DEFAULT 0,"
" created_at INTEGER DEFAULT (unixepoch()),"
" updated_at INTEGER DEFAULT (unixepoch())"
");"
"CREATE INDEX IF NOT EXISTS idx_na_node ON node_addresses(node_id);";
char* err = nullptr;
if (sqlite3_exec(m_db, sql, nullptr, nullptr, &err) != SQLITE_OK) {
GUI_ERROR("initTables: %s", err);
sqlite3_free(err);
} else {
GUI_INFO("Database tables initialized (11 tables)");
}
}
/* ======================================================================== */
QList<ChannelRow> DbManager::getChannels() const {
QList<ChannelRow> list;
sqlite3_stmt* stmt = prepareOrNull(
"SELECT c.channel_id, c.name, c.is_dm, c.last_message, c.last_msg_at"
" FROM channels c JOIN channel_members cm ON c.channel_id = cm.channel_id"
" WHERE cm.node_id = ? ORDER BY c.last_msg_at DESC");
if (!stmt) return list;
sqlite3_bind_int64(stmt, 1, static_cast<sqlite3_int64>(m_myNodeId));
while (sqlite3_step(stmt) == SQLITE_ROW) {
ChannelRow r;
r.channelId = colText(stmt, 0);
r.name = colText(stmt, 1);
r.isDm = sqlite3_column_int(stmt, 2) != 0;
r.lastMessage = colText(stmt, 3);
r.lastMsgAt = sqlite3_column_int64(stmt, 4);
list.append(r);
}
sqlite3_finalize(stmt);
return list;
}
QList<MessageRow> DbManager::getMessages(const QString& channelId, int limit) const {
QList<MessageRow> list;
sqlite3_stmt* stmt = prepareOrNull(
"SELECT id, author_node_id, content, content_type, timestamp,"
" signature, is_outgoing, is_read"
" FROM messages WHERE channel_id = ? ORDER BY timestamp ASC LIMIT ?");
if (!stmt) return list;
sqlite3_bind_text(stmt, 1, channelId.toUtf8().constData(), -1, SQLITE_STATIC);
sqlite3_bind_int(stmt, 2, limit);
while (sqlite3_step(stmt) == SQLITE_ROW) {
MessageRow m;
m.id = sqlite3_column_int64(stmt, 0);
m.channelId = channelId;
m.authorNodeId = static_cast<quint64>(sqlite3_column_int64(stmt, 1));
m.content = colText(stmt, 2);
m.contentType = colText(stmt, 3);
m.timestamp = sqlite3_column_int64(stmt, 4);
m.signature = colBlob(stmt, 5);
m.isOutgoing = sqlite3_column_int(stmt, 6) != 0;
m.isRead = sqlite3_column_int(stmt, 7) != 0;
list.append(m);
}
sqlite3_finalize(stmt);
return list;
}
QList<AccountRow> DbManager::getAccounts(bool contactsOnly) const {
QList<AccountRow> list;
const char* sql = contactsOnly
? "SELECT node_id, display_name, avatar_color, avatar_letter, is_contact"
" FROM accounts WHERE is_contact = 1 ORDER BY display_name"
: "SELECT node_id, display_name, avatar_color, avatar_letter, is_contact"
" FROM accounts ORDER BY display_name";
sqlite3_stmt* stmt = prepareOrNull(sql);
if (!stmt) return list;
while (sqlite3_step(stmt) == SQLITE_ROW) {
AccountRow a;
a.nodeId = static_cast<quint64>(sqlite3_column_int64(stmt, 0));
a.displayName = colText(stmt, 1);
a.avatarColor = colText(stmt, 2);
a.avatarLetter = colText(stmt, 3);
a.isContact = sqlite3_column_int(stmt, 4) != 0;
list.append(a);
}
sqlite3_finalize(stmt);
return list;
}
AccountRow DbManager::getAccount(quint64 nodeId) const {
AccountRow a;
sqlite3_stmt* stmt = prepareOrNull(
"SELECT node_id, display_name, avatar_color, avatar_letter, is_contact"
" FROM accounts WHERE node_id = ?");
if (!stmt) return a;
sqlite3_bind_int64(stmt, 1, static_cast<sqlite3_int64>(nodeId));
if (sqlite3_step(stmt) == SQLITE_ROW) {
a.nodeId = static_cast<quint64>(sqlite3_column_int64(stmt, 0));
a.displayName = colText(stmt, 1);
a.avatarColor = colText(stmt, 2);
a.avatarLetter = colText(stmt, 3);
a.isContact = sqlite3_column_int(stmt, 4) != 0;
}
sqlite3_finalize(stmt);
return a;
}
QByteArray DbManager::getNodeEdPub(quint64 nodeId) const {
sqlite3_stmt* stmt = prepareOrNull(
"SELECT ed25519_pubkey FROM nodes WHERE node_id=?");
if (!stmt) return {};
sqlite3_bind_int64(stmt, 1, static_cast<sqlite3_int64>(nodeId));
QByteArray r;
if (sqlite3_step(stmt) == SQLITE_ROW)
r = colBlob(stmt, 0);
sqlite3_finalize(stmt);
return r;
}
QList<DbManager::BucketMeta> DbManager::getBucketMeta(const QString& channelId) const {
QList<BucketMeta> list;
sqlite3_stmt* stmt = prepareOrNull(
"SELECT day_unix, hour, msg_count, sig_hash"
" FROM bucket_meta WHERE channel_id=? ORDER BY day_unix, hour");
if (!stmt) return list;
sqlite3_bind_text(stmt, 1, channelId.toUtf8().constData(), -1, SQLITE_STATIC);
while (sqlite3_step(stmt) == SQLITE_ROW) {
BucketMeta b;
b.day = sqlite3_column_int64(stmt, 0);
b.hour = sqlite3_column_int(stmt, 1);
b.count = sqlite3_column_int(stmt, 2);
b.hash = colBlob(stmt, 3);
list.append(b);
}
sqlite3_finalize(stmt);
return list;
}
void DbManager::setBucketMeta(const QString& channelId, qint64 day, int hour,
int count, const QByteArray& hash) {
sqlite3_stmt* stmt = prepareOrNull(
"INSERT OR REPLACE INTO bucket_meta(channel_id,day_unix,hour,msg_count,sig_hash)"
" VALUES(?,?,?,?,?)");
if (!stmt) return;
sqlite3_bind_text(stmt, 1, channelId.toUtf8().constData(), -1, SQLITE_STATIC);
sqlite3_bind_int64(stmt, 2, day);
sqlite3_bind_int(stmt, 3, hour);
sqlite3_bind_int(stmt, 4, count);
sqlite3_bind_blob(stmt, 5, hash.constData(), hash.size(), SQLITE_STATIC);
sqlite3_step(stmt);
sqlite3_finalize(stmt);
}
QList<QByteArray> DbManager::getMessageSigs(const QString& channelId,
qint64 day, int hour) const {
QList<QByteArray> sigs;
qint64 dayStart = day + hour * 3600;
qint64 dayEnd = dayStart + 3600;
sqlite3_stmt* stmt = prepareOrNull(
"SELECT signature FROM messages WHERE channel_id=?"
" AND timestamp/1000 >= ? AND timestamp/1000 < ? ORDER BY timestamp");
if (!stmt) return sigs;
sqlite3_bind_text(stmt, 1, channelId.toUtf8().constData(), -1, SQLITE_STATIC);
sqlite3_bind_int64(stmt, 2, dayStart);
sqlite3_bind_int64(stmt, 3, dayEnd);
while (sqlite3_step(stmt) == SQLITE_ROW)
sigs.append(colBlob(stmt, 0));
sqlite3_finalize(stmt);
return sigs;
}
QList<MessageRow> DbManager::getMessagesBySigs(const QList<QByteArray>& sigs) const {
QList<MessageRow> msgs;
if (sigs.isEmpty()) return msgs;
QString sql = "SELECT id, channel_id, author_node_id, content, content_type,"
" timestamp, signature, is_outgoing, is_read FROM messages"
" WHERE signature IN (";
for (int i = 0; i < sigs.size(); i++)
sql += (i > 0 ? ",?" : "?");
sql += ") ORDER BY timestamp";
sqlite3_stmt* stmt = prepareOrNull(sql.toUtf8().constData());
if (!stmt) return msgs;
for (int i = 0; i < sigs.size(); i++)
sqlite3_bind_blob(stmt, i + 1, sigs[i].constData(), sigs[i].size(), SQLITE_STATIC);
while (sqlite3_step(stmt) == SQLITE_ROW) {
MessageRow m;
m.id = sqlite3_column_int64(stmt, 0);
m.channelId = colText(stmt, 1);
m.authorNodeId = static_cast<quint64>(sqlite3_column_int64(stmt, 2));
m.content = colText(stmt, 3);
m.contentType = colText(stmt, 4);
m.timestamp = sqlite3_column_int64(stmt, 5);
m.signature = colBlob(stmt, 6);
m.isOutgoing = sqlite3_column_int(stmt, 7) != 0;
m.isRead = sqlite3_column_int(stmt, 8) != 0;
msgs.append(m);
}
sqlite3_finalize(stmt);
return msgs;
}
QList<quint64> DbManager::getOnlinePeers() const {
QList<quint64> peers;
sqlite3_stmt* stmt = prepareOrNull(
"SELECT node_id FROM nodes WHERE is_online=1 AND node_id != ?");
if (!stmt) return peers;
sqlite3_bind_int64(stmt, 1, static_cast<sqlite3_int64>(m_myNodeId));
while (sqlite3_step(stmt) == SQLITE_ROW)
peers.append(static_cast<quint64>(sqlite3_column_int64(stmt, 0)));
sqlite3_finalize(stmt);
return peers;
}
bool DbManager::insertMessage(const QString& channelId, quint64 authorNodeId,
const QString& content, qint64 timestamp, int isOutgoing) {
static const unsigned char sig64[64] = {};
sqlite3_stmt* stmt = prepareOrNull(
"INSERT INTO messages"
" (channel_id,author_node_id,content,timestamp,signature,is_outgoing,is_read)"
" VALUES(?,?,?,?,?,?,1)");
if (!stmt) return false;
sqlite3_bind_text(stmt, 1, channelId.toUtf8().constData(), -1, SQLITE_STATIC);
sqlite3_bind_int64(stmt, 2, static_cast<sqlite3_int64>(authorNodeId));
sqlite3_bind_text(stmt, 3, content.toUtf8().constData(), -1, SQLITE_STATIC);
sqlite3_bind_int64(stmt, 4, timestamp);
sqlite3_bind_blob(stmt, 5, sig64, 64, SQLITE_STATIC);
sqlite3_bind_int(stmt, 6, isOutgoing);
bool ok = (sqlite3_step(stmt) == SQLITE_DONE);
sqlite3_finalize(stmt);
/* update bucket_meta */
qint64 day = timestamp / 86400000; /* ms per day */
int hour = static_cast<int>((timestamp % 86400000) / 3600000);
auto sigs = getMessageSigs(channelId, day, hour);
int cnt = sigs.size();
QByteArray hash(32, '\0');
if (cnt > 0) {
EVP_MD_CTX* md_ctx = EVP_MD_CTX_new();
EVP_DigestInit_ex(md_ctx, EVP_sha256(), NULL);
for (auto& s : sigs)
EVP_DigestUpdate(md_ctx, s.constData(), s.size());
unsigned int len = SHA256_DIGEST_LENGTH;
uint8_t buf[SHA256_DIGEST_LENGTH];
EVP_DigestFinal_ex(md_ctx, buf, &len);
EVP_MD_CTX_free(md_ctx);
hash = QByteArray(reinterpret_cast<const char*>(buf), static_cast<int>(len));
}
setBucketMeta(channelId, day, hour, cnt, hash);
return ok;
}
QByteArray DbManager::getLocalPubkey() const {
sqlite3_stmt* stmt = prepareOrNull(
"SELECT x25519_pubkey FROM local_identity WHERE id=1");
if (!stmt) return {};
QByteArray r;
if (sqlite3_step(stmt) == SQLITE_ROW)
r = colBlob(stmt, 0);
sqlite3_finalize(stmt);
return r;
}
QList<NodeAddr> DbManager::getOnlinePeerAddresses(quint64 excludeNodeId) const {
QList<NodeAddr> list;
sqlite3_stmt* stmt = prepareOrNull(
"SELECT na.node_id, na.family, na.address, na.port"
" FROM node_addresses na JOIN nodes n ON na.node_id = n.node_id"
" WHERE n.is_online=1 AND na.is_nat=0 AND na.node_id != ?"
" ORDER BY RANDOM()");
if (!stmt) return list;
sqlite3_bind_int64(stmt, 1, static_cast<sqlite3_int64>(excludeNodeId));
while (sqlite3_step(stmt) == SQLITE_ROW) {
NodeAddr a;
a.nodeId = static_cast<quint64>(sqlite3_column_int64(stmt, 0));
a.family = sqlite3_column_int(stmt, 1);
a.address = colBlob(stmt, 2);
a.port = static_cast<quint16>(sqlite3_column_int(stmt, 3));
list.append(a);
}
sqlite3_finalize(stmt);
return list;
}
/* ======================================================================== */
static constexpr unsigned char kDummySig[64] = {
0x00,0x01,0x02,0x03,0x04,0x05,0x06,0x07,
0x08,0x09,0x0A,0x0B,0x0C,0x0D,0x0E,0x0F,
0x10,0x11,0x12,0x13,0x14,0x15,0x16,0x17,
0x18,0x19,0x1A,0x1B,0x1C,0x1D,0x1E,0x1F,
0x20,0x21,0x22,0x23,0x24,0x25,0x26,0x27,
0x28,0x29,0x2A,0x2B,0x2C,0x2D,0x2E,0x2F,
0x30,0x31,0x32,0x33,0x34,0x35,0x36,0x37,
0x38,0x39,0x3A,0x3B,0x3C,0x3D,0x3E,0x3F
};
void DbManager::seedStubData() {
GUI_INFO("Seeding stub data...");
sqlite3_exec(m_db, "BEGIN", nullptr, nullptr, nullptr);
/* my_node dummy pubkey */
unsigned char pub_dummy[32] = {};
sqlite3_stmt* ins = prepareOrNull(
"INSERT OR IGNORE INTO local_identity(id,node_id,name,x25519_pubkey,ed25519_pubkey)"
" VALUES(1,?,'MyDevice',?,?)");
sqlite3_bind_int64(ins, 1, m_myNodeId);
sqlite3_bind_blob(ins, 2, pub_dummy, 32, SQLITE_STATIC);
sqlite3_bind_blob(ins, 3, pub_dummy, 32, SQLITE_STATIC);
sqlite3_step(ins); sqlite3_finalize(ins);
/* nodes + accounts */
struct { quint64 id; const char* name; const char* letter; const char* color; } accs[] = {
{1, "Alice", "A", "#4A90E2"}, {2, "Bob", "B", "#7ED321"},
{3, "Carol", "C", "#F5A623"}, {4, "Dave", "D", "#9013FE"},
{5, "Eve", "E", "#00B894"},
};
for (auto& a : accs) {
ins = prepareOrNull("INSERT OR IGNORE INTO nodes(node_id,name,x25519_pubkey,ed25519_pubkey)"
" VALUES(?,?,?,?)");
sqlite3_bind_int64(ins, 1, static_cast<sqlite3_int64>(a.id));
sqlite3_bind_text(ins, 2, a.name, -1, SQLITE_STATIC);
sqlite3_bind_blob(ins, 3, pub_dummy, 32, SQLITE_STATIC);
sqlite3_bind_blob(ins, 4, pub_dummy, 32, SQLITE_STATIC);
sqlite3_step(ins); sqlite3_finalize(ins);
ins = prepareOrNull("INSERT OR IGNORE INTO accounts(node_id,display_name,avatar_color,avatar_letter)"
" VALUES(?,?,?,?)");
sqlite3_bind_int64(ins, 1, static_cast<sqlite3_int64>(a.id));
sqlite3_bind_text(ins, 2, a.name, -1, SQLITE_STATIC);
sqlite3_bind_text(ins, 3, a.color, -1, SQLITE_STATIC);
sqlite3_bind_text(ins, 4, a.letter, -1, SQLITE_STATIC);
sqlite3_step(ins); sqlite3_finalize(ins);
}
/* set some nodes online for demo */
sqlite3_exec(m_db,
"UPDATE nodes SET is_online=1 WHERE node_id IN (1,2,3)", nullptr, nullptr, nullptr);
/* node addresses for online peers (stub, for invite link demo) */
struct { quint64 id; int fam; unsigned char addr[16]; int port; int nat; } addrs[] = {
{1, 4, {192,168,1,10}, 8001, 0},
{1, 6, {0xfd,0x12,0,0,0,0,0,0,0,0,0,0,0,0,0,1}, 8001, 0},
{2, 4, {10,0,0,5}, 9001, 0},
{2, 4, {172,16,0,100}, 9001, 0},
{3, 4, {192,168,5,20}, 10001, 1}, /* Carol behind NAT */
};
for (auto& ad : addrs) {
ins = prepareOrNull("INSERT OR IGNORE INTO node_addresses(node_id,family,address,port,is_nat)"
" VALUES(?,?,?,?,?)");
sqlite3_bind_int64(ins, 1, static_cast<sqlite3_int64>(ad.id));
sqlite3_bind_int(ins, 2, ad.fam);
sqlite3_bind_blob(ins, 3, ad.addr, ad.fam == 4 ? 4 : 16, SQLITE_STATIC);
sqlite3_bind_int(ins, 4, ad.port);
sqlite3_bind_int(ins, 5, ad.nat);
sqlite3_step(ins); sqlite3_finalize(ins);
}
/* channels (5) */
struct { const char* cid; const char* name; } chs[] = {
{"ch:general", "#general"},
{"ch:random", "#random"},
{"ch:dev-team", "#dev-team"},
{"ch:announce", "#announcements"},
{"ch:offtopic", "#offtopic"},
};
for (auto& ch : chs) {
ins = prepareOrNull("INSERT OR IGNORE INTO channels(channel_id,name) VALUES(?,?)");
sqlite3_bind_text(ins, 1, ch.cid, -1, SQLITE_STATIC);
sqlite3_bind_text(ins, 2, ch.name, -1, SQLITE_STATIC);
sqlite3_step(ins); sqlite3_finalize(ins);
/* my node member */
ins = prepareOrNull("INSERT OR IGNORE INTO channel_members(channel_id,node_id) VALUES(?,?)");
sqlite3_bind_text(ins, 1, ch.cid, -1, SQLITE_STATIC);
sqlite3_bind_int64(ins, 2, static_cast<sqlite3_int64>(m_myNodeId));
sqlite3_step(ins); sqlite3_finalize(ins);
/* all 5 accounts as members (everyone in every channel for demo) */
for (auto& a : accs) {
ins = prepareOrNull("INSERT OR IGNORE INTO channel_members(channel_id,node_id) VALUES(?,?)");
sqlite3_bind_text(ins, 1, ch.cid, -1, SQLITE_STATIC);
sqlite3_bind_int64(ins, 2, static_cast<sqlite3_int64>(a.id));
sqlite3_step(ins); sqlite3_finalize(ins);
}
}
/* messages (8) */
struct { const char* cid; quint64 author; qint64 ts; const char* text; quint8 o; } msgs[] = {
{"ch:general", 1, 1719200100000, "Hey team! How's the deployment going?", 0},
{"ch:general", 2, 1719200220000, "All green on staging. Running the final integration tests now.", 0},
{"ch:general", 3, 1719200520000, "I've pushed the ETCP-42 fix to staging. BGP handshake should be stable now. Please review the patch when you get a chance.", 0},
{"ch:general", 4, 1719200700000, "Wait — the BGP session is still flapping after the handshake. I'm seeing the keepalive timer fire but no route updates come through. Logs attached.", 0},
{"ch:general", 1, 1719200880000, "Reviewed the patch. LGTM. Let's merge and deploy to production tomorrow morning.", 0},
{"ch:general", 2, 1719201000000, "Can you share the full logs for the BGP session? I'll dig into the keepalive timer logic.", 0},
{"ch:general", 3, 1719201180000, "Sounds good. Let's coordinate in #dev-team. I'll prepare the release notes.", 0},
{"ch:general", 1, 1719201300000, "Deploy scheduled for tomorrow 10:00 UTC. All features are frozen — no more merges to main.", 0},
};
for (auto& m : msgs) {
ins = prepareOrNull(
"INSERT OR IGNORE INTO messages"
" (channel_id,author_node_id,content,timestamp,signature,is_outgoing,is_read)"
" VALUES(?,?,?,?,?,?,1)");
sqlite3_bind_text(ins, 1, m.cid, -1, SQLITE_STATIC);
sqlite3_bind_int64(ins, 2, static_cast<sqlite3_int64>(m.author));
sqlite3_bind_text(ins, 3, m.text, -1, SQLITE_STATIC);
sqlite3_bind_int64(ins, 4, m.ts);
sqlite3_bind_blob(ins, 5, kDummySig, 64, SQLITE_STATIC);
sqlite3_bind_int(ins, 6, m.o);
sqlite3_step(ins); sqlite3_finalize(ins);
}
/* reactions */
struct { qint64 msgRowId; quint64 node; const char* emoji; } reacts[] = {
{2, 1, "\xF0\x9F\x91\x8D"}, {2, 3, "\xF0\x9F\x91\x8D"},
{2, 1, "\xF0\x9F\x91\x8B"}, {2, 3, "\xF0\x9F\x91\x8B"}, {2, 4, "\xF0\x9F\x91\x8B"},
{3, 1, "\xF0\x9F\x91\x8D"}, {3, 2, "\xF0\x9F\x91\x8D"}, {3, 4, "\xF0\x9F\x91\x8D"},
{3, 1, "\xE2\x9D\xA4"},
{4, 2, "\xF0\x9F\x98\xAE"},
{5, 1, "\xF0\x9F\x91\x8D"}, {5, 2, "\xF0\x9F\x91\x8D"}, {5, 3, "\xF0\x9F\x91\x8D"},
{5, 4, "\xF0\x9F\x91\x8D"}, {5, 5, "\xF0\x9F\x91\x8D"},
{5, 2, "\xF0\x9F\x8E\x89"}, {5, 5, "\xF0\x9F\x8E\x89"},
{7, 1, "\xF0\x9F\xA4\x96"}, {7, 2, "\xF0\x9F\xA4\x96"},
{8, 2, "\xF0\x9F\x9A\x80"}, {8, 3, "\xF0\x9F\x9A\x80"}, {8, 4, "\xF0\x9F\x9A\x80"},
{8, 1, "\xE2\x9D\xA4"}, {8, 2, "\xE2\x9D\xA4"}, {8, 3, "\xE2\x9D\xA4"},
{8, 4, "\xE2\x9D\xA4"}, {8, 5, "\xE2\x9D\xA4"}, {8, 1, "\xE2\x9D\xA4"}, {8, 1, "\xE2\x9D\xA4"},
};
std::string lastMsg;
qint64 lastTs = 0;
for (auto& r : reacts) {
ins = prepareOrNull(
"INSERT OR IGNORE INTO reactions(message_id,node_id,emoji,signature)"
" VALUES(?,?,?,?)");
sqlite3_bind_int64(ins, 1, r.msgRowId);
sqlite3_bind_int64(ins, 2, static_cast<sqlite3_int64>(r.node));
sqlite3_bind_text(ins, 3, r.emoji, -1, SQLITE_STATIC);
sqlite3_bind_blob(ins, 4, kDummySig, 64, SQLITE_STATIC);
sqlite3_step(ins); sqlite3_finalize(ins);
}
lastMsg = "Deploy scheduled for tomorrow 10:00 UTC. All features are frozen — no more merges to main.";
lastTs = 1719201300000;
/* update channel last_msg */
ins = prepareOrNull("UPDATE channels SET last_message=?, last_msg_at=? WHERE channel_id='ch:general'");
sqlite3_bind_text(ins, 1, lastMsg.c_str(), -1, SQLITE_STATIC);
sqlite3_bind_int64(ins, 2, lastTs);
sqlite3_step(ins); sqlite3_finalize(ins);
sqlite3_exec(m_db, "COMMIT", nullptr, nullptr, nullptr);
GUI_INFO("Seed data inserted (5 accounts, 5 channels, 8 messages, %d reactions, %d addresses)",
static_cast<int>(sizeof(reacts)/sizeof(reacts[0])),
static_cast<int>(sizeof(addrs)/sizeof(addrs[0])));
}
QString DbManager::getUiState(const QString& key) const {
sqlite3_stmt* stmt = prepareOrNull("SELECT value FROM ui_state WHERE key=?");
if (!stmt) return {};
sqlite3_bind_text(stmt, 1, key.toUtf8().constData(), -1, SQLITE_STATIC);
QString val;
if (sqlite3_step(stmt) == SQLITE_ROW)
val = colText(stmt, 0);
sqlite3_finalize(stmt);
return val;
}
void DbManager::setUiState(const QString& key, const QString& value) {
sqlite3_stmt* stmt = prepareOrNull(
"INSERT OR REPLACE INTO ui_state(key,value) VALUES(?,?)");
if (!stmt) return;
sqlite3_bind_text(stmt, 1, key.toUtf8().constData(), -1, SQLITE_STATIC);
sqlite3_bind_text(stmt, 2, value.toUtf8().constData(), -1, SQLITE_STATIC);
sqlite3_step(stmt);
sqlite3_finalize(stmt);
}