Database runtime

Code: src/IntentSharp/Data/SemanticDatabase.cs, SqlSafetyChecker.cs, SchemaDiscovery.cs, DatabaseSchema.cs.

SemanticDatabase turns a natural-language request into one parameterized SQL statement, checks it, and runs it only if it passes. It works over any ADO.NET DbConnection.

await using var conn = new SqliteConnection("Data Source=shop.db");
var db = new SemanticDatabase(client, conn, new DatabaseOptions { DryRun = true });
var result = await db.QueryAsync("customers who ordered more than 3 times this year");
Console.WriteLine(result.Plan.Sql);           // reviewed, not executed
var executed = await db.ExecutePlanAsync(result.Plan);   // the safety check runs again
foreach (var row in executed.Rows) Console.WriteLine(string.Join(", ", row.Values));

Options and defaults

DatabaseOptions defaults to the safest value for every setting:

Option	Default	Effect
`AllowWrites`	`false`	Only `SELECT` and `WITH` are allowed.
`AllowedTables`	`null`	Null means every discovered table; an empty list means no table at all.
`DryRun`	`false`	When true, `QueryAsync` and `UpsertAsync` return the checked plan without executing.
`MaxRows`	1000	Rows read per query; `QueryResult.Truncated` reports when the cap was hit.
`CommandTimeout`	30 s	ADO.NET command timeout.
`RequireParameters`	`true`	Reject inlined comparison values (see the checker rules).
`SchemaCacheDuration`	10 min	How long discovered schema is reused.
`UseTransactionsForWrites`	`true`	Writes run in a transaction that rolls back on a `DbException`.

Methods

GetSchemaAsync(refresh) discovers and caches the schema, guarded by a semaphore.
PlanAsync(intent) asks the model for a QueryPlan (Sql, Parameters, Explanation, TablesUsed) and returns it with a SqlSafetyReport. Nothing runs. The model sees only the allowed tables, the dialect name, and instructions to use @name parameters and no trailing semicolon. A validator runs SqlSafetyChecker on every attempt and also reports any parameter the SQL uses that has no value in Parameters, so both are fed back to the model. MaxAttempts is 3.
QueryAsync(intent) plans, then throws SemanticDatabaseException when planning failed, the model returned an empty Sql (it declined; the explanation is included), or the check failed. Otherwise it executes, or returns the plan when DryRun is set.
ExecutePlanAsync(plan) runs a plan the caller already reviewed. The safety check runs again, against the same table set PlanAsync uses: AllowedTables when set (an empty list blocks every table), otherwise every discovered table.
UpsertAsync(tableName, sourceData, intent) requires AllowWrites, an existing table and, when AllowedTables is set, membership in it. The model maps source fields to columns by meaning and writes one INSERT, UPDATE or MERGE for that table. With several source rows, the prompt asks it to handle the first row only. DryRun returns the plan. The validator on every attempt and the final check both enforce this: they run SqlSafetyChecker with that one table as the allowlist (other tables in AllowedTables do not count), and refuse any other statement kind ("UpsertAsync accepts only one INSERT, UPDATE or MERGE on table; a DELETE statement is not allowed.") and a DELETE anywhere in the text ("... ; remove the DELETE."), for example in a MERGE clause or in a second SQL Server statement.

QueryResult holds the Plan, Safety, Rows (case-insensitive dictionaries), RowsAffected, Truncated, Executed, Planning (the AIResult from planning) and Elapsed. As<T>() maps rows to objects through JSON.

Parameters

The checker collects parameter names from the SQL (@name, :name or $name). A prefix that follows :, @ or a word character does not start a parameter, so PostgreSQL casts (x::int) and T-SQL system variables (@@ROWCOUNT) are not read as parameters.

At execution each name becomes an ADO.NET parameter named @name. Its value is looked up in QueryPlan.Parameters case-insensitively, ignoring a leading @, : or $ on the dictionary key. A name with no entry throws SemanticDatabaseException naming the parameter before the connection is used; an entry whose value is null binds as DBNull. JSON values are converted to CLR strings, longs, doubles and booleans. Binding is always by @name, which some providers do not match against :name or $name in the SQL.

Schema discovery

AdoSchemaDiscovery.DetectDialect looks at the connection's full type name. The rows are tested in this order and the first match wins; MySql comes before SqlClient because the MySql.Data connection is MySql.Data.MySqlClient.MySqlConnection:

Connection type contains	Dialect	Discovery
`Sqlite`	SQLite	`sqlite_master` and `PRAGMA table_info`; includes primary keys
`MySql`	MySQL	`INFORMATION_SCHEMA.COLUMNS` joined to `TABLES`; primary keys from `TABLE_CONSTRAINTS` and `KEY_COLUMN_USAGE`
`SqlClient`	SQL Server (T-SQL)	same
`Npgsql`	PostgreSQL	same
`Oracle` or anything else	Oracle / SQL	`DbConnection.GetSchema("Columns")`

The INFORMATION_SCHEMA path excludes system schemas. After reading columns it runs a second query, INFORMATION_SCHEMA.TABLE_CONSTRAINTS joined to KEY_COLUMN_USAGE where CONSTRAINT_TYPE = 'PRIMARY KEY', and sets ColumnSchema.IsPrimaryKey. If that query fails (a database error, or a provider that lacks the views), keys are left unset and discovery still succeeds; nothing is logged. This path, including the key query, is untested against live SQL Server, PostgreSQL and MySQL servers; the tests cover SQLite only. Discovery opens and closes the connection if it was closed. The connection string is never copied into DatabaseSchema. Supply your own ISchemaDiscovery to the constructor to replace it.

