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

Last change on this file since 19282 was 19282, checked in by tbretz, 10 months 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.