| 1 | #include "Database.h"
|
|---|
| 2 |
|
|---|
| 3 | #include <regex>
|
|---|
| 4 |
|
|---|
| 5 | #include <boost/algorithm/string/join.hpp>
|
|---|
| 6 | #include <boost/tokenizer.hpp>
|
|---|
| 7 | #include <boost/algorithm/string.hpp>
|
|---|
| 8 |
|
|---|
| 9 | #include "tools.h"
|
|---|
| 10 | #include "Time.h"
|
|---|
| 11 | #include "Configuration.h"
|
|---|
| 12 |
|
|---|
| 13 | #include <TROOT.h>
|
|---|
| 14 | #include <TSystem.h>
|
|---|
| 15 | #include <TFile.h>
|
|---|
| 16 | #include <TTree.h>
|
|---|
| 17 |
|
|---|
| 18 | using namespace std;
|
|---|
| 19 |
|
|---|
| 20 | // ------------------------------------------------------------------------
|
|---|
| 21 |
|
|---|
| 22 | void SetupConfiguration(Configuration &conf)
|
|---|
| 23 | {
|
|---|
| 24 | po::options_description control("Database options");
|
|---|
| 25 | control.add_options()
|
|---|
| 26 | ("uri,u", var<string>()->required(), "Database link as in\n\tuser:password@server[:port]/database[?compress=0|1].")
|
|---|
| 27 | ("query,q", var<string>(""), "MySQL query (overwrites --file)")
|
|---|
| 28 | ("file", var<string>("rootify.sql"), "An ASCII file with the MySQL query (overwrites --query)")
|
|---|
| 29 | ("ignore-null,i", po_switch(), "Do not skip rows containing any NULL field")
|
|---|
| 30 | ("display,d", po_switch(), "Displays contents on the screen (most usefull in combination with mysql statements as SHOW or EXPLAIN)")
|
|---|
| 31 | ("write,w", var<string>(""), "Write output to an ascii file")
|
|---|
| 32 | ("delimiter", var<string>(""), "The delimiter used if contents are displayed with --display (default=\\t)")
|
|---|
| 33 | ("explain", po_switch(), "Requests an EXPLAIN from the server (shows the server optimized query)\nsee also https://dev.mysql.com/doc/refman/explain-output.html")
|
|---|
| 34 | ("profiling", po_switch(), "Turn on profiling and print profile")
|
|---|
| 35 | ("var.*", var<string>(), "Predefined SQL user variables (@VAR)")
|
|---|
| 36 | ("env.*", vars<string>(), "Predefined environment for substitutions in the query ($ENV)")
|
|---|
| 37 | ("list.*", var<string>(), "Predefined environment for substitutions in the query ($ENV). The list is read from the given file (one list entry per line)")
|
|---|
| 38 | ("print-connection", po_switch(), "Print database connection information")
|
|---|
| 39 | ("verbose,v", var<uint16_t>(1), "Verbosity (0: quiet, 1: default, 2: more, 3, ...)")
|
|---|
| 40 | ;
|
|---|
| 41 |
|
|---|
| 42 | po::options_description root("Root file options");
|
|---|
| 43 | root.add_options()
|
|---|
| 44 | ("out,o", var<string>("rootify.root"), "Output root file name")
|
|---|
| 45 | ("force,f", po_switch(), "Force overwriting an existing root file ('RECREATE')")
|
|---|
| 46 | ("update", po_switch(), "Update an existing root file with the new tree ('UPDATE')")
|
|---|
| 47 | ("compression,c", var<uint16_t>(1), "zlib compression level for the root file")
|
|---|
| 48 | ("tree,t", var<string>("Result"), "Name of the root tree")
|
|---|
| 49 | ("ignore", vars<string>(), "Ignore the given columns")
|
|---|
| 50 | ("null,n", po_switch(), "Redirect the output file to /dev/null (mainly for debugging purposes, e.g. performance studies)")
|
|---|
| 51 | ("no-fill", po_switch(), "Do not fill events into the root file (mainly for debugging purposes, e.g. performance studies)")
|
|---|
| 52 | ;
|
|---|
| 53 |
|
|---|
| 54 | po::positional_options_description p;
|
|---|
| 55 | p.add("file", 1); // The 1st positional options (n=1)
|
|---|
| 56 | p.add("out", 1); // The 2nd positional options (n=1)
|
|---|
| 57 |
|
|---|
| 58 | conf.AddOptions(control);
|
|---|
| 59 | conf.AddOptions(root);
|
|---|
| 60 | conf.SetArgumentPositions(p);
|
|---|
| 61 | }
|
|---|
| 62 |
|
|---|
| 63 | void PrintUsage()
|
|---|
| 64 | {
|
|---|
| 65 | cout <<
|
|---|
| 66 | "rootifysql - Converts the result of a mysql query into a root file\n"
|
|---|
| 67 | "\n"
|
|---|
| 68 | "For convenience, this documentation uses the extended version of the options, "
|
|---|
| 69 | "refer to the output below to get the abbreviations.\n"
|
|---|
| 70 | "\n"
|
|---|
| 71 | "Writes the result of a mysql query into a root file. For each column, a branch is "
|
|---|
| 72 | "created of type double with the field name as name. This is usually the column name "
|
|---|
| 73 | "if not specified otherwise by the AS mysql directive.\n"
|
|---|
| 74 | "\n"
|
|---|
| 75 | "Columns with CHAR or VARCHAR as field type are ignored. DATETIME, DATE and TIME "
|
|---|
| 76 | "columns are converted to unix time (time_t). Rows containing any file which is "
|
|---|
| 77 | "NULL are skipped if not suppressed by the --ignore-null option. Ideally, the query "
|
|---|
| 78 | "is compiled in a way that no NULL field is returned. With the --display option the "
|
|---|
| 79 | "result of the request is printed on the screen (NULL skipping still in action). "
|
|---|
| 80 | "This can be useful to create an ascii file or to show results as 'SHOW DATABASES' "
|
|---|
| 81 | "or 'EXPLAIN table'. To redirect the contents into an ascii file, the option -v0 "
|
|---|
| 82 | "is useful. To suppress writing to an output file --null can be used.\n"
|
|---|
| 83 | "\n"
|
|---|
| 84 | "The default is to read the query from a file called rootify.sql. Except if a different "
|
|---|
| 85 | "filename is specified by the --file option or a query is given with --query.\n"
|
|---|
| 86 | "\n"
|
|---|
| 87 | "As a trick, the rootify.sql file can be made excutable (chmod u+x rootify.sql). "
|
|---|
| 88 | "If the first line contains '#!rootifysql', the script can be executed directly.\n"
|
|---|
| 89 | "\n"
|
|---|
| 90 | "Columns whose name start with @ are skipped. If you want them in your output file "
|
|---|
| 91 | "give them a name using AS, e.g. 'SELECT @A:=5 AS A'.\n"
|
|---|
| 92 | "\n"
|
|---|
| 93 | "You can use variables in your sql query like @MyVar and define them on the "
|
|---|
| 94 | "command line. In this example with --var.MyVar=5\n"
|
|---|
| 95 | "\n"
|
|---|
| 96 | "You can use environment definitions for substitutions in your SQL query. "
|
|---|
| 97 | "For example --env.TEST=5 would replace $TEST or ${TEST} in your query by 5."
|
|---|
| 98 | "If you specify one environment variable more than once, a list is created. "
|
|---|
| 99 | "For example --env.TEST=1 --env.TEST=2 --env.TEST=3 would substitute "
|
|---|
| 100 | "$TEST or ${TEST} by '1, 2, 3'. This is useful for the SQL `IN` keyword. "
|
|---|
| 101 | "You can also read the values for an enviroment substitution from a file "
|
|---|
| 102 | "(one element per line), e.g. --env.TEST=file.txt. Empty lines and lines "
|
|---|
| 103 | "starting with a # are skipped.\n"
|
|---|
| 104 | "\n"
|
|---|
| 105 | "Comments in the query-file can be placed according to the SQL standard inline "
|
|---|
| 106 | "/*comment*/ or introduced with # (shell script style) or -- (SQL style).\n"
|
|---|
| 107 | "\n"
|
|---|
| 108 | "In case of success, 0 is returned, a value>0 otherwise.\n"
|
|---|
| 109 | "\n"
|
|---|
| 110 | "Usage: rootifysql [rootify.sql [rootify.root]] [-u URI] [-q query|-f file] [-i] [-o out] [-f] [-cN] [-t tree] [-vN]\n"
|
|---|
| 111 | "\n"
|
|---|
| 112 | ;
|
|---|
| 113 | cout << endl;
|
|---|
| 114 | }
|
|---|
| 115 |
|
|---|
| 116 | struct ExplainParser
|
|---|
| 117 | {
|
|---|
| 118 | string sql;
|
|---|
| 119 |
|
|---|
| 120 | vector<string> vec;
|
|---|
| 121 |
|
|---|
| 122 | string substitute(string _str, const regex &expr)
|
|---|
| 123 | {
|
|---|
| 124 | smatch match;
|
|---|
| 125 | while (regex_search(_str, match, expr, regex_constants::format_first_only))
|
|---|
| 126 | {
|
|---|
| 127 | const auto &len = match.length(0);
|
|---|
| 128 | const auto &pos = match.position(0);
|
|---|
| 129 | const auto &str = match.str(0);
|
|---|
| 130 |
|
|---|
| 131 | const auto it = find(vec.cbegin(), vec.cend(), str);
|
|---|
| 132 | const size_t id = it==vec.cend() ? vec.size() : it-vec.cbegin();
|
|---|
| 133 |
|
|---|
| 134 | _str.replace(pos, len, "{"+to_string(id)+"}");
|
|---|
| 135 |
|
|---|
| 136 | if (it==vec.cend())
|
|---|
| 137 | vec.push_back(str);//.substr(1, str.size()-2));
|
|---|
| 138 | }
|
|---|
| 139 |
|
|---|
| 140 | return _str;
|
|---|
| 141 | }
|
|---|
| 142 |
|
|---|
| 143 | string substitute(const string &str, const string &expr)
|
|---|
| 144 | {
|
|---|
| 145 | return substitute(str, regex(expr));
|
|---|
| 146 | }
|
|---|
| 147 |
|
|---|
| 148 | vector<string> queries;
|
|---|
| 149 |
|
|---|
| 150 | string resub(string str)
|
|---|
| 151 | {
|
|---|
| 152 | // search for "KEYWORD expression"
|
|---|
| 153 | regex reg("\\{[0-9]+\\}");
|
|---|
| 154 |
|
|---|
| 155 | //smatch match;
|
|---|
| 156 | smatch match;
|
|---|
| 157 | while (regex_search(str, match, reg, regex_constants::format_first_only))
|
|---|
| 158 | {
|
|---|
| 159 | const auto &len = match.length(0);
|
|---|
| 160 | const auto &pos = match.position(0);
|
|---|
| 161 | const auto &arg = match.str(0); // Argument
|
|---|
| 162 |
|
|---|
| 163 | const auto idx = atoi(arg.c_str()+1);
|
|---|
| 164 |
|
|---|
| 165 | str.replace(pos, len, resub(vec[idx]));
|
|---|
| 166 | }
|
|---|
| 167 |
|
|---|
| 168 | return str;
|
|---|
| 169 | }
|
|---|
| 170 |
|
|---|
| 171 | void expression(string expr, size_t indent=0)
|
|---|
| 172 | {
|
|---|
| 173 | if (expr[0]=='{')
|
|---|
| 174 | {
|
|---|
| 175 | const auto idx = atoi(expr.c_str()+1);
|
|---|
| 176 |
|
|---|
| 177 | // This is a subquery
|
|---|
| 178 | if (vec[idx].substr(0,3)=="(/*")
|
|---|
| 179 | {
|
|---|
| 180 | cout << setw(indent) << ' ' << "(\n";
|
|---|
| 181 | find_tokens(vec[idx], indent+4);
|
|---|
| 182 | cout << setw(indent) << ' ' << ") ";
|
|---|
| 183 | }
|
|---|
| 184 | else
|
|---|
| 185 | // This is just something to substitute back
|
|---|
| 186 | if (vec[idx].substr(0,2)=="({")
|
|---|
| 187 | {
|
|---|
| 188 | cout << setw(indent) << ' ' << "(" << resub(vec[idx]) << ") ";
|
|---|
| 189 | }
|
|---|
| 190 | else
|
|---|
| 191 | {
|
|---|
| 192 | if (indent>0)
|
|---|
| 193 | cout << setw(indent) << ' ';
|
|---|
| 194 | cout << resub(vec[idx]);
|
|---|
| 195 | }
|
|---|
| 196 | }
|
|---|
| 197 | else
|
|---|
| 198 | {
|
|---|
| 199 | if (indent>0)
|
|---|
| 200 | cout << setw(indent) << ' ';
|
|---|
| 201 | cout << resub(expr);
|
|---|
| 202 | }
|
|---|
| 203 | }
|
|---|
| 204 |
|
|---|
| 205 | void find_tokens(string str, size_t indent=0)
|
|---|
| 206 | {
|
|---|
| 207 | // ( COMMENT )?( TOKEN )?(( {NNN} | NNN )( AS|ON ( {NNN}) ))?(,)?)
|
|---|
| 208 | //regex reg("(\\/\\*\\ select\\#[0-9]+\\ \\*\\/\\ *)?([a-zA-Z ]+)?((\\{[0-9]+\\}|[0-9]+)(\\ ?([Aa][Ss]|[Oo][Nn])\\ ?(\\{[0-9]+\\}))?(,)?)");
|
|---|
| 209 |
|
|---|
| 210 | const string _com = "\\/\\*\\ select\\#[0-9]+\\ \\*\\/\\ *";
|
|---|
| 211 |
|
|---|
| 212 | const string _tok = "[a-zA-Z_ ]+";
|
|---|
| 213 |
|
|---|
| 214 | const string _nnn = "\\{[0-9]+\\}|[0-9]+";
|
|---|
| 215 |
|
|---|
| 216 | const string _as = "\\ ?([Aa][Ss])\\ ?";
|
|---|
| 217 |
|
|---|
| 218 | // ( _nnn ) ( _as ( _nnn ))?(,)? // can also match noting in between two {NNN}
|
|---|
| 219 | const string _exp = "("+_nnn+")" + "("+_as+"("+_nnn+"))?(,)?";
|
|---|
| 220 |
|
|---|
| 221 | // Matche: ( _com )? ( ( _tok )? ( _exp ) | ( _tok ) )
|
|---|
| 222 | regex reg("("+_com+")?" + "(" + "("+_tok+")?"+"("+_exp+")" + "|" + "("+_tok+")" + ")");
|
|---|
| 223 |
|
|---|
| 224 | smatch match;
|
|---|
| 225 | while (regex_search(str, match, reg, regex_constants::format_first_only))
|
|---|
| 226 | {
|
|---|
| 227 |
|
|---|
| 228 | const auto &com = match.str(1); // comment
|
|---|
| 229 | const auto &tok1 = Tools::Trim(match.str(3)); // token with expression
|
|---|
| 230 | const auto &arg1 = match.str(5); // argument 1
|
|---|
| 231 | const auto &as = match.str(7); // as
|
|---|
| 232 | const auto &arg2 = match.str(8); // argument 2
|
|---|
| 233 | const auto &comma = match.str(9); // comma
|
|---|
| 234 | const auto &tok2 = Tools::Trim(match.str(10)); // token without expression
|
|---|
| 235 |
|
|---|
| 236 | if (!com.empty())
|
|---|
| 237 | cout << setw(indent) << ' ' << "\033[34m" << com << "\033[0m" << '\n';
|
|---|
| 238 |
|
|---|
| 239 | if (!tok1.empty())
|
|---|
| 240 | cout << setw(indent) << ' ' << "\033[32m" << tok1 << "\033[0m" << '\n';
|
|---|
| 241 | if (!tok2.empty())
|
|---|
| 242 | cout << setw(indent) << ' ' << "\033[32m" << tok2 << "\033[0m" << '\n';
|
|---|
| 243 |
|
|---|
| 244 | if (!arg1.empty())
|
|---|
| 245 | {
|
|---|
| 246 | expression(arg1, indent+4);
|
|---|
| 247 |
|
|---|
| 248 | if (!as.empty())
|
|---|
| 249 | cout << " \033[33m" << as << "\033[0m ";
|
|---|
| 250 |
|
|---|
| 251 | if (!arg2.empty())
|
|---|
| 252 | expression(arg2);
|
|---|
| 253 |
|
|---|
| 254 | if (!comma.empty())
|
|---|
| 255 | cout << ',';
|
|---|
| 256 |
|
|---|
| 257 | cout << '\n';
|
|---|
| 258 | }
|
|---|
| 259 |
|
|---|
| 260 | str = str.substr(match.position(0)+match.length(0));
|
|---|
| 261 | }
|
|---|
| 262 | }
|
|---|
| 263 |
|
|---|
| 264 |
|
|---|
| 265 | ExplainParser(const string &_sql) : sql(_sql)
|
|---|
| 266 | {
|
|---|
| 267 | // substitute all strings
|
|---|
| 268 | sql = substitute(sql, "'[^']*'");
|
|---|
| 269 |
|
|---|
| 270 | // substitute all escaped sequences (`something`.`something-else`)
|
|---|
| 271 | sql = substitute(sql, "`[^`]*`(\\.`[^`]*`)*");
|
|---|
| 272 |
|
|---|
| 273 | // substitute all paranthesis
|
|---|
| 274 | sql = substitute(sql, "[a-zA-Z0-9_]*\\([^\\(\\)]*\\)");
|
|---|
| 275 |
|
|---|
| 276 | //cout << sql << "\n\n";
|
|---|
| 277 | find_tokens(sql);
|
|---|
| 278 | cout << endl;
|
|---|
| 279 | }
|
|---|
| 280 | };
|
|---|
| 281 |
|
|---|
| 282 | // Remove queries...
|
|---|
| 283 | void format(string sql)
|
|---|
| 284 | {
|
|---|
| 285 | ExplainParser p(sql);
|
|---|
| 286 |
|
|---|
| 287 | /*
|
|---|
| 288 |
|
|---|
| 289 | SELECT
|
|---|
| 290 | [ALL | DISTINCT | DISTINCTROW ]
|
|---|
| 291 | [HIGH_PRIORITY]
|
|---|
| 292 | [STRAIGHT_JOIN]
|
|---|
| 293 | [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
|
|---|
| 294 | [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
|
|---|
| 295 | select_expr [, select_expr ...]
|
|---|
| 296 | [FROM table_references
|
|---|
| 297 | [PARTITION partition_list]
|
|---|
| 298 | [WHERE where_condition]
|
|---|
| 299 | [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
|
|---|
| 300 | [HAVING where_condition]
|
|---|
| 301 | [WINDOW window_name AS (window_spec)
|
|---|
| 302 | [, window_name AS (window_spec)] ...]
|
|---|
| 303 | [ORDER BY {col_name | expr | position}
|
|---|
| 304 | [ASC | DESC], ... [WITH ROLLUP]]
|
|---|
| 305 | [LIMIT {[offset,] row_count | row_count OFFSET offset}]
|
|---|
| 306 | [INTO OUTFILE 'file_name'
|
|---|
| 307 | [CHARACTER SET charset_name]
|
|---|
| 308 | export_options
|
|---|
| 309 | | INTO DUMPFILE 'file_name'
|
|---|
| 310 | | INTO var_name [, var_name]]
|
|---|
| 311 | [FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] [NOWAIT | SKIP LOCKED]
|
|---|
| 312 | | LOCK IN SHARE MODE]]
|
|---|
| 313 | */
|
|---|
| 314 |
|
|---|
| 315 | /*
|
|---|
| 316 | table_references:
|
|---|
| 317 | escaped_table_reference [, escaped_table_reference] ...
|
|---|
| 318 |
|
|---|
| 319 | escaped_table_reference:
|
|---|
| 320 | table_reference
|
|---|
| 321 | | { OJ table_reference }
|
|---|
| 322 |
|
|---|
| 323 | table_reference:
|
|---|
| 324 | table_factor
|
|---|
| 325 | | join_table
|
|---|
| 326 |
|
|---|
| 327 | table_factor:
|
|---|
| 328 | tbl_name [PARTITION (partition_names)]
|
|---|
| 329 | [[AS] alias] [index_hint_list]
|
|---|
| 330 | | table_subquery [AS] alias [(col_list)]
|
|---|
| 331 | | ( table_references )
|
|---|
| 332 |
|
|---|
| 333 | join_table:
|
|---|
| 334 | table_reference [INNER | CROSS] JOIN table_factor [join_condition]
|
|---|
| 335 | | table_reference STRAIGHT_JOIN table_factor
|
|---|
| 336 | | table_reference STRAIGHT_JOIN table_factor ON conditional_expr
|
|---|
| 337 | | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
|
|---|
| 338 | | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
|
|---|
| 339 |
|
|---|
| 340 | join_condition:
|
|---|
| 341 | ON conditional_expr
|
|---|
| 342 | | USING (column_list)
|
|---|
| 343 |
|
|---|
| 344 | index_hint_list:
|
|---|
| 345 | index_hint [, index_hint] ...
|
|---|
| 346 |
|
|---|
| 347 | index_hint:
|
|---|
| 348 | USE {INDEX|KEY}
|
|---|
| 349 | [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
|
|---|
| 350 | | IGNORE {INDEX|KEY}
|
|---|
| 351 | [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
|
|---|
| 352 | | FORCE {INDEX|KEY}
|
|---|
| 353 | [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
|
|---|
| 354 |
|
|---|
| 355 | index_list:
|
|---|
| 356 | index_name [, index_name] ...
|
|---|
| 357 | */
|
|---|
| 358 |
|
|---|
| 359 | }
|
|---|
| 360 |
|
|---|
| 361 | int finish(Database &connection, const uint16_t &verbose, const bool &profiling, const bool &print_connection)
|
|---|
| 362 | {
|
|---|
| 363 | if (verbose>0)
|
|---|
| 364 | {
|
|---|
| 365 | try
|
|---|
| 366 | {
|
|---|
| 367 | const auto resw =
|
|---|
| 368 | connection.query("SHOW WARNINGS").store();
|
|---|
| 369 |
|
|---|
| 370 | if (resw.num_rows()>0)
|
|---|
| 371 | cout << "\n" << resw.num_rows() << " Warning(s) issued:\n\n";
|
|---|
| 372 |
|
|---|
| 373 | for (size_t i=0; i<resw.num_rows(); i++)
|
|---|
| 374 | {
|
|---|
| 375 | const mysqlpp::Row &roww = resw[i];
|
|---|
| 376 |
|
|---|
| 377 | cout << roww["Level"] << '[' << roww["Code"] << "]: ";
|
|---|
| 378 | cout << roww["Message"] << '\n';
|
|---|
| 379 | }
|
|---|
| 380 | cout << endl;
|
|---|
| 381 |
|
|---|
| 382 | }
|
|---|
| 383 | catch (const exception &e)
|
|---|
| 384 | {
|
|---|
| 385 | cerr << "\nSHOW WARNINGS\n\n";
|
|---|
| 386 | cerr << "SQL query failed:\n" << e.what() << endl;
|
|---|
| 387 | return 1;
|
|---|
| 388 | }
|
|---|
| 389 | }
|
|---|
| 390 |
|
|---|
| 391 | if (profiling)
|
|---|
| 392 | {
|
|---|
| 393 | try
|
|---|
| 394 | {
|
|---|
| 395 | const auto N =
|
|---|
| 396 | connection.query("SHOW PROFILES").store().num_rows();
|
|---|
| 397 |
|
|---|
| 398 | const auto resp =
|
|---|
| 399 | connection.query("SHOW PROFILE ALL FOR QUERY "+to_string(verbose?N-1:N)).store();
|
|---|
| 400 |
|
|---|
| 401 | cout << '\n';
|
|---|
| 402 | cout << left;
|
|---|
| 403 | cout << setw(26) << "Status" << ' ';
|
|---|
| 404 | cout << right;
|
|---|
| 405 | cout << setw(11) << "Duration" << ' ';
|
|---|
| 406 | cout << setw(11) << "CPU User" << ' ';
|
|---|
| 407 | cout << setw(11) << "CPU System" << '\n';
|
|---|
| 408 | cout << "--------------------------------------------------------------\n";
|
|---|
| 409 | for (size_t i=0; i<resp.num_rows(); i++)
|
|---|
| 410 | {
|
|---|
| 411 | const mysqlpp::Row &rowp = resp[i];
|
|---|
| 412 |
|
|---|
| 413 | cout << left;
|
|---|
| 414 | cout << setw(26) << rowp["Status"] << ' ';
|
|---|
| 415 | cout << right;
|
|---|
| 416 | cout << setw(11) << rowp["Duration"] << ' ';
|
|---|
| 417 | cout << setw(11) << rowp["CPU_user"] << ' ';
|
|---|
| 418 | cout << setw(11) << rowp["CPU_system"] << '\n';
|
|---|
| 419 | }
|
|---|
| 420 | cout << "--------------------------------------------------------------\n";
|
|---|
| 421 | cout << endl;
|
|---|
| 422 | }
|
|---|
| 423 | catch (const exception &e)
|
|---|
| 424 | {
|
|---|
| 425 | cerr << "\nSHOW PROFILE ALL\n\n";
|
|---|
| 426 | cerr << "SQL query failed:\n" << e.what() << '\n' <<endl;
|
|---|
| 427 | return 2;
|
|---|
| 428 | }
|
|---|
| 429 | }
|
|---|
| 430 |
|
|---|
| 431 | if (print_connection)
|
|---|
| 432 | {
|
|---|
| 433 | try
|
|---|
| 434 | {
|
|---|
| 435 | // Exchange _send and _received as it is the view of the server
|
|---|
| 436 | const auto &res1 = connection.query("SHOW STATUS LIKE 'Bytes_%'").store();
|
|---|
| 437 | cout << left << setw(16) << res1[1]["Variable_name"] << ' ' << Tools::Scientific(res1[0]["Value"]) << endl;
|
|---|
| 438 | cout << left << setw(16) << res1[0]["Variable_name"] << ' ' << Tools::Scientific(res1[1]["Value"]) << endl;
|
|---|
| 439 | cout << endl;
|
|---|
| 440 | }
|
|---|
| 441 | catch (const exception &e)
|
|---|
| 442 | {
|
|---|
| 443 | cerr << "\nSHOW STATUS LIKE 'Bytes_%'\n\n";
|
|---|
| 444 | cerr << "SQL query failed:\n" << e.what() << endl;
|
|---|
| 445 | return 3;
|
|---|
| 446 | }
|
|---|
| 447 | }
|
|---|
| 448 |
|
|---|
| 449 | return 0;
|
|---|
| 450 |
|
|---|
| 451 | }
|
|---|
| 452 |
|
|---|
| 453 | int main(int argc, const char* argv[])
|
|---|
| 454 | {
|
|---|
| 455 | Time start;
|
|---|
| 456 |
|
|---|
| 457 | gROOT->SetBatch();
|
|---|
| 458 |
|
|---|
| 459 | Configuration conf(argv[0]);
|
|---|
| 460 | conf.SetPrintUsage(PrintUsage);
|
|---|
| 461 | SetupConfiguration(conf);
|
|---|
| 462 |
|
|---|
| 463 | if (!conf.DoParse(argc, argv))
|
|---|
| 464 | return 127;
|
|---|
| 465 |
|
|---|
| 466 | // ----------------------------- Evaluate options --------------------------
|
|---|
| 467 | const string uri = conf.Get<string>("uri");
|
|---|
| 468 | const string out = conf.Get<string>("out");
|
|---|
| 469 | const string file = conf.Get<string>("file");
|
|---|
| 470 | const string tree = conf.Get<string>("tree");
|
|---|
| 471 | const bool force = conf.Get<bool>("force");
|
|---|
| 472 | const bool ignorenull = conf.Get<bool>("ignore-null");
|
|---|
| 473 | const bool update = conf.Get<bool>("update");
|
|---|
| 474 | const bool display = conf.Get<bool>("display");
|
|---|
| 475 | const string write = conf.Get<string>("write");
|
|---|
| 476 | const bool noout = conf.Get<bool>("null");
|
|---|
| 477 | const bool nofill = conf.Get<bool>("no-fill");
|
|---|
| 478 | const bool explain = conf.Get<bool>("explain");
|
|---|
| 479 | const bool profiling = conf.Get<bool>("profiling");
|
|---|
| 480 | const uint16_t verbose = conf.Get<uint16_t>("verbose");
|
|---|
| 481 | const uint16_t compression = conf.Get<uint16_t>("compression");
|
|---|
| 482 | const string delimiter = conf.Get<string>("delimiter");
|
|---|
| 483 | const vector<string> _ignore = conf.Vec<string>("ignore");
|
|---|
| 484 | const bool print_connection = conf.Get<bool>("print-connection");
|
|---|
| 485 | //const vector<Map> mymap = conf.Vec<Map>("map");
|
|---|
| 486 |
|
|---|
| 487 | // -------------------------------------------------------------------------
|
|---|
| 488 |
|
|---|
| 489 | const auto vars = conf.GetWildcardOptions("var.*");
|
|---|
| 490 |
|
|---|
| 491 | vector<string> variables;
|
|---|
| 492 | for (const auto &var : vars)
|
|---|
| 493 | variables.emplace_back('@'+var.substr(4)+":="+Tools::Trim(conf.Get<string>(var)));
|
|---|
| 494 |
|
|---|
| 495 | // -------------------------------------------------------------------------
|
|---|
| 496 |
|
|---|
| 497 | if (verbose>0)
|
|---|
| 498 | cout << "\n------------------------ Rootify SQL -------------------------" << endl;
|
|---|
| 499 |
|
|---|
| 500 | string query = conf.Get<string>("query");
|
|---|
| 501 | if (query.empty())
|
|---|
| 502 | {
|
|---|
| 503 | if (verbose>0)
|
|---|
| 504 | cout << "Reading query from file '" << file << "'." << endl;
|
|---|
| 505 |
|
|---|
| 506 | ifstream fin(file);
|
|---|
| 507 | if (!fin)
|
|---|
| 508 | {
|
|---|
| 509 | cerr << "Could not open query in '" << file << "': " << strerror(errno) << endl;
|
|---|
| 510 | return 4;
|
|---|
| 511 | }
|
|---|
| 512 | getline(fin, query, (char)fin.eof());
|
|---|
| 513 | }
|
|---|
| 514 |
|
|---|
| 515 | if (query.empty())
|
|---|
| 516 | {
|
|---|
| 517 | cerr << "No query specified." << endl;
|
|---|
| 518 | return 5;
|
|---|
| 519 | }
|
|---|
| 520 |
|
|---|
| 521 | // -------------------------------------------------------------------------
|
|---|
| 522 |
|
|---|
| 523 | map<string, vector<string>> envs;
|
|---|
| 524 |
|
|---|
| 525 | for (const auto &env : conf.GetWildcardOptions("env.*"))
|
|---|
| 526 | envs[env.substr(4)] = conf.Vec<string>(env);
|
|---|
| 527 |
|
|---|
| 528 | for (const auto &env : conf.GetWildcardOptions("list.*"))
|
|---|
| 529 | {
|
|---|
| 530 | const string fname = conf.Get<string>(env);
|
|---|
| 531 | const string &ident = env.substr(5);
|
|---|
| 532 |
|
|---|
| 533 | ifstream fin(fname);
|
|---|
| 534 | if (!fin)
|
|---|
| 535 | {
|
|---|
| 536 | cerr << "Could not open environment in '" << fname << "' for ${" << ident << "}: " << strerror(errno) << endl;
|
|---|
| 537 | return 6;
|
|---|
| 538 | }
|
|---|
| 539 |
|
|---|
| 540 | for (string line; getline(fin, line); )
|
|---|
| 541 | {
|
|---|
| 542 | const auto &l = Tools::Trim(line);
|
|---|
| 543 | if (!l.empty() && l[0]!='#')
|
|---|
| 544 | envs[ident].push_back(line);
|
|---|
| 545 | }
|
|---|
| 546 |
|
|---|
| 547 | if (verbose>0)
|
|---|
| 548 | cout << "Found " << envs[ident].size() << " list element(s) for ${" << ident << "}" << endl;
|
|---|
| 549 | }
|
|---|
| 550 |
|
|---|
| 551 | for (const auto &env : envs)
|
|---|
| 552 | {
|
|---|
| 553 | regex rexpr("\\$(\\{"+env.first+"\\}|"+env.first+"\\b)");
|
|---|
| 554 | query = regex_replace(query, rexpr, boost::join(env.second, ", "));
|
|---|
| 555 | }
|
|---|
| 556 |
|
|---|
| 557 | // -------------------------- Check for file permssion ---------------------
|
|---|
| 558 | // Strictly speaking, checking for write permission and existance is not necessary,
|
|---|
| 559 | // but it is convenient that the user does not find out that it failed after
|
|---|
| 560 | // waiting long for the query result
|
|---|
| 561 | //
|
|---|
| 562 | // I am using root here instead of boost to be
|
|---|
| 563 | // consistent with the access pattern by TFile
|
|---|
| 564 | TString path(noout?"/dev/null":out.c_str());
|
|---|
| 565 | gSystem->ExpandPathName(path);
|
|---|
| 566 |
|
|---|
| 567 | if (!noout)
|
|---|
| 568 | {
|
|---|
| 569 | FileStat_t stat;
|
|---|
| 570 | const Int_t exist = !gSystem->GetPathInfo(path, stat);
|
|---|
| 571 | const Bool_t _write = !gSystem->AccessPathName(path, kWritePermission) && R_ISREG(stat.fMode);
|
|---|
| 572 |
|
|---|
| 573 | if ((update && !exist) || (update && exist && !_write) || (force && exist && !_write))
|
|---|
| 574 | {
|
|---|
| 575 | cerr << "File '" << path << "' is not writable." << endl;
|
|---|
| 576 | return 7;
|
|---|
| 577 | }
|
|---|
| 578 |
|
|---|
| 579 | if (!update && !force && exist)
|
|---|
| 580 | {
|
|---|
| 581 | cerr << "File '" << path << "' already exists." << endl;
|
|---|
| 582 | return 8;
|
|---|
| 583 | }
|
|---|
| 584 | }
|
|---|
| 585 |
|
|---|
| 586 | Time start2;
|
|---|
| 587 |
|
|---|
| 588 | // --------------------------- Connect to database -------------------------------------------------
|
|---|
| 589 |
|
|---|
| 590 | if (query.back()!='\n')
|
|---|
| 591 | query += '\n';
|
|---|
| 592 |
|
|---|
| 593 | if (verbose>0)
|
|---|
| 594 | cout << "Connecting to database..." << endl;
|
|---|
| 595 |
|
|---|
| 596 |
|
|---|
| 597 | Database connection(uri); // Keep alive while fetching rows
|
|---|
| 598 |
|
|---|
| 599 | if (verbose>0)
|
|---|
| 600 | {
|
|---|
| 601 | cout << "Client Version: " << connection.client_version() << '\n';
|
|---|
| 602 | cout << "Server Version: " << connection.server_version() << endl;
|
|---|
| 603 | }
|
|---|
| 604 |
|
|---|
| 605 | if (print_connection)
|
|---|
| 606 | {
|
|---|
| 607 | try
|
|---|
| 608 | {
|
|---|
| 609 | const auto &res1 = connection.query("SHOW STATUS LIKE 'Compression'").store();
|
|---|
| 610 | cout << "Compression of database connection is " << string(res1[0][1]) << endl;
|
|---|
| 611 |
|
|---|
| 612 | const auto &res2 = connection.query("SHOW STATUS LIKE 'Ssl_cipher'").store();
|
|---|
| 613 | cout << "Connection to databases is " << (string(res2[0][1]).empty()?"UNENCRYPTED":"ENCRYPTED ("+string(res2[0][1])+")") << endl;
|
|---|
| 614 | }
|
|---|
| 615 | catch (const exception &e)
|
|---|
| 616 | {
|
|---|
| 617 | cerr << "\nSHOW STATUS LIKE 'Compression'\n\n";
|
|---|
| 618 | cerr << "SQL query failed:\n" << e.what() << endl;
|
|---|
| 619 | return 9;
|
|---|
| 620 | }
|
|---|
| 621 | }
|
|---|
| 622 |
|
|---|
| 623 | try
|
|---|
| 624 | {
|
|---|
| 625 | if (profiling)
|
|---|
| 626 | connection.query("SET PROFILING=1").execute();
|
|---|
| 627 | }
|
|---|
| 628 | catch (const exception &e)
|
|---|
| 629 | {
|
|---|
| 630 | cerr << "\nSET profiling=1\n\n";
|
|---|
| 631 | cerr << "SQL query failed:\n" << e.what() << endl;
|
|---|
| 632 | return 10;
|
|---|
| 633 | }
|
|---|
| 634 |
|
|---|
| 635 | // -------------------------- Set user defined variables -------------------
|
|---|
| 636 | if (variables.size()>0)
|
|---|
| 637 | {
|
|---|
| 638 | if (verbose>0)
|
|---|
| 639 | cout << "Setting user defined variables..." << endl;
|
|---|
| 640 |
|
|---|
| 641 | const string varset =
|
|---|
| 642 | "SET\n "+boost::algorithm::join(variables, ",\n ");
|
|---|
| 643 |
|
|---|
| 644 | try
|
|---|
| 645 | {
|
|---|
| 646 | connection.query(varset).execute();
|
|---|
| 647 | }
|
|---|
| 648 | catch (const exception &e)
|
|---|
| 649 | {
|
|---|
| 650 | cerr << '\n' << varset << "\n\n";
|
|---|
| 651 | cerr << "SQL query failed:\n" << e.what() << endl;
|
|---|
| 652 | return 11;
|
|---|
| 653 | }
|
|---|
| 654 |
|
|---|
| 655 | if (verbose>2)
|
|---|
| 656 | cout << '\n' << varset << '\n' << endl;
|
|---|
| 657 | }
|
|---|
| 658 |
|
|---|
| 659 | // ------------------------- Explain query if requested --------------------
|
|---|
| 660 |
|
|---|
| 661 | if (explain)
|
|---|
| 662 | {
|
|---|
| 663 | try
|
|---|
| 664 | {
|
|---|
| 665 | const auto res0 =
|
|---|
| 666 | connection.query("EXPLAIN FORMAT=JSON "+query).store();
|
|---|
| 667 |
|
|---|
| 668 | cout << res0[0][0] << endl;
|
|---|
| 669 | cout << endl;
|
|---|
| 670 |
|
|---|
| 671 | const mysqlpp::StoreQueryResult res1 =
|
|---|
| 672 | connection.query("EXPLAIN "+query).store();
|
|---|
| 673 |
|
|---|
| 674 | for (size_t i=0; i<res1.num_rows(); i++)
|
|---|
| 675 | {
|
|---|
| 676 | const mysqlpp::Row &row = res1[i];
|
|---|
| 677 |
|
|---|
| 678 | cout << "\nid : " << row["id"];
|
|---|
| 679 | cout << "\nselect type : " << row["select_type"];
|
|---|
| 680 |
|
|---|
| 681 | if (!row["table"].is_null())
|
|---|
| 682 | cout << "\ntable : " << row["table"];
|
|---|
| 683 |
|
|---|
| 684 | if (!row["partitions"].is_null())
|
|---|
| 685 | cout << "\npartitions : " << row["partitions"];
|
|---|
| 686 |
|
|---|
| 687 | if (!row["key"].is_null())
|
|---|
| 688 | cout << "\nselected key : " << row["key"] << " [len=" << row["key_len"] << "] out of (" << row["possible_keys"] << ")";
|
|---|
| 689 |
|
|---|
| 690 | if (!row["type"].is_null())
|
|---|
| 691 | cout << "\njoin type : " << row["type"];
|
|---|
| 692 |
|
|---|
| 693 | //if (!row["possible_keys"].is_null())
|
|---|
| 694 | // cout << "\npossible_keys: " << row["possible_keys"];
|
|---|
| 695 |
|
|---|
| 696 | //if (!row["key_len"].is_null())
|
|---|
| 697 | // cout << "\nkey_len : " << row["key_len"];
|
|---|
| 698 |
|
|---|
| 699 | if (!row["ref"].is_null())
|
|---|
| 700 | cout << "\nref : (" << row["ref"] << ") compared to the index";
|
|---|
| 701 |
|
|---|
| 702 | if (!row["rows"].is_null())
|
|---|
| 703 | cout << "\nrows : " << row["rows"];
|
|---|
| 704 |
|
|---|
| 705 | if (!row["filtered"].is_null())
|
|---|
| 706 | cout << "\nfiltered : " << row["filtered"];
|
|---|
| 707 |
|
|---|
| 708 | if (!row["extra"].is_null())
|
|---|
| 709 | cout << "\nExtra : " << row["extra"];
|
|---|
| 710 |
|
|---|
| 711 | cout << endl;
|
|---|
| 712 | }
|
|---|
| 713 |
|
|---|
| 714 | cout << endl;
|
|---|
| 715 |
|
|---|
| 716 | const mysqlpp::StoreQueryResult res2 =
|
|---|
| 717 | connection.query("SHOW WARNINGS").store();
|
|---|
| 718 |
|
|---|
| 719 | for (size_t i=0; i<res2.num_rows(); i++)
|
|---|
| 720 | {
|
|---|
| 721 | const mysqlpp::Row &row = res2[i];
|
|---|
| 722 |
|
|---|
| 723 | // 1003 //
|
|---|
| 724 | cout << row["Level"] << '[' << row["Code"] << "]:\n";
|
|---|
| 725 | if (uint32_t(row["Code"])==1003)
|
|---|
| 726 | format(row["Message"].c_str());
|
|---|
| 727 | else
|
|---|
| 728 | cout << row["Message"] << '\n' << endl;
|
|---|
| 729 |
|
|---|
| 730 | }
|
|---|
| 731 |
|
|---|
| 732 | }
|
|---|
| 733 | catch (const exception &e)
|
|---|
| 734 | {
|
|---|
| 735 | cerr << '\n' << query << "\n\n";
|
|---|
| 736 | cerr << "SQL query failed:\n" << e.what() << endl;
|
|---|
| 737 | return 12;
|
|---|
| 738 | }
|
|---|
| 739 |
|
|---|
| 740 | return 0;
|
|---|
| 741 | }
|
|---|
| 742 |
|
|---|
| 743 | // -------------------------- Request data from database -------------------
|
|---|
| 744 | if (verbose>0)
|
|---|
| 745 | cout << "Requesting data..." << endl;
|
|---|
| 746 |
|
|---|
| 747 | if (verbose>2)
|
|---|
| 748 | cout << '\n' << query << endl;
|
|---|
| 749 |
|
|---|
| 750 | const mysqlpp::UseQueryResult res =
|
|---|
| 751 | connection.query(query).use();
|
|---|
| 752 |
|
|---|
| 753 | // -------------------------------------------------------------------------
|
|---|
| 754 |
|
|---|
| 755 | if (verbose>0)
|
|---|
| 756 | {
|
|---|
| 757 | cout << "Opening file '" << path << "' [compression=" << compression << "]...\n";
|
|---|
| 758 | cout << "Writing data to tree '" << tree << "'" << endl;
|
|---|
| 759 | }
|
|---|
| 760 |
|
|---|
| 761 | // ----------------------------- Open output file --------------------------
|
|---|
| 762 | TFile tfile(path, update?"UPDATE":(force?"RECREATE":"CREATE"), "Rootify SQL", compression);
|
|---|
| 763 | if (tfile.IsZombie())
|
|---|
| 764 | return 13;
|
|---|
| 765 |
|
|---|
| 766 | // -------------------------------------------------------------------------
|
|---|
| 767 |
|
|---|
| 768 | // get the first row to get the field description
|
|---|
| 769 | mysqlpp::Row row = res.fetch_row();
|
|---|
| 770 | if (!row)
|
|---|
| 771 | {
|
|---|
| 772 | cerr << "Empty set returned... nothing to write." << endl;
|
|---|
| 773 | finish(connection, verbose, profiling, print_connection);
|
|---|
| 774 | return 14;
|
|---|
| 775 | }
|
|---|
| 776 |
|
|---|
| 777 | if (verbose>0)
|
|---|
| 778 | cout << "Trying to setup " << row.size() << " branches..." << endl;
|
|---|
| 779 |
|
|---|
| 780 | if (verbose>1)
|
|---|
| 781 | cout << endl;
|
|---|
| 782 |
|
|---|
| 783 | const mysqlpp::FieldNames &l = *row.field_list().list;
|
|---|
| 784 |
|
|---|
| 785 | vector<double> buf(l.size());
|
|---|
| 786 | vector<uint8_t> typ(l.size(),'n'); // n=number [double], d is used for DateTime
|
|---|
| 787 |
|
|---|
| 788 | UInt_t cols = 0;
|
|---|
| 789 |
|
|---|
| 790 |
|
|---|
| 791 | // -------------------- Configure branches of TTree ------------------------
|
|---|
| 792 | TTree *ttree = new TTree(tree.c_str(), query.c_str());
|
|---|
| 793 |
|
|---|
| 794 | size_t skipat = 0;
|
|---|
| 795 | size_t skipreg = 0;
|
|---|
| 796 | for (size_t i=0; i<l.size(); i++)
|
|---|
| 797 | {
|
|---|
| 798 | const string t = row[i].type().sql_name();
|
|---|
| 799 |
|
|---|
| 800 | if (t.find("DATETIME")!=string::npos)
|
|---|
| 801 | typ[i] = 'd';
|
|---|
| 802 | else
|
|---|
| 803 | if (t.find("DATE")!=string::npos)
|
|---|
| 804 | typ[i] = 'D';
|
|---|
| 805 | else
|
|---|
| 806 | if (t.find("TIME")!=string::npos)
|
|---|
| 807 | typ[i] = 'T';
|
|---|
| 808 | else
|
|---|
| 809 | if (t.find("VARCHAR")!=string::npos)
|
|---|
| 810 | typ[i] = 'V';
|
|---|
| 811 | else
|
|---|
| 812 | if (t.find("CHAR")!=string::npos)
|
|---|
| 813 | typ[i] = 'C';
|
|---|
| 814 |
|
|---|
| 815 | bool found = false;
|
|---|
| 816 | for (const auto &pattern: _ignore)
|
|---|
| 817 | {
|
|---|
| 818 | if (regex_match(l[i], regex(pattern)))
|
|---|
| 819 | {
|
|---|
| 820 | found = true;
|
|---|
| 821 | typ[i] = '-';
|
|---|
| 822 | skipreg++;
|
|---|
| 823 | break;
|
|---|
| 824 | }
|
|---|
| 825 | }
|
|---|
| 826 |
|
|---|
| 827 | if (l[i][0]=='@')
|
|---|
| 828 | {
|
|---|
| 829 | typ[i] = '@';
|
|---|
| 830 | skipat++;
|
|---|
| 831 | }
|
|---|
| 832 |
|
|---|
| 833 | const bool use = l[i][0]!='@' && typ[i]!='V' && typ[i]!='C' && !found;
|
|---|
| 834 |
|
|---|
| 835 | if (verbose>1)
|
|---|
| 836 | cout << (use?" + ":" - ") << l[i].c_str() << " [" << t << "] {" << typ[i] << "}\n";
|
|---|
| 837 |
|
|---|
| 838 | if (use)
|
|---|
| 839 | {
|
|---|
| 840 | // string name = l[i];
|
|---|
| 841 | // for (const auto &m: mymap)
|
|---|
| 842 | // name = boost::regex_replace(l[i], boost::regex(m.first), m.second);
|
|---|
| 843 |
|
|---|
| 844 | ttree->Branch(l[i].c_str(), buf.data()+i);
|
|---|
| 845 | cols++;
|
|---|
| 846 | }
|
|---|
| 847 | }
|
|---|
| 848 | // -------------------------------------------------------------------------
|
|---|
| 849 |
|
|---|
| 850 | if (verbose>1)
|
|---|
| 851 | cout << endl;
|
|---|
| 852 | if (verbose>0)
|
|---|
| 853 | {
|
|---|
| 854 | if (skipreg)
|
|---|
| 855 | cout << skipreg << " branches skipped due to ignore list." << endl;
|
|---|
| 856 | if (skipat)
|
|---|
| 857 | cout << skipat << " branches skipped due to name starting with @." << endl;
|
|---|
| 858 | cout << "Configured " << cols << " branches.\nFilling branches..." << endl;
|
|---|
| 859 | }
|
|---|
| 860 |
|
|---|
| 861 | ofstream fout(write);
|
|---|
| 862 | if (!write.empty() && !fout)
|
|---|
| 863 | cout << "WARNING: Writing to '" << write << "' failed: " << strerror(errno) << endl;
|
|---|
| 864 |
|
|---|
| 865 | if (display)
|
|---|
| 866 | {
|
|---|
| 867 | cout << endl;
|
|---|
| 868 | cout << "#";
|
|---|
| 869 | for (size_t i=0; i<l.size(); i++)
|
|---|
| 870 | cout << ' ' << l[i].c_str();
|
|---|
| 871 | cout << endl;
|
|---|
| 872 | }
|
|---|
| 873 |
|
|---|
| 874 | if (!write.empty())
|
|---|
| 875 | {
|
|---|
| 876 | fout << "#";
|
|---|
| 877 | for (size_t i=0; i<l.size(); i++)
|
|---|
| 878 | fout << ' ' << l[i].c_str();
|
|---|
| 879 | fout << endl;
|
|---|
| 880 | }
|
|---|
| 881 |
|
|---|
| 882 | // ---------------------- Fill TTree with DB data --------------------------
|
|---|
| 883 | size_t count = 0;
|
|---|
| 884 | size_t skip = 0;
|
|---|
| 885 | do
|
|---|
| 886 | {
|
|---|
| 887 | count++;
|
|---|
| 888 |
|
|---|
| 889 | ostringstream sout;
|
|---|
| 890 |
|
|---|
| 891 | size_t idx=0;
|
|---|
| 892 | for (auto col=row.begin(); col!=row.end(); col++, idx++)
|
|---|
| 893 | {
|
|---|
| 894 | if (display || !write.empty())
|
|---|
| 895 | {
|
|---|
| 896 | if (idx>0)
|
|---|
| 897 | sout << (delimiter.empty()?"\t":delimiter);
|
|---|
| 898 | sout << col->c_str();
|
|---|
| 899 | }
|
|---|
| 900 |
|
|---|
| 901 | if (!ignorenull && col->is_null())
|
|---|
| 902 | {
|
|---|
| 903 | skip++;
|
|---|
| 904 | break;
|
|---|
| 905 | }
|
|---|
| 906 |
|
|---|
| 907 | switch (typ[idx])
|
|---|
| 908 | {
|
|---|
| 909 | case 'd':
|
|---|
| 910 | buf[idx] = time_t((mysqlpp::DateTime)(*col));
|
|---|
| 911 | break;
|
|---|
| 912 |
|
|---|
| 913 | case 'D':
|
|---|
| 914 | buf[idx] = time_t((mysqlpp::Date)(*col));
|
|---|
| 915 | break;
|
|---|
| 916 |
|
|---|
| 917 | case 'T':
|
|---|
| 918 | buf[idx] = time_t((mysqlpp::Time)(*col));
|
|---|
| 919 | break;
|
|---|
| 920 |
|
|---|
| 921 | case 'V':
|
|---|
| 922 | case 'C':
|
|---|
| 923 | case '-':
|
|---|
| 924 | case '@':
|
|---|
| 925 | break;
|
|---|
| 926 |
|
|---|
| 927 | default:
|
|---|
| 928 | buf[idx] = atof(col->c_str());
|
|---|
| 929 | }
|
|---|
| 930 | }
|
|---|
| 931 |
|
|---|
| 932 | if (idx==row.size())
|
|---|
| 933 | {
|
|---|
| 934 | if (!nofill)
|
|---|
| 935 | ttree->Fill();
|
|---|
| 936 |
|
|---|
| 937 | if (display)
|
|---|
| 938 | cout << sout.str() << endl;
|
|---|
| 939 | if (!write.empty())
|
|---|
| 940 | fout << sout.str() << '\n';
|
|---|
| 941 | }
|
|---|
| 942 |
|
|---|
| 943 | row = res.fetch_row();
|
|---|
| 944 |
|
|---|
| 945 |
|
|---|
| 946 | } while (row);
|
|---|
| 947 |
|
|---|
| 948 | // -------------------------------------------------------------------------
|
|---|
| 949 |
|
|---|
| 950 | if (display)
|
|---|
| 951 | cout << '\n' << endl;
|
|---|
| 952 |
|
|---|
| 953 | if (verbose>0)
|
|---|
| 954 | {
|
|---|
| 955 | cout << count << " rows fetched." << endl;
|
|---|
| 956 | if (skip>0)
|
|---|
| 957 | cout << skip << " rows skipped due to NULL field." << endl;
|
|---|
| 958 |
|
|---|
| 959 | cout << ttree->GetEntries() << " rows filled into tree." << endl;
|
|---|
| 960 | }
|
|---|
| 961 |
|
|---|
| 962 | ttree->Write();
|
|---|
| 963 | tfile.Close();
|
|---|
| 964 |
|
|---|
| 965 | if (verbose>0)
|
|---|
| 966 | {
|
|---|
| 967 | const auto sec = Time().UnixTime()-start.UnixTime();
|
|---|
| 968 |
|
|---|
| 969 | cout << Tools::Scientific(tfile.GetSize()) << "B written to disk.\n";
|
|---|
| 970 | cout << "File closed.\n";
|
|---|
| 971 | cout << "Execution time: " << sec << "s ";
|
|---|
| 972 | cout << "(" << Tools::Fractional(sec/count) << "s/row)\n";
|
|---|
| 973 | cout << "--------------------------------------------------------------" << endl;
|
|---|
| 974 | }
|
|---|
| 975 |
|
|---|
| 976 | return finish(connection, verbose, profiling, print_connection);
|
|---|
| 977 | }
|
|---|
