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

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