Index: trunk/FACT++/src/fits2sql.cc =================================================================== --- trunk/FACT++/src/fits2sql.cc (revision 19100) +++ trunk/FACT++/src/fits2sql.cc (revision 19101) @@ -65,4 +65,5 @@ ("max", var(int64_t(0)), "Maximum number of events to process (0: all), mainly for test purpose") ("engine", var("InnoDB"), "Database engine to be used when a new table is created") + ("duplicate", var(""), "Specifies an assignment_list for an 'ON DUPLICATE KEY UPDATE' expression") ; @@ -72,5 +73,5 @@ ("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,22 +95,18 @@ "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,13 +114,13 @@ "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,5 +145,10 @@ "\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,4 +274,5 @@ const string engine = conf.Get("engine"); + const string duplicate = conf.Get("duplicate"); const bool print_extensions = conf.Get("print-extensions"); @@ -545,8 +548,11 @@ } } - query += "\n)"; // ON DUPLICATE KEY UPDATE\n"; + query += "\n)"; count ++; } + + if (!duplicate.empty()) + query += "\nON DUPLICATE KEY UPDATE\n " + duplicate; if (verbose>0) @@ -591,4 +597,28 @@ 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(int64_t(0)), "Maximum number of events to process (0: all), mainly for test purpose") ("engine", var("InnoDB"), "Database engine to be used when a new table is created") + ("duplicate", var(""), "Specifies an assignment_list for an 'ON DUPLICATE KEY UPDATE' expression") ; @@ -114,6 +115,6 @@ "\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,5 +145,10 @@ "\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,4 +297,5 @@ const string engine = conf.Get("engine"); + const string duplicate = conf.Get("duplicate"); const bool print_branches = conf.Get("print-branches"); @@ -550,8 +557,11 @@ 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,5 +606,30 @@ 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