Changeset 19151 for trunk

Timestamp:

08/11/18 20:10:40 (6 years ago)

Author:

tbretz

Message:

Implemented conditional execution.

Location:

trunk/FACT++/src

Files:

• fits2sql.cc (modified) (10 diffs)
• root2sql.cc (modified) (10 diffs)

trunk/FACT++/src/fits2sql.cc

@@ -67 +67 @@
         ("ignore-errors",  po_switch(),               "Adds the IGNORE keyword to the INSERT query (turns errors into warnings, ignores rows with errors)")
         ("const.*",        var<string>(),             "Insert a constant number into the given column (--const.mycolumn=5). A special case is `/..../N/`")
+        ("conditional",    po_switch(),               "Conditional insert. Only insert if no entry exists yet with the constants defined by --const")
         ("delete",         po_switch(),               "Delete all entries first which fit all constant columns defined by --const")
         ;
@@ -79 +80 @@
         ("print-insert",   po_switch(),               "Print the INSERT query (note that it contains all data)")
         ("print-create",   po_switch(),               "Print the CREATE query")
+        ("print-select",   po_switch(),               "Print the SELECT query for the conditional execution")
         ("print-delete",   po_switch(),               "Print the DELETE query")
         ("verbose,v",      var<uint16_t>(1),          "Verbosity (0: quiet, 1: default, 2: more, 3, ...)")
@@ -192 +194 @@
         "data from the table before inserting new data which fulfills the condition "
         "defined by the `--const` directives.\n"
+        "\n"
+        "The constant values can also be used for a conditional execution (--conditional). "
+        "If any row with the given constant values are found, the execution is stopped "
+        "(note that this happend after the table drop/create but before the delete/insert.\n"
         "\n"
         "If a query failed, the query is printed to stderr together with the error message. "
@@ -318 +324 @@
     const bool noinsert          = conf.Get<bool>("no-insert");
     const bool dry_run           = conf.Get<bool>("dry-run");
+    const bool conditional       = conf.Get<bool>("conditional");
     const bool run_delete        = conf.Get<bool>("delete");
 
@@ -330 +337 @@
     const bool print_create      = conf.Get<bool>("print-create");
     const bool print_insert      = conf.Get<bool>("print-insert");
+    const bool print_select      = conf.Get<bool>("print-select");
     const bool print_delete      = conf.Get<bool>("print-delete");
 
@@ -405 +413 @@
     const auto fixed = conf.GetWildcardOptions("const.*");
 
-    string rmquery = "DELETE FROM `"+table+"` WHERE 1";
+    string where;
     for (auto it=fixed.cbegin(); it!=fixed.cend(); it++)
     {
@@ -443 +451 @@
 
         vec.emplace_back(name, val);
-        rmquery += " AND `"+name+"`="+val;
+        where += " AND `"+name+"`="+val;
     }
 
