using Npgsql;
2 using NpgsqlTypes;
3 using System;
4 using System.Collections.Generic;
5 using System.Data;
6 using System.Text.RegularExpressions;
7
8 namespace SchemaExplorer
9 {
10 public class PostgreSQLSchemaProvider : IDbSchemaProvider, IDbConnectionStringEditor
11 {
12 public string Name
13 {
14 get
15 {
16 return "PostgreSQLSchemaProvider";
17 }
18 }
19
20 public string Description
21 {
22 get
23 {
24 return "PostgreSQL Schema Provider";
25 }
26 }
27
28 public string ConnectionString
29 {
30 get
31 {
32 return string.Empty;
33 }
34 }
35
36 public bool EditorAvailable
37 {
38 get
39 {
40 return false;
41 }
42 }
43
44 public bool ShowEditor(
string currentConnectionString)
45 {
46 return false;
47 }
48
49 public TableSchema[] GetTables(
string connectionString, DatabaseSchema database)
50 {
51 List<TableSchema> list =
new List<TableSchema>
();
52 using (NpgsqlConnection npgsqlConnection =
new NpgsqlConnection(connectionString))
53 {
54 npgsqlConnection.Open();
55 using (NpgsqlCommand npgsqlCommand =
new NpgsqlCommand(
"select tablename, tableowner,obj_description(relfilenode,‘pg_class‘) as pg_description from pg_catalog.pg_tables as t left join pg_catalog.pg_class as c on t.tablename = c.relname where t.schemaname = ‘public‘ order by t.tablename", npgsqlConnection))
56 {
57 using (NpgsqlDataReader npgsqlDataReader =
npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection))
58 {
59 while (npgsqlDataReader.Read())
60 {
61 if (!npgsqlDataReader.IsDBNull(
0) && npgsqlDataReader.GetString(
0).ToUpper() !=
"CODESMITH_EXTENDED_PROPERTIES")
62 {
63 list.Add(
new TableSchema(database, npgsqlDataReader.GetString(
0), npgsqlDataReader.IsDBNull(
1) ?
string.Empty : npgsqlDataReader.GetString(
1), DateTime.MinValue,
new ExtendedProperty[] {
new ExtendedProperty(
"CS_Description", npgsqlDataReader.IsDBNull(
2) ?
string.Empty : npgsqlDataReader.GetString(
2), DbType.String, PropertyStateEnum.ReadOnly) }));
64 }
65 }
66 }
67 }
68 }
69 return list.ToArray();
70 }
71
72 public IndexSchema[] GetTableIndexes(
string connectionString, TableSchema table)
73 {
74 List<IndexSchema> list =
new List<IndexSchema>
();
75 if (table.Columns ==
null)
76 table.Columns.AddRange(GetTableColumns(connectionString, table));
77 DataSet ds =
new DataSet();
78 using (NpgsqlConnection npgsqlConnection =
new NpgsqlConnection(connectionString))
79 {
80 npgsqlConnection.Open();
81 string text =
string.Format(
"select schemaname, tablename, indexname, tablespace from pg_catalog.pg_indexes where schemaname=‘public‘ and tablename = ‘{0}‘", table.Name);
82 using (NpgsqlCommand npgsqlCommand =
new NpgsqlCommand(text, npgsqlConnection))
83 {
84 using (NpgsqlDataAdapter nda =
new NpgsqlDataAdapter(npgsqlCommand))
85 {
86 nda.Fill(ds,
"pg_indexes");
87 }
88
89 foreach (DataRow item
in ds.Tables[
"pg_indexes"].Rows)
90 {
91 string @string =
string.Empty;
92 @string = item.Field<
string>(
"indexname");
93 if (!
string.IsNullOrEmpty(@string))
94 {
95 string text2 =
string.Format(
"SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS \"tablespace\", a.attname as \"colname\", x.indisunique as \"unique\", x.indisprimary as \"primary\", x.indisclustered as \"clustered\" FROM pg_catalog.pg_index x JOIN pg_catalog.pg_class c ON c.oid = x.indrelid JOIN pg_catalog.pg_class i ON i.oid = x.indexrelid JOIN pg_catalog.pg_attribute a ON a.attrelid = i.relfilenode LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_catalog.pg_tablespace t ON t.oid = i.reltablespace WHERE c.relkind = ‘r‘::\"char\" AND i.relkind = ‘i‘::\"char\" AND n.nspname=‘public‘ AND c.relname=‘{0}‘ AND i.relname =‘{1}‘", table.Name, @string);
96
97 using (NpgsqlCommand npgsqlCommand2 =
new NpgsqlCommand(text2, npgsqlConnection))
98 {
99 using (NpgsqlDataAdapter nda2 =
new NpgsqlDataAdapter(npgsqlCommand2))
100 {
101 nda2.Fill(ds,
"pg_catalog");
102 }
103 }
104 List<
string> list2 =
new List<
string>
();
105 bool? isPrimaryKey =
false;
106 bool? isUnique =
false;
107 bool? isClustered =
false;
108 foreach (DataRow item2
in ds.Tables[
"pg_catalog"].Rows)
109 {
110 isPrimaryKey = item2.Field<
bool?>(
"primary");
// (!npgsqlDataReader2.IsDBNull(6) && npgsqlDataReader2.GetBoolean(6));
111 isUnique = item2.Field<
bool?>(
"unique");
//(!npgsqlDataReader2.IsDBNull(5) && npgsqlDataReader2.GetBoolean(5));
112 isClustered = item2.Field<
bool?>(
"clustered");
//(!npgsqlDataReader2.IsDBNull(7) && npgsqlDataReader2.GetBoolean(7));
113 list2.Add(item2.Field<
string>(
"colname"));
114 }
115 list.Add(
new IndexSchema(table, @string, isPrimaryKey ??
false, isUnique ??
false, isClustered ??
false, list2.ToArray()));
116 list2.Clear();
117 ds.Tables[
"pg_catalog"].Clear();
118 }
119 }
120 }
121 }
122 return list.ToArray();
123 }
124
125 public ColumnSchema[] GetTableColumns(
string connectionString, TableSchema table)
126 {
127 List<ColumnSchema> list =
new List<ColumnSchema>
();
128 using (NpgsqlConnection npgsqlConnection =
new NpgsqlConnection(connectionString))
129 {
130 npgsqlConnection.Open();
131 string text =
string.Format(
"select column_name, is_nullable, character_maximum_length, numeric_precision, numeric_scale, data_type, udt_name,col_description(b.attrelid,b.attnum) as pg_description from information_schema.columns as a join pg_attribute as b on a.column_name=b.attname join pg_class as c on a.table_name=c.relname and b.attrelid = c.oid where b.attnum>0 and a.table_schema = ‘public‘ and a.table_name=‘{0}‘", table.Name);
132 using (NpgsqlCommand npgsqlCommand =
new NpgsqlCommand(text, npgsqlConnection))
133 {
134 using (NpgsqlDataReader npgsqlDataReader =
npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection))
135 {
136 while (npgsqlDataReader.Read())
137 {
138 bool allowDBNull = npgsqlDataReader.IsDBNull(
1) || npgsqlDataReader.GetString(
1) ==
"YES";
139 byte precision = (
byte)(npgsqlDataReader.IsDBNull(
3) ?
0 : npgsqlDataReader.GetInt32(
3));
140 int size = npgsqlDataReader.IsDBNull(
2) ?
0 : npgsqlDataReader.GetInt32(
2);
141 int scale = npgsqlDataReader.IsDBNull(
4) ?
0 : npgsqlDataReader.GetInt32(
4);
142 string name = npgsqlDataReader.IsDBNull(
0) ?
string.Empty : npgsqlDataReader.GetString(
0);
143 string text2 = npgsqlDataReader.IsDBNull(
5) ?
string.Empty : npgsqlDataReader.GetString(
5);
144 string type = npgsqlDataReader.IsDBNull(
6) ?
string.Empty : npgsqlDataReader.GetString(
6);
145 list.Add(
new ColumnSchema(table, name, PostgreSQLSchemaProvider.GetDbType(type), text2, size, precision, scale, allowDBNull,
new ExtendedProperty[]
146 {
147 new ExtendedProperty(
"NpgsqlDbType", PostgreSQLSchemaProvider.GetNativeDbType(text2), DbType.String),
148 new ExtendedProperty(
"CS_Description",npgsqlDataReader.IsDBNull(
7) ?
string.Empty : npgsqlDataReader.GetString(
7), DbType.String)
149 }));
150 }
151 if (!
npgsqlDataReader.IsClosed)
152 {
153 npgsqlDataReader.Close();
154 }
155 }
156 }
157 }
158 return list.ToArray();
159 }
160
161 public TableKeySchema[] GetTableKeys(
string connectionString, TableSchema table)
162 {
163 List<TableKeySchema> list =
new List<TableKeySchema>
();
164 using (NpgsqlConnection npgsqlConnection =
new NpgsqlConnection(connectionString))
165 {
166 npgsqlConnection.Open();
167 string text =
string.Format(
"SELECT constraint_name as constrname FROM information_schema.table_constraints WHERE table_name = ‘{0}‘ AND constraint_type = ‘FOREIGN KEY‘ AND constraint_schema=‘public‘", table.Name);
168 using (NpgsqlCommand npgsqlCommand =
new NpgsqlCommand(text, npgsqlConnection))
169 {
170 string text2 =
string.Format(
"SELECT px.conname as constrname, att.attname as colname, fore.relname as reftabname, fatt.attname as refcolname, CASE px.confupdtype WHEN ‘a‘ THEN ‘NO ACTION‘ WHEN ‘r‘ THEN ‘RESTRICT‘ WHEN ‘c‘ THEN ‘CASCADE‘ WHEN ‘n‘ THEN ‘SET NULL‘ WHEN ‘d‘ THEN ‘SET DEFAULT‘ END AS on_update, CASE px.confdeltype WHEN ‘a‘ THEN ‘NO ACTION‘ WHEN ‘r‘ THEN ‘RESTRICT‘ WHEN ‘c‘ THEN ‘CASCADE‘ WHEN ‘n‘ THEN ‘SET NULL‘ WHEN ‘d‘ THEN ‘SET DEFAULT‘ END AS on_delete, CASE px.contype WHEN ‘p‘ THEN true WHEN ‘f‘ THEN false END as IsPrimaryKey from pg_constraint px left join pg_class home on (home.oid = px.conrelid) left join pg_class fore on (fore.oid = px.confrelid) left join pg_attribute att on (att.attrelid = px.conrelid AND att.attnum = ANY(px.conkey)) left join pg_attribute fatt on (fatt.attrelid = px.confrelid AND fatt.attnum = ANY(px.confkey)) where (home.relname = ‘{0}‘) and px.contype = ‘f‘ order by constrname", table.Name);
171 using (NpgsqlCommand npgsqlCommand2 =
new NpgsqlCommand(text2, npgsqlConnection))
172 {
173 NpgsqlDataAdapter npgsqlDataAdapter =
new NpgsqlDataAdapter(npgsqlCommand);
174 DataSet dataSet =
new DataSet();
175 npgsqlDataAdapter.Fill(dataSet,
"constraint");
176 npgsqlDataAdapter.SelectCommand =
npgsqlCommand2;
177 npgsqlDataAdapter.Fill(dataSet,
"keys");
178 if (dataSet.Tables[
0].Rows.Count >
0)
179 {
180 dataSet.Relations.Add(
"Contraint_to_Keys", dataSet.Tables[
0].Columns[
"constrname"], dataSet.Tables[
1].Columns[
"constrname"]);
181 foreach (DataRow dataRow
in dataSet.Tables[
0].Rows)
182 {
183 string name = dataRow[
"constrname"].ToString();
184 DataRow[] childRows = dataRow.GetChildRows(
"Contraint_to_Keys");
185 string[] array =
new string[childRows.Length];
186 string[] array2 =
new string[childRows.Length];
187 string name2 =
table.Name;
188 string primaryKeyTable = childRows[
0][
"reftabname"].ToString();
189 for (
int i =
0; i < childRows.Length; i++
)
190 {
191 array2[i] = childRows[i][
"colname"].ToString();
192 array[i] = childRows[i][
"refcolname"].ToString();
193 }
194 list.Add(
new TableKeySchema(table.Database, name, array2, name2, array, primaryKeyTable));
195 }
196 }
197 }
198 }
199 string text3 =
string.Format(
"SELECT px.conname as constrname FROM pg_constraint px left join pg_class fore on fore.oid = px.confrelid where fore.relname = ‘{0}‘", table.Name);
200 using (NpgsqlCommand npgsqlCommand3 =
new NpgsqlCommand(text3, npgsqlConnection))
201 {
202 string text4 =
string.Format(
"SELECT px.conname as constrname, fatt.attname as colname, home.relname as reftabname, att.attname as refcolname, CASE px.confupdtype WHEN ‘a‘ THEN ‘NO ACTION‘ WHEN ‘r‘ THEN ‘RESTRICT‘ WHEN ‘c‘ THEN ‘CASCADE‘ WHEN ‘n‘ THEN ‘SET NULL‘ WHEN ‘d‘ THEN ‘SET DEFAULT‘ END AS on_update, CASE px.confdeltype WHEN ‘a‘ THEN ‘NO ACTION‘ WHEN ‘r‘ THEN ‘RESTRICT‘ WHEN ‘c‘ THEN ‘CASCADE‘ WHEN ‘n‘ THEN ‘SET NULL‘ WHEN ‘d‘ THEN ‘SET DEFAULT‘ END AS on_delete, CASE px.contype WHEN ‘p‘ THEN true WHEN ‘f‘ THEN false END as IsPrimaryKey from pg_constraint px left join pg_class home on (home.oid = px.conrelid) left join pg_class fore on (fore.oid = px.confrelid) left join pg_attribute att on (att.attrelid = px.conrelid AND att.attnum = ANY(px.conkey)) left join pg_attribute fatt on (fatt.attrelid = px.confrelid AND fatt.attnum = ANY(px.confkey)) where (fore.relname = ‘{0}‘) order by constrname", table.Name);
203 using (NpgsqlCommand npgsqlCommand4 =
new NpgsqlCommand(text4, npgsqlConnection))
204 {
205 NpgsqlDataAdapter npgsqlDataAdapter2 =
new NpgsqlDataAdapter();
206 DataSet dataSet2 =
new DataSet();
207 npgsqlDataAdapter2.SelectCommand =
npgsqlCommand3;
208 npgsqlDataAdapter2.Fill(dataSet2,
"constraint");
209 npgsqlDataAdapter2.SelectCommand =
npgsqlCommand4;
210 npgsqlDataAdapter2.Fill(dataSet2,
"keys");
211 if (dataSet2.Tables[
0].Rows.Count >
0)
212 {
213 dataSet2.Relations.Add(
"Contraint_to_Keys", dataSet2.Tables[
0].Columns[
"constrname"], dataSet2.Tables[
1].Columns[
"constrname"]);
214 foreach (DataRow dataRow2
in dataSet2.Tables[
0].Rows)
215 {
216 string name3 = dataRow2[
"constrname"].ToString();
217 DataRow[] childRows2 = dataRow2.GetChildRows(
"Contraint_to_Keys");
218 string[] array3 =
new string[childRows2.Length];
219 string[] array4 =
new string[childRows2.Length];
220 string foreignKeyTable = childRows2[
0][
"reftabname"].ToString();
221 string name4 =
table.Name;
222 for (
int j =
0; j < childRows2.Length; j++
)
223 {
224 array4[j] = childRows2[j][
"refcolname"].ToString();
225 array3[j] = childRows2[j][
"colname"].ToString();
226 }
227 list.Add(
new TableKeySchema(table.Database, name3, array4, foreignKeyTable, array3, name4));
228 }
229 }
230 }
231 }
232 }
233 return list.ToArray();
234 }
235
236 public PrimaryKeySchema GetTablePrimaryKey(
string connectionString, TableSchema table)
237 {
238 PrimaryKeySchema result =
null;
239 DataSet ds =
new DataSet();
240 using (NpgsqlConnection npgsqlConnection =
new NpgsqlConnection(connectionString))
241 {
242 npgsqlConnection.Open();
243 string text =
string.Format(
"select constraint_name from information_schema.table_constraints where constraint_schema=‘public‘ and table_name=‘{0}‘ and constraint_type=‘PRIMARY KEY‘", table.Name);
244 using (NpgsqlCommand npgsqlCommand =
new NpgsqlCommand(text, npgsqlConnection))
245 {
246 using (NpgsqlDataAdapter nda =
new NpgsqlDataAdapter(npgsqlCommand))
247 {
248 nda.Fill(ds,
"table_constraints");
249 }
250 }
251
252 string text2 =
string.Format(
"select px.conname as ConstraintName, att.attname as ColumnName from pg_constraint px inner join pg_class home on (home.oid = px.conrelid) left join pg_attribute att on (att.attrelid = px.conrelid AND att.attnum = ANY(px.conkey)) where (home.relname = ‘{0}‘) and px.contype = ‘p‘", table.Name);
253
254
255 using (NpgsqlCommand npgsqlCommand2 =
new NpgsqlCommand(text2, npgsqlConnection))
256 {
257 using (NpgsqlDataAdapter nda =
new NpgsqlDataAdapter(npgsqlCommand2))
258 {
259 nda.Fill(ds,
"pg_constraint");
260 }
261 }
262
263 foreach (DataRow item
in ds.Tables[
"table_constraints"].Rows)
264 {
265 List<
string> list =
new List<
string>
();
266 foreach (DataRow item2
in ds.Tables[
"pg_constraint"].Rows)
267 {
268 list.Add(item2.Field<
string>(
"ColumnName"));
269 }
270 result =
new PrimaryKeySchema(table, item.Field<
string>(
"constraint_name"), list.ToArray());
271 }
272
273 }
274 return result;
275 }
276
277 public DataTable GetTableData(
string connectionString, TableSchema table)
278 {
279 DataTable dataTable;
280 using (NpgsqlConnection npgsqlConnection =
new NpgsqlConnection(connectionString))
281 {
282 dataTable =
new DataTable(table.Name);
283 string text =
string.Format(
"SELECT * FROM {0}", table.Name);
284 using (NpgsqlDataAdapter npgsqlDataAdapter =
new NpgsqlDataAdapter(text, npgsqlConnection))
285 {
286 npgsqlDataAdapter.Fill(dataTable);
287 }
288 if (npgsqlConnection.State !=
ConnectionState.Closed)
289 {
290 npgsqlConnection.Close();
291 }
292 }
293 return dataTable;
294 }
295
296 public ExtendedProperty[] GetExtendedProperties(
string connectionString, SchemaObjectBase schemaObject)
297 {
298 List<ExtendedProperty> list =
new List<ExtendedProperty>
();
299 if (schemaObject
is ColumnSchema)
300 {
301 ColumnSchema columnSchema = schemaObject
as ColumnSchema;
302 string text =
string.Format(
"select pg_get_serial_sequence(c.table_name, c.column_name) as EXTRA, COLUMN_DEFAULT, data_type \r\n from pg_tables t\r\n INNER JOIN information_schema.columns c on t.tablename = c.table_name\r\n WHERE schemaname = ‘{0}‘ \r\n AND table_name = ‘{1}‘\r\n AND COLUMN_NAME = ‘{2}‘\r\n order by ordinal_position", columnSchema.Table.Database.Name, columnSchema.Table.Name, columnSchema.Name);
303 using (NpgsqlConnection npgsqlConnection =
new NpgsqlConnection(connectionString))
304 {
305 npgsqlConnection.Open();
306 using (NpgsqlCommand npgsqlCommand =
new NpgsqlCommand(text, npgsqlConnection))
307 {
308 using (IDataReader dataReader =
npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection))
309 {
310 while (dataReader.Read())
311 {
312 string text2 = dataReader.IsDBNull(
0) ?
string.Empty : dataReader.GetString(
0).ToLower();
313 string value = dataReader.IsDBNull(
1) ?
null : dataReader.GetString(
1).ToUpper();
314 string value2 = dataReader.GetString(
2).ToUpper();
315 bool flag = !
string.IsNullOrEmpty(text2);
316 list.Add(
new ExtendedProperty(
"CS_IsIdentity", flag, columnSchema.DataType));
317 if (flag)
318 {
319 list.Add(
new ExtendedProperty(
"CS_IdentitySeed",
1, columnSchema.DataType));
320 list.Add(
new ExtendedProperty(
"CS_IdentityIncrement",
1, columnSchema.DataType));
321 }
322 list.Add(
new ExtendedProperty(
"CS_Default", value, DbType.String));
323 list.Add(
new ExtendedProperty(
"CS_SystemType", value2, DbType.String));
324 list.Add(
new ExtendedProperty(
"CS_Sequence", text2.ToUpper(), DbType.String));
325 }
326 if (!
dataReader.IsClosed)
327 {
328 dataReader.Close();
329 }
330 }
331 }
332 if (npgsqlConnection.State !=
ConnectionState.Closed)
333 {
334 npgsqlConnection.Close();
335 }
336 }
337 }
338 return list.ToArray();
339
