|
0
|
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 #> |
