void ns_machine_analysis_data_loader::set_up_spec_to_load(const unsigned long & region_id, unsigned long & sample_id, unsigned long & experiment_id_a, ns_sql & sql, const bool load_excluded_regions){
const bool region_specified(region_id != 0);
const bool sample_specified(sample_id != 0);
if (region_id == 0 && sample_id == 0 && experiment_id_a==0)
throw ns_ex("No data requested!");
if (region_id != 0){
sql << "SELECT sample_id FROM sample_region_image_info WHERE id = " << region_id;
ns_sql_result res;
sql.get_rows(res);
if (res.size() == 0)
throw ns_ex("ns_experiment_movement_results::load()::Could not load region information ") << region_id;
sample_id = atol(res[0][0].c_str());
}
if (sample_id != 0){
sql << "SELECT experiment_id FROM capture_samples WHERE id = " << sample_id;
ns_sql_result res;
sql.get_rows(res);
if (res.size() == 0)
throw ns_ex("ns_experiment_movement_results::load()::Could not load sample information ") << sample_id;
experiment_id_a = atol(res[0][0].c_str());
}
sql << "SELECT name FROM experiments WHERE id=" << experiment_id_a;
ns_sql_result res;
sql.get_rows(res);
if (res.size() == 0)
throw ns_ex("ns_experiment_movement_results::load()::Could not load experiment id=") << experiment_id_a;
experiment_name_ = res[0][0];
experiment_id_ = experiment_id_a;
std::vector<unsigned long> sample_ids;
if (!region_specified && !sample_specified){
sql << "SELECT id FROM capture_samples WHERE censored=0 AND experiment_id = " << experiment_id_a;
if (sample_id != 0)
sql << " AND id = " << sample_id;
ns_sql_result samp;
sql.get_rows(samp);
samples.resize(samp.size());
for (unsigned int i = 0; i < samp.size(); i++)
samples[i].set_id(atol(samp[i][0].c_str()));
}
else{
//add just the sample
samples.resize(1,sample_id);
}
}
void ns_capture_sample_region_data::generate_summary_info(ns_capture_sample_region_data_timepoint & mean_timepoint,ns_capture_sample_region_data_timepoint & first_timepoint,ns_capture_sample_region_data_timepoint & last_timepoint){
mean_timepoint.set_as_zero();
first_timepoint.set_as_zero();
last_timepoint.set_as_zero();
if (timepoints.size() == 0)
throw ns_ex("ns_capture_sample_region_data::generate_summary_info()::No timepoints present!");
unsigned long decile_size = timepoints.size()/10;
if (decile_size == 0)
decile_size = 1;
for (unsigned int i = 0; i < decile_size; i++){
first_timepoint+=timepoints[i];
mean_timepoint+=timepoints[i];
}
for (unsigned int i = decile_size; i < timepoints.size()-decile_size; i++)
mean_timepoint+=timepoints[i];
for (unsigned int i = timepoints.size()-decile_size; i < timepoints.size(); i++){
last_timepoint+=timepoints[i];
mean_timepoint+=timepoints[i];
}
first_timepoint/=(double)decile_size;
last_timepoint/=(double)decile_size;
mean_timepoint/=timepoints.size();
}
void ns_machine_analysis_sample_data::load(const ns_death_time_annotation_set::ns_annotation_type_to_load & annotation_type_to_load,const unsigned long sample_id, const ns_region_metadata & sample_metadata,ns_sql & sql,
const unsigned long specific_region_id, const bool include_excluded_regions, const ns_machine_analysis_region_data::ns_loading_details & loading_details){
bool calculate_missing_data = false;
device_name_ = sample_metadata.device;
ns_sql_result reg;
sql << "SELECT r.id FROM sample_region_image_info as r WHERE r.sample_id = " << sample_id << " AND r.censored=0 ";
if (!include_excluded_regions)
sql << " AND r.excluded_from_analysis=0";
if (specific_region_id!=0)
sql << " AND r.id = " << specific_region_id;
sql << " ORDER BY r.name";
sql.get_rows(reg);
if (reg.empty() && specific_region_id!=0)
throw ns_ex("Could not identify region ") << specific_region_id << ". Was it excluded?";
regions.reserve(reg.size());
for (unsigned int i = 0; i < reg.size(); i++){
try{
unsigned int s = regions.size();
regions.resize(s+1);
unsigned long region_id = atol(reg[i][0].c_str());
regions[s].metadata = sample_metadata;
regions[s].metadata.load_only_region_info_from_db(region_id,"",sql);
regions[s].metadata.technique = "Lifespan Machine";
regions[s].load_from_db(annotation_type_to_load,loading_details,region_id,sql);
//break;
}
catch(ns_ex & ex){
std::cerr << regions.rbegin()->metadata.sample_name << "::" << regions.rbegin()->metadata.region_name << ": " << ex.text() << "\n";
regions.pop_back();
}
}
sample_name_ = sample_metadata.sample_name;
sample_id_ = sample_id;
}
void ns_capture_sample_region_statistics_set::set_sample_data(const ns_capture_sample_statistics_set & samples){
for (unsigned int i = 0; i < regions.size(); i++){
std::map<unsigned long,ns_capture_sample_image_statistics *>::const_iterator p = samples.samples_sorted_by_id.find(regions[i].metadata.sample_id);
if (p==samples.samples_sorted_by_id.end())
throw ns_ex("Could not find sample statistics for sample id ") << regions[i].metadata.sample_id << "(" << regions[i].metadata.sample_name << ")" << "\n";
regions[i].set_sample_info(*p->second);
}
}
ns_time_series_denoising_parameters ns_time_series_denoising_parameters::load_from_db(const unsigned long region_id, ns_sql & sql){
sql << "SELECT time_series_denoising_flag FROM sample_region_image_info WHERE id = " << region_id;
ns_sql_result res;
sql.get_rows(res);
if (res.size() == 0)
throw ns_ex("ns_time_series_denoising_parameters::load_from_db()::Could not find region ") << region_id << " in db";
ns_time_series_denoising_parameters p;
p.subtract_out_median_movement_score_from_time_series = res[0][0] == "1";
return p;
}
void ns_table_format_processor::sql_get_column_names(const std::string & table_name, std::vector<std::string> & column_names, ns_image_server_sql * sql) {
*sql << "DESCRIBE " << table_name;
ns_sql_result res;
sql->get_rows(res);
column_names.resize(res.size());
for (unsigned int i = 0; i < res.size(); i++)
column_names[i] = res[i][0];
if (column_names.size() == 0)
throw ns_ex("No column names received for table ") << table_name << "!";
}
ns_time_series_denoising_parameters ns_time_series_denoising_parameters::load_from_db(const unsigned long region_id, ns_sql & sql){
sql << "SELECT time_series_denoising_flag FROM sample_region_image_info WHERE id = " << region_id;
ns_sql_result res;
sql.get_rows(res);
if (res.size() == 0)
throw ns_ex("ns_time_series_denoising_parameters::load_from_db()::Could not find region ") << region_id << " in db";
ns_time_series_denoising_parameters p;
p.movement_score_normalization = (ns_time_series_denoising_parameters::ns_movement_score_normalization_type)atol(res[0][0].c_str());
return p;
}
void ns_capture_sample_region_data::set_sample_info(const ns_capture_sample_image_statistics & sample_stats){
unsigned long s(0);
unsigned long last_rt(0),last_st(0);
for (unsigned int i = 0; i < timepoints.size(); i++){
while(true){
if (timepoints[i].time == sample_stats.scans[s].scheduled_time_date)
break;
s++;
if (s >= sample_stats.scans.size())
throw ns_ex("Could not find time ") << timepoints[i].time;
}
if (timepoints[i].time < last_rt)
throw ns_ex("Region Timepoints is not sorted!");
if ((unsigned long)sample_stats.scans[s].start_time < last_st)
throw ns_ex("Sample Timepoints is not sorted!");
last_rt = timepoints[i].time;
last_st = sample_stats.scans[s].start_time;
timepoints[i].sample_statistics = sample_stats.scans[s];
}
}
void ns_table_format_processor::get_column_name_indicies(const std::vector<ns_table_column_spec> & spec) {
unsigned long num_found(0);
for (long i = 0; i < column_names.size() && num_found < column_names.size(); i++) {
for (long j = 0; j < spec.size(); j++) {
if (column_names[i] == spec[j].name) {
*spec[j].index = i;
num_found++;
}
}
}
if (num_found != spec.size())
throw ns_ex("ns_column_name_info::get_column_name_indicies::Could not find all columns!");
}
void * ns_usb_context::get_context(){
#ifndef WIN32
#ifdef USE_NEW_USB
if (context == 0){
libusb_context * c;
int r = libusb_init(&c);
context = c;
if (r < 0)
throw ns_ex("ns_usb_control::Could not initialize libusb");
}
#else
if (context == 0){
usb_init();
context = (void *)this;
}
#endif
#endif
return context;
};
bool ns_usb_control::reset_device(int bus,int device_id){
#ifdef _WIN32
return false;
#else
#ifdef USE_NEW_USB
libusb_device **devices;
int r;
ssize_t cnt;
ns_acquire_lock_for_scope lock(usb_context.libusb_lock,__FILE__,__LINE__);
libusb_context * context = (libusb_context *)usb_context.get_context();
try{
cnt = libusb_get_device_list(context, &devices);
if (cnt < 0)
throw ns_ex("ns_usb_control::Could not enumerate devices!");
try{
libusb_device *d;
libusb_device *requested_device(0);
int i = 0;
while ((d= devices[i++]) != NULL) {
struct libusb_device_descriptor desc;
unsigned long bus_number(libusb_get_bus_number(d));
unsigned long device_address(libusb_get_device_address(d));
if (bus_number == bus && device_address == device_id){
requested_device = d;
}
else
libusb_unref_device(d);//don't hold onto devices that aren't used
}
if(requested_device==0){
libusb_free_device_list(devices,0);
lock.release();
return 0;
}
struct libusb_device_handle *device_handle;
int err = libusb_open(requested_device,&device_handle);
if (err != 0)
throw ns_ex("ns_usb_control::Could not open device: ") << err;
err = libusb_reset_device(device_handle);
if (err){
libusb_close(device_handle);
libusb_unref_device(requested_device);
throw ns_ex("ns_usb::reset_device::Could not reset device: ") << err;
}
libusb_close(device_handle);
libusb_unref_device(requested_device);
}
catch(...){
//don't leak device name lists
libusb_free_device_list(devices, 0);
throw;
}
}
catch(...){
//release the context so that it can be reset
usb_context.release_context();
throw;
}
libusb_free_device_list(devices, 0);
lock.release();
return true;
#else
ns_acquire_lock_for_scope lock(usb_context.libusb_lock,__FILE__,__LINE__);
usb_find_busses();
usb_find_devices();
struct usb_device *device(0);
ssize_t i = 0;
int err = 0;
for (struct usb_bus * cur_bus = usb_busses; cur_bus; cur_bus= cur_bus->next){
int cur_bus_number(atoi(cur_bus->dirname));
for (struct usb_device *dev = cur_bus->devices; dev; dev = dev->next){
int cur_device_id(dev->devnum);
//char * filename(dev->filename);
if (cur_bus_number == bus && device_id == cur_device_id){
device = dev;
break;
}
}
}
if(!device){
lock.release();
return 0;
}
struct usb_dev_handle *handle;
handle = usb_open(device);
try{
if (err)
throw ns_ex("ns_usb::reset_device::Could not acquire handle for device");
err = usb_reset(handle);
//err2 = usb_reset(handle);
if (err)
throw ns_ex("ns_usb::reset_device::Could not reset device");
usb_close(handle);
}
catch(...){
usb_close(handle);
throw;
}
lock.release();
return true;
#endif
#endif
}
/*
void ns_capture_sample_region_statistics_set::output_plate_statistics_with_mortality_data(const ns_survival_data_summary_aggregator & survival_data, std::ostream & o){
ns_capture_sample_region_data_timepoint mean,first,last;
for (unsigned int i = 0; i < regions.size(); i++){
regions[i].metadata.out_JMP_plate_identity_data(o);
o << ",";
o << regions[i].censored?"1":"0";
o << ",";
o << regions[i].excluded?"1":"0";
o << ",";
ns_survival_data_summary_aggregator::ns_plate_list::const_iterator region_mortality_data(survival_data.plate_list.find(regions[i].metadata.plate_name()));
if (region_mortality_data == survival_data.plate_list.end()){
survival_data.out_JMP_empty_summary_data(o);
}
else{
survival_data.out_JMP_summary_data(region_mortality_data,o);
}
o << ",";
regions[i].generate_summary_info(mean,first,last);
mean.output_jmp_data(o,mean.time,false,false,",");
first.output_jmp_data(o,mean.time,false,false,",");
last.output_jmp_data(o,mean.time,false,false,"\n");
}
}
*/
void ns_capture_sample_region_statistics_set::load_whole_experiment(const unsigned long experiment_id,ns_sql & sql,bool process_raw_image_stats){
std::string experiment_name;
sql << "SELECT name FROM experiments WHERE id = " << experiment_id;
ns_sql_result res1;
sql.get_rows(res1);
if (res1.size() == 0)
throw ns_ex("Could not find experiment id ") << experiment_id;
experiment_name = res1[0][0];
ns_sql_result res;
sql << "SELECT id,name,device_name,censored,description,excluded_from_analysis,incubator_name, incubator_location "
"FROM capture_samples WHERE experiment_id=" << experiment_id;
sql.get_rows(res);
ns_genotype_fetcher genotypes;
genotypes.load_from_db(&sql);
for (unsigned int j = 0; j < res.size(); j++){
ns_region_metadata sample_metadata;
sample_metadata.sample_id = atol(res[j][0].c_str());
sample_metadata.experiment_name = experiment_name;
sample_metadata.sample_name = res[j][1];
sample_metadata.device = res[j][2];
sample_metadata.incubator_name = res[j][6];
sample_metadata.incubator_location = res[j][7];
bool sample_censored=(res[j][3]!="0"),
sample_excluded=(res[j][5]!="0");
std::string sample_details = res[j][4];
sql << "SELECT id,censored,excluded_from_analysis FROM sample_region_image_info WHERE sample_id=" << sample_metadata.sample_id;
ns_sql_result res2;
sql.get_rows(res2);
unsigned long plate_index(regions.size());
regions.resize(plate_index+res2.size());
for (unsigned long k = 0; k < res2.size(); ++k){
ns_region_metadata metadata(sample_metadata);
unsigned long region_id(atol(res2[k][0].c_str()));
metadata.load_from_db(region_id,"",sql);
bool region_censored(res2[k][1]!="0"),
region_excluded(res2[k][2]!="0");
// if (region_censored || region_excluded){
// char a;
// a++;
// }
//std::cerr << "EX";
regions[plate_index+k].load_from_db(region_id,metadata,sample_censored || region_censored,sample_excluded || region_excluded,sql,process_raw_image_stats);
if (sample_details.size() > 0)
regions[plate_index+k].metadata.details += sample_details;
}
}
build_id_mapping();
}
/*
ns_64_bit ns_atoi64(const char * s){
#ifdef _WIN32
return _atoi64(s);
#else
return atoll(s);
#endif
}*/
void ns_capture_sample_image_statistics::load_from_db(unsigned long id,ns_sql & sql){
sample_id = id;
sql << "SELECT name,device_name, position_x,position_y,size_x,size_y, experiment_id FROM capture_samples WHERE id="<<id;
ns_sql_result res;
sql.get_rows(res);
if(res.size()==0)
throw ns_ex("Could not find sample id ") << sample_id << " in the db.";
sample_name = res[0][0];
device_name = res[0][1];
position.x = atof(res[0][2].c_str());
position.y = atof(res[0][3].c_str());
size.x = atof(res[0][4].c_str());
size.y = atof(res[0][5].c_str());
sql << "SELECT name FROM experiments WHERE id = " << res[0][6];
experiment_name = sql.get_value();
sql << "SELECT s.scheduled_time, s.time_at_start, s.time_at_finish, s.missed, s.problem,s.time_at_imaging_start,"
"s.time_spent_reading_from_device,s.time_spent_writing_to_disk,"
"s.total_time_during_read,s.time_during_transfer_to_long_term_storage,"
"s.time_during_deletion_from_local_storage, "
"s.total_time_spent_during_programmed_delay,"
"t.intensity_average,t.intensity_std,t.intensity_entropy, t.intensity_top_percentile,t.intensity_bottom_percentile, "
"i.registration_vertical_offset,i.registration_horizontal_offset "
"FROM (capture_schedule as s LEFT OUTER JOIN captured_images as i ON i.id = s.captured_image_id) "
"LEFT OUTER JOIN image_statistics as t ON i.image_statistics_id = t.id "
"WHERE s.sample_id=" << sample_id << " AND s.scheduled_time < UNIX_TIMESTAMP(NOW()) ORDER BY s.scheduled_time ASC";
sql.get_rows(res);
scans.resize(res.size());
for (unsigned int i = 0; i < scans.size(); i++){
scans[i].scheduled_time_date = atol(res[i][0].c_str());
scans[i].start_time_date = atol(res[i][1].c_str());
scans[i].stop_time_date = atol(res[i][2].c_str());
scans[i].data_start_time_date = atol(res[i][5].c_str());
scans[i].scan_position = position;
scans[i].scan_size = size;
scans[i].missed = (res[i][3]!="0");
scans[i].problem = (res[i][4]!="0");
scans[i].time_spent_reading_from_device = ns_atoi64(res[i][6].c_str())/1000.0/1000.0/60;
scans[i].time_spent_writing_to_disk = ns_atoi64(res[i][7].c_str())/1000.0/1000.0;
scans[i].total_time_during_read = ns_atoi64(res[i][8].c_str())/1000.0/1000.0/60.0;
scans[i].total_time_spent_during_programmed_delay = ns_atoi64(res[i][11].c_str())/1000.0/60.0;
scans[i].time_during_transfer_to_long_term_storage = ns_atoi64(res[i][9].c_str())/1000.0/1000.0;
scans[i].time_during_deletion_from_local_storage = ns_atoi64(res[i][10].c_str())/1000.0/1000.0;
scans[i].registration_offset.y = atol(res[i][17].c_str());
scans[i].registration_offset.x = atol(res[i][18].c_str());
if (res[i][10] != "NULL"){
scans[i].image_stats.image_statistics.mean = atof(res[i][12].c_str());
scans[i].image_stats.image_statistics.variance = atof(res[i][13].c_str());
scans[i].image_stats.image_statistics.entropy = atof(res[i][14].c_str());
scans[i].image_stats.image_statistics.top_percentile_average= atof(res[i][15].c_str());
scans[i].image_stats.image_statistics.bottom_percentile_average= atof(res[i][16].c_str());
}
else{
scans[i].image_stats.image_statistics.mean = 0;
scans[i].image_stats.image_statistics.variance = 0;
scans[i].image_stats.image_statistics.entropy = 0;
scans[i].image_stats.image_statistics.top_percentile_average = 0;
scans[i].image_stats.image_statistics.bottom_percentile_average = 0;
}
// if (scans[i].error != 0)
// cerr << "ERROR FOUND";
}
if (scans.size() == 0)
return;
//normalize times to start of experiment
date_of_first_sample_scan = scans[0].scheduled_time_date;
for (unsigned int i = 0; i < scans.size(); i++){
scans[i].date_of_first_sample_scan = date_of_first_sample_scan;
if (scans[i].scheduled_time_date != 0)
scans[i].scheduled_time=scans[i].scheduled_time_date-date_of_first_sample_scan;
else scans[i].scheduled_time = 0;
if (scans[i].start_time_date != 0)
scans[i].start_time=scans[i].start_time_date-date_of_first_sample_scan;
else scans[i].start_time = 0;
if (scans[i].stop_time_date != 0)
scans[i].stop_time=scans[i].stop_time_date-date_of_first_sample_scan;
else scans[i].stop_time = 0;
if (scans[i].data_start_time_date != 0)
scans[i].data_start_time=scans[i].data_start_time_date - date_of_first_sample_scan;
else scans[i].data_start_time = 0;
}
calculate_running_statistics();
}
bool ns_machine_analysis_region_data::load_from_db(const ns_death_time_annotation_set::ns_annotation_type_to_load & annotation_type_to_load,const ns_loading_details & details,const unsigned long region_id,ns_sql & sql){
death_time_annotation_set.clear();
metadata.region_id = region_id;
if (annotation_type_to_load == ns_death_time_annotation_set::ns_recalculate_from_movement_quantification_data)
return recalculate_from_saved_movement_quantification(region_id,sql);
ns_image_server_results_subject results_subject;
results_subject.region_id = region_id;
std::vector<ns_image_server_results_storage::ns_death_time_annotation_file_type> files_to_open;
switch(annotation_type_to_load){
case ns_death_time_annotation_set::ns_all_annotations:
case ns_death_time_annotation_set::ns_censoring_and_movement_states:
files_to_open.push_back(ns_image_server_results_storage::ns_worm_position_annotations);
files_to_open.push_back(ns_image_server_results_storage::ns_censoring_and_movement_transitions);
break;
case ns_death_time_annotation_set::ns_censoring_data:
case ns_death_time_annotation_set::ns_movement_transitions:
case ns_death_time_annotation_set::ns_censoring_and_movement_transitions:
files_to_open.push_back(ns_image_server_results_storage::ns_censoring_and_movement_transitions);
break;
case ns_death_time_annotation_set::ns_movement_states:
files_to_open.push_back(ns_image_server_results_storage::ns_worm_position_annotations);
case ns_death_time_annotation_set::ns_no_annotations:
break;
default: throw ns_ex("ns_machine_analysis_data_loader::Unknown annotation type request");
}
bool could_load_all_files(true);
for (unsigned int i = 0; i < files_to_open.size(); i++){
ns_image_server_results_file results(image_server.results_storage.machine_death_times(results_subject,files_to_open[i],"time_path_image_analysis",sql));
ns_acquire_for_scope<std::istream> tp_i(results.input());
if (tp_i.is_null()){
could_load_all_files = false;
continue;
}
death_time_annotation_set.read(annotation_type_to_load,tp_i(),details==ns_machine_analysis_region_data::ns_exclude_fast_moving_animals);
tp_i.release();
}
if (!could_load_all_files)
return false;
//remove out of bounds data
/* for (unsigned int i = 0 ; i < death_time_annotation_set.size(); i++)
if (death_time_annotation_set.events[i].type == ns_moving_worm_disappearance){
ns_death_time_annotation a(death_time_annotation_set.events[i]);
std::cerr << a.description() << "\n";
}*/
if (metadata.time_of_last_valid_sample != 0){
for (ns_death_time_annotation_set::iterator p = death_time_annotation_set.begin(); p != death_time_annotation_set.end();){
// if(p->stationary_path_id.group_id == 28)
// std::cerr << "MA";
if (p->time.fully_unbounded())
throw ns_ex("Fully unbounded interval encountered!");
if ((!p->time.period_start_was_not_observed && p->time.period_start > metadata.time_of_last_valid_sample))
p->time.period_start_was_not_observed = true;
if ((!p->time.period_end_was_not_observed && p->time.period_end > metadata.time_of_last_valid_sample))
p->time.period_end_was_not_observed = true;
if (p->time.fully_unbounded())
p = death_time_annotation_set.erase(p);
else{
//events that straddle the externally specified last observation become unbounded
if (!p->time.period_end > metadata.time_of_last_valid_sample){
p->time.period_end_was_not_observed;
}
p++;
}
}
}
return true;
}
void ns_buffered_capture_scheduler::update_local_buffer_from_central_server(ns_image_server_device_manager::ns_device_name_list & connected_devices,ns_local_buffer_connection & local_buffer, ns_sql & central_db) {
if (connected_devices.size() == 0)
return;
ns_acquire_lock_for_scope lock(buffer_capture_scheduler_lock,__FILE__,__LINE__);
local_buffer.clear_query();
central_db.clear_query();
std::string local_time = local_buffer.get_value("SELECT UNIX_TIMESTAMP(NOW())"),
central_time = central_db.get_value("SELECT UNIX_TIMESTAMP(NOW())");
const ns_synchronized_time update_start_time(atol(local_time.c_str())-10,atol(central_time.c_str())-10);//go ten seconds into the past
//to make sure all writes
//are committed
//now we update the local buffer to the central node.
commit_all_local_schedule_changes_to_central_db(update_start_time,local_buffer,central_db);
//now that all the local buffer data is reflected in the central database, we check to see if there is any new data in the central database.
//if so, we wipe the local buffer and update everything.
capture_schedule.load_if_needed(¢ral_db);
//get any new or updated capture schedule events
central_db << "SELECT sched.id, samp.id, samp.experiment_id, UNIX_TIMESTAMP(sched.time_stamp),UNIX_TIMESTAMP(samp.time_stamp)";
for (unsigned int i = 0; i < capture_schedule.table_format.column_names.size(); i++)
central_db << ",`sched`.`" << capture_schedule.table_format.column_names[i] << "`";
central_db << " FROM capture_schedule as sched, capture_samples as samp "
<< "WHERE (samp.device_name='" << connected_devices[0].name << "'";
for (unsigned int i = 1; i < connected_devices.size(); i++)
central_db << " OR samp.device_name='" << connected_devices[i].name << "'";
central_db << ")"
<< " AND sched.time_at_start = 0 "
<< " AND sched.sample_id = samp.id "
<< " AND sched.time_at_finish = 0 "
//here, we could bring the entire local database completely up to date
//but only scans in the future will make any difference, so we only download
//those who are still scheduled for the future
//this old command would fully update the database, as time_of_last_update_from_central_db
//would be set to 0
//<< " AND sched.scheduled_time > " << (time_of_last_update_from_central_db.remote_time-image_server.maximum_allowed_local_scan_delay()) //only get events in the future
//however, now we only grab the future, relevant scans.
<< " AND sched.scheduled_time > " << (update_start_time.remote_time-image_server.maximum_allowed_local_scan_delay()) //only get events in the future
<< " AND sched.time_stamp > FROM_UNIXTIME(" << time_of_last_update_from_central_db.remote_time <<") "
<< " AND sched.time_stamp <= FROM_UNIXTIME(" << update_start_time.remote_time << ") "
<< " ORDER BY sched.scheduled_time ASC";
ns_sql_result new_schedule;
central_db.get_rows(new_schedule);
std::set<unsigned long> altered_experiment_ids;
std::set<unsigned long> altered_sample_ids;
for (unsigned int i = 0; i < new_schedule.size(); i++) {
// if (atol(new_schedule[i][4].c_str()) > central_time_of_last_update_from_central_db){
altered_sample_ids.insert(atol(new_schedule[i][1].c_str()));
altered_experiment_ids.insert(atol(new_schedule[i][2].c_str()));
// }
}
const unsigned long new_timestamp(update_start_time.local_time);
if (new_schedule.size() != 0) {
if (new_schedule.size() > 4)
image_server.register_server_event(ns_image_server_event("ns_buffered_capture_scheduler::")
<< new_schedule.size() << " new capture schedule entries found. Updating local buffer.",¢ral_db);
//if samples or experiments have changed or added, update them.
//we need to do this *before* updating the capture schedule,
//as the addition of a capture schedule item might trigger a scan immediately
//and that scan will fail if the sample and experiemnts information isn't already in the local database.
if (altered_sample_ids.size() > 0) {
capture_samples.load_if_needed("capture_samples",¢ral_db);
experiments.load_if_needed("experiments",¢ral_db);
std::string sample_where_clause(std::string(" WHERE ") + ns_compile_sql_where_clause(altered_sample_ids,"id")),
experiment_where_clause(std::string(" WHERE ") + ns_compile_sql_where_clause(altered_experiment_ids,"id"));
ns_sql_result capture_sample_data;
ns_get_all_column_data_from_table("capture_samples",capture_samples.column_names,sample_where_clause,capture_sample_data,¢ral_db);
ns_sql_result experiment_data;
ns_get_all_column_data_from_table("experiments",experiments.column_names,experiment_where_clause,experiment_data,¢ral_db);
std::cerr << "Updating local buffer with information about " << capture_sample_data.size() << " samples\n";
//local_buffer_db.send_query("DELETE FROM buffered_capture_samples");
if (capture_samples.time_stamp_column_id == -1)
throw ns_ex("Could not find capture sample time stamp column!");
long last_displayed_percent(-5);
for(unsigned int i = 0; i < capture_sample_data.size(); i++) {
const long percent((100*i)/capture_sample_data.size());
if (percent >= last_displayed_percent+5) {
std::cerr << percent << "%...";
last_displayed_percent = percent;
}
std::string values;
values += "`";
values += capture_samples.column_names[0] + "`='" + local_buffer.escape_string(capture_sample_data[i][0]) + "'";
for (unsigned int j = 1; j < capture_samples.column_names.size(); j++) {
if (j == capture_samples.time_stamp_column_id) //we need to update the local time stamp here, so that if there might be a clock asynchrony between the
continue; //central server and local server that would allow remote timestamps to be in the future according to local
//which would trigger the local server to update the central in the next check, ad infinitum.
values += std::string(",`") + capture_samples.column_names[j] + "`='" + local_buffer.escape_string(capture_sample_data[i][j]) + "'";
}
values += std::string(",`time_stamp`=FROM_UNIXTIME(") + ns_to_string(new_timestamp) + ")";
local_buffer << "INSERT INTO buffered_capture_samples SET " << values
<< " ON DUPLICATE KEY UPDATE " << values;
local_buffer.send_query();
}
std::cerr << "Done.\n";
//local_buffer.send_query("DELETE FROM buffered_experiments");
for(unsigned int i = 0; i < experiment_data.size(); i++) {
std::string values;
values += "`";
values += experiments.column_names[0] + "`='" + local_buffer.escape_string(experiment_data[i][0]) + "'";
for (unsigned int j = 1; j < experiments.column_names.size(); j++) {
if (experiments.time_stamp_column_id == j)
continue;
values += std::string(",`") + experiments.column_names[j] + "`='" + local_buffer.escape_string(experiment_data[i][j]) + "'";
}
values += std::string(",time_stamp=FROM_UNIXTIME(") + ns_to_string(new_timestamp) + ")";
local_buffer << "INSERT INTO buffered_experiments SET " << values;
local_buffer << " ON DUPLICATE KEY UPDATE " << values;
local_buffer.send_query();
}
}
std::cerr << "Updating local buffer with information about " << new_schedule.size() << " schedule time points...\n";
long last_displayed_percent = -5;
for (unsigned int i = 0; i < new_schedule.size(); i++) {
const long percent((100*i)/new_schedule.size());
if (percent >= last_displayed_percent+5) {
std::cerr << percent << "%...";
last_displayed_percent = percent;
}
std::string all_values;
all_values += "`";
all_values += capture_schedule.table_format.column_names[0] + "`='" + local_buffer.escape_string(new_schedule[i][5]) + "'";
for (unsigned int j = 1; j < capture_schedule.table_format.column_names.size(); j++) {
if (j == capture_schedule.time_stamp_column)
continue;
all_values += std::string( ", `") + capture_schedule.table_format.column_names[j] + "`='" + local_buffer.escape_string(new_schedule[i][5+j]) + "'";
}
all_values+=std::string(",time_stamp=FROM_UNIXTIME(") + ns_to_string(new_timestamp) + ")";
std::string update_values;
update_values += std::string("problem=") + new_schedule[i][5+capture_schedule.problem_column] + ","
+ std::string("scheduled_time=") + new_schedule[i][5+capture_schedule.scheduled_time_column] + ","
+ std::string("missed=") + new_schedule[i][5+capture_schedule.missed_column] + ","
+ std::string("censored=") + new_schedule[i][5+capture_schedule.censored_column] +","
+ std::string("transferred_to_long_term_storage=") + new_schedule[i][5+capture_schedule.transferred_to_long_term_storage_column] +","
+ std::string("time_during_transfer_to_long_term_storage=") + new_schedule[i][5+capture_schedule.time_during_transfer_to_long_term_storage_column] +","
+ std::string("time_during_deletion_from_local_storage=") + new_schedule[i][5+capture_schedule.time_during_deletion_from_local_storage_column] + ","
+ std::string("time_stamp=FROM_UNIXTIME(") + ns_to_string(update_start_time.local_time) + ")";
local_buffer << "INSERT INTO buffered_capture_schedule SET " << all_values
<< " ON DUPLICATE KEY UPDATE " << update_values;
local_buffer.send_query();
}
std::cerr << "Done.\n";
}
//if no changes to the schedule were made, look to see find changes made to any capture samples
else {
ns_sql_result capture_sample_data;
ns_get_all_column_data_from_table("capture_samples",capture_samples.column_names,
std::string("WHERE time_stamp >= FROM_UNIXTIME(") + ns_to_string(time_of_last_update_from_central_db.remote_time) +") "
" AND time_stamp < FROM_UNIXTIME(" + ns_to_string(update_start_time.remote_time) +") "
,capture_sample_data,¢ral_db);
if (capture_sample_data.size() > 0) {
std::cerr << "Copying over " << capture_sample_data.size() << " samples\n";
//local_buffer_db.send_query("DELETE FROM buffered_capture_samples");
for(unsigned int i = 0; i < capture_sample_data.size(); i++) {
std::string values;
values += "`";
values += capture_samples.column_names[0] + "`='" + local_buffer.escape_string(capture_sample_data[i][0]) + "'";
for (unsigned int j = 1; j < capture_samples.column_names.size(); j++)
values += std::string(",`") + capture_samples.column_names[j] + "`='" + local_buffer.escape_string(capture_sample_data[i][j]) + "'";
local_buffer << "INSERT INTO buffered_capture_samples SET " << values
<< " ON DUPLICATE KEY UPDATE " << values;
local_buffer.send_query();
}
}
}
local_buffer.send_query("COMMIT");
//lock.unlock();
commit_all_local_non_schedule_changes_to_central_db(update_start_time,local_buffer,central_db);
central_db << "SELECT k,v FROM constants WHERE time_stamp > FROM_UNIXTIME(" << time_of_last_update_from_central_db.remote_time << ")";
ns_sql_result cres;
central_db.get_rows(cres);
if (cres.size() > 0) {
std::cerr << "Updating " << cres.size() << " constants in local buffer\n";
}
for (unsigned int i = 0; i < cres.size(); i++)
image_server.set_cluster_constant_value(local_buffer.escape_string(cres[i][0]),local_buffer.escape_string(cres[i][1]),&local_buffer,update_start_time.local_time);
time_of_last_update_from_central_db = update_start_time;
store_last_update_time_in_db(time_of_last_update_from_central_db,local_buffer);
lock.release();
}
void ns_buffered_capture_scheduler::commit_all_local_schedule_changes_to_central_db(const ns_synchronized_time & update_start_time,ns_local_buffer_connection & local_buffer_sql, ns_sql & central_db) {
if (time_of_last_update_from_central_db.local_time == ns_default_update_time)
get_last_update_time(local_buffer_sql);
buffered_capture_schedule.load_if_needed(&local_buffer_sql);
ns_sql_result updated_data;
const std::string altered_data_condition(
std::string("time_stamp > FROM_UNIXTIME(") + ns_to_string(time_of_last_update_from_central_db.local_time) +
") AND time_stamp <= FROM_UNIXTIME(" + ns_to_string(update_start_time.local_time) + ") ");
const unsigned long new_timestamp(time_of_last_update_from_central_db.remote_time);
ns_get_all_column_data_from_table("buffered_capture_schedule",buffered_capture_schedule.table_format.column_names,
std::string("WHERE ") + altered_data_condition + " AND uploaded_to_central_db != 3",
updated_data,&local_buffer_sql);
std::vector<ns_db_key_mapping> mappings(updated_data.size());
if (updated_data.size() > 8)
image_server.register_server_event(ns_image_server_event("ns_buffered_capture_scheduler::Committing ") << updated_data.size() << " recorded capture events to the central database.",¢ral_db);
std::vector<ns_ex *> errors;
for (unsigned long i = 0; i < updated_data.size(); i++) {
try {
unsigned long captured_image_id = atol(updated_data[i][buffered_capture_schedule.image_id_column].c_str());
unsigned long problem_id = atol(updated_data[i][buffered_capture_schedule.problem_column].c_str());
unsigned long central_captured_image_id(0),
central_problem_id(0);
if (captured_image_id != 0 || problem_id != 0) {
central_db << "SELECT captured_image_id,problem FROM capture_schedule WHERE id = " << updated_data[i][buffered_capture_schedule.id_column];
ns_sql_result res;
central_db.get_rows(res);
if (res.size() == 0)
throw ns_ex("Could not find capture schedule entry in central db for sample id " ) << updated_data[i][buffered_capture_schedule.id_column] << " finishing at time " << updated_data[i][buffered_capture_schedule.time_at_finish_column];
central_captured_image_id = atol(res[0][0].c_str());
central_problem_id = atol(res[0][1].c_str());
}
const bool need_to_make_new_capture_image(captured_image_id != 0 && central_captured_image_id != captured_image_id);
//we need to make new entries in the central database for any new images or events
if (need_to_make_new_capture_image) {
mappings[i].captured_image.load_from_db(captured_image_id,&local_buffer_sql);
mappings[i].old_captured_image = mappings[i].captured_image;
mappings[i].old_image = mappings[i].image;
if (mappings[i].captured_image.capture_images_image_id != 0)
mappings[i].image.load_from_db(mappings[i].captured_image.capture_images_image_id,&local_buffer_sql);
}
else {
mappings[i].old_image = mappings[i].image;
mappings[i].old_captured_image = mappings[i].captured_image;
mappings[i].captured_image.captured_images_id = central_captured_image_id;
}
bool need_to_make_new_problem(problem_id != 0 && central_problem_id != problem_id);
if (need_to_make_new_problem) {
local_buffer_sql << "SELECT id,event,time,minor FROM buffered_host_event_log WHERE id = " << updated_data[i][buffered_capture_schedule.problem_column];
ns_sql_result res;
local_buffer_sql.get_rows(res);
mappings[i].old_problem_id = mappings[i].problem_id;
if (res.size() == 0) {
mappings[i].problem_id = image_server.register_server_event(ns_ex("Could not find problem id ") << updated_data[i][buffered_capture_schedule.problem_column] << " in local database buffer!",¢ral_db);
need_to_make_new_problem=false;
}
else {
mappings[i].problem_text = res[0][1];
mappings[i].problem_time = atol(res[0][2].c_str());
mappings[i].problem_minor = res[0][3] != "0";
}
}
else {
mappings[i].old_problem_id = mappings[i].problem_id;
mappings[i].problem_id = central_problem_id;
}
if (need_to_make_new_capture_image && mappings[i].image.id != 0) {
mappings[i].image.id = 0;
mappings[i].image.save_to_db(0,¢ral_db,false);
mappings[i].captured_image.capture_images_image_id = mappings[i].image.id;
}
if (need_to_make_new_capture_image) {
mappings[i].captured_image.captured_images_id = 0;
mappings[i].captured_image.save(¢ral_db);
}
if (need_to_make_new_problem) {
mappings[i].old_problem_id = mappings[i].problem_id;
ns_image_server_event ev;
ev << mappings[i].problem_text;
if (mappings[i].problem_minor) ev << ns_ts_minor_event;
ev.set_time(mappings[i].problem_time);
mappings[i].problem_id = image_server.register_server_event(ev,¢ral_db);
}
}
catch(ns_ex & ex) {
mappings[i].error << "Error while making mapping: " << ex.text();
errors.push_back(&mappings[i].error);
}
}
for (unsigned long i = 0; i < updated_data.size(); i++) {
if (mappings[i].error.text().size() > 0)
continue;
try {
central_db << "Update capture_schedule SET ";
for (unsigned int j = 0; j < buffered_capture_schedule.table_format.column_names.size(); ++j) {
if (j == buffered_capture_schedule.id_column
|| j == buffered_capture_schedule.image_id_column ||
j == buffered_capture_schedule.problem_column || j ==
buffered_capture_schedule.timestamp_column) continue;
central_db << "`" << buffered_capture_schedule.table_format.column_names[j] << "`='" << central_db.escape_string(updated_data[i][j]) << "',";
}
central_db << "captured_image_id = " << mappings[i].captured_image.captured_images_id
<< ", problem = " << mappings[i].problem_id;
//we set the timestamp as old so that it does not trigger a re-download when the server tries to update its cache
central_db << ", time_stamp=FROM_UNIXTIME("<< new_timestamp <<") ";
central_db << " WHERE id = " << updated_data[i][0];
central_db.send_query();
if (mappings[i].old_captured_image.captured_images_id != mappings[i].captured_image.captured_images_id ||
mappings[i].old_problem_id != mappings[i].problem_id) {
local_buffer_sql << "UPDATE buffered_capture_schedule SET captured_image_id = " << mappings[i].captured_image.captured_images_id
<< ", problem = " << mappings[i].problem_id << ", time_stamp = FROM_UNIXTIME("<< new_timestamp
<<") WHERE id = " << updated_data[i][buffered_capture_schedule.id_column];
local_buffer_sql.send_query();
local_buffer_sql << "DELETE FROM buffered_captured_images WHERE id = " << mappings[i].old_captured_image.captured_images_id;
local_buffer_sql.send_query();
local_buffer_sql << "DELETE FROM buffered_images WHERE id = " << mappings[i].old_image.id;
local_buffer_sql.send_query();
local_buffer_sql << "DELETE FROM buffered_host_event_log WHERE id = " << mappings[i].old_problem_id;
local_buffer_sql.send_query();
}
if (updated_data[i][buffered_capture_schedule.time_at_finish_column] != "0") {
local_buffer_sql << "DELETE FROM buffered_capture_schedule WHERE id = " << updated_data[i][buffered_capture_schedule.id_column];
local_buffer_sql.send_query();
// local_buffer_sql.clear_query();
}
}
catch(ns_ex & ex) {
mappings[i].error << "Error during central update: " << ex.text();
errors.push_back(&mappings[i].error);
}
}
for (unsigned int i = 0; i < mappings.size(); i++) {
if (mappings[i].error.text().size() > 0) {
local_buffer_sql << "UPDATE buffered_capture_schedule SET uploaded_to_central_db=3 WHERE id = " << updated_data[i][buffered_capture_schedule.id_column];
local_buffer_sql.send_query();
image_server.register_server_event(ns_image_server::ns_register_in_central_db_with_fallback,ns_ex("Could not update central db: ") << mappings[i].error.text());
}
}
//update modified sample data.
updated_data.resize(0);
if (capture_samples.column_names.size() == 0) {
capture_samples.load_column_names_from_db("capture_samples",¢ral_db);
if (capture_samples.column_names.size() == 0)
throw ns_ex("ns_buffered_capture_scheduler::commit_all_local_schedule_changes_to_central_db()::Capture sample table appears to have no columns!");
}
if (capture_samples.column_names[0] != "id")
throw ns_ex("ns_buffered_capture_scheduler::commit_all_local_schedule_changes_to_central_db()::Capture sample table does not have its id in the first column!");
ns_get_all_column_data_from_table("buffered_capture_samples",capture_samples.column_names,
std::string("WHERE ") + altered_data_condition,
updated_data,&local_buffer_sql);
if (capture_samples.time_stamp_column_id == -1)
throw ns_ex("Could not find capture sample time stamp column!");
for (unsigned int i = 0; i < updated_data.size(); i++) {
central_db << "UPDATE capture_samples SET ";
//skip id column; we don't want to cause any unneccisary db shuffling by changing ids (even if we are changing the ids to the value they already are)
central_db << capture_samples.column_names[1] << "='" << central_db.escape_string(updated_data[i][1]);
for (unsigned int j = 2; j < capture_samples.column_names.size(); ++j) {
if (j == capture_samples.time_stamp_column_id)
continue;
else central_db << "',`" << capture_samples.column_names[j] << "`='" << central_db.escape_string(updated_data[i][j]);
}
central_db << "',time_stamp=FROM_UNIXTIME(" << new_timestamp << ") ";
central_db << "WHERE id = " << updated_data[i][0];
central_db.send_query();
}
}
