Changeset 19101 for trunk

Timestamp:

07/31/18 12:07:31 (6 years ago)

Author:

tbretz

Message:

Allow an 'ON DUPLICATE KEY UPDATE' clause

Location:

trunk/FACT++/src

Files:

• fits2sql.cc (modified) (8 diffs)
• root2sql.cc (modified) (6 diffs)

trunk/FACT++/src/fits2sql.cc

@@ -65 +65 @@
         ("max",            var<int64_t>(int64_t(0)),  "Maximum number of events to process (0: all), mainly for test purpose")
         ("engine",         var<string>("InnoDB"),     "Database engine to be used when a new table is created")
+        ("duplicate",      var<string>(""),           "Specifies an assignment_list for an 'ON DUPLICATE KEY UPDATE' expression")
         ;
 
@@ -72 +73 @@
         ("dry-run",        po_switch(),               "Skip any query which changes the databse (might result in consecutive failures)")
         ("print-extensions", po_switch(),             "Print extensions (tables) from fits file")
-        ("print-columns", po_switch(),               "Print columns in fits table")
+        ("print-columns",  po_switch(),               "Print columns in fits table")
         ("print-insert",   po_switch(),               "Print the INSERT query (note that it contains all data)")
         ("print-create",   po_switch(),               "Print the CREATE query")
@@ -94 +95 @@
         "refer to the output below to get the abbreviations.\n"
         "\n"
-        "This is a general purpose tool to fill the contents of a root file into a database "
-        "as long as this is technically possible and makes sense. Note that root can even "
-        "write complex data like a TH1F into a database, this is not the purpose of this "
-        "program.\n"
-        "\n"
-        "Each root tree has branches and leaves (the basic data types). These leaves can "
-        "be read independently of the classes which were used to write the root file. "
-        "The default tree to read from is 'Events' but the name can be overwritten "
-        "using --tree. The default table name to fill the data into is identical to "
-        "the tree name. It can be overwritten using --table.\n"
-        "\n"
-        "The name of each column to which data is filled from a leave is obtained from "
-        "the leaves' names. The leave names can be checked using --print-leaves. "
-        "A --print-branches exists for convenience to print only the high-level branches. "
-        "Sometimes these names might be quite unconvenient like MTime.fTime.fMilliSec or "
-        "just MHillas.fWidth. To allow to simplify column names, regular expressions "
-        "(using boost's regex) can be defined to change the names. Note that these regular "
-        "expressions are applied one by one on each leaf's name. A valid expression could "
+        "This is a general purpose tool to fill the contents of a fite file into a database.\n"
+        "\n"
+        "Each fits file contians several table, so called extenbsions. Each tables has "
+        "a number of columns compilsed from basic data types. The default extension to "
+        "read from is the first one on the file but the name can be overwritten using "
+        "--extension. The default table name to fill the data into is identical to "
+        "the extension name. It can be overwritten using --table.\n"
+        "\n"
+        "The name of each column to which data is filled is obtained from "
+        "the fits column names. The column names can be checked using --print-columns. "
+        "Sometimes these names might not be convenient. To allow to simplify or replace "
+        "column names, regular expressions (using boost's regex) can be defined to change "
+        "the names. Note that these regular expressions are applied one by one on each "
+        "columns's name. A valid expression could "
         "be:\n"
         "   --map=MHillas\\.f/\n"
@@ -117 +114 @@
         "once. They are applied in sequence. A single match does not stop the sequence.\n"
         "\n"
-        "Sometimes it might also be convenient to skip a leaf. This can be done with "
-        "the --ignore resource. If the given regular expresion yield a match, the "
-        "leaf will be ignored. Note that the regular expression work on the raw-name "
-        "of the leaf not the readily mapped SQL column names. Example:\n"
+        "Sometimes it might also be convenient to skip a column. This can be done with "
+        "the --ignore resource. If the given regular expresion yields a match, the "
+        "column will be ignored. Note that the regular expression works on the raw-name "
+        "of the column not the readily mapped SQL column names. Example:\n"
         "   --ignore=ThetaSq\\..*\n"
         "will skip all leaved which start with 'ThetaSq.'. This option can be used"
         "more than once.\n"
         "\n"
-        "The data type of each column is kept as close as possible to the leaves' data "
+        "The data type of each column is kept as close as possible to the columns' data "
         "types. If for some reason this is not wanted, the data type of the SQL column "
         "can be overwritten with --sql-type sql-column/sql-ytpe, for example:\n"
@@ -148 +145 @@
         "\n"
         "All columns are created as NOT NULL as default and the table is created as "
