source: trunk/FACT++/src/root2sql.cc@ 20041

Last change on this file since 20041 was 19968, checked in by tbretz, 5 years ago
Fixed a bug that broke the meaning of 'max'
File size: 30.7 KB
Line 
1#include <boost/regex.hpp>
2#include <boost/filesystem.hpp>
3#include <boost/algorithm/string/join.hpp>
4
5#include "Database.h"
6
7#include "tools.h"
8#include "Time.h"
9#include "Configuration.h"
10
11#include <TROOT.h>
12#include <TFile.h>
13#include <TTree.h>
14#include <TLeaf.h>
15#include <TError.h>
16
17#include "FileEntry.h"
18
19using namespace std;
20namespace fs = boost::filesystem;
21
22// ------------------------------------------------------------------------
23
24/*
25struct Map : pair<string, string>
26{
27 Map() { }
28};
29
30std::istream &operator>>(std::istream &in, Map &m)
31{
32 const istreambuf_iterator<char> eos;
33 string txt(istreambuf_iterator<char>(in), eos);
34
35 const boost::regex expr("((.*)[^\\\\])/(.*)");
36 boost::smatch match;
37 if (!boost::regex_match(txt, match, expr))
38 throw runtime_error("Could not evaluate map argument: "+txt);
39
40 m.first = match[1].str();
41 m.second = match[3].str();
42
43 return in;
44}
45*/
46
47void SetupConfiguration(Configuration &conf)
48{
49 po::options_description control("Root to SQL");
50 control.add_options()
51 ("uri,u", var<string>()->required(), "Database link as in\n\tuser:password@server[:port]/database[?compress=0|1].")
52 ("file", var<string>()->required(), "The root file to read from")
53 ("create", po_switch(), "Create the database if not existing")
54 ("drop", po_switch(), "Drop the table (implies create)")
55 ("tree,t", var<string>("Events"), "Name of the root tree to convert")
56 ("table", var<string>(""), "Name of the table to use (default is the tree name)")
57 ("map", vars<Configuration::Map>(),"A regular expression which is applied to the leaf name befoee it is used as SQL column name)")
58 ("sql-type", vars<Configuration::Map>(),"Allows to overwrite the calculated SQL type for a given column e.g. 'sql-column-name/UNSIGNED IN'")
59 ("ignore", vars<string>(), "Ignore the given leaf, if the given regular expression matches")
60 ("primary", vars<string>(), "List of columns to be used as primary keys during table creation (in connection with --create)")
61 ("first", var<int64_t>(int64_t(0)), "First event to start with (default: 0), mainly for test purpose")
62 ("max", var<int64_t>(int64_t(0)), "Maximum number of events to process (0: all), mainly for test purpose")
63 ("engine", var<string>(""), "Database engine to be used when a new table is created")
64 ("row-format", var<string>(""), "Defines the ROW_FORMAT keyword for table creation")
65 ("duplicate", vars<string>(), "Specifies an assignment_list for an 'ON DUPLICATE KEY UPDATE' expression")
66 ("ignore-errors", po_switch(), "Adds the IGNORE keyword to the INSERT query (turns errors into warnings, ignores rows with errors)")
67 ("const.*", var<string>(), "Insert a constant number into the given column (--const.mycolumn=5). A special case is `/.../.../`")
68 ("conditional", po_switch(), "Conditional insert. Only insert if no entry exists yet with the constants defined by --const")
69 ("delete", po_switch(), "Delete all entries first which fit all constant columns defined by --const")
70 ("index", po_switch(), "If a table is created, all const columns are used as a single index (INDEX)")
71 ("unique", po_switch(), "If a table is created, all const columns are used as a unqiue index (UNIQUE)")
72 ;
73
74 po::options_description debug("Debug options");
75 debug.add_options()
76 ("no-insert", po_switch(), "Does not insert any data into the table")
77 ("dry-run", po_switch(), "Skip any query which changes the databse (might result in consecutive failures)")
78 ("print-connection", po_switch(), "Print database connection information")
79 ("print-ls", po_switch(), "Calls TFile::ls()")
80 ("print-branches", po_switch(), "Print the branches found in the tree")
81 ("print-leaves", po_switch(), "Print the leaves found in the tree (this is what is processed)")
82 ("print-insert", po_switch(), "Print the INSERT query (note that it contains all data)")
83 ("print-create", po_switch(), "Print the CREATE query")
84 ("print-select", po_switch(), "Print the SELECT query for the conditional execution")
85 ("print-delete", po_switch(), "Print the DELETE query")
86 ("verbose,v", var<uint16_t>(1), "Verbosity (0: quiet, 1: default, 2: more, 3, ...)")
87 ;
88
89 po::positional_options_description p;
90 p.add("file", 1); // The 1st positional options (n=1)
91
92 conf.AddOptions(control);
93 conf.AddOptions(debug);
94 conf.SetArgumentPositions(p);
95}
96
97void PrintUsage()
98{
99 cout <<
100 "root2sql - Fills the data from a root file into a database\n"
101 "\n"
102 "For convenience, this documentation uses the extended version of the options, "
103 "refer to the output below to get the abbreviations.\n"
104 "\n"
105 "This is a general purpose tool to fill the contents of a root file into a database "
106 "as long as this is technically possible and makes sense. Note that root can even "
107 "write complex data like a TH1F into a database, this is not the purpose of this "
108 "program.\n"
109 "\n"
110 "Each root tree has branches and leaves (the basic data types). These leaves can "
111 "be read independently of the classes which were used to write the root file. "
112 "The default tree to read from is 'Events' but the name can be overwritten "
113 "using --tree. The default table name to fill the data into is identical to "
114 "the tree name. It can be overwritten using --table.\n"
115 "\n"
116 "To get a list of the contents (keys and trees) of a root file, you can use --print-ls. "
117 "The name of each column to which data is filled from a leave is obtained from "
118 "the leaves' names. The leave names can be checked using --print-leaves. "
119 "A --print-branches exists for convenience to print only the high-level branches. "
120 "Sometimes these names might be quite unconvenient like MTime.fTime.fMilliSec or "
121 "just MHillas.fWidth. To allow to simplify column names, regular expressions "
122 "(using boost's regex) can be defined to change the names. Note that these regular "
123 "expressions are applied one by one on each leaf's name. A valid expression could "
124 "be:\n"
125 " --map=MHillas\\.f/\n"
126 "which would remove all occurances of 'MHillas.f'. This option can be used more than "
127 "once. They are applied in sequence. A single match does not stop the sequence.\n"
128 "\n"
129 "Sometimes it might also be convenient to skip a leaf. This can be done with "
130 "the --ignore resource. If the given regular expresion yields a match, the "
131 "leaf will be ignored. Note that the regular expression works on the raw-name "
132 "of the leaf not the readily mapped SQL column names. Example:\n"
133 " --ignore=ThetaSq\\..*\n"
134 "will skip all leaved which start with 'ThetaSq.'. This option can be used"
135 "more than once.\n"
136 "\n"
137 "The data type of each column is kept as close as possible to the leaves' data "
138 "types. If for some reason this is not wanted, the data type of the SQL column "
139 "can be overwritten with --sql-type sql-column/sql-ytpe, for example:\n"
140 " --sql-type=FileId/UNSIGNED INT\n"
141 "while the first argument of the name of the SQL column to which the data type "
142 "should be applied. The second column is the basic SQL data type. The option can "
143 "be given more than once.\n"
144 "\n"
145 "Database interaction:\n"
146 "\n"
147 "To drop an existing table, --drop can be used.\n"
148 "\n"
149 "To create a table according to theSQL column names and data types, --create "
150 "can be used. The query used can be printed with --print-create even --create "
151 "has not been specified.\n"
152 "\n"
153 "To choose the columns which should become primary keys, use --primary, "
154 "for example:\n"
155 " --primary=col1\n"
156 "To define more than one column as primary key, the option can be given more than "
157 "once. Note that the combination of these columns must be unique.\n"
158 "\n"
159 "All columns are created as NOT NULL as default. To force a database engine "
160 "and/or a storage format, use --engine and --row-format.\n"
161 "\n"
162 "Usually, the INSERT query would fail if the PRIMARY key exists already. "
163 "This can be avoided using the 'ON DUPLICATE KEY UPDATE' directive. With the "
164 "--duplicate, you can specify what should be updated in case of a duplicate key. "
165 "To keep the row untouched, you can just update the primary key "
166 "with the identical primary key, e.g. --duplicate='MyPrimary=VALUES(MyPrimary)'. "
167 "The --duplicate resource can be specified more than once to add more expressions "
168 "to the assignment_list. For more details, see the MySQL manual.\n"
169 "\n"
170 "For debugging purpose, or to just create or drop a table, the final insert "
171 "query can be skipped using --no-insert. Note that for performance reason, "
172 "all data is collected in memory and a single INSERT query is issued at the "
173 "end.\n"
174 "\n"
175 "Another possibility is to add the IGNORE keyword to the INSERT query by "
176 "--ignore-errors, which essentially ignores all errors and turns them into "
177 "warnings which are printed after the query succeeded.\n"
178 "\n"
179 "Using a higher verbosity level (-v), an overview of the written columns or all "
180 "processed leaves is printed depending on the verbosity level. The output looks "
181 "like the following\n"
182 " Leaf name [root data type] (SQL name)\n"
183 "for example\n"
184 " MTime.fTime.fMilliSec [Long64_t] (MilliSec)\n"
185 "which means that the leaf MTime.fTime.fMilliSec is detected to be a Long64_t "
186 "which is filled into a column called MilliSec. Leaves with non basic data types "
187 "are ignored automatically and are marked as (-n/a-). User ignored columns "
188 "are marked as (-ignored-).\n"
189 "\n"
190 "A constant value for the given file can be inserted by using the --const directive. "
191 "For example --const.mycolumn=42 would insert 42 into a column called mycolumn. "
192 "The column is created as INT UNSIGNED as default which can be altered by "
193 "--sql-type. A special case is a value of the form `/regex/format/`. Here, the given "
194 "regular expression is applied to the filename and it is newly formated with "
195 "the new format string. Uses the standard formatting rules to replace matches "
196 "(those used by ECMAScript's replace method).\n"
197 "\n"
198 "Usually the previously defined constant values are helpful to create an index "
199 "which relates unambiguously the inserted data to the file. It might be useful "
200 "to delete all data which belongs to this particular file before new data is "
201 "entered. This can be achieved with the `--delete` directive. It deletes all "
202 "data from the table before inserting new data which fulfills the condition "
203 "defined by the `--const` directives.\n"
204 "\n"
205 "The constant values can also be used for a conditional execution (--conditional). "
206 "If any row with the given constant values are found, the execution is stopped "
207 "(note that this happend after the table drop/create but before the delete/insert.\n"
208 "\n"
209 "To ensure efficient access for a conditonal execution, it makes sense to have "
210 "an index created for those columns. This can be done during table creation "
211 "with the --index option.\n"
212 "\n"
213 "To create the index as a UNIQUE INDEX, you can use the --unique option which "
214 "implies --index.\n"
215 "\n"
216 "If a query failed, the query is printed to stderr together with the error message. "
217 "For the main INSERT query, this is only true if the verbosity level is at least 2 "
218 "or the query has less than 80*25 bytes.\n"
219 "\n"
220 "In case of success, 0 is returned, a value>0 otherwise.\n"
221 "\n"
222 "Usage: root2sql [options] -uri URI rootfile.root\n"
223 "\n"
224 ;
225 cout << endl;
226}
227
228void ErrorHandlerAll(Int_t level, Bool_t abort, const char *location, const char *msg)
229{
230 if (string(msg).substr(0,24)=="no dictionary for class ")
231 return;
232 if (string(msg).substr(0,15)=="unknown branch ")
233 return;
234
235 DefaultErrorHandler(level, abort, location, msg);
236}
237
238int main(int argc, const char* argv[])
239{
240 Time start;
241
242 gROOT->SetBatch();
243 SetErrorHandler(ErrorHandlerAll);
244
245 Configuration conf(argv[0]);
246 conf.SetPrintUsage(PrintUsage);
247 SetupConfiguration(conf);
248
249 if (!conf.DoParse(argc, argv))
250 return 127;
251
252 // ----------------------------- Evaluate options --------------------------
253 const string uri = conf.Get<string>("uri");
254 const string file = conf.Get<string>("file");
255 const string tree = conf.Get<string>("tree");
256 const string table = conf.Get<string>("table").empty() ? tree : conf.Get<string>("table");
257
258 const uint16_t verbose = conf.Get<uint16_t>("verbose");
259 const int64_t first = conf.Get<int64_t>("first");
260 const int64_t max = conf.Get<int64_t>("max");
261
262 const bool drop = conf.Get<bool>("drop");
263 const bool create = conf.Get<bool>("create") || drop;
264 const bool noinsert = conf.Get<bool>("no-insert");
265 const bool dry_run = conf.Get<bool>("dry-run");
266 const bool conditional = conf.Get<bool>("conditional");
267 const bool run_delete = conf.Get<bool>("delete");
268 const bool index = conf.Get<bool>("index");
269 const bool unique = conf.Get<bool>("unique");
270
271 const string engine = conf.Get<string>("engine");
272 const string row_format = conf.Get<string>("row-format");
273
274 const vector<string> duplicate = conf.Vec<string>("duplicate");
275
276 const bool ignore_errors = conf.Get<bool>("ignore-errors");
277
278 const bool print_connection = conf.Get<bool>("print-connection");
279 const bool print_ls = conf.Get<bool>("print-ls");
280 const bool print_branches = conf.Get<bool>("print-branches");
281 const bool print_leaves = conf.Get<bool>("print-leaves");
282 const bool print_create = conf.Get<bool>("print-create");
283 const bool print_insert = conf.Get<bool>("print-insert");
284 const bool print_select = conf.Get<bool>("print-select");
285 const bool print_delete = conf.Get<bool>("print-delete");
286
287 const auto mymap = conf.Vec<Configuration::Map>("map");
288 const auto sqltypes = conf.Vec<Configuration::Map>("sql-type");
289
290 const vector<string> _ignore = conf.Vec<string>("ignore");
291 const vector<string> primary = conf.Vec<string>("primary");
292
293 // -------------------------------------------------------------------------
294
295 if (verbose>0)
296 {
297 cout << "\n-------------------------- Evaluating file -------------------------\n";
298 cout << "Start Time: " << Time::sql << Time(Time::local) << endl;
299 }
300
301 TFile f(file.c_str());
302 if (f.IsZombie())
303 {
304 cerr << "Could not open file " << file << endl;
305 return 1;
306 }
307
308 if (verbose>0 && !print_ls)
309 cout << "File: " << file << endl;
310
311 if (print_ls)
312 {
313 cout << '\n';
314 f.ls();
315 cout << '\n';
316 }
317
318 TTree *T = 0;
319 f.GetObject(tree.c_str(), T);
320 if (!T)
321 {
322 cerr << "Could not open tree " << tree << endl;
323 return 2;
324 }
325
326 if (verbose>0)
327 cout << "Tree: " << tree << endl;
328
329 T->SetMakeClass(1);
330
331 TObjArray *branches = T->GetListOfBranches();
332 TObjArray *leaves = T->GetListOfLeaves();
333
334 if (print_branches)
335 {
336 cout << '\n';
337 branches->Print();
338 }
339
340 if (verbose>0)
341 cout << T->GetEntriesFast() << " events found." << endl;
342
343
344 if (verbose>0)
345 cout << branches->GetEntries() << " branches found." << endl;
346
347 if (print_leaves)
348 {
349 cout << '\n';
350 leaves->Print();
351 }
352 if (verbose>0)
353 cout << leaves->GetEntries() << " leaves found." << endl;
354
355 string query =
356 "CREATE TABLE IF NOT EXISTS `"+table+"`\n"
357 "(\n";
358
359 vector<FileEntry::Container> vec;
360
361 const auto fixed = conf.GetWildcardOptions("const.*");
362
363 string where;
364 vector<string> vindex;
365 for (auto it=fixed.cbegin(); it!=fixed.cend(); it++)
366 {
367 const string name = it->substr(6);
368 string val = conf.Get<string>(*it);
369
370 boost::smatch match;
371 if (boost::regex_match(val, match, boost::regex("\\/(.+)(?<!\\\\)\\/(.*)(?<!\\\\)\\/")))
372 {
373 const string reg = match[1];
374 const string fmt = match[2];
375
376 val = boost::regex_replace(file, boost::regex(reg), fmt.empty()?"$0":fmt,
377 boost::regex_constants::format_default|boost::regex_constants::format_no_copy);
378
379 if (verbose>0)
380 {
381 cout << "Regular expression detected for constant column `" << *it << "`\n";
382 cout << "Filename converted with /" << reg << "/ to /" << fmt << "/\n";
383 cout << "Filename: " << file << '\n';
384 cout << "Result: " << val << endl;
385 }
386 }
387
388 if (verbose>2)
389 cout << "\n" << val << " [-const-]";
390 if (verbose>1)
391 cout << " (" << name << ")";
392
393 string sqltype = "INT UNSIGNED";
394
395 for (auto m=sqltypes.cbegin(); m!=sqltypes.cend(); m++)
396 if (m->first==name)
397 sqltype = m->second;
398
399 if (!vec.empty())
400 query += ",\n";
401 query += " `"+name+"` "+sqltype+" NOT NULL COMMENT '--user--'";
402
403 vec.emplace_back(name, val);
404 where += " AND `"+name+"`="+val;
405 vindex.emplace_back(name);
406 }
407
408 const size_t nvec = vec.size();
409
410 TIter Next(leaves);
411 TObject *o = 0;
412 while ((o=Next()))
413 {
414 TLeaf *L = dynamic_cast<TLeaf*>(o);//c.GetLeaf(o->GetName());
415 if (!L)
416 continue;
417
418 if (verbose>2)
419 cout << '\n' << L->GetTitle() << " {" << L->GetTypeName() << "}";
420
421 if (L->GetLenStatic()!=L->GetLen())
422 {
423 if (verbose>2)
424 cout << " (-skipped-)";
425 continue;
426 }
427
428
429 string name = o->GetName();
430
431 bool found = false;
432 for (auto b=_ignore.cbegin(); b!=_ignore.cend(); b++)
433 {
434 if (boost::regex_match(name, boost::regex(*b)))
435 {
436 found = true;
437 if (verbose>2)
438 cout << " (-ignored-)";
439 break;
440 }
441 }
442 if (found)
443 continue;
444
445 const string tn = L->GetTypeName();
446
447 const auto it = FileEntry::LUT.root(tn);
448 if (it==FileEntry::LUT.cend())
449 {
450 if (verbose>2)
451 cout << " (-n/a-)";
452 continue;
453 }
454
455 if (verbose==2)
456 cout << '\n' << L->GetTitle() << " {" << L->GetTypeName() << "}";
457
458 for (auto m=mymap.cbegin(); m!=mymap.cend(); m++)
459 name = boost::regex_replace(name, boost::regex(m->first), m->second);
460
461 if (verbose>1)
462 cout << " (" << name << ")";
463
464 string sqltype = it->sql;
465
466 for (auto m=sqltypes.cbegin(); m!=sqltypes.cend(); m++)
467 if (m->first==name)
468 sqltype = m->second;
469
470 if (!vec.empty())
471 query += ",\n";
472
473 const size_t N = L->GetLenStatic();
474 for (size_t i=0; i<N; i++)
475 {
476 query += " `"+name;
477 if (N>1)
478 query += "["+to_string(i)+"]";
479 query += "` "+sqltype+" NOT NULL COMMENT '"+o->GetTitle()+"'";
480 if (N>1 && i!=N-1)
481 query += ",\n";
482 }
483
484 vec.emplace_back(o->GetTitle(), name, it->type, L->GetLenStatic());
485 T->SetBranchAddress(o->GetTitle(), vec.back().ptr);
486 }
487
488 if (verbose>1)
489 cout << "\n\n";
490 if (verbose>0)
491 {
492 if (nvec>0)
493 cout << nvec << " constant value column(s) configured." << endl;
494 cout << vec.size()-nvec << " leaf/leaves setup for reading." << endl;
495 }
496
497 UInt_t datatype = 0;
498 const bool has_datatype = T->SetBranchAddress("DataType.fVal", &datatype) >= 0;
499 if (has_datatype && verbose>0)
500 cout << "Rows with DataType.fVal!=1 will be skipped." << endl;
501
502 // -------------------------------------------------------------------------
503 // Setting up branch status (must be after all SetBranchAddress)
504 T->SetBranchStatus("*", 0);
505
506 Next.Reset();
507 while ((o=Next()))
508 {
509 const TLeaf *L = dynamic_cast<TLeaf*>(o);//c.GetLeaf(o->GetName());
510 if (!L)
511 continue;
512
513 const TBranch *B = L->GetBranch();
514 if (!B)
515 continue;
516
517 if (!B->GetAddress())
518 continue;
519
520 T->SetBranchStatus(B->GetName(), 1);
521 if (verbose>2)
522 cout << "Enable Branch: " << B->GetName() << endl;
523 }
524
525 // -------------------------------------------------------------------------
526 // Checking for database connection
527
528 if (verbose>0)
529 {
530 cout << "Connecting to database...\n";
531 cout << "Client Version: " << mysqlpp::Connection().client_version() << endl;
532 }
533
534 Database connection(uri);
535
536 if (verbose>0)
537 cout << "Server Version: " << (connection.connected()?connection.server_version():"<n/a>") << endl;
538
539 if (print_connection && connection.connected())
540 {
541 try
542 {
543 const auto &res1 = connection.query("SHOW STATUS LIKE 'Compression'").store();
544 cout << "Compression of databse connection is " << string(res1[0][1]) << endl;
545
546 const auto &res2 = connection.query("SHOW STATUS LIKE 'Ssl_cipher'").store();
547 cout << "Connection to databases is " << (string(res2[0][1]).empty()?"UNENCRYPTED":"ENCRYPTED ("+string(res2[0][1])+")") << endl;
548 }
549 catch (const exception &e)
550 {
551 cerr << "\nSHOW STATUS LIKE COMPRESSION\n\n";
552 cerr << "SQL query failed:\n" << e.what() << endl;
553 return 3;
554 }
555 }
556
557 // -------------------------------------------------------------------------
558
559 if (verbose>0)
560 cout << "\n--------------------------- Database Table -------------------------" << endl;
561
562 if (!primary.empty())
563 query += ",\n PRIMARY KEY USING BTREE (`"+boost::algorithm::join(primary, "`, `")+"`)";
564
565 if (!vindex.empty() && (index || unique))
566 query += ",\n "+string(unique?"UNIQUE ":"")+"INDEX USING BTREE (`"+boost::algorithm::join(vindex, "`, `")+"`)";
567
568 query +=
569 "\n)\n"
570 "DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci\n";
571 if (!engine.empty())
572 query += "ENGINE="+engine+"\n";
573 if (!row_format.empty())
574 query += "ROW_FORMAT="+row_format+"\n";
575 query += "COMMENT='created by "+fs::path(conf.GetName()).filename().string()+"'\n";
576
577 // FIXME: Can we omit the catching to be able to print the
578 // query 'autmatically'?
579 try
580 {
581 if (drop)
582 {
583 // => Simple result
584 if (!dry_run)
585 connection.query("DROP TABLE `"+table+"`").execute();
586 if (verbose>0)
587 {
588 if (!dry_run)
589 cout << "Table `" << table << "` dropped." << endl;
590 else
591 cout << "Dropping table `" << table << "`skipped!" << endl;
592 }
593
594 }
595 }
596 catch (const exception &e)
597 {
598 cerr << "DROP TABLE `" << table << "`\n\n";
599 cerr << "SQL query failed:\n" << e.what() << '\n' << endl;
600 return 4;
601 }
602
603 try
604 {
605 if (create && !dry_run)
606 connection.query(query).execute();
607 }
608 catch (const exception &e)
609 {
610 cerr << query << "\n\n";
611 cerr << "SQL query failed:\n" << e.what() << '\n' << endl;
612 return 5;
613 }
614
615 if (print_create)
616 cout << query << endl;
617
618 if (create && verbose>0)
619 {
620 if (!dry_run)
621 cout << "Table `" << table << "` created." << endl;
622 else
623 cout << "Creating table `" << table << "`skipped!" << endl;
624 }
625
626 try
627 {
628 if (conditional && !fixed.empty() && !drop)
629 {
630 const mysqlpp::StoreQueryResult res =
631 connection.query("SELECT 1 FROM `"+table+"` WHERE 1"+where+" LIMIT 1").store();
632
633 if (res.num_rows()>0)
634 {
635 if (verbose>0)
636 {
637 cout << "Conditional execution... detected existing rows!\n";
638 cout << "Exit.\n" << endl;
639 }
640 return 0;
641 }
642 }
643 }
644 catch (const exception &e)
645 {
646 cerr << "SELECT 1 FROM `" << table << "` WHERE 1" << where << " LIMIT 1\n\n";
647 cerr << "SQL query failed: " << e.what() << endl;
648 return 6;
649 }
650
651 if (print_select)
652 cout << "SELECT 1 FROM `" << table << "` WHERE 1" << where << " LIMIT 1" << endl;
653
654 try
655 {
656 if (run_delete)
657 {
658 if (verbose>0)
659 cout << "Deleting rows...";
660
661 if (!fixed.empty() && !drop && !dry_run)
662 {
663 if (verbose>0)
664 cout << endl;
665
666 const mysqlpp::SimpleResult res =
667 connection.query("DELETE FROM `"+table+"` WHERE 1"+where).execute();
668
669 if (verbose>0)
670 cout << res.rows() << " row(s) deleted.\n" << endl;
671 }
672 else
673 if (verbose>0)
674 cout << " skipped." << endl;
675 }
676 }
677 catch (const exception &e)
678 {
679 cerr << "DELETE FROM `"+table+"` WHERE 1" << where << "\n\n";
680 cerr << "SQL query failed: " << e.what() << endl;
681 return 7;
682 }
683
684 if (print_delete)
685 cout << "DELETE FROM `"+table+"` WHERE 1" << where << endl;
686
687
688 // -------------------------------------------------------------------------
689
690 if (verbose>0)
691 cout << "\n---------------------------- Reading file --------------------------" << endl;
692
693 //query = update ? "UPDATE" : "INSERT";
694 query = "INSERT ";
695 if (ignore_errors)
696 query += "IGNORE ";
697 query += "`"+table+"`\n"
698 "(\n";
699
700 for (auto c=vec.cbegin(); c!=vec.cend(); c++)
701 {
702 if (c!=vec.cbegin())
703 query += ",\n";
704
705 const size_t N = c->num;
706 for (size_t i=0; i<N; i++)
707 {
708 if (N==1)
709 query += " `"+c->column+"`";
710 else
711 query += " `"+c->column+"["+to_string(i)+"]`";
712
713 if (N>1 && i!=N-1)
714 query += ",\n";
715 }
716 }
717
718 query +=
719 "\n)\n"
720 "VALUES\n";
721
722 size_t count = 0;
723
724 const size_t num = max>0 && first+max<T->GetEntriesFast() ? (first+max) : T->GetEntriesFast();
725 for (size_t j=first; j<num; j++)
726 {
727 T->GetEntry(j);
728 if (has_datatype && datatype!=1)
729 continue;
730
731 if (count>0)
732 query += ",\n";
733
734 query += "(\n";
735
736 for (auto c=vec.cbegin(); c!=vec.cend(); c++)
737 {
738 if (c!=vec.cbegin())
739 query += ",\n";
740
741 const size_t N = c->num;
742 for (size_t i=0; i<N; i++)
743 {
744 query += " "+c->fmt(i);
745
746 if (print_insert && i==0)
747 query += " /* "+c->column+" -> "+c->branch+" */";
748
749 if (N>1 && i!=N-1)
750 query += ",\n";
751 }
752 }
753 query += "\n)";
754
755 count ++;
756 }
757
758 if (!duplicate.empty())
759 query += "\nON DUPLICATE KEY UPDATE\n " + boost::join(duplicate, ",\n ");
760
761 if (verbose>0)
762 cout << count << " out of " << num << " row(s) read from file [N=" << first << ".." << num-1 << "]." << endl;
763
764 if (count==0)
765 {
766 if (verbose>0)
767 {
768 cout << "Total execution time: " << Time().UnixTime()-start.UnixTime() << "s.\n";
769 cout << "Success.\n" << endl;
770 }
771 return 0;
772 }
773
774 // -------------------------------------------------------------------------
775
776 if (verbose>0)
777 {
778 cout << "\n--------------------------- Inserting data -------------------------" << endl;
779 cout << "Sending INSERT query (" << query.length() << " bytes)" << endl;
780 }
781
782 try
783 {
784 if (!noinsert && !dry_run)
785 {
786 auto q = connection.query(query);
787 q.execute();
788 cout << q.info() << '\n' << endl;
789 }
790 else
791 cout << "Insert query skipped!" << endl;
792
793 if (print_insert)
794 cout << query << endl;
795 }
796 catch (const exception &e)
797 {
798 if (verbose>1 || query.length()<80*25)
799 cerr << query << "\n\n";
800 cerr << "SQL query failed (" << query.length() << " bytes):\n" << e.what() << '\n' << endl;
801 return 8;
802 }
803
804 if (verbose>0)
805 {
806 const auto sec = Time().UnixTime()-start.UnixTime();
807 cout << "Total execution time: " << sec << "s ";
808 cout << "(" << Tools::Fractional(sec/count) << "s/row)\n";
809
810 try
811 {
812 const auto resw =
813 connection.query("SHOW WARNINGS").store();
814
815 for (size_t i=0; i<resw.num_rows(); i++)
816 {
817 const mysqlpp::Row &roww = resw[i];
818
819 cout << roww["Level"] << '[' << roww["Code"] << "]: ";
820 cout << roww["Message"] << '\n';
821 }
822 cout << endl;
823
824 }
825 catch (const exception &e)
826 {
827 cerr << "\nSHOW WARNINGS\n\n";
828 cerr << "SQL query failed:\n" << e.what() << '\n' <<endl;
829 return 9;
830 }
831 }
832
833 if (print_connection)
834 {
835 try
836 {
837 // Exchange _send and _received as it is the view of the server
838 const auto &res1 = connection.query("SHOW STATUS LIKE 'Bytes_%'").store();
839 cout << left << setw(16) << res1[1]["Variable_name"] << ' ' << Tools::Scientific(res1[0]["Value"]) << endl;
840 cout << left << setw(16) << res1[0]["Variable_name"] << ' ' << Tools::Scientific(res1[1]["Value"]) << endl;
841 cout << endl;
842 }
843 catch (const exception &e)
844 {
845 cerr << "\nSHOW STATUS LIKE 'Bytes_%'\n\n";
846 cerr << "SQL query failed:\n" << e.what() << endl;
847 return 10;
848 }
849 }
850
851 cout << "Success!\n" << endl;
852 return 0;
853}
Note: See TracBrowser for help on using the repository browser.