Context Navigation

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

Visit:

Last change on this file since 19177 was 19162, checked in by tbretz, 6 years ago
Updated returns.
File size: 32.1 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("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	return finish(connection, verbose, profiling, print_connection)+20;
774	}
775
776	if (verbose>0)
777	cout << "Trying to setup " << row.size() << " branches..." << endl;
778
779	if (verbose>1)
780	cout << endl;
781
782	const mysqlpp::FieldNames &l = *row.field_list().list;
783
784	vector<double> buf(l.size());
785	vector<uint8_t> typ(l.size(),'n'); // n=number [double], d is used for DateTime
786
787	UInt_t cols = 0;
788
789
790	// -------------------- Configure branches of TTree ------------------------
791	TTree *ttree = new TTree(tree.c_str(), query.c_str());
792
793	size_t skipat = 0;
794	size_t skipreg = 0;
795	for (size_t i=0; i<l.size(); i++)
796	{
797	const string t = row[i].type().sql_name();
798
799	if (t.find("DATETIME")!=string::npos)
800	typ[i] = 'd';
801	else
802	if (t.find("DATE")!=string::npos)
803	typ[i] = 'D';
804	else
805	if (t.find("TIME")!=string::npos)
806	typ[i] = 'T';
807	else
808	if (t.find("VARCHAR")!=string::npos)
809	typ[i] = 'V';
810	else
811	if (t.find("CHAR")!=string::npos)
812	typ[i] = 'C';
813
814	bool found = false;
815	for (const auto &pattern: _ignore)
816	{
817	if (regex_match(l[i], regex(pattern)))
818	{
819	found = true;
820	typ[i] = '-';
821	skipreg++;
822	break;
823	}
824	}
825
826	if (l[i][0]=='@')
827	{
828	typ[i] = '@';
829	skipat++;
830	}
831
832	const bool use = l[i][0]!='@' && typ[i]!='V' && typ[i]!='C' && !found;
833
834	if (verbose>1)
835	cout << (use?" + ":" - ") << l[i].c_str() << " [" << t << "] {" << typ[i] << "}\n";
836
837	if (use)
838	{
839	// string name = l[i];
840	// for (const auto &m: mymap)
841	// name = boost::regex_replace(l[i], boost::regex(m.first), m.second);
842
843	ttree->Branch(l[i].c_str(), buf.data()+i);
844	cols++;
845	}
846	}
847	// -------------------------------------------------------------------------
848
849	if (verbose>1)
850	cout << endl;
851	if (verbose>0)
852	{
853	if (skipreg)
854	cout << skipreg << " branches skipped due to ignore list." << endl;
855	if (skipat)
856	cout << skipat << " branches skipped due to name starting with @." << endl;
857	cout << "Configured " << cols << " branches.\nFilling branches..." << endl;
858	}
859
860	ofstream fout(write);
861	if (!write.empty() && !fout)
862	cout << "WARNING: Writing to '" << write << "' failed: " << strerror(errno) << endl;
863
864	if (display)
865	{
866	cout << endl;
867	cout << "#";
868	for (size_t i=0; i<l.size(); i++)
869	cout << ' ' << l[i].c_str();
870	cout << endl;
871	}
872
873	if (!write.empty())
874	{
875	fout << "#";
876	for (size_t i=0; i<l.size(); i++)
877	fout << ' ' << l[i].c_str();
878	fout << endl;
879	}
880
881	// ---------------------- Fill TTree with DB data --------------------------
882	size_t count = 0;
883	size_t skip = 0;
884	do
885	{
886	count++;
887
888	ostringstream sout;
889
890	size_t idx=0;
891	for (auto col=row.begin(); col!=row.end(); col++, idx++)
892	{
893	if (display \|\| !write.empty())
894	{
895	if (idx>0)
896	sout << (delimiter.empty()?"\t":delimiter);
897	sout << col->c_str();
898	}
899
900	if (!ignorenull && col->is_null())
901	{
902	skip++;
903	break;
904	}
905
906	switch (typ[idx])
907	{
908	case 'd':
909	buf[idx] = time_t((mysqlpp::DateTime)(*col));
910	break;
911
912	case 'D':
913	buf[idx] = time_t((mysqlpp::Date)(*col));
914	break;
915
916	case 'T':
917	buf[idx] = time_t((mysqlpp::Time)(*col));
918	break;
919
920	case 'V':
921	case 'C':
922	case '-':
923	case '@':
924	break;
925
926	default:
927	buf[idx] = atof(col->c_str());
928	}
929	}
930
931	if (idx==row.size())
932	{
933	if (!nofill)
934	ttree->Fill();
935
936	if (display)
937	cout << sout.str() << endl;
938	if (!write.empty())
939	fout << sout.str() << '\n';
940	}
941
942	row = res.fetch_row();
943
944
945	} while (row);
946
947	// -------------------------------------------------------------------------
948
949	if (display)
950	cout << '\n' << endl;
951
952	if (verbose>0)
953	{
954	cout << count << " rows fetched." << endl;
955	if (skip>0)
956	cout << skip << " rows skipped due to NULL field." << endl;
957
958	cout << ttree->GetEntries() << " rows filled into tree." << endl;
959	}
960
961	ttree->Write();
962	tfile.Close();
963
964	if (verbose>0)
965	{
966	const auto sec = Time().UnixTime()-start.UnixTime();
967
968	cout << Tools::Scientific(tfile.GetSize()) << "B written to disk.\n";
969	cout << "File closed.\n";
970	cout << "Execution time: " << sec << "s ";
971	cout << "(" << Tools::Fractional(sec/count) << "s/row)\n";
972	cout << "--------------------------------------------------------------" << endl;
973	}
974
975	return finish(connection, verbose, profiling, print_connection);
976	}

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

Download in other formats: