1 //--------------------------------------------------------------------- 2 // <copyright file="EntityStoreSchemaGeneratorDatabaseSchemaLoader.cs" company="Microsoft"> 3 // Copyright (c) Microsoft Corporation. All rights reserved. 4 // </copyright> 5 // 6 // @owner Microsoft 7 // @backupOwner Microsoft 8 //--------------------------------------------------------------------- 9 using System.Collections.Generic; 10 using System.Globalization; 11 using System.Data.Common; 12 using System.Data.EntityClient; 13 using System.Data.Entity.Design.Common; 14 using System.Text; 15 using System.Diagnostics; 16 using System.Linq; 17 18 namespace System.Data.Entity.Design.SsdlGenerator 19 { 20 /// <summary> 21 /// Responsible for Loading Database Schema Information 22 /// </summary> 23 internal class EntityStoreSchemaGeneratorDatabaseSchemaLoader 24 { 25 private readonly EntityConnection _connection; 26 private readonly Version _storeSchemaModelVersion; 27 private readonly string _providerInvariantName; 28 EntityStoreSchemaGeneratorDatabaseSchemaLoader(string providerInvariantName, string connectionString)29 public EntityStoreSchemaGeneratorDatabaseSchemaLoader(string providerInvariantName, string connectionString) 30 { 31 Debug.Assert(providerInvariantName != null, "providerInvariantName parameter is null"); 32 Debug.Assert(connectionString != null, "connectionString parameter is null"); 33 _providerInvariantName = providerInvariantName; 34 _connection = CreateStoreSchemaConnection(providerInvariantName, connectionString, out _storeSchemaModelVersion); 35 } 36 CreateStoreSchemaConnection(string providerInvariantName, string connectionString, out Version storeSchemaModelVersion)37 private static EntityConnection CreateStoreSchemaConnection(string providerInvariantName, string connectionString, out Version storeSchemaModelVersion) 38 { 39 // We are going to try loading all versions of the store schema model starting from the newest. 40 // The first version of the model that was shipped with EntityFrameworkVersions.Version1 and EntityFrameworkVersions.Version2 is the last one 41 // we try, if it fails to load let the exception to propagate up to the caller. 42 foreach (var version in EntityFrameworkVersions.ValidVersions.Where(v => v > EntityFrameworkVersions.Version2).OrderByDescending(v => v)) 43 { 44 try 45 { 46 storeSchemaModelVersion = version; 47 return EntityStoreSchemaGenerator.CreateStoreSchemaConnection(providerInvariantName, connectionString, storeSchemaModelVersion); 48 } 49 catch (Exception e) 50 { 51 // Ignore the exception with the current version and try the next one. 52 if (!MetadataUtil.IsCatchableExceptionType(e)) 53 { 54 throw; 55 } 56 } 57 } 58 storeSchemaModelVersion = EntityFrameworkVersions.Version2; 59 return EntityStoreSchemaGenerator.CreateStoreSchemaConnection(providerInvariantName, connectionString, storeSchemaModelVersion); 60 } 61 Open()62 public void Open() 63 { 64 _connection.Open(); 65 } 66 Close()67 public void Close() 68 { 69 _connection.Close(); 70 } 71 72 public string ProviderInvariantName 73 { 74 get { return _providerInvariantName; } 75 } 76 77 public Version StoreSchemaModelVersion 78 { 79 get { return _storeSchemaModelVersion; } 80 } 81 LoadFunctionDetails(IEnumerable<EntityStoreSchemaFilterEntry> filters)82 public FunctionDetailsReader LoadFunctionDetails(IEnumerable<EntityStoreSchemaFilterEntry> filters) 83 { 84 return FunctionDetailsReader.Create(_connection, filters, _storeSchemaModelVersion); 85 } 86 LoadViewDetails(IEnumerable<EntityStoreSchemaFilterEntry> filters)87 public IEnumerable<DataRow> LoadViewDetails(IEnumerable<EntityStoreSchemaFilterEntry> filters) 88 { 89 TableDetailsCollection views = new TableDetailsCollection(); 90 return LoadDataTable( 91 ViewDetailSql, 92 rows => 93 rows 94 .OrderBy(r => r.Field<string>("SchemaName")) 95 .ThenBy(r=>r.Field<string>("TableName")) 96 .ThenBy(r=>r.Field<int>("Ordinal")), 97 views, 98 EntityStoreSchemaFilterObjectTypes.View, 99 filters, 100 ViewDetailAlias); 101 } 102 LoadTableDetails(IEnumerable<EntityStoreSchemaFilterEntry> filters)103 public IEnumerable<DataRow> LoadTableDetails(IEnumerable<EntityStoreSchemaFilterEntry> filters) 104 { 105 TableDetailsCollection table = new TableDetailsCollection(); 106 return LoadDataTable( 107 TableDetailSql, 108 rows => 109 rows 110 .OrderBy(r => r.Field<string>("SchemaName")) 111 .ThenBy(r => r.Field<string>("TableName")) 112 .ThenBy(r => r.Field<int>("Ordinal")), 113 table, 114 EntityStoreSchemaFilterObjectTypes.Table, 115 filters, 116 TableDetailAlias); 117 } 118 LoadFunctionReturnTableDetails(IEnumerable<EntityStoreSchemaFilterEntry> filters)119 public IEnumerable<DataRow> LoadFunctionReturnTableDetails(IEnumerable<EntityStoreSchemaFilterEntry> filters) 120 { 121 Debug.Assert(_storeSchemaModelVersion >= EntityFrameworkVersions.Version3, "_storeSchemaModelVersion >= EntityFrameworkVersions.Version3"); 122 TableDetailsCollection table = new TableDetailsCollection(); 123 return LoadDataTable( 124 FunctionReturnTableDetailSql, 125 rows => 126 rows 127 .OrderBy(r => r.Field<string>("SchemaName")) 128 .ThenBy(r => r.Field<string>("TableName")) 129 .ThenBy(r => r.Field<int>("Ordinal")), 130 table, 131 EntityStoreSchemaFilterObjectTypes.Function, 132 filters, 133 FunctionReturnTableDetailAlias); 134 } 135 LoadRelationships(IEnumerable<EntityStoreSchemaFilterEntry> filters)136 public IEnumerable<DataRow> LoadRelationships(IEnumerable<EntityStoreSchemaFilterEntry> filters) 137 { 138 RelationshipDetailsCollection table = new RelationshipDetailsCollection(); 139 return LoadDataTable( 140 RelationshipDetailSql, 141 rows => 142 rows 143 .OrderBy(r => r.Field<string>("RelationshipName")) 144 .ThenBy(r => r.Field<string>("RelationshipId")) 145 .ThenBy(r => r.Field<int>("Ordinal")), 146 table, 147 EntityStoreSchemaFilterObjectTypes.Table, 148 filters, 149 RelationshipDetailFromTableAlias, 150 RelationshipDetailToTableAlias); 151 } 152 153 /// <summary> 154 /// The underlying connection that this DbSchemaLoader class is using 155 /// This is used to get the provider manifest information only. 156 /// </summary> 157 public System.Data.Common.DbConnection InnerConnection 158 { 159 get { return _connection.StoreConnection; } 160 } 161 162 internal EntityConnection EntityConnection 163 { 164 get { return _connection; } 165 } 166 LoadDataTable(string sql, Func<IEnumerable<DataRow>, IEnumerable<DataRow>> orderByFunc, DataTable table, EntityStoreSchemaFilterObjectTypes queryTypes, IEnumerable<EntityStoreSchemaFilterEntry> filters, params string[] filterAliases)167 private IEnumerable<DataRow> LoadDataTable(string sql, Func<IEnumerable<DataRow>, IEnumerable<DataRow>> orderByFunc, DataTable table, EntityStoreSchemaFilterObjectTypes queryTypes, IEnumerable<EntityStoreSchemaFilterEntry> filters, params string[] filterAliases) 168 { 169 using (EntityCommand command = CreateFilteredCommand(_connection, sql, null, queryTypes, new List<EntityStoreSchemaFilterEntry>(filters), filterAliases)) 170 { 171 using (DbDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess)) 172 { 173 object[] values = new object[table.Columns.Count]; 174 while (reader.Read()) 175 { 176 reader.GetValues(values); 177 table.Rows.Add(values); 178 } 179 180 return orderByFunc(table.AsEnumerable()); 181 } 182 } 183 } 184 CreateFilteredCommand(EntityConnection connection, string sql, string orderByClause, EntityStoreSchemaFilterObjectTypes queryTypes, List<EntityStoreSchemaFilterEntry> filters, string[] filterAliases)185 internal static EntityCommand CreateFilteredCommand(EntityConnection connection, string sql, string orderByClause, EntityStoreSchemaFilterObjectTypes queryTypes, List<EntityStoreSchemaFilterEntry> filters, string[] filterAliases) 186 { 187 EntityCommand command = connection.CreateCommand(); 188 command.CommandType = CommandType.Text; 189 command.CommandTimeout = 0; 190 191 if (filters.Count == 0) 192 { 193 if (!string.IsNullOrEmpty(orderByClause)) 194 { 195 command.CommandText = sql + Environment.NewLine + orderByClause; 196 } 197 else 198 { 199 command.CommandText = sql; 200 } 201 return command; 202 } 203 204 StringBuilder whereClause = new StringBuilder(); 205 foreach (string alias in filterAliases) 206 { 207 StringBuilder allows = new StringBuilder(); 208 StringBuilder excludes = new StringBuilder(); 209 foreach (EntityStoreSchemaFilterEntry entry in filters) 210 { 211 // only apply filters of the correct type 212 if ((queryTypes & entry.Types) == 0) 213 { 214 continue; 215 } 216 217 218 if (entry.Effect == EntityStoreSchemaFilterEffect.Allow) 219 { 220 AddFilterEntry(command, allows, alias, entry); 221 } 222 else 223 { 224 Debug.Assert(entry.Effect == EntityStoreSchemaFilterEffect.Exclude, "did you add new value?"); 225 AddFilterEntry(command, excludes, alias, entry); 226 } 227 } 228 229 if (allows.Length != 0) 230 { 231 if (whereClause.Length != 0) 232 { 233 whereClause.Append(Environment.NewLine); 234 whereClause.Append("AND"); 235 whereClause.Append(Environment.NewLine); 236 } 237 whereClause.Append("("); 238 whereClause.Append(allows); 239 whereClause.Append(")"); 240 } 241 242 if (excludes.Length != 0) 243 { 244 if (whereClause.Length != 0) 245 { 246 whereClause.Append(Environment.NewLine); 247 whereClause.Append("AND"); 248 whereClause.Append(Environment.NewLine); 249 } 250 whereClause.Append("NOT ("); 251 whereClause.Append(excludes); 252 whereClause.Append(")"); 253 } 254 } 255 256 257 // did we end up with a where clause? 258 StringBuilder sqlStatement = new StringBuilder(sql); 259 if (whereClause.Length != 0) 260 { 261 sqlStatement.Append(Environment.NewLine); 262 sqlStatement.Append("WHERE"); 263 sqlStatement.Append(Environment.NewLine); 264 sqlStatement.Append(whereClause); 265 } 266 267 if (!string.IsNullOrEmpty(orderByClause)) 268 { 269 sqlStatement.Append(Environment.NewLine); 270 sqlStatement.Append(orderByClause); 271 } 272 273 command.CommandText = sqlStatement.ToString(); 274 275 return command; 276 } 277 AddFilterEntry(EntityCommand command, StringBuilder segment, string alias, EntityStoreSchemaFilterEntry entry)278 private static void AddFilterEntry(EntityCommand command, StringBuilder segment, string alias, EntityStoreSchemaFilterEntry entry) 279 { 280 281 StringBuilder filterText = new StringBuilder(); 282 AddComparison(command, filterText, alias, "CatalogName", entry.Catalog); 283 AddComparison(command, filterText, alias, "SchemaName", entry.Schema); 284 285 string name = entry.Name; 286 bool allNull = (entry.Catalog == null && entry.Schema == null && entry.Name == null); 287 if (allNull) 288 { 289 name = "%"; 290 } 291 AddComparison(command, filterText, alias, "Name", name); 292 293 if (segment.Length != 0) 294 { 295 segment.Append(" OR "); 296 } 297 segment.Append("("); 298 segment.Append(filterText); 299 segment.Append(")"); 300 } 301 AddComparison(EntityCommand command, StringBuilder segment, string alias, string propertyName, string value)302 private static void AddComparison(EntityCommand command, StringBuilder segment, string alias, string propertyName, string value) 303 { 304 if (value != null) 305 { 306 if (segment.Length != 0) 307 { 308 segment.Append(" AND "); 309 } 310 311 segment.Append(alias); 312 segment.Append("."); 313 segment.Append(propertyName); 314 segment.Append(" LIKE @"); 315 string parameterName = "p" + command.Parameters.Count.ToString(CultureInfo.InvariantCulture); 316 segment.Append(parameterName); 317 EntityParameter parameter = new EntityParameter(); 318 parameter.ParameterName = parameterName; 319 parameter.Value = value; 320 command.Parameters.Add(parameter); 321 } 322 } 323 324 private static readonly string ViewDetailAlias = "v"; 325 private static readonly string ViewDetailSql = @" 326 SELECT 327 v.CatalogName 328 , v.SchemaName 329 , v.Name 330 , v.ColumnName 331 , v.Ordinal 332 , v.IsNullable 333 , v.TypeName 334 , v.MaxLength 335 , v.Precision 336 , v.DateTimePrecision 337 , v.Scale 338 , v.IsIdentity 339 , v.IsStoreGenerated 340 , CASE WHEN pk.IsPrimaryKey IS NULL THEN false ELSE pk.IsPrimaryKey END as IsPrimaryKey 341 FROM ( 342 SELECT 343 v.CatalogName 344 , v.SchemaName 345 , v.Name 346 , c.Id as ColumnId 347 , c.Name as ColumnName 348 , c.Ordinal 349 , c.IsNullable 350 , c.ColumnType.TypeName as TypeName 351 , c.ColumnType.MaxLength as MaxLength 352 , c.ColumnType.Precision as Precision 353 , c.ColumnType.DateTimePrecision as DateTimePrecision 354 , c.ColumnType.Scale as Scale 355 , c.IsIdentity 356 , c.IsStoreGenerated 357 FROM 358 SchemaInformation.Views as v 359 cross apply 360 v.Columns as c ) as v 361 LEFT OUTER JOIN ( 362 SELECT 363 true as IsPrimaryKey 364 , pkc.Id 365 FROM 366 OfType(SchemaInformation.ViewConstraints, Store.PrimaryKeyConstraint) as pk 367 CROSS APPLY pk.Columns as pkc) as pk 368 ON v.ColumnId = pk.Id 369 "; 370 371 private static readonly string TableDetailAlias = "t"; 372 private static readonly string TableDetailSql = @" 373 SELECT 374 t.CatalogName 375 , t.SchemaName 376 , t.Name 377 , t.ColumnName 378 , t.Ordinal 379 , t.IsNullable 380 , t.TypeName 381 , t.MaxLength 382 , t.Precision 383 , t.DateTimePrecision 384 , t.Scale 385 , t.IsIdentity 386 , t.IsStoreGenerated 387 , CASE WHEN pk.IsPrimaryKey IS NULL THEN false ELSE pk.IsPrimaryKey END as IsPrimaryKey 388 FROM ( 389 SELECT 390 t.CatalogName 391 , t.SchemaName 392 , t.Name 393 , c.Id as ColumnId 394 , c.Name as ColumnName 395 , c.Ordinal 396 , c.IsNullable 397 , c.ColumnType.TypeName as TypeName 398 , c.ColumnType.MaxLength as MaxLength 399 , c.ColumnType.Precision as Precision 400 , c.ColumnType.DateTimePrecision as DateTimePrecision 401 , c.ColumnType.Scale as Scale 402 , c.IsIdentity 403 , c.IsStoreGenerated 404 FROM 405 SchemaInformation.Tables as t 406 cross apply 407 t.Columns as c ) as t 408 LEFT OUTER JOIN ( 409 SELECT 410 true as IsPrimaryKey 411 , pkc.Id 412 FROM 413 OfType(SchemaInformation.TableConstraints, Store.PrimaryKeyConstraint) as pk 414 CROSS APPLY pk.Columns as pkc) as pk 415 ON t.ColumnId = pk.Id 416 "; 417 418 private static readonly string FunctionReturnTableDetailAlias = "tvf"; 419 private static readonly string FunctionReturnTableDetailSql = @" 420 SELECT 421 tvf.CatalogName 422 , tvf.SchemaName 423 , tvf.Name 424 , tvf.ColumnName 425 , tvf.Ordinal 426 , tvf.IsNullable 427 , tvf.TypeName 428 , tvf.MaxLength 429 , tvf.Precision 430 , tvf.DateTimePrecision 431 , tvf.Scale 432 , false as IsIdentity 433 , false as IsStoreGenerated 434 , false as IsPrimaryKey 435 FROM ( 436 SELECT 437 t.CatalogName 438 , t.SchemaName 439 , t.Name 440 , c.Id as ColumnId 441 , c.Name as ColumnName 442 , c.Ordinal 443 , c.IsNullable 444 , c.ColumnType.TypeName as TypeName 445 , c.ColumnType.MaxLength as MaxLength 446 , c.ColumnType.Precision as Precision 447 , c.ColumnType.DateTimePrecision as DateTimePrecision 448 , c.ColumnType.Scale as Scale 449 FROM 450 OfType(SchemaInformation.Functions, Store.TableValuedFunction) as t 451 cross apply 452 t.Columns as c ) as tvf 453 "; 454 455 private static readonly string RelationshipDetailFromTableAlias = "r.FromTable"; 456 private static readonly string RelationshipDetailToTableAlias = "r.ToTable"; 457 private static readonly string RelationshipDetailSql = @" 458 SELECT 459 r.ToTable.CatalogName as ToTableCatalog 460 , r.ToTable.SchemaName as ToTableSchema 461 , r.ToTable.Name as ToTableName 462 , r.ToColumnName 463 , r.FromTable.CatalogName as FromTableCatalog 464 , r.FromTable.SchemaName as FromTableSchema 465 , r.FromTable.Name as FromTableName 466 , r.FromColumnName 467 , r.Ordinal 468 , r.RelationshipName 469 , r.RelationshipId 470 , r.IsCascadeDelete 471 FROM ( 472 SELECT 473 fks.ToColumn.Parent as ToTable 474 , fks.ToColumn.Name as ToColumnName 475 , c.Parent as FromTable 476 , fks.FromColumn.Name as FromColumnName 477 , fks.Ordinal as Ordinal 478 , c.Name as RelationshipName 479 , c.Id as RelationshipId 480 , c.DeleteRule = 'CASCADE' as IsCascadeDelete 481 FROM 482 OfType(SchemaInformation.TableConstraints, Store.ForeignKeyConstraint) as c, 483 ( SELECT 484 Ref(fk.Constraint) as cRef 485 , fk.ToColumn 486 , fk.FromColumn 487 , fk.Ordinal 488 FROM 489 c.ForeignKeys as fk) as fks 490 WHERE fks.cRef = Ref(c)) as r 491 "; 492 } 493 } 494