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