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