pengdows.sql 0.2.1-preview.22

pengdows.flatfile

pengdows.flatfile is a strict flat-file SQL engine packaged as a real ADO.NET provider — DbConnection/DbCommand/DbDataReader/DbTransaction, not a read-only shim. It is built for import/export systems, ETL, deterministic test fixtures, and teaching actual standards-oriented SQL over CSV, TSV, pipe-delimited, fixed-width, and NDJSON data.

It is not trying to compete with SQLite, PostgreSQL, SQL Server, or any other database engine on query speed, concurrency, recovery, or server-grade durability. The point is to make flat files correct, typed, queryable, mutable, inspectable, and easy to use from ordinary .NET applications.

Pure BCL, zero external NuGet dependencies. Multi-targets net8.0 and net10.0.

Origin & philosophy

This started narrower than it is today: strict RFC 4180 CSV support, implemented as a real ADO.NET provider, built specifically so pengdows.etl would have something reliable to read and write source files through. CSV is deceptively hard, and most libraries handle the easy cases while drifting on line endings, quoting, embedded records, malformed input, or edge-case interoperability. Correct CSV behavior is therefore a central feature, not an implementation detail.

Once flat files can be described as typed tables, SQL over the top makes sense: filtering, projection, joins, grouping, validation, and mutation are exactly the operations people already need around import/export and local data workflows. And if the project is going to expose SQL, it should expose real SQL semantics rather than a casual vendor-flavored approximation. The project has two separate standards contracts: pengdows.sql targets complete ISO SQL grammar/AST coverage, while pengdows.flatfile implements and tests a classified execution subset. See SQL_CONFORMANCE_PLAN.md. The flat-file manifest/publication model is documented in MANIFEST_AND_PUBLICATION.md.

The breadth of the project is a natural extension of those uses, not a runaway wishlist. Strict CSV led to a parser abstraction; typed files led to tables; multiple typed tables led to catalogs and schemas; SQL access led to binding, execution, mutation, and provider behavior. Each layer exists because it makes flat-file ETL, reproducible fixtures, or SQL teaching more useful.

TSV, pipe, custom-delimited, fixed-width, and NDJSON support beyond strict CSV weren't a deliberate multi-format push — they were added because they were easy. Once a record-parsing abstraction (IRecordParser) existed for strict CSV, the other line-oriented formats were cheap, low-marginal-cost extensions of the same shape, not separate design efforts.

The rest of the shape followed from one decision: wanting file descriptions (TableDefinition/ColumnDefinition) to carry real types instead of just strings, numbers, and dates. Typed column descriptions are naturally per-table, which means naturally supporting more than one table — and once you have multiple typed tables, a directory of them is a schema, and the directory a connection points at (with catalog.json declaring its schema subdirectories) is a database. File → table, schema-subdirectory → schema, connection root → database — that mapping is real in TableCatalog, not just a metaphor.

That, in turn, makes the same model valuable as a portable, deterministic test fixture format. A directory of typed tables is a self-contained known state — no server, no container, nothing to provision. Zip the directory, check it into source control, unzip it in a test run, and you have an exact, repeatable dataset for integration tests, reproduction cases, and business-logic validation.

The third intended use case is teaching. Because this engine rejects unsupported vendor behavior instead of quietly accepting dialect habits, it can be used to teach actual conformant SQL concepts: null semantics and three-valued logic, joins, grouping, set operations, views, parameters, constraints, and the difference between "works in one product" and "is standard SQL."

Why flat-file fixtures?

Complex business systems often need very specific database states to reproduce or validate behavior. In a live or shared database, that can mean careful hand-editing of records before every run, only to have the test consume, mutate, or delete the setup data. The feedback loop becomes slow, fragile, and person-dependent.

With pengdows.flatfile, the database state is just files. A test can start by unzipping a known-good fixture directory, run the application against it through ordinary ADO.NET APIs, then validate the output by query, file diff, or direct human inspection. Tools like Beyond Compare, Git diff, WinMerge, Meld, Rider, or VS Code can show every changed table, sidecar, view, and generated file across the whole run. When the run is over, reset is just deleting the working directory and unzipping the fixture again. The same artifact can support local developer repros, QA scenario packs, regression tests, and business-rule review.

