sqldialect

type Dialect interface {
	// Kind names the database, for logs, errors and capability decisions.
	Kind() Kind

	// Rebind turns `?` placeholders into whatever this database expects.
	// SQLite returns the query untouched; PostgreSQL numbers them $1, $2, …
	Rebind(query string) string

	// BlobType is the column type for opaque bytes — a serialized vector.
	BlobType() string

	// JSONText reads a top-level string field out of a JSON column.
	//
	// The fourth thing the two databases genuinely disagree about, and the one
	// that keeps a whole feature from crossing: pkg/graphflow stores a
	// temporal fact's validity inside an edge's `properties` JSON and reads it
	// back with json_extract, which PostgreSQL does not have. Written once
	// here, the same query works on both.
	JSONText(column, key string) string

	// JSONTextGuarded reads a top-level string field and yields NULL when the
	// column holds no JSON at all.
	//
	// The guard is not decoration. `properties` is a TEXT column and an edge
	// written without any carries the empty string; SQLite's json_extract
	// raises "malformed JSON" on it and PostgreSQL's ::jsonb raises "invalid
	// input syntax". Both fail the whole query over one such row, so every
	// call site used to wrap the read in `CASE WHEN json_valid(...)`, which is
	// SQLite-only syntax and the reason graph retrieval did not run on
	// PostgreSQL at all.
	//
	// What the two guards test is not identical: SQLite asks whether the text
	// parses, PostgreSQL only whether it is non-empty. They coincide for every
	// row this codebase can write — properties come from json.Marshal — and a
	// genuinely malformed row is corruption that should surface as an error
	// rather than be silently read as NULL.
	JSONTextGuarded(column, key string) string

	// JSONFlag reads a boolean-ish field as 1 or 0, guarded the same way.
	//
	// Separate from JSONTextGuarded because the two databases disagree about
	// what a JSON `true` reads back as: SQLite's json_extract gives the
	// integer 1, PostgreSQL's ->> gives the text 'true'. A call site comparing
	// the raw read against 1 is correct on SQLite and quietly false on
	// PostgreSQL — inferred edges would have looked explicit, and every
	// inference rule would have re-derived them on top of themselves.
	JSONFlag(column, key string) string

	// JSONEachEntry expands a JSON object column into one row per top-level
	// field, as a FROM-clause fragment joining the rows of `je(key, value)`
	// onto the table already named.
	//
	// A fragment rather than an expression because this is the one thing here
	// that is not a scalar: enumerating the keys of a JSON object is a join on
	// both databases, and the two spell the join itself differently — SQLite
	// has a table-valued json_each that goes in a comma join, PostgreSQL needs
	// CROSS JOIN LATERAL and a column alias list. So the fragment begins with
	// its own separator and is appended straight after the table.
	//
	// Every other JSON helper here reads a key the caller already knows. This
	// answers the opposite question — which keys are there at all — which is
	// what anything deriving a shape from stored data has to ask first, and
	// what nothing in this codebase could ask before.
	//
	// Guarded the way JSONTextGuarded is, and one step further: a column that
	// holds no JSON, or holds JSON that is not an object, yields no rows
	// rather than failing the statement. Both databases raise on json_each of
	// a scalar, and a single such row would take the whole scan down.
	JSONEachEntry(column string) string

	// JSONArrayContains tests whether a JSON array field contains a value,
	// as an expression carrying exactly one `?` placeholder for it.
	//
	// SQLite reaches for json_each and a correlated subquery; PostgreSQL has
	// a containment operator. Neither spelling survives on the other, and the
	// SQLite one failed on PostgreSQL with "syntax error at end of input" —
	// an error that names nothing, because the parser gave up at `json_each`.
	JSONArrayContains(column, key string) string

	// AutoIncrementPK is the column definition for a surrogate integer key
	// the database assigns.
	//
	// SQLite spells it INTEGER PRIMARY KEY AUTOINCREMENT, which PostgreSQL
	// rejects at the parser. The one place this appears is the ontology action
	// audit table, created lazily on the first ontology_action_apply — so on
	// PostgreSQL that DDL failed, the table never existed, and every action
	// apply failed with it. Nothing caught it because the action tests run on
	// SQLite and the PostgreSQL tool coverage only listed action types.
	AutoIncrementPK() string

	// JSONSet writes a JSON value into a top-level field, as an expression
	// carrying one `?` placeholder for the new value (itself JSON text).
	//
	// SQLite's json_set and PostgreSQL's jsonb_set differ in name, in how the
	// path is spelled, and in whether the result needs casting back to text.
	JSONSet(column, key string) string

	// IsDuplicateColumn reports whether err is "this column already exists",
	// which an idempotent ALTER TABLE ADD COLUMN must swallow.
	//
	// A method rather than a string match at the call site: SQLite says
	// "duplicate column name: x" and PostgreSQL says `column "x" of relation
	// "y" already exists`. The original code matched the SQLite wording
	// inline, so the same migration would have failed on its second start
	// against PostgreSQL — the kind of thing that only shows up on the second
	// start, in production.
	IsDuplicateColumn(err error) bool
}

type Execer interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

type Kind string