extras/MonoDevelop.Database/MonoDevelop.Database.Sql.Firebird/FirebirdSchemaProvider.cs

   1 //
   2 // Authors:
   3 //   Christian Hergert  <chris@mosaix.net>
   4 //   Daniel Morgan <danielmorgan@verizon.net>
   5 //   Ben Motmans  <ben.motmans@gmail.com>
   6 //
   7 // Copyright (C) 2005 Mosaix Communications, Inc.
   8 // Copyright (C) 2005 Daniel Morgan
   9 // Copyright (c) 2007 Ben Motmans
  10 //
  11 // Permission is hereby granted, free of charge, to any person obtaining a copy
  12 // of this software and associated documentation files (the "Software"), to deal
  13 // in the Software without restriction, including without limitation the rights
  14 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
  15 // copies of the Software, and to permit persons to whom the Software is
  16 // furnished to do so, subject to the following conditions:
  17 //
  18 // The above copyright notice and this permission notice shall be included in
  19 // all copies or substantial portions of the Software.
  20 //
  21 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  22 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  23 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
  24 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  25 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  26 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
  27 // THE SOFTWARE.
  28 //
  29
  30 using System;
  31 using System.Data;
  32 using FirebirdSql.Data.Firebird;
  33 using System.Collections.Generic;
  34 
