Mercurial > pub > bltoolkit

comparison Source/Templates/Sybase.ttinclude @ 0:f990fcb411a9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Копия текущей версии из github
author cin
date Thu, 27 Mar 2014 21:46:09 +0400
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:f990fcb411a9
1 <#
2 ConnectionType = "Sybase.Data.AseClient.AseConnection, Sybase.AdoNet2.AseClient";
3 #><#+
4
5 bool GenerateSybaseSystemTables = false;
6
7 private void LoadServerMetadata()
8 {
9 var tables = CreateList(new { ID = 0, Table = new Table() });
10 var columns = CreateList(new { ID = 0, Column = new Column() });
11
12 using (var conn = GetConnection())
13 using (var cmd = conn.CreateCommand())
14 {
15 // Load tables & vies.
16 //
17 cmd.CommandText = @"
18 SELECT
19 id,
20 USER_NAME(uid),
21 name,
22 type
23 FROM
24 sysobjects
25 WHERE
26 type IN ('U','V')";
27
28 using (var rd = cmd.ExecuteReader())
29 {
30 while (rd.Read())
31 {
32 var t = new
33 {
34 ID = Convert.ToInt32(rd[0]),
35 Table = new Table
36 {
37 Owner = rd[1].ToString(),
38 TableName = rd[2].ToString(),
39 ClassName = rd[2].ToString(),
40 IsView = rd[3].ToString()[0] == 'V',
41 BaseClassName = BaseEntityClass,
42 }
43 };
44
45 tables.Add(t);
46 }
47 }
48
49 // Load columns.
50 //
51 cmd.CommandText = @"
52 SELECT
53 o.id,
54 c.colid,
55 c.name,
56 c.status,
57 c.usertype,
58 t.type,
59 c.length,
60 c.prec,
61 c.scale,
62 t.name as typename,
63 Convert(bit, c.status & 0x08) isNullable,
64 Convert(bit, c.status & 0x80) isIdentity
65 FROM
66 syscolumns c
67 JOIN sysobjects o ON c.id = o.id
68 JOIN systypes t ON c.usertype = t.usertype
69 WHERE
70 o.type IN ('U','V')";
71
72 using (var rd = cmd.ExecuteReader())
73 {
74 while (rd.Read())
75 {
76 var col = new
77 {
78 ID = Convert.ToInt32(rd["id"]),
79 Column = new Column
80 {
81 ID = Convert.ToInt16 (rd["colid"]),
82 ColumnName = Convert.ToString (rd["name"]),
83 MemberName = Convert.ToString (rd["name"]),
84 ColumnType = Convert.ToString (rd["typename"]),
85 IsNullable = Convert.ToBoolean(rd["isNullable"]),
86 IsIdentity = Convert.ToBoolean(rd["isIdentity"]),
87 Length = rd.IsDBNull(rd.GetOrdinal("length")) ? 0 : Convert.ToInt64(rd["length"]),
88 Precision = rd.IsDBNull(rd.GetOrdinal("prec")) ? 0 : Convert.ToInt32(rd["prec"]),
89 Scale = rd.IsDBNull(rd.GetOrdinal("scale")) ? 0 : Convert.ToInt32(rd["scale"]),
90 }
91 };
92
93 var c = col.Column;
94
95 switch (Convert.ToInt32(rd["type"]))
96 {
97 case 34 /* image */ : c.Type = "byte[]"; c.DbType = DbType.Binary; c.SqlDbType = SqlDbType.Image; break;
98 case 35 /* text */ : c.Type = "string"; c.DbType = DbType.String; c.SqlDbType = SqlDbType.Text; break;
99 case 45 /* binary */ : c.Type = "byte[]"; c.DbType = DbType.Binary; c.SqlDbType = SqlDbType.Binary; break;
100 case 48 /* tinyint */ : c.Type = "byte"; c.DbType = DbType.Byte; c.SqlDbType = SqlDbType.TinyInt; break;
101 case 49 /* date */ : c.Type = "DateTime"; c.DbType = DbType.Date; c.SqlDbType = SqlDbType.Date; break;
102 case 50 /* bit */ : c.Type = "bool"; c.DbType = DbType.Boolean; c.SqlDbType = SqlDbType.Bit; break;
103 case 51 /* time */ : c.Type = "DateTime"; c.DbType = DbType.Time; c.SqlDbType = SqlDbType.Time; break;
104 case 52 /* smallint */ : c.Type = "short"; c.DbType = DbType.Int16; c.SqlDbType = SqlDbType.SmallInt; break;
105 case 55 /* decimal */ : c.Type = "decimal"; c.DbType = DbType.Decimal; c.SqlDbType = SqlDbType.Decimal; break;
106 case 56 /* int */ : c.Type = "int"; c.DbType = DbType.Int32; c.SqlDbType = SqlDbType.Int; break;
107 case 58 /* smalldatetime */ : c.Type = "DateTime"; c.DbType = DbType.DateTime; c.SqlDbType = SqlDbType.SmallDateTime; break;
108 case 59 /* real */ : c.Type = "float"; c.DbType = DbType.Single; c.SqlDbType = SqlDbType.Real; break;
109 case 60 /* money */ : c.Type = "decimal"; c.DbType = DbType.Currency; c.SqlDbType = SqlDbType.Money; break;
110 case 61 /* datetime */ : c.Type = "DateTime"; c.DbType = DbType.DateTime; c.SqlDbType = SqlDbType.DateTime; break;
111 case 62 /* float */ : c.Type = "double"; c.DbType = DbType.Double; c.SqlDbType = SqlDbType.Float; break;
112 case 63 /* numeric */ : c.Type = "decimal"; c.DbType = DbType.Decimal; c.SqlDbType = SqlDbType.Decimal; break;
113 case 65 /* usmallint */ : c.Type = "ushort"; c.DbType = DbType.UInt16; c.SqlDbType = SqlDbType.SmallInt; break;
114 case 66 /* uint */ : c.Type = "uint"; c.DbType = DbType.UInt32; c.SqlDbType = SqlDbType.Int; break;
115 case 67 /* ubigint */ : c.Type = "ulong"; c.DbType = DbType.UInt64; c.SqlDbType = SqlDbType.BigInt; break;
116 case 122 /* smallmoney */ : c.Type = "decimal"; c.DbType = DbType.Currency; c.SqlDbType = SqlDbType.SmallMoney; break;
117 case 174 /* unitext */ : c.Type = "string"; c.DbType = DbType.String; c.SqlDbType = SqlDbType.NText; break;
118 case 189 /* bigdatetime */ : c.Type = "DateTime"; c.DbType = DbType.DateTime2; c.SqlDbType = SqlDbType.DateTime2; break;
119 case 190 /* bigtime */ : c.Type = "DateTime"; c.DbType = DbType.DateTime2; c.SqlDbType = SqlDbType.DateTime2; break;
120 case 191 /* bigint */ : c.Type = "long"; c.DbType = DbType.Int64; c.SqlDbType = SqlDbType.BigInt; break;
121
122 case 37 /* varbinary */
123 /* timestamp */ :
124 c.Type = "byte[]";
125 c.DbType = DbType.Binary;
126 c.SqlDbType = Convert.ToString(rd["typename"]) == "timestamp" ? SqlDbType.Timestamp : SqlDbType.VarBinary;
127 break;
128
129 case 39 /* sysname */
130 /* longsysname */
131 /* varchar */
132 /* nvarchar */ :
133
134 c.Type = "string";
135
136 if (Convert.ToString(rd["typename"]) == "nvarchar")
137 {
138 c.DbType = DbType.String;
139 c.SqlDbType = SqlDbType.NVarChar;
140 }
141 else
142 {
143 c.DbType = DbType.AnsiString;
144 c.SqlDbType = SqlDbType.VarChar;
145 }
146
147 break;
148
149 case 47 /* char */
150 /* nchar */ :
151
152 c.Type = Convert.ToInt32 (rd["length"]) == 1 ? "char" : "string";
153 c.DbType = Convert.ToString(rd["typename"]) == "char" ? DbType.AnsiStringFixedLength : DbType.StringFixedLength;
154 c.SqlDbType = Convert.ToString(rd["typename"]) == "char" ? SqlDbType.Char : SqlDbType.NChar;
155 break;
156
157 case 135 /* unichar */ :
158 c.Type = Convert.ToInt32 (rd["length"]) == 1 ? "char" : "string";
159 c.DbType = DbType.StringFixedLength;
160 c.SqlDbType = SqlDbType.NChar;
161 break;
162
163 case 155 /* univarchar */ :
164 c.Type = Convert.ToInt32 (rd["length"]) == 1 ? "char" : "string";
165 c.DbType = DbType.String;
166 c.SqlDbType = SqlDbType.NVarChar;
167 break;
168
169 case 36 /* extended type */ :
170 case 38 /* intn */ :
171 case 68 /* uintn */ :
172 case 106 /* decimaln */ :
173 case 108 /* numericn */ :
174 case 109 /* floatn */ :
175 case 110 /* moneyn */ :
176 case 111 /* datetimn */ :
177 case 123 /* daten */ :
178 case 147 /* timen */ :
179 case 187 /* bigdatetimen */ :
180 case 188 /* bigtimen */ : c.Type = "byte[]"; c.DbType = DbType.Binary; c.SqlDbType = SqlDbType.Binary; break;
181 }
182
183 switch (c.Type)
184 {
185 case "string" :
186 case "byte[]" : c.IsClass = true; break;
187 }
188
189 if (c.IsNullable && !c.IsClass)
190 c.Type += "?";
191
192 columns.Add(col);
193 }
194 }
195
196 // Load PKs.
197 //
198 cmd.CommandText = @"
199 SELECT
200 i.id,
201 i.name,
202 INDEX_COL(USER_NAME(o.uid) + '.' + o.name, i.indid, c.colid) AS colname,
203 INDEX_COLORDER(USER_NAME(o.uid) + '.' + o.name, i.indid, c.colid) AS [order],
204 c.colid
205 FROM
206 sysindexes i
207 JOIN sysobjects o ON i.id = o.id
208 JOIN syscolumns c ON i.id = c.id
209 WHERE
210 i.status2 & 2 = 2 AND
211 i.status & 2048 = 2048 AND
212 i.indid > 0 AND
213 c.colid < i.keycnt + CASE WHEN i.indid = 1 THEN 1 ELSE 0 END";
214
215 using (var rd = cmd.ExecuteReader())
216 {
217 while (rd.Read())
218 {
219 var id = Convert.ToInt32 (rd["id"]);
220 var colid = Convert.ToInt32 (rd["colid"]);
221 var colname = Convert.ToString(rd["colname"]);
222
223 columns.Single(_ => _.ID == id && _.Column.ColumnName == colname).Column.PKIndex = colid;
224 }
225 }
226
227 // Load FKs.
228 //
229 cmd.CommandText = @"
230 SELECT
231 o.name as Name,
232 c.tableid as ThisTable,
233 r.reftabid as OtherTable,
234 COL_NAME(c.tableid, r.fokey1) as ThisColumn1,
235 COL_NAME(c.tableid, r.fokey2) as ThisColumn2,
236 COL_NAME(c.tableid, r.fokey3) as ThisColumn3,
237 COL_NAME(c.tableid, r.fokey4) as ThisColumn4,
238 COL_NAME(c.tableid, r.fokey5) as ThisColumn5,
239 COL_NAME(c.tableid, r.fokey6) as ThisColumn6,
240 COL_NAME(c.tableid, r.fokey7) as ThisColumn7,
241 COL_NAME(c.tableid, r.fokey8) as ThisColumn8,
242 COL_NAME(c.tableid, r.fokey9) as ThisColumn9,
243 COL_NAME(c.tableid, r.fokey10) as ThisColumn10,
244 COL_NAME(c.tableid, r.fokey11) as ThisColumn11,
245 COL_NAME(c.tableid, r.fokey12) as ThisColumn12,
246 COL_NAME(c.tableid, r.fokey13) as ThisColumn13,
247 COL_NAME(c.tableid, r.fokey14) as ThisColumn14,
248 COL_NAME(c.tableid, r.fokey15) as ThisColumn15,
249 COL_NAME(c.tableid, r.fokey16) as ThisColumn16,
250 COL_NAME(r.reftabid, r.refkey1) as OtherColumn1,
251 COL_NAME(r.reftabid, r.refkey2) as OtherColumn2,
252 COL_NAME(r.reftabid, r.refkey3) as OtherColumn3,
253 COL_NAME(r.reftabid, r.refkey4) as OtherColumn4,
254 COL_NAME(r.reftabid, r.refkey5) as OtherColumn5,
255 COL_NAME(r.reftabid, r.refkey6) as OtherColumn6,
256 COL_NAME(r.reftabid, r.refkey7) as OtherColumn7,
257 COL_NAME(r.reftabid, r.refkey8) as OtherColumn8,
258 COL_NAME(r.reftabid, r.refkey9) as OtherColumn9,
259 COL_NAME(r.reftabid, r.refkey10) as OtherColumn10,
260 COL_NAME(r.reftabid, r.refkey11) as OtherColumn11,
261 COL_NAME(r.reftabid, r.refkey12) as OtherColumn12,
262 COL_NAME(r.reftabid, r.refkey13) as OtherColumn13,
263 COL_NAME(r.reftabid, r.refkey14) as OtherColumn14,
264 COL_NAME(r.reftabid, r.refkey15) as OtherColumn15,
265 COL_NAME(r.reftabid, r.refkey16) as OtherColumn16
266 FROM
267 sysreferences r
268 JOIN sysconstraints c ON r.constrid = c.constrid
269 JOIN sysobjects o ON c.constrid = o.id
270 JOIN sysobjects o3 ON c.tableid = o3.id
271 LEFT JOIN sysobjects o2 ON r.reftabid = o2.id
272 JOIN sysreferences r2 ON r.constrid = r2.constrid
273 LEFT JOIN sysindexes i ON r2.indexid = i.indid AND r2.reftabid = i.id
274 WHERE
275 c.status = 64";
276
277 using (var rd = cmd.ExecuteReader())
278 {
279 while (rd.Read())
280 {
281 var name = Convert.ToString(rd["Name"]);
282 var thisTableID = Convert.ToInt32 (rd["ThisTable"]);
283 var otherTableID = Convert.ToInt32 (rd["OtherTable"]);
284
285 var thisTable = (from t in tables where t.ID == thisTableID select t.Table).Single();
286 var otherTable = (from t in tables where t.ID == otherTableID select t.Table).Single();
287
288 thisTable.ForeignKeys.Add(name, new ForeignKey { KeyName = name, MemberName = name, OtherTable = otherTable });
289
290 for (int i = 1; i <= 16; i++)
291 {
292 if (rd.IsDBNull(rd.GetOrdinal("ThisColumn" + i)))
293 break;
294
295 var thisColumnName = Convert.ToString(rd["ThisColumn" + i]);
296 var otherColumnName = Convert.ToString(rd["OtherColumn" + i]);
297
298 var thisColumn = (from c in columns where c.ID == thisTableID && c.Column.ColumnName == thisColumnName select c.Column).Single();
299 var otherColumn = (from c in columns where c.ID == otherTableID && c.Column.ColumnName == otherColumnName select c.Column).Single();
300
301 var key = thisTable.ForeignKeys[name];
302
303 key.ThisColumns. Add(thisColumn);
304 key.OtherColumns.Add(otherColumn);
305 }
306 }
307 }
308 }
309
310 var qc =
311 from c in columns
312 group c by c.ID into gr
313 join t in tables on gr.Key equals t.ID
314 select new { t.Table, gr };
315
316 foreach (var c in qc)
317 {
318 foreach (var col in from col in c.gr orderby col.Column.ID select col.Column)
319 c.Table.Columns.Add(col.ColumnName, col);
320
321 if (c.Table.Owner == "dbo")
322 {
323 c.Table.Owner = null;
324 Tables.Add(c.Table.TableName, c.Table);
325 }
326 else
327 {
328 Tables.Add(c.Table.Owner + "." + c.Table.TableName, c.Table);
329 }
330 }
331
332 if (GenerateSybaseSystemTables)
333 {
334 Tables.Add("sysobjects", new Table
335 {
336 TableName = "sysobjects",
337 ClassName = "SysObject",
338 BaseClassName = BaseEntityClass,
339 Columns =
340 {
341 { "name", new VarCharColumn { ColumnName = "name", Length = 255 }},
342 { "id", new IntColumn { ColumnName = "id" }},
343 { "uid", new IntColumn { ColumnName = "uid" }},
344 { "type", new Column {
345 ColumnName = "type",
346 Type = "string",
347 ColumnType = "char",
348 DbType = DbType.AnsiStringFixedLength,
349 SqlDbType = SqlDbType.Char,
350 Length = 2,
351 }},
352 { "userstat", new SmallIntColumn { ColumnName = "userstat" }},
353 { "sysstat", new SmallIntColumn { ColumnName = "sysstat" }},
354 { "indexdel", new SmallIntColumn { ColumnName = "indexdel" }},
355 { "schemacnt", new SmallIntColumn { ColumnName = "schemacnt" }},
356 { "sysstat2", new IntColumn { ColumnName = "sysstat2" }},
357 { "crdate", new DateTimeColumn { ColumnName = "crdate" }},
358 { "expdate", new DateTimeColumn { ColumnName = "expdate" }},
359 { "deltrig", new IntColumn { ColumnName = "deltrig" }},
360 { "instrig", new IntColumn { ColumnName = "instrig" }},
361 { "updtrig", new IntColumn { ColumnName = "updtrig" }},
362 { "seltrig", new IntColumn { ColumnName = "seltrig" }},
363 { "ckfirst", new IntColumn { ColumnName = "ckfirst" }},
364 { "cache", new SmallIntColumn { ColumnName = "cache" }},
365 { "audflags", new IntColumn { ColumnName = "audflags", IsNullable = true }},
366 { "objspare", new IntColumn { ColumnName = "objspare" }},
367 { "versionts", new Column {
368 ColumnName = "versionts",
369 IsNullable = true,
370 Type = "byte[]",
371 ColumnType = "binary",
372 IsClass = true,
373 DbType = DbType.Binary,
374 SqlDbType = SqlDbType.Binary,
375 Length = 6,
376 }},
377 { "loginame", new VarCharColumn { ColumnName = "loginame", Length = 30 }},
378 }
379 });
380
381 int n = 1;
382
383 foreach (var col in Tables["sysobjects"].Columns)
384 {
385 var c = col.Value;
386
387 c.ID = n++;
388 c.MemberName = c.ColumnName;
389
390 if (c.IsNullable && !c.IsClass)
391 c.Type = c.Type + "?";
392 }
393 }
394 }
395
396 class VarCharColumn : Column
397 {
398 public VarCharColumn()
399 {
400 Type = "string";
401 ColumnType = "varchar";
402 IsClass = true;
403 DbType = DbType.AnsiString;
404 SqlDbType = SqlDbType.VarChar;
405 }
406 }
407
408 class IntColumn : Column
409 {
410 public IntColumn()
411 {
412 Type = "int";
413 ColumnType = "int";
414 DbType = DbType.Int32;
415 SqlDbType = SqlDbType.Int;
416 Length = 4;
417 }
418 }
419
420 class SmallIntColumn : Column
421 {
422 public SmallIntColumn()
423 {
424 Type = "short";
425 ColumnType = "smallint";
426 DbType = DbType.Int16;
427 SqlDbType = SqlDbType.SmallInt;
428 Length = 2;
429 }
430 }
431
432 class DateTimeColumn : Column
433 {
434 public DateTimeColumn()
435 {
436 Type = "DateTime";
437 ColumnType = "datetime";
438 DbType = DbType.DateTime;
439 SqlDbType = SqlDbType.DateTime;
440 Length = 8;
441 }
442 }
443
444 #>