public NpgsqlCommandBuilder(NpgsqlDataAdapter adapter) : base() { DataAdapter = adapter; this.QuotePrefix = "\""; this.QuoteSuffix = "\""; }
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; } } }
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; }
/// <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; }