Mercurial > pub > bltoolkit

comparison Source/Templates/PostgreSQL.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 = "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 #>