Changeset 19068

Timestamp:

07/21/18 14:57:22 (6 years ago)

Author:

tbretz

Message:

Made the code and output more consistent, added the option to update and some more details.

File:

• trunk/FACT++/src/calcsource.cc (modified) (6 diffs)

trunk/FACT++/src/calcsource.cc

@@ -49 +49 @@
     control.add_options()
         ("uri,u",          var<string>()->required(), "Database link as in\n\tuser:password@server[:port]/database.")
-        ("ra",             var<double>()/*5.575539)*/,                "")
-        ("dec",            var<double>()/*22.014500)*/,                "")
-        ("focal-dist",     var<double>(4889.),                "")
         //("source-key",     var<uint32_t>(5),                "")
         //("source-name",    var<string>(""),               "")
-        ("file",           var<uint32_t>(171011115)->required(),           "")
-        ("drop",           po_switch(),               "Drop the table (implied create)")
-        ("list-files",     po_switch(),               "")
-        ("tree,t",         var<string>("Events"),     "Name of the root tree to convert")
-        ("table",          var<string>("Events"),     "")
-        ("update",         var<string>(""),     "")
-        ("create",         po_switch(),     "")
+        ("file",           var<uint32_t>(0),          "FileId (YYMMDDXXX), defined the events to be processed (if omitted (=0), a list of possible numbers is printed)")
+        ("table.events",   var<string>("Events"),     "Name of the table where the events are stored")
+        ("table.runinfo",  var<string>("RunInfo"),    "Name of the table where the run-info data is stored")
+        ("table.source",   var<string>("Source"),     "Name of the table where the sources are stored")
+        ("table.position", var<string>("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<double>(),             "Right ascension of the source (use together with --dec)")
+        ("dec",            var<double>(),             "Declination of the source (use together with --ra)")
+        ("focal-dist",     var<double>(4889.),        "Focal distance of the camera in millimeter")
         ("verbose,v",      var<uint16_t>(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 +195 @@
 
     // ----------------------------- 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<string>("uri");
-    const uint32_t file        = conf.Get<uint32_t>("file");
-    const string   tree        = conf.Get<string>("tree");
-    const string   table       = conf.Get<string>("table");
+    double source_ra            = conf.Has("ra")  ? conf.Get<double>("ra")  : 0;
+    double source_dec           = conf.Has("dec") ? conf.Get<double>("dec") : 0;
+
     //string  source_name      = conf.Get<string>("source-name");
     //uint32_t source_key      = conf.Has("source-key")  ? conf.Get<uint32_t>("source-key") : 0;
-    double   source_ra       = conf.Has("ra")  ? conf.Get<double>("ra")  : 0;
-    double   source_dec      = conf.Has("dec") ? conf.Get<double>("dec") : 0;
-    double   focal_dist    = conf.Get<double>("focal-dist");
-    //const bool     print_create   = conf.Get<bool>("print-create");
-    //const bool     print_insert   = conf.Get<bool>("print-insert");
-    const bool     drop        = conf.Get<bool>("drop");
-    const bool     create      = conf.Get<bool>("create") || drop;
-    const string   update      = conf.Get<string>("update");
-    const bool     list_files  = conf.Get<bool>("list-files");
-    const uint16_t verbose     = conf.Get<uint16_t>("verbose");
+
+    const string   uri          = conf.Get<string>("uri");
+    const string   tab_events   = conf.Get<string>("table.events");
+    const string   tab_runinfo  = conf.Get<string>("table.runinfo");
+    const string   tab_source   = conf.Get<string>("table.source");
+    const string   tab_position = conf.Get<string>("table.position");
+
+    const uint32_t file         = conf.Get<uint32_t>("file");
+
+    const double   focal_dist   = conf.Get<double>("focal-dist");
+
+    const bool     print_meta   = conf.Get<bool>("print-meta");
+    const bool     print_insert = conf.Get<bool>("print-insert");
+
+    const bool     force        = conf.Get<bool>("force");
+    const bool     drop         = conf.Get<bool>("drop");
+    const bool     create       = conf.Get<bool>("create") || drop;
+    const bool     update       = conf.Get<bool>("update");
+    const bool     noinsert     = conf.Get<bool>("no-insert");
+    const bool     dry_run      = conf.Get<bool>("dry-run");
+    const uint16_t verbose      = conf.Get<uint16_t>("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; i<res.num_rows(); i++)
@@ -230 +264 @@
     }
 
+    // -------------------------------------------------------------------------
+    // retrieve source from the database
+
     if (verbose>0)
+    {
         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 +330 @@
         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 +368 @@
     ins << setprecision(16);
 
-    ostringstream upd;
-    upd << setprecision(16);
+    vector<string> upd;
 
     size_t count = 0;
@@ -468 +521 @@
         */
 
-        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;