Changeset 19113

Timestamp:

07/31/18 20:57:08 (6 years ago)

Author:

tbretz

Message:

Use ON DUPLICATE KEY for updating not the UPDATE directive.

File:

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

trunk/FACT++/src/calcsource.cc

@@ -36 +36 @@
         ("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)")
+        ("update",         po_switch(),               "Uses the ON DUPLICATE KEY to update already existing extries")
         ("ra",             var<double>(),             "Right ascension of the source (use together with --dec)")
         ("dec",            var<double>(),             "Declination of the source (use together with --ra)")
@@ -322 +322 @@
     ins << setprecision(16);
 
-    vector<string> upd;
-
     size_t count = 0;
     while (auto row=res1.fetch_row())
@@ -475 +473 @@
         */
 
-        if (!update)
-            ins << "( " << file << ", " << event << ", " << v.X() << ", " << v.Y() << " ),\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());
-        }
+        ins << "( " << file << ", " << event << ", " << v.X() << ", " << v.Y() << " ),\n";
     }
 
@@ -505 +495 @@
     // drop table if requested
 
-    if (drop && !update)
+    if (drop)
     {
         try
@@ -529 +519 @@
     // crate table if requested
 
-    if (create && !update)
+    if (create)
     {
         if (verbose>0)
@@ -572 +562 @@
     // delete old entries from table
 
-    if (!drop && !update)
+    if (!drop)
     {
         if (verbose>0)
@@ -605 +595 @@
     // insert data into table
 
-    if (!update)
-    {
-        if (verbose>0)
-            cout << "Inserting data into table " << tab_position << "." << endl;
-
-        string query2 = "INSERT ";
-        if (ignore_errors)
-            query2 += "IGNORE ";
-        query2 += "`"+tab_position+"` (FileId, EvtNumber, X, Y) VALUES\n"+
-            ins.str().substr(0, ins.str().size()-2)+
-            "\n";
-
-        try
-        {
-            if (!noinsert)
-            {
-                const mysqlpp::SimpleResult res =
-                    connection.query(query2).execute();
-
-                if (verbose>0)
-                    cout << res.info() << '\n' << endl;
-            }
-        }
-        catch (const exception &e)
-        {
-            cerr << query2 << "\n\n";
-            cerr << "SQL query (" << query2.length() << " bytes) failed:\n" << e.what() << endl;
-            return 8;
-        }
-
-        if (print_insert)
-            cout << query2 << endl;
-    }
-
-
-
-    // -------------------------------------------------------------------------
-    // update data in table
-
+    if (verbose>0)
+        cout << "Inserting data into table " << tab_position << "." << endl;
+
+    string query2 = "INSERT ";
+    if (ignore_errors)
+        query2 += "IGNORE ";
+    query2 += "`"+tab_position+"` (FileId, EvtNumber, X, Y) VALUES\n"+
+        ins.str().substr(0, ins.str().size()-2)+
+        "\n";
     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;
-        }
-    }
+        query2 += "ON DUPLICATE KEY X=VALUES(X), Y=VALUES(Y)\n";
+
+    try
+    {
+        if (!noinsert)
+        {
+            const mysqlpp::SimpleResult res =
+                connection.query(query2).execute();
+
+            if (verbose>0)
+                cout << res.info() << '\n' << endl;
+        }
+    }
+    catch (const exception &e)
+    {
+        cerr << query2 << "\n\n";
+        cerr << "SQL query (" << query2.length() << " bytes) failed:\n" << e.what() << endl;
+        return 8;
+    }
+
+    if (print_insert)
+        cout << query2 << endl;
 
     if (verbose>0)