Context Navigation

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

Visit:

Last change on this file since 19184 was 19183, checked in by tbretz, 8 years ago
This version compiles at ISDC, mainly replacing for-loops by loops, regex by boost::regex and smatch::position(int=0) by position() etc
File size: 32.5 KB

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

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

Download in other formats: