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

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