|
0
|
1 <#
|
|
|
2 ConnectionType = "MySql.Data.MySqlClient.MySqlConnection, MySql.Data";
|
|
|
3 #><#+
|
|
|
4
|
|
|
5 private void LoadServerMetadata()
|
|
|
6 {
|
|
|
7 var tables = CreateList(new { ID = "", Table = new Table() });
|
|
|
8 var columns = CreateList(new { ID = "", Column = new Column() });
|
|
|
9
|
|
|
10 using (var conn = GetConnection())
|
|
|
11 using (var cmd = conn.CreateCommand())
|
|
|
12 {
|
|
|
13 string schemasToSkip = "'information_schema', 'cr_debug', 'mysql'";
|
|
|
14
|
|
|
15 // Load tables & views.
|
|
|
16 //
|
|
|
17
|
|
|
18 cmd.CommandText = "SELECT DATABASE()";
|
|
|
19 _databaseName = Convert.ToString(cmd.ExecuteScalar());
|
|
|
20
|
|
|
21 cmd.CommandText = string.Format(@"
|
|
|
22 SELECT
|
|
|
23 CONCAT(TABLE_SCHEMA, '.', TABLE_NAME),
|
|
|
24 TABLE_SCHEMA,
|
|
|
25 TABLE_NAME,
|
|
|
26 TABLE_TYPE
|
|
|
27 FROM
|
|
|
28 INFORMATION_SCHEMA.tables
|
|
|
29 WHERE
|
|
|
30 TABLE_SCHEMA NOT IN ({0}) AND
|
|
|
31 TABLE_TYPE IN ('BASE TABLE', 'VIEW')
|
|
|
32 {1}",
|
|
|
33 schemasToSkip,
|
|
|
34 GetDatabaseSqlFilter(""));
|
|
|
35
|
|
|
36 using (var rd = cmd.ExecuteReader())
|
|
|
37 {
|
|
|
38 while (rd.Read())
|
|
|
39 {
|
|
|
40 var t = new
|
|
|
41 {
|
|
|
42 ID = Convert.ToString(rd[0]),
|
|
|
43 Table = new Table
|
|
|
44 {
|
|
|
45 Owner = null, // there is no concept of table owner in MySql
|
|
|
46 TableName = rd[2].ToString(),
|
|
|
47 ClassName = rd[2].ToString(),
|
|
|
48 DataContextPropertyName = rd[2].ToString(),
|
|
|
49 IsView = rd[3].ToString() == "VIEW",
|
|
|
50 BaseClassName = BaseEntityClass,
|
|
|
51 }
|
|
|
52 };
|
|
|
53
|
|
|
54 tables.Add(t);
|
|
|
55 }
|
|
|
56 }
|
|
|
57
|
|
|
58 cmd.CommandText = "SELECT @@session.sql_mode";
|
|
|
59 string sqlMode = Convert.ToString(cmd.ExecuteScalar());
|
|
|
60
|
|
|
61 // Load columns.
|
|
|
62 //
|
|
|
63 cmd.CommandText = string.Format(@"
|
|
|
64 SELECT
|
|
|
65 CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) as id,
|
|
|
66 CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END as isNullable,
|
|
|
67 ORDINAL_POSITION as colid,
|
|
|
68 COLUMN_NAME as name,
|
|
|
69 c.DATA_TYPE as dataType,
|
|
|
70 CHARACTER_MAXIMUM_LENGTH as length,
|
|
|
71 NUMERIC_PRECISION as prec,
|
|
|
72 NUMERIC_SCALE as scale,
|
|
|
73 EXTRA = 'auto_increment' as isIdentity,
|
|
|
74 c.COLUMN_TYPE as columnType,
|
|
|
75 COLUMN_DEFAULT as columnDefault,
|
|
|
76 EXTRA as extra
|
|
|
77 FROM
|
|
|
78 INFORMATION_SCHEMA.COLUMNS c
|
|
|
79 WHERE
|
|
|
80 TABLE_SCHEMA NOT IN ({0})
|
|
|
81 {1}",
|
|
|
82 schemasToSkip,
|
|
|
83 GetDatabaseSqlFilter(""));
|
|
|
84
|
|
|
85 using (var rd = cmd.ExecuteReader())
|
|
|
86 {
|
|
|
87 while (rd.Read())
|
|
|
88 {
|
|
|
89 var col = new
|
|
|
90 {
|
|
|
91 ID = Convert.ToString(rd["id"]),
|
|
|
92 Column = new Column
|
|
|
93 {
|
|
|
94 ID = Convert.ToInt16 (rd["colid"]),
|
|
|
95 ColumnName = Convert.ToString (rd["name"]),
|
|
|
96 MemberName = Convert.ToString (rd["name"]),
|
|
|
97 ColumnType = Convert.ToString (rd["dataType"]),
|
|
|
98 IsNullable = Convert.ToBoolean(rd["isNullable"]),
|
|
|
99 IsIdentity = Convert.ToBoolean(rd["isIdentity"]),
|
|
|
100 Length = rd.IsDBNull(rd.GetOrdinal("length")) ? 0 : Convert.ToInt64(rd["length"]),
|
|
|
101 Precision = rd.IsDBNull(rd.GetOrdinal("prec")) ? 0 : Convert.ToInt32(rd["prec"]),
|
|
|
102 Scale = rd.IsDBNull(rd.GetOrdinal("scale")) ? 0 : Convert.ToInt32(rd["scale"]),
|
|
|
103 ColumnDefault = Convert.ToString(rd["columnDefault"]),
|
|
|
104 Extra = Convert.ToString(rd["extra"]).ToUpper()
|
|
|
105 }
|
|
|
106 };
|
|
|
107
|
|
|
108 var c = col.Column;
|
|
|
109 var columnType = Convert.ToString(rd["columnType"]).ToLower();
|
|
|
110 var unsigned = columnType.Contains("unsigned");
|
|
|
111 var realAsFloat = sqlMode.ToLower().Contains("real_as_float");
|
|
|
112
|
|
|
113 switch (c.ColumnType)
|
|
|
114 {
|
|
|
115 case "longtext" :
|
|
|
116 case "mediumtext" :
|
|
|
117 case "tinytext" :
|
|
|
118 case "text" : c.Type = "string"; c.DbType = DbType.String; c.SqlDbType = SqlDbType.Text; break;
|
|
|
119 case "binary" : c.Type = "byte[]"; c.DbType = DbType.Binary; c.SqlDbType = SqlDbType.Binary; break;
|
|
|
120 case "date" : c.Type = "DateTime"; c.DbType = DbType.Date; c.SqlDbType = SqlDbType.Date; break;
|
|
|
121 case "time" : c.Type = "DateTime"; c.DbType = DbType.Time; c.SqlDbType = SqlDbType.Time; break;
|
|
|
122 case "bit" : c.Type = "bool"; c.DbType = DbType.Boolean; c.SqlDbType = SqlDbType.Bit; break;
|
|
|
123 case "numeric" :
|
|
|
124 case "decimal" :
|
|
|
125 case "dec" :
|
|
|
126 case "fixed" : c.Type = "decimal"; c.DbType = DbType.Decimal; c.SqlDbType = SqlDbType.Decimal; break;
|
|
|
127 case "datetime" : c.Type = "DateTime"; c.DbType = DbType.DateTime; c.SqlDbType = SqlDbType.DateTime; break;
|
|
|
128 case "float" : c.Type = "float"; c.DbType = DbType.Single; c.SqlDbType = SqlDbType.Float; break;
|
|
|
129 case "double" : c.Type = "double"; c.DbType = DbType.Double; c.SqlDbType = SqlDbType.Float; break;
|
|
|
130 case "varbinary" : c.Type = "byte[]"; c.DbType = DbType.Binary; c.SqlDbType = SqlDbType.VarBinary; break;
|
|
|
131 case "varchar" : c.Type = "string"; c.DbType = DbType.String; c.SqlDbType = SqlDbType.VarChar; break;
|
|
|
132 case "year" : c.Type = "DateTime"; c.DbType = DbType.Date; c.SqlDbType = SqlDbType.Date; break;
|
|
|
133 case "enum" :
|
|
|
134 case "set" : c.Type = "string"; c.DbType = DbType.String; c.SqlDbType = SqlDbType.VarChar; break;
|
|
|
135 case "bool" :
|
|
|
136 case "boolean" : c.Type = "bool"; c.DbType = DbType.Boolean; c.SqlDbType = SqlDbType.Bit; break;
|
|
|
137 case "serial" : c.Type = "ulong"; c.DbType = DbType.UInt64; c.SqlDbType = SqlDbType.BigInt; break;
|
|
|
138 case "mediumblob" :
|
|
|
139 case "longblob" :
|
|
|
140 case "blob" : c.Type = "byte[]"; c.DbType = DbType.Binary; c.SqlDbType = SqlDbType.Image; break;
|
|
|
141 case "tinyblob" : c.Type = "byte[]"; c.DbType = DbType.Binary; c.SqlDbType = SqlDbType.Binary; break;
|
|
|
142
|
|
|
143 case "smallint" : c.Type = unsigned ? "ushort" : "short"; c.DbType = unsigned ? DbType.UInt16 : DbType.Int16; c.SqlDbType = SqlDbType.SmallInt; break;
|
|
|
144 case "mediumint" :
|
|
|
145 case "int" :
|
|
|
146 case "integer" : c.Type = unsigned ? "uint" : "int"; c.DbType = unsigned ? DbType.UInt32 : DbType.Int32; c.SqlDbType = SqlDbType.Int; break;
|
|
|
147 case "real" : c.Type = realAsFloat ? "float" : "double"; c.DbType = realAsFloat ? DbType.Single : DbType.Double; c.SqlDbType = SqlDbType.Real; break;
|
|
|
148 case "bigint" : c.Type = unsigned ? "ulong" : "long"; c.DbType = unsigned ? DbType.UInt64 : DbType.Int64; c.SqlDbType = SqlDbType.BigInt; break;
|
|
|
149 case "char" : c.Type = "string"; c.DbType = DbType.StringFixedLength; c.SqlDbType = SqlDbType.Char; break;
|
|
|
150 case "timestamp" :
|
|
|
151 c.Type = "DateTime";
|
|
|
152 c.DbType = DbType.DateTime;
|
|
|
153 c.SqlDbType = SqlDbType.Timestamp;
|
|
|
154
|
|
|
155 if(c.ColumnDefault == "CURRENT_TIMESTAMP" || c.Extra.Contains("ON UPDATE CURRENT_TIMESTAMP"))
|
|
|
156 {
|
|
|
157 c.Attributes.Add(string.Format(
|
|
|
158 "NonUpdatable(OnInsert = {0}, OnUpdate = {1})",
|
|
|
159 c.ColumnDefault == "CURRENT_TIMESTAMP" ? "true" : "false",
|
|
|
160 c.Extra.Contains("ON UPDATE CURRENT_TIMESTAMP") ? "true" : "false"));
|
|
|
161 }
|
|
|
162 break;
|
|
|
163 case "tinyint" :
|
|
|
164 if(columnType == "tinyint(1)")
|
|
|
165 {
|
|
|
166 c.Type = "bool";
|
|
|
167 c.DbType = DbType.Boolean;
|
|
|
168 c.SqlDbType = SqlDbType.Bit;
|
|
|
169 }
|
|
|
170 else
|
|
|
171 {
|
|
|
172 c.Type = unsigned ? "byte" : "sbyte";
|
|
|
173 c.DbType = unsigned ? DbType.Byte : DbType.SByte;
|
|
|
174 c.SqlDbType = SqlDbType.TinyInt;
|
|
|
175 }
|
|
|
176 break;
|
|
|
177 default :
|
|
|
178 throw new System.IO.InvalidDataException(string.Format("Unknown column type: {0}.", c.ColumnType));
|
|
|
179 }
|
|
|
180
|
|
|
181 switch (c.Type)
|
|
|
182 {
|
|
|
183 case "string" :
|
|
|
184 case "byte[]" : c.IsClass = true; break;
|
|
|
185 }
|
|
|
186
|
|
|
187 if (c.IsNullable && !c.IsClass)
|
|
|
188 c.Type += "?";
|
|
|
189
|
|
|
190 columns.Add(col);
|
|
|
191 }
|
|
|
192 }
|
|
|
193
|
|
|
194 // Load PKs.
|
|
|
195 //
|
|
|
196 cmd.CommandText = string.Format(@"
|
|
|
197 SELECT
|
|
|
198 CONCAT(k.TABLE_SCHEMA, '.', k.TABLE_NAME) as id,
|
|
|
199 k.CONSTRAINT_NAME as name,
|
|
|
200 k.COLUMN_NAME as colname,
|
|
|
201 k.ORDINAL_POSITION as colid
|
|
|
202 FROM
|
|
|
203 INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
|
|
|
204 JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c ON k.CONSTRAINT_NAME = c.CONSTRAINT_NAME
|
|
|
205 WHERE
|
|
|
206 c.CONSTRAINT_TYPE='PRIMARY KEY' AND
|
|
|
207 k.TABLE_SCHEMA NOT IN ({0})
|
|
|
208 {1}
|
|
|
209 GROUP BY id, colid",
|
|
|
210 schemasToSkip,
|
|
|
211 GetDatabaseSqlFilter("k"));
|
|
|
212
|
|
|
213 using (var rd = cmd.ExecuteReader())
|
|
|
214 {
|
|
|
215 while (rd.Read())
|
|
|
216 {
|
|
|
217 var id = Convert.ToString(rd["id"]);
|
|
|
218 var colid = Convert.ToInt32 (rd["colid"]);
|
|
|
219 var colname = Convert.ToString(rd["colname"]);
|
|
|
220
|
|
|
221 columns.Single(_ => _.ID == id && _.Column.ColumnName == colname).Column.PKIndex = colid;
|
|
|
222 }
|
|
|
223 }
|
|
|
224
|
|
|
225 // Load FKs.
|
|
|
226 //
|
|
|
227 cmd.CommandText = string.Format(@"
|
|
|
228 SELECT
|
|
|
229 CONSTRAINT_NAME as Name,
|
|
|
230 CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) as ThisTable,
|
|
|
231 COLUMN_NAME as ThisColumn,
|
|
|
232 CONCAT(REFERENCED_TABLE_SCHEMA, '.', REFERENCED_TABLE_NAME) as OtherTable,
|
|
|
233 REFERENCED_COLUMN_NAME as OtherColumn,
|
|
|
234 ORDINAL_POSITION as Ordinal
|
|
|
235 FROM
|
|
|
236 INFORMATION_SCHEMA.key_column_usage
|
|
|
237 WHERE
|
|
|
238 TABLE_SCHEMA NOT IN ({0}) AND
|
|
|
239 REFERENCED_TABLE_NAME IS NOT NULL AND
|
|
|
240 REFERENCED_COLUMN_NAME IS NOT NULL
|
|
|
241 {1}
|
|
|
242 ORDER BY
|
|
|
243 ThisTable,
|
|
|
244 Ordinal",
|
|
|
245 schemasToSkip,
|
|
|
246 GetDatabaseSqlFilter(""));
|
|
|
247
|
|
|
248 if(!string.IsNullOrEmpty(DatabaseName))
|
|
|
249 cmd.CommandText += string.Format(" AND TABLE_SCHEMA = '{0}' ", DatabaseName);
|
|
|
250
|
|
|
251 using (var rd = cmd.ExecuteReader())
|
|
|
252 {
|
|
|
253 while (rd.Read())
|
|
|
254 {
|
|
|
255 var name = Convert.ToString(rd["Name"]);
|
|
|
256 var thisTableID = Convert.ToString(rd["ThisTable"]);
|
|
|
257 var otherTableID = Convert.ToString(rd["OtherTable"]);
|
|
|
258 var thisColumnName = Convert.ToString(rd["ThisColumn"]);
|
|
|
259 var otherColumnName = Convert.ToString(rd["OtherColumn"]);
|
|
|
260
|
|
|
261 var thisTable = (from t in tables where t.ID == thisTableID select t.Table).Single();
|
|
|
262 var otherTable = (from t in tables where t.ID == otherTableID select t.Table).Single();
|
|
|
263 var thisColumn = (from c in columns where c.ID == thisTableID && c.Column.ColumnName == thisColumnName select c.Column).Single();
|
|
|
264 var otherColumn = (from c in columns where c.ID == otherTableID && c.Column.ColumnName == otherColumnName select c.Column).Single();
|
|
|
265
|
|
|
266 if (thisTable.ForeignKeys.ContainsKey(name) == false)
|
|
|
267 thisTable.ForeignKeys.Add(name, new ForeignKey { KeyName = name, MemberName = name, OtherTable = otherTable });
|
|
|
268
|
|
|
269 var key = thisTable.ForeignKeys[name];
|
|
|
270
|
|
|
271 key.ThisColumns. Add(thisColumn);
|
|
|
272 key.OtherColumns.Add(otherColumn);
|
|
|
273 }
|
|
|
274 }
|
|
|
275 }
|
|
|
276
|
|
|
277 var qc =
|
|
|
278 from c in columns
|
|
|
279 group c by c.ID into gr
|
|
|
280 join t in tables on gr.Key equals t.ID
|
|
|
281 select new { t.Table, gr };
|
|
|
282
|
|
|
283 foreach (var c in qc)
|
|
|
284 {
|
|
|
285 foreach (var col in from col in c.gr orderby col.Column.ID select col.Column)
|
|
|
286 c.Table.Columns.Add(col.ColumnName, col);
|
|
|
287
|
|
|
288 // there is no concept of table owner in MySql so we just use table name
|
|
|
289 Tables.Add(c.Table.TableName, c.Table);
|
|
|
290 }
|
|
|
291 }
|
|
|
292
|
|
|
293 private string _databaseName;
|
|
|
294 private string GetDatabaseSqlFilter(string optionalTablePrefix)
|
|
|
295 {
|
|
|
296 if(string.IsNullOrEmpty(_databaseName)) return "";
|
|
|
297 string tablePrefix = string.IsNullOrEmpty(optionalTablePrefix) ? "" : optionalTablePrefix + ".";
|
|
|
298 return string.Format(" AND {0}TABLE_SCHEMA = '{1}' ", tablePrefix, _databaseName);
|
|
|
299 }
|
|
|
300
|
|
|
301 public partial class Column
|
|
|
302 {
|
|
|
303 public string ColumnDefault;
|
|
|
304 public string Extra;
|
|
|
305 }
|
|
|
306
|
|
|
307 #> |