SqlSafetyChecker: a guard, not a parser

SqlSafetyChecker.Check(sql, options, knownTables) first replaces string literals ('...', with '' inside) by '' and removes -- and /* */ comments, so quoted text cannot fool the keyword checks. Quoted names, "name", [name] and `name`, are kept as written, with a doubled closing character ("", ]], ``) staying inside the name. A quote or -- inside a name therefore never opens a literal or a comment that hides the rest of the statement: in SELECT name AS "a'" FROM users; DELETE FROM users; --' the checker still sees the ; and the DELETE. StripCommentsAndStrings(sql, out count) is this step on its own.

Databases do not all read quoting and comments the same way, and the checker reads them one way only. So syntax that one database treats as text and another as code is a violation in itself, with a message that says what to change:

Refused	Why
a backslash right before a quote inside `'...'` (`\'`) or `"..."` (`\"`)	MySQL reads it as an escaped quote, so the text runs on
a `[bracketed]` name that contains `'`, `"`, `, `\`, `$`, `#`, `--`, `/` or `/`	PostgreSQL reads `[ ]` as an array subscript, so the inside is code
`--` followed by anything but whitespace	MySQL does not read `--x` as a comment
a carriage return without a line feed inside a `--` comment	PostgreSQL ends the comment there; SQLite and MySQL do not
`/!` or `/M!`	MySQL and MariaDB run the inside of an executable comment
`/*` inside a block comment	PostgreSQL and SQL Server nest block comments; SQLite and MySQL do not
`#` outside quotes and comments	MySQL starts a comment there (this also refuses SQL Server `#temp` tables)
a dollar quote, `$$` or $tag$ , where a `$` does not follow a letter, digit, `_` or `$`	PostgreSQL quotes text with it; elsewhere `$` is part of a name

Then:

Empty SQL is a violation.
One statement only: a ; other than a trailing one is a violation.
The first word is the StatementKind. Allowed: SELECT, WITH; plus INSERT, UPDATE, DELETE, MERGE when AllowWrites is true. REPLACE is never an allowed kind.
Read-only mode also rejects the data-changing words INSERT, UPDATE, DELETE, MERGE, UPDATETEXT and WRITETEXT anywhere, and REPLACE anywhere it is not followed by ( (the MySQL and SQLite REPLACE INTO statement, not the REPLACE(...) string function). With AllowWrites, a statement whose kind is WITH and that contains one of those words is rejected ("A statement that starts with WITH must only read data; ..."), because it would run as a query: outside a transaction and with no affected-row count.
A deny-list of keywords is always rejected: DROP, TRUNCATE, ALTER, CREATE, GRANT, REVOKE, EXEC, EXECUTE, SHUTDOWN, ATTACH, DETACH, PRAGMA, VACUUM, REINDEX, LOAD_FILE, OUTFILE, DUMPFILE, OPENROWSET, OPENQUERY, BULK, COPY, and the SQL Server statement words BACKUP, DBCC, KILL, RECONFIGURE, WAITFOR, USE and DENY. Each is matched as a whole word, ignoring case, so user_id or backup_date passes. A separate case-insensitive pattern \b(?:sp|xp)_\w+ rejects system and extended procedure names such as sp_executesql and xp_cmdshell.
Table names after FROM, JOIN, INTO, UPDATE, MERGE INTO and DELETE FROM are checked against AllowedTables whenever it is non-null (an empty list makes every referenced table a violation), and against knownTables only when AllowedTables is null. CTE names are exempt. Matching ignores brackets, quotes and backticks, and accepts a match on the last name segment. An unquoted SET after UPDATE is not a table, so an ON CONFLICT (...) DO UPDATE SET upsert (SQLite, PostgreSQL) passes.
With RequireParameters, a statement that has a WHERE, contains string or numeric literals (outside TOP, LIMIT, OFFSET, FETCH FIRST/NEXT), and has no parameters at all is rejected.

Limits, by design and by omission:

It is regex-based. It rejects some valid SQL (a column named copy or bulk) and cannot prove a statement harmless.
The SQL Server statement words are deny-listed at the cost of some valid SQL. A column or alias named exactly use, deny, kill, backup, dbcc, reconfigure or waitfor, quoted or not, is refused too; names that only contain one of them, such as user_id, pass. USE also refuses MySQL's USE INDEX hint and SQL Server's OPTION (USE HINT (...)) and USE PLAN query hints.
Tables listed after a comma (FROM a, b) are not extracted, so only a is checked.
The word after FROM or UPDATE is read as a table even where it is not one: EXTRACT(YEAR FROM col), SUBSTRING(x FROM 1) and MySQL's ON DUPLICATE KEY UPDATE col = ... name a column, which then fails the table check. Only DO UPDATE SET is recognised.
SQL Server runs several statements written one after another without a ;, so the one-statement rule does not see SELECT ... SELECT ... there. Every deny-listed word is still rejected anywhere in the text, and read-only mode rejects every data-changing word too, but a trailing statement made only of words on neither list is not refused for being a second statement; only the table and literal checks apply to it.
The literal rule fires only when the statement has no parameters. One parameter plus inlined literals passes. Literals in an INSERT ... VALUES without WHERE pass.
The procedure pattern also rejects a column or alias that starts with sp_ or xp_.
Keywords and ; inside a quoted name stay visible to the checks, so a name such as "delete" or "a;b" is refused although the statement is harmless.