Projects

• pengdows.flatfile/: flat-file catalog, row readers/writers, binding, execution, transactions, and the ADO.NET provider surface
• pengdows.sql/: reusable, dependency-free SQL lexer, parser, tokens, and AST — no dependency on pengdows.flatfile, designed to be reusable by other SQL-consuming tools
• pengdows.sql.Tests/: parser/AST conformance tests, independent of catalogs, files, and provider execution
• pengdows.flatfile.Tests/: xUnit suites for SQL and file-format conformance, provider and functional requirements, regressions, and unit/code-coverage assurance. See TESTING.md.

NuGet package

The prerelease package targets net8.0 and net10.0. Build both packages from the repository root with:

dotnet pack pengdows.flatfile.slnx -c Release -o ./artifacts/packages

Install pengdows.flatfile in an application; NuGet will bring in the matching pengdows.sql dependency automatically. The current public prerelease is 0.2.1-preview.3.

Supported file formats

Not supported: Parquet, or any binary/columnar format. This is a text/line-oriented engine (plus fixed-width).

Encoding is configurable per table (Encoding.GetEncoding(name) — any name .NET recognizes, defaults to UTF-8). Note: sequential reads auto-detect a BOM (which can silently override the declared encoding); offset/seeked reads used by the indexing subsystem do not.

SQL support (SQL:2023 subset)

Standards posture, from SQL_STANDARDS_STATUS.md: complete ISO grammar/AST coverage is the pengdows.sql design target; provider execution is a documented SQL:2023 subset. Vendor-only quirks are deliberately not emulated, and a query relying on nonstandard behavior should be rejected rather than silently accepted. SQL-92 is the execution floor.

• SELECT (genuinely streams, does not materialize the whole table), INSERT, UPDATE, DELETE, MERGE, TRUNCATE
• CREATE/DROP/ALTER TABLE, CREATE/DROP VIEW, CREATE/DROP INDEX, EXPLAIN
• GRANT/REVOKE and role statements are parsed and accepted as provider no-ops; the flat-file provider does not currently implement authentication or authorization.
• JOIN (Inner, Left, Right, Full Outer, Cross, Natural), derived tables, LATERAL, JSON_TABLE
• Common Table Expressions (WITH; WITH RECURSIVE supports multiple CTEs and N-branch UNION ALL anchor/recursive chains, capped at 1000 iterations; mutual recursion and non-UNION ALL recursive combinators are rejected)
• Window functions (ROW_NUMBER, RANK, LAG, LEAD, NTILE, ...) with full OVER/framing support
• Set operations (UNION, INTERSECT, EXCEPT [ALL], with correct precedence)
• GROUP BY/GROUPING SETS/ROLLUP/CUBE, HAVING, aggregates with FILTER/DISTINCT
• Subqueries (scalar, ANY/ALL, EXISTS)
• Genuine three-valued (TRUE/FALSE/UNKNOWN) predicate logic — one shared evaluator used identically by WHERE/JOIN...ON/HAVING
• JSON functions: JSON_VALUE, JSON_QUERY, JSON_EXISTS, JSON_OBJECT, JSON_ARRAY, JSON_TABLE
• Parameter markers: positional ? (auto-numbered) and named :name (resolved against DbParameter.ParameterName, case-insensitively). A statement cannot mix the two families. Vendor-specific @name and $1 forms are rejected.
• Supported subset: ISO SQL-invoked procedures with LANGUAGE SQL, compound BEGIN/END bodies, IN parameter declarations, CALL, and ADO.NET CommandType.StoredProcedure. Full SQL/PSM control flow, OUT/INOUT, functions, and triggers remain unsupported.

See SQL_STANDARDS_STATUS.md for the full, current subset and explicit gaps, and SQL_SPEC_CHECKLIST.md for the spec-by-spec implementation checklist.

Reading and writing — this is not read-only

