static List <BackendType> LoadBackendTypes(NpgsqlConnector connector) { // Select all basic, range and enum types along with their array type's OID and text delimiter // For range types, select also the subtype they contain const string query = @"SELECT typname, pg_type.oid, typtype, typarray, typdelim, rngsubtype " + @"FROM pg_type " + @"LEFT OUTER JOIN pg_range ON (pg_type.oid = pg_range.rngtypid) " + @"WHERE typtype IN ('b', 'r', 'e') " + @"ORDER BY typtype"; var types = new List <BackendType>(); using (var command = new NpgsqlCommand(query, connector)) { command.AllResultTypesAreUnknown = true; using (var dr = command.GetReader(CommandBehavior.SequentialAccess)) { while (dr.Read()) { var name = dr.GetString(0); Contract.Assume(name != null); var oid = Convert.ToUInt32(dr[1]); Contract.Assume(oid != 0); var type = (BackendTypeType)dr.GetString(2)[0]; var arrayOID = Convert.ToUInt32(dr[3]); var textDelimiter = dr.GetString(4)[0]; var rangeSubtypeOID = type == BackendTypeType.Range ? UInt32.Parse(dr.GetString(5)) : 0; types.Add(new BackendType { Name = name, OID = oid, Type = type, ArrayOID = arrayOID, ArrayTextDelimiter = textDelimiter, RangeSubtypeOID = rangeSubtypeOID, }); } } } /*foreach (var notFound in _typeHandlers.Where(t => t.Oid == -1)) { * _log.WarnFormat("Could not find type {0} in pg_type", notFound.PgNames[0]); * }*/ return(types); }
private IEnumerable<string> GetPrimaryKeys(String tablename) { if (string.IsNullOrEmpty(tablename)) { yield break; } String getPKColumns = "select a.attname from pg_catalog.pg_class ct, pg_catalog.pg_class ci, pg_catalog.pg_attribute a, pg_catalog.pg_index i WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid AND i.indisprimary AND ct.relname = :tablename"; using (NpgsqlConnection metadataConn = _connection.Clone()) { using (NpgsqlCommand c = new NpgsqlCommand(getPKColumns, metadataConn)) { c.Parameters.Add(new NpgsqlParameter("tablename", NpgsqlDbType.Text)); c.Parameters["tablename"].Value = tablename; using (NpgsqlDataReader dr = c.GetReader(CommandBehavior.SingleResult | CommandBehavior.SequentialAccess)) { while (dr.Read()) { yield return dr.GetString(0); } } } } }
private Dictionary<string, Column> GetColumns() { StringBuilder sb = new StringBuilder(); // the column index is used to find data. // any changes to the order of the columns needs to be reflected in struct Columns sb.Append( "SELECT a.attname AS column_name, a.attnotnull AS column_notnull, a.attrelid AS table_id, a.attnum AS column_num, d.adsrc as column_default"); sb.Append( " FROM pg_attribute a LEFT OUTER JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum WHERE a.attnum > 0 AND ("); bool first = true; for (int i = 0; i < CurrentDescription.NumFields; ++i) { if (CurrentDescription[i].TableOID != 0) { if (!first) { sb.Append(" OR "); } sb.AppendFormat("(a.attrelid={0} AND a.attnum={1})", CurrentDescription[i].TableOID, CurrentDescription[i].ColumnAttributeNumber); first = false; } } sb.Append(')'); // if the loop ended without setting first to false, then there will be no results from the query if (first) { return null; } using (NpgsqlConnection connection = _connection.Clone()) { using (NpgsqlCommand command = new NpgsqlCommand(sb.ToString(), connection)) { using (NpgsqlDataReader reader = command.GetReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult) ) { Dictionary<string, Column> columnLookup = new Dictionary<string, Column>(); while (reader.Read()) { Column column = new Column(reader); columnLookup.Add(column.Key, column); } return columnLookup; } } } }
private Dictionary<long, Table> GetTablesFromOids(List<int> oids) { if (oids.Count == 0) { return new Dictionary<long, Table>(); //Empty collection is simpler than requiring tests for null; } // the column index is used to find data. // any changes to the order of the columns needs to be reflected in struct Tables StringBuilder sb = new StringBuilder( "SELECT current_database(), nc.nspname, c.relname, c.oid FROM pg_namespace nc, pg_class c WHERE c.relnamespace = nc.oid AND (c.relkind = 'r' OR c.relkind = 'v') AND c.oid IN ("); bool first = true; foreach (int oid in oids) { if (!first) { sb.Append(','); } sb.Append(oid); first = false; } sb.Append(')'); using (NpgsqlConnection connection = _connection.Clone()) { using (NpgsqlCommand command = new NpgsqlCommand(sb.ToString(), connection)) { using (NpgsqlDataReader reader = command.GetReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult) ) { Dictionary<long, Table> oidLookup = new Dictionary<long, Table>(oids.Count); int columnCount = reader.FieldCount; while (reader.Read()) { Table t = new Table(reader); oidLookup.Add(t.Id, t); } return oidLookup; } } } }
private KeyLookup GetKeys(Int32 tableOid) { string getKeys = "select a.attname, ci.relname, i.indisprimary from pg_catalog.pg_class ct, pg_catalog.pg_class ci, pg_catalog.pg_attribute a, pg_catalog.pg_index i WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid AND i.indisunique AND ct.oid = :tableOid order by ci.relname"; KeyLookup lookup = new KeyLookup(); using (NpgsqlConnection metadataConn = _connection.Clone()) { NpgsqlCommand c = new NpgsqlCommand(getKeys, metadataConn); c.Parameters.Add(new NpgsqlParameter("tableOid", NpgsqlDbType.Integer)).Value = tableOid; using (NpgsqlDataReader dr = c.GetReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) { string previousKeyName = null; string possiblyUniqueColumn = null; string columnName; string currentKeyName; // loop through adding any column that is primary to the primary key list // add any column that is the only column for that key to the unique list // unique here doesn't mean general unique constraint (with possibly multiple columns) // it means all values in this single column must be unique while (dr.Read()) { columnName = dr.GetString(0); currentKeyName = dr.GetString(1); // if i.indisprimary if (dr.GetBoolean(2)) { // add column name as part of the primary key lookup.primaryKey.Add(columnName); } if (currentKeyName != previousKeyName) { if (possiblyUniqueColumn != null) { lookup.uniqueColumns.Add(possiblyUniqueColumn); } possiblyUniqueColumn = columnName; } else { possiblyUniqueColumn = null; } previousKeyName = currentKeyName; } // if finished reading and have a possiblyUniqueColumn name that is // not null, then it is the name of a unique column if (possiblyUniqueColumn != null) { lookup.uniqueColumns.Add(possiblyUniqueColumn); } return lookup; } } }
internal ForwardsOnlyDataReader GetReader(CommandBehavior cb) { try { CheckConnectionState(); // reset any responses just before getting new ones Connector.Mediator.ResetResponses(); // Set command timeout. m_Connector.Mediator.CommandTimeout = CommandTimeout; using (m_Connector.BlockNotificationThread()) { ForwardsOnlyDataReader reader; if (parse == null) { reader = new ForwardsOnlyDataReader(m_Connector.QueryEnum(this), cb, this, m_Connector.BlockNotificationThread(), false); if (type == CommandType.StoredProcedure && reader.FieldCount == 1 && reader.GetDataTypeName(0) == "refcursor") { // When a function returns a sole column of refcursor, transparently // FETCH ALL from every such cursor and return those results. StringBuilder sb = new StringBuilder(); while (reader.Read()) { sb.Append("fetch all from \"").Append(reader.GetString(0)).Append("\";"); } sb.Append(";"); // Just in case the list of cursors is empty. //reader = new NpgsqlCommand(sb.ToString(), Connection).GetReader(reader._behavior); // Passthrough the commandtimeout to the inner command, so user can also control its timeout. // TODO: Check if there is a better way to handle that. NpgsqlCommand c = new NpgsqlCommand(sb.ToString(), Connection); c.CommandTimeout = this.CommandTimeout; reader = c.GetReader(reader._behavior); } } else { BindParameters(); reader = new ForwardsOnlyDataReader(m_Connector.ExecuteEnum(new NpgsqlExecute(bind.PortalName, 0)), cb, this, m_Connector.BlockNotificationThread(), true); } return reader; } } catch (IOException ex) { throw ClearPoolAndCreateException(ex); } }