public IEnumerable <IEnumerable <DBTableSchema> > GetTableRoutes(DBTableSchema fromTable, DBTableSchema toTable) { var paths = new List <IEnumerable <DBTableSchema> >(); FindPathsRecursive(fromTable, toTable, Enumerable.Empty <DBTableSchema>(), paths.Add); return(paths); }
private DBKeyRoot FindKeyRootRecursively(DBTableSchema table, DBColumnSchema keyColumn) { if (!(keyColumn.IsPrimaryKey || keyColumn.IsForeignKey || keyColumn.IsUnique)) { throw new ArgumentException("Column is not a key", "keyColumn"); } if ((keyColumn.IsPrimaryKey || keyColumn.IsUnique) && !keyColumn.IsForeignKey) { return new DBKeyRoot { RootColumn = keyColumn.Name, RootTable = table.Name, IsAutoIncrement = keyColumn.IsAutoIncrement, UsesSequence = keyColumn.UsesSequence } } ; Debug.Assert(keyColumn.IsForeignKey); // Since this is not a root primary key, recursively call the linked to primary key foreach (var foreignKey in table.ForeignKeys) { for (int i = 0; i < foreignKey.ForeignKeyColumns.Length; i++) { if (foreignKey.ForeignKeyColumns[i] == keyColumn.Name) { var tableName = foreignKey.ReferenceTable; var columnName = foreignKey.ReferenceColumns[i]; var pkTable = this[tableName]; var pkColumn = pkTable.GetColumn(columnName); // If it's a foreign key to itself (stupid but possible), then it is the root if (table.Name == pkTable.Name && keyColumn.Name == pkColumn.Name) { return new DBKeyRoot { RootColumn = keyColumn.Name, RootTable = table.Name, IsAutoIncrement = keyColumn.IsAutoIncrement, UsesSequence = keyColumn.UsesSequence } } ; return(FindKeyRootRecursively(pkTable, pkColumn)); } } } throw new Exception(string.Format("Unable to find key root for {0}.{1}", table.Name, keyColumn.Name)); }
private static MultiKeyDictionary2 <T, T[]> ConvertRowToMultiKeyDictionaryInternal <T>( this DBTableSchema tableSchema, IEnumerable <IEnumerable <object> > dataRows, Func <object, T> baseTypeTransformer ) { return (dataRows .ToMultiKeyDictionary2Ex( tableSchema .PrimaryKeyColumns .Select( col => (Func <IEnumerable <object>, T>)(row => baseTypeTransformer(row.ElementAt(col.Position - 1))) ), row => row.Select(baseTypeTransformer).ToArray() )); }
private void FindPathsRecursive(DBTableSchema from, DBTableSchema to, IEnumerable <DBTableSchema> currentPath, Action <IEnumerable <DBTableSchema> > notifyFoundPath) { if (currentPath.Contains(from)) { return; // cyclic path, ignore } currentPath = currentPath.Concat(from); if (from == to) { notifyFoundPath(currentPath); } foreach (var foreignKey in from.ForeignKeys) { FindPathsRecursive(this[foreignKey.ReferenceTable], to, currentPath, notifyFoundPath); } }
private int CalculateHeight(DBTableSchema table, HashSet <DBTableSchema> visitedTables = null) { if (visitedTables == null) { visitedTables = new HashSet <DBTableSchema>(); } if (visitedTables.Contains(table)) { return(0); } visitedTables.Add(table); if (!table.HeightHasBeenCalculated) { table.Height = !table.ForeignKeys.Any() ? 1 : table.ForeignKeys.Select(fk => CalculateHeight(this[fk.ReferenceTable], visitedTables)).Max() + 1; table.HeightHasBeenCalculated = true; } return(table.Height); }
public static ISQLBuilder DuplicateRow(this ISQLBuilder sqlBuilder, DBTableSchema table, IEnumerable <object> sourcePrimaryKey, IEnumerable <object> destPrimaryKey, out string identityVariable, IEnumerable <ColumnValue> overrideColumns = null) { // Query structure: // INSERT INTO Table([PkCol1], ..., [PkColN], [NonPkCol1], ..., [NonPkColN]) VALUES SELECT {destPrimaryKey1}, ..., {destPrimaryKeyN}, [NonPkCol1], ..., [NonPkColN] FROM Table WHERE [PkCol1] = {sourcePrimaryKey1}, ..., [PkColN] = {sourcePrimaryKeyN} bool isAutoIncrement; bool usesGenerator; bool specifiesPrimaryKey; #region Validation if (sourcePrimaryKey == null) { throw new SoftwareException("Source primary key not specified"); } if (destPrimaryKey == null) { destPrimaryKey = Enumerable.Empty <object>(); } if (overrideColumns == null) { overrideColumns = Enumerable.Empty <ColumnValue>(); } if (table.PrimaryKeyColumns.Length == 0) { throw new SoftwareException("Table '{0}' does not have a primary key", table.Name); } if (!sourcePrimaryKey.Any()) { throw new SoftwareException("Inconsistent primary key parameter. Table {0} primary key has {1} columns, argument specified {2} values", table.Name, table.PrimaryKeyColumns.Length, sourcePrimaryKey.Count()); } isAutoIncrement = table.PrimaryKeyColumns.Length == 1 && table.PrimaryKeyColumns[0].IsAutoIncrement; usesGenerator = false; specifiesPrimaryKey = destPrimaryKey.Any(); if (!(isAutoIncrement || usesGenerator) && !specifiesPrimaryKey) { throw new SoftwareException("Destination primary key not specified"); } #endregion identityVariable = null; var sourcePrimaryKeyArray = sourcePrimaryKey.ToArray(); // Declare variable to store generated identity (if applicable) if ((isAutoIncrement || usesGenerator) && !specifiesPrimaryKey) { identityVariable = string.Format("uniquedup{0}", sqlBuilder.VariableDeclarationCount + 1); sqlBuilder.DeclareVariable(identityVariable, typeof(long)); } // get the non-primary key columns var nonPkColumns = table.Columns.Where(c => !c.IsPrimaryKey); // disable autoincrement if user specified key if (isAutoIncrement && specifiesPrimaryKey) { sqlBuilder.DisableAutoIncrementID(table.Name); } sqlBuilder .Emit("INSERT INTO ").TableName(table.Name).Emit("("); // TODO: Changed Union to Concat -- will this introduce errors? ((specifiesPrimaryKey || usesGenerator) ? table.PrimaryKeyColumns : Enumerable.Empty <DBColumnSchema>() ).Concat(nonPkColumns) .WithDescriptions() .ForEach( colDescription => { if (colDescription.Index > 0) { sqlBuilder.Emit(", "); } sqlBuilder.ColumnName(colDescription.Item.Name); } ); sqlBuilder.Emit(") SELECT "); var overrideColumnsLookup = overrideColumns.ToDictionary(c => c.ColumnName, c => c.Value); if (specifiesPrimaryKey) { // insert explicit primary key values destPrimaryKey .WithDescriptions() .ForEach( destPrimaryKeyValue => { if (destPrimaryKeyValue.Index > 0) { sqlBuilder.Emit(", "); } sqlBuilder.Literal(destPrimaryKeyValue.Item); } ); } else if (usesGenerator) { // insert call to generator throw new NotImplementedException(); } nonPkColumns .WithDescriptions() .ForEach( colDescription => { if (specifiesPrimaryKey || usesGenerator || colDescription.Index > 0) { sqlBuilder.Emit(", "); } if (overrideColumnsLookup.ContainsKey(colDescription.Item.Name)) { sqlBuilder.Literal(overrideColumnsLookup[colDescription.Item.Name]); } else { sqlBuilder.ColumnName(colDescription.Item.Name); } } ); sqlBuilder.Emit(" FROM ").TableName(table.Name).Emit(" WHERE "); table .PrimaryKeyColumns .WithDescriptions() .ForEach( pkCol => { if (pkCol.Index > 0) { sqlBuilder.Emit(" AND "); } sqlBuilder .ColumnName(pkCol.Item.Name) .Emit(" = ") .Literal(sourcePrimaryKeyArray[pkCol.Index]); } ); sqlBuilder.EndOfStatement(); if (isAutoIncrement) { sqlBuilder.EnableAutoIncrementID(table.Name); } if ((isAutoIncrement || usesGenerator) && !specifiesPrimaryKey) { sqlBuilder.AssignVariable(identityVariable, SQLBuilderCommand.LastIdentity(table.Name)); } return(sqlBuilder); }
public IEnumerable <Tuple <DBTableSchema, IEnumerable <DBColumnSchema> > > GetDependencies(DBTableSchema table, IEnumerable <DBColumnSchema> tableColumns, bool fetchTransitiveDependencies = true, bool fetchArtificialOnly = false) { return(GetDependenciesRecursive(table, tableColumns, fetchTransitiveDependencies: fetchTransitiveDependencies, fetchArtificialOnly: fetchArtificialOnly)); }
private IEnumerable <Tuple <DBTableSchema, IEnumerable <DBColumnSchema> > > GetDependenciesRecursive(DBTableSchema table, IEnumerable <DBColumnSchema> tableColumns, HashSet <string> visitedTables = null, bool fetchTransitiveDependencies = true, bool fetchArtificialOnly = false) { #region Argument Validation if (table == null) { throw new ArgumentNullException("table"); } if (tableColumns == null) { throw new ArgumentNullException("tableColumns"); } if (!tableColumns.Any()) { throw new ArgumentException("No columns specified", "tableColumns"); } #endregion if (visitedTables == null) { visitedTables = new HashSet <string>(); } if (visitedTables.Contains(table.Name)) { return(Enumerable.Empty <Tuple <DBTableSchema, IEnumerable <DBColumnSchema> > >()); } visitedTables.Add(table.Name); var keyColumnNames = tableColumns.Select(c => c.Name); var dependencies = ( from foreignKey in Relations where fetchArtificialOnly ? foreignKey.KeyType.HasFlag(DBKeyType.Artificial) : true && foreignKey.ReferenceTable == table.Name && foreignKey.ReferenceColumns.ContainSameElements(keyColumnNames) let foreignKeyTable = this[foreignKey.ForeignKeyTable] select Tuple.Create(foreignKeyTable, from fkCol in foreignKey.ForeignKeyColumns select foreignKeyTable[fkCol]) ); if (fetchTransitiveDependencies) { // Fetch the transitive dependencies // e.g A.ColID -> B.ID -> table.ID foreach (var dependency in dependencies.ToArray()) { dependencies = dependencies.Concat(GetDependenciesRecursive(dependency.Item1, dependency.Item2, visitedTables)); } } return(dependencies); }
public static MultiKeyDictionary2 <string, string[]> ConvertDataToStringBasedMultiKeyDictionary(this DBTableSchema tableSchema, IEnumerable <IEnumerable <object> > dataRows) { return(ConvertRowToMultiKeyDictionaryInternal(tableSchema, dataRows, (x) => x != null ? Convert.ChangeType(x, typeof(string)) as string : "NULL")); }
public static MultiKeyDictionary2 <object, object[]> ConvertDataToMultiKeyDictionary(this DBTableSchema tableSchema, IEnumerable <IEnumerable <object> > dataRows) { return(ConvertRowToMultiKeyDictionaryInternal(tableSchema, dataRows, (x) => x)); }