Mercurial > pub > bltoolkit
comparison Source/Templates/PostgreSQL.ttinclude @ 0:f990fcb411a9
Копия текущей версии из 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 = "Npgsql.NpgsqlConnection, Npgsql"; | |
3 #><#+ | |
4 private void LoadServerMetadata() | |
5 { | |
6 var tables = CreateList(new { ID = 0, Table = new Table() }); | |
7 var columns = CreateList(new { ID = 0, Column = new Column() }); | |
8 | |
9 if (!string.IsNullOrEmpty(DataProviderAssembly)) | |
10 { | |
11 System.Reflection.Assembly.LoadFile(DataProviderAssembly.Replace("Npgsql.dll", "Mono.Security.dll")); | |
12 } | |
13 | |
14 using (var conn = GetConnection()) | |
15 using (var cmd = conn.CreateCommand()) | |
16 { | |
17 // Load tables & vies. | |
18 // | |
19 cmd.CommandText = @" | |
20 SELECT | |
21 pg_class.oid, | |
22 pg_namespace.nspname, | |
23 pg_class.relname, | |
24 pg_class.relkind | |
25 FROM | |
26 pg_class | |
27 INNER JOIN pg_user ON (pg_class.relowner = pg_user.usesysid) | |
28 INNER JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid) | |
29 WHERE | |
30 pg_class.relkind IN ('r','v') | |
31 AND pg_namespace.nspname NOT IN ('pg_catalog','information_schema')"; | |
32 | |
33 using (var rd = cmd.ExecuteReader()) | |
34 { | |
35 while (rd.Read()) | |
36 { | |
37 var t = new | |
38 { | |
39 ID = Convert.ToInt32(rd[0]), | |
40 Table = new Table | |
41 { | |
42 Owner = rd[1].ToString(), | |
43 TableName = rd[2].ToString(), | |
44 ClassName = rd[2].ToString(), | |
45 IsView = rd[3].ToString() == "v", | |
46 BaseClassName = BaseEntityClass, | |
47 } | |
48 }; | |
49 | |
50 tables.Add(t); | |
51 } | |
52 } | |
53 | |
54 // Load columns. | |
55 // | |
56 cmd.CommandText = @" | |
57 SELECT | |
58 pg_attribute.attrelid as id, | |
59 pg_attribute.attnum as colid, | |
60 pg_attribute.attname as name, | |
61 0 as status, | |
62 0 as usertype, | |
63 pg_attribute.atttypid as type, | |
64 COALESCE(CAST(substring(format_type(pg_attribute.atttypid, pg_attribute.atttypmod) from '[0-9]{1,}') as INTEGER),0) as lenght, | |
65 CASE WHEN pg_attribute.atttypid = 1700 AND format_type(pg_attribute.atttypid, pg_attribute.atttypmod) != 'numeric' THEN | |
66 COALESCE(CAST(regexp_replace(regexp_replace(format_type(pg_attribute.atttypid, pg_attribute.atttypmod),'[^,]*,',''),'[)]$','') as INTEGER),0) | |
67 ELSE | |
68 0 | |
69 END as prec, | |
70 format_type(pg_attribute.atttypid, pg_attribute.atttypmod) as typename, | |
71 CASE WHEN CAST(pg_attribute.atttypid AS TEXT) IN ('17','25','1042','1043') THEN | |
72 false | |
73 ELSE | |
74 NOT pg_attribute.attnotnull | |
75 END as isnullable, | |
76 COALESCE(pg_attrdef.adsrc ~* 'nextval',FALSE) as isidentity | |
77 FROM | |
78 pg_attribute | |
79 LEFT JOIN pg_attrdef ON (pg_attribute.attrelid = pg_attrdef.adrelid AND | |
80 pg_attribute.attnum = pg_attrdef.adnum) | |
81 INNER JOIN pg_class ON (pg_attribute.attrelid = pg_class.oid) | |
82 INNER JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid) | |
83 WHERE | |
84 pg_class.relkind IN ('r','v') AND | |
85 pg_namespace.nspname NOT IN ('pg_catalog','information_schema') AND | |
86 pg_attribute.attnum > 0 AND | |
87 NOT pg_attribute.attisdropped"; | |
88 | |
89 bool addNpgsqlTypes = false; | |
90 | |
91 using (var rd = cmd.ExecuteReader()) | |
92 { | |
93 while (rd.Read()) | |
94 { | |
95 var col = new | |
96 { | |
97 ID = Convert.ToInt32(rd["id"]), | |
98 Column = new Column | |
99 { | |
100 ID = Convert.ToInt16(rd["colid"]), | |
101 ColumnName = Convert.ToString (rd["name"]), | |
102 MemberName = Convert.ToString (rd["name"]), | |
103 ColumnType = Convert.ToString (rd["typename"]), | |
104 IsNullable = Convert.ToBoolean(rd["isnullable"]), | |
105 IsIdentity = Convert.ToBoolean(rd["isidentity"]), | |
106 Length = rd.IsDBNull(rd.GetOrdinal("lenght")) ? 0 : Convert.ToInt64(rd["lenght"]), | |
107 Precision = rd.IsDBNull(rd.GetOrdinal("prec")) ? 0 : Convert.ToInt32(rd["prec"]), | |
108 //Scale = rd.IsDBNull(rd.GetOrdinal("scale")) ? 0 : Convert.ToInt32(rd["scale"]), | |
109 } | |
110 }; | |
111 | |
112 var c = col.Column; | |
113 var oid = Convert.ToInt32(rd["type"]); | |
114 | |
115 c.Type = "object"; | |
116 c.DbType = DbType.Object; | |
117 c.SqlDbType = SqlDbType.Variant; | |
118 | |
119 switch (oid) | |
120 { | |
121 case 16 /* bool */ : c.Type = "Boolean"; c.DbType = DbType.Boolean; c.SqlDbType = SqlDbType.Bit; break; | |
122 case 17 /* bytea */ : c.Type = "Byte[]"; c.DbType = DbType.Binary; c.SqlDbType = SqlDbType.Binary; break; | |
123 case 20 /* int8 */ : c.Type = "Int64"; c.DbType = DbType.Int64; c.SqlDbType = SqlDbType.BigInt; break; | |
124 case 21 /* int2 */ : c.Type = "Int16"; c.DbType = DbType.Int16; c.SqlDbType = SqlDbType.SmallInt; break; | |
125 case 23 /* int4 */ : c.Type = "Int32"; c.DbType = DbType.Int32; c.SqlDbType = SqlDbType.Int; break; | |
126 case 25 /* text */ : c.Type = "String"; c.DbType = DbType.String; c.SqlDbType = SqlDbType.Text; break; | |
127 case 142 /* xml */ : c.Type = "String"; c.DbType = DbType.Xml; c.SqlDbType = SqlDbType.Xml; break; | |
128 case 600 /* point */ : c.Type = "NpgsqlPoint"; addNpgsqlTypes = true; break; | |
129 case 601 /* lseg */ : c.Type = "NpgsqlLSeg"; addNpgsqlTypes = true; break; | |
130 case 602 /* path */ : c.Type = "NpgsqlPath"; addNpgsqlTypes = true; break; | |
131 case 603 /* box */ : c.Type = "NpgsqlBox"; addNpgsqlTypes = true; break; | |
132 case 604 /* polygon */ : c.Type = "NpgsqlPolygon"; addNpgsqlTypes = true; break; | |
133 case 700 /* float4 */ : c.Type = "Single"; c.DbType = DbType.Single; c.SqlDbType = SqlDbType.Real; break; | |
134 case 701 /* float8 */ : c.Type = "Double"; c.DbType = DbType.Double; c.SqlDbType = SqlDbType.Float; break; | |
135 case 718 /* circle */ : c.Type = "NpgsqlCircle"; addNpgsqlTypes = true; break; | |
136 case 790 /* money */ : c.Type = "Decimal"; c.DbType = DbType.Decimal; c.SqlDbType = SqlDbType.Money; break; | |
137 case 829 /* macaddr */ : c.Type = "NpgsqlMacAddress"; addNpgsqlTypes = true; break; | |
138 case 869 /* inet */ : c.Type = "NpgsqlInet"; addNpgsqlTypes = true; break; | |
139 case 1042 /* char */ : c.Type = "String"; c.DbType = DbType.String; c.SqlDbType = SqlDbType.Char; break; | |
140 case 1043 /* varchar */ : c.Type = "String"; c.DbType = DbType.String; c.SqlDbType = SqlDbType.VarChar; break; | |
141 case 1082 /* date */ : c.Type = "DateTime"; c.DbType = DbType.Date; c.SqlDbType = SqlDbType.Date; break; | |
142 case 1083 /* time */ : c.Type = "DateTime"; c.DbType = DbType.Time; c.SqlDbType = SqlDbType.Time; break; | |
143 case 1114 /* datetime */ : c.Type = "DateTime"; c.DbType = DbType.DateTime; c.SqlDbType = SqlDbType.DateTime; break; | |
144 case 1560 /* bit */ : c.Type = "BitString"; addNpgsqlTypes = true; break; | |
145 case 1700 /* numeric */ : c.Type = "Decimal"; c.DbType = DbType.Decimal; c.SqlDbType = SqlDbType.Decimal; break; | |
146 case 1186 /* interval */ : c.Type = "NpgsqlInterval"; addNpgsqlTypes = true; break; | |
147 case 1184 /* timestamp with time zone */ : | |
148 case 1266 /* timestamp with time zone */ : c.Type = "DateTime"; c.DbType = DbType.DateTime2; c.SqlDbType = SqlDbType.DateTime2; break; | |
149 case 2950 /* Guid */ : c.Type = "Guid"; c.DbType = DbType.Binary; c.SqlDbType = SqlDbType.UniqueIdentifier; break; | |
150 default: | |
151 if (oid < 100000) | |
152 throw new Exception(string.Format("Type '{0}' ({1}) is not expected in PostgreSQL.tt", rd["typename"], rd["type"])); | |
153 break; | |
154 } | |
155 | |
156 switch (c.Type) | |
157 { | |
158 case "object" : | |
159 case "String" : | |
160 case "byte[]" : c.IsClass = true; break; | |
161 } | |
162 | |
163 if (c.IsNullable && !c.IsClass) | |
164 c.Type += "?"; | |
165 | |
166 columns.Add(col); | |
167 } | |
168 } | |
169 | |
170 if (addNpgsqlTypes) | |
171 Usings.Add("NpgsqlTypes"); | |
172 | |
173 // Load PKs. | |
174 // | |
175 cmd.CommandText = @" | |
176 SELECT | |
177 pg_constraint.conrelid as id, | |
178 pg_constraint.conname as ""name"", | |
179 (select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[1]) as colname, | |
180 pg_constraint.conkey[1] as ""order"", | |
181 pg_constraint.conkey[1] as colid | |
182 FROM | |
183 pg_constraint | |
184 WHERE | |
185 pg_constraint.contype = 'p'"; | |
186 | |
187 using (var rd = cmd.ExecuteReader()) | |
188 { | |
189 while (rd.Read()) | |
190 { | |
191 var id = Convert.ToInt32(rd["id"]); | |
192 var colid = Convert.ToInt32(rd["colid"]); | |
193 | |
194 columns.Single(_ => _.ID == id && _.Column.ID == colid).Column.PKIndex = colid; | |
195 } | |
196 } | |
197 | |
198 // Load FKs. | |
199 // | |
200 cmd.CommandText = @" | |
201 SELECT | |
202 pg_constraint.conname as ""Name"", | |
203 pg_constraint.conrelid as ""ThisTable"", | |
204 pg_constraint.confrelid as ""OtherTable"", | |
205 (select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[01]) as ""ThisColumn1"", | |
206 (select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[02]) as ""ThisColumn2"", | |
207 (select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[03]) as ""ThisColumn3"", | |
208 (select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[04]) as ""ThisColumn4"", | |
209 (select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[05]) as ""ThisColumn5"", | |
210 (select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[06]) as ""ThisColumn6"", | |
211 (select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[07]) as ""ThisColumn7"", | |
212 (select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[08]) as ""ThisColumn8"", | |
213 (select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[09]) as ""ThisColumn9"", | |
214 (select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[10]) as ""ThisColumn10"", | |
215 (select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[11]) as ""ThisColumn11"", | |
216 (select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[12]) as ""ThisColumn12"", | |
217 (select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[13]) as ""ThisColumn13"", | |
218 (select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[14]) as ""ThisColumn14"", | |
219 (select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[15]) as ""ThisColumn15"", | |
220 (select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[16]) as ""ThisColumn16"", | |
221 (select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[01]) as ""OtherColumn1"", | |
222 (select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[02]) as ""OtherColumn2"", | |
223 (select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[03]) as ""OtherColumn3"", | |
224 (select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[04]) as ""OtherColumn4"", | |
225 (select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[05]) as ""OtherColumn5"", | |
226 (select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[06]) as ""OtherColumn6"", | |
227 (select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[07]) as ""OtherColumn7"", | |
228 (select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[08]) as ""OtherColumn8"", | |
229 (select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[09]) as ""OtherColumn9"", | |
230 (select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[10]) as ""OtherColumn10"", | |
231 (select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[11]) as ""OtherColumn11"", | |
232 (select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[12]) as ""OtherColumn12"", | |
233 (select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[13]) as ""OtherColumn13"", | |
234 (select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[14]) as ""OtherColumn14"", | |
235 (select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[15]) as ""OtherColumn15"", | |
236 (select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[16]) as ""OtherColumn16"" | |
237 FROM | |
238 pg_constraint | |
239 WHERE | |
240 pg_constraint.contype = 'f'"; | |
241 | |
242 using (var rd = cmd.ExecuteReader()) | |
243 { | |
244 while (rd.Read()) | |
245 { | |
246 var name = Convert.ToString(rd["Name"]); | |
247 var thisTableID = Convert.ToInt32 (rd["ThisTable"]); | |
248 var otherTableID = Convert.ToInt32 (rd["OtherTable"]); | |
249 | |
250 var thisTable = (from t in tables where t.ID == thisTableID select t.Table).Single(); | |
251 var otherTable = (from t in tables where t.ID == otherTableID select t.Table).Single(); | |
252 | |
253 thisTable.ForeignKeys.Add(name, new ForeignKey { KeyName = name, MemberName = name, OtherTable = otherTable }); | |
254 | |
255 for (int i = 1; i <= 16; i++) | |
256 { | |
257 if (rd.IsDBNull(rd.GetOrdinal("ThisColumn" + i))) | |
258 break; | |
259 | |
260 var thisColumnName = Convert.ToString(rd["ThisColumn" + i]); | |
261 var otherColumnName = Convert.ToString(rd["OtherColumn" + i]); | |
262 | |
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 var key = thisTable.ForeignKeys[name]; | |
267 | |
268 key.ThisColumns. Add(thisColumn); | |
269 key.OtherColumns.Add(otherColumn); | |
270 } | |
271 } | |
272 } | |
273 } | |
274 | |
275 var qc = | |
276 from c in columns | |
277 group c by c.ID into gr | |
278 join t in tables on gr.Key equals t.ID | |
279 select new { t.Table, gr }; | |
280 | |
281 foreach (var c in qc) | |
282 { | |
283 foreach (var col in from col in c.gr orderby col.Column.ID select col.Column) | |
284 c.Table.Columns.Add(col.ColumnName, col); | |
285 | |
286 if (c.Table.Owner == "postgres") | |
287 { | |
288 c.Table.Owner = null; | |
289 Tables.Add(c.Table.TableName, c.Table); | |
290 } | |
291 else | |
292 { | |
293 Tables.Add(c.Table.Owner + "." + c.Table.TableName, c.Table); | |
294 } | |
295 } | |
296 } | |
297 #> |