Pure.RelationalSchema.Storage.PostgreSQL.Querying 0.1.0-preview.0.1.0

Pure.RelationalSchema.Storage.PostgreSQL.Querying

PostgreSQL row querying for the Pure relational schema ecosystem — IQueryable / IAsyncEnumerable stored datasets over hash-named tables, read through IDbConnection.

Overview

Pure.RelationalSchema.Storage.PostgreSQL.Querying reads rows from a live PostgreSQL database through the storage abstractions defined in Pure.RelationalSchema.Storage:

• Row access — PostgreSqlStoredTableDataSet exposes the rows of a table as both IQueryable<IRow> and IAsyncEnumerable<IRow>, backed by a SELECT of all columns.
• Schema-wide access — PostgreSqlStoredSchemaDataSet maps every ITable of an ISchema to its PostgreSqlStoredTableDataSet. It implements IPostgreSqlStoredSchemaDataSet, which is defined in the separate Pure.RelationalSchema.Storage.PostgreSQL.Abstractions package.

This package covers reads only. Creating the tables and inserting rows is handled by the related packages listed below.

The tables must already exist with the hash-based names produced by Pure.RelationalSchema.Storage.PostgreSQL.Creation (any other creator that produces the same names works too). Schema, table and column identifiers are the first 63 hexadecimal characters of the hashes from Pure.RelationalSchema.HashCodes, not the model names. The caller owns the IDbConnection: it must be opened before use, and this package never disposes it.

Public API

IPostgreSqlStoredSchemaDataSet (an IStoredSchemaDataSet with an IDbConnection Connection property) lives in Pure.RelationalSchema.Storage.PostgreSQL.Abstractions, so other packages can depend on it without depending on this one. All SQL statement builders and the row enumerables are internal.

Dependencies

• Pure.RelationalSchema.Storage — storage abstractions (IStoredSchemaDataSet, IStoredTableDataSet, IRow) that this package implements
• Pure.RelationalSchema.Storage.PostgreSQL.Abstractions — IPostgreSqlStoredSchemaDataSet, the interface implemented by PostgreSqlStoredSchemaDataSet
• Pure.RelationalSchema.HashCodes — hash codes used to derive PostgreSQL object names
• Pure.Primitives.Abstractions — base interfaces for primitive types (IString, etc.)
• Pure.Primitives — concrete primitive type implementations
• Pure.Primitives.String.Operations — string composition utilities used to build SQL statements
• Pure.Primitives.Choices — choice types
• Pure.Collections.Generic — generic collection implementations (e.g. Dictionary<TKey1, TKey2, TValue> with dual-key lookup)

Target Frameworks

• .NET 8
• .NET 9
• .NET 10

Installation

dotnet add package Pure.RelationalSchema.Storage.PostgreSQL.Querying

The example below also uses Pure.RelationalSchema.Storage.PostgreSQL.Creation to create the tables. Any creator that produces the same hash-based names works; Creation is not a dependency of this package.

Usage

using System.Data;
using Npgsql;
using Pure.RelationalSchema.Abstractions.Column;
using Pure.RelationalSchema.Abstractions.Schema;
using Pure.RelationalSchema.Abstractions.Table;
using Pure.RelationalSchema.ColumnType;
using Pure.RelationalSchema.Storage.Abstractions;
using Pure.RelationalSchema.Storage.PostgreSQL.Abstractions;
using Pure.RelationalSchema.Storage.PostgreSQL.Creation;
using Pure.RelationalSchema.Storage.PostgreSQL.Querying;
using Column = Pure.RelationalSchema.Column.Column;
using Schema = Pure.RelationalSchema.Schema.Schema;
using String = Pure.Primitives.String.String;
using Table = Pure.RelationalSchema.Table.Table;

// The caller owns the connection and opens it.
IDbConnection connection = new NpgsqlConnection(connectionString);
connection.Open();

// Describe a schema with one table.
IColumn idColumn = new Column(new String("Id"), new IntColumnType());
IColumn nameColumn = new Column(new String("Name"), new StringColumnType());
ITable usersTable = new Table(new String("Users"), [idColumn, nameColumn], []);
ISchema schema = new Schema(new String("Shop"), [usersTable], []);

// Create the tables with hash-based names (DDL runs lazily, on first access).
ISchema createdSchema = new PostgreSqlCreatedSchema(schema, connection);
_ = createdSchema.Tables.ToArray();

// Query them.
IPostgreSqlStoredSchemaDataSet dataSet =
    new PostgreSqlStoredSchemaDataSet(createdSchema, connection);

IStoredTableDataSet users = dataSet[usersTable];

// Synchronous: IQueryable<IRow>
foreach (IRow row in users)
{
    string name = row.Cells[nameColumn].Value.TextValue;
    Console.WriteLine(name);
}

// Asynchronous: IAsyncEnumerable<IRow>
await foreach (IRow row in users)
{
    string id = row.Cells[idColumn].Value.TextValue;
    Console.WriteLine(id);
}

Every cell value is an IString; read its text through TextValue.

Related packages

• Pure.RelationalSchema.Storage.PostgreSQL.Abstractions — the shared IPostgreSqlStoredSchemaDataSet interface
• Pure.RelationalSchema.Storage.PostgreSQL.Creation — creates the schema and tables this package reads from
• Pure.RelationalSchema.Storage.PostgreSQL.Insertion — inserts rows into those tables