class/Npgsql/Npgsql/NpgsqlSchema.cs

   1 // Npgsql.NpgsqlCommand.cs
   2 //
   3 // Author:
   4 //  Josh Cooley <jbnpgsql@tuxinthebox.net>
   5 //
   6 //  Copyright (C) 2002-2005 The Npgsql Development Team
   7 //  npgsql-general@gborg.postgresql.org
   8 //  http://gborg.postgresql.org/project/npgsql/projdisplay.php
   9 //
  10 // This library is free software; you can redistribute it and/or
  11 // modify it under the terms of the GNU Lesser General Public
  12 // License as published by the Free Software Foundation; either
  13 // version 2.1 of the License, or (at your option) any later version.
  14 //
  15 // This library is distributed in the hope that it will be useful,
  16 // but WITHOUT ANY WARRANTY; without even the implied warranty of
  17 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  18 // Lesser General Public License for more details.
  19 //
  20 // You should have received a copy of the GNU Lesser General Public
  21 // License along with this library; if not, write to the Free Software
  22 // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
  23
  24
  25 using System;
  26 using System.Data;
  27 using System.Text;
  28
  29 namespace Npgsql
  30 {
  31         /// <summary>
  32         /// Provides the underlying mechanism for reading schema information.
  33         /// </summary>
  34         internal sealed class NpgsqlSchema
  35         {
  36         private NpgsqlConnection _connection;
  37
  38         /// <summary>
  39         /// Creates an NpgsqlSchema that can read schema information from the database.
  40         /// </summary>
  41         /// <param name="connection">An open database connection for reading metadata.</param>
  42         internal NpgsqlSchema(NpgsqlConnection connection)
  43         {
  44             _connection = connection;
  45         }
  46
  47         /// <summary>
  48         /// Returns the MetaDataCollections that lists all possible collections.
  49         /// </summary>
  50         /// <returns>The MetaDataCollections</returns>
  51         internal static DataTable GetMetaDataCollections()
  52         {
  53             DataTable metaDataCollections = new DataTable("MetaDataCollections");
  54
  55             metaDataCollections.Columns.AddRange(new DataColumn[] {
  56                                                                       new DataColumn("CollectionName"),
  57                                                                       new DataColumn("NumberOfRestrictions", typeof(int)),
  58                                                                       new DataColumn("NumberOfIdentifierParts", typeof(int)) });
  59
  60             // Add(object[] { CollectionName, NumberOfRestrictions, NumberOfIdentifierParts })
  61             metaDataCollections.Rows.Add(new object[]{"MetaDataCollections",0,0});
  62             metaDataCollections.Rows.Add(new object[]{"Restrictions",0,0});
  63             metaDataCollections.Rows.Add(new object[]{"Databases",1,1});
  64             metaDataCollections.Rows.Add(new object[]{"Tables",4,3});
  65             metaDataCollections.Rows.Add(new object[]{"Columns",4,4});
  66             metaDataCollections.Rows.Add(new object[]{"Views",3,3});
  67             metaDataCollections.Rows.Add(new object[]{"Users",1,1});
  68
  69             return metaDataCollections;
  70         }
  71
  72         /// <summary>
  73         /// Returns the Restrictions that contains the meaning and position of the values in the restrictions array.
  74         /// </summary>
  75         /// <returns>The Restrictions</returns>
  76         internal static DataTable GetRestrictions()
  77         {
  78             DataTable restrictions = new DataTable("Restrictions");
  79
  80             restrictions.Columns.AddRange(new DataColumn[] {
  81                                                                new DataColumn("CollectionName"),
  82                                                                new DataColumn("RestrictionName"),
  83                                                                new DataColumn("RestrictionDefault"),
  84                                                                new DataColumn("RestrictionNumber", typeof(int)) });
  85
  86             restrictions.Rows.Add(new object[]{"Databases","Name","Name",1});
  87             restrictions.Rows.Add(new object[]{"Tables","Catalog","table_catalog",1});
  88             restrictions.Rows.Add(new object[]{"Tables","Schema","table_schema",2});
  89             restrictions.Rows.Add(new object[]{"Tables","Table","table_name",3});
  90             restrictions.Rows.Add(new object[]{"Tables","TableType","table_type",4});
  91             restrictions.Rows.Add(new object[]{"Columns","Catalog","table_catalog",1});
  92             restrictions.Rows.Add(new object[]{"Columns","Schema","table_schema",2});
  93             restrictions.Rows.Add(new object[]{"Columns","Table","table_name",3});
  94             restrictions.Rows.Add(new object[]{"Columns","Column","column_name",4});
  95             restrictions.Rows.Add(new object[]{"Views","Catalog","table_catalog",1});
  96             restrictions.Rows.Add(new object[]{"Views","Schema","table_schema",2});
  97             restrictions.Rows.Add(new object[]{"Views","Table","table_name",3});
  98
  99             return restrictions;
 100         }
 101
 102         private NpgsqlCommand BuildCommand(StringBuilder query, string[] restrictions, params string[] names)
 103         {
 104             NpgsqlCommand command = new NpgsqlCommand();
 105
 106             if (restrictions != null && names != null)
 107             {
 108                 bool addWhere = true;
 109                 for(int i=0; i<restrictions.Length && i<names.Length; ++i)
 110                 {
 111                     if (restrictions[i] != null && restrictions[i].Length != 0)
 112                     {
 113                         if (addWhere)
 114                         {
 115                             query.Append(" WHERE ");
 116                             addWhere = false;
 117                         }
 118                         else
 119                         {
 120                             query.Append(" AND ");
 121                         }
 122                         query.AppendFormat("{0} = :{0}", names[i]);
 123
 124                         command.Parameters.Add(new NpgsqlParameter(names[i], restrictions[i]));
 125                     }
 126                 }
 127             }
 128             command.CommandText = query.ToString();
 129             command.Connection = _connection;
 130
 131             return command;
 132         }
 133
 134         /// <summary>
 135         /// Returns the Databases that contains a list of all accessable databases.
 136         /// </summary>
 137         /// <param name="restrictions">The restrictions to filter the collection.</param>
 138         /// <returns>The Databases</returns>
 139         internal DataTable GetDatabases(string[] restrictions)
 140         {
 141             DataTable databases = new DataTable("Databases");
 142
 143             databases.Columns.AddRange(new DataColumn[] {
 144                                                             new DataColumn("database_name"),
 145                                                             new DataColumn("owner"),
 146                                                             new DataColumn("encoding") });
 147
 148             StringBuilder getDatabases = new StringBuilder();
 149
 150             getDatabases.Append("SELECT d.datname AS database_name, u.usename AS owner, pg_catalog.pg_encoding_to_char(d.encoding) AS encoding FROM pg_catalog.pg_database d LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid");
 151
 152             using (NpgsqlCommand command = BuildCommand(getDatabases, restrictions, "datname"))
 153             using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
 154             {
 155                 adapter.Fill(databases);
 156             }
 157
 158             return databases;
 159         }
 160
 161         /// <summary>
 162         /// Returns the Tables that contains table and view names and the database and schema they come from.
 163         /// </summary>
 164         /// <param name="restrictions">The restrictions to filter the collection.</param>
 165         /// <returns>The Tables</returns>
 166         internal DataTable GetTables(string[] restrictions)
 167         {
 168             DataTable tables = new DataTable("Tables");
 169
 170             tables.Columns.AddRange(new DataColumn[] {
 171                                                          new DataColumn("table_catalog"),
 172                                                          new DataColumn("table_schema"),
 173                                                          new DataColumn("table_name"),
 174                                                          new DataColumn("table_type") });
 175
 176             StringBuilder getTables = new StringBuilder();
 177
 178             getTables.Append("SELECT table_catalog, table_schema, table_name, table_type FROM information_schema.tables");
 179
 180             using (NpgsqlCommand command = BuildCommand(getTables, restrictions, "table_catalog", "table_schema", "table_name", "table_type"))
 181             using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
 182             {
 183                 adapter.Fill(tables);
 184             }
 185
 186             return tables;
 187         }
 188
 189         /// <summary>
 190         /// Returns the Columns that contains information about columns in tables.
 191         /// </summary>
 192         /// <param name="restrictions">The restrictions to filter the collection.</param>
 193         /// <returns>The Columns.</returns>
 194         internal DataTable GetColumns(string[] restrictions)
 195         {
 196             DataTable columns = new DataTable("Columns");
 197
 198             columns.Columns.AddRange(new DataColumn[] {
 199                                                           new DataColumn("table_catalog"),
 200                                                           new DataColumn("table_schema"),
 201                                                           new DataColumn("table_name"),
 202                                                           new DataColumn("column_name"),
 203                                                           new DataColumn("ordinal_position", typeof(int)),
 204                                                           new DataColumn("column_default"),
 205                                                           new DataColumn("is_nullable"),
 206                                                           new DataColumn("data_type"),
 207                                                           new DataColumn("character_maximum_length", typeof(int)),
 208                                                           new DataColumn("character_octet_length", typeof(int)),
 209                                                           new DataColumn("numeric_precision", typeof(int)),
 210                                                           new DataColumn("numeric_precision_radix", typeof(int)),
 211                                                           new DataColumn("numeric_scale", typeof(int)),
 212                                                           new DataColumn("datetime_precision", typeof(int)),
 213                                                           new DataColumn("character_set_catalog"),
 214                                                           new DataColumn("character_set_schema"),
 215                                                           new DataColumn("character_set_name"),
 216                                                           new DataColumn("collation_catalog") });
 217
 218             StringBuilder getColumns = new StringBuilder();
 219
 220             getColumns.Append("SELECT table_catalog, table_schema, table_name, column_name, ordinal_position, column_default, is_nullable, udt_name AS data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_precision_radix, numeric_scale, datetime_precision, character_set_catalog, character_set_schema, character_set_name, collation_catalog FROM information_schema.columns");
 221
 222             using (NpgsqlCommand command = BuildCommand(getColumns, restrictions, "table_catalog", "table_schema", "table_name", "column_name"))
 223             using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
 224             {
 225                 adapter.Fill(columns);
 226             }
 227
 228             return columns;
 229         }
 230
 231         /// <summary>
 232         /// Returns the Views that contains view names and the database and schema they come from.
 233         /// </summary>
 234         /// <param name="restrictions">The restrictions to filter the collection.</param>
 235         /// <returns>The Views</returns>
 236         internal DataTable GetViews(string[] restrictions)
 237         {
 238             DataTable views = new DataTable("Views");
 239
 240             views.Columns.AddRange(new DataColumn[] {
 241                                                         new DataColumn("table_catalog"),
 242                                                         new DataColumn("table_schema"),
 243                                                         new DataColumn("table_name"),
 244                                                         new DataColumn("check_option"),
 245                                                         new DataColumn("is_updatable") });
 246
 247             StringBuilder getViews = new StringBuilder();
 248
 249             getViews.Append("SELECT table_catalog, table_schema, table_name, check_option, is_updatable FROM information_schema.views");
 250
 251             using (NpgsqlCommand command = BuildCommand(getViews, restrictions, "table_catalog", "table_schema", "table_name"))
 252             using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
 253             {
 254                 adapter.Fill(views);
 255             }
 256
 257             return views;
 258         }
 259
 260         /// <summary>
 261         /// Returns the Users containing user names and the sysid of those users.
 262         /// </summary>
 263         /// <param name="restrictions">The restrictions to filter the collection.</param>
 264         /// <returns>The Users.</returns>
 265         internal DataTable GetUsers(string[] restrictions)
 266         {
 267             DataTable users = new DataTable("Users");
 268
 269             users.Columns.AddRange(new DataColumn[] {
 270                                                         new DataColumn("user_name"),
 271                                                         new DataColumn("user_sysid", typeof(int)) });
 272
 273             StringBuilder getUsers = new StringBuilder();
 274
 275             getUsers.Append("SELECT usename as user_name, usesysid as user_sysid FROM pg_catalog.pg_user");
 276
 277             using (NpgsqlCommand command = BuildCommand(getUsers, restrictions, "usename"))
 278             using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
 279             {
 280                 adapter.Fill(users);
 281             }
 282
 283             return users;
 284         }
 285         }
 286 }