Changeset 19150

Timestamp:

08/11/18 14:46:15 (6 years ago)

Author:

tbretz

Message:

Implemented an option to pre-delete entries from the table based on the --const directive.

Location:

trunk/FACT++/src

Files:

• fits2sql.cc (modified) (10 diffs)
• root2sql.cc (modified) (11 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/`")
+        ("delete",         po_switch(),               "Delete all entries first which fit all constant columns defined by --const")
         ;
 
@@ -78 +79 @@
         ("print-insert",   po_switch(),               "Print the INSERT query (note that it contains all data)")
         ("print-create",   po_switch(),               "Print the CREATE query")
+        ("print-delete",   po_switch(),               "Print the DELETE query")
         ("verbose,v",      var<uint16_t>(1),          "Verbosity (0: quiet, 1: default, 2: more, 3, ...)")
         ;
@@ -183 +185 @@
         "regular expression is applied to the filename and N specifies the N-th "
         "sub-sequence which matches. To debug what matches, verbosity can be set to 3.\n"
+        "\n"
+        "Usually the previously defined constant values are helpful to create an index "
+        "which relates unambiguously the inserted data to the file. It might be useful "
+        "to delete all data which belongs to this particular file before new data is "
+        "entered. This can be achieved with the `--delete` directive. It deletes all "
+        "data from the table before inserting new data which fulfills the condition "
+        "defined by the `--const` directives.\n"
         "\n"
         "If a query failed, the query is printed to stderr together with the error message. "
@@ -309 +318 @@
     const bool noinsert          = conf.Get<bool>("no-insert");
     const bool dry_run           = conf.Get<bool>("dry-run");
+    const bool run_delete        = conf.Get<bool>("delete");
 
     const string engine          = conf.Get<string>("engine");
@@ -320 +330 @@
     const bool print_create      = conf.Get<bool>("print-create");
     const bool print_insert      = conf.Get<bool>("print-insert");
+    const bool print_delete      = conf.Get<bool>("print-delete");
 
     const vector<Map> mymap      = conf.Vec<Map>("map");
@@ -394 +405 @@
     const auto fixed = conf.GetWildcardOptions("const.*");
 
+    string rmquery = "DELETE FROM `"+table+"` WHERE 1";
     for (auto it=fixed.cbegin(); it!=fixed.cend(); it++)
     {
@@ -431 +443 @@
 
         vec.emplace_back(name, val);
+        rmquery += " AND `"+name+"`="+val;
     }
 
@@ -492 +505 @@
             query += "   `"+name;
             if (N>1)
-                query += "["+to_string(i)+"]";
+                query += "["+to_string((long long int)i)+"]";
             query += "` "+sqltype;
             if (col.type=='A')
-                query += '('+to_string(col.num)+')';
+                query += '('+to_string((long long int)col.num)+')';
             query += " NOT NULL COMMENT '"+ic->first;
             if (!col.unit.empty())
@@ -619 +632 @@
         cout << "Table `" << table << "` created." << endl;
 
+
+    try
+    {
+        if (run_delete && !fixed.empty() && !drop && !dry_run)
+        {
+            const mysqlpp::SimpleResult res =
+                connection.query(rmquery).execute();
+
+            if (verbose>0)
+                cout << res.rows() << " row(s) deleted.\n" << endl;
+        }
+    }
+    catch (const exception &e)
+    {
+        cerr << rmquery << "\n\n";
+        cerr << "SQL query failed: " << e.what() << endl;
+        return 7;
+    }
+
+    if (print_delete)
+        cout << rmquery << endl;
+
     // -------------------------------------------------------------------------
 
@@ -642 +677 @@
                 query += "   `"+c->column+"`";
             else
-                query += "   `"+c->column+"["+to_string(i)+"]`";
+                query += "   `"+c->column+"["+to_string((long long int)i)+"]`";
 
             if (N>1 && i!=N-1)

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/`")
+        ("delete",         po_switch(),               "Delete all entries first which fit all constant columns defined by --const")
         ;
 
@@ -81 +82 @@
         ("print-insert",   po_switch(),               "Print the INSERT query (note that it contains all data)")
         ("print-create",   po_switch(),               "Print the CREATE query")
+        ("print-delete",   po_switch(),               "Print the DELETE query")
         ("verbose,v",      var<uint16_t>(1),          "Verbosity (0: quiet, 1: default, 2: more, 3, ...)")
         ;
@@ -190 +192 @@
         "regular expression is applied to the filename and N specifies the N-th "
         "sub-sequence which matches. To debug what matches, verbosity can be set to 3.\n"
+        "\n"
+        "Usually the previously defined constant values are helpful to create an index "
+        "which relates unambiguously the inserted data to the file. It might be useful "
+        "to delete all data which belongs to this particular file before new data is "
+        "entered. This can be achieved with the `--delete` directive. It deletes all "
+        "data from the table before inserting new data which fulfills the condition "
+        "defined by the `--const` directives.\n"
         "\n"
         "If a query failed, the query is printed to stderr together with the error message. "
@@ -338 +347 @@
     const bool noinsert          = conf.Get<bool>("no-insert");
     const bool dry_run           = conf.Get<bool>("dry-run");
+    const bool run_delete        = conf.Get<bool>("delete");
 
     const string engine          = conf.Get<string>("engine");
@@ -351 +361 @@
     const bool print_create      = conf.Get<bool>("print-create");
     const bool print_insert      = conf.Get<bool>("print-insert");
+    const bool print_delete      = conf.Get<bool>("print-delete");
 
     const vector<Map> mymap      = conf.Vec<Map>("map");
@@ -417 +428 @@
     const auto fixed = conf.GetWildcardOptions("const.*");
 
+    string rmquery = "DELETE FROM `"+table+"` WHERE 1";
     for (auto it=fixed.cbegin(); it!=fixed.cend(); it++)
     {
@@ -454 +466 @@
 
         vec.emplace_back(name, val);
+        rmquery += " AND `"+name+"`="+val;
     }
 
@@ -473 +486 @@
             continue;
         }
+
 
         string name = o->GetName();
@@ -523 +537 @@
             query += "   `"+name;
             if (N>1)
-                query += "["+to_string(i)+"]";
+                query += "["+to_string((long long int)i)+"]";
             query += "` "+sqltype+" NOT NULL COMMENT '"+o->GetTitle()+"'";
             if (N>1 && i!=N-1)
@@ -654 +668 @@
         cout << "Table `" << table << "` created." << endl;
 
+
+    try
+    {
+        if (run_delete && !fixed.empty() && !drop && !dry_run)
+        {
+            const mysqlpp::SimpleResult res =
+                connection.query(rmquery).execute();
+
+            if (verbose>0)
+                cout << res.rows() << " row(s) deleted.\n" << endl;
+        }
+    }
+    catch (const exception &e)
+    {
+        cerr << rmquery << "\n\n";
+        cerr << "SQL query failed: " << e.what() << endl;
+        return 7;
+    }
+
+    if (print_delete)
+        cout << rmquery << endl;
+
+
     // -------------------------------------------------------------------------
 
@@ -677 +714 @@
                 query += "   `"+c->column+"`";
             else
-                query += "   `"+c->column+"["+to_string(i)+"]`";
+                query += "   `"+c->column+"["+to_string((long long int)i)+"]`";
 
             if (N>1 && i!=N-1)