gboolean dive_db_update(gint dive_id,gchar *dive_datetime,gulong dive_duration,gdouble dive_maxdepth,gdouble dive_mintemp,gdouble dive_maxtemp,gchar *dive_notes,gint site_id,gdouble dive_visibility,gdouble dive_weight)
{
gint rc;
gchar *sqlcmd,*sqlErrMsg=NULL;
gboolean rval = TRUE;
sqlcmd=sqlite3_mprintf(
"UPDATE Dive SET dive_datetime='%s',dive_duration=%lu,dive_maxdepth=%f,dive_mintemp=%f,dive_maxtemp=%f,"
"dive_notes='%q',site_id=%d,dive_visibility=%f,dive_weight=%f WHERE dive_id=%d;",
dive_datetime, dive_duration, dive_maxdepth, dive_mintemp, dive_maxtemp,
dive_notes,site_id, dive_visibility, dive_weight, dive_id
);
if(handle_transactions) db_begin_transaction();
rc=sqlite3_exec(logbook_db,sqlcmd,NULL,0,&sqlErrMsg);
if(rc!=SQLITE_OK) {
g_log(G_LOG_DOMAIN, G_LOG_LEVEL_ERROR,"Error in dive_db_update()\nCode=%d\nQuery='%s'\nError Message='%s'\n",rc,sqlcmd,sqlErrMsg);
sqlite3_free(sqlErrMsg);
rval=FALSE;
if(handle_transactions) db_rollback_transaction();
}
else {
db_not_saved();
if(handle_transactions) db_commit_transaction();
preferences_load_template_dive_number();
}
sqlite3_free(sqlcmd);
return rval;
}
int dm_sievescript_activate(uint64_t user_idnr, char *scriptname)
{
Connection_T c; PreparedStatement_T s; volatile gboolean t = FALSE;
assert(scriptname);
c = db_con_get();
TRY
db_begin_transaction(c);
s = db_stmt_prepare(c,"UPDATE %ssievescripts SET active = 0 WHERE owner_idnr = ? ", DBPFX);
db_stmt_set_u64(s, 1, user_idnr);
db_stmt_exec(s);
db_con_clear(c);
s = db_stmt_prepare(c,"UPDATE %ssievescripts SET active = 1 WHERE owner_idnr = ? AND name = ?", DBPFX);
db_stmt_set_u64(s, 1, user_idnr);
db_stmt_set_str(s, 2, scriptname);
db_stmt_exec(s);
db_commit_transaction(c);
t = TRUE;
CATCH(SQLException)
LOG_SQLERROR;
db_rollback_transaction(c);
FINALLY
db_con_close(c);
END_TRY;
return t;
}
static long long int _update_recent(volatile GList *slices, uint64_t seq)
{
INIT_QUERY;
Connection_T c;
volatile long long int count = 0;
if (! (slices = g_list_first(slices)))
return count;
c = db_con_get();
TRY
db_begin_transaction(c);
while (slices) {
Connection_execute(c, "UPDATE %smessages SET recent_flag = 0, seq = %" PRIu64
" WHERE recent_flag = 1 AND seq < %" PRIu64
" AND message_idnr IN (%s)",
DBPFX, seq, seq, (gchar *)slices->data);
count += Connection_rowsChanged(c);
if (! g_list_next(slices)) break;
slices = g_list_next(slices);
}
db_commit_transaction(c);
CATCH(SQLException)
LOG_SQLERROR;
count = DM_EQUERY;
db_rollback_transaction(c);
FINALLY
db_con_close(c);
g_list_destroy(slices);
END_TRY;
return count;
}
int dm_sievescript_rename(uint64_t user_idnr, char *scriptname, char *newname)
{
int active = 0;
Connection_T c; ResultSet_T r; PreparedStatement_T s; volatile int t = FALSE;
assert(scriptname);
/*
* According to the draft RFC, a script with the same
* name as an existing script should *atomically* replace it.
*/
c = db_con_get();
TRY
db_begin_transaction(c);
s = db_stmt_prepare(c,"SELECT active FROM %ssievescripts WHERE owner_idnr = ? AND name = ?", DBPFX);
db_stmt_set_u64(s,1, user_idnr);
db_stmt_set_str(s,2, newname);
r = db_stmt_query(s);
if (db_result_next(r)) {
active = db_result_get_int(r,0);
db_con_clear(c);
s = db_stmt_prepare(c, "DELETE FROM %ssievescripts WHERE owner_idnr = ? AND name = ?", DBPFX);
db_stmt_set_u64(s, 1, user_idnr);
db_stmt_set_str(s, 2, newname);
db_stmt_exec(s);
}
db_con_clear(c);
s = db_stmt_prepare(c, "UPDATE %ssievescripts SET name = ?, active = ? WHERE owner_idnr = ? AND name = ?", DBPFX);
db_stmt_set_str(s, 1, newname);
db_stmt_set_int(s, 2, active);
db_stmt_set_u64(s, 3, user_idnr);
db_stmt_set_str(s, 4, scriptname);
db_stmt_exec(s);
t = db_commit_transaction(c);
CATCH(SQLException)
LOG_SQLERROR;
t = DM_EQUERY;
db_rollback_transaction(c);
FINALLY
db_con_close(c);
END_TRY;
return t;
}
gint dive_db_insert(gchar *dive_datetime,gulong dive_duration,gdouble dive_maxdepth,gdouble dive_mintemp,gdouble dive_maxtemp, gchar *dive_notes,gint site_id,gdouble dive_visibility, gdouble dive_weight)
{
gint rc;
gchar *sqlcmd,*sqlErrMsg=NULL;
gint new_dive_id=0;
sqlcmd=sqlite3_mprintf(
"INSERT INTO Dive (dive_datetime,dive_duration,dive_maxdepth,dive_mintemp,dive_maxtemp,dive_notes,site_id,dive_visibility,dive_weight) VALUES ('%s',%lu,%f,%f,%f,'%q',%d,%f,%f)",
dive_datetime, dive_duration,dive_maxdepth,dive_mintemp,
dive_maxtemp,dive_notes,site_id,dive_visibility,dive_weight
);
if(handle_transactions) db_begin_transaction();
rc=sqlite3_exec (logbook_db, sqlcmd, NULL, 0, &sqlErrMsg);
if(rc!=SQLITE_OK) {
g_log(G_LOG_DOMAIN, G_LOG_LEVEL_ERROR,"Error in dive_db_insert()\nCode=%d\nQuery='%s'\nError Message='%s'\n",rc,sqlcmd,sqlErrMsg);
sqlite3_free(sqlErrMsg);
}
else {
new_dive_id=sqlite3_last_insert_rowid(logbook_db);
db_not_saved();
/* insert child entities for template */
if(dive_db_template_dive_number>0) {
sqlite3_free (sqlcmd);
sqlcmd =sqlite3_mprintf(
"INSERT INTO Dive_Buddy (dive_id,buddy_id) SELECT %d,buddy_id FROM Dive_Buddy JOIN Dive ON Dive_Buddy.dive_id=Dive.dive_id WHERE dive_number=%ld;"
"INSERT INTO Dive_Equipment (dive_id,equipment_id) SELECT %d,equipment_id FROM Dive_Equipment JOIN Dive ON Dive_Equipment.dive_id=Dive.dive_id WHERE dive_number=%ld;"
"INSERT INTO Dive_Type (dive_id,type_id) SELECT %d,type_id FROM Dive_Type JOIN Dive ON Dive_Type.dive_id=Dive.dive_id WHERE dive_number=%ld;"
"INSERT INTO Dive_Tank (dive_id,tank_id,dive_tank_avg_depth,dive_tank_O2,dive_tank_He,dive_tank_stime,dive_tank_etime,dive_tank_spressure,dive_tank_epressure) SELECT %d,tank_id,%f,dive_tank_O2,dive_tank_He,%d,%d,dive_tank_spressure,dive_tank_epressure FROM Dive_Tank JOIN Dive ON Dive_Tank.dive_id=Dive.dive_id WHERE dive_number=%ld",
new_dive_id, dive_db_template_dive_number, new_dive_id,
dive_db_template_dive_number, new_dive_id,
dive_db_template_dive_number, new_dive_id, NULL_DEPTH,
NULL_TIME, NULL_TIME, dive_db_template_dive_number
);
rc=sqlite3_exec (logbook_db, sqlcmd, NULL, 0, &sqlErrMsg);
if(rc != SQLITE_OK) {
g_log(G_LOG_DOMAIN,G_LOG_LEVEL_ERROR,"Error in dive_db_insert()\nCode=%d\nError Message='%s'\n",rc,sqlErrMsg);
sqlite3_free(sqlErrMsg);
new_dive_id=0;
}
}
}
if(handle_transactions) {
if(new_dive_id) db_commit_transaction();
else db_rollback_transaction();
preferences_load_template_dive_number();
}
sqlite3_free(sqlcmd);
return new_dive_id;
}
int dm_sievescript_add(uint64_t user_idnr, char *scriptname, char *script)
{
Connection_T c; ResultSet_T r; PreparedStatement_T s; volatile int t = FALSE;
assert(scriptname);
c = db_con_get();
TRY
db_begin_transaction(c);
s = db_stmt_prepare(c,"SELECT COUNT(*) FROM %ssievescripts WHERE owner_idnr = ? AND name = ?", DBPFX);
db_stmt_set_u64(s, 1, user_idnr);
db_stmt_set_str(s, 2, scriptname);
r = db_stmt_query(s);
if (db_result_next(r)) {
db_con_clear(c);
s = db_stmt_prepare(c,"DELETE FROM %ssievescripts WHERE owner_idnr = ? AND name = ?", DBPFX);
db_stmt_set_u64(s, 1, user_idnr);
db_stmt_set_str(s, 2, scriptname);
db_stmt_exec(s);
}
db_con_clear(c);
s = db_stmt_prepare(c,"INSERT INTO %ssievescripts (owner_idnr, name, script, active) VALUES (?,?,?,1)", DBPFX);
db_stmt_set_u64(s, 1, user_idnr);
db_stmt_set_str(s, 2, scriptname);
db_stmt_set_blob(s, 3, script, strlen(script));
db_stmt_exec(s);
t = db_commit_transaction(c);
CATCH(SQLException)
LOG_SQLERROR;
db_rollback_transaction(c);
t = DM_EQUERY;
FINALLY
db_con_close(c);
END_TRY;
return t;
}
int do_migrate(int migrate_limit)
{
Connection_T c; ResultSet_T r;
int id = 0;
volatile int count = 0;
DbmailMessage *m;
qprintf ("Migrate legacy 2.2.x messageblks to mimeparts...\n");
if (!yes_to_all) {
qprintf ("\tmigration skipped. Use -y option to perform migration.\n");
return 0;
}
qprintf ("Preparing to migrate up to %d physmessages.\n", migrate_limit);
c = db_con_get();
TRY
db_begin_transaction(c);
r = db_query(c, "SELECT DISTINCT(physmessage_id) FROM %smessageblks LIMIT %d", DBPFX, migrate_limit);
while (db_result_next(r))
{
count++;
id = db_result_get_u64(r,0);
m = dbmail_message_new(NULL);
m = dbmail_message_retrieve(m, id);
if(! dm_message_store(m)) {
if(verbose) qprintf ("%d ",id);
db_update("DELETE FROM %smessageblks WHERE physmessage_id = %d", DBPFX, id);
}
dbmail_message_free(m);
}
db_commit_transaction(c);
CATCH(SQLException)
LOG_SQLERROR;
db_rollback_transaction(c);
return -1;
FINALLY
db_con_close(c);
END_TRY;
qprintf ("Migration complete. Migrated %d physmessages.\n", count);
return 0;
}
gboolean dive_db_delete(gint dive_id,glong dive_number)
{
gint rc;
gchar *sqlcmd, *sqlErrMsg = NULL;
gboolean rval = TRUE;
sqlcmd=sqlite3_mprintf("DELETE FROM Dive WHERE dive_id=%d",dive_id);
if(handle_transactions) db_begin_transaction();
rc=sqlite3_exec (logbook_db,sqlcmd,NULL,0,&sqlErrMsg);
if(rc!=SQLITE_OK) {
g_log(G_LOG_DOMAIN,G_LOG_LEVEL_ERROR,"Error in dive_db_delete()\nCode=%d\nQuery='%s'\nError Message='%s'\n",rc,sqlcmd,sqlErrMsg);
sqlite3_free(sqlErrMsg);
rval=FALSE;
if(handle_transactions) db_rollback_transaction();
}
else {
db_not_saved();
if(handle_transactions) db_commit_transaction();
preferences_load_template_dive_number();
}
sqlite3_free(sqlcmd);
return rval;
}
static void perform(void)
{
word last_report_day = 9;
// Initialise database
{
db_init(conf[conf_db_server], conf[conf_db_user], conf[conf_db_password], conf[conf_db_name]);
word e;
if((e=database_upgrade(vstpdb)))
{
_log(CRITICAL, "Error %d in upgrade_database(). Aborting.", e);
exit(1);
}
}
{
time_t now = time(NULL);
struct tm * broken = localtime(&now);
if(broken->tm_hour >= REPORT_HOUR)
{
last_report_day = broken->tm_wday;
}
}
while(run)
{
stats[ConnectAttempt]++;
int run_receive = !open_stompy(STOMPY_PORT);
while(run_receive && run)
{
holdoff = 0;
{
time_t now = time(NULL);
struct tm * broken = localtime(&now);
if(broken->tm_hour >= REPORT_HOUR && broken->tm_wday != last_report_day)
{
last_report_day = broken->tm_wday;
report_stats();
}
}
word r = read_stompy(body, FRAME_SIZE, 64);
_log(DEBUG, "read_stompy() returned %d.", r);
if(!r && run && run_receive)
{
if(db_start_transaction())
{
run_receive = false;
}
if(run_receive) process_frame(body);
if(!db_errored)
{
if(db_commit_transaction())
{
db_rollback_transaction();
run_receive = false;
}
else
{
// Send ACK
if(ack_stompy())
{
_log(CRITICAL, "Failed to write message ack. Error %d %s", errno, strerror(errno));
run_receive = false;
}
}
}
else
{
// DB error occurred during processing of frame.
db_rollback_transaction();
run_receive = false;
}
}
else if(run && run_receive)
{
if(r != 3)
{
run_receive = false;
_log(CRITICAL, "Receive error %d on stompy connection.", r);
}
else
{
// Don't report these because it is normal on VSTP stream
// _log(MINOR, "Receive timeout on stompy connection.");
}
}
} // while(run_receive && run)
close_stompy();
{
word i;
if(holdoff < 256) holdoff += 38;
else holdoff = 256;
for(i = 0; i < holdoff + 64 && run; i++) sleep(1);
}
} // while(run)
if(interrupt)
{
_log(CRITICAL, "Terminating due to interrupt.");
}
db_disconnect();
report_stats();
}
int main(int argc, char **argv)
{
char config_file_path[256];
opt_filename = NULL;
opt_url = NULL;
fetch_all = false;
test_mode = false;
verbose = false;
opt_insecure = false;
used_insecure = false;
strcpy(config_file_path, "/etc/openrail.conf");
word usage = false;
int c;
while ((c = getopt (argc, argv, ":c:u:f:tpih")) != -1)
switch (c)
{
case 'c':
strcpy(config_file_path, optarg);
break;
case 'u':
if(!opt_filename) opt_url = optarg;
break;
case 'f':
if(!opt_url) opt_filename = optarg;
break;
case 'a':
fetch_all = true;
break;
case 't':
test_mode = true;
break;
case 'p':
verbose = true;
break;
case 'i':
opt_insecure = true;
break;
case 'h':
usage = true;
break;
case ':':
break;
case '?':
default:
usage = true;
break;
}
char * config_fail;
if((config_fail = load_config(config_file_path)))
{
printf("Failed to read config file \"%s\": %s\n", config_file_path, config_fail);
usage = true;
}
if(usage)
{
printf("%s %s Usage: %s [-c /path/to/config/file.conf] [-u <url> | -f <path> | -a] [-t | -r] [-p][-i]\n", NAME, BUILD, argv[0]);
printf(
"-c <file> Path to config file.\n"
"Data source:\n"
"default Fetch latest update.\n"
"-u <url> Fetch from specified URL.\n"
"-f <file> Use specified file. (Must already be decompressed.)\n"
"Actions:\n"
"default Apply data to database.\n"
"-t Report datestamp on download or file, do not apply to database.\n"
"Options:\n"
"-i Insecure. Circumvent certificate checks if necessary.\n"
"-p Print activity as well as logging.\n"
);
exit(1);
}
char zs[1024];
start_time = time(NULL);
debug = *conf[conf_debug];
_log_init(debug?"/tmp/tscdb.log":"/var/log/garner/tscdb.log", (debug?1:(verbose?4:0)));
_log(GENERAL, "");
_log(GENERAL, "%s %s", NAME, BUILD);
// Enable core dumps
struct rlimit limit;
if(!getrlimit(RLIMIT_CORE, &limit))
{
limit.rlim_cur = RLIM_INFINITY;
setrlimit(RLIMIT_CORE, &limit);
}
int i;
for(i = 0; i < MATCHES; i++)
{
if(regcomp(&match[i], match_strings[i], REG_ICASE + REG_EXTENDED))
{
sprintf(zs, "Failed to compile regex match %d", i);
_log(MAJOR, zs);
}
}
// Initialise database
if(db_init(conf[conf_db_server], conf[conf_db_user], conf[conf_db_password], conf[conf_db_name])) exit(1);
{
word e;
if((e=database_upgrade(cifdb)))
{
_log(CRITICAL, "Error %d in upgrade_database(). Aborting.", e);
exit(1);
}
}
run = 1;
tiploc_ignored = false;
// Zero the stats
{
word i;
for(i = 0; i < MAXStats; i++) { stats[i] = 0; }
}
if(fetch_file())
{
if(opt_url || opt_filename)
{
_log(GENERAL, "Failed to find data.");
exit(1);
}
{
char report[256];
_log(GENERAL, "Failed to fetch file.");
sprintf(report, "Failed to collect timetable update after %lld attempts.", stats[Fetches]);
email_alert(NAME, BUILD, "Timetable Update Failure Report", report);
}
exit(1);
}
char in_q = 0;
char b_depth = 0;
size_t ibuf = 0;
size_t iobj = 0;
size_t buf_end;
// Determine applicable update
{
MYSQL_RES * result0;
MYSQL_ROW row0;
last_update_id[0] = '\0';
if(!db_query("SELECT MAX(id) FROM updates_processed"))
{
result0 = db_store_result();
if((row0 = mysql_fetch_row(result0)))
{
strcpy(last_update_id, row0[0]);
}
mysql_free_result(result0);
}
}
if(last_update_id[0] == '\0')
{
_log(CRITICAL, "Failed to determine last update id from database.");
exit(1);
}
if(test_mode)
{
}
else
{
char c, pc;
pc = 0;
// Run through the file splitting off each JSON object and passing it on for processing.
// DB may have dropped out due to long delay
(void) db_connect();
if(db_start_transaction()) _log(CRITICAL, "Failed to initiate database transaction.");
while((buf_end = fread(buffer, 1, MAX_BUF, fp_result)) && run && !db_errored)
{
for(ibuf = 0; ibuf < buf_end && run && !db_errored; ibuf++)
{
c = buffer[ibuf];
if(c != '\r' && c != '\n')
{
obj[iobj++] = c;
if(iobj >= MAX_OBJ)
{
_log(CRITICAL, "Object buffer overflow!");
exit(1);
}
if(c == '"' && pc != '\\') in_q = ! in_q;
if(!in_q && c == '{') b_depth++;
if(!in_q && c == '}' && b_depth-- && !b_depth)
{
obj[iobj] = '\0';
process_object(obj);
iobj = 0;
}
}
pc = c;
}
}
fclose(fp_result);
if(db_errored)
{
_log(CRITICAL, "Update rolled back due to database error.");
(void) db_rollback_transaction();
}
else
{
_log(GENERAL, "Committing database updates...");
if(db_commit_transaction())
{
_log(CRITICAL, "Database commit failed.");
}
else
{
_log(GENERAL, "Committed.");
}
}
}
#define REPORT_SIZE 16384
char report[REPORT_SIZE];
report[0] = '\0';
_log(GENERAL, "");
_log(GENERAL, "End of run:");
if(used_insecure)
{
strcat(report, "*** Warning: Insecure download used.\n");
_log(GENERAL, "*** Warning: Insecure download used.");
}
sprintf(zs, " Elapsed time: %ld minutes", (time(NULL) - start_time + 30) / 60);
_log(GENERAL, zs);
strcat(report, zs); strcat(report, "\n");
if(test_mode)
{
sprintf(zs, "Test mode. No database changes made.");
_log(GENERAL, zs);
strcat(report, zs); strcat(report, "\n");
exit(0);
}
for(i=0; i<MAXStats; i++)
{
sprintf(zs, "%25s: %s", stats_category[i], commas_q(stats[i]));
if(i == DBError && stats[i]) strcat(zs, " ****************");
_log(GENERAL, zs);
strcat(report, zs);
strcat(report, "\n");
}
db_disconnect();
email_alert(NAME, BUILD, "Timetable Update Report", report);
exit(0);
}
static void perform(void)
{
word last_report_day = 9;
word stompy_timeout = true;
// Initialise database connection
while(db_init(conf[conf_db_server], conf[conf_db_user], conf[conf_db_password], conf[conf_db_name]) && run)
{
_log(CRITICAL, "Failed to initialise database connection. Will retry...");
word i;
for(i = 0; i < 64 && run; i++) sleep(1);
}
create_database();
handle = 0xfff0;
{
time_t now = time(NULL);
struct tm * broken = localtime(&now);
if(broken->tm_hour >= REPORT_HOUR)
{
last_report_day = broken->tm_wday;
}
last_message_count_report = now;
message_count = message_count_rel = 0;
}
// Status
status_last_td_processed = 0;
{
word describer;
for(describer = 0; describer < DESCRIBERS; describer++)
{
status_last_td_actual[describer] = last_td_processed[describer] = 0;
timeout_reported[describer] = false;
}
}
// Signalling
{
word i,j;
for(i = 0; i < DESCRIBERS; i++)
{
for(j = 0; j < SIG_BYTES; j++)
{
signalling[i][j] = 0xffff;
}
}
}
while(run)
{
stats[ConnectAttempt]++;
int run_receive = !open_stompy(STOMPY_PORT);
while(run_receive && run)
{
holdoff = 0;
{
time_t now = time(NULL);
struct tm * broken = localtime(&now);
if(broken->tm_hour >= REPORT_HOUR && broken->tm_wday != last_report_day)
{
last_report_day = broken->tm_wday;
report_stats();
}
if(now - last_message_count_report > MESSAGE_COUNT_REPORT_INTERVAL)
{
char query[256];
sprintf(query, "INSERT INTO message_count VALUES('tddb', %ld, %d)", now, message_count);
if(!db_query(query))
{
message_count = 0;
last_message_count_report = now;
}
sprintf(query, "INSERT INTO message_count VALUES('tddbrel', %ld, %d)", now, message_count_rel);
if(!db_query(query))
{
message_count_rel = 0;
}
}
}
int r = read_stompy(body, FRAME_SIZE, 64);
_log(DEBUG, "read_stompy() returned %d.", r);
if(!r && run && run_receive)
{
if(stompy_timeout)
{
_log(MINOR, "TD message stream - Receive OK.");
stompy_timeout = false;
}
if(db_start_transaction())
{
run_receive = false;
}
if(run_receive) process_frame(body);
if(!db_errored)
{
if(db_commit_transaction())
{
db_rollback_transaction();
run_receive = false;
}
else
{
// Send ACK
if(ack_stompy())
{
_log(CRITICAL, "Failed to write message ack. Error %d %s", errno, strerror(errno));
run_receive = false;
}
}
}
else
{
// DB error.
db_rollback_transaction();
run_receive = false;
}
}
else if(run && run_receive)
{
if(r != 3)
{
run_receive = false;
_log(CRITICAL, "Receive error %d on stompy connection.", r);
}
else
{
if(!stompy_timeout) _log(MINOR, "TD message stream - Receive timeout.");
stompy_timeout = true;
}
}
if(run) check_timeout();
} // while(run_receive && run)
close_stompy();
if(run) check_timeout();
{
word i;
if(holdoff < 256) holdoff += 34;
else holdoff = 256;
for(i = 0; i < holdoff + 64 && run; i++) sleep(1);
}
}
if(interrupt)
{
_log(CRITICAL, "Terminating due to interrupt.");
}
db_disconnect();
report_stats();
}
