internal DataTable GetIndexColumns(string[] restrictions)
{
DataTable indexColumns = new DataTable("IndexColumns");
indexColumns.Locale = CultureInfo.InvariantCulture;
indexColumns.Columns.AddRange(
new DataColumn[]
{
new DataColumn("table_catalog"), new DataColumn("table_schema"), new DataColumn("table_name"),
new DataColumn("index_name"), new DataColumn("column_name")
});
StringBuilder getIndexColumns = new StringBuilder();
getIndexColumns.Append(
@"select current_database() as table_catalog,
n.nspname as table_schema,
t.relname as table_name,
i.relname as index_name,
a.attname as column_name
from
pg_class t join
pg_index ix on t.oid = ix.indrelid join
pg_class i on ix.indexrelid = i.oid join
pg_attribute a on t.oid = a.attrelid left join
pg_namespace n on i.relnamespace = n.oid
where
i.relkind = 'i'
and n.nspname not in ('pg_catalog', 'pg_toast')
and pg_catalog.pg_table_is_visible(i.oid)
and a.attnum = ANY(ix.indkey)
and t.relkind = 'r'");
using (
NpgsqlCommand command =
BuildCommand(getIndexColumns, restrictions, false, "current_database()", "n.nspname", "t.relname", "i.relname", "a.attname"))
{
using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
{
adapter.Fill(indexColumns);
}
}
return indexColumns;
}
internal DataTable GetForeignKeys(string[] restrictions)
{
StringBuilder getForeignKeys = new StringBuilder();
getForeignKeys.Append(
@"select
current_database() as ""CONSTRAINT_CATALOG"",
pgn.nspname as ""CONSTRAINT_SCHEMA"",
pgc.conname as ""CONSTRAINT_NAME"",
current_database() as ""TABLE_CATALOG"",
pgtn.nspname as ""TABLE_SCHEMA"",
pgt.relname as ""TABLE_NAME"",
'FOREIGN KEY' as ""CONSTRAINT_TYPE"",
pgc.condeferrable as ""IS_DEFERRABLE"",
pgc.condeferred as ""INITIALLY_DEFERRED""
from pg_catalog.pg_constraint pgc
inner join pg_catalog.pg_namespace pgn on pgc.connamespace = pgn.oid
inner join pg_catalog.pg_class pgt on pgc.conrelid = pgt.oid
inner join pg_catalog.pg_namespace pgtn on pgt.relnamespace = pgtn.oid
where pgc.contype='f'
");
using (
NpgsqlCommand command =
BuildCommand(getForeignKeys, restrictions, false, "current_database()", "pgtn.nspname", "pgt.relname", "pgc.conname"))
{
using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
{
DataTable table = new DataTable("ForeignKeys");
adapter.Fill(table);
return table;
}
}
}
/// <summary>
/// Returns the Users containing user names and the sysid of those users.
/// </summary>
/// <param name="restrictions">The restrictions to filter the collection.</param>
/// <returns>The Users.</returns>
internal DataTable GetUsers(string[] restrictions)
{
DataTable users = new DataTable("Users");
users.Locale = CultureInfo.InvariantCulture;
users.Columns.AddRange(new DataColumn[] { new DataColumn("user_name"), new DataColumn("user_sysid", typeof(int)) });
StringBuilder getUsers = new StringBuilder();
getUsers.Append("SELECT usename as user_name, usesysid as user_sysid FROM pg_catalog.pg_user");
using (NpgsqlCommand command = BuildCommand(getUsers, restrictions, "usename"))
{
using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
{
adapter.Fill(users);
}
}
return users;
}
/// <summary>
/// Returns the Views that contains view names and the database and schema they come from.
/// </summary>
/// <param name="restrictions">The restrictions to filter the collection.</param>
/// <returns>The Views</returns>
internal DataTable GetViews(string[] restrictions)
{
DataTable views = new DataTable("Views");
views.Locale = CultureInfo.InvariantCulture;
views.Columns.AddRange(
new DataColumn[]
{
new DataColumn("table_catalog"), new DataColumn("table_schema"), new DataColumn("table_name"),
new DataColumn("check_option"), new DataColumn("is_updatable")
});
StringBuilder getViews = new StringBuilder();
getViews.Append(
"SELECT table_catalog, table_schema, table_name, check_option, is_updatable FROM information_schema.views");
using (NpgsqlCommand command = BuildCommand(getViews, restrictions, "table_catalog", "table_schema", "table_name"))
{
using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
{
adapter.Fill(views);
}
}
return views;
}
/// <summary>
/// Returns the Columns that contains information about columns in tables.
/// </summary>
/// <param name="restrictions">The restrictions to filter the collection.</param>
/// <returns>The Columns.</returns>
internal DataTable GetColumns(string[] restrictions)
{
DataTable columns = new DataTable("Columns");
columns.Locale = CultureInfo.InvariantCulture;
columns.Columns.AddRange(
new DataColumn[]
{
new DataColumn("table_catalog"), new DataColumn("table_schema"), new DataColumn("table_name"),
new DataColumn("column_name"), new DataColumn("ordinal_position", typeof (int)), new DataColumn("column_default"),
new DataColumn("is_nullable"), new DataColumn("data_type"),
new DataColumn("character_maximum_length", typeof (int)), new DataColumn("character_octet_length", typeof (int)),
new DataColumn("numeric_precision", typeof (int)), new DataColumn("numeric_precision_radix", typeof (int)),
new DataColumn("numeric_scale", typeof (int)), new DataColumn("datetime_precision", typeof (int)),
new DataColumn("character_set_catalog"), new DataColumn("character_set_schema"),
new DataColumn("character_set_name"), new DataColumn("collation_catalog")
});
StringBuilder getColumns = new StringBuilder();
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");
using (
NpgsqlCommand command =
BuildCommand(getColumns, restrictions, "table_catalog", "table_schema", "table_name", "column_name"))
{
using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
{
adapter.Fill(columns);
}
}
return columns;
}
/// <summary>
/// Returns the Tables that contains table and view names and the database and schema they come from.
/// </summary>
/// <param name="restrictions">The restrictions to filter the collection.</param>
/// <returns>The Tables</returns>
internal DataTable GetTables(string[] restrictions)
{
DataTable tables = new DataTable("Tables");
tables.Locale = CultureInfo.InvariantCulture;
tables.Columns.AddRange(
new DataColumn[]
{
new DataColumn("table_catalog"), new DataColumn("table_schema"), new DataColumn("table_name"),
new DataColumn("table_type")
});
StringBuilder getTables = new StringBuilder();
getTables.Append("SELECT table_catalog, table_schema, table_name, table_type FROM information_schema.tables");
using (
NpgsqlCommand command =
BuildCommand(getTables, restrictions, "table_catalog", "table_schema", "table_name", "table_type"))
{
using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
{
adapter.Fill(tables);
}
}
return tables;
}
/// <summary>
/// Returns the Databases that contains a list of all accessable databases.
/// </summary>
/// <param name="restrictions">The restrictions to filter the collection.</param>
/// <returns>The Databases</returns>
internal DataTable GetDatabases(string[] restrictions)
{
DataTable databases = new DataTable("Databases");
databases.Locale = CultureInfo.InvariantCulture;
databases.Columns.AddRange(
new DataColumn[] { new DataColumn("database_name"), new DataColumn("owner"), new DataColumn("encoding") });
StringBuilder getDatabases = new StringBuilder();
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");
using (NpgsqlCommand command = BuildCommand(getDatabases, restrictions, "datname"))
{
using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
{
adapter.Fill(databases);
}
}
return databases;
}