/// <summary>
/// Builds the foreign keys.
/// </summary>
private void BuildForeignKeys()
{
// Get the foreign key information
DataTable fkeys = GetOleDbSchema(OleDbSchemaGuid.Foreign_Keys, "", _schemaFilter, "", "");
foreach (DataRow dr in fkeys.Rows)
{
try
{
TableSchema primaryKeyTable;
TableSchema foreignKeyTable;
string fkTableName = dr["FK_TABLE_NAME"].ToString();
string pkTableName = dr["PK_TABLE_NAME"].ToString();
string fkName = dr["FK_NAME"].ToString();
if (result.ForeignKeys.ContainsKey(fkName))
{
continue;
}
if (result.Tables.TryGetValue(pkTableName, out primaryKeyTable) &&
result.Tables.TryGetValue(fkTableName, out foreignKeyTable))
{
ForeignKeySchema fk = new ForeignKeySchema(result, fkName, pkTableName, fkTableName);
result.ForeignKeys.Add(fkName, fk);
primaryKeyTable.ForeignKeys.Add(fkName, fk);
if (!foreignKeyTable.ForeignKeys.ContainsKey(fkName))
{
foreignKeyTable.ForeignKeys.Add(fkName, fk);
}
}
}
catch (Exception ex)
{
Errors.Add(ex);
}
}
foreach (DataRow dr in fkeys.Rows)
{
try
{
string fkName = dr["FK_NAME"].ToString();
string primaryColumnName = dr["PK_COLUMN_NAME"].ToString();
string foreignColumnName = dr["FK_COLUMN_NAME"].ToString();
ForeignKeySchema key;
if (result.ForeignKeys.TryGetValue(fkName, out key))
{
key.Associations.Add(new ForeignKeyAssociation(key, primaryColumnName, foreignColumnName));
}
}
catch (Exception ex)
{
Errors.Add(ex);
}
}
}
private static string BuildCreateTableQuery(TableSchema ts)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("CREATE TABLE [" + ts.TableName + "] (\n");
bool pkey = false;
for (int i = 0; i < ts.Columns.Count; i++)
{
string value = BuildColumnStatement(ts.Columns[i], ts, ref pkey);
stringBuilder.Append(value);
if (i < ts.Columns.Count - 1)
{
stringBuilder.Append(",\n");
}
}
if (ts.PrimaryKey != null && ts.PrimaryKey.Count > 0 && !pkey)
{
stringBuilder.Append(",\n");
stringBuilder.Append(" PRIMARY KEY (");
for (int j = 0; j < ts.PrimaryKey.Count; j++)
{
stringBuilder.Append("[" + ts.PrimaryKey[j] + "]");
if (j < ts.PrimaryKey.Count - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append(")\n");
}
else
{
stringBuilder.Append("\n");
}
if (ts.ForeignKeys.Count > 0)
{
stringBuilder.Append(",\n");
for (int k = 0; k < ts.ForeignKeys.Count; k++)
{
ForeignKeySchema foreignKeySchema = ts.ForeignKeys[k];
string value2 = $" FOREIGN KEY ([{foreignKeySchema.ColumnName}])\n REFERENCES [{foreignKeySchema.ForeignTableName}]([{foreignKeySchema.ForeignColumnName}])";
stringBuilder.Append(value2);
if (k < ts.ForeignKeys.Count - 1)
{
stringBuilder.Append(",\n");
}
}
}
stringBuilder.Append("\n");
stringBuilder.Append(");\n");
if (ts.Indexes != null)
{
for (int l = 0; l < ts.Indexes.Count; l++)
{
string str = BuildCreateIndex(ts.TableName, ts.Indexes[l]);
stringBuilder.Append(str + ";\n");
}
}
return(stringBuilder.ToString());
}
public static TriggerSchema GenerateDeleteTrigger(ForeignKeySchema fks)
{
var trigger = new TriggerSchema();
trigger.Name = MakeTriggerName(fks, "fkd");
trigger.Type = TriggerType.Before;
trigger.Event = TriggerEvent.Delete;
trigger.Table = fks.ForeignTableName;
string triggerName = trigger.Name;
if (!fks.CascadeOnDelete)
{
trigger.Body = "SELECT RAISE(ROLLBACK, 'delete on table " + fks.ForeignTableName +
" violates foreign key constraint " + triggerName + "')" +
" WHERE (SELECT " + fks.ColumnName +
" FROM " + fks.TableName + " WHERE " + fks.ColumnName + " = OLD." +
fks.ForeignColumnName +
") IS NOT NULL; ";
}
else
{
trigger.Body = "DELETE FROM [" + fks.TableName + "] WHERE " + fks.ColumnName + " = OLD." +
fks.ForeignColumnName + "; ";
}
return(trigger);
}
public static TriggerSchema GenerateUpdateTrigger(ForeignKeySchema fks)
{
var trigger = new TriggerSchema();
trigger.Name = MakeTriggerName(fks, "fku");
trigger.Type = TriggerType.Before;
trigger.Event = TriggerEvent.Update;
trigger.Table = fks.TableName;
string triggerName = trigger.Name;
string nullString = "";
if (fks.IsNullable)
{
nullString = " NEW." + fks.ColumnName + " IS NOT NULL AND";
}
trigger.Body = "SELECT RAISE(ROLLBACK, 'update on table " + fks.TableName +
" violates foreign key constraint " + triggerName + "')" +
" WHERE" + nullString + " (SELECT " + fks.ForeignColumnName +
" FROM " + fks.ForeignTableName + " WHERE " + fks.ForeignColumnName + " = NEW." +
fks.ColumnName +
") IS NULL; ";
return(trigger);
}
/// <summary>
/// Initializes a new instance of the <see cref="ForeignKeyElement"/> class.
/// </summary>
/// <param name="schema">The schema.</param>
public ForeignKeyElement(ForeignKeySchema schema)
{
this.schema = schema;
// this.ControlPoints.Add(new ControlPoint())
//this.Con
}
private static void CheckForeignKey(ForeignKeySchema foreignKey, string foreignKeyName, ForeignKeyRule rule)
{
foreignKey.Name.Should().Be(foreignKeyName, "Meno cudzieho kľúča musí byť správne.");
foreignKey.PrimaryKeyTableName.Should().Be("ParentTable", "Meno tabuľky s primárnym kľúčom musí byť správne.");
foreignKey.PrimaryKeyTableColumns.Should().Equal(new string[] { "Id" });
foreignKey.ForeignKeyTableColumns.Should().Equal(new string[] { "ParentId" });
foreignKey.DeleteRule.Should().Be(rule, "Pravidlo pri vymazaní (DELETE RULE) musí byť správne.");
foreignKey.UpdateRule.Should().Be(rule, "Pravidlo pri vymazaní (UPDATE RULE) musí byť správne.");
}
public void LoadTest()
{
DatabaseSchema schema = DatabaseSchema.Load(Helper.SchemaPath);
Assert.AreEqual(6, schema.Tables.Count);
foreach (TableSchema table in schema.Tables)
{
Assert.IsNotNull(table.Columns);
Assert.Greater(table.Columns.Count, 1, "Tables has columns less 1");
}
Assert.AreEqual(6, schema.ForeignKeys.Count);
foreach (ForeignKeySchema key in schema.ForeignKeys)
{
Assert.IsNotNull(key.Associations);
Assert.Greater(key.Associations.Count, 0);
}
TableSchema categoriesTable = schema.Tables["Categories"];
Assert.IsNotNull(categoriesTable);
Assert.AreEqual(4, categoriesTable.Columns.Count);
Assert.AreEqual(2, categoriesTable.ForeignKeys.Count);
Assert.IsTrue(categoriesTable.HasForeignKeys);
ForeignKeySchema selfKey = categoriesTable.ForeignKeys["FK_Categories_Categories"];
Assert.AreSame(schema.ForeignKeys["FK_Categories_Categories"], selfKey);
Assert.AreSame(selfKey.PrimaryTable, categoriesTable);
Assert.AreSame(selfKey.ForeignTable, categoriesTable);
Assert.AreEqual(1, selfKey.Associations.Count);
ForeignKeyAssociation association = selfKey.Associations[0];
Assert.AreSame(association.Key, selfKey);
Assert.IsNotNull(association.ForeignColumn);
Assert.IsNotNull(association.PrimaryColumn);
Assert.IsNotEmpty(association.ForeignColumnName);
Assert.IsNotEmpty(association.PrimaryColumnName);
List <ColumnSchema> columns = selfKey.Associations.GetColumns(selfKey.PrimaryTable);
Assert.AreEqual(1, columns.Count);
ColumnSchema primaryKeyColumn = columns[0];
Assert.IsNotNull(primaryKeyColumn);
Assert.AreEqual("CategoryID", primaryKeyColumn.Name);
}
private ForeignKeySchema CreateForeignKey(
DataRow foreignKeyData,
List <string> primaryKeyColumns,
List <string> foreignKeyColumns)
{
ForeignKeySchema foreignKey = new ForeignKeySchema(
(string)foreignKeyData[ForeignKeyQueryNames.ForeignKeyName],
(string)foreignKeyData[ForeignKeyQueryNames.ReferencedTableName],
primaryKeyColumns,
(string)foreignKeyData[ForeignKeyQueryNames.ParentTableName],
foreignKeyColumns);
foreignKey.DeleteRule = GetForeignKeyRule((string)foreignKeyData[ForeignKeyQueryNames.DeleteRule]);
foreignKey.UpdateRule = GetForeignKeyRule((string)foreignKeyData[ForeignKeyQueryNames.UpdateRule]);
return(foreignKey);
}
/// <summary>
/// Add foreign key schema object from the specified components (Read from SQL Server).
/// </summary>
/// <param name="ts">The table schema to whom foreign key schema should be added to</param>
private void CreateForeignKeySchema(TableSchema ts)
{
ts.ForeignKeys = new List <ForeignKeySchema>();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(
@"SELECT " +
@" ColumnName = CU.COLUMN_NAME, " +
@" ForeignTableName = PK.TABLE_NAME, " +
@" ForeignColumnName = PT.COLUMN_NAME, " +
@" DeleteRule = C.DELETE_RULE, " +
@" IsNullable = COL.IS_NULLABLE " +
@"FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C " +
@"INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME " +
@"INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME " +
@"INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME " +
@"INNER JOIN " +
@" ( " +
@" SELECT i1.TABLE_NAME, i2.COLUMN_NAME " +
@" FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 " +
@" INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME " +
@" WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' " +
@" ) " +
@"PT ON PT.TABLE_NAME = PK.TABLE_NAME " +
@"INNER JOIN INFORMATION_SCHEMA.COLUMNS AS COL ON CU.COLUMN_NAME = COL.COLUMN_NAME AND FK.TABLE_NAME = COL.TABLE_NAME " +
@"WHERE FK.Table_NAME='" + ts.TableName + "'", conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ForeignKeySchema fkc = new ForeignKeySchema();
fkc.ColumnName = (string)reader["ColumnName"];
fkc.ForeignTableName = (string)reader["ForeignTableName"];
fkc.ForeignColumnName = (string)reader["ForeignColumnName"];
fkc.CascadeOnDelete = (string)reader["DeleteRule"] == "CASCADE";
fkc.IsNullable = (string)reader["IsNullable"] == "YES";
fkc.TableName = ts.TableName;
ts.ForeignKeys.Add(fkc);
}
}
}
}
public static TriggerSchema GenerateInsertTrigger(ForeignKeySchema fks)
{
TriggerSchema triggerSchema = new TriggerSchema();
triggerSchema.Name = MakeTriggerName(fks, "fki");
triggerSchema.Type = TriggerType.Before;
triggerSchema.Event = TriggerEvent.Insert;
triggerSchema.Table = fks.TableName;
string text = "";
if (fks.IsNullable)
{
text = " NEW." + fks.ColumnName + " IS NOT NULL AND";
}
triggerSchema.Body = "SELECT RAISE(ROLLBACK, 'insert on table " + fks.TableName + " violates foreign key constraint " + triggerSchema.Name + "') WHERE" + text + " (SELECT " + fks.ForeignColumnName + " FROM " + fks.ForeignTableName + " WHERE " + fks.ForeignColumnName + " = NEW." + fks.ColumnName + ") IS NULL; ";
return(triggerSchema);
}
private static void CreateForeignKeySchema(SqlConnection conn, TableSchema ts)
{
ts.ForeignKeys = new List <ForeignKeySchema>();
using (SqlDataReader sqlDataReader = new SqlCommand("SELECT ColumnName = CU.COLUMN_NAME, ForeignTableName = PK.TABLE_NAME, ForeignColumnName = PT.COLUMN_NAME, DeleteRule = C.DELETE_RULE, IsNullable = COL.IS_NULLABLE FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_NAME INNER JOIN INFORMATION_SCHEMA.COLUMNS AS COL ON CU.COLUMN_NAME = COL.COLUMN_NAME AND FK.TABLE_NAME = COL.TABLE_NAME WHERE FK.Table_NAME='" + ts.TableName + "'", conn).ExecuteReader())
{
while (sqlDataReader.Read())
{
ForeignKeySchema foreignKeySchema = new ForeignKeySchema();
foreignKeySchema.ColumnName = (string)sqlDataReader["ColumnName"];
foreignKeySchema.ForeignTableName = (string)sqlDataReader["ForeignTableName"];
foreignKeySchema.ForeignColumnName = (string)sqlDataReader["ForeignColumnName"];
foreignKeySchema.CascadeOnDelete = ((string)sqlDataReader["DeleteRule"] == "CASCADE");
foreignKeySchema.IsNullable = ((string)sqlDataReader["IsNullable"] == "YES");
foreignKeySchema.TableName = ts.TableName;
ts.ForeignKeys.Add(foreignKeySchema);
}
}
}
private void LoadForeignKeysSchema(DatabaseSchema database, DataTable foreignKeysData, DataTable foreignKeyColumnsData)
{
DataView columnsView = foreignKeyColumnsData.DefaultView;
List <string> primaryKeyColumns = new List <string>();
List <string> foreignKeyColumns = new List <string>();
foreach (DataRow fkRow in foreignKeysData.Rows)
{
int foreignKeyId = (int)fkRow[ForeignKeyQueryNames.ForeignKeyId];
columnsView.RowFilter = $"[{ForeignKeyColumnsQueryNames.ForeignKeyId}] = {foreignKeyId}";
primaryKeyColumns.Clear();
foreignKeyColumns.Clear();
foreach (DataRowView fkColumnRow in columnsView)
{
primaryKeyColumns.Add((string)fkColumnRow.Row[ForeignKeyColumnsQueryNames.ReferencedColumnName]);
foreignKeyColumns.Add((string)fkColumnRow.Row[ForeignKeyColumnsQueryNames.ParentColumnName]);
}
ForeignKeySchema foreignKey = CreateForeignKey(fkRow, primaryKeyColumns, foreignKeyColumns);
database.Tables[(string)fkRow[ForeignKeyQueryNames.ParentTableName]].ForeignKeys.Add(foreignKey);
}
}
public void SetForeignKeyMemberNameTest()
{
var thisTable = new TableSchema {
TableName = "Xxx",
};
var otherTable = new TableSchema {
TableName = "Zzz",
};
var key = new ForeignKeySchema
{
KeyName = "FK_Xxx_YyyZzz",
MemberName = "FK_Xxx_YyyZzz",
ThisColumns = new List <ColumnSchema>
{
new ColumnSchema {
MemberName = "XxxID", IsPrimaryKey = true
},
new ColumnSchema {
MemberName = "YyyZzzID"
},
},
OtherColumns = new List <ColumnSchema>
{
new ColumnSchema {
MemberName = "ZzzID"
},
},
ThisTable = thisTable,
OtherTable = otherTable,
};
var key1 = new ForeignKeySchema
{
KeyName = "FK_Xxx_Zzz",
MemberName = "FK_Xxx_Zzz",
ThisColumns = new List <ColumnSchema>
{
new ColumnSchema {
MemberName = "XxxID", IsPrimaryKey = true
},
new ColumnSchema {
MemberName = "ZzzID"
},
},
OtherColumns = new List <ColumnSchema>
{
new ColumnSchema {
MemberName = "ZzzID"
},
},
ThisTable = thisTable,
OtherTable = otherTable,
};
key.ThisTable.ForeignKeys = new List <ForeignKeySchema> {
key, key1
};
key.ThisTable.Columns = key.ThisColumns;
key.BackReference = new ForeignKeySchema
{
KeyName = key.KeyName + "_BackReference",
MemberName = key.MemberName + "_BackReference",
AssociationType = AssociationType.Auto,
OtherTable = key.ThisTable,
ThisColumns = key.OtherColumns,
OtherColumns = key.ThisColumns,
};
SchemaProviderBase.SetForeignKeyMemberName(new GetSchemaOptions {
}, key.ThisTable, key);
Assert.That(key.MemberName, Is.EqualTo("YyyZzz"));
}
/// <summary>
/// returns the CREATE TABLE DDL for creating the SQLite table from the specified
/// table schema object.
/// </summary>
/// <param name="ts">The table schema object from which to create the SQL statement.</param>
/// <returns>CREATE TABLE DDL for the specified table.</returns>
private static string BuildCreateTableQuery(TableSchema ts)
{
var sb = new StringBuilder();
sb.Append("CREATE TABLE [" + ts.TableName + "] (\n");
bool pkey = false;
for (int i = 0; i < ts.Columns.Count; i++)
{
ColumnSchema col = ts.Columns[i];
string cline = BuildColumnStatement(col, ts, ref pkey);
sb.Append(cline);
if (i < ts.Columns.Count - 1)
{
sb.Append(",\n");
}
}
// add primary keys...
if (ts.PrimaryKey != null && ts.PrimaryKey.Count > 0 & !pkey)
{
sb.Append(",\n");
sb.Append(" PRIMARY KEY (");
for (int i = 0; i < ts.PrimaryKey.Count; i++)
{
sb.Append("[" + ts.PrimaryKey[i] + "]");
if (i < ts.PrimaryKey.Count - 1)
{
sb.Append(", ");
}
}
sb.Append(")\n");
}
else
{
sb.Append("\n");
}
// add foreign keys...
if (ts.ForeignKeys.Count > 0)
{
sb.Append(",\n");
for (int i = 0; i < ts.ForeignKeys.Count; i++)
{
ForeignKeySchema foreignKey = ts.ForeignKeys[i];
string stmt = string.Format(" FOREIGN KEY ([{0}])\n REFERENCES [{1}]([{2}])", foreignKey.ColumnName, foreignKey.ForeignTableName, foreignKey.ForeignColumnName);
sb.Append(stmt);
if (i < ts.ForeignKeys.Count - 1)
{
sb.Append(",\n");
}
}
}
sb.Append("\n");
sb.Append(");\n");
// Create any relevant indexes
if (ts.Indexes != null)
{
for (int i = 0; i < ts.Indexes.Count; i++)
{
string stmt = BuildCreateIndex(ts.TableName, ts.Indexes[i]);
sb.Append(stmt + ";\n");
}
}
string query = sb.ToString();
return(query);
}
public void SetForeignKeyMemberNameTest()
{
var thisTable = new TableSchema {
TableName = "Xxx",
};
var otherTable = new TableSchema {
TableName = "Zzz",
};
var key = new ForeignKeySchema
{
KeyName = "FK_Xxx_YyyZzz",
MemberName = "FK_Xxx_YyyZzz",
ThisColumns = new List <ColumnSchema>
{
new ColumnSchema {
MemberName = "XxxID", IsPrimaryKey = true
},
new ColumnSchema {
MemberName = "YyyZzzID"
},
},
OtherColumns = new List <ColumnSchema>
{
new ColumnSchema {
MemberName = "ZzzID"
},
},
ThisTable = thisTable,
OtherTable = otherTable,
};
var key1 = new ForeignKeySchema
{
KeyName = "FK_Xxx_Zzz",
MemberName = "FK_Xxx_Zzz",
ThisColumns = new List <ColumnSchema>
{
new ColumnSchema {
MemberName = "XxxID", IsPrimaryKey = true
},
new ColumnSchema {
MemberName = "ZzzID"
},
},
OtherColumns = new List <ColumnSchema>
{
new ColumnSchema {
MemberName = "ZzzID"
},
},
ThisTable = thisTable,
OtherTable = otherTable,
};
key.ThisTable.ForeignKeys = new List <ForeignKeySchema> {
key, key1
};
key.ThisTable.Columns = key.ThisColumns;
key.BackReference = new ForeignKeySchema
{
KeyName = key.KeyName + "_BackReference",
MemberName = key.MemberName + "_BackReference",
AssociationType = AssociationType.Auto,
OtherTable = key.ThisTable,
ThisColumns = key.OtherColumns,
OtherColumns = key.ThisColumns,
};
typeof(SchemaProviderBase).GetMethod("SetForeignKeyMemberName", System.Reflection.BindingFlags.Static | System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.NonPublic)
.Invoke(null, new object[] { new GetSchemaOptions {
}, key.ThisTable, key });
//SchemaProviderBase.SetForeignKeyMemberName(new GetSchemaOptions {}, key.ThisTable, key);
Assert.That(key.MemberName, Is.EqualTo("YyyZzz"));
}
public override DatabaseSchema GetSchema(DataConnection dataConnection, GetSchemaOptions options = null)
{
if (options is GetTablesSchemaOptions)
{
var opt = options as GetTablesSchemaOptions;
if (options == null)
{
opt = new GetTablesSchemaOptions();
}
IncludedSchemas = GetHashSet(options.IncludedSchemas, options.StringComparer);
ExcludedSchemas = GetHashSet(options.ExcludedSchemas, options.StringComparer);
IncludedCatalogs = GetHashSet(options.IncludedCatalogs, options.StringComparer);
ExcludedCatalogs = GetHashSet(options.ExcludedCatalogs, options.StringComparer);
var includedTables = GetHashSet(opt.IncludedTables, options.StringComparer);
var excludedTables = GetHashSet(opt.ExcludedTables, options.StringComparer);
GenerateChar1AsString = options.GenerateChar1AsString;
var dbConnection = (DbConnection)dataConnection.Connection;
InitProvider(dataConnection);
DataTypes = GetDataTypes(dataConnection);
DataTypesDic = new Dictionary <string, DataTypeInfo>(DataTypes.Count, StringComparer.OrdinalIgnoreCase);
foreach (var dt in DataTypes)
{
if (!DataTypesDic.ContainsKey(dt.TypeName))
{
DataTypesDic.Add(dt.TypeName, dt);
}
}
List <TableSchema> tables;
List <ProcedureSchema> procedures;
if (options.GetTables)
{
tables =
(
from t in GetTables(dataConnection)
where
(IncludedSchemas.Count == 0 || IncludedSchemas.Contains(t.SchemaName)) &&
(ExcludedSchemas.Count == 0 || !ExcludedSchemas.Contains(t.SchemaName)) &&
(IncludedCatalogs.Count == 0 || IncludedCatalogs.Contains(t.CatalogName)) &&
(ExcludedCatalogs.Count == 0 || !ExcludedCatalogs.Contains(t.CatalogName)) &&
(includedTables.Count == 0 || includedTables.Contains(t.TableName)) &&
(excludedTables.Count == 0 || !excludedTables.Contains(t.TableName))
select new TableSchema
{
ID = t.TableID,
CatalogName = t.CatalogName,
SchemaName = t.SchemaName,
TableName = t.TableName,
Description = t.Description,
IsDefaultSchema = t.IsDefaultSchema,
IsView = t.IsView,
TypeName = ToValidName(t.TableName),
Columns = new List <ColumnSchema>(),
ForeignKeys = new List <ForeignKeySchema>(),
IsProviderSpecific = t.IsProviderSpecific
}
).ToList();
var pks = GetPrimaryKeys(dataConnection);
#region Columns
var columns =
from c in GetColumns(dataConnection)
join pk in pks
on c.TableID + "." + c.Name equals pk.TableID + "." + pk.ColumnName into g2
from pk in g2.DefaultIfEmpty()
join t in tables on c.TableID equals t.ID
orderby c.Ordinal
select new { t, c, dt = GetDataType(c.DataType), pk };
foreach (var column in columns)
{
var dataType = column.c.DataType;
var systemType = GetSystemType(dataType, column.c.ColumnType, column.dt, column.c.Length, column.c.Precision, column.c.Scale);
var isNullable = column.c.IsNullable;
var columnType = column.c.ColumnType ?? GetDbType(dataType, column.dt, column.c.Length, column.c.Precision, column.c.Scale);
column.t.Columns.Add(new ColumnSchema
{
Table = column.t,
ColumnName = column.c.Name,
ColumnType = columnType,
IsNullable = isNullable,
MemberName = ToValidName(column.c.Name),
MemberType = ToTypeName(systemType, isNullable),
SystemType = systemType ?? typeof(object),
DataType = GetDataType(dataType, column.c.ColumnType, column.c.Length, column.c.Precision, column.c.Scale),
ProviderSpecificType = GetProviderSpecificType(dataType),
SkipOnInsert = column.c.SkipOnInsert || column.c.IsIdentity,
SkipOnUpdate = column.c.SkipOnUpdate || column.c.IsIdentity,
IsPrimaryKey = column.pk != null,
PrimaryKeyOrder = column.pk?.Ordinal ?? -1,
IsIdentity = column.c.IsIdentity,
Description = column.c.Description,
Length = column.c.Length,
Precision = column.c.Precision,
Scale = column.c.Scale,
});
}
#endregion
#region FK
var fks = GetForeignKeys(dataConnection);
foreach (var fk in fks.OrderBy(f => f.Ordinal))
{
var thisTable = (from t in tables where t.ID == fk.ThisTableID select t).FirstOrDefault();
var otherTable = (from t in tables where t.ID == fk.OtherTableID select t).FirstOrDefault();
if (thisTable == null || otherTable == null)
{
continue;
}
var thisColumn = (from c in thisTable.Columns where c.ColumnName == fk.ThisColumn select c).SingleOrDefault();
var otherColumn = (from c in otherTable.Columns where c.ColumnName == fk.OtherColumn select c).SingleOrDefault();
if (thisColumn == null || otherColumn == null)
{
continue;
}
var key = thisTable.ForeignKeys.FirstOrDefault(f => f.KeyName == fk.Name);
if (key == null)
{
key = new ForeignKeySchema
{
KeyName = fk.Name,
MemberName = ToValidName(fk.Name),
ThisTable = thisTable,
OtherTable = otherTable,
ThisColumns = new List <ColumnSchema>(),
OtherColumns = new List <ColumnSchema>(),
CanBeNull = true,
};
thisTable.ForeignKeys.Add(key);
}
key.ThisColumns.Add(thisColumn);
key.OtherColumns.Add(otherColumn);
}
#endregion
var pst = GetProviderSpecificTables(dataConnection);
if (pst != null)
{
tables.AddRange(pst);
}
}
else
{
tables = new List <TableSchema>();
}
if (options.GetProcedures)
{
#region Procedures
var sqlProvider = dataConnection.DataProvider.CreateSqlBuilder();
var procs = GetProcedures(dataConnection);
var procPparams = GetProcedureParameters(dataConnection);
var n = 0;
if (procs != null)
{
procedures =
(
from sp in procs
where
(IncludedSchemas.Count == 0 || IncludedSchemas.Contains(sp.SchemaName)) &&
(ExcludedSchemas.Count == 0 || !ExcludedSchemas.Contains(sp.SchemaName)) &&
(IncludedCatalogs.Count == 0 || IncludedCatalogs.Contains(sp.CatalogName)) &&
(ExcludedCatalogs.Count == 0 || !ExcludedCatalogs.Contains(sp.CatalogName))
join p in procPparams on sp.ProcedureID equals p.ProcedureID
into gr
select new ProcedureSchema
{
CatalogName = sp.CatalogName,
SchemaName = sp.SchemaName,
ProcedureName = sp.ProcedureName,
MemberName = ToValidName(sp.ProcedureName),
IsFunction = sp.IsFunction,
IsTableFunction = sp.IsTableFunction,
IsResultDynamic = sp.IsResultDynamic,
IsAggregateFunction = sp.IsAggregateFunction,
IsDefaultSchema = sp.IsDefaultSchema,
Parameters =
(
from pr in gr
join dt in DataTypes
on pr.DataType equals dt.TypeName into g1
from dt in g1.DefaultIfEmpty()
let systemType = GetSystemType(pr.DataType, null, dt, pr.Length, pr.Precision, pr.Scale)
orderby pr.Ordinal
select new ParameterSchema
{
SchemaName = pr.ParameterName,
SchemaType = GetDbType(pr.DataType, dt, pr.Length, pr.Precision, pr.Scale),
IsIn = pr.IsIn,
IsOut = pr.IsOut,
IsResult = pr.IsResult,
Size = pr.Length,
ParameterName = ToValidName(pr.ParameterName ?? "par" + ++n),
ParameterType = ToTypeName(systemType, true),
SystemType = systemType ?? typeof(object),
DataType = GetDataType(pr.DataType, null, pr.Length, pr.Precision, pr.Scale),
ProviderSpecificType = GetProviderSpecificType(pr.DataType),
}
).ToList()
} into ps
where ps.Parameters.All(p => p.SchemaType != "table type")
select ps
).ToList();
var current = 1;
var isActiveTransaction = dataConnection.Transaction != null;
if (GetProcedureSchemaExecutesProcedure && isActiveTransaction)
{
throw new LinqToDBException("Cannot read schema with GetSchemaOptions.GetProcedures = true from transaction. Remove transaction or set GetSchemaOptions.GetProcedures to false");
}
if (!isActiveTransaction)
{
dataConnection.BeginTransaction();
}
try
{
foreach (var procedure in procedures)
{
if (!procedure.IsResultDynamic && (!procedure.IsFunction || procedure.IsTableFunction) && options.LoadProcedure(procedure))
{
var commandText = sqlProvider.ConvertTableName(new System.Text.StringBuilder(),
procedure.CatalogName,
procedure.SchemaName,
procedure.ProcedureName).ToString();
LoadProcedureTableSchema(dataConnection, procedure, commandText, tables);
}
options.ProcedureLoadingProgress(procedures.Count, current++);
}
}
finally
{
if (!isActiveTransaction)
{
dataConnection.RollbackTransaction();
}
}
}
else
{
procedures = new List <ProcedureSchema>();
}
var psp = GetProviderSpecificProcedures(dataConnection);
if (psp != null)
{
procedures.AddRange(psp);
}
#endregion
}
else
{
procedures = new List <ProcedureSchema>();
}
return(ProcessSchema(new DatabaseSchema
{
DataSource = GetDataSourceName(dbConnection),
Database = GetDatabaseName(dbConnection),
ServerVersion = dbConnection.ServerVersion,
Tables = tables,
Procedures = procedures,
ProviderSpecificTypeNamespace = GetProviderSpecificTypeNamespace(),
DataTypesSchema = DataTypesSchema,
}, options));
}
return(base.GetSchema(dataConnection, options));
}
private static string MakeTriggerName(ForeignKeySchema fks, string prefix)
{
return(prefix + "_" + fks.TableName + "_" + fks.ColumnName + "_" + fks.ForeignTableName + "_" + fks.ForeignColumnName);
}
