source: trunk/FACT++/src/fits2sql.cc@ 20086

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