1 #region MIT license
2 //
3 // MIT license
4 //
5 // Copyright (c) 2009 Novell, Inc.
6 //
7 // Permission is hereby granted, free of charge, to any person obtaining a copy
8 // of this software and associated documentation files (the "Software"), to deal
9 // in the Software without restriction, including without limitation the rights
10 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
11 // copies of the Software, and to permit persons to whom the Software is
12 // furnished to do so, subject to the following conditions:
13 //
14 // The above copyright notice and this permission notice shall be included in
15 // all copies or substantial portions of the Software.
16 //
17 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
18 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
19 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
20 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
21 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
22 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
23 // THE SOFTWARE.
24 //
25 #endregion
26 
27 using System;
28 using System.Data;
29 using System.Data.Common;
30 using System.Linq;
31 
32 using DbLinq.Vendor;
33 
34 namespace DbLinq.SqlServer
35 {
36 #if !MONO_STRICT
37     public
38 #endif
39     class SqlServerSchemaLoader : DbSchemaLoader
40     {
41         readonly IVendor vendor = new SqlServerVendor();
42         public override IVendor Vendor {
43             get {return vendor;}
44             set {}
45         }
46 
GetForeignKeys(DbConnection connection)47         protected override DataTable GetForeignKeys(DbConnection connection)
48         {
49             var t = new DataTable("ForeignKeys");
50             using (var c = connection.CreateCommand())
51             {
52                 c.CommandText = @"
53 SELECT
54     rc.CONSTRAINT_NAME      AS 'CONSTRAINT_NAME',
55     'FOREIGN KEY'           AS 'CONSTRAINT_TYPE',
56     rcu_from.TABLE_NAME     AS 'TABLE_NAME',
57     rcu_from.TABLE_SCHEMA   AS 'TABLE_SCHEMA',
58     rcu_from.COLUMN_NAME    AS 'FKEY_FROM_COLUMN',
59     rcu_to.TABLE_SCHEMA     AS 'FKEY_TO_SCHEMA',
60     rcu_to.TABLE_NAME       AS 'FKEY_TO_TABLE',
61     rcu_to.COLUMN_NAME      AS 'FKEY_TO_COLUMN'
62 FROM
63     INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
64 INNER JOIN
65     INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rcu_from ON
66         rc.CONSTRAINT_CATALOG   = rcu_from.CONSTRAINT_CATALOG AND
67         rc.CONSTRAINT_NAME      = rcu_from.CONSTRAINT_NAME
68 INNER JOIN
69     INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rcu_to ON
70         rc.UNIQUE_CONSTRAINT_CATALOG  = rcu_to.CONSTRAINT_CATALOG AND
71         rc.UNIQUE_CONSTRAINT_NAME     = rcu_to.CONSTRAINT_NAME
72 ";
73                 using (var r = c.ExecuteReader())
74                     t.Load(r);
75             }
76             return t;
77         }
78 
GetColumns(DbConnection connection)79         protected override DataTable GetColumns(DbConnection connection)
80         {
81             var t = new DataTable("Columns");
82             using (var c = connection.CreateCommand())
83             {
84                 c.CommandText = @"
85 SELECT
86     columns.TABLE_CATALOG,
87     columns.TABLE_SCHEMA,
88     columns.TABLE_NAME,
89     columns.COLUMN_NAME,
90     columns.ORDINAL_POSITION,
91     columns.COLUMN_DEFAULT,
92     (SELECT CAST (COUNT(*) AS BIT)
93      FROM
94         INFORMATION_SCHEMA.TABLE_CONSTRAINTS rc
95      INNER JOIN
96         INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rcu ON
97             rc.CONSTRAINT_CATALOG   = rcu.CONSTRAINT_CATALOG AND
98             rc.CONSTRAINT_NAME      = rcu.CONSTRAINT_NAME
99      WHERE
100 		rc.CONSTRAINT_TYPE  = 'PRIMARY KEY' AND
101 		rc.TABLE_CATALOG    = columns.TABLE_CATALOG AND
102 		rc.TABLE_SCHEMA     = columns.TABLE_SCHEMA AND
103 		rc.TABLE_NAME       = columns.TABLE_NAME AND
104 		rcu.COLUMN_NAME     = columns.COLUMN_NAME
105     ) AS 'PRIMARY_KEY',
106     columns.IS_NULLABLE,
107     columns.DATA_TYPE,
108     columns.CHARACTER_MAXIMUM_LENGTH,
109     columns.CHARACTER_OCTET_LENGTH,
110     CAST(columns.NUMERIC_PRECISION AS INT) AS 'NUMERIC_PRECISION',
111     columns.NUMERIC_PRECISION_RADIX,
112     columns.NUMERIC_SCALE,
113     columns.DATETIME_PRECISION,
114     columns.CHARACTER_SET_CATALOG,
115     columns.CHARACTER_SET_SCHEMA,
116     columns.CHARACTER_SET_NAME,
117     columns.COLLATION_CATALOG
118 FROM
119     INFORMATION_SCHEMA.COLUMNS columns
120 ORDER BY
121     columns.TABLE_CATALOG, columns.TABLE_SCHEMA,
122     columns.TABLE_NAME, columns.COLUMN_NAME
123 ";
124                 using (var r = c.ExecuteReader())
125                     t.Load(r);
126             }
127             return t;
128         }
129     }
130 }
131