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

Last change on this file since 19279 was 19279, checked in by tbretz, 10 months ago
Added TFile::ls() as an option.
File size: 33.0 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                cout << "Table `" << table << "` dropped." << endl;
669        }
670    }
671    catch (const exception &e)
672    {
673        cerr << "DROP TABLE `" << table << "`\n\n";
674        cerr << "SQL query failed:\n" << e.what() << '\n' << endl;
675        return 4;
676    }
677
678    try
679    {
680        if (create && !dry_run)
681            connection.query(query).execute();
682    }
683    catch (const exception &e)
684    {
685        cerr << query << "\n\n";
686        cerr << "SQL query failed:\n" << e.what() << '\n' << endl;
687        return 5;
688    }
689
690    if (print_create)
691        cout << query << endl;
692
693    if (create && verbose>0)
694        cout << "Table `" << table << "` created." << endl;
695
696
697    try
698    {
699        if (conditional && !fixed.empty() && !drop)
700        {
701            const mysqlpp::StoreQueryResult res =
702                connection.query("SELECT 1 FROM `"+table+"` WHERE 1"+where+" LIMIT 1").store();
703
704            if (res.num_rows()>0)
705            {
706                if (verbose>0)
707                {
708                    cout << "Conditional execution... detected existing rows!\n";
709                    cout << "Exit.\n" << endl;
710                }
711                return 0;
712            }
713        }
714    }
715    catch (const exception &e)
716    {
717        cerr << "SELECT 1 FROM `" << table << "` WHERE 1" << where << " LIMIT 1\n\n";
718        cerr << "SQL query failed: " << e.what() << endl;
719        return 6;
720    }
721
722    if (print_select)
723        cout << "SELECT 1 FROM `" << table << "` WHERE 1" << where << " LIMIT 1" << endl;
724
725    try
726    {
727        if (run_delete)
728        {
729            if (verbose>0)
730                cout << "Deleting rows...";
731
732            if (!fixed.empty() && !drop && !dry_run)
733            {
734                if (verbose>0)
735                    cout << endl;
736
737                const mysqlpp::SimpleResult res =
738                    connection.query("DELETE FROM `"+table+"` WHERE 1"+where).execute();
739
740                if (verbose>0)
741                    cout << res.rows() << " row(s) deleted.\n" << endl;
742            }
743            else
744                if (verbose>0)
745                    cout << " skipped." << endl;
746        }
747    }
748    catch (const exception &e)
749    {
750        cerr << "DELETE FROM `"+table+"` WHERE 1" << where << "\n\n";
751        cerr << "SQL query failed: " << e.what() << endl;
752        return 7;
753    }
754
755    if (print_delete)
756        cout << "DELETE FROM `"+table+"` WHERE 1" << where << endl;
757
758
759    // -------------------------------------------------------------------------
760
761    if (verbose>0)
762        cout << "\n---------------------------- Reading file --------------------------" << endl;
763
764    //query = update ? "UPDATE" : "INSERT";
765    query = "INSERT ";
766    if (ignore_errors)
767        query += "IGNORE ";
768    query += "`"+table+"`\n"
769        "(\n";
770
771    for (auto c=vec.cbegin(); c!=vec.cend(); c++)
772    {
773        if (c!=vec.cbegin())
774            query += ",\n";
775
776        const size_t N = c->num;
777        for (size_t i=0; i<N; i++)
778        {
779            if (N==1)
780                query += "   `"+c->column+"`";
781            else
782                query += "   `"+c->column+"["+to_string(i)+"]`";
783
784            if (N>1 && i!=N-1)
785                query += ",\n";
786        }
787    }
788
789    query +=
790        "\n)\n"
791        "VALUES\n";
792
793    size_t count = 0;
794
795    const size_t num = max>0 && (max-first)<T->GetEntriesFast() ? (max-first) : T->GetEntriesFast();
796    for (size_t j=first; j<num; j++)
797    {
798        T->GetEntry(j);
799        if (has_datatype && datatype!=1)
800            continue;
801
802        if (count>0)
803            query += ",\n";
804
805        query += "(\n";
806
807        for (auto c=vec.cbegin(); c!=vec.cend(); c++)
808        {
809            if (c!=vec.cbegin())
810                query += ",\n";
811
812            const size_t N = c->num;
813            for (size_t i=0; i<N; i++)
814            {
815                query += "   "+c->fmt(i);
816
817                if (print_insert && i==0)
818                    query += " /* "+c->column+" -> "+c->branch+" */";
819
820                if (N>1 && i!=N-1)
821                    query += ",\n";
822            }
823        }
824        query += "\n)";
825
826        count ++;
827    }
828
829    if (!duplicate.empty())
830        query += "\nON DUPLICATE KEY UPDATE\n   " + boost::join(duplicate, ",\n   ");
831
832    if (verbose>0)
833        cout << count << " out of " << num << " row(s) read from file [N=" << first << ".." << num-1 << "]." << endl;
834
835    if (count==0)
836    {
837        if (verbose>0)
838        {
839            cout << "Total execution time: " << Time().UnixTime()-start.UnixTime() << "s.\n";
840            cout << "Success.\n" << endl;
841        }
842        return 0;
843    }
844
845    // -------------------------------------------------------------------------
846
847    if (verbose>0)
848    {
849        cout << "\n--------------------------- Inserting data -------------------------" << endl;
850        cout << "Sending INSERT query (" << query.length() << " bytes)"  << endl;
851    }
852
853    try
854    {
855        if (!noinsert && !dry_run)
856        {
857            auto q = connection.query(query);
858            q.execute();
859            cout << q.info() << '\n' << endl;
860        }
861        else
862            cout << "Insert query skipped!" << endl;
863
864        if (print_insert)
865            cout << query << endl;
866    }
867    catch (const exception &e)
868    {
869        if (verbose>1 || query.length()<80*25)
870            cerr << query << "\n\n";
871        cerr << "SQL query failed (" << query.length() << " bytes):\n" << e.what() << '\n' << endl;
872        return 8;
873    }
874
875    if (verbose>0)
876    {
877        const auto sec = Time().UnixTime()-start.UnixTime();
878        cout << "Total execution time: " << sec << "s ";
879        cout << "(" << Tools::Fractional(sec/count) << "s/row)\n";
880
881        try
882        {
883            const auto resw =
884                connection.query("SHOW WARNINGS").store();
885
886            for (size_t i=0; i<resw.num_rows(); i++)
887            {
888                const mysqlpp::Row &roww = resw[i];
889
890                cout << roww["Level"] << '[' << roww["Code"] << "]: ";
891                cout << roww["Message"] << '\n';
892            }
893            cout << endl;
894
895        }
896        catch (const exception &e)
897        {
898            cerr << "\nSHOW WARNINGS\n\n";
899            cerr << "SQL query failed:\n" << e.what() << '\n' <<endl;
900            return 9;
901        }
902    }
903
904    if (print_connection)
905    {
906        try
907        {
908            // Exchange _send and _received as it is the view of the server
909            const auto &res1 = connection.query("SHOW STATUS LIKE 'Bytes_%'").store();
910            cout << left << setw(16) << res1[1]["Variable_name"] << ' ' << Tools::Scientific(res1[0]["Value"]) << endl;
911            cout << left << setw(16) << res1[0]["Variable_name"] << ' ' << Tools::Scientific(res1[1]["Value"]) << endl;
912            cout << endl;
913        }
914        catch (const exception &e)
915        {
916            cerr << "\nSHOW STATUS LIKE 'Bytes_%'\n\n";
917            cerr << "SQL query failed:\n" << e.what() << endl;
918            return 10;
919        }
920    }
921
922    cout << "Success!\n" << endl;
923    return 0;
924}
Note: See TracBrowser for help on using the repository browser.