INSERT/UPDATE/DELETE/MERGE/TRUNCATE and the full DDL set genuinely mutate the underlying files. CREATE VIEW writes a {name}.view.json sidecar (query text + column names) — nothing is materialized; every reference to the view re-parses and re-executes the stored query at bind time.

FlatFileTransaction provides file-level transactional mutation with snapshot/undo-journal rollback, not full ACID: the first modification to a file within a transaction copies it to a per-transaction temp journal; Rollback() restores the backups and deletes newly-created files; disposing an uncommitted transaction implicitly rolls back. One transaction per connection. Isolation: all four ISO levels are accepted (ReadUncommitted, ReadCommitted (the default), RepeatableRead, Serializable); Snapshot (not an ISO level) throws NotSupportedException. Under RepeatableRead or Serializable, a readonly connection's transaction takes one consistent snapshot of the whole database at its first statement, so another connection's later commits, including DDL, don't change what it sees. Writer transactions read live files: only one writer can be open, so nothing else can change them. Writes need no isolation between connections because only one writer can be open (see below). What it does not provide — MVCC, database-style row/page locking, or WAL-based durability — is exactly what's needed before "ACID" would be an accurate label.

Schema and multi-table support

Tables are described by *.table.json files (TableDefinitionLoader), or built in-memory via TableDefinitionSniffer.Sniff(...) (infers format from file extension, samples up to sampleSize rows — default 1000 — to infer each column's CLR type: bool → long → decimal → DateTime → string fallback, with a leading-zero heuristic that keeps values like "007" as strings).

TableCatalog auto-discovers every *.table.json/*.view.json in a directory for multi-table queries; an optional catalog.json declares per-schema subdirectories for schema.table-qualified names.

Use path=/data/database to open that directory as a multi-table database. Use file=/data/orders.csv to open one file as one table; the provider uses an adjacent orders.table.json when present and otherwise sniffs the schema. definition=/data/custom.table.json can be supplied with file= to select an explicit sidecar, while file= remains authoritative for the data path.

Connection string keys

One writer, unlimited readers. Any number of readonly connections may be open at once. At most one non-readonly connection may write a given piece of data: a directory-mode writer owns its whole catalog (including catalog.json schema directories), a single-file writer owns one file, and single-file writers on different files in the same folder do not contend. Within a process, a second overlapping writer waits up to connectionTimeout for the first to close, then throws FlatFileException with SQLSTATE HYT00 (timeout expired); this does not depend on OS file locking, so it holds even with DOTNET_SYSTEM_IO_DISABLEFILELOCKING set. One process per database is the supported model: a writer in another process is refused immediately with a FlatFileException (SQLSTATE 08004, connection rejected) naming that process (application name, process id, host). Detecting another process relies on the OS file lock, so it is off when .NET file locking is disabled and may not work on network filesystems.

Keys are case-insensitive. Boolean values accept true/false, yes/no, or 1/0; any other value, or a timeout that isn't a non-negative whole number, throws ArgumentException rather than silently falling back to a default.

ClrTypeMap supports exactly 19 CLR types: bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double, decimal, string, DateTime, DateOnly, TimeOnly, DateTimeOffset, Guid, byte[].

information_schema.tables and information_schema.columns are always queryable via standard SQL (SELECT * FROM information_schema.columns WHERE table_name = '...'), even on a flat directory with no catalog.json — it's the SQL-standard metadata interface, not a user-defined schema. This is what lets a generic, capability-probing schema inspector (e.g. pengdows.poco.mint) generate typed entities from a flat-file "database" the same way it would against any other ADO.NET provider, without flatfile-specific code on the consuming side.

Build and test

dotnet build pengdows.flatfile.slnx
dotnet test pengdows.flatfile.slnx

To run a targeted test file or class during development:

dotnet test pengdows.flatfile.Tests/pengdows.flatfile.Tests.csproj --filter "FullyQualifiedName~SqlParserTests"

Repository notes

• The solution file is pengdows.flatfile.slnx
• Standards/compliance notes live in SQL_STANDARDS_STATUS.md and STRICT_CSV_COMPLIANCE.md
• The purpose and classification rules for each test suite live in TESTING.md
• Contributor guidance lives in AGENTS.md