DeveelDB  20151217
complete SQL database system, primarly developed for .NET/Mono frameworks
DatabaseMetadata.cs
Go to the documentation of this file.
1 //
2 // Copyright 2010-2014 Deveel
3 //
4 // Licensed under the Apache License, Version 2.0 (the "License");
5 // you may not use this file except in compliance with the License.
6 // You may obtain a copy of the License at
7 //
8 // http://www.apache.org/licenses/LICENSE-2.0
9 //
10 // Unless required by applicable law or agreed to in writing, software
11 // distributed under the License is distributed on an "AS IS" BASIS,
12 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 // See the License for the specific language governing permissions and
14 // limitations under the License.
15 
16 using System;
17 using System.Data;
18 using System.Data.Common;
19 using System.Globalization;
20 using System.Text;
21 
22 using SysDataTable = System.Data.DataTable;
23 using SysDataRow = System.Data.DataRow;
24 
25 namespace Deveel.Data.Client {
26  internal class DatabaseMetadata {
27  public DatabaseMetadata(DeveelDbConnection connection) {
28  this.connection = connection;
29  }
30 
31  private readonly DeveelDbConnection connection;
32 
33  public SysDataTable GetSchemata(string[] restrictions) {
34  if (restrictions == null)
35  throw new ArgumentException();
36 
37  if (restrictions.Length > 1)
38  throw new ArgumentException();
39 
40  string schema = restrictions[0];
41  if (schema == null)
42  schema = "%";
43 
44  var dataTable = new SysDataTable("Schemata");
45 
46  dataTable.Columns.Add("TABLE_SCHEMA");
47  dataTable.Columns.Add("TABLE_CATALOG");
48 
49  var command = connection.CreateCommand(" SELECT * \n" +
50  " FROM \"INFORMATION_SCHEMA.TABLES\" \n" +
51  " WHERE \"TABLE_SCHEMA\" LIKE ? \n" +
52  " ORDER BY \"TABLE_SCHEMA\" \n");
53 
54  command.Parameters.Add(schema);
55  command.Prepare();
56 
57  using (DeveelDbDataReader reader = command.ExecuteReader()) {
58  SysDataRow row = dataTable.NewRow();
59  row["TABLE_SCHEMA"] = reader.GetString(0);
60  row["TABLE_CATALOG"] = reader.GetString(1);
61  dataTable.Rows.Add(row);
62  }
63 
64  return dataTable;
65  }
66 
67  public SysDataTable GetTables(string[] restrictions) {
68  if (restrictions == null)
69  throw new ArgumentNullException("restrictions");
70  if (restrictions.Length < 3)
71  throw new ArgumentException();
72 
73  //TODO: still not officially supported...
74  string catalog = restrictions[0];
75  string schema = restrictions[1];
76  string table = restrictions[2];
77 
78  string[] types = new string[restrictions.Length - 3];
79  Array.Copy(restrictions, 3, types, 0, types.Length);
80 
81  SysDataTable dataTable = new SysDataTable("Tables");
82  dataTable.Columns.Add("TABLE_CATALOG");
83  dataTable.Columns.Add("TABLE_SCHEMA");
84  dataTable.Columns.Add("TABLE_NAME");
85  dataTable.Columns.Add("TABLE_TYPE");
86  dataTable.Columns.Add("REMARKS");
87  dataTable.Columns.Add("TYPE_CATALOG");
88  dataTable.Columns.Add("TYPE_SCHEMA");
89  dataTable.Columns.Add("TYPE_NAME");
90  dataTable.Columns.Add("SELF_REFERENCING_COL_NAME");
91  dataTable.Columns.Add("REF_GENERATION");
92 
93  if (table == null)
94  table = "%";
95  if (schema == null)
96  schema = "%";
97 
98  // The 'types' argument
99  String typePart = "";
100  int typeSize = 0;
101  if (types.Length > 0) {
102  StringBuilder buf = new StringBuilder();
103  buf.Append(" AND \"TABLE_TYPE\" IN ( ");
104  for (int i = 0; i < types.Length - 1; ++i) {
105  buf.Append("?, ");
106  }
107  buf.Append("? ) \n");
108  typeSize = types.Length;
109  typePart = buf.ToString();
110  }
111 
112  // Create the statement
113 
114  DeveelDbCommand command = connection.CreateCommand(" SELECT * \n" +
115  " FROM \"INFORMATION_SCHEMA.TABLES\" \n" +
116  " WHERE \"TABLE_SCHEMA\" LIKE ? \n" +
117  " AND \"TABLE_NAME\" LIKE ? \n" +
118  typePart +
119  " ORDER BY \"TABLE_TYPE\", \"TABLE_SCHEMA\", \"TABLE_NAME\" \n");
120  command.Parameters.Add(schema);
121  command.Parameters.Add(table);
122  if (typeSize > 0) {
123  for (int i = 0; i < typeSize; ++i)
124  command.Parameters.Add(types[i]);
125  }
126 
127  command.Prepare();
128 
129  using (DeveelDbDataReader reader = command.ExecuteReader()) {
130  while (reader.Read()) {
131  SysDataRow row = dataTable.NewRow();
132  row["TABLE_CATALOG"] = reader.GetString(0);
133  row["TABLE_SCHEMA"] = reader.GetString(1);
134  row["TABLE_NAME"] = reader.GetString(2);
135  row["TABLE_TYPE"] = reader.GetString(3);
136  row["REMARKS"] = reader.GetString(4);
137  // the other columns are always NULL so it's useless to read...
138 
139  dataTable.Rows.Add(row);
140  }
141  }
142 
143  return dataTable;
144  }
145 
146  public SysDataTable GetColumns(string[] restrictions) {
147  if (restrictions == null)
148  throw new ArgumentNullException("restrictions");
149  if (restrictions.Length < 4)
150  throw new ArgumentException("COLUMNS collection requires at least 4 arguments.");
151 
152  string catalog = restrictions[0];
153  string schema = restrictions[1];
154  string table = restrictions[2];
155  string column = restrictions[3];
156 
157  if (table == null)
158  table = "%";
159  if (schema == null)
160  schema = "%";
161  if (column == null)
162  column = "%";
163 
164  SysDataTable dataTable = new SysDataTable("Columns");
165  dataTable.Columns.Add("TABLE_CATALOG");
166  dataTable.Columns.Add("TABLE_SCHEMA");
167  dataTable.Columns.Add("TABLE_NAME");
168  dataTable.Columns.Add("COLUMN_NAME");
169  dataTable.Columns.Add("DATA_TYPE", typeof(int));
170  dataTable.Columns.Add("TYPE_NAME");
171  dataTable.Columns.Add("COLUMN_SIZE", typeof(int));
172  dataTable.Columns.Add("BUFFER_LENGTH", typeof(int));
173  dataTable.Columns.Add("DECIMAL_DIGITS", typeof(int));
174  dataTable.Columns.Add("NUM_PREC_RADIX", typeof(int));
175  dataTable.Columns.Add("NULLABLE", typeof(bool));
176  dataTable.Columns.Add("REMARKS");
177  dataTable.Columns.Add("COLUMN_DEFAULT");
178  dataTable.Columns.Add("SQL_DATA_TYPE");
179  dataTable.Columns.Add("SQL_DATETIME_SUB");
180  dataTable.Columns.Add("CHAR_OCTET_LENGTH", typeof(int));
181  dataTable.Columns.Add("ORDINAL_POSITION", typeof(int));
182  dataTable.Columns.Add("IS_NULLABLE", typeof (bool));
183 
184  DeveelDbCommand command = connection.CreateCommand(" SELECT * \n" +
185  " FROM INFORMATION_SCHEMA.COLUMNS \n" +
186  " WHERE \"TABLE_SCHEMA\" LIKE ? \n" +
187  " AND \"TABLE_NAME\" LIKE ? \n" +
188  " AND \"COLUMN_NAME\" LIKE ? \n" +
189  "ORDER BY \"TABLE_SCHEMA\", \"TABLE_NAME\", \"ORDINAL_POSITION\"");
190  command.Parameters.Add(schema);
191  command.Parameters.Add(table);
192  command.Parameters.Add(column);
193  command.Prepare();
194 
195  using (DeveelDbDataReader reader = command.ExecuteReader()) {
196  while (reader.Read()) {
197  SysDataRow row = dataTable.NewRow();
198  row["TABLE_CATALOG"] = reader.GetString(0);
199  row["TABLE_SCHEMA"] = reader.GetString(1);
200  row["TABLE_NAME"] = reader.GetString(2);
201  row["COLUMN_NAME"] = reader.GetString(3);
202  row["DATA_TYPE"] = reader.GetInt32(4);
203  row["TYPE_NAME"] = reader.GetString(5);
204  row["COLUMN_SIZE"] = reader.GetInt32(6);
205  row["BUFFER_LENGTH"] = reader.GetInt32(7);
206  row["DECIMAL_DIGITS"] = reader.GetInt32(8);
207  row["NUM_PREC_RADIX"] = reader.GetInt32(9);
208  row["NULLABLE"] = reader.GetBoolean(10);
209  row["REMARKS"] = reader.GetString(11);
210  row["COLUMN_DEFAULT"] = reader.GetString(12);
211  row["SQL_DATA_TYPE"] = reader.GetString(13);
212  row["SQL_DATETIME_SUB"] = reader.GetString(14);
213  row["CHAR_OCTET_LENGTH"] = reader.GetInt32(15);
214  row["ORDINAL_POSITION"] = reader.GetInt32(16);
215  row["IS_NULLABLE"] = reader.GetString(17) == "YES";
216  dataTable.Rows.Add(row);
217  }
218  }
219 
220  return dataTable;
221  }
222 
223  public SysDataTable GetColumnPrivileges(string[] restrictions) {
224  if (restrictions == null)
225  throw new ArgumentNullException("restrictions");
226  if (restrictions.Length < 3)
227  throw new ArgumentException();
228 
229  string catalog = restrictions[0];
230  string schema = restrictions[1];
231  string table = restrictions[2];
232  string column = restrictions[3];
233 
234  if (table == null)
235  throw new ArgumentException("The table name must be specified.");
236 
237  if (column == null)
238  column = "%";
239 
240  SysDataTable dataTable = new SysDataTable("Column_Privileges");
241 
242  dataTable.Columns.Add("TABLE_CATALOG");
243  dataTable.Columns.Add("TABLE_SCHEMA");
244  dataTable.Columns.Add("TABLE_NAME");
245  dataTable.Columns.Add("COLUMN_NAME");
246  dataTable.Columns.Add("GRANTOR");
247  dataTable.Columns.Add("GRANTEE");
248  dataTable.Columns.Add("PRIVILEGE");
249  dataTable.Columns.Add("IS_GRANTABLE", typeof(bool));
250 
251  DeveelDbCommand command = connection.CreateCommand(" SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES \n" +
252  " WHERE (? IS NULL OR \"TABLE_SCHEMA\" = ? ) \n" +
253  " AND (? IS NOT NULL AND \"TABLE_NAME\" = ? ) \n" +
254  " AND \"COLUMN_NAME\" LIKE ? \n" +
255  " ORDER BY \"COLUMN_NAME\", \"PRIVILEGE\" ");
256  command.Parameters.Add(schema);
257  command.Parameters.Add(schema);
258  command.Parameters.Add(table);
259  command.Parameters.Add(table);
260  command.Parameters.Add(column);
261 
262  command.Prepare();
263 
264  using (DeveelDbDataReader reader = command.ExecuteReader()) {
265  while (reader.Read()) {
266  SysDataRow row = dataTable.NewRow();
267  row["TABLE_CATALOG"] = reader.GetString(0);
268  row["TABLE_SCHEMA"] = reader.GetString(1);
269  row["TABLE_NAME"] = reader.GetString(2);
270  row["COLUMN_NAME"] = reader.GetString(3);
271  row["GRANTOR"] = reader.GetString(4);
272  row["GRANTEE"] = reader.GetString(5);
273  row["PRIVILEGE"] = reader.GetString(6);
274  row["IS_GRANTABLE"] = reader.GetString(7) == "YES";
275  dataTable.Rows.Add(row);
276  }
277  }
278 
279  return dataTable;
280  }
281 
282  public SysDataTable GetTablePrivileges(string[] restrictions) {
283  if (restrictions == null)
284  throw new ArgumentNullException("restrictions");
285  if (restrictions.Length < 3)
286  throw new ArgumentException();
287 
288  string catalog = restrictions[0];
289  string schema = restrictions[1];
290  string table = restrictions[2];
291 
292  if (schema == null)
293  schema = "%";
294  if (table == null)
295  table = "%";
296 
297  SysDataTable dataTable = new SysDataTable("TablePrivileges");
298  dataTable.Columns.Add("TABLE_CATALOG");
299  dataTable.Columns.Add("TABLE_SCHEMA");
300  dataTable.Columns.Add("TABLE_NAME");
301  dataTable.Columns.Add("PRIVILEGE");
302  dataTable.Columns.Add("GRANTOR");
303  dataTable.Columns.Add("GRANTEE");
304  dataTable.Columns.Add("IS_GRANTABLE", typeof(bool));
305 
306  DeveelDbCommand command = connection.CreateCommand(" SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES \n" +
307  " WHERE \"TABLE_SCHEMA\" LIKE ? \n" +
308  " AND \"TABLE_NAME\" LIKE ? \n" +
309  " ORDER BY \"TABLE_SCHEMA\", \"TABLE_NAME\", \"PRIVILEGE\" ");
310 
311  command.Parameters.Add(schema);
312  command.Parameters.Add(table);
313  command.Prepare();
314 
315  using (DeveelDbDataReader reader = command.ExecuteReader()) {
316  while (reader.Read()) {
317  SysDataRow row = dataTable.NewRow();
318  row["TABLE_CATALOG"] = reader.GetString(0);
319  row["TABLE_SCHEMA"] = reader.GetString(1);
320  row["TABLE_NAME"] = reader.GetString(2);
321  row["GRANTOR"] = reader.GetString(3);
322  row["GRANTEE"] = reader.GetString(4);
323  row["PRIVILEGE"] = reader.GetString(5);
324  row["IS_GRANTABLE"] = reader.GetString(6) == "YES";
325  dataTable.Rows.Add(row);
326  }
327  }
328 
329  return dataTable;
330  }
331 
332  public SysDataTable GetPrimaryKeys(string[] restrictions) {
333  if (restrictions == null)
334  throw new ArgumentNullException("restrictions");
335  if (restrictions.Length < 3)
336  throw new ArgumentException();
337 
338  string catalog = restrictions[0];
339  string schema = restrictions[1];
340  string table = restrictions[2];
341 
342  SysDataTable dataTable = new SysDataTable("PrimaryKeys");
343  dataTable.Columns.Add("TABLE_CATALOG");
344  dataTable.Columns.Add("TABLE_SCHEMA");
345  dataTable.Columns.Add("TABLE_NAME");
346  dataTable.Columns.Add("COLUMN_NAME");
347  dataTable.Columns.Add("KEY_SEQ", typeof(int));
348  dataTable.Columns.Add("PK_NAME");
349 
350  DeveelDbCommand command = connection.CreateCommand(" SELECT * \n" +
351  " FROM INFORMATION_SCHEMA.PrimaryKeys \n" +
352  " WHERE ( ? IS NULL OR \"TABLE_SCHEMA\" = ? ) \n" +
353  " AND \"TABLE_NAME\" = ? \n" +
354  " ORDER BY \"COLUMN_NAME\"");
355 
356  command.Parameters.Add(schema);
357  command.Parameters.Add(schema);
358  command.Parameters.Add(table);
359 
360  command.Prepare();
361 
362  using (DeveelDbDataReader reader = command.ExecuteReader()) {
363  SysDataRow row = dataTable.NewRow();
364  row["TABLE_CATALOG"] = reader.GetString(0);
365  row["TABLE_SCHEMA"] = reader.GetString(1);
366  row["TABLE_NAME"] = reader.GetString(2);
367  row["COLUMN_NAME"] = reader.GetString(3);
368  row["KEY_SEQ"] = reader.GetInt32(4);
369  row["PK_NAME"] = reader.GetString(5);
370  dataTable.Rows.Add(row);
371  }
372 
373  return dataTable;
374  }
375 
376  public SysDataTable GetImportedKeys(string[] restrictions) {
377  if (restrictions == null)
378  throw new ArgumentNullException("restrictions");
379  if (restrictions.Length < 3)
380  throw new ArgumentException();
381 
382  string catalog = restrictions[0];
383  string schema = restrictions[1];
384  string table = restrictions[2];
385 
386  var dataTable = new SysDataTable("ImportedKey");
387  dataTable.Columns.Add("PKTABLE_CATALOG");
388  dataTable.Columns.Add("PKTABLE_SCHEMA");
389  dataTable.Columns.Add("PKTABLE_NAME");
390  dataTable.Columns.Add("PKCOLUMN_NAME");
391  dataTable.Columns.Add("FKTABLE_CATALOG");
392  dataTable.Columns.Add("FKTABLE_SCHEMA");
393  dataTable.Columns.Add("FKTABLE_NAME");
394  dataTable.Columns.Add("FKCOLUMN_NAME");
395  dataTable.Columns.Add("KEY_SEQ");
396  dataTable.Columns.Add("UPDATE_RULE");
397  dataTable.Columns.Add("DELETE_RULE");
398  dataTable.Columns.Add("FK_NAME");
399  dataTable.Columns.Add("PK_NAME");
400  dataTable.Columns.Add("DEFERRABILITY");
401 
402  DeveelDbCommand command = connection.CreateCommand(" SELECT * FROM INFORMATION_SCHEMA.imported_keys \n" +
403  " WHERE ( ? IS NULL OR \"FKTABLE_SCHEMA\" = ? )\n" +
404  " AND \"FKTABLE_NAME\" = ? \n" +
405  " ORDER BY \"FKTABLE_SCHEMA\", \"FKTABLE_NAME\", \"KEY_SEQ\"");
406 
407  command.Parameters.Add(schema);
408  command.Parameters.Add(schema);
409  command.Parameters.Add(table);
410 
411  command.Prepare();
412 
413  using (DeveelDbDataReader reader = command.ExecuteReader()) {
414  if (reader.HasRows) {
415  while (reader.Read()) {
416  var row = dataTable.NewRow();
417  row["PKTABLE_CATALOG"] = reader.GetString(0);
418  row["PKTABLE_SCHEMA"] = reader.GetString(1);
419  row["PKTABLE_NAME"] = reader.GetString(2);
420  row["PKCOLUMN_NAME"] = reader.GetString(3);
421  row["FKTABLE_CATALOG"] = reader.GetString(4);
422  row["FKTABLE_SCHEMA"] = reader.GetString(5);
423  row["FKTABLE_NAME"] = reader.GetString(6);
424  row["FKCOLUMN_NAME"] = reader.GetString(7);
425  row["KEY_SEQ"] = reader.GetInt32(8);
426  row["UPDATE_RULE"] = reader.GetString(9);
427  row["DELETE_RULE"] = reader.GetString(10);
428  row["FK_NAME"] = reader.GetString(11);
429  row["PK_NAME"] = reader.GetString(12);
430  row["DEFERRABILITY"] = reader.GetInt32(13);
431  dataTable.Rows.Add(row);
432  }
433  }
434  }
435 
436  return dataTable;
437  }
438 
439  public SysDataTable GetExportedKeys(string[] restrictions) {
440  if (restrictions == null)
441  throw new ArgumentNullException("restrictions");
442  if (restrictions.Length < 3)
443  throw new ArgumentException();
444 
445  string catalog = restrictions[0];
446  string schema = restrictions[1];
447  string table = restrictions[2];
448 
449  SysDataTable dataTable = new SysDataTable("ExportedKey");
450  dataTable.Columns.Add("PKTABLE_CATALOG");
451  dataTable.Columns.Add("PKTABLE_SCHEMA");
452  dataTable.Columns.Add("PKTABLE_NAME");
453  dataTable.Columns.Add("PKCOLUMN_NAME");
454  dataTable.Columns.Add("FKTABLE_CATALOG");
455  dataTable.Columns.Add("FKTABLE_SCHEMA");
456  dataTable.Columns.Add("FKTABLE_NAME");
457  dataTable.Columns.Add("FKCOLUMN_NAME");
458  dataTable.Columns.Add("KEY_SEQ");
459  dataTable.Columns.Add("UPDATE_RULE");
460  dataTable.Columns.Add("DELETE_RULE");
461  dataTable.Columns.Add("FK_NAME");
462  dataTable.Columns.Add("PK_NAME");
463  dataTable.Columns.Add("DEFERRABILITY");
464 
465  DeveelDbCommand command = connection.CreateCommand(" SELECT * FROM INFORMATION_SCHEMA.imported_keys \n" +
466  " WHERE ( ? IS NULL OR \"PKTABLE_SCHEMA\" = ? ) \n" +
467  " AND \"PKTABLE_NAME\" = ? \n" +
468  "ORDER BY \"FKTABLE_SCHEMA\", \"FKTABLE_NAME\", \"KEY_SEQ\"");
469 
470  command.Parameters.Add(schema);
471  command.Parameters.Add(schema);
472  command.Parameters.Add(table);
473 
474  command.Prepare();
475 
476  using (DeveelDbDataReader reader = command.ExecuteReader()) {
477  if (reader.HasRows) {
478  while (reader.Read()) {
479  var row = dataTable.NewRow();
480  row["PKTABLE_CATALOG"] = reader.GetString(0);
481  row["PKTABLE_SCHEMA"] = reader.GetString(1);
482  row["PKTABLE_NAME"] = reader.GetString(2);
483  row["PKCOLUMN_NAME"] = reader.GetString(3);
484  row["FKTABLE_CATALOG"] = reader.GetString(4);
485  row["FKTABLE_SCHEMA"] = reader.GetString(5);
486  row["FKTABLE_NAME"] = reader.GetString(6);
487  row["FKCOLUMN_NAME"] = reader.GetString(7);
488  row["KEY_SEQ"] = reader.GetInt32(8);
489  row["UPDATE_RULE"] = reader.GetString(9);
490  row["DELETE_RULE"] = reader.GetString(10);
491  row["FK_NAME"] = reader.GetString(11);
492  row["PK_NAME"] = reader.GetString(12);
493  row["DEFERRABILITY"] = reader.GetInt32(13);
494  dataTable.Rows.Add(row);
495  }
496  }
497  }
498 
499  return dataTable;
500  }
501 
502  public SysDataTable GetRestrictions() {
503  object[][] restrictions = new object[][]
504  {
505  new object[] {"Schemata", "Schema", "", 0},
506  new object[] {"Tables", "Catalog", "", 0},
507  new object[] {"Tables", "Schema", "", 1},
508  new object[] {"Tables", "Table", "", 2},
509  new object[] {"Tables", "TableType", "", 3},
510  new object[] {"Columns", "Catalog", "", 0},
511  new object[] {"Columns", "Schema", "", 1},
512  new object[] {"Columns", "Table", "", 2},
513  new object[] {"Columns", "Column", "", 3},
514  new object[] {"PrimaryKeys", "Database", "", 0},
515  new object[] {"PrimaryKeys", "Schema", "", 1},
516  new object[] {"PrimaryKeys", "Table", "", 2},
517  new object[] {"ExportedKeys", "Catalog", "", 0},
518  new object[] {"ExportedKeys", "Schema", "", 1},
519  new object[] {"ExportedKeys", "Table", "", 2},
520  new object[] {"ImportedKeys", "Catalog", "", 0},
521  new object[] {"ImportedKeys", "Schema", "", 1},
522  new object[] {"ImportedKeys", "Table", "", 2},
523  new object[] {"ColumnPrivileges", "Catalog", "", 0},
524  new object[] {"ColumnPrivileges", "Schema", "", 1},
525  new object[] {"ColumnPrivileges", "Table", "", 2},
526  new object[] {"ColumnPrivileges", "Column", "", 3},
527  new object[] {"TablePrivileges", "Catalog", "", 0},
528  new object[] {"TablePrivileges", "Schema", "", 1},
529  new object[] {"TablePrivileges", "Table", "", 2},
530  new object[] {"UserPrivileges", "UserName", "", 0}
531  };
532 
533  SysDataTable dt = new SysDataTable("Restrictions");
534  dt.Columns.Add(new DataColumn("CollectionName", typeof(string)));
535  dt.Columns.Add(new DataColumn("RestrictionName", typeof(string)));
536  dt.Columns.Add(new DataColumn("RestrictionDefault", typeof(string)));
537  dt.Columns.Add(new DataColumn("RestrictionNumber", typeof(int)));
538 
539  FillTable(dt, restrictions);
540 
541  return dt;
542  }
543 
544  private SysDataTable GetCollections() {
545  object[][] collections = new object[][]
546  {
547  new object[] {"MetaDataCollections", 0, 0},
548  new object[] {"DataSourceInformation", 0, 0},
549  new object[] {"DataTypes", 0, 0},
550  new object[] {"Restrictions", 0, 0},
551  new object[] {"ReservedWords", 0, 0},
552  new object[] {"Databases", 1, 1},
553  new object[] {"Tables", 4, 2},
554  new object[] {"Columns", 4, 4},
555  new object[] {"PrimaryKeys", 4, 3},
556  new object[] {"ExportedKeys", 4, 3},
557  new object[] {"ImportedKeys", 4, 3},
558  new object[] { "UserPrivileges", 1, 0}
559  };
560 
561  SysDataTable dt = new SysDataTable("MetaDataCollections");
562  dt.Columns.Add("CollectionName", typeof(string));
563  dt.Columns.Add("NumberOfRestrictions", typeof(int));
564  dt.Columns.Add("NumberOfIdentifierParts", typeof(int));
565 
566  FillTable(dt, collections);
567 
568  return dt;
569  }
570 
571  private SysDataTable GetDataSourceInformation() {
572  SysDataTable dt = new SysDataTable("DataSourceInformation");
573  dt.Columns.Add("CompositeIdentifierSeparatorPattern", typeof(string));
574  dt.Columns.Add("DataSourceProductName", typeof(string));
575  dt.Columns.Add("DataSourceProductVersion", typeof(string));
576  dt.Columns.Add("DataSourceProductVersionNormalized", typeof(string));
577  dt.Columns.Add("GroupByBehavior", typeof(GroupByBehavior));
578  dt.Columns.Add("IdentifierPattern", typeof(string));
579  dt.Columns.Add("IdentifierCase", typeof(IdentifierCase));
580  dt.Columns.Add("OrderByColumnsInSelect", typeof(bool));
581  dt.Columns.Add("ParameterMarkerFormat", typeof(string));
582  dt.Columns.Add("ParameterMarkerPattern", typeof(string));
583  dt.Columns.Add("ParameterNameMaxLength", typeof(int));
584  dt.Columns.Add("ParameterNamePattern", typeof(string));
585  dt.Columns.Add("QuotedIdentifierPattern", typeof(string));
586  dt.Columns.Add("QuotedIdentifierCase", typeof(IdentifierCase));
587  dt.Columns.Add("StatementSeparatorPattern", typeof(string));
588  dt.Columns.Add("StringLiteralPattern", typeof(string));
589  dt.Columns.Add("SupportedJoinOperators", typeof(SupportedJoinOperators));
590 
591  SysDataRow row = dt.NewRow();
592  row["CompositeIdentifierSeparatorPattern"] = "\\.";
593  row["DataSourceProductName"] = "DeveelDB";
594  row["DataSourceProductVersion"] = connection.ServerVersion;
595  row["DataSourceProductVersionNormalized"] = connection.ServerVersion;
596  row["GroupByBehavior"] = GroupByBehavior.Unrelated;
597  row["IdentifierPattern"] =
598  @"(^\`\p{Lo}\p{Lu}\p{Ll}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Nd}@$#_]*$)|(^\`[^\`\0]|\`\`+\`$)|(^\"" + [^\""\0]|\""\""+\""$)";
599  row["IdentifierCase"] = IdentifierCase.Insensitive;
600  row["OrderByColumnsInSelect"] = false;
601  row["ParameterMarkerFormat"] = "{0}";
602  row["ParameterMarkerPattern"] = "(@[A-Za-z0-9_$#]*)";
603  row["ParameterNameMaxLength"] = 128;
604  row["ParameterNamePattern"] = @"^[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)";
605  row["QuotedIdentifierPattern"] = @"(([^\`]|\`\`)*)";
606  row["QuotedIdentifierCase"] = IdentifierCase.Sensitive;
607  row["StatementSeparatorPattern"] = ";";
608  row["StringLiteralPattern"] = "'(([^']|'')*)'";
609  row["SupportedJoinOperators"] = 15;
610  dt.Rows.Add(row);
611 
612  return dt;
613  }
614 
615  public SysDataTable GetDataTypes() {
616  SysDataTable dataTable = new SysDataTable("DataTypes");
617 
618  dataTable.Columns.Add("TYPE_NAME", typeof(string));
619  dataTable.Columns.Add("DATA_TYPE", typeof(int));
620  dataTable.Columns.Add("PRECISION", typeof(int));
621  dataTable.Columns.Add("LITERAL_PREFIX", typeof(string));
622  dataTable.Columns.Add("LITERAL_SUFFIX", typeof(string));
623  dataTable.Columns.Add("CREATE_PARAMS", typeof(string));
624  dataTable.Columns.Add("NULLABLE", typeof(bool));
625  dataTable.Columns.Add("CASE_SENSITIVE", typeof(bool));
626  dataTable.Columns.Add("SEARCHABLE", typeof(bool));
627  dataTable.Columns.Add("UNSIGNED_ATTRIBUTE", typeof(bool));
628  dataTable.Columns.Add("FIXED_PREC_SCALE", typeof(bool));
629  dataTable.Columns.Add("AUTO_INCREMENT", typeof(bool));
630  dataTable.Columns.Add("LOCAL_TYPE_NAME");
631  dataTable.Columns.Add("MINIMUM_SCALE", typeof(int));
632  dataTable.Columns.Add("MAXIMUM_SCALE", typeof(int));
633  dataTable.Columns.Add("SQL_DATA_TYPE", typeof(string));
634  dataTable.Columns.Add("SQL_DATETIME_SUB", typeof(string));
635  dataTable.Columns.Add("NUM_PREC_RADIX", typeof(int));
636 
637  DeveelDbCommand command = connection.CreateCommand("SELECT * FROM INFORMATION_SCHEMA.DATA_TYPES");
638 
639  using (DeveelDbDataReader reader = command.ExecuteReader()) {
640  while (reader.Read()) {
641  SysDataRow row = dataTable.NewRow();
642 
643  row["TYPE_NAME"] = reader.GetString(0);
644  row["DATA_TYPE"] = reader.GetInt32(1);
645  row["PRECISION"] = reader.GetInt32(2);
646  row["LITERAL_PREFIX"] = reader.GetString(3);
647  row["LITERAL_SUFFIX"] = reader.GetString(4);
648  row["CREATE_PARAMS"] = reader.GetString(5);
649  row["NULLABLE"] = reader.GetBoolean(6);
650  row["CASE_SENSITIVE"] = reader.GetBoolean(7);
651  row["SEARCHABLE"] = reader.GetBoolean(8);
652  row["UNSIGNED_ATTRIBUTE"] = reader.GetBoolean(9);
653  row["FIXED_PREC_SCALE"] = reader.GetBoolean(10);
654  row["AUTO_INCREMENT"] = reader.GetBoolean(11);
655  row["LOCAL_TYPE_NAME"] = reader.GetString(12);
656  row["MINIMUM_SCALE"] = reader.GetInt32(13);
657  row["MAXIMUM_SCALE"] = reader.GetInt32(14);
658  row["SQL_DATA_TYPE"] = reader.GetString(15);
659  row["SQL_DATETIME_SUB"] = reader.GetString(16);
660  row["NUM_PREC_RADIX"] = reader.GetInt32(17);
661 
662  dataTable.Rows.Add(row);
663  }
664  }
665 
666  return dataTable;
667  }
668 
669  public virtual SysDataTable GetUserPrivileges(string[] restrictions) {
670  if (restrictions == null)
671  throw new ArgumentNullException("restrictions");
672  if (restrictions.Length < 1)
673  throw new ArgumentException();
674 
675  var userName = restrictions[0];
676 
677  var dataTable = new SysDataTable("UserPrivileges");
678  dataTable.Columns.Add("TABLE_CATALOG");
679  dataTable.Columns.Add("GRANTEE");
680  dataTable.Columns.Add("OBJECT_TYPE");
681  dataTable.Columns.Add("OBJECT_NAME");
682  dataTable.Columns.Add("PRIVS");
683  dataTable.Columns.Add("IS_GRANTABLE", typeof(bool));
684  dataTable.Columns.Add("GRANTER");
685 
686  var command = connection.CreateCommand("SELECT * FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE (? IS NULL OR \"GRANTEE\" = ?)");
687  command.Parameters.Add(userName);
688  command.Parameters.Add(userName);
689 
690  command.Prepare();
691 
692  using (var reader = command.ExecuteReader()) {
693  while (reader.Read()) {
694  var row = dataTable.NewRow();
695  row["TABLE_CATALOG"] = reader.GetString(0);
696  row["GRANTEE"] = reader.GetString(1);
697  row["OBJECT_TYPE"] = reader.GetString(2);
698  row["OBJECT_NAME"] = reader.GetString(3);
699  row["PRIVS"] = reader.GetString(4);
700  row["IS_GRANTABLE"] = reader.GetBoolean(5);
701  dataTable.Rows.Add(row);
702  }
703  }
704 
705  return dataTable;
706  }
707 
708  public virtual SysDataTable GetSchema(string collection, String[] restrictions) {
709  if (connection.State != ConnectionState.Open)
710  throw new DataException("GetSchema can only be called on an open connection.");
711 
712  collection = collection.ToUpper(CultureInfo.InvariantCulture);
713 
714  SysDataTable dt = null;
715 
716  switch (collection) {
717  // common collections
718  case "METADATACOLLECTIONS":
719  dt = GetCollections();
720  break;
721  case "DATASOURCEINFORMATION":
722  dt = GetDataSourceInformation();
723  break;
724  case "DATATYPES":
725  dt = GetDataTypes();
726  break;
727  case "RESTRICTIONS":
728  dt = GetRestrictions();
729  break;
730  case "RESERVEDWORDS":
731  //TODO: dt = GetReservedWords();
732  break;
733  }
734 
735  if (restrictions == null)
736  restrictions = new string[2];
737  if (connection != null &&
738  connection.Settings.Schema != null &&
739  connection.Settings.Schema.Length > 0 &&
740  restrictions.Length > 1 &&
741  restrictions[1] == null)
742  restrictions[1] = connection.Settings.Schema;
743 
744  switch (collection) {
745  case "SCHEMATA":
746  dt = GetSchemata(restrictions);
747  break;
748  case "TABLES":
749  dt = GetTables(restrictions);
750  break;
751  case "COLUMNS":
752  dt = GetColumns(restrictions);
753  break;
754  case "TABLEPRIVILEGES":
755  dt = GetTablePrivileges(restrictions);
756  break;
757  case "COLUMNPRIVILEGES":
758  dt = GetColumnPrivileges(restrictions);
759  break;
760  case "PRIMARYKEYS":
761  dt = GetPrimaryKeys(restrictions);
762  break;
763  case "EXPORTEDKEYS":
764  dt = GetExportedKeys(restrictions);
765  break;
766  case "IMPORTEDKEYS":
767  dt = GetImportedKeys(restrictions);
768  break;
769  case "USERPRIVILEGES":
770  dt = GetUserPrivileges(restrictions);
771  break;
772  }
773 
774 
775  if (dt == null)
776  throw new DataException("Invalid collection name");
777 
778  return dt;
779  }
780 
781  private static void FillTable(SysDataTable dt, object[][] data) {
782  foreach (object[] dataItem in data) {
783  SysDataRow row = dt.NewRow();
784  for (int i = 0; i < dataItem.Length; i++)
785  row[i] = dataItem[i];
786  dt.Rows.Add(row);
787  }
788  }
789  }
790 }
Deveel.Data.Client.DatabaseMetadata.GetTables
SysDataTable GetTables(string[] restrictions)
Definition: DatabaseMetadata.cs:67
Deveel.Data.Client.DatabaseMetadata.GetColumns
SysDataTable GetColumns(string[] restrictions)
Definition: DatabaseMetadata.cs:146
Deveel.Data.Client.DatabaseMetadata.connection
readonly DeveelDbConnection connection
Definition: DatabaseMetadata.cs:31
Deveel.Data.Client.DatabaseMetadata.DatabaseMetadata
DatabaseMetadata(DeveelDbConnection connection)
Definition: DatabaseMetadata.cs:27
Deveel.Data.Client.DeveelDbCommand.Parameters
new DeveelDbParameterCollection Parameters
Definition: DeveelDbCommand.cs:236
SysDataTable
System.Data.DataTable SysDataTable
Definition: DatabaseMetadata.cs:22
Deveel.Data.Client.DatabaseMetadata.GetExportedKeys
SysDataTable GetExportedKeys(string[] restrictions)
Definition: DatabaseMetadata.cs:439
Deveel.Data.Client.DeveelDbCommand.ExecuteReader
new DeveelDbDataReader ExecuteReader(CommandBehavior behavior)
Definition: DeveelDbCommand.cs:313
Deveel.Data.Client.DeveelDbDataReader.GetBoolean
override bool GetBoolean(int ordinal)
Definition: DeveelDbDataReader.cs:217
Deveel.Data.Client.DatabaseMetadata.GetSchema
virtual SysDataTable GetSchema(string collection, String[] restrictions)
Definition: DatabaseMetadata.cs:708
Deveel.Data.Client.DatabaseMetadata.FillTable
static void FillTable(SysDataTable dt, object[][] data)
Definition: DatabaseMetadata.cs:781
Deveel.Data.Client.DeveelDbDataReader.GetString
override string GetString(int ordinal)
Definition: DeveelDbDataReader.cs:257
Deveel.Data.Client.DatabaseMetadata.GetUserPrivileges
virtual SysDataTable GetUserPrivileges(string[] restrictions)
Definition: DatabaseMetadata.cs:669
Deveel.Data.Client.DatabaseMetadata.GetSchemata
SysDataTable GetSchemata(string[] restrictions)
Definition: DatabaseMetadata.cs:33
Deveel.Data.Client.DatabaseMetadata.GetPrimaryKeys
SysDataTable GetPrimaryKeys(string[] restrictions)
Definition: DatabaseMetadata.cs:332
Deveel.Data.Client.DatabaseMetadata.GetTablePrivileges
SysDataTable GetTablePrivileges(string[] restrictions)
Definition: DatabaseMetadata.cs:282
Deveel.Data.Client.DatabaseMetadata.GetImportedKeys
SysDataTable GetImportedKeys(string[] restrictions)
Definition: DatabaseMetadata.cs:376
Deveel.Data.Client.DatabaseMetadata.GetColumnPrivileges
SysDataTable GetColumnPrivileges(string[] restrictions)
Definition: DatabaseMetadata.cs:223
Generated by   doxygen 1.8.10