protected override void AnalyzeStructure()
{
using (QSqlBase s = GetSql())
{
s.Open(@"
select table_name,row_format,table_rows,avg_row_length,data_length,index_length
from information_schema.tables
where table_type='BASE TABLE' and table_schema='" + databaseName + "'");
while (s.GetRow())
{
DbTable dbTable = tables[s["table_name"]];
dbTable.tableRows = s.GetInt(2);
dbTable.avgRowLength = s.GetInt(3);
dbTable.dataLength = s.GetInt(4);
dbTable.indexLength = s.GetInt(5);
// tables.Add(dbTable.name, table);
}
s.Open(@"
select c.table_name,c.constraint_name,c.constraint_type,s.non_unique,s.seq_in_index,s.column_name,s.cardinality,s.index_type
from information_schema.table_constraints c
inner join information_schema.statistics s on s.table_name=c.table_name and c.table_schema=s.table_schema
and s.index_name=c.constraint_name
where c.table_schema = '" + databaseName + "' order by c.table_name, s.seq_in_index");
while (s.GetRow())
{
DbTableConstraint constraint = tables[s["table_name"]].GetOrAddConstraint(s["constraint_name"], s["constraint_type"]);
constraint.AddColumn(constraint.dbTable.columns[s["column_name"]], s.GetInt("seq_in_index"), s.GetBool("non_unique"), s.GetInt("cardinality"));
}
s.Open(@"
select constraint_name,table_name,column_name,ordinal_position,position_in_unique_constraint,
referenced_table_name,referenced_column_name
from information_schema.KEY_COLUMN_USAGE
where table_schema= '" + databaseName + "'");
while (s.GetRow())
{
DbTableConstraint constraint = tables[s["table_name"]].GetOrAddConstraint(s["constraint_name"], "FOREIGN_KEY");
DbTable refTable = s["referenced_table_name"] != "" ? tables[s["referenced_table_name"]] : null;
if (refTable != null)
{
constraint.AddReference(s["column_name"], refTable, s["referenced_column_name"], s.GetInt("ordinal_position"),
s.GetInt("position_in_unique_constraint"));
}
}
}
}
public int GetExpectedRowsInQuery(string sql)
{
int result = 0;
try
{
using (QSqlBase s = GetSql())
{
s.Open("select count(*) from (" + sql + ") t1010ee");
if (s.GetRow())
{
result = s.GetInt(0);
}
}
}
catch (Exception ex)
{
}
return(result);
}
string GetColumnValue(QSqlBase s, int i)
{
string result = null;
try
{
if (!s.IsNull(i))
{
result = s.GetString(i);
}
}
catch (Exception e)
{
if (!columnGetDataErrors.Contains(i))
{
A.AddToLog("error getting value for column " + i + " - " + e.Message, true, MsgStatus.Warning);
A.AddToLog("further errors for column " + i + " will be suppressed", false, MsgStatus.Warning);
columnGetDataErrors.Add(i);
}
}
return(result);
}
public void SaveData()
{
if (!Directory.Exists(directoryTextbox.Text))
{
return;
}
using (renderer = new RenderResults(T.AddFileToPath(directoryTextbox.Text, fileNameTextbox.Text)))
{
renderer.betweenColumns = colSeparator.Text;
renderer.betweenRows = rowSeparator.Text;
renderer.betweenColumns = colSeparator.Text;
renderer.includeHeaders = columnTitles.GetValue();
renderer.escapeWhenNecessary = escapeWhenNecessary.GetValue();
renderer.padStrings = padStrings.GetValue();
renderer.putStringColumnsInSingleQuotes = singleQuoteStrings.GetValue();
renderer.putStringColumnsInDoubleQuotes = doubleQuoteStrings.GetValue();
renderer.removeNewLines = removeNewlines.GetValue();
renderer.removeNonAscii = stripNonAscii.GetValue();
renderer.includeNulls = printNulls.GetValue();
renderer.removeTime = removeTime.GetValue();
renderer.format = fileType == "Code" ? formatCombo.GetValue() : "";
renderer.rowTemplate = fileType.IsOneOf("Code", "Custom") ? S.Set("rowTemplate", rowTemplate.Text) : "";
renderer.columnTemplate = fileType.IsOneOf("Code", "Custom") ? S.Set("columnTemplate", columnTemplate.Text) : "";
string selectedTable = tableMode ? tableCombo.GetValue() : null;
int rowsExpected = 0;
string sql = null;
if (tableMode)
{
sql = "select " + columnsList.GetSelectedStrings(",") + " from " + selectedTable;
rowsExpected = A.db.GetExpectedRowsInQuery(sql);
}
else
{
rowsExpected = selectedIndices != null && saveSelected.Checked ? selectedIndices.Count : query.rows.Count;
}
selectedColumnsIndexes.Clear();
for (int i = 0; i < columnsList.Items.Count; i++)
{
int pos = 0;
if (columnsList.GetSelected(i))
{
selectedColumnsIndexes.Add(i);
if (tableMode)
{
renderer.AddColumn(new QueryColumnInfo(pos++, A.db.tables[selectedTable].columns[columnsList.Items[i].ToString()]));
}
else
{
renderer.AddColumn(query.columns[i]);
}
}
}
if (columnTitles.Checked)
{
renderer.WriteHeader();
}
using (QSqlBase s = A.db.GetSql())
{
sForWriting = s;
if (tableMode)
{
sForWriting.Open(sql);
}
DlgSaveAsSave dlg = new DlgSaveAsSave(this, rowsExpected);
DialogResult result = dlg.ShowDialog(this);
renderer.Flush();
if (result == DialogResult.Yes)
{
System.Diagnostics.Process.Start(directoryTextbox.Text);
}
else if (result == DialogResult.Cancel)
{
sForWriting.CancelQuery();
}
}
}
Close();
}
public void Run()
{
A.AddToLog("running query...");
A.SetStatus(Status.Executing);
columnGetDataErrors.Clear();
rows.Clear();
columns.Clear();
queryCancelled = false;
expectedRows = A.db.GetExpectedRowsInQuery(expr);
using (QSqlBase s = A.db.GetSql())
{
if (!queryCancelled)
{
watch.Reset();
watch.Start();
s.Open(expr);
TimeSpan span = watch.Elapsed;
queryMs = Convert.ToInt32(span.TotalMilliseconds);
executionTime = span.ToString("ss':'ff");
watch.Reset();
watch.Start();
bool first = true;
A.SetStatus(Status.LoadingRows);
while (queryCancelled == false && s.GetRow())
{
if (first)
{
lock (columns)
{
for (int i = 0; i < s.FieldCount; i++)
{
columns.Add(new QueryColumnInfo(i, s.GetColumnName(i), s.GetColumnType(i)));
}
}
first = false;
}
List <string> row = new List <string>();
for (int i = 0; i < s.FieldCount; i++)
{
string txt = GetColumnValue(s, i);
if (txt != null)
{
lock (columns)
{
columns[i].UpdateMaxWidth(txt.Length);
}
}
row.Add(txt);
}
bool notify = false;
lock (rows)
{
if (rows.Count > 10000000)
{
throw new ApplicationException("too many rows");
}
rows.Add(row);
notify = rows.Count == 100 || (rows.Count % queryUpdateFreq == 0);
A.SetProgress(rows.Count, expectedRows);
}
if (notify)
{
resultsList.resultsReady = true;
}
}
}
}
if (queryCancelled)
{
A.SetStatus(Status.Cancelled);
}
else
{
resultsList.resultsReady = true;
A.SetStatus(Status.Ready);
TimeSpan span = watch.Elapsed;
loadingTime = span.ToString("ss':'ff");
lock (rows)
{
Settings.SaveQueryDiagnostics(_currentQueryId, true, rows.Count, queryMs);
A.AddToLog("execution time: " + executionTime);
A.AddToLog("loading time: " + loadingTime);
A.AddToLog("rows: " + rows.Count);
}
}
}
