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