int cMsgList::DeliverMessagesSinceSync(unsigned sync)
{
db_iterator it;
int n = 0;
cUser *user = NULL;
nMySQL::cQuery DelQ(mQuery);
SetBaseTo(&mModel);
mQuery.Clear();
SelectFields(mQuery.OStream());
mQuery.OStream() << "WHERE date_sent >=" << sync;
for(it = db_begin(); it != db_end(); ++it, ++n ) {
if (!user || user->mNick != mModel.mReceiver)
user = mServer->mUserList.GetUserByNick(mModel.mReceiver);
if(user) {
DeliverModelToUser(user);
DelQ.Clear();
DelQ.OStream() << "DELETE FROM " << mMySQLTable.mName;
WherePKey(DelQ.OStream());
DelQ.Query();
}
}
DelQ.Clear();
mQuery.Clear();
return n;
}
void cIPLog::GetLastLogin(const string &who, bool isNick, int limit, ostream &os)
{
string ip;
if(isNick)
os << autosprintf(_("Nick %s has lately been in the hub with the following IP"), who.c_str()) << "\n";
else
os << autosprintf(_("IP %s has lately been in the hub with following nicknames"), who.c_str()) << "\n";
MakeSearchQuery(who, isNick, 1, limit);
SetBaseTo(&mModel);
os << "\n ";
os << setw(25) << setiosflags(ios::left) << toUpper(_("Date"));
os << (isNick ? "IP" : toUpper(_("Nickname"))) << "\n";
os << " " << string(25+20,'=') << endl;
db_iterator it;
for(it = db_begin(); it != db_end(); ++it) {
cBanList::Num2Ip(mModel.mIP, ip);
os << " " << setw(25) << setiosflags(ios::left) << cTime(mModel.mDate,0).AsDate();
os << (isNick ? ip : mModel.mNick) << endl;
}
mQuery.Clear();
}
int cMsgList::DeliverMessagesForUser(cUser *dest)
{
db_iterator it;
int n = 0;
long max_date = 0;
mQuery.Clear();
SelectFields(mQuery.OStream());
mQuery.OStream() << "WHERE " << "receiver" << "='" ;
WriteStringConstant(mQuery.OStream(),dest->mNick);
mQuery.OStream()<< "'";
SetBaseTo(&mModel);
for( it = db_begin(); it != db_end(); ++it, ++n ) {
if (mModel.mDateSent > max_date)
max_date = mModel.mDateSent;
DeliverModelToUser(dest);
}
mQuery.Clear();
mQuery.OStream() << "DELETE FROM " << mMySQLTable.mName << " WHERE receiver = '" ;
WriteStringConstant(mQuery.OStream(),dest->mNick);
mQuery.OStream() << "' AND date_sent <= " << max_date;
mQuery.Query();
return n;
}
void cIPLog::GetHistory(const string &who, bool isNick, int limit, ostream &os)
{
string ip;
if(isNick)
os << autosprintf(_("Last %d events of nick %s:"), limit, who.c_str()) << "\r\n";
else
os << autosprintf(_("Last %d events of IP %s:"), limit, who.c_str()) << "\r\n";
MakeSearchQuery(who, isNick, -1, limit);
SetBaseTo(&mModel);
const char *Actions[]={_("connect"),_("login"),_("logout"),_("disconnect")};
const char *Infos[]={
"--",
_("bad nick or nick temporarily banned"),
_("used different nick in chat"),
_("kicked"),
_("redirected"),
_("exit from the hub"),
_("critical hub load"),
_("timeout"),
_("user did nothing for too long time"),
_("hub full"),
_("share limit"),
_("no tag or not valid"),
_("tag breaks hub rules"),
_("wrong password"),
_("error in login sequence"),
_("syntax error in some messages"),
_("invalid key")
};
os << "\n ";
os << setw(20) << setiosflags(ios::left) << toUpper(_("Date"));
os << setw(20) << setiosflags(ios::left) << toUpper(_("Action"));
os << setw(15) << setiosflags(ios::left) << (isNick ? "IP" : toUpper(_("Nickname")));
os << toUpper(_("Info")) << "\n";
os << " " << string(20+20+15+25,'=') << endl;
db_iterator it;
for(it = db_begin(); it != db_end(); ++it) {
cBanList::Num2Ip(mModel.mIP, ip);
os << " " << setw(20) << setiosflags(ios::left) << cTime(mModel.mDate,0).AsDate();
os << setw(20) << setiosflags(ios::left);
if(mModel.mType < 4)
os << Actions[mModel.mType];
else
os << mModel.mType;
os << setw(15) << setiosflags(ios::left) << (isNick ? ip : mModel.mNick.substr(0,14));
if(mModel.mInfo < 16) {
if(strlen(Infos[mModel.mInfo]) > 0)
os << Infos[mModel.mInfo];
} else
os << mModel.mInfo;
os << endl;
}
mQuery.Clear();
}
int cMsgList::PrintSubjects(ostream &os, const string &nick, bool IsSender)
{
int n = 0;
mQuery.Clear();
SelectFields(mQuery.OStream());
mQuery.OStream() << "WHERE " << (IsSender ? "sender" : "receiver") << "='";
WriteStringConstant(mQuery.OStream(), nick );
mQuery.OStream() << "'";
db_iterator it;
SetBaseTo(&mModel);
for(it = db_begin(); it != db_end(); ++it) {
os << mModel.AsSubj() << endl;
n++;
}
mQuery.Clear();
return 0;
}
void mbb_stat_pool_save(struct mbb_stat_pool *pool)
{
static GStaticMutex mutex = G_STATIC_MUTEX_INIT;
mbb_log_lvl_t mask;
mbb_log_mask(LOG_MASK_DEL, MBB_LOG_QUERY, &mask);
if (! g_static_mutex_trylock(&mutex)) {
mbb_log("wait for mutex");
g_static_mutex_lock(&mutex);
}
if (! mbb_task_poll_state())
goto out;
mbb_log("save to db");
if (! db_begin())
goto out;
if (! save_records(pool->ustat, unit_rec_save))
goto rollback;
if (! save_records(pool->lstat, link_rec_save))
goto rollback;
if (! save_records(pool->ulstat, unit_link_rec_save))
goto rollback;
db_commit();
goto out;
rollback:
db_rollback();
out:
mbb_log_mask(LOG_MASK_SET, mask, NULL);
g_static_mutex_unlock(&mutex);
mbb_stat_pool_free(pool);
}
cConfMySQL::db_iterator &cConfMySQL::db_begin()
{
return db_begin(mQuery);
}
/**
* The core parse function. Parses an XML file and stores it in the database according to
* the xmlparser.xsl template.
*
* @param thrdata Pointer to a threadData_t structure with database connection, log context, settings, etc
* @param job Pointer to a parseJob_t structure containing the job information
*
* @return Return values:
* @code
* STAT_SUCCESS : Successfully registered report
* STAT_FTOOBIG : XML report file is too big
* STAT_XMLFAIL : Could not parse the XML report file
* STAT_SYSREG : Failed to register the system into the systems or systems_hostname tables
* STAT_RTERIDREG: Failed to get a new rterid value
* STAT_GENDB : Failed to start an SQL transaction (BEGIN)
* STAT_RTEVRUNS : Failed to register the rteval run into rtevalruns or rtevalruns_details
* STAT_CYCLIC : Failed to register the data into cyclic_statistics or cyclic_rawdata tables
* STAT_REPMOVE : Failed to move the report file
* @endcode
*/
inline int parse_report(threadData_t *thrdata, parseJob_t *job)
{
int syskey = -1, rterid = -1;
int rc = -1;
xmlDoc *repxml = NULL;
char *destfname;
// Check file size - and reject too big files
if( check_filesize(thrdata, job->filename) == 0 ) {
writelog(thrdata->dbc->log, LOG_ERR,
"[Thread %i] (submid: %i) Report file '%s' is too big, rejected",
thrdata->id, job->submid, job->filename);
return STAT_FTOOBIG;
}
repxml = xmlParseFile(job->filename);
if( !repxml ) {
writelog(thrdata->dbc->log, LOG_ERR,
"[Thread %i] (submid: %i) Could not parse XML file: %s",
thrdata->id, job->submid, job->filename);
return STAT_XMLFAIL;
}
pthread_mutex_lock(thrdata->mtx_sysreg);
syskey = db_register_system(thrdata->dbc, thrdata->xslt, repxml);
if( syskey < 0 ) {
writelog(thrdata->dbc->log, LOG_ERR,
"[Thread %i] Failed to register system (submid: %i, XML file: %s)",
thrdata->id, job->submid, job->filename);
rc = STAT_SYSREG;
goto exit;
}
rterid = db_get_new_rterid(thrdata->dbc);
if( rterid < 0 ) {
writelog(thrdata->dbc->log, LOG_ERR,
"[Thread %i] Failed to register rteval run (submid: %i, XML file: %s)",
thrdata->id, job->submid, job->filename);
rc = STAT_RTERIDREG;
goto exit;
}
pthread_mutex_unlock(thrdata->mtx_sysreg);
if( db_begin(thrdata->dbc) < 1 ) {
rc = STAT_GENDB;
goto exit;
}
// Create a new filename of where to save the report
destfname = get_destination_path(thrdata->dbc->log, thrdata->destdir, job, rterid);
if( !destfname ) {
writelog(thrdata->dbc->log, LOG_ERR,
"[Thread %i] Failed to generate local report filename for (submid: %i) %s",
thrdata->id, job->submid, job->filename);
db_rollback(thrdata->dbc);
rc = STAT_UNKNFAIL;
goto exit;
}
if( db_register_rtevalrun(thrdata->dbc, thrdata->xslt, repxml, job->submid,
syskey, rterid, destfname) < 0 ) {
writelog(thrdata->dbc->log, LOG_ERR,
"[Thread %i] Failed to register rteval run (submid: %i, XML file: %s)",
thrdata->id, job->submid, job->filename);
db_rollback(thrdata->dbc);
rc = STAT_RTEVRUNS;
goto exit;
}
if( db_register_cyclictest(thrdata->dbc, thrdata->xslt, repxml, rterid) != 1 ) {
writelog(thrdata->dbc->log, LOG_ERR,
"[Thread %i] Failed to register cyclictest data (submid: %i, XML file: %s)",
thrdata->id, job->submid, job->filename);
db_rollback(thrdata->dbc);
rc = STAT_CYCLIC;
goto exit;
}
// When all database registrations are done, move the file to it's right place
if( make_report_dir(thrdata->dbc->log, destfname) < 1 ) { // Make sure report directory exists
db_rollback(thrdata->dbc);
rc = STAT_REPMOVE;
goto exit;
}
if( rename(job->filename, destfname) < 0 ) { // Move the file
writelog(thrdata->dbc->log, LOG_ERR,
"[Thread %i] (submid: %i) Failed to move report file from %s to %s (%s)",
thrdata->id, job->submid, job->filename, destfname, strerror(errno));
db_rollback(thrdata->dbc);
rc = STAT_REPMOVE;
goto exit;
}
free_nullsafe(destfname);
rc = STAT_SUCCESS;
db_commit(thrdata->dbc);
writelog(thrdata->dbc->log, LOG_INFO,
"[Thread %i] Report parsed and stored (submid: %i, rterid: %i)",
thrdata->id, job->submid, rterid);
exit:
xmlFreeDoc(repxml);
return rc;
}
static void
blobcache_prune(sqlite3 *db)
{
int rc;
sqlite3_stmt *sel, *del;
int64_t currentsize, pruned_bytes = 0;
int pruned_items = 0;
if(db_begin(db))
return;
if(db_get_int64_from_query(db, "SELECT sum(length(payload)) FROM item",
¤tsize)) {
db_rollback(db);
return;
}
estimated_cache_size = currentsize;
uint64_t limit = blobcache_compute_maxsize(currentsize) * 9 / 10;
if(currentsize <= limit) {
db_rollback(db);
return;
}
rc = sqlite3_prepare_v2(db,
"SELECT _rowid_,length(payload) "
"FROM item "
"ORDER BY lastaccess",
-1, &sel, NULL);
if(rc != SQLITE_OK) {
TRACE(TRACE_ERROR, "SQLITE", "SQL Error %d at %s:%d",
rc, __FUNCTION__, __LINE__);
db_rollback(db);
return;
}
while((rc = sqlite3_step(sel)) == SQLITE_ROW) {
int itemsize = sqlite3_column_int(sel, 1);
int64_t id = sqlite3_column_int64(sel, 0);
rc = sqlite3_prepare_v2(db, "DELETE FROM item WHERE _rowid_ = ?1",
-1, &del, NULL);
if(rc != SQLITE_OK) {
TRACE(TRACE_ERROR, "SQLITE", "SQL Error at %s:%d",
__FUNCTION__, __LINE__);
sqlite3_finalize(sel);
db_rollback(db);
return;
}
sqlite3_bind_int(del, 1, id);
rc = sqlite3_step(del);
sqlite3_finalize(del);
if(rc != SQLITE_DONE) {
sqlite3_finalize(sel);
db_rollback(db);
return;
}
currentsize -= itemsize;
pruned_bytes += itemsize;
pruned_items++;
if(currentsize <= limit)
break;
}
TRACE(TRACE_DEBUG, "CACHE",
"Pruned %d items, %"PRId64" bytes from cache",
pruned_items, pruned_bytes);
estimated_cache_size = currentsize;
sqlite3_finalize(sel);
db_commit(db);
}
static void *
blobcache_get0(sqlite3 *db, const char *key, const char *stash,
size_t *sizep, int pad, int *is_expired,
char **etagp, time_t *mtimep)
{
int rc;
void *rval = NULL;
sqlite3_stmt *stmt;
time_t now;
if(db_begin(db))
return NULL;
rc = sqlite3_prepare_v2(db,
"SELECT payload,expiry,etag,modtime FROM item "
"WHERE k=?1 AND stash=?2",
-1, &stmt, NULL);
if(rc) {
db_rollback(db);
return NULL;
}
time(&now);
sqlite3_bind_text(stmt, 1, key, -1, SQLITE_STATIC);
sqlite3_bind_text(stmt, 2, stash, -1, SQLITE_STATIC);
rc = sqlite3_step(stmt);
if(rc != SQLITE_ROW) {
sqlite3_finalize(stmt);
db_rollback(db);
return NULL;
}
int expired = now > sqlite3_column_int64(stmt, 1);
if(!expired || is_expired != NULL) {
sqlite3_column_blob(stmt, 0);
size_t size = sqlite3_column_bytes(stmt, 0);
if(size > 0) {
rval = malloc(size + pad);
memset(rval + size, 0, pad);
memcpy(rval, sqlite3_column_blob(stmt, 0), size);
*sizep = size;
}
}
if(is_expired != NULL)
*is_expired = expired;
if(etagp != NULL) {
const char *str = (const char *)sqlite3_column_text(stmt, 2);
if(str != NULL)
*etagp = strdup(str);
}
if(mtimep != NULL)
*mtimep = sqlite3_column_int(stmt, 3);
sqlite3_finalize(stmt);
// Update atime
rc = sqlite3_prepare_v2(db,
"UPDATE item SET "
"lastaccess = ?3 "
"WHERE k = ?1 AND stash = ?2",
-1, &stmt, NULL);
if(rc != SQLITE_OK) {
TRACE(TRACE_ERROR, "SQLITE", "SQL Error at %s:%d",
__FUNCTION__, __LINE__);
db_rollback(db);
return rval;
} else {
sqlite3_bind_text(stmt, 1, key, -1, SQLITE_STATIC);
sqlite3_bind_text(stmt, 2, stash, -1, SQLITE_STATIC);
sqlite3_bind_int(stmt, 3, time(NULL));
rc = sqlite3_step(stmt);
sqlite3_finalize(stmt);
}
db_commit(db);
return rval;
}
static JSBool
js_db_txn(JSContext *cx, JSObject *obj, uintN argc,
jsval *argv, jsval *rval)
{
js_db_t *jd = JS_GetPrivate(cx, obj);
if(js_db_check(cx, jd))
return JS_FALSE;
if(argc != 1) {
JS_ReportError(cx, "Invalid number of arguments");
return JS_FALSE;
}
if(jd->jd_transaction) {
JS_ReportError(cx, "Nesting transactions is not allowed");
return JS_FALSE;
}
retry:
if(db_begin(jd->jd_db)) {
JS_ReportError(cx, "Failed to start transaction");
return JS_FALSE;
}
jd->jd_transaction = 1;
int r = JS_CallFunctionValue(cx, NULL, argv[0], 0, NULL, rval);
jd->jd_transaction = 0;
if(jd->jd_stmt) {
sqlite3_finalize(jd->jd_stmt);
jd->jd_stmt = NULL;
}
if(!r) {
if(jd->jd_debug)
TRACE(TRACE_DEBUG, "JS", "Transaction rollbacked due to error");
db_rollback(jd->jd_db);
if(JS_IsExceptionPending(cx)) {
jsval exn;
if(!JS_GetPendingException(cx, &exn)) {
return JS_FALSE;
}
if(JSVAL_IS_OBJECT(exn)) {
JSClass *c = JS_GetClass(cx, JSVAL_TO_OBJECT(exn));
if(c == &db_deadlock_exn) {
if(jd->jd_debug)
TRACE(TRACE_DEBUG, "JS", "Catched deadlock exception, retrying");
JS_ClearPendingException(cx);
JS_BeginRequest(cx);
usleep(100000);
JS_EndRequest(cx);
goto retry;
}
}
}
return JS_FALSE;
}
if(*rval == JSVAL_TRUE) {
if(jd->jd_debug)
TRACE(TRACE_DEBUG, "JS", "Transaction committed");
db_commit(jd->jd_db);
} else {
if(jd->jd_debug)
TRACE(TRACE_DEBUG, "JS", "Transaction rollbacked");
db_rollback(jd->jd_db);
}
return JS_TRUE;
}
int main(int argc, char *argv[]) {
char *action;
char *name;
char *prefix;
int ret;
int fail = 0;
char *state;
if (argc != 4 || (strcmp(argv[1], "load") && strcmp(argv[1], "save") && strcmp(argv[1], "save+"))) {
printf("Brain manipulation\n");
printf("Usage: %s load <name> <filename prefix>\n", argv[0]);
printf(" %s save <name> <filename prefix>\n", argv[0]);
printf(" %s save+ <name> <filename prefix>\n", argv[0]);
return 1;
}
action = argv[1];
name = argv[2];
prefix = argv[3];
state = "db_connect";
ret = db_connect();
if (ret) goto fail;
else log_info("brain", ret, state);
state = "db_begin";
ret = db_begin();
if (ret) goto fail;
else log_info("brain", ret, state);
if (!strcmp(action, "load")) {
state = "input_list aux";
ret = input_list(name, prefix, "aux", LIST_AUX);
if (ret) { log_warn("brain", ret, state); fail = 1; }
else log_info("brain", ret, state);
state = "input_list ban";
ret = input_list(name, prefix, "ban", LIST_BAN);
if (ret) { log_warn("brain", ret, state); fail = 1; }
else log_info("brain", ret, state);
state = "input_list grt";
ret = input_list(name, prefix, "grt", LIST_GREET);
if (ret) { log_warn("brain", ret, state); fail = 1; }
else log_info("brain", ret, state);
state = "input_map swp";
ret = input_map(name, prefix, "swp", MAP_SWAP);
if (ret) { log_warn("brain", ret, state); fail = 1; }
else log_info("brain", ret, state);
state = "input_brain";
ret = input_brain(name, prefix);
if (ret) { log_warn("brain", ret, state); fail = 1; }
else log_info("brain", ret, state);
} else if (!strcmp(action, "save") || !strcmp(action, "save+")) {
enum file_type type = FILETYPE_MEGAHAL8;
if (!strcmp(action, "save+"))
type = FILETYPE_SQLHAL0;
state = "output_list aux";
ret = output_list(name, prefix, "aux", LIST_AUX);
if (ret) { log_warn("brain", ret, state); fail = 1; }
else log_info("brain", ret, state);
state = "output_list ban";
ret = output_list(name, prefix, "ban", LIST_BAN);
if (ret) { log_warn("brain", ret, state); fail = 1; }
else log_info("brain", ret, state);
state = "output_list grt";
ret = output_list(name, prefix, "grt", LIST_GREET);
if (ret) { log_warn("brain", ret, state); fail = 1; }
else log_info("brain", ret, state);
state = "output_map swp";
ret = output_map(name, prefix, "swp", MAP_SWAP);
if (ret) { log_warn("brain", ret, state); fail = 1; }
else log_info("brain", ret, state);
state = "output_brain";
ret = output_brain(name, type, prefix);
if (ret) { log_warn("brain", ret, state); fail = 1; }
else log_info("brain", ret, state);
} else {
fail = 1;
}
if (fail) {
state = "db_rollback";
ret = db_rollback();
if (ret) goto fail;
else log_info("brain", ret, state);
} else {
state = "db_commit";
ret = db_commit();
if (ret) goto fail;
else log_info("brain", ret, state);
}
state = "db_disconnect";
ret = db_disconnect();
if (ret) goto fail;
else log_info("brain", ret, state);
return 0;
fail:
log_fatal("brain", ret, state);
return 1;
}
int db_connect(void) {
if (conn == NULL) {
conn = PQconnectdb("");
if (conn == NULL)
return -EDB;
if (PQstatus(conn) != CONNECTION_OK) {
log_error("DB", PQstatus(conn), PQerrorMessage(conn));
PQfinish(conn);
conn = NULL;
} else {
PGresult *res = NULL;
const char *brains[] = { "brains" };
const char *words[] = { "words" };
const char *lists[] = { "lists" };
const char *maps[] = { "maps" };
const char *models[] = { "models" };
const char *nodes[] = { "nodes" };
int nodes_created = 0;
int server_ver;
server_ver = PQserverVersion(conn);
if (server_ver < 80400) {
log_error("DB", server_ver, "Server version must be 8.4.0+");
PQfinish(conn);
conn = NULL;
return -EDB;
}
if (db_begin()) goto fail2;
res = PQprepare(conn, "table_exists", "SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename = $1", 1, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
/* BRAIN */
res = PQexecPrepared(conn, "table_exists", 1, brains, NULL, NULL, 1);
if (PQresultStatus(res) != PGRES_TUPLES_OK) goto fail;
if (PQntuples(res) != 1) {
PQclear(res);
res = PQexec(conn, "CREATE TABLE brains (id BIGSERIAL UNIQUE, name TEXT,"\
" PRIMARY KEY (name),"\
" CONSTRAINT valid_id CHECK (id > 0))");
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
}
PQclear(res);
/* WORD */
res = PQexecPrepared(conn, "table_exists", 1, words, NULL, NULL, 1);
if (PQresultStatus(res) != PGRES_TUPLES_OK) goto fail;
if (PQntuples(res) != 1) {
PQclear(res);
res = PQexec(conn, "CREATE TABLE words (id SERIAL UNIQUE, word TEXT, added TIMESTAMP NOT NULL DEFAULT NOW(),"\
" PRIMARY KEY (word),"\
" CONSTRAINT valid_id CHECK (id > 0))");
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
}
PQclear(res);
/* LIST */
res = PQexecPrepared(conn, "table_exists", 1, lists, NULL, NULL, 1);
if (PQresultStatus(res) != PGRES_TUPLES_OK) goto fail;
if (PQntuples(res) != 1) {
PQclear(res);
res = PQexec(conn, "CREATE TABLE lists (type INT NOT NULL, brain BIGINT NOT NULL, word BIGINT NOT NULL,"\
" PRIMARY KEY (brain, type, word),"\
" FOREIGN KEY (brain) REFERENCES brains (id) ON UPDATE CASCADE ON DELETE CASCADE,"\
" FOREIGN KEY (word) REFERENCES words (id) ON UPDATE CASCADE ON DELETE CASCADE,"\
" CONSTRAINT valid_type CHECK (type >= 1 AND type <= 3))");
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQexec(conn, "CREATE INDEX lists_words ON lists (word)");
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
}
PQclear(res);
/* MAP */
res = PQexecPrepared(conn, "table_exists", 1, maps, NULL, NULL, 1);
if (PQresultStatus(res) != PGRES_TUPLES_OK) goto fail;
if (PQntuples(res) != 1) {
PQclear(res);
res = PQexec(conn, "CREATE TABLE maps (type INT NOT NULL, brain BIGINT NOT NULL, key BIGINT NOT NULL, value BIGINT NOT NULL,"\
" PRIMARY KEY (brain, key),"\
" FOREIGN KEY (brain) REFERENCES brains (id) ON UPDATE CASCADE ON DELETE CASCADE,"\
" FOREIGN KEY (key) REFERENCES words (id) ON UPDATE CASCADE ON DELETE CASCADE,"\
" FOREIGN KEY (value) REFERENCES words (id) ON UPDATE CASCADE ON DELETE CASCADE,"\
" CONSTRAINT valid_type CHECK (type = 4))");
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQexec(conn, "CREATE INDEX maps_keys ON maps (key)");
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQexec(conn, "CREATE INDEX maps_values ON maps (value)");
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
}
PQclear(res);
/* MODEL */
res = PQexecPrepared(conn, "table_exists", 1, nodes, NULL, NULL, 1);
if (PQresultStatus(res) != PGRES_TUPLES_OK) goto fail;
if (PQntuples(res) != 1) {
PQclear(res);
res = PQexec(conn, "CREATE TABLE nodes (id BIGSERIAL UNIQUE, brain BIGINT NOT NULL, parent BIGINT, word BIGINT, usage BIGINT NOT NULL, count BIGINT NOT NULL,"\
" PRIMARY KEY (brain, id),"\
" FOREIGN KEY (parent) REFERENCES nodes (id) ON UPDATE CASCADE ON DELETE CASCADE,"\
" FOREIGN KEY (word) REFERENCES words (id) ON UPDATE CASCADE ON DELETE CASCADE,"\
" CONSTRAINT valid_id CHECK (id > 0),"\
" CONSTRAINT valid_usage CHECK (usage >= 0),"\
" CONSTRAINT valid_count CHECK (count >= 0),"\
" CONSTRAINT valid_root CHECK (parent IS NOT NULL OR word IS NULL),"\
" CONSTRAINT valid_fin CHECK (parent IS NULL OR word IS NOT NULL OR usage = 0))");
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQexec(conn, "CREATE INDEX nodes_words ON nodes (word)");
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQexec(conn, "CREATE UNIQUE INDEX nodes_child ON nodes (parent, word)");
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
nodes_created = 1;
}
PQclear(res);
res = PQexecPrepared(conn, "table_exists", 1, models, NULL, NULL, 1);
if (PQresultStatus(res) != PGRES_TUPLES_OK) goto fail;
if (PQntuples(res) != 1) {
PQclear(res);
res = PQexec(conn, "CREATE TABLE models (brain BIGINT NOT NULL, contexts BIGINT NOT NULL, forward BIGINT, backward BIGINT,"\
" PRIMARY KEY (brain),"\
" FOREIGN KEY (brain) REFERENCES brains (id) ON UPDATE CASCADE ON DELETE CASCADE,"\
" FOREIGN KEY (forward) REFERENCES nodes (id),"\
" FOREIGN KEY (backward) REFERENCES nodes (id),"\
" CONSTRAINT valid_order CHECK (contexts >= 0))");
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
}
PQclear(res);
if (nodes_created) {
res = PQexec(conn, "ALTER TABLE nodes"\
" ADD FOREIGN KEY (brain) REFERENCES models (brain) ON UPDATE CASCADE ON DELETE CASCADE");
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
}
/* BRAIN */
res = PQprepare(conn, "brain_add", "INSERT INTO brains (name) VALUES($1)", 1, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "brain_add_id", "SELECT currval('brains_id_seq')", 0, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "brain_get", "SELECT id FROM brains WHERE name = $1", 1, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
/* WORD */
res = PQprepare(conn, "word_add", "INSERT INTO words (word) VALUES($1)", 1, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "word_add_id", "SELECT currval('words_id_seq')", 0, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "word_get", "SELECT id FROM words WHERE word = $1", 1, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "word_str", "SELECT word FROM words WHERE id = $1", 1, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
/* LIST */
res = PQprepare(conn, "list_add", "INSERT INTO lists (brain, type, word) VALUES($1, $2, $3)", 3, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "list_get", "SELECT word FROM lists WHERE brain = $1 AND type = $2 AND word = $3", 3, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "list_iter", "SELECT lists.word, words.word FROM lists, words"\
" WHERE brain = $1 AND type = $2 AND words.id = lists.word"\
" ORDER BY words.word NULLS LAST", 2, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "list_zap", "DELETE FROM lists WHERE brain = $1 AND type = $2", 2, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
/* MAP */
res = PQprepare(conn, "map_add", "INSERT INTO maps (brain, type, key, value) VALUES($1, $2, $3, $4)", 4, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "map_get", "SELECT value FROM maps WHERE brain = $1 AND type = $2 AND key = $3", 3, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "map_iter", "SELECT maps.key, maps.value, words_k.word, words_v.word"\
" FROM maps, words AS words_k, words AS words_v"\
" WHERE brain = $1 AND type = $2 AND words_k.id = maps.key AND words_v.id = maps.value"\
" ORDER BY words_k.word NULLS LAST", 2, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "map_zap", "DELETE FROM maps WHERE brain = $1 AND type = $2", 2, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
/* MODEL */
res = PQprepare(conn, "model_add", "INSERT INTO models (brain, contexts) VALUES($1, $2)", 2, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_get", "SELECT contexts FROM models WHERE brain = $1", 1, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_set", "UPDATE models SET contexts = $2 WHERE brain = $1", 1, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_zap", "DELETE FROM models WHERE brain = $1", 1, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_create", "INSERT INTO nodes (brain, usage, count) VALUES($1, 0, 0)", 1, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_fastcreate", "INSERT INTO nodes (brain, usage, count, word, parent) VALUES($1, $2, $3, $4, $5)", 5, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_create_id", "SELECT currval('nodes_id_seq')", 0, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_rootupdate", "UPDATE nodes SET parent = NULL, usage = $2, count = $3 WHERE id = $1", 3, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_update", "UPDATE nodes SET usage = $2, count = $3 WHERE id = $1", 3, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_root_get", "SELECT forward, backward FROM models WHERE brain = $1", 1, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_root_set", "UPDATE models SET forward = $2, backward = $3 WHERE brain = $1", 3, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_node_get", "SELECT id, word, usage, count FROM nodes"\
" WHERE brain = $1 AND (id = $2 OR parent = $2)"\
" ORDER BY (SELECT words.word FROM words WHERE words.id = nodes.word) NULLS LAST", 2, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_node_find", "SELECT id, word, usage, count FROM nodes"\
" WHERE brain = $1 AND parent = $2 AND word = $3", 3, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_word_exists", "SELECT word FROM nodes WHERE brain = $1 AND word = $2 LIMIT 1", 2, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_word_random", "SELECT word FROM nodes WHERE brain = $1 AND parent = $2"\
" ORDER BY random() LIMIT 1", 2, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_node_random", "SELECT id, word, usage, count FROM nodes"\
" WHERE brain = $1 AND parent = $2"\
" ORDER BY random() LIMIT 1", 2, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_node_first", "SELECT id, parent, word, usage, count FROM nodes"\
" WHERE brain = $1 AND parent = $2"\
" ORDER BY id LIMIT 1", 2, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_node_prev", "SELECT id, parent, word, usage, count FROM nodes"\
" WHERE brain = $1 AND parent = $2 AND id < $3"\
" ORDER BY id DESC LIMIT 1", 3, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_node_next", "SELECT id, parent, word, usage, count FROM nodes"\
" WHERE brain = $1 AND parent = $2 AND id > $3"\
" ORDER BY id LIMIT 1", 3, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_node_last", "SELECT id, parent, word, usage, count FROM nodes"\
" WHERE brain = $1 AND parent = $2"\
" ORDER BY id DESC LIMIT 1", 2, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = PQprepare(conn, "model_brain_words", "SELECT id, ROW_NUMBER() OVER (ORDER BY id) - 1, word "\
" FROM words WHERE id IN (SELECT word FROM nodes WHERE brain=$1) ORDER BY word", 1, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) goto fail;
PQclear(res);
res = NULL;
if(db_commit()) goto fail2;
fail:
if (res != NULL) {
log_error("db_connect", PQresultStatus(res), PQresultErrorMessage(res));
PQclear(res);
fail2:
PQfinish(conn);
conn = NULL;
}
}
}
if (conn == NULL)
return -EDB;
return OK;
}
