view release on metacpan or  search on metacpan

share/skills/dbio-duckdb-database/SKILL.md  view on Meta::CPAN

mapped by the DDL layer; reach them via raw SQL / escape hatches.
`datetime_parser_type` is **inherited** (`DateTime::Format::MySQL`) — not
overridden in `DBIO::DuckDB::Storage`. `sqlt_type` returns the DBI driver
name, `DuckDB`.

## Identity / Sequences

DuckDB has no AUTO_INCREMENT/IDENTITY emitted by this driver. Auto-increment
columns get a real `CREATE SEQUENCE` + `DEFAULT nextval('<table>_<col>_seq')`.
Sequences start at `1000000` so manual small-ID inserts don't collide
(DuckDB sequences do **not** auto-advance on explicit inserts the way PG
IDENTITY does). UUID/GUID auto columns default via `uuid()` instead — no
sequence. Introspection detects `nextval(...)` defaults to recover
`is_auto_increment` + sequence name.

## Transactions & MVCC

DuckDB is ACID with MVCC (single-writer, optimistic). Standard
`BEGIN/COMMIT/ROLLBACK`. Savepoints supported: `SAVEPOINT` /
`RELEASE SAVEPOINT` / `ROLLBACK TO SAVEPOINT` (wired in Storage).
`SELECT ... FOR UPDATE` is **not** supported — SQLMaker disables
`_lock_select`. `CHECKPOINT` flushes the WAL.

## Pagination

Standard `LIMIT ? OFFSET ?` — inherited unchanged from `DBIO::SQLMaker`,
no DuckDB override.

## Identifier Quoting & Case Folding

Double-quote identifiers (`sql_quote_char '"'`), PostgreSQL-style. Unquoted
identifiers fold to lowercase; comparisons are case-insensitive for unquoted
names. The driver quotes all identifiers via `DBIO::SQL::Util::_quote_ident`.

## Unicode

DuckDB is UTF-8 native throughout (VARCHAR is UTF-8); no charset/collation
connection setup needed, unlike MySQL.

## Introspection Catalog

`DBIO::DuckDB::Introspect` reads standard `information_schema` views plus
DuckDB system views: `duckdb_tables()`, `duckdb_columns()`,
`duckdb_indexes()`, `duckdb_constraints()`. View bodies come from
`information_schema.views`. Only the `main` schema by default; pass
`catalog` for attached file/DuckLake catalogs. Quack RPC remote catalogs
are **opaque** to these views — use `PRAGMA table_info('remote.tbl')`.

## FK Handling

FKs are **readable but NOT emitted** in install DDL. DuckDB validates the
referenced column tuple against the parent PK/unique *in the same column
order* at CREATE TABLE time; DBIO FK condition hashes are alphabetically
sorted and often mismatch. Since FKs have no runtime effect in 1.x, install
DDL skips them; introspect/diff can still round-trip user-added FKs.

## Testing Env Vars

`DBIO_TEST_DUCKDB_DSN`, `DBIO_TEST_DUCKDB_DBUSER`, `DBIO_TEST_DUCKDB_DBPASS`
(`DBIO::DuckDB::Test`). If `DBIO_TEST_DUCKDB_DSN` is unset, tests default to
`dbi:DuckDB:dbname=:memory:` with no external credentials.