namespace MonoDevelop.Database.Sql
  35 {
  36         // see:
  37         // http://firebird.sourceforge.net/index.php?op=devel&sub=netprovider&id=examples
  38         // http://www.alberton.info/firebird_sql_meta_info.html
  39         public class FirebirdSchemaProvider : AbstractSchemaProvider
  40         {
  41                 public FirebirdSchemaProvider (IConnectionProvider connectionProvider)
  42                         : base (connectionProvider)
  43                 {
  44                 }
  45
  46                 public override bool SupportsSchemaType (Type type)
  47                 {
  48                         if (type == typeof(TableSchema))
  49                                 return true;
  50                         else if (type == typeof(ViewSchema))
  51                                 return true;
  52                         else if (type == typeof(ProcedureSchema))
  53                                 return true;
  54                         else if (type == typeof(AggregateSchema))
  55                                 return true;
  56                         else if (type == typeof(GroupSchema))
  57                                 return true;
  58                         else if (type == typeof(UserSchema))
  59                                 return true;
  60                         else if (type == typeof(LanguageSchema))
  61                                 return true;
  62                         else if (type == typeof(OperatorSchema))
  63                                 return true;
  64                         else if (type == typeof(RoleSchema))
  65                                 return true;
  66                         else if (type == typeof(SequenceSchema))
  67                                 return true;
  68                         else if (type == typeof(DataTypeSchema))
  69                                 return true;
  70                         else if (type == typeof(TriggerSchema))
  71                                 return true;
  72                         else if (type == typeof(ColumnSchema))
  73                                 return true;
  74                         else if (type == typeof(ConstraintSchema))
  75                                 return true;
  76                         else if (type == typeof(RuleSchema))
  77                                 return true;
  78                         else
  79                                 return false;
  80                 }
  81
  82                 public override ICollection<TableSchema> GetTables ()
  83                 {
  84                         CheckConnectionState ();
  85                         List<TableSchema> tables = new List<TableSchema> ();
  86
  87                         IDbCommand command = connectionProvider.CreateCommand (
  88                                 "SELECT RDB$RELATION_NAME, RDB$SYSTEM_FLAG, RDB$OWNER_NAME, RDB$DESCRIPTION FROM RDB$RELATIONS "+
  89                                 "WHERE RDB$VIEW_BLR IS NULL;"
  90                         );
  91
  92                         using (command) {
  93                                 using (IDataReader r = command.ExecuteReader()) {
  94                                         while (r.Read ()) {
  95                                                 TableSchema table = new TableSchema (this);
  96
  97                                                 table.Name = r.GetString (0);
  98                                                 table.IsSystemTable = (!r.IsDBNull (1) && r.GetInt32 (1) != 0);
  99                                                 table.OwnerName = r.GetString (2);
 100                                                 table.Comment = r.GetString (3);
 101
 102                                                 tables.Add (table);
 103                                         }
 104                                         r.Close ();
 105                                 }
 106                                 connectionProvider.Close (command.Connection);
 107                         }
 108
 109                         return tables;
 110                 }
 111
 112                 public override ICollection<ColumnSchema> GetTableColumns (TableSchema table)
 113                 {
 114                         return GetTableOrViewColumns (table.Name);
 115                 }
 116
 117                 public override ICollection<ViewSchema> GetViews ()
 118                 {
 119                         CheckConnectionState ();
 120                         List<ViewSchema> views = new List<ViewSchema> ();
 121
 122                         IDbCommand command = connectionProvider.CreateCommand (
 123                                 "SELECT RDB$RELATION_NAME, RDB$SYSTEM_FLAG, RDB$OWNER_NAME, RDB$DESCRIPTION, RDB$VIEW_SOURCE FROM RDB$RELATIONS "+
 124                                 "WHERE RDB$VIEW_SOURCE IS NOT NULL;"
 125                         );
 126                         using (command) {
 127                                 using (IDataReader r = command.ExecuteReader()) {
 128                                         while (r.Read ()) {
 129                                                 ViewSchema view = new ViewSchema (this);
 130
 131                                                 view.Name = r.GetString (0);
 132                                                 view.IsSystemView = (!r.IsDBNull (1) && r.GetInt32 (1) != 0);
 133                                                 view.OwnerName = r.GetString (2);
 134                                                 view.Comment = r.GetString (3);
 135                                                 //TODO: view.Definition = 4 (ascii blob)
 136
 137                                                 views.Add (view);
 138                                         }
 139                                         r.Close ();
 140                                 }
 141                                 connectionProvider.Close (command.Connection);
 142                         }
 143                         return views;
 144                 }
 145
 146                 public override ICollection<ColumnSchema> GetViewColumns (ViewSchema view)
 147                 {
 148                         return GetTableOrViewColumns (view.Name);
 149                 }
 150
 151                 public override ICollection<ProcedureSchema> GetProcedures ()
 152                 {
 153                         CheckConnectionState ();
 154                         List<ProcedureSchema> procedures = new List<ProcedureSchema> ();
 155
 156                         IDbCommand command = connectionProvider.CreateCommand (
 157                                 "SELECT RDB$PROCEDURE_NAME, RDB$SYSTEM_FLAG, RDB$OWNER_NAME, RDB$DESCRIPTION, RDB$PROCEDURE_SOURCE FROM RDB$PROCEDURES;"
 158                         );
 159
 160                         using (command) {
 161                                 using (IDataReader r = command.ExecuteReader()) {
 162                                         while (r.Read ()) {
 163                                                 ProcedureSchema procedure = new ProcedureSchema (this);
 164
 165                                                 procedure.Name = r.GetString (0);
 166                                                 procedure.IsSystemProcedure = (!r.IsDBNull (1) && r.GetInt32 (1) != 0);
 167                                                 procedure.OwnerName = r.GetString (2);
 168                                                 procedure.Comment = r.GetString (3);
 169                                                 //TODO: procedure.Definition = 4 (ascii blob)
 170
 171                                                 procedures.Add (procedure);
 172                                         }
 173                                         r.Close ();
 174                                 }
 175                                 connectionProvider.Close (command.Connection);
 176                         }
 177
 178                         return procedures;
 179                 }
 180
 181                 public override ICollection<ColumnSchema> GetProcedureColumns (ProcedureSchema procedure)
 182                 {
 183                         CheckConnectionState ();
 184                         List<ColumnSchema> columns = new List<ColumnSchema> ();
 185
 186                         IDbCommand command = connectionProvider.CreateCommand (
 187                                 "SELECT RDB$PARAMETER_NAME, RDB$PARAMETER_NUMBER, RDB$DESCRIPTION, RDB$SYSTEM_FLAG "
 188                                 + "FROM RDB$PROCEDURE_PARAMETERS "
 189                                 + "WHERE RDB$PROCEDURE_NAME = '" + procedure.Name + "' "
 190                                 + "AND RDB$PARAMETER_TYPE = 1 "
 191                                 + "ORDER BY 2;"
 192                         );
 193
 194                         using (command) {
 195                                 using (IDataReader r = command.ExecuteReader()) {
 196                                         while (r.Read ()) {
 197                                                 ColumnSchema column = new ColumnSchema (this);
 198
 199                                                 column.Name = r.GetString (0);
 200                                                 column.OwnerName = procedure.Name;
 201                                                 column.Comment = r.GetString (2);
 202                                                 //TODO: data type
 203
 204                                                 columns.Add (column);
 205                                         }
 206                                         r.Close ();
 207                                 }
 208                                 connectionProvider.Close (command.Connection);
 209                         }
 210
 211                         return columns;
 212                 }
 213
 214                 public override ICollection<ParameterSchema> GetProcedureParameters (ProcedureSchema procedure)
 215                 {
 216                         CheckConnectionState ();
 217                         List<ParameterSchema> parameters = new List<ParameterSchema> ();
 218
 219                         IDbCommand command = connectionProvider.CreateCommand (
 220                                 "SELECT RDB$PARAMETER_NAME, RDB$PARAMETER_NUMBER, RDB$DESCRIPTION, RDB$SYSTEM_FLAG "
 221                                 + "FROM RDB$PROCEDURE_PARAMETERS "
 222                                 + "WHERE RDB$PROCEDURE_NAME = '" + procedure.Name + "' "
 223                                 + "AND RDB$PARAMETER_TYPE = 0 "
 224                                 + "ORDER BY 2;"
 225                         );
 226
 227                         using (command) {
 228                                 using (IDataReader r = command.ExecuteReader()) {
 229                                         while (r.Read ()) {
 230                                                 ParameterSchema parameter = new ParameterSchema (this);
 231
 232                                                 parameter.Name = r.GetString (0);
 233                                                 parameter.OwnerName = procedure.Name;
 234                                                 parameter.Comment = r.GetString (2);
 235                                                 //TODO: data type
 236
 237                                                 parameters.Add (parameter);
 238                                         }
 239                                         r.Close ();
 240                                 }
 241                                 connectionProvider.Close (command.Connection);
 242                         }
 243
 244                         return parameters;
 245                 }
 246
 247                 //TODO:
 248                 public override ICollection<ConstraintSchema> GetTableConstraints (TableSchema table)
 249                 {
 250                         CheckConnectionState ();
 251                         List<ConstraintSchema> constraints = new List<ConstraintSchema> ();
 252
 253                         IDbCommand command = connectionProvider.CreateCommand ("SHOW TABLE STATUS FROM `" + table.OwnerName + "`;");
 254                         using (command) {
 255                                 using (IDataReader r = command.ExecuteReader()) {
 256 //                                      ConstraintSchema constraint = new ConstraintSchema (this);
 257 //                                      constraint.PrimaryKey = pkColumn;
 258 //                                      constraint.ForeignKey = fkColumn;
 259 //
 260 //                                      constraints.Add (constraint);
 261                                         r.Close ();
 262                                 }
 263                                 connectionProvider.Close (command.Connection);
 264                         }
 265
 266                         return constraints;
 267                 }
 268
 269                 public override ICollection<UserSchema> GetUsers ()
 270                 {
 271                         CheckConnectionState ();
 272                         List<UserSchema> users = new List<UserSchema> ();
 273
 274                         IDbCommand command = connectionProvider.CreateCommand ("SELECT DISTINCT RDB$USER FROM RDB$USER_PRIVILEGES;");
 275                         using (command) {
 276                                 using (IDataReader r = command.ExecuteReader ()) {
 277                                         while (r.Read ()) {
 278                                                 UserSchema user = new UserSchema (this);
 279                                                 user.Name = r.GetString (0);
 280                                                 users.Add (user);
 281                                         }
 282                                         r.Close ();
 283                                 }
 284                                 connectionProvider.Close (command.Connection);
 285                         }
 286
 287                         return users;
 288                 }
 289
 290                 public override ICollection<TriggerSchema> GetTriggers ()
 291                 {
 292                         CheckConnectionState ();
 293                         List<TriggerSchema> triggers = new List<TriggerSchema> ();
 294
 295                         IDbCommand command = connectionProvider.CreateCommand ("SELECT RDB$TRIGGER_NAME, RDB$RELATION_NAME, RDB$TRIGGER_TYPE FROM RDB$TRIGGERS;");
 296                         using (command) {
 297                                 using (IDataReader r = command.ExecuteReader ()) {
 298                                         while (r.Read ()) {
 299                                                 TriggerSchema trigger = new TriggerSchema (this);
 300                                                 trigger.Name = r.GetString (0);
 301                                                 //TODO: table and type
 302                                                 triggers.Add (trigger);
 303                                         }
 304                                         r.Close ();
 305                                 }
 306                                 connectionProvider.Close (command.Connection);
 307                         }
 308
 309                         return triggers;
 310                 }
 311
 312                 //TODO:
 313                 public override DataTypeSchema GetDataType (string name)
 314                 {
 315                         if (name == null)
 316                                 throw new ArgumentNullException ("name");
 317                         name = name.ToUpper ();
 318
 319                         DataTypeSchema dts = new DataTypeSchema (this);
 320                         dts.Name = name;
 321                         switch (name) {
 322                                         //TODO: IMPLEMENT
 323                                 case "":
 324                                         break;
 325                                 default:
 326                                         dts = null;
 327                                         break;
 328                         }
 329
 330                         return dts;
 331                 }
 332
 333                 private ICollection<ColumnSchema> GetTableOrViewColumns (string name)
 334                 {
 335                         CheckConnectionState ();
 336                         List<ColumnSchema> columns = new List<ColumnSchema> ();
 337
 338                         IDbCommand command = connectionProvider.CreateCommand (
 339                                 "SELECT r.RDB$FIELD_NAME AS field_name, r.RDB$DESCRIPTION AS field_description, "
 340                                 + "r.RDB$DEFAULT_VALUE AS field_default_value, r.RDB$NULL_FLAG AS field_not_null_constraint, "
 341                                 + "f.RDB$FIELD_LENGTH AS field_length, f.RDB$FIELD_PRECISION AS field_precision, "
 342                                 + "f.RDB$FIELD_SCALE AS field_scale, "
 343                                 + "CASE f.RDB$FIELD_TYPE WHEN 261 THEN 'BLOB' WHEN 14 THEN 'CHAR' WHEN 40 THEN 'CSTRING' "
 344                                 + "WHEN 11 THEN 'D_FLOAT' WHEN 27 THEN 'DOUBLE' WHEN 10 THEN 'FLOAT' WHEN 16 THEN 'INT64' "
 345                                 + "WHEN 8 THEN 'INTEGER' WHEN 9 THEN 'QUAD' WHEN 7 THEN 'SMALLINT' WHEN 12 THEN 'DATE' "
 346                                 + "WHEN 13 THEN 'TIME' WHEN 35 THEN 'TIMESTAMP' WHEN 37 THEN 'VARCHAR' ELSE 'UNKNOWN' "
 347                                 + "END AS field_type, f.RDB$FIELD_SUB_TYPE AS field_subtype, "
 348                                 + "coll.RDB$COLLATION_NAME AS field_collation, cset.RDB$CHARACTER_SET_NAME AS field_charset "
 349                                 + "FROM RDB$RELATION_FIELDS r LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME "
 350                                 + "LEFT JOIN RDB$COLLATIONS coll ON f.RDB$COLLATION_ID = coll.RDB$COLLATION_ID "
 351                                 + "LEFT JOIN RDB$CHARACTER_SETS cset ON f.RDB$CHARACTER_SET_ID = cset.RDB$CHARACTER_SET_ID "
 352                                 + "WHERE r.RDB$RELATION_NAME='" + name + "' ORDER BY r.RDB$FIELD_POSITION;"
 353                         );
 354                         using (command) {
 355                                 using (IDataReader r = command.ExecuteReader()) {
 356                                         while (r.Read ()) {
 357                                                 ColumnSchema column = new ColumnSchema (this);
 358
 359                                                 column.Name = r.GetString (0);
 360                                                 column.DataTypeName = r.GetString (8);
 361                                                 column.NotNull = (!r.IsDBNull (3) && r.GetInt32 (3) == 1);
 362                                                 column.Default = r.GetString (2);
 363                                                 column.Comment = r.GetString (1);
 364                                                 column.OwnerName = name;
 365                                                 column.Length = r.GetInt32 (4);
 366                                                 column.Precision = r.GetInt32 (5);
 367                                                 column.Scale = r.GetInt32 (6);
 368
 369                                                 columns.Add (column);
 370                                         }
 371                                         r.Close ();
 372                                 };
 373                                 connectionProvider.Close (command.Connection);
 374                         }
 375
 376                         return columns;
 377                 }
 378         }
 379 }