public CExplainableQuery(IQueryableConnection connection, StoredQuery query)
{
this.connection = connection;
this.query = query;
InitExplanation();
}
public void SetQuery(StoredQuery qry)
{
currentQuery = qry;
InitDescriptions();
InitParams();
}
public void CopyParamsFrom(StoredQuery qry)
{
foreach (var p in qry.Parameters)
{
this.Parameters[p.Key] = p.Value;
}
}
public Dictionary<string, string> ListFieldNames(string tablename)
{
var tblqry = new StoredQuery("pragma table_info(" + tablename + ");");
if (Query(tblqry))
{
var dt = ResultsAsDataTable();
if (dt != null)
{
var lst = new Dictionary<string, string>();
foreach (var row in dt.AsEnumerable())
{
lst.Add(row.Field<string>(1), row.Field<string>(2));
}
return lst;
}
CloseQuery();
}
return null;
}
public MSSQLQueryExplanation(IQueryableConnection connection, StoredQuery query)
: base(connection, query)
{
}
public static CExplainableQuery NewExplain(IQueryableConnection connection, StoredQuery query)
{
if (query.SQL.StartsWith("select", StringComparison.OrdinalIgnoreCase))
{
if (connection is MySQLQueryableConnection)
{
return new MySQLQueryExplanation(connection, query);
}
else if (connection is MSSQLQueryableConnection)
{
return null; // new MSSQLQueryExplanation(connection, query);
}
}
return null;
}
protected override void InitExplanation()
{
string sql = "EXPLAIN " + query.SQL;
explainQuery = new StoredQuery(sql);
explainQuery.CopyParamsFrom(query);
if (connection.Query(explainQuery))
{
try
{
results = connection.ResultsAsDataTable();
}
catch (Exception e)
{
error = e.Message;
results = null;
}
connection.CloseQuery();
}
}
private bool AskForParameters(StoredQuery qry)
{
// Check for query params
if (qry.HasParameters())
{
// Display query param window
var frm = new QueryParams();
frm.SetQuery(qry);
if (frm.ShowDialog() == true)
{
frm.SaveParamsToQuery();
return true;
}
}
else
{
// if there are no parameters, that's OK
return true;
}
return false;
}
private string ProcessParameters(StoredQuery qry)
{
string exampleqrystring = qry.ToString();
// Loop through query parameters
foreach (var param in qry.Parameters)
{
// Replace defined parameter placeholder with given values
exampleqrystring = exampleqrystring.Replace("?" + param.Key, "'" + param.Value + "'");
exampleqrystring = exampleqrystring.Replace(":" + param.Key, "'" + param.Value + "'");
exampleqrystring = exampleqrystring.Replace("@" + param.Key, "'" + param.Value + "'");
}
// Return parsed query
return exampleqrystring;
}
private void cmbQueries_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
btnRefreshQuery.IsEnabled = false;
var row = cmbQueries.SelectedItem;
if (row != null)
{
var link = new AppDBQueryLink(row);
currentQuery = null;
edSQL.Text = link.sqltext;
// Disable query action buttons
enableQueryActionControls(true);
}
else
{
currentQuery = null;
edSQL.Text = string.Empty;
// Disable query action buttons
enableQueryActionControls(false);
}
}
private void btnGoQuery_Click(object sender, RoutedEventArgs e)
{
btnRefreshQuery.IsEnabled = false;
// parse query parameters
var row = cmbQueries.SelectedItem;
if (row != null)
{
var link = new AppDBQueryLink(row);
if (currentQuery == null)
{
currentQuery = new StoredQuery(link.sqltext);
}
// Aks for query parameters and save them
if (!AskForParameters(currentQuery))
{
// Return if parameter input was canceled
return;
}
currentQuery.RewriteParameters(DBConnection.GetParamPrefixChar());
// Processs query parameters
edSQL.Text = ProcessParameters(currentQuery);
barQuery.Items.Clear();
var hyjackquery = false;
CExplainableQuery expl = QueryExplanationFactory.NewExplain(DBConnection, currentQuery);
if (expl != null)
{
if (expl.HasErrors())
{
MessageBox.Show(expl.GetErrorMsg(), "Query error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
else if (!expl.IsAllIndexed())
{
if (MessageBox.Show("This query does not fully make use of indexes, are you sure you want to execute this query?", "Query warning", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.No)
{
hyjackquery = true;
}
}
else if (expl.IsUsingBadStuff())
{
if (MessageBox.Show("This query could take a long time to run, are you sure you want to execute this query?", "Query warning", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.No)
{
hyjackquery = true;
}
}
else if (expl.GetMaxResults() >= 65535)
{
if (MessageBox.Show("This query could possibly return a lot of rows, are you sure you want to execute this query?", "Query warning", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.No)
{
hyjackquery = true;
}
}
}
if (hyjackquery)
{
// execute the explain instead (only works for mysql queries)
var qryExplain = expl._get();
DBConnection.Query(qryExplain);
}
else
{
// execute query and get result set
DBConnection.Query(currentQuery);
}
DataTable dt;
try
{
// todo: datatable contains all results, no cursor/rowtravel/stream
dt = DBConnection.ResultsAsDataTable();
// Enable Refresh query button
btnRefreshQuery.IsEnabled = true;
// Enable Feed to action
btnFeedToAction.IsEnabled = true;
}
catch (Exception x)
{
// todo: handle query errors in a better way
MessageBox.Show(x.Message);
return;
}
// display results in datagrid
gridQueryResults.AutoGenerateColumns = true;
if (dt != null)
{
gridQueryResults.ItemsSource = dt.DefaultView;
string s = "Results: " + dt.Rows.Count;
barQuery.Items.Add(s);
}
else
{
gridQueryResults.ItemsSource = null;
string s = "No results";
barQuery.Items.Add(s);
}
}
}
public List<string> ListTableNames()
{
var tblqry = new StoredQuery("SELECT name FROM sqlite_master WHERE type='table';");
if (Query(tblqry))
{
var dt = ResultsAsDataTable();
if (dt != null)
{
var lst = new List<string>();
foreach (var row in dt.AsEnumerable())
{
lst.Add(row.Field<string>(0));
}
return lst;
}
CloseQuery();
}
return null;
}
public bool Query(StoredQuery qry)
{
currentCmd = new SQLiteCommand(qry.SQL, db);
foreach (var p in qry.Parameters)
{
currentCmd.Parameters.AddWithValue(p.Key, p.Value);
}
return true;
}
public StoredQuery(StoredQuery qry)
{
this.SQL = qry.SQL;
CopyParamsFrom(qry);
}
public MSSQLQueryExplanation(IQueryableConnection connection, StoredQuery query)
: base(connection, query)
{
}
public List<string> ListTableNames()
{
var tblqry = new StoredQuery("SHOW TABLES;");
if (Query(tblqry))
{
var dt = ResultsAsDataTable();
if (dt != null)
{
var lst = new List<string>();
foreach (var row in dt.AsEnumerable())
{
lst.Add(row.Field<string>(0));
}
return lst;
}
CloseQuery();
}
return null;
}
