int
db_deselect ()
{
db_disconnect (lyricDb);
db_disconnect (mediaDb);
db_disconnect (bibleDb);
return TRUE;
}
int batch_si(int conn, char *command)
{
char instance[63] = "";
int business = 0;
char *sql;
int rowc;
row_t *rows = NULL;
if (sscanf(command, "SI %[^.].%i\n", instance, &business) != 2) {
chat(conn, "ERROR: Invalid syntax\n");
return 0;
}
chat(conn, "Performing SalesInvoice batch run for instance '%s', business '%i' ... ",
instance, business);
db_connect(config->dbs);
/* verify instance and business exist */
asprintf(&sql, "SELECT * FROM instance WHERE id='%s';", instance);
rowc = batch_fetch_rows(NULL, 0, sql, &rows);
free(sql);
if (rowc == 0) {
chat(conn, "ERROR: instance '%s' does not exist\n", instance);
db_disconnect(config->dbs);
return 0;
}
rows = NULL;
asprintf(&sql, "SELECT * FROM business WHERE id='%i';", business);
rowc = batch_fetch_rows(instance, 0, sql, &rows);
free(sql);
if (rowc == 0) {
chat(conn, "ERROR: business '%s.%i' does not exist\n",
instance, business);
db_disconnect(config->dbs);
return 0;
}
rows = NULL;
chat(conn, CLERK_RESP_OK);
/* lock salesinvoice table */
batch_exec_sql(instance, business,
"BEGIN WORK; LOCK TABLE salesinvoice IN EXCLUSIVE MODE");
/* generate salesinvoices */
batch_exec_sql(instance, business, "SELECT process_salesorders();");
/* commit changes and release lock */
batch_exec_sql(instance, business, "COMMIT WORK;");
db_disconnect(config->dbs);
chat(conn, CLERK_RESP_OK);
return 0;
}
word db_row_count(void)
{
// Returns number of rows affected by preceding DELETE or UPDATE.
// Doesn't work after a SELECT.
MYSQL_RES * result;
MYSQL_ROW row;
if(db_connect()) return 9;
if (mysql_query(mysql_object, "SELECT row_count()"))
{
char zs[1024];
sprintf(zs, "db_row_count(): mysql_query() Error %u: %s", mysql_errno(mysql_object), mysql_error(mysql_object));
_log(CRITICAL, zs);
db_disconnect();
return 3;
}
result = mysql_store_result(mysql_object);
row = mysql_fetch_row(result);
mysql_free_result(result);
return atoi(row[0]);
}
word db_query(const char * const query)
{
char zs[2048];
if(strlen(query) > 2000)
{
_log(MAJOR, "db_query() called with overlong query.");
return 99;
}
sprintf(zs, "db_query(\"%s\")", query);
_log(PROC, zs);
if(db_connect()) return 9;
if(mysql_query(mysql_object, query))
{
sprintf(zs, "db_query(): mysql_query() Error %u: %s Query:", mysql_errno(mysql_object), mysql_error(mysql_object));
_log(CRITICAL, zs);
char report[128];
strncpy(report, query, 96);
report[96] = 0;
if(strlen(query) > 96)
{
strcat(report, "...");
}
sprintf(zs,"\"%s\"", report);
_log(CRITICAL, zs);
db_disconnect();
return 3;
}
return 0;
}
void teardown(void)
{
auth_disconnect();
db_disconnect();
config_free();
mempool_close(&queue_pool);
}
static void close_ticker(struct PgDatabase *db, double sleep_time)
{
log_debug("%s: close_ticker, %f", db->name, sleep_time);
db->state = DB_CLOSED;
db_disconnect(db->c_ticker);
db_sleep(db->c_ticker, sleep_time);
}
PGconn * db_connect(struct cks_config *config)
{
PGconn *conn = NULL;
char *pghost = NULL,
*pgport = NULL,
*pgoptions = NULL,
*pgtty = NULL;
char *dbName = NULL;
pghost = (char *)config->dbsrvr_ip;
pgport = (char *)config->dbsrvr_port;
pgoptions = NULL;
pgtty = NULL;
dbName = (char *)config->dbsrvr_db;
conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);
if(PQstatus(conn) == CONNECTION_BAD)
{
fprintf(stderr,_("db.c: Failed to connect to postgres"));
fprintf(stderr,_("db.c: Connection to database '%s' failed.\n"), dbName);
fprintf(stderr,"db.c: %s", PQerrorMessage(conn));
db_disconnect(conn);
return NULL;
}
return conn;
}
int main(int argc, char *argv[])
{
int i, foreground, port;
void (*timeseal_init)(const char * ) = chessd_function("timeseal_init");
int (*net_init)(int ) = chessd_function("net_init");
void (*initial_load)(void ) = chessd_function("initial_load");
void (*db_connect)(void ) = chessd_function("db_connect");
void (*db_disconnect)(void ) = chessd_function("db_disconnect");
port = DEFAULT_PORT;
foreground = 0;
/* enable malloc checking in libc */
setenv("MALLOC_CHECK_", "2", 1);
while((i = getopt(argc, argv, "p:fR:T:")) != -1) {
switch(i) {
case 'p':
port = atoi(optarg);
break;
case 'f':
foreground = 1;
break;
case 'T':
timeseal_init(optarg);
break;
case 'R':
do_chroot(optarg);
break;
default:
usage();
}
}
if (!foreground && daemonize()){
printf("Problem with Daemonization - Aborting\n");
exit(1);
}
signal(SIGTERM, TerminateServer);
signal(SIGSEGV, segv_handler);
signal(SIGBUS, segv_handler);
signal(SIGINT, TerminateServer);
signal(SIGPIPE, BrokenPipe);
if (net_init(port)) {
fprintf(stderr, "CHESSD: Network initialize failed on port %d.\n", port);
exit(1);
}
fprintf(stderr, "CHESSD: Initialized on port %d\n", port);
initial_load();
db_connect();
main_event_loop();
db_disconnect();
/* will never get here - uses TerminateServer */
return 0;
}
void db_delete_tag(int tag_id) {
char *query = NULL;
char query_outline[] = "DELETE FROM tags WHERE tag_id = \"\"";
int query_length = 0;
int rc = SQLITE_ERROR; /* return code of sqlite operation */
int written = 0; /* number of characters written */
sqlite3 *conn = NULL;
sqlite3_stmt *res = NULL;
DEBUG(ENTRY);
assert(tag_id > 0);
DEBUG("Deleting tag with tag ID %d", tag_id);
/* prepare query */
query_length = strlen(query_outline) + num_digits(tag_id);
query = malloc(query_length * sizeof(*query) + 1);
assert(query != NULL);
written = snprintf(query, query_length + 1, "DELETE FROM tags WHERE tag_id = \"%d\"", tag_id);
assert(written == query_length);
/* connect to database */
conn = db_connect();
assert(conn != NULL);
rc = db_execute_statement(conn, query, &res);
db_finalize_statement(conn, query, res);
db_disconnect(conn);
free_single_ptr((void **)&query);
DEBUG("File with ID %d was %sdeleted successfully.", tag_id, rc == 0 ? "" : "not ")
DEBUG(EXIT);
} /* db_delete_tag */
void db_remove_file(int file_id) {
char *query = NULL;
char query_outline[] = "DELETE FROM files WHERE file_id = ";
int query_length = 0;
int rc = SQLITE_ERROR; /* return code of sqlite operation */
int written = 0; /* number of characters written */
sqlite3 *conn = NULL;
sqlite3_stmt *res = NULL;
DEBUG(ENTRY);
assert(file_id > 0);
DEBUG("Removing file ID %d", file_id);
/* prepare query */
query_length = strlen(query_outline) + num_digits(file_id);
query = malloc(query_length * sizeof(*query) + 1);
assert(query != NULL);
written = snprintf(query, query_length + 1, "DELETE FROM files WHERE file_id = %d", file_id);
assert(written == query_length);
/* connect to database */
conn = db_connect();
assert(conn != NULL);
rc = db_execute_statement(conn, query, &res);
db_finalize_statement(conn, query, res);
db_disconnect(conn);
free_single_ptr((void **)&query);
DEBUG("Removing file ID %d was %ssuccessful", file_id, rc == SQLITE_DONE ? "" : "not ");
DEBUG(EXIT);
} /* db_remove_file */
void db_add_tag_to_file(int tag_id, int file_id) {
char *query = NULL;
char query_outline[] = "INSERT INTO file_has_tag VALUES(, )";
int query_length = 0;
int rc = SQLITE_ERROR; /* return code of sqlite operation */
int written = 0; /* number of characters written */
sqlite3 *conn = NULL;
sqlite3_stmt *res = NULL;
DEBUG(ENTRY);
assert(tag_id >= 0);
assert(file_id > 0);
/* prepare query */
query_length = strlen(query_outline) + num_digits(tag_id) + num_digits(file_id);
query = malloc(query_length * sizeof(*query) + 1);
assert(query != NULL);
written = snprintf(query, query_length + 1, "INSERT INTO file_has_tag VALUES(%d, %d)", file_id, tag_id);
assert(written == query_length);
/* connect to database */
conn = db_connect();
assert(conn != NULL);
rc = db_execute_statement(conn, query, &res);
db_finalize_statement(conn, query, res);
db_disconnect(conn);
free_single_ptr((void **)&query);
DEBUG("Adding tag ID %d to file ID %d was %ssuccessful", tag_id, file_id, rc == SQLITE_DONE ? "" : "not ");
DEBUG(EXIT);
} /* db_add_tag_to_file */
static void main_event_loop(void)
{
void (*select_loop)(void ) = chessd_function("select_loop");
while (1) {
select_loop();
/* check the reload flag */
if (chessd_reload_flag) {
void (*reload_close)(void ) = chessd_function("reload_close");
void (*reload_open)(void );
void (*db_disconnect)(void ) = chessd_function("db_disconnect");
db_disconnect();
chessd_reload_flag = 0;
fprintf(stderr, "CHESSD: Reloading server code!\n");
/* free up local vars */
reload_close();
/* close the handle to the shared lib */
dlclose(chessd_so_handle);
chessd_so_handle = NULL;
/* re-initialise local variables */
reload_open = chessd_function("reload_open");
reload_open();
select_loop = chessd_function("select_loop");
void (*db_connect)(void ) = chessd_function("db_connect");
db_connect();
}
}
}
void teardown(void)
{
auth_disconnect();
db_disconnect();
config_free();
g_mime_shutdown();
}
/* destroy the DB connection */
static void perm_destroy_db(void)
{
if (db_conn) {
db_disconnect(db_conn);
db_ctx_free(db_conn);
db_conn = NULL;
}
}
//int batch_mail_all(int conn)
int batch_all(int conn, char *cmd)
{
char *bus;
char *command;
char *inst;
char *sql;
int rowc;
row_t *business = NULL;
row_t *instance = NULL;
db_connect(config->dbs);
/* fetch list of instances */
asprintf(&sql,
"SELECT * FROM instance WHERE id NOT IN ('default', 'test');");
rowc = batch_fetch_rows(NULL, 0, sql, &instance);
free(sql);
if (rowc == 0) {
chat(conn, "No instances found. Stopping batch run.\n");
db_disconnect(config->dbs);
return 0;
}
while (instance != NULL) {
/* find businesses for this instance */
inst = db_field(instance, "id")->fvalue;
asprintf(&sql, "SELECT * FROM business;");
rowc = batch_fetch_rows(inst, 0, sql, &business);
free(sql);
if (rowc > 0) {
/* perform command run for each business */
while (business != NULL) {
bus = db_field(business, "id")->fvalue;
asprintf(&command, "%s %s.%s", cmd, inst, bus);
handle_command(conn, command);
free(command);
business = business->next;
}
}
instance = instance->next;
}
db_disconnect(config->dbs);
return 0;
}
END_TEST
START_TEST(test_db_connect)
{
int res;
db_disconnect();
res = db_connect();
fail_unless(res==0, "Unable to connect to db");
}
int main()
{
start_log("log");
db_connect("ouroboros","ouroboros", "ouroboros_development");
db_exec_query("SELECT * FROM users", 10);
db_disconnect();
return 1;
}
static void mod_destroy(void)
{
if (write_mc) db_cmd_free(write_mc);
if (write_acc) db_cmd_free(write_acc);
if (acc_db) {
db_disconnect(acc_db);
db_ctx_free(acc_db);
}
}
int db_int_array_from_query(char *desired_column_name, char *result_query, int **result_array) {
bool column_match = false;
int result = 0;
int column_count = 0;
int desired_column_index = 0;
int i = 0;
int num_results = 0;
sqlite3 *conn = NULL;
sqlite3_stmt *res = NULL;
DEBUG(ENTRY);
assert(desired_column_name != NULL);
assert(result_query != NULL);
assert(*result_array == NULL);
DEBUG("Creating array from the %s column of the query: %s", desired_column_name, result_query);
num_results = db_count_from_query(result_query);
if(num_results > 0) {
*result_array = malloc(num_results * sizeof(**result_array));
assert(*result_array != NULL);
conn = db_connect();
assert(conn != NULL);
db_prepare_statement(conn, result_query, &res);
column_count = sqlite3_column_count(res);
for(desired_column_index = 0; desired_column_index < column_count; desired_column_index++) { /* find the requested column */
if(strcmp(desired_column_name, sqlite3_column_name(res, desired_column_index)) == 0) {
DEBUG("Matching column found (%s)", desired_column_name);
column_match = true;
break;
}
}
if(column_match == false) {
DEBUG("Matching column not found (%s)", desired_column_name);
ERROR("Unexpected input received from database.");
}
for(i = 0; db_step_statement(conn, result_query, res) == SQLITE_ROW; i++) {
result = sqlite3_column_int(res, desired_column_index);
(*result_array)[i] = result;
}
db_finalize_statement(conn, result_query, res);
db_disconnect(conn);
}
DEBUG(EXIT);
return num_results;
} /* db_int_array_from_query */
int sb_lua_db_disconnect(lua_State *L)
{
sb_lua_ctxt_t *ctxt;
ctxt = sb_lua_get_context(L);
if (ctxt->con)
db_disconnect(ctxt->con);
ctxt->con = NULL;
return 0;
}
static void TerminateServer(int sig)
{
void (*output_shut_mess)(void ) = chessd_function("output_shut_mess");
void (*TerminateCleanup)(void ) = chessd_function("TerminateCleanup");
void (*net_close)(void ) = chessd_function("net_close");
void (*db_disconnect)(void ) = chessd_function("db_disconnect");
fprintf(stderr, "CHESSD: Received signal %d\n", sig);
output_shut_mess();
TerminateCleanup();
net_close();
db_disconnect();
exit(1);
}
char *db_get_file_location(int file_id) {
char *file_location = NULL;
char *query = NULL;
char *tmp_file_directory = NULL; /* holds text from the query so it can be copied to a new memory location */
char *tmp_file_name = NULL; /* hold name of file until it can be copied to a new memory location */
char query_outline[] = "SELECT file_location, file_name FROM files WHERE file_id = ";
int file_location_length = 0; /* length of the file location to return */
int query_length = 0;
int written = 0; /* number of characters written */
sqlite3 *conn = NULL;
sqlite3_stmt *res = NULL;
DEBUG(ENTRY);
assert(file_id > 0);
DEBUG("Retrieving physical location for file with ID %d", file_id);
/* prepare query */
query_length = strlen(query_outline) + num_digits(file_id);
query = malloc(query_length * sizeof(*query) + 1);
written = snprintf(query, query_length + 1, "SELECT file_location, file_name FROM files WHERE file_id = %d", file_id);
assert(written == query_length);
/* connect to database */
conn = db_connect();
assert(conn != NULL);
db_execute_statement(conn, query, &res);
/* get file location and name */
tmp_file_directory = (char *)sqlite3_column_text(res, 0);
assert(tmp_file_directory != NULL);
tmp_file_name = (char *)sqlite3_column_text(res, 1);
assert(tmp_file_name != NULL);
/* build full file path from name and directory */
file_location_length = strlen(tmp_file_directory) + strlen(tmp_file_name) + 1;
file_location = malloc(file_location_length * sizeof(*file_location) + 1);
written = snprintf((char *)file_location, file_location_length + 1, "%s/%s", tmp_file_directory, tmp_file_name);
assert(written == file_location_length);
db_finalize_statement(conn, query, res);
db_disconnect(conn);
free_single_ptr((void *)&query);
DEBUG("File id %d corresponds to %s", file_id, file_location);
DEBUG(EXIT);
return file_location;
} /* db_get_file_location */
int main(int argc, char** argv) {
printf("Test %s performance\n", DB_NAME);
create_test_data();
db_connect();
#ifdef TT
db_execute_direct("drop table bench");
#else
exit_on_error = false;
db_execute_direct("drop table if exists bench");
exit_on_error = true;
#endif
db_execute_direct(
get_str(
"CREATE TABLE bench ("
"id TT_INT NOT NULL, "
"value TT_INT NOT NULL, "
"PRIMARY KEY (id) "
")"
" UNIQUE HASH ON (id) pages = 40000",
"CREATE UNLOGGED TABLE bench ("
"id integer NOT NULL, "
"value integer NOT NULL, "
"CONSTRAINT bench_pk PRIMARY KEY (id) "
")",
"CREATE TABLE IF NOT EXISTS bench ("
"id integer NOT NULL, "
"value integer NOT NULL, "
"PRIMARY KEY (id)"
") ENGINE = MEMORY"
)
);
db_prepare();
int32_t insert_cps, select_cps, select1_cps, update_cps, delete_cps;
bench(select1_func, TEST_COUNT, "select1", &select1_cps);
bench(insert_func, TEST_COUNT, "insert", &insert_cps);
reorder_test_data();
bench(select_func, TEST_COUNT, "select", &select_cps);
reorder_test_data();
bench(update_func, TEST_COUNT, "update", &update_cps);
reorder_test_data();
bench(delete_func, TEST_COUNT, "delete", &delete_cps);
db_execute_direct("drop table bench");
db_disconnect();
printf("%20s |%6s | %6s | %6s | %6s | %6s |\n", "Db Name", "select1", "insert", "select", "update", "delete");
printf("%20s | %6d | %6d | %6d | %6d | %6d |\n", DB_NAME, select1_cps, insert_cps, select_cps, update_cps, delete_cps);
return 0;
}
int main(int argc, char **argv)
{
signal(SIGCHLD, SIG_IGN);
signal(SIGUSR1, SIG_IGN);
test_mongo_connection();
int port = argc >= 2 ? atoi(argv[1]) : DEFAULT_PORT;
int server = create_server_socket(port);
while (1)
{
struct sockaddr cli_addr;
int cli_len = sizeof(cli_addr);
int client = accept(server, &cli_addr, &cli_len);
if (client < 0)
die("accept");
int pid = fork();
if (pid < 0)
die("fork");
if (pid == 0)
{
if (dup2(client, STDIN_FILENO) < 0) die("dup2 stdin");
if (dup2(client, STDOUT_FILENO) < 0) die("dup2 stdout");
D(" [%d] process started\n", getpid());
if (db_connect() == 0)
{
process_client();
db_disconnect();
}
else
{
WriteLn("DB connection problem, sorry");
D(" [%d] cannot connect to db\n", getpid());
}
D(" [%d] process finished\n", getpid());
shutdown(client, 2);
exit(0);
}
close(client);
}
close(server);
return 0;
}
void cpl_db_close()
{
if (delete_user) db_cmd_free(delete_user);
delete_user = NULL;
if (write_script) db_cmd_free(write_script);
write_script = NULL;
if (get_script) db_cmd_free(get_script);
get_script = NULL;
if (ctx) {
db_disconnect(ctx);
db_ctx_free(ctx);
ctx = NULL;
}
}
int db_tag_id_from_tag_name(char *tag_name) {
char *query = NULL;
int tag_id = -1;
char query_outline[] = "SELECT tag_id FROM tags WHERE tag_name = \"\"";
int query_length = 0;
int written = 0; /* number of characters written */
sqlite3 *conn = NULL;
sqlite3_stmt *res = NULL;
DEBUG(ENTRY);
assert(tag_name != NULL);
DEBUG("Retrieving tag name for tag ID %s", tag_name);
if(strcmp(tag_name, "/") == 0) {
tag_id = 0;
} else {
/* prepare query */
query_length = strlen(query_outline) + strlen(tag_name);
query = malloc(query_length * sizeof(*query) + 1);
assert(query != NULL);
written = snprintf(query, query_length + 1, "SELECT tag_id FROM tags WHERE tag_name = \"%s\"", tag_name);
assert(written == query_length);
/* connect to database */
conn = db_connect();
assert(conn != NULL);
db_execute_statement(conn, query, &res);
/* get name corresponding to tag_id */
tag_id = sqlite3_column_int(res, 0);
db_finalize_statement(conn, query, res);
db_disconnect(conn);
free_single_ptr((void **)&query);
}
DEBUG("Tag %s corresponds to tag ID %d", tag_name, tag_id);
DEBUG(EXIT);
return tag_id;
} /* db_tag_id_from_tag_name */
char *db_tag_name_from_tag_id(int tag_id) {
char *query = NULL;
char *tag_name = NULL;
char query_outline[] = "SELECT tag_name FROM tags WHERE tag_id = ";
int num_digits_in_id = 0;
int query_length = 0;
int query_outline_length = 0;
int written = 0; /* number of characters written */
sqlite3 *conn = NULL;
sqlite3_stmt *res = NULL;
DEBUG(ENTRY);
assert(tag_id > 0);
DEBUG("Retrieving tag name for tag ID %d", tag_id);
/* prepare query */
num_digits_in_id = num_digits(tag_id);
query_outline_length = strlen(query_outline);
query_length = query_outline_length + num_digits_in_id;
query = malloc(query_length * sizeof(*query) + 1);
assert(query != NULL);
written = snprintf(query, query_length + 1, "%s%d", query_outline, tag_id);
assert(written == query_length);
/* connect to database */
conn = db_connect();
assert(conn != NULL);
db_execute_statement(conn, query, &res);
/* get name corresponding to tag_id */
tag_name = strdup((char *)sqlite3_column_text(res, 0));
db_finalize_statement(conn, query, res);
db_disconnect(conn);
free_single_ptr((void **)&query);
DEBUG("Tag ID %d corresponds to tag %s", tag_id, tag_name);
DEBUG(EXIT);
return tag_name;
} /* db_tag_name_from_tag_id */
int db_count_from_query(char *query) {
char *count_query = NULL;
char select_count_outline[] = "SELECT COUNT(*) FROM ()";
int count = 0; /* number of rows returned from the count query */
int length = 0; /* length of the sqlite3 count query */
int written = 0; /* characters written by snprintf */
sqlite3 *conn = NULL;
sqlite3_stmt *res = NULL;
DEBUG(ENTRY);
assert(query != NULL);
DEBUG("Calculating number of results returned from query: %s", query);
/* calculate query length */
length = strlen(query) + strlen(select_count_outline);
/* build count query */
count_query = malloc(length * sizeof(*count_query) + 1);
assert(count_query != NULL);
written = snprintf(count_query, length + 1, "SELECT COUNT(*) FROM (%s)", query);
assert(written == length);
DEBUG("Complete query: %s", count_query);
conn = db_connect();
assert(conn != NULL);
/* compile prepared statement */
db_execute_statement(conn, count_query, &res);
/* get result of count */
count = sqlite3_column_int(res, 0);
db_finalize_statement(conn, count_query, res);
db_disconnect(conn);
free_single_ptr((void **)&count_query);
DEBUG("Query returns a count of %d", count);
DEBUG(EXIT);
return count;
} /* db_count_from_query */
void db_delete_empty_tags() {
int rc = SQLITE_ERROR; /* return code of sqlite operation */
sqlite3 *conn = NULL;
sqlite3_stmt *res = NULL;
char query[] = "DELETE FROM tags WHERE tag_name NOT IN (SELECT DISTINCT tag_name FROM all_tables)";
DEBUG(ENTRY);
DEBUG("Preparing to purge all empty tags from the database.");
/* connect to database */
conn = db_connect();
assert(conn != NULL);
rc = db_execute_statement(conn, query, &res);
db_finalize_statement(conn, query, res);
db_disconnect(conn);
DEBUG("Purging empty tags was %ssuccessful", rc == SQLITE_DONE ? "" : "not");
DEBUG(EXIT);
} /* db_delete_empty_tags */
int db_get_all_files(int **files) {
char query[] = "SELECT file_id FROM files";
int count = 0;
sqlite3 *conn = NULL;
DEBUG(ENTRY);
assert(*files == NULL);
DEBUG("Retrieving all files");
conn = db_connect();
assert(conn != NULL);
count = db_int_array_from_query("file_id", query, files);
db_disconnect(conn);
DEBUG("Returning a list of %d files", count);
DEBUG(EXIT);
return count;
} /* db_get_all_files */