@@ -635 +643 @@
     try
     {
+        if (conditional && !fixed.empty() && !drop)
+        {
+            const mysqlpp::StoreQueryResult res =
+                connection.query("SELECT 1 FROM `"+table+"` WHERE 1"+where+" LIMIT 1").store();
+
+            if (res.num_rows()>0)
+            {
+                if (verbose>0)
+                {
+                    cout << "Conditional execution... detected existing rows!\n";
+                    cout << "Exit." << endl;
+                }
+                return 0;
+            }
+        }
+    }
+    catch (const exception &e)
+    {
+        cerr << "SELECT 1 FROM `"+table+"` WHERE 1" << where << " LIMIT 1\n\n";
+        cerr << "SQL query failed: " << e.what() << endl;
+        return 7;
+    }
+
+    if (print_select)
+        cout << "SELECT 1 FROM `"+table+"` WHERE 1" << where << " LIMIT 1" << endl;
+
+
+    try
+    {
         if (run_delete && !fixed.empty() && !drop && !dry_run)
         {
             const mysqlpp::SimpleResult res =
-                connection.query(rmquery).execute();
+                connection.query("DELETE FROM `"+table+"` WHERE 1"+where).execute();
 
             if (verbose>0)
@@ -646 +683 @@
     catch (const exception &e)
     {
-        cerr << rmquery << "\n\n";
+        cerr << "DELETE FROM `"+table+"` WHERE 1" << where << "\n\n";
         cerr << "SQL query failed: " << e.what() << endl;
         return 7;
@@ -652 +689 @@
 
     if (print_delete)
-        cout << rmquery << endl;
+        cout << "DELETE FROM `"+table+"` WHERE 1" << where << endl;
 
     // -------------------------------------------------------------------------

trunk/FACT++/src/root2sql.cc

@@ -70 +70 @@
         ("ignore-errors",  po_switch(),               "Adds the IGNORE keyword to the INSERT query (turns errors into warnings, ignores rows with errors)")
         ("const.*",        var<string>(),             "Insert a constant number into the given column (--const.mycolumn=5). A special case is `/..../N/`")
+        ("conditional",    po_switch(),               "Conditional insert. Only insert if no entry exists yet with the constants defined by --const")
         ("delete",         po_switch(),               "Delete all entries first which fit all constant columns defined by --const")
         ;
@@ -82 +83 @@
         ("print-insert",   po_switch(),               "Print the INSERT query (note that it contains all data)")
         ("print-create",   po_switch(),               "Print the CREATE query")
+        ("print-select",   po_switch(),               "Print the SELECT query for the conditional execution")
         ("print-delete",   po_switch(),               "Print the DELETE query")
         ("verbose,v",      var<uint16_t>(1),          "Verbosity (0: quiet, 1: default, 2: more, 3, ...)")
@@ -199 +201 @@
         "data from the table before inserting new data which fulfills the condition "
         "defined by the `--const` directives.\n"
+        "\n"
+        "The constant values can also be used for a conditional execution (--conditional). "
+        "If any row with the given constant values are found, the execution is stopped "
+        "(note that this happend after the table drop/create but before the delete/insert.\n"
         "\n"
         "If a query failed, the query is printed to stderr together with the error message. "
@@ -347 +353 @@
     const bool noinsert          = conf.Get<bool>("no-insert");
     const bool dry_run           = conf.Get<bool>("dry-run");
+    const bool conditional       = conf.Get<bool>("conditional");
     const bool run_delete        = conf.Get<bool>("delete");
 
@@ -361 +368 @@
     const bool print_create      = conf.Get<bool>("print-create");
     const bool print_insert      = conf.Get<bool>("print-insert");
+    const bool print_select      = conf.Get<bool>("print-select");
     const bool print_delete      = conf.Get<bool>("print-delete");
 
@@ -428 +436 @@
     const auto fixed = conf.GetWildcardOptions("const.*");
 
-    string rmquery = "DELETE FROM `"+table+"` WHERE 1";
+    string where;
     for (auto it=fixed.cbegin(); it!=fixed.cend(); it++)
     {
@@ -466 +474 @@
 
         vec.emplace_back(name, val);
-        rmquery += " AND `"+name+"`="+val;
+        where += " AND `"+name+"`="+val;
     }
 
@@ -671 +679 @@
     try
     {
+        if (conditional && !fixed.empty() && !drop)
+        {
+            const mysqlpp::StoreQueryResult res =
+                connection.query("SELECT 1 FROM `"+table+"` WHERE 1"+where+" LIMIT 1").store();
+
+            if (res.num_rows()>0)
+            {
+                if (verbose>0)
+                {
+                    cout << "Conditional execution... detected existing rows!\n";
+                    cout << "Exit." << endl;
+                }
+                return 0;
+            }
+        }
+    }
+    catch (const exception &e)
+    {
+        cerr << "SELECT 1 FROM `"+table+"` WHERE 1" << where << " LIMIT 1\n\n";
+        cerr << "SQL query failed: " << e.what() << endl;
+        return 7;
+    }
+
+    if (print_select)
+        cout << "SELECT 1 FROM `"+table+"` WHERE 1" << where << " LIMIT 1" << endl;
+
+    try
+    {
         if (run_delete && !fixed.empty() && !drop && !dry_run)
         {
             const mysqlpp::SimpleResult res =
-                connection.query(rmquery).execute();
+                connection.query("DELETE FROM `"+table+"` WHERE 1"+where).execute();
 
             if (verbose>0)
@@ -682 +718 @@
     catch (const exception &e)
     {
-        cerr << rmquery << "\n\n";
+        cerr << "DELETE FROM `"+table+"` WHERE 1" << where << "\n\n";
         cerr << "SQL query failed: " << e.what() << endl;
         return 7;
@@ -688 +724 @@
 
     if (print_delete)
-        cout << rmquery << endl;
+        cout << "DELETE FROM `"+table+"` WHERE 1" << where << endl;