Index: trunk/FACT++/src/calcsource.cc =================================================================== --- trunk/FACT++/src/calcsource.cc (revision 19067) +++ trunk/FACT++/src/calcsource.cc (revision 19068) @@ -49,20 +49,23 @@ control.add_options() ("uri,u", var()->required(), "Database link as in\n\tuser:password@server[:port]/database.") - ("ra", var()/*5.575539)*/, "") - ("dec", var()/*22.014500)*/, "") - ("focal-dist", var(4889.), "") //("source-key", var(5), "") //("source-name", var(""), "") - ("file", var(171011115)->required(), "") - ("drop", po_switch(), "Drop the table (implied create)") - ("list-files", po_switch(), "") - ("tree,t", var("Events"), "Name of the root tree to convert") - ("table", var("Events"), "") - ("update", var(""), "") - ("create", po_switch(), "") + ("file", var(0), "FileId (YYMMDDXXX), defined the events to be processed (if omitted (=0), a list of possible numbers is printed)") + ("table.events", var("Events"), "Name of the table where the events are stored") + ("table.runinfo", var("RunInfo"), "Name of the table where the run-info data is stored") + ("table.source", var("Source"), "Name of the table where the sources are stored") + ("table.position", var("Position"), "Name of the table where the calculated posiiton will be stored") + ("force", po_switch(), "Force processing even if there is no database connection") + ("drop", po_switch(), "Drop the table (implies create)") + ("create", po_switch(), "Create the table if it does not yet exist") + ("update", po_switch(), "Update the table.position instead of inserting it (disables drop, create and delete)") + ("no-insert", po_switch(), "Does not insert or update any data to any table") + ("dry-run", po_switch(), "Skip any query which changes the databse (might result in consecutive failures)") + ("ra", var(), "Right ascension of the source (use together with --dec)") + ("dec", var(), "Declination of the source (use together with --ra)") + ("focal-dist", var(4889.), "Focal distance of the camera in millimeter") ("verbose,v", var(1), "Verbosity (0: quiet, 1: default, 2: more, 3, ...)") - ("no-insert", po_switch(), "Does not insert any data to the table") - //("print-insert", po_switch(), "Print the INSERT query (note that it contains all data)") - //("print-create", po_switch(), "Print the CREATE query") + ("print-meta", po_switch(), "Print meta-queries (DROP, CREATE, DELETE, SELECT)") + ("print-insert", po_switch(), "Print the INSERT/UPDATE queries") ; @@ -192,34 +195,65 @@ // ----------------------------- Evaluate options -------------------------- + if (conf.Has("ra")^conf.Has("dec")) + throw runtime_error("--ra and --dec can only be used together"); + const bool has_radec = conf.Has("ra") && conf.Has("dec"); - const string uri = conf.Get("uri"); - const uint32_t file = conf.Get("file"); - const string tree = conf.Get("tree"); - const string table = conf.Get("table"); + double source_ra = conf.Has("ra") ? conf.Get("ra") : 0; + double source_dec = conf.Has("dec") ? conf.Get("dec") : 0; + //string source_name = conf.Get("source-name"); //uint32_t source_key = conf.Has("source-key") ? conf.Get("source-key") : 0; - double source_ra = conf.Has("ra") ? conf.Get("ra") : 0; - double source_dec = conf.Has("dec") ? conf.Get("dec") : 0; - double focal_dist = conf.Get("focal-dist"); - //const bool print_create = conf.Get("print-create"); - //const bool print_insert = conf.Get("print-insert"); - const bool drop = conf.Get("drop"); - const bool create = conf.Get("create") || drop; - const string update = conf.Get("update"); - const bool list_files = conf.Get("list-files"); - const uint16_t verbose = conf.Get("verbose"); + + const string uri = conf.Get("uri"); + const string tab_events = conf.Get("table.events"); + const string tab_runinfo = conf.Get("table.runinfo"); + const string tab_source = conf.Get("table.source"); + const string tab_position = conf.Get("table.position"); + + const uint32_t file = conf.Get("file"); + + const double focal_dist = conf.Get("focal-dist"); + + const bool print_meta = conf.Get("print-meta"); + const bool print_insert = conf.Get("print-insert"); + + const bool force = conf.Get("force"); + const bool drop = conf.Get("drop"); + const bool create = conf.Get("create") || drop; + const bool update = conf.Get("update"); + const bool noinsert = conf.Get("no-insert"); + const bool dry_run = conf.Get("dry-run"); + const uint16_t verbose = conf.Get("verbose"); // ------------------------------------------------------------------------- - - if (list_files) + // Checking for database connection + + Database connection(uri); // Keep alive while fetching rows + + try + { + if (!force) + connection.connected(); + } + catch (const exception &e) + { + cerr << "SQL connection failed: " << e.what() << endl; + return 1; + } + + // ------------------------------------------------------------------------- + // list file-ids in the events table + + if (file==0) { const string query = - "SELECT FileId FROM Events GROUP BY FileId"; - - cout << query << endl; + "SELECT FileId FROM `"+tab_events+"` GROUP BY FileId"; + + if (print_meta) + cout << query << endl; const mysqlpp::StoreQueryResult res = - Database(uri).query(query).store(); + connection.query(query).store(); for (size_t i=0; i0) + { cout << "\n------------------------- Evaluating source ------------------------" << endl; - - cout << "Requesting coordinates from RunInfo/Source table for 20" << file/1000 << "/" << file%1000 << endl; + cout << "Requesting coordinates from " << tab_runinfo << "/" << tab_source << " table for 20" << file/1000 << "/" << file%1000 << endl; + } if (!has_radec) { const string query = - "SELECT Source.fRightAscension, Source.fDeclination, Source.fSourceName" - " FROM RunInfo" - " LEFT JOIN Source" + "SELECT "+tab_source+".fRightAscension, "+tab_source+".fDeclination, "+tab_source+".fSourceName" + " FROM `"+tab_runinfo+"`"+ + " LEFT JOIN `"+tab_source+"`"+ " USING (fSourceKey)" " WHERE fNight=20"+to_string(file/1000)+ " AND fRunID="+to_string(file%1000); - cout << query << endl; + if (print_meta) + cout << query << endl; + + try + { + const mysqlpp::StoreQueryResult res = + connection.query(query).store(); + + if (res.num_rows()!=1) + { + cerr << "No coordinates from " << tab_runinfo << " for " << file << endl; + return 2; + } + + source_ra = res[0][0]; + source_dec = res[0][1]; + + if (verbose>0) + cout << "Using coordinates " << source_ra << "h / " << source_dec << " deg for '" << res[0][2] << "'" << endl; + } + catch (const exception &e) + { + cerr << query << "\n"; + cerr << "SQL query failed:\n" << e.what() << endl; + return 3; + } + } + else + if (verbose>0) + cout << "Using coordinates " << source_ra << "h / " << source_dec << " deg from resources." << endl; + +/* + if (!source_name.empty()) + { + cout << "Requesting coordinates for '" << source_name << "'" << endl; const mysqlpp::StoreQueryResult res = - Database(uri).query(query).store(); + connection.query("SELECT `Ra`, `Dec` WHERE fSourceName='"+source_name+"'").store(); if (res.num_rows()!=1) { - cerr << "No coordinates from RunInfo for " << file << endl; + cerr << "No " << (res.num_rows()>1?"unique ":"") << "coordinates found for '" << source_name << "'" << endl; return 1; } @@ -258,41 +330,23 @@ source_ra = res[0][0]; source_dec = res[0][1]; - - cout << "Using coordinates " << source_ra << "h / " << source_dec << " deg for " << res[0][2] << endl; - } - else - cout << "Using coordinates " << source_ra << "h / " << source_dec << " deg from resources." << endl; - -/* - if (!source_name.empty()) - { - cout << "Requesting coordinates for '" << source_name << "'" << endl; - - const mysqlpp::StoreQueryResult res = - Database(uri).query("SELECT `Ra`, `Dec` WHERE fSourceName='"+source_name+"'").store(); - - if (res.num_rows()!=1) - { - cerr << "No " << (res.num_rows()>1?"unique ":"") << "coordinates found for '" << source_name << "'" << endl; - return 1; - } - - source_ra = res[0][0]; - source_dec = res[0][1]; } */ + // ------------------------------------------------------------------------- + // create INSERT/UPDATE query (calculate positions) + if (verbose>0) - cout << "\n-------------------------- Evaluating file ------------------------" << endl; - - - Database connection(uri); // Keep alive while fetching rows + { + cout << "\n-------------------------- Evaluating file ------------------------"; + cout << "\nRequesting data from table `" << tab_events << "`" << endl; + } const string query = "SELECT `Ra`, `Dec`, MJD, MilliSec, NanoSec, Zd, Az, EvtNumber" - " FROM `"+table+"`" + " FROM `"+tab_events+"`" " WHERE FileId="+to_string(file); - cout << query << endl; + if (print_meta) + cout << query << endl; const mysqlpp::UseQueryResult res1 = @@ -314,6 +368,5 @@ ins << setprecision(16); - ostringstream upd; - upd << setprecision(16); + vector upd; size_t count = 0; @@ -468,97 +521,200 @@ */ - if (1/*insert*/) + if (!update) ins << "( " << file << ", " << event << ", " << v.X() << ", " << v.Y() << " ),\n"; - - if (!update.empty()) - upd << "UPDATE " << update << " SET X=" << v.X() << ", Y=" << v.Y() << " WHERE FileId=" << file << " AND EvtNumber=" << event <<";\n"; - }; + else + { + ostringstream out; + out << setprecision(16); + out << "UPDATE `" << tab_position << "` SET X=" << v.X() << ", Y=" << v.Y() << " WHERE FileId=" << file << " AND EvtNumber=" << event; + upd.emplace_back(out.str()); + } + } if (connection.errnum()) { cerr << "SQL error fetching row: " << connection.error() << endl; - return 7; - } - - - if (drop) - { - cout << "Drop table Position." << endl; - const mysqlpp::SimpleResult res2 = - Database(uri).query("DROP TABLE Position").execute(); - - //cout << res.rows() << " rows affected." << res.info() << endl; - } - - - if (create) - { - cout << "Create table Position." << endl; - const mysqlpp::SimpleResult res3 = - Database(uri).query("CREATE TABLE IF NOT EXISTS Position\n" - "(\n" - " FileId INT UNSIGNED NOT NULL,\n" - " EvtNumber INT UNSIGNED NOT NULL,\n" - " X FLOAT NOT NULL,\n" - " Y FLOAT NOT NULL,\n" - " PRIMARY KEY (FileId, EvtNumber)\n" - ")\n" - "DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci\n" - "ENGINE=MyISAM\n" - "COMMENT='created by root2db'\n").execute(); - - //cout << res.rows() << " rows affected." << res.info() << endl; - - } - else - { - // FIXME: Only if entries exist? - cout << "Delete old entries from Position." << endl; - const mysqlpp::SimpleResult res3 = - Database(uri).query("DELETE FROM Position WHERE FileId="+to_string(file)).execute(); - - cout << res3.rows() << " rows affected." << endl; - } - - if (1/*insert*/) - { - cout << "Insert data into table Position." << endl; - const string query3 = - "INSERT `Position` (FileId, EvtNumber, X, Y) VALUES\n"+ + return 4; + } + + if (verbose>0) + cout << "Processed " << count << " events.\n" << endl; + + // ------------------------------------------------------------------------- + // drop table if requested + + if (drop && !update) + { + try + { + if (verbose>0) + cout << "Dropping table `" << tab_position << "`" << endl; + + if (!dry_run) + connection.query("DROP TABLE `"+tab_position+"`").execute(); + + if (verbose>0) + cout << "Table `" << tab_position << "` dropped.\n" << endl; + } + catch (const exception &e) + { + cerr << "DROP TABLE `" << tab_position << "`\n\n"; + cerr << "SQL query failed:\n" << e.what() << endl; + return 5; + } + } + + // ------------------------------------------------------------------------- + // crate table if requested + + if (create && !update) + { + if (verbose>0) + cout << "Creating table `" << tab_position << "`" << endl; + + const string query2 = + "CREATE TABLE IF NOT EXISTS `"+tab_position+"`\n" + "(\n" + " FileId INT UNSIGNED NOT NULL,\n" + " EvtNumber INT UNSIGNED NOT NULL,\n" + " X FLOAT NOT NULL,\n" + " Y FLOAT NOT NULL,\n" + " PRIMARY KEY (FileId, EvtNumber)\n" + ")\n" + "DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci\n" + "ENGINE=MyISAM\n" + "COMMENT='created by "+conf.GetName()+"'\n"; + + try + { + if (!dry_run) + connection.query(query2).execute(); + } + catch (const exception &e) + { + cerr << query2 << "\n\n"; + cerr << "SQL query failed:\n" << e.what() << endl; + return 6; + } + + if (print_meta) + cout << query2 << endl; + + if (verbose>0) + cout << "Table `" << tab_position << "` created.\n" << endl; + } + + // ------------------------------------------------------------------------- + // delete old entries from table + + if (!drop && !update) + { + if (verbose>0) + cout << "Deleting old entries from table `" << tab_position << "`" << endl; + + const string query2 = + "DELETE FROM `"+tab_position+"` WHERE FileId="+to_string(file); + + try + { + if (!dry_run) + { + const mysqlpp::SimpleResult res = + connection.query(query2).execute(); + + if (verbose>0) + cout << res.rows() << " row(s) affected.\n" << endl; + } + } + catch (const exception &e) + { + cerr << query2 << "\n\n"; + cerr << "SQL query failed:\n" << e.what() << endl; + return 7; + } + + if (print_meta) + cout << query2 << endl; + } + + // ------------------------------------------------------------------------- + // insert data into table + + if (!update) + { + if (verbose>0) + cout << "Inserting data into table " << tab_position << "." << endl; + + const string query2 = + "INSERT `"+tab_position+"` (FileId, EvtNumber, X, Y) VALUES\n"+ ins.str().substr(0, ins.str().size()-2)+ "\n"; - if (0/*print_insert*/) - cout << query3 << endl; - - const mysqlpp::SimpleResult res3 = - Database(uri).query(query3).execute(); - - cout << res3.info() << endl; - } - - if (!update.empty()) - { - cout << upd.str() << endl; - const mysqlpp::StoreQueryResult res3 = - connection.query(upd.str()).store(); - - /* - mysqlpp::Connection con(db, server, user, pass); - // Set option to allow multiple queries to be issued. - mysqlpp::MultiStatementsOption* opt = new mysqlpp::MultiStatementsOption(true); - con.set_option(opt); - // Build either single queries or multiple queries strung together. - mysqlpp::Query query = con.query(); - .... - // Issue query - query.store(); - // Do NOT delete 'opt'; it will be destroyed by the 'con' object when it - // falls out of scope. - */ - } - - //cout << ins.str().substr(0, ins.str().size()-2) << endl; - //cout << count << endl; + try + { + if (!noinsert) + { + const mysqlpp::SimpleResult res = + connection.query(query2).execute(); + + if (verbose>0) + cout << res.rows() << " row(s) affected.\n" << endl; + } + } + catch (const exception &e) + { + cerr << query2 << "\n\n"; + cerr << "SQL query failed:\n" << e.what() << endl; + return 8; + } + + if (print_insert) + cout << query2 << endl; + } + + + + // ------------------------------------------------------------------------- + // update data in table + + if (update) + { + if (verbose>0) + cout << "Updating data in table " << tab_position << "." << endl; + + size_t cnt = 0; + for (const auto &str : upd) + { + try + { + if (!noinsert && !dry_run) + { + const mysqlpp::SimpleResult res = + connection.query(str).execute(); + + cnt += res.rows(); + } + } + catch (const exception &e) + { + cerr << str << "\n\n"; + cerr << "SQL query failed:\n" << e.what() << endl; + return 9; + } + } + + if (verbose>0) + cout << cnt << " row(s) out of " << upd.size() << " affected.\n" << endl; + + if (print_insert) + { + for (const auto &str : upd) + cout << str << '\n'; + cout << endl; + } + } + + if (verbose>0) + cout << "Total execution time: " << Time().UnixTime()-start.UnixTime() << "s\n" << endl; return 0;