-        "MyISAM (default).\n"
+        "InnoDB (default).\n"
+        "\n"
+        "Usually, the INSERT query would fail if the PRIMARY key exists already. "
+        "This can be avoided using the 'ON DUPLICATE KEY' directive. With the "
+        "--duplicate,you can specify what should be updated in case of a duplicate key. "
+        "For detaily, see the MySQL manual.\n"
         "\n"
         "For debugging purpose, or to just create or drop a table, the final insert "
@@ -272 +274 @@
 
     const string engine          = conf.Get<string>("engine");
+    const string duplicate       = conf.Get<string>("duplicate");
 
     const bool print_extensions  = conf.Get<bool>("print-extensions");
@@ -545 +548 @@
             }
         }
-        query += "\n)";  // ON DUPLICATE KEY UPDATE\n";
+        query += "\n)";
 
         count ++;
     }
+
+    if (!duplicate.empty())
+        query += "\nON DUPLICATE KEY UPDATE\n   " + duplicate;
 
     if (verbose>0)
@@ -591 +597 @@
         cout << count << " row(s) inserted.\n\n";
         cout << "Total execution time: " << Time().UnixTime()-start.UnixTime() << "s\n" << endl;
+/*
+        try
+        {
+            const auto resw =
+                connection.query("SHOW WARNINGS").store();
+
+            if (resw.num_rows()>0)
+                cout << "\nWARNINGS:\n\n";
+
+            for (size_t i=0; i<resw.num_rows(); i++)
+            {
+                const mysqlpp::Row &roww = resw[i];
+
+                cout << roww["Level"] << '[' << roww["Code"] << "]: ";
+                cout << roww["Message"] << '\n' << endl;
+            }
+
+        }
+        catch (const exception &e)
+        {
+            cerr << "\nSHOW WARNINGS\n\n";
+            cerr << "SQL query failed:\n" << e.what() << endl;
+            return 6;
+        }*/
     }
 

trunk/FACT++/src/root2sql.cc

@@ -61 +61 @@
         ("max",            var<int64_t>(int64_t(0)),  "Maximum number of events to process (0: all), mainly for test purpose")
         ("engine",         var<string>("InnoDB"),     "Database engine to be used when a new table is created")
+        ("duplicate",      var<string>(""),           "Specifies an assignment_list for an 'ON DUPLICATE KEY UPDATE' expression")
         ;
 
@@ -114 +115 @@
         "\n"
         "Sometimes it might also be convenient to skip a leaf. This can be done with "
-        "the --ignore resource. If the given regular expresion yield a match, the "
-        "leaf will be ignored. Note that the regular expression work on the raw-name "
+        "the --ignore resource. If the given regular expresion yields a match, the "
+        "leaf will be ignored. Note that the regular expression works on the raw-name "
         "of the leaf not the readily mapped SQL column names. Example:\n"
         "   --ignore=ThetaSq\\..*\n"
@@ -144 +145 @@
         "\n"
         "All columns are created as NOT NULL as default and the table is created as "
-        "MyISAM (default).\n"
+        "InnoDB (default).\n"
+        "\n"
+        "Usually, the INSERT query would fail if the PRIMARY key exists already. "
+        "This can be avoided using the 'ON DUPLICATE KEY' directive. With the "
+        "--duplicate,you can specify what should be updated in case of a duplicate key. "
+        "For detaily, see the MySQL manual.\n"
         "\n"
         "For debugging purpose, or to just create or drop a table, the final insert "
@@ -291 +297 @@
 
     const string engine          = conf.Get<string>("engine");
+    const string duplicate       = conf.Get<string>("duplicate");
 
     const bool print_branches    = conf.Get<bool>("print-branches");
@@ -550 +557 @@
                 query += " /* "+c->column+" -> "+c->branch+" */";
         }
-        query += "\n)";  // ON DUPLICATE KEY UPDATE\n";
+        query += "\n)";
 
         count ++;
     }
+
+    if (!duplicate.empty())
+        query += "\nON DUPLICATE KEY UPDATE\n   " + duplicate;
 
     if (verbose>0)
@@ -596 +606 @@
         cout << count << " row(s) inserted.\n\n";
         cout << "Total execution time: " << Time().UnixTime()-start.UnixTime() << "s\n" << endl;
-    }
+/*
+        try
+        {
+            const auto resw =
+                connection.query("SHOW WARNINGS").store();
+
+            if (resw.num_rows()>0)
+                cout << "\nWARNINGS:\n\n";
+
+            for (size_t i=0; i<resw.num_rows(); i++)
+            {
+                const mysqlpp::Row &roww = resw[i];
+
+                cout << roww["Level"] << '[' << roww["Code"] << "]: ";
+                cout << roww["Message"] << '\n' << endl;
+            }
+
+        }
+        catch (const exception &e)
+        {
+            cerr << "\nSHOW WARNINGS\n\n";
+            cerr << "SQL query failed:\n" << e.what() << endl;
+            return 6;
+        }*/
+    }
+
 
     return 0;