public static List <Table> ResortTables(List <Table> tables, List <TableForeignKey> foreignKeys) { string[] tableNames = tables.Select(item => item.Name).ToArray(); List <string> sortedTableNames = TableReferenceHelper.ResortTableNames(tableNames, foreignKeys); int i = 1; foreach (string tableName in sortedTableNames) { Table table = tables.FirstOrDefault(item => item.Name == tableName); if (table != null) { table.Order = i++; } } return(tables.OrderBy(item => item.Order).ToList()); }
public virtual async Task <SchemaInfo> GetSchemaInfoAsync(SchemaInfoFilter filter = null) { if (filter == null) { filter = new SchemaInfoFilter(); } this.FeedbackInfo("Getting schema info..."); DatabaseObjectType dbObjectType = filter.DatabaseObjectType; SchemaInfo schemaInfo = new SchemaInfo(); using (DbConnection connection = this.CreateConnection()) { if (this.NeedFetchObjects(DatabaseObjectType.UserDefinedType, filter.UserDefinedTypeNames, filter)) { schemaInfo.UserDefinedTypes = await this.GetUserDefinedTypesAsync(connection, filter); } if (this.NeedFetchObjects(DatabaseObjectType.Function, filter.FunctionNames, filter)) { schemaInfo.Functions = await this.GetFunctionsAsync(connection, filter); } if (this.NeedFetchObjects(DatabaseObjectType.Table, filter.TableNames, filter)) { schemaInfo.Tables = await this.GetTablesAsync(connection, filter); } if (this.NeedFetchObjects(DatabaseObjectType.View, filter.ViewNames, filter)) { schemaInfo.Views = await this.GetViewsAsync(connection, filter); } if (this.NeedFetchObjects(DatabaseObjectType.Procedure, filter.ProcedureNames, filter)) { schemaInfo.Procedures = await this.GetProceduresAsync(connection, filter); } if (this.NeedFetchTableObjects(DatabaseObjectType.TableColumn, filter, null)) { schemaInfo.TableColumns = await this.GetTableColumnsAsync(connection, filter); } if (this.NeedFetchTableObjects(DatabaseObjectType.TablePrimaryKey, filter, null)) { schemaInfo.TablePrimaryKeys = await this.GetTablePrimaryKeysAsync(connection, filter); } if ((this.Option.SortObjectsByReference && schemaInfo.Tables.Count > 1) || this.NeedFetchTableObjects(DatabaseObjectType.TableForeignKey, filter, null)) { schemaInfo.TableForeignKeys = await this.GetTableForeignKeysAsync(connection, filter); } if (this.NeedFetchTableObjects(DatabaseObjectType.TableIndex, filter, null)) { schemaInfo.TableIndexes = await this.GetTableIndexesAsync(connection, filter, this.Option.IncludePrimaryKeyWhenGetTableIndex); } if (this.NeedFetchTableObjects(DatabaseObjectType.TableConstraint, filter, null)) { schemaInfo.TableConstraints = await this.GetTableConstraintsAsync(connection, filter); } if (this.NeedFetchTableObjects(DatabaseObjectType.TableTrigger, filter, filter.TableTriggerNames)) { schemaInfo.TableTriggers = await this.GetTableTriggersAsync(connection, filter); } } if (this.Option.SortObjectsByReference) { if (schemaInfo.Tables.Count > 1) { schemaInfo.Tables = TableReferenceHelper.ResortTables(schemaInfo.Tables, schemaInfo.TableForeignKeys); } DbObjectHelper.Resort(schemaInfo.Views); DbObjectHelper.Resort(schemaInfo.Functions); DbObjectHelper.Resort(schemaInfo.Procedures); } this.FeedbackInfo("End get schema info."); return(schemaInfo); }
public virtual async Task <string> GenerateDataScriptsAsync(SchemaInfo schemaInfo) { StringBuilder sb = new StringBuilder(); if (this.option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile)) { this.ClearScriptFile(GenerateScriptMode.Data); } int i = 0; int pickupIndex = -1; if (schemaInfo.PickupTable != null) { foreach (Table table in schemaInfo.Tables) { if (table.Owner == schemaInfo.PickupTable.Owner && table.Name == schemaInfo.PickupTable.Name) { pickupIndex = i; break; } i++; } } i = 0; using (DbConnection connection = this.dbInterpreter.CreateConnection()) { int tableCount = schemaInfo.Tables.Count - (pickupIndex == -1 ? 0 : pickupIndex); int count = 0; foreach (Table table in schemaInfo.Tables) { if (this.dbInterpreter.CancelRequested) { break; } if (i < pickupIndex) { i++; continue; } count++; string strTableCount = $"({count}/{tableCount})"; string tableName = table.Name; List <TableColumn> columns = schemaInfo.TableColumns.Where(item => item.Owner == table.Owner && item.TableName == tableName).OrderBy(item => item.Order).ToList(); bool isSelfReference = TableReferenceHelper.IsSelfReference(tableName, schemaInfo.TableForeignKeys); TablePrimaryKey primaryKey = schemaInfo.TablePrimaryKeys.FirstOrDefault(item => item.Owner == table.Owner && item.TableName == tableName); string primaryKeyColumns = primaryKey == null ? "" : string.Join(",", primaryKey.Columns.OrderBy(item => item.Order).Select(item => this.GetQuotedString(item.ColumnName))); long total = await this.dbInterpreter.GetTableRecordCountAsync(connection, table); if (this.option.DataGenerateThreshold.HasValue && total > this.option.DataGenerateThreshold.Value) { this.FeedbackInfo($"Record count of table \"{table.Name}\" exceeds {this.option.DataGenerateThreshold.Value},ignore it."); continue; } int pageSize = this.dbInterpreter.DataBatchSize; this.FeedbackInfo($"{strTableCount}Table \"{table.Name}\":record count is {total}."); Dictionary <long, List <Dictionary <string, object> > > dictPagedData; if (isSelfReference) { string parentColumnName = schemaInfo.TableForeignKeys.FirstOrDefault(item => item.Owner == table.Owner && item.TableName == tableName && item.ReferencedTableName == tableName)?.Columns?.FirstOrDefault()?.ColumnName; string strWhere = $" WHERE { this.GetQuotedString(parentColumnName)} IS NULL"; dictPagedData = await this.GetSortedPageData(connection, table, primaryKeyColumns, parentColumnName, columns, strWhere); } else { dictPagedData = await this.dbInterpreter.GetPagedDataListAsync(connection, table, columns, primaryKeyColumns, total, pageSize); } this.FeedbackInfo($"{strTableCount}Table \"{table.Name}\":data read finished."); if (count > 1) { if (this.option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToString)) { sb.AppendLine(); } else if (this.option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile)) { this.AppendScriptsToFile(Environment.NewLine, GenerateScriptMode.Data); } } i++; if (this.option.BulkCopy && this.dbInterpreter.SupportBulkCopy && !this.option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile)) { continue; } else { this.AppendDataScripts(sb, table, columns, dictPagedData); } } } var dataScripts = string.Empty; try { dataScripts = sb.ToString(); } catch (OutOfMemoryException ex) { this.FeedbackError("Exception occurs:" + ex.Message); } finally { sb.Clear(); } return(dataScripts); }
public virtual async Task <SchemaInfo> GetSchemaInfoAsync(SelectionInfo selectionInfo) { SchemaInfo schemaInfo = new SchemaInfo(); using (DbConnection connection = this.dbConnector.CreateConnection()) { if (this.NeedFetchObjects(selectionInfo.UserDefinedTypeNames)) { schemaInfo.UserDefinedTypes = await this.GetUserDefinedTypesAsync(connection, selectionInfo.UserDefinedTypeNames); } if (this.NeedFetchObjects(selectionInfo.FunctionNames)) { schemaInfo.Functions = await this.GetFunctionsAsync(connection, selectionInfo.FunctionNames); } if (this.NeedFetchObjects(selectionInfo.TableNames)) { schemaInfo.Tables = await this.GetTablesAsync(connection, selectionInfo.TableNames); schemaInfo.TableColumns = await this.GetTableColumnsAsync(connection, selectionInfo.TableNames); if (this.Option.GenerateKey) { schemaInfo.TablePrimaryKeys = await this.GetTablePrimaryKeysAsync(connection, selectionInfo.TableNames); schemaInfo.TableForeignKeys = await this.GetTableForeignKeysAsync(connection, selectionInfo.TableNames); } if (this.Option.GenerateIndex) { schemaInfo.TableIndexes = await this.GetTableIndexesAsync(connection, selectionInfo.TableNames); } } if (this.Option.SortTablesByKeyReference && this.Option.GenerateKey) { string[] tableNames = schemaInfo.Tables.Select(item => item.Name).ToArray(); List <string> sortedTableNames = TableReferenceHelper.ResortTableNames(tableNames, schemaInfo.TableForeignKeys); int i = 1; foreach (string tableName in sortedTableNames) { Table table = schemaInfo.Tables.FirstOrDefault(item => item.Name == tableName); if (table != null) { table.Order = i++; } } schemaInfo.Tables = schemaInfo.Tables.OrderBy(item => item.Order).ToList(); } if (this.NeedFetchObjects(selectionInfo.ViewNames)) { schemaInfo.Views = ViewHelper.ResortViews(await this.GetViewsAsync(connection, selectionInfo.ViewNames)); } if (this.NeedFetchObjects(selectionInfo.TriggerNames)) { schemaInfo.Triggers = await this.GetTriggersAsync(connection, selectionInfo.TriggerNames); } if (this.NeedFetchObjects(selectionInfo.ProcedureNames)) { schemaInfo.Procedures = await this.GetProceduresAsync(connection, selectionInfo.ProcedureNames); } } return(schemaInfo); }