Index: trunk/FACT++/src/rootifysql.cc =================================================================== --- trunk/FACT++/src/rootifysql.cc (revision 19090) +++ trunk/FACT++/src/rootifysql.cc (revision 19091) @@ -4,4 +4,6 @@ #include +#include +#include #include "tools.h" @@ -37,4 +39,5 @@ ("delimiter", var(""), "The delimiter used if contents are displayed with --display (default=\\t)") ("explain", po_switch(), "Requests an EXPLAIN from the server (shows the serveroptimized query)") + ("profiling", po_switch(), "Turn on profiling and print profile") ("var.*", var(), "Predefined SQL user variables (@VAR)") ("env.*", vars(), "Predefined environment for substitutions in the query ($ENV)") @@ -98,4 +101,245 @@ ; cout << endl; +} + +void print(string sql) +{ + +} + +// Remove queries... +void format(string sql) +{ + regex reg1("`[^`]*`"); + + vector vec; + + cout << "[" << endl; + smatch match; + while (regex_search(sql, match, reg1, regex_constants::format_first_only)) + { + const auto &len = match.length(0); + const auto &pos = match.position(0); + const auto &str = match.str(0); + + + const auto it = find(vec.cbegin(), vec.cend(), str); + const size_t id = it==vec.cend() ? vec.size() : it-vec.cbegin(); + + sql.replace(pos, len, "{"+to_string(id)+"}"); + + if (it==vec.cend()) + { + cout << match.size() << "|"; + cout << id << "|" << len << "|" << pos << "|" << str << endl; + + vec.push_back(str);//.substr(1, str.size()-2)); + } + } + + + cout << "]" << endl; + + cout << endl << sql << endl; + + + regex reg2("\\([^\\(\\)]*\\)"); + + cout << "[" << endl; + while (regex_search(sql, match, reg2, regex_constants::format_first_only)) + { + const auto &len = match.length(0); + const auto &pos = match.position(0); + const auto &str = match.str(0); + + const auto it = find(vec.cbegin(), vec.cend(), str); + const size_t id = it==vec.cend() ? vec.size() : it-vec.cbegin(); + + sql.replace(pos, len, "{"+to_string(id)+"}"); + + if (it==vec.cend()) + { + cout << match.size() << "|"; + cout << id << "|" << len << "|" << pos << "|" << str << endl; + + vec.push_back(str);//.substr(1, str.size()-2)); + } + } + + cout << "]" << endl; + + cout << endl << sql << endl; + + + regex reg3a("\\{[0-9]+\\}(\\.\\{[0-9]+\\})+"); + + cout << "[" << endl; + while (regex_search(sql, match, reg3a, regex_constants::format_first_only)) + { + const auto &len = match.length(0); + const auto &pos = match.position(0); + const auto &str = match.str(0); + + const auto it = find(vec.cbegin(), vec.cend(), str); + const size_t id = it==vec.cend() ? vec.size() : it-vec.cbegin(); + + sql.replace(pos, len, "{"+to_string(id)+"}"); + + if (it==vec.cend()) + { + cout << match.size() << "|"; + cout << id << "|" << len << "|" << pos << "|" << str << endl; + + vec.push_back(str);//.substr(1, str.size()-2)); + } + } + + cout << "]" << endl; + + cout << endl << sql << endl; + + + regex reg3("\\{[0-9]+\\}+\\ AS\\ \\{[0-9]+\\}"); + + cout << "[" << endl; + while (regex_search(sql, match, reg3, regex_constants::format_first_only)) + { + const auto &len = match.length(0); + const auto &pos = match.position(0); + const auto &str = match.str(0); + + const auto it = find(vec.cbegin(), vec.cend(), str); + const size_t id = it==vec.cend() ? vec.size() : it-vec.cbegin(); + + sql.replace(pos, len, "{"+to_string(id)+"}"); + + if (it==vec.cend()) + { + cout << match.size() << "|"; + cout << id << "|" << len << "|" << pos << "|" << str << endl; + + vec.push_back(str);//.substr(1, str.size()-2)); + } + } + + cout << "]" << endl; + + cout << endl << sql << endl; + + regex reg4("(\\/\\*\\ [a-z]+\\#[0-9]+\\ \\*\\/)?\\ ([a-z ]+)\\ (\\{[0-9]+\\}(,\\{[0-9]+\\})*)"); + + while (regex_search(sql, match, reg4)) + { + //const auto &len = match.length(0); + //const auto &pos = match.position(0); + const auto &com = match.str(1); // Comment + const auto &tok = match.str(2); // Token + const auto &arg = match.str(3); // Argument + + if (!com.empty()) + cout << '\n' << com << '\n'; + + cout << tok << '\n'; + //cout << " " << arg << '\n'; + + vector exprs; + boost::split(exprs, arg, boost::is_any_of(",")); + for (auto &expr : exprs) + cout << " " << expr << " | " << vec[atoi(expr.c_str()+1)] << "," << endl; + + sql = sql.substr(match.position(0)+match.length(0)); + } + + cout << endl; + + + /* + + regex reg5("\\{[0-9]+\\}"); + + cout << "SELECT\n"; + + const string select = match.str(0); + + vector exprs; + boost::split(exprs, select, boost::is_any_of(",")); + for (auto &expr : exprs) + cout << " " << expr << " | " << vec[atoi(expr.c_str()+1)] << "," << endl; + } + + */ + +/* + + SELECT + [ALL | DISTINCT | DISTINCTROW ] + [HIGH_PRIORITY] + [STRAIGHT_JOIN] + [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] + [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] + select_expr [, select_expr ...] + [FROM table_references + [PARTITION partition_list] + [WHERE where_condition] + [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]] + [HAVING where_condition] + [WINDOW window_name AS (window_spec) + [, window_name AS (window_spec)] ...] + [ORDER BY {col_name | expr | position} + [ASC | DESC], ... [WITH ROLLUP]] + [LIMIT {[offset,] row_count | row_count OFFSET offset}] + [INTO OUTFILE 'file_name' + [CHARACTER SET charset_name] + export_options + | INTO DUMPFILE 'file_name' + | INTO var_name [, var_name]] + [FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] [NOWAIT | SKIP LOCKED] + | LOCK IN SHARE MODE]] + */ + + /* +table_references: + escaped_table_reference [, escaped_table_reference] ... + +escaped_table_reference: + table_reference + | { OJ table_reference } + +table_reference: + table_factor + | join_table + +table_factor: + tbl_name [PARTITION (partition_names)] + [[AS] alias] [index_hint_list] + | table_subquery [AS] alias [(col_list)] + | ( table_references ) + +join_table: + table_reference [INNER | CROSS] JOIN table_factor [join_condition] + | table_reference STRAIGHT_JOIN table_factor + | table_reference STRAIGHT_JOIN table_factor ON conditional_expr + | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition + | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor + +join_condition: + ON conditional_expr + | USING (column_list) + +index_hint_list: + index_hint [, index_hint] ... + +index_hint: + USE {INDEX|KEY} + [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list]) + | IGNORE {INDEX|KEY} + [FOR {JOIN|ORDER BY|GROUP BY}] (index_list) + | FORCE {INDEX|KEY} + [FOR {JOIN|ORDER BY|GROUP BY}] (index_list) + +index_list: + index_name [, index_name] ... + */ + } @@ -125,4 +369,5 @@ const bool nofill = conf.Get("no-fill"); const bool explain = conf.Get("explain"); + const bool profiling = conf.Get("profiling"); const uint16_t verbose = conf.Get("verbose"); const uint16_t compression = conf.Get("compression"); @@ -222,4 +467,16 @@ Database connection(uri); // Keep alive while fetching rows + try + { + if (profiling) + connection.query("SET PROFILING=1").execute(); + } + catch (const exception &e) + { + cerr << "\nSET profiling=1\n\n"; + cerr << "SQL query failed:\n" << e.what() << endl; + return 6; + } + // -------------------------- Set user defined variables ------------------- if (variables.size()>0) @@ -237,7 +494,7 @@ catch (const exception &e) { - cerr << varset << "\n\n"; + cerr << '\n' << varset << "\n\n"; cerr << "SQL query failed:\n" << e.what() << endl; - return 5; + return 7; } @@ -303,6 +560,4 @@ cout << endl; - return 0; - const mysqlpp::StoreQueryResult res2 = connection.query("SHOW WARNINGS").store(); @@ -316,4 +571,7 @@ cout << row["Message"] << '\n' << endl; + if (uint32_t(row["Code"])==1003) + format(row["Message"].c_str()); + } @@ -321,7 +579,7 @@ catch (const exception &e) { - cerr << query << "\n\n"; + cerr << '\n' << query << "\n\n"; cerr << "SQL query failed:\n" << e.what() << endl; - return 6; + return 8; } @@ -347,5 +605,5 @@ TFile tfile(path, update?"UPDATE":(force?"RECREATE":"CREATE"), "Rootify SQL", compression); if (tfile.IsZombie()) - return 7; + return 9; // ------------------------------------------------------------------------- @@ -356,5 +614,5 @@ { cerr << "Empty set returned... nothing to write." << endl; - return 8; + return 10; } @@ -540,4 +798,41 @@ } + if (profiling) + { + try + { + const auto resp = + connection.query("SHOW PROFILE ALL").store(); + + cout << '\n'; + cout << left; + cout << setw(20) << "Status" << ' '; + cout << right; + cout << setw(11) << "Duration" << ' '; + cout << setw(11) << "CPU User" << ' '; + cout << setw(11) << "CPU System" << '\n'; + cout << "--------------------------------------------------------\n"; + for (size_t i=0; i