Context Navigation

source: trunk/FACT++/src/rootifysql.cc@ 19102

Visit:

Last change on this file since 19102 was 19100, checked in by tbretz, 6 years ago
Warnings are displayed if they happen.
File size: 28.2 KB

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

Note: See TracBrowser for help on using the repository browser.

Download in other formats: