Changeset 19091

Timestamp:

07/28/18 19:25:27 (6 years ago)

Author:

tbretz

Message:

Added some code for profiling and explaining the query. The EXPLAIN code is still under development and not yet ready.

File:

• trunk/FACT++/src/rootifysql.cc (modified) (12 diffs)

trunk/FACT++/src/rootifysql.cc

@@ -4 +4 @@
 
 #include <boost/algorithm/string/join.hpp>
+#include <boost/tokenizer.hpp>
+#include <boost/algorithm/string.hpp>
 
 #include "tools.h"
@@ -37 +39 @@
         ("delimiter",     var<string>(""),             "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<string>(),               "Predefined SQL user variables (@VAR)")
         ("env.*",         vars<string>(),              "Predefined environment for substitutions in the query ($ENV)")
@@ -98 +101 @@
         ;
     cout << endl;
+}
+
+void print(string sql)
+{
+
+}
+
+// Remove queries...
+void format(string sql)
+{
+    regex reg1("`[^`]*`");
+
+    vector<string> 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<string> 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<string> 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 +369 @@
     const bool     nofill      = conf.Get<bool>("no-fill");
     const bool     explain     = conf.Get<bool>("explain");
+    const bool     profiling   = conf.Get<bool>("profiling");
     const uint16_t verbose     = conf.Get<uint16_t>("verbose");
     const uint16_t compression = conf.Get<uint16_t>("compression");
@@ -222 +467 @@
     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 +494 @@
         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 +560 @@
             cout << endl;
 
-            return 0;
-
             const mysqlpp::StoreQueryResult res2 =
                 connection.query("SHOW WARNINGS").store();
@@ -316 +571 @@
                 cout << row["Message"] << '\n' << endl;
 
+                if (uint32_t(row["Code"])==1003)
+                    format(row["Message"].c_str());
+
             }
 
@@ -321 +579 @@
         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 +605 @@
     TFile tfile(path, update?"UPDATE":(force?"RECREATE":"CREATE"), "Rootify SQL", compression);
     if (tfile.IsZombie())
-        return 7;
+        return 9;
 
     // -------------------------------------------------------------------------
@@ -356 +614 @@
     {
         cerr << "Empty set returned... nothing to write." << endl;
-        return 8;
+        return 10;
     }
 
@@ -540 +798 @@
     }
 
+    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<resp.num_rows(); i++)
+            {
+                const mysqlpp::Row &rowp = resp[i];
+
+                cout << left;
+                cout << setw(20) << rowp["Status"] << ' ';
+                cout << right;
+                cout << setw(11) << rowp["Duration"] << ' ';
+                cout << setw(11) << rowp["CPU_user"] << ' ';
+                cout << setw(11) << rowp["CPU_system"] << '\n';
+            }
+            cout << "--------------------------------------------------------\n";
+            cout << endl;
+        }
+        catch (const exception &e)
+        {
+            cerr << "\nSHOW PROFILE ALL\n\n";
+            cerr << "SQL query failed:\n" << e.what() << endl;
+            return 11;
+        }
+    }
+
     return 0;
 }