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